\app\index\controllerFinance

<?php
// +----------------------------------------------------------------------
// | Copyright (c) 2017 http://www.sycit.cn
// +----------------------------------------------------------------------
// | Author: Peter.Zhang  <hyzwd@outlook.com>
// +----------------------------------------------------------------------
// | Date:   2017/9/20
// +----------------------------------------------------------------------
// | Title:  Finance.php
// +----------------------------------------------------------------------
namespace app\index\controller;

use app\index\model\Purchase;
use app\index\model\Finance AS FinanceModel;
use app\index\model\PurchaseOrders;
use think\Db;
use think\Request;

class Finance extends Common_base
{
    public function _initialize()
    {
        // 是否有权限
        IS_ROOT([1,4])  ? true : $this->error('没有权限');
        return parent::_initialize(); // TODO: Change the autogenerated stub
    }

    public function index() {
        //获取前6个月的时间戳：
        $qltime= strtotime(date("Y-m", strtotime("-6 month")) . "-01");
        //获取当前时间戳
        $dqtime= strtotime(date("Y-m-d H:i:s"));
        //获取当前年份
        $dateD = date("Y");
        //获取去年年份
        $dateL = date("Y", strtotime("-1 year"));

        //取得分类
        $sort = Db::name('finance_sort')->select();
        //统计区间总值
        $Sum = Db::name('finance')
            ->where('create_time','between time',[$qltime, $dqtime])
            ->sum('amount');
        //四舍五入
        $aSum = sprintf("%.2f", $Sum / 10000);
        $dArr = array();
        //遍历查询 收款项目
        foreach ($sort as $key=>$val) {
            $dArr[$key]['name'] = $val['sname'];
            $sort = Db::name('finance')->where('sort', $val['sid'])->where('create_time','between time',[$qltime, $dqtime])->sum('amount');
            if ($sort > 0) {
                $dArr[$key]['y'] = round($sort/$Sum * 100, 2); //计算百分比
            } else {
                $dArr[$key]['y'] = 0;
            }

        }
        // 获取今年的销售总额
        $amountD = Db::name('finance')->whereTime('create_time', 'year')->sum('amount');
        // 获取去年的销售总额
        $amountL = Db::name('finance')->whereTime('create_time', 'last year')->sum('amount');

        $assign = [
            'title'  => '概况',
            'qldate' => date("Y年m月", strtotime("-6 month")),
            'dqdate' => date("Y年m月"),
            'dArr'   => json_encode($dArr),
            'aSum'   => $aSum,
            'amountD'=> sprintf("%.2f", $amountD / 10000),
            'amountL'=> sprintf("%.2f", $amountL / 10000),
            'dateD'  => $dateD,
            'dateL'  => $dateL,
            'dateDAm'=> self::date_amount($dateD),//获取今年的每月销售总额
            'dateLAm'=> self::date_amount($dateL),//获取去年的每月销售总额
        ];
        $this->assign($assign);
        return $this->fetch();
        //p(json_encode(self::DateAmount($dateD)));
    }

    //显示订金收款类
    public function deposit() {
        $Request = Request::instance();
        $query = $Request->param(); // 分页查询传参数
        $m = $Request->param('m');
        $k = $Request->param('k');
        $purchase = new Purchase();
        if ($m == 'pnumber' && $k !=='') {
            $list = $purchase->scope('pnumber', $k)->paginate('', false, ['query' => $query ]);
        } else {
            $list = $purchase->where('affirm','gt', 0)->where('pshoudj', '=', 0)->paginate();
        }
        // 获取分页显示
        $page = $list->render();
        $assign = [
            'title' => '订金收款',
            'list' => $list,
            'page'  => $page,
            'empty' => '<tr><td colspan="10" align="center">当前条件没有查到数据</td></tr>',
        ];
        $this->assign($assign);
        return $this->fetch();
    }

    //出货收款
    public function balance() {
        $Request = Request::instance();
        $query = $Request->param(); // 分页查询传参数
        $m = $Request->param('m');
        $k = $Request->param('k');
        $purchase = new Purchase();
        if ($m == 'pnumber' && $k !=='') {
            $list = $purchase->scope('pnumber', $k)->paginate('', false, ['query' => $query ]);
        } else {
            $list = $purchase->where('affirm','gt', 0)->where('pshoudj', '=', 1)->where('status', '<', 5)->paginate();
        }

        // 获取分页显示
        $page = $list->render();
        $assign = [
            'title' => '出货收款',
            'list' => $list,
            'page'  => $page,
            'empty' => '<tr><td colspan="10" align="center">当前条件没有查到数据</td></tr>',
        ];
        $this->assign($assign);
        return $this->fetch();
    }

    //确认发货 list
    public function fahuo() {
        $Request = Request::instance();
        $query = $Request->param(); // 分页查询传参数
        $m = $Request->param('m');
        $k = $Request->param('k');
        $purchase = new Purchase();
        if ($m == 'pnumber' && $k !=='') {
            $list = $purchase->scope('pnumber', $k)->paginate('', false, ['query' => $query ]);

        } else {
            $list = $purchase->where('pshoudj','>',1)->where('status','<',5)->paginate();
        }

        // 获取分页显示
        $page = $list->render();
        $assign = [
            'title' => '确认发货',
            'list' => $list,
            'page' => $page,
            'empty' => '<tr><td colspan="8" align="center">当前条件没有查到数据</td></tr>',
        ];
        $this->assign($assign);
        return $this->fetch();
    }

    //订单收款
    public function skjilu() {
        $Request = Request::instance();
        $lime = '30';
        $query = $Request->param(); // 分页查询传参数
        $m = $Request->param('m');
        $k = $Request->param('k');
        //
        $FinanceModel = new FinanceModel();
        if ($m == 'pnumber' && $k !=='') {
            $list = $FinanceModel->scope('pnumber', $k)->order('shoukuan_time desc')->paginate($lime, false, ['query' => $query ]);
        } elseif ($m == 'sort' && $k !=='') {
            $list = $FinanceModel->where('sort','=', $k)->order('shoukuan_time desc')->paginate($lime, false, ['query' => $query ]);
        } elseif ($m == 'date' && $k !=='') {
            $date = explode("~", $k);
            $date_start = strtotime(date('Y-m-01', strtotime($date[0])));
            $date_end = strtotime(date('Y-m-d', strtotime("$date[1] +1 month -1 day")));
            if (!$date_start || !$date_end) {
                $this->error('日期格式错误');
            }
            $date_by = $date_end - $date_start;
            if ($date_by <= 0) {
                $this->error('日期格式错误');
            }

            $list = $FinanceModel->where('shoukuan_time', 'between', [$date_start, $date_end])->order('shoukuan_time desc')->paginate($lime);
        } else {
            $list = $FinanceModel->order('shoukuan_time desc')->paginate($lime);
        }

        //exit();
        // 获取分页显示
        $page = $list->render();

        $assign = [
            'title' => '订单收款',
            'list' => $list,
            'page' => $page,
            'empty' => '<tr><td colspan="8" align="center">当前条件没有查到数据</td></tr>',
        ];
        $this->assign($assign);
        return $this->fetch();
        //p($list);
    }

    //客户收款
    public function khmoney() {
        $Request = Request::instance();
        $lime = '30';
        $query = $Request->param(); // 分页查询传参数
        $m = $Request->param('m');
        $k = $Request->param('k');
        $assign = [
            'title' => '客户收款',
        ];
        $this->assign($assign);
        return $this->fetch();
    }

    //单号查询记录
    public function dialog_pnumber() {
        $Request = Request::instance();
        $m = $Request->param('m');
        $k = $Request->param('k');
        if ($m !== 'pnumber' || empty($k)) {
            $this->error('非法访问');
        }
        $Finance = new FinanceModel();
        $list = $Finance::where('fpnumber', $k)->select();
        if (empty($list)) {
            $this->error('非法访问');
        }
        //实收金额
        $sumAmount = $Finance::where('fpnumber', $k)->sum('amount');
        $Purchase = new Purchase();
        $PurchaseOrders = new PurchaseOrders();
        //客户信息
        $pcsname = $Purchase::where('pnumber', $k)->find();
        //实际金额
        $shiji = $PurchaseOrders::where('ord_pnumber', $k)->sum('amount');
        //优惠金额
        if (empty($pcsname['pamount'])) {
            $youhui = '数据有错';
        } else {
            $youhui = $shiji - $pcsname['pamount'];
        }

        $assign = [
            'pnumber' => $k,
            'list' => $list,
            'sumAmount' => $sumAmount,
            'pcsname' => $pcsname,
            'shiji' => $shiji,
            'youhui' => $youhui,
        ];
        $this->assign($assign);
        return $this->fetch();
    }

    //销售统计
    public function statistics() {
        $Request = Request::instance();
        $query = $Request->param(); // 分页查询传参数
        $m = $Request->param('m'); // 状态查询
        $k = $Request->param('k'); // 单号查询
        $q = $Request->param('q'); // 名称查询
        $Finance = new FinanceModel();
        $Purchase = new Purchase();
        $PurchaseOrders = new PurchaseOrders();

        $where1 = ['status'=>5];
        $field  = ['pnumber,pcsname,pyouhui,pamount,pcount,pstart_date,pend_date'];
        $lime = '20';
        //条件
        switch ($q) {
            //本周销售
            case 'week':
                //统计数量
                $pcount = $Purchase->where($where1)->whereTime('create_time','week')->count();
                //统计金额
                $psum = $Purchase->where($where1)->whereTime('create_time','week')->sum('pamount');
                //查询已出库订单
                $list = $Purchase->where($where1)->whereTime('create_time','week')->field($field)->paginate($lime, false, ['query' => $query ]);
                break;

            //本月销售
            case 'month':
                //统计数量
                $pcount = $Purchase->where($where1)->whereTime('create_time','month')->count();
                //统计金额
                $psum = $Purchase->where($where1)->whereTime('create_time','month')->sum('pamount');
                //查询已出库订单
                $list = $Purchase->where($where1)->whereTime('create_time','month')->field($field)->paginate($lime, false, ['query' => $query ]);
                break;

            //上月销售
            case 'lastmonth':
                //统计数量
                $pcount = $Purchase->where($where1)->whereTime('create_time','last month')->count();
                //统计金额
                $psum = $Purchase->where($where1)->whereTime('create_time','last month')->sum('pamount');
                //查询已出库订单
                $list = $Purchase->where($where1)->whereTime('create_time','last month')->field($field)->paginate($lime, false, ['query' => $query ]);
                break;

            //今年销售
            case 'year':
                //统计数量
                $pcount = $Purchase->where($where1)->whereTime('create_time','year')->count();
                //统计金额
                $psum = $Purchase->where($where1)->whereTime('create_time','year')->sum('pamount');
                //查询已出库订单
                $list = $Purchase->where($where1)->whereTime('create_time','year')->field($field)->paginate($lime, false, ['query' => $query ]);
                break;

            //去年销售
            case 'lastyear':
                //统计数量
                $pcount = $Purchase->where($where1)->whereTime('create_time','last year')->count();
                //统计金额
                $psum = $Purchase->where($where1)->whereTime('create_time','last year')->sum('pamount');
                //查询已出库订单
                $list = $Purchase->where($where1)->whereTime('create_time','last year')->field($field)->paginate($lime, false, ['query' => $query ]);
                break;

            //默认查询
            default:
                //统计数量
                $pcount = $Purchase->where($where1)->count();
                //统计金额
                $psum = $Purchase->where($where1)->sum('pamount');
                //查询已出库订单
                if ($m == 'pnumber' && $k !=='') {
                    //
                    $list = $Purchase->scope('pnumber', $k)->field($field)->paginate($lime, false, ['query' => $query ]);
                } else {
                    $list = $Purchase->where($where1)->field($field)->paginate($lime);
                }
                break;
        }

        //关联统计收款
        foreach ($list as $key=>$val) {
            //总收款
            $allamo = FinanceModel::where('fpnumber',$val['pnumber'])->sum('amount');
            $list[$key]['allamo'] = '￥'.number_format($allamo,2);
            //订单订金
            //$allamo = FinanceModel::where('fpnumber',$val['pnumber'])->where('sort',1)->sum('amount');
            //$list[$key]['sortd'] = '￥'.number_format($allamo,2);
            //订单余款
            //$allamo = FinanceModel::where('fpnumber',$val['pnumber'])->where('sort',2)->sum('amount');
            //$list[$key]['sorty'] = '￥'.number_format($allamo,2);
            //总订单金额
            //$allddje = PurchaseOrders::where('ord_pnumber',$val['pnumber'])->sum('amount');
            //$list[$key]['allddje'] = '￥'.number_format($allddje,2);
            //订单惠额
            //$yhje = ($allddje * 1) - ($val['pamount'] * 1);
            //$list[$key]['yhje'] = '￥'.number_format($yhje,2);
            //附表详情数量
            $list[$key]['count'] = PurchaseOrders::where('ord_pnumber',$val['pnumber'])->count();
            //附表详情
            $list[$key]['sun'] = PurchaseOrders::where('ord_pnumber',$val['pnumber'])->order('xuhao','asc')->select();
        }

        // 获取分页显示
        $page = $list->render();
        //
        $assign = [
            'title' => '销售统计',
            'list' => $list,
            'page' => $page,
            'psum' => '￥'.sprintf("%.2f", $psum / 10000).'万元',
            'pcount' => $pcount,
            'empty'  => '<tr><td colspan="18" align="center">当前条件没有查到数据</td></tr>'
        ];
        $this->assign($assign);
        return $this->fetch();
        //p($list);
    }

    //查询销售额，分每月
    public function date_amount($date='2017') {
        //循环当前查询的年份，每个月销售
        for ($i=1;$i<=12;$i++) {
            $dqy = $date.'-'.$i;  //月份
            $dqy1 = $dqy.'-01 0:0:0'; //月份第一天
            $dqy2 = $dqy.'-'. date('t',strtotime($dqy)) .' 23:59:59'; //月份最后一天
            $Am = Db::name('finance')
                ->where('create_time', '>=',strtotime($dqy1))
                ->where('create_time', '<=', strtotime($dqy2))
                ->sum('amount');
            $dateArr[] = sprintf("%.2f", $Am / 10000);
        }
        return join($dateArr,',');
    }

    //导出Excel 订单收款
    public function skjilu_excel() {
        $Request = Request::instance();
        $m = $Request->param('m');
        $k = $Request->param('k');

        $Finance = new FinanceModel();
        //访问模块 $excelData
        $Customers = new Customers();
        if ($m === 'pnumber') {
            //按编号导出
            $list = $Finance->scope('pnumber', $k)->order('shoukuan_time desc')->select();
            $headTitle = \think\Config::get('syc_webname')." - 订单收款(".$k.")";
            $title = "客户名称(".$k.")导出记录";
        } elseif ($m === 'sort') {
            //按收款类目导出
            $list = $Finance->where('sort','=', $k)->order('shoukuan_time desc')->select();
            $sort_name = Db::name('finance_sort')->where('sid', $k)->find();
            $headTitle = \think\Config::get('syc_webname')." - 订单收款(".$sort_name['sname'].")";
            $title = "类目(".$sort_name['sname'].")导出记录";
        } elseif ($m === 'date') {
            //日期分类导出
            $date = explode("~", $k);
            $date_start = strtotime(date('Y-m-01', strtotime($date[0])));
            $date_end = strtotime(date('Y-m-d', strtotime("$date[1] +1 month -1 day")));
            if (!$date_start || !$date_end) {
                $this->error('日期格式错误');
            }
            $date_by = $date_end - $date_start;
            if ($date_by <= 0) {
                $this->error('日期格式错误');
            }

            $list = $Finance->where('shoukuan_time', 'between', [$date_start, $date_end])->order('shoukuan_time desc')->select();
            $headTitle = \think\Config::get('syc_webname')." - 订单收款(".$k.")";
            $title = $k . "订单收款记录";
        } else {
            $list = $Finance::where('status','>=', 1)->order('shoukuan_time desc')->select();
            $headTitle = \think\Config::get('syc_webname')." - 订单收款";
            $title = "全部记录-".date('Y-m-d');
        }

        foreach ($list as $key=>$val) {
            $dataResult[$key]['pnumber'] = $val['fpnumber'];
            $dataResult[$key]['fcus_name'] = $val['fcus_name'];
            $dataResult[$key]['sort'] = $val['sort']['sname'];
            //$dataResult[$key]['amount'] = number_format($val['amount'],2);
            $dataResult[$key]['amount'] = $val['amount'];
            $dataResult[$key]['fuid'] = $val['fuid'];
            $dataResult[$key]['shoukuan_time'] = date('Y-m-d', $val['shoukuan_time']);
            $dataResult[$key]['schedule'] = $list[$key]->schedule->fs_remark;

        }

        $headtitle= '<thead><tr style="height:50px;border-style:none;"><th border="0" style="height:60px;width:270px;font-size:22px;" colspan="7">'.$headTitle.'</th></tr>';
        $titlename = "<tr style='height:30px;'> 
                       <th style='width:100px;'>销售单号</th> 
                       <th style='width:100px;'>客户名称</th> 
                       <th style='width:100px;'>收款类目</th> 
                       <th style='width:150px;'>收款金额</th> 
                       <th style='width:100px;'>收款人</th> 
                       <th style='width:100px;'>收款日期</th> 
                       <th style='width:200px;'>收款内容</th> 
                   </tr></thead>";
        $filename = $title.".xls";
        $Customers->excelData($dataResult,$titlename,$headtitle,$filename);
    }
}