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

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

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

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








$data = findQuery("SELECT 
    tbl_transaction.id,
    tbl_transaction.challengeid,
    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.Transid
    ,tbl_transaction.dateadded ordrddate,
      tbl_transaction.clubid
     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 $where and tbl_transaction.challengeid=$chlngid and  tbl_transaction.volunteer>0
    
    union all
    
    SELECT 
    tbl_transaction.id,
      tbl_transaction.challengeid,
    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.Transid
    ,tbl_transaction.dateadded ordrddate,
      tbl_transaction.clubid
     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 $where and tbl_transaction.challengeid=$chlngid and  tbl_transaction.volunteer=0 and tbl_transaction.Received>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) {
        $clubbid   = $row['clubid'];
        $dataaclub = findQuery("SELECT * FROM tbl_club WHERE id=$clubbid");
        $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';
            }
        }


        $Ordrdqty1 = 0;

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

        if ($dataprod) {
            $datae = findQuery("select * from  `tbl_challenge` where id=$chlngid");


            if ($datae) {
                $prod1 = 0;
                $prod2 = 0;
                $prod3 = 0;

                $qty1 = 0;
                $qty2 = 0;
                $qty3 = 0;

                $product1  = '';
                $product2  = '';
                $product3  = '';
                $Ordrdqty1 = 0;
                $Ordrdqty2 = 0;
                $Ordrdqty3 = 0;


                if ($dataprod[0]['item']) {
                    if ($dataprod[0]['item'] == 1) {
                        $prod1 = 1;
                        $qty1  = $dataprod[0]['qty'];
                    } elseif ($dataprod[0]['item'] == 2) {
                        $prod2 = 2;
                        $qty2  = $dataprod[0]['qty'];
                    } else {
                        $prod3 = 3;
                        $qty3  = $dataprod[0]['qty'];
                    }
                }

                if ($dataprod[1]['item']) {
                    if ($dataprod[1]['item'] == 1) {
                        $prod1 = 1;
                        $qty1  = $dataprod[1]['qty'];
                    } elseif ($dataprod[1]['item'] == 2) {
                        $prod2 = 2;
                        $qty2  = $dataprod[1]['qty'];
                    } else {
                        $prod3 = 3;
                        $qty3  = $dataprod[1]['qty'];
                    }
                }

                if ($dataprod[2]['item']) {
                    if ($dataprod[2]['item'] == 1) {
                        $prod1 = 1;
                        $qty1  = $dataprod[2]['qty'];
                    } elseif ($dataprod[2]['item'] == 2) {
                        $prod2 = 2;
                        $qty2  = $dataprod[2]['qty'];
                    } else {
                        $prod3 = 3;
                        $qty3  = $dataprod[2]['qty'];
                    }
                }


                if ($prod1 == 1) {
                    $product1  = $datae[0]['product_name1'];
                    $Ordrdqty1 = $qty1;
                }

                if ($prod2 == 2) {
                    $product2  = $datae[0]['product_name2'];
                    $Ordrdqty2 = $qty2;
                }

                if ($prod3 == 3) {
                    $product3  = $datae[0]['product_name3'];
                    $Ordrdqty3 = $qty3;
                }
            }
        }



        $lineData = [
                        $row['name'],
                        $row['Mobile'],
                        $address,
                        date('d-m-Y', strtotime($row['ordrddate'])),
                        $row['assembly'],
                        $row['panchayat_name'],
                         $row['ward_name'],
                         $dataaclub[0]['club_name'] ? $dataaclub[0]['club_name'] : 'NA',
                         $product1,
                         $Ordrdqty1,

                        $row['Amount'],
                        $row['Received'],
                        $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;
