<?php

require("includes/check_auth_user.php");
require("includes/dbconnect.php");
include ('includes/function.php');

//$keycond = " AND `series`='$zseries' ";
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

$zroot = $_SESSION["zdbroot"];

$success = false;

$acc = 'actran';
$bank = 'bankrecp';
$cust = 'accmast';

$ztit = $_SESSION["tit_" . $dbname];
$usertype = $_SESSION["usertype_" . $dbname];
$accdb = $acc ;
$maindb = $bank;
$custdb = $cust;
$billdb = $bild;

//$trandb = 'estitran';
$file = 'bankrecp.php';

$keyfld = "vouchno";

$rangeFrom = $_REQUEST["rangeFrom"];
$rangeTo = $_REQUEST["rangeTo"];
$searchTerm = $_REQUEST["ssearch"];
$operator = $_REQUEST["soperator"];
$mpara = $_REQUEST["para"];



$cond = "";
if ($operator == "=") {
    if ($_REQUEST["sfield"] == "1") {
        $cond = "AND `vouchno` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "2") {
        $cond = "AND `mydt` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "3") {
        $cond = "AND `custname` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "4") {
        $cond = "AND `amount` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "5") {
        $cond = "AND `drawbank` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "6") {
        $cond = "AND `stafname` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "7") {
        $cond = "AND `amount` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "8") {
        $cond = "AND `compname` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "9") {
        $cond = "AND `chqno` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "10") {
        $cond = "AND `chqdt` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "11") {
        $cond = "AND `clrdt` = '$searchTerm'";
    } else if ($_REQUEST["sfield"] == "12") {
        $cond = "AND `retdt` = '$searchTerm'";
    }
} else {
    if ($_REQUEST["sfield"] == "1") {
        $cond = "AND `vouchno` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "2") {
        $cond = "AND `mydt` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "3") {
        $cond = "AND `custname` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "4") {
        $cond = "AND `amount` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "5") {
        $cond = "AND `drawbank` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "6") {
        $cond = "AND `stafname` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "7") {
        $cond = "AND `amount` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "8") {
        $cond = "AND `compname` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "9") {
        $cond = "AND `chqno` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "10") {
        $cond = "AND `chqdt` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "11") {
        $cond = "AND `clrdt` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["sfield"] == "12") {
        $cond = "AND `retdt` LIKE '%$searchTerm%'";
    }
}


$sortcond = "ORDER BY `vouchno`";

if ($_REQUEST["sortBy"] == "1") {
    $sortcond = " ORDER BY `vouchno`";
} else if ($_REQUEST["sortBy"] == "2") {
    $sortcond = " ORDER BY `mydt`";
} else if ($_REQUEST["sortBy"] == "3") {
    $sortcond = " ORDER BY `custname`";
} else if ($_REQUEST["sortBy"] == "4") {
    $sortcond = " ORDER BY `amount`";
} else if ($_REQUEST["sortBy"] == "5") {
    $sortcond = " ORDER BY `drawbank`";
} else if ($_REQUEST["sortBy"] == "6") {
    $sortcond = " ORDER BY `stafname`";
} else if ($_REQUEST["sortBy"] == "7") {
    $sortcond = " ORDER BY `amount`";
} else if ($_REQUEST["sortBy"] == "8") {
    $sortcond = " ORDER BY `compname`";
} else if ($_REQUEST["sortBy"] == "9") {
    $sortcond = " ORDER BY `chqno`";
} else if ($_REQUEST["sortBy"] == "10") {
    $sortcond = " ORDER BY `chqdt`";
} else if ($_REQUEST["sortBy"] == "11") {
    $sortcond = " ORDER BY `clrdt`";
} else if ($_REQUEST["sortBy"] == "12") {
    $sortcond = " ORDER BY `retdt`";
}


$ordcond = "DESC";
if ($_REQUEST["orderAs"] == "desc") {
    $ordcond = " DESC";
} else {
    $ordcond = " ASC";
}
//mysqli_query($conn, "Create temporary table `banksearch` SELECT a.*, date_format(a.vouchdt,'%d-%m-%Y') as mydt,
//                date_format(a.cleardt,'%d-%m-%Y') as clrdt ,date_format(a.chqdate,'%d-%m-%Y') as chqdt,
//                date_format(a.recvdate,'%d-%m-%Y') as recvdt ,date_format(a.retdate,'%d-%m-%Y') as retdt,
//                b.name as bank, c.compname, d.name as stafname, e.name as custname  FROM `$maindb` a, `$custdb` b, company c, `$custdb` d, `$custdb` e 
//                where a.bankname=b.accode and a.company=c.code and a.staff=d.accode and a.party=e.accode")or die(mysqli_error($conn));
mysqli_query($conn, "Create temporary table `banksearch` SELECT a.*, date_format(a.vouchdt,'%d-%m-%Y') as mydt,
                date_format(a.cleardt,'%d-%m-%Y') as clrdt ,date_format(a.chqdate,'%d-%m-%Y') as chqdt,
                date_format(a.recvdate,'%d-%m-%Y') as recvdt ,date_format(a.retdate,'%d-%m-%Y') as retdt,
                 d.name as bname, e.name as custname  FROM `$maindb` a, `$custdb` d, `$custdb` e 
                where  a.bankname=d.accode and a.party=e.accode")or die(mysqli_error($conn));

$srchresult = mysqli_query($conn, "SELECT * FROM `banksearch` where $keyfld != '' $cond $sortcond $ordcond   LIMIT $rangeFrom, $rangeTo ")or die(mysqli_error($conn));

$status = '';
$ctr = 0;
while ($r = mysqli_fetch_assoc($srchresult)) {
    $retdat = $r['retdate'];
    $postch = $r['postchq'];
//    $getclr = mysqli_query($conn, "SELECT `clrdate` FROM `$accdb` WHERE `vouchno` = '$row[vouchno]' and `chqno`='$row[chqno]' and `source`='BR'");
//    $fetclr = mysqli_fetch_array($getclr);
    $clrdate = $r['cleardt'];
    $style = '';
    $status = '';
    if ($postch != '1') {
        $status = 'Pending';
    }
    if ($retdat != '0000-00-00') {
        $status = 'Cancelled';
        $style = 'color:red';
    }

    if ($clrdate != '0000-00-00') {
        $status = 'Cleared';
        $style = 'color:green';
    }


    $rows[] = ['success' => true,
        'value' => $ctr,
        'vouchno' => $r['vouchno'],
        'mydt' => $r['mydt'],
        'recvdt' => $r['recvdt'],
        'recpno' => $r['recpno'],
        'custname' => $r['custname'],
        'bname' => $r['bname'],
        'stafname' => $r['stafname'],
        'amount' => $r['amount'],
        'compname' => $r['compname'],
        'chqno' => $r['chqno'],
        'chqdt' => $r['chqdt'],
        'clrdt' => $r['clrdt'],
        'retdt' => $r['retdt'],
        'status' => $status,
        'style' => $style,
        'action' => "$file?mode=edit&vouchno=$r[vouchno]",
    ];
    $ctr++;
}
//browse($rows);
header("Content-type: application\json");
echo json_encode($rows);
