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

$ward      = $_REQUEST['ward'];
$panchayat = $_REQUEST['panchayat'];
$assembly  = $_REQUEST['assembly'];

$where = 'tbl_transaction.id>0 and tbl_transaction.clubid=0 ';







if ($ward > 0) {
    $where .= " and tbl_ward.id=$ward ";
}

if ($panchayat > 0) {
    $where .= " and tbl_panchayat.id=$panchayat";
}

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

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



    $data = findQuery("SELECT 
    tbl_ward.ward_name AS Ward,
    tbl_panchayat.panchayat_name AS Panchayat,
    tbl_assembly.name AS Assembly,
    IFNULL(SUM(tbl_transactiondetails.Totqty), 0) AS qty,
    tbl_ward.id AS id,
    tbl_panchayat.id AS panid,
    IFNULL(SUM(tbl_transactiondetails.Amount), 0) AS Received
FROM tbl_transaction
LEFT 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_assembly ON tbl_assembly.id = tbl_customer.Assembly
INNER JOIN tbl_panchayat ON tbl_panchayat.id = tbl_customer.Panchayat
INNER JOIN tbl_district ON tbl_district.id = tbl_customer.District
WHERE tbl_transaction.challengeid = $idch
  AND tbl_transaction.donation = 0
  AND tbl_transaction.sponsorship = 0
  AND tbl_transaction.Received > 0
  AND tbl_transaction.status = 0
  AND tbl_transaction.Amount > 0
 AND $where
GROUP BY 
    tbl_ward.ward_name,
    tbl_panchayat.panchayat_name,
    tbl_assembly.name,
    tbl_ward.id,
    tbl_panchayat.id
ORDER BY Received DESC
");




    $datasum = findQuery("Select 
   ifnull(sum(Received),0) Received
    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_assembly ON tbl_assembly.id = tbl_customer.Assembly
INNER JOIN tbl_panchayat ON tbl_panchayat.id = tbl_customer.Panchayat
INNER JOIN tbl_district ON tbl_district.id = tbl_customer.District
WHERE tbl_transaction.challengeid = $idch
  AND tbl_transaction.donation = 0
  AND tbl_transaction.sponsorship = 0
  AND tbl_transaction.Received > 0
  AND tbl_transaction.status = 0
  AND tbl_transaction.Amount > 0
 AND $where ");
    $totalReceived = 0;
    $totalReceived = $datasum[0]['Received'];







    $Totqty = 0;





    if ($data) {
        $message = 'true';
        for ($i = 0;$i < count($data);$i++) {
            $Totqty += $data[$i]['qty'];


            $data2[] = [



'ward'        => $data[$i]['Ward'],
'panchayat'   => $data[$i]['Panchayat'],
'assembly'    => $data[$i]['Assembly'],
'wardid'      => $data[$i]['id'],
'panchayatid' => $data[$i]['panid'],
'amount'      => $data[$i]['Received'],
'quantity'    => $data[$i]['qty'],


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




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

return;

?>

