File manager

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

Back
<?php class Import { function getArrayFields($id, $db) { $return_array = array(); $data_list = $db->sql_query("SELECT `name` FROM `source_dms_fields_name_v2` WHERE `dms_field_id` = '$id' "); foreach ($data_list as $row) { $return_array = array_merge($return_array, array($row['name'])); } 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_dms_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 getProfessionID($data, $utility, $db) { $id = 0; if ($utility->isNotEmpty($data)) { $data_id = $db->select("SELECT id FROM source_profession WHERE name = '$data' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty($data_id)) { $id = $data_id; } else { $id = $db->sql_query_id("INSERT INTO source_profession (name,status) VALUES ('$data','1')"); } } return $id; } function getNatureOfBusinessID($data, $utility, $db) { $id = 0; if ($utility->isNotEmpty($data)) { $data_id = $db->select("SELECT id FROM source_nature_of_business WHERE name = '$data' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty($data_id)) { $id = $data_id; } else { $current_user = $_SESSION['user']['id']; $id = $db->sql_query_id("INSERT INTO source_nature_of_business (name,`added_by`,`last_modified_by`,`add_source`,status) VALUES ('$data','$current_user','$current_user',0,'1')"); } } 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; // $id = $db -> sql_query_id("INSERT INTO sales_consultant (first_name,status) VALUES ('$name','1')"); } } return $id; } function getVehicleColorID($color_file, $utility, $db) { $color_id = 0; if ($utility->isNotEmpty($color_file)) { $color_data = $db->select("SELECT id FROM vehicle_color WHERE name = '$color_file' and status = 1 LIMIT 1"); if ($utility->isNotEmpty($color_data)) { $color_id = $color_data; } else { $color_id = $db->sql_query_id("INSERT INTO vehicle_color (name,status) VALUES ('$color_file','1')"); } } return $color_id; } function getVehicleBrandID($brand_file, $utility, $db) { $brand_id = 0; if ($utility->isNotEmpty($brand_file)) { $brand_file = $db->escape($brand_file); $brand_data = $db->select("SELECT id FROM source_brand WHERE name = '$brand_file' and status = 1 LIMIT 1"); if ($utility->isNotEmpty($brand_data)) { $brand_id = $brand_data; } else { $brand_id = 0; // $db -> sql_query_id("INSERT INTO source_brand (name,status) VALUES ('$brand_file','1')"); } } return $brand_id; } function getVehicleModelID($brand_id, $model_name, $utility, $db) { $model_id = 0; if ($utility->isNotEmpty($model_name)) { $model_data = $db->select("SELECT id FROM source_car_model WHERE brand_id = '$brand_id' AND name = '$model_name' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty(trim($model_data))) { $model_id = $model_data; } else { $model_id = 0; //$db -> sql_query_id("INSERT INTO source_car_model (brand_id,name,model_variant_description,status) VALUES ('$brand_id','$model_name','$model_description','1')"); } } return $model_id; } function getVehicleModelVariantID($model_id, $model_variant_name, $utility, $db) { $model_variant_id = 0; if ($utility->isNotEmpty($model_variant_name)) { $model_variant_data = $db->select("SELECT id FROM source_car_model_variant WHERE car_model_id = '$model_id' AND name = '$model_variant_name' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty(trim($model_variant_data))) { $model_variant_id = $model_variant_data; } else { $model_variant_id = $db->sql_query_id("INSERT INTO source_car_model_variant (car_model_id,name,status) VALUES ('$model_id','$model_variant_name','1')"); } } return $model_variant_id; } function getLeadSourceID($data, $utility, $db) { $source_of_contact = 0; if ($utility->isNotEmpty(trim($data))) { $data = $db->escape($data); $source_inquiry_data = $db->select("SELECT id FROM source_of_inquiry WHERE name = '$data' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty($source_inquiry_data)) { $source_of_contact = $source_inquiry_data; } else { $source_of_contact = $db->sql_query_id("INSERT INTO source_of_inquiry (name,status) VALUES ('$data','1')"); } } return $source_of_contact; } function getModeOfSale($data, $utility, $db) { $id = 0; if ($utility->isNotEmpty(trim($data))) { $data_id = $db->select("SELECT id FROM source_mode_of_sale WHERE name = '$data' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty($data_id)) { $id = $data_id; } else { $id = $db->sql_query_id("INSERT INTO source_mode_of_sale (name,status) VALUES ('$data','1')"); } } return $id; } function getTypeOfPayment($data, $utility, $db) { $id = 0; if ($utility->isNotEmpty($data)) { $data_id = $db->select("SELECT id FROM source_type_of_payment WHERE name = '$data' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty($data_id)) { $id = $data_id; } else { $id = $db->sql_query_id("INSERT INTO source_type_of_payment (name,status) VALUES ('$data','1')"); } } return $id; } function getInsuranceCompanyID($insurance_company_file, $utility, $db) { $insurance_company_id = 0; if ($utility->isNotEmpty($insurance_company_file)) { $insurance_company_data = $db->select("SELECT id FROM source_insurance_company WHERE name = '$insurance_company_file' and status = 1 LIMIT 1"); if ($utility->isNotEmpty($insurance_company_data)) { $insurance_company_id = $insurance_company_data; } else { $insurance_company_id = $db->sql_query_id("INSERT INTO source_insurance_company (name,status) VALUES ('$insurance_company_file','1')"); } } return $insurance_company_id; } function getAffiliationID($affiliation_category_id, $data, $utility, $db) { if ($data == '') { return ''; //to prevent inserting even no affiliations } $id = 0; if ($utility->isNotEmpty($data)) { $data = $db->escape($data); $id_data = $db->select("SELECT id FROM `customer_affiliations_content` WHERE `name` = '$data' AND affiliation_id = '$affiliation_category_id' AND status = 1 LIMIT 1"); if ($utility->isNotEmpty($id_data)) { $id = $id_data; } else { $id = $db->sql_query_id("INSERT INTO customer_affiliations_content (affiliation_id,name) VALUES ('$affiliation_category_id','$data')"); } } 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 get_aha_status($status) { if (strpos($status, 'Y') !== false \|\| strpos($status, 'y') !== false) { return true; }; return false; } function getDuplicateByEmailAndMobile($data_email, $data_mobile, $data_company_dealer, $utility, $db) { if ($utility->isNotEmpty($data_mobile)) { $append_mobile = " AND (`mobile_phone_1` = '$data_mobile' "; } else { $append_mobile = ""; } if ($utility->isNotEmpty($data_email)) { $append_email = " OR `email_1` = '$data_email') "; } else { $append_email = ")"; } $query = "SELECT c.id as customer_record_id,c.type,c.corporation_name,c.first_name,c.last_name,cc.email_1,cc.mobile_phone_1,cd.company_dealer_id FROM customer c INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN customer_dms cd ON c.id = cd.customer_record_id WHERE 1 %s %s AND c.type = 1 AND c.status = 1 "; return ($db->return_result(sprintf($query, $append_mobile, $append_email))); // exit; } function alphaNumericOnly($s) { $str = preg_replace("/[^a-zA-Z0-9]+/", "", $s); if (strlen($str) < 5) { return ""; } return $str; } // function fix_mobile_format($data){ // if(strlen($data) < 5){ // return ""; // } // $final_data = ""; // //if area code equal to 639xx it will change to 09xxxxxxxxx // if(substr($data,0,2) === "09"){ // $final_data = "+639".substr($data,2,strlen($data)); // }else if(substr($data,0,3) === "639"){ // $final_data = "+639".substr($data,3,strlen($data)); // }else if(substr($data,0,1) === "9" && strlen($data) == 10){ // $final_data = "+639".substr($data,1,strlen($data)); // }else{ // $final_data = $data; // } // //if number start with 09xxx, check the length, if length is not 11 return blank, if not 09xxx return it // if(substr($final_data,0,4) === "+639"){ // if(strlen($final_data) == 13){ // return $final_data; // }else{ // return $final_data; // } // } // return $final_data; // } function getDmsCompanyId($data_dealer, $utility, $db) { $query = "SELECT sd.id as dms_id,sc.id as company_id FROM source_dms sd INNER JOIN source_company sc ON sd.id = sc.dms_id INNER JOIN source_company_dealer scd ON sc.id = scd.company_id WHERE sd.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 getDuplicatePlate($plate_number, $utility, $db) { $count = $db->select("SELECT COUNT(1) FROM `vehicle` v INNER JOIN sales s ON v.id = s.vehicle_id WHERE v.`plate_number` = '$plate_number' AND v.`status` = 1 AND s.status = 1"); return intval($count); } function getDuplicateCS($conduction_sticker, $utility, $db) { $count = $db->select("SELECT COUNT(1) FROM `vehicle` v INNER JOIN sales s ON v.id = s.vehicle_id WHERE v.`conduction_sticker` = '$conduction_sticker' AND v.`status` = 1 AND s.status = 1"); return intval($count); } function check_string_exist($str) { $str = strtolower($str); $none = array("na", "n/a", "none"); if (in_array($str, $none)) { return true; } return false; } function read_city_id($data_city, $db) { return $db->select("SELECT ac.`id` FROM `all_city` ac LEFT OUTER JOIN all_state astate ON ac.provCode = astate.provCode WHERE ac.status = 1 AND CONCAT(ac.`citymunDesc`, ' (',astate.provDesc,')') LIKE '%$data_city%' ORDER by ac.`citymunDesc` ASC LIMIT 1"); } function start_import($row, $customer_class, $utility, $db) { // print_r($row);exit; //store raw data from excel $data_dms = $this->checkExist($row, $GLOBALS['dms'], $utility); $data_company = $this->checkExist($row, $GLOBALS['company'], $utility); $data_dealer = $this->checkExist($row, $GLOBALS['dealer'], $utility); $data_customer_no = $this->checkExist($row, $GLOBALS['customer_no'], $utility); $data_corporation_name = $this->checkExist($row, $GLOBALS['corporation_name'], $utility); $data_first_name = $this->checkExist($row, $GLOBALS['first_name'], $utility); $data_middle_name = $this->checkExist($row, $GLOBALS['middle_name'], $utility); $data_last_name = $this->checkExist($row, $GLOBALS['last_name'], $utility); $data_suffix = $this->checkExist($row, $GLOBALS['suffix'], $utility); $data_profession = $this->checkExist($row, $GLOBALS['profession'], $utility); $data_position = $this->checkExist($row, $GLOBALS['position'], $utility); $data_business_company = $this->checkExist($row, $GLOBALS['business_company'], $utility); $data_nature_of_business = $this->checkExist($row, $GLOBALS['nature_of_business'], $utility); $data_affiliation = $this->checkExist($row, $GLOBALS['affiliation'], $utility); $data_facebook = $this->checkExist($row, $GLOBALS['facebook'], $utility); $data_address_1 = $this->checkExist($row, $GLOBALS['address_1'], $utility); $data_address_2 = $this->checkExist($row, $GLOBALS['address_2'], $utility); $data_email = $this->checkExist($row, $GLOBALS['email'], $utility); $data_mobile = $this->checkExist($row, $GLOBALS['mobile'], $utility); $data_business_phone = $this->checkExist($row, $GLOBALS['business_phone'], $utility); $data_birthday = $this->checkExist($row, $GLOBALS['birthday'], $utility); $data_gender = $this->checkExist($row, $GLOBALS['gender'], $utility); $data_brand = $this->checkExist($row, $GLOBALS['brand'], $utility); $data_model = $this->checkExist($row, $GLOBALS['model'], $utility); $data_model_variant = $this->checkExist($row, $GLOBALS['model_variant'], $utility); $data_color = $this->checkExist($row, $GLOBALS['color'], $utility); $data_conduction_sticker = $this->checkExist($row, $GLOBALS['conduction_sticker'], $utility); $data_plate_number = $this->checkExist($row, $GLOBALS['plate_number'], $utility); $data_vin = $this->checkExist($row, $GLOBALS['vin'], $utility); $data_registration_no = $this->checkExist($row, $GLOBALS['registration_no'], $utility); $data_date = $this->checkExist($row, $GLOBALS['date'], $utility); $data_release_no = $this->checkExist($row, $GLOBALS['release_no'], $utility); $data_mode_of_sale = $this->checkExist($row, $GLOBALS['mode_of_sale'], $utility); $data_type_of_payment = $this->checkExist($row, $GLOBALS['type_of_payment'], $utility); $data_insurance = $this->checkExist($row, $GLOBALS['insurance'], $utility); $data_source = $this->checkExist($row, $GLOBALS['source'], $utility); $data_offered_vehicle = $this->checkExist($row, $GLOBALS['offered_vehicle'], $utility); $data_sales_manager = $this->checkExist($row, $GLOBALS['sales_manager'], $utility); $data_sales_consultant = $this->checkExist($row, $GLOBALS['sales_consultant'], $utility); $data_model_year = $this->checkExist($row, $GLOBALS['model_year'], $utility); $data_end_user = $this->checkExist($row, $GLOBALS['end_user'], $utility); $data_car_club = $this->checkExist($row, $GLOBALS['car_club'], $utility); $data_city = $this->checkExist($row, $GLOBALS['city'], $utility); $data_aha_status = $this->checkExist($row, $GLOBALS['aha_status'], $utility); //data cleansing use for validations $data_email = $utility->isValidEmail($data_email); $data_email = $db->escape($data_email); $data_mobile = $utility->fix_mobile_format($utility->remove_non_numeric($data_mobile)); $data_mobile = $db->escape($data_mobile); $data_business_phone = $utility->fix_mobile_format($utility->remove_non_numeric($data_business_phone)); $data_birthday = $utility->fix_date_v2($data_birthday); $data_date = $utility->fix_date_v2($data_date); $data_conduction_sticker = $this->alphaNumericOnly($data_conduction_sticker); $data_plate_number = $this->alphaNumericOnly($data_plate_number); $data_vin = $this->alphaNumericOnly($data_vin); //end data cleansing use for validations //generated ids $data_customer_record_id = $db->select("SELECT UUID_SHORT()"); $data_vehicle_record_id = $db->select("SELECT UUID_SHORT()"); $data_sales_record_id = $db->select("SELECT UUID_SHORT()"); $data_duplicate_customer_record = false; $data_duplicate_customer_record_identical = false; //validate required data if (!$utility->isNotEmpty($data_date)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_date']), $row); $GLOBALS['no_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } $date_now = date("Y-m-d"); // this format is string comparable //validate date data if (!($date_now >= $data_date)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_date']), $row); $GLOBALS['invalid_date_count']++; $GLOBALS['not_inserted_count']++; return 0; } //validate birthday if ( (!$utility->isNotEmpty($data_corporation_name) && ($utility->isNotEmpty($data_first_name) && $utility->isNotEmpty($data_last_name))) ) { if ($utility->isNotEmpty($data_birthday)) { // echo $data_birthday;exit; $from = new DateTime($data_birthday); $to = new DateTime('today'); $customer_ind_age = intval($from->diff($to)->y); if ($customer_ind_age < 17) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['under_age']), $row); $GLOBALS['under_age_count']++; $GLOBALS['not_inserted_count']++; return 0; } } } if ( (!$utility->isNotEmpty($data_corporation_name) && (!$utility->isNotEmpty($data_first_name) \|\| !$utility->isNotEmpty($data_last_name))) ) { // if(!$utility->isNotEmpty($data_first_name) \|\| !$utility->isNotEmpty($data_last_name)){ $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_name']), $row); $GLOBALS['no_name_count']++; $GLOBALS['not_inserted_count']++; return 0; // } } // if(!$utility->isNotEmpty($data_first_name) \|\| !$utility->isNotEmpty($data_last_name)){ // if(!$utility->isNotEmpty($data_corporation_name) ){ // $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_name']),$row); // $GLOBALS['no_name_count']++; // $GLOBALS['not_inserted_count']++; // return 0; // } // } if ($utility->isNotEmpty($data_first_name) && $utility->isNotEmpty($data_last_name)) { if (!$utility->isNotEmpty($data_mobile)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_mobile']), $row); $GLOBALS['no_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } if (strlen($data_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_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; } } if (strlen($data_mobile) <= 3) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_mobile']), $row); $GLOBALS['invalid_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } if (!$utility->isNotEmpty($data_conduction_sticker)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_cs']), $row); $GLOBALS['no_cs_count']++; $GLOBALS['not_inserted_count']++; return 0; } //start validate dealer if (!$utility->isNotEmpty($data_dealer)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_dealer']), $row); $GLOBALS['no_dealer_count']++; $GLOBALS['not_inserted_count']++; return 0; } $data_dealer = $this->getDealerIdByNameOrCode($data_dealer, $utility, $db); if (intval($data_dealer) === 0) { $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 //get dms & company using dealer id $data_dms_company = $this->getDmsCompanyId($data_dealer, $utility, $db); if (count($data_dms_company) < 1) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_dealer']), $row); $GLOBALS['no_dealer_count']++; $GLOBALS['not_inserted_count']++; return 0; } $data_dms = $data_dms_company["dms"]; $data_company = $data_dms_company["company"]; //start validate company dealer //get id from source company dealer // echo $data_company.' ' .$data_dealer." asda";exit; $data_company_dealer = $this->getCompanyDealerIdByName($data_company, $data_dealer, $utility, $db); if (intval($data_company_dealer) === 0) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_dealer']), $row); $GLOBALS['no_dealer_count']++; $GLOBALS['not_inserted_count']++; return 0; } //end validate company dealer //validate sc/gm name $data_sales_manager = $this->getSalesConsultant($data_sales_manager, $utility, $db); if (!$data_sales_manager) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_gm']), $row); $GLOBALS['invalid_gm_count']++; $GLOBALS['not_inserted_count']++; return 0; } $data_sales_consultant = $this->getSalesConsultant($data_sales_consultant, $utility, $db); if (!$data_sales_consultant) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['invalid_sc']), $row); $GLOBALS['invalid_sc_count']++; $GLOBALS['not_inserted_count']++; return 0; } //check duplicate vvehicle if ($utility->isNotEmpty($data_plate_number)) { if (in_array($data_plate_number, $GLOBALS['vehicle_data_plate'])) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_plate']), $row); $GLOBALS['duplicate_plate_count']++; $GLOBALS['not_inserted_count']++; return 0; } else if ($this->getDuplicatePlate($data_plate_number, $utility, $db)) { //separet to lessen databasae request $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_plate']), $row); $GLOBALS['duplicate_plate_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { $GLOBALS['vehicle_data_plate'][] = $data_plate_number; //if duplicate customer this will remain in GLOBALS['vehicle_data_plate'] // this will be remove } } if ($utility->isNotEmpty($data_conduction_sticker)) { if (in_array($data_conduction_sticker, $GLOBALS['vehicle_data_cs'])) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_cs']), $row); $GLOBALS['duplicate_cs_count']++; $GLOBALS['not_inserted_count']++; return 0; } else if ($this->getDuplicateCS($data_conduction_sticker, $utility, $db)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_cs']), $row); $GLOBALS['duplicate_cs_count']++; $GLOBALS['not_inserted_count']++; return 0; } else { $GLOBALS['vehicle_data_cs'][] = $data_conduction_sticker; //if duplicate customer this will remain in GLOBALS['vehicle_data_cs'] // this will be remove } } $data_brand = $this->getVehicleBrandID($data_brand, $utility, $db); if (!$data_brand) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_brand']), $row); $GLOBALS['no_brand_count']++; $GLOBALS['not_inserted_count']++; return 0; } $data_model = $this->getVehicleModelID($data_brand, $data_model, $utility, $db); if (!$data_model) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['no_model']), $row); $GLOBALS['no_model_count']++; $GLOBALS['not_inserted_count']++; return 0; } if ($this->get_aha_status($data_aha_status)) { $data_aha_status = 1; } else { $data_aha_status = 0; } //corporation area $data_nature_of_business = $this->getNatureOfBusinessID($data_nature_of_business, $utility, $db); $data_affiliation = $this->getAffiliationID('1', $data_affiliation, $utility, $db); $data_car_club = $this->getAffiliationID('5', $data_car_club, $utility, $db); $customer_data_query = 'INSERT INTO customer (`id`,`corporation_name`,`salutation_id`,`first_name`,`middle_name`,`last_name`,`suffix_id`,`gender_id`,`nationality_id`,`date_of_birth`,`marital_status_id`,`source_id`,`mode_of_contact_id`,`spouse_name`,`no_of_children`,`occupation`,`contact_person`,/`sales_person`,/`date_created`,`time_created`,`upload_by`,`religion_id`,`nature_of_business_id`,`profession_id`,`corporation_id`,`type`,`upload_source`,`aha_status`,`status`) VALUES %s '; $customer_record_query = 'INSERT INTO customer_dms (`customer_record_id`,`customer_dms_id`,`company_dealer_id`,`status`) VALUES %s '; $data_corporation_name_final = ""; $upload_source = 0; if ($utility->isNotEmpty($data_corporation_name)) { $data_corporation_name_final = $data_corporation_name; } else if ($utility->isNotEmpty($data_business_company)) { $data_corporation_name_final = $data_business_company; $upload_source = 1; } $data_corporation_name_final_id = 0; if ($utility->isNotEmpty($data_corporation_name_final)) { $corporation_details_info_query = "SELECT c.id,c.`corporation_name` FROM `customer` c WHERE c.`corporation_name` = '$data_corporation_name_final' AND c.status = 1 AND c.type = 2 GROUP BY c.id LIMIT 1 "; $data_corporation_info = array(); $corporation_details_info = $db->sql_query($corporation_details_info_query); foreach ($corporation_details_info as $data) { $data_corporation_info = $data; } if (count($data_corporation_info) > 0) { $dms_comapny_dealer_count = $customer_class->read_customer_dms_company_dealer($data_corporation_info['id'], $data_dms, $data_company_dealer, $db); if ($dms_comapny_dealer_count > 0) { // has identical record $data_corporation_name_final_id = $data_corporation_info['id']; } else { // has same name but not same dms $customer_record = "('" . $data_corporation_info['id'] . "','$data_customer_no','$data_company_dealer','1')"; $inserted_customer_count = $db->sql_query_num_inserted(sprintf($customer_record_query, $customer_record)); } } else { //has no any record $data_corporation_name_final_id = $db->select("SELECT UUID_SHORT()"); $customer_data = "('$data_corporation_name_final_id','$data_corporation_name_final','','','','','','','608','','','','','','','','','$data_date','','" . $GLOBALS['user_id'] . "','','$data_nature_of_business','0','0','2','$upload_source','$data_aha_status','1')"; $db->sql_query_id(sprintf($customer_data_query, $customer_data)); if ($utility->isNotEmpty($data_city)) { $address_1_city_id = $db->select("SELECT ac.`id` FROM `all_city` ac LEFT OUTER JOIN all_state astate ON ac.provCode = astate.provCode WHERE ac.status = 1 AND CONCAT(ac.`citymunDesc`, ' (',astate.provDesc,')') LIKE '%$data_city%' ORDER by ac.`citymunDesc` ASC LIMIT 1"); } else { $address_1_city_id = ''; } $customer_contact = "('','','$data_facebook','','','','$data_corporation_name_final_id','$data_address_1','$data_address_2','0','0','$address_1_city_id','','','','$data_email','','$data_business_phone','','$data_mobile','','' ,'1')"; $customer_contact_query = 'INSERT INTO customer_contact (`viber`,`telegram`,`facebook`,`instagram`,`whatsapp`,`website`,`customer_id`,`address_1`,`address_2`,`address_1_state`,`address_2_state`,`address_1_city`,`address_2_city`,`address_1_postal`,`address_2_postal`,`email_1`,`email_2`,`business_phone`,`fax_phone`,`mobile_phone_1`,`mobile_phone_2`,`residential_phone`,`status`) VALUES ' . $customer_contact . ' '; $db->sql_query($customer_contact_query); $customer_record = "('$data_corporation_name_final_id','$data_customer_no','$data_company_dealer','1')"; $inserted_customer_count = $db->sql_query_num_inserted(sprintf($customer_record_query, $customer_record)); if (!($utility->isNotEmpty($data_first_name) && $utility->isNotEmpty($data_last_name))) { if ($utility->isNotEmpty($data_affiliation)) { $GLOBALS['customer_affiliation'][] = "('$data_affiliation', '$data_corporation_name_final_id', '1' )"; } if ($utility->isNotEmpty($data_car_club)) { $GLOBALS['customer_affiliation'][] = "('$data_car_club', '$data_corporation_name_final_id', '1' )"; } } } } //start check duplicates //set to indicidual only if (($utility->isNotEmpty($data_first_name) && $utility->isNotEmpty($data_last_name))) { $duplicate_information = $this->getDuplicateByEmailAndMobile($data_email, $data_mobile, $data_company_dealer, $utility, $db); //will read customer individual only if (count($duplicate_information) > 0) { if ($this->in_array_y($duplicate_information, 'company_dealer_id', $data_company_dealer)) { if ($duplicate_information[0]['type'] == 1) { if ( ($data_first_name == $duplicate_information[0]['first_name']) && ($data_last_name == $duplicate_information[0]['last_name']) && !$data_duplicate_customer_record_identical ) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_customer']), $row); $GLOBALS['duplicate_customer_count']++; $GLOBALS['not_inserted_count']++; $data_duplicate_customer_record_identical = true; // return 0; } } else if ($duplicate_information[0]['type'] == 2) { //corporation will ignore all time if ( $data_corporation_name == $duplicate_information[0]['corporation_name'] && !$data_duplicate_customer_record_identical ) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_customer']), $row); $GLOBALS['duplicate_customer_count']++; $GLOBALS['not_inserted_count']++; $data_duplicate_customer_record_identical = true; // return 0; } } if (!$data_duplicate_customer_record_identical) { if ($data_mobile == $duplicate_information[0]['mobile_phone_1']) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_mobile']), $row); $GLOBALS['duplicate_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } if ($data_email == $duplicate_information[0]['email_1']) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_email']), $row); $GLOBALS['duplicate_email_count']++; $GLOBALS['not_inserted_count']++; return 0; } } } else { if ($duplicate_information[0]['type'] == 1) { if (($data_first_name == $duplicate_information[0]['first_name']) && ($data_last_name == $duplicate_information[0]['last_name']) && !$data_duplicate_customer_record ) { $data_duplicate_customer_record = true; } } else if ($duplicate_information[0]['type'] == 2) { //corporation will ignore all time if ( $data_corporation_name == $duplicate_information[0]['corporation_name'] && !$data_duplicate_customer_record ) { $data_duplicate_customer_record = true; } } if (!$data_duplicate_customer_record) { if ($data_mobile == $duplicate_information[0]['mobile_phone_1']) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_mobile']), $row); $GLOBALS['duplicate_mobile_count']++; $GLOBALS['not_inserted_count']++; return 0; } if ($data_email == $duplicate_information[0]['email_1']) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array($GLOBALS['duplicate_email']), $row); $GLOBALS['duplicate_email_count']++; $GLOBALS['not_inserted_count']++; return 0; } } } } if (!$data_duplicate_customer_record_identical) { //INSERT CUSTOMER DATA if (!$data_duplicate_customer_record) { //data cleansing use for inserting final data -customer if (($utility->isNotEmpty($data_first_name) && $utility->isNotEmpty($data_last_name))) { $data_suffix = $this->getSuffix($data_suffix); $data_profession = $this->getProfessionID($data_profession, $utility, $db); $data_gender = $this->getGenderID($data_gender, $utility); $customer_data = "('$data_customer_record_id','','','$data_first_name','$data_middle_name','$data_last_name','$data_suffix','$data_gender','608','$data_birthday','','','','','','$data_position','','$data_date','','" . $GLOBALS['user_id'] . "','','$data_nature_of_business','$data_profession','$data_corporation_name_final_id','1','0','$data_aha_status','1')"; $db->sql_query_id(sprintf($customer_data_query, $customer_data)); if ($utility->isNotEmpty($data_city)) { $address_1_city_id = $db->select("SELECT ac.`id` FROM `all_city` ac LEFT OUTER JOIN all_state astate ON ac.provCode = astate.provCode WHERE ac.status = 1 AND CONCAT(ac.`citymunDesc`, ' (',astate.provDesc,')') LIKE '%$data_city%' ORDER by ac.`citymunDesc` ASC LIMIT 1"); } else { $address_1_city_id = ''; } $customer_contact = "('','','$data_facebook','','','','$data_customer_record_id','$data_address_1','$data_address_2','0','0','$address_1_city_id','','','','$data_email','','$data_business_phone','','$data_mobile','','' ,'1')"; $customer_contact_query = 'INSERT IGNORE INTO customer_contact (`viber`,`telegram`,`facebook`,`instagram`,`whatsapp`,`website`,`customer_id`,`address_1`,`address_2`,`address_1_state`,`address_2_state`,`address_1_city`,`address_2_city`,`address_1_postal`,`address_2_postal`,`email_1`,`email_2`,`business_phone`,`fax_phone`,`mobile_phone_1`,`mobile_phone_2`,`residential_phone`,`status`) VALUES ' . $customer_contact . ' '; $db->sql_query($customer_contact_query); if ($utility->isNotEmpty($data_affiliation)) { $GLOBALS['customer_affiliation'][] = "('$data_affiliation', '$data_customer_record_id', '1' )"; } if ($utility->isNotEmpty($data_car_club)) { $GLOBALS['customer_affiliation'][] = "('$data_car_club', '$data_customer_record_id', '1' )"; } } } else { $data_customer_record_id = $duplicate_information[0]['customer_record_id']; //means duplicate information but not dms source //update customer company if (intval($data_corporation_name_final_id) !== 0) { $this->updateIndividualCompany($data_corporation_name_final_id, $data_customer_record_id, $db); } } $customer_record = "('$data_customer_record_id','$data_customer_no','$data_company_dealer','1')"; $inserted_customer_count = $db->sql_query_num_inserted(sprintf($customer_record_query, $customer_record)); $GLOBALS['inserted_count']++; } else { $data_customer_record_id = $duplicate_information[0]['customer_record_id']; //from existing record identical dups //update customer company id if (intval($data_corporation_name_final_id) !== 0) { $this->updateIndividualCompany($data_corporation_name_final_id, $data_customer_record_id, $db); } } } else { // no individual customer $data_customer_record_id = $data_corporation_name_final_id; $GLOBALS['inserted_count']++; } //end check duplicates //set owner if corporation name us no empty if ($utility->isNotEmpty($data_corporation_name)) { $data_customer_record_id = $data_corporation_name_final_id; } //data cleansing use for inserting final data -vehicle & sales // $data_brand = $this->getVehicleBrandID($data_brand,$utility,$db); // $data_model = $this->getVehicleModelID($data_brand,$data_model,$utility,$db); $data_model_variant = $this->getVehicleModelVariantID($data_model, $data_model_variant, $utility, $db); $data_color = $this->getVehicleColorID($data_color, $utility, $db); $data_mode_of_sale = $this->getModeOfSale($data_mode_of_sale, $utility, $db); $data_type_of_payment = $this->getTypeOfPayment($data_type_of_payment, $utility, $db); $data_insurance = $this->getInsuranceCompanyID($data_insurance, $utility, $db); $data_source = $this->getLeadSourceID($data_source, $utility, $db); //INSERT VEHICLE DATA $GLOBALS['vehicle_data'][] = "('$data_vehicle_record_id','$data_customer_no', '$data_customer_record_id','$data_color', '$data_brand', '', '', '', '', '', '', '$data_model', '', '$data_model_variant', NULLIF('$data_plate_number',''), '', '', '$data_vin', NULLIF('$data_conduction_sticker',''), '', '', '','$data_date','', '$data_model_year', '', '', '', '$data_company_dealer', '" . $GLOBALS['user_id'] . "', '', '$data_registration_no','1')"; //INSERT SALES DATA $GLOBALS['sales_data'][] = "('$data_sales_record_id',NULLIF('$data_vehicle_record_id',''),NULLIF('$data_plate_number',''),'$data_customer_no',NULLIF('$data_conduction_sticker',''),NULLIF('',''),'','','','','$data_date','','','$data_insurance','','','','$data_source','','','','','','','','','$data_date','$data_sales_consultant','','$data_company_dealer','" . $GLOBALS['user_id'] . "','1','','','$data_mode_of_sale','$data_type_of_payment','$data_sales_manager','','$data_end_user','','')"; $data_offered_vehicle = $this->check_string_exist($data_offered_vehicle) ? '' : $data_offered_vehicle; $data_offered_vehicle_brand = $this->getVehicleBrandID($data_offered_vehicle, $utility, $db); if ($utility->isNotEmpty($data_offered_vehicle)) { $GLOBALS['vehicle_offered'][] = "('$data_sales_record_id', '$data_offered_vehicle_brand','0','$data_offered_vehicle','1')"; } } }

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