public function show($referen)
{
$empresa = Session::get('empresa');
Checklist::where('chk_referen', $referen)->delete();
$documentos = \DB::connection('master')->table('mdb_tipodocum')->select('doc_clave', 'doc_nombre')->get();
foreach ($documentos as $doc) {
$result = Result::where('res_referen', $referen)->first();
if (!empty($result)) {
$validacion = Validation::where('attribute_id', 9)->where('id', $result->validations_id)->whereRaw('SUBSTRING_INDEX(val_data, "|", 1) = ' . $doc->doc_clave)->count();
if ($validacion > 0) {
$imagen = \DB::connection('users')->table('opauimg')->where('pk_referencia', $referen)->where('imgtipodoc', $doc->doc_clave)->count();
if ($imagen > 0) {
$check = 1;
} else {
$check = 0;
}
} else {
$check = 2;
}
} else {
$check = 0;
}
$data = ["chk_referen" => $referen, "chk_document" => $doc->doc_nombre, "chk_status" => $check, "chk_company" => $empresa];
Checklist::create($data);
}
$result = Checklist::where('chk_company', $empresa)->get();
return view('list_document')->with(['document' => $result, 'referen' => $referen]);
}
public function subirArchivo(Request $request)
{
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$columns = '(telefono, periodo, valor)';
$pdo->exec('SET foreign_key_checks = 0');
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " INTO TABLE `comisiones`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns . "\n SET ID = NULL");
$huerfanas = \DB::select("select distinct(comisiones.telefono) from simcards RIGHT join comisiones on simcards.numero = comisiones.telefono where simcards.numero is null");
$ICC = \DB::table('simcards')->select('ICC')->orderBy(\DB::raw('ICC*1'))->first();
$ICC = $ICC->ICC;
$fecha_vencimiento = date_add(new \DateTime(), date_interval_create_from_date_string("6 months"));
foreach ($huerfanas as $huerfana) {
$ICC = $ICC - 1;
try {
\App\Simcard::create(['numero' => $huerfana->telefono, 'ICC' => $ICC, 'fecha_vencimiento' => $fecha_vencimiento, 'fecha_activacion' => null, 'nombreSubdistribuidor' => 'SIN ASIGNAR', 'tipo' => 1, 'paquete' => 0, 'fecha_entrega' => null]);
} catch (Exception $e) {
}
}
$pdo->exec('SET foreign_key_checks = 1');
return \Redirect::route('finanzas')->with('result', $affectedRows);
}
public function index(Request $request)
{
$claves = ['' => ''] + \DB::connection('master')->table('mdb_cpedimen')->lists('cpe_clave', 'cpe_clave');
$fechas = \DB::connection('users')->table('optr08')->lists('pk_periodo', 'pk_periodo');
$auditoria = \DB::connection('users')->table('optr01')->join('optr03', 'optr01.pk_referencia', '=', 'optr03.pk_referencia')->where('ref_tipodoc', 'P')->where('sec_status', '!=', 1)->where('pk_aduana', 'LIKE', '%' . $request->aduana . '%')->where('pk_patente', 'LIKE', '%' . $request->patente . '%')->where('pk_pedimento', 'LIKE', '%' . $request->pedimento . '%')->where('ref_clave', 'LIKE', '%' . $request->clave . '%')->where('ref_tipo', $request->operacion)->whereRaw('YEAR(ref_fechapago) LIKE "%' . $request->anio . '%"')->whereRaw('MONTH(ref_fechapago) LIKE "%' . $request->mes . '%"')->orderby('ref_fechapago')->paginate(10);
$auditoria->setPath('administracion');
return view('administration.index')->with(['claves' => $claves, 'fechas' => $fechas, 'auditoria' => $auditoria]);
}
public function diplomado()
{
$certificados = \DB::connection('mysql')->select('call certificados(176);');
if (count($certificados) > 0) {
$certificado = $certificados[0];
return $this->certificado(['logo_izq' => $certificado->logotipo_sponsor, 'logo_der' => $certificado->logo_tipo_certificado, 'copy1' => 'certifican que:', 'copy2' => $certificado->nombre, 'copy3' => 'C.C ' . $certificado->idnumber, 'copy4' => 'Asistio y Aprobo el programa', 'copy5' => $certificado->servicio, 'copy6' => 'Con una intensidad horaria de 48 horas', 'copy7' => 'El contenido del programa comprendio:', 'copy8' => explode(",", $certificado->modulos), 'copy9' => $certificado->sucursal . ' ' . $certificado->fecha_final, 'copy10' => $certificado->firma_convenio]);
}
return "No puede generar este certificado";
}
public function search(Request $request)
{
// Gets the query string from our form submission
$query = Request::input('search');
// Returns an array of articles that have the query string located somewhere within
// our articles titles. Paginates them so we can break up lots of search results.
//$articles = DB::table('articles')->where('title', 'LIKE', '%' . $query . '%')->paginate(10);
//$articles = DB::table('articles')->where('title', 'LIKE', '%' . $query . '%')->paginate(10);
$articles = DB::connection('sqlsrv2')->select(DB::raw("SELECT [CNF_BlueBox].INTKEY,[CNF_BlueBox].IntKeyPO,[CNF_BlueBox].BlueBoxNum,[CNF_BlueBox].BoxQuant, [CNF_PO].POnum,[CNF_SKU].Variant,[CNF_SKU].ClrDesc,[CNF_STYLE].StyCod FROM [BdkCLZGtest].[dbo].[CNF_BlueBox] FULL outer join [BdkCLZGtest].[dbo].CNF_PO on [CNF_PO].INTKEY = [CNF_BlueBox].IntKeyPO FULL outer join [BdkCLZGtest].[dbo].[CNF_SKU] on [CNF_SKU].INTKEY = [CNF_PO].SKUKEY FULL outer join [BdkCLZGtest].[dbo].[CNF_STYLE] on [CNF_STYLE].INTKEY = [CNF_SKU].STYKEY WHERE [CNF_BlueBox].INTKEY = :somevariable"), array('somevariable' => $query));
// returns a view and passes the view the list of articles and the original query.
return view('page.search', compact('articles', 'query'));
}
public function update(ListRequest $request, $referen)
{
$empresa = Session::get('empresa');
$input = Input::all();
$pedimento = \DB::connection('users')->table('optr01')->select('ref_transport1', 'ref_tipo')->where('pk_referencia', $referen)->first();
$result = Checklist::where('chk_opera', $pedimento->ref_tipo)->where('chk_transport', $pedimento->ref_transport1)->get();
foreach ($result as $res) {
$doc = $input['id_' . $res->id];
$digital = 0;
$fisico = 0;
if (isset($input['exp_digital_' . $res->id])) {
$digital = $input['exp_digital_' . $res->id];
}
if (isset($input['exp_material_' . $res->id])) {
$fisico = $input['exp_material_' . $res->id];
}
Record::updateOrCreate(['exp_referen' => $referen, 'exp_document' => $doc], ['exp_material' => $fisico, 'exp_company' => $empresa, 'exp_digital' => $digital]);
//$id = 'chk'.$request->chk_id;
}
return redirect()->back();
}
public function manager(Request $request, $id)
{
$dados = $request->all();
return \DB::connection('mysql')->table('feira')->where('id', $id)->update(['status' => $dados['status']]);
}
public function getDocument()
{
$documento = \DB::connection('master')->table('mdb_tipodocum')->select('doc_clave', 'doc_nombre')->get();
return \Response::json(['documento' => $documento]);
}
public function testConnection()
{
if (\DB::connection()->getDatabaseName()) {
echo "Connected sucessfully to database \n" . \DB::connection()->getDatabaseName() . ".";
}
}
public function postPlanificacion()
{
$input = Request::input('mantt_ejec');
//$date = date_create_from_format('d/m/y', '27/05/1990');
$date = \DateTime::createFromFormat('Y-m-d', $input);
$fechaformato = $date->format('Y-m-d');
$activos = \DB::table('ACTIVOS')->where('n_equipo', '=', Request::input('cod_equipo'))->get();
$id_activo = \DB::table('ACTIVOS')->where('n_equipo', '=', Request::input('cod_equipo'))->pluck('id_activo');
$tipo = \DB::table('ACTIVOS')->where('n_equipo', '=', Request::input('cod_equipo'))->pluck('tipo');
$nequipo = \DB::table('ACTIVOS')->where('n_equipo', '=', Request::input('cod_equipo'))->pluck('n_equipo');
$resultado = \DB::table('PLAN_MTTO')->where('id_activo', '=', $id_activo)->orderBy('correlativo_mtto', 'desc')->first();
$clasificacion = \DB::table('ACTIVOS')->where('n_equipo', '=', Request::input('cod_equipo'))->pluck('clasificacion');
$correlativo = $resultado->correlativo_mtto + 1;
$datos = array('id_mtto' => (int) Request::input('id_mtto'), 'desc_mantt' => Request::input('desc_mantt'), 'tipomantt' => (int) Request::input('tipomantt'), 'id_grupo' => (int) Request::input('id_grupo'), 'frecuencia' => Request::input('frecuencia'), 'nequipo' => $nequipo, 'tipo' => (int) $tipo, 'correlativo' => $correlativo, 'fecha' => $fechaformato);
if ($clasificacion != 1) {
$sql = "begin add_plan_mtto(:v_id_activo, :v_tipo, :v_desc_mantt, :v_tipo_mantt, :v_grupo_trabajo, :v_frecuencia, :v_mantt_ejec, :v_mantt_prog, :v_n_equipo, :v_corr_mtto, :v_oper_mtto); end;";
$pdo = \DB::connection()->getPdo();
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':v_id_activo', $id_activo);
$stmt->bindParam(':v_tipo', $datos['tipo']);
$stmt->bindParam(':v_desc_mantt', $datos['desc_mantt']);
$stmt->bindParam(':v_tipo_mantt', $datos['tipomantt']);
$stmt->bindParam(':v_grupo_trabajo', $datos['id_grupo']);
$stmt->bindParam(':v_frecuencia', $datos['frecuencia']);
$stmt->bindParam(':v_mantt_ejec', $datos['fecha']);
$stmt->bindParam(':v_mantt_prog', $datos['fecha']);
$stmt->bindParam(':v_n_equipo', ${$datos}['nequipo']);
$stmt->bindParam(':v_corr_mtto', $correlativo);
$stmt->bindParam(':v_oper_mtto', $datos['id_mtto']);
$stmt->execute();
//dd("pasa");
//obtngo todos los datos del ultimo creado en plan_mtto
$resultado2 = \DB::table('PLAN_MTTO')->orderBy('ID_PLAN_MTTO', 'desc')->first();
// $sigla=Plan_Grupo_Trabajo::where('ID_GRUPO',Request::input('id_grupo'))->get(['sigla']);
$sigla = \DB::table('PLAN_GRUPO_TRABAJO')->where('id_grupo', '=', $resultado2->id_grupo)->pluck('sigla');
// dd($sigla);
// dd($sigla);
$users = \DB::table('PROCED_BASE')->where('id_grupo_trabajo', '=', $resultado2->id_grupo)->orderBy('seq_doc', 'desc')->first();
//incremento el numero correlativo
$correlativo = $users->seq_doc + 1;
$datos2 = array('sigla' => $sigla, 'correlativo' => $correlativo);
// dd($datos);
$sql = "begin add_proced(:v_name_proced, :v_grupo_trabajo, :v_sigla_grupo, :v_next_doc, :v_id_plan_mtto); end;";
$pdo = \DB::connection()->getPdo();
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':v_name_proced', $datos['desc_mantt']);
$stmt->bindParam(':v_grupo_trabajo', $datos['id_grupo']);
$stmt->bindParam(':v_sigla_grupo', $datos2['sigla']);
$stmt->bindParam(':v_next_doc', $datos2['correlativo']);
$stmt->bindParam(':v_id_plan_mtto', $resultado2->id_plan_mtto);
$stmt->execute();
$ultimo = \DB::table('PROCED_BASE')->orderBy('ID_PROCEDIMIENTO', 'desc')->first();
return redirect()->route('procedimientos.prueba', [$ultimo->id_procedimiento]);
}
dd($resultado2);
}
public function postEditActividad()
{
if (Request::ajax()) {
$contador = \DB::table('proced_actividad')->join('proced_base', 'proced_actividad.id_procedimiento', '=', 'proced_base.id_procedimiento')->where('proced_actividad.id_procedimiento', '=', Request::input('id_procedimiento'))->count();
$contador = $contador + 1;
$datos = array('id_proc' => (int) Request::input('id_procedimiento'), 'item' => $contador, 'actividad' => Request::input('actividad'), 'detalle' => Request::input('detalle'));
// $ej=Request::input('name_proced');
$sql = "begin add_actividad(:v_name_actividad, :v_item, :v_id_procedimiento, :v_detalle); end;";
$pdo = \DB::connection()->getPdo();
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':v_name_actividad', $datos['actividad']);
$stmt->bindParam(':v_item', $datos['item']);
$stmt->bindParam(':v_id_procedimiento', $datos['id_proc']);
$stmt->bindParam(':v_detalle', $datos['detalle']);
$stmt->execute();
$ultimo = \DB::table('PROCED_ACTIVIDAD')->orderBy('ID_ACTIVIDAD', 'desc')->first();
//añade una nueva clave valor al arreglo asociativo
$datos['id'] = $ultimo->id_actividad;
// $last=\DB::connection()->getPdo()->lastInsertId();
/*
$Asignaturas= \DB::table('salas')
->where('campus_id',$ej)
->get();
//no se porque da error usando eloquent
// $Asignaturas=Asignaturas::where('departamento_id',$ej)->get());
*/
return response()->json($datos);
}
}
public function subirArchivo(Request $request)
{
$action = $request['accion'];
if ($action == "ADD") {
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$columns = '(numero,ICC,fecha_vencimiento,tipo,nombreSubdistribuidor)';
$pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " IGNORE INTO TABLE `simcards`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("delete from simcards_temp");
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `simcards_temp`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("UPDATE simcards_temp SET nombreSubdistribuidor = REPLACE(REPLACE(nombreSubdistribuidor, '\r', ''), '\n', '');");
$pdo->exec("update simcards inner join simcards_temp on simcards.numero = simcards_temp.numero set simcards.fecha_vencimiento=simcards_temp.fecha_vencimiento,simcards.nombreSubdistribuidor = simcards_temp.nombreSubdistribuidor, simcards.tipo = simcards_temp.tipo, simcards.fecha_activacion = null");
return \Redirect::route('simcard')->with('result', $affectedRows);
} else {
if ($action == "UPLOAD") {
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$pdo->exec("delete from simcards_temp");
$columns = '(ICC,numero, fecha_activacion)';
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `simcards_temp`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("update simcards inner join simcards_temp on simcards.numero = simcards_temp.numero set simcards.fecha_activacion=simcards_temp.fecha_activacion ");
$pdo->exec("UPDATE simcards SET fecha_vencimiento = DATE_ADD(fecha_activacion, INTERVAL 6 MONTH) where fecha_activacion is not null and tipo = 1");
$pdo->exec("UPDATE simcards SET fecha_vencimiento = DATE_ADD(fecha_activacion, INTERVAL 1 YEAR) where fecha_activacion is not null and tipo = 2");
return \Redirect::route('simcard')->with('result', $affectedRows);
} else {
if ($action == "ADDL") {
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$columns = '(nombreSubdistribuidor,numero, ICC,fecha_activacion, @dummy, @dummy, @dummy,@dummy,@dummy,@dummy,@dummy)';
$pdo->exec("delete from simcards_temp");
$pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " IGNORE INTO TABLE `simcards`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES" . $columns . "\n SET tipo = 2");
$pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `simcards_temp`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES" . $columns . "\n SET tipo = 2");
$pdo->exec("update simcards inner join simcards_temp on simcards.numero = simcards_temp.numero set simcards.fecha_activacion=simcards_temp.fecha_activacion, simcards.nombreSubdistribuidor = simcards_temp.nombreSubdistribuidor");
$columns = '(@dummy,numero, @dummy,fecha_activacion,NIT, nombre_empresa, direccion_empresa,cod_scl,cod_punto,valor,plan)';
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `libres`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("UPDATE simcards SET fecha_vencimiento = DATE_ADD(fecha_activacion, INTERVAL 1 YEAR) where fecha_activacion is not null and tipo = 2");
return \Redirect::route('simcard')->with('result', $affectedRows);
}
}
}
return \Redirect::route('simcard', ['result' => []]);
}
public function postUpload()
{
$user = Auth::user();
if (!$user) {
return redirect('/login');
}
if (Input::hasFile('file')) {
set_time_limit(600);
$cfsv2ColumnNames = ["station_id", "date", "gph200_0", "gph850_0", "h200_0", "h850_0", "p_msl_0", "p_sfl_0", "temp200_0", "temp850_0", "u200_0", "u850_0", "v200_0", "v850_0", "gph200_6", "gph850_6", "h200_6", "h850_6", "p_msl_6", "p_sfl_6", "temp200_6", "temp850_6", "u200_6", "u850_6", "v200_6", "v850_6", "gph200_12", "gph850_12", "h200_12", "h850_12", "p_msl_12", "p_sfl_12", "temp200_12", "temp850_12", "u200_12", "u850_12", "v200_12", "v850_12", "gph200_18", "gph850_18", "h200_18", "h850_18", "p_msl_18", "p_sfl_18", "temp200_18", "temp850_18", "u200_18", "u850_18", "v200_18", "v850_18", "actual_rainfall", "predict_rainfall"];
$file = Input::file('file');
$name = time() . '-' . $file->getClientOriginalName();
$new_path = public_path() . '/uploads/';
$file->move($new_path, $name);
$cfs_col_string = implode(",", $cfsv2ColumnNames);
$csv = fopen($new_path . $name, 'r');
$csv_col_names = fgetcsv($csv);
$col_index = [];
for ($i = 0; $i < count($cfsv2ColumnNames); $i++) {
$col_index[$i] = array_search($cfsv2ColumnNames[$i], $csv_col_names);
}
$date_index = array_search("date", $csv_col_names);
$pdo = \DB::connection()->getPdo();
while (!feof($csv)) {
$var = fgetcsv($csv);
if ($var) {
$new_var = [];
for ($i = 0; $i < count($cfsv2ColumnNames); $i++) {
if ($i == 1) {
// date
$new_var[$i] = $pdo->quote(date("Y-m-d", strtotime($var[$col_index[$i]])));
} else {
$new_var[$i] = $pdo->quote($col_index[$i] !== false ? $var[$col_index[$i]] : null);
}
}
$q = 'insert into cfsv2s (' . implode(",", $cfsv2ColumnNames) . ') values (' . implode(",", $new_var) . ')';
$pdo->query($q);
}
}
try {
unlink($new_path . $name);
} catch (Exception $e) {
}
return view('upload')->with('user', $user)->with('success', true);
}
return view('upload')->with('user', $user)->with('error', 'กรุณาเลือกไฟล์');
}
public function subirArchivo(Request $request)
{
$action = $request['accion'];
if ($action == "ADD") {
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$pdo->exec('SET foreign_key_checks = 0');
/*
if (($gestor = fopen($file,'r')) !== FALSE) {
while (($vars = fgetcsv($gestor, 1000, ";")) !== FALSE) {
$numero = $vars[0];
$plan = $vars[4];
$fecha = $vars[5];
$sim = \App\Libre::find($numero);
if($sim != null){
$sim->fecha_activacion = $fecha;
$sim->plan = $plan;
$sim->save();
}
$sim = \App\Simcard::find($numero);
if($sim != null){
$sim->fecha_activacion = $fecha;
$sim->save();
}
}
}
return \Redirect::route('simcard')->with('result' , 15);
//*/
$columns = '(numero,ICC,fecha_vencimiento,tipo,nombreSubdistribuidor)';
$pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " IGNORE INTO TABLE `simcards`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("delete from simcards_temp");
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `simcards_temp`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("UPDATE simcards_temp SET nombreSubdistribuidor = REPLACE(REPLACE(nombreSubdistribuidor, '\r', ''), '\n', '');");
$pdo->exec("update simcards inner join simcards_temp on simcards.numero = simcards_temp.numero set simcards.ICC = simcards_temp.ICC, simcards.fecha_vencimiento=simcards_temp.fecha_vencimiento,simcards.nombreSubdistribuidor = simcards_temp.nombreSubdistribuidor, simcards.tipo = simcards_temp.tipo, simcards.fecha_activacion = null");
$pdo->exec('SET foreign_key_checks = 1');
return \Redirect::route('simcard')->with('result', $affectedRows);
} else {
if ($action == "UPLOAD") {
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$pdo->exec("delete from simcards_temp");
$columns = '(ICC,numero, fecha_activacion)';
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `simcards_temp`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("update simcards inner join simcards_temp on simcards.numero = simcards_temp.numero set simcards.fecha_activacion=simcards_temp.fecha_activacion ");
$pdo->exec("UPDATE simcards SET fecha_vencimiento = DATE_ADD(fecha_activacion, INTERVAL 6 MONTH) where fecha_activacion is not null and tipo = 1");
$pdo->exec("UPDATE simcards SET fecha_vencimiento = DATE_ADD(fecha_activacion, INTERVAL 1 YEAR) where fecha_activacion is not null and tipo = 2");
return \Redirect::route('simcard')->with('result', $affectedRows);
} else {
if ($action == "ADDL") {
$file = $request->file('image');
if (!file_exists($file)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = \DB::connection()->getPdo();
} catch (PDOException $e) {
die("database connection failed: " . $e->getMessage());
}
$columns = '(nombreSubdistribuidor,numero, ICC,fecha_activacion, @dummy, @dummy, @dummy,@dummy,@dummy,@dummy,@dummy)';
$pdo->exec("delete from simcards_temp");
$var = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " IGNORE INTO TABLE `simcards`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES" . $columns . "\n SET tipo = 2");
$var = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `simcards_temp`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES" . $columns . "\n SET tipo = 2");
$var = $pdo->exec("delete libres.* from libres inner join simcards_temp on simcards_temp.numero = libres.numero");
$pdo->exec("delete recargas.* from recargas inner join simcards on simcards.numero = recargas.telefono inner join simcards_temp on simcards_temp.ICC = simcards.ICC");
$var = $pdo->exec("update simcards inner join simcards_temp on simcards.ICC = simcards_temp.ICC set simcards.numero=simcards_temp.numero,simcards.fecha_activacion=simcards_temp.fecha_activacion, simcards.nombreSubdistribuidor = simcards_temp.nombreSubdistribuidor, simcards.tipo = 2");
$var = $pdo->exec("delete simcards_temp.* from simcards_temp left join simcards on simcards.ICC = simcards_temp.ICC where simcards.ICC is not null");
$var = $pdo->exec("update simcards inner join simcards_temp on simcards.numero = simcards_temp.numero set simcards.ICC=simcards_temp.ICC,simcards.fecha_activacion=simcards_temp.fecha_activacion, simcards.nombreSubdistribuidor = simcards_temp.nombreSubdistribuidor");
$columns = '(@dummy,numero, @dummy,fecha_activacion,NIT, nombre_empresa, direccion_empresa,cod_scl,cod_punto,valor,plan)';
$affectedRows = $pdo->exec("\n LOAD DATA LOCAL INFILE " . $pdo->quote($file) . " REPLACE INTO TABLE `libres`\n FIELDS TERMINATED BY " . $pdo->quote(";") . "\n LINES TERMINATED BY " . $pdo->quote("\n") . "\n IGNORE 0 LINES " . $columns);
$pdo->exec("UPDATE simcards SET fecha_vencimiento = DATE_ADD(fecha_activacion, INTERVAL 1 YEAR) where fecha_activacion is not null and tipo = 2");
return \Redirect::route('simcard')->with('result', $affectedRows);
}
}
}
return \Redirect::route('simcard', ['result' => []]);
}
public function exporttoexcel($input)
{
$date = date('d/m/Y');
$ngaynhanbatdau = $input['ngaynhanbatdau'];
$ngaynhanketthuc = $input['ngaynhanketthuc'];
$loaicongvanden = $input['loaicongvan'];
// get array
\DB::connection()->setFetchMode(\PDO::FETCH_ASSOC);
if ($ngaynhanbatdau == '' && $ngaynhanketthuc == '' && $loaicongvanden == 0) {
$baocaocongvandens = \DB::table('congvandens')->get();
} else {
if ($loaicongvanden == 0) {
$baocaocongvandens = \DB::table('congvandens')->where('ngaynhancongvan', '>=', $ngaynhanbatdau)->where('ngaynhancongvan', '<=', $ngaynhanketthuc)->select('*')->get();
} else {
$baocaocongvandens = \DB::table('congvandens')->where('ngaynhancongvan', '>=', $ngaynhanbatdau)->where('ngaynhancongvan', '<=', $ngaynhanketthuc)->orWhere('danhmucloaicongvan_id', '=', $loaicongvanden)->select('*')->get();
}
}
$data = (array) $baocaocongvandens;
$filename = 'Bao_cao_cong_van_den_ngay_' . str_replace('/', '_', $date);
\Excel::create($filename, function ($excel) use($filename, $data) {
// Set the title
$excel->setTitle($filename);
$excel->sheet('Sheetname', function ($sheet) use($filename, $data) {
$sheet->fromArray($data, null, 'A1', false, false);
});
})->export('xls');
}