<?php

set_time_limit(0);
//ini_set('memory_limit', '1024M');

ini_set('memory_limit', '25600M');

//ini_set('memory_limit','-1'); // enabled the full memory available.


class Report
{
    private $db_con;

    public function __construct()
    {
        global $con;
        $this->db_con = $con;
    }

    public function report_mail($input_data): array
    {
        $mail = new Mail();
        $item_details = new ItemDetails();
        $output_message = new OutputMessage();

        $input_data->usr_id = $input_data->usr_report_usr_mail_id;
        $tmp_report_data = $this->report_generate($input_data)[0];

        $tmp_file_name = ucwords(implode("_",explode(" ", $tmp_report_data["usr_name"])))."_" . date('m-d-Y', strtotime($input_data->usr_report_start_date)) . "_" . date('m-d-Y', strtotime($input_data->usr_report_end_date));
        $tmp_title = $tmp_report_data["usr_name"] . " completed orders " . date('m/d/Y', strtotime($input_data->usr_report_start_date)) . " - " . date('m/d/Y', strtotime($input_data->usr_report_end_date));

        $report_file = $this->generate_report_pdf($tmp_title, $tmp_file_name, $tmp_report_data, true);

        if (!$report_file) {
            $api_output = $output_message->errorOutput("Report send ERROR");
        } else {
            $tmp_usr_details = $item_details->details_item_user("uid='" . $input_data->usr_report_usr_mail_id . "'");

            if (count($tmp_usr_details) > 0) {
                $tmp_inspector_name = $tmp_usr_details[0]["first_name"] . " " . $tmp_usr_details[0]["last_name"];
                $tmp_title = "Your Completed Report from " . date('m/d/Y', strtotime($input_data->usr_report_start_date)) . " to " . date('m/d/Y', strtotime($input_data->usr_report_end_date));
                $tmp_mail_data = "Hi, " . $tmp_inspector_name . ".<br>" . $tmp_title . " attached bellow.</p>";


                $send_mail_status = $mail->send_mail($tmp_usr_details[0]["mail"], $tmp_inspector_name, $tmp_title, $tmp_mail_data, $report_file, false);
                if ($send_mail_status) {
                    $api_output = $output_message->successOutput("", "Report send successfully");
                } else {
                    $api_output = $output_message->errorOutput("Message send ERROR");
                }
            } else {
                $api_output = $output_message->errorOutput("Report send ERROR");
            }
        }
        return $api_output;
    }

    private function report_generate($input_data)
    {

        $item_details = new ItemDetails();
        $beautify_arr = new ItemBeautify();
        $report_data = [];

        $tmp_sql = "";

        if ($input_data->usr_id != "all") {
            $tmp_sql = "uid='" . $input_data->usr_id . "' AND ";
        }


        $get_inspector_sql = "SELECT * FROM " . tbl_usr . " WHERE " . $tmp_sql . "usr_type='inspector' AND status='true' ORDER BY uid DESC";

        $get_inspector_query = mysqli_query($this->db_con, $get_inspector_sql) or (die($this->db_con));

        while ($get_inspector_arr = mysqli_fetch_assoc($get_inspector_query)) {
            $tmp_usr_data = [];
            $tmp_tot_amount = 0;
            $tmp_debit = 0;
            $tmp_credit = 0;
            $tmp_name = strtoupper($get_inspector_arr["first_name"] . " " . $get_inspector_arr["last_name"]);
            $tmp_usr_data["usr_name"] = $tmp_name;
            $tmp_usr_data["usr_id"] = $get_inspector_arr["uid"];

            $get_order_sql = "  SELECT DISTINCT
                                    a.work_order_id as report_work_order_id,
                                    a.city as report_city,
                                    a.completion_date as report_completion_date,
                                    a.work_code as report_work_code,
                                    a.address as report_address,
                                    a.zip_code as report_zip_code, a.alias as report_alias,
                                    a.amount as report_amount,
                                    b.amount as report_price_amount

                                FROM " . tbl_work_order . " a
                                JOIN " . tbl_price_list . " b ON a.work_code=b.work_code AND a.zip_code=b.zip_code
                                WHERE   LOWER(a.alias) IN ('" . implode("','", explode(",", strtolower($get_inspector_arr["alias"]))) . "')
                                        AND DATE(completion_date) between '" . $input_data->usr_report_start_date . "'
                                        AND '" . $input_data->usr_report_end_date . "'
                                ORDER BY completion_date DESC";

            $get_order_query = mysqli_query($this->db_con, $get_order_sql) or (die($this->db_con));

            $tmp_usr_data["tot_order"] = mysqli_num_rows($get_order_query);

            while ($get_order_arr = mysqli_fetch_assoc($get_order_query)) {
                $tmp_data = [
                    "usr_id" => "",
                    "usr_name" => strtoupper($tmp_name . " (" . $get_order_arr["report_alias"] . ")"),
                    "complete_date" => date('m/d/Y', strtotime($get_order_arr["report_completion_date"])),
                    "work_order_id" => strtoupper($get_order_arr["report_work_order_id"]),
                    "work_code" => strtoupper($get_order_arr["report_work_code"]),
                    "address" => strtoupper($get_order_arr["report_address"]),
                    "city" => strtoupper($get_order_arr["report_city"]),
                    "zip_code" => strtoupper($get_order_arr["report_zip_code"])
                ];

                if ($get_order_arr["report_price_amount"] == null) {
                    $tmp_data["amount"] = "Not Found";
                    if ($get_order_arr["report_amount"] == 0) {
                        $tmp_data["amount"] = "Not Paid";
                    }
                } else {
                    if ($get_order_arr["report_amount"] != 0) {
                        $tmp_data["amount"] = $get_order_arr["report_price_amount"];
                        $tmp_tot_amount += $get_order_arr["report_price_amount"];
                    }
                    else{
                        $tmp_data["amount"] = "Not Paid";
                    }
                }

                $tmp_usr_data["report"]["order_details"][] = $tmp_data;
            }


            $get_dc_sql = " SELECT * 
                            FROM " . tbl_debit_credit . "
                            WHERE   usr_id='" . $get_inspector_arr["uid"] . "'
                                    AND status='true'
                                    AND DATE(dc_date) between '" . $input_data->usr_report_start_date . "' AND '" . $input_data->usr_report_end_date . "'
                                    ORDER BY dc_date ASC";

            $get_dc_query = mysqli_query($this->db_con, $get_dc_sql) or (die($this->db_con));

            while ($get_dc_arr = mysqli_fetch_assoc($get_dc_query)) {
                if ($get_dc_arr["dc_type"] == "credit") {
                    $tmp_credit += $get_dc_arr["dc_amount"];
                } else {
                    $tmp_debit += $get_dc_arr["dc_amount"];
                }

                $tmp_dc_data = [
                    "date" => date('m/d/Y', strtotime($get_dc_arr["dc_date"])),
                    "comment" => $get_dc_arr["dc_comment"],
                    "amount" => $get_dc_arr["dc_amount"],
                    "type" => $get_dc_arr["dc_type"]
                ];

                $tmp_usr_data["report"]["dc_details"][] = $tmp_dc_data;
            }

            $tmp_usr_data["amount"] = number_format((float)filter_var((($tmp_tot_amount + $tmp_credit) - $tmp_debit), FILTER_VALIDATE_FLOAT), 2, '.', '');
            $tmp_usr_data["tot_debit"] = number_format((float)filter_var($tmp_debit, FILTER_VALIDATE_FLOAT), 2, '.', '');
            $tmp_usr_data["tot_credit"] = number_format((float)filter_var($tmp_credit, FILTER_VALIDATE_FLOAT), 2, '.', '');

            //$tmp_usr_data["asd"]=$tmp_tot_amount;

            if ($input_data->usr_id == "all") {
                $tmp_usr_data["complete_date"] = "";
                $tmp_usr_data["work_order_id"] = "";
                $tmp_usr_data["work_code"] = "";
                $tmp_usr_data["address"] = "";
                $tmp_usr_data["zip_code"] = "";
                $tmp_usr_data["city"] = "";
                $tmp_usr_data["state"] = "";
            }

            /*
            if (count($tmp_usr_data["report"]) > 0) {
                $report_data[] = $tmp_usr_data;
            }*/
             $report_data[] = $tmp_usr_data;
            
        }

        return $report_data;
    }

    private function generate_report_pdf($file_title, $file_name, $file_data, $single_report)
    {
        $make_pdf = new MakePdf();
        $report_inspector_lst_data = "";
        $report_order_lst_data = "";
        $report_debit_credit_lst_data = "";
        $tmp_tot_amount = 0;
        $item_details = new ItemDetails();

        if ($single_report) {
            $report_template = file_get_contents("lib/template/report/template_report_single.html");
        } else {
            $report_template = file_get_contents("lib/template/report/template_report_all.html");
        }

        if ($single_report) {
            //echo "<pre>"; print_r($file_data["report"]["order_details"]); echo "</pre>"; die;
            foreach ($file_data["report"]["order_details"] as $single_order_data) {
                if ($single_order_data["amount"] == "Not Found" || $single_order_data["amount"] == "Not Paid") {
                    $tmp_amount = strtoupper($single_order_data["amount"]);
                } else {
                    $tmp_amount = "$" . number_format((float)filter_var($single_order_data["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ',');
                }
                $zipCode = $single_order_data["zip_code"];
      
                $state = $item_details->get_state_from_zip($zipCode);

                // $zip_state_sql = "SELECT zip_code, state FROM " . tbl_zip_state . " WHERE zip_code = '".$zipCode."' LIMIT 0,1";
                // $zip_state_query = mysqli_query($this->db_con, $zip_state_sql);
                // $zip_state_arr = mysqli_fetch_assoc($zip_state_query);
                // $state = $zip_state_arr['state'];
                //$state = 'NY';
                $report_order_lst_data .= /** @lang text */
                    '<tr>
                       <td>' . $single_order_data["usr_name"] . '</td>
                       <td>' . $single_order_data["complete_date"] . '</td>
                       <td>' . $single_order_data["work_order_id"] . '</td>
                       <td>' . $single_order_data["work_code"] . '</td>
                       <td>' . strtoupper($single_order_data["address"]) . '</td>
                       <td>' . $single_order_data["city"] . '</td>       
                       <td>' . $single_order_data["zip_code"] . '</td>    
                       <td>' . $state . '</td>              
                       <td>' . $tmp_amount . '</td>
                   </tr>';
            }

            foreach ($file_data["report"]["dc_details"] as $single_dc_data) {
                $tmp_color = "green";
                $tmp_sign = "+";
                if ($single_dc_data["type"] == "debit") {
                    $tmp_color = "red";
                    $tmp_sign = "-";
                }
                $report_debit_credit_lst_data .= /** @lang text */
                    '<tr><td style="color:' . $tmp_color . '">' . $single_dc_data["date"] . ' (' . $single_dc_data["comment"] . ') ' . $tmp_sign . ' $ ' . $single_dc_data["amount"] . '</td></tr>';
            }

            $tmp_sub_total = (($file_data["amount"] - $file_data["tot_credit"]) + $file_data["tot_debit"]);
            $report_template = str_replace("{{order_lst}}", $report_order_lst_data, $report_template);
            $report_template = str_replace("{{sub_total_price}}", number_format((float)filter_var($tmp_sub_total, FILTER_VALIDATE_FLOAT), 2, '.', ','), $report_template);
            $report_template = str_replace("{{debit_credit_lst}}", $report_debit_credit_lst_data, $report_template);
            $report_template = str_replace("{{total_order}}", $file_data["tot_order"], $report_template);
            $tmp_tot_amount = $file_data["amount"];

        } else {
            foreach ($file_data as $single_inspector) {
                if ($single_inspector["amount"] == "Not Found" || $single_inspector["amount"] == "Not Paid") {
                    $tmp_amount = strtoupper($single_inspector["amount"]);
                } else {
                    $tmp_amount = "$" . number_format((float)filter_var($single_inspector["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ',');
                }
                $report_inspector_lst_data .= /** @lang text */
                    '<tr>
                       <td>' . $single_inspector["usr_name"] . '</td>
                       <td>' . $tmp_amount . '</td>
                   </tr>';
                $tmp_tot_amount += $single_inspector["amount"];
            }
            $report_template = str_replace("{{inspector_lst}}", $report_inspector_lst_data, $report_template);
           $report_template = str_replace("{{total_inspector}}", count($file_data), $report_template);
        }

        $report_template = str_replace("{{total_price}}", number_format((float)filter_var($tmp_tot_amount, FILTER_VALIDATE_FLOAT), 2, '.', ','), $report_template);
        $report_template = str_replace("{{order_name}}", ucwords($file_title), $report_template);

        //echo "<pre>"; print_r($report_template); echo "</pre>"; die;

        return $make_pdf->create_pdf($file_name, $report_template);
    }

    public function report_download($input_data)
    {
        // ini_set('display_startup_errors', 1);
        // ini_set('display_errors', 1);
        // error_reporting(-1);

        $output_message = new OutputMessage();

        $tmp_single_report = false;

        if ($input_data->usr_download_type == "single") {
            $input_data->usr_id = $input_data->usr_report_usr_mail_id;
        }

       $tmp_report_data = $this->report_generate($input_data);

        $tmp_file_name = "report-" . $input_data->usr_report_start_date . "-to-" . $input_data->usr_report_end_date . "-";

        if ($input_data->usr_download_type == "single") {
            $tmp_title = $tmp_report_data[0]["usr_name"] . " ";
            $tmp_file_name .= implode("-", explode(" ", strtolower($tmp_report_data[0]["usr_name"])));
            $tmp_file_name = ucwords(implode("_",explode(" ", $tmp_report_data[0]["usr_name"])))."_" . date('m-d-Y', strtotime($input_data->usr_report_start_date)) . "_" . date('m-d-Y', strtotime($input_data->usr_report_end_date));
            $tmp_single_report = true;
            $tmp_report_data = $tmp_report_data[0];
        } else {
            $tmp_title = "all user";
            $tmp_file_name .= "all-inspector";
        }

        $tmp_title .= " completed orders " . date('m/d/Y', strtotime($input_data->usr_report_start_date)) . " - " . date('m/d/Y', strtotime($input_data->usr_report_end_date));

        if($input_data->usr_download_format && $input_data->usr_download_format === "csv"){
            
            $report_file = $this->generate_report_csv($tmp_title, $tmp_file_name, $tmp_report_data, $tmp_single_report);

            if (!$report_file) {
                $api_output = $output_message->errorOutput("Report send ERROR");
            } else {
                $api_output = $output_message->successOutput(report_via_url_csv . $report_file, "Report download successfully");
            }
        
        }else{
            $report_file = $this->generate_report_pdf($tmp_title, $tmp_file_name, $tmp_report_data, $tmp_single_report);

            if (!$report_file) {
                $api_output = $output_message->errorOutput("Report send ERROR");
            } else {
                $api_output = $output_message->successOutput(report_via_url . $report_file, "Report download successfully");
            }    
        }
        // echo " in report_download function";
        // echo "<pre>"; print_r($api_output); echo "</pre>";
        // die;
        return $api_output;
    }


    public function report_list($input_data): array
    {

        $output_message = new OutputMessage();

        $report_details_data = $this->report_generate($input_data);

        if (!$report_details_data) {
            $api_output = $output_message->errorOutput('No report found');
        } else {
            $api_output = $output_message->successOutput($report_details_data, "report generated successfully");
        }
        return $api_output;
    }


//Generate CSV Report
    public function generate_report_csv($tmp_title, $tmp_file_name, $tmp_report_data, $tmp_single_report){
        
        $file = fopen(report_via_loc_csv . $tmp_file_name . ".csv", 'w');

        $item_details = new ItemDetails();
        $file_header = [
            "item_1" => "User (Inspector)",
            "item_2" => "Date",
            "item_3" => "Order #",
            "item_4" => "Work ID",
            "item_5" => "Address",
            "item_6" => "City",
            "item_7" => "Zip Code",
            "item_8" => "State",
            "item_9" => "Amount"
        ];

        $report_arr = $tmp_report_data['report']['order_details'];
        $formatted_report_arr = array();
        foreach($report_arr as $report_key => $report_value){
            $formatted_report_arr[$report_key]['usr_name'] = $report_value['usr_name'];
            $formatted_report_arr[$report_key]['complete_date'] = $report_value['complete_date'];
            $formatted_report_arr[$report_key]['work_order_id'] = $report_value['work_order_id'];
            $formatted_report_arr[$report_key]['work_code'] = $report_value['work_code'];
            $formatted_report_arr[$report_key]['address'] = $report_value['address'];
            $formatted_report_arr[$report_key]['city'] = $report_value['city'];
            $formatted_report_arr[$report_key]['zip_code'] = $report_value['zip_code'];

            // $zip_state_sql = "SELECT zip_code, state FROM " . tbl_zip_state . " WHERE zip_code = '".$report_value['zip_code']."' LIMIT 0,1";
            // $zip_state_query = mysqli_query($this->db_con, $zip_state_sql);
            // $zip_state_arr = mysqli_fetch_assoc($zip_state_query);
            //$state = $zip_state_arr['state'];
           
            $state = $item_details->get_state_from_zip($report_value['zip_code']);
            $formatted_report_arr[$report_key]['state'] = $state;
            $formatted_report_arr[$report_key]['amount'] = $report_value['amount'];

        }
        // echo "Report array";
        // echo "<pre>"; print_r($report_arr); echo "</pre>";
        // echo "Report formatted array";

        // echo "<pre>"; print_r($formatted_report_arr); echo "</pre>"; die;
        fputcsv($file, $file_header);
        // foreach ($report_arr as $report_key => $report_val)
        // {
        //     if(!$report_val['usr_id']){
        //         unset($report_arr[$report_key]['usr_id']);
        //     }
        //     $zip_state_sql = "SELECT zip_code, state FROM " . tbl_zip_state . " WHERE zip_code = '".$report_val['zip_code']."' LIMIT 0,1";
        //     $zip_state_query = mysqli_query($this->db_con, $zip_state_sql);
        //     $zip_state_arr = mysqli_fetch_assoc($zip_state_query);
        //     $state = $zip_state_arr['state'];
        //     $report_arr[$report_key]['state'] = $state;
        // }
        foreach ($formatted_report_arr as $report_key => $report_val)
        {
            fputcsv( $file, array_values( $report_val ) );
            
        }
        
        if (fclose($file)) {
            return $tmp_file_name . ".csv";
        } else {
            return false;
        }
    }
}