芝麻web文件管理V1.00

编辑当前文件:/home/sditechnicalteam/socialsolargreens.pk/controller/PayRecController.php

<?php

include 'partial/db.php';
global $dbobj;
class PayRecController extends db {

public function Search($StDate='' ,$EnDate='')
        {
            if (empty($StDate) && empty($EnDate)) 
            {
                $StDate = date('Y-m-d');
                $EnDate = date('Y-m-d');
            }

$opening_bal_query = "SELECT AccountNo, AccountName, rec1 + rec2 AS Receivable, pay1 + pay2 AS Payable FROM(SELECT AccountNo, AccountTitle AS AccountName, Rec, Pay, CASE WHEN HeadId = 1 AND rec >= 0 THEN Rec ELSE 0 END AS rec1, CASE WHEN rec < 0 THEN Rec * - 1 ELSE 0 END AS pay1, CASE WHEN HeadId = 2 AND pay < 0 THEN pay * - 1 ELSE 0 END AS rec2, CASE WHEN pay >= 0 THEN pay ELSE 0 END AS pay2 FROM (SELECT Headid, AccountNo, AccountTitle, Dr + drr AS sumdr, Cr + crr AS sumcr, CASE WHEN HeadId = 1 THEN dr + drr - cr - crr ELSE 0 END AS Rec, CASE WHEN HeadId = 2 THEN cr + crr - dr - drr ELSE 0 END AS Pay FROM (SELECT AccountNo, Headid, AccountTitle, Cr, Dr, CASE WHEN HeadId = 1 THEN (SELECT IFNULL(SUM(Dr) - SUM(Cr), 0) AS Expr1 FROM TranscationDetails WHERE (AccountNo = ThirdLevel.AccountNo) AND (TransDate between '".$StDate."' AND '".$EnDate."')) ELSE 0 END AS drr, CASE WHEN HeadId = 2 THEN (SELECT IFNULL(SUM(cr) - SUM(dr), 0) AS Expr1 FROM TranscationDetails WHERE (AccountNo = ThirdLevel.AccountNo) AND (TransDate between '2021-01-01' AND '2022-01-01')) ELSE 0 END AS crr FROM ThirdLevel WHERE (Headid IN (1, 2))) AS derivedtbl_1) AS derivedtbl_2 ORDER BY AccountNo LIMIT 100) AS derivedtbl_3";
            if ($returndata = mysqli_query($this->con, $opening_bal_query)) {
                $return['StDate'] = $StDate;
                $return['EnDate'] = $EnDate;
                if(mysqli_num_rows($returndata) > 0){
                    while ($row = mysqli_fetch_assoc($returndata)) {
                        $return['data'][] = $row;
                    }
                }else{
                    $return[] = "Record not Found";
                }
                return $return;
                die;
            }   else{
                return "Error Code [246] : " . mysqli_error($this->con);
                die;
            }
        }
}