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'));
}
}
