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++;
}
}
}
}
}
