<?php
include("../public/impOpreation.php");
include("../library/uploadImages.php");
include '../library/Classes/PHPExcel/IOFactory.php';

$do = $_GET['do'];
$today = date("Y-m-d H:i:s");
$date = date("Y-m-d");

if (empty($do)) {
  $smarty->display("header.html");

  if ($_SESSION['charityids'])
    $searchQuery .= ' and charities.id in(' . $_SESSION['charityids'] . ')';

  $allCharities = R::getAll('select * from charities where del < 2' . $searchQuery);
  $smarty->assign('allCharities', $allCharities);

  $smarty->display("subventionPayview/add.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} else if ($do == "show") {
  header('location:subventionPayController.php');
} elseif ($do == "report") {
  $smarty->display("header.html");

  if ($_SESSION['charityids'])
    $searchQuery .= ' and charities.id in(' . $_SESSION['charityids'] . ')';

  $charitiesCount = R::getCell('select count(id) from charities where del < 2');
  $smarty->assign('charitiesCount', $charitiesCount);

  $beneficiariesCount = R::getCell('select count(id) from beneficiaries where del < 2');
  $smarty->assign('beneficiariesCount', $beneficiariesCount);

  $allPaid = R::getAll('select * from subventionpay where del < 2');
  $smarty->assign('paidCount', count($allPaid));
  $moneyCount = R::getCell('select sum(financial_aid) from subventionpay where del < 2');
  $smarty->assign('moneyCount', $moneyCount);

  $repeated = R::getAll('select count(id) from subventionpay where del < 2 group by benefeciary_id having count(id) > 1');
  $smarty->assign('reCounter', count($repeated));

  $smarty->display("subventionPayview/report.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} else if ($do == 'reportajax') {
  showajax();
} elseif ($do == "getReportBen") {
  $benIds = filter_input(INPUT_POST, 'benIds');
  $benIds = rtrim($benIds,',');
  $allBen = R::getAll('select b.name, GROUP_CONCAT(ch.charityname) as charityname, SUM(p.financial_aid) as financial_aid,
                          IF(p.inkind_aid REGEXP "^[0-9]+$",SUM(p.inkind_aid),"")AS inkindN,
                          IF(p.inkind_aid NOT REGEXP "^[0-9]+$",GROUP_CONCAT(p.inkind_aid),"")AS inkindS
                       FROM beneficiaries b
                       JOIN subventionpay p ON p.benefeciary_id = b.id
                       JOIN charities ch ON ch.id = p.charity_id
                       where b.id in(' . $benIds . ')
                       GROUP BY p.benefeciary_id');
  $smarty->assign('allBen', $allBen);
  $smarty->display("subventionPayview/report_data.html");
} else if ($do == 'savedata') {
  savedata();
} else if ($do == 'removecontroller') {
  removecontroller();
} elseif ($do == "getSubvention") {
  $charity_id = filter_input(INPUT_POST, 'id');
  $month = filter_input(INPUT_POST, 'month');
  $allBen = R::getAll('SELECT b.id as beneficiary_id,b.del as benDel,b.name, b.idnumber,subvention.* ,subventionpay.id as payId, subventionpay.month,
  (CASE WHEN COUNT(subventionpay.id) > 0 THEN 1 ELSE 0 END)  AS paid , subventionpay.financial_aid AS paidF, subventionpay.inkind_aid As paidI, subventionpay.notes as paidN
   from beneficiaries b left join subvention ON subvention.beneficier_id = b.id
   left join subventionpay ON b.id = subventionpay.benefeciary_id AND subventionpay.month = "' . $month . '"
   where b.charity_id = ' . $charity_id . ' and b.del < 2 
   GROUP BY b.id');

  if (count($allBen) > 0) {
    $smarty->assign('allBen', $allBen);
    $smarty->assign('month', $month);
    $smarty->display('subventionPayview/benef_tbl.html');
  } else {
    echo -1;
  }
} else if ($do == 'gettabledata') {
  gettabledata();
} elseif ($do == "printPayedSubvention") {
  $charity_id = filter_input(INPUT_POST, 'id');
  $charityName = R::getCell('select charityname from charities where id = ' . $charity_id);
  $month = filter_input(INPUT_POST, 'month');
  $allBen = R::getAll('SELECT b.id as beneficiary_id,b.del as benDel,b.name, b.idnumber,subvention.* ,subventionpay.id as payId, subventionpay.month, subventionpay.financial_aid AS paidF, subventionpay.inkind_aid As paidI, subventionpay.notes as paidN
   from beneficiaries b join subvention ON subvention.beneficier_id = b.id
   left join subventionpay ON subventionpay.benefeciary_id = b.id 
   where b.charity_id = ' . $charity_id . ' and subventionpay.month = "' . $month . '" and b.del < 2 GROUP BY b.id');
   
  if (count($allBen) > 0) {
    $smarty->assign('allBen', $allBen);
    $smarty->assign('charityName', $charityName);
    $smarty->assign('month', $month);
    $smarty->display('subventionPayview/print.html');
  } else {
    echo -1;
  }
} elseif ($do == "printIDs") {
  $ids = filter_input(INPUT_POST, 'id');
  $ids = rtrim($ids, ',');
  $allBen = R::getAll('SELECT * from beneficiaries where id in (' . $ids . ')');
  if (count($allBen) > 0) {
    $smarty->assign('allBen', $allBen);
    $smarty->display('subventionPayview/print_ids.html');
  } else {
    echo -1;
  }
} elseif ($do == "addexcel") {
  include_once("../public/authentication.php");

  if ($_SESSION['charityids'])
    $searchQuery .= ' and charities.id in(' . $_SESSION['charityids'] . ')';

  $allCharities = R::getAll('select * from charities where del < 2' . $searchQuery);
  $smarty->assign('allCharities', $allCharities);

  $allTypes = R::getAll('select * from guaranteetype where del < 2');
  $smarty->assign('allTypes', $allTypes);

  $smarty->display("header.html");
  $smarty->display("subventionPayview/uploadexcel.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} elseif ($do == "addfromexcel") {
  include_once("../public/authentication.php");
  try {
    addFromExcel();
    header("location:subventionPayController.php");
  } catch (Exception $e) {
    header("location:subventionPayController.php");
  }
}


function savedata()
{
  $today = date("Y-m-d H:i:s");
  $userid = $_SESSION['userid'];
  $charity_id = filter_input(INPUT_POST, 'charity_id');
  $benefeciary_id = filter_input(INPUT_POST, 'benefeciary_id');
  $financial_aid = filter_input(INPUT_POST, 'financial_aid');
  $inkind_aid = filter_input(INPUT_POST, 'inkind_aid');
  $notes = filter_input(INPUT_POST, 'notes');
  $month = filter_input(INPUT_POST, 'month');
  if ($benefeciary_id && $charity_id) {
    $paid = R::getCell('select id from subventionpay where charity_id = ' . $charity_id . ' and benefeciary_id = ' . $benefeciary_id . ' and month = "' . $month . '"');
    if ($paid != 1) {
      $subventionPay = R::dispense('subventionpay');
      $subventionPay->del = 0;
      $subventionPay->sysdate = $today;
      $subventionPay->user_id = $userid;
      $subventionPay->charity_id = $charity_id;
      $subventionPay->benefeciary_id = $benefeciary_id;
      $subventionPay->month = $month;
      $subventionPay->financial_aid = $financial_aid;
      $subventionPay->inkind_aid = $inkind_aid;
      $subventionPay->notes = $notes;

      $subventionPayid = R::store($subventionPay);
    }
  }
  if ($subventionPayid) {
    echo 1;
  } else {
    echo 0;
  }
}

function removecontroller()
{
  $today = date("Y-m-d H:i:s");
  $date = date('Y-m-d');
  $userid = $_SESSION['userid'];
  $id = filter_input(INPUT_POST, 'id');
  $tables = R::load('subventionPay', $id);
  $tables->del = 2;
  $tables->del_date = $today;
  $tables->deluserid = $userid;
  try {
    R::store($tables);
    echo 1;
  } catch (Exception $e) {
    echo 0;
  }
}

function gettabledata()
{
  $table = $_POST['table'];
  $tableid = $_POST['tableid'];
  $tableval = $_POST['tableval'];
  $join = $_POST['join'];
  $tableid = $table . "." . $tableid;
  $getdata = R::getRow("SELECT * FROM $table $join WHERE $tableid = ?", [$tableval]);
  echo json_encode($getdata);
}

function showajax()
{
  global $smarty;

  try {

    $searchQuery = '';
    // if ($_SESSION['charityids'])
    //   $searchQuery .= ' and subventionpay.charity_id in(' . $_SESSION['charityids'] . ')';
    $allPaid = R::getAll('select *,count(id) as counter from subventionpay where del < 2 group by benefeciary_id');
    // var_dump($allPaid);
    $benArr = [];
    $reCounter = 0;
    $maxCount = 1;
    foreach ($allPaid as $paid) {
      if ($paid['counter'] > 1) {
        $reCount = $paid['counter'];
        $chCount = R::getCell('SELECT count(DISTINCT charity_id) from subventionpay where benefeciary_id = ' . $paid['benefeciary_id'] . '');
        if ($reCount > $chCount) {
          $reCount = $chCount;
        }
        $benArr[$reCount]['ids'] .= $paid['benefeciary_id'] . ',';
        $benArr[$reCount][] = $paid['benefeciary_id'];
        if ($maxCount < $reCount)
          $maxCount = $paid['counter'];
      } else {
        $benArr[1]['ids'] .= $paid['benefeciary_id'] . ',';
        $benArr[1][] = $paid['benefeciary_id'];
      }
    }

    foreach ($benArr as $key => $value) {
      $benArr[$key]['counter'] = count($value) - 1;
    }
    $smarty->assign('counterArr', $benArr);
    $smarty->display("subventionPayview/report_ajax.html");
  } catch (Exception $e) {
    echo -1;
  }
}
function addFromExcel()
{
  ## upload file temporarely
  $handle = new upload($_FILES['subvsheet']);
  $excelfileName = uploadfile($handle, "../upload/subventionpay");
  $inputFileName = "../upload/subventionpay/" . $excelfileName;

  $charity_id = filter_input(INPUT_POST, 'charity_id');
  $month = filter_input(INPUT_POST, 'month');
  //Transaction
  $mytransactions = new Transaction();
  try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);

    $objPHPExcel = $objReader->load($inputFileName);

    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    for ($row = 4; $row <= $highestRow; $row++) {
      //  Read a row of data into an array
      $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
      $rowData = $rowData[0];

      $col = 0;
      $idNo = $rowData[$col];
      $col++;
      $money = $rowData[$col];
      $col++;
      $value = $rowData[$col];
      $col++;
      $guarantee_type_id = $rowData[$col];
      $col++;
      $comment = $rowData[$col];
      $col++;

      $ben = R::findOne('beneficiaries', 'where idnumber = "' . $idNo . '"');
      if ($ben) {
        $benId = $ben->id;
        if ($benId && $charity_id) {
          $paid = R::getCell('select COUNT(id) from subventionpay where charity_id = ' . $charity_id . ' and benefeciary_id = ' . $benId . ' and month = "' . $month . '"');
          if ($paid < 1) {
            $subventionPay = R::dispense('subventionpay');
            $subventionPay->del = 0;
            $subventionPay->sysdate = date("Y-m-d H:i:s");
            $subventionPay->user_id = $_SESSION['userid'];
            $subventionPay->charity_id = $charity_id;
            $subventionPay->benefeciary_id = $benId;
            $subventionPay->month = $month;
            $subventionPay->financial_aid = $money;
            $subventionPay->inkind_aid = $value;
            $subventionPay->guarantee_type_id = $guarantee_type_id;
            R::store($subventionPay);
          }
        }
      }
    }
    unlink($inputFileName);
    $mytransactions->commit();
  } catch (Exception $e) {
    echo $e;
    $mytransactions->rollback($e);
  }
}
