<?php
namespace app\admin\controller;
use think\Db;
use think\facade\Request;
class Package extends Common
{
    /**
     * 商家菜品套餐-套餐列表
     */
    public function lists(){
        if(Request::isAjax()){
            $data = Request::except('file');

            $where = ' 1=1 and ';
            $channel = $data['channel'] ? $data['channel'] : 1;

            $where .= ($channel && $channel==4) ? ' re.type_id in (1,2,3)' : " re.type_id in ($channel)";
            $where .= $data['key'] ? ' and s.sellername like "%'.$data['key'].'%"' : "";
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //套餐信息
            $rs = Db::query("SELECT
                                s.*
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_seller_food_set AS s
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.sid
                            WHERE ".$where." order by s.sid".$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['cdate'] = date("Y年m月d日",$value['cdate']);
            }

            //获取总条数
            $total = Db::query("SELECT
                            s.id 
                        FROM
                            `xt-member-two-LINE`.xt_dc_dz_seller_food_set AS s
                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.sid
                        WHERE ".$where."");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','套餐列表');
        return $this->fetch();
    }

    /**
     * 创建套餐-商家列表
     */
    public function sellerlist(){
        if(Request::isAjax()){
            $data = Request::except('file');

            $where = '';
            $channel = $data['channel'] ? $data['channel'] : 1;

            $where .= ($channel && $channel==4) ? ' re.type_id in (1,2,3)' : " re.type_id in ($channel)";
            $where .= $data['key'] ? ' and s.sellername like "%'.$data['key'].'%"' : "";
            
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //商家基本数据
            $rs = Db::query("SELECT
                                s.id,
                                s.sellername
                            FROM
                                `xt-member-two-LINE`.xt_seller AS s
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.id
                            WHERE
                                s.isopen_dc = 1 
                            AND ".$where." GROUP BY s.id order by s.id".$limit);
            //获取总条数
            $total = Db::query("SELECT
                            s.id 
                        FROM
                            `xt-member-two-LINE`.xt_seller AS s
                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.id
                        WHERE
                            s.isopen_dc = 1 
                            AND ".$where."
                        GROUP BY
                            s.id");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        return $this->fetch();
    }

    /**
     * 创建菜品套餐-完成创建
     */
    public function add_foodset(){
        $data = Request::except('file');
        if(Request::isAjax()){
            $arr = [];
            $arr['sid']             = $data['seller_id'] ? $data['seller_id'] : 0;
            $arr['sellername']      = $data['seller_name'] ? $data['seller_name'] : '';
            $arr['name']            = $data['name'];
            $arr['market_price']    = $data['market_price'];
            $arr['sale_price']      = $data['sale_price'];
            $arr['cost_price']      = $data['cost_price'];
            $arr['discount']        = bcdiv($data['sale_price'], $data['market_price'], 1);//折扣  = 现价【销售价】 / 原价【市场价】
            $arr['service']         = isset($data['server']) ? implode(',', $data['server']) : '';
            $arr['main_url']        = $data['upload_main_val'];
            $arr['detail_url']      = $data['upload_detail_val'];
            $time                   = $data['date'] ? explode(' - ', $data['date']) : '';
            $arr['eff_time_start']  = $time ? strtotime(date($time[0],time())) : 0;
            $arr['eff_time_end']    = $time ? strtotime(date($time[1],time())) : 0;
            $arr['use_time']        = $data['usetime'];
            $arr['use_notice']      = $data['usenotice'];
            $arr['status']          = $data['status'];
            $arr['cdate']           = time();
            $arr['founder']         = $_SESSION['think']['username'];
            //有新加参数 需在此后追加。。。

            $keys =  implode(',',array_keys($arr));

            $sql = "INSERT INTO `xt-member-two-LINE`.xt_dc_dz_seller_food_set  ($keys) VALUES (";
            foreach ($arr as $key => $value) {
                $sql .= '"'.$value.'",';
            }
            $sql = substr( $sql,0, strlen($sql)-1 );
            $sql .=")";
            $res = Db::query($sql);

            if(!$res){
                $result = ['code'=>1,'msg'=>'添加成功!','rel'=>1];
            }else{
                $result = ['code'=>0,'msg'=>'添加失败!','rel'=>1];
            }
            return $result;
        }
        return $this->fetch();
    }

    /**
     * 优惠券-卡券详情
     */
    public function footsetdetail(){
        //print_r(Request::except('file'));die;
        $data = Request::except('file');
        //获取套餐详情
        $rs = Db::query("SELECT
                                *
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_seller_food_set
                            WHERE id = ".$data['id']."
                            ");
        switch ($rs[0]['status']) {
            case '0':
                $rs[0]['status'] = '已上架';
                break;
            case '1':
                $rs[0]['status'] = '已下架';
                break;
            default:
                # code...
                break;
        }
        $rs[0]['eff_time_end']      =  $rs[0]['eff_time_end'] ? date("Y年m月d日",$rs[0]['eff_time_end']) : '';
        $rs[0]['eff_time_start']    =  $rs[0]['eff_time_start'] ? date("Y年m月d日",$rs[0]['eff_time_start']) : '';
        $rs[0]['lasttime']          =  $rs[0]['lasttime'] ? date("Y年m月d日",$rs[0]['lasttime']) : '';
        $rs[0]['cdate']             =  $rs[0]['cdate'] ? date("Y年m月d日",$rs[0]['cdate']) : '';
        $rs[0]['off_time']          =  $rs[0]['off_time'] ? date("Y年m月d日",$rs[0]['off_time']) : '';
        $rs[0]['on_time']           =  $rs[0]['on_time'] ? date("Y年m月d日",$rs[0]['on_time']) : '';
        $this->assign ('rs',$rs[0]);
        $this->assign ( 'title','套餐详情');
        return $this->fetch();
    }

    /**
     * 创建套餐-选择商户
     */
    public function selectseller(){
        $this->assign ( 'title','创建套餐');
        return $this->fetch();
    }

    /**
     * 菜品套餐 上架/下架
     */
    public function foodState(){
        $id=input('post.id');
        $status=input('post.status');
        if (empty($id)){
            $result['status'] = 0;
            $result['info'] = '套餐不存在!';
            $result['url'] = url('lists');
            return $result;
        }
        if($status==1)
            $strtime = ' ,on_time  = '.time();
        else
            $strtime = ' ,off_time = '.time();

        $sql = "UPDATE `xt-member-two-LINE`.xt_dc_dz_seller_food_set set status = ".$status.",lasttime = ".time().", operator = '".$_SESSION['think']['username']."' $strtime where id = ".$id;
        $ups = Db::query($sql);
        if(!$ups){
            $result['code'] = 1;
            $result['info'] = '变更成功!';
            $result['url'] = url('lists');
        }else{
            $result['code'] = 0;
            $result['info'] = '变更失败,请联系管理员!';
            $result['url'] = url('lists');
        }
        
        return $result;
    }

    

    /**
     * 创建优惠券-卡券类型选择
     */
    public function cardtypelist(){
        if(Request::isAjax()){
            //商家基本数据
            $rs = Db::query("SELECT
                                *
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_base ");
            
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'rel'=>2];
        }
        return $this->fetch();
    }

    /**
     * 商家卡券-卡券投放-卡券列表
     */
    public function cardput(){
        if(Request::isAjax()){
            $data = Request::except('file');

            $where = '';
            $channel = $data['channel'] ? $data['channel'] : 1;

            $where .= ($channel && $channel==4) ? ' re.type_id in (1,2,3)' : " re.type_id in ($channel)";
            $where .= $data['key'] ? ' and s.sellername like "%'.$data['key'].'%"' : "";
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //卡券信息
            $rs = Db::query("SELECT
                                s.*
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS s
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.sid
                            WHERE  s.owner = 2 AND
                            ".$where." order by s.sid".$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['create_time'] = date("Y年m月d日",$value['create_time']);
                $rs[$key]['contributor_time'] = $value['contributor_time'] ? date("Y年m月d日",$value['contributor_time']) : '';
                switch ($value['status']) {
                    case '1':
                        $rs[$key]['status_msg'] = '已投放';
                        break;
                    case '2':
                        $rs[$key]['status_msg'] = '未投放';
                        break;
                    case '3':
                        $rs[$key]['status_msg'] = '停止投放';
                        break;
                    default:
                        # code...
                        break;
                }
            }

            //获取总条数
            $total = Db::query("SELECT
                            s.id 
                        FROM
                            `xt-member-two-LINE`.xt_dc_dz_card_info AS s
                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.sid
                        WHERE  s.owner = 2 AND
                            ".$where."");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','商家卡券');
        return $this->fetch();
    }

    /**
     * 商家卡券-卡券投放- 停止投放
     */
    public function cardstop(){
        if(Request::isAjax()){
            $data = Request::except('file');
            $ups = Db::query("UPDATE `xt-member-two-LINE`.xt_dc_dz_card_info set status = 3 where id = ".$data['id']);
            if(!$ups){
                $result['code'] = 1;
                $result['msg'] = '该卡券已停止投放！';
            }else{
                $result['code'] = 0;
                $result['msg'] = '请求错误,请联系管理员!';
            }
            return $result;
        }
        return view();
    }

    /**
     * 商家卡券-卡券投放- 投放-弹层
     */
    public function cardputin(){
        $id = Request::except('file');
        if(request()->isPost()){
            $data = input('post.');

            //商家基本数据
            $end_time = Db::query("SELECT
                            end_time
                        FROM
                            `xt-member-two-LINE`.xt_dc_dz_card_info
                        WHERE id=".$data['cid']);

            $arr = [];
            $time              = $data['date'] ? explode(' - ', $data['date']) : '';
            $arr['receive_start_time'] = $time ? strtotime(date($time[0],time())) : '';
            $arr['receive_end_time']   = $time ? strtotime(date($time[1],time())) : '';
            $arr['total_limit']        = $data['total_limit']=='on' ? 1 : 0;
            $arr['total']              = $data['total'] ? intval($data['total']) : 0;
            $arr['totals']             = $data['total'] ? intval($data['total']) : 0;
            $arr['receive_condition']  = $data['receive_condition'] ? intval($data['receive_condition']) : 0;
            $arr['order_price']        = $data['order_price'] ? $data['order_price'] : 0;
            $arr['contributor']        = $_SESSION['think']['username'];
            $arr['contributor_time']   = time();
            $arr['status']             = 1;

            if(($end_time[0]['end_time']!=0) && ($end_time[0]['end_time'] < $arr['receive_end_time'])) {
                $result['code'] = 0;
                $result['msg'] = '领取期限必须在卡券有效期内！';
                return $result;
            }

            $sql = "UPDATE `xt-member-two-LINE`.xt_dc_dz_card_info SET "; 
            foreach ($arr as $key=>$val){
                $sql .= "".$key."='".$val."',";
            }
            $sql = substr( $sql,0, strlen($sql)-1 );
            $sql .= ' where id = '.$data['cid'];

            $ups = Db::query($sql);
            if(!$ups){
                $result['code'] = 1;
                $result['msg'] = '已成功投放！';
            }else{
                $result['code'] = 0;
                $result['msg'] = '请求错误,请联系管理员!';
            }
            return $result;
        }
        //商家基本数据
        $rs = Db::query("SELECT
                            id,
                            sellername,
                            card_title,
                            end_time
                        FROM
                            `xt-member-two-LINE`.xt_dc_dz_card_info
                        WHERE id=".$id['id']);
        $this->assign ( 'rs',$rs[0]);
        $this->assign ( 'title','卡券投放');
        return $this->fetch();
    }

    /**
     * 平台卡券-优惠券-卡券列表
     */
    public function platformcard(){
        if(Request::isAjax()){
            $data = Request::except('file');
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //卡券信息
            $rs = Db::query("SELECT
                                s.*
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS s
                            WHERE s.owner = 1 order by s.sid".$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['create_time'] = date("Y年m月d日",$value['create_time']);
                switch ($value['status']) {
                    case '1':
                        $rs[$key]['status'] = '已投放';
                        break;
                    case '2':
                        $rs[$key]['status'] = '未投放';
                        break;
                    case '3':
                        $rs[$key]['status'] = '停止投放';
                        break;
                    default:
                        # code...
                        break;
                }
            }

            //获取总条数
            $total = Db::query("SELECT
                            s.id 
                        FROM
                            `xt-member-two-LINE`.xt_dc_dz_card_info AS s
                            
                        WHERE s.owner = 1");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','平台卡券');
        return $this->fetch();
    }

    /**
     * 创建优惠券-选择商户
     */
    public function selectplatform(){
        $this->assign ( 'title','创建卡券');
        return $this->fetch();
    }

    /**
     * 平台卡券-卡券投放-卡券列表
     */
    public function cardputforplat(){
        if(Request::isAjax()){
            $data = Request::except('file');
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //卡券信息
            $rs = Db::query("SELECT
                                s.*
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS s
                            WHERE  s.owner = 1 order by s.sid".$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['create_time'] = date("Y年m月d日",$value['create_time']);
                $rs[$key]['contributor_time'] = $value['contributor_time'] ? date("Y年m月d日",$value['contributor_time']) : '';
                switch ($value['status']) {
                    case '1':
                        $rs[$key]['status_msg'] = '已投放';
                        break;
                    case '2':
                        $rs[$key]['status_msg'] = '未投放';
                        break;
                    case '3':
                        $rs[$key]['status_msg'] = '停止投放';
                        break;
                    default:
                        # code...
                        break;
                }
            }

            //获取总条数
            $total = Db::query("SELECT
                            s.id 
                        FROM
                            `xt-member-two-LINE`.xt_dc_dz_card_info AS s
                        WHERE  s.owner = 1");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','平台卡券');
        return $this->fetch();
    }

    /**
     * 商家卡券-数据统计-统计列表
     */
    public function sellercarddatalist(){
        if(Request::isAjax()){
            $data = Request::except('file');

            $where = '';
            $channel = $data['channel'] ? $data['channel'] : 1;

            $where .= ($channel && $channel==4) ? ' AND rel.type_id in (1,2,3)' : " AND rel.type_id in ($channel)";
            $where .= $data['key'] ? ' AND s.sellername like "%'.$data['key'].'%"' : "";

            //日期
            $time              = $data['time'] ? explode(' - ', $data['time']) : '';
            $start = $time ? strtotime(date($time[0],time())) : 0;
            $end   = $time ? strtotime(date($time[1],time())) : 0;

            $where .= $data['time'] ? ' AND i.contributor_time BETWEEN '.$start.' AND '.$end : '';
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //卡券信息
            $rs = Db::query("SELECT
                                i.*,
                                s.sellername 
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS i
                                LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON i.sid = s.id 
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel as rel ON rel.sid = s.id
                            WHERE
                                i.`owner` = 2
                                AND i.STATUS IN (1,3)
                            ".$where.$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['contributor_time'] = date("Y年m月d日",$value['contributor_time']);
                switch ($value['status']) {
                    case '1':
                        $rs[$key]['status'] = '已投放';
                        break;
                    case '2':
                        $rs[$key]['status'] = '未投放';
                        break;
                    case '3':
                        $rs[$key]['status'] = '停止投放';
                        break;
                    default:
                        # code...
                        break;
                }
                //领取人数
                $receive_people_number = Db::query("SELECT
                                        count(
                                        DISTINCT ( openid )) AS total 
                                    FROM
                                        `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                    WHERE
                                        cid = ".$value['id']);
                $rs[$key]['receive_people_number'] = $receive_people_number[0]['total'];
                //领取次数
                $receive_frequency = Db::query("SELECT
                                    count(1) as total
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                WHERE
                                    cid = ".$value['id']);
                $rs[$key]['receive_frequency'] = $receive_frequency[0]['total'];
                //核销人数
                $write_off_people_number = Db::query("SELECT
                                    count(
                                        DISTINCT ( openid )) AS total 
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                WHERE
                                    status = 1 and cid = ".$value['id']);
                $rs[$key]['write_off_people_number'] = $write_off_people_number[0]['total'];
                //核销次数
                $write_off_people_frequency = Db::query("SELECT
                                    count(1) as total
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                WHERE
                                    status = 1 and cid = ".$value['id']);
                $rs[$key]['write_off_people_frequency'] = $write_off_people_frequency[0]['total'];
                //应收金额： 需要先查询出该卡券都在哪些账单中核销，并计算该卡券所牵扯账单的总的应收金额
                $amount_receivable = Db::query("SELECT
                                            SUM(should_price) as should_price,
                                            SUM(actual_price) as actual_price 
                                        FROM
                                            `xt-member-two-LINE`.xt_dc_dz_seller_flowing_log 
                                        WHERE
                                            rid IN (
                                            SELECT
                                                rid 
                                            FROM
                                                `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                            WHERE
                                                STATUS = 1
                                            AND  cid = ".$value['id'].")");
                $rs[$key]['amount_receivable'] = $amount_receivable[0]['should_price'];
                $rs[$key]['paid_in_amount']    = $amount_receivable[0]['actual_price'];
            }

            //获取总条数
            $total = Db::query("SELECT
                                i.*,
                                s.sellername 
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS i
                                LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON i.sid = s.id 
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel as rel ON rel.sid = s.id
                            WHERE
                                i.`owner` = 2
                                AND i.STATUS IN (1,3)
                            ".$where."");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','商家卡券信息统计');
        return $this->fetch();
    }

    /**
     * 商家卡券-数据统计-某个商家订单详情
     */
    public function sellercarddatalog(){
        $data = Request::except('file');
        if(Request::isAjax()){
            $where = '';
            //证明是需要根据名称搜索，忽略隐藏域商家ID
            $where .= ' AND s.id = '.$data['seller_id'].'';
            //如果flag==1 则是查看平台红包
            $where .= $data['flag']==1 ? ' AND i.owner = 1' : ' AND i.owner = 2';
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //订单信息
            $rs = Db::query("SELECT
                                r.id,
                                r.sid,
                                r.tableinfo,
                                r.ddate,
                                r.ddate_solt,
                                r.rname,
                                r.sex,
                                s.sellername,
                                data_rel.cid,
                                data_rel.oid 
                                FROM
                                    `xt-member-two-LINE`.xt_dc_reserve AS r
                                    LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON r.sid = s.id
                                    LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS data_rel ON data_rel.rid = r.id 
                                    LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_info AS i ON data_rel.cid = i.id 
                                WHERE
                                   data_rel.oid IS NOT NULL  and data_rel.status = 1".$where.$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['rname'] = $value['sex']==1 ? $value['rname'].'先生' : ($value['sex']==2 ? $value['rname'].'女士' : $value['rname']);
                $rs[$key]['ddate'] = date("Y年m月d日",$value['ddate']);
                if($value['tableinfo']){
                    $tableinfo = json_decode($value['tableinfo'],true);
                    $tableinfo = array_column($tableinfo, 'name');
                    $rs[$key]['tableinfo'] = implode('、', $tableinfo);
                }else{
                    $rs[$key]['tableinfo'] = '';
                }
                switch ($value['ddate_solt']) {
                    case '1':
                        $rs[$key]['ddate_solt'] = '早餐';
                        break;
                    case '2':
                        $rs[$key]['ddate_solt'] = '午餐';
                        break;
                    case '3':
                        $rs[$key]['ddate_solt'] = '晚餐';
                        break;
                    default:
                        # code...
                        break;
                }
                //查询账单所牵扯的卡券
                if($value['oid']) {
                    $card_title = Db::query("SELECT
                                    i.card_title
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_discount_act act
                                    LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_data_rel rel ON act.cid = rel.id
                                    LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_info i ON rel.cid = i.id 
                                WHERE
                                    orderid = ".$value['oid']);

                    $rs[$key]['card_title'] = implode("<br/>", array_column($card_title, 'card_title'));
                    //查询提前买单账单金额
                    $price = Db::query("SELECT
                                            should_price,
                                            actual_price 
                                        FROM
                                            `xt-member-two-LINE`.xt_dc_dz_seller_flowing_log 
                                        WHERE
                                            rid = ".$value['id']);
                    $rs[$key]['should_price'] = $price ? $price[0]['should_price'] : '';
                    $rs[$key]['actual_price'] = $price ? $price[0]['actual_price'] : '';
                }
            }

            //获取总条数
            $total = Db::query("SELECT
                                r.id,
                                r.sid,
                                r.tableinfo,
                                r.ddate,
                                r.ddate_solt,
                                r.rname,
                                r.sex,
                                s.sellername,
                                data_rel.cid,
                                data_rel.oid 
                                FROM
                                    `xt-member-two-LINE`.xt_dc_reserve AS r
                                    LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON r.sid = s.id
                                    LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS data_rel ON data_rel.rid = r.id 
                                    LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_info AS i ON data_rel.cid = i.id 
                                WHERE
                                   data_rel.oid IS NOT NULL and data_rel.status = 1 ".$where);
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $msgs = $data['flag'] == 1 ? '平台卡券信息统计' : '商家卡券信息统计';
        $this->assign ( 'sid',$data['id']);
        $this->assign ( 'flag',$data['flag']);
        $this->assign ( 'title',$msgs);
        return $this->fetch();
    }

    /**
     * 平台卡券-数据统计-统计列表
     */
    public function platcarddatalist(){
        if(Request::isAjax()){
            $data = Request::except('file');

            $where = '';
            //日期
            $time              = $data['time'] ? explode(' - ', $data['time']) : '';
            $start = $time ? strtotime(date($time[0],time())) : 0;
            $end   = $time ? strtotime(date($time[1],time())) : 0;

            $where .= $data['time'] ? ' AND i.contributor_time BETWEEN '.$start.' AND '.$end : '';
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //卡券信息
            $rs = Db::query("SELECT
                                i.*,
                                s.sellername 
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS i
                                LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON i.sid = s.id 
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel as rel ON rel.sid = s.id
                            WHERE
                                i.`owner` = 1
                                AND i.STATUS IN (1,3)
                            ".$where.$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                $rs[$key]['contributor_time'] = date("Y年m月d日",$value['contributor_time']);
                switch ($value['status']) {
                    case '1':
                        $rs[$key]['status'] = '已投放';
                        break;
                    case '2':
                        $rs[$key]['status'] = '未投放';
                        break;
                    case '3':
                        $rs[$key]['status'] = '停止投放';
                        break;
                    default:
                        # code...
                        break;
                }
                //领取人数
                $receive_people_number = Db::query("SELECT
                                        count(
                                        DISTINCT ( openid )) AS total 
                                    FROM
                                        `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                    WHERE
                                        cid = ".$value['id']);
                $rs[$key]['receive_people_number'] = $receive_people_number[0]['total'];
                //领取次数
                $receive_frequency = Db::query("SELECT
                                    count(1) as total
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                WHERE
                                    cid = ".$value['id']);
                $rs[$key]['receive_frequency'] = $receive_frequency[0]['total'];
                //核销人数
                $write_off_people_number = Db::query("SELECT
                                    count(
                                        DISTINCT ( openid )) AS total 
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                WHERE
                                    status = 1 and cid = ".$value['id']);
                $rs[$key]['write_off_people_number'] = $write_off_people_number[0]['total'];
                //核销次数
                $write_off_people_frequency = Db::query("SELECT
                                    count(1) as total
                                FROM
                                    `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                WHERE
                                    status = 1 and cid = ".$value['id']);
                $rs[$key]['write_off_people_frequency'] = $write_off_people_frequency[0]['total'];
                //应收金额： 需要先查询出该卡券都在哪些账单中核销，并计算该卡券所牵扯账单的总的应收金额
                $amount_receivable = Db::query("SELECT
                                            SUM(should_price) as should_price,
                                            SUM(actual_price) as actual_price 
                                        FROM
                                            `xt-member-two-LINE`.xt_dc_dz_seller_flowing_log 
                                        WHERE
                                            rid IN (
                                            SELECT
                                                rid 
                                            FROM
                                                `xt-member-two-LINE`.xt_dc_dz_card_data_rel 
                                            WHERE
                                                STATUS = 1
                                            AND  cid = ".$value['id'].")");
                $rs[$key]['amount_receivable'] = $amount_receivable[0]['should_price'];
                $rs[$key]['paid_in_amount']    = $amount_receivable[0]['actual_price'];
                //总金额：查询该卡券所牵扯的账单总金额
                $price = Db::query("SELECT
                                                sum(price) as price
                                            FROM
                                                `xt-member-two-LINE`.xt_dc_dz_advance_order 
                                            WHERE
                                                id IN (
                                                SELECT
                                                    orderid 
                                                FROM
                                                    `xt-member-two-LINE`.xt_dc_dz_discount_act 
                                            WHERE
                                                cid IN ( SELECT id FROM `xt-member-two-LINE`.xt_dc_dz_card_data_rel WHERE cid = ".$value['id']." ))");
                $rs[$key]['order_price_total'] = $price ? $price[0]['price'] : '';
            }

            //获取总条数
            $total = Db::query("SELECT
                                i.*,
                                s.sellername 
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info AS i
                                LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON i.sid = s.id 
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel as rel ON rel.sid = s.id
                            WHERE
                                i.`owner` = 1
                                AND i.STATUS IN (1,3)
                            ".$where."");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','平台卡券信息统计');
        return $this->fetch();
    }

    /**
     * 商家卡券-数据统计-统计列表
     */
    public function platcarddatalog(){
        if(Request::isAjax()){
            $data = Request::except('file');

            $where = '';
            $channel = $data['channel'] ? $data['channel'] : 1;

            $where .= ($channel && $channel==4) ? ' AND re.type_id in (1,2,3)' : " AND re.type_id in ($channel)";
            $where .= $data['key'] ? ' AND s.sellername like "%'.$data['key'].'%"' : "";

            //日期
            $time              = $data['time'] ? explode(' - ', $data['time']) : '';
            $start = $time ? strtotime(date($time[0],time())) : 0;
            $end   = $time ? strtotime(date($time[1],time())) : 0;

            $where .= $data['time'] ? ' AND i.contributor_time BETWEEN '.$start.' AND '.$end : '';
            
            $offset = ($data["page"] - 1) * $data["limit"];
            $limit  = " LIMIT $offset,".$data['limit'];

            //卡券信息
            $rs = Db::query("SELECT
                                i.id,
                                i.price,
                                o.sid,
                                o.sellername,
                                i.card_title,
                                rel.oid 
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS rel
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_info AS i ON rel.cid = i.id
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_advance_order AS o ON rel.oid = o.id
                                LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON o.sid = s.id
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.id 
                            WHERE
                                i.`owner` = 1 
                                AND rel.oid != 0 
                            ".$where."GROUP BY o.sid,i.id ".$limit);
            //格式化数据
            foreach ($rs as $key => $value) {
                //核销人数
                $write_off_people_number = Db::query("SELECT
                                            count(
                                            DISTINCT ( rel.openid )) AS total 
                                        FROM
                                            `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS rel
                                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_advance_order AS o ON rel.oid = o.id 
                                        WHERE
                                            rel.STATUS = 1 
                                            AND rel.cid = ".$value['id']." 
                                            AND o.sid = ".$value['sid']);
                $rs[$key]['write_off_people_number'] = $write_off_people_number[0]['total'];
                //核销次数
                $write_off_people_frequency = Db::query("SELECT
                                            count( 1 ) AS total 
                                        FROM
                                            `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS rel
                                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_advance_order AS o ON rel.oid = o.id 
                                        WHERE
                                            rel.STATUS = 1 
                                            AND rel.cid = ".$value['id']." 
                                            AND o.sid = ".$value['sid']);
                $rs[$key]['write_off_people_frequency'] = $write_off_people_frequency[0]['total'];
                //订单总金额/红包总金额 
                $card_order_price =  Db::query("SELECT
                                            SUM(o.price) as order_price,
                                            SUM(i.price) as card_price
                                        FROM
                                            `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS rel
                                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_advance_order AS o ON rel.oid = o.id 
                                            LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_info as i ON rel.cid = i.id
                                        WHERE
                                            rel.STATUS = 1 
                                            AND rel.cid = ".$value['id']." 
                                            AND o.sid = ".$value['sid']);
                $rs[$key]['order_price_total'] = $card_order_price[0]['order_price'];
                $rs[$key]['card_price_total']  = $card_order_price[0]['card_price'];
            }

            //获取总条数
            $total = Db::query("SELECT
                                i.id,
                                i.price,
                                o.sid,
                                o.sellername,
                                i.card_title,
                                rel.oid 
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_data_rel AS rel
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_info AS i ON rel.cid = i.id
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_advance_order AS o ON rel.oid = o.id
                                LEFT JOIN `xt-member-two-LINE`.xt_seller AS s ON o.sid = s.id
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_seller_type_rel AS re ON re.sid = s.id 
                            WHERE
                                i.`owner` = 1 
                                AND rel.oid != 0 
                            ".$where."GROUP BY o.sid,i.id ");
            return $result = ['code'=>0,'msg'=>'获取成功!','data'=>$rs,'count'=>count($total),'rel'=>1];
        }
        $this->assign ( 'title','平台卡券信息统计');
        return $this->fetch();
    }
}