public function insertDataIntoRateChart() { if (empty($this->request->data['file_path']) || empty($this->request->data['broker']) || empty($this->request->data['gas_product'])) { return new CakeResponse(array('body' => json_encode(0))); } $data = new Spreadsheet_Excel_Reader("plupload/" . $this->request->data['file_path'], true); $temp = $data->dumptoarray(); $rowCount = count($temp); $head = $temp[1]; $finalData = array(); for ($i = 2; $i <= $rowCount; $i++) { $row = $temp[$i]; $data = array(); for ($j = 1; $j <= count($head); $j++) { $data[$head[$j]] = $row[$j]; } $finalData[] = $data; } $results = array(); foreach ($finalData as $r) { $r['broker_info_id'] = $this->request->data['broker']; $r['price_code'] = $r['PRICE CODE']; $consumption_range = $r['Consumption']; $c_array = explode("-", $consumption_range); $start_range = $this->tofloat($c_array[0]); $r['consumption_range_start'] = $start_range; if (isset($c_array[1])) { $end_range = $this->tofloat($c_array[1]); $r['consumption_range_end'] = $end_range; } else { $r['consumption_range_end'] = 10000000; } $r['region_code'] = $r['PES Region']; $r['standing_charge_per_year'] = $r['STANDING CHARGE p/year']; $r['standing_charge_per_day'] = $r['STANDING CHARGE p/day']; $r['unit_rate_per_kwh'] = $r['Unit Rate p/kwh']; $r['quote_gas_product_id'] = $this->request->data['gas_product']; $results[] = $r; } $flag = 0; $rateCharts = $this->QuoteGasRateChart->find('all', array('conditions' => array('quote_gas_product_id' => $this->request->data['gas_product'], 'broker_info_id' => $this->request->data['broker']))); foreach ($rateCharts as $chart) { $this->QuoteGasRateChart->id = $chart['QuoteGasRateChart']['id']; $this->QuoteGasRateChart->delete(); } if ($this->QuoteGasRateChart->saveAll($results)) { $flag = 1; $this->Session->setFlash('Uploaded Successfully', 'flash_success'); } else { $this->Session->setFlash('Error Occurred', 'flash_error'); } return new CakeResponse(array('body' => json_encode($flag))); }
public function recepcion() { if ($this->request->is('post')) { $data = new Spreadsheet_Excel_Reader($this->data['Data']['archivo']['tmp_name'], true); $temp = $data->dumptoarray(0); $datas = array(); $tlfs = array(); for ($i = 2; $i < count($temp); $i++) { $CedulaOrif = $temp[$i][2] . $temp[$i][3]; $Nombre = $temp[$i][4] . " " . $temp[$i][5] . " " . $temp[$i][6] . " " . $temp[$i][7]; $Sexo = $temp[$i][8]; $FechaRaw = strtotime($temp[$i][9]); $FechaNac = date('Y-m-d', $FechaRaw); $GrupoSocial = $temp[$i][10]; $Estado = $temp[$i][11]; $Direccion = $temp[$i][12]; $Email = $temp[$i][13]; $Fecha = date('d/m/Y'); //Fecha $j = 14; while ($temp[$i][$j] != ' ' && $j < 42) { $tlf = array("CedulaOrif" => $CedulaOrif, "Telefono" => $temp[$i][$j] . $temp[$i][$j + 1]); if (!empty($CedulaOrif)) { array_push($tlfs, $tlf); } $j += 2; } $data = array("CedulaOrif" => $CedulaOrif, "Nombre" => $Nombre, "Sexo" => $Sexo, "FechaNac" => $FechaNac, "GrupoSocial" => $GrupoSocial, "Estado" => $Estado, "Direccion" => $Direccion, "Email" => $Email, "Fecha" => $Fecha); if (!empty($CedulaOrif)) { array_push($datas, $data); } } unlink($this->data['Data']['archivo']['tmp_name']); $this->Data->create(); $this->Data->saveMany($datas); $this->DataTel->create(); $this->DataTel->saveMany($tlfs); // debug($tlfs); debug("Fin de la guardada"); } }
public function excelUploadStates() { //$this->layout = "defaultSearch"; if ($this->request->is('post')) { /* echo "<pre>"; print_r($this->request->data); exit; */ $excelData = $this->request->data['State']; if (!$excelData['excelFile']['error']) { $data = new Spreadsheet_Excel_Reader($excelData['excelFile']['tmp_name'], true); $temp = $data->dumptoarray(); //$this->log($temp, 'debug'); /* echo "<pre>"; print_r($temp); exit; */ foreach ($temp as $row) { if ($row['1'] == 'id') { continue; } /* echo "<pre>"; print_r($row); exit; */ $row['id'] = $row['1']; $row['state'] = $row['2']; $rowId = $this->State->field('id', array('State.state' => $row['state'])); if ($rowId) { $this->State->id = $rowId; $this->State->save($row); } else { $this->State->create(); $this->State->save($row); } } $this->Session->setFlash("Excel file uploaded Successfully"); $this->redirect($this->referer()); } } }
} if ($_POST) { $msg = ""; if ($_FILES["file"]["name"] == "") { $msg = "Please enter file Excel"; } else { $infofile = pathinfo($_FILES["file"]["name"]); if (strtolower($infofile["extension"]) != "xls" && strtolower($infofile["extension"]) != "xlsx") { $msg = 'Not allow extentions file, you can only upload file xlsx, xls'; } } if ($msg == "") { require_once _ROOT . 'libraries/excel/excel_reader2.php'; //$data = new Spreadsheet_Excel_Reader(_UPLOAD."excel/TPMG Inventory.xls"); $data = new Spreadsheet_Excel_Reader($_FILES["file"]["tmp_name"]); $arrData = $data->dumptoarray(0); if (!empty($arrData) && is_array($arrData)) { unset($arrData[1], $arrData[2], $arrData[3]); if (count($arrData) > 0) { foreach ($arrData as $val) { switch (trim($val[7])) { case "Giải nhất": $val[7] = 1; break; case "Giải II": $val[7] = 2; break; case "Giải III": $val[7] = 3; break; default:
function import_partner() { if (!empty($this->data)) { if ($this->{$this->modelClass}->ExcelValidate()) { $data = $this->data; $user_id = $this->Auth->user('id'); $excel_data = new Spreadsheet_Excel_Reader($data[$this->modelClass]['user_image']['tmp_name'], true); $this->data = array(); $result = array(); $partners = $excel_data->dumptoarray(); $partner_data_array = array('name', 'email', 'password', 'country', 'zipcode', 'county', 'district', 'city', 'telephone', 'mobile', 'address', 'observations', 'site_url', 'nif_nipc', 'facebook_link', 'google_link', 'twitter', 'linkedIn', 'legal_form', 'social_capital', 'activities_description', 'social_designation', 'creation_date'); array_shift($partners); $this->Partner->data[$this->modelClass] = array(); foreach ($partners as $user_detials) { $check_data = array_filter($user_detials); if (!empty($check_data)) { $this->loadModel('County'); $this->loadModel('District'); $partner_details[$this->modelClass] = array_combine($partner_data_array, $user_detials); $this->{$this->modelClass}->set($partner_details); $userName = $partner_details['Partner']['name']; $this->{$this->modelClass}->set($partner_details); if ($this->{$this->modelClass}->PotentialValidate()) { // pr($partner_details); $partner_details['Partner']['address'] = html_entity_decode(utf8_decode($partner_details['Partner']['address'])); // pr($partner_details);die; $finddata = $this->{$this->modelClass}->find('first', array('conditions' => array('email' => $partner_details['Partner']['email']))); $county_id = $this->County->find('first', array('conditions' => array('name' => trim($partner_details['Partner']['county'])))); $district_id = $this->District->find('first', array('conditions' => array('name' => trim($partner_details['Partner']['district'])))); if (!empty($county_id)) { $partner_details['Partner']['county'] = $county_id['County']['id']; } else { $partner_details['Partner']['county'] = 1; } if (!empty($district_id)) { $partner_details['Partner']['district'] = $district_id['District']['id']; } else { $partner_details['Partner']['district'] = 1; } // pr($partner_details); die; if (empty($finddata)) { $data = $partner_details; $data[$this->modelClass]['user_role_id'] = Configure::read('user_roles.partner'); $data[$this->modelClass]['is_approved'] = 1; $data[$this->modelClass]['is_potential'] = 1; $data[$this->modelClass]['verified_potential'] = 1; $data[$this->modelClass]['promoter_id'] = $user_id; $data[$this->modelClass]['username'] = $partner_details[$this->modelClass]['name']; $data[$this->modelClass]['password'] = $this->Auth->password($partner_details[$this->modelClass]['password']); $partner_details = $data; $this->{$this->modelClass}->create(); if ($this->{$this->modelClass}->save($partner_details, array('validate' => false))) { //pr($this->data); //pr($partner_details); die; $userId = $this->{$this->modelClass}->id; $partnerdata['Comercial'] = $partner_details['Partner']; //pr($partnerdata); die; $this->{$this->modelClass}->UserDetail->savePotentialPartnerAdmin($userId, $partnerdata); $this->Session->setFlash(__('Potential Partner record has been added.'), 'success'); $this->render('import_partners_from_excel'); } } else { $validationserrors = array('0' => 'This Email already exist'); if ($userName != '') { $result['notadded'][$userName] = $validationserrors; } } } else { $validationserrors = array_values($this->{$this->modelClass}->validationErrors); $validationserrora = $validationserrors; $result['notadded'][$userName] = $validationserrora; } } } // pr($result); die; $this->set('result', $result); $this->set('pageHeading', "Partner Import Messages"); $this->render('import_partners_from_excel'); } } }
$extra = 1; if (($handle = fopen($filename, "r")) !== FALSE) { while (($line = fgetcsv($handle)) !== FALSE) { $arr[] = $line; } fclose($handle); } break; case "xls": $start = 1; $extra = 0; require_once 'excel_reader2.php'; /* conserve memory for large worksheets by not storing the extended */ /* information about cells like fonts, colors, etc. */ $data = new Spreadsheet_Excel_Reader($filename, false); $arr = $data->dumptoarray(0); break; } //$max = sizeof($arr); $max = 5; $result = mysqli_query($connection, "SHOW COLUMNS FROM customers"); $fields_to_ignore[] = "id"; $fields_to_ignore[] = "new"; $fields_to_ignore[] = "last_updated"; $fields_to_ignore[] = "locked_by"; $fields_to_ignore[] = "datetime_locked"; $fields_to_ignore[] = "cleaned_number"; $fields_to_ignore[] = "list_id"; if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { if (!in_array($row['Field'], $fields_to_ignore)) {
function parse_er($file) { $er = Common::inc_module_factory('ExcelReader', true); $data = new Spreadsheet_Excel_Reader($file); return $data->dumptoarray(); }
public function recepcion() { $bancosRaw = $this->Cliente->find('all'); $bancos = array(); //Solo para que se vea mas pretty $bancos = $this->Cliente->find('list', array('fields' => array('rif', 'nombre'))); $this->set('bancos', $bancos); if (!empty($this->data)) { $empresaId = $this->data['Pago']['banco']; if ($this->data['Pago']['banco'] == 7) { $nombrearchivo = WWW_ROOT . str_replace("/", DS, 'files/') . $this->data['Pagos']['archivo']['name']; /* copiamos el archivo*/ if (move_uploaded_file($this->data['Pagos']['archivo']['tmp_name'], $nombrearchivo)) { $data = new Spreadsheet_Excel_Reader($nombrearchivo, true); $temp = $data->dumptoarray(1); $pagos = array(); for ($i = 2; $i < count($temp); $i++) { $fechaRaw = strtotime($temp[$i][11]); $FechaPago = date('Y-m-d', $fechaRaw); $CedulaOrif = $temp[$i][1]; $Producto = $temp[$i][8]; //Para que se vea del tipo TdC - $MontoPago = str_replace(',', '', $temp[$i][12]); //Para retirar las comas de los numero 1,000.00 $status = 'no-confirmado'; //Se guardara asi siempre y cuano no se consida el registro en clien_pagos $pagoPrevio = $this->ClienPago->find('first', array('conditions' => array('ClienPago.FECH_PAGO' => $FechaPago, 'ClienPago.COD_PROD LIKE' => $Producto . '%', 'ClienPago.TOTAL_PAGO' => $MontoPago))); if (count($pagoPrevio) > 0) { $status = 'confirmado'; $DiasMora = $temp[$i][20]; $update = array('ClienPago' => array('unique_id' => $pagoPrevio['ClienPago']['unique_id'], 'diasMora' => $DiasMora, 'EST_PAGO' => 'confirmado')); $this->ClienPago->save($update); } $pago = array('RIF_EMP' => $empresaId, 'Fecha' => date('Y/m/d'), 'CedulaOrif' => $CedulaOrif, 'Nombre' => $temp[$i][2], 'Contrato' => $temp[$i][3], 'Cuenta' => $temp[$i][34], 'Producto' => $temp[$i][9], 'FechaPago' => $FechaPago, 'MontoPago' => $MontoPago, 'DiasMora' => $temp[$i][20], 'status' => $status); array_push($pagos, $pago); } $this->Pago->create(); $this->Pago->saveMany($pagos); } } else { //Banco Bicentenario $pagos = array(); $nombrearchivo = WWW_ROOT . str_replace("/", DS, 'files/') . $this->data['Pagos']['archivo']['name']; /* copiamos el archivo*/ if (move_uploaded_file($this->data['Pagos']['archivo']['tmp_name'], $nombrearchivo)) { $hoy = date('Y-m-d'); $objPHPExcel = PHPExcel_IOFactory::load($nombrearchivo); $count = 0; foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { if ($count == 0 || $count == 1) { //:a pestaña que tiene los datos que me interesan foreach ($worksheet->getRowIterator() as $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(FALSE); // Loop all cells, even if it is not set if ($row->getRowIndex() > 1) { $i = 0; foreach ($cellIterator as $cell) { if ($i == 8) { $fechaRaw = $cell->getCalculatedValue(); $timestamp = PHPExcel_Shared_Date::ExcelToPHP($fechaRaw); $FechaPago = date("Y-m-d", $timestamp); } elseif ($i == 1) { $CedulaOrif = $cell->getCalculatedValue(); } elseif ($i == 5) { $Producto = $cell->getCalculatedValue(); } elseif ($i == 9) { $MontoPago = $cell->getCalculatedValue(); } elseif ($i == 3) { $nombre = $cell->getCalculatedValue(); } elseif ($i == 4) { $contrato = $cell->getCalculatedValue(); $cuenta = $cell->getCalculatedValue(); } elseif ($i == 14) { $DiasMora = $cell->getCalculatedValue(); } $i++; } $status = 'no-confirmado'; $pagoPrevio = $this->ClienPago->find('first', array('conditions' => array('ClienPago.FECH_PAGO' => $FechaPago, 'ClienPago.PRODUCTO LIKE' => '%' . $Producto . '%', 'ClienPago.TOTAL_PAGO' => $MontoPago))); if (count($pagoPrevio) > 0) { $status = 'confirmado'; if (empty($DiasMora)) { $DiasMora = '2500'; } $update = array('ClienPago' => array('unique_id' => $pagoPrevio['ClienPago']['unique_id'], 'diasMora' => $DiasMora, 'EST_PAGO' => 'confirmado')); $this->ClienPago->save($update); } if (!empty($CedulaOrif)) { $pago = array('RIF_EMP' => $empresaId, 'Fecha' => date('Y-m-d'), 'CedulaOrif' => $CedulaOrif, 'Nombre' => $nombre, 'Contrato' => $contrato, 'Cuenta' => $cuenta, 'Producto' => $Producto, 'FechaPago' => $FechaPago, 'MontoPago' => $MontoPago, 'DiasMora' => 2500, 'status' => $status); array_push($pagos, $pago); } } } $this->Pago->create(); $this->Pago->saveMany($pagos); } $count++; } } } } }