<?php
ob_start();
session_start();
include 'connect.php';

if (isset($_SESSION['adminemail'])) {
    $user = $_SESSION['adminemail'];
    $gtp = mysqli_query($conn, "SELECT * FROM admin WHERE username = '$user'");
    $gtp1 = mysqli_fetch_assoc($gtp);
    include 'top.php';
?>

<div id="page-wrapper">
    <div id="page-inner">
        <div class="main-content">
            <div class="main-content-inner">
                <div class="breadcrumbs ace-save-state" id="breadcrumbs">
                    <ul class="breadcrumb">
                        <li>
                            <i class="ace-icon fa fa-home home-icon"></i>
                            <a href="#">Home</a>
                        </li>
                        <li class="active">Outstandings</li>
                    </ul><!-- /.breadcrumb -->
                </div>
                <div class="page-content">
                    <div class="page-header">
                        <?php
                        $fi = mysqli_query($conn, "SELECT * FROM financial WHERE user = '$user' ORDER BY ID DESC");
                        $fi1 = mysqli_fetch_assoc($fi);
                        $financial = $fi1['financial'];
                        ?>
                        <table style="width:100%;">
                            <tr>
                                <td style="width:50%;">
                                    <h1>Outstandings</h1>
                                </td>
                                <td style="width:50%; text-align:right; padding-right:15px; font-size:15px;">
                                    <i class="fa fa-calendar"></i>&nbsp; <?php echo $financial; ?>
                                </td>
                            </tr>
                        </table>
                    </div><!-- /.page-header -->
                    <br />
                    <div class="row">
                        <div class="col-xs-12">
                            <!-- PAGE CONTENT BEGINS -->
                            <script src="assets/js/jquery.js"></script>
                            <?php
                            if (isset($_GET['type'])) {
                                $type = $_GET['type'];
                                if ($type == 'payables') {
                                    echo "<table class='table table-bordered table-condensed' style='width:75%; margin:auto;'>
                                            <thead>
                                                <tr>
                                                    <th style='width:70%;'>Supplier</th>
                                                    <th style='width:30%;'>Amount</th>
                                                </tr>
                                            </thead>";
                                    $tpy = 0;
                                    $sup = mysqli_query($conn, "SELECT * FROM supplier ORDER BY name ASC");
                                    while ($sup1 = mysqli_fetch_assoc($sup)) {
                                        //payables
                                        $pur = mysqli_query($conn, "SELECT SUM(total) AS debit FROM purchase WHERE supplier = '$sup1[ID]' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        $pur1 = mysqli_fetch_assoc($pur);
                                        $pym = mysqli_query($conn, "SELECT SUM(amount) AS credit FROM payment WHERE supplier = '$sup1[ID]' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        $pym1 = mysqli_fetch_assoc($pym);
                                        $sopcr = mysqli_query($conn, "SELECT SUM(amount) AS credit FROM supplier_ledger WHERE supplier = '$sup1[ID]' AND paymenttype = 'credit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        if ($sopcr) {
                                            $sopcr1 = mysqli_fetch_assoc($sopcr);
                                            $sopcr_amount = $sopcr1['credit'];
                                        } else {
                                            $sopcr_amount = 0;
                                        }
                                        $sopdb = mysqli_query($conn, "SELECT SUM(amount) AS debit FROM supplier_ledger WHERE supplier = '$sup1[ID]' AND paymenttype = 'debit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        if ($sopdb) {
                                            $sopdb1 = mysqli_fetch_assoc($sopdb);
                                            $sopdb_amount = $sopdb1['debit'];
                                        } else {
                                            $sopdb_amount = 0;
                                        }
                                        $payable = $pur1['debit'] - $pym1['credit'] - $sopcr_amount + $sopdb_amount;
                                        if ($payable != 0) {
                                            echo "<tr><td>".$sup1['name']."</td><td style='text-align:right;'>".number_format($payable,2,'.','')."</td></tr>";
                                            $tpy += $payable;
                                        }
                                    }
                                    echo "<tr><td style='text-align:right;'></td><td style='text-align:right;'>".number_format($tpy,2,'.','')."</td></tr></table>";
                                } elseif ($type == 'receivables') {
                                    echo "<table class='table table-bordered table-condensed' style='width:75%; margin:auto;'>
                                            <thead>
                                                <tr>
                                                    <th style='width:70%;'>Customer</th>
                                                    <th style='width:30%;'>Amount</th>
                                                </tr>
                                            </thead>";
                                    $trc = 0;
                                    $sup = mysqli_query($conn, "SELECT * FROM customer ORDER BY name ASC");
                                    while ($sup1 = mysqli_fetch_assoc($sup)) {
                                        //receivables
                                        $stl = 0;
                                        $sal = mysqli_query($conn, "SELECT SUM(total) AS credit FROM invoice WHERE customer = '$sup1[ID]' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]' GROUP BY invoiceno");
                                        while ($sal1 = mysqli_fetch_assoc($sal)) {
                                            $stl += $sal1['credit'];
                                        }
                                        $rec = mysqli_query($conn, "SELECT SUM(amount) AS debit FROM receipt WHERE customer = '$sup1[ID]' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        $rec1 = mysqli_fetch_assoc($rec);
                                        $copcr = mysqli_query($conn, "SELECT SUM(amount) AS credit FROM debitor_ledger WHERE customer = '$sup1[ID]' AND paymenttype = 'credit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        $copcr1 = mysqli_fetch_assoc($copcr);
                                        $copdb = mysqli_query($conn, "SELECT SUM(amount) AS debit FROM debitor_ledger WHERE customer = '$sup1[ID]' AND paymenttype = 'debit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                        $copdb1 = mysqli_fetch_assoc($copdb);
                                        $receivable = $stl - $rec1['debit'] + $copcr1['credit'] - $copdb1['debit'];
                                        if ($receivable != 0) {
                                            echo "<tr><td>".$sup1['name']."</td><td style='text-align:right;'>".number_format($receivable,2,'.','')."</td></tr>";
                                            $trc += $receivable;
                                        }
                                    }
                                    echo "<tr><td style='text-align:right;'></td><td style='text-align:right;'>".number_format($trc,2,'.','')."</td></tr></table>";
                                }
                                echo "<br /><br /><center><a href='outstandings.php'>Back</a></center><br />";
                            } else {
                                //payables
                                $pur = mysqli_query($conn, "SELECT SUM(total) AS debit FROM purchase WHERE date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                $pur1 = mysqli_fetch_assoc($pur);
                                $pym = mysqli_query($conn, "SELECT SUM(amount) AS credit FROM payment WHERE date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                $pym1 = mysqli_fetch_assoc($pym);
                                $sopcr = mysqli_query($conn, "SELECT SUM(amount) AS credit FROM creditor_ledger WHERE paymenttype = 'credit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                if ($sopcr) {
                                    $sopcr1 = mysqli_fetch_assoc($sopcr);
                                    $sopcr_amount = $sopcr1['credit'];
                                } else {
                                    $sopcr_amount = 0;
                                }
                                $sopdb = mysqli_query($conn, "SELECT SUM(amount) AS debit FROM creditor_ledger WHERE paymenttype = 'debit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                if ($sopdb) {
                                    $sopdb1 = mysqli_fetch_assoc($sopdb);
                                    $sopdb_amount = $sopdb1['debit'];
                                } else {
                                    $sopdb_amount = 0;
                                }
                                $payable = $pur1['debit'] - $pym1['credit'] - $sopcr_amount + $sopdb_amount;
                                //receivables
                                $stl = 0;
                                $sal = mysqli_query($conn, "SELECT SUM(total) AS credit FROM invoice WHERE date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]' GROUP BY invoiceno");
                                while ($sal1 = mysqli_fetch_assoc($sal)) {
                                    $stl += $sal1['credit'];
                                }
                                $rec = mysqli_query($conn, "SELECT SUM(amount) AS debit FROM receipt WHERE date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                $rec1 = mysqli_fetch_assoc($rec);
                                $copcr = mysqli_query($conn, "SELECT SUM(amount) AS credit FROM debitor_ledger WHERE paymenttype = 'credit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                $copcr1 = mysqli_fetch_assoc($copcr);
                                $copdb = mysqli_query($conn, "SELECT SUM(amount) AS debit FROM debitor_ledger WHERE paymenttype = 'debit' AND date BETWEEN '$fi1[fromdate]' AND '$fi1[todate]'");
                                $copdb1 = mysqli_fetch_assoc($copdb);
                                $receivable = $stl - $rec1['debit'] + $copcr1['credit'] - $copdb1['debit'];
                                echo "<br /><table class='table table-bordered table-condensed' style='width:75%; margin:auto;'>
                                        <thead>
                                            <tr>
                                                <th>Description</th>
                                                <th>Amount</th>
                                            </tr>
                                        </thead>
                                        <tr>
                                            <td><a href='outstandings.php?type=payables'>Payables</a></td>
                                            <td style='text-align:right;'>".number_format($payable,2,'.','')."</td>
                                        </tr>
                                        <tr>
                                            <td><a href='outstandings.php?type=receivables'>Receivables</a></td>
                                            <td style='text-align:right;'>".number_format($receivable,2,'.','')."</td>
                                        </tr>
                                    </table>
                                    <br /><br />";
                            }
                            ?>
                            <br>
                            <br>
                            <!-- PAGE CONTENT ENDS -->
                        </div><!-- /.col -->
                    </div><!-- /.row -->
                </div><!-- /.page-content -->
            </div>
        </div><!-- /.main-content -->
    </div><!-- /.main-content -->
</div><!-- /.main-content -->
<?php
    include 'bottom.php';
} else {
    header("location:login.php");
}
ob_flush();
?>
