<?php
include("../public/impOpreation.php");

//the global file operation
include_once("../public/config.php");

$do = $_GET['do'];




//check and use the condetion that suite this action
if (empty($do)) {

    $allClientTypes = R::getAll('SELECT * FROM typeclient WHERE conditions = 0 ORDER BY typeName ASC');
    $smarty->assign('allClientTypes',$allClientTypes);
    
    $fromdate = $_POST['fromdate'];
    $todate = $_POST['todate'];
    $typeclientid = $_POST['typeclientid'];
    $searchQuery = "";
    $searchQuery2 = "";
    $typQuery = "";

    if($typeclientid != '' && $typeclientid != '-20'){
        $typQuery .= ' and cust.typeclientid LIKE "%'.$typeclientid.'%" ';
    }

    if ($fromdate != '' && $todate != '') {
        $searchQuery .= ' and s.sellbilldate   >= "' . $fromdate . ' 00-00-00" and s.sellbilldate  <=  "' . $todate . ' 23-59-59" ';
        $searchQuery2 .= ' and sr.returnsellbilldate   >= "' . $fromdate . ' 00-00-00" and sr.returnsellbilldate  <=  "' . $todate . ' 23-59-59" ';
        
            
    $sql = "
          WITH RECURSIVE cat_tree AS (
    SELECT
        productCatId,
        productCatParent,
        productCatId AS main_category_id
    FROM productcat
    WHERE productCatParent = 0 AND conditions = 0

    UNION ALL

    SELECT
        c.productCatId,
        c.productCatParent,
        ct.main_category_id
    FROM productcat c
    JOIN cat_tree ct ON ct.productCatId = c.productCatParent
)

SELECT
    ct.main_category_id           AS category_id,
    mc.productCatName             AS category_name,
    cust.clientid                 AS customer_id,
    cust.clientname               AS customer_name,
    cust.typeclientid             AS typeclient_id,
    cust.clientcode               AS customer_code,
    SUM(t.profit)                 AS profit
FROM (
    SELECT
        p.productCatId        AS category_id,
        s.sellbillclientid   AS customer_id,
        (si.sellbilldetailtotalprice - si.discountvalue) AS profit
    FROM sellbill s
    JOIN sellbilldetail si ON si.sellbillid = s.sellbillid
    JOIN product p ON p.productId = si.sellbilldetailproductid
    WHERE s.conditions = 0 $searchQuery

    UNION ALL

    SELECT
        p.productCatId        AS category_id,
        sr.returnsellbillclientid AS customer_id,
        -(sri.returnsellbilldetailtotalprice - sri.discountvalue) AS profit
    FROM returnsellbill sr
    JOIN returnsellbilldetail sri ON sri.returnsellbillid = sr.returnsellbillid
    JOIN product p ON p.productId = sri.returnsellbilldetailproductid
    WHERE sr.conditions = 0 $searchQuery2

) t
JOIN cat_tree ct ON ct.productCatId = t.category_id
JOIN productcat mc ON mc.productCatId = ct.main_category_id
JOIN client cust ON cust.clientid = t.customer_id
where 1=1 $typQuery
GROUP BY ct.main_category_id, cust.clientid
ORDER BY cust.clientname";
    // print_r($sql);
    // exit();
    $data = R::getAll($sql);
   
  $categories = array();   // الهيدر
$customers  = array();   // الصفوف
$table      = array();   // القيم
$rowTotals  = array();
$colTotals  = array();

foreach ($data as $row) {

    $catId  = $row['category_id'];
    $custId = $row['customer_id'];
    $profit = (float)$row['profit'];
   
    $typeIds = explode(',', $row['typeclient_id']);
    $typeName = '';
    if(in_array('-20', $typeIds)){
        $typeName = 'الكل';
    }
    else{
        foreach ($typeIds as $id) {
            if($id > 0)
                $typeName .= R::getCell('SELECT typeName FROM typeclient WHERE typeId = ?', [$id]);
        }
    }
    
    $categories[$catId] = $row['category_name'];
    // $customers[$custId] = $row['customer_name'];
    $customers[$custId] = array(
        'customer_name' => $row['customer_name'],
        'typeName' => $typeName,
        'customer_code' => $row['customer_code']
    );

    if (!isset($table[$custId])) {
        $table[$custId] = array();
    }

    if (!isset($table[$custId][$catId])) {
        $table[$custId][$catId] = 0;
    }

    $table[$custId][$catId] += $profit;

    if (!isset($rowTotals[$custId])) {
        $rowTotals[$custId] = 0;
    }
    $rowTotals[$custId] += $profit;

    if (!isset($colTotals[$catId])) {
        $colTotals[$catId] = 0;
    }
    $colTotals[$catId] += $profit;
}

$grandTotal = array_sum($colTotals);

  

    }
    
    $smarty->assign('categories',$categories);
    $smarty->assign('customers',$customers);
    $smarty->assign('table',$table);
    $smarty->assign('rowTotals',$rowTotals);
    $smarty->assign('colTotals',$colTotals);
    $smarty->assign('grandTotal',$grandTotal);
    $smarty->display("header.html");
    $smarty->display("customerSalesReportMainCategoriesView/show.html");
    $smarty->display("footer.html");
 
    //here the smarty templates
} 

