<?php
include_once("../public/con_reedbean.php");
$do = $_GET['do'];
if (empty($do)) {
   $smarty->display("chartserp/index.html");
}else if ($do == 'categories') {
    categories();
}else if ($do == 'products') {
    products();
}else if ($do == 'select2products') {
    select2products();
}else if ($do == 'select2clients') {
    select2clients();
}else if ($do == 'select2categories') {
    select2categories();    
}else if ($do == 'clients') {
    clients();
}else if ($do == 'sellbills') {
    sellbills();
}else if ($do == 'chartssellbill') {
   $smarty->display("chartserp/chartssellbill.html");
}else if ($do == 'chartsproducts') {   
   $smarty->display("chartserp/chartsproducts.html");
}else if ($do == 'chartsclients') {  
   $smarty->display("chartserp/chartsclients.html");     
}else if ($do == 'chartscategories') {  
   $smarty->display("chartserp/chartscategories.html");   
}else if ($do == 'getdata') {
    getdata();
}

function select2categories() {
        
    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm']; 
    
    $productsData = R::getAll("SELECT productCatId, productCatName as name
    FROM  productcat 
    WHERE productCatName LIKE '%" . $name . "%' limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['productCatId'];
        $row_array['text'] = $pro['name'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}

function select2products() {
        
    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm']; 
    
    $productsData = R::getAll("SELECT productId,CONCAT(productName,'/',productCatName) as name
    FROM product left join productcat on product.productCatId = productcat.productCatId
    WHERE CONCAT(productName,'/',productCatName) LIKE '%" . $name . "%' limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['productId'];
        $row_array['text'] = $pro['name'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}

function select2clients() {
        
    $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 categories() {
   $fromdate = $_POST['fromdate'];
   $todate = $_POST['todate'];
   $kindprice = $_POST['kindprice'];
   $categorieid = $_POST['categorieid'];
   if ($categorieid != '') {
           if($fromdate != '' && $todate != ''){
               $fromdate = strtotime("first day of this month",strtotime($fromdate));
               $todate = strtotime("last day of this month",strtotime($todate));
            }else{
               $today = date('Y-m-d');  
               $fromdate = strtotime("first day of this month",strtotime($today));
               $todate = strtotime("last day of this month",strtotime($today));
            }     
            $new_allData = array(); 
            while($fromdate <= $todate)
            {
                 $start = date('Y-m-d', $fromdate);
                 $end = date("Y-m-t", strtotime($start));
                 $searchQuery = "";
                 $searchQuery .= 'and  sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '" and sellbilldetail.sellbilldetailcatid = " '.$categorieid.'"';             
                
                  $mounthd = date('m', $fromdate);
                  $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
                  $mounthds = $mounths[intval($mounthd)];
                   if($kindprice == ''){$kindprice = profitevaluation();}
                  $getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery"); 
                  
                 array_push($new_allData, $getdata);
                 $fromdate = strtotime("+1 month", $fromdate);
            }
   }else{
        if($fromdate == '' || $todate == ''){
           $fromdate = date('Y-m-d');
           $todate   = date('Y-m-d');
        }
       if($kindprice == ''){$kindprice = profitevaluation();}
       $searchQuery .='and  sellbilldetail.sellbilldetaildate  >= "' . $fromdate . '" and sellbilldetail.sellbilldetaildate <= "' . $todate . '" '; 
       $new_allData = R::getAll("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals,sellbilldetailcatid, productCatName as Namedata, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery group by sellbilldetailcatid");
   }
   
   echo json_encode($new_allData);
}

function products() {
   $fromdate = $_POST['fromdate'];
   $todate = $_POST['todate'];
   $kindprice = $_POST['kindprice'];
   $productid = $_POST['productid'];
   if ($productid != '') {
           if($fromdate != '' && $todate != ''){
               $fromdate = strtotime("first day of this month",strtotime($fromdate));
               $todate = strtotime("last day of this month",strtotime($todate));
            }else{
               $today = date('Y-m-d');  
               $fromdate = strtotime("first day of this month",strtotime($today));
               $todate = strtotime("last day of this month",strtotime($today));
            }     
            $new_allData = array(); 
            while($fromdate <= $todate)
            {
                 $start = date('Y-m-d', $fromdate);
                 $end = date("Y-m-t", strtotime($start));
                 $searchQuery = "";
                 $searchQuery .= 'and  sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '" and sellbilldetail.sellbilldetailproductid = " '.$productid.'"';             
                
                  $mounthd = date('m', $fromdate);
                  $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
                  $mounthds = $mounths[intval($mounthd)];
                   if($kindprice == ''){$kindprice = profitevaluation();}
                  $getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery"); 
                  
                 array_push($new_allData, $getdata);
                 $fromdate = strtotime("+1 month", $fromdate);
            }
   }else{
       if($fromdate == '' || $todate == ''){
           $fromdate = date('Y-m-d');
           $todate   = date('Y-m-d');
       }
       if($kindprice == ''){$kindprice = profitevaluation();}
       $searchQuery .='and  sellbilldetail.sellbilldetaildate  >= "' . $fromdate . '" and sellbilldetail.sellbilldetaildate <= "' . $todate . '" '; 

       $new_allData = R::getAll("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, productCatName, productName as Namedata, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery group by sellbilldetailproductid");
   }
   echo json_encode($new_allData);
}

function clients() {
        $clientid = $_POST['clientid']; 
        $fromdate = $_POST['fromdate'];
        $todate = $_POST['todate'];
        $kindprice = $_POST['kindprice'];
        
        if ($clientid != '') {
            if($fromdate != '' && $todate != ''){
               $fromdate = strtotime("first day of this month",strtotime($fromdate));
               $todate = strtotime("last day of this month",strtotime($todate));
            }else{
               $today = date('Y-m-d');  
               $fromdate = strtotime("first day of this month",strtotime($today));
               $todate = strtotime("last day of this month",strtotime($today));
            }     
            $new_allData = array(); 
            while($fromdate <= $todate)
            {
                 $start = date('Y-m-d', $fromdate);
                 $end = date("Y-m-t", strtotime($start));
                 $searchQuery = "";
                 $searchQuery .= 'and  sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '" and sellbill.sellbillclientid = " '.$clientid.'"';             
                
                  $mounthd = date('m', $fromdate);
                  $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
                  $mounthds = $mounths[intval($mounthd)];
                   if($kindprice == ''){$kindprice = profitevaluation();}
                  $getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery"); 
                  
                 array_push($new_allData, $getdata);
                 $fromdate = strtotime("+1 month", $fromdate);
            }
      }else{
        if($fromdate == '' || $todate == ''){
            $fromdate = date('Y-m-d');  
            $todate = date('Y-m-d');  
        }
        if($kindprice == ''){$kindprice = profitevaluation();}
        $searchQuery = '';
        $searchQuery .= " and sellbilldetail.sellbilldetaildate >= '$fromdate' and sellbilldetail.sellbilldetaildate <= '$todate' group by sellbillclientid";
         $new_allData = R::getAll("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, clientname as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join client on sellbill.sellbillclientid = client.clientid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
      }

     echo json_encode($new_allData);
}


// function product() {
//         $productid = $_POST['productid'];
//         $fromdate = $_POST['fromdate'];
//         $todate = $_POST['todate'];
//         $kindprice = $_POST['kindprice'];
//         if($fromdate != '' && $todate != ''){
//            $fromdate = strtotime("first day of this month",strtotime($fromdate));
//            $todate = strtotime("last day of this month",strtotime($todate));
//         }else{
//            $today = date('Y-m-d');  
//            $fromdate = strtotime("first day of this month",strtotime($today));
//            $todate = strtotime("last day of this month",strtotime($today));
//         } 
//        $new_allData = array();
//        if($productid  > 0) {
//         while($fromdate <= $todate)
//         {
//              $start = date('Y-m-d', $fromdate);
//              $end = date("Y-m-t", strtotime($start));
//              $searchQuery = "";
//              $searchQuery .= 'and  sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '"';
//              if ($productid != '') {
//              $searchQuery .= " and sellbilldetail.sellbilldetailproductid = ".$productid. " ";
//              }
//               $mounthd = date('m', $fromdate);


//               $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
//               $mounthds = $mounths[intval($mounthd)];
//               if($kindprice == ''){$kindprice = profitevaluation();}
//               $getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery"); 
              
//              array_push($new_allData, $getdata);
//              $fromdate = strtotime("+1 month", $fromdate);
//         }
//      }
//      echo json_encode($new_allData);
// }

function sellbills() {
        $fromdate = $_POST['fromdate'];
        $todate = $_POST['todate'];
        $kindprice = $_POST['kindprice'];
        if($fromdate != '' && $todate != ''){
           $fromdate = strtotime("first day of this month",strtotime($fromdate));
           $todate = strtotime("last day of this month",strtotime($todate));
        }else{
           $today = date('Y-m-d');  
           $fromdate = strtotime("first day of this month",strtotime($today));
           $todate = strtotime("last day of this month",strtotime($today));
        } 
       $new_allData = array(); 
        while($fromdate <= $todate)
        {
             $start = date('Y-m-d', $fromdate);
             $end = date("Y-m-t", strtotime($start));
             $searchQuery = "";
             $searchQuery .= 'and  sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '"';
              $mounthd = date('m', $fromdate);
              $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
              $mounthds = $mounths[intval($mounthd)];
              if($kindprice == ''){$kindprice = profitevaluation();}
              $getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery"); 
              
             array_push($new_allData, $getdata);
             $fromdate = strtotime("+1 month", $fromdate);
        }

     echo json_encode($new_allData);
}


function profitevaluation(){
    $Profitevaluation = R::getCell("select Profitevaluation from programsettings where programsettingsid = 1");
    $kindprice = '';
    switch ($Profitevaluation) {
        case 'first': 
            $kindprice .= 'sellbilldetail.buyprice';
            break;
        case 'last': 
            $kindprice .= 'sellbilldetail.lastbuyprice';
            break;
        case 'last_discount': 
            $kindprice .= 'sellbilldetail.lastbuyprice_withDiscount';
            break;
        case 'mean': 
            $kindprice .= 'sellbilldetail.meanbuyprice';
            break;
        case 'mean_discount': 
            $kindprice .= 'sellbilldetail.meanbuyprice_withDiscount';
            break;
        case 'generalPrice': 
            $kindprice .= 'product.productBuyPrice';
            break;
        default:
            $kindprice .='sellbilldetail.buyprice';
            break;
        
    }
   return $kindprice;
}

function getdata() {
     // for ($i = 1; $i <= 200; $i++) {
     //    $tblmarks = R::xdispense('tbl_marks');
     //    $tblmarks->student_name = substr(str_shuffle('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'),1,10);
     //    $tblmarks->marks = rand(100000, 999999);
     //    R::store($tblmarks);
     //  }   
        $getdata = R::getAll("SELECT id,student_name,marks FROM tbl_marks ORDER BY id limit 81");
        echo json_encode($getdata);
}

?>