<?php
namespace app\admin\controller;
use think\Db;
use think\facade\Request;
class Card extends Common
{
    /**
     * 商家卡券-优惠券-卡券列表
     */
    public function sellercard(){
        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']);
                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
                            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 carddetail(){
        //print_r(Request::except('file'));die;
        $data = Request::except('file');
        //获取运营人员列表
        $rs = Db::query("SELECT
                                car.*,
                                base.*
                            FROM
                                `xt-member-two-LINE`.xt_dc_dz_card_info as car
                                LEFT JOIN `xt-member-two-LINE`.xt_dc_dz_card_base as base
                                on car.bid = base.card_id
                            WHERE id = ".$data['id']."
                            ");
        switch ($rs[0]['status']) {
            case '1':
                $rs[0]['status_meg'] =  '已投放';
                break;
            case '2':
                $rs[0]['status_meg'] =  '未投放';
                break;
            case '3':
                $rs[0]['status_meg'] =  '停止投放';
                break;
            default:
                # code...
                break;
        }
        $rs[0]['start_time'] =  $rs[0]['start_time'] ? date("Y年m月d日",$rs[0]['start_time']) : '';
        $rs[0]['end_time']   =  $rs[0]['end_time'] ? date("Y年m月d日",$rs[0]['end_time']) : '';

        $rs[0]['receive_start_time'] =  $rs[0]['receive_start_time'] ? date("Y年m月d日",$rs[0]['receive_start_time']) : '';
        $rs[0]['receive_end_time']   =  $rs[0]['receive_end_time'] ? date("Y年m月d日",$rs[0]['receive_end_time']) : '';
        $this->assign ('rs',$rs[0]);
        $this->assign ( 'title','优惠券详情');
        return $this->fetch();
    }

    /**
     * 创建优惠券-选择商户
     */
    public function selectseller(){
        $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 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 add_card(){
        $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['bid']        = $data['card_id'];
            $arr['card_title'] = $data['title'];
            $arr['price']      = $data['price'];
            $arr['owner']      = $data['card_from_type'];
            $time              = $data['date'] ? explode(' - ', $data['date']) : '';
            $arr['start_time'] = $time ? strtotime(date($time[0],time())) : 0;
            $arr['end_time']   = $time ? strtotime(date($time[1],time())) : 0;
            $arr['create_time']= time();
            $arr['creator']    = $_SESSION['think']['username'];
            //有新加参数 需在此后追加。。。

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

            $res = Db::query("INSERT INTO `xt-member-two-LINE`.xt_dc_dz_card_info 
                ($key) 
                VALUES(".$arr['sid'].",'".$arr['sellername']."',".$arr['bid'].",'".$arr['card_title']."',".$arr['price'].",".$arr['owner'].",".$arr['start_time'].",".$arr['end_time'].",".$arr['create_time'].",'".$arr['creator']."')");

            if(!$res){
                $result = ['code'=>1,'msg'=>'添加成功!','rel'=>1];
            }else{
                $result = ['code'=>0,'msg'=>'添加失败!','rel'=>1];
            }
            return $result;
        }
        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(){
        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']);
                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
                            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();
    }
}