File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/app/finance/finance_crud.php

Back
<?php include_once("../../cfg/db.php"); include_once("../../api/controllers/utility.php"); $utility = new Utility(); $type = $db->escape($_POST['type']); if($type == 1){ // add new fni record (dist/js/finance/finance.js) $json_arr = array(); $plate_cs_number_1 = ""; $plate_cs_number_2 = ""; $current_user = $_SESSION['user']['id']; $plate_cs_number = $db->escape($_POST['new_plate_cs_number']); if(strpos($plate_cs_number, '/') > 0){ $multi_data = array(); $multi_data = explode("/", $plate_cs_number); $plate_cs_number_1 = alphaNumericOnly($multi_data[0]); $plate_cs_number_2 = alphaNumericOnly($multi_data[1]); } else { $plate_cs_number_1 = alphaNumericOnly($plate_cs_number); } $brand = $db->escape($_POST['new_brand']); $model = $db->escape($_POST['new_model']); $variant = $db->escape($_POST['new_variant']); $company = ""; $dealer = $db->escape($_POST['new_dealer']); $customer_fullname = $db->escape($_POST['new_customer_fullname']); $customer_mobile = $db->escape($_POST['new_customer_mobile']); $customer_other_contact = $db->escape($_POST['new_customer_other_contact']); $customer_email = $db->escape($_POST['new_customer_email']); $customer_address = $db->escape($_POST['new_customer_address']); $policy_issue_date = $utility->fix_date($db->escape($_POST['new_policy_issue_date']), false, $utility); $policy_start_date = $utility->fix_date($db->escape($_POST['new_policy_start_date']), false, $utility); $policy_end_date = $utility->fix_date($db->escape($_POST['new_policy_end_date']), false, $utility); $policy_number = alphaNumericOnly($db->escape($_POST['new_policy_number'])); $bank = $db->escape($_POST['new_bank']); $insurance_provider = $db->escape($_POST['new_insurance_provider']); $insurance_type = $db->escape($_POST['new_insurance_type']); $sales_consultant = $db->escape($_POST['new_sales_consultant']); $paid_amount = $db->escape($_POST['new_paid_amount']); $lock_in_year = $db->escape($_POST['new_lock_in_year']); $terms = $db->escape($_POST['new_terms']); $lock_in = $db->escape($_POST['new_lock_in']); // CHECK AND VALIDATE DEALER, GET IT'S COMPANY $check_dealer = $db->select("SELECT COUNT(1) FROM source_company_dealer WHERE dealer_id = '$dealer' AND status = 1 AND type = 1"); if($check_dealer != 1){ $json_arr['status'] = 2; // error in dealer validation echo json_encode($json_arr); exit; } else { $company = $db->select("SELECT company_id FROM source_company_dealer WHERE dealer_id = '$dealer'"); } // CHECK FOR DUPLICATE RECORD $check_duplicate = $db->select("SELECT COUNT(1) FROM finance WHERE 1 AND `policy_number` = '$policy_number' AND `plate_cs_number1` = '$plate_cs_number_1' AND `plate_cs_number2` = '$plate_cs_number_2' AND `policy_issue_date` = '$policy_issue_date' AND `policy_start_date` = '$policy_start_date' AND `policy_end_date` = '$policy_end_date' AND `status` = 1"); if($check_duplicate > 0){ $get_duplicate_id = " id "; $get_duplicate_plate_cs_number1 = " plate_cs_number1 "; $query = "SELECT %s FROM finance WHERE 1 AND `policy_number` = '$policy_number' AND `plate_cs_number1` = '$plate_cs_number_1' AND `plate_cs_number2` = '$plate_cs_number_2' AND `policy_issue_date` = '$policy_issue_date' AND `policy_start_date` = '$policy_start_date' AND `policy_end_date` = '$policy_end_date' AND `status` = 1"; $duplicate_id = $db->select(sprintf($query, $get_duplicate_id)); $duplicate_plate_cs_number = $db->select(sprintf($query, $get_duplicate_plate_cs_number1)); $json_arr['status'] = 3; // duplicate $json_arr['duplicate_id'] = $duplicate_id; $json_arr['duplicate_plate_cs_number'] = $duplicate_plate_cs_number; echo json_encode($json_arr); exit; } //CHECK EMAIL IF VALID $check_email = $utility->isValidEmail($customer_email); if(!$check_email){ $json_arr['status'] = 4; // invalid email echo json_encode($json_arr); exit; } $customer_other_contact != "" ? $customer_mobile = $customer_mobile . " / " . $customer_other_contact : $customer_mobile; $insert_query = "INSERT INTO `finance` ( `plate_cs_number1`, `plate_cs_number2`, `brand_id`, `model_id`, `variant_id`, `customer_fullname`, `customer_mobile_number`, `customer_email`, `company_id`, `dealer_id`, `customer_address`, `insurance_provider`, `policy_number`, `policy_issue_date`, `policy_start_date`, `policy_end_date`, `insurance_business_type`, `bank_name`, `sales_consultant`, `locked_in`, `locked_in_years`, `paid_amount`, `terms`, `uploaded_by`, `add_source` ) VALUES ( '$plate_cs_number_1', '$plate_cs_number_2', '$brand', '$model', '$variant', '$customer_fullname', '$customer_mobile', '$customer_email', '$company', '$dealer', '$customer_address', '$insurance_provider', '$policy_number', '$policy_issue_date', '$policy_start_date', '$policy_end_date', '$insurance_type', '$bank', '$sales_consultant', '$lock_in', '$lock_in_year', '$paid_amount', '$terms', '$current_user', '2' )"; // echo $insert_query; exit; $insert_query_execute = $db->sql_query_num_inserted($insert_query); if($insert_query_execute > 0){ $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // not added, failed } echo json_encode($json_arr); } else if($type == 2){ // read details (dist/js/finance/finance_information.js) $json_arr = array(); $record_id = $db->escape($_POST['edit_id']); $query = "SELECT id, plate_cs_number1, plate_cs_number2, brand_id, model_id, variant_id, customer_fullname, customer_mobile_number, customer_email, company_id, dealer_id, customer_address, insurance_provider, policy_number, policy_issue_date, policy_start_date, policy_end_date, insurance_business_type, bank_name, sales_consultant, locked_in, locked_in_years, paid_amount, terms FROM finance WHERE id = '$record_id' AND status = 1"; $finance_query_list = $db->sql_query($query); foreach($finance_query_list AS $row){ $json_arr['record_id'] = $row['id']; $json_arr['plate_cs_number'] = $row['plate_cs_number2'] == "" ? $row['plate_cs_number1'] : $row['plate_cs_number1'] . "/" . $row['plate_cs_number2']; $json_arr['brand_id'] = $row['brand_id']; $json_arr['model_id'] = $row['model_id']; $json_arr['variant_id'] = $row['variant_id']; $json_arr['customer_fullname'] = $row['customer_fullname']; $json_arr['customer_mobile_number'] = $row['customer_mobile_number']; $json_arr['customer_email'] = $row['customer_email']; $json_arr['company_id'] = $row['company_id']; $json_arr['dealer_id'] = $row['dealer_id']; $json_arr['customer_address'] = $row['customer_address']; $json_arr['insurance_provider'] = $row['insurance_provider']; $json_arr['policy_number'] = $row['policy_number']; $json_arr['policy_issue_date'] = formatDate($row['policy_issue_date']); $json_arr['policy_start_date'] = formatDate($row['policy_start_date']); $json_arr['policy_end_date'] = formatDate($row['policy_end_date']); $json_arr['insurance_business_type'] = $row['insurance_business_type']; $json_arr['bank_name'] = $row['bank_name']; $json_arr['sales_consultant'] = $row['sales_consultant']; $json_arr['locked_in'] = $row['locked_in']; $json_arr['locked_in_years'] = $row['locked_in_years']; $json_arr['paid_amount'] = $row['paid_amount']; $json_arr['terms'] = $row['terms']; } if(strpos($json_arr['customer_mobile_number'], '/') > 0){ $multi_mobile = array(); $multi_mobile = explode("/", $json_arr['customer_mobile_number']); $json_arr['customer_mobile_number'] = formatMobile($multi_mobile[0]); $json_arr['customer_other_contact'] = $multi_mobile[1]; } else { $json_arr['customer_mobile_number'] = formatMobile($json_arr['customer_mobile_number']); $json_arr['customer_other_contact'] = ""; } echo json_encode($json_arr); } else if($type == 3){ // update details (dist/js/finance/finance_information.js) $arr_updating_data = array(); $edit_id = $db->escape($_POST['edit_id']); $update_plate_cs_number = $db->escape($_POST['update_plate_cs_number']); $update_brand = $db->escape($_POST['update_brand']); $update_model = $db->escape($_POST['update_model']); $update_variant = $db->escape($_POST['update_variant']); $update_dealer = $db->escape($_POST['update_dealer']); $update_customer_fullname = $arr_updating_data[4] = $db->escape($_POST['update_customer_fullname']); $fetched_mobile_number = $db->escape($_POST['update_customer_mobile']); if($fetched_mobile_number == "+63") { $fetched_mobile_number = ""; } $update_customer_mobile = $arr_updating_data[5] = $fetched_mobile_number; $update_customer_other_contact = $db->escape($_POST['update_customer_other_contact']); $update_customer_email = $arr_updating_data[6] = $db->escape($_POST['update_customer_email']); $update_customer_address = $arr_updating_data[7] = $db->escape($_POST['update_customer_address']); $update_policy_issue_date = $arr_updating_data[8] = $utility->fix_date($db->escape($_POST['update_policy_issue_date']), false, $utility); $update_policy_start_date = $arr_updating_data[9] = $utility->fix_date($db->escape($_POST['update_policy_start_date']), false, $utility); $update_policy_end_date = $arr_updating_data[10] = $utility->fix_date($db->escape($_POST['update_policy_end_date']), false, $utility); $update_policy_number = $arr_updating_data[11] = alphaNumericOnly($db->escape($_POST['update_policy_number'])); $update_bank = $arr_updating_data[12] = $db->escape($_POST['update_bank']); $update_insurance_provider = $db->escape($_POST['update_insurance_provider']); $update_insurance_type = $db->escape($_POST['update_insurance_type']); $update_sales_consultant = $arr_updating_data[15] = $db->escape($_POST['update_sales_consultant']); $update_paid_amount = $arr_updating_data[16] = $db->escape($_POST['update_paid_amount']); $update_lock_in_year = $arr_updating_data[17] = $db->escape($_POST['update_lock_in_year']); $update_lock_in = $db->escape($_POST['update_lock_in']); $update_terms = $arr_updating_data[19] = $db->escape($_POST['update_terms']); $current_user = $_SESSION['user']['id']; $current_date = $db->select("SELECT NOW()"); $arr_updating_data[2] = $db->select("SELECT name FROM finance_source_brand WHERE id = " . $update_brand); $arr_updating_data[20] = $db->select("SELECT name FROM finance_source_car_model WHERE id = " . $update_model); $arr_updating_data[21] = $db->select("SELECT name FROM finance_source_car_model_variant WHERE id = " . $update_variant); $arr_updating_data[3] = $db->select("SELECT name FROM source_dealer WHERE id = " . $update_dealer); $arr_updating_data[13] = $db->select("SELECT name FROM finance_ins_provider WHERE id = " . $update_insurance_provider); $arr_updating_data[14] = $db->select("SELECT name FROM finance_ins_business_type WHERE id = " . $update_insurance_type); $update_lock_in == 0 ? $arr_updating_data[18] = "No" : $arr_updating_data[18] = "Yes"; $update_plate_cs_number1 = ""; $update_plate_cs_number2 = ""; if(strpos($update_plate_cs_number, '/') > 0){ $multi_data = array(); $multi_data = explode("/", $update_plate_cs_number); $update_plate_cs_number1 = $arr_updating_data[0] = alphaNumericOnly($multi_data[0]); $update_plate_cs_number2 = $arr_updating_data[1] = alphaNumericOnly($multi_data[1]); } else { $update_plate_cs_number1 = $arr_updating_data[0] = alphaNumericOnly($update_plate_cs_number); $update_plate_cs_number2 = $arr_updating_data[1] = ""; } $update_company = ""; $get_company_count = $db->select("SELECT COUNT(1) FROM source_company_dealer WHERE `dealer_id` = '$update_dealer' AND type = 1 AND status = 1"); if($get_company_count == 1){ $update_company = $db->select("SELECT company_id FROM source_company_dealer WHERE `dealer_id` = '$update_dealer' AND type = 1 AND status = 1"); } else { $json_arr['status'] = 2; // dealer error echo json_encode($json_arr); return; } // CHECK IF DUPLICATE // get original data $original_policy_number = ""; $original_plate_cs_number1 = ""; $original_plate_cs_number2 = ""; $original_policy_issue_date = ""; $original_policy_start_date = ""; $original_policy_end_date = ""; $original_customer_mobile = ""; $original_customer_email = ""; $check_data_arr = array(); $original_query = "SELECT * FROM finance WHERE `id` = '$edit_id' AND `status` = 1"; $original_data_list = $db->sql_query($original_query); foreach($original_data_list AS $data){ // mga field na basehan kung nabago ba ang isang buong record $original_policy_number = $data['policy_number']; $original_plate_cs_number1 = $data['plate_cs_number1']; $original_plate_cs_number2 = $data['plate_cs_number2']; $original_policy_issue_date = $data['policy_issue_date']; $original_policy_start_date = $data['policy_start_date']; $original_policy_end_date = $data['policy_end_date']; $data['flag'] == 0 ? $flag = 1 : $flag = 0; // fields to check if data are change in a record $check_data_arr[0] = $data['plate_cs_number1']; $check_data_arr[1] = $data['plate_cs_number2']; $check_data_arr[2] = $db->select("SELECT name FROM finance_source_brand WHERE id = " . $data['brand_id']); $check_data_arr[20] = $db->select("SELECT name FROM finance_source_car_model WHERE id = " . $data['model_id']); $check_data_arr[21] = $db->select("SELECT name FROM finance_source_car_model_variant WHERE id = " . $data['variant_id']); $check_data_arr[3] = $db->select("SELECT name FROM source_dealer WHERE id = " . $data['dealer_id']); $check_data_arr[4] = $data['customer_fullname']; $check_data_arr[5] = $data['customer_mobile_number']; $check_data_arr[6] = $data['customer_email']; $check_data_arr[7] = $db->escape($data['customer_address']); $check_data_arr[8] = $data['policy_issue_date']; $check_data_arr[9] = $data['policy_start_date']; $check_data_arr[10] = $data['policy_end_date']; $check_data_arr[11] = $data['policy_number']; $check_data_arr[12] = $data['bank_name']; $check_data_arr[13] = $db->select("SELECT name FROM finance_ins_provider WHERE id = " . $data['insurance_provider']); $check_data_arr[14] = $db->select("SELECT name FROM finance_ins_business_type WHERE id = " . $data['insurance_business_type']); $check_data_arr[15] = $data['sales_consultant']; $check_data_arr[16] = $data['paid_amount']; $check_data_arr[17] = $data['locked_in_years']; $data['locked_in'] == 0 ? $check_data_arr[18] = "No" : $check_data_arr[18] = "Yes"; $check_data_arr[19] = $data['terms']; } if( $original_policy_number == $update_policy_number && $original_plate_cs_number1 == $update_plate_cs_number1 && $original_plate_cs_number2 == $update_plate_cs_number2 && $original_policy_issue_date == $update_policy_issue_date && $original_policy_start_date == $update_policy_start_date && $original_policy_end_date == $update_policy_end_date ){ $update_customer_other_contact != "" ? $update_customer_mobile = $update_customer_mobile . " / " . $update_customer_other_contact : $update_customer_mobile; $arr_updating_data[5] = $update_customer_mobile; $update_query = "UPDATE `finance` SET `plate_cs_number1` = '$update_plate_cs_number1', `plate_cs_number2` = '$update_plate_cs_number2', `brand_id` = '$update_brand', `model_id` = '$update_model', `variant_id` = '$update_variant', `company_id` = '$update_company', `dealer_id` = '$update_dealer', `customer_fullname` = '$update_customer_fullname', `customer_mobile_number` = '$update_customer_mobile', `customer_email` = '$update_customer_email', `customer_address` = '$update_customer_address', `policy_issue_date` = '$update_policy_issue_date', `policy_start_date` = '$update_policy_start_date', `policy_end_date` = '$update_policy_end_date', `policy_end_date` = '$update_policy_end_date', `policy_number` = '$update_policy_number', `bank_name` = '$update_bank', `insurance_provider` = '$update_insurance_provider', `insurance_business_type` = '$update_insurance_type', `sales_consultant` = '$update_sales_consultant', `paid_amount` = '$update_paid_amount', `locked_in_years` = '$update_lock_in_year', `locked_in` = '$update_lock_in', `terms` = '$update_terms', `flag` = '$flag' WHERE `id` = '$edit_id' "; // echo $update_query; return; $update_query_execute = $db->sql_query_num_inserted($update_query); if($update_query_execute > 0){ $descript = checkForUpdatedData($arr_updating_data, $check_data_arr, $original_policy_number, $original_policy_issue_date, $db); if($descript != ""){ activityLog($descript, $original_plate_cs_number1, $edit_id, $db); } $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // update failed } echo json_encode($json_arr); return; } else { // update conflict $check_for_duplicate = "SELECT COUNT(1) FROM finance WHERE `policy_number` = '$update_policy_number' AND `plate_cs_number1` = '$update_plate_cs_number1' AND `plate_cs_number2` = '$update_plate_cs_number2' AND `policy_issue_date` = '$update_policy_issue_date' AND `policy_start_date` = '$update_policy_start_date' AND `policy_end_date` = '$update_policy_end_date' AND `plate_cs_number1` = '$update_plate_cs_number1' AND `status` = 1 "; // echo $check_for_duplicate; return 0; $duplicate_count = $db->select($check_for_duplicate); if($duplicate_count > 0){ $json_arr['status'] = 3; // duplicate found echo json_encode($json_arr); return; } else { $update_customer_other_contact != "" ? $update_customer_mobile = $update_customer_mobile . " / " . $update_customer_other_contact : $update_customer_mobile; $update_query = "UPDATE `finance` SET `plate_cs_number1` = '$update_plate_cs_number1', `plate_cs_number2` = '$update_plate_cs_number2', `brand_id` = '$update_brand', `model_id` = '$update_model', `variant_id` = '$update_variant', `company_id` = '$update_company', `dealer_id` = '$update_dealer', `customer_fullname` = '$update_customer_fullname', `customer_mobile_number` = '$update_customer_mobile', `customer_email` = '$update_customer_email', `customer_address` = '$update_customer_address', `policy_issue_date` = '$update_policy_issue_date', `policy_start_date` = '$update_policy_start_date', `policy_end_date` = '$update_policy_end_date', `policy_end_date` = '$update_policy_end_date', `policy_number` = '$update_policy_number', `bank_name` = '$update_bank', `insurance_provider` = '$update_insurance_provider', `insurance_business_type` = '$update_insurance_type', `sales_consultant` = '$update_sales_consultant', `paid_amount` = '$update_paid_amount', `locked_in_years` = '$update_lock_in_year', `locked_in` = '$update_lock_in', `terms` = '$update_terms', `flag` = '$flag' WHERE `id` = '$edit_id' "; $update_query_execute = $db->sql_query_num_inserted($update_query); if($update_query_execute > 0){ $descript = checkForUpdatedData($arr_updating_data, $check_data_arr, $original_policy_number, $original_policy_issue_date, $db); if($descript != ""){ activityLog($descript, $original_plate_cs_number1, $edit_id, $db); } $json_arr['status'] = 0; // success } else { $json_arr['status'] = 1; // update failed } echo json_encode($json_arr); return; } } echo json_encode($json_arr); } else if($type == 4){ // delete fni record (resources/views/common/script/finance/finance_information.js => swalDeleteFNIRecord function) $record_id = $db->escape($_POST['record_id']); $get_plate_cs_number = $db->select("SELECT plate_cs_number1 FROM finance WHERE id = '$record_id'"); $get_policy_number = $db->select("SELECT policy_number FROM finance WHERE id = '$record_id'"); $get_policy_issue_date = $db->select("SELECT policy_issue_date FROM finance WHERE id = '$record_id'"); $current_user = $_SESSION['user']['id']; $json_arr = array(); $delete_query = "UPDATE `finance` SET `status` = 0 WHERE `id` = '$record_id'"; $delete_query_execute = $db->sql_query_num_inserted($delete_query); // echo $delete_query_execute; return; if($delete_query_execute > 0){ $json_arr['status'] = 0; // success $record_count = $db->select("SELECT COUNT(1) FROM finance WHERE plate_cs_number1 = '$get_plate_cs_number' AND status = 1"); if($record_count > 0){ $json_arr['record_plate_cs'] = $get_plate_cs_number; } $json_arr['record_count'] = $record_count; $description = "Delete record: [" . $get_policy_number . "," . $get_policy_issue_date . "]:"; $log_activity = "INSERT INTO `all_activity_log_fni_n_pdc`(`plate_cs_number1`, `description`, `type`, `done_by`) VALUES ('$get_plate_cs_number', '$description', 1, '$current_user')"; $log_execute = $db->sql_query($log_activity); } else { $json_arr['status'] = 1; // deletion failed } echo json_encode($json_arr); } // FUNCTIONS function alphaNumericOnly($s){ $str = preg_replace("/[^a-zA-Z0-9]+/", "", $s); if(strlen($str) < 5){ return ""; } return $str; } function formatDate($data){ // 2022-12-31 $date = array(); $date = explode("-", $data); return $date[1] . "/" . $date[2] . "/" . $date[0]; } function formatMobile($data){ // +639464023949 return substr($data, 3, 12); } function checkForUpdatedData($arr_data, $check_data_arr, $policy_number, $issue_date, $db){ $description = ""; $desc_arr = array('Plate CS 1', 'Plate CS 2', 'Brand', 'Dealer', 'Customer Fullname', 'Customer Mobile', 'Customer Email', 'Customer Address','Issue Date', 'Start Date', 'End Date', 'Policy Number', 'Bank', 'Ins. Com', 'Ins. Type', 'Sales Consultant', 'Paid Amount', 'Lock-in Years', 'Lock-in', 'Terms', 'Model', 'Variant'); for($i=0; $i<count($arr_data); $i++){ $arr = $arr_data[$i]; $check_data = $check_data_arr[$i]; if($arr != $check_data){ $description = $description . "[" . $desc_arr[$i] . ": \"" . $check_data_arr[$i] . "\" to \"" . $arr_data[$i] . "\"]: "; } } if($description != ""){ $description = "Update record: " . $policy_number . "," . $issue_date . ": " . $description; } return $description; } function activityLog($descript, $plate_cs_number, $edit_id, $db){ $current_user = $_SESSION['user']['id']; $current_date = $db->select("SELECT NOW()"); $log_query = "INSERT INTO all_activity_log_fni_n_pdc(`plate_cs_number1`, `description`, `type`, `done_by`) VALUES('$plate_cs_number', '$descript', 1, '$current_user')"; $db->sql_query($log_query); $modifier_query = "UPDATE `finance` SET `modified_by` = '$current_user', `date_modified` = '$current_date' WHERE id = '$edit_id'"; $db->sql_query($modifier_query); } ?>

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