File manager

File manager - Edit - /home/autoph/public_html/connectv1/print_finance_report03032025.php

Back
<?php ini_set('max_execution_time', '0'); set_time_limit(0); //same code above ini_set('memory_limit','-1'); date_default_timezone_set('Asia/Manila'); include('cfg/db.php'); include_once("api/controllers/utility.php"); if(!isset($_SESSION['user']['id'])){ header('location: home.php'); return; } $current_user = $_SESSION['user']['id']; if(!(isset($global_action_permissions['5']['46']) && in_array("2", $global_action_permissions['5']['46']))){ echo "You are not allowed to do this action."; exit; } $generated_by = ""; $user_role = $db->select("SELECT employee_role_id FROM employee_account WHERE id = '$current_user'"); if($user_role == 45 \|\| $user_role == 1){ $generated_by = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name']; } else { $generated_by = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'] . " (" . $_SESSION['user']['company_code'] . ")"; } $utility = new Utility(); $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ? $_SESSION['user']['company_permissions_implode'] : '-1' ; // GLOBAL VARIABLES $company = 0; $dealer = 0; $brand = 0; $model = 0; $variant = 0; $filter_date = 0; $start_date = ""; $end_date = ""; $remaining_days = 0; $inactive = 0; $company_q = ""; $dealer_q = ""; $brand_q = ""; $model_q = ""; $variant_q = ""; $filter_date_q = ""; $remaining_days_q = ""; $inactive_q = ""; $filter_header_date = ""; $filter_date_filename = ""; $uuid = $db->select("SELECT UUID()"); $rem1 = ""; $mask = $_REQUEST['mask']; if(isset($_REQUEST['filter_date']) && $_REQUEST['filter_date'] != 0){ // ex: 2021-10-01@2021-10-08 $filter_date = $db->escape(trim($_REQUEST['filter_date'])); $start_date = substr($filter_date, 0, 10); $end_date = substr($filter_date, 11, 10); $diff = date_diff(date_create($start_date), date_create($end_date)); // compute date interval $interval = (int)$diff->format("%a"); if($interval == 0){ // $filter_header_date = substr($utility->remove_non_numeric($start_date), 2, 6); $filter_header_date = $db->select("SELECT DATE_FORMAT('".$start_date."', '%b %d, %Y')"); $filter_date_filename = $utility->remove_non_numeric($start_date); } else { $filter_header_date = $db->select("SELECT DATE_FORMAT('".$start_date."', '%b %d, %Y')") . " to " . $db->select("SELECT DATE_FORMAT('".$end_date."', '%b %d, %Y')"); $filter_date_filename = $utility->remove_non_numeric($start_date) . "-" . $utility->remove_non_numeric($end_date);; } $filter_date_q = " AND f.policy_end_date BETWEEN '$start_date' AND '$end_date' "; } if(isset($_REQUEST['company']) && $_REQUEST['company'] != 0){ $company = $db->escape($_REQUEST['company']); $company_q = " AND f.company_id = '$company' "; } else { $company_q = " AND f.company_id IN(" . $granted_company_ids . ")"; } if(isset($_REQUEST['dealer']) && $_REQUEST['dealer'] != 0){ $dealer = $db->escape($_REQUEST['dealer']); $dealer_q = " AND f.dealer_id = '$dealer' "; } if(isset($_REQUEST['brand']) && $_REQUEST['brand'] != 0){ $brand = $db->escape($_REQUEST['brand']); $brand_q = " AND t1.brand_id = '$brand' "; } if(isset($_REQUEST['model']) && $_REQUEST['model'] != 0){ $model = $db->escape($_REQUEST['model']); $model_q = " AND t1.model_id = '$model' "; } if(isset($_REQUEST['variant']) && $_REQUEST['variant'] != 0){ $variant = $db->escape($_REQUEST['variant']); $variant_q = " AND t1.variant_id = '$variant' "; } if(isset($_REQUEST['rem_day']) && $_REQUEST['rem_day'] != 0){ $remaining_days = $db->escape($_REQUEST['rem_day']); $rem_day = array(); $rem_day = explode(",", $remaining_days); $rem1 = $rem_day[1]; $remaining_start_date = $db->select("SELECT DATE_ADD(CURDATE(), INTERVAL ".$rem_day[0]." DAY)"); $remaining_end_date = $db->select("SELECT DATE_ADD(CURDATE(), INTERVAL ".$rem_day[1]." DAY)"); $remaining_days_q = " AND f.policy_end_date BETWEEN '$remaining_start_date' AND '$remaining_end_date' "; } if(isset($_GET['inactive']) && $_GET['inactive'] != 0){ $inactive = $db->escape($_GET['inactive']); $current_date = $db->select("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS today"); $sub_date = $db->select("SELECT DATE_SUB(CURDATE(), INTERVAL " . $inactive . " DAY) AS day;"); if($inactive == 1){ // All inactive $inactive_q = " HAVING max_year <= '$current_date'"; } else { $inactive_q = " HAVING max_year <= '$sub_date'"; // $filter_date = " AND f.policy_end_date < '$sub_date' "; } } $count = " COUNT() "; $fields = ""; if($mask == 0){ if($inactive != 0){ $fields = " MAX(f.policy_end_date) AS max_year, f.id, f.customer_fullname, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.code AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date "; } else { $fields = " f.id, f.customer_fullname, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.code AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date "; } } else { if($inactive != 0){ $fields = " MAX(f.policy_end_date) AS max_year, f.id, f.customer_fullname, f.customer_mobile_number, f.customer_email, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.code AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date "; } else { $fields = " f.id, f.customer_fullname, f.customer_mobile_number, f.customer_email, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.code AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date "; } } // $fields = " t1.id, t1.customer_fullname, t1.plate_number, t1.conduction_sticker, t1.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(t1.policy_end_date, '%b %d, %Y') AS policy_end_date, sb.name AS brand, scm.name AS model "; $query_print = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE 1 AND f.status = 1 " . $filter_date_q. $company_q . $dealer_q . $remaining_days_q . " GROUP BY f.plate_cs_number1, f.plate_cs_number2 " . $inactive_q . " ORDER BY f.policy_end_date DESC"; // echo sprintf($query_print, $fields); return; $fetch_print_finance_report_list = $db->sql_query(sprintf($query_print, $fields)); $fetch_print_finance_report_count = $db->select("SELECT COUNT() FROM (".sprintf($query_print, $fields).") AS count"); $data_to_insert = array(); $company_arr = array(); $filename_arr = array(); foreach($fetch_print_finance_report_list AS $row){ $company_item = $row['company']; if(!(in_array($company_item, $company_arr))){ $uuid = $db->select("SELECT UUID()"); array_push($company_arr, $company_item); array_push($filename_arr, $uuid); } } $pdf_file_name = strtr($db->cipher($uuid, true), "+/=", "-_~" ).'.'.'pdf'; // $mask == 0 ? $col_width = array(10, 97, 30, 20, 42) : $col_width = array(10, 47, 27, 62, 17, 11, 23); // $mask == 0 ? $col_header = array('No.', 'Name', 'Plate/CS', 'Dealer', 'Policy End') : $col_header = array('No.', 'Name', 'Mobile', 'Email', 'Plate/CS', 'Dealer', 'Policy End'); $mask == 0 ? $col_width = array(10, 97, 30, 20, 42,47) : $col_width = array(10, 47, 27, 40, 17, 11, 23,22); $mask == 0 ? $col_header = array('No.', 'Name', 'Plate/CS', 'Dealer', 'Policy End','Policy Number') : $col_header = array('No.', 'Name', 'Mobile', 'Email', 'Plate/CS', 'Dealer', 'Policy End','Policy Number'); $ctr = 1; $company_arr_length = count($company_arr); $company_code_list_arr = array(); $company_name_list_arr = array(); $company_count = array(); for($i=0; $i<$company_arr_length; $i++){ $company_code = $company_arr[$i]; $file_name = $filename_arr[$i]; $company_id = $db->select("SELECT id FROM source_company WHERE code = '$company_code'"); $company_name = $db->select("SELECT name FROM source_company WHERE code = '$company_code'"); array_push($company_code_list_arr, $company_code); array_push($company_name_list_arr, $company_name); $filename = 'report/finance_report/data/'.$file_name.'.'.'php'; $fp = fopen($filename, 'w'); $string = implode(';',array_map('replaceExistingSemiColon', $col_header)); fwrite($fp, $string.PHP_EOL); if($mask == 0){ $company_q = " AND f.company_id = '$company_id'"; $query_print = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE 1 AND f.status = 1 " . $filter_date_q. $company_q . $dealer_q . $remaining_days_q . " GROUP BY f.plate_cs_number1, f.plate_cs_number2 " . $inactive_q . " ORDER BY f.policy_end_date DESC"; // echo sprintf($query_print, $fields); return; $new = $db->sql_query(sprintf($query_print, $fields)); $comp_count = $db->select("SELECT COUNT() FROM (".sprintf($query_print, $fields).") AS count"); array_push($company_count, $comp_count); foreach($new AS $row){ $plate_cs_number = $row['plate_cs_number1'] != "" && $row['plate_cs_number2'] == "" ? $row['plate_cs_number1'] : ($row['plate_cs_number1'] == "" && $row['plate_cs_number2'] != "" ? $row['plate_cs_number2'] : $row['plate_cs_number1'] . " / " . $row['plate_cs_number2']); // $plate_cs_number = $row['plate_number'] == "" ? $row['conduction_sticker'] : $row['plate_number']; $data_to_insert = array( $ctr, $row['customer_fullname'], $plate_cs_number, $row['dealer'], // trim(($row['brand'] == "" ? "??" : $row['brand']) . " / " . ($row['model'] == "" ? "??" : $row['model'])), $row['policy_end_date'], $row['policy_number'], ); $ctr++; $string = implode(';', array_map('replaceExistingSemiColon', $data_to_insert)); fwrite($fp, $string.PHP_EOL); } } else { $company_q = " AND f.company_id = '$company_id'"; $query_print = "SELECT %s FROM finance f INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE 1 AND f.status = 1 " . $filter_date_q. $company_q . $dealer_q . $remaining_days_q . " GROUP BY f.plate_cs_number1, f.plate_cs_number2 " . $inactive_q . " ORDER BY f.policy_end_date DESC"; // echo sprintf($query_print, $fields); return; $new = $db->sql_query(sprintf($query_print, $fields)); // $comp_count = $db->select(sprintf($query_print, $count)); $comp_count = $db->select(sprintf("SELECT COUNT() FROM (".$query_print, $fields).") AS count"); array_push($company_count, $comp_count); foreach($new AS $row){ $plate_cs_number = $row['plate_cs_number1'] != "" && $row['plate_cs_number2'] == "" ? $row['plate_cs_number1'] : ($row['plate_cs_number1'] == "" && $row['plate_cs_number2'] != "" ? $row['plate_cs_number2'] : $row['plate_cs_number1'] . " / " . $row['plate_cs_number2']); // $plate_cs_number = $row['plate_number'] == "" ? $row['conduction_sticker'] : $row['plate_number']; $data_to_insert = array( $ctr, $row['customer_fullname'], $row['customer_mobile_number'], $row['customer_email'], $plate_cs_number, $row['dealer'], // trim(($row['brand'] == "" ? "??" : $row['brand']) . " / " . ($row['model'] == "" ? "??" : $row['model'])), $row['policy_end_date'], $row['policy_number'] ); $ctr++; $string = implode(';', array_map('replaceExistingSemiColon', $data_to_insert)); fwrite($fp, $string.PHP_EOL); } } } fclose($fp); $company_header = ""; $dealer_header = ""; $brand_header = ""; $model_header = ""; $variant_header = ""; $filter_date_header = ""; $company_filename = ""; $dealer_filename = ""; $brand_filename = ""; $model_filename = ""; $variant_filename = ""; // $filter_date == 0 ? $filter_date_header = "NOT SET" : $filter_date_header = $filter_header_date; if($filter_date == 0){ if($remaining_days == 0){ $filter_date_header = "NOT SET / ALL"; } else { $filter_date_header = $rem1 . " days remaining"; } } else { $filter_date_header = $filter_header_date; } $company == 0 ? $company_header = "ALL" : $company_header = $db->select("SELECT code FROM source_company WHERE id = '$company'"); $dealer == 0 ? $dealer_header = "ALL" : $dealer_header = $db->select("SELECT code FROM source_dealer WHERE id = '$dealer'"); $brand == 0 ? $brand_header = "ALL" : $brand_header = $db->select("SELECT name FROM source_brand WHERE id = '$brand'"); $model == 0 ? $model_header = "ALL" : $model_header = $db->select("SELECT name FROM source_car_model WHERE id = '$model'"); $variant == 0 ? $variant_header = "ALL" : $variant_header = $db->select("SELECT name FROM source_car_model_variant WHERE id = '$variant'"); // GENERATE PDF include('include/fpdf183/mc_table_finance.php'); // $mask == 0 ? $col_width = array(10, 62, 25, 60, 42) : $col_width = array(10, 47, 25, 40, 15, 37, 23); if($filter_date_filename == ""){ $filter_date_header == "NOT SET / ALL" ? $filter_date_filename = "" : $filter_date_filename = strtr(strtoupper(trim(str_replace("remaining", "", $filter_date_header))), " ", "_"); } else { } $company_header == "ALL" ? $company_filename = "" : $company_filename = "_" . $company_header; $dealer_header == "ALL" ? $dealer_filename = "" : $dealer_filename = "_" . $dealer_header; if($brand_header == "ALL") { $brand_filename = ""; } else { $brand_filename = "_" . $brand_header; } if($model_header == "ALL") { $model_filename = ""; } else { $model_filename = "_" . $model_header; } if($variant_header == "ALL") { $variant_filename = ""; } else { $variant_filename = "_" . $variant_header; } $pdf = new PDF_MC_Table('P','mm','Legal'); $title = 'AutoHub - FNI Report'; $pdf->SetTitle($title); $pdf->AddPage(); $pdf->SetFont('Arial', '', 10); $pdf_request_data[] = array('Policy End Date', ': ', $filter_date_header); $pdf_request_data[] = array('Company', ': ', $company_header); $pdf_request_data[] = array('Dealer', ': ', $dealer_header); // $pdf_request_data[] = array('Brand', ': ', $brand_header); // $pdf_request_data[] = array('Model', ': ', $model_header); // $pdf_request_data[] = array('Variant', ': ', $variant_header); $pdf_request_data[] = array('Total Data Count', ': ', number_format($fetch_print_finance_report_count)); $pdf_request_data[] = array('Generated by', ': ', $generated_by); $request_header_widths = array(30, 10, 92, 25, 35); $pdf->SetWidths($request_header_widths); foreach($pdf_request_data as $row){ $pdf->Row($row, false); } $pdf->Ln(2); $pdf->SetFont('Arial', '', 11); $pdf->SetFillColor(255,255,255); $pdf->Ln(5); $pdf->SetFont('Arial', '', 9); // for($i=0; $i<count($filename_arr); $i++){ // $file_name = $filename_arr[$i]; // $record_data = $pdf->LoadData('report/finance_report/data/'.$file_name.'.'.'php'); // $pdf->Ln(3); // $pdf->Write(7, $company_code_list_arr[$i] . ": " . $company_count[$i]); // $pdf->Ln(7); // $pdf->FancyTable($record_data, $col_width); // $record_data = []; // unlink('report/finance_report/data/'.$file_name.'.'.'php'); // } for($i=0; $i<count($filename_arr); $i++){ $file_name = $filename_arr[$i]; $record_data = $pdf->LoadData('report/finance_report/data/'.$file_name.'.'.'php'); $pdf->Ln(3); $pdf->Write(8, $company_code_list_arr[$i] . ": " . $company_count[$i]); $pdf->Ln(8); $pdf->FancyTable($record_data, $col_width); $record_data = []; unlink('report/finance_report/data/'.$file_name.'.'.'php'); } $filename = "FNI_REPORT.pdf"; // $filename = "FNI_REPORT_" . $filter_date_filename . $company_filename . $dealer_filename . $brand_filename . $model_filename . ".pdf"; $pdf->Output($filename, 'D'); if($count > 0){ $return_array['status'] = 1; $return_array['message'] = "Request Generated"; $return_array['file'] = str_replace(".pdf", "", $pdf_file_name); $file = str_replace(".pdf", "", $pdf_file_name); } else { $return_array['status'] = 0; $return_array['message'] = "Request Generation Failed"; } echo json_encode($return_array); function replaceExistingSemiColon($s){ return str_replace(";", " ", $s); } function removeHyphen($s){ return str_replace("-", "", $s); } function cleanStr($string) { $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens. return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars. } function mask($str, $first, $last) { $str = cleanStr($str); $len = strlen($str); $toShow = $first + $last; return substr($str, 0, $len <= $toShow ? 0 : $first).str_repeat("*", $len - ($len <= $toShow ? 0 : $toShow)).substr($str, $len - $last, $len <= $toShow ? 0 : $last); } ?>

| ver. 1.4 | . | PHP 7.3.33 | Generation time: 0 | proxy | phpinfo | Settings