蜘蛛程序抓取页面内容并按格式插入数据库

最新推荐文章于 2024-10-01 20:02:09 发布
myeye520
最新推荐文章于 2024-10-01 20:02:09 发布
阅读量199
点赞数
分类专栏： thinkphp 文章标签：蜘蛛爬虫
本文链接：https://blog.csdn.net/myeye520/article/details/104048499
版权
thinkphp 专栏收录该内容
3 篇文章 0 订阅
订阅专栏
<?php
namespace app\api\controller;
use think\Db;

/**
 * 策略业绩报表蜘蛛程序
 * Description of Spiderreport
 * Date 2017-11-14 14:31
 * @author Carter
 */
class Spiderreport {
    /**
     * 单策略业绩报表蜘蛛
     */
    public function single(){
        debug('begin');
        $datas = [];
        Db::execute('truncate table lhy_strategy_report');   // 清空表
        $file_path = ROOT_PATH.'TB回测/单一策略/';
        $title_res = Db::name('strategy_market')->where(['kind' => 2])->field(['title','num'])->select();
        //dump($title_res);exit;
        foreach ($title_res as $value){
            $value_arr = explode('-',$value['title']);
            $index_files = glob($file_path.$value_arr[0].'/'.$value_arr[1].'/SavedReport/xlrpt.files/XL_Summary.html');
            $moeny_files = glob($file_path.$value_arr[0].'/'.$value_arr[1].'/SavedReport/xlrpt.files/XL_AsisVarying_1.html');
            dump($index_files[0]);
            if(file_exists($index_files[0])){
                $html = iconv('gbk', 'utf-8', file_get_contents($index_files[0]));
                $money_html = iconv('gbk', 'utf-8', file_get_contents($moeny_files[0]));
                preg_match_all('#<td.+?>(.+?)<[/]?td>#', $html, $res);
                //dump($res);exit;
                preg_match_all('#<td.*?>(.+?)<[/]?td>#', $money_html, $money_res);
                $data = [
                    'num' => $value['num'],   //策略编号
                    'start_money' => $money_res[1][7],   //初始资金
                    'current_money' => $money_res[1][8],  //当前资金
                    'income_ratio' => trim($res[1][114]),  //总收益率
                    'sharp_ratio' => trim($res[1][128],'%'),   //夏普比率
                    'year_income_ratio' => 0, //年化收益率
                    'max_revoked_value' => $res[1][148],  //最大回撤值
                    'max_revoked_time' => $res[1][150],  //最大回撤时间
                    'max_revoked_ratio' => 0,   //最大回撤率
                    'income_risk_ratio' => 0,  //收益风险比
                    'run_day' => $res[1][130],  //总运行天数
                    'have_run_day' => $res[1][134],  //有持仓天数
                    'max_null_day' => $res[1][136], //最大空仓时间
                    'net_profit' => $res[1][6],  //净利润
                    'net_profit_long' => $res[1][7],  //净利润(多)
                    'net_profit_short' => $res[1][8],  //净利润(空)
                    'total_profit' => $res[1][10],   //总盈利
                    'total_profit_long' => $res[1][11],  //总盈利(多)
                    'total_profit_short' => $res[1][12],  //总盈利(空)
                    'total_loss' => $res[1][14],   //总亏损
                    'total_loss_long' => $res[1][15],  //总亏损(多)
                    'total_loss_short' => $res[1][16],  //总亏损(空)
                    'total_gain_loss_ratio' => $res[1][18],  //总盈利/总亏损
                    'total_gain_loss_long_ratio' => $res[1][19],  //总盈利/总亏损(多)
                    'total_gain_loss_short_ratio' => $res[1][20],  //总盈利/总亏损(空)
                    'profit_ratio' => trim($res[1][26],'%'),   //胜率(盈利比率)
                    'profit_long_ratio' => trim($res[1][27],'%'),  //胜率(盈利比率)(多)
                    'profit_short_ratio' => trim($res[1][28],'%'),  //胜率(盈利比率)(空)
                    'month_avg_profit' => $res[1][120],  //月平均利润
                    'avg_profit' => $res[1][46],  //平均利润
                    'avg_profit_long' => $res[1][47],  //平均利润(多)
                    'avg_profit_short' => $res[1][48],  //平均利润(空)
                    'avg_gain' => $res[1][50],  //平均盈利
                    'avg_gain_long' => $res[1][51],  //平均盈利(多)
                    'avg_gain_short' => $res[1][52],  //平均盈利(空)
                    'avg_loss' => $res[1][54],   //平均亏损
                    'avg_loss_long' => $res[1][55],  //平均亏损(多)
                    'avg_loss_short' => $res[1][56],  //平均亏损(空)
                    'avg_gain_loss_ratio' => $res[1][58],  //盈亏比
                    'avg_gain_loss_ratio_long' => $res[1][59],  //盈亏比(多)
                    'avg_gain_loss_ratio_short' => $res[1][60],  //盈亏比(空)
                    'buy_hand_count' => $res[1][22],  //交易手数
                    'buy_hand_count_long' => $res[1][23],  //交易手数(多)
                    'buy_hand_count_short' => $res[1][24],  //交易手数(空)
                    'profit_hand_count' => $res[1][30],  //盈利手数
                    'profit_hand_count_long' => $res[1][31],  //盈利手数(多)
                    'profit_hand_count_short' => $res[1][32],  //盈利手数(空)
                    'loss_hand_count' => $res[1][38],  //亏损手数
                    'loss_hand_count_long' => $res[1][39],  //亏损手数(多)
                    'loss_hand_count_short' => $res[1][40],  //亏损手数(空)
                    'max_profit' => $res[1][62],  //最大盈利
                    'max_profit_long' => $res[1][63],  //最大盈利(多)
                    'max_profit_short' => $res[1][64],  //最大盈利(空)
                    'max_loss' => $res[1][66],  //最大亏损
                    'max_loss_long' => $res[1][67],  //最大亏损(多)
                    'max_loss_short' => $res[1][68],  //最大亏损(空)
                    'max_profit_hand_count' => $res[1][82],  //最大连盈手数
                    'max_profit_hand_count_long' => $res[1][83],  //最大连盈手数(多)
                    'max_profit_hand_count_short' => $res[1][84],  //最大连盈手数(空)
                    'max_loss_hand_count' => $res[1][86],  //最大连亏手数
                    'max_loss_hand_count_long' => $res[1][87],  //最大连亏手数(多)
                    'max_loss_hand_count_short' => $res[1][88],  //最大连亏手数(空)
                    'status' => 1,   //状态 默认1：正常，0：不正常
                    'create_time' => date('Y-m-d H:i:s'),  //创建时间
                    'update_time' => date('Y-m-d H:i:s'),   //修改时间
                ];
                $datas[] = $data;
            }
            //dump(file_exists($index_files[0]));
        }
        //dump($datas);
        Db::name('strategy_report')->insertAll($datas);
        debug('end');
        echo debug('begin','end').'s<br>';
        echo debug('begin','end','m').'kb<br>';
    }
    
    /**
     * 组合策略业绩报表蜘蛛
     */
    public function multi(){
        debug('begin');
        $datas = [];
        $file_path = ROOT_PATH.'TB回测/组合策略/';
        $title_res = Db::name('strategy_market')->where(['kind' => 4])->field(['title','num'])->select();
        //dump($title_res);exit;
        foreach ($title_res as $value){
            $index_files = glob($file_path.$value['title'].'/SavedReport/xlrpt.files/XL_Summary.html');
            $moeny_files = glob($file_path.$value['title'].'/SavedReport/xlrpt.files/XL_AsisVarying_1.html');
            dump($index_files[0]);
            if(file_exists($index_files[0])){
                $html = iconv('gbk', 'utf-8', file_get_contents($index_files[0]));
                $money_html = iconv('gbk', 'utf-8', file_get_contents($moeny_files[0]));
                preg_match_all('#<td.+?>(.+?)<[/]?td>#', $html, $res);
                //dump($res);exit;
                preg_match_all('#<td.*?>(.+?)<[/]?td>#', $money_html, $money_res);
                $data = [
                    'num' => $value['num'],   //策略编号
                    'start_money' => $money_res[1][7],   //初始资金
                    'current_money' => $money_res[1][8],  //当前资金
                    'income_ratio' => trim($res[1][114]),  //总收益率
                    'sharp_ratio' => trim($res[1][128],'%'),   //夏普比率
                    'year_income_ratio' => 0, //年化收益率
                    'max_revoked_value' => $res[1][148],  //最大回撤值
                    'max_revoked_time' => $res[1][150],  //最大回撤时间
                    'max_revoked_ratio' => 0,   //最大回撤率
                    'income_risk_ratio' => 0,  //收益风险比
                    'run_day' => $res[1][130],  //总运行天数
                    'have_run_day' => $res[1][134],  //有持仓天数
                    'max_null_day' => $res[1][136], //最大空仓时间
                    'net_profit' => $res[1][6],  //净利润
                    'net_profit_long' => $res[1][7],  //净利润(多)
                    'net_profit_short' => $res[1][8],  //净利润(空)
                    'total_profit' => $res[1][10],   //总盈利
                    'total_profit_long' => $res[1][11],  //总盈利(多)
                    'total_profit_short' => $res[1][12],  //总盈利(空)
                    'total_loss' => $res[1][14],   //总亏损
                    'total_loss_long' => $res[1][15],  //总亏损(多)
                    'total_loss_short' => $res[1][16],  //总亏损(空)
                    'total_gain_loss_ratio' => $res[1][18],  //总盈利/总亏损
                    'total_gain_loss_long_ratio' => $res[1][19],  //总盈利/总亏损(多)
                    'total_gain_loss_short_ratio' => $res[1][20],  //总盈利/总亏损(空)
                    'profit_ratio' => trim($res[1][26],'%'),   //胜率(盈利比率)
                    'profit_long_ratio' => trim($res[1][27],'%'),  //胜率(盈利比率)(多)
                    'profit_short_ratio' => trim($res[1][28],'%'),  //胜率(盈利比率)(空)
                    'month_avg_profit' => $res[1][120],  //月平均利润
                    'avg_profit' => $res[1][46],  //平均利润
                    'avg_profit_long' => $res[1][47],  //平均利润(多)
                    'avg_profit_short' => $res[1][48],  //平均利润(空)
                    'avg_gain' => $res[1][50],  //平均盈利
                    'avg_gain_long' => $res[1][51],  //平均盈利(多)
                    'avg_gain_short' => $res[1][52],  //平均盈利(空)
                    'avg_loss' => $res[1][54],   //平均亏损
                    'avg_loss_long' => $res[1][55],  //平均亏损(多)
                    'avg_loss_short' => $res[1][56],  //平均亏损(空)
                    'avg_gain_loss_ratio' => $res[1][58],  //盈亏比
                    'avg_gain_loss_ratio_long' => $res[1][59],  //盈亏比(多)
                    'avg_gain_loss_ratio_short' => $res[1][60],  //盈亏比(空)
                    'buy_hand_count' => $res[1][22],  //交易手数
                    'buy_hand_count_long' => $res[1][23],  //交易手数(多)
                    'buy_hand_count_short' => $res[1][24],  //交易手数(空)
                    'profit_hand_count' => $res[1][30],  //盈利手数
                    'profit_hand_count_long' => $res[1][31],  //盈利手数(多)
                    'profit_hand_count_short' => $res[1][32],  //盈利手数(空)
                    'loss_hand_count' => $res[1][38],  //亏损手数
                    'loss_hand_count_long' => $res[1][39],  //亏损手数(多)
                    'loss_hand_count_short' => $res[1][40],  //亏损手数(空)
                    'max_profit' => $res[1][62],  //最大盈利
                    'max_profit_long' => $res[1][63],  //最大盈利(多)
                    'max_profit_short' => $res[1][64],  //最大盈利(空)
                    'max_loss' => $res[1][66],  //最大亏损
                    'max_loss_long' => $res[1][67],  //最大亏损(多)
                    'max_loss_short' => $res[1][68],  //最大亏损(空)
                    'max_profit_hand_count' => $res[1][82],  //最大连盈手数
                    'max_profit_hand_count_long' => $res[1][83],  //最大连盈手数(多)
                    'max_profit_hand_count_short' => $res[1][84],  //最大连盈手数(空)
                    'max_loss_hand_count' => $res[1][86],  //最大连亏手数
                    'max_loss_hand_count_long' => $res[1][87],  //最大连亏手数(多)
                    'max_loss_hand_count_short' => $res[1][88],  //最大连亏手数(空)
                    'status' => 1,   //状态 默认1：正常，0：不正常
                    'create_time' => date('Y-m-d H:i:s'),  //创建时间
                    'update_time' => date('Y-m-d H:i:s'),   //修改时间
                ];
                $datas[] = $data;
            }
            //dump(file_exists($index_files[0]));
        }
        Db::name('strategy_report')->insertAll($datas);
        debug('end');
        echo debug('begin','end').'s<br>';
        echo debug('begin','end','m').'kb<br>';
    }
}