zcju/app/Http/Controllers/Admin/SqlController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Model\Data;
use App\Model\Department;
use App\Model\Job;
use App\Model\Log;
use App\Model\Project;
use App\Model\Table_eight;
use App\Model\Table_five;
use App\Model\Table_four;
use App\Model\Table_name;
use App\Model\Table_nine;
use App\Model\Table_one;
use App\Model\Table_seven;
use App\Model\Table_six;
use App\Model\Table_three;
use App\Model\Table_two;
use App\Model\Tablelist;
use App\Model\User;
use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;

class SqlController extends CommonController
{

    public function index()
    {
        $data = Data::all();
        return view('admin.sql.index',compact('data'));

    }

    //备份导出sql为xls
    public function backups(Request $request)
    {
        //生成备份文件名
        $excle_path = date('YmdHis');

        //数据库所有表名
        $tables = DB::select('SHOW TABLES');
        $tables =  json_decode( json_encode( $tables),true);
        $table_name = array();
        foreach($tables as $k => $v){
            $table_name[] = $v['Tables_in_zcju'];
        }

        //存datas表
        $input = $request->all();
        $input['path'] = $excle_path.'_'.$input['name'] ;
        Data::create($input);

        //循环查表导出excle文件

        Excel::create($excle_path.'_'.$request->all()['name'],function($excle) use ($table_name){
            foreach($table_name as $value){
                $data = DB::table($value)->get();
                $data =  json_decode( json_encode( $data),true);

                $excle->sheet($value,function($sheet) use ($data){
                    $sheet->fromArray($data);
                });
            }
        })->store('xls',storage_path('databases/'));


        $this->mkFolder(storage_path('databases/'));

        return redirect('/sql');
    }


    // 恢复备份 xls导入到数据库
    public function recovery($id)
    {


        $path = Data::where('id','=',$id)->first()->path;
        $filePath = 'storage/databases/'.$path.'.xls';
        Excel::load($filePath, function($reader) {
            $data = $reader->toArray();

            foreach($data[0] as $value){
               if(!Data::find($value['id'])){
                   Data::create($value);
               }else{
                   Data::where('id','=',$value['id'])->update($value);
               }
            }
            foreach($data[1] as $value){
                if(!Department::find($value['id'])){
                    Department::create($value);
                }else{
                    Department::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[2] as $value){
                if(!Log::find($value['id'])){
                    Log::create($value);
                }else{
                    Log::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[4] as $value){
                if(!Project::find($value['id'])){
                    Project::create($value);
                }else{
                    Project::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[5] as $value){
                if(!Job::find($value['id'])){
                    Job::create($value);
                }else{
                    Job::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[6] as $value){
                if(!Table_eight::find($value['id'])){
                    Table_eight::create($value);
                }else{
                    Table_eight::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[7] as $value){
                if(!Table_five::find($value['id'])){
                    Table_five::create($value);
                }else{
                    Table_five::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[8] as $value){
                if(!Table_four::find($value['id'])){
                    Table_four::create($value);
                }else{
                    Table_four::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[9] as $value){
                if(!Tablelist::find($value['id'])){
                    Tablelist::create($value);
                }else{
                    Tablelist::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[10] as $value){
                if(!Table_name::find($value['id'])){
                    Table_name::create($value);
                }else{
                    Table_name::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[11] as $value){
                if(!Table_nine::find($value['id'])){
                    Table_nine::create($value);
                }else{
                    Table_nine::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[12] as $value){
                if(!Table_one::find($value['id'])){
                    Table_one::create($value);
                }else{
                    Table_one::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[13] as $value){
                if(!Table_seven::find($value['id'])){
                    Table_seven::create($value);
                }else{
                    Table_seven::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[14] as $value){
                if(!Table_six::find($value['id'])){
                    Table_six::create($value);
                }else{
                    Table_six::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[15] as $value){
                if(!Table_three::find($value['id'])){
                    Table_three::create($value);
                }else{
                    Table_three::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[16] as $value){
                if(!Table_two::find($value['id'])){
                    Table_two::create($value);
                }else{
                    Table_two::where('id','=',$value['id'])->update($value);
                }
            }
            foreach($data[17] as $value){
                if(!User::find($value['id'])){
                    User::create($value);
                }else{
                    User::where('id','=',$value['id'])->update($value);
                }
            }

        });
        return redirect('/sql');
    }

    //导出xls  下载链接
    public function download($id)
    {
        $header = array('Content-Type'=>'application/x-xls');
        $path = Data::find($id)->path;

        $file = storage_path('databases/'.$path.'.xls').Input::get('file');
        $file_name = date('YmdHis').'.xls';
        return response()->download($file,$file_name,$header);
    }

//    修改文件夹权限
    protected static function mkFolder($path)
    {

        if (!is_readable($path)) {

            mkdir($path, 0777, true);

        }

    }

}