sports_admin/app/Http/Controllers/Admin/DatabaseCleanController.php

<?php
    /**
     * Created by PhpStorm.
     * User: blues
     * Date: 2019/1/4
     * Time: 18:36
     */


    namespace App\Http\Controllers\Admin;

    use App\Http\Controllers\Controller;
    use App\Models\GameType;
    use Request;
    use Illuminate\Support\Facades\DB;

    class DatabaseCleanController extends Controller
    {
        private $table_names;
        public function __construct()
        {
            $this->table_names=$this->tableDescription();
        }

        //特殊表单
        private function specialTable(){
            return [
                'account','account_group'
            ];
        }


        //不处理表单
        private function blacklist(){
            return [
                'account_bank'
            ];
        }

        function index()
        {
           if (!empty($_POST)){
               $time    =   $_POST['time']??'';
               $rel =[];
               if(!isset($_POST['games'])&&empty($_POST['games'] ))
                   return ['status'=>-1,'data'=>[],'msg'=>'请选择清理表'];
               if(time()-strtotime($time)<=120*3600*24)
                   return ['status'=>-1,'data'=>[],'msg'=>'请选择半年之前的数据'];
               foreach ($_POST['games'] as $k=> $v){

                       $rel[$k]['name']=$this->tableName($k);
                       $rel[$k]['success']=$this->del($k,$time);

                   if(strpos($k,'game_')===0){
                       $k=$k.'_buy';
                       $rel[$k]['name']=$this->tableName($k);
                       $rel[$k]['success']=$this->del($k,$time);
                   }
               }
               return ['status'=>1,'data'=>$rel,'msg'=>'成功'];
           }
            //表名 非游戏表
            $tables = DB::select("SELECT tablename FROM pg_tables WHERE tablename not like 'pg_%' 	and (tablename not like 'Z_%')and (tablename not like 'sql_%') and (tablename not like 'game_%')") ;


           $tables  =   array_map(function ($v){
               $a['gameCode'] =   $v->tablename ;
               $a['name']=$this->tableName($v->tablename);
               $a['time']=!empty($this->fieldTimes($v->tablename))?$this->fieldTimes($v->tablename):$this->getOtherFields(  $v->tablename);
               return $a;
           },$tables);

           //表分类
            $special=[];
            $normal=[];
            $other  =   [];
            foreach ($tables as $m=>$n) {
                if(in_array($n['gameCode'],$this->blacklist()))continue;
                if(in_array($n['name'],['未知','未用']))continue;

                if (in_array($n['gameCode'], $this->specialTable())) {
                    $special[] = $n;
                } elseif (!empty($n['time'])) {
                    $normal[]= $n;
                } else {
                    $other[]  = $n;
                }
            }
            $games = new GameType();
            $gamelist = $games->getGameList();

            return view('admin.systemSet/databaseClean', ['gamelist' => array_map(function ($v){ $v['gameCode']='game_'.$v['gameCode'];
                return $v;},$gamelist), 'special'=>$special,'other'=>$other,'normal'=>$normal,'limitdate' => date('Y-m-d 00:00:00', time() - 120 * 24 * 3600)]);
        }

        function del($table_name = '', $time = '')
        {

            if(!$time)return -1;
            $field  =   $this->fieldTimes($table_name);
            $other  =  $this->getOtherFields($table_name) ;
            $field=!empty($field)||empty($other)?$field:$this->fieldTimes($table_name,$other);
            $del = DB::table($table_name);
            if(isset($field['field'])){
                if ($field['type']=='varchar'||strpos($field['type'],'int')!==false){
                    $time =   strtotime($time);
                }

            $del= $del->  where($field['field'],'<',$time);
            }

            try{
                return $del->delete();
            }catch (\Exception $e){
                return  $e->getMessage();
            }

        }


        /**
         * @param $table_name 表名
         * @param string $field_name like字段名
         * @return array    返回字段名称，类型，说明
         */
        private function fieldTimes($table_name,$field_name='time'){

            $field = DB::select("SELECT a.attname AS field,t.typname AS type ,col_description(a.attrelid,a.attnum) as comment FROM pg_class c,pg_attribute a,pg_type t
WHERE c.relname = '" . $table_name . "' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid and a.attname like '%".$field_name."%'
ORDER BY a.attnum;");
            $arr=[];


            if($field&&count($field)>=1){
                foreach ($field[0] as $k=>$v){
                    $arr[$k]=$v;
                }
            }

            return $arr;
        }

        /**
         * 表备注
         * @return mixed
         */
        private function tableDescription()
        {
            $json = '{"withdraw_rule":"提现流水表","user_log_record":"用户日志记录","system_log":"管理员操作日志表","sixmoney_prize":"六合中奖记录表","sixmoney_buy":"六合购买记录表","prize_api_log":"开奖详情表","orderurl":"投注记录表","nagent_money":"级差代理资金记录表","nagent_edgerecharge":"级差代理代充","nagent_detailed":"级差代理详情表","money_take":"提现申请记录表","money_return":"回水记录表","money_red":"答题红包好运彩金表","money_recharge_remark":"充值记录备注表","money_recharge":"充值记录表","money_prize":"中奖记录表","money_nagent":"级差代理申请金额表","money_details":"资金详情表","money_count":"用户资金统计表","money_buy":"购买记录表","money_back":"资金反水记录表","money_agent":"代理资金申请表","message_read":"消息记录表","message":"消息提示表","lucky_money_take":"红包领取记录表","lucky_money":"红包发送记录表","lottery_money_log":"彩金记录表","long_record":"长龙记录表","long_main":"长龙期号表","logfile":"登录日志表","game_xy28_buy":"幸运28购买表","game_xy28":"幸运28游戏表","game_xjssc_buy":"新疆时时彩购买表","game_xjssc":"新疆时时彩游戏表","game_xjp28_buy":"新加坡28购买表","game_xjp28":"新加坡28游戏表","game_tw28_buy":"台湾28购买表","game_tw28":"台湾28游戏表","game_tjssc_buy":"天津时时彩购买表","game_tjssc":"天津时时彩游戏表","game_tjkl10_buy":"天津快乐10购买表","game_tjkl10":"天津快乐10游戏表","game_threessc_buy":"三分时时彩购买表","game_threessc":"三分时时彩游戏表","game_ssq_buy":"双色球购买表","game_ssq":"双色球游戏表","game_sixlottery_log":"极速六合彩开奖号码表","game_sixlottery":"六合彩游戏表","game_sixlottery_buy":"六合彩购买表","game_shssl_buy":"上海时时乐购买表","game_shssl":"上海时时乐游戏表","game_shkuai3_buy":"上海快3购买表","game_shkuai3":"上海快3游戏表","game_sfsixlottery_log":"极速六合彩开奖号码表","game_sfsixlottery_buy":"极速六合彩购买表","game_sfsixlottery":"极速六合彩游戏表","game_sd11x5_buy":"山东11x5购买表","game_sd11x5":"山东11x5游戏表","game_rule_info":"游戏投注号码统计金额","game_qqffc_buy":"QQ分分彩购买表","game_qqffc":"QQ分分彩游戏表","game_prize":"预期开奖时间表","game_preno":"预备游戏信息表","game_pl3_buy":"排列3购买表","game_pl3":"排列3游戏表","game_onessc_buy":"极速分分彩购买表","game_onessc":"极速分分彩游戏表","game_onepk10_buy":"极速pk10购买表","game_onepk10":"极速PK10游戏表","game_onekuai3_buy":"极速快3购买表","game_onekuai3":"极速快3游戏表","game_one28_buy":"极速28购买表","game_one28":"极速28游戏表","game_oggame_buy":"真人视讯购买表","game_oggame":"真人视讯表","game_luckyft_buy":"幸运飞艇购买表","game_luckyft":"幸运飞艇游戏表","game_jxkuai3_buy":"江西快3购买表","game_jxkuai3":"江西快3游戏表","game_jspk10_buy":"三分pk拾购买表","game_jspk10":"三分pk拾游戏表","game_jskuai3_buy":"江苏快3购买表","game_jskuai3":"江苏快3游戏表","game_js11x5_buy":"江苏11x5购买表","game_js11x5":"江苏11x5游戏表","game_jnd28_buy":"加拿大28购买表","game_jnd28":"加拿大28游戏表","game_hs28_buy":"韩式28购买表","game_hs28":"韩式28游戏表","game_hnkuai3_buy":"湖南快3购买表","game_hnkuai3":"湖南快3游戏表","game_hlj11x5_buy":"黑龙江11x5购买表","game_hlj11x5":"黑龙江11x5游戏表","game_hebkuai3":"河北快3购买表","game_hebkuai3":"河北快游戏表","game_hbkuai3_buy":"湖北快3购买表","game_hbkuai3":"湖北快3游戏表","game_gxkuai3_buy":"广西快3购买表","game_gxkuai3":"广西快3游戏表","game_gxkl10_buy":"广西快乐10购买表","game_gxkl10":"广西快乐10游戏表","game_gpxy28_buy":"高赔幸运28购买表","game_gpxy28":"高赔幸运28游戏表","game_gpjnd28_buy":"高赔加拿大28购买表","game_gpjnd28":"高赔加拿大28","game_gdkl10_buy":"广东快乐10购买表","game_gdkl10":"广东快乐10游戏表","game_gd11x5_buy":"广东11x5购买表","game_gd11x5":"广东11x5游戏表","game_fc3d_buy":"福彩3D购买表","game_fc3d":"福彩3D游戏表","game_dj28_buy":"东京28购买表","game_dj28":"东京28游戏表","game_dice_buy":"摇骰子购买表","game_dice":"摇骰子游戏表","game_cqssc_buy":"重庆时时彩购买表","game_cqssc":"重庆时时彩","game_cqkl10_buy":"重庆快乐10购买表","game_cqkl10":"重庆快乐10","game_bjpk10_buy":"北京PK10购买表","game_bjpk10":"北京PK10","game_betcount":"六合彩投注详情","feedback":"反馈表","game_ankuai3":"安徽快3游戏表","game_ahkuai3_buy":"安徽快3购买表","chat_users":"聊天室用户表","chat_message":"聊天室消息表","btrecord":"不知道是什么表，有用，","bet_count":"投注统计","account_token":"用户token表","oggame_betting_ogrbv":"OG视讯表","kygame_betting_ogrbv":"开元棋牌","aggame_betting_ogrbv":"AG视讯"}';
            return json_decode($json,1);
        }


        /**
         * 优先使用数组中的字段删除
         */
        private function getOtherFields ($table_name)
        {
            $other= [
                'lucky_money'=>'created_at',
                'oggame_betting_ogrbv'=>'AddTime',
                'kygame_betting_ogrbv'=>'GameEndTime',
            ];
            return $other[$table_name]??'null';
        }

        /**
         * 获取表名称
         * @param $table
         * @return string
         */
        private function tableName($table){
              return $name= $this->table_names[$table]??'未知';
     }
    }