<?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");
  $allAreas = R::getAll('select * from area');
  $smarty->assign('allAreas', $allAreas);

  $allDisease = R::getAll('select * from disease');
  $smarty->assign('allDisease', $allDisease);

$searchQuery = "";
  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("beneficiariesview/add.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
}elseif ($do == "addSimple") {
  $smarty->display("header.html");

$searchQuery = "";
  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("beneficiariesview/add_smpl.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} elseif ($do == "show") {
  include_once("../public/authentication.php");
  $smarty->assign('date', $date);
  $smarty->display("header.html");

  $searchQuery = "";
  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("beneficiariesview/show.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} elseif ($do == "edit") {
  include_once("../public/authentication.php");

  $id = filter_input(INPUT_GET, 'id');
  $data = R::load('beneficiaries', $id);
  $smarty->assign('data', $data);

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

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

  $allAreas = R::getAll('select * from area');
  $smarty->assign('allAreas', $allAreas);

  $allDisease = R::getAll('select * from disease');
  $smarty->assign('allDisease', $allDisease);

  $allFamily = R::getAll('select * from beneficiaries_family where beneficiaries_id = ' . $id);
  $smarty->assign('allFamily', $allFamily);
  $allEco = R::getAll('select * from beneficiaries_eco where beneficiaries_id = ' . $id);
  $smarty->assign('allEco', $allEco);

  $smarty->display("header.html");
  $smarty->display("beneficiariesview/edit.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} else if ($do == 'savedata') {
  savedata();
} else if ($do == 'showajax') {
  showajax();
} else if ($do == 'removecontroller') {
  removecontroller();
} elseif ($do == "addexcel") {
  include_once("../public/authentication.php");

  $searchQuery = "";
  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("header.html");
  $smarty->display("beneficiariesview/uploadexcel.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
}elseif ($do == "addexceltkafol") {
  include_once("../public/authentication.php");

$smarty->assign('tkafol', 1);
  
  $allCharities = R::getAll('select * from charities where id = 0');
  $smarty->assign('allCharities', $allCharities);
  $smarty->display("header.html");
  $smarty->display("beneficiariesview/uploadexcel.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} elseif ($do == "search") {
  include_once("../public/authentication.php");

  $smarty->display("header.html");
  $smarty->display("beneficiariesview/search.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} else if ($do == 'getBenData') {
  $idNo = filter_input(INPUT_POST, 'idno');
  if ($idNo)
  {
    $log = R::dispense('charitysearchlog');
    $log->user_id	= $_SESSION['userid'];
    $log->charity_id	= $_SESSION['charityids'];
    $log->sysdate	= date("Y-m-d H:i:s");
    $log->idnumber	= $idNo;

    $ben = R::getRow('select * from beneficiaries where idnumber = "' . $idNo . '"');
    
    if ($ben) {
    $log->benefeciary_id	= $ben['id'];
  
      $smarty->assign("benName", $ben['name']);
      $smarty->assign("benPhone", $ben['phone_no']);
      // $charityName = R::getCell('select charityname from charities where id = ' . $ben['charity_id']);
      // $subvention = R::getRow('select * from subvention where beneficier_id = ' . $ben['id']);
      $allData = R::getAll('SELECT charityname,charityphone, p.financial_aid , p.inkind_aid ,p.month
                          FROM subventionpay p JOIN charities ON p.charity_id = charities.id 
                          WHERE p.benefeciary_id =' . $ben['id']);
      $smarty->assign("allData", $allData);
      $smarty->display("beneficiariesview/search_data.html");
    } else
      echo -1;
      R::store($log);
  }else
  echo -1;
  
}  elseif ($do == "searchLog") {
  include_once("../public/authentication.php");
  
  $smarty->display("header.html");
  $smarty->display("beneficiariesview/search_log.html");
  $smarty->assign("charity", 1);
  $smarty->display("footer.html");
} else if ($do == 'getLogData') {
  $idNo = filter_input(INPUT_POST, 'idno');
  
  if ($idNo)
  {
    $ben = R::getRow('select * from charitysearchlog where idnumber = "' . $idNo . '"');
    
    if ($ben) {
      $smarty->assign("benName", $ben['name']);
      $smarty->assign("benName", $ben['name']);
      $smarty->assign("benPhone", $ben['phone_no']);
      // $charityName = R::getCell('select charityname from charities where id = ' . $ben['charity_id']);
      // $subvention = R::getRow('select * from subvention where beneficier_id = ' . $ben['id']);
      $allData = R::getAll('SELECT charityname,charityphone, p.financial_aid , p.inkind_aid ,p.month
                          FROM subventionpay p JOIN charities ON p.charity_id = charities.id 
                          WHERE p.benefeciary_id =' . $ben['id']);
      $smarty->assign("allData", $allData);
      $smarty->display("beneficiariesview/search_data.html");
    } else
      echo -1;
  }else
  echo -1;
  
}elseif ($do == "addfromexcel") {
  include_once("../public/authentication.php");
  try {
  if(isset($_POST['tkafol']) && $_POST['tkafol'] == 1)
    addFromExcelTkafol();   
  else
    addFromExcel();
    header("location:?do=show");
  } catch (Exception $e) {
    header("location:?do=show");
  }
} else if ($do == "addappend") {
  $itr = filter_input(INPUT_POST, 'itr');
  $dataitr = filter_input(INPUT_POST, 'dataitr');
  $container = filter_input(INPUT_POST, 'container');
  $smarty->assign('itr', $itr);
  $smarty->assign('dataitr', $dataitr);
  $smarty->assign('charity', 1);
  $smarty->display("beneficiariesview/" . $container . "_row.html");
} else if ($do == 'savearea') {
  savearea();
} else if ($do == 'savedisease') {
  savedisease();
} else if ($do == 'gettabledata') {
  gettabledata();
} else if ($do == 'getselectdata') {
  getselectdata();
} else if ($do == 'getselectmultiple') {
  getselectmultiple();
} else if ($do == 'getMultipledit') {
  getMultipledit();
} else if ($do == 'getMultidata') {
  getMultidata();
} else if ($do == 'updateVal') {
  $id = filter_input(INPUT_POST, 'id');
  $val = filter_input(INPUT_POST, 'val');
  $data = R::count('disease', " name = ? and del < 2 and id != ?", [$val, $id]);
  if ($data < 1) {
    try {
      R::exec('UPDATE disease SET name=? where id = ?', [$val, $id]);
      echo 1;
    } catch (Exception $e) {
      echo 0;
    }
  } else {
    echo -1;
  }
} elseif ($do == "sucess") {
  //here the smarty templates
  $smarty->display("succes.html");
} elseif ($do == "error") {
  //here the smarty templates
  $smarty->display("error.html");
}


function savedata()
{
  $today = date("Y-m-d H:i:s");
  $date = date('Y-m-d');
  $userid = $_SESSION['userid'];

  $area_id = filter_input(INPUT_POST, 'area_id');
  $charity_id = filter_input(INPUT_POST, 'charity_id');
  $name = filter_input(INPUT_POST, 'b_name');
  $nickname = filter_input(INPUT_POST, 'nickname');
  $age = filter_input(INPUT_POST, 'age');
  $idnumber = filter_input(INPUT_POST, 'idnumber');
  $phone_no = filter_input(INPUT_POST, 'phone_no');
  $address = filter_input(INPUT_POST, 'address');
  $marital_status = filter_input(INPUT_POST, 'marital_status');
  $work = filter_input(INPUT_POST, 'work');
  $workplace = filter_input(INPUT_POST, 'workplace');
  $diseas = filter_input(INPUT_POST, 'diseas');
  $status_description = filter_input(INPUT_POST, 'status_description');
  $social_status = filter_input(INPUT_POST, 'social_status');
  $health_status = filter_input(INPUT_POST, 'health_status');
  $floors_no = filter_input(INPUT_POST, 'floors_no');
  $rooms_no = filter_input(INPUT_POST, 'rooms_no');
  $rent = filter_input(INPUT_POST, 'rent');
  $bathroom = filter_input(INPUT_POST, 'bathroom');
  $flooring = filter_input(INPUT_POST, 'flooring');
  $ceiling = filter_input(INPUT_POST, 'ceiling');
  $walls = filter_input(INPUT_POST, 'walls');
  $lighting = filter_input(INPUT_POST, 'lighting');
  $hall = filter_input(INPUT_POST, 'hall');
  $bedroom = filter_input(INPUT_POST, 'bedroom');
  $bathroom_desc = filter_input(INPUT_POST, 'bathroom_desc');
  $kitchen = filter_input(INPUT_POST, 'kitchen');
  /////////////Machines//////////////
  $cooker = filter_input(INPUT_POST, 'cooker');
  $cooker_no = filter_input(INPUT_POST, 'cooker_no');
  $cooker_status = filter_input(INPUT_POST, 'cooker_status');
  $cooker_notes = filter_input(INPUT_POST, 'cooker_notes');
  $washer = filter_input(INPUT_POST, 'washer');
  $washer_no = filter_input(INPUT_POST, 'washer_no');
  $washer_status = filter_input(INPUT_POST, 'washer_status');
  $washer_notes = filter_input(INPUT_POST, 'washer_notes');
  $fridge = filter_input(INPUT_POST, 'fridge');
  $fridge_no = filter_input(INPUT_POST, 'fridge_no');
  $fridge_status = filter_input(INPUT_POST, 'fridge_status');
  $fridge_notes = filter_input(INPUT_POST, 'fridge_notes');
  $fan = filter_input(INPUT_POST, 'fan');
  $fan_no = filter_input(INPUT_POST, 'fan_no');
  $fan_status = filter_input(INPUT_POST, 'fan_status');
  $fan_notes = filter_input(INPUT_POST, 'fan_notes');
  $phone = filter_input(INPUT_POST, 'phone');
  $phone_nu = filter_input(INPUT_POST, 'phone_nu');
  $phone_status = filter_input(INPUT_POST, 'phone_status');
  $phone_notes = filter_input(INPUT_POST, 'phone_notes');
  $casset = filter_input(INPUT_POST, 'casset');
  $casset_no = filter_input(INPUT_POST, 'casset_no');
  $casset_status = filter_input(INPUT_POST, 'casset_status');
  $casset_notes = filter_input(INPUT_POST, 'casset_notes');
  $blender = filter_input(INPUT_POST, 'blender');
  $blender_no = filter_input(INPUT_POST, 'blender_no');
  $blender_status = filter_input(INPUT_POST, 'blender_status');
  $blender_notes = filter_input(INPUT_POST, 'blender_notes');
  $tv = filter_input(INPUT_POST, 'tv');
  $tv_no = filter_input(INPUT_POST, 'tv_no');
  $tv_status = filter_input(INPUT_POST, 'tv_status');
  $tv_notes = filter_input(INPUT_POST, 'tv_notes');
  $machine_name = filter_input(INPUT_POST, 'machine_name');
  $machine_no = filter_input(INPUT_POST, 'machine_no');
  $machine_status = filter_input(INPUT_POST, 'machine_status');
  $machine_notes = filter_input(INPUT_POST, 'machine_notes');
  /////////////Furniture//////////////////
  $bed = filter_input(INPUT_POST, 'bed');
  $bed_no = filter_input(INPUT_POST, 'bed_no');
  $bed_status = filter_input(INPUT_POST, 'bed_status');
  $bed_notes = filter_input(INPUT_POST, 'bed_notes');
  $wardrobe = filter_input(INPUT_POST, 'wardrobe');
  $wardrobe_no = filter_input(INPUT_POST, 'wardrobe_no');
  $wardrobe_status = filter_input(INPUT_POST, 'wardrobe_status');
  $wardrobe_notes = filter_input(INPUT_POST, 'wardrobe_notes');
  $couch = filter_input(INPUT_POST, 'couch');
  $couch_no = filter_input(INPUT_POST, 'couch_no');
  $couch_status = filter_input(INPUT_POST, 'couch_status');
  $couch_notes = filter_input(INPUT_POST, 'couch_notes');
  $salon = filter_input(INPUT_POST, 'salon');
  $salon_no = filter_input(INPUT_POST, 'salon_no');
  $salon_status = filter_input(INPUT_POST, 'salon_status');
  $salon_notes = filter_input(INPUT_POST, 'salon_notes');
  $chair = filter_input(INPUT_POST, 'chair');
  $chair_no = filter_input(INPUT_POST, 'chair_no');
  $chair_status = filter_input(INPUT_POST, 'chair_status');
  $chair_notes = filter_input(INPUT_POST, 'chair_notes');
  $library = filter_input(INPUT_POST, 'library');
  $library_no = filter_input(INPUT_POST, 'library_no');
  $library_status = filter_input(INPUT_POST, 'library_status');
  $library_notes = filter_input(INPUT_POST, 'library_notes');
  $carpet = filter_input(INPUT_POST, 'carpet');
  $carpet_no = filter_input(INPUT_POST, 'carpet_no');
  $carpet_status = filter_input(INPUT_POST, 'carpet_status');
  $carpet_notes = filter_input(INPUT_POST, 'carpet_notes');
  $mat = filter_input(INPUT_POST, 'mat');
  $mat_no = filter_input(INPUT_POST, 'mat_no');
  $mat_status = filter_input(INPUT_POST, 'mat_status');
  $mat_notes = filter_input(INPUT_POST, 'mat_notes');
  $blanket = filter_input(INPUT_POST, 'blanket');
  $blanket_no = filter_input(INPUT_POST, 'blanket_no');
  $blanket_status = filter_input(INPUT_POST, 'blanket_status');
  $blanket_notes = filter_input(INPUT_POST, 'blanket_notes');

  $beneficiaries_needs = filter_input(INPUT_POST, 'beneficiaries_needs');
  $observer_needs = filter_input(INPUT_POST, 'observer_needs');
  $nearest_shop = filter_input(INPUT_POST, 'nearest_shop');
  $nearest_neighbour = filter_input(INPUT_POST, 'nearest_neighbour');
  $other_opinion = filter_input(INPUT_POST, 'other_opinion');
  $observer_opinion = filter_input(INPUT_POST, 'observer_opinion');
  $final_opinion = filter_input(INPUT_POST, 'final_opinion');
  $reasons = filter_input(INPUT_POST, 'reasons');

  $beneficiariesid = filter_input(INPUT_POST, 'beneficiariesid');
  $edit = 0;
  if (!$beneficiariesid) {
    $beneficiaries = R::dispense('beneficiaries');
    $beneficiaries->del = 0;
    $beneficiaries->sysdate = $today;
    $beneficiaries->user_id = $userid;
    $beneficiaries->del_date = '';
    $beneficiaries->deluserid = '';
    $handle = new upload($_FILES['image']);
    if($handle)
        $image = uploadImages($handle, "../views/default/images/beneficiaries", 300, 300);
  } else {
    $edit = 1;
    $beneficiaries = R::load('beneficiaries', $beneficiariesid);

    $beneficiaries->del = 1;
    $beneficiaries->update_date = $today;
    $beneficiaries->updateuserid = $userid;

    $handle = new upload($_FILES['image']);
    $image = updateImages($handle, "oldimage", "../views/default/images/beneficiaries", 300, 300);
    unlink("../views/default/images/beneficiaries" . $beneficiaries->image);
  }
  if ($area_id > 0)
    $beneficiaries->area_id = $area_id;
  $beneficiaries->charity_id = $charity_id;
  if ($image)
    $beneficiaries->image = $image;
  $beneficiaries->name  = $name;
  $beneficiaries->nickname = $nickname;
  $beneficiaries->phone_no = $phone_no;
  $beneficiaries->address = $address;
  $beneficiaries->age = $age;
  $beneficiaries->idnumber = $idnumber;
  $beneficiaries->marital_status = $marital_status;
  $beneficiaries->work = $work;
  $beneficiaries->workplace = $workplace;
  $beneficiaries->diseas = $diseas;
  $beneficiaries->status_description = $status_description;
  $beneficiaries->social_status = $social_status;
  $beneficiaries->health_status = $health_status;
  $beneficiaries->floors_no = $floors_no;
    if ($rooms_no > 0)
    $beneficiaries->rooms_no = $rooms_no;
  if ($rent > 0)
    $beneficiaries->rent = $rent;
  $beneficiaries->bathroom = $bathroom;
  $beneficiaries->flooring = $flooring;
  $beneficiaries->ceiling = $ceiling;
  $beneficiaries->walls = $walls;
  $beneficiaries->lighting = $lighting;
  $beneficiaries->hall = $hall;
  $beneficiaries->bedroom = $bedroom;
  $beneficiaries->bathroom_desc = $bathroom_desc;
  $beneficiaries->kitchen = $kitchen;
  $beneficiaries->cooker = $cooker;
  $beneficiaries->cooker_no = $cooker_no;
  $beneficiaries->cooker_status = $cooker_status;
  $beneficiaries->cooker_notes = $cooker_notes;
  $beneficiaries->washer = $washer;
  $beneficiaries->washer_no = $washer_no;
  $beneficiaries->washer_status = $washer_status;
  $beneficiaries->washer_notes = $washer_notes;
  $beneficiaries->fridge = $fridge;
  $beneficiaries->fridge_no = $fridge_no;
  $beneficiaries->fridge_status = $fridge_status;
  $beneficiaries->fridge_notes = $fridge_notes;
  $beneficiaries->fan = $fan;
  $beneficiaries->fan_no = $fan_no;
  $beneficiaries->fan_status = $fan_status;
  $beneficiaries->fan_notes = $fan_notes;
  $beneficiaries->phone = $phone;
  $beneficiaries->phone_nu = $phone_nu;
  $beneficiaries->phone_status = $phone_status;
  $beneficiaries->phone_notes = $phone_notes;
  $beneficiaries->casset = $casset;
  $beneficiaries->casset_no = $casset_no;
  $beneficiaries->casset_status = $casset_status;
  $beneficiaries->casset_notes = $casset_notes;
  $beneficiaries->blender = $blender;
  $beneficiaries->blender_no = $blender_no;
  $beneficiaries->blender_status = $blender_status;
  $beneficiaries->blender_notes = $blender_notes;
  $beneficiaries->tv = $tv;
  $beneficiaries->tv_no = $tv_no;
  $beneficiaries->tv_status = $tv_status;
  $beneficiaries->tv_notes = $tv_notes;
  $beneficiaries->machine_name = $machine_name;
  $beneficiaries->machine_no = $machine_no;
  $beneficiaries->machine_status = $machine_status;
  $beneficiaries->machine_notes = $machine_notes;
  $beneficiaries->bed = $bed;
  $beneficiaries->bed_no = $bed_no;
  $beneficiaries->bed_status = $bed_status;
  $beneficiaries->bed_notes = $bed_notes;
  $beneficiaries->wardrobe = $wardrobe;
  $beneficiaries->wardrobe_no = $wardrobe_no;
  $beneficiaries->wardrobe_status = $wardrobe_status;
  $beneficiaries->wardrobe_notes = $wardrobe_notes;
  $beneficiaries->couch = $couch;
  $beneficiaries->couch_no = $couch_no;
  $beneficiaries->couch_status = $couch_status;
  $beneficiaries->couch_notes = $couch_notes;
  $beneficiaries->salon = $salon;
  $beneficiaries->salon_no = $salon_no;
  $beneficiaries->salon_status = $salon_status;
  $beneficiaries->salon_notes = $salon_notes;
  $beneficiaries->chair = $chair;
  $beneficiaries->chair_no = $chair_no;
  $beneficiaries->chair_status = $chair_status;
  $beneficiaries->chair_notes = $chair_notes;
  $beneficiaries->library = $library;
  $beneficiaries->library_no = $library_no;
  $beneficiaries->library_status = $library_status;
  $beneficiaries->library_notes = $library_notes;
  $beneficiaries->carpet = $carpet;
  $beneficiaries->carpet_no = $carpet_no;
  $beneficiaries->carpet_status = $carpet_status;
  $beneficiaries->carpet_notes = $carpet_notes;
  $beneficiaries->mat = $mat;
  $beneficiaries->mat_no = $mat_no;
  $beneficiaries->mat_status = $mat_status;
  $beneficiaries->mat_notes = $mat_notes;
  $beneficiaries->blanket = $blanket;
  $beneficiaries->blanket_no = $blanket_no;
  $beneficiaries->blanket_status = $blanket_status;
  $beneficiaries->blanket_notes = $blanket_notes;
  $beneficiaries->beneficiaries_needs = $beneficiaries_needs;
  $beneficiaries->observer_needs = $observer_needs;
  $beneficiaries->nearest_shop = $nearest_shop;
  $beneficiaries->nearest_neighbour = $nearest_neighbour;
  $beneficiaries->other_opinion = $other_opinion;
  $beneficiaries->observer_opinion = $observer_opinion;
  $beneficiaries->final_opinion = $final_opinion;
  $beneficiaries->reasons = $reasons;

  try {
    $beneficiariesid = R::store($beneficiaries);
    saveFamilyData($beneficiariesid, $edit);
    saveEcoData($beneficiariesid, $edit);
    echo 1;
  } catch (Exception $e) {
      echo $e;
    echo 0;
  }
}
function showajax()
{
  $columns = array('id', 'name', 'phone_no', 'idnumber', 'charity_id', 'sysdate', 'id', 'id');
  $start_date = $_POST['start_date'];
  $end_date   = $_POST['end_date'];
  $del        = $_POST['del'];
  $data1      = $_POST['data1'];
  $data2      = $_POST['data2'];
  $data3      = $_POST['data3'];
  $chID      = $_POST['chID'];

  $searchQuery = " ";
  if ($data1 != '') {
    $searchQuery .=  " and beneficiaries.id = " . $data1 . " ";
  }
  if ($data2 != '') {
    $searchQuery .=  " and beneficiaries.idnumber like '%" . $data2 . "%' ";
  }
  if ($data3 != '') {
    $searchQuery .=  " and beneficiaries.id =" . $data3 . " ";
  }
  if ($chID != '') {
    $searchQuery .=  " and beneficiaries.charity_id =" . $chID . " ";
  }

  if ($del == '') {
    $searchQuery .= " and beneficiaries.del < 2 ";
  }

  if ($start_date != '') {
    $searchQuery .= 'and  DATE(beneficiaries.sysdate)  >= "' . $start_date . '"';
  }
  if ($end_date != '') {
    $searchQuery .= 'and DATE(beneficiaries.sysdate) <= "' . $end_date . '" ';
  }

  if ($_SESSION['charityids'] && !$data1 && !$data2 && !$data3 && !$chID)
    $searchQuery .= ' and beneficiaries.charity_id in(' . $_SESSION['charityids'] . ')';

  if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
    $searchQuery .= 'and ( beneficiaries.id LIKE "%' . $_POST["search"]["value"] . '%" 
                    OR beneficiaries.phone_no LIKE "%' . $_POST["search"]["value"] . '%"
                    OR beneficiaries.idnumber LIKE "%' . $_POST["search"]["value"] . '%"
                    OR beneficiaries.sysdate LIKE "%' . $_POST["search"]["value"] . '%"
                    OR beneficiaries.name LIKE "%' . $_POST["search"]["value"] . '%"
                    )';
  }

//   $searchQuery .= ' GROUP BY beneficiaries.id ';

  if (isset($_POST["order"])) {
    $searchQuery .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . '  ';
  } else {
    $searchQuery .= "ORDER BY  beneficiaries.id DESC ";
  }

  $totals = R::count('beneficiaries', 'LEFT JOIN user
         ON beneficiaries.user_id = user.userid WHERE 1 ' . $searchQuery . ' ');

  if (isset($_POST['start']) && $_POST['length'] != '-1') {
    $searchQuery .= "LIMIT " . intval($_POST['start']) . ", " .
      intval($_POST['length']);
  }
// , MAX(financial_aid) AS lastFin, COUNT(DISTINCT subventionpay.charity_id) AS chCount
  $rResult = R::getAll('SELECT beneficiaries.*, charities.charityname 
                             FROM `beneficiaries` 
                             LEFT JOIN user ON beneficiaries.user_id = user.userid 
                             LEFT JOIN charities ON beneficiaries.charity_id = charities.id  
                             WHERE 1 ' . $searchQuery . ' ');
                            //  LEFT JOIN subventionpay ON beneficiaries.id = subventionpay.benefeciary_id

  $apps = count($rResult);

  $output = array(
    "draw" => intval($_POST["draw"]),
    "recordsTotal" => intval($apps),
    "recordsFiltered" => $totals,
    "data" => array()
  );

  foreach ($rResult as $row) {
    $date = date('Y-m-d');
    $sub_array = array();
    $sub_array[] = $row["id"];
    $sub_array[] = $row["name"];
    $sub_array[] = $row["phone_no"];
    $sub_array[] = $row["idnumber"];
    $sub_array[] = $row["charityname"];
    // $sub_array[] = $row["chCount"];
    // $sub_array[] = $row["lastFin"];
    $sub_array[] = $row["sysdate"];

    if ($row["del"] < 2) {
      $sub_array[] = '<a href="beneficiariesController.php?do=edit&id=' . $row["id"] . '" type="button" class="btn btn-default btn-lg editicon"></a>';
      $sub_array[] = '<a href="javascript:;" data-id="' . $row["id"] . '" data-controll="beneficiariesController" type="button" class="btn btn-default btn-lg deleteicon removecontroller"  ></a>';
    } else  if ($row["del"] == 5) {
      $sub_array[] = '<a href="beneficiariesController.php?do=edit&id=' . $row["id"] . '" type="button" class="btn btn-default btn-lg editicon"></a>';
    } else {
      $sub_array[] = '<a href="beneficiariesController.php?do=edit&id=' . $row["id"] . '" type="button" class="btn btn-default btn-lm ">تفاصيل</a>';
      $sub_array[] = 'محذوف ';
    }

    $output['data'][] = $sub_array;
  }

  echo json_encode($output, JSON_UNESCAPED_SLASHES);
}


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('beneficiaries', $id);
  $tables->del = 2;
  $tables->del_date = $today;
  $tables->deluserid = $userid;
  try {
    R::store($tables);
    echo 1;
  } catch (Exception $e) {
    echo 0;
  }
}


function saveFamilyData($beneficiariesid, $edit)
{
  $familyItr = filter_input(INPUT_POST, 'familyItr');
  $familyIds = '';
  for ($i = 1; $i <= $familyItr; $i++) {
    $f_name = filter_input(INPUT_POST, 'name' . $i);
    if ($f_name) {
      $f_relation = filter_input(INPUT_POST, 'relation' . $i);
      $f_age = filter_input(INPUT_POST, 'age' . $i);
      $f_id_number = filter_input(INPUT_POST, 'id_number' . $i);
      $f_status = filter_input(INPUT_POST, 'status' . $i);
      $f_job = filter_input(INPUT_POST, 'job' . $i);
      $f_salary = filter_input(INPUT_POST, 'salary' . $i);
      $f_health_status = filter_input(INPUT_POST, 'health_status' . $i);
      $f_education_status = filter_input(INPUT_POST, 'education_status' . $i);

      if ($edit = 1) {
        $family_id = filter_input(INPUT_POST, 'family_id' . $i);
        $family = R::load('beneficiaries_family', $family_id);
      } else {
        $family = R::dispense('beneficiaries_family');
      }
      $family->beneficiaries_id = $beneficiariesid;
      $family->name = $f_name;
      $family->relation = $f_relation;
      $family->age = $f_age;
      $family->id_number = $f_id_number;
      $family->status = $f_status;
      $family->job = $f_job;
      $family->salary = $f_salary;
      $family->health_status = $f_health_status;
      $family->education_status = $f_education_status;

      $id = R::store($family);
      $familyIds .= $id . ',';
    }
  }
  $familyIds = rtrim($familyIds, ',');
  if ($familyIds)
    R::exec('DELETE from beneficiaries_family where id not in(' . $familyIds . ') and beneficiaries_id = ' . $beneficiariesid);
}


function saveEcoData($beneficiariesid, $edit)
{
  $ecoItr = filter_input(INPUT_POST, 'ecoItr');
  $ecoIds = '';
  for ($i = 1; $i <= $ecoItr; $i++) {
    $income = filter_input(INPUT_POST, 'income' . $i);
    $paying = filter_input(INPUT_POST, 'paying' . $i);
    if ($income || $paying) {
      if ($edit = 1) {
        $eco_id = filter_input(INPUT_POST, 'eco_id' . $i);
        $eco = R::load('beneficiaries_eco', $eco_id);
      } else {
        $eco = R::dispense('beneficiaries_eco');
      }
      $eco->beneficiaries_id = $beneficiariesid;
      $eco->income = $income;
      $eco->paying = $paying;

      $id = R::store($eco);
      $ecoIds .= $id . ',';
    }
  }

  $ecoIds = rtrim($ecoIds, ',');
  if ($ecoIds)
    R::exec('DELETE from beneficiaries_eco where id not in(' . $ecoIds . ') and beneficiaries_id = ' . $beneficiariesid);
}

function savearea()
{
  $today = date("Y-m-d H:i:s");
  $date = date('Y-m-d');
  $userid = $_SESSION['userid'];
  $name = filter_input(INPUT_POST, 'name');
  $areaId = filter_input(INPUT_POST, 'areaId');
  $selectitr  = filter_input(INPUT_POST, 'selectitr');
  try {
    if (!$areaId) {
      $area = R::dispense('area');
      $area->user_id = $userid;
      $area->sysdate = $date;
    } else {
      $area = R::load('area', $areaId);
      $text = $name;
    }
    $area->name = $name;
    $areaId = R::store($area);

    $last = R::getROW("SELECT id, name FROM `area` order by id desc");
    $areaId = $areaId;
    $text = $name;
    echo json_encode(array('id' => $areaId, 'selectname' => 'areaId', 'selectid' => 'areaId', 'text' => $text, 'selectid' => 'areaId', 'selectitr' => 'areaId'));
  } catch (Exception $e) {
    echo 0;
  }
}

function savedisease()
{
  $today = date("Y-m-d H:i:s");
  $date = date('Y-m-d');
  $userid = $_SESSION['userid'];
  $name = filter_input(INPUT_POST, 'name');
  $diseaseId = filter_input(INPUT_POST, 'diseaseId');
  $selectitr  = filter_input(INPUT_POST, 'selectitr');
  try {
    if (!$diseaseId) {
      $disease = R::dispense('disease');
      $disease->user_id = $userid;
      $disease->sysdate = $date;
    } else {
      $disease = R::load('disease', $diseaseId);
      $text = $name;
    }
    $disease->name = $name;
    $diseaseId = R::store($disease);

    $last = R::getROW("SELECT id, name FROM `disease` order by id desc");
    $diseaseId = $diseaseId;
    $text = $name;
    echo json_encode(array('id' => $diseaseId, 'selectname' => 'diseaseId', 'selectid' => 'diseaseId', 'text' => $text, 'selectid' => 'diseaseId', 'selectitr' => 'diseaseId'));
  } 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 getMultidata()
{
  global $smarty;

  $tableid = $_POST['tableid'];
  $table = $_POST['table'];
  $tableval = $_POST['tableval'];
  $allData = R::getAll("SELECT * FROM $table WHERE $tableid in(" . $tableval . ")");
  $smarty->assign('allData', $allData);
  $smarty->display("beneficiariesview/modalMult.html");
}

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

function getselectmultiple()
{
  $searchterm = $_POST['searchTerms'];
  $tablesearch = $_POST['tablesearch'];
  $table = $_POST['table'];
  $del = $_POST['del'];
  $charitysubjectid = $_POST['charitysubjectid'];

  $row_array = array();
  $return_arr = array();
  $search = '';
  if ($del) {
    $search .= " and $table.del < 2";
  }
  $charityid = '0';
  $charitystosubjects = R::findAll('charityaddsubjectdetails', 'charitysubjectid = ? and del < 2', [$charitysubjectid]);
  foreach ($charitystosubjects as $data) {
    $charityid .= ',' . $data->charityid;
  }
  $search .= " and $table.id not in ($charityid) ";
  $allData = R::getAll("SELECT $table.id,CONCAT($tablesearch) as name
        FROM $table 
        WHERE  CONCAT($tablesearch) LIKE '%" . $searchterm . "%' $search limit 50");
  foreach ($allData as $data) {
    $row_array['id'] = $data['id'];
    $row_array['text'] = $data['name'];
    array_push($return_arr, $row_array);
  }

  echo json_encode($return_arr);
}

function getMultipledit()
{
  $ids = $_POST['ids'];
  $benf = R::findOne('beneficiaries', 'id = ?', [$ids]);
  $dis = R::getAll('SELECT id, name as text FROM disease WHERE id in (0,' . $benf->diseas . ') ');
  echo json_encode($dis);
}


function addFromExcel()
{
  ## upload file temporarely
  $handle = new upload($_FILES['benefsheet']);
  $excelfileName = uploadfile($handle, "../upload/benefeciaries");
  $inputFileName = "../upload/benefeciaries/" . $excelfileName;

  $charity_id = filter_input(INPUT_POST, 'charity_id');
  //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;
      $name = $rowData[$col];
      $col++;
      $address = $rowData[$col];
      $col++;
      $phone = $rowData[$col];
      $col++;
      $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) {
        if ($ben->charity_id == $charity_id)
          continue;
        else {
          $benId = $ben->id;
          if ($benId && $charity_id) {
            $month = date("Y-m");
            $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->notes = $comment;
              R::store($subventionPay);
            }
          }
        }
      } else {
        if ($name && $idNo) {
          $millenium = substr($idNo, 0, 1);
          $birthyear = substr($idNo, 1, 2);
          if ($millenium == 2)
            $year = $birthyear + 1900;
          if ($millenium == 3)
            $year = $birthyear + 2000;

          $thisYear = date('Y');
          $age = $thisYear - $year;


          $ben = R::dispense('beneficiaries');
          $ben->del = 0;
          $ben->sysdate = date("Y-m-d H:i:s");
          $ben->user_id = $_SESSION['userid'];
          $ben->name = $name;
          $ben->phone_no = $phone;
          $ben->age = $age;
          $ben->idnumber = $idNo;
          $ben->address = $address;
          $ben->charity_id = $charity_id;
          $benId = R::store($ben);

          $subvention = R::dispense('subvention');
          $subvention->del = 0;
          $subvention->sysdate = date("Y-m-d H:i:s");
          $subvention->user_id = $_SESSION['userid'];
          $subvention->beneficier_id = $benId;
          $subvention->charity_id = $charity_id;
          $subvention->financial_aid   = $money;
          $subvention->inkind_aid = $value;
          $subvention->guarantee_type_id = $guarantee_type_id;
          $subvention->notes = $comment;

          R::store($subvention);

          if ($benId && $charity_id) {
            $month = date("Y-m");
            $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->notes = $comment;
              R::store($subventionPay);
            }
          }
        }
      }
    }
    unlink($inputFileName);
    $mytransactions->commit();
  } catch (Exception $e) {
    echo $e;
    $mytransactions->rollback();
  }
}

function addFromExcelTkafol()
{
  ## upload file temporarely
  $handle = new upload($_FILES['benefsheet']);
  $excelfileName = uploadfile($handle, "../upload/benefeciaries");
  $inputFileName = "../upload/benefeciaries/" . $excelfileName;

  $charity_id = filter_input(INPUT_POST, 'charity_id');
  //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;
      $name = $rowData[$col];
      $col++;
      $address = $rowData[$col];
      $col++;
      $phone = $rowData[$col];
      $col++;
      $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 . '" and charity_id = '.$charity_id);
        if ($ben) {
          continue;
        }else
        {
            if ($name && $idNo) {
          $millenium = substr($idNo, 0, 1);
          $birthyear = substr($idNo, 1, 2);
          if ($millenium == 2)
            $year = $birthyear + 1900;
          if ($millenium == 3)
            $year = $birthyear + 2000;

          $thisYear = date('Y');
          $age = $thisYear - $year;


          $ben = R::dispense('beneficiaries');
          $ben->del = 0;
          $ben->sysdate = date("Y-m-d H:i:s");
          $ben->user_id = $_SESSION['userid'];
          $ben->name = $name;
          $ben->phone_no = $phone;
          $ben->age = $age;
          $ben->idnumber = $idNo;
          $ben->address = $address;
          $ben->charity_id = 0;
          $benId = R::store($ben);

          $subvention = R::dispense('subvention');
          $subvention->del = 0;
          $subvention->sysdate = date("Y-m-d H:i:s");
          $subvention->user_id = $_SESSION['userid'];
          $subvention->beneficier_id = $benId;
          $subvention->charity_id = $charity_id;
          $subvention->financial_aid   = $money;
          $subvention->inkind_aid = $value;
          $subvention->guarantee_type_id = $guarantee_type_id;
          $subvention->notes = $comment;

          R::store($subvention);

          if ($benId && $charity_id) {
            $month = date("Y-m");
            $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->notes = $comment;
              R::store($subventionPay);
            }
          }
        }
        }
    }
    unlink($inputFileName);
    $mytransactions->commit();
  } catch (Exception $e) {
    echo $e;
    $mytransactions->rollback();
  }
}
