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' => 'Invalid spreadsheet file. Contact your system administrator.
Error: ' . $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'] . "| Merged Customer | " . $count_record . " |
";
$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'] . "| " . $GLOBALS['customer_already_exists'] . " | " . $count_record . " |
";
$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'] . "| Dealer Not Found | " . $count_record . " |
";
$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'] . "| No Customer No. | " . $count_record . " |
";
$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'] . "| Empty Customer Name | " . $count_record . " |
";
$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'] . "| No Email Address | " . $count_record . " |
";
$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'] . "| Invalid Email Address | " . $count_record . " |
";
$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'] . "| Email Already Exist | " . $count_record . " |
";
$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'] . "| Mobile Already Exist | " . $count_record . " |
";
$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'] . "| No Mobile Number | " . $count_record . " |
";
$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'] . "| " . $GLOBALS['invalid_mobile'] . " | " . $count_record . " |
";
$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'] . "| Total Not Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_not_inserted . " |
";
$GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "| Total Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_inserted . " |
";
$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'] . "| Duplicates (Already in Database) | " . $count_record . " |
";
$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'] . "| Dealer Not Found | " . $count_record . " |
";
$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'] . "| No Customer No. | " . $count_record . " |
";
$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'] . "| No Plate & CS | " . $count_record . " |
";
$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'] . "| Total Not Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_not_inserted . " |
";
$GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "| Total Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_inserted . " |
";
$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']."| Duplicates (Already in Database) | ".$count_record." |
";
// $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'] . "| Dealer Not Found | " . $count_record . " |
";
$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'] . "| No Customer No. | " . $count_record . " |
";
$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'] . "| No Plate & CS | " . $count_record . " |
";
$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'] . "| No RO Number | " . $count_record . " |
";
$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'] . "| Total Not Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_not_inserted . " |
";
$GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "| Total Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_inserted . " |
";
$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'] . "| Duplicates (Already in Database) | " . $count_record . " |
";
$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'] . "| Dealer Not Found | " . $count_record . " |
";
$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'] . "| No Customer No. | " . $count_record . " |
";
$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'] . "| No Plate & CS | " . $count_record . " |
";
$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'] . "| No Order Number | " . $count_record . " |
";
$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'] . "| Total Not Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_not_inserted . " |
";
$GLOBALS['data_count_list'] = $GLOBALS['data_count_list'] . "| Total Inserted " . $GLOBALS['database_type_name'] . " | " . $count_record_inserted . " |
";
$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'] . "| Total Data | " . $GLOBALS['loop_count'] . " |
";
$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'] . "| Data with issues | |
";
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;
}
}
}