<?php

session_start();

require('../lib/dbclass.php');




require('../PhpXlsxGenerator.php') ;


// Filter the excel data
function filterData(&$str)
{
    $str = preg_replace("/\t/", '\\t', $str);
    $str = preg_replace("/\r?\n/", '\\n', $str);

    if (strstr($str, '"')) {
        $str = '"' . str_replace('"', '""', $str) . '"';
    }
}

// Excel file name for download
$fileName = 'Sponsor Details.xlsx';

// Column names
$excelData[] = ['Name','Mobile','Address','Date',	'Assembly',	'Panchayat','Ward','Product','Qty','Amount','Received'];

if ($_SESSION['where1']) {
    $where = $_SESSION['where1'];
} else {
    $where = '';
}

if ($_SESSION['challengeid']) {
    $chlngid = $_SESSION['challengeid'];
} else {
    $chlngid = 0;
}









$data = findQuery("SELECT 
    tbl_transaction.id,
    tbl_transaction.orderforothers,
    tbl_customer.id cuid,
    tbl_customer.name,
     tbl_customer.Mobile
    ,tbl_ward.ward_name
    ,tbl_panchayat.panchayat_name
    ,tbl_assembly.name assembly
    ,
    tbl_district.district_name
    ,tbl_transaction.Received
    ,tbl_transaction.Amount
    
    ,tbl_transaction.dateadded ordrddate
     from tbl_transaction
     inner join tbl_customer on tbl_customer.id=tbl_transaction.customerid inner join tbl_ward on tbl_ward.id=tbl_customer.Ward inner join tbl_panchayat on tbl_panchayat.id=tbl_customer.Panchayat
    inner join tbl_assembly on tbl_assembly.id=tbl_customer.Assembly 
    inner join tbl_district on tbl_district.id=tbl_customer.District where tbl_transaction.status=0 and tbl_transaction.sponsorship=1 and tbl_transaction.challengeid=$chlngid and $where
    
");









if ($data) {
    // Output each row of the data
    foreach ($data as $index => $row) {
        $id      = $row['id'];
        $cuid    = $row['cuid'];
        $chlngid = $row['challengeid'];



        if ($row['orderforothers'] == 1) {
            $dataaddrs     = findQuery("SELECT * FROM tbl_transaction WHERE id=$id");
            $address       = $dataaddrs[0]['address'];
            $row['Mobile'] = $dataaddrs[0]['mobile'];
            $row['name']   = $dataaddrs[0]['name'];
        } else {
            $dataaddrs = findQuery("SELECT * FROM tbl_customer WHERE id=$cuid");
            $address   = $dataaddrs[0]['Address'];

            if ($address == '') {
                $address = 'NA';
            }
        }






        $dataprod = findQuery("SELECT tbl_product.product_name,tbl_transactiondetails.qty,tbl_transactiondetails.rate 
													FROM `tbl_transaction` inner join tbl_transactiondetails on tbl_transaction.id=tbl_transactiondetails.hdrid 
													inner join tbl_product on tbl_product.id=tbl_transactiondetails.item where tbl_transactiondetails.hdrid=$id");
        $product = '';

        if ($dataprod) {
            for ($iq = 0;$iq < count($dataprod);$iq++) {
                $product .= $dataprod[$iq]['product_name'] . ' (' . $dataprod[$iq]['qty'] . ') ,';
            }
        }





        $lineData = [
                        $row['name'],
                        $row['Mobile'],
                        $address,
                        date('d-m-Y', strtotime($row['ordrddate'])),
                        $row['assembly'],
                        $row['panchayat_name'],
                         $row['ward_name'],
                         $product,
                        $row['Amount'],
                        $row['Received'],];
        $excelData[] = $lineData;
    }
} else {
    $excelData .= 'No records found...' . "\n";
}

// Export data to excel and download as xlsx file
$xlsx = CodexWorld\PhpXlsxGenerator::fromArray($excelData);
$xlsx->downloadAs($fileName);

exit;
