File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/api/upload/upload_spreadsheet_car_club.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('../../vendor/autoload.php'); include_once("../../api/controllers/car_club.php"); include_once("../../api/controllers/brand.php"); include_once("../../api/controllers/model.php"); $car_club = new CarClub(); $brand_class = new Brand(); $model_class = new Model(); 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 (!isset($_FILES['file'])) { $return_arr["status"] = 0; // $return_arr["change_name"] = $gen_filename.".".$file_extension; // $return_arr["orig_name"] = $filename; $return_arr["message"] = " Error has occured."; echo json_encode($return_arr); exit; } // Getting file name $filename = $_FILES['file']['name']; $gen_filename = $db->select('SELECT UUID()'); //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']; $location = "files/" . $gen_filename . "." . $file_extension; // Check extension if (!in_array($file_extension, $valid_ext)) { $return_arr["status"] = 0; $return_arr["change_name"] = $gen_filename . "." . $file_extension; $return_arr["orig_name"] = $filename; $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["change_name"] = $gen_filename . "." . $file_extension; $return_arr["orig_name"] = $filename; $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 //process excel file // move_uploaded_file($_FILES['file']['tmp_name'], $location); if (!move_uploaded_file($_FILES["file"]["tmp_name"], $location)) { $return_arr["status"] = 0; $return_arr["change_name"] = $gen_filename . "." . $file_extension; $return_arr["orig_name"] = $filename; $return_arr["message"] = " File Not Uploaded."; exit; } $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); // $spreadsheet->getActiveSheet()->getStyle("K")->getNumberFormat()->setFormatCode("YYYY-MM-DD"); // $spreadsheet->getActiveSheet()->getStyle('K') // ->getNumberFormat() // ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); $data = $spreadsheet->getActiveSheet()->toArray(); } catch (\Exception $e) { $return_arr["status"] = 0; $return_arr["change_name"] = $gen_filename . "." . $file_extension; $return_arr["orig_name"] = $filename; $return_arr["message"] = " Invalid File."; echo json_encode($return_arr); exit; } $data = array_map(array($utility_class, 'upperCaseNestedArray'), $data); $data = array_map(array($db, 'escape'), $data); $highestRow = $spreadsheet->getActiveSheet()->getHighestRow(); $highestColumn = $spreadsheet->getActiveSheet()->getHighestColumn(); $ColumnNumber = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); $GLOBALS['total_data_count'] = 0; $GLOBALS['not_inserted_data_count'] = 0; $GLOBALS['inserted_data_count'] = 0; $GLOBALS['not_inserted_list_v2'] = array(); $GLOBALS['data_count_list_v2'] = array(); // $GLOBALS['data_count_list'] = ""; // $array_club_name = getArrayFields(1,$db); // $array_brand = getArrayFields(2,$db); // $array_model = getArrayFields(3,$db); // $array_membership_fee = getArrayFields(4,$db); // $array_president_n_mobile = getArrayFields(5,$db); // $array_members_count = getArrayFields(6,$db); // $array_active_members_count = getArrayFields(7,$db); // $array_facebook_members_count = getArrayFields(8,$db); // $array_assigned_person = getArrayFields(9,$db); // $array_club_status = getArrayFields(10,$db); //update fields $array_data = json_decode($_REQUEST['json_data'], true); foreach ($array_data as $key => $value) { $array_data[$key] = $db->escape(strtoupper($array_data[$key])); } updateCarClubFields($array_data, $db); $user_session = $utility_class->user(); $query_insert_update_data = array(); $header_column_found = false; foreach ($data as $row) { // //check if all value in row if not empty foreach ($row as $row1) { if ($utility_class->isNotEmpty($row1)) { break; } continue 2; } if (!$header_column_found) { // get matched field from database and excel globals $issue_summary_index = getIssueSummaryField('ISSUE SUMMARY', $ColumnNumber, $row); $GLOBALS['car_club_name'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[1])); $GLOBALS['brand'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[2])); $GLOBALS['model'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[3])); $GLOBALS['membership_fee'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[4])); $GLOBALS['president_n_mobile'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[5])); $GLOBALS['members_count'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[6])); $GLOBALS['active_members_count'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[7])); $GLOBALS['facebook_members_count'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[8])); $GLOBALS['assigned_person'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[9])); $GLOBALS['club_status'] = getFieldFromExcel($row, $ColumnNumber, array($array_data[10])); // check required fields if ( $utility_class->isNotEmpty($GLOBALS['car_club_name']) && $utility_class->isNotEmpty($GLOBALS['brand']) && $utility_class->isNotEmpty($GLOBALS['model']) && $utility_class->isNotEmpty($GLOBALS['membership_fee']) && $utility_class->isNotEmpty($GLOBALS['president_n_mobile']) && $utility_class->isNotEmpty($GLOBALS['members_count']) && $utility_class->isNotEmpty($GLOBALS['active_members_count']) && $utility_class->isNotEmpty($GLOBALS['facebook_members_count']) && $utility_class->isNotEmpty($GLOBALS['assigned_person']) && $utility_class->isNotEmpty($GLOBALS['club_status']) ) { $header_column_found = true; $GLOBALS['row_headers'] = $row; continue; } } else { $GLOBALS['total_data_count']++; //start counting of data if header is found $data_car_club_name = checkExist($row, $GLOBALS['car_club_name'], $utility_class); $data_brand = checkExist($row, $GLOBALS['brand'], $utility_class); $data_model = checkExist($row, $GLOBALS['model'], $utility_class); $data_membership_fee = checkExist($row, $GLOBALS['membership_fee'], $utility_class); $data_president_n_mobile = checkExist($row, $GLOBALS['president_n_mobile'], $utility_class); $data_members_count = checkExist($row, $GLOBALS['members_count'], $utility_class); $data_active_members_count = checkExist($row, $GLOBALS['active_members_count'], $utility_class); $data_facebook_members_count = checkExist($row, $GLOBALS['facebook_members_count'], $utility_class); $data_assigned_person = checkExist($row, $GLOBALS['assigned_person'], $utility_class); $data_club_status = checkExist($row, $GLOBALS['club_status'], $utility_class); if (!$utility_class->isNotEmpty($data_car_club_name)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array("EMPTY CLUB NAME"), $row); $GLOBALS['not_inserted_data_count']++; continue; } if (!$utility_class->isNotEmpty($data_assigned_person)) { $GLOBALS['not_inserted_list_v2'][] = array_merge(array("EMPTY ASSIGNED PERSON"), $row); $GLOBALS['not_inserted_data_count']++; continue; } //car club name id //get affiliations_content_id exist $car_club_obj = $car_club->read_affiliation_car_club_by_name($data_car_club_name, $db); //insert affiliations_content and get id if not exist $car_club_arr = array(); foreach ($car_club_obj as $car_club_row) { $car_club_arr = $car_club_row; } if (count($car_club_arr) <= 0) { $affiliation_car_club_id = $car_club->create_affiliation_car_club($data_car_club_name, $db); } else { $affiliation_car_club_id = $car_club_arr['id']; } //end car club name id //assigned name id //get assigned_id if exist $car_club_assigned_obj = $car_club->read_car_club_assigned_by_name($data_assigned_person, $db); //insert affiliations_content and get id if not exist $car_club_assigned_arr = array(); foreach ($car_club_assigned_obj as $car_club_assigned_row) { $car_club_assigned_arr = $car_club_assigned_row; } if (count($car_club_assigned_arr) <= 0) { $car_club_assigned_id = $car_club->create_car_club_assigned($data_assigned_person, $db); } else { $car_club_assigned_id = $car_club_assigned_arr['id']; } //end assigned club name id //brand and model $brand_id = ""; if (strpos($data_brand, 'ALL') === false) { $brand_id = $brand_class->read_brand_id_by_name_v2($data_brand, $db); if (!$utility_class->isNotEmpty($brand_id)) { $brand_id = $brand_class->create_brand_v2($data_brand, 0, $db); } } $model_id = ""; if ($utility_class->isNotEmpty($brand_id) && strpos($data_model, 'ALL') === false) { $model_id = $model_class->read_model_id_by_name_v2($data_brand, $data_model, $db); if (!$utility_class->isNotEmpty($model_id)) { $model_id = $model_class->create_model_v2($data_brand, $data_model, 0, $db); } } $data_club_status = 0; if ($data_club_status == "ACTIVE") { $data_club_status = 1; } $query_insert_update_data[] = "(NULLIF('$affiliation_car_club_id',''), NULLIF('$brand_id',''), NULLIF('$model_id',''), NULLIF('$data_active_members_count',''), NULLIF('$data_members_count',''), NULLIF('$data_facebook_members_count',''), NULLIF('$data_membership_fee',''), NULLIF('$car_club_assigned_id',''), NULLIF('" . $user_session['id'] . "',''), NULLIF('$data_club_status',''), NOW(), 1 )"; $q = 'INSERT IGNORE INTO `car_clubs`( `car_club_name_id`, `brand_id`, `model_id`, `active_members_count`, `members_count`, `facebook_members_count`, `membership_fee`, `assigned_person`, `user_id`, `car_club_status`, `date_added`, `status`) VALUES ' . implode(",", $query_insert_update_data) . ' ON DUPLICATE KEY UPDATE car_club_name_id = VALUES (car_club_name_id), brand_id = VALUES (brand_id), model_id = VALUES (model_id), active_members_count = VALUES (active_members_count), members_count = VALUES (members_count), facebook_members_count = VALUES (facebook_members_count), membership_fee = VALUES (membership_fee), assigned_person = VALUES (assigned_person), car_club_status = VALUES (car_club_status), status = VALUES (status) '; $car_club_id = $db->sql_query_id($q); if (!$car_club_id) { $car_club_id = $car_club->read_car_club_id_by_car_club_name_id($affiliation_car_club_id, $db); } //car club president $data_president_n_mobile_arr = explode("/", $data_president_n_mobile); if (count($data_president_n_mobile_arr) > 0) { $data_president_name = ""; if (isset($data_president_n_mobile_arr[0])) { $name_parts = $utility_class->separateName($data_president_n_mobile_arr[0]); if (count($name_parts) > 0) { $data_president_name = $name_parts[0] . ' ' . $name_parts[1]; $data_president_mobile = ""; if (isset($data_president_n_mobile_arr[1])) { $data_president_mobile = $utility_class->fix_mobile_format_v2($utility_class->remove_non_numeric($data_president_n_mobile_arr[1])); } $pass_data['id'] = ""; $pass_data['Firstname'] = $name_parts[0]; $pass_data['Lastname'] = $name_parts[1]; $pass_data['Mobile'] = $data_president_mobile; $pass_data['Email'] = ""; if ($utility_class->isNotEmpty($pass_data['Mobile'])) { $user_member_id = $car_club->read_user_id_by_name_mobile($pass_data, $db); if (!$utility_class->isNotEmpty($user_member_id)) { $user_member_id = $car_club->create_user($pass_data, $utility_class, $db); } } else { $user_member_id = $car_club->read_user_id_by_name($pass_data, $db); if (!$utility_class->isNotEmpty($user_member_id)) { $user_member_id = $car_club->create_user($pass_data, $utility_class, $db); } } //add as officer $car_club->update_car_club_member_officers_status($car_club_id, $user_member_id, 1, 0, $db); $customer_contact_person_array = array(); $customer_contact_person_array[] = "('1','$user_member_id','" . $user_session['id'] . "',NOW(),'$car_club_id','1')"; $contact_person_to_insert = implode(',', $customer_contact_person_array); $car_club->create_car_club_officers($contact_person_to_insert, $db); //add as member $car_club_user_array_data['car_club_id'] = $car_club_id; $car_club_user_array_data['car_club_user_id'] = $user_member_id; $car_club_user_array_data['user_id'] = $user_session['id']; $car_club_user_array_data['member_status'] = 1; $car_club_user_id = $car_club->read_member_id_by_user_id_car_club_id($car_club_user_array_data, $db); if (!$utility_class->isNotEmpty($car_club_user_id)) { $car_club_user_id = $car_club->create_member($car_club_user_array_data, $db); } } } } $GLOBALS['inserted_data_count']++; } } // if(count($query_insert_update_data) > 0){ // // echo implode(",\n", $query_insert_update_data); // //do the insert update if exist // $q = 'INSERT INTO `car_clubs`( `car_club_name_id`, `brand_id`, `model_id`, `active_members_count`, `members_count`, `facebook_members_count`, `membership_fee`, `assigned_person`, `user_id`, `car_club_status`, `date_added`, `status`) // VALUES '.implode(",",$query_insert_update_data).' // ON DUPLICATE KEY UPDATE // car_club_name_id = VALUES (car_club_name_id), // brand_id = VALUES (brand_id), // model_id = VALUES (model_id), // active_members_count = VALUES (active_members_count), // members_count = VALUES (members_count), // facebook_members_count = VALUES (facebook_members_count), // membership_fee = VALUES (membership_fee), // assigned_person = VALUES (assigned_person), // car_club_status = VALUES (car_club_status) // '; // $rows_affected_count = $db -> sql_query_num_inserted($q); // } if (!$header_column_found) { $return_arr["status"] = 2; $return_arr["change_name"] = $gen_filename . "." . $file_extension; $return_arr["orig_name"] = $filename; $return_arr["message"] = " Header not found."; echo json_encode($return_arr); } else { $return_arr["status"] = 1; $return_arr["total"] = $GLOBALS['total_data_count']; $return_arr["inserted"] = $GLOBALS['inserted_data_count']; $return_arr["not_inserted"] = $GLOBALS['not_inserted_data_count']; // $return_arr["affected"]= $rows_affected_count; $return_arr["change_name"] = $gen_filename . "." . $file_extension; $return_arr["orig_name"] = $filename; $return_arr["message"] = " File Uploaded."; ///START GEN LOG $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $GLOBALS['data_count_list_v2'][] = array("Total Data", (string) ($GLOBALS['total_data_count'])); $GLOBALS['data_count_list_v2'][] = array("Inserted Data", (string) ($GLOBALS['inserted_data_count'])); $GLOBALS['data_count_list_v2'][] = array("Not Inserted Data", (string) ($GLOBALS['not_inserted_data_count'])); $data_count_list_v2_count = count($GLOBALS['data_count_list_v2']); $spreadsheet ->getActiveSheet() ->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_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_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/' . $filename); //END GEN LOG echo json_encode($return_arr); } //FUNCTIONS function getFieldFromExcel($row, $ColumnNumber, $array_data) { for ($i = 0; $i < $ColumnNumber; $i++) { if (in_array(trim($row[$i]), $array_data, true)) { return $row[$i]; } } return ""; } // function returnData($file_name){ // $return_arr["status"] = 1; // $return_arr["message"]=" Data Imported Successfully."; // $return_arr["data_count_list"] = $GLOBALS['data_count_list']; // $return_json = json_encode($return_arr); // echo $return_json; // } function updateCarClubFields($array_data, $db) { foreach ($array_data as $key => $value) { $query = "UPDATE `import_module_fields` SET `field`='$value' WHERE 1 AND id = $key"; $db->sql_query($query); } } function getArrayFields($id, $db) { $return_array = array(); $data_list = $db->sql_query("SELECT `field` FROM `import_module_fields` WHERE 1 AND id = '$id' "); foreach ($data_list as $row) { $return_array = array_merge($return_array, array($row['field'])); } return $return_array; } function getIssueSummaryField($str, $ColumnNumber, $array_data) { for ($i = 0; $i < $ColumnNumber; $i++) { if (in_array($str, $array_data, true)) { return $i; } } return "-5"; } function checkExist($row, $field, $utility) { if (!$utility->isNotEmpty($field)) { return ""; } $key = array_search($field, $GLOBALS['row_headers']); return isset($row[$key]) ? $row[$key] : ''; }

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