<?php
/**
 * Created by PhpStorm.
 * User: chouchou
 * Date: 2019-7-21
 * Time: 19:45
 */

namespace app\impexp\controller;


use app\admin\controller\Staff;
use app\admin\model\CopyModel;
use app\admin\model\DepartmentModel;
use app\admin\model\ElectricMeter;
use app\admin\model\StaffModel;
use app\station\model\Station;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\facade\Session;

/**
 * 导出模块
 * Class Export
 * @package app\impexp\controller
 */
class Export
{
    /**
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getExportData($ids){

        $data = CopyModel::alias('cp')
            ->field('cp.current_number,cp.current_time,cp.current_amount,cp.remark,em.number as em_number,emt.name as emt_name')
            ->field('statement.repo_power,statement.yd_unit,statement.financ_unit,statement.financ_pay,statement.recharge,statement.power_over,statement.remark as statement_remark')
            ->field('pw.name as pw_name')
            ->field('payment.check_numb,payment.invoice_numb,payment.pay_date,payment.tax_rate,payment.tax_amount')
            ->field('it.name as invoice_name')
            ->field('staff.name as staff_name,staff_pay.name as pay_name,staff_settle.name as settle_name')
            ->field('settle.amount,settle.settle_date,settle.invoice_numb as settle_invoice_numb,settle.client')
            ->field('CASE WHEN o.payment_status = "1" THEN "已缴费"  ELSE "未缴费" END AS pay_status,
            CASE WHEN o.settle_status = "1" THEN "已结算"  ELSE "未结算"  END AS sett_status')

            ->leftJoin(['statement'],'statement.copy_id=cp.id')
            ->leftJoin(['electric_meter' => 'em'],'em.id = cp.em_id')
            ->leftJoin(['electric_meter_type'=> 'emt'],'emt.id = em.emt_id')
            ->leftJoin(['payment'],'payment.cp_id=cp.id')
            ->leftJoin(['settle'],'settle.cp_id=cp.id')

            ->leftJoin(['staff'],'staff.id = cp.staff_id')
            ->leftJoin(['staff' => 'staff_pay'],'staff_pay.id = payment.pay_sid')
            ->leftJoin(['staff' => 'staff_settle'],'staff_settle.id = settle.settle_sid')
            ->leftJoin(['pay_way'=>'pw'],'pw.id=payment.pay_way')
            ->leftJoin(['invoice_type'=> 'it'],'it.id = payment.invoice_type')
            ->leftJoin(['order'=> 'o'],'o.cp_id = cp.id')
            ->leftJoin(['station_info'=>'s'],'s.station_id = em.station_id')
            ->where(function($query) use ($ids){
                if($ids != '')  $query->where('cp.em_id','in',$ids);
            })
            ->where(function($query){
                $map = Session::get('cp_map');
                if($map) {
                    $query->where($map);
                }
            })
            ->select();
        if($data) return $data->toArray();
    }

    public function ExportExl(){
        $user = Session::get('user');
        if(!$user['is_admin']){
            $dIds = (new DepartmentModel)->getDeparmentIds();

            $sIds = Station::field('station_id')->where('department_id','in',$dIds)->select()->toArray();

            $temp = [];
            if(is_array($sIds)){
                foreach ($sIds as $k => $v){
                    $temp [] = $v['station_id'];
                }
            }
            $sIds = implode(',',$temp); //查询所有基站ID


            $ems = ElectricMeter::field('id')->where('station_id','in',$sIds)->select()->toArray();

            $temp = [];
            if(is_array($ems)){
                foreach ($ems as $k => $v){
                    $temp [] = $v['id'];
                }
            }
            $ems = implode(',',$temp); //查询所有电表ID
        } else {
            $ems = CopyModel::field('em_id')->select()->toArray();
            $temp = [];
            if(is_array($ems)){
                foreach ($ems as $k => $v){
                    $temp [] = $v['em_id'];
                }
            }
            $ems = implode(',',$temp); //查询所有电表ID
        }
        $data = $this->getExportData($ems);
        $this->makeExcel($data,'抄表资料');
    }

    public function makeExcel($data,$name){
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $sheet->setCellValue('A1','抄表员');
            $sheet->setCellValue('B1','表号');
            $sheet->setCellValue('C1','当前表数');
            $sheet->setCellValue('D1','本次抄表日期');
            $sheet->setCellValue('E1','本期余额');
            $sheet->setCellValue('F1','抄表单备注');

            $sheet->setCellValue('G1','电表类型');
            $sheet->setCellValue('H1','报移动电量');
            $sheet->setCellValue('I1','报移动单价');
            $sheet->setCellValue('J1','财务单价');
            $sheet->setCellValue('K1','财务支出金额');
            $sheet->setCellValue('L1','本次充值金额');
            $sheet->setCellValue('M1','电费余额');

            $sheet->setCellValue('N1','账单备注');
            $sheet->setCellValue('O1','缴费单支票号');
            $sheet->setCellValue('P1','缴费发票号');
            $sheet->setCellValue('Q1','缴费员');
            $sheet->setCellValue('R1','缴费日期');


            $sheet->setCellValue('S1','税率');
            $sheet->setCellValue('T1','缴费方式');
            $sheet->setCellValue('U1','发票类型');
            $sheet->setCellValue('V1','结算客户');
            $sheet->setCellValue('W1','结算金额');
            $sheet->setCellValue('X1','结算日期');
            $sheet->setCellValue('Y1','结算发票号');
            $sheet->setCellValue('Z1','结算人');
            $sheet->setCellValue('AA1','税额');

            $sheet->setCellValue('AB1','缴费情况');
            $sheet->setCellValue('AC1','结算状态');




            for ($i = 0; $i < count($data); $i++) {
                $sheet->setCellValue('A'.($i+2),$data[$i]['staff_name']);
                $sheet->setCellValue('B'.($i+2),$data[$i]['em_number']);
                $sheet->setCellValue('C'.($i+2),$data[$i]['current_number']);
                $sheet->setCellValue('D'.($i+2),$data[$i]['current_time']);
                $sheet->setCellValue('E'.($i+2),$data[$i]['current_amount']);
                $sheet->setCellValue('F'.($i+2),$data[$i]['remark']);
                $sheet->setCellValue('G'.($i+2),$data[$i]['emt_name']);
                $sheet->setCellValue('H'.($i+2),$data[$i]['repo_power']);
                $sheet->setCellValue('I'.($i+2),$data[$i]['yd_unit']);
                $sheet->setCellValue('J'.($i+2),$data[$i]['financ_unit']);
                $sheet->setCellValue('K'.($i+2),$data[$i]['financ_pay']);
                $sheet->setCellValue('L'.($i+2),$data[$i]['recharge']);
                $sheet->setCellValue('M'.($i+2),$data[$i]['power_over']);
                $sheet->setCellValue('N'.($i+2),$data[$i]['statement_remark']);

                $sheet->setCellValue('O'.($i+2),$data[$i]['check_numb']);
                $sheet->setCellValue('P'.($i+2),$data[$i]['invoice_numb']);
                $sheet->setCellValue('Q'.($i+2),$data[$i]['pay_name']);
                $sheet->setCellValue('R'.($i+2),date('Y-m-d',$data[$i]['pay_date']));

                $sheet->setCellValue('S'.($i+2),$data[$i]['tax_rate']);
                $sheet->setCellValue('T'.($i+2),$data[$i]['pw_name']);
                $sheet->setCellValue('U'.($i+2),$data[$i]['invoice_name']);

                $sheet->setCellValue('V'.($i+2),$data[$i]['client']);

                $sheet->setCellValue('W'.($i+2),$data[$i]['amount']);
                $sheet->setCellValue('X'.($i+2),date('Y-m-d',$data[$i]['settle_date']));
                $sheet->setCellValue('Y'.($i+2),$data[$i]['settle_invoice_numb']);
                $sheet->setCellValue('Z'.($i+2),$data[$i]['settle_name']);

                $sheet->setCellValue('AA'.($i+2),$data[$i]['tax_amount']);
                $sheet->setCellValue('AB'.($i+2),$data[$i]['pay_status']);
                $sheet->setCellValue('AC'.($i+2),$data[$i]['sett_status']);



            }

            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$name.'.xlsx"');
            header('Cache-Control: max-age=0');
            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

            ob_end_clean();
            $writer->save('php://output');
            exit;
        }

}