<?php
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, GET, OPTIONS');
header('Access-Control-Max-Age: 1000');
header('Access-Control-Allow-Headers: Content-Type');
require_once('../lib/dbclass.php');


$datas = findQuery('SELECT * FROM tbl_challenge WHERE status=0 AND CURDATE() between challenge_startdate and challenge_enddate');

if (!$datas) {
    $datas = findQuery('SELECT * FROM tbl_challenge WHERE status=0 order by id desc limit 1');
}

$name      = $_REQUEST['name'];
$club      = $_REQUEST['club'];
$volunteer = $_REQUEST['volunteer'];
$ward      = $_REQUEST['ward'];
$district  = $_REQUEST['district'];
$ward      = $_REQUEST['ward'];
$assembly  = $_REQUEST['assembly'];
$panchayat = $_REQUEST['panchayat'];
$uniquedid = $_REQUEST['uniquedid'];
$where     = 'tbl_transaction.id>0 and tbl_transaction.sponsorship=0 and tbl_transaction.clubid>0';

if ($uniquedid == '') {
    $uniquedid = '0';
}

$Totrecev = 0;
$Totqty   = 0;

if ($name != '') {
    $where .= " and (tbl_customer.name like '%$name%' or tbl_customer.Mobile like '%$name%') ";
}


if ($ward > 0) {
    $where .= " and tbl_customer.Ward=$ward ";
}

if ($club > 0) {
    $where .= " and tbl_transaction.clubid=$club";
}

if ($district > 0) {
    $where .= " and tbl_customer.District=$district";
}

if ($ward > 0) {
    //$where.=" and tbl_customer.Ward=$ward";
}

if ($assembly > 0) {
    $where .= " and tbl_customer.Assembly=$assembly";
}

if ($panchayat > 0) {
    $where .= " and tbl_customer.Panchayat=$panchayat";
}

if ($datas) {
    $id = $datas[0]['id'];



    $data = findQuery("SELECT tbl_transaction.hideidendity,clubid,tbl_transaction.uniquedid,
    tbl_transaction.volunteer,
    tbl_customer.id customerid,
    tbl_transaction.id,
    DATE_FORMAT(tbl_transaction.dateadded,'%H:%i:%s')orderedtime,
    tbl_customer.name
    ,tbl_ward.ward_name
    ,tbl_panchayat.panchayat_name
    ,tbl_assembly.name assembly
    ,tbl_panchayat.id panchayatid,
    tbl_district.district_name
    ,tbl_transaction.Received
    ,MONTHNAME(tbl_transaction.dateadded) month
    ,day(tbl_transaction.dateadded) day,
    tbl_transaction.dateadded
     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.challengeid=$id  and  tbl_transaction.volunteer>0 and tbl_transaction.Received>0 and $where
    
    union all
    
    SELECT tbl_transaction.hideidendity,clubid,tbl_transaction.uniquedid,
    tbl_transaction.volunteer,
    tbl_customer.id customerid,
    tbl_transaction.id,
    DATE_FORMAT(tbl_transaction.dateadded,'%H:%i:%s')orderedtime,
    tbl_customer.name
    ,tbl_ward.ward_name
    ,tbl_panchayat.panchayat_name
    ,tbl_assembly.name assembly
    ,tbl_panchayat.id panchayatid,
    tbl_district.district_name
    ,tbl_transaction.Received
    ,MONTHNAME(tbl_transaction.dateadded) month
    ,day(tbl_transaction.dateadded) day,tbl_transaction.dateadded
     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.challengeid=$id  and  tbl_transaction.volunteer=0 and tbl_transaction.Received>0 and $where
    
    
    
    
    
   
    
   ");


    $dataqty = findQuery("SELECT sum(tbl_transactiondetails.qty)qty
     from tbl_transaction inner join tbl_transactiondetails on tbl_transaction.id=tbl_transactiondetails.hdrid
     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.challengeid=$id  and  tbl_transaction.volunteer>0 and tbl_transaction.Received>0 and $where
    
    union all
    
    SELECT sum(tbl_transactiondetails.qty)qty
     from tbl_transaction inner join tbl_transactiondetails on tbl_transaction.id=tbl_transactiondetails.hdrid
     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.challengeid=$id  and  tbl_transaction.volunteer=0 and tbl_transaction.Received>0 and $where
    
    
    
    
    
   
    
   ");






    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, ['dateadded' => SORT_DESC,'id' => SORT_DESC]);

    $data = array_values($data);

    $totalReceived = 0;
    $Totqty        = 0;

    foreach ($data as $entry) {
        $totalReceived += $entry['Received'];
    }

    foreach ($dataqty as $entrys) {
        $Totqty += $entrys['qty'];
    }
    $data = array_slice($data, 0, 200);
    $data = array_values($data);

    if ($data) {
        $message = 'true';
        for ($i = 0;$i < count($data);$i++) {
            //$Totrecev+=$data[$i]['Received'];
            $fullypaid = 0;

            if ($data[$i]['Received'] > 0) {
                $fullypaid = 1;
            }

            $volunteerthrough = 0;


            if ($volunteer > 0) {
                if ($data[$i]['volunteer'] == $volunteer) {
                    $volunteerthrough = 1;
                }
            }

            $canchangeward = 0;

            if ($data[$i]['uniquedid'] == $uniquedid) {
                $canchangeward = 1;
            }

            if ($data[$i]['hideidendity'] == 1) {
                $data[$i]['name'] = 'xxxxxxxxxx';
            }

            $clubid = $data[$i]['clubid'];

            if ($clubid > 0) {
                $club     = findQuery("SELECT * from tbl_club where id=$clubid");
                $name     = $data[$i]['name'];
                $clubname = $club[0]['club_name'];
            } else {
                $name     = $data[$i]['name'];
                $clubname = '';
            }
            //$Totrecev+=$data[$i]['Received'];
            $id       = $data[$i]['id'];
            $dataprod = findQuery("SELECT tbl_transactiondetails.qty
													FROM `tbl_transaction` inner join tbl_transactiondetails on tbl_transaction.id=tbl_transactiondetails.hdrid 
													where tbl_transactiondetails.hdrid=$id");
            $quantity = 0;

            if ($dataprod) {
                $quantity = $dataprod[0]['qty'];
            }
            //$Totqty+=$quantity;


            $data2[] = [


'name'          => $name,
'ward'          => $data[$i]['ward_name'],
'panchayat'     => $data[$i]['panchayat_name'],
'assembly'      => $data[$i]['assembly'],
'district'      => $data[$i]['district_name'],
'amount'        => $data[$i]['Received'],
'month'         => $data[$i]['month'],
'day'           => $data[$i]['day'],
'time'          => $data[$i]['orderedtime'],
'id'            => $data[$i]['id'],
'fullypaid'     => $fullypaid,
'panchayatid'   => $data[$i]['panchayatid'],
'customerid'    => $data[$i]['customerid'],
'caneditorder'  => $volunteerthrough,
'canchangeward' => $canchangeward,
'receiptname'   => $data[$i]['name'],
'club'          => $clubname,

];
        }
    }
} else {
    $message = 'false';
    $data2[] = ['error' => 'Not Found'];
}



echo json_encode(['Status' => $message, 'data' => $data2,'datareceived' => $totalReceived,'dataqty' => $Totqty]);

return;

?>

