<?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_vendor_pay'])){  
    $message = '';  
    $uniqueId= time().'-'.mt_rand();	
    $temp = explode(".", $_FILES["fileToUpload1"]["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["fileToUpload1"]["size"] > 2000000){
        $message = $message . 'The file you uploaded is too large. File size should be less than 2 MB';
    }else{
        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["fileToUpload1"]["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) {
                    if($count == 0){	

                        $setVal = 0;

                        $set = 0;                        

                        for($i=0;$i<30;$i++){
                            if(!isset($row[$i])){
                                $row[$i] = " ";
                            }
                            $cellVal = clean_input_data($row[$i]); 
                            if($cellVal == 'invoicenumber'){
                                $invoicenumberRow = $i;                              
                            }
                            if($cellVal == 'loannumber'){
                                $loannumberRow = $i;
                                $set++;
                            }
                            if($cellVal == 'amountpaid'){
                                $invoiceamountRow = $i;
                                $set++;
                            }
                            if($cellVal == 'vendorid'){
                                $vendoridRow = $i;
                                $set++;
                            }
                            if($cellVal == 'vendorname'){
                                $vendornameRow = $i;
                                $set++;
                            }
                            if($cellVal == 'propertyaddress'){
                                $propertyaddressRow = $i;
                                $set++;
                            }
                            if($cellVal == 'state'){
                                $stateRow = $i;
                                $set++;
                            }
                            if($cellVal == 'zipcode'){
                                $zipcodeRow = $i;
                                $set++;
                            }
                            if($cellVal == 'paydate'){
                                $paydateRow = $i;
                                $set++;
                            }
                            if($cellVal == 'paymentreference'){
                                $payrefRow = $i;
                                $set++;
                            }
                            if($cellVal == 'workordernumber'){
                                $workordernumberRow = $i;
                            }
                            if($cellVal == 'achnumber/check#/wirenumber'){
                                $instrumentnumberRow = $i;
                            }
                            if($cellVal == 'client'){
                                $clientaliasRow = $i;
                                $set++;
                            }  

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

                    if($setVal != 1){                       
                        header("Location:vendor-pay-report.php?mess=Invalid Excel Format");
                    }
                    if(!isset($invoicenumberRow) && !isset($workordernumberRow)){
                        header("Location:vendor-pay-report.php?mess=Invalid Excel Format");
                    }
                   
                    if(!isset($zipcodeRow) || !isset($loannumberRow) || !isset($invoiceamountRow) || !isset($stateRow) || !isset($vendornameRow) ||!isset($vendoridRow) || !isset($payrefRow) || !isset($clientaliasRow) || !isset($propertyaddressRow)|| !isset($paydateRow)){
                        
                        header("Location:vendor-pay-report.php?mess=Invalid Excel Format");
                    }

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

                        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);
                        
                        $pay_date = isset($row[$paydateRow]) ? clean_cell_input_data($row[$paydateRow]) : "";
                       
                        $vendorid = isset($row[$vendoridRow]) ? clean_cell_input_data($row[$vendoridRow]) : "";

                        $vendorname = isset($row[$vendornameRow]) ? clean_cell_input_data($row[$vendornameRow]) : "";

                        $zipcode = isset($row[$zipcodeRow]) ? clean_cell_input_data($row[$zipcodeRow]) : "";

                        $state = isset($row[$stateRow]) ? clean_cell_input_data($row[$stateRow]) : "";

                        $property_address = isset($row[$propertyaddressRow]) ? clean_cell_input_data($row[$propertyaddressRow]) : "";

                        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]) : "";

                        $client_alias = isset($row[$clientaliasRow]) ? clean_cell_input_data($row[$clientaliasRow]) : "";

                        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){
                                $check_invoice_row = mysqli_fetch_array($check_invoice_run);
                                $prop_set = 0;
                                if(strlen($loan_no)>0 && strlen($zipcode)>0 && strlen($property_address)>0 && strlen($state)>0){
                                    $int_property_id = $check_invoice_row['prop_id'];
                                    $invoice_id = $check_invoice_row['invoice_id'];
                                    $prev_vendor_pay_amount = $check_invoice_row['vendor_pay'];
                                    $int_invoice_amount = $check_invoice_row['invoice_amount'];
                                    $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'];
                                        $int_full_add = $check_property_row['prop_full_add'];
                                        $int_state = $check_property_row['prop_state'];
                                        $int_zip = $check_property_row['prop_zip'];
                                        $prop_count = 0;
                                        if($int_loan_no == $loan_no){
                                            $prop_count++;
                                        }
                                        if($int_full_add == $property_address){
                                            $prop_count++;
                                        }
                                        if($int_state == $state){
                                            $prop_count++;
                                        }
                                        if($int_zip == $zipcode){
                                            $prop_count++;
                                        }
                                        if($prop_count == 4){
                                            $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;
                                        }
                                    }
                                }
                                $vendor_set = 0;
                                $check_vendor_query = "SELECT * FROM vendor WHERE vendor_name = '$vendorname' AND vendor_id = '$vendorid'";
                                $check_vendor_run = mysqli_query($conn, $check_vendor_query);

                                if(mysqli_num_rows($check_vendor_run) == 1){
                                    $check_vendor_row = mysqli_fetch_array($check_vendor_run);
                                    $vendor_id = $check_vendor_row['vendor_id'];
                                    if(isset($vendor_id)){
                                        if(strlen($vendor_id) > 0){
                                            $vendor_set = 1; 
                                        }
                                    }                               
                                }  
                                $bid_detail = 0;
                                $is_system_bid = $check_invoice_row['is_system_bid'];
                                if($is_system_bid == NULL){
                                    $is_system_bid = 0;
                                }

                                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 && $vendor_set == 1 && strlen($pay_ref) > 0 && $bid_detail == 1){
                                    $check_vendor_pay_query = "SELECT * FROM vendor_pay_settle WHERE vendor_id = $vendor_id AND 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 vendor_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 vendor_pay_settle SET amount = $invoice_amount,vendor_id = $vendor_id , invoice_id = $invoice_id, ref_no = '$pay_ref',instrument_number = '$instrument_number', pay_date = '$pay_date',create_time = '$time', status = 0, transact_id = '$uniqueId'";
                                            $check_vendor_pay_run = mysqli_query($conn, $check_vendor_pay_query);                                      
                                        
                                        
                                    } 
                                    $insert_detail_query = "UPDATE invoice SET vendor_pay = $invoice_amount_new, vendor_is_settle = 1 WHERE invoice_no = '$inv_no'";
                                    $insert_detail_run = mysqli_query($conn, $insert_detail_query);

                                    if(mysqli_affected_rows($conn) == 1){
                                        if($invoice_amount_new > $int_invoice_amount){
                                            $time = date("Y-m-d H:i:s");
            
                                            $alert_string = "settled Vendor Pay more than invoive amount 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_row++;
                                        if(strlen($w_o_no)>0 && $is_system_bid == 1){
                                            $insert_detail_query_bid = "UPDATE bid SET vendor_is_settle = 1 WHERE bid_wo_no = '$w_o_no'";
                                            $insert_detail_run_bid = mysqli_query($conn, $insert_detail_query_bid);
                                        }                                    
                                    }
                                }else{
                                    $message = $message . "Invoice #$inv_no: Incorrect details filled.<br>"; 
                                }
                            }else{                               
                                $message = $message . "Invoice #$inv_no does not exist.<br>"; 
                            }                            
                        }
                    }
                    $count++;    
                }
                    $set_status_query = "UPDATE vendor_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 Vendor payments settled.";
                    }else{
                        $message = $message . "$insert_row Vendor 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>Vendor Pay<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>';
        }
    }
   
?>    
     <?php          if($user_type == 'admin'){     ?>              
    <div class="row">      

         <div class="col-md-6">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <b>Vendor Payment </b> 
                </div> <!-- "panel-heading" -->                
                <div class="panel-body">
                <form action = 'vendor-pay-report.php?upload_xl_vendor_pay=' method = 'POST' enctype = 'multipart/form-data'>
                <div class="form-group col-md-6">
                    <label for="fileToUpload1">Upload Vendor Payment Details Excel</label>
				    <input type="file" class="upload-file" required name="fileToUpload1" id="fileToUpload1"> 
                    <br>
                    <input type="submit" class="btn btn-success" value = "Upload"> 
                    <a href="excels/vendor_pay.xlsx" download class="btn btn-danger" style = 'float:right;'>
                     Vendor Pay 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 -->          
     <?php          }    ?>     
   <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-vendor-pay-report">
                                    <thead>
                                        <tr>
                                                <th>
                                                   Invoice#
                                                </th>
                                                <th>
                                                   WO#
                                                </th>
                                                <th>
                                                  Client Name
                                                </th>
                                                <th>
                                                   Loan Number                                 </th>
                                                   <th>
                                                   Vendor ID
                                                   </th>
                                                   <th>
                                                Vendor Name
                                                </th> 
                                                <th>
                                                Vendor Pay Amount
                                                </th> 
                                                <th>
                                                Vendor Pay Date
                                                </th>
                                                <th>
                                                  Vendor Reference#
                                                </th>  
                                                <th>
                                               ACH Number/ Check #/ Wire Number 
                                                </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";   ?>