<?php  
    require_once(__DIR__ . '/../../../extensions/Spreadsheet/autoload.php'); // Adjust the path as needed
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Cell\Cell;
    $spreadsheet = new Spreadsheet();
    $activeWorksheet = $spreadsheet->getActiveSheet();
    $activeWorksheet->setCellValue('A1', 'Hello World !');
    $activeWorksheet->setCellValue('A2', 2);
    $activeWorksheet->setCellValue('A3', 3);
    $activeWorksheet->setCellValue('A4', 4);
    
    /*$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
    $drawing->setWorksheet($spreadsheet->getActiveSheet());
    $drawing->setPath('logo.png');
    $drawing->setWidthAndHeight(158, 72);
    $drawing->setResizeProportional(true);
    
    $drawing->setOffsetX(100);    // this is how
    $drawing->setOffsetY(30);    //
    */
    $formula1 = "=SUM(A2:A4)";
    
    $activeWorksheet->setCellValue('A5', $formula1);
    
    // Create a writer and save the spreadsheet to a file
    $writer = new Xlsx($spreadsheet);
    $mod_dis = Distributers::model()->findByPk((int)$distributer_id);
    $file_name='Order_'.$mod_dis->distributer_name.'_'.$year.'.xls';
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=\"" . $file_name . "\"");
    header('Cache-Control: max-age=0');
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    //$writer->save('php://output');
    /*$filename = 'example.xls';
    
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment;filename=\"$filename\"");
    */
    
   /* $price_date = $year.'-'.$month.'-'.'01';
    $cnd = 'is_active=:active OR (is_active=:discontinue AND ((discontinuing_month>=:month AND discontinuing_year=:year) OR discontinuing_year>:year))';
    $params = [
        ':active' => 'Y',
        ':discontinue' => 'D',
        ':month' => (int)$month,
        ':year' => (int)$year
    ];
    $model_inventory = Inventory::model()->findAll([
        'condition' => $cnd,
        'order' => 'product_category_id,gmc_sort_order asc',
        'params' => $params
    ]);
            
    $file = fopen("upload/test.csv", "w");
    $j = 0;
    $mod_dis = Distributers::model()->with('region_det')->findByPk((int)$distributer_id);
    $region_id= (int)$mod_dis->region_id;
    $arr_months=Order::getmonths();
    $list[$j++] = 'ORDER REVIEW FORM QTY';
    $jan='';$feb='';$mar='';$apr='';$may='';$jun='';$jul='';$aug='';$sep='';$oct='';$nov='';$dec='';
    if($month<=3){
      $year =$year-1;
    }
    if((int)$month>0){
        switch((int)$month){
            case 1:
                $jan='Input your Order in this month=>';
                break;
            case 2:
                $feb='Input your Order in this month=>';
                break;
            case 3:
                $mar='Input your Order in this month =>'.'https://blueskylogistics.me/yamaha_new_18_11_2022/images/darrow.png';
                break;
            case 4:
                $apr='Input your Order in this month=>';
                break;
            case 5:
                $may='Input your Order in this month=>';
                break;
            case 6:
                $jun='Input your Order in this month=>';
                break;
            case 7:
                $jul='Input your Order in this month=>';
                break;
            case 8:
                $aug='Input your Order in this month=>';
                break;
            case 9:
                $sep='Input your Order in this month=>';
                break;
            case 10:
                $oct='Input your Order in this month=>';
                break;
            case 11:
                $nov='Input your Order in this month=>';
                break;
            case 12:
                $dec='Input your Order in this month=>';
                break;
                        
        }
    }
    $list[$j++] = 'Distributor!!'.$mod_dis->distributer_name.'!!!!!'.$apr.'!!!!!'.$may.'!!!!!'.$jun.'!!!!!'.'!!!!!'.$jul.'!!!!!'.$aug.'!!!!!'.$sep.'!!!!!'.'!!!!!'.$oct.'!!!!!'.$nov.'!!!!!'.$dec.'!!!!!'.'!!!!!'.$jan.'!!!!!'.$feb.'!!!!!'.$mar ;
    $year1 =(int)$year+1;
    $list[$j++] = '!PRODUCT CATEGORY!GMC!Model!'.$mod_dis->region_det->region_name.'!CBM!BASE CODE!01-04-'.$year.'!!!!!01-05-'.$year.'!!!!!01-06-'.$year.'!!!!!1Q TOTAL!!!!!01-07-'.$year.'!!!!!01-08-'.$year.'!!!!!01-09-'.$year.'!!!!!2Q TOTAL!!!!!01-10-'.$year.'!!!!!01-11-'.$year.'!!!!!01-12-'.$year.'!!!!!3Q TOTAL!!!!!01-01-'.$year1.'!!!!!01-02-'.$year1.'!!!!!01-03-'.$year1.'!!!!!4Q TOTAL!!!!!TOTAL'.$year.'-'.$year1;
    
    $list[$j++] = '!!!!!!!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM!ORDER!Supply Confirmation!SHIP!DIF!CBM';
    foreach ($model_inventory as $i => $row) {
                $apr_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="4" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $may_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="5" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jun_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="6" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jul_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="7" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $aug_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="8" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $sep_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="9" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $oct_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="10" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $nov_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="11" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $dec_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="12" AND parent_det.year='.$year.' AND parent_det.distributer_id='.(int)$distributer_id));
                $jan_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="1" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $feb_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="2" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                $mar_model=OrderDetails::model()->with('parent_det')->find(array('select'=>'quantity_ordered,quantity_confirmed,quantity_shipped,is_done','condition'=>'product_id='.(int)$row['id'].' AND parent_det.month="3" AND parent_det.year='.$year1.' AND parent_det.distributer_id='.(int)$distributer_id));
                //ordered//
                $qty_apr=(int)$apr_model->quantity_ordered;
                $qty_may=(int)$may_model->quantity_ordered;
                $qty_jun=(int)$jun_model->quantity_ordered;
                $qty_jul=(int)$jul_model->quantity_ordered;
                $qty_aug=(int)$aug_model->quantity_ordered;
                $qty_sep=(int)$sep_model->quantity_ordered;
                $qty_oct=(int)$oct_model->quantity_ordered;
                $qty_nov=(int)$nov_model->quantity_ordered;
                $qty_dec=(int)$dec_model->quantity_ordered;
                $qty_jan=(int)$jan_model->quantity_ordered;
                $qty_feb=(int)$feb_model->quantity_ordered;
                $qty_mar=(int)$mar_model->quantity_ordered;
                $qty_q1=$qty_apr+$qty_may+$qty_jun;
                $qty_q2=$qty_jul+$qty_aug+$qty_sep;
                $qty_q3=$qty_oct+$qty_nov+$qty_dec;
                $qty_q4=$qty_jan+$qty_feb+$qty_mar;
                $qty_tot=$qty_q1+$qty_q2+$qty_q3+$qty_q4;
                //supplyconfirmation//
                
                $supplyqty_apr=(int)$apr_model->quantity_confirmed;
                $dsupplyqty_apr='';
                if($qty_apr>0 && $apr_model->is_done=="Y"){
                    $dsupplyqty_apr=$supplyqty_apr;
                }
                $supplyqty_may=(int)$may_model->quantity_confirmed;
                $dsupplyqty_may='';
                if($qty_may>0 && $may_model->is_done=="Y"){
                    $dsupplyqty_may=$supplyqty_may;
                }
                $supplyqty_jun=(int)$jun_model->quantity_confirmed;
                $dsupplyqty_jun='';
                if($qty_jun>0 && $jun_model->is_done=="Y"){
                    $dsupplyqty_jun=$supplyqty_jun;
                }
                $supplyqty_jul=(int)$jul_model->quantity_confirmed;
                $dsupplyqty_jul='';
                if($qty_jul>0 && $jul_model->is_done=="Y"){
                    $dsupplyqty_jul=$supplyqty_jul;
                }
                $supplyqty_aug=(int)$aug_model->quantity_confirmed;
                $dsupplyqty_aug='';
                if($qty_aug>0 && $aug_model->is_done=="Y"){
                    $dsupplyqty_aug=$supplyqty_aug;
                }
                $supplyqty_sep=(int)$sep_model->quantity_confirmed;
                $dsupplyqty_sep='';
                if($qty_sep>0&&$sep_model->is_done=="Y"){
                    $dsupplyqty_sep=$supplyqty_sep;
                }
                $supplyqty_oct=(int)$oct_model->quantity_confirmed;
                $dsupplyqty_oct='';
                if($qty_oct>0&&$oct_model->is_done=="Y"){
                    $dsupplyqty_oct=$supplyqty_oct;
                }
                $supplyqty_nov=(int)$nov_model->quantity_confirmed;
                $dsupplyqty_nov='';
                if($qty_nov>0&&$nov_model->is_done=="Y"){
                    $dsupplyqty_nov=$supplyqty_nov;
                }
                $supplyqty_dec=(int)$dec_model->quantity_confirmed;
                $dsupplyqty_dec='';
                if($qty_dec>0&&$dec_model->is_done=="Y"){
                    $dsupplyqty_dec=$supplyqty_dec;
                }
                $supplyqty_jan=(int)$jan_model->quantity_confirmed;
                $dsupplyqty_jan='';
                if($qty_jan>0&&$jan_model->is_done=="Y"){
                    $dsupplyqty_jan=$supplyqty_jan;
                }
                $supplyqty_feb=(int)$feb_model->quantity_confirmed;
                $dsupplyqty_feb='';
                if($qty_feb>0&&$feb_model->is_done=="Y"){
                    $dsupplyqty_feb=$supplyqty_feb;
                }
                $supplyqty_mar=(int)$mar_model->quantity_confirmed;
                $dsupplyqty_mar='';
                if($qty_mar>0&&$mar_model->is_done=="Y"){
                    $dsupplyqty_mar=$supplyqty_mar;
                }
                $supplyqty_q1=$supplyqty_apr+$supplyqty_may+$supplyqty_jun;
                $supplyqty_q2=$supplyqty_jul+$supplyqty_aug+$supplyqty_sep;
                $supplyqty_q3=$supplyqty_oct+$supplyqty_nov+$supplyqty_dec;
                $supplyqty_q4=$supplyqty_jan+$supplyqty_feb+$supplyqty_mar;
                $supplyqty_tot=$supplyqty_q1+$supplyqty_q2+$supplyqty_q3+$supplyqty_q4;
                //supplyconfirmation//
                //supplycbm//
                $supplycbmqty_apr=$supplyqty_apr*$row['cbm'];
                $supplycbmqty_may=$supplyqty_may*$row['cbm'];
                $supplycbmqty_jun=$supplyqty_jun*$row['cbm'];
                $supplycbmqty_jul=$supplyqty_jul*$row['cbm'];
                $supplycbmqty_aug=$supplyqty_aug*$row['cbm'];
                $supplycbmqty_sep=$supplyqty_sep*$row['cbm'];
                $supplycbmqty_oct=$supplyqty_oct*$row['cbm'];
                $supplycbmqty_nov=$supplyqty_nov*$row['cbm'];
                $supplycbmqty_dec=$supplyqty_dec*$row['cbm'];
                $supplycbmqty_jan=$supplyqty_jan*$row['cbm'];
                $supplycbmqty_feb=$supplyqty_feb*$row['cbm'];
                $supplycbmqty_mar=$supplyqty_mar*$row['cbm'];
                $supplycbmqty_q1=$supplycbmqty_apr+$supplycbmqty_may+$supplycbmqty_jun;
                $supplycbmqty_q2=$supplycbmqty_jul+$supplycbmqty_aug+$supplycbmqty_sep;
                $supplycbmqty_q3=$supplycbmqty_oct+$supplycbmqty_nov+$supplycbmqty_dec;
                $supplycbmqty_q4=$supplycbmqty_jan+$supplycbmqty_feb+$supplycbmqty_mar;
                $supplycbmqty_tot=$supplycbmqty_q1+$supplycbmqty_q2+$supplycbmqty_q3+$supplycbmqty_q4;
                //supplycbm//
                //shipped cbm//
                $shp_apr=(int)$apr_model->quantity_shipped;
                $shp_may=(int)$may_model->quantity_shipped;
                $shp_jun=(int)$jun_model->quantity_shipped;
                $shp_jul=(int)$jul_model->quantity_shipped;
                $shp_aug=(int)$aug_model->quantity_shipped;
                $shp_sep=(int)$sep_model->quantity_shipped;
                $shp_oct=(int)$oct_model->quantity_shipped;
                $shp_nov=(int)$nov_model->quantity_shipped;
                $shp_dec=(int)$dec_model->quantity_shipped;
                $shp_jan=(int)$jan_model->quantity_shipped;
                $shp_feb=(int)$feb_model->quantity_shipped;
                $shp_mar=(int)$mar_model->quantity_shipped;
                $shp_q1=$shp_apr+$shp_may+$shp_jun;
                $shp_q2=$shp_jul+$shp_aug+$shp_sep;
                $shp_q3=$shp_oct+$shp_nov+$shp_dec;
                $shp_q4=$shp_jan+$shp_feb+$shp_mar;
                $shp_tot=$shp_q1+$shp_q2+$shp_q3+$shp_q4;
                //shipped cbm//
                //region value//
                    $price_cnd = 'product_id='.(int)$row['id'].' AND region_id='.(int)$region_id.' AND from_date <="'.$price_date.'" AND to_date >="'.$price_date.'"';
                    $mod_inv_price=InventoryPrice::model()->find(array('condition'=>$price_cnd));
                //region value//
                
                
                
                $list[$j++] = '!'.$row['category_det']['product_category']. '!' . $row['gmc_code'] . '!' . $row['model_no']
                        . '!' . (float)$mod_inv_price['price'] . '!' . $row['cbm'] . '!' . $row['base_code'] 
                        . '!'.$qty_apr.'!'.$dsupplyqty_apr.'!'.$shp_apr.'!'.($supplyqty_apr-$shp_apr).'!'.$supplycbmqty_apr. '!'.$qty_may.'!'.$dsupplyqty_may.'!'.$shp_may.'!0!'.$supplycbmqty_may. '!'.$qty_jun.'!'.$dsupplyqty_jun.'!'.$shp_jun.'!0!'.$supplycbmqty_jun. '!'.$qty_q1.'!'.$supplyqty_q1.'!'.$shp_q1.'!0!'.$supplycbmqty_q1
                        . '!'.$qty_jul.'!'.$dsupplyqty_jul.'!'.$shp_jul.'!0!'.$supplycbmqty_jul. '!'.$qty_aug.'!'.$dsupplyqty_aug.'!'.$shp_aug.'!0!'.$supplycbmqty_aug. '!'.$qty_sep.'!'.$dsupplyqty_sep.'!'.$shp_sep.'!0!'.$supplycbmqty_sep. '!'.$qty_q2.'!'.$supplyqty_q2.'!'.$shp_q2.'!0!'.$supplycbmqty_q2
                        . '!'.$qty_oct.'!'.$dsupplyqty_oct.'!'.$shp_oct.'!0!'.$supplycbmqty_oct. '!'.$qty_nov.'!'.$dsupplyqty_nov.'!'.$shp_nov.'!0!'.$supplycbmqty_nov. '!'.$qty_dec.'!'.$dsupplyqty_dec.'!'.$shp_dec.'!0!'.$supplycbmqty_dec. '!'.$qty_q3.'!'.$supplyqty_q3.'!'.$shp_q3.'!0!'.$supplycbmqty_q3
                        . '!'.$qty_jan.'!'.$dsupplyqty_jan.'!'.$shp_jan.'!0!0'. '!'.$qty_feb.'!'.$dsupplyqty_feb.'!'.$shp_feb.'!0!0'. '!'.$qty_mar.'!'.$dsupplyqty_mar.'!'.$shp_mar.'!0!0'. '!'.$qty_q4.'!'.$supplyqty_q4.'!'.$shp_q4.'!0!'.$supplycbmqty_q4
                        . '!'.$qty_tot.'!'.$supplyqty_tot.'!'.$shp_tot.'!0!'.$supplycbmqty_tot;
            }
            $j++;
            foreach ($list as $line) {
                echo implode(',', explode('!', $line)).'\n';
            }
            /*fclose($file);
            $filename = 'upload/test.csv';
            $path = Yii::app()->getBaseUrl(true) . '/upload';
            header('Content-Type: application/csv');
            $file_name='Order_'.$mod_dis->distributer_name.'_'.$year.'.csv';
            header('Content-disposition: attachment; filename="'.$file_name.'"'); 
            readfile($filename);
            Yii::app()->end();*/
        ?>