<?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 = 'Donation Details.xlsx';

// Column names
$excelData[] = ['Name','Mobile','Remarks','Date',	'Assembly',	'Panchayat','Ward',	'Amount','Received','Order id','Transaction id/Bank Reference No.'];

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

if ($_SESSION['searchTransactionType_drop'] == 4) {
    $where1 .= 'and tbl_transaction.subscription=1';
}

if ($_SESSION['searchTransactionType_drop'] == 3) {
    $where1 .= 'and tbl_transaction.subscription=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
    ,tbl_transaction.Transid,orderid
     from tbl_transaction
     inner join tbl_customer on tbl_customer.id=tbl_transaction.customerid left 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.donation=1 and $where1 and  
    tbl_transaction.volunteer=0 and tbl_transaction.Received>0
    
    
    union all
    
    
    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
   ,tbl_transaction.Transid,orderid
     from tbl_transaction
     inner join tbl_customer on tbl_customer.id=tbl_transaction.customerid left 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.donation=1 and $where1 and  
    tbl_transaction.volunteer>0
    
    
");




function array_msort($array, $cols)
{
    $colarr = [];

    foreach ($cols as $col => $order) {
        $colarr[$col] = [];

        foreach ($array as $k => $row) {
            $colarr[$col]['_' . $k] = strtolower($row[$col]);
        }
    }
    $eval = 'array_multisort(';

    foreach ($cols as $col => $order) {
        $eval .= '$colarr[\'' . $col . '\'],' . $order . ',';
    }
    $eval = substr($eval, 0, -1) . ');';
    eval($eval);
    $ret = [];

    foreach ($colarr as $col => $arr) {
        foreach ($arr as $k => $v) {
            $k = substr($k, 1);

            if (!isset($ret[$k])) {
                $ret[$k] = $array[$k];
            }
            $ret[$k][$col] = $array[$k][$col];
        }
    }

    return $ret;
}
$data = array_msort($data, ['id' => SORT_DESC]);

$data = array_values($data);




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

        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';
            }
        }


        $lineData = [
                        $row['name'],
                        $row['Mobile'],
                        $address,
                        date('d-m-Y', strtotime($row['ordrddate'])),
                        $row['assembly'],
                        $row['panchayat_name'],
                         $row['ward_name'],
                        $row['Amount'],
                        $row['Received'],
                         $row['orderid'] ? $row['orderid'] : 'NA',
                        $row['Transid'] ? $row['Transid'] : 'NA',
                        ];
        $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;
