<?php

if ( ! defined( 'ABSPATH' ) ) exit;

require_once plugin_dir_path( __DIR__ ).'/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

add_action('admin_init', 'dvs_report_download_report');
function dvs_report_download_report() {

	// DOWNLOAD REPORT
	if(isset($_POST['hxs_download_report'])) {

		$first_date = new DateTime('now');
		$first_date->modify('first day of this month');
		$first_date = $first_date->format('Y-m-d');

		$last_date = new DateTime('now');
		$last_date->modify('last day of this month');
		$last_date = $last_date->format('Y-m-d');

		$pre_hxs_date = $first_date . ' - ' . $last_date;

	    $hxs_date = $_POST['hxs_date'] ?? $pre_hxs_date;
	    $hxs_courier = $_POST['hxs_courier'] ?? $_GET['hxs_courier'] ?? 'All';
	    $hxs_city = $_POST['hxs_city'] ?? 'All';
	 
		$created_at_min = substr($hxs_date, 0, 10) . ' 00:00:00';
		$created_at_max = substr($hxs_date, -10) . ' 23:59:59';

	    $parameters[] = [
	        'key' => 'courier_datetime',
	        'value' => "'{$created_at_min}' AND '{$created_at_max}'",
	        'operator' => 'BETWEEN',
	    ];

		if($hxs_city && $hxs_city != "All") {
			$parameters[] = [
			    'key' => 'courier_destination',
			    'value' => $hxs_city,
			    'operator' => 'LIKE',
			];
		}

		if($hxs_courier && $hxs_courier != "All") {
			$parameters[] = [
			    'key' => 'courier_name',
			    'value' => $hxs_courier,
			    'operator' => 'LIKE',
			];
		}	

	    $bookings = new CourierBooking([
	        'shop_url' => get_home_url(),
	    ]);
	    $bookings->paginate_by = 10000000;
	    $parcels = $bookings->get($parameters);

		if (count($parcels) > 0) {

			// Initialize Spreadsheet
			$spreadsheet = new Spreadsheet();

			// Add Report data to the first sheet
			$sheetIndex = 0;
			$spreadsheet->setActiveSheetIndex($sheetIndex);
			$sheet = $spreadsheet->getActiveSheet();
			$sheet->setTitle('Report'); // Set title to 'Report'

			// Set up CSV file headers
			$headers = ['Date', 'Booked', 'Loadsheet', 'Shipped', 'Refused', 'Delivered', 'Returned', 'Cancelled'];

			// Add headers to the 'Report' sheet
			foreach ($headers as $key => $header) {
			    $sheet->setCellValueByColumnAndRow($key + 1, 1, $header);
			}

		    // Apply bold style to the headers
		    $boldStyle = [
		        'font' => [
		            'bold' => true,
		        ],
		    ];
		    $sheet->getStyle('A1:G1')->applyFromArray($boldStyle);

			// Initialize status counts array
			$status_counts = [];

			// Calculate status counts
			foreach ($parcels as $parcel) {
			    $status = $parcel->courier_status;
			    $courier_datetime = $parcel->courier_datetime;

			    // Check if courier_datetime is not null
			    if ($courier_datetime !== null) {
			        $date = date('Y-m-d', strtotime($courier_datetime));

			        if (!isset($status_counts[$date])) {
			            $status_counts[$date] = [
			                'active' => 0,
			                'loadsheet' => 0,
			                'shipped' => 0,
			                'refused' => 0,
			                'delivered' => 0,
			                'returned' => 0,
			                'cancelled' => 0,
			            ];
			        }

			        $status_counts[$date][$status]++;
			    }
			}

			// Initialize total counts
			$total_counts = [
			    'active' => 0,
			    'loadsheet' => 0,
			    'shipped' => 0,
			    'refused' => 0,
			    'delivered' => 0,
			    'returned' => 0,
			    'cancelled' => 0,
			];

			$rowCount = 2;

			// Extract month and year from $hxs_date
			list($start_date_string, $end_date_string) = explode(' - ', $hxs_date);
			list($start_year, $start_month) = explode('-', $start_date_string);
			$start_date = new DateTime("$start_year-$start_month-01");
			list($end_year, $end_month) = explode('-', $end_date_string);
			$end_date = new DateTime("$end_year-$end_month-" . date('t', strtotime($end_date_string)));

			$current_date = clone $start_date;
			while ($current_date <= $end_date) {
			    $date_str = $current_date->format('Y-m-d');
			    $dvs_date_str = $current_date->format('d/m/Y');

				// Check if the date exists in $status_counts before accessing it
				if (isset($status_counts[$date_str])) {
				    $data = [$dvs_date_str];
				    $countData = $status_counts[$date_str];
				    $totalCount = array_sum($countData);
				    if ($totalCount > 0) {
				        foreach ($countData as $status => $count) {
				        	$total_counts[$status] += $count;
				            if($count == 0 ) {
				            	$data[] = '';
				            }
				            else {	
				            	$data[] = $count;
				            }
				        }
				    }
				} else {
				    // If the date doesn't exist, create an array with the date only
				    $data = [$dvs_date_str];
				}

				// Set the data to the row
				foreach ($data as $key => $value) {
				    $sheet->setCellValueByColumnAndRow($key + 1, $rowCount, $value);
				}

				$rowCount++;
				$current_date->modify('+1 day');
			}


		    // Add total counts to the last row after all dates
		    $data = ['Total = ' . array_sum($total_counts)];
		    foreach ($total_counts as $count) {
		        $data[] = $count;
		    }

		    // Set the total counts row
		    foreach ($data as $key => $value) {
		        $sheet->setCellValueByColumnAndRow($key + 1, $rowCount, $value);
		    }

		   // Apply bold style to the total row
		    $boldStyle = [
		        'font' => [
		            'bold' => true,
		        ],
		    ];

		    $sheet->getStyle("A{$rowCount}:G{$rowCount}")->applyFromArray($boldStyle);


			// Add Parcels data to the 'Parcels' sheet
			$newSheet = $spreadsheet->createSheet();
			$newSheet->setTitle('Parcels'); // Set title to 'Parcels'

			// Set headers for the 'Parcels' sheet
			$headers = [
			    'Booked',
			    'Shipped',
			    'Received',
			    'Days',
			    'Order Number',
			    'Name',
			    'Phone',
			    'Email',
			    'Address',
			    'City',
			    'Order Total',
			    'Courier COD',
			    'Products',
			    'Courier Name',
			    'Courier Tracking',
			    'Tracking Status',
			    'Courier Status',
			];

			// Add headers to the 'Parcels' sheet
			foreach ($headers as $key => $header) {
			    $newSheet->setCellValueByColumnAndRow($key + 1, 1, $header);
			}

		    // Apply bold style to the headers
		    $boldStyle = [
		        'font' => [
		            'bold' => true,
		        ],
		    ];
		    $newSheet->getStyle('A1:Q1')->applyFromArray($boldStyle);


			// Add data to the 'Parcels' sheet
			$rowCount = 2;
			foreach ($parcels as $parcel) {
			    $dvs_courier_datetime = !empty($parcel->courier_datetime) ? date('d/m/Y', strtotime($parcel->courier_datetime)) : '';
			    $dvs_shipped_datetime = !empty($parcel->shipped_datetime) ? date('d/m/Y', strtotime($parcel->shipped_datetime)) : '';
			    $dvs_received_datetime = !empty($parcel->received_datetime) ? date('d/m/Y', strtotime($parcel->received_datetime)) : '';
			    $dvs_parcel_days = get_post_meta($parcel->order_id, '_dvs_parcel_days', true);

			    // Add data to each column
			    $columnsData = [
			        $dvs_courier_datetime,
			        $dvs_shipped_datetime,
			        $dvs_received_datetime,
			        $dvs_parcel_days,
			        $parcel->order_name,
			        $parcel->consignee_name,
			        $parcel->consignee_phone,
			        $parcel->consignee_email,
			        $parcel->consignee_address,
			        $parcel->courier_destination,
			        (int)$parcel->order_total,
			        (int)$parcel->order_amount,
			        $parcel->order_products,
			        $parcel->courier_name,
			        $parcel->courier_tracking,
			        $parcel->courier_delivery,
			        $parcel->courier_status,
			    ];

			    foreach ($columnsData as $key => $data) {
			        $newSheet->setCellValueByColumnAndRow($key + 1, $rowCount, $data);
			    }

			    $rowCount++;
			}

			// Create separate sheets for each courier status
			$courierStatuses = ['active', 'loadsheet', 'shipped', 'refused', 'delivered', 'returned', 'cancelled'];
			foreach ($courierStatuses as $status) {
			    // Filter parcels based on the status
			    $filteredParcels = array_filter($parcels, function ($parcel) use ($status) {
			        return $parcel->courier_status === $status;
			    });

			    // Create a new worksheet for the status
			    $newSheet = $spreadsheet->createSheet();
			    if ($status == 'active') {
			        $newSheet->setTitle(ucfirst('booked')); // Set title to ucfirst of status
			    } else {
			        $newSheet->setTitle(ucfirst($status)); // Set title to ucfirst of status
			    }

			    // Add headers to the sheet
			    foreach ($headers as $key => $header) {
			        $newSheet->setCellValueByColumnAndRow($key + 1, 1, $header);
			    }

			    // Apply bold style to the headers
			    $boldStyle = [
			        'font' => [
			            'bold' => true,
			        ],
			    ];
			    $newSheet->getStyle('A1:Q1')->applyFromArray($boldStyle);

			    
			    // Add data to the sheet
			    $rowCount = 2;
			    foreach ($filteredParcels as $parcel) {

				    $dvs_courier_datetime = !empty($parcel->courier_datetime) ? date('d/m/Y', strtotime($parcel->courier_datetime)) : '';
				    $dvs_shipped_datetime = !empty($parcel->shipped_datetime) ? date('d/m/Y', strtotime($parcel->shipped_datetime)) : '';
				    $dvs_received_datetime = !empty($parcel->received_datetime) ? date('d/m/Y', strtotime($parcel->received_datetime)) : '';
				    $dvs_parcel_days = get_post_meta($parcel->order_id, '_dvs_parcel_days', true);

			        $columnsData = [
				        $dvs_courier_datetime,
				        $dvs_shipped_datetime,
				        $dvs_received_datetime,
				        $dvs_parcel_days,
			            $parcel->order_name,
			            $parcel->consignee_name,
			            $parcel->consignee_phone,
			            $parcel->consignee_email,
			            $parcel->consignee_address,
			            $parcel->courier_destination,
			            (int)$parcel->order_total,
			            (int)$parcel->order_amount,
			            $parcel->order_products,
			            $parcel->courier_name,
			            $parcel->courier_tracking,
			            $parcel->courier_delivery,
			            $parcel->courier_status,
			        ];
			        foreach ($columnsData as $key => $data) {
			            $newSheet->setCellValueByColumnAndRow($key + 1, $rowCount, $data);
			        }

			        $rowCount++;
			    }
			}

			// Set the first sheet as active at the end
			$spreadsheet->setActiveSheetIndex($sheetIndex);

			// Create a new Xlsx object and save the spreadsheet to a file
			$writer = new Xlsx($spreadsheet);
			$filename = 'ucp-report.xlsx';
			$writer->save($filename);

			// Output the file for download
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			header('Content-Disposition: attachment;filename="' . $filename . '"');
			header('Cache-Control: max-age=0');
			$writer->save('php://output');
			exit();
		}
		else{
			$error = true;
		}
	}
}

function dvs_courier_report_page() {	
    
    global $couriers;

	$first_date = new DateTime('now');
	$first_date->modify('first day of this month');
	$first_date = $first_date->format('Y-m-d');

	$last_date = new DateTime('now');
	$last_date->modify('last day of this month');
	$last_date = $last_date->format('Y-m-d');

	$pre_hxs_date = $first_date . ' - ' . $last_date;

    $hxs_date = $_POST['hxs_date'] ?? $pre_hxs_date;
    $hxs_courier = $_POST['hxs_courier'] ?? $_GET['hxs_courier'] ?? 'All';
    $hxs_city = $_POST['hxs_city'] ?? 'All';    
 
	$created_at_min = substr($hxs_date, 0, 10) . ' 00:00:00';
	$created_at_max = substr($hxs_date, -10) . ' 23:59:59';

    $parameters[] = [
        'key' => 'courier_datetime',
        'value' => "'{$created_at_min}' AND '{$created_at_max}'",
        'operator' => 'BETWEEN',
    ];

	if($hxs_city && $hxs_city != "All") {
		$parameters[] = [
		    'key' => 'courier_destination',
		    'value' => $hxs_city,
		    'operator' => 'LIKE',
		];
	}

	if($hxs_courier && $hxs_courier != "All") {
		$parameters[] = [
		    'key' => 'courier_name',
		    'value' => $hxs_courier,
		    'operator' => 'LIKE',
		];
	}	

    $bookings = new CourierBooking([
        'shop_url' => get_home_url(),
    ]);
    $bookings->paginate_by = 10000000;
    $parcels = $bookings->get($parameters);

?>

<head>
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-T3c6CoIi6uLrA9TneNEoa7RxnatzjcDSCmG1MXxSR1GAsXEV/Dwwykc2MPK8M2HN" crossorigin="anonymous">
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css" />

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-C6RzsynM9kWDrMNeT87bh95OGNyZPhcTNXj1NW7RuBCsyN/o0jlpcV8Qyq46cDfL" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    <script type="text/javascript" src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js"></script>
    <style>
    	input[type=text] {
    		font-size: 13px;
    	}
        .select2-selection {
            height: 40px !important;
            border-color: #8c8f94 !important;
        }
        .select2-container--default .select2-selection--single {
            border-radius: 0px !important;
        }
        .select2-container .select2-selection--single .select2-selection__rendered {
            line-height: 37px !important;
            font-size: 13px;
        }
        .select2-container--default .select2-selection--single .select2-selection__rendered {
            color: #595c5e !important;
        }
        .select2-container--default .select2-selection--single .select2-selection__arrow {
            height: 37px !important;
        }
		.select2-results__option {
			font-size: 12px !important;
			padding: 5px 10px !important;
    		margin-bottom: 0px !important;
		}        
        tbody td {
        	color: #333333 !important;
        }
		.td-bold {
			font-weight: bold;
		}           
        .dvs-ucp-table-head td {
        	background-color: #f1f1f1;
        	font-size: 12px;
        }        
		.dvs-ucp-analytics {
			font-size: 13px;
			box-shadow: 0 1px 1px rgba(0,0,0,.04)
		}
		.dvs-ucp-analytics th {
		    padding-left: 15px;
		    background-color: #f1f1f1;
		    font-weight: 500;
		}
		.hxs-stats-city {
			font-size: 14px;
		}
		.hxs-stats-parcel {
			text-align: center;
		}
		.hxs-stats-parcel a {
			color: inherit;
			text-decoration: none;
		}
		.hxs-stats-parcel a:hover {
			text-decoration: underline;
		}
		.hxs-stats-cod {
			text-align: center;
		}
		.hxs-stats-percentage {
			text-align: center;
			font-weight: bold;
		}
		.hxs-count-color {
			color: #333333;
			text-decoration: none;
		}

    </style>
</head>

<body>
    <div class="container-fluid mt-4 mb-4">
        <div class="row">
            <div class="col-2">
            </div>
            <div id="hxs-stats-filters" class="col-8">
                <h4 class="text-center">Courier Report</h4>
                <p class="text-center mb-4">Note: You can select the date range of same month only</p>
                
                <form class="input-group" method="post" action="" id="hxs-stats-form" >

                    <input class="form-control" type="text" id="hxs_date" name="hxs_date" value="<?= $hxs_date ?>" autocomplete="off" placeholder="Date Range">

                    <select id="hxs_courier" name="hxs_courier" class="form-control">
                        <?php
                        	unset($couriers["all"]);
                        	echo '<option value="All">All Couriers</option>';
                            foreach ($couriers as $courier) {
								$selected = ($hxs_courier == $courier) ? 'selected' : '';
								echo '<option ' . $selected . '>' . $courier . '</option>';                                 
                            }
                        ?>
                    </select>

                    <select id="hxs_city" name="hxs_city" class="form-control">
                        <?php
                            include_once('dvs-ucp-cities.php');
                            echo '<option value="All">All Citites</option>';
                            foreach ($cities as $city) {
								$selected = ($hxs_city == $city) ? 'selected' : '';
								echo '<option ' . $selected . '>' . $city . '</option>';                    	
                            }
                            if(empty($selected) && $hxs_city != "All") {
                            	echo '<option selected>' . $hxs_city . '</option>';
                            }
                        ?>
                    </select>

                    <input class="form-control" type="hidden" name="hxs_analytics_search" value="true">
                    <button class="btn btn-success" name="hxs_stats_btn" type="submit">Search</button>
					<?php
					if (count($parcels) > 0) { ?>
						<button class="btn btn-outline-success" id="hxs_download_report" name="hxs_download_report" type="submit" style="margin-left: 15px;border-radius: 5px;">Download Report</button>
					<?php
					}
					?>                    
                </form>
            </div>       
        </div>    
    </div>    

	<div class="container-fluid">
		<?php
		if(count($parcels) == 0) {
			echo '<div class="row"><div class="col-2"></div><div class="col-8"><div class="alert alert-warning" role="alert">Sorry: No shipment found</div></div></div>';
		}
		else {
			// Find the key for the given value
			$courier_key = array_search($hxs_courier, $couriers);
			if (empty($courier_key)) {
			    $courier_key = 'all';
			}
			$dvs_ucp_admin_url = get_admin_url(null, 'admin.php?page=dvs_ucp_');

			$status_counts = [];

			foreach ($parcels as $parcel) {
				$status = $parcel->courier_status;
				$courier_datetime = $parcel->courier_datetime;

			    // Check if courier_datetime is not null
			    if ($courier_datetime !== null) {
			        $date = date('Y-m-d', strtotime($courier_datetime));

			        if (!isset($status_counts[$date])) {
			            $status_counts[$date] = [
			                'active' => 0,
			                'loadsheet' => 0,
			                'shipped' => 0,
			                'refused' => 0,
			                'delivered' => 0,
			                'returned' => 0,
			                'cancelled' => 0,
			            ];
			        }

			        $status_counts[$date][$status]++;
			    }
			}

			echo '<div class="row">';
			echo '<div class="col-3"></div>';
			echo '<div class="col-6 mb-2" style="font-size: 12px">';
			echo '<div class="table-responsive">';
			echo '<table class="table table-bordered table-striped table-hover">';
			echo '<thead class="thead-dark text-center">';
			echo '<tr><th>Date</th><th>Booked</th><th>Loadsheet</th><th>Shipped</th><th>Refused</th><th>Delivered</th><th>Returned</th><th>Cancelled</th></tr>';
			echo '</thead>';
			echo '<tbody>';


			// Extract month and year from $hxs_date
			list($start_date_string, $end_date_string) = explode(' - ', $hxs_date);
			list($start_year, $start_month) = explode('-', $start_date_string);
			$start_date = new DateTime("$start_year-$start_month-01");
			list($end_year, $end_month) = explode('-', $end_date_string);
			$end_date = new DateTime("$end_year-$end_month-" . date('t', strtotime($end_date_string)));


			// Initialize total counts
			$total_counts = [
			    'active' => 0,
			    'loadsheet' => 0,
			    'shipped' => 0,
			    'refused' => 0,
			    'delivered' => 0,
			    'returned' => 0,
			    'cancelled' => 0,
			];

			// Iterate through each date of the month
			$current_date = clone $start_date;
			while ($current_date <= $end_date) {
			    $date_str = $current_date->format('Y-m-d');
			    $date_display = $current_date->format('d/m/Y');
			    echo '<tr class="text-center">';
			    echo '<td>' . $date_display . '</td>';
			    // Check if the date exists in $status_counts before accessing it
			    if (isset($status_counts[$date_str])) {
			        foreach ($status_counts[$date_str] as $status => $count) {
						echo '<td>' . ($count > 0 ? '<a href="'.$dvs_ucp_admin_url.$courier_key.'_logs&hxs_search_btn&hxs_date='.$date_str.'&hxs_status='.$status.'" target="_blank" class="hxs-count-color">'.$count.'</a>' : '') . '</td>';
			            // echo '<td>' . ($count == 0 ? '' : $count) . '</td>';
			            $total_counts[$status] += $count;
			        }
			    } else {
			        for ($i = 0; $i < 7; $i++) {
			            echo '<td></td>';
			        }
			    }
			    echo '</tr>';
			    $current_date->modify('+1 day');
			}

			echo '</tbody>';
			echo '<tfoot class="text-center">';
			echo '<tr><th></th><th>Booked</th><th>Loadsheet</th><th>Shipped</th><th>Refused</th><th>Delivered</th><th>Returned</th><th>Cancelled</th></tr>';
			echo '<tr>';
			echo '<td>Total = ' . array_sum($total_counts) . '</td>';
			// Display total counts
			foreach ($total_counts as $count) {
			    echo '<td>' . $count . '</td>';
			}
			echo '</tr>';
			echo '</tfoot>';
			echo '</table>';
			echo '</div>';
			echo '</div>';
			echo '</div>';
		}
	echo '</div>';

	?>

	<script>
	    $(function() {
	        $('input[name="hxs_date"]').daterangepicker({
	        	autoUpdateInput: <?php echo $hxs_date ?>,
	            showDropdowns: true,
	            minViewMode: 'months', // Set minimum view mode to months
	            singleDatePicker: false,
	            locale: {
	            	cancelLabel: 'Clear',
	                format: 'YYYY-MM-DD'
	            }
	        });
	        
	      	$('input[name="hxs_date"]').on('apply.daterangepicker', function(ev, picker) {
	            var startOfMonth = moment(picker.startDate).startOf('month');
	            var endOfMonth = moment(picker.startDate).endOf('month');
	            $(this).val(startOfMonth.format('YYYY-MM-DD') + ' - ' + endOfMonth.format('YYYY-MM-DD'));
	        });

	        $('input[name="hxs_date"]').on('cancel.daterangepicker', function(ev, picker) {
	            $(this).val('');
	        });

	    });
	</script>

    <script type="text/javascript">
        $(document).ready(function() {
            $('#hxs_courier').select2({
            });
        });
    </script>

    <script type="text/javascript">
        $(document).ready(function() {
            $('#hxs_city').select2({
                tags: true
            });
        });
    </script> 

</body>

<br>
<a href="https://www.youtube.com/watch?v=UP_3CZmyl4k&t=992s" target="blank" class="woocommerce-Button button">Video: How to use Courier Reports</a>
<br>
<br>
<a href="https://wa.me/9203038518000" target="blank" class="woocommerce-Button button">Get Support</a>
<br>

<?php
}