public function fileupload($file_name) { // $file_name = $result['filename']; // for header columns checking $headers_array = array('1' => array('0' => 'Vendor(FILTER)', '1' => 'Plant', '2' => 'Products(FILTER)', '3' => 'Capabilities(FILTER)', '4' => 'Equipment(FILTER)', '5' => 'Street', '6' => 'City', '7' => 'State', '8' => 'Country', '9' => 'Zipcode'), '2' => array('0' => 'Client(FILTER)', '1' => 'Division(FILTER)', '2' => 'LocationType(FILTER)', '3' => 'Street1', '4' => 'City', '5' => 'State', '6' => 'Country', '7' => 'Zipcode'), '3' => array('0' => 'Distributor(FILTER)', '1' => 'PlantType(FILTER)', '2' => 'Plant', '3' => 'PlantID', '4' => 'DistributionType(FILTER)', '5' => 'Performance(DynamicMapPinColor)', '6' => 'Street', '7' => 'City', '8' => 'State', '9' => 'Country', '10' => 'Zipcode')); if (file_exists($file_name)) { require_once APPPATH . 'third_party/upload.php'; $xlsx = new SimpleXLSX($file_name); $err_flag = false; if (count($xlsx->sheets) > 2) { // checking number of sheets $excel_data = array(); for ($i = 1; $i < 4; $i++) { $excel_data[$i] = $xlsx->rows($i); } foreach ($excel_data as $s => $sheet) { // looping sheets if (count($sheet[0])) { foreach ($sheet[0] as $k => $v) { $vv = preg_replace('/\\s+/', '', $v); if ($headers_array[$s][$k] != $vv) { // checking file headers $err_flag = true; } } } } } else { $err_flag = true; } if ($err_flag) { // return error if not match echo json_encode(array('fail' => 'Columns Headers not matched')); die; } else { set_time_limit(900); // store file detail in log table $log_sql = 'insert into log_files(`file_name`,`temp_name`,`date_uploaded`,`ipaddress`) values ("' . $_REQUEST['filename'] . '","' . $file_name . '","' . gmdate('Y-m-d H:i:s') . '","' . $_SERVER['REMOTE_ADDR'] . '")'; $this->db->query($log_sql); // delete existing data $arr_del_sql = array(); array_push($arr_del_sql, 'SET FOREIGN_KEY_CHECKS = 0;'); array_push($arr_del_sql, 'truncate user_capabilities;'); array_push($arr_del_sql, 'truncate user_distribution_types;'); array_push($arr_del_sql, 'truncate user_equipments;'); array_push($arr_del_sql, 'truncate user_products;'); array_push($arr_del_sql, 'truncate user_details;'); array_push($arr_del_sql, 'truncate users;'); array_push($arr_del_sql, 'truncate capabilities;'); array_push($arr_del_sql, 'truncate distribution_types;'); array_push($arr_del_sql, 'truncate division;'); array_push($arr_del_sql, 'truncate equipments;'); array_push($arr_del_sql, 'truncate location_type;'); array_push($arr_del_sql, 'truncate plant_type;'); array_push($arr_del_sql, 'truncate products;'); foreach ($arr_del_sql as $del_sql) { $this->db->query($del_sql); } // store new excel data // table names $tables = array('Capabilities' => 'capabilities', 'Vendor' => 'users', 'Client' => 'users', 'Distributor' => 'users', 'Products' => 'products', 'Equipment' => 'equipments', 'Division' => 'division', 'LocationType' => 'location_type', 'PlantType' => 'plant_type', 'DistributionType' => 'distribution_types'); $bridge_tables = array('Capabilities' => 'user_capabilities', 'Products' => 'user_products', 'Equipment' => 'user_equipments', 'DistributionType' => 'user_distribution_types'); // reading excell $excel_data = array(); for ($s = 1; $s < 4; $s++) { // extract values from excel $excel_data[$s] = $xlsx->rows($s); } $filter_index = array(); $headings = array(); $user_details_vals = ''; // framing inser values $master_array = array(); foreach ($excel_data as $s => $sheet) { // looping sheets $row_cnt = count($sheet); foreach ($sheet[0] as $k => $v) { // identifiyin filter columns if (strpos($v, '(FILTER)')) { $filter_index[$s][$k] = trim(str_replace('(FILTER)', '', $v)); $flt = preg_replace('/\\s+/', '', $filter_index[$s][$k]); // select master data $qry = 'select id,name from ' . $tables[$flt]; $ds = $this->db->query($qry); // while ($row = mysql_fetch_array($ds)) { foreach ($ds->result_array() as $row) { $master_array[$s][$k][$row['id']] = $row['name']; } } $txt = preg_replace('/\\s+/', '', str_replace('(FILTER)', '', $v)); //remove spaces $headings[$s][$k] = $txt; } for ($r = 1; $r < $row_cnt; $r++) { //looping records in each sheet $record_data = array(); foreach ($sheet[$r] as $k => $val) { // insert values into master tables if (!empty($filter_index[$s][$k])) { $name = preg_replace('/\\s+/', '', trim($filter_index[$s][$k])); //remove spaces if ($k == 0) { $val = trim($val); if ($val != '' && (!isset($master_array[$s][$k]) || !in_array($val, $master_array[$s][$k]))) { //if(!isset($master_array[$s][$k]) || !in_array($val,$master_array[$s][$k])){ //$val = ($val==''?'Blank':$val); $ins = 'INSERT INTO ' . $tables[$name] . ' (`name`,`ipaddress`) VALUES ("' . addslashes($val) . '","' . $_SERVER['REMOTE_ADDR'] . '")'; $res = $this->db->query($ins); $insert_id = $this->db->insert_id(); $master_array[$s][$k][$insert_id] = $val; } } else { $arrvals = explode(', ', $val); foreach ($arrvals as $v) { $v = trim($v); if (!isset($master_array[$s][$k]) || !in_array($v, $master_array[$s][$k])) { if ($v != '') { //$v = ($v==''?'Blank':$v); $ins = 'INSERT INTO ' . $tables[$name] . ' (`name`,`ipaddress`) VALUES ("' . addslashes($v) . '","' . $_SERVER['REMOTE_ADDR'] . '")'; $res = $this->db->query($ins); $insert_id = $this->db->insert_id(); $master_array[$s][$k][$insert_id] = $v; } } } } $record_data[$s][$k] = !empty($master_array[$s][$k]) ? array_search(trim($val), $master_array[$s][$k]) : ''; } } // insert values into main details table $street_val = ''; if (array_search('Street', $headings[$s])) { $street_val = $sheet[$r][array_search('Street', $headings[$s])]; } if (array_search('Street1', $headings[$s])) { $street_val = $sheet[$r][array_search('Street1', $headings[$s])]; } $zip_code = ''; if (array_search('Zipcode', $headings[$s])) { $zip_code = preg_replace('/\\s+/', '', trim($sheet[$r][array_search('Zipcode', $headings[$s])])); // remove space in zipcode if (is_numeric($zip_code)) { if (strlen($zip_code) == 4) { $zip_code = '0' . $zip_code; } else { if (strlen($zip_code) == 3) { $zip_code = '00' . $zip_code; } } } else { if (strpos($zip_code, '-')) { // consider first value if two zip codes are given $zip = explode('-', $zip_code); $zip_code = $zip[0]; } } } if ($record_data[$s][0] != '') { $ins = 'INSERT INTO user_details (user_type_id,users_id,street,city,state,country,zip_code,plant,plant_id,division_id,location_type_id,plant_type_id,performance,ipaddress) VALUES(' . $s . ',' . $record_data[$s][0] . ',' . '"' . $xlsx->addString($street_val) . '",' . '"' . (array_search('City', $headings[$s]) ? $sheet[$r][array_search('City', $headings[$s])] : '""') . '",' . '"' . (array_search('State', $headings[$s]) ? $sheet[$r][array_search('State', $headings[$s])] : '""') . '",' . '"' . (array_search('Country', $headings[$s]) ? $sheet[$r][array_search('Country', $headings[$s])] : '""') . '",' . '"' . (array_search('Zipcode', $headings[$s]) ? $zip_code : '""') . '",' . '"' . (array_search('Plant', $headings[$s]) ? $sheet[$r][array_search('Plant', $headings[$s])] : '') . '",' . '"' . (array_search('PlantID', $headings[$s]) ? $sheet[$r][array_search('PlantID', $headings[$s])] : '') . '",' . (array_search('Division', $headings[$s]) && $record_data[$s][array_search('Division', $headings[$s])] != '' ? $record_data[$s][array_search('Division', $headings[$s])] : 'null') . ',' . (array_search('LocationType', $headings[$s]) && $record_data[$s][array_search('LocationType', $headings[$s])] != '' ? $record_data[$s][array_search('LocationType', $headings[$s])] : 'null') . ',' . (array_search('PlantType', $headings[$s]) && $record_data[$s][array_search('PlantType', $headings[$s])] != '' ? $record_data[$s][array_search('PlantType', $headings[$s])] : 'null') . ',' . '"' . (array_search('Performance(DynamicMapPinColor)', $headings[$s]) ? $sheet[$r][array_search('Performance(DynamicMapPinColor)', $headings[$s])] : '') . '","' . $_SERVER['REMOTE_ADDR'] . '"' . ');'; $res = $this->db->query($ins); $row_id = $this->db->insert_id(); // insert values into bridge tables if ($row_id) { foreach ($filter_index[$s] as $k => $v) { if (trim($sheet[$r][$k])) { $name = preg_replace('/\\s+/', '', trim($filter_index[$s][$k])); //remove spaces if (isset($bridge_tables[$name])) { $ch_qry = ''; $arrvals = explode(', ', $sheet[$r][$k]); foreach ($arrvals as $v) { if (trim($v) != '') { $ch_qry .= '(' . $row_id . ',' . array_search(trim($v), $master_array[$s][$k]) . '),'; } } $qry = 'insert into ' . $bridge_tables[$name] . ' values ' . trim($ch_qry, ',') . ';'; $this->db->query($qry); } } } } } } } // update latitude and longitude values from available zipcodes //Commented to on 22 MAY 2014 /* $latlong_sql = 'UPDATE user_details AS d SET d.longitude= (SELECT u.Longitude FROM usa_zips u WHERE u.ZIPCode=d.zip_code LIMIT 1), d.latitude=(SELECT u.Latitude FROM usa_zips u WHERE u.ZIPCode=d.zip_code LIMIT 1);'; $ds = $this->db->query($latlong_sql);*/ // update latitude and longitude values from available address library $qry = 'SELECT id,street,city,state,country,zip_code FROM user_details WHERE is_updated="0"'; $ds = $this->db->query($qry); foreach ($ds->result_array() as $row) { $addr = ''; if (trim($row['street']) != '') { $addr .= $row['street']; if ($row['street'] != '') { $addr .= ' ' . $row['city']; if ($row['state'] != '') { $addr .= ' ' . $row['state']; if ($row['state'] != $row['country'] && $row['country'] != '') { $addr .= ' ' . $row['country']; } } } } // check in address library $chk_sql = 'SELECT latitude,longitude FROM address_lib WHERE street="' . $row['street'] . '" AND city="' . $row['city'] . '" AND state="' . $row['state'] . '" AND country="' . $row['country'] . '";'; $chk_ds = mysql_query($chk_sql); $rec = mysql_fetch_row($chk_ds); if (!empty($rec)) { // if exist, update to user details table $latlong_sql = 'UPDATE user_details SET longitude= "' . $rec[1] . '",latitude="' . $rec[0] . '",is_updated="1" where id=' . $row['id']; $res = mysql_query($latlong_sql); } else { $this->getgooglelatlang($row['id']); } } // fetching errored records $sql = 'SELECT ud.id,ud.user_type_id,ud.street,ud.city,ud.state,ud.country,ud.zip_code,ud.latitude,ud.longitude,u.name FROM user_details ud LEFT JOIN users u ON ud.users_id=u.id WHERE latitude IS NULL'; $res = $this->db->query($sql); $err = array(); foreach ($res->result_array() as $row) { $err[$row['user_type_id']][] = array('id' => $row['id'], 'street' => $row['street'], 'city' => $row['city'], 'user_type_id' => $row['user_type_id'], 'name' => $row['name'], 'country' => $row['country'], 'zip_code' => $row['zip_code'], 'latitude' => $row['latitude'], 'longitude' => $row['longitude']); } $sql = 'SELECT COUNT(ud.id) as cnt,ud.user_type_id FROM user_details ud WHERE ud.latitude IS NOT NULL GROUP BY ud.user_type_id'; $res = $this->db->query($sql); $succ_arr = array(); foreach ($res->result_array() as $row) { $succ_arr[$row['user_type_id']] = $row['cnt']; } $rs = array('success' => true, 'err_data' => $err, 'succ_data' => $succ_arr); echo json_encode($rs); } } else { echo json_encode(array('fail' => 'unable to get file')); } }