File manager

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

Back
<?php class Import { function getArrayFields($id, $db){ $return_array = array(); $data_list = $db->sql_query("SELECT `name` FROM `source_finance_pdc_fields_name` WHERE `finance_field_pdc_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_headers']); return isset($row[$key]) ? $row[$key] : '' ; } 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 $data_company = $this->checkExist($row, $GLOBALS['company'], $utility); $data_dealer = $this->checkExist($row, $GLOBALS['dealer'], $utility); $data_brand = $this->checkExist($row, $GLOBALS['brand'], $utility); $data_model = $this->checkExist($row, $GLOBALS['model'], $utility); $data_insurance_company = $this->checkExist($row, $GLOBALS['insurance_company'], $utility); $data_policy_number = $this->checkExist($row, $GLOBALS['policy_number'], $utility); $data_client_name = $this->checkExist($row, $GLOBALS['client_name'], $utility); $data_client_mobile = $this->checkExist($row, $GLOBALS['client_mobile'], $utility); $data_client_email = $this->checkExist($row, $GLOBALS['client_email'], $utility); $data_bank_branch = $this->checkExist($row, $GLOBALS['bank_branch'], $utility); $data_check_date = $this->checkExist($row, $GLOBALS['check_date'], $utility); $data_check_number = $this->checkExist($row, $GLOBALS['check_number'], $utility); $data_check_amount = $this->checkExist($row, $GLOBALS['check_amount'], $utility); $data_plate_cs_number = $this->checkExist($row, $GLOBALS['plate_cs_number'], $utility); $data_variant = 0; //data cleansing use for validations $data_client_mobile = $utility->fix_mobile_format(remove_non_numeric($data_client_mobile)); $data_policy_number = $this->alphaNumericOnly($data_policy_number); // validate required data if((!$utility->isNotEmpty($data_dealer))){ // CHECK DEALER $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_dealer']), $row); $GLOBALS['no_dealer_count']++; $GLOBALS['not_inserted_count']++; return 0; } 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_model))){ // CHECK MODEL $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_model']), $row); $GLOBALS['no_model_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK INSURANCE COMPANY $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_insurance_company']), $row); $GLOBALS['no_insurance_company_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK POLICY NUMBER $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_policy_number']), $row); $GLOBALS['no_policy_number_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK CLIENT NAME $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_client_name']), $row); $GLOBALS['no_client_name_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK CLIENT MOBILE $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_client_mobile']), $row); $GLOBALS['no_client_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK CLIENT EMAIL $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_client_email']), $row); $GLOBALS['no_client_email_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK CHECK DATE $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_check_date']), $row); $GLOBALS['no_check_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // CHECK CHECK NUMBER $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_check_number']), $row); $GLOBALS['no_check_number_count']++; $GLOBALS['not_inserted_count']++; return 0; } if((!$utility->isNotEmpty($data_model))){ // 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; } // VALIDATIONS // dealer $data_customer_dealer_count = $db->select("SELECT COUNT(1) FROM source_dealer WHERE code = '$data_dealer' OR name = '$data_dealer'"); $data_company = ""; if($data_customer_dealer_count > 0){ $data_dealer = $db->select("SELECT id FROM source_dealer WHERE code = '$data_dealer' OR name = '$data_dealer'"); $data_company = $db->select("SELECT company_id FROM source_company_dealer WHERE dealer_id = '$data_dealer' AND status != 0"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['dealer_error']), $row); $GLOBALS['dealer_error_count']++; $GLOBALS['not_inserted_count']++; return 0; } // brand $data_brand_count = $db->select("SELECT COUNT(1) FROM finance_source_brand WHERE name = '$data_brand'"); if($data_brand_count > 0){ $data_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; } // model $data_model_count = $db->select("SELECT COUNT(1) FROM finance_source_car_model WHERE name = '$data_model'"); if($data_model_count > 0){ $data_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; } // insurance company $verified_insurance_provider_count = $db->select("SELECT COUNT(1) FROM finance_ins_provider WHERE name = '$data_insurance_company' AND status = 1"); if($verified_insurance_provider_count > 0){ $data_insurance_company = $db->select("SELECT id FROM finance_ins_provider WHERE name = '$data_insurance_company' AND status = 1"); } else { if(isset($GLOBALS['insurance_provider_map'][(($data_insurance_company))])){ $data_insurance_company = str_replace("'", "\'", $GLOBALS['insurance_provider_map'][(($data_insurance_company))]); $data_insurance_company = $db->select("SELECT id FROM finance_ins_provider WHERE name = '$data_insurance_company'"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_ins_provider']), $row); $GLOBALS['not_inserted_count']++; return 0; } } // client mobile number validation if(strpos($data_client_mobile, '/') > 0){ $multi_mobile = array(); $multi_mobile = explode("/", $data_client_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_client_mobile = $multi_mobile[0] . " / " . $multi_mobile[1]; } } else { $data_client_mobile = $utility->fix_mobile_format(remove_non_numeric($data_client_mobile)); $isValidMobile = isMobileValid($data_client_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; } } // client email validation if(strpos($data_client_email, '/') > 0){ $multi_email = array(); $multi_email = explode("/", $data_client_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_client_email = $multi_email[0] . " / " . $multi_email[1]; } } else { $isValidEmail = $utility->isValidEmail($data_client_email); if(!$isValidEmail){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_email']), $row); $GLOBALS['not_inserted_count']++; return 0; } } // bank/branch $data_bank = ""; $data_branch = ""; if(strpos($data_bank_branch, '/') > 0){ $bank_branch_details = array(); $bank_branch_details = explode("/", $data_bank_branch); $data_bank = $bank_branch_details[0]; $data_branch = $bank_branch_details[1]; $bank_data_count = $db->select("SELECT COUNT(1) FROM bank_db WHERE `abbreviation` = '$data_bank' OR `name` = '$data_bank' AND status = 1"); if($bank_data_count > 0){ $data_bank = $db->select("SELECT id FROM bank_db WHERE `abbreviation` = '$data_bank' OR `name` = '$data_bank' AND status = 1"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_bank']), $row); $GLOBALS['unknown_bank_count']++; $GLOBALS['not_inserted_count']++; return 0; } } else { $bank_data_count = $db->select("SELECT COUNT(1) FROM bank_db WHERE `abbreviation` = '$data_bank_branch' OR `name` = '$data_bank_branch' AND status = 1"); if($bank_data_count > 0){ $data_bank = $db->select("SELECT id FROM bank_db WHERE `abbreviation` = '$data_bank_branch' OR `name` = '$data_bank_branch' AND status = 1"); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['unknown_bank']), $row); $GLOBALS['unknown_bank_count']++; $GLOBALS['not_inserted_count']++; return 0; } } // check date $arr_date = array(); $arr_date = explode("/", $data_check_date); if(count($arr_date) == 3){ $month_check = $arr_date[0]; $day_check = $arr_date[1]; $year_check = $arr_date[2]; if(!checkdate($month_check, $day_check, $year_check)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_check_date']), $row); $GLOBALS['not_inserted_count']++; return 0; } $data_check_date = $utility->fix_date($data_check_date, false, $utility); } else { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_check_date']), $row); $GLOBALS['not_inserted_count']++; return 0; } // check plate/cs number $data_plate_cs_number_1 = ""; $data_plate_cs_number_2 = ""; if(strpos($data_plate_cs_number, '/') > 0){ $multi_data = array(); $multi_data = explode("/", $data_plate_cs_number); $data_plate_cs_number_1 = $this->alphaNumericOnly($multi_data[0]); $data_plate_cs_number_2 = $this->alphaNumericOnly($multi_data[1]); } else { $data_plate_cs_number_1 = $this->alphaNumericOnly($data_plate_cs_number); } // ===== START CHECK DUPLICATES ====> $duplicate_record_count = getDuplicateRecord($data_plate_cs_number_1, $data_plate_cs_number_2, $data_check_number, $data_check_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 $data_uploader = $_SESSION['user']['id']; $current_date = $db->select("SELECT NOW() AS today"); $db->sql_query("INSERT INTO `finance_pdc` (`plate_cs_number1`, `plate_cs_number2`, `brand_id`, `model_id`, `variant_id`, `client_name`, `client_mobile`, `client_email`, `company_id`, `dealer_id`, `insurance_company_id`, `policy_number`, `bank_id`, `branch`, `check_date`, `pdc_check_number`, `check_amount`, `added_by`, `date_added`, `modified_by`, `date_modified`, `status`) VALUES ('$data_plate_cs_number_1', '$data_plate_cs_number_2', '$data_brand', '$data_model', '$data_variant', '$data_client_name', '$data_client_mobile', '$data_client_email', '$data_company', '$data_dealer', '$data_insurance_company', '$data_policy_number', '$data_bank', '$data_branch', '$data_check_date', '$data_check_number', '$data_check_amount', '$data_uploader', '$current_date', '$data_uploader', '$current_date', 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($plate_cs_number_1, $plate_cs_number_2, $check_number, $check_date, $db){ $count = 0; $query = ""; if($plate_cs_number_2 != ""){ $query = "SELECT COUNT(1) FROM `finance_pdc` 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 `pdc_check_number` = '$check_number' AND `check_date` = '$check_date'"; } else { $query = "SELECT COUNT(1) FROM `finance_pdc` WHERE (`plate_cs_number1` = '$plate_cs_number_1' OR `plate_cs_number2` = '$plate_cs_number_1') AND `pdc_check_number` = '$check_number' AND `check_date` = '$check_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