<?php 
function product_amount_2(DB $db,$product_type,$data,$price,$products,&$amount){
    if(!empty($data)){
        foreach($data as $d){
            $count =count($d);
            if($count==1){continue;}
            $product_id = $d['product_id'];
            
            foreach($d as $key=>$l){
               if($key=='product_id'){continue;}
                if(!isset($amount[$key])){
                    $amount[$key]=0;
                }
                
                $price_data = $price[$key]??[];
                
                if(!empty($price_data)){
                    $price_set = 0;
                    foreach($price_data as  $k){
                        if($k['product_id']==$product_id){
                            $main_price = $k;
                            $price_set = 1;
                        }
                    }
                   
                    if(!$price_set){
                        $main_price = $products[$product_id];
                        $db->product_price_log($product_id,['sale_price'=>$main_price['sale_price'],'unit_cost'=>$main_price['unit_cost']]); 
                    }
                }
                if(empty($main_price)){ continue;}
                if($product_type==PRODUCT_TYPE_PACKAGING || $product_type==PRODUCT_TYPE_RAW){
                    //print_r($main_price['unit_cost']*$d[$key]);exit;
                    $amount[$key]+=$main_price['unit_cost']*$d[$key]; continue;
                }
                $amount[$key]+=$main_price['sale_price']*$d[$key]; continue;
            }
        }
    }
}

$date_range = $_POST['date_range'];
if(!empty($date_range)){
    $row_product = $db->selectAll('products','where type = '.PRODUCT_TYPE_RAW,'id,sale_price,unit_cost');
    $package_product = $db->selectAll('products','where type = '.PRODUCT_TYPE_PACKAGING,'id,sale_price,unit_cost');
    $general->arrayIndexChange($row_product);
    $general->arrayIndexChange($package_product);
    $chart_accounts = $db->get_company_data()['chart_of_account']??[];
    $chart_of_account_ids=[];
    $ledgers=[];
    $chart_of_account_wise_ledgers=[];
    if(!empty($chart_accounts)){
        foreach($chart_accounts as $ca){
            $chart_of_account_ids[] =$ca;
        }
        $ledgers = $db->selectAll('a_heads','where  charts_accounts_id  in('.implode(',',$chart_of_account_ids).')','id, charts_accounts_id ,title');
        $general->arrayIndexChange($ledgers);
        foreach($ledgers as $l){
            $chart_of_account_wise_ledgers[$l['charts_accounts_id']][]=$l;
        }
    }
    
    $dates=[];
    $product_log_query=[];
    $opening_product_log_query=[];
    $closed_product_log_query=[];
    $unit_price_query = [];
    $head_middle_query =[];
    $big_date=0;
    $small_date=0;
    $s=1;
    
    foreach($date_range as $date){
        $general->getFromToFromString($date,$from,$to);
        $dates["Q$s"]= [$from,$to];
        $product_log_query[]="SUM(CASE WHEN time BETWEEN $from AND $to THEN quantity * unit_price ELSE 0 END) AS 'Q$s'";
        $opening_product_log_query[]= " SUM(CASE WHEN action_time BETWEEN 0 AND $from THEN quantity ELSE 0 END) AS 'Q$s'";
        $closed_product_log_query[]= " SUM(CASE WHEN action_time BETWEEN 0 AND $to THEN quantity ELSE 0 END) AS 'Q$s'";
        $head_middle_query[]="SUM(CASE WHEN time BETWEEN $from AND $to THEN debit ELSE 0 END) -SUM(CASE WHEN time BETWEEN $from AND $to THEN credit ELSE 0 END) AS 'Q$s'";
        $unit_price_query["Q$s"] = $db->get_product_price_log_price($to);
        $s++;
        if($small_date==0 || $from<$small_date){
            $small_date = $from;
        }
        if($to>$big_date){
            $big_date = $to;
        }
    }
    $jArray[fl()]=$general->make_date($small_date,'i');
    $jArray[fl()]=$general->make_date($big_date,'i');
    $row_product_amount = [];
    $row_opening_product_amount = [];
    $row_closed_product_amount = [];
    $head_opening_balance=[];
    $chart_of_account_wise_data=[];
    $chart_of_account_wise_total=[];
    if(!empty($row_product)){
        $row_product_ids=array_keys($row_product);
        //$row_query = "SELECT CASE $product_log_query ELSE 'out_of_range' END AS date_range,product_id, SUM(quantity*unit_price) AS price FROM purchase_details WHERE time BETWEEN $small_date AND $big_date AND  product_id IN (".implode(',',$row_product_ids).") GROUP BY product_id,date_range ORDER BY `date_range` ASC;";
        $row_query = "SELECT ".implode(',',$product_log_query)." FROM purchase_details WHERE time BETWEEN $small_date AND $big_date AND product_id IN (".implode(',',$row_product_ids).") ORDER BY  product_id ASC;";

        $row_opening_query = "SELECT  product_id, ".implode(',',$opening_product_log_query)." FROM product_stock_log WHERE action_time BETWEEN 0 AND $big_date AND product_id IN (".implode(',',$row_product_ids).") GROUP BY  product_id ORDER BY  product_id ASC;";
        $row_closed_query = "SELECT  product_id, ".implode(',',$closed_product_log_query)." FROM product_stock_log WHERE action_time BETWEEN 0 AND $big_date AND product_id IN (".implode(',',$row_product_ids).") GROUP BY  product_id ORDER BY  product_id ASC;";

        


        $jArray[fl()]=$row_query;
        $jArray[fl()]=$row_opening_query;
        $jArray[fl()]=$row_closed_query;
        $row_product_log = $db->fetchQuery($row_query);
        //$row_product_log_2 = $db->fetchQuery($row_query_2);
        if(!empty($row_product_log)){
            $row_product_amount=$row_product_log[0];
        }
        $row_opening_product_log = $db->fetchQuery($row_opening_query);
        $row_closed_product_log = $db->fetchQuery($row_closed_query);

        $jArray[fl()]=$row_product_amount;
        $jArray[fl()]=$row_product_log;
        $jArray[fl()]=$row_opening_product_log;
        $jArray[fl()]=$row_closed_product_log;
        $jArray[fl()]=$unit_price_query;

        product_amount_2(
            $db,
            PRODUCT_TYPE_RAW,
            $row_opening_product_log,
            $unit_price_query,
            $row_product,
            $row_opening_product_amount,
        );
        product_amount_2(
            $db,
            PRODUCT_TYPE_RAW,
            $row_closed_product_log,
            $unit_price_query,
            $row_product,
            $row_closed_product_amount,
        );
        $jArray[fl()]=$row_opening_product_amount;
        $jArray[fl()]=$row_product_amount;
        $jArray[fl()]=$row_closed_product_amount;
    
    }
    if(!empty($ledgers)){
        $head_query = "SELECT head_id, ".implode(',',$head_middle_query)." FROM a_head_entry WHERE  time BETWEEN $small_date AND $big_date and head_id in(".implode(',',array_keys($ledgers)).") GROUP BY head_id ORDER BY head_id ASC;";    
        $jArray[fl()]=$head_query;
        $head_opening_balance = $db->fetchQuery($head_query);
        $general->arrayIndexChange($head_opening_balance,'head_id');
        foreach($head_opening_balance as $l){
            $ledger = $ledgers[$l['head_id']];
            $charts_accounts_id=$ledger['charts_accounts_id'];
            foreach($dates as $key=>$d){
                if(!isset($chart_of_account_wise_total[$charts_accounts_id][$key])){
                    $chart_of_account_wise_total[$charts_accounts_id][$key]=0; 
                }
                $chart_of_account_wise_total[$charts_accounts_id][$key]+=$l[$key];
            }
            
        }
    }
    $jArray[fl()]=$chart_of_account_wise_total;
    $jArray[fl()]=$head_opening_balance;
    
    $package_product_amount = [];
    $package_opening_product_amount = [];
    $package_closed_product_amount = [];
    if(!empty($package_product)){
        $package_product_ids=array_keys($package_product);
        $package_query = "SELECT ".implode(',',$product_log_query)." FROM purchase_details WHERE time BETWEEN $small_date AND $big_date AND product_id IN (".implode(',',$package_product_ids).") ORDER BY  product_id ASC;";
        $package_opening_query = "SELECT  product_id, ".implode(',',$opening_product_log_query)." FROM product_stock_log WHERE action_time BETWEEN 0 AND $big_date AND product_id IN (".implode(',',$package_product_ids).") GROUP BY  product_id ORDER BY  product_id ASC;";
        $package_closed_query = "SELECT  product_id, ".implode(',',$closed_product_log_query)." FROM product_stock_log WHERE action_time BETWEEN 0 AND $big_date AND product_id IN (".implode(',',$package_product_ids).") GROUP BY  product_id ORDER BY  product_id ASC;";
        $package_product_log = $db->fetchQuery($package_query);
        if(!empty($package_product_log)){
            $package_product_amount=$package_product_log[0];
        }
        $package_opening_product_log = $db->fetchQuery($package_opening_query);
        $package_closed_product_log = $db->fetchQuery($package_closed_query);
        $jArray[fl()]=$package_product_log;
        $jArray[fl()]=$package_opening_product_log;
        $jArray[fl()]=$package_closed_product_log;
        product_amount_2(
            $db,
            PRODUCT_TYPE_PACKAGING,
            $package_opening_product_log,
            $unit_price_query,
            $package_product,
            $package_opening_product_amount,
        );
        product_amount_2(
            $db,
            PRODUCT_TYPE_PACKAGING,
            $package_closed_product_log,
            $unit_price_query,
            $package_product,
            $package_closed_product_amount,
        );
        $jArray[fl()]=$package_closed_product_amount;
        $jArray[fl()]=$package_product_amount;
        $jArray[fl()]=$package_opening_product_amount;
        $jArray[fl()]=$package_closed_product_amount;
    }
}
$rData=[];
$title_data = [];

$row_consumed = [];
$row_op =[];
$row_purchased =[];
$row_cl =[];

$package_consumed = [];
$package_op =[];
$package_purchased =[];
$package_cl =[];

$cost_of_goods = [];

$chart_of_account_rData=[];

$head=[
    ['title'=>' ','key'=>'title'],
];
$factory_overhead_account_head_id = $acc->get_chart_of_account(COA_FACTORY_OVER_HEAD);
if(!empty($dates)){
    foreach($dates as $key=>$date){
        $debit_key = "debit_$key";
        $credit_key = "credit_$key";
        $head[]=['title'=>'Debit','key'=>$debit_key];
        $head[]=['title'=>'Credit','key'=>$credit_key];

        $title_data[$debit_key]=['t'=>$general->make_date($date[0]).' to '.$general->make_date($date[1]),'b'=>1,'col'=>2];
        $title_data[$credit_key]=false;

        $row_amount = $row_product_amount[$key]??0;
        $row_op_amount = $row_opening_product_amount[$key]??0;
        $row_cl_amount = $row_closed_product_amount[$key]??0;
        $row_materials_consumed = $row_amount+$row_op_amount+$row_cl_amount;

        $row_consumed[$debit_key]=['t'=>$general->numberFormat($row_materials_consumed)];
        $row_consumed[$credit_key]=['t'=>'0.00'];

        $row_op[$debit_key]=['t'=>$general->numberFormat($row_op_amount)];
        $row_op[$credit_key]=['t'=>'0.00'];

        $row_purchased[$debit_key]=['t'=>'0.00'];
        $row_purchased[$credit_key]=['t'=>$general->numberFormat($row_amount)];

        $row_cl[$debit_key]=['t'=>$general->numberFormat($row_cl_amount)];
        $row_cl[$credit_key]=['t'=>'0.00'];

        $package_amount = $package_product_amount[$key]??0;
        $package_op_amount = $package_opening_product_amount[$key]??0;
        $package_cl_amount = $package_closed_product_amount[$key]??0;
        $package_materials_consumed = $package_amount+$package_op_amount+$package_cl_amount;

        $package_consumed[$debit_key]=['t'=>$general->numberFormat($package_materials_consumed)];
        $package_consumed[$credit_key]=['t'=>'0.00'];

        $package_op[$debit_key]=['t'=>$general->numberFormat($package_op_amount)];
        $package_op[$credit_key]=['t'=>'0.00'];
        $package_purchased[$debit_key]=['t'=>'0.00'];
        $package_purchased[$credit_key]=['t'=>$general->numberFormat($package_amount)];
        $jArray[fl()][$key]=$package_cl_amount;
        $package_cl[$debit_key]=['t'=>$general->numberFormat($package_cl_amount)];
        $package_cl[$credit_key]=['t'=>'0.00'];


        $factory_overhead_amount = $chart_of_account_wise_total[$factory_overhead_account_head_id][$key]??0 ;
        $cost_of_goods_amount = $row_materials_consumed+$package_materials_consumed+$factory_overhead_amount;//এখানে একটা বাদ থাকলো
        $jArray[fl()][$key]=[$row_materials_consumed,$package_materials_consumed];
        $cost_of_goods[$debit_key]=['t'=>'0.00','key'=>$key];
        $cost_of_goods[$credit_key]=['t'=>$general->numberFormat(-1*$cost_of_goods_amount),'key'=>$key];

       


    }
}
$rData[]=['title'=>['t'=>'Particulars','b'=>1],...$title_data];;
$rData[] = ['title'=>['t'=>'Cost of Goods Sales','b'=>1],...$cost_of_goods];
$rData[] = ['title'=>['t'=>'Raw materials consumed','b'=>1],...$row_consumed];
$rData[] = ['title'=>['t'=>'Opening Raw Materials'],...$row_op];
$rData[] = ['title'=>['t'=>'Add: Purchased Raw Materials'],...$row_purchased];
$rData[] = ['title'=>['t'=>'Less: Closing Raw Materials'],...$row_cl];

$rData[] = ['title'=>['t'=>'Packing Materials consumed','b'=>1],...$package_consumed];
$rData[] = ['title'=>['t'=>'Opening Packing Materials'],...$package_op];
$rData[] = ['title'=>['t'=>'Add: Purchased Packing Materials during the year'],...$package_purchased];
$rData[] = ['title'=>['t'=>'Less: Closing Packing Materials'],...$package_cl];
$jArray[fl()]=$acc->chart_of_accounts();
$jArray[fl()]=$chart_of_account_wise_ledgers;
$jArray[fl()]=$chart_accounts;
if(!empty($chart_accounts)){
    foreach($acc->chart_of_accounts() as $ca){
        $chart_account = $chart_accounts[$ca['id']]??0;
        $ledger= $chart_of_account_wise_ledgers[$chart_account]??[];
        $jArray[fl()][]=$ledger;
        $ca_amount = [];
        $total_amount=[];
        foreach($dates as $key=>$date){
            $debit_key = "debit_$key";
            $credit_key = "credit_$key";
            $ch_of_ac_total = floatval(@$chart_of_account_wise_total[$chart_account][$key]) ;
            if($ch_of_ac_total>0){
                $debit=$ch_of_ac_total;
                $credit=0;
            }
            else{
                $debit=0;
                $credit=-1*$ch_of_ac_total;
            }
            $ca_amount[$debit_key]=['t'=>$general->numberFormat($debit)];
            $ca_amount[$credit_key]=['t'=>$general->numberFormat($debit)];

            $total_amount[$debit_key]=['t'=>$general->numberFormat($debit),'key'=>$key];
            $total_amount[$credit_key]=['t'=>$general->numberFormat($credit),'key'=>$key];
        }
        $rData[]=['title'=>['t'=>$ca['title'],'b'=>1],...$ca_amount];
        
        foreach($ledger as $l){
            $l_amount = [];
            foreach($dates as $key=>$date){
                $debit_key = "debit_$key";
                $credit_key = "credit_$key";
                $ledger_amount = floatval(@$head_opening_balance[$l['id']][$key]);
                if($ledger_amount>0){
                    $debit=$ledger_amount;
                    $credit=0;
                }
                else{
                    $debit=0;
                    $credit=-1*$ledger_amount;
                }
                $l_amount[$debit_key]=['t'=>$general->numberFormat($debit),'key'=>$key];
                $l_amount[$credit_key]=['t'=>$general->numberFormat($credit),'key'=>$key];
            }
            $rData[]=['title'=>['t'=>$l['title']],...$l_amount];
        }
        $rData[]=['title'=>['t'=>$ca['title'].' Distribution','b'=>1],...$total_amount];

    }
}

$jArray[fl()]=$rData;


$fileName='item_wise_sale_'.TIME.rand(0,999).'.txt';
$report_data=[
    'name'      => 'saleReport',
    'title'     => 'Sale List',
    'info'      => [],
    'fileName'  => $fileName,
    'head'=>$head,
    'data'=>$rData
];
$gAr['report_data']= $report_data;
textFileWrite(json_encode($report_data),ROOT_DIR.'/print_file/'.$fileName);
$jArray['html']     = $general->fileToVariable(ROOT_DIR.'/ajax/report.phtml');
$jArray['status']=1;