File manager

File manager - Edit - /home/autoph/public_html/connectv1/app/upload/process_excel.php

Back
<?php //TODO //era dealer id ini_set('max_execution_time', '0'); set_time_limit(0); //same code above ini_set('memory_limit', '-1'); include_once("../../cfg/db.php"); require_once 'process_import.php'; include_once("../language/word_mapping.php"); include '../../vendor/autoload.php'; //import PHPSpreadSheet // include 'era_power.php'; //import ERA Functions include_once("../../api/controllers/utility.php"); $utility = new Utility(); if (!file_exists('files')) { mkdir('files', 0777, true); } if (!file_exists('logs')) { mkdir('logs', 0777, true); } if (!isset($_SESSION['user']['id'])) { echo "Session Expired."; exit; } if (!isset($_POST['db-type']) && !isset($_POST['dms-type']) && !isset($_POST['dealer']) && !isset($_POST['company'])) { echo "Invalid Action"; exit; } $return_arr["status"] = 0; $return_arr["message"] = " Please select Spreadsheet file."; //Reprot Error Message $GLOBALS['user_id'] = $_SESSION['user']['id']; $GLOBALS['customer_already_exists'] = "Customer already registered."; $GLOBALS['customer_empty_id'] = "Customer number not found"; $GLOBALS['customer_name_empty'] = "Customer name not found"; $GLOBALS['vehicle_already_exists'] = "Vehicle data already added."; $GLOBALS['service_already_exists'] = "Service data already added."; $GLOBALS['service_ro_number_not_found'] = "RO number not found."; $GLOBALS['sale_already_exists'] = "Sales data already added."; $GLOBALS['mobile_already_exists'] = "Mobile already registered."; $GLOBALS['email_already_exists'] = "Email already registered."; $GLOBALS['no_email_address'] = "No email address."; $GLOBALS['no_mobile_number'] = "No mobile number."; $GLOBALS['invalid_email_address'] = "Invalid email address."; $GLOBALS['no_chassis_number'] = "No chassis number."; $GLOBALS['no_serial_number'] = "No serial number."; $GLOBALS['no_vin_number'] = "No VIN number."; $GLOBALS['no_plate_number'] = "No plate number."; $GLOBALS['no_conduction_sticker'] = "No conduction sticker."; $GLOBALS['no_plate_and_conduction_sticker'] = "No plate & conduction sticker."; $GLOBALS['no_order_number'] = "No order number."; $GLOBALS['no_company'] = "Company not found."; $GLOBALS['no_dealer'] = "Dealer not found."; $GLOBALS['invalid_mobile'] = "Invalid mobile."; $GLOBALS['invalid_mobile_count'] = 0; $GLOBALS['current_data_row'] = 0; $GLOBALS['total_data_count'] = 0; $GLOBALS['service_ro_number'] = array(); $GLOBALS['not_inserted_list'] = array(); $GLOBALS['not_inserted_list_v2'] = array(); $GLOBALS['show_error'] = 0; $GLOBALS['last_service_ro'] = ""; $GLOBALS['dms_type'] = $_POST['dms-type']; $GLOBALS['database_type'] = $_POST['db-type']; $GLOBALS['dms_type_name'] = $db->select("SELECT name FROM source_dms WHERE id = '" . $GLOBALS['dms_type'] . "'"); $GLOBALS['database_type_name'] = $db->select("SELECT name FROM source_dms_type WHERE id = '" . $GLOBALS['database_type'] . "'"); $GLOBALS['data_count_list'] = ""; $GLOBALS['data_count_list_v2'] = array(); $GLOBALS['not_inserted_count'] = 0; $GLOBALS['customer_inserted_count'] = 0; $GLOBALS['vehicle_inserted_count'] = 0; $GLOBALS['service_inserted_count'] = 0; $GLOBALS['loop_count'] = 0; $GLOBALS['sale_inserted_count'] = 0; $GLOBALS['customer_not_inserted_count'] = 0; $GLOBALS['vehicle_not_inserted_count'] = 0; $GLOBALS['service_not_inserted_count'] = 0; $GLOBALS['sale_not_inserted_count'] = 0; //customer $GLOBALS['customer_duplicate_count'] = 0; $GLOBALS['customer_sharing_count'] = 0; $GLOBALS['no_dealer_count'] = 0; $GLOBALS['no_company_count'] = 0; $GLOBALS['customer_empty_id_count'] = 0; $GLOBALS['no_email_address_count'] = 0; $GLOBALS['invalid_email_address_count'] = 0; $GLOBALS['customer_name_empty_count'] = 0; $GLOBALS['email_already_exists_count'] = 0; $GLOBALS['mobile_already_exists_count'] = 0; $GLOBALS['no_mobile_number_count'] = 0; //vehicle $GLOBALS['no_plate_and_conduction_sticker_count'] = 0; //service $GLOBALS['service_ro_number_not_found_count'] = 0; //sales $GLOBALS['no_order_number_count'] = 0; $GLOBALS['company'] = $_POST['company']; $GLOBALS['dealer'] = $_POST['dealer']; if (intval($GLOBALS['dealer']) <> 0) { $GLOBALS['company_dealer_id'] = getDealerID($db); } $GLOBALS['database_type_fields'] = array(); $GLOBALS['row_dms_headers'] = array(); $GLOBALS['customer_data_individual'] = array(); $GLOBALS['customer_data_corporation'] = array(); $GLOBALS['customer_master_record'] = array(); $GLOBALS['customer_contact'] = array(); $GLOBALS['customer_business'] = array(); $GLOBALS['customer_hobbies'] = array(); $GLOBALS['customer_affiliation'] = array(); $GLOBALS['customer_contact_person'] = array(); $GLOBALS['vehicle_data'] = array(); $GLOBALS['vehicle_notes'] = array(); $GLOBALS['service_data'] = array(); $GLOBALS['service_history'] = array(); $GLOBALS['vehicle_offered'] = array(); $GLOBALS['sales_data'] = array(); $issue_summary_index = '-5'; if ($_FILES["import_excel"]["name"] != '') { $allowed_extension = array('xls', 'csv', 'xlsx'); $file_array = explode(".", $_FILES["import_excel"]["name"]); $file_extension = end($file_array); if (in_array($file_extension, $allowed_extension)) { $file_name = 'user_' . $_SESSION['user']['employee_id'] . '_' . time() . '.' . $file_extension; move_uploaded_file($_FILES['import_excel']['tmp_name'], $file_name); $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file_name); $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type); try { $spreadsheet = $reader->load($file_name); unlink($file_name); $data = $spreadsheet->getActiveSheet()->toArray(); } catch (\Exception $e) { $GLOBALS['not_inserted_list'] = array(array('id' => 'No Data Inserted', 'type' => '', 'problem' => '<strong>Invalid spreadsheet file. Contact your system administrator.<br><span class="text-danger">Error:</span></strong> ' . $e)); $GLOBALS['show_error']++; $return_arr["status"] = 1; $return_arr["message"] = " Data Imported Successfully."; $return_arr["selected_dms_name"] = $GLOBALS['dms_type_name']; $return_arr["selected_dms_type"] = $GLOBALS['database_type_name']; // $return_arr["valid_data"] = $GLOBALS['inserted_count']; $return_arr["invalid_data"] = $GLOBALS['not_inserted_count']; $return_arr["invalid_data_list"] = $GLOBALS['not_inserted_list']; $return_arr["show_error"] = $GLOBALS['show_error']; $return_arr["data_count_list"] = $GLOBALS['data_count_list']; $return_json = json_encode($return_arr); echo json_encode($return_arr); exit; } // print_r($data);exit; $highestRow = $spreadsheet->getActiveSheet()->getHighestRow(); $highestColumn = $spreadsheet->getActiveSheet()->getHighestColumn(); $ColumnNumber = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); //get fields switch ($GLOBALS['database_type']) { case 1: //customer $array_customer_id = getArrayFields(1, $db); // print_r($array_customer_id);exit; $array_salutation = getArrayFields(2, $db); $array_first_name = getArrayFields(3, $db); $array_middle_name = getArrayFields(4, $db); $array_last_name = getArrayFields(5, $db); $array_suffix = getArrayFields(110, $db); $array_customer_name = getArrayFields(6, $db); $array_gender = getArrayFields(7, $db); $array_birthday = getArrayFields(8, $db); $array_nationality = getArrayFields(10, $db); $array_marital_status = getArrayFields(11, $db); $array_no_of_children = getArrayFields(12, $db); $array_hobby = getArrayFields(13, $db); $array_spouse = getArrayFields(14, $db); $array_occupation = getArrayFields(15, $db); $array_email_1 = getArrayFields(16, $db); $array_email_2 = getArrayFields(17, $db); $array_mobile_1 = getArrayFields(18, $db); $array_mobile_2 = getArrayFields(19, $db); $array_fax_phone = getArrayFields(20, $db); $array_business_phone = getArrayFields(109, $db); $array_residential_phone = getArrayFields(21, $db); $array_address_1 = getArrayFields(108, $db); $array_city_1 = getArrayFields(22, $db); $array_state_1 = getArrayFields(23, $db); $array_postal_1 = getArrayFields(24, $db); $array_address_2 = getArrayFields(25, $db); $array_city_2 = getArrayFields(26, $db); $array_state_2 = getArrayFields(27, $db); $array_postal_2 = getArrayFields(28, $db); $array_source_inquiry = getArrayFields(29, $db); $array_business_name = getArrayFields(30, $db); $array_contact_person = getArrayFields(32, $db); $array_date_created = getArrayFields(33, $db); $array_time_created = getArrayFields(34, $db); $array_dms = getArrayFields(145, $db); $array_company = getArrayFields(134, $db); $array_dealer = getArrayFields(114, $db); $array_religion = getArrayFields(124, $db); $array_affiliation = getArrayFields(123, $db); $array_viber = getArrayFields(125, $db); $array_telegram = getArrayFields(126, $db); $array_facebook = getArrayFields(127, $db); $array_instagram = getArrayFields(128, $db); $array_whatsapp = getArrayFields(129, $db); $array_website = getArrayFields(130, $db); $array_nature_of_work = getArrayFields(131, $db); $array_profession = getArrayFields(132, $db); break; case 2: //vehicle $array_customer_id = getArrayFields(35, $db); $array_color = getArrayFields(36, $db); $array_brand = getArrayFields(37, $db); $array_model = getArrayFields(38, $db); $array_engine = getArrayFields(39, $db); $array_gas = getArrayFields(40, $db); $array_insurance_company = getArrayFields(41, $db); $array_insurance_type = getArrayFields(42, $db); $array_insurance_expiration_date = getArrayFields(43, $db); $array_finance_company = getArrayFields(44, $db); $array_model_variant_description = getArrayFields(45, $db); $array_plate_number = getArrayFields(46, $db); $array_chassis_number = getArrayFields(47, $db); $array_serial_number = getArrayFields(48, $db); $array_vin_number = getArrayFields(49, $db); $array_conduction_sticker = getArrayFields(50, $db); $array_who_drives = getArrayFields(51, $db); $array_purchase_car_as = getArrayFields(52, $db); $array_delivery_date = getArrayFields(53, $db); $array_vehicle_release_year = getArrayFields(54, $db); $array_description = getArrayFields(55, $db); $array_transmission = getArrayFields(56, $db); $array_service_advisor = getArrayFields(57, $db); $array_sales_person = getArrayFields(58, $db); $array_modify_date = getArrayFields(59, $db); $array_notes_for_vehicle = getArrayFields(60, $db); $array_date_created = getArrayFields(61, $db); $array_time_created = getArrayFields(62, $db); $array_dms = getArrayFields(146, $db); $array_company = getArrayFields(135, $db); $array_dealer = getArrayFields(115, $db); $array_model_no = getArrayFields(122, $db); $array_registration_no = getArrayFields(133, $db); break; case 3: //service $array_customer_id = getArrayFields(63, $db); $array_ro_number = getArrayFields(64, $db); $array_ro_date = getArrayFields(65, $db); $array_ro_total = getArrayFields(66, $db); $array_plate_number = getArrayFields(67, $db); $array_conduction_sticker = getArrayFields(68, $db); $array_serial_number = getArrayFields(69, $db); $array_mileage = getArrayFields(70, $db); $array_recommendation = getArrayFields(71, $db); $array_insurance_paid_account = getArrayFields(72, $db); $array_start_date_of_service = getArrayFields(73, $db); $array_end_date_of_service = getArrayFields(74, $db); $array_service_advisor = getArrayFields(75, $db); $array_job_code = getArrayFields(76, $db); $array_notes = getArrayFields(77, $db); $array_problem_cause = getArrayFields(78, $db); $array_correction = getArrayFields(79, $db); $array_job_summary = getArrayFields(80, $db); $array_customer_paid_amount = getArrayFields(81, $db); $array_brand = getArrayFields(111, $db); $array_model = getArrayFields(112, $db); $array_model_variant_description = getArrayFields(120, $db); $array_dms = getArrayFields(147, $db); $array_company = getArrayFields(136, $db); $array_dealer = getArrayFields(116, $db); break; case 4: //sales $array_customer_id = getArrayFields(82, $db); $array_conduction_sticker = getArrayFields(83, $db); $array_order_number = getArrayFields(84, $db); $array_serial_number = getArrayFields(85, $db); $array_engine_number = getArrayFields(86, $db); $array_po_number = getArrayFields(87, $db); $array_floor_plan_amount = getArrayFields(88, $db); $array_activity_date = getArrayFields(89, $db); $array_sales_cost = getArrayFields(90, $db); $array_sale_price = getArrayFields(91, $db); $array_insurance_company = getArrayFields(92, $db); $array_insurance_type = getArrayFields(93, $db); $array_finance_company = getArrayFields(94, $db); $array_insurance_expiration_date = getArrayFields(95, $db); $array_mode_of_contact = getArrayFields(96, $db); $array_source = getArrayFields(97, $db); $array_warehouse = getArrayFields(98, $db); $array_stock_number = getArrayFields(99, $db); $array_age_of_unit = getArrayFields(100, $db); $array_claim_amount = getArrayFields(101, $db); $array_claim_number = getArrayFields(102, $db); $array_body_size_code = getArrayFields(103, $db); $array_deal_number = getArrayFields(104, $db); $array_deal_date = getArrayFields(105, $db); $array_sales_person = getArrayFields(106, $db); $array_delivery_date = getArrayFields(107, $db); $array_plate_number = getArrayFields(113, $db); $array_dms = getArrayFields(148, $db); $array_company = getArrayFields(137, $db); $array_dealer = getArrayFields(117, $db); $array_brand = getArrayFields(118, $db); $array_model = getArrayFields(119, $db); $array_model_variant_description = getArrayFields(121, $db); $array_mode_of_sale = getArrayFields(138, $db); $array_type_of_sale = getArrayFields(139, $db); $array_group_manager = getArrayFields(140, $db); $array_sales_discount = getArrayFields(141, $db); $array_end_user = getArrayFields(142, $db); $array_driver = getArrayFields(143, $db); $array_finance_expiration = getArrayFields(144, $db); $array_vehicle_offered = getArrayFields(149, $db); break; } $column_found = false; foreach ($data as $row) { $row = array_map('trim', $row); $GLOBALS['current_data_row']++; //put excel row to fields array if (!$column_found) { $row = array_map("strtolower", $row); //common fields for all db type $issue_summary_index = getIssueSummaryField('issue summary', $ColumnNumber, $row); $GLOBALS['database_type_fields']['customer_id'] = getFieldFromExcel($row, $ColumnNumber, $array_customer_id); $GLOBALS['database_type_fields']['dms'] = getFieldFromExcel($row, $ColumnNumber, $array_dms); $GLOBALS['database_type_fields']['company'] = getFieldFromExcel($row, $ColumnNumber, $array_company); $GLOBALS['database_type_fields']['dealer'] = getFieldFromExcel($row, $ColumnNumber, $array_dealer); switch ($GLOBALS['database_type']) { case 1: //customer $GLOBALS['database_type_fields']['salutation'] = getFieldFromExcel($row, $ColumnNumber, $array_salutation); $GLOBALS['database_type_fields']['first_name'] = getFieldFromExcel($row, $ColumnNumber, $array_first_name); $GLOBALS['database_type_fields']['middle_name'] = getFieldFromExcel($row, $ColumnNumber, $array_middle_name); $GLOBALS['database_type_fields']['last_name'] = getFieldFromExcel($row, $ColumnNumber, $array_last_name); $GLOBALS['database_type_fields']['suffix'] = getFieldFromExcel($row, $ColumnNumber, $array_suffix); $GLOBALS['database_type_fields']['customer_name'] = getFieldFromExcel($row, $ColumnNumber, $array_customer_name); $GLOBALS['database_type_fields']['gender'] = getFieldFromExcel($row, $ColumnNumber, $array_gender); $GLOBALS['database_type_fields']['birthday'] = getFieldFromExcel($row, $ColumnNumber, $array_birthday); $GLOBALS['database_type_fields']['nationality'] = getFieldFromExcel($row, $ColumnNumber, $array_nationality); $GLOBALS['database_type_fields']['marital_status'] = getFieldFromExcel($row, $ColumnNumber, $array_marital_status); $GLOBALS['database_type_fields']['no_of_children'] = getFieldFromExcel($row, $ColumnNumber, $array_no_of_children); $GLOBALS['database_type_fields']['hobby'] = getFieldFromExcel($row, $ColumnNumber, $array_hobby); $GLOBALS['database_type_fields']['spouse'] = getFieldFromExcel($row, $ColumnNumber, $array_spouse); $GLOBALS['database_type_fields']['occupation'] = getFieldFromExcel($row, $ColumnNumber, $array_occupation); $GLOBALS['database_type_fields']['email_1'] = getFieldFromExcel($row, $ColumnNumber, $array_email_1); $GLOBALS['database_type_fields']['email_2'] = getFieldFromExcel($row, $ColumnNumber, $array_email_2); $GLOBALS['database_type_fields']['mobile_1'] = getFieldFromExcel($row, $ColumnNumber, $array_mobile_1); $GLOBALS['database_type_fields']['mobile_2'] = getFieldFromExcel($row, $ColumnNumber, $array_mobile_2); $GLOBALS['database_type_fields']['fax_phone'] = getFieldFromExcel($row, $ColumnNumber, $array_fax_phone); $GLOBALS['database_type_fields']['business_phone'] = getFieldFromExcel($row, $ColumnNumber, $array_business_phone); $GLOBALS['database_type_fields']['residential_phone'] = getFieldFromExcel($row, $ColumnNumber, $array_residential_phone); $GLOBALS['database_type_fields']['city_1'] = getFieldFromExcel($row, $ColumnNumber, $array_city_1); $GLOBALS['database_type_fields']['state_1'] = getFieldFromExcel($row, $ColumnNumber, $array_state_1); $GLOBALS['database_type_fields']['postal_1'] = getFieldFromExcel($row, $ColumnNumber, $array_postal_1); $GLOBALS['database_type_fields']['address_2'] = getFieldFromExcel($row, $ColumnNumber, $array_address_2); $GLOBALS['database_type_fields']['city_2'] = getFieldFromExcel($row, $ColumnNumber, $array_city_2); $GLOBALS['database_type_fields']['state_2'] = getFieldFromExcel($row, $ColumnNumber, $array_state_2); $GLOBALS['database_type_fields']['postal_2'] = getFieldFromExcel($row, $ColumnNumber, $array_postal_2); $GLOBALS['database_type_fields']['source_inquiry'] = getFieldFromExcel($row, $ColumnNumber, $array_source_inquiry); $GLOBALS['database_type_fields']['business_name'] = getFieldFromExcel($row, $ColumnNumber, $array_business_name); $GLOBALS['database_type_fields']['contact_person'] = getFieldFromExcel($row, $ColumnNumber, $array_contact_person); $GLOBALS['database_type_fields']['date_created'] = getFieldFromExcel($row, $ColumnNumber, $array_date_created); $GLOBALS['database_type_fields']['time_created'] = getFieldFromExcel($row, $ColumnNumber, $array_time_created); $GLOBALS['database_type_fields']['address_1'] = getFieldFromExcel($row, $ColumnNumber, $array_address_1); $GLOBALS['database_type_fields']['affiliation'] = getFieldFromExcel($row, $ColumnNumber, $array_affiliation); $GLOBALS['database_type_fields']['religion'] = getFieldFromExcel($row, $ColumnNumber, $array_religion); $GLOBALS['database_type_fields']['viber'] = getFieldFromExcel($row, $ColumnNumber, $array_viber); $GLOBALS['database_type_fields']['telegram'] = getFieldFromExcel($row, $ColumnNumber, $array_telegram); $GLOBALS['database_type_fields']['facebook'] = getFieldFromExcel($row, $ColumnNumber, $array_facebook); $GLOBALS['database_type_fields']['instagram'] = getFieldFromExcel($row, $ColumnNumber, $array_instagram); $GLOBALS['database_type_fields']['whatsapp'] = getFieldFromExcel($row, $ColumnNumber, $array_whatsapp); $GLOBALS['database_type_fields']['website'] = getFieldFromExcel($row, $ColumnNumber, $array_website); $GLOBALS['database_type_fields']['nature_of_work'] = getFieldFromExcel($row, $ColumnNumber, $array_nature_of_work); $GLOBALS['database_type_fields']['profession'] = getFieldFromExcel($row, $ColumnNumber, $array_profession); // echo $GLOBALS['database_type_fields']['email_1'];exit; if ( isNotEmpty($GLOBALS['database_type_fields']['customer_id']) && isNotEmpty($GLOBALS['database_type_fields']['first_name']) && isNotEmpty($GLOBALS['database_type_fields']['last_name']) && isNotEmpty($GLOBALS['database_type_fields']['customer_name']) /* && isNotEmpty($GLOBALS['database_type_fields']['email_1']) / ) { $column_found = true; $GLOBALS['row_dms_headers'] = $row; continue 2; } break; case 2: //vehicle $GLOBALS['database_type_fields']['color'] = getFieldFromExcel($row, $ColumnNumber, $array_color); $GLOBALS['database_type_fields']['brand'] = getFieldFromExcel($row, $ColumnNumber, $array_brand); $GLOBALS['database_type_fields']['model'] = getFieldFromExcel($row, $ColumnNumber, $array_model); $GLOBALS['database_type_fields']['model_no'] = getFieldFromExcel($row, $ColumnNumber, $array_model_no); $GLOBALS['database_type_fields']['engine'] = getFieldFromExcel($row, $ColumnNumber, $array_engine); $GLOBALS['database_type_fields']['gas'] = getFieldFromExcel($row, $ColumnNumber, $array_gas); $GLOBALS['database_type_fields']['insurance_company'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_company); $GLOBALS['database_type_fields']['insurance_type'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_type); $GLOBALS['database_type_fields']['insurance_expiration_date'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_expiration_date); $GLOBALS['database_type_fields']['finance_company'] = getFieldFromExcel($row, $ColumnNumber, $array_finance_company); $GLOBALS['database_type_fields']['model_variant_description'] = getFieldFromExcel($row, $ColumnNumber, $array_model_variant_description); $GLOBALS['database_type_fields']['plate_number'] = getFieldFromExcel($row, $ColumnNumber, $array_plate_number); $GLOBALS['database_type_fields']['chassis_number'] = getFieldFromExcel($row, $ColumnNumber, $array_chassis_number); $GLOBALS['database_type_fields']['serial_number'] = getFieldFromExcel($row, $ColumnNumber, $array_serial_number); $GLOBALS['database_type_fields']['vin_number'] = getFieldFromExcel($row, $ColumnNumber, $array_vin_number); $GLOBALS['database_type_fields']['conduction_sticker'] = getFieldFromExcel($row, $ColumnNumber, $array_conduction_sticker); $GLOBALS['database_type_fields']['who_drives'] = getFieldFromExcel($row, $ColumnNumber, $array_who_drives); $GLOBALS['database_type_fields']['purchase_car_as'] = getFieldFromExcel($row, $ColumnNumber, $array_purchase_car_as); $GLOBALS['database_type_fields']['delivery_date'] = getFieldFromExcel($row, $ColumnNumber, $array_delivery_date); $GLOBALS['database_type_fields']['vehicle_release_year'] = getFieldFromExcel($row, $ColumnNumber, $array_vehicle_release_year); $GLOBALS['database_type_fields']['description'] = getFieldFromExcel($row, $ColumnNumber, $array_description); $GLOBALS['database_type_fields']['transmission'] = getFieldFromExcel($row, $ColumnNumber, $array_transmission); $GLOBALS['database_type_fields']['service_advisor'] = getFieldFromExcel($row, $ColumnNumber, $array_service_advisor); $GLOBALS['database_type_fields']['sales_person'] = getFieldFromExcel($row, $ColumnNumber, $array_sales_person); $GLOBALS['database_type_fields']['modify_date'] = getFieldFromExcel($row, $ColumnNumber, $array_modify_date); $GLOBALS['database_type_fields']['notes_for_vehicle'] = getFieldFromExcel($row, $ColumnNumber, $array_notes_for_vehicle); $GLOBALS['database_type_fields']['date_created'] = getFieldFromExcel($row, $ColumnNumber, $array_date_created); $GLOBALS['database_type_fields']['time_created'] = getFieldFromExcel($row, $ColumnNumber, $array_time_created); $GLOBALS['database_type_fields']['registration_no'] = getFieldFromExcel($row, $ColumnNumber, $array_registration_no); if ( // isNotEmpty($GLOBALS['database_type_fields']['customer_id']) && (isNotEmpty($GLOBALS['database_type_fields']['conduction_sticker']) \|\| isNotEmpty($GLOBALS['database_type_fields']['plate_number'])) ) { $column_found = true; $GLOBALS['row_dms_headers'] = $row; // $GLOBALS['not_inserted_list_v2'][] = array_map("strtoupper", array_merge(array('issue summary'),$row)); continue 2; } break; case 3: //service $GLOBALS['database_type_fields']['ro_number'] = getFieldFromExcel($row, $ColumnNumber, $array_ro_number); $GLOBALS['database_type_fields']['ro_date'] = getFieldFromExcel($row, $ColumnNumber, $array_ro_date); $GLOBALS['database_type_fields']['ro_total'] = getFieldFromExcel($row, $ColumnNumber, $array_ro_total); $GLOBALS['database_type_fields']['plate_number'] = getFieldFromExcel($row, $ColumnNumber, $array_plate_number); $GLOBALS['database_type_fields']['conduction_sticker'] = getFieldFromExcel($row, $ColumnNumber, $array_conduction_sticker); $GLOBALS['database_type_fields']['serial_number'] = getFieldFromExcel($row, $ColumnNumber, $array_serial_number); $GLOBALS['database_type_fields']['mileage'] = getFieldFromExcel($row, $ColumnNumber, $array_mileage); $GLOBALS['database_type_fields']['recommendation'] = getFieldFromExcel($row, $ColumnNumber, $array_recommendation); $GLOBALS['database_type_fields']['insurance_paid_account'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_paid_account); $GLOBALS['database_type_fields']['start_date_of_service'] = getFieldFromExcel($row, $ColumnNumber, $array_start_date_of_service); $GLOBALS['database_type_fields']['end_date_of_service'] = getFieldFromExcel($row, $ColumnNumber, $array_end_date_of_service); $GLOBALS['database_type_fields']['service_advisor'] = getFieldFromExcel($row, $ColumnNumber, $array_service_advisor); $GLOBALS['database_type_fields']['job_code'] = getFieldFromExcel($row, $ColumnNumber, $array_job_code); $GLOBALS['database_type_fields']['notes'] = getFieldFromExcel($row, $ColumnNumber, $array_notes); $GLOBALS['database_type_fields']['problem_cause'] = getFieldFromExcel($row, $ColumnNumber, $array_problem_cause); $GLOBALS['database_type_fields']['correction'] = getFieldFromExcel($row, $ColumnNumber, $array_correction); $GLOBALS['database_type_fields']['job_summary'] = getFieldFromExcel($row, $ColumnNumber, $array_job_summary); $GLOBALS['database_type_fields']['customer_paid_amount'] = getFieldFromExcel($row, $ColumnNumber, $array_customer_paid_amount); $GLOBALS['database_type_fields']['brand'] = getFieldFromExcel($row, $ColumnNumber, $array_brand); $GLOBALS['database_type_fields']['model'] = getFieldFromExcel($row, $ColumnNumber, $array_model); $GLOBALS['database_type_fields']['model_variant_description'] = getFieldFromExcel($row, $ColumnNumber, $array_model_variant_description); if ( // isNotEmpty($GLOBALS['database_type_fields']['customer_id']) && (isNotEmpty($GLOBALS['database_type_fields']['conduction_sticker']) \|\| isNotEmpty($GLOBALS['database_type_fields']['plate_number'])) && isNotEmpty($GLOBALS['database_type_fields']['ro_number']) ) { $column_found = true; $GLOBALS['row_dms_headers'] = $row; // $GLOBALS['not_inserted_list_v2'][] = array_map("strtoupper", array_merge(array('issue summary'),$row)); continue 2; } break; case 4: //sales $GLOBALS['database_type_fields']['customer_id'] = getFieldFromExcel($row, $ColumnNumber, $array_customer_id); $GLOBALS['database_type_fields']['plate_number'] = getFieldFromExcel($row, $ColumnNumber, $array_plate_number); $GLOBALS['database_type_fields']['conduction_sticker'] = getFieldFromExcel($row, $ColumnNumber, $array_conduction_sticker); $GLOBALS['database_type_fields']['order_number'] = getFieldFromExcel($row, $ColumnNumber, $array_order_number); $GLOBALS['database_type_fields']['serial_number'] = getFieldFromExcel($row, $ColumnNumber, $array_serial_number); $GLOBALS['database_type_fields']['engine_number'] = getFieldFromExcel($row, $ColumnNumber, $array_engine_number); $GLOBALS['database_type_fields']['po_number'] = getFieldFromExcel($row, $ColumnNumber, $array_po_number); $GLOBALS['database_type_fields']['floor_plan_amount'] = getFieldFromExcel($row, $ColumnNumber, $array_floor_plan_amount); $GLOBALS['database_type_fields']['activity_date'] = getFieldFromExcel($row, $ColumnNumber, $array_activity_date); $GLOBALS['database_type_fields']['sales_cost'] = getFieldFromExcel($row, $ColumnNumber, $array_sales_cost); $GLOBALS['database_type_fields']['sale_price'] = getFieldFromExcel($row, $ColumnNumber, $array_sale_price); $GLOBALS['database_type_fields']['insurance_company'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_company); $GLOBALS['database_type_fields']['insurance_type'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_type); $GLOBALS['database_type_fields']['finance_company'] = getFieldFromExcel($row, $ColumnNumber, $array_finance_company); $GLOBALS['database_type_fields']['insurance_expiration_date'] = getFieldFromExcel($row, $ColumnNumber, $array_insurance_expiration_date); $GLOBALS['database_type_fields']['mode_of_contact'] = getFieldFromExcel($row, $ColumnNumber, $array_mode_of_contact); $GLOBALS['database_type_fields']['source'] = getFieldFromExcel($row, $ColumnNumber, $array_source); $GLOBALS['database_type_fields']['warehouse'] = getFieldFromExcel($row, $ColumnNumber, $array_warehouse); $GLOBALS['database_type_fields']['stock_number'] = getFieldFromExcel($row, $ColumnNumber, $array_stock_number); $GLOBALS['database_type_fields']['age_of_unit'] = getFieldFromExcel($row, $ColumnNumber, $array_age_of_unit); $GLOBALS['database_type_fields']['claim_amount'] = getFieldFromExcel($row, $ColumnNumber, $array_claim_amount); $GLOBALS['database_type_fields']['claim_number'] = getFieldFromExcel($row, $ColumnNumber, $array_claim_number); $GLOBALS['database_type_fields']['body_size_code'] = getFieldFromExcel($row, $ColumnNumber, $array_body_size_code); $GLOBALS['database_type_fields']['deal_number'] = getFieldFromExcel($row, $ColumnNumber, $array_deal_number); $GLOBALS['database_type_fields']['deal_date'] = getFieldFromExcel($row, $ColumnNumber, $array_deal_date); $GLOBALS['database_type_fields']['sales_person'] = getFieldFromExcel($row, $ColumnNumber, $array_sales_person); $GLOBALS['database_type_fields']['delivery_date'] = getFieldFromExcel($row, $ColumnNumber, $array_delivery_date); $GLOBALS['database_type_fields']['brand'] = getFieldFromExcel($row, $ColumnNumber, $array_brand); $GLOBALS['database_type_fields']['model'] = getFieldFromExcel($row, $ColumnNumber, $array_model); $GLOBALS['database_type_fields']['model_variant_description'] = getFieldFromExcel($row, $ColumnNumber, $array_model_variant_description); $GLOBALS['database_type_fields']['mode_of_sale'] = getFieldFromExcel($row, $ColumnNumber, $array_mode_of_sale); $GLOBALS['database_type_fields']['type_of_sale'] = getFieldFromExcel($row, $ColumnNumber, $array_type_of_sale); $GLOBALS['database_type_fields']['group_manager'] = getFieldFromExcel($row, $ColumnNumber, $array_group_manager); $GLOBALS['database_type_fields']['sales_discount'] = getFieldFromExcel($row, $ColumnNumber, $array_sales_discount); $GLOBALS['database_type_fields']['end_user'] = getFieldFromExcel($row, $ColumnNumber, $array_end_user); $GLOBALS['database_type_fields']['driver'] = getFieldFromExcel($row, $ColumnNumber, $array_driver); $GLOBALS['database_type_fields']['finance_expiration'] = getFieldFromExcel($row, $ColumnNumber, $array_finance_expiration); $GLOBALS['database_type_fields']['vehicle_offered'] = getFieldFromExcel($row, $ColumnNumber, $array_vehicle_offered); if ( // isNotEmpty($GLOBALS['database_type_fields']['customer_id']) && // isNotEmpty($GLOBALS['database_type_fields']['order_number']) && isNotEmpty($GLOBALS['database_type_fields']['conduction_sticker']) ) { $column_found = true; $GLOBALS['row_dms_headers'] = $row; // $GLOBALS['not_inserted_list_v2'][] = array_map("strtoupper", array_merge(array('issue summary'),$row)); continue 2; } break; } } else { $GLOBALS['total_data_count']++; $row = array_map(array($utility, 'upperCaseNestedArray'), $row); $row = array_map(array($db, 'escape'), $row); if (!implode($row)) { continue; } // if(isArrayEmpty($row)){ // continue; // } // print_r($row); $GLOBALS['loop_count']++; process($GLOBALS['dms_type'], $GLOBALS['dms_type_name'], $GLOBALS['database_type'], $GLOBALS['database_type_name'], $row, $utility, $db); } } //field not found error UI if (!$column_found) { $GLOBALS['not_inserted_list'] = array(array('id' => 'No Data Inserted', 'type' => $GLOBALS['database_type_name'], 'problem' => 'Invalid spreadsheet file. Required field not found. Contact your system administrator.')); $GLOBALS['show_error']++; } switch ($GLOBALS['database_type']) { case 1: //customer // print_r($GLOBALS['customer_data_individual']);exit; // print_r($GLOBALS['customer_contact']);exit; $inserted_contact_count = 0; $inserted_customer_count = 0; if (count($GLOBALS['customer_data_individual']) > 0) { $q = '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`,`type`,`status`) VALUES ' . implode(",", $GLOBALS['customer_data_individual']) . ' '; $db->sql_query($q); // echo $q; } if (count($GLOBALS['customer_master_record']) > 0) { $q = 'INSERT INTO customer_dms (`dms_id`,`customer_record_id`,`customer_dms_id`,`company_dealer_id`,`status`) VALUES ' . implode(",", $GLOBALS['customer_master_record']) . ' '; // $db -> sql_query($q); $inserted_customer_count = $db->sql_query_num_inserted($q); } if (count($GLOBALS['customer_contact']) > 0) { $q = '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 ' . implode(",", $GLOBALS['customer_contact']) . ' '; // $db -> sql_query($q); $inserted_contact_count = $db->sql_query_num_inserted($q); } if (count($GLOBALS['customer_hobbies']) > 0) { $q = 'INSERT INTO `customer_interest_list` (`interest_id`, `customer_id`, `status`) VALUES ' . implode(",", $GLOBALS['customer_hobbies']) . ' '; $db->sql_query($q); } if (count($GLOBALS['customer_affiliation']) > 0) { $q = 'INSERT INTO `customer_affiliations_list` (`affiliations_content_id`, `customer_id`, `status`) VALUES ' . implode(",", $GLOBALS['customer_affiliation']) . ' '; $db->sql_query($q); } if (count($GLOBALS['customer_contact_person']) > 0) { $q = 'INSERT INTO `customer_contact_person` (`full_name`, `customer_id`, `status`) VALUES ' . implode(",", $GLOBALS['customer_contact_person']) . ' '; $db->sql_query($q); } //Clean trash Records $db->sql_query("DELETE cd FROM customer_dms cd LEFT OUTER JOIN customer_contact cc ON cd.customer_record_id = cc.customer_id WHERE cc.id IS NULL"); $db->sql_query("DELETE cd FROM customer_dms cd LEFT OUTER JOIN customer c ON cd.customer_record_id = c.id WHERE c.id IS NULL"); $db->sql_query("DELETE c FROM customer c LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id WHERE cc.id IS NULL"); $db->sql_query("DELETE c FROM customer c LEFT OUTER JOIN customer_dms cd ON c.id = cd.customer_record_id WHERE c.id IS NULL"); $db->sql_query("DELETE cc FROM customer_contact cc LEFT OUTER JOIN customer c ON cb.customer_id = c.id WHERE c.id IS NULL"); $db->sql_query("DELETE cc FROM customer_contact cc LEFT OUTER JOIN customer_dms cd ON cc.customer_id = cd.customer_record_id WHERE cd.id IS NULL"); $db->sql_query("DELETE cb FROM customer_business cb LEFT OUTER JOIN customer_contact cc ON cb.customer_id = cc.customer_id WHERE cc.id IS NULL"); $db->sql_query("DELETE cb FROM customer_interest_list cb LEFT OUTER JOIN customer_contact cc ON cb.customer_id = cc.customer_id WHERE cc.id IS NULL"); $db->sql_query("DELETE cb FROM customer_affiliations_list cb LEFT OUTER JOIN customer_contact cc ON cb.customer_id = cc.customer_id WHERE cc.id IS NULL"); $db->sql_query("DELETE cb FROM customer_contact_person cb LEFT OUTER JOIN customer_contact cc ON cb.customer_id = cc.customer_id WHERE cc.id IS NULL"); if (intval($GLOBALS['customer_sharing_count']) > 0) { $count_record = max(0, $GLOBALS['customer_sharing_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Merged Customer </td> <td class='text-center'><h5><span class='badge bg-success' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Merged Customer", $count_record); } $garbage_data = max(0, $inserted_customer_count - $inserted_contact_count); if (intval($GLOBALS['customer_duplicate_count']) > 0 \|\| $garbage_data > 0) { $count_record = (max(0, $GLOBALS['customer_duplicate_count']) + $garbage_data); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>" . $GLOBALS['customer_already_exists'] . "</td> <td class='text-center'><h5><span class='badge bg-warning' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['customer_already_exists'], $count_record); } if (intval($GLOBALS['no_dealer_count']) > 0) { $count_record = max(0, $GLOBALS['no_dealer_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Dealer Not Found</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Dealer Not Found", $count_record); } if (intval($GLOBALS['customer_empty_id_count']) > 0) { $count_record = max(0, $GLOBALS['customer_empty_id_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Customer No.</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Customer No.", $count_record); } if (intval($GLOBALS['customer_name_empty_count']) > 0) { $count_record = max(0, $GLOBALS['customer_name_empty_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Empty Customer Name</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Empty Customer Name", $count_record); } if (intval($GLOBALS['no_email_address_count']) > 0) { $count_record = max(0, $GLOBALS['no_email_address_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Email Address</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Email Address", $count_record); } if (intval($GLOBALS['invalid_email_address_count']) > 0) { $count_record = max(0, $GLOBALS['invalid_email_address_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Invalid Email Address</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Invalid Email Address", $count_record); } if (intval($GLOBALS['email_already_exists_count']) > 0) { $count_record = max(0, $GLOBALS['email_already_exists_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Email Already Exist</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Email Already Exist", $count_record); } if (intval($GLOBALS['mobile_already_exists_count']) > 0) { $count_record = max(0, $GLOBALS['mobile_already_exists_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Mobile Already Exist</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Mobile Already Exist", $count_record); } if (intval($GLOBALS['no_mobile_number_count']) > 0) { $count_record = max(0, $GLOBALS['no_mobile_number_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Mobile Number</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Mobile Number", $count_record); } if (intval($GLOBALS['invalid_mobile_count']) > 0) { $count_record = max(0, $GLOBALS['invalid_mobile_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>" . $GLOBALS['invalid_mobile'] . "</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['invalid_mobile'], $count_record); } $count_record_inserted = (max(0, $GLOBALS['customer_inserted_count']) + $inserted_contact_count); $count_record_not_inserted = (intval($GLOBALS['customer_not_inserted_count']) + max(0, $inserted_customer_count - $inserted_contact_count)); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Not Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record_not_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-success' >" . $count_record_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Total Not Inserted " . $GLOBALS['database_type_name'], (string) $count_record_not_inserted); $GLOBALS['data_count_list_v2'][] = array("Total Inserted " . $GLOBALS['database_type_name'], (string) $count_record_inserted); break; case 2: //vehicle //remove auto increment vehivle id //make big int vehicle id //make big int vehicle_id in vehicle_notes //make unique vehicle_id in vehicle_notes //set null cs and plate //set unique cs and plate // print_r($GLOBALS['vehicle_data']);exit; $inserted_data_count = 0; if (count($GLOBALS['vehicle_notes']) > 0) { $q = 'INSERT IGNORE INTO `vehicle_notes`(`vehicle_id`, `data`, `status`) VALUES ' . implode(",", $GLOBALS['vehicle_notes']) . ' '; $db->sql_query($q); } if (count($GLOBALS['vehicle_data']) > 0) { $q = 'INSERT IGNORE INTO vehicle (id,customer_id, customer_record_id,color_id,brand_id,engine_type,gas_type,insurance_company_id,insurance_type_id,insurance_expiration_date,finance_company_id,model_id,model_no,model_variant_description,plate_number,chassis_number,serial_number, vin_number, conduction_sticker, who_drives, purchase_car_as_id, delivery_date, date_created, time_created, vehicle_release_year, description, transmission, service_advisor, company_dealer_id, upload_by, sales_person, registration_number, status) VALUES ' . implode(",", $GLOBALS['vehicle_data']) . ' '; // echo $q;exit; $inserted_data_count = $db->sql_query_num_inserted($q); } $db->sql_query("DELETE vn FROM `vehicle_notes` vn LEFT OUTER JOIN vehicle v ON vn.`vehicle_id`= v.id WHERE `v`.`id` is NULL"); if (max(0, (count($GLOBALS['vehicle_data']) - intval($inserted_data_count))) > 0) { $count_record = max(0, (count($GLOBALS['vehicle_data']) - intval($inserted_data_count))); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Duplicates (Already in Database)</td> <td class='text-center'><h5><span class='badge bg-warning' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Duplicates (Already in Database)", $count_record); } if (intval($GLOBALS['no_dealer_count']) > 0) { $count_record = max(0, $GLOBALS['no_dealer_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Dealer Not Found</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Dealer Not Found", $count_record); } if (intval($GLOBALS['customer_empty_id_count']) > 0) { $count_record = max(0, $GLOBALS['customer_empty_id_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Customer No.</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Customer No.", $count_record); } if (intval($GLOBALS['no_plate_and_conduction_sticker_count']) > 0) { $count_record = max(0, $GLOBALS['no_plate_and_conduction_sticker_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Plate & CS</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Plate & CS", $count_record); } $count_record_not_inserted = (intval($GLOBALS['vehicle_not_inserted_count']) + max(0, (count($GLOBALS['vehicle_data']) - intval($inserted_data_count)))); $count_record_inserted = max(0, $inserted_data_count); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Not Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record_not_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-success' >" . $count_record_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Total Not Inserted " . $GLOBALS['database_type_name'], (string) $count_record_not_inserted); $GLOBALS['data_count_list_v2'][] = array("Total Inserted " . $GLOBALS['database_type_name'], (string) $count_record_inserted); break; case 3: //service //insert collected Service RO Number // echo count($GLOBALS['vehicle_data']);exit; if (count($GLOBALS['vehicle_data']) > 0) { $q = 'INSERT IGNORE INTO vehicle (id,brand_id,model_id,model_variant_description,plate_number,conduction_sticker, upload_by, company_dealer_id,modified_by,status) VALUES ' . implode(",", $GLOBALS['vehicle_data']) . ' ON DUPLICATE KEY UPDATE brand_id = VALUES (brand_id), model_id = VALUES (model_id), model_variant_description = VALUES (model_variant_description), plate_number = VALUES (plate_number), conduction_sticker = VALUES (conduction_sticker), modified_by = VALUES (modified_by), date_modified = NOW() /company_dealer_id = VALUES (company_dealer_id), upload_by = VALUES (upload_by)/ '; // echo $q; // exit; $db->sql_query_num_inserted($q); } $inserted_data_count = 0; if (count($GLOBALS['service_data']) > 0) { $q = 'INSERT IGNORE INTO `service`(id,vehicle_id,notes,`customer_id`,`ro_number`,`ro_date`,`plate_number`,`serial_number`,`ro_total`,`mileage`,`recommendation`,`insurance_paid_account`,`conduction_sticker`,`start_date_of_service`,`end_date_of_service`,`service_advisor`,`company_dealer_id`,`upload_by`,`status`,`brand_id`,`model_id`) VALUES ' . implode(",", $GLOBALS['service_data']) . ' ON DUPLICATE KEY UPDATE vehicle_id = VALUES (vehicle_id), notes = VALUES (notes), ro_date = VALUES (ro_date), ro_total = VALUES (ro_total), mileage = VALUES (mileage), recommendation = VALUES (recommendation), insurance_paid_account = VALUES (insurance_paid_account), start_date_of_service = VALUES (start_date_of_service), end_date_of_service = VALUES (end_date_of_service), service_advisor = VALUES (service_advisor), company_dealer_id = VALUES (company_dealer_id), upload_by = VALUES (upload_by) '; // echo $q;exit; $inserted_data_count = $db->sql_query_num_inserted($q); } // echo $q;exit; if (count($GLOBALS['service_history']) > 0) { // $temp_table_name = 'tmp_'.uniqid(); // $tmpTable = "CREATE TEMPORARY TABLE $temp_table_name ( // `ro_number` varchar(50) NOT NULL, // `job_code` varchar(100) NOT NULL, // `problem_cause` varchar(200) NOT NULL, // `correction` longtext NOT NULL, // `job_summary` longtext NOT NULL, // `amount` varchar(20) NOT NULL // )"; // // echo $tmpTable;exit; // $db -> sql_query($tmpTable); $q = 'INSERT INTO service_history (`ro_number`,`job_code`,`problem_cause`,`correction`,`job_summary`,`amount`) VALUES ' . implode(",", $GLOBALS['service_history']) . ' '; $db->sql_query($q); // $q = "INSERT INTO service_history // (`ro_number`,`job_code`,`problem_cause`,`correction`,`job_summary`,`amount`) // SELECT // FROM $temp_table_name t1 // WHERE NOT EXISTS(SELECT ro_number // FROM service_history t2 // WHERE t2.ro_number = t1.ro_number)"; // $db -> sql_query($q); } // if(max(0,(count($GLOBALS['service_data']) - intval($inserted_data_count))) > 0){ // $count_record = max(0,(count($GLOBALS['service_data']) - intval($inserted_data_count))); // $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Duplicates (Already in Database)</td> <td class='text-center'><h5><span class='badge bg-warning' >".$count_record."</span></h5></td></tr>"; // $GLOBALS['data_count_list_v2'][] = array("Duplicates (Already in Database)",$count_record); // } if (intval($GLOBALS['no_dealer_count']) > 0) { $count_record = max(0, $GLOBALS['no_dealer_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Dealer Not Found</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Dealer Not Found", $count_record); } if (intval($GLOBALS['customer_empty_id_count']) > 0) { $count_record = max(0, $GLOBALS['customer_empty_id_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Customer No.</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Customer No.", $count_record); } if (intval($GLOBALS['no_plate_and_conduction_sticker_count']) > 0) { $count_record = max(0, $GLOBALS['no_plate_and_conduction_sticker_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Plate & CS</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Plate & CS", $count_record); } if (intval($GLOBALS['service_ro_number_not_found_count']) > 0) { $count_record = max(0, $GLOBALS['service_ro_number_not_found_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No RO Number</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No RO Number", $count_record); } // $count_record_not_inserted = (intval($GLOBALS['service_not_inserted_count']) + max(0,(count($GLOBALS['service_data']) - intval($inserted_data_count)))); $count_record_not_inserted = intval($GLOBALS['service_not_inserted_count']); // $count_record_inserted = max(0, $inserted_data_count); $count_record_inserted = ($GLOBALS['loop_count'] - $count_record_not_inserted); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Not Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record_not_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-success' >" . $count_record_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Total Not Inserted " . $GLOBALS['database_type_name'], (string) $count_record_not_inserted); $GLOBALS['data_count_list_v2'][] = array("Total Inserted " . $GLOBALS['database_type_name'], (string) $count_record_inserted); // if(count($GLOBALS['service_ro_number']) > 0){ // $insert_service = array_values(array_unique($GLOBALS['service_ro_number'], SORT_REGULAR)); // // print_r($insert_service);return; // foreach($insert_service as $data){ // $service_ro_table = "service"; // $service_ro_fields = array("customer_id", "ro_number", "ro_date", "plate_number", "serial_number", "ro_total", "mileage", "recommendation", "insurance_paid_account", "conduction_sticker", "start_date_of_service", "end_date_of_service", "service_advisor", "company_dealer_id", "dms_id", "upload_by" ,"status","brand_id","model_id"); // $service_ro_data = array( $data[0], $data[1], $data[2] , $data[3], $data[4], $data[5], $data[6], $data[7], $data[8], $data[9], $data[10], $data[11], $data[12], $data[13], $data[14], $data[15], "1", $data[16], $data[17] ); // insertData($service_ro_fields,$service_ro_table,$service_ro_data,$db); // } // } break; case 4: //sales //remove auto inc //make big int sales id //make big int sales id //make unique customerid //make unique order number ' if (count($GLOBALS['vehicle_data']) > 0) { $q = 'INSERT IGNORE INTO vehicle (id,brand_id,model_id,model_variant_description,plate_number, conduction_sticker, company_dealer_id, upload_by , status) VALUES ' . implode(",", $GLOBALS['vehicle_data']) . ' '; $db->sql_query_num_inserted($q); } $inserted_data_count = 0; if (count($GLOBALS['sales_data']) > 0) { $q = 'INSERT IGNORE INTO `sales`(id,`vehicle_id`,`plate_number`,`customer_id`,`conduction_sticker`, `order_number`, `serial_number`, `engine_number`, `po_number`, `floor_plan_amount`, `activity_date`, `sales_cost`, `sale_price`, `insurance_company_id`, `insurance_type_id`, `insurance_expiration_date`, `finance_company_id`, `source_id`, `mode_of_contact_id`, `warehouse`, `stock_number`, `age_of_unit`, `body_size_code`, `claim_number`, `claim_amount`, `deal_number`, `deal_date`, `sales_person`, `delivery_date`, `company_dealer_id`, `upload_by`, `status`,brand_id,model_id,`mode_of_sale_id`,`type_of_payment_id`,`group_manager_id`,`sale_discount`,`end_user`,`driver`,`finance_expiration_date`) VALUES ' . implode(",", $GLOBALS['sales_data']) . ' '; // echo $q;exit; $inserted_data_count = $db->sql_query_num_inserted($q); } if (count($GLOBALS['vehicle_offered']) > 0) { $q = 'INSERT INTO `sales_brands_offered`( `sales_id`, `brand_id`, `model_id`, `description`, `status`) VALUES ' . implode(",", $GLOBALS['vehicle_offered']) . ' '; $db->sql_query($q); } $db->sql_query("DELETE sbo FROM `sales_brands_offered` sbo LEFT OUTER JOIN sales s ON sbo.`sales_id`= s.id WHERE `s`.`id` is NULL"); if (max(0, (count($GLOBALS['sales_data']) - intval($inserted_data_count))) > 0) { $count_record = max(0, (count($GLOBALS['sales_data']) - intval($inserted_data_count))); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Duplicates (Already in Database)</td> <td class='text-center'><h5><span class='badge bg-warning' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Duplicates (Already in Database)", $count_record); } if (intval($GLOBALS['no_dealer_count']) > 0) { $count_record = max(0, $GLOBALS['no_dealer_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Dealer Not Found</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Dealer Not Found", $count_record); } if (intval($GLOBALS['customer_empty_id_count']) > 0) { $count_record = max(0, $GLOBALS['customer_empty_id_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Customer No.</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Customer No.", $count_record); } if (intval($GLOBALS['no_plate_and_conduction_sticker_count']) > 0) { $count_record = max(0, $GLOBALS['no_plate_and_conduction_sticker_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Plate & CS</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Plate & CS", $count_record); } if (intval($GLOBALS['no_order_number_count']) > 0) { $count_record = max(0, $GLOBALS['no_order_number_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>No Order Number</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("No Order Number", $count_record); } $count_record_not_inserted = (intval($GLOBALS['sale_not_inserted_count']) + max(0, (count($GLOBALS['sales_data']) - intval($inserted_data_count)))); $count_record_inserted = max(0, $inserted_data_count); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Not Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-danger' >" . $count_record_not_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Inserted " . $GLOBALS['database_type_name'] . "</td> <td class='text-center'><h5><span class='badge bg-success' >" . $count_record_inserted . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Total Not Inserted " . $GLOBALS['database_type_name'], (string) $count_record_not_inserted); $GLOBALS['data_count_list_v2'][] = array("Total Inserted " . $GLOBALS['database_type_name'], (string) $count_record_inserted); break; } $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Total Data</td> <td class='text-center'><h5><span class='badge bg-primary' >" . $GLOBALS['loop_count'] . "</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Total Data", (string) $GLOBALS['loop_count']); $filename = pathinfo($_FILES["import_excel"]["name"], PATHINFO_FILENAME) . '.' . 'xlsx'; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "<tr><td>Data with issues</td> <td class='text-center'><h5><a href='app/upload/logs/" . $filename . "'><span class='badge bg-primary' style='cursor:pointer;'>Download</span></a></h5></td></tr>"; returnData($filename, $highestColumn, $issue_summary_index); } else { $return_arr["status"] = 0; $return_arr["message"] = " Only .xls .csv or .xlsx file allowed."; } } else { $return_arr["status"] = 0; $return_arr["message"] = " Please select Spreadsheet file."; } //FOR ALL FUNCTIONS function checkHasAlpha($s) { if (preg_match("/[a-z]/i", $s)) { return $s; } return ""; } function alphaNumericOnly($s) { return preg_replace("/[^a-zA-Z0-9]+/", "", $s); } function getFieldFromExcel($row, $ColumnNumber, $array_data) { $array_data = array_map('strtolower', $array_data); for ($i = 0; $i < $ColumnNumber; $i++) { if (in_array(trim($row[$i]), $array_data, true)) { return $row[$i]; } } return ""; } function getIssueSummaryField($str, $ColumnNumber, $array_data) { $array_data = array_map('strtolower', $array_data); for ($i = 0; $i < $ColumnNumber; $i++) { if (in_array($str, $array_data, true)) { return $i; } } return "-5"; } function getArrayFields($id, $db) { $return_array = array(); $data_list = $db->sql_query("SELECT `name` FROM `source_dms_fields_name` WHERE `dms_id` = '" . $GLOBALS['dms_type'] . "' AND `dms_field_id` = '$id' "); foreach ($data_list as $row) { $return_array = array_merge($return_array, array($row['name'])); } return $return_array; } function checkIfExistInArray($array, $key, $val) { for ($i = 0; $i < count($GLOBALS['service_ro_number']); $i++) { // echo $GLOBALS['service_ro_number'][$i][$key].'-'.$val; if (isset($GLOBALS['service_ro_number'][$i][$key]) && $GLOBALS['service_ro_number'][$i][$key] == $val) { return true; } } return false; } function getGenderID($gender) { $gender_id = "0"; if (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 getMaritalID($marital_status) { $marital_status = strtolower($marital_status); $marital_status_id = "0"; switch ($marital_status) { case "married"; $marital_status_id = "1"; break; case "widowed"; $marital_status_id = "2"; break; case "separated"; $marital_status_id = "3"; break; case "divorced"; $marital_status_id = "4"; break; case "single"; $marital_status_id = "5"; break; } return $marital_status_id; } // function countCustomerEmail($email_address,$db){ // $email_address_count_query = "SELECT count(id) FROM customer_contact WHERE email_1 = '$email_address' and status = 1"; // $email_address_count = $db -> select($email_address_count_query); // if($email_address_count > 0 ){ // return $email_address_count; // } // return 0; // } function countCustomerMobileIndividual($mobile_phone, $last_name, $db) { $mobile_phone_count_query = "SELECT count(ci.id) as count ,ci.last_name FROM `customer` ci INNER JOIN customer_contact cc ON ci.`id` = cc.customer_id WHERE cc.mobile_phone_1 = '$mobile_phone' and ci.status = 1 LIMIT 1"; $mobile_phone_count = $db->return_result($mobile_phone_count_query); $info = array(); foreach ($mobile_phone_count as $data) { $info[0] = $data['count']; $info[1] = $data['last_name']; } if (isNotEmpty($last_name)) { if ($last_name == $info[1]) { return 0; } else { return $info[0]; } } return $info[0]; } function countCustomerMobileCorporation($mobile_phone, $db) { $mobile_phone_count_query = "SELECT count(ccp.id) FROM `customer` ccp INNER JOIN customer_contact cc ON ccp.id = cc.customer_id WHERE cc.mobile_phone_1 = '$mobile_phone'"; $mobile_phone_count = $db->select($mobile_phone_count_query); return $mobile_phone_count; } function checkMasterData($company_name, $first_name, $last_name, $mobile_phone_1, $db) { $customer_info_data_count = 0; if ((isNotEmpty($first_name) && isNotEmpty($last_name))) { $append_query = " AND (ci.first_name = '$first_name' and ci.last_name = '$last_name' and cc.mobile_phone_1 = '$mobile_phone_1') "; } else { // $append_query = " AND (ci.corporation_name = '$company_name' and cc.mobile_phone_1 = '$mobile_phone_1') "; $append_query = " AND (ci.corporation_name = '$company_name') "; } $customer_info_individual_data_query = "SELECT count(cd.id) FROM customer ci INNER JOIN customer_dms cd ON ci.id=cd.customer_record_id INNER JOIN customer_contact cc ON cd.customer_record_id=cc.customer_id WHERE 1 %s AND cd.status = 1 AND ci.status = 1 "; // echo sprintf($customer_info_individual_data_query,$append_query);exit; return $db->select(sprintf($customer_info_individual_data_query, $append_query)); } function countSalesID($customer_id, $order_number, $db) { $data_count_query = "SELECT count(id) FROM sales WHERE customer_id = '$customer_id' and order_number = '$order_number' and status = 1"; $data_count = $db->select($data_count_query); return $data_count; } function removeNumSpecial($data) { $filtered_input = trim(preg_replace("/\s(?:[\d_]\|[^\w\s])+/", "", $data)); return $filtered_input; } function isNotEmpty($data) { return preg_match('/\S/', $data); } function checkIfAlpha($data) { $pattern = "/^[a-zA-Z ]+$/"; return preg_match($pattern, $data); // return (ctype_alpha($data)); false including space } function insertData($field, $table, $data, $db) { return $db->sql_query_id("INSERT INTO $table (" . implode(",", $field) . ") VALUES (" . implode(",", array_map('quote', $data)) . ")"); } function recordCount($field, $table, $where_field, $where_data, $status, $db) { $where_status = ($status) ? ' AND status = 1 ' : ''; // echo "SELECT $field FROM $table WHERE $where_field = '$where_data' $where_status ";exit; return $db->select("SELECT $field FROM $table WHERE $where_field = '$where_data' $where_status "); } function recordCountRows($field, $table, $where_field, $where_data, $status, $db) { $where_status = ($status) ? ' AND status = 1 ' : ''; return $db->sql_query("SELECT $field FROM $table WHERE $where_field = '$where_data' $where_status ")->num_rows; } function getLeadSourceID($data, $db) { $source_of_contact = 0; if (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 (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 getSalesConsultant($name, $db) { $id = 0; if (isNotEmpty(($name))) { $query = $db->select("SELECT id FROM `sales_consultant` WHERE CONCAT(first_name,' ',last_name) LIKE '%$name%' LIMIT 1"); if (isNotEmpty($query)) { $id = $query; } else { $id = $db->sql_query_id("INSERT INTO sales_consultant (first_name,status) VALUES ('$name','1')"); } } return $id; } function getModeOfSale($data, $db) { $id = 0; if (isNotEmpty(trim($data))) { $data_id = $db->select("SELECT id FROM source_mode_of_sale WHERE name = '$data' AND status = 1 LIMIT 1"); if (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, $db) { $id = 0; if (isNotEmpty(trim($data))) { $data_id = $db->select("SELECT id FROM source_type_of_payment WHERE name = '$data' AND status = 1 LIMIT 1"); if (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 getNatureOfWorkID($data, $db) { $id = 0; if (isNotEmpty(trim($data))) { $data = $db->escape($data); $data_id = $db->select("SELECT id FROM source_nature_of_business WHERE name = '$data' AND status = 1 LIMIT 1"); if (isNotEmpty($data_id)) { $id = $data_id; } else { $id = $db->sql_query_id("INSERT INTO source_nature_of_business (name,status) VALUES ('$data','1')"); } } return $id; } function getProfessionID($data, $db) { $id = 0; if (isNotEmpty(trim($data))) { $data = $db->escape($data); $data_id = $db->select("SELECT id FROM source_profession WHERE name = '$data' AND status = 1 LIMIT 1"); if (isNotEmpty($data_id)) { $id = $data_id; } else { $id = $db->sql_query_id("INSERT INTO source_profession (name,status) VALUES ('$data','1')"); } } return $id; } function getReligionID($data, $db) { $id = 0; if (isNotEmpty(trim($data))) { $data = $db->escape($data); $data_id = $db->select("SELECT id FROM source_religion WHERE name = '$data' AND status = 1 LIMIT 1"); if (isNotEmpty($data_id)) { $id = $data_id; } else { $id = 0; //$db -> sql_query_id("INSERT INTO source_of_inquiry (name) VALUES ('$data')"); } } return $id; } function getAffiliationID($affiliation_category_id, $data, $db) { if ($data == '') { return ''; } $id = 0; if (isNotEmpty(trim($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 (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 getInterestID($interest_category_id, $data, $db) { if ($data == '') { return ''; } $id = 0; if (isNotEmpty(trim($data))) { $data = $db->escape($data); $id_data = $db->select("SELECT id FROM `customer_interest_content` WHERE `name` = '$data' AND interest_id = '$interest_category_id' AND status = 1 LIMIT 1"); if (isNotEmpty($id_data)) { $id = $id_data; } else { $id = $db->sql_query_id("INSERT INTO customer_interest_content (interest_id,name) VALUES ('$interest_category_id','$data')"); } } return $id; } function getDealerID($db) { $company_dealer_id = 0; $company_id = $GLOBALS['company']; $dealer_id = $GLOBALS['dealer']; $type = 1; //1:cutomer //0:employee $get_company_dealer_id_query = $db->sql_query("SELECT id FROM `source_company_dealer` WHERE `company_id` = '$company_id' and `dealer_id` = '$dealer_id' and type= '1' and status = '1' LIMIT 1"); $get_company_dealer_id_query_count = $get_company_dealer_id_query->num_rows; if ($get_company_dealer_id_query_count > 0) { while ($row1 = $get_company_dealer_id_query->fetch_assoc()) { $company_dealer_id = $row1['id']; } } else { $company_dealer_id = $db->sql_query_id("INSERT INTO source_company_dealer (`company_id`, `dealer_id`, `type`, `status`) VALUES ('$company_id','$dealer_id','1','1')"); } return $company_dealer_id; } function getDealerNameID($data_dealer, $db) { $company_dealer_id = 0; $company_id = $GLOBALS['company']; $dealer_id = $db->select("SELECT id FROM `source_dealer` WHERE status = 1 AND (`code` = '$data_dealer' OR `name` = '$data_dealer' OR CONCAT(name,' ',code) = '$data_dealer') LIMIT 1"); if (isNotEmpty($dealer_id)) { $type = 1; //1:cutomer //0:employee $get_company_dealer_id_query = $db->sql_query("SELECT id FROM `source_company_dealer` WHERE `company_id` = '$company_id' and `dealer_id` = '$dealer_id' and type= '1' and status = '1' LIMIT 1"); $get_company_dealer_id_query_count = $get_company_dealer_id_query->num_rows; if ($get_company_dealer_id_query_count > 0) { while ($row1 = $get_company_dealer_id_query->fetch_assoc()) { $company_dealer_id = $row1['id']; } } else { $company_dealer_id = 0; // $db -> sql_query_id("INSERT INTO source_company_dealer (`company_id`, `dealer_id`, `type`, `status`) VALUES ('$company_id','$dealer_id','1','1')"); } } return $company_dealer_id; } function getCompanyNameCode($data_company, $db) { $company_id = $db->select("SELECT id FROM `source_company` WHERE status = 1 AND (`code` = '$data_company' OR `name` = '$data_company' OR CONCAT(name,' ',code) = '$data_company') LIMIT 1"); if (isNotEmpty($company_id)) { return $company_id; } return 0; } function getVehicleColorID($color_file, $db) { $color_id = 0; if (isNotEmpty($color_file)) { $color_data = $db->select("SELECT id FROM vehicle_color WHERE name = '$color_file' and status = 1 LIMIT 1"); if (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 getVehicleOwnerId($customer_id, $db) { $id = 0; if (isNotEmpty($customer_id)) { $data_id = $db->select("SELECT `customer_record_id` FROM `customer_dms` WHERE `customer_dms_id` = '$customer_id' LIMIT 1"); if (isNotEmpty($data_id)) { $id = $data_id; } else { $id = 0; //$db -> sql_query_id("INSERT INTO vehicle_color (name,status) VALUES ('$color_file','1')"); } } return $id; } function getVehicleBrandID($brand_file, $db) { $brand_id = 0; if (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 (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, $db) { $model_id = 0; if (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 (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, $db) { $model_variant_id = 0; if (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 (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 getInsuranceCompanyID($insurance_company_file, $db) { $insurance_company_id = 0; if (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 (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 getFinanceCompanyID($finance_company_file, $db) { $finance_company_id = 0; if (isNotEmpty($finance_company_file)) { $finance_company_data = $db->select("SELECT id FROM source_finance_company WHERE name = '$finance_company_file' and status = 1 LIMIT 1"); if (isNotEmpty($finance_company_data)) { $finance_company_id = $finance_company_data; } else { $finance_company_id = $db->sql_query_id("INSERT INTO source_finance_company (name,status) VALUES ('$finance_company_file','1')"); } } return $finance_company_id; } function getPurchaseCarAsID($purchase_car_as_file, $db) { $purchase_car_as_id = 0; if (isNotEmpty($purchase_car_as_file)) { $purchase_car_as_data = $db->select("SELECT id FROM vehicle_purchase_car_as WHERE name = '$purchase_car_as_file' and status = 1 LIMIT 1"); if (isNotEmpty($purchase_car_as_data)) { $purchase_car_as_id = $purchase_car_as_data; } else { $purchase_car_as_id = $db->sql_query_id("INSERT INTO vehicle_purchase_car_as (name,status) VALUES ('$purchase_car_as_file','1')"); } } return $purchase_car_as_id; } function getInsuranceTypeID($insurance_type_file, $db) { $insurance_company_id = 0; if (isNotEmpty($insurance_type_file)) { $insurance_type_data = $db->select("SELECT id FROM source_insurance_type WHERE name = '$insurance_type_file' and status = 1 LIMIT 1"); if (isNotEmpty($insurance_type_data)) { $insurance_company_id = $insurance_type_data; } else { $insurance_company_id = 0; //$db -> sql_query_id("INSERT INTO source_insurance_type (name,status) VALUES ('$insurance_type_file','1')"); } } return $insurance_company_id; } function quote($str) { return sprintf("'%s'", $str); } function validateEmail($data) { return (filter_var($data, FILTER_VALIDATE_EMAIL)); } function removeNonNumeric($data) { return preg_replace("/[^0-9]/", "", $data); } function removeNonNumericCurrency($data) { return preg_replace("/[^0-9.]/", "", $data); } function removeNumeric($data) { $return_data = preg_replace('/\d/', '', $data); if (strlen($return_data) < 3) { return ""; } return $return_data; } function validateNumber($data) { if (strlen($data) < 3) { 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 ""; } } return $final_data; } function getCityAndState($address, $city, $state, $db) { $complete_address = array(); $complete_address[0] = $address; if (!isNotEmpty($city)) { $complete_address[1] = "0"; $complete_address[2] = "0"; return $complete_address; } $data = trim(str_replace("city", "", strtolower($city))); $state_city = $db->sql_query("SELECT id,provCode FROM all_city WHERE citymunDesc LIKE '%$data%' ORDER BY citymunDesc ASC LIMIT 1"); if ($state_city->num_rows > 0) { foreach ($state_city as $data_row) { $complete_address[1] = $data_row['id']; $complete_address[2] = $data_row['provCode']; } } else { $complete_address[0] = $address . ' ' . $city . ' ' . $state; $complete_address[1] = "0"; $complete_address[2] = "0"; } return $complete_address; } function getNationality($nationality, $db) { if (!isNotEmpty($nationality)) { return "608"; } $nationality = strtolower($nationality); $nationality_id = $db->sql_query("SELECT num_code FROM `all_countries` WHERE `nationality` LIKE '$nationality%' ORDER BY `nationality` ASC LIMIT 1"); if ($nationality_id->num_rows > 0) { foreach ($nationality_id as $data_row) { return $data_row['num_code']; } } return "608"; } function returnData($file_name, $highestColumn, $issue_summary_index) { $return_arr["status"] = 1; $return_arr["message"] = " Data Imported Successfully."; $return_arr["selected_dms_name"] = $GLOBALS['dms_type_name']; $return_arr["selected_dms_type"] = $GLOBALS['database_type_name']; $return_arr["invalid_data"] = $GLOBALS['not_inserted_count']; $return_arr["invalid_data_list"] = $GLOBALS['not_inserted_list']; $return_arr["show_error"] = $GLOBALS['show_error']; $return_arr["data_count_list"] = $GLOBALS['data_count_list']; $return_json = json_encode($return_arr); $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); for ($i = 'A'; $i != $highestColumn; $i++) { $sheet->getColumnDimension($i)->setAutoSize(true); } foreach ($GLOBALS['not_inserted_list_v2'] as $key => $subArr) { unset($subArr[intval($issue_summary_index) + 1]); $GLOBALS['not_inserted_list_v2'][$key] = $subArr; } unset($GLOBALS['row_dms_headers'][intval($issue_summary_index)]); usort($GLOBALS['not_inserted_list_v2'], function ($a, $b) { return $a[0] <=> $b[0]; }); $GLOBALS['not_inserted_list_v2'] = array_merge(array(array_map("strtoupper", array_merge(array('ISSUE SUMMARY'), $GLOBALS['row_dms_headers']))), $GLOBALS['not_inserted_list_v2']); if (count($GLOBALS['data_count_list_v2']) > 0) { $GLOBALS['not_inserted_list_v2'] = array_merge($GLOBALS['data_count_list_v2'], $GLOBALS['not_inserted_list_v2']); } $sheet->fromArray($GLOBALS['not_inserted_list_v2'], NULL, 'A1'); $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); $writer->save('logs/' . $file_name); echo $return_json; } function delete_col(&$array, $offset) { return array_walk($array, function (&$v) use ($offset) { array_splice($v, $offset, 1); }); } function checkExist($row_data, $field) { if (!isNotEmpty($field)) { return ""; } $key = array_search($field, $GLOBALS['row_dms_headers']); return isset($row_data[$key]) ? $row_data[$key] : ''; } function insertCustomer($customer_id, $company_name, $data_salutation, $first_name, $middle_name, $last_name, $suffix_id, $gender_id, $nationality_id, $date_of_birth, $marital_status_id, $source_of_contact, $mode_of_contact_id, $spouse_name, $no_of_children, $occupation, /$sales_person,*/ $date_created, $time_created, $selected_dms_id, $data_religion_id, $data_nature_of_work_id, $data_profession_id, $db) { $customer_master_id = $db->select("SELECT UUID_SHORT()"); if ((isNotEmpty($first_name) && isNotEmpty($last_name))) { $GLOBALS['customer_data_individual'][] = "('$customer_master_id','','$data_salutation','$first_name','$middle_name','$last_name','$suffix_id','$gender_id','$nationality_id','$date_of_birth','$marital_status_id','$source_of_contact','$mode_of_contact_id','$spouse_name','$no_of_children','$occupation','','$date_created','$time_created','" . $_SESSION['user']['id'] . "','" . $data_religion_id . "','" . $data_nature_of_work_id . "','" . $data_profession_id . "','1','1')"; } else { $GLOBALS['customer_data_individual'][] = "('$customer_master_id','$company_name','0','','','','0','0','$nationality_id','$date_of_birth','$marital_status_id','$source_of_contact','$mode_of_contact_id','$spouse_name','$no_of_children','$occupation','','$date_created','$time_created','" . $_SESSION['user']['id'] . "','" . $data_religion_id . "','" . $data_nature_of_work_id . "','" . $data_profession_id . "','2','1')"; } $GLOBALS['customer_master_record'][] = "('$selected_dms_id','$customer_master_id','$customer_id','" . $GLOBALS['company_dealer_id'] . "','1' )"; return $customer_master_id; } function insertMasterRecord($selected_dms_id, $current_id, $customer_id, $date_created, $time_created, $db) { $customer_dms_table = "customer_dms"; $customer_dms_fields = array("customer_record_id", "customer_dms_id", "company_dealer_id", "status"); $customer_dms_data = array($current_id, $customer_id, $GLOBALS['company_dealer_id'], "1"); insertData($customer_dms_fields, $customer_dms_table, $customer_dms_data, $db); } function insertCustomerAllData( $data_customer_id, $data_customer_name, $data_salutation, $data_first_name, $data_middle_name, $data_last_name, $data_suffix_id, $data_gender_id, $data_nationality_id, $data_dob, $data_marital_status_id, $data_source_of_contact_id, $data_mode_of_contact_id, $data_spouse, $data_no_of_children, $data_occupation, $data_contact_person, //$data_sales_person, $data_date_created, $data_time_created, $selected_dms_id, $address_1, $address_2, $address_1_state, $address_2_state, $address_1_city, $address_2_city, $address_1_postal, $address_2_postal, $data_email_1, $data_email_2, $data_business_phone, $data_fax_phone, $data_mobile_1, $data_mobile_2, $data_residential_phone, $data_business_name, $business_address, $business_state_id, $business_city_id, $business_postal, // $interest_id, // $data_hobby, $interest_list_id, $affiliations_list_id, $data_religion_id, $data_viber, $data_telegram, $data_facebook, $data_instagram, $data_whatsapp, $data_website, $data_nature_of_work_id, $data_profession_id, $db ) { $customer_record_id = insertCustomer( $data_customer_id, $data_customer_name, $data_salutation, $data_first_name, $data_middle_name, $data_last_name, $data_suffix_id, $data_gender_id, $data_nationality_id, $data_dob, $data_marital_status_id, $data_source_of_contact_id, $data_mode_of_contact_id, $data_spouse, $data_no_of_children, $data_occupation, //$data_sales_person, $data_date_created, $data_time_created, $selected_dms_id, $data_religion_id, $data_nature_of_work_id, $data_profession_id, $db ); $GLOBALS['customer_contact'][] = "('$data_viber','$data_telegram','$data_facebook','$data_instagram','$data_whatsapp','$data_website','$customer_record_id','$address_1','$address_2','$address_1_state','$address_2_state','$address_1_city','$address_2_city','$address_1_postal','$address_2_postal','$data_email_1','$data_email_2','$data_business_phone','$data_fax_phone','$data_mobile_1','$data_mobile_2','$data_residential_phone' ,'1')"; //insert customer business if not empty if (isNotEmpty($data_business_name)) { } if (isNotEmpty(trim($interest_list_id))) { $GLOBALS['customer_hobbies'][] = "('$interest_list_id', '$customer_record_id', '1' )"; } if (isNotEmpty(trim($affiliations_list_id))) { $GLOBALS['customer_affiliation'][] = "('$affiliations_list_id', '$customer_record_id', '1' )"; } if (isNotEmpty(trim($data_contact_person))) { $GLOBALS['customer_contact_person'][] = "('$data_contact_person', '$customer_record_id', '1' )"; } // $GLOBALS['customer_inserted_count']++; } function getMasterRecord($company_name, $first_name, $last_name, $email_address, $db) { if ((isNotEmpty($first_name) && isNotEmpty($last_name))) { $append_query = " AND (ci.first_name = '$first_name' and ci.last_name = '$last_name' and cc.mobile_phone_1 = '$email_address') "; } else { // $append_query = " AND (ci.corporation_name = '$company_name' and cc.mobile_phone_1 = '$email_address') "; $append_query = " AND (ci.corporation_name = '$company_name') "; } $return_data = array(); $customer_info_individual_data_query = "SELECT cd.`dms_id`, cd.`customer_record_id`, cd.`customer_dms_id`, cd.`company_dealer_id`,cc.email_1,cc.mobile_phone_1,ci.last_name FROM customer ci INNER JOIN customer_dms cd ON ci.id=cd.customer_record_id INNER JOIN customer_contact cc ON cd.customer_record_id=cc.customer_id WHERE 1 %s AND cd.status = 1"; $customer_individual_info_data = $db->sql_query(sprintf($customer_info_individual_data_query, $append_query)); while ($row = $customer_individual_info_data->fetch_assoc()) { $return_data = array("dms_id" => $row["dms_id"], "customer_record_id" => $row["customer_record_id"], "customer_dms_id" => $row["customer_dms_id"], "company_dealer_id" => $row["company_dealer_id"], "mobile_phone_1" => $row["mobile_phone_1"], "email_1" => $row["email_1"], "last_name" => $row["last_name"]); } return $return_data; } function getExistVehicleBrandId($vehicle_id, $db) { return $db->select("SELECT brand_id FROM vehicle WHERE id = '$vehicle_id' LIMIT 1"); } function getExistVehicleModelId($vehicle_id, $db) { return $db->select("SELECT model_id FROM vehicle WHERE id = '$vehicle_id' LIMIT 1"); } function getExistVehicleModelVariantId($vehicle_id, $db) { return $db->select("SELECT model_variant_description FROM vehicle WHERE id = '$vehicle_id' LIMIT 1"); } function getVehicleID($data_plate_number, $data_conduction_sticker, $db) { if (isNotEmpty($data_plate_number)) { $vehicle_id = $db->select("SELECT id FROM vehicle WHERE plate_number = '$data_plate_number' LIMIT 1"); if ( isNotEmpty($vehicle_id) ) { return $vehicle_id; } } if (isNotEmpty($data_conduction_sticker)) { $vehicle_id = $db->select("SELECT id FROM vehicle WHERE conduction_sticker = '$data_conduction_sticker' LIMIT 1"); if ( isNotEmpty($vehicle_id) ) { return $vehicle_id; } } return ""; } function findIndex($customer_id, array $original_array, array $new_array) { for ($i = 0; $i < count($original_array); $i++) { if (isset($new_array[$i])) { // if(strpos( $arr_customer_individual[$found_index], $last_name ) !== FALSE ){ // } return $i; } } }

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