<?php
include("../public/impOpreation.php");
$mySaveEx = new SaveMySqlExtDAO();

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

if (empty($do)) {
    $smarty->display("header.html");
    $smarty->display("branchProfitabilityReportView/show.html");
    $smarty->display("footer.html");
} elseif ($do == "select2branchs") {

    $searchTerm = $_POST['searchTerm'];
    $allData = R::getAll("SELECT branchId as id, branchName as name
    FROM  branch
    WHERE  branchName LIKE '%" . $searchTerm . "%' limit 50");
    $return_arr = array();
    foreach ($allData as $data) {
        $row_array = array();
        $row_array['id'] = $data['id'];
        $row_array['text'] = $data['name'];
        array_push($return_arr, $row_array);
    }
    echo json_encode($return_arr);
} elseif ($do == "select2stores") {
    $searchTerm = $_POST['searchTerm'];
    $allData = R::getAll("SELECT storeId as id, storeName as name
    FROM  store
    WHERE conditions = 0 and  storeName LIKE '%" . $searchTerm . "%' limit 50");
    $return_arr = array();
    foreach ($allData as $data) {
        $row_array = array();
        $row_array['id'] = $data['id'];
        $row_array['text'] = $data['name'];
        array_push($return_arr, $row_array);
    }
    echo json_encode($return_arr);
} elseif ($do == "select2saves") {

    $searchTerm = $_POST['searchTerm'];
    $allData = R::getAll("SELECT saveid as id, savename as name
    FROM  save
    WHERE conditions = 0 and  savename LIKE '%" . $searchTerm . "%' limit 50");
    $return_arr = array();
    foreach ($allData as $data) {
        $row_array = array();
        $row_array['id'] = $data['id'];
        $row_array['text'] = $data['name'];
        array_push($return_arr, $row_array);
    }
    echo json_encode($return_arr);
} elseif ($do == "show") {

    $myEmpEX = new EmployeeMySqlExtDAO();
    $start_date = filter_input(INPUT_POST, 'start_date');
    $end_date = filter_input(INPUT_POST, 'end_date');
    $branch_id = filter_input(INPUT_POST, 'branch_id');
    $store_id = filter_input(INPUT_POST, 'store_id');
    $save_id = filter_input(INPUT_POST, 'save_id');

    $message = '';
    $storedetail = '';
    $sellbillqs = '';
    $retsellbillqs = '';
    $restaurantrawdestruction = '';
    $expenses = '';
    $storereport = '';
    $transfermoneyq = '';
    $transfermoneyfrom = '';
    $transfermoneyto = '';
    $employee = '';
    $salaryreport = '';
    $salaryreportdate = '';

    if ($start_date) {
        $message .= ' من تاريخ  : ' . $start_date;
        // $storedetail .= ' and storedetail.storedetaildate >= "' . $start_date . '"';
        $restaurantrawdestruction .= ' and restaurantrawdestruction.sysdate >= "' . $start_date . '"';
        $expenses .= ' and expenses.expensesdate >= "' . $start_date . '"';
        $storereport .= ' and storereport.storereportdate >=  "' . $start_date . '"';
        $transfermoneyq .= ' and transfermoney.transfermoneydate >=  "' . $start_date . '"';
        $storemovementq .= ' and storemovement.transferproductdate >=  "' . $start_date . '"';
        $start_date = $start_date . " 00:00:00";
        $salaryreport .= ' and salaryreport.salaryreportdate >=  "' . $start_date . '"';
        $sellbillqs .= ' and sellbill.sellbilldate >=  "' . $start_date . '"';
        $retsellbillqs .= ' and returnsellbill.returnsellbilldate >=  "' . $start_date . '"';
        $salaryreportdate .= ' and salaryreport.salaryreportdate >=  "' . $start_date . '"';
        // $storereport .= ' and sellbillandrutern.sellbilldate >=  "' . $start_date . '"';
    }
    if ($end_date) {
        $message .= ' /  الى تاريخ : ' . $end_date;
        // $storedetail .= ' and storedetail.storedetaildate <= "' . $end_date . '"';
        $restaurantrawdestruction .= ' and restaurantrawdestruction.sysdate <= "' . $end_date . '"';
        $expenses .= ' and expenses.expensesdate <= "' . $end_date . '"';
        $storereport .= ' and storereport.storereportdate <=    "' . $end_date . '"';
        $transfermoneyq .= ' and transfermoney.transfermoneydate <=    "' . $end_date . '"';
        $storemovementq .= ' and storemovement.transferproductdate <=    "' . $end_date . '"';
        $end_date = $end_date . ' 23:59:59';
        $salaryreport .= ' and salaryreport.salaryreportdate <=  "' . $end_date . '"';
        $sellbillqs .= ' and sellbill.sellbilldate <=  "' . $end_date . '"';
        $retsellbillqs .= ' and returnsellbill.returnsellbilldate <=  "' . $end_date . '"';
        $salaryreportdate .= ' and salaryreport.salaryreportdate <=  "' . $end_date . '"';
        // $sellbillretqs .= ' and sellbillandrutern.sellbilldate <=  "' . $end_date . '"';
    }

    if ($branch_id > 0) {
        $branchName = R::getCell('select branchName from branch where branchId = ?', [$branch_id]);
        $message .= ' / الفرع : ' . $branchName;
        $employee .= ' and employee.branchid =  "' . $branch_id . '"';
    }

    $storemovementfrom = 0;
    $storemovementto = 0;
    if ($store_id) {
        $storeName = R::getCell('select storeName from store where storeId = ' . $store_id);
        $message .= '  / المخزن : ' . $storeName;
        $storedetail .= ' and storedetail.storeid =  ' . $store_id;
        $restaurantrawdestruction .= ' and restaurantrawdestruction.storeid = "' . $store_id . '"';
        $sellbillqs .= ' and sellbilldetail.storeid =  "' . $store_id . '"';
        $retsellbillqs .= ' and returnsellbilldetail.storeid = ' . $store_id;
        $storereport .= ' and storereport.storeid = ' . $store_id;


        $storemovementfromBuyPrice = R::getCell('select SUM(storemovement.transferproductamount *  product.productBuyPrice) FROM storemovement
            join product on product.productId =  storemovement.productid  where storemovement.conditions = 0 and storemovement.storeidfrom =  "' . $store_id . '"' . $storemovementq);
        $storemovementfromSellPrice = R::getCell('select SUM(storemovement.transferproductamount *  product.productSellAllPrice) FROM storemovement
            join product on product.productId =  storemovement.productid  where storemovement.conditions = 0 and storemovement.storeidfrom =  "' . $store_id . '"' . $storemovementq);

        $storemovementtoBuyPrice = R::getCell('select SUM(storemovement.transferproductamount *  product.productBuyPrice) FROM storemovement
            join product on product.productId =  storemovement.productid  where storemovement.conditions = 0 and storemovement.storeidto =  "' . $store_id . '"' . $storemovementq);
        $storemovementtoSellPrice = R::getCell('select SUM(storemovement.transferproductamount *  product.productSellAllPrice) FROM storemovement
            join product on product.productId =  storemovement.productid  where storemovement.conditions = 0 and storemovement.storeidto =  "' . $store_id . '"' . $storemovementq);


        $storemovementfrom = $storemovementtoBuyPrice - $storemovementfromBuyPrice;
        $storemovementto = $storemovementtoSellPrice - $storemovementfromSellPrice;
    }


    $transfermoney = 0;
    if ($save_id) {
        $saveName = R::getCell('select savename from save where saveid = ' . $save_id);
        $message .= ' / الخزنة : ' . $saveName;
        $expenses .= ' and expenses.saveid = "' . $save_id . '"';
        $transfermoneyfrom .= ' and transfermoney.saveidfrom = "' . $save_id . '"';
        $transfermoneyto .= ' and transfermoney.saveidto = "' . $save_id . '"';
        $transfermoney =  R::getCell('select SUM(transfermoneyvalue) FROM transfermoney
       where conditions = 0  ' .  $transfermoneyq . ' ' .  $transfermoneyto) - R::getCell('select SUM(transfermoneyvalue) FROM transfermoney
          where conditions = 0 ' . $transfermoneyq . ' ' . $transfermoneyfrom);
    }







    // $sellbillqs = ' and sellbill.sellbillsaveid = ' . $saveid;
    // $retsellbillqs = ' and returnsellbill.returnsellbillsaveid = ' . $saveid;
    // $sellbillretqs = ' and sellbillandrutern.sellbillsaveid = ' . $saveid;
    // $expensesqs = ' and expenses.saveid = ' . $saveid;


    // if ($start_date) {
    //     $message .= ' - التاريخ من: ' . $start_date;
    //     $expensesqs .= ' and expenses.expensesdate >= "' . $start_date . '"';
    //     $from = $from . " 00:00:00";
    //     $sellbillqs .= ' and sellbill.sellbilldate >=  "' . $start_date . '"';
    //     $retsellbillqs .= ' and returnsellbill.returnsellbilldate >=  "' . $start_date . '"';
    //     $sellbillretqs .= ' and sellbillandrutern.sellbilldate >=  "' . $start_date . '"';
    // }
    // if ($end_date) {
    //     $message .= ' - التاريخ إلى: ' . $end_date;
    //     $expensesqs .= ' and expenses.expensesdate <= "' . $end_date . '"';
    //     $to = $to . ' 23:59:59';
    //     $sellbillqs .= ' and sellbill.sellbilldate <=  "' . $end_date . '"';
    //     $retsellbillqs .= ' and returnsellbill.returnsellbilldate <=  "' . $end_date . '"';
    //     $sellbillretqs .= ' and sellbillandrutern.sellbilldate <=  "' . $end_date . '"';
    // }

    $programsettings = R::getRow("select  * FROM programsettings where programsettingsid = 1 ");

    $productBuyPrice = 'productBuyPrice';

    // if ($programsettings['Inventoryevaluation'] == 'first') {
    //     $productBuyPrice = 'productBuyPrice';
    // } else if ($programsettings['Inventoryevaluation'] == 'last') {
    //     $productBuyPrice = 'lastbuyprice';
    // } else if ($programsettings['Inventoryevaluation'] == 'last_discount') {
    //     $productBuyPrice = 'lastbuyprice_withDiscount';
    // } else if ($programsettings['Inventoryevaluation'] == 'mean') {
    //     $productBuyPrice = 'meanbuyprice';
    // } else if ($programsettings['Inventoryevaluation'] == 'mean_discount') {
    //     $productBuyPrice = 'meanbuyprice_withDiscount';
    // } else if ($programsettings['Inventoryevaluation'] == 'tax') {
    //     $productBuyPrice = 'lastbuyprice_withTax';
    // } else if ($programsettings['Inventoryevaluation'] == 'mean_tax') {
    //     $productBuyPrice = 'meanbuyprice_withTax';
    // } else if ($programsettings['Inventoryevaluation'] == 'generalPrice') {
    //     $productBuyPrice = 'buypricereal_precentage';
    // } else if ($programsettings['Inventoryevaluation'] == 'realBuyPrice') {
    //     $productBuyPrice = 'productbuypricereal';
    // }


    $allstoredetails = R::getRow("select  SUM(productquantity * $productBuyPrice) as totalproductBuyPrice, SUM(productquantity * productSellUnitPrice) as totallastbuyprice FROM storedetail 
    join product on product.productId =  storedetail.productid
     where conditions = 0 " . $storedetail);


    // $allSellBills = R::getRow("select  GROUP_CONCAT(DISTINCT `sellbillid` SEPARATOR ',') AS sellbillids , SUM(sellbilltotalbill) as sellbilltotalbill FROM sellbill where conditions = 0 " . $sellbillqs);
    // $sellbillids = $allSellBills['sellbillids'] . '0';


    //  echo 'select SUM(sellbilldetailquantity * meanbuyprice_withDiscount) FROM sellbilldetail where sellbillid in ('.$sellbillids.')';
    //     exit();
    $allSellBilldetails = R::getCell('select SUM(sellbilldetailquantity * sellbilldetailprice) FROM sellbilldetail join sellbill on sellbill.sellbillid = sellbilldetail.sellbillid where conditions = 0 ' . $sellbillqs);
    $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * returnsellbilldetailprice) FROM returnsellbilldetail
    join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);

    $allSellBilldetailsBuyPrice = R::getCell('select SUM(sellbilldetailquantity * product.lastbuyprice) FROM sellbilldetail 
    join sellbill on sellbill.sellbillid = sellbilldetail.sellbillid
    JOIN product ON product.productId = sellbilldetail.sellbilldetailproductid
    where sellbill.conditions = 0 ' . $sellbillqs);

    // $allRetSellBills = R::getRow("select GROUP_CONCAT(DISTINCT `returnsellbillid` SEPARATOR ',') AS returnsellbillids , SUM(returnsellbilltotalbill) as returnsellbilltotalbill FROM returnsellbill where conditions = 0 " . $retsellbillqs);
    // $returnsellbillids = $allRetSellBills['returnsellbillids'] . '0';


    // if ($programsettings['Profitevaluation'] == 'first') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * buyprice) FROM returnsellbilldetail
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'last') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * lastbuyprice) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'last_discount') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * lastbuyprice_withDiscount) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'mean') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * meanbuyprice) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'mean_discount') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * meanbuyprice_withDiscount) FROM returnsellbilldetail
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'tax') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * lastbuyprice_withTax) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'mean_tax') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * meanbuyprice_withTax) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'generalPrice') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * returnsellbilldetailpriceC) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // } else if ($programsettings['Profitevaluation'] == 'realBuyPrice') {
    //     $returnsellbillidetails = R::getCell('select SUM(returnsellbilldetailquantity * returnsellbilldetailtotalpriceC) FROM returnsellbilldetail 
    //                                         join returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid where returnsellbill.conditions = 0 ' . $retsellbillqs);
    // }





    $restaurantrawdestructionValue = R::getCell('select SUM(quantity * lastbuyprice) FROM restaurantrawdestruction where conditions = 0 ' . $restaurantrawdestruction);

    $expensesValue = R::getCell('select SUM(expensesValue) FROM expenses where conditions = 0 ' . $expenses);


    $storereport = R::getCell('select SUM(storereport.productquantity *  product.lastbuyprice) FROM storereport
     join product on product.productId =  storereport.productid  where storereporttype = 0 ' . $storereport) -
        R::getCell('select SUM(storereport.productquantity *  product.lastbuyprice) FROM storereport
     join product on product.productId =  storereport.productid  where storereporttype  = 1 ' . $storereport);

    // $employees = R::getAll('SELECT employeeId FROM employee where conditions = 0 ' . $employee);
    // $allsalarysfinal  = 0;
    // foreach ($employees as $employ) {
    $allsalarysfinal = R::getCell('select SUM(salaryreportnet) FROM salaryreport JOIN employee ON employee.employeeId = salaryreport.employeeid where salaryreport.conditions = 0 ' . $employee . ' ' . $salaryreportdate);
    // }



    $smarty->display("header.html");

    $smarty->assign('message', $message);
    $smarty->assign('storemovementfrom', $storemovementfrom);
    $smarty->assign('storemovementto', $storemovementto);
    $smarty->assign('transfermoney', $transfermoney);
    $smarty->assign('allstoredetails', $allstoredetails);
    $smarty->assign('allSellBilldetailsBuyPrice', $allSellBilldetailsBuyPrice);
    $smarty->assign('allSellBilldetails', $allSellBilldetails);
    $smarty->assign('allRetSellBills', $allRetSellBills);
    $smarty->assign('returnsellbillidetails', $returnsellbillidetails);


    $smarty->assign('restaurantrawdestructionValue', $restaurantrawdestructionValue);
    $smarty->assign('expensesValue', $expensesValue);
    $smarty->assign('storereport', $storereport);
    $smarty->assign('allsalarysfinal', $allsalarysfinal);


    $netprofit = ($allSellBilldetails-$returnsellbillidetails) - $allSellBilldetailsBuyPrice - $restaurantrawdestructionValue - $expensesValue - $allsalarysfinal + $storereport;
    $smarty->assign('netprofit', $netprofit);
    $smarty->display("branchProfitabilityReportView/show.html");
    $smarty->display("footer.html");
}
