File manager

File manager - Edit - /home/autoph/public_html/connectv1/api/controllers/import_finance.php

Back
<?php class Import { function getArrayFields($id, $db){ $return_array = array(); $data_list = $db->sql_query("SELECT `name` FROM `source_finance_fields_name` WHERE `finance_field_id` = '$id'"); foreach($data_list as $row){ $return_array = array_merge($return_array, array($row['name'])); } // print_r($return_array); exit; return $return_array; } function getIssueSummaryField($str, $ColumnNumber, $array_data){ for($i = 0; $i < $ColumnNumber; $i++){ if(in_array($str, $array_data, true)){ return $i; } } return "-5"; } function getFieldFromExcel($row, $ColumnNumber, $array_data){ for($i = 0; $i < $ColumnNumber; $i++){ if(in_array(trim($row[$i]), $array_data, true)){ return $row[$i]; } } return ""; } function checkExist($row, $field, $utility){ if(!$utility->isNotEmpty($field)){ return ""; } $key = array_search($field, $GLOBALS['row_finance_headers']); return isset($row[$key]) ? $row[$key] : '' ; } function getDmsIdByName($data_dms, $utility,$db){ $id = $db -> select("SELECT `id` FROM `source_dms` WHERE `name` = '$data_dms' LIMIT 1"); if($utility->isNotEmpty($id)){ return $id; } return 0; } function getCompanyIdByName($data_dms, $data_company, $utility, $db){ $id = $db -> select("SELECT id FROM `source_company` WHERE (`code` = '$data_company' OR `name` = '$data_company') AND dms_id = '$data_dms' LIMIT 1"); if($utility->isNotEmpty($id)){ return $id; } return 0; } function getDealerIdByNameOrCode($data_dealer, $utility, $db){ $id = $db->select("SELECT id FROM `source_dealer` WHERE (`code` = '$data_dealer' OR `name` = '$data_dealer') AND status = 1 LIMIT 1"); if($utility->isNotEmpty($id)){ return $id; } return 0; } function getCompanyDealerIdByName($data_company,$data_dealer,$utility,$db){ $company_dealer_id = $db -> select("SELECT id FROM `source_company_dealer` WHERE `company_id` = '$data_company' AND `dealer_id` = '$data_dealer' AND type= '1' AND status = '1' LIMIT 1"); if($utility->isNotEmpty($company_dealer_id)){ return $company_dealer_id; } return 0; } function getGenderID($gender,$utility){ $gender_id = "0"; $gender = strtolower($gender); if($utility->isNotEmpty($gender)){ if(substr($gender,0,1) == "m" ){ $gender_id = "1"; }else if(substr($gender,0,1) == "f" ){ $gender_id = "2"; } } return $gender_id; } function getSuffix($data_suffix){ $id = "0"; switch($data_suffix){ case "I"; $id = "1"; break; case "II"; $id = "2"; break; case "III"; $id = "3"; break; case "JR"; $id = "4"; break; case "SR"; $id = "5"; break; } return $id; } function getSalesConsultant($name, $utility, $db){ $id = 0; if($utility->isNotEmpty(($name))){ $query = $db->select("SELECT id FROM `sales_consultant` WHERE CONCAT(first_name,' ',last_name) LIKE '%$name%' AND status = 1 LIMIT 1"); if($utility->isNotEmpty($query)){ $id = $query; } else { return false; } } return $id; } function validateDate($date, $format = 'Y-m-d') { $d = DateTime::createFromFormat($format, $date); // The Y ( 4 digits year ) returns TRUE for any integer with any number of digits so changing the comparison from == to === fixes the issue. return $d && $d->format($format) === $date; } function in_array_r($needle, $haystack, $strict = false) { foreach ($haystack as $item) { if (($strict ? $item === $needle : $item == $needle) \|\| (is_array($item) && $this->in_array_r($needle, $item, $strict))) { return true; } } return false; } function in_array_y($array, $key, $val) { foreach ($array as $item) if (isset($item[$key]) && $item[$key] == $val) return true; return false; } function getDuplicateByEmailAndMobile($data_customer_email, $data_customer_mobile, $data_company_dealer, $utility, $db){ if($utility->isNotEmpty($data_customer_mobile)){ $append_mobile = " AND (`customer_mobile_number` = '$data_customer_mobile' "; } else { $append_mobile = ""; } if($utility->isNotEmpty($data_customer_email)){ $append_email = " OR `customer_email` = '$data_customer_email') "; } else { $append_email = ")"; } $query = "SELECT id, customer_fullname, customer_email, customer_mobile_number, company_id, dealer_id FROM finance WHERE 1 %s %s AND status = 1 "; // echo sprintf($query, $append_mobile, $append_email); exit; return ($db->return_result(sprintf($query, $append_mobile, $append_email))); } function alphaNumericOnly($s){ $str = preg_replace("/[^a-zA-Z0-9]+/", "", $s); if(strlen($str) < 5){ return ""; } return $str; } function getDmsCompanyId($data_dealer, $utility, $db){ $query = "SELECT sdms.id as dms_id, sc.id as company_id FROM source_dms sdms INNER JOIN source_company sc ON sdms.id = sc.dms_id INNER JOIN source_company_dealer scd ON sc.id = scd.company_id WHERE sdms.status = 1 AND sc.status = 1 AND scd.type = 1 AND scd.status = 1 AND scd.dealer_id = '$data_dealer' LIMIT 1"; $results = $db->sql_query($query); $data_dms_company = array(); foreach($results as $result){ $data_dms_company =(array( 'dms' => $result['dms_id'], 'company'=>$result['company_id'] )); } return ($data_dms_company); } function updateIndividualCompany($corporation_id,$individual_id, $db){ $db -> sql_query("UPDATE customer SET corporation_id = '$corporation_id' WHERE id = '$individual_id' "); } function check_string_exist($str){ $str = strtolower($str); $none = array("na", "n/a", "none"); if(in_array($str, $none)){ return true; } return false; } function start_import($row, $customer_class, $utility, $db){ //store raw data from excel $mobile_import = $db->select("SELECT `status` FROM `settings_fni_pdc` WHERE `type` = 1 AND `field` = 1 AND `operation` = 1"); $email_import = $db->select("SELECT `status` FROM `settings_fni_pdc` WHERE `type` = 1 AND `field` = 2 AND `operation` = 1"); $policy_import = $db->select("SELECT `status` FROM `settings_fni_pdc` WHERE `type` = 1 AND `field` = 3 AND `operation` = 1"); $data_brand = $this->checkExist($row, $GLOBALS['brand'], $utility); $data_model = $this->checkExist($row, $GLOBALS['model'], $utility); $data_variant = $this->checkExist($row, $GLOBALS['variant'], $utility); $data_plate_cs_number = $this->checkExist($row, $GLOBALS['plate_cs_number'], $utility); $data_customer_fullname = $this->checkExist($row, $GLOBALS['customer_fullname'], $utility); $data_customer_mobile = $this->checkExist($row, $GLOBALS['customer_mobile'], $utility); $data_customer_email = $this->checkExist($row, $GLOBALS['customer_email'], $utility); $data_customer_address = $this->checkExist($row, $GLOBALS['customer_address'], $utility); $data_customer_company = $this->checkExist($row, $GLOBALS['company'], $utility); $data_customer_dealer = $this->checkExist($row, $GLOBALS['dealer'], $utility); $data_insurance_provider = $this->checkExist($row, $GLOBALS['insurance_provider'], $utility); $data_policy_number = $this->checkExist($row, $GLOBALS['policy_number'], $utility); $data_policy_issue_date = $this->checkExist($row, $GLOBALS['policy_issue_date'], $utility); $data_policy_start_date = $this->checkExist($row, $GLOBALS['policy_start_date'], $utility); $data_policy_end_date = $this->checkExist($row, $GLOBALS['policy_end_date'], $utility); $data_insurance_type = $this->checkExist($row, $GLOBALS['insurance_type'], $utility); $data_bank_name = $this->checkExist($row, $GLOBALS['bank_name'], $utility); $data_sales_consultant = $this->checkExist($row, $GLOBALS['sales_consultant'], $utility); $data_locked_in = $this->checkExist($row, $GLOBALS['locked_in'], $utility); $data_locked_in_year = $this->checkExist($row, $GLOBALS['locked_in_year'], $utility); $data_paid_amount = $this->checkExist($row, $GLOBALS['paid_amount'], $utility); $data_terms = $this->checkExist($row, $GLOBALS['terms'], $utility); $data_uploader = $_SESSION['user']['id']; //data cleansing use for validations $data_customer_mobile = $utility->fix_mobile_format(remove_non_numeric($data_customer_mobile)); $data_policy_number = $this->alphaNumericOnly($data_policy_number); //end data cleansing use for validations $data_policy_issue_date = $utility->fix_date($data_policy_issue_date, false, $utility); $data_policy_start_date = $utility->fix_date($data_policy_start_date, false, $utility); $data_policy_end_date = $utility->fix_date($data_policy_end_date, false, $utility); // validate required data if((!$utility->isNotEmpty($data_brand))){ // CHECK BRAND $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_brand']), $row); $GLOBALS['no_brand_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_brand))){ // CHECK MODEL $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_model']), $row); $GLOBALS['not_inserted_count']++; return 0; } if(!$utility->isNotEmpty($data_customer_email)){ // CHECK EMAIL IF HAS if($email_import == 1){ // email is required $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_customer_email']), $row); $GLOBALS['no_customer_email_count']++; $GLOBALS['not_inserted_count']++; return 0; } } else { if(strpos($data_customer_email, '/') > 0){ $multi_email = array(); $multi_email = explode("/", $data_customer_email); $isValidEmail1 = $utility->isValidEmail($multi_email[0]); $isValidEmail2 = $utility->isValidEmail($multi_email[1]); if($isValidEmail1 == 0 && $isValidEmail2 == 0){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_email']), $row); $GLOBALS['invalid_email_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { $data_customer_email = $multi_email[0] . " / " . $multi_email[1]; } } else { $isValidEmail = $utility->isValidEmail($data_customer_email); if(!$isValidEmail){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_email']), $row); $GLOBALS['invalid_email_count']++; $GLOBALS['not_inserted_count']++; return 0; } } } $plate_cs_number_1 = ""; $plate_cs_number_2 = ""; if(!$utility->isNotEmpty($data_plate_cs_number)){ // CHECK PLATE/CS NUMBER $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_plate_cs_number']), $row); $GLOBALS['no_plate_cs_number_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { if(strpos($data_plate_cs_number, '/') > 0){ $multi_data = array(); $multi_data = explode("/", $data_plate_cs_number); $plate_cs_number_1 = $this->alphaNumericOnly($multi_data[0]); $plate_cs_number_2 = $this->alphaNumericOnly($multi_data[1]); } else { $plate_cs_number_1 = $this->alphaNumericOnly($data_plate_cs_number); } } if($utility->isNotEmpty($data_customer_mobile)){ // CHECK MOBILE NUMBER if(strpos($data_customer_mobile, '/') > 0){ $multi_mobile = array(); $multi_mobile = explode("/", $data_customer_mobile); $multi_mobile[0] = $utility->fix_mobile_format(remove_non_numeric($multi_mobile[0])); $multi_mobile[1] = $utility->fix_mobile_format(remove_non_numeric($multi_mobile[1])); $isValid1 = isMobileValid($multi_mobile[0]); $isValid2 = isMobileValid($multi_mobile[1]); if($isValid1 == 0 && $isValid2 == 0){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_mobile']), $row); $GLOBALS['invalid_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { $data_customer_mobile = $multi_mobile[0] . " / " . $multi_mobile[1]; } } else { $data_customer_mobile = $utility->fix_mobile_format(remove_non_numeric($data_customer_mobile)); $isValidMobile = isMobileValid($data_customer_mobile); if(!$isValidMobile){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_mobile']), $row); $GLOBALS['invalid_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } } // if(strlen($data_customer_mobile) != 13){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_mobile']), $row); // $GLOBALS['invalid_mobile_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } // if(!substr($data_customer_mobile, 0, 4 ) === "+639"){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_mobile']), $row); // $GLOBALS['invalid_mobile_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } } else { if($mobile_import == 1){ // mobile is required $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_customer_mobile']), $row); $GLOBALS['no_customer_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } } if(!$utility->isNotEmpty($data_policy_number)){ // CHECK POLICY NUMBER if($policy_import == 1){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_number']), $row); $GLOBALS['not_inserted_count']++; return 0; } } if((!$utility->isNotEmpty($data_insurance_provider))){ // CHECK INSURANCE PROVIDER $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_insurance_provider']), $row); $GLOBALS['no_insurance_provider_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_insurance_type))){ // CHECK INSURANCE TYPE $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_insurance_type']), $row); $GLOBALS['no_insurance_type_count']++; $GLOBALS['not_inserted_count']++; return 0; } $customer_firstname = ""; $customer_lastname = ""; if((!$utility->isNotEmpty($data_customer_fullname))){ // CHECK CUSTOMER NAME $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_customer_fullname']), $row); $GLOBALS['no_customer_fullname_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!$utility->isNotEmpty($data_policy_issue_date)){ // CHECK POLICY ISSUE DATE $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_issue_date']), $row); $GLOBALS['no_policy_issue_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!$utility->isNotEmpty($data_policy_start_date)){ // CHECK POLICY START DATE $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_start_date']), $row); $GLOBALS['no_policy_start_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(trim($data_locked_in) === null){ // CHECK LOCKED IN $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_locked_in']), $row); $GLOBALS['not_inserted_count']++; return 0; } if(!$utility->isNotEmpty($data_sales_consultant)){ // CHECK SALES CONSULTANT $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_sales_consultant']), $row); $GLOBALS['not_inserted_count']++; return 0; } if(!$utility->isNotEmpty($data_locked_in_year)){ // CHECK LOCKED IN YEARS $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_locked_in_years']), $row); $GLOBALS['not_inserted_count']++; return 0; } // if(!$utility->isNotEmpty($data_policy_end_date)){ // CHECK POLICY END DATE // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_end_date']), $row); // $GLOBALS['no_policy_end_date_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } // $data_policy_issue_date = $utility->fix_date($data_policy_issue_date, false, $utility); // $data_policy_start_date = $utility->fix_date($data_policy_start_date, false, $utility); // $data_policy_end_date = $utility->fix_date($data_policy_end_date, false, $utility); // $date_diff = $db->select("SELECT DATEDIFF('".$data_policy_end_date."', '".$data_policy_start_date."')"); // if($date_diff < 0){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['confusing_date']), $row); // $GLOBALS['not_inserted_count']++; // return 0; // } // else if($date_diff < 365){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['less_than_year_date']), $row); // $GLOBALS['not_inserted_count']++; // return 0; // } if(!$utility->isNotEmpty($data_policy_end_date)){ // CHECK POLICY END DATE $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_end_date']), $row); $GLOBALS['no_policy_end_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { // 4/4/2021 4/12/2021 10/4/2021 10/12/2021 $arr_date = array(); $date = str_replace("-", "/", $data_policy_issue_date); $arr_date = explode("/", $date); if(count($arr_date) != 3){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_issue_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } $month_issue = $arr_date[0]; $day_issue = $arr_date[1]; $year_issue = $arr_date[2]; if(!is_numeric($month_issue)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_issue_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!is_numeric($day_issue)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_issue_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!is_numeric($year_issue)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_issue_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } // if(!checkdate($month_issue, $day_issue, $year_issue)){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_issue_date_format']), $row); // // $GLOBALS['invalid_date_format_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } else { // CHECK POLICY START DATE if(!$utility->isNotEmpty($data_policy_start_date)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_start_date']), $row); $GLOBALS['no_policy_start_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { // 4/4/2021 4/12/2021 10/4/2021 10/12/2021 $arr_date = array(); $date = str_replace("-", "/", $data_policy_start_date); $arr_date = explode("/", $date); if(count($arr_date) != 3){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_start_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } $month_start = $arr_date[0]; $day_start = $arr_date[1]; $year_start = $arr_date[2]; if(!is_numeric($month_start)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_start_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!is_numeric($day_start)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_start_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!is_numeric($year_start)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_start_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } // if(!checkdate($month_start, $day_start, $year_start)){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_start_date_format']), $row); // // $GLOBALS['invalid_date_format_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } else { // CHECK POLICY END DATE if(!$utility->isNotEmpty($data_policy_end_date)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_end_date']), $row); $GLOBALS['no_policy_end_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { // 4/4/2021 4/12/2021 10/4/2021 10/12/2021 $arr_date = array(); $date = str_replace("-", "/", $data_policy_end_date); $arr_date = explode("/", $date); if(count($arr_date) != 3){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_end_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } $month_end = $arr_date[0]; $day_end = $arr_date[1]; $year_end = $arr_date[2]; if(!is_numeric($month_end)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_end_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!is_numeric($day_end)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_end_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } if(!is_numeric($year_end)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_end_date_format']), $row); // $GLOBALS['invalid_date_format_count']++; $GLOBALS['not_inserted_count']++; return 0; } // if(!checkdate($month_end, $day_end, $year_end)){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_policy_end_date_format']), $row); // // $GLOBALS['invalid_date_format_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } else { $data_policy_issue_date = $utility->fix_date($data_policy_issue_date, false, $utility); $data_policy_start_date = $utility->fix_date($data_policy_start_date, false, $utility); $data_policy_end_date = $utility->fix_date($data_policy_end_date, false, $utility); $date_diff = $db->select("SELECT DATEDIFF('".$data_policy_end_date."', '".$data_policy_start_date."')"); if($date_diff < 0){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['confusing_date']), $row); $GLOBALS['not_inserted_count']++; return 0; } else if($date_diff < 365){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['less_than_year_date']), $row); $GLOBALS['not_inserted_count']++; return 0; } } } } } } } // VALIDATE BRAND $data_brand_count = $db->select("SELECT COUNT(1) FROM finance_source_brand WHERE name = '$data_brand'"); $brand = ""; if($data_brand_count > 0){ $brand = $db->select("SELECT id FROM finance_source_brand WHERE name = '$data_brand' AND status != 0"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_brand']), $row); $GLOBALS['not_inserted_count']++; return 0; } //end validate brand // validate model $data_model_count = $db->select("SELECT COUNT(1) FROM finance_source_car_model WHERE name = '$data_model'"); $model = ""; if($data_brand_count > 0){ $model = $db->select("SELECT id FROM finance_source_car_model WHERE name = '$data_model' AND status != 0"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_model']), $row); $GLOBALS['not_inserted_count']++; return 0; } //end validate model // validate variant $variant = ""; if($data_variant != ""){ $data_variant_count = $db->select("SELECT COUNT(1) FROM finance_source_car_model_variant WHERE name = '$data_variant'"); if($data_variant_count > 0){ $variant = $db->select("SELECT id FROM finance_source_car_model_variant WHERE name = '$data_variant' AND status != 0"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_variant']), $row); $GLOBALS['not_inserted_count']++; return 0; } } //end validate variant //start validate dealer if(!$utility->isNotEmpty($data_customer_dealer)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_dealer']), $row); $GLOBALS['no_dealer_count']++; $GLOBALS['not_inserted_count']++; return 0; } // VALIDATE DEALER $data_customer_dealer_count = $db->select("SELECT COUNT(1) FROM source_dealer WHERE code = '$data_customer_dealer' OR name = '$data_customer_dealer'"); $company = ""; if($data_customer_dealer_count > 0){ $data_customer_dealer = $db->select("SELECT id FROM source_dealer WHERE code = '$data_customer_dealer' OR name = '$data_customer_dealer'"); $company = $db->select("SELECT company_id FROM source_company_dealer WHERE dealer_id = '$data_customer_dealer' AND status != 0"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_dealer']), $row); $GLOBALS['no_dealer_count']++; $GLOBALS['not_inserted_count']++; return 0; } //end validate dealer // VALIDATE INSURANCE PROVIDER $verified_insurance_provider_count = $db->select("SELECT COUNT(1) FROM finance_ins_provider WHERE name = '$data_insurance_provider' AND status = 1"); if($verified_insurance_provider_count > 0){ $data_insurance_provider = $db->select("SELECT id FROM finance_ins_provider WHERE name = '$data_insurance_provider'"); } else { if(isset($GLOBALS['insurance_provider_map'][(($data_insurance_provider))])){ $data_insurance_provider = str_replace("'", "\'", $GLOBALS['insurance_provider_map'][(($data_insurance_provider))]); $data_insurance_provider = $db->select("SELECT id FROM finance_ins_provider WHERE name = '$data_insurance_provider'"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_ins_provider']), $row); $GLOBALS['not_inserted_count']++; return 0; } } // VALIDATE INSURANCE BUSINESS TYPE $verified_insurance_business_type_count = $db->select("SELECT COUNT(1) FROM finance_ins_business_type WHERE name = '$data_insurance_type' AND status = 1"); if($verified_insurance_business_type_count > 0){ $data_insurance_type = $db->select("SELECT id FROM finance_ins_business_type WHERE name = '$data_insurance_type'"); } else { if(isset($GLOBALS['insurance_type_map'][(($data_insurance_type))])){ $data_insurance_type = $GLOBALS['insurance_type_map'][(($data_insurance_type))]; $data_insurance_type = $db->select("SELECT id FROM finance_ins_business_type WHERE name = '$data_insurance_type'"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_ins_business_type']), $row); $GLOBALS['not_inserted_count']++; return 0; } } //============================================================================================= START CHECK DUPLICATES ====> $duplicate_record_count = getDuplicateRecord($data_policy_number, $plate_cs_number_1, $plate_cs_number_2, $data_policy_issue_date, $data_policy_start_date, $data_policy_end_date, $db); if($duplicate_record_count > 0){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_record']), $row); $GLOBALS['duplicate_record_count']++; $GLOBALS['not_inserted_count']++; return 0; } // NO DUPLICATE $db->sql_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', '$data_customer_fullname', '$data_customer_mobile', '$data_customer_email', '$company', '$data_customer_dealer', '$data_customer_address', '$data_insurance_provider', '$data_policy_number', '$data_policy_issue_date', '$data_policy_start_date', '$data_policy_end_date', '$data_insurance_type', '$data_bank_name', '$data_sales_consultant', '$data_locked_in', '$data_locked_in_year', '$data_paid_amount', '$data_terms', '$data_uploader', '1')"); $GLOBALS['inserted_count']++; } } function getDuplicatePlateCs($plate_cs_number, $db){ $count = $db->select("SELECT COUNT(1) FROM `finance` WHERE `plate_cs_number` = '$plate_cs_number' AND status = 1"); return intval($count); } function getDuplicateEmail($email, $db){ $count = $db->select("SELECT COUNT(1) FROM `finance` WHERE `customer_email` = '$email' AND status = 1"); return intval($count); } function getDuplicateMobile($mobile, $db){ $count = $db->select("SELECT COUNT(1) FROM `finance` WHERE `customer_mobile_number` = '$mobile' AND status = 1"); return intval($count); } function getDuplicateRecord($policy_number, $plate_cs_number_1, $plate_cs_number_2, $policy_issue_date, $policy_start_date, $policy_end_date, $db){ $count = 0; $query = ""; if($plate_cs_number_2 != ""){ $query = "SELECT COUNT(1) FROM `finance` WHERE (`plate_cs_number1` = '$plate_cs_number_1' OR `plate_cs_number1` = '$plate_cs_number_2' OR `plate_cs_number2` = '$plate_cs_number_1' OR `plate_cs_number2` = '$plate_cs_number_2') AND `policy_number` = '$policy_number' AND `policy_start_date` = '$policy_start_date' AND `policy_end_date` = '$policy_end_date' AND `policy_issue_date` = '$policy_issue_date'"; } else { $query = "SELECT COUNT(1) FROM `finance` WHERE (`plate_cs_number1` = '$plate_cs_number_1' OR `plate_cs_number2` = '$plate_cs_number_1') AND `policy_number` = '$policy_number' AND `policy_start_date` = '$policy_start_date' AND `policy_end_date` = '$policy_end_date' AND `policy_issue_date` = '$policy_issue_date'"; } $count = $db->select($query); // echo $count; exit; return intval($count); } function getDuplicatePolicyNumber($policy_number, $db){ $count = $db->select("SELECT COUNT(1) FROM `finance` WHERE `policy_number` = '$policy_number' AND status = 1"); return intval($count); } function isMobileValid($data){ if(substr($data, 0, 4 ) === "+639"){ if(strlen($data) == 13){ return 1; } else { return 0; } } else { return 0; } } function remove_non_numeric($data){ return preg_replace("/[^0-9\/s\/]/", "", $data); // return preg_replace("/[^0-9]/","",$data); } ?>

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