<?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 ReportNew
{
    private $db_con;

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

    public function report_list($input_data): array
    {

        $output_message = new OutputMessage();

        $report_details_data = $this->report_details($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;
    }

    function formatDollarAmount($amount, $sign = true) {
        $absAmount = number_format(abs($amount), 2);
        $symbol =  $sign?'$ ':'';
        return ($amount < 0 ? '-' : '') . $symbol . $absAmount;
    }

    function cleanAmount($amount) {
        $amount = str_replace('$', '', $amount);
        $amount = str_replace(',', '', $amount);
        return (float)$amount;
    }

    private function report_details($input_data)
    {
        // ini_set('display_errors', 1);
        // ini_set('display_startup_errors', 1);
        // error_reporting(E_ALL);
        $item_details = new ItemDetails();
        $beautify_arr = new ItemBeautify();

        
        $start_date = $input_data->usr_report_start_date;
        $end_date = $input_data->usr_report_end_date;

        if ($input_data->usr_id == "all") {
            $report_data = [];
            // All users
            $get_inspector_sql = "  SELECT *
                                    FROM " . tbl_usr . "
                                    WHERE   usr_type in ('inspector', 'agent')
                                            AND status='true'
                                    ORDER BY uid DESC";

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

            while ($inspector = mysqli_fetch_assoc($get_inspector_query)) {
                $usr_type = $inspector["usr_type"];
                $usr_id = $inspector["uid"];

                $total_debit = 0;
                $total_credit = 0;
                $total_amount = 0;
                $total_order_count = 0;

                if($usr_type == "agent"){
                    // handle salary
                    $salary = $inspector['salary'];

                    //if start date is older then create date of agent then set start date to create date
                    $date_time = $inspector['date_time'];
                    
                    $_start_date = date('Y-m-d', strtotime($start_date));
                    $_end_date = date('Y-m-d', strtotime($end_date));

                    if($date_time > $start_date){
                        $_start_date = date('Y-m-d', strtotime($date_time));
                    }

                    if($date_time > $end_date){
                        $_end_date = date('Y-m-d', strtotime($date_time));
                    }


                    $_start_date = new DateTime($_start_date);
                    $_end_date = new DateTime($_end_date);
                    $interval = $_start_date->diff($_end_date);
                    $weeks = $interval->days / 7;
                    $weeks = ceil($weeks);
                    // echo $weeks;
                    $total_amount = $weeks * $salary;

                    if($total_amount < 0){
                        $total_amount = 0;
                    }


                }else{
                    // handle commission
                    $alias = implode("','", explode(",", strtolower($inspector["alias"])));
                    $get_order_sql = "  SELECT SUM(b.amount) as total_price_amount,
                                                count(a.id) as total_order_count
                                        FROM " . tbl_work_order . " a
                                        JOIN " . tbl_price_list . " b ON a.work_code=b.work_code AND a.zip_code=b.zip_code AND b.status='true'
                                        WHERE   LOWER(a.alias) IN ('$alias') AND a.status='true'
                                                AND DATE(completion_date) between '$start_date' AND '$end_date'";
                    $get_order_query = mysqli_query($this->db_con, $get_order_sql) or (die($this->db_con));
                    $get_order_arr = mysqli_fetch_assoc($get_order_query);
                    $total_amount = $get_order_arr["total_price_amount"];       
                    $total_order_count = $get_order_arr["total_order_count"];         
                }
                
                
                $get_dc_sql = " SELECT * 
                                FROM " . tbl_debit_credit . "
                                WHERE   usr_id='$usr_id'
                                        AND status='true'
                                         AND DATE(dc_date) between '$start_date' AND '$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") {
                        $total_credit += $get_dc_arr["dc_amount"];
                    } else {
                        $total_debit += $get_dc_arr["dc_amount"];
                    }
                }
    
                $final_amount = ($total_amount + $total_credit) - $total_debit;
                $user_data = array(
                    "usr_id" => $usr_id,
                    "usr_name" => strtoupper($inspector["first_name"] . " " . $inspector["last_name"]),
                    "usr_type" => strtoupper($usr_type),
                    "amount" =>  $this->formatDollarAmount($final_amount),
                    "tot_debit" => $this->formatDollarAmount($total_debit),
                    "tot_credit" => $this->formatDollarAmount($total_credit),
                    "tot_order" => $total_order_count,
                );
                

                $report_data[] = $user_data;
                
            }
    
            return $report_data;

        }else{
            $user_data = [];

            // Single user
            $usr_id = $input_data->usr_id;
            $get_inspector_sql = "  SELECT *
                                    FROM " . tbl_usr . "
                                    WHERE   uid='$usr_id'
                                            AND status='true'
                                    ORDER BY uid DESC";

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

            $usr_id = $inspector["uid"];
            $usr_type = $inspector["usr_type"];

            $full_name = strtoupper($inspector["first_name"] . " " . $inspector["last_name"]);
            $user_data['type'] = $usr_type;
            $user_data['usr_name'] = $full_name;

            $total_amount = 0;
            $total_debit = 0;
            $total_credit = 0;

            $get_dc_sql = " SELECT * 
                            FROM " . tbl_debit_credit . "
                            WHERE   usr_id='$usr_id'
                                    AND status='true'
                                    AND DATE(dc_date) between '$start_date' AND '$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") {
                    $total_credit += $get_dc_arr["dc_amount"];
                } else {
                    $total_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" => $this->formatDollarAmount($get_dc_arr["dc_amount"]),
                    "type" => $get_dc_arr["dc_type"]
                ];

                $user_data["dc_details"][] = $tmp_dc_data;
            }

            if($usr_type == "agent"){
                // handle salary
                $salary = $inspector['salary'];

                //calculate weeks in start and end date
                $_start_date = date('Y-m-d', strtotime($start_date));
                $_end_date = date('Y-m-d', strtotime($end_date));
                $date_time = $inspector['date_time'];

                if($date_time > $start_date){
                    $_start_date = date('Y-m-d', strtotime($date_time));
                }

                if($date_time > $end_date){
                    $_end_date = date('Y-m-d', strtotime($date_time));
                }


                $_start_date = new DateTime($_start_date);
                $_end_date = new DateTime($_end_date);
                $interval = $_start_date->diff($_end_date);
                $weeks = $interval->days / 7;
                $weeks = ceil($weeks);
                $total_amount = $weeks * $salary;

                $agent_data = array(
                    "usr_id" => $usr_id,
                    "usr_name" => $full_name,
                    "usr_type" => strtoupper($usr_type),
                    "amount" => $this->formatDollarAmount(($total_amount + $total_credit) - $total_debit),
                    "weeks_amount" => $this->formatDollarAmount($total_amount),
                    "week" => ' ',
                );
                $user_data['details'][] = $agent_data;
                $user_data['total_weeks'] = $weeks;
                $user_data['total_amount'] = $total_amount;
            }else{
                // handle commission
                $alias = implode("','", explode(",", strtolower($inspector["alias"])));
                $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.state as report_state,
                                            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 AND b.status = 'true'
                                    WHERE   LOWER(a.alias) IN ('$alias')
                                            AND DATE(completion_date) between '$start_date' AND '$end_date'  AND a.status = 'true'
                                    ORDER BY completion_date DESC";

                $get_order_query = mysqli_query($this->db_con, $get_order_sql) or (die($this->db_con));
                $total_order_count = mysqli_num_rows($get_order_query);
                $user_data["total_order"] = $total_order_count;
                while ($order = mysqli_fetch_assoc($get_order_query)) {
                    $order_data = [
                        "usr_id" => $usr_id,
                        "usr_name" => strtoupper($full_name . " (" . $order["report_alias"] . ")"),
                        "complete_date" => date('m/d/Y', strtotime($order["report_completion_date"])),
                        "work_order_id" => strtoupper($order["report_work_order_id"]),
                        "amount" => $order["report_price_amount"] > 0? $this->formatDollarAmount($order["report_price_amount"]): 'Not Paid',
                        "work_code" => strtoupper($order["report_work_code"]),
                        "address" => strtoupper($order["report_address"]),
                        "city" => strtoupper($order["report_city"]),
                        "zip_code" => strtoupper($order["report_zip_code"]),
                        "state" => strtoupper($order["report_state"]),
                    ];

                    $user_data['details'][] = $order_data;

                    if ($order["report_price_amount"] > 0) {
                        $total_amount += $order["report_price_amount"];
                    }
                }
            }

       

            $user_data["total_amount"] =  ($total_amount + $total_credit) - $total_debit;
            $user_data["total_debit"] =  $total_debit;
            $user_data["total_credit"] =  $total_credit;

            return $user_data;
        }

    }


    public function report_download($input_data)
    {

        // error_reporting(E_ALL);
        // ini_set('display_errors', '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_details($input_data);

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

        $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["usr_name"] . " ";
            $tmp_file_name .= implode("-", explode(" ", strtolower($tmp_report_data["usr_name"])));
            $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_single_report = true;
        } else {
            $tmp_title = "all user";
            $tmp_file_name .= "all-inspector";
        }

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

        if(isset($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");
            }    
        }
        
        return $api_output;
    }

    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) {
            if($file_data["type"] == "inspector"){
                $report_template = file_get_contents("lib/template/report/template_report_single.html");
            }else{
                $report_template = file_get_contents("lib/template/report/template_report_agent.html");
            }
        } else {
            $report_template = file_get_contents("lib/template/report/template_report_all.html");
        }

        if ($single_report) {
            // echo "<pre>"; print_r($file_data); echo "</pre>"; die;
            if($file_data["type"] == "inspector"){
                foreach ($file_data["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 = $single_order_data["amount"];
                    }
          
                    $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>' . $single_order_data["state"] . '</td>              
                           <td>' . $tmp_amount . '</td>
                       </tr>';
                }
                
                $report_template = str_replace("{{total_order}}", $file_data["total_order"], $report_template);

            }else{
                foreach ($file_data["details"] as $order) {
                    $tmp_amount = $order["weeks_amount"];
                    $usr_name = $order["usr_name"];
                    $week = $order["week"];
                    $report_order_lst_data .= /** @lang text */
                        "<tr>
                           <td>$usr_name</td>        
                           <td>$tmp_amount</td>
                       </tr>";
                }
                
                $report_template = str_replace("{{total_weeks}}", $file_data["total_weeks"], $report_template);
            }

            if(isset($file_data["dc_details"])){
                foreach ($file_data["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["total_amount"] - $file_data["total_credit"]) + $file_data["total_debit"]);
            $report_template = str_replace("{{order_lst}}", $report_order_lst_data, $report_template);
            $report_template = str_replace("{{sub_total_price}}", $this->formatDollarAmount($tmp_sub_total, false), $report_template);
            $report_template = str_replace("{{debit_credit_lst}}", $report_debit_credit_lst_data, $report_template);
            $tmp_tot_amount = $file_data["total_amount"];

        } else {
            foreach ($file_data as $user) {
                if ($user["amount"] == "Not Found" || $user["amount"] == "Not Paid") {
                    $tmp_amount = strtoupper($user["amount"]);
                } else {
                    $tmp_amount = $user["amount"];
                }

                $usr_name = $user["usr_name"];
                $usr_type = $user["usr_type"];

                if(strtolower($usr_type) == "inspector"){
                    $image = '<img style="width: 10px" src="../../files/images/inspector.png" alt="logo"/>';
                }else{
                    $image = '<img style="width: 8px" src="../../files/images/agent.png" alt="logo"/>';
                }

                $report_inspector_lst_data .=
                    "<tr>
                       <td>$image $usr_name</td>
                       <td>$tmp_amount</td>
                   </tr>";
                $tmp_tot_amount += $this->cleanAmount($user["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}}", $this->formatDollarAmount($tmp_tot_amount, false), $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 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();
    
        if ($tmp_single_report) {
            $type = $tmp_report_data["type"];
    
            if ($type === "inspector") {
                $file_header = [
                    "User (Inspector)",
                    "Date",
                    "Order #",
                    "Work ID",
                    "Address",
                    "City",
                    "Zip Code",
                    "State",
                    "Amount"
                ];
                fputcsv($file, $file_header);
    
                foreach ($tmp_report_data["details"] as $row) {
                    $state = $item_details->get_state_from_zip($row["zip_code"]);
    
                    // Format amount
                    if ($row["amount"] === "Not Found" || $row["amount"] === "Not Paid") {
                        $amount = strtoupper($row["amount"]);
                    } else {
                        $amount = $row["amount"];
                    }
    
                    $row_data = [
                        $row["usr_name"],
                        $row["complete_date"],
                        $row["work_order_id"],
                        $row["work_code"],
                        strtoupper($row["address"]),
                        $row["city"],
                        $row["zip_code"],
                        $state,
                        $amount
                    ];
                    fputcsv($file, $row_data);
                }
    
            } else {
                $file_header = [
                    "User (Agent)",
                    "Amount"
                ];
                fputcsv($file, $file_header);
    
                foreach ($tmp_report_data["details"] as $row) {
                    $amount = $row["amount"];
                    $row_data = [
                        $row["usr_name"] . " (" . $row["week"] . ")",
                        $amount
                    ];
                    fputcsv($file, $row_data);
                }
            }
    
            if (!empty($tmp_report_data["dc_details"])) {
                fputcsv($file, []); // Empty row for spacing
                fputcsv($file, ["Date (Comment) - Amount"]);
    
                foreach ($tmp_report_data["dc_details"] as $dc) {
                    $sign = $dc["type"] === "debit" ? "-" : "+";
                    $amount = $dc["amount"];
                    $line = $dc["date"] . " (" . $dc["comment"] . ") " . $sign . " " . $amount;
                    fputcsv($file, [$line]);
                }
            }
    
        } else {
            $file_header = [
                "User (Inspector/Agent)",
                "Amount"
            ];
            fputcsv($file, $file_header);
    
            foreach ($tmp_report_data as $row) {
                $amount = ($row["amount"] === "Not Found" || $row["amount"] === "Not Paid") 
                    ? strtoupper($row["amount"]) 
                    : $row["amount"];
    
                $row_data = [
                    $row["usr_name"] . " (" . $row["usr_type"] . ")",
                    $amount
                ];
                fputcsv($file, $row_data);
            }
        }
    
        fclose($file);
        return $tmp_file_name . ".csv";
    }

    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_details($input_data);

        $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 . "'");
            //$api_output = $output_message->successOutput(report_via_url . $report_file, "Report download successfully");

            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_via_url . $report_file, "Report send successfully");
                } else {
                    $api_output = $output_message->errorOutput("Message send ERROR");
                }
            } else {
                $api_output = $output_message->errorOutput("Report send ERROR");
            }
        }
        return $api_output;
    }
    
}