PHP DB::connection方法代码示例

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