<?php
session_start();
require_once '../utils/errorhandler.php';
require_once '../utils/response.php';
require_once '../model/dbclass.php';
require_once '../model/model.php';
require_once '../model/transaction.php';
require_once '../model/user.php';
require_once '../model/customer.php';
require_once '../model/glaccount.php';
require_once '../model/administration.php';
require_once '../model/organisationinfo.php';

date_default_timezone_set('Africa/Lagos');
if ($_SESSION['wuseremail'] === null || !isset($_SESSION['wuseremail'])) {
     exit(badRequest(204,'Invalid session data. Proceed to login'));
}

$glaccount = new GLAccount();
$transaction = new Transaction();
$organisationinfo = new OrganisationInfo();
$resorganisation = $organisationinfo->getOrganisationInfo("1");


$asat = (isset($_POST["currentdate"]) && $_POST["currentdate"] !== '') ? $_POST["currentdate"] : date('Y-m-d');
$location = (isset($_POST["location"]) && $_POST["location"] > 0) ? $_POST["location"] : $_SESSION["location_id"];
//$year = (isset($_POST["year"]) && $_POST["year"] > 0) ? $_POST["year"] : date('Y');
//$month = (isset($_POST["month"]) && $_POST["month"] > 0) ? $_POST["month"] : date('m');
//$endday = cal_days_in_month(CAL_GREGORIAN, $month, $year); 	

//$startdate = $year . "-" . $month . "-01";
//$enddate = $year . "-" . $month . "-" . $endday;

$assetarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'ASSET' ORDER BY description ASC");
if($resultgls){
    $assetstotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $assetstotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $assetarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "assettotal" => $assetstotal
            );
    }
}

$casharray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'CASH' ORDER BY description ASC");
if($resultgls){
    $cashtotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $cashtotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $casharray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "cashtotal" => $cashtotal
            );
    }
}

$fixedassetarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'FIXED ASSET' ORDER BY description ASC");
if($resultgls){
    $fixedassettotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $fixedassettotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $fixedassetarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "fixedassettotal" => $fixedassettotal
            );
    }
}

$currentassetarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'CURRENT ASSETS' ORDER BY description ASC");
if($resultgls){
    $currentassettotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $currentassettotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $currentassetarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "currentassettotal" => $currentassettotal
            );
    }
}

$othercurrentassetarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'OTHER CURRENT ASSET' ORDER BY description ASC");
if($resultgls){
    $othercurrentassettotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $othercurrentassettotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $othercurrentassetarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "othercurrentassettotal" => $othercurrentassettotal
            );
    }
}

$otherassetarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'OTHER ASSET' ORDER BY description ASC");
if($resultgls){
    $otherassettotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $otherassettotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $otherassetarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "otherassettotal" => $otherassettotal
            );
    }
}

$inventoryarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'INVENTORY' ORDER BY description ASC");
if($resultgls){
    $inventorytotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $inventorytotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $inventoryarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "inventorytotal" => $inventorytotal
            );
    }
}

$payablearray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'ACCOUNTS PAYABLE' ORDER BY description ASC");
if($resultgls){
    $payabletotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $payabletotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $payablearray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "payabletotal" => $payabletotal
            );
    }
}

$depreciationarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'ACCUMULATED DEPRECIATION' ORDER BY description ASC");
if($resultgls){
    $depreciationtotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $depreciationtotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $depreciationarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "depreciationtotal" => $depreciationtotal
            );
    }
}

$expensetotal = 0;
$expensearray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'EXPENSE' ORDER BY description ASC");
if($resultgls){
    
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["debit"] - $resulttrans[0]["credit"]),2);
            $expensetotal += round($bal,2);
            if($bal >= 0){
                $debit = $bal;
            }else{
                $credit = -1 * $bal;
            }
        }
        $expensearray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "expensetotal" => $expensetotal
            );
    }
}

//credits
$costofsalesarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'COST OF SALES' ORDER BY description ASC");
if($resultgls){
    $costofsalestotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $costofsalestotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $costofsalesarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "costofsalestotal" => $costofsalestotal
            );
    }
}


$receivablesarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'ACCOUNTS RECEIVABLE' ORDER BY description ASC");
if($resultgls){
    $receivablestotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $receivablestotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $receivablesarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "receivablestotal" => $receivablestotal
            );
    }
}

$liabilitiesarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'LIABILITIES' ORDER BY description ASC");
if($resultgls){
    $liabilitiestotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $liabilitiestotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $liabilitiesarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "liabilitiestotal" => $liabilitiestotal
            );
    }
}

$currentliabilitiesarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'OTHER CURRENT LIABILITIES' ORDER BY description ASC");
if($resultgls){
    $currentliabilitiestotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $currentliabilitiestotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $currentliabilitiesarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "othercurrentliabilitiestotal" => $currentliabilitiestotal
            );
    }
}

$longliabilitiesarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'LONG TERM LIABILITIES' ORDER BY description ASC");
if($resultgls){
    $longliabilitiestotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $longliabilitiestotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $longliabilitiesarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "longtermliabilitiestotal" => $longliabilitiestotal
            );
    }
}

$equityarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'EQUITY' ORDER BY description ASC");
if($resultgls){
    $equitytotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $equitytotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $equityarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "equity" => $equitytotal
            );
    }
}

$retainedequityarray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'EQUITY RETAINED EARNINGS' ORDER BY description ASC");
if($resultgls){
    $retainedequitytotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $retainedequitytotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $retainedequityarray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "equityretainedtotal" => $retainedequitytotal
            );
    }
}
 
$equitynotclosearray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'EQUITY DOES NOT CLOSE' ORDER BY description ASC");
if($resultgls){
    $equityclosestotal = 0;
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $equityclosestotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $equitynotclosearray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "equitydoesnotclosetotal" => $equityclosestotal
            );
    }
}   

$incomearray = array();
$resultgls = $glaccount->executeByQuerySelector("SELECT * FROM glaccount WHERE accounttype = 'INCOME' ORDER BY description ASC");
$incometotal = 0;
if($resultgls){
    
    foreach($resultgls as $resultgl){
        $accountname = $resultgl["description"];
        $debit = 0;
        $credit = 0;
        $resulttrans = $transaction->executeTransactionByQuerySelector("SELECT SUM(credit) AS credit, SUM(debit) AS debit FROM tdetail WHERE accountnumber = '" . $resultgl["accountnumber"] . "' AND DATE(transactiondate) <= '$asat'");
        if($resulttrans){
            $bal = round(($resulttrans[0]["credit"] - $resulttrans[0]["debit"]),2);
            $incometotal += round($bal,2);
            if($bal >= 0){
                $credit = $bal;
            }else{
                $debit = -1 * $bal;
            }
        }
        $incomearray[] = array(
            "accountname" => $accountname,
            "debit" => $debit,
            "credit" => $credit,
            "incometotal" => $incometotal
            );
    }
}   


$trialdata = array(
    "asset" => $assetarray,
    "cash" => $casharray,
    "fixedasset" => $fixedassetarray,
    "currentassets" => $currentassetarray,
    "otherasset" => $otherassetarray,
    "othercurrentasset" => $othercurrentassetarray,
    "inventory" => $inventoryarray,
    "accountspayable" => $payablearray,
    "accumulateddepreciation" => $depreciationarray,
    
    "costofsales" => $costofsalesarray,
    "liabilities" => $liabilitiesarray,
    "othercurrentliabilities" => $currentliabilitiesarray,
    "longtermliabilities" => $longliabilitiesarray,
    "accountsreceivable" => $receivablesarray,
    "equity" => $equityarray,
    "equityretainedearnings" => $retainedequityarray,
    "equitydoesnotclose" => $equitynotclosearray,
    
    "profit" => round(($expensetotal . $incometotal),2)
    );


if($trialdata){
    echo success($trialdata, 200, "Successful", "Successful");
}else{
    echo badRequest(204, "Some went wrong. Try again");
}

?>