<?php
include "include/db.php";
include "include/adminauth.php";
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;


function clean_input_data($cellVal)
{
    include "include/db.php";
    $cellVal = trim($cellVal);
    $cellVal = mysqli_real_escape_string($conn, $cellVal);
    $cellVal = strtolower($cellVal);
    $cellVal = str_replace(' ', '', $cellVal);
    $cellVal = str_replace('*', '', $cellVal);
    return $cellVal;
}

function clean_cell_input_data($cellVal1)
{
    include "include/db.php";
    $cellVal1 = trim($cellVal1);
    $cellVal1 = mysqli_real_escape_string($conn, $cellVal1);
    return $cellVal1;
}
if (isset($_REQUEST['mess'])) {
    $message = $_REQUEST['mess'];
}

if (isset($_REQUEST['upload_xl_client_pay'])) {
    $message = '';
    $uniqueId = time() . '-' . mt_rand();
    $temp = explode(".", $_FILES["fileToUpload"]["name"]);
    $pic_name =  $uniqueId . '.' . end($temp);
    $target_dir = getcwd() . DIRECTORY_SEPARATOR . "reports/";
    $target_file = $target_dir . $pic_name;
    $imageFileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));
    $time = date("Y-m-d H:i:s");

    if ($_FILES["fileToUpload"]["size"] > 2000000) {
        $message = $message . 'The file you uploaded is too large. File size should be less than 2 MB';
    } else {
        // Allow certain file formats
        if ($imageFileType != "xlsx" && $imageFileType != "xls") {
            $pic_name = '';
            $message = $message . 'The file you uploaded is not of xlsx/xls format. Please upload a XLSX/XLS file.';
        } else {
            if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {

                if ($imageFileType == "xlsx") {
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
                } elseif ($imageFileType == "xls") {
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
                }
                if ($reader) {
                    $reader->setReadDataOnly(true);
                    $spreadsheet = $reader->load($target_file);
                    $sheetData = $spreadsheet->getActiveSheet()->toArray();
                    $count = 0;
                    $insert_row = 0;
                    foreach ($sheetData as $row) {
                        // get columns
                        if ($count == 0) {

                            $setVal = 0;

                            $set = 0;
                            for ($i = 0; $i < 20; $i++) {
                                if (!isset($row[$i])) {
                                    $row[$i] = " ";
                                }

                                $cellVal = clean_input_data($row[$i]);


                                if ($cellVal == 'workordernumber') {

                                    $workordernumberRow = $i;
                                }
                                if ($cellVal == 'invoicenumber') {

                                    $invoicenumberRow = $i;
                                }
                                if ($cellVal == 'loannumber') {

                                    $loannumberRow = $i;
                                    $set++;
                                }

                                if ($cellVal == 'clientpayamount') {

                                    $invoiceamountRow = $i;
                                    $set++;
                                }

                                if ($cellVal == 'payreference') {
                                    $payrefRow = $i;
                                    $set++;
                                }

                                if ($cellVal == 'invoicedate') {
                                    $invoicedateRow = $i;
                                }
                                if ($cellVal == 'clientpaydate') {

                                    $paydateRow = $i;
                                    $set++;
                                }
                                if ($cellVal == 'client') {

                                    $clientaliasRow = $i;
                                    $set++;
                                }

                                if ($set == 5) {
                                    $setVal = 1;
                                }
                            }
                        }

                        if ($setVal != 1) {
                            header("Location:pay-report.php?mess=Invalid Excel Format");
                        }

                        if (!isset($workordernumberRow) && !isset($invoicenumberRow)) {
                            header("Location:pay-report.php?mess=Invalid Excel Format - Work Order Number Missing");
                        }

                        if (!isset($loannumberRow) || !isset($invoiceamountRow) || !isset($paydateRow) || !isset($payrefRow) || !isset($clientaliasRow)) {
                            header("Location:pay-report.php?mess=Invalid Excel Format");
                        }

                        if ($count > 0) {
                            if(!isset($workordernumberRow)){
                                $workordernumberRow = '';
                            }
                            if(strlen($workordernumberRow) > 0){
                                $w_o_no = isset($row[$workordernumberRow]) ? clean_cell_input_data($row[$workordernumberRow]) : "";
                            }else{
                                $w_o_no = "";
                            }

                            if(!isset($invoicenumberRow)){
                                $invoicenumberRow = $workordernumberRow;
                            }
                            $w_o_no = str_replace('_DED', '', $w_o_no);
                            $w_o_no = str_replace('_ded', '', $w_o_no);

                            if(strlen($invoicenumberRow) > 0){
                                $inv_no = isset($row[$invoicenumberRow]) ? clean_cell_input_data($row[$invoicenumberRow]) : "";
                            }else{
                                $inv_no = "";
                            } 

                            $inv_no = str_replace('_DED', '', $inv_no);
                            $inv_no = str_replace('_ded', '', $inv_no);

                            if(strlen($inv_no) == 0){
                                $inv_no = $w_o_no;
                            } 

                            $loan_no = isset($row[$loannumberRow]) ? clean_cell_input_data($row[$loannumberRow]) : "";

                            $invoice_amount = isset($row[$invoiceamountRow]) ? clean_cell_input_data($row[$invoiceamountRow]) : "";
                            $invoice_amount = str_replace(' ', '', $invoice_amount);
                            $invoice_amount = str_replace('$', '', $invoice_amount);
                            $invoice_amount =  number_format($invoice_amount, 2, '.', '');
                            $client_alias = isset($row[$clientaliasRow]) ? clean_cell_input_data($row[$clientaliasRow]) : "";

                            $pay_date = isset($row[$paydateRow]) ? clean_cell_input_data($row[$paydateRow]) : "";

                            if (strlen($pay_date) > 0) {
                                if (strpos($pay_date, '/') !== false) {
                                    $pay_date = DateTime::createFromFormat('m/d/Y', "$pay_date");
                                    $pay_date = $pay_date->format('Y-m-d h:i:s');
                                } elseif (strpos($pay_date, '-') !== false) {
                                    $pay_date = DateTime::createFromFormat('m-d-Y', "$pay_date");
                                    $pay_date = $pay_date->format('Y-m-d h:i:s');
                                } else {
                                    $pay_date = date("Y-m-d h:i:s", ($pay_date - 25569) * 86400);
                                }
                            } else {
                                $pay_date = date("Y-m-d h:i:s");
                            }

                            $pay_ref = isset($row[$payrefRow]) ? clean_cell_input_data($row[$payrefRow]) : "";

                            if (strlen($inv_no) > 0) {
                                $check_invoice_query = "SELECT * FROM invoice WHERE invoice_no = '$inv_no' AND is_delete IS NULL AND is_approve = 1";
                                $check_invoice_run = mysqli_query($conn, $check_invoice_query);

                                if (mysqli_num_rows($check_invoice_run) == 1) {
                                    $prop_set = 0;
                                    if (strlen($loan_no) > 0) {
                                        $check_invoice_row = mysqli_fetch_array($check_invoice_run);
                                        $int_property_id = $check_invoice_row['prop_id'];
                                        $invoice_id = $check_invoice_row['invoice_id'];
                                        $prev_vendor_pay_amount = $check_invoice_row['client_pay'];
                                        $int_vendor_pay_amount = $check_invoice_row['vendor_pay'];
                                        $check_property_query = "SELECT * FROM property WHERE prop_id = $int_property_id";
                                        $check_property_run = mysqli_query($conn, $check_property_query);

                                        if (mysqli_num_rows($check_property_run) == 1) {
                                            $check_property_row = mysqli_fetch_array($check_property_run);
                                            $int_loan_no = $check_property_row['prop_case_num'];
                                            if ($int_loan_no == $loan_no) {
                                                $prop_set = 1;
                                            }
                                        }
                                    }

                                    $client_set = 0;
                                    if (strlen($client_alias) > 0) {
                                        $client_alias = strtolower($client_alias);
                                        $int_client_id = $check_invoice_row['client_id'];
                                        $check_client_query = "SELECT * FROM client WHERE client_id = $int_client_id";
                                        $check_client_run = mysqli_query($conn, $check_client_query);

                                        if (mysqli_num_rows($check_client_run) == 1) {
                                            $check_client_row = mysqli_fetch_array($check_client_run);
                                            $int_client_alias = $check_client_row['client_alias'];
                                            $int_client_name = $check_client_row['client_name'];
                                            $int_client_alias = strtolower($int_client_alias);
                                            $int_client_name = strtolower($int_client_name);
                                            if ($int_client_alias == $client_alias) {
                                                $client_set = 1;
                                            } elseif ($int_client_name == $client_alias) {
                                                $client_set = 1;
                                            }
                                        }
                                    }

                                    $bid_detail = 0;
                                    $is_system_bid = $check_invoice_row['is_system_bid'];
                                    if ($is_system_bid == NULL) {
                                        $is_system_bid = '';
                                    }

                                    if (strlen($w_o_no) == 0 && $is_system_bid == 0) {
                                        $bid_detail = 1;
                                    } elseif (strlen($w_o_no) > 0 && $is_system_bid == 1) {
                                        $int_bid_wo_no = $check_invoice_row['bid_wo_no'];
                                        if ($int_bid_wo_no == $w_o_no) {
                                            $bid_detail = 1;
                                        }
                                    }

                                    if ($prop_set == 1 && $client_set == 1 && $bid_detail == 1 && strlen($pay_ref) > 0) {
                                        $check_vendor_pay_query = "SELECT * FROM client_pay_settle WHERE invoice_id = $invoice_id AND ref_no = '$pay_ref'";
                                        $check_vendor_pay_run = mysqli_query($conn, $check_vendor_pay_query);
                                        if (mysqli_num_rows($check_vendor_pay_run) == 1) {
                                            $check_vendor_pay_row = mysqli_fetch_array($check_vendor_pay_run);
                                            $vendor_pay_id = $check_vendor_pay_row['id'];
                                            $vendor_amount_prev = $check_vendor_pay_row['amount'];
                                            $vendor_status = $check_vendor_pay_row['status'];
                                            $vendor_transact_id = $check_vendor_pay_row['transact_id'];
                                            if ($vendor_transact_id == $uniqueId && $vendor_status == 0) {
                                                $invoice_amount_new = $prev_vendor_pay_amount + $invoice_amount;
                                                $invoice_amount = $invoice_amount + $vendor_amount_prev;
                                                $check_vendor_pay_query = "UPDATE client_pay_settle SET amount = $invoice_amount, pay_date = '$pay_date' WHERE id = $vendor_pay_id";
                                                $check_vendor_pay_run = mysqli_query($conn, $check_vendor_pay_query);
                                            } elseif ($vendor_status == 1) {
                                                $message = $message . "Payment # $pay_ref already settled.<br>";
                                            }
                                        } elseif (mysqli_num_rows($check_vendor_pay_run) == 0) {
                                            $invoice_amount_new = $prev_vendor_pay_amount + $invoice_amount;


                                            $check_vendor_pay_query = "INSERT INTO client_pay_settle SET amount = $invoice_amount, invoice_id = $invoice_id, ref_no = '$pay_ref', pay_date = '$pay_date',create_time = '$time', transact_id = '$uniqueId', status = 0";
                                            $check_vendor_pay_run = mysqli_query($conn, $check_vendor_pay_query);
                                        }
                                        if ($int_vendor_pay_amount  > $invoice_amount_new) {
                                            $time = date("Y-m-d H:i:s");

                                            $alert_string = "Client Pay less than Vendor Pay in Invoice# $inv_no.";
                                            $query_cat_verify_alert = "INSERT INTO admin_alert SET admin_alert_id = NULL,admin_alert_time = '$time', admin_alert_link = 'invoice_finalize.php?in_no=$invoice_id',admin_id= $admin_id, admin_alert_activity = '$alert_string'";
                                            $run_cat_verify_alert = mysqli_query($conn, $query_cat_verify_alert);
                                        } else {
                                            $temp_diff = $invoice_amount_new - $int_vendor_pay_amount;
                                            $temp_diff_frac = $temp_diff / $invoice_amount_new;
                                            $temp_diff_frac = $temp_diff_frac * 100;
                                            if ($temp_diff_frac < 40) {
                                                $alert_string = "Profit Less than 40 in Invoice# $inv_no.";
                                                $query_cat_verify_alert = "INSERT INTO admin_alert SET admin_alert_id = NULL,admin_alert_time = '$time', admin_alert_link = 'invoice_finalize.php?in_no=$invoice_id',admin_id= $admin_id, admin_alert_activity = '$alert_string'";
                                                $run_cat_verify_alert = mysqli_query($conn, $query_cat_verify_alert);
                                            }
                                        }

                                        $insert_detail_query = "UPDATE invoice SET client_pay = $invoice_amount_new, is_settle = 1 WHERE invoice_no = '$inv_no'";
                                        $insert_detail_run = mysqli_query($conn, $insert_detail_query);
                                        if (mysqli_affected_rows($conn) == 1) {
                                            if (strlen($w_o_no) > 0 && $is_system_bid == 1) {
                                                $insert_row++;
                                                $insert_detail_query_bid = "UPDATE bid SET is_settle = 1 WHERE bid_wo_no = '$w_o_no'";
                                                $insert_detail_run_bid = mysqli_query($conn, $insert_detail_query_bid);
                                            } else {
                                                $insert_row++;
                                            }
                                        }
                                    } else {
                                        $message = $message . "Invoice #$inv_no: Incorrect details filled.<br>";
                                    }
                                } else {
                                    $message = $message . "Invoice #$inv_no does not exist or invoice is already settled.<br>";
                                }
                            }
                        }
                        $count++;
                    }
                    $set_status_query = "UPDATE client_pay_settle SET status = 1 WHERE transact_id = '$uniqueId'";
                    $set_status_run = mysqli_query($conn, $set_status_query);

                    if ($insert_row > 0) {
                        $successmessage = "$insert_row client payments settled.";
                    } else {
                        $message = $message . "$insert_row client payments settled.";
                    }
                }
            } else {
                $message = $message . "The File could not be uploaded due to technical reasons. Try again.";
            }
        }
    }
}
include "header.php";

?>
<div id="page-wrapper">
    <h3>Accounts Receivable<a class='btn btn-warning' style='float:right;' href='index.php' id=''>Cancel</a></h3>
    <?php
    if (isset($successmessage)) {
        if (strlen($successmessage) > 0) {
            echo '<div class="alert alert-success">
            <strong>' . $successmessage . '</strong></div>';
        }
    }

    if (isset($message)) {
        if (strlen($message) > 0) {
            echo '<div class="alert alert-danger">
            <strong>' . $message . '</strong>
            </div>';
        }
    }

    ?>
    <div class="row">
        <div class="col-md-6">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <b>Client Payment </b>
                </div> <!-- "panel-heading" -->
                <div class="panel-body">
                    <form action='pay-report.php?upload_xl_client_pay=' method='POST' enctype='multipart/form-data'>
                        <div class="form-group col-md-8">
                            <label for="fileToUpload">Upload Client Payment Details Excel</label>
                            <input type="file" class="upload-file" required name="fileToUpload" id="fileToUpload">
                            <br>
                            <input type="submit" class="btn btn-success" value="Upload">
                            <a href="excels/client_pay.xlsx" download class="btn btn-danger" style='float:right;'>
                                Client Payment Excel Format
                            </a>
                        </div>

                    </form>

                </div> <!-- "panel-body" -->
            </div> <!-- panel panel-default -->



        </div> <!--  col lg 6-->

        <div class="col-md-6">




        </div> <!--  col lg 6-->


    </div> <!-- row -->
    <style>
        th {
            font-size: 12px;
            padding: 0;
        }

        td {
            font-size: 12px;
            padding: 0;
        }
        
    </style>
    <div class="row">
        <div class="col-lg-12">
            <div class="panel panel-default">
                <div class="panel-body">

                    <table width="100%" class="table table-striped table-bordered table-hover" id="dataTables-bidwiz-pay-report">
                        <thead>
                            <tr>

                                <th>
                                    Invoice#
                                </th>
                                <th>
                                    WO#
                                </th>
                                <th>
                                    Client Name
                                </th>
                                <th>
                                    Loan Number </th>
                                <th>
                                    Invoice Amount
                                </th>

                                <th>
                                    Invoice Date
                                </th>

                                <th>
                                    Client Pay Amount
                                </th>

                                <th>
                                    Client Pay Date
                                </th>
                                <th>
                                    Client Reference#
                                </th>
                                <th>
                                    Crew Pay Amount
                                </th>

                                <th>
                                    Projected Profit
                                </th>

                                <th>
                                    Actual Profit
                                </th>

                                <th>
                                    Staff
                                </th>


                            </tr>
                        </thead>
                        <tbody>



                        </tbody>

                    </table>

                </div><!-- panel-body  -->
            </div> <!-- panel panel-default -->
        </div> <!--  col lg 12-->
    </div> <!-- row -->


</div> <!--  page wrapper -->


<?php include "footer.php";   ?>