<?php
if (isset($_POST['curlpost']) && $_POST['curlpost'] == 1) {
    //using ob_clean in first line @cpanel   makes The output appears to be a binary or garbled text that cannot be interpreted as valid output, so use it here more proper
    ob_clean(); // to prevent red dot coming with jsonresponse at royal that return in do=getParmacyDataRoyal
} else {
    //the global file operation
    include("../public/impOpreation.php");
}


require_once('../models/dao/YoutubeLinkDAO.class.php');
require_once('../models/dto/YoutubeLink.class.php');
require_once('../models/mysql/YoutubeLinkMySqlDAO.class.php');
require_once('../models/mysql/ext/YoutubeLinkMySqlExtDAO.class.php');
//global varable
global $showoutside;

//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");

$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();

//here the db files that include in the file
// GOES HERE ....................
include("../public/include_dao.php");

//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
//Clientdebtchange
require_once('../models/dao/ClientdebtchangeDAO.class.php');
require_once('../models/dto/Clientdebtchange.class.php');
require_once('../models/mysql/ClientdebtchangeMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientdebtchangeMySqlExtDAO.class.php');


$do = $_GET['do'];

$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
//Clientdebtchange
$clientDeptChange = new Clientdebtchange();
$clientDeptChangeDAO = new ClientdebtchangeMySqlDAO();
$clientDeptChangeExt = new ClientdebtchangeMySqlExtDAO();

if (empty($do)) {

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);

    if($firstGov)
    {
        $firstGovArea = R::getRow('select * from goverarea where governmentid = ' . $firstGov['governmetid'] . ' limit 1');
        $firstGovAreaData = R::getRow('select * from clientarea where id = ' . $firstGovArea['clientareaid']);
        $smarty->assign('firstGov', $firstGov);
        $smarty->assign('firstGovArea', $firstGovAreaData);
    }
    
    $smarty->assign('firstGov', $firstGov);
    $smarty->assign('firstGovArea', $firstGovAreaData);
    $smarty->display("header.html");
    $smarty->display("reportprovinceview/index.html");
    $smarty->display("footer.html");
} else if ($do == 'getdata') {
// $object = json_decode(json_encode($array), FALSE);
    $date = date("Y-m-d");
    $fromdate = $_POST['fromdate'];
    $todate = $_POST['todate'];
    $clientid = $_POST['clientid'];
    $governmentid = $_POST['governmentid'];
    $clientareaid = $_POST['clientareaid'];

    if ($governmentid == '' && $clientareaid == '' && $clientid != '') {
        $clients = R::getRow("SELECT *  FROM client
            LEFT JOIN clientarea on client.clientareaid = clientarea.id
            LEFT JOIN goverarea on client.clientareaid = goverarea.clientareaid
            LEFT JOIN government on goverarea.governmentid = government.governmetid
           where clientid = ? ", [$clientid]);
        $clientdebtchanget = R::getRow("SELECT *  FROM clientdebtchange where clientid = ? order by clientdebtchangeid desc ", [$clientid]);
        $sellbills = R::getAll("SELECT sellbillid, sellbilldate ,sellbillaftertotalbill  FROM sellbill where sellbillclientid = ? and conditions = 0 ", [$clientid]);

        $sellbillsn = array();
        $clientdebt = $clients['clientdebt'];
        $sellbills = json_decode(json_encode($sellbills), FALSE);
        foreach ($sellbills as $sellbill) {
            $todays = date("Y-m-d H:i:s");
            $diff = (strtotime($todays) - strtotime($sellbill->sellbilldate)) / (60 * 60 * 24);
            if ($clientdebt > $sellbill->sellbillaftertotalbill) {
                $sellbill->sellbillaftertotalbill2 = $sellbill->sellbillaftertotalbill;
            } else {
                $sellbill->sellbillaftertotalbill2 = $clientdebt;
            }
            $sellbill->days = round($diff);
            if ($clientdebt > 0) {
                array_push($sellbillsn, $sellbill);
            }
            $clientdebt -= $sellbill->sellbillaftertotalbill;
        }
        $smarty->assign('types', 0);
        $smarty->assign('style', 3);
        $smarty->assign('clients', $clients);
        $smarty->assign('clientdebtchanget', $clientdebtchanget);
        $smarty->assign('allsellbills', $sellbillsn);

        $smarty->assign('types', 2);
    } else {
        $government = R::getRow("SELECT *  FROM government where governmetid = ? ", [$governmentid]);
        $smarty->assign('government', $government);
        if ($clientareaid == '') {
            $goverareas = R::getAll("SELECT goverarea .*  FROM goverarea where governmentid = ? ", [$governmentid]);
        } else {
            $goverareas = R::getAll("SELECT goverarea.*  FROM goverarea where governmentid = ? and clientareaid = ?", [$governmentid, $clientareaid]);
        }
        $goverareas = json_decode(json_encode($goverareas), FALSE);
        $allsumclientdebt = 0;
        foreach ($goverareas as $goverarea) {
            $clientareat = R::getRow("SELECT *  FROM clientarea where id = ?", [$goverarea->clientareaid]);
            $goverarea->clientareaname = $clientareat['name'];
            if (!$clientid) {
                $clients = R::getAll("SELECT *  FROM client where clientareaid = ? and clientdebt > 0 ", [$goverarea->clientareaid]);
            } else {
                $clients = R::getAll("SELECT *  FROM client where clientareaid = ? and clientid = ? and clientdebt > 0 ", [$goverarea->clientareaid, $clientid]);
            }

            # transform  $clients   to array of objects
            //$clients = json_decode(json_encode($clients), FALSE);
            //from key value pair to std class instead of json_encode , json_decode it has some problems for some charecters
            $TempArr = array();
            foreach ($clients as $cl) {
                $obj = new stdClass();
                foreach ($cl as $key1 => $value1) {
                    $obj->$key1 = $value1;
                }
                $TempArr[] = $obj;
            }
            $clients = $TempArr;



            $sumclientdebt = 0;
            foreach ($clients as $client) {
                $clientdebtchanget = R::getRow("SELECT *  FROM clientdebtchange where clientid = ? order by clientdebtchangeid desc ", [$client->clientid]);
                $client->clientdebtchangeamount = $clientdebtchanget['clientdebtchangeamount'];
                $client->clientdebtchangedate = $clientdebtchanget['clientdebtchangedate'];
                if ($fromdate == '' && $todate == '') {
                    $sellbills = R::getAll("SELECT sellbillid, sellbilldate ,sellbillaftertotalbill  FROM sellbill where sellbillclientid = ? and conditions = 0 ", [$client->clientid]);
                } else {
                    $sellbills = R::getAll("SELECT sellbillid, sellbilldate ,sellbillaftertotalbill  FROM sellbill where sellbillclientid = ? and  sellbilldate >= ? and sellbilldate <= ? and conditions = 0 ", [$client->clientid, $fromdate . ' 00-00-00', $todate . ' 23-59-55']);
                }

                $sellbillsn = array();
                $clientdebt = $client->clientdebt;
                $sumclientdebt += $client->clientdebt;
                $sellbills = json_decode(json_encode($sellbills), FALSE);
                foreach ($sellbills as $sellbill) {
                    $todays = date("Y-m-d H:i:s");
                    $diff = (strtotime($todays) - strtotime($sellbill->sellbilldate)) / (60 * 60 * 24);

                    if ($clientdebt > $sellbill->sellbillaftertotalbill) {
                        $sellbill->sellbillaftertotalbill2 = $sellbill->sellbillaftertotalbill;
                    } else {
                        $sellbill->sellbillaftertotalbill2 = $clientdebt;
                    }
                    $sellbill->days = round($diff);

                    if ($clientdebt > 0) {

                        array_push($sellbillsn, $sellbill);
                    }
                    $clientdebt -= $sellbill->sellbillaftertotalbill;
                }
                $client->allsellbills = $sellbillsn;
            }
            $goverarea->sumclientdebt = $sumclientdebt;
            $allsumclientdebt += $sumclientdebt;
            $goverarea->allclients = $clients;
            $goverarea->clientdebt = $clientdebt;
        }
        $smarty->assign('types', 1);
        $smarty->assign('style', 3);
        $smarty->assign('goverareas', $goverareas);
        $smarty->assign('allsumclientdebt', $allsumclientdebt);
    }


    $smarty->display("reportprovinceview/getdata.html");
} else if ($do == 'getParmacyDataRoyal') {
    $date = date("Y-m-d");
    //$.post('sellbillAjaxController.php?do=makeSellBillPayment', {id_pay: id_pay, client_id: client_id}, function (data) {
    $clientid = (int) $_POST['clientid'];
    $erpDB = $_POST["erpDB"];
    //choose erpDB user has choosen in obygy_app
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $erpDB;
        /*         * *** just used rd beans depend on connection ** */
        $dbhostrb = ConnectionProperty::getHost();
        $dbuserrb = ConnectionProperty::getUser();
        $dbpasswordrb = ConnectionProperty::getPassword();
        $dbnamerb = ConnectionProperty::getDatabase();

        R::addDatabase('erpDBOfPOST', 'mysql:host=' . $dbhostrb . ';dbname=' . $dbnamerb . '', $dbuserrb, $dbpasswordrb);
        R::selectDatabase('erpDBOfPOST');
//        R::exec('SET NAMES latin1');
        /*         * ***** end of red beans *** */
    }
    if ($clientid > 0) {
        $programsetting = $ProgramsettingDAO->load(1);
        $clients = R::getRow("SELECT *  FROM client where clientid = ? ", [$clientid]);
        //$clientdebtchanget = R::getRow("SELECT *  FROM clientdebtchange where clientid = ? order by clientdebtchangeid desc ", [$clientid]);
        $sellbills = R::getAll("SELECT sellbillid, sellbilldate ,sellbillaftertotalbill,sellbillfinalbill
                                FROM sellbill
                                where sellbillclientid = ? and sellbill.conditions = 0
                                and obgyBillType = 1 and sellbillfinalbill > 0
                                ORDER BY  `sellbilldate`  asc ,sellbillid asc  ", [$clientid]); //join user on user.userid = sellbill.userid //and user.usergroupid in ($programsetting->obgyPharmacyGroups)

        $sellbillsn = array();
        //$clientdebt = $clients['clientdebt'];
        $sellbills = json_decode(json_encode($sellbills), FALSE);
        foreach ($sellbills as $sellbill) {
            $todays = date("Y-m-d H:i:s");
            $diff = (strtotime($todays) - strtotime($sellbill->sellbilldate)) / (60 * 60 * 24);
            //get payments on bill @ clientPayedDeptSellBillsController.php
            $client_dept_change = $clientDeptChangeExt->sumByBillIdAndControl($sellbill->sellbillid, 'clientPayedDeptSellBillsController.php');
            //affect sellbillfinalbill "which is client debt because of that bill"
            $sellbill->sellbillfinalbill = $sellbill->sellbillfinalbill - ((float) $client_dept_change->clientdebtchangeamount);
//            if ($clientdebt > $sellbill->sellbillfinalbill) {
//                $sellbill->sellbillfinalbill2 = $sellbill->sellbillfinalbill;
//            } else {
//                $sellbill->sellbillfinalbill2 = $clientdebt;
//            }
            $sellbill->days = round($diff);
//            if ($clientdebt > 0) {
            if ($sellbill->sellbillfinalbill > 0) {
                $simpleDataOfBill = new stdClass();
                $simpleDataOfBill->id = $sellbill->sellbillid;
                $simpleDataOfBill->billtotal = $sellbill->sellbillaftertotalbill;
                $simpleDataOfBill->remain = $sellbill->sellbillfinalbill;
                $simpleDataOfBill->date = $sellbill->sellbilldate;
                $simpleDataOfBill->days = $sellbill->days;
                array_push($sellbillsn, $simpleDataOfBill);
            }
//            } else {
//                break;
//            }
//            $clientdebt -= $sellbill->sellbillfinalbill;
        }

        ########################
        $sellbillsRet = R::getAll("SELECT returnsellbillid, returnsellbilldate ,returnsellbillaftertotalbill,returnsellbillfinalbill,returnsellbilltotalpayed,visaPayed
                                FROM returnsellbill
                                where returnsellbillclientid = ? and returnsellbill.conditions = 0
                                and obgyBillType = 1 and returnsellbillfinalbill < 0
                                ORDER BY  `returnsellbilldate`  asc ,returnsellbillid asc  ", [$clientid]); //join user on user.userid = returnsellbill.userid//and user.usergroupid in ($programsetting->obgyPharmacyGroups)
        $sellbillsnRet = array();
        $sellbillsRet = json_decode(json_encode($sellbillsRet), FALSE);
        foreach ($sellbillsRet as $sellbill) {
            $todays = date("Y-m-d H:i:s");
            $diff = (strtotime($todays) - strtotime($sellbill->returnsellbilldate)) / (60 * 60 * 24);
            //get payments on bill @ clientPayedDeptSellBillsController.php
            $client_dept_change = $clientDeptChangeExt->sumByBillIdAndControl($sellbill->returnsellbillid, 'clientPayedDeptReturnSellBillsController.php');
            //affect sellbillfinalbill "which is client debt because of that bill"
            $sellbill->returnsellbillfinalbill = $sellbill->returnsellbillfinalbill + abs((float) $client_dept_change->clientdebtchangeamount);
            $payed = $sellbill->returnsellbilltotalpayed + $sellbill->visaPayed - abs((float) $client_dept_change->clientdebtchangeamount);
            $sellbill->days = round($diff);
            if ($sellbill->returnsellbillfinalbill < 0) {
                $simpleDataOfBill = new stdClass();
                $simpleDataOfBill->id = $sellbill->returnsellbillid;
                $simpleDataOfBill->billtotal = $sellbill->returnsellbillaftertotalbill;
                $simpleDataOfBill->remain = $sellbill->returnsellbillfinalbill;
                $simpleDataOfBill->date = $sellbill->returnsellbilldate;
                $simpleDataOfBill->days = $sellbill->days;
                $simpleDataOfBill->payed = $payed;
                array_push($sellbillsnRet, $simpleDataOfBill);
            }
        }
        $data = array();
        $data['sell'] = $sellbillsn;
        $data['ret'] = $sellbillsnRet;
        echo json_encode($data);
    }
} else if ($do == 'clientBills') {
    $date = date("Y-m-d");
    //$.post('sellbillAjaxController.php?do=makeSellBillPayment', {id_pay: id_pay, client_id: client_id}, function (data) {
    $clientid = (int) $_POST['clientid'];
    $erpDB = $_POST["erpDB"];
    //choose erpDB user has choosen in obygy_app
    if (isset($erpDB) && !empty($erpDB)) {
        $_SESSION['dbname'] = $erpDB;
        /*         * *** just used rd beans depend on connection ** */
        $dbhostrb = ConnectionProperty::getHost();
        $dbuserrb = ConnectionProperty::getUser();
        $dbpasswordrb = ConnectionProperty::getPassword();
        $dbnamerb = ConnectionProperty::getDatabase();

        //$_SESSION['dbname'] = $dbnamerb = 'royalerp';
        R::addDatabase('erpDBOfPOST', 'mysql:host=' . $dbhostrb . ';dbname=' . $dbnamerb . '', $dbuserrb, $dbpasswordrb);
        R::selectDatabase('erpDBOfPOST');
//        R::exec('SET NAMES latin1');
        /*         * ***** end of red beans *** */
    }
    if ($clientid > 0) {
        //$programsetting = $ProgramsettingDAO->load(1);
        $clients = R::getRow("SELECT *  FROM client where clientid = ? ", [$clientid]);
        ####### كل فواتير العميل
        $sellbills = R::getAll("SELECT sellbillid, sellbilldate ,sellbillaftertotalbill,sellbillfinalbill,sellbilltotalpayed
                                FROM sellbill
                                where sellbillclientid = ? and sellbill.conditions = 0
                                ORDER BY  `sellbilldate`  asc ,sellbillid asc  ", [$clientid]); //and sellbillfinalbill > 0 //and obgyBillType = 1 //join user on user.userid = sellbill.userid //and user.usergroupid in ($programsetting->obgyPharmacyGroups)
        $sellbillWithRemain = array();
        $sellbillWithoutRemain = array();
        $clientdebt = $clients['clientdebt'];
        $sellbills = json_decode(json_encode($sellbills), FALSE);
        foreach ($sellbills as $sellbill) {
            $todays = date("Y-m-d H:i:s");
            $diff = (strtotime($todays) - strtotime($sellbill->sellbilldate)) / (60 * 60 * 24);
            //get payments on bill @ clientPayedDeptSellBillsController.php
            $client_dept_change = $clientDeptChangeExt->sumByBillIdAndControl($sellbill->sellbillid, 'clientPayedDeptSellBillsController.php');
            $sellbill->payments = R::getAll('SELECT clientdebtchangeid, paytype, billid , clientdebtchangeamount FROM clientdebtchange WHERE `billid` = ' . $sellbill->sellbillid . ' and tablename = "clientPayedDeptSellBillsController.php" ');
            //affect sellbillfinalbill "which is client debt because of that bill"
            $sellbill->sellbillfinalbill = $sellbill->sellbillfinalbill - ((float) $client_dept_change->clientdebtchangeamount);
            $sellbill->days = round($diff);

            ##push obj in proper obj
            $simpleDataOfBill = new stdClass();
            $simpleDataOfBill->id = $sellbill->sellbillid;
            $simpleDataOfBill->billtotal = $sellbill->sellbillaftertotalbill;
            $simpleDataOfBill->remain = $sellbill->sellbillfinalbill;
            $simpleDataOfBill->date = $sellbill->sellbilldate;
            $simpleDataOfBill->days = $sellbill->days;
            $simpleDataOfBill->payments = $sellbill->payments;


            if ($simpleDataOfBill->remain == 0) {//no rmain
                array_push($sellbillWithoutRemain, $simpleDataOfBill);
            } elseif ($simpleDataOfBill->remain > 0) {//with remain "debt on client from sellbill"
                //$clientdebt < 0 means i owe him money
                //his debt cover this bill
                if ($clientdebt < 0 && abs($clientdebt) >= $simpleDataOfBill->remain) {
                    array_push($sellbillWithoutRemain, $simpleDataOfBill);
                    $clientdebt += $simpleDataOfBill->remain;
                }
                //his debt dont cover all this bill, we use what he has from his debt , then show the remain as debt
                else if ($clientdebt < 0 && abs($clientdebt) < $simpleDataOfBill->remain) {
                    $simpleDataOfBill->remain = abs($clientdebt);
                    array_push($sellbillWithoutRemain, $simpleDataOfBill);
                    //
                    $remainMpneyFromThisBill = $simpleDataOfBill->remain - abs($clientdebt);
                    $simpleDataOfBill->remain += $remainMpneyFromThisBill;
                    array_push($sellbillWithRemain, $simpleDataOfBill);
                    $clientdebt = $remainMpneyFromThisBill; //now it is  >  0
                } else {//his debt doesnot cover this bill
                    array_push($sellbillWithRemain, $simpleDataOfBill);
                    $clientdebt += $simpleDataOfBill->remain;
                }
            }
        }

        ########################
        $sellbillsRet = R::getAll("SELECT returnsellbillid, returnsellbilldate ,returnsellbillaftertotalbill,returnsellbillfinalbill,returnsellbilltotalpayed,visaPayed
                                FROM returnsellbill
                                where returnsellbillclientid = ? and returnsellbill.conditions = 0
                                ORDER BY  `returnsellbilldate`  asc ,returnsellbillid asc  ", [$clientid]); //and returnsellbillfinalbill < 0//and obgyBillType = 1//join user on user.userid = returnsellbill.userid//and user.usergroupid in ($programsetting->obgyPharmacyGroups)
        $sellbillRetWithRemain = array();
        $sellbillRetWithoutRemain = array();
        $sellbillsRet = json_decode(json_encode($sellbillsRet), FALSE);
        foreach ($sellbillsRet as $sellbill) {
            $todays = date("Y-m-d H:i:s");
            $diff = (strtotime($todays) - strtotime($sellbill->returnsellbilldate)) / (60 * 60 * 24);
            //get payments on bill @ clientPayedDeptSellBillsController.php
            $client_dept_change = $clientDeptChangeExt->sumByBillIdAndControl($sellbill->returnsellbillid, 'clientPayedDeptReturnSellBillsController.php');
            $sellbill->payments = R::getAll('SELECT clientdebtchangeid, paytype, billid , clientdebtchangeamount FROM clientdebtchange WHERE `billid` = ' . $sellbill->returnsellbillid . ' and tablename = "clientPayedDeptReturnSellBillsController.php" ');
            //affect sellbillfinalbill "which is client debt because of that bill"
            $sellbill->returnsellbillfinalbill = $sellbill->returnsellbillfinalbill + abs((float) $client_dept_change->clientdebtchangeamount);
            $payed = $sellbill->returnsellbilltotalpayed + $sellbill->visaPayed - abs((float) $client_dept_change->clientdebtchangeamount);
            $sellbill->days = round($diff);


            ##push obj in proper obj
            //her it is supposed to be استرجاع
            //returnsellbillfinalbill is always  = 0 , no calc needed
            if ($sellbill->returnsellbillfinalbill <= 0) {
                $simpleDataOfBill = new stdClass();
                $simpleDataOfBill->id = $sellbill->returnsellbillid;
                $simpleDataOfBill->billtotal = $sellbill->returnsellbillaftertotalbill;
                $simpleDataOfBill->remain = $sellbill->returnsellbillfinalbill;
                $simpleDataOfBill->date = $sellbill->returnsellbilldate;
                $simpleDataOfBill->days = $sellbill->days;
                $simpleDataOfBill->payed = $payed;
                $simpleDataOfBill->payments = $sellbill->payments;
                array_push($sellbillRetWithoutRemain, $simpleDataOfBill);
            }
        }
        $data = array();
        $data['sellWithRemain'] = $sellbillWithRemain;
        $data['sellWithoutRemain'] = $sellbillWithoutRemain;
        $data['retWithRemain'] = $sellbillRetWithRemain;
        $data['retWithoutRemain'] = $sellbillRetWithoutRemain;
        echo json_encode($data);
    }
} else if ($do == 'select2client') {
    select2client();
} else if ($do == 'select2government') {
    select2government();
} else if ($do == 'select2clientarea') {
    select2clientarea();
}

function select2government() {

    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm'];

    $productsData = R::getAll("SELECT governmetid, governmentname
    FROM  government
    WHERE governmentname LIKE '%" . $name . "%' limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['governmetid'];
        $row_array['text'] = $pro['governmentname'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}

function select2client() {
    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm'];


    $productsData = R::getAll("SELECT clientid, clientname as name
    FROM client
    WHERE clientname LIKE '%" . $name . "%'  limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['clientid'];
        $row_array['text'] = $pro['name'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}

function select2clientarea() {
    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm'];
    $governmentid = $_POST['governmentid'];
    if ($governmentid) {
        $search = " goverarea.governmentid = $governmentid and ";



        $productsData = R::getAll("SELECT clientarea.id, clientarea.name
    FROM clientarea  LEFT JOIN  goverarea on  goverarea.clientareaid = clientarea.id
    WHERE  $search clientarea.name LIKE '%" . $name . "%'  limit 50");
        foreach ($productsData as $pro) {
            $row_array['id'] = $pro['id'];
            $row_array['text'] = $pro['name'];
            array_push($return_arr, $row_array);
        }
    }

    echo json_encode($return_arr);
}
?>