<?php 
require_once "include/db.php";
include "include/adminauth.php";
$dash_year = date("Y", time());
$current_year = $dash_year;
$select_string = "Annual $dash_year";
$dash_option = 'y';
$dash_value = '';
  if(isset($_REQUEST['year']) && isset($_REQUEST['option']) &&  isset($_REQUEST['value'])){
    if(strlen($_REQUEST['year']) > 0 && strlen($_REQUEST['option']) > 0 &&  strlen($_REQUEST['value']) > 0){   

        if($_REQUEST['year'] > 1990 && $_REQUEST['year'] < 2100){
            $dash_year = $_REQUEST['year'];
        }
        if($_REQUEST['option'] == 'y' || $_REQUEST['option'] == 'q' || $_REQUEST['option'] == 'm'){
            $dash_option = $_REQUEST['option'];
            if($dash_option == 'm'){
                if($_REQUEST['value'] >=0 && $_REQUEST['value'] <=11){
                    $dash_value = $_REQUEST['value'];
                }else{
                    $dash_value = 11;
                }
                $m_names = ['January', 'February', 'March', 
               'April', 'May', 'June', 'July', 
               'August', 'September', 'October', 'November', 'December'];
                
                $dash_month = $m_names[$dash_value];
                $select_string = "$dash_month $dash_year";
                $dash_value = $dash_value + 1;
            }
            if($dash_option == 'q'){
                if($_REQUEST['value'] >= 1 && $_REQUEST['value'] <= 4){
                    $dash_value = $_REQUEST['value'];
                }else{
                    $dash_value = 4;
                }
                $select_string = "Quarter $dash_value $dash_year";
            }
            if($dash_option == 'y'){
                $select_string = "Annual $dash_year";
            }
        }
        
        $time12 = strtotime("-1 year", time());
        $twelve_mnt = date("Y-m-d h:i:s", $time12);
        $twelve_mnt_date = date("Y-m-d", $time12);


        $time11 = strtotime("-11 month", time());
        $eleven_mnt = date("Y-m-d h:i:s", $time11);

        $time1 = strtotime("-1 month", time());
        $one_mnt = date("Y-m-d h:i:s", $time1);
        $one_mnt_date = date("Y-m-d", $time1);

        $estimate_query_unsettled = '';
       
        if($dash_option == 'y'){
            $estimate_query_unsettled = " AND YEAR(create_time) = $dash_year";
        }elseif($dash_option == 'm'){
            $estimate_query_unsettled = " AND YEAR(create_time) = $dash_year AND MONTH(create_time) = $dash_value";
        }elseif($dash_option == 'q'){
            $estimate_query_unsettled = " AND YEAR(create_time) = $dash_year AND QUARTER(create_time) = $dash_value";
        }else{
            $estimate_query_unsettled = " AND YEAR(create_time) = $dash_year";
        }

        $fetch_year_unpaid_est_query_generated = "SELECT * FROM bid WHERE is_delete IS NULL" . $estimate_query_unsettled;

        $fetch_year_unpaid_est_run_generated = mysqli_query($conn, $fetch_year_unpaid_est_query_generated);
        $pending_est_amt_twelve_generated = 0;
        while($fetch_year_unpaid_est_row_generated = mysqli_fetch_array($fetch_year_unpaid_est_run_generated)){
            $fetch_year_unpaid_est_amount_generated = $fetch_year_unpaid_est_row_generated['bid_amount'];
            $pending_est_amt_twelve_generated = $pending_est_amt_twelve_generated + $fetch_year_unpaid_est_amount_generated;            
        }
        $estimate_generated = $pending_est_amt_twelve_generated;

        $fetch_year_unpaid_est_query = "SELECT * FROM bid WHERE is_delete IS NULL AND is_settle IS NULL" . $estimate_query_unsettled;

        $fetch_year_unpaid_est_run = mysqli_query($conn, $fetch_year_unpaid_est_query);
        $pending_est_amt_twelve = 0;
        while($fetch_year_unpaid_est_row = mysqli_fetch_array($fetch_year_unpaid_est_run)){
            $fetch_year_unpaid_est_amount = $fetch_year_unpaid_est_row['bid_amount'];
            $pending_est_amt_twelve = $pending_est_amt_twelve + $fetch_year_unpaid_est_amount;            
        }
        $estimate_unpaid = $pending_est_amt_twelve;
        
        $fetch_one_month_unpaid_est_query = "SELECT * FROM bid WHERE is_delete IS NULL AND is_settle IS NULL AND create_time >= '$one_mnt'";

        $fetch_one_month_unpaid_est_run = mysqli_query($conn, $fetch_one_month_unpaid_est_query);
        $pending_est_amt_one = 0;
        while($fetch_one_month_unpaid_est_row = mysqli_fetch_array($fetch_one_month_unpaid_est_run)){
            $fetch_one_month_unpaid_est_amount = $fetch_one_month_unpaid_est_row['bid_amount'];
            $pending_est_amt_one = $pending_est_amt_one + $fetch_one_month_unpaid_est_amount;           
        }
        
        if($current_year == $dash_year){
            $pending_est_amt_overdue_12 = $pending_est_amt_twelve - $pending_est_amt_one;
            if($pending_est_amt_one > 0){
                $total_due_est = $estimate_generated - $pending_est_amt_one;
            }else{
                $total_due_est = $estimate_generated;

            }
        }else{
            $pending_est_amt_overdue_12 = $pending_est_amt_twelve;
            $total_due_est = $estimate_generated;
        }
        
        if($pending_est_amt_overdue_12 >= 0){
            $estimate_overdue = $pending_est_amt_overdue_12;
        }else{
            $estimate_overdue = 0;
        }
        
        $estimate_not_due = $pending_est_amt_one;        

        $fetch_one_month_paid_est_query = "SELECT * FROM bid WHERE is_delete IS NULL AND is_settle = 1" . $estimate_query_unsettled;

        $fetch_one_month_paid_est_run = mysqli_query($conn, $fetch_one_month_paid_est_query);
        $paid_est_amt_one = 0;
        while($fetch_one_month_paid_est_row = mysqli_fetch_array($fetch_one_month_paid_est_run)){
            $fetch_one_month_paid_bid_wo_no = $fetch_one_month_paid_est_row['bid_wo_no'];
            $fetch_one_month_paid_inv_id = "SELECT * FROM invoice WHERE is_delete IS NULL AND is_settle = 1 AND bid_wo_no = '$fetch_one_month_paid_bid_wo_no'";
            $fetch_one_month_paid_inv_id_run = mysqli_query($conn, $fetch_one_month_paid_inv_id);
            while($fetch_one_month_paid_inv_id_row = mysqli_fetch_array($fetch_one_month_paid_inv_id_run)){
                $fetch_estimate_paid_inv_id_row = $fetch_one_month_paid_inv_id_row['invoice_id'];
                $fetch_client_pay_amount_est_query = "SELECT * FROM client_pay_settle WHERE invoice_id = $fetch_estimate_paid_inv_id_row";
                $fetch_client_pay_amount_est_run = mysqli_query($conn, $fetch_client_pay_amount_est_query);
                while($fetch_client_pay_amount_est_row = mysqli_fetch_array($fetch_client_pay_amount_est_run)){
                    $fetch_one_month_paid_est_amount = $fetch_client_pay_amount_est_row['amount']; 
                    $paid_est_amt_one = $paid_est_amt_one + $fetch_one_month_paid_est_amount;
                }
            }
        }
        $estimate_paid = $paid_est_amt_one;

        $estimate_progress = 0;

        if($estimate_paid > 0 && $total_due_est > 0){
            $estimate_progress = $estimate_paid/$total_due_est;
            $estimate_progress = $estimate_progress * 100;
        }    
        
        $fetch_year_unpaid_invoice_query_generated = "SELECT * FROM invoice WHERE is_delete IS NULL" . $estimate_query_unsettled;

        $fetch_year_unpaid_invoice_run_generated = mysqli_query($conn, $fetch_year_unpaid_invoice_query_generated);
        $pending_invoice_amt_twelve_generated = 0;
        while($fetch_year_unpaid_invoice_row_generated = mysqli_fetch_array($fetch_year_unpaid_invoice_run_generated)){
            $fetch_year_unpaid_invoice_amount_generated = $fetch_year_unpaid_invoice_row_generated['invoice_amount'];
            $pending_invoice_amt_twelve_generated = $pending_invoice_amt_twelve_generated + $fetch_year_unpaid_invoice_amount_generated;
            
        }
        $invoice_generated = $pending_invoice_amt_twelve_generated;
                        
        $fetch_year_unpaid_invoice_query = "SELECT * FROM invoice WHERE is_delete IS NULL AND is_settle IS NULL" . $estimate_query_unsettled;

        $fetch_year_unpaid_invoice_run = mysqli_query($conn, $fetch_year_unpaid_invoice_query);
        $pending_invoice_amt_twelve = 0;
        while($fetch_year_unpaid_invoice_row = mysqli_fetch_array($fetch_year_unpaid_invoice_run)){
            $fetch_year_unpaid_invoice_amount = $fetch_year_unpaid_invoice_row['invoice_amount'];
            $pending_invoice_amt_twelve = $pending_invoice_amt_twelve + $fetch_year_unpaid_invoice_amount;
            
        }
        $invoice_unpaid = $pending_invoice_amt_twelve;
        
        $fetch_one_month_unpaid_invoice_query = "SELECT * FROM invoice WHERE is_delete IS NULL AND is_settle IS NULL AND create_time >= '$one_mnt'";

        $fetch_one_month_unpaid_invoice_run = mysqli_query($conn, $fetch_one_month_unpaid_invoice_query);
        $pending_invoice_amt_one = 0;
        while($fetch_one_month_unpaid_invoice_row = mysqli_fetch_array($fetch_one_month_unpaid_invoice_run)){
            $fetch_one_month_unpaid_invoice_amount = $fetch_one_month_unpaid_invoice_row['invoice_amount'];
            $pending_invoice_amt_one = $pending_invoice_amt_one + $fetch_one_month_unpaid_invoice_amount;            
        }

        if($current_year == $dash_year){
            $pending_invoice_amt_overdue_12 = $pending_invoice_amt_twelve - $pending_invoice_amt_one;
            if($pending_invoice_amt_one > 0){
                $total_due_inv = $invoice_generated - $pending_invoice_amt_one;

            }
            $total_due_inv = $invoice_generated ;
        }else{
            $pending_invoice_amt_overdue_12 = $pending_invoice_amt_twelve;
            $total_due_inv = $invoice_generated;
        }
        if($pending_invoice_amt_overdue_12 >= 0){
            $invoice_overdue = $pending_invoice_amt_overdue_12;
        }else{
            $invoice_overdue = 0;
        }
                                                
        $invoice_not_due = $pending_invoice_amt_one;
           
                
        $fetch_one_month_paid_invoice_query = "SELECT * FROM invoice WHERE is_delete IS NULL AND is_settle = 1" . $estimate_query_unsettled;

        $fetch_one_month_paid_invoice_run = mysqli_query($conn, $fetch_one_month_paid_invoice_query);
        $paid_invoice_amt_one = 0;
        while($fetch_one_month_paid_invoice_row = mysqli_fetch_array($fetch_one_month_paid_invoice_run)){
            $fetch_one_month_paid_invoice_id = $fetch_one_month_paid_invoice_row['invoice_id'];
            $fetch_client_pay_amount_invoice_query = "SELECT * FROM client_pay_settle WHERE invoice_id = $fetch_one_month_paid_invoice_id";
            $fetch_client_pay_amount_invoice_run = mysqli_query($conn, $fetch_client_pay_amount_invoice_query);
            while($fetch_client_pay_amount_invoice_row = mysqli_fetch_array($fetch_client_pay_amount_invoice_run)){
                $fetch_one_month_paid_invoice_amount = $fetch_client_pay_amount_invoice_row['amount'];
                $paid_invoice_amt_one = $paid_invoice_amt_one + $fetch_one_month_paid_invoice_amount;
            }
        }        
 
        $period_vendor_pay = 0;
        $fetch_month_invoice_query_vendor = "SELECT * FROM staff_pay WHERE pay_type = 'vendor' AND is_delete IS NULL". $estimate_query_unsettled;      
        $fetch_month_invoice_run_vendor = mysqli_query($conn, $fetch_month_invoice_query_vendor);
        while($fetch_month_invoice_row_vendor = mysqli_fetch_array($fetch_month_invoice_run_vendor)){
            $fetch_month_vendor_pay = $fetch_month_invoice_row_vendor['pay_amount'];
            $period_vendor_pay = $period_vendor_pay + $fetch_month_vendor_pay;
        }

        $period_staff_pay = 0;

        $fetch_month_invoice_query_staff = "SELECT * FROM staff_pay WHERE pay_type = 'staff' AND is_delete IS NULL". $estimate_query_unsettled;      
        $fetch_month_invoice_run_staff = mysqli_query($conn, $fetch_month_invoice_query_staff);
        while($fetch_month_invoice_row_staff = mysqli_fetch_array($fetch_month_invoice_run_staff)){
            $fetch_month_staff_pay = $fetch_month_invoice_row_staff['pay_amount'];
            $period_staff_pay = $period_staff_pay + $fetch_month_staff_pay;
        }

        $invoice_paid = $paid_invoice_amt_one;
        $invoice_progress = 0;
        if($invoice_paid > 0 && $total_due_inv > 0){
            $invoice_progress = $invoice_paid/$total_due_inv;
            $invoice_progress = $invoice_progress * 100;
        }    
        $period_profit = $invoice_paid - $period_vendor_pay - $period_staff_pay;
        $estimate_generated = number_format($estimate_generated, 2, '.', '');
        $invoice_generated = number_format($invoice_generated, 2, '.', '');
        $estimate_overdue = number_format($estimate_overdue, 2, '.', '');
        $estimate_not_due = number_format($estimate_not_due, 2, '.', '');
        $estimate_progress = number_format($estimate_progress, 0, '.', '');
        $estimate_paid = number_format($estimate_paid, 2, '.', '');
        $invoice_unpaid = number_format($invoice_unpaid, 2, '.', '');
        $invoice_overdue = number_format($invoice_overdue, 2, '.', '');
        $invoice_not_due = number_format($invoice_not_due, 2, '.', '');
        $period_staff_pay = number_format($period_staff_pay, 2, '.', '');
        $period_vendor_pay = number_format($period_vendor_pay, 2, '.', '');
        $period_profit = number_format($period_profit, 2, '.', '');
        $invoice_progress = number_format($invoice_progress, 0, '.', '');
        $invoice_paid = number_format($invoice_paid, 2, '.', '');
        $final_array = array("estimate_generated"=> $estimate_generated,"estimate_overdue" => $estimate_overdue, "estimate_progress" => $estimate_progress, "invoice_generated" => $invoice_generated, "estimate_paid" =>$estimate_paid ,"invoice_unpaid" => $invoice_unpaid,"invoice_overdue" =>  $invoice_overdue,"invoice_not_due" => $invoice_not_due,"invoice_progress" =>  $invoice_progress,"invoice_paid" =>  $invoice_paid,"select_string" =>  $select_string, "period_staff_pay" => $period_staff_pay, "period_vendor_pay" => $period_vendor_pay, "period_profit"=>$period_profit);
        echo json_encode($final_array);
        
    }          
  }
?>
