<?php 
    include_once("../include/main.inc.php");
    check_session('MyCpanel','index.php');
    include_once('vendors/SimpleXLSXGen.php');
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);


    $books[] = array('Sn.', 'Invoice ID', 'User name', 'User Mobile', 'User Email', 'Applicants name', 'Mobile', 'Email', 'Organization', 'Billing Address', 'Delivery Address', 'Products', 'Payment', 'e-Kyc', 'Video', 'CA-Approval', 'Download', 'Delivery', 'Order', 'Total Amount', 'Discount', 'GST', 'Total', 'Paid', 'Balance', 'Order ID');
    
    $whr = ' WHERE order_id>0';
    if(isset($_REQUEST['f_order']) && !empty($_REQUEST['f_order'])){$whr .= " AND order_status='".$_REQUEST['f_order']."'"; }
    if(isset($_REQUEST['f_payment']) && !empty($_REQUEST['f_payment'])){$whr .= " AND payment_status='".$_REQUEST['f_payment']."'"; }
    if(isset($_REQUEST['f_agent']) && !empty($_REQUEST['f_agent'])){
        if($_REQUEST['f_agent'] == 'unassigned'){
            $whr .= " AND agentId IS NULL"; 
        }else{
            $whr .= " AND agentId=".$_REQUEST['f_agent']; 
        }
    }
    if(isset($_REQUEST['f_keyword']) && !empty($_REQUEST['f_keyword'])){$whr .= " AND (applicants_name LIKE '%".$_REQUEST['f_keyword']."%' OR applicants_org LIKE '%".$_REQUEST['f_keyword']."%' OR applicants_mobile LIKE '%".$_REQUEST['f_keyword']."%' OR bill_ogname LIKE '%".$_REQUEST['f_keyword']."%' OR bill_address LIKE '%".$_REQUEST['f_keyword']."%')"; }

    $sql= mysqli_query($dbconn,"SELECT orders_tbl1.*, customer_tbl.cname, cemail, cmobile, sum(order_payment_history.requested_amount) AS paid_amount FROM orders_tbl1 LEFT JOIN customer_tbl ON customer_tbl.cid=orders_tbl1.customer_id LEFT JOIN order_payment_history ON order_payment_history.invoice_id=orders_tbl1.invoice_id ".$whr." GROUP BY orders_tbl1.invoice_id ORDER BY order_id DESC ");
  

    $itms = mysqli_query($dbconn,"SELECT oi.*, catName FROM order_items AS oi LEFT JOIN category_tbl AS ct ON oi.category_id=ct.catID ");
    $odritm = [];
    
    while($itm = mysqli_fetch_array($itms)){
        $odritm[$itm['invoice_id']][] = $itm;
    }

    $sn = 1;
    while($row = mysqli_fetch_array($sql)){
        $product = [];
        $ons =1; 
        foreach($odritm[$row['invoice_id']] as $ot){
            $prd = ($ons++).'. '.(empty($ot['brand'])?'Token: ':'Signature: ').$ot['catName'];
            if(!empty($ot['brand'])){$prd .= ', '.$ot['brand'];}
            if(!empty($ot['validity'])){$prd .= ', '.$ot['validity'];}
            if(!empty($ot['user_type'])){$prd .= ', '.$ot['user_type'];}
            if(!empty($ot['validation'])){$prd .= ', '.$ot['validation'].' Year';}
            if(!empty($ot['token_type'])){$prd .= ', '.$ot['token_type'];}
            if(empty($ot['brand'])){$prd .= ', Qty-'.$ot['quantity'];}            
            $product[] = $prd;
        }
        $books[] = array($sn++, $row['invoice_id'], $row['cname'], $row['cmobile'], $row['cemail'],  $row['applicants_name'],$row['applicants_mobile'], $row['applicants_email'],$row['applicants_org'],$row['bill_address'].', '.$row['bill_city'].', '.$row['bill_state'].', '.$row['bill_zip'].', '.$row['bill_country'], $row['delivery_address'].', '.$row['delivery_city'].', '.$row['delivery_state'].', '.$row['delivery_zip'].', '.$row['delivery_country'], implode(' | ', $product), $row['payment_status'], $row['ekyc'], $row['video'], $row['caApproval'], $row['download_status'], $row['delivery_status'], $row['order_status'], round($row['total_amount']), round($row['discount']), round($row['gst_amount']), $ttl = ($row['total_amount']-$row['discount'])+$row['gst_amount'], round($row['paid_amount']), $ttl-$row['paid_amount']);
    }
    //  echo '<pre>';
    //  print_r($books);
    //  exit();
    $xlsx = Shuchkin\SimpleXLSXGen::fromArray( $books );
    //$xlsx->saveAs('books.xlsx'); 
    $xlsx->download();

    
?>
