File manager

File manager - Edit - /home/autoph/public_html/connectv1/api/upload/upload_spreadsheet.php

Back
<?php ini_set('max_execution_time', '0'); set_time_limit(0); ini_set('memory_limit','-1'). include_once("../../cfg/db.php"); include_once("../../app/language/word_mapping.php"); include_once('../../vendor/autoload.php'); include_once("../../api/controllers/utility.php"); include_once("../../api/controllers/customer.php"); include_once("../../api/controllers/import.php"); $utility = new Utility(); $customer_class = new Customer(); $import = new Import(); 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($_REQUEST["request"])){ $request = $_REQUEST["request"]; if($request === 'delete'){ $file_name = $_REQUEST["name"]; $location = "files/".$file_name; if(file_exists($location)){ unlink($location); } exit; } } if(!$utility -> isNotEmpty($_FILES['file']['name'])){ $return_arr["status"]=0; $return_arr["message"]=" Error has occured."; exit; } // Getting file name $filename = $_FILES['file']['name']; //Valid extension $valid_ext = array('xls', 'csv', 'xlsx'); // file extension $file_extension = pathinfo($filename, PATHINFO_EXTENSION); $file_extension = strtolower($file_extension); $location = "files/".$filename = $_FILES['file']['name']; // Check extension if(!in_array($file_extension,$valid_ext)){ $return_arr["status"]=0; $return_arr["message"]=" Invalid file type."; exit; } $maxsize = 50097152; $minsize = 1; if($_FILES['file']['size'] >= $maxsize \|\| $_FILES['file']['size'] == 0 ){ $return_arr["status"]=0; $return_arr["message"]=" File too large. Image must less than ".round(($maxsize/1000000), 0)." megabytes."; echo json_encode($return_arr); exit; } //VALIDATIONS SUCCESS CONTINUE BELOW //GLOBALS //COUNTINGS $GLOBALS['total_data_count'] = 0; $GLOBALS['inserted_count'] = 0; $GLOBALS['inserted_vehicle_count'] = 0; $GLOBALS['inserted_sales_count'] = 0; $GLOBALS['not_inserted_count'] = 0; $GLOBALS['no_dms_count']= 0; $GLOBALS['no_brand_count']= 0; $GLOBALS['no_model_count']= 0; $GLOBALS['no_company_count']= 0; $GLOBALS['no_dealer_count']= 0; $GLOBALS['no_name_count']= 0; $GLOBALS['no_mobile_count']= 0; $GLOBALS['invalid_gm_count']=0; $GLOBALS['invalid_sc_count']= 0; $GLOBALS['invalid_mobile_count']= 0; $GLOBALS['no_cs_count']= 0; $GLOBALS['duplicate_plate_count']= 0; $GLOBALS['duplicate_cs_count']= 0; $GLOBALS['duplicate_customer_count']= 0; $GLOBALS['duplicate_mobile_count']= 0; $GLOBALS['duplicate_email_count']= 0; $GLOBALS['no_date_count']= 0; $GLOBALS['invalid_date_count']= 0; $GLOBALS['under_age_count']= 0; //ARRAYS $GLOBALS['row_dms_headers'] = array(); $GLOBALS['not_inserted_list_v2'] = array(); //ERROR MESSAGES $GLOBALS['no_brand'] = "Brand not found."; $GLOBALS['no_model'] = "Model not found."; $GLOBALS['no_dms'] = "DMS not found."; $GLOBALS['no_company'] = "Company not found."; $GLOBALS['no_dealer'] = "Dealer not found."; $GLOBALS['no_name']= "Customer name not found."; $GLOBALS['no_mobile']= "Mobile not found."; $GLOBALS['invalid_mobile']= "Invalid mobile."; $GLOBALS['invalid_gm']= "Group manager not found."; $GLOBALS['invalid_sc']= "Sales consultant not found."; $GLOBALS['no_cs']= "Conduction sticker not found."; $GLOBALS['duplicate_plate']= "Duplicate plate."; $GLOBALS['duplicate_cs']= "Duplicate conduction sticker."; $GLOBALS['duplicate_customer']= "Duplicate customer record."; $GLOBALS['duplicate_mobile']= "Duplicate mobile."; $GLOBALS['duplicate_email']= "Duplicate email."; $GLOBALS['no_date']= "Invalid date format."; $GLOBALS['invalid_date']= "Invalid date."; $GLOBALS['under_age']= "Invalid age (must be 17 and above)."; //ARRAY TO BE INSERTED $GLOBALS['user_id']= $_SESSION['user']['id']; $GLOBALS['customer_affiliation'] = array(); $GLOBALS['vehicle_data'] = array(); $GLOBALS['vehicle_data_plate'] = array(); $GLOBALS['vehicle_data_cs'] = array(); $GLOBALS['sales_data'] = array(); $GLOBALS['vehicle_offered'] = array(); //REPORTING $GLOBALS['data_count_list'] = ""; $GLOBALS['data_count_list_v2'] = array(); //process excel file move_uploaded_file($_FILES['file']['tmp_name'], $location); $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($location); $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type); try { $spreadsheet = $reader->load($location); //remove excel after store to phpspreadsheet unlink($location); $data = $spreadsheet->getActiveSheet()->toArray(); } catch (\Exception $e) { $return_arr["status"]=2; $return_arr["header_column_not_found_message"] = array(array('message'=>'<strong>Invalid spreadsheet file. Contact your system administrator.<br><span class="text-danger">Error:</span></strong> '.$e)); echo json_encode($return_arr); exit; } $data = array_map(array($utility, 'upperCaseNestedArray'), $data); $data = array_map(array($db, 'escape'), $data); $highestRow = $spreadsheet->getActiveSheet()->getHighestRow(); $highestColumn = $spreadsheet->getActiveSheet()->getHighestColumn(); $ColumnNumber = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // print_r($data);exit; // echo count($data);exit; // echo $highestRow.' '.$highestColumn.' '.$ColumnNumber;exit; //get available fieldnames from database $array_dms = $import->getArrayFields(1,$db); $array_company = $import->getArrayFields(2,$db); $array_dealer = $import->getArrayFields(3,$db); $array_customer_no = $import->getArrayFields(4,$db); $array_corporation_name = $import->getArrayFields(5,$db); $array_first_name = $import->getArrayFields(6,$db); $array_middle_name = $import->getArrayFields(7,$db); $array_last_name = $import->getArrayFields(8,$db); $array_suffix = $import->getArrayFields(9,$db); $array_profession = $import->getArrayFields(10,$db); $array_position = $import->getArrayFields(11,$db); $array_business_company = $import->getArrayFields(12,$db); $array_nature_of_business = $import->getArrayFields(13,$db); $array_affiliation = $import->getArrayFields(14,$db); $array_facebook = $import->getArrayFields(15,$db); $array_address_1 = $import->getArrayFields(16,$db); $array_address_2 = $import->getArrayFields(17,$db); $array_email = $import->getArrayFields(18,$db); $array_mobile = $import->getArrayFields(19,$db); $array_business_phone = $import->getArrayFields(20,$db); $array_birthday = $import->getArrayFields(21,$db); $array_gender = $import->getArrayFields(22,$db); $array_brand = $import->getArrayFields(23,$db); $array_model = $import->getArrayFields(24,$db); $array_model_variant = $import->getArrayFields(25,$db); $array_color = $import->getArrayFields(26,$db); $array_conduction_sticker = $import->getArrayFields(27,$db); $array_plate_number = $import->getArrayFields(28,$db); $array_vin = $import->getArrayFields(29,$db); $array_registration_no = $import->getArrayFields(30,$db); $array_date = $import->getArrayFields(31,$db); $array_release_no = $import->getArrayFields(32,$db); $array_mode_of_sale = $import->getArrayFields(33,$db); $array_type_of_payment = $import->getArrayFields(34,$db); $array_insurance = $import->getArrayFields(35,$db); $array_source = $import->getArrayFields(36,$db); $array_offered_vehicle = $import->getArrayFields(37,$db); $array_sales_manager = $import->getArrayFields(38,$db); $array_sales_consultant = $import->getArrayFields(39,$db); $array_model_year = $import->getArrayFields(40,$db); $array_end_user = $import->getArrayFields(41,$db); $array_car_club = $import->getArrayFields(42,$db); $array_city = $import -> getArrayFields(43,$db); $array_aha_status = $import -> getArrayFields(44,$db); $header_column_found = false; foreach($data as $row){ // print_r($row); if(!$header_column_found){ $issue_summary_index = $import->getIssueSummaryField('ISSUE SUMMARY',$ColumnNumber,$row); //get matched field from database and excel globals $GLOBALS['dms']= $import->getFieldFromExcel($row,$ColumnNumber,$array_dms); $GLOBALS['company']= $import->getFieldFromExcel($row,$ColumnNumber,$array_company); $GLOBALS['dealer']= $import->getFieldFromExcel($row,$ColumnNumber,$array_dealer); $GLOBALS['customer_no']= $import->getFieldFromExcel($row,$ColumnNumber,$array_customer_no); $GLOBALS['corporation_name']= $import->getFieldFromExcel($row,$ColumnNumber,$array_corporation_name); $GLOBALS['first_name']= $import->getFieldFromExcel($row,$ColumnNumber,$array_first_name); $GLOBALS['middle_name']= $import->getFieldFromExcel($row,$ColumnNumber,$array_middle_name); $GLOBALS['last_name']= $import->getFieldFromExcel($row,$ColumnNumber,$array_last_name); $GLOBALS['suffix']= $import->getFieldFromExcel($row,$ColumnNumber,$array_suffix); $GLOBALS['profession']= $import->getFieldFromExcel($row,$ColumnNumber,$array_profession); $GLOBALS['position']= $import->getFieldFromExcel($row,$ColumnNumber,$array_position); $GLOBALS['business_company']= $import->getFieldFromExcel($row,$ColumnNumber,$array_business_company); $GLOBALS['nature_of_business']= $import->getFieldFromExcel($row,$ColumnNumber,$array_nature_of_business); $GLOBALS['affiliation']= $import->getFieldFromExcel($row,$ColumnNumber,$array_affiliation); $GLOBALS['facebook']= $import->getFieldFromExcel($row,$ColumnNumber,$array_facebook); $GLOBALS['address_1']= $import->getFieldFromExcel($row,$ColumnNumber,$array_address_1); $GLOBALS['address_2']= $import->getFieldFromExcel($row,$ColumnNumber,$array_address_2); $GLOBALS['email']= $import->getFieldFromExcel($row,$ColumnNumber,$array_email); $GLOBALS['mobile']= $import->getFieldFromExcel($row,$ColumnNumber,$array_mobile); $GLOBALS['business_phone']= $import->getFieldFromExcel($row,$ColumnNumber,$array_business_phone); $GLOBALS['birthday']= $import->getFieldFromExcel($row,$ColumnNumber,$array_birthday); $GLOBALS['gender']= $import->getFieldFromExcel($row,$ColumnNumber,$array_gender); $GLOBALS['brand']= $import->getFieldFromExcel($row,$ColumnNumber,$array_brand); $GLOBALS['model']= $import->getFieldFromExcel($row,$ColumnNumber,$array_model); $GLOBALS['model_variant']= $import->getFieldFromExcel($row,$ColumnNumber,$array_model_variant); $GLOBALS['color']= $import->getFieldFromExcel($row,$ColumnNumber,$array_color); $GLOBALS['conduction_sticker']= $import->getFieldFromExcel($row,$ColumnNumber,$array_conduction_sticker); $GLOBALS['plate_number']= $import->getFieldFromExcel($row,$ColumnNumber,$array_plate_number); $GLOBALS['vin']= $import->getFieldFromExcel($row,$ColumnNumber,$array_vin); $GLOBALS['registration_no']= $import->getFieldFromExcel($row,$ColumnNumber,$array_registration_no); $GLOBALS['date']= $import->getFieldFromExcel($row,$ColumnNumber,$array_date); $GLOBALS['release_no']= $import->getFieldFromExcel($row,$ColumnNumber,$array_release_no); $GLOBALS['mode_of_sale']= $import->getFieldFromExcel($row,$ColumnNumber,$array_mode_of_sale); $GLOBALS['type_of_payment']= $import->getFieldFromExcel($row,$ColumnNumber,$array_type_of_payment); $GLOBALS['insurance']= $import->getFieldFromExcel($row,$ColumnNumber,$array_insurance); $GLOBALS['source']= $import->getFieldFromExcel($row,$ColumnNumber,$array_source); $GLOBALS['offered_vehicle']= $import->getFieldFromExcel($row,$ColumnNumber,$array_offered_vehicle); $GLOBALS['sales_manager']= $import->getFieldFromExcel($row,$ColumnNumber,$array_sales_manager); $GLOBALS['sales_consultant']= $import->getFieldFromExcel($row,$ColumnNumber,$array_sales_consultant); $GLOBALS['model_year']= $import->getFieldFromExcel($row,$ColumnNumber,$array_model_year); $GLOBALS['end_user']= $import->getFieldFromExcel($row,$ColumnNumber,$array_end_user); $GLOBALS['car_club']= $import->getFieldFromExcel($row,$ColumnNumber,$array_car_club); $GLOBALS['city'] = $import->getFieldFromExcel($row,$ColumnNumber,$array_city); $GLOBALS['aha_status'] = $import->getFieldFromExcel($row,$ColumnNumber,$array_aha_status); //check required fields if( // $utility->isNotEmpty($GLOBALS['dms']) && // $utility->isNotEmpty($GLOBALS['company']) && $utility->isNotEmpty($GLOBALS['dealer']) && // $utility->isNotEmpty($GLOBALS['customer_no']) && $utility->isNotEmpty($GLOBALS['corporation_name']) && $utility->isNotEmpty($GLOBALS['first_name']) && $utility->isNotEmpty($GLOBALS['middle_name']) && $utility->isNotEmpty($GLOBALS['last_name']) && // $utility->isNotEmpty($GLOBALS['suffix']) && $utility->isNotEmpty($GLOBALS['profession']) && $utility->isNotEmpty($GLOBALS['position']) && // $utility->isNotEmpty($GLOBALS['business_company']) && $utility->isNotEmpty($GLOBALS['nature_of_business']) && $utility->isNotEmpty($GLOBALS['affiliation']) && $utility->isNotEmpty($GLOBALS['facebook']) && $utility->isNotEmpty($GLOBALS['address_1']) && $utility->isNotEmpty($GLOBALS['address_2']) && $utility->isNotEmpty($GLOBALS['email']) && $utility->isNotEmpty($GLOBALS['mobile']) && $utility->isNotEmpty($GLOBALS['business_phone']) && $utility->isNotEmpty($GLOBALS['birthday']) && $utility->isNotEmpty($GLOBALS['gender']) && $utility->isNotEmpty($GLOBALS['brand']) && $utility->isNotEmpty($GLOBALS['model']) && $utility->isNotEmpty($GLOBALS['model_variant']) && $utility->isNotEmpty($GLOBALS['color']) && $utility->isNotEmpty($GLOBALS['conduction_sticker']) && $utility->isNotEmpty($GLOBALS['plate_number']) && $utility->isNotEmpty($GLOBALS['vin']) && // $utility->isNotEmpty($GLOBALS['registration_no']) && $utility->isNotEmpty($GLOBALS['date']) && $utility->isNotEmpty($GLOBALS['release_no']) && $utility->isNotEmpty($GLOBALS['mode_of_sale']) && $utility->isNotEmpty($GLOBALS['type_of_payment']) && $utility->isNotEmpty($GLOBALS['insurance']) && $utility->isNotEmpty($GLOBALS['source']) && $utility->isNotEmpty($GLOBALS['offered_vehicle']) && $utility->isNotEmpty($GLOBALS['sales_manager']) && $utility->isNotEmpty($GLOBALS['sales_consultant']) && $utility->isNotEmpty($GLOBALS['model_year']) && $utility->isNotEmpty($GLOBALS['end_user']) && $utility->isNotEmpty($GLOBALS['car_club']) && $utility->isNotEmpty($GLOBALS['city']) ){ $header_column_found = true; $GLOBALS['row_dms_headers'] = $row; continue; } }else{ $GLOBALS['total_data_count']++; //start counting of data if header is found $import->start_import($row,$customer_class,$utility,$db); } } if(!$header_column_found){ $return_arr["status"]=2; $return_arr["header_column_not_found_message"] = array(array('message'=>'<strong>Invalid spreadsheet file. Required field not found, download the template or contact your system administrator.</strong>')); echo json_encode($return_arr); exit; }else{ if(count($GLOBALS['customer_affiliation']) > 0){ $customer_affiliation_query = 'INSERT INTO `customer_affiliations_list` (`affiliations_content_id`, `customer_id`, `status`) VALUES '.implode(",",$GLOBALS['customer_affiliation']).' '; $db -> sql_query($customer_affiliation_query); } if(count($GLOBALS['vehicle_data']) > 0){ $vehicle_data_query = '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']).' '; $GLOBALS['inserted_vehicle_count'] = $db -> sql_query_num_inserted($vehicle_data_query); } if(count($GLOBALS['sales_data']) > 0){ $sales_data_query = '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']).' '; $GLOBALS['inserted_sales_count'] = $db -> sql_query_num_inserted($sales_data_query); } if(count($GLOBALS['vehicle_offered']) > 0){ $vehicle_offered_query = 'INSERT INTO `sales_brands_offered`( `sales_id`, `brand_id`, `model_id`, `description`, `status`) VALUES '.implode(",",$GLOBALS['vehicle_offered']).' '; $db -> sql_query($vehicle_offered_query); } $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 cc.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_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 v FROM vehicle v // LEFT OUTER JOIN customer_contact cc // ON v.customer_record_id = cc.customer_id // WHERE cc.id IS NULL"); $db -> sql_query("DELETE s FROM sales s LEFT OUTER JOIN vehicle v ON s.vehicle_id = v.id WHERE v.id IS NULL"); $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(intval($GLOBALS['no_dms_count']) > 0){ $count_record = max(0,$GLOBALS['no_dms_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_dms']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_dms'],$count_record); } if(intval($GLOBALS['no_company_count']) > 0){ $count_record = max(0,$GLOBALS['no_company_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_company']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_company'],$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>".$GLOBALS['no_dealer']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_dealer'],$count_record); } if(intval($GLOBALS['no_date_count']) > 0){ $count_record = max(0,$GLOBALS['no_date_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_date']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_date'],$count_record); } if(intval($GLOBALS['invalid_date_count']) > 0){ $count_record = max(0,$GLOBALS['invalid_date_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['invalid_date']."</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_date'],$count_record); } if(intval($GLOBALS['no_name_count']) > 0){ $count_record = max(0,$GLOBALS['no_name_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_name']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_name'],$count_record); } if(intval($GLOBALS['no_mobile_count']) > 0){ $count_record = max(0,$GLOBALS['no_mobile_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_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['no_mobile'],$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); } if(intval($GLOBALS['under_age_count']) > 0){ $count_record = max(0,$GLOBALS['under_age_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['under_age']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['under_age'],$count_record); } if(intval($GLOBALS['duplicate_customer_count']) > 0){ $count_record = max(0,$GLOBALS['duplicate_customer_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['duplicate_customer']."</td> <td class='text-center'><h5><span class='badge bg-warning' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['duplicate_customer'],$count_record); } if(intval($GLOBALS['duplicate_mobile_count']) > 0){ $count_record = max(0,$GLOBALS['duplicate_mobile_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['duplicate_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['duplicate_mobile'],$count_record); } if(intval($GLOBALS['duplicate_email_count']) > 0){ $count_record = max(0,$GLOBALS['duplicate_email_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['duplicate_email']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['duplicate_email'],$count_record); } if(intval($GLOBALS['no_cs_count']) > 0){ $count_record = max(0,$GLOBALS['no_cs_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_cs']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_cs'],$count_record); } if(intval($GLOBALS['no_brand_count']) > 0){ $count_record = max(0,$GLOBALS['no_brand_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_brand']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_brand'],$count_record); } if(intval($GLOBALS['no_model_count']) > 0){ $count_record = max(0,$GLOBALS['no_model_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['no_model']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['no_model'],$count_record); } if(intval($GLOBALS['duplicate_cs_count']) > 0){ $count_record = max(0,$GLOBALS['duplicate_cs_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['duplicate_cs']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['duplicate_cs'],$count_record); } if(intval($GLOBALS['duplicate_plate_count']) > 0){ $count_record = max(0,$GLOBALS['duplicate_plate_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['duplicate_plate']."</td> <td class='text-center'><h5><span class='badge bg-danger' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array($GLOBALS['duplicate_plate'],$count_record); } if(intval($GLOBALS['invalid_gm_count']) > 0){ $count_record = max(0,$GLOBALS['invalid_gm_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['invalid_gm']."</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_gm'],$count_record); } if(intval($GLOBALS['invalid_sc_count']) > 0){ $count_record = max(0,$GLOBALS['invalid_sc_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>".$GLOBALS['invalid_sc']."</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_sc'],$count_record); } if(intval($GLOBALS['inserted_vehicle_count']) > 0){ $count_record = max(0,$GLOBALS['inserted_vehicle_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Inserted Vehicle Record</td> <td class='text-center'><h5><span class='badge bg-primary' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array('Inserted Vehicle Record',$count_record); } if(intval($GLOBALS['inserted_sales_count']) > 0){ $count_record = max(0,$GLOBALS['inserted_sales_count']); $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Inserted Sales Record</td> <td class='text-center'><h5><span class='badge bg-primary' >".$count_record."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array('Inserted Sales Record',$count_record); } $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Total Not Inserted</td> <td class='text-center'><h5><span class='badge bg-danger' >".intval($GLOBALS['not_inserted_count'])."</span></h5></td></tr>"; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Total Inserted</td> <td class='text-center'><h5><span class='badge bg-success' >".max(0, $GLOBALS['inserted_count'])."</span></h5></td></tr>"; $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Total Data</td> <td class='text-center'><h5><span class='badge bg-primary' >".$GLOBALS['total_data_count']."</span></h5></td></tr>"; $GLOBALS['data_count_list_v2'][] = array("Total Not Inserted",(string) intval($GLOBALS['not_inserted_count'])); $GLOBALS['data_count_list_v2'][] = array("Total Inserted",(string) max(0, $GLOBALS['inserted_count'])); $GLOBALS['data_count_list_v2'][] = array("Total Data",(string) $GLOBALS['total_data_count']); if(intval($GLOBALS['not_inserted_count']) > 0){ $GLOBALS['data_count_list'] = $GLOBALS['data_count_list']."<tr><td>Data with issues</td> <td class='text-center'><h5><a href='api/upload/logs/".$filename."'><span class='badge bg-primary' style='cursor:pointer;'>Download</span></a></h5></td></tr>"; } returnData($filename,$highestColumn,$issue_summary_index); } 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["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); $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $data_count_list_v2_count = count($GLOBALS['data_count_list_v2']); // echo ('B'.($data_count_list_v2_count+1).':'.(string) $highestColumn.'1');exit; $spreadsheet ->getActiveSheet() // ->getStyle('B'.($data_count_list_v2_count+1).':'.(string) $highestColumn.'1') ->getStyle('B'.($data_count_list_v2_count+1).':'.(string) $highestColumn.($data_count_list_v2_count+1)) ->getFill() ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) ->getStartColor() ->setARGB('FFFF00'); $spreadsheet ->getActiveSheet() ->getStyle('A'.($data_count_list_v2_count+1).'') ->getFill() ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) ->getStartColor() ->setARGB('FF0000'); $spreadsheet->getActiveSheet()->getStyle('A'.($data_count_list_v2_count+1)) ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE); $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($data_count_list_v2_count> 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; } ?>

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