package com.winsun.controller;

import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.winsun.auth.core.annotion.Permission;
import com.winsun.auth.core.base.controller.BaseController;
import com.winsun.auth.core.common.model.ResponseData;
import com.winsun.auth.core.shiro.ShiroUser;
import com.winsun.auth.core.util.IOUtils;
import com.winsun.bean.Bill;
import com.winsun.bean.HhrUser;
import com.winsun.bean.School;
import com.winsun.bean.SysUser;
import com.winsun.mapper.BillMapper;
import com.winsun.mapper.HhrUserMapper;
import com.winsun.mapper.SchoolMapper;
import com.winsun.mapper.SysUserMapper;
import com.winsun.utils.ExcelDealUtils;
import com.winsun.utils.ImportExcel;
import com.winsun.utils.MapUtil;

import lombok.extern.slf4j.Slf4j;


/**
 * 账单管理
 * @author Warden
 *
 */
@Slf4j
@RestController
@RequestMapping("/bill")
public class BillController extends BaseController{
	
	private static BillMapper billMapper;
	private static SysUserMapper sysUserMapper;
	private static HhrUserMapper hhrUserMapper;
	private static SchoolMapper schoolMapper;
	
    /**
     * 导入
     */
    private static final int HEADER_NUM = -1;
    private static final int SHEET_INDEX = 0;
    
	private static String E_SORT = "序号";
	private static String E_MONTH = "月份";
	private static String E_BILL_TYPE = "项目类型";
	private static String E_BILL_DETAIL = "收支详情";
	private static String E_SUBST_NAME = "县分";
	private static String E_SCHOOL_NAME = "学校";
	private static String E_CAMPUS = "校区";
	private static String E_NETWORK_NAME = "网点名称";
	private static String E_NETWORK_CODE = "网点编码";
	private static String E_AMOUNT = "金额";
	private static String E_TAX_POINT = "税点";
	
	@Autowired
	public BillController(BillMapper billMapper,SysUserMapper sysUserMapper,HhrUserMapper hhrUserMapper,SchoolMapper schoolMapper) {
		BillController.billMapper = billMapper;
		BillController.sysUserMapper = sysUserMapper;
		BillController.hhrUserMapper = hhrUserMapper;
		BillController.schoolMapper = schoolMapper;
	}
	
	@Permission(menuname = "获取账单清单", value ="/list", method = RequestMethod.POST)
	public ResponseData<Page<Bill>> getList(@Param("substName") String substName,
			@Param("schoolName") String schoolName,@Param("billType") String billType,@Param("billDetail") String billDetail,
			@Param("month") String month,@Param("pageNo") Integer pageNo,@Param("pageSize") Integer pageSize){
		Page<Bill> page = new Page<>(pageNo,pageSize);
		Wrapper<Bill> wrapper = new EntityWrapper<Bill>();
		wrapper.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
		wrapper.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
		wrapper.eq(StringUtils.isNoneBlank(billType), "bill_type", billType);
		wrapper.eq(StringUtils.isNoneBlank(billDetail), "bill_detail", billDetail);
		wrapper.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapper.eq("del_flag", 0);
		
		List<Bill> billList = billMapper.selectPage(page, wrapper);
		page.setRecords(billList);
		
		return ResponseData.success(page, "查询成功");
	}
	
	@Permission(menuname = "删除账单", value = "/deleteBill", method = RequestMethod.POST)
	public ResponseData<Object> deleteBill(@Param("id") String id){
		Bill bill = new Bill();
		bill.setId(id);
		bill.setDelFlag(1);
		
		int num = billMapper.updateById(bill);
		if(num > 0) {
			return ResponseData.success("删除成功！");
		}
		
		return ResponseData.error("删除失败");
	}
	
	@Permission(menuname = "导出账单清单", value = "exportList", method = RequestMethod.POST)
    public void exportList(@Param("substName") String substName,
			@Param("schoolName") String schoolName,@Param("billType") String billType,
			@Param("billDetail") String billDetail,@Param("month") String month
    		) throws IOException {

		Wrapper<Bill> wrapper = new EntityWrapper<Bill>();
		wrapper.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
		wrapper.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
		wrapper.eq(StringUtils.isNoneBlank(billType), "bill_type", billType);
		wrapper.eq(StringUtils.isNoneBlank(billDetail), "bill_detail", billDetail);
		wrapper.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapper.eq("del_flag", 0);
		
		List<Map<String, Object>> list = billMapper.selectMaps(wrapper);

        ServletOutputStream os = null;
        XSSFWorkbook workbook = ExcelDealUtils.getWorkBook(ExcelDealUtils.billExcel("账单清单"), list);
        try {

            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            log.error("文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
    }
	
	@Permission(menuname = "账单导入模板", method = RequestMethod.POST, value = "downloadTemplate")
    public ResponseData<String> downloadTemplate() {
        ArrayList<String> columns = new ArrayList<>();
        columns.add(E_SORT);
        columns.add(E_MONTH);
        columns.add(E_BILL_TYPE);
        columns.add(E_BILL_DETAIL);
        columns.add(E_SUBST_NAME);
        columns.add(E_SCHOOL_NAME);
        columns.add(E_CAMPUS);
        columns.add(E_NETWORK_NAME);
        columns.add(E_NETWORK_CODE);
        columns.add(E_AMOUNT);
        columns.add(E_TAX_POINT);
        ServletOutputStream os = null;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet("账单导入模板");
            
            //添加表头
            final XSSFRow row = sheet.createRow(0);
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            columns.forEach(val -> {
                XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
                cell.setCellValue(val);
            });
            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;fileName=data.xlsx");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            log.error("账单导入模板文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
        return ResponseData.error("账单导入模板出错");
    }
	
    @Permission(menuname = "上传账单数据", value = "importBill", method = RequestMethod.POST)
    public ResponseData<String> importOrder(@RequestParam(name = "file") MultipartFile file) {
        StringBuffer info = new StringBuffer();
        if (!StringUtils.endsWithAny(file.getOriginalFilename(), "xlsx", "xls")) {
            return ResponseData.error("手工上传数据仅支持Excel文件，其他格式不支持！");
        }
        ShiroUser user = getShiroUser();
        // 当前是否有数据权限
        boolean hasDataPermission = user.getRoleNames().stream().anyMatch(data -> StringUtils.equalsAny(data, "活动上单员", "数据管理员", "超级管理员"));
        if (!hasDataPermission) {
            return ResponseData.error("无数据权限！");
        }
        ImportExcel importExcel = null;
        try {
            importExcel = new ImportExcel(file, HEADER_NUM, SHEET_INDEX);
        } catch (Exception e) {
            log.error("受理单导入异常", e.getMessage());
            return ResponseData.error("导入异常！");
        }
        List<Map<String, Object>> listMap = importExcel.getDataListMap();
        if (listMap.size() == 1) {
            return ResponseData.error("文件不能为空");
        }

        if (!listMap.get(0).get("c").toString().equals(E_BILL_TYPE)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("d").toString().equals(E_BILL_DETAIL)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("j").toString().equals(E_AMOUNT)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        if (!listMap.get(0).get("k").toString().equals(E_TAX_POINT)) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }

        if (listMap.get(0).size() != 11) {
            return ResponseData.error("模板不对，请选择正确的模板！");
        }
        //校验数据
        try {
            //校验数据
            for (int i = 1; i < listMap.size(); i++) {
                Map<String, Object> temp = listMap.get(i);

                if (!temp.containsKey("c")) {
                    return ResponseData.error("第" + (i + 1) + "行的项目类型不能为空");
                }
                if (StringUtils.isBlank(temp.get("d").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的收支详情不能为空");
                }
                if (StringUtils.isBlank(temp.get("j").toString())) {
                    return ResponseData.error("第" + (i + 1) + "行的金额不能为空");
                }
            }
        } catch (Exception e) {
            log.error("放号清单导入异常" + e.getMessage(), e);
            return ResponseData.error("导入异常！");
        }
        
        // 去掉首行标题
        listMap.remove(0);
        int num = dealUploadData(listMap);
        info.append("导入" + num+ "条数据！");
 
        
        return ResponseData.success(info.toString(), "导入完成");
    }
    
    public int dealUploadData(List<Map<String,Object>> list){
    	int num = 0;
    	List<Map<String, Object>> insertList = new ArrayList<Map<String,Object>>();
    	List<Map<String, Object>> phoneSchoolList = new ArrayList<Map<String,Object>>();

    	
    	for(Map<String, Object> map : list) {
    		String month = (String)map.get("b");
    		String billType = (String)map.get("c");
    		String billDetail = (String)map.get("d");
    		String substName = (String)map.get("e");
    		String schoolName = (String)map.get("f");
    		String campus = (String)map.get("g");
    		String networkName = (String)map.get("h");
    		String networkCode = (String)map.get("i");
    		double amount = Double.parseDouble((String)map.get("j"));
    		Bill bill = new Bill();
    		
    		if(StringUtils.isNoneBlank((String)map.get("k"))) {
        		double taxPoint = Double.parseDouble((String)map.get("k"));
        		double taxAmount = amount / taxPoint ;
        		bill.setTaxPoint(taxPoint);
        		bill.setTaxAmount(taxAmount);;
    		}
    		
    		bill.setMonth(month);
    		bill.setBillType(billType);
    		bill.setBillDetail(billDetail);
    		bill.setSubstName(substName);
    		bill.setSchoolName(schoolName);
    		bill.setCampus(campus);
    		bill.setNetworkName(networkName);
    		bill.setNetworkCode(networkCode);
    		bill.setAmount(amount);
			
    		num += billMapper.insert(bill);
    	}
    	
    	return num;
    }
    
    @Permission(menuname = "获取账单汇总", value ="/statisList", method = RequestMethod.POST)
	public ResponseData<Page<Map<String, Object>>> statisList(@Param("month") String month,@Param("statisType") String statisType,
			@Param("substName") String substName,@Param("schoolName") String schoolName,
			@Param("expenseType") String expenseType,
			@Param("pageNo") Integer pageNo,@Param("pageSize") Integer pageSize){
    	
		Page<Map<String, Object>> page = new Page<>(pageNo,pageSize);
		// 支出部分
		String billType = "佣金入账";
		Wrapper<Bill> wrapperIncome = new EntityWrapper<Bill>();
		wrapperIncome.setSqlSelect(" month,subst_name substName,school_name schoolName,bill_type billType,bill_detail billDetail,round(sum(amount),2) amount,round(sum(tax_amount),2) taxAmount ");
		wrapperIncome.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperIncome.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
		wrapperIncome.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
		wrapperIncome.eq("bill_type", billType);
		wrapperIncome.eq("del_flag", 0);
		if("substName".equals(statisType)) {
			wrapperIncome.groupBy("subst_name").groupBy("bill_detail").groupBy("month");
			wrapperIncome.orderBy("subst_name", true).orderBy("bill_detail", true).orderBy("month", false);
		}else if("schoolName".equals(statisType)) {
			wrapperIncome.groupBy("subst_name").groupBy("school_name").groupBy("bill_detail").groupBy("month");
			wrapperIncome.orderBy("subst_name", true).orderBy("school_name", true).orderBy("bill_detail", true).orderBy("month", false);
		}else {
			wrapperIncome.groupBy("month").groupBy("bill_detail");
			wrapperIncome.orderBy("bill_type", true).orderBy("bill_detail", true).orderBy("month", false);
		}

		List<Map<String, Object>> incomeBillList = billMapper.selectMaps(wrapperIncome);
		
		Wrapper<Bill> wrapperIncomeSum = new EntityWrapper<Bill>();
		wrapperIncomeSum.setSqlSelect(" sum(amount) amount,sum(tax_amount) tax_amount ");
		wrapperIncomeSum.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperIncomeSum.eq(StringUtils.isNoneBlank(billType), "bill_type", billType);
		wrapperIncomeSum.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
		wrapperIncomeSum.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
		Map<String, Object> incomeSumMap = billMapper.selectMaps(wrapperIncomeSum).get(0);
		
		// 支出部分
		billType = expenseType.indexOf("成本") > -1 ? "佣金支出,外包成本,营销成本" : "佣金支出" ;
		Wrapper<Bill> wrapperExpense = new EntityWrapper<Bill>();
		wrapperExpense.setSqlSelect(" month,subst_name substName,school_name schoolName,bill_type billType,bill_detail billDetail,round(sum(amount),2) amount,round(sum(tax_amount),2) taxAmount ");
		wrapperExpense.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperExpense.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
		wrapperExpense.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
		wrapperExpense.in(StringUtils.isNoneBlank(billType), "bill_type", billType);
		wrapperExpense.eq("del_flag", 0);
		if("substName".equals(statisType)) {
			wrapperExpense.groupBy("subst_name").groupBy("bill_detail").groupBy("month");
			wrapperExpense.orderBy("subst_name", true).orderBy("bill_detail", true).orderBy("month", false);
		}else if("schoolName".equals(statisType)) {
			wrapperExpense.groupBy("subst_name").groupBy("school_name").groupBy("bill_detail").groupBy("month");
			wrapperExpense.orderBy("subst_name", true).orderBy("school_name", true).orderBy("bill_detail", true).orderBy("month", false);
		}else {
			wrapperExpense.groupBy("month").groupBy("bill_detail");
			wrapperExpense.orderBy("bill_type", true).orderBy("bill_detail", true).orderBy("month", false);
		}
		List<Map<String, Object>> expenseBillList = billMapper.selectMaps(wrapperExpense);
		
		Wrapper<Bill> wrapperExpenseSum = new EntityWrapper<Bill>();
		wrapperExpenseSum.setSqlSelect(" sum(amount) amount,sum(tax_amount) tax_amount ");
		wrapperExpenseSum.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperExpenseSum.in(StringUtils.isNoneBlank(billType), "bill_type", billType);
		wrapperExpenseSum.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
		wrapperExpenseSum.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
		Map<String, Object> expenseSumMap = billMapper.selectMaps(wrapperExpenseSum).get(0);
		
		String incomeSum = "0";
		String expenseSum = "0";
		String taxAmount = "0";
		DecimalFormat df = new DecimalFormat("#.00");
		if(null != incomeSumMap){
			incomeSum = df.format((Double)incomeSumMap.get("amount"));
		}
		if(null != expenseSumMap){
			expenseSum = df.format((Double)expenseSumMap.get("amount"));
			taxAmount = df.format((Double)expenseSumMap.get("tax_amount"));
		}
		
		Map<String, Object> firstMap = new HashMap<>();
		firstMap.put("month", "总计");
		firstMap.put("substName","");
		firstMap.put("schoolName", "");
		firstMap.put("target", statisType);
		firstMap.put("billType", "");
		firstMap.put("billDetail", "");
		firstMap.put("amount", incomeSum);
		firstMap.put("billTypeExpense", "");
		firstMap.put("billDetailExpense", "");
		firstMap.put("amountExpense", expenseSum);
		firstMap.put("taxAmount", taxAmount);
		
		List<Map<String, Object>> imcomeList = addGroupSum(incomeBillList,statisType);
		List<Map<String, Object>> expenseList = addGroupSum(expenseBillList,statisType);
		
		List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
		if("substName".equals(statisType) || "schoolName".equals(statisType)) {
			dataList = dealDataOther(imcomeList,expenseList,statisType);
		}else {
			dataList = dealData(imcomeList,expenseList,statisType);
		}
		dataList.add(0,firstMap);
		
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		if(dataList.size() < pageNo*pageSize) {
			resultList = dataList.subList((pageNo-1)*pageSize, dataList.size());
		}else {
			resultList = dataList.subList((pageNo-1)*pageSize, pageNo*pageSize);
		}
		
		page.setRecords(resultList);
		page.setTotal(dataList.size());
		
		return ResponseData.success(page, "查询成功");
	}
    
    @Permission(menuname = "导出账单汇总清单", value = "exportStatisList", method = RequestMethod.POST)
    public void exportStatisList(@Param("month") String month,@Param("statisType") String statisType,
			@Param("dataType") String dataType,@Param("substName") String substName,@Param("schoolName") String schoolName,
			@Param("expenseType") String expenseType
    		) throws IOException {

    	// 支出部分
		String billType = "佣金入账";
		Wrapper<Bill> wrapperIncome = new EntityWrapper<Bill>();
		wrapperIncome.setSqlSelect(" month,school_name schoolName,bill_type billType,bill_detail billDetail,round(sum(amount),2) amount,round(sum(tax_amount),2) taxAmount ");
		wrapperIncome.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperIncome.eq("bill_type", billType);
		wrapperIncome.eq("del_flag", 0);
		wrapperIncome.groupBy("month").groupBy("bill_detail");
		wrapperIncome.orderBy("bill_type", true).orderBy("bill_detail", true).orderBy("month", false);
		List<Map<String, Object>> incomeBillList = billMapper.selectMaps(wrapperIncome);
		
		Wrapper<Bill> wrapperIncomeSum = new EntityWrapper<Bill>();
		wrapperIncomeSum.setSqlSelect(" sum(amount) amount,sum(tax_amount) tax_amount ");
		wrapperIncomeSum.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperIncomeSum.eq(StringUtils.isNoneBlank(billType), "bill_type", billType);
		Map<String, Object> incomeSumMap = billMapper.selectMaps(wrapperIncomeSum).get(0);
		
		// 支出部分
		billType = "佣金部分".equals(statisType) ? "佣金支出" : "佣金支出,外包成本,营销成本";
		Wrapper<Bill> wrapperExpense = new EntityWrapper<Bill>();
		wrapperExpense.setSqlSelect(" month,school_name schoolName,bill_type billType,bill_detail billDetail,round(sum(amount),2) amount,round(sum(tax_amount),2) taxAmount ");
		wrapperExpense.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperExpense.in("bill_type", billType);
		wrapperExpense.eq("del_flag", 0);
		wrapperExpense.groupBy("month").groupBy("bill_detail");
		wrapperExpense.orderBy("bill_type", true).orderBy("bill_detail", true).orderBy("month", false);
		List<Map<String, Object>> expenseBillList = billMapper.selectMaps(wrapperExpense);
		
		Wrapper<Bill> wrapperExpenseSum = new EntityWrapper<Bill>();
		wrapperExpenseSum.setSqlSelect(" sum(amount) amount,sum(tax_amount) tax_amount ");
		wrapperExpenseSum.eq(StringUtils.isNoneBlank(month), "month", month);
		wrapperExpenseSum.in("bill_type", billType);
		Map<String, Object> expenseSumMap = billMapper.selectMaps(wrapperExpenseSum).get(0);
		
		String incomeSum = "0";
		String expenseSum = "0";
		String taxAmount = "0";
		DecimalFormat df = new DecimalFormat("#.00");
		if(null != incomeSumMap.get("amount")){
			incomeSum = df.format((Double)incomeSumMap.get("amount"));
		}
		if(null != expenseSumMap.get("amount")){
			expenseSum = df.format((Double)expenseSumMap.get("amount"));
			taxAmount = df.format((Double)expenseSumMap.get("tax_amount"));
		}
		
		Map<String, Object> firstMap = new HashMap<>();
		firstMap.put("month", "总计");
		firstMap.put("county","");
		firstMap.put("school", "");
		firstMap.put("target", dataType);
		firstMap.put("billType", "");
		firstMap.put("billDetail", "");
		firstMap.put("amount", incomeSum);
		firstMap.put("billTypeExpense", "");
		firstMap.put("billDetailExpense", "");
		firstMap.put("amountExpense", expenseSum);
		firstMap.put("taxAmount", taxAmount);
		
		List<Map<String, Object>> imcomeList = addGroupSum(incomeBillList,"");
		List<Map<String, Object>> expenseList = addGroupSum(expenseBillList,"");
		List<Map<String, Object>> dataList = dealData(imcomeList,expenseList,dataType);
		dataList.add(0,firstMap);

        ServletOutputStream os = null;
        XSSFWorkbook workbook = ExcelDealUtils.getWorkBook(ExcelDealUtils.billStatisExcel("账单汇总清单"), dataList);
        try {

            HttpServletResponse response = getHttpServletResponse();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            log.error("文件出错！" + e.getMessage(), e);
        } finally {
            IOUtils.closeQuite(workbook, os);
        }
    }
    
    @Permission(menuname = "获取账单汇总(总经理)", value ="/statisManagerList", method = RequestMethod.POST)
   	public ResponseData<Page<Map<String, Object>>> statisManagerList(@Param("statisType") String statisType,
			@Param("expenseType") String expenseType,@Param("userName") String name,
			@Param("pageNo") Integer pageNo,@Param("pageSize") Integer pageSize) throws ParseException{
       	
    	Page<Map<String, Object>> page = new Page<>(pageNo,pageSize);
    	statisType = "userName";
		// 支出部分
		String billType = "佣金入账";
		Wrapper<Bill> wrapperIncome = new EntityWrapper<Bill>();
		wrapperIncome.setSqlSelect(" month,subst_name substName,school_name schoolName,bill_type billType,bill_detail billDetail,round(sum(amount),2) amount,round(sum(tax_amount),2) taxAmount ");
		wrapperIncome.eq("bill_type", billType);
		wrapperIncome.eq("del_flag", 0);
		wrapperIncome.groupBy("subst_name").groupBy("school_name").groupBy("bill_detail").groupBy("month");
		wrapperIncome.orderBy("subst_name", true).orderBy("school_name", true).orderBy("bill_detail", true).orderBy("month", false);

		List<Map<String, Object>> incomeBillList = billMapper.selectMaps(wrapperIncome);
		
//		Wrapper<Bill> wrapperIncomeSum = new EntityWrapper<Bill>();
//		wrapperIncomeSum.setSqlSelect(" sum(amount) amount,sum(tax_amount) tax_amount ");
//		wrapperIncomeSum.eq(StringUtils.isNoneBlank(billType), "bill_type", billType);
//		wrapperIncomeSum.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
//		wrapperIncomeSum.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
//		Map<String, Object> incomeSumMap = billMapper.selectMaps(wrapperIncomeSum).get(0);
		
		// 支出部分
		billType = expenseType.indexOf("成本") > -1 ? "佣金支出,外包成本,营销成本" : "佣金支出" ;
		Wrapper<Bill> wrapperExpense = new EntityWrapper<Bill>();
		wrapperExpense.setSqlSelect(" month,subst_name substName,school_name schoolName,bill_type billType,bill_detail billDetail,round(sum(amount),2) amount,round(sum(tax_amount),2) taxAmount ");
		wrapperExpense.in(StringUtils.isNoneBlank(billType), "bill_type", billType);
		wrapperExpense.eq("del_flag", 0);
		wrapperExpense.groupBy("subst_name").groupBy("school_name").groupBy("bill_detail").groupBy("month");
		wrapperExpense.orderBy("subst_name", true).orderBy("school_name", true).orderBy("bill_detail", true).orderBy("month", false);
	
		List<Map<String, Object>> expenseBillList = billMapper.selectMaps(wrapperExpense);
		
//		Wrapper<Bill> wrapperExpenseSum = new EntityWrapper<Bill>();
//		wrapperExpenseSum.setSqlSelect(" sum(amount) amount,sum(tax_amount) tax_amount ");
//		wrapperExpenseSum.eq(StringUtils.isNoneBlank(month), "month", month);
//		wrapperExpenseSum.in(StringUtils.isNoneBlank(billType), "bill_type", billType);
//		wrapperExpenseSum.eq(StringUtils.isNoneBlank(substName), "subst_name", substName);
//		wrapperExpenseSum.eq(StringUtils.isNoneBlank(schoolName), "school_name", schoolName);
//		Map<String, Object> expenseSumMap = billMapper.selectMaps(wrapperExpenseSum).get(0);
		
		Wrapper<School> wrapperSchool = new EntityWrapper<School>();
   		wrapperSchool.setSqlSelect(" school_name schoolName,id ");
   		wrapperSchool.eq("status", "1");
   		Map<String, Object> schoolMaps = MapUtil.listToMap(schoolMapper.selectMaps(wrapperSchool), "id", "schoolName");
   		//List<Map<String, Object>> schoolList = schoolMapper.selectMaps(wrapperSchool);
   		List<Map<String, Object>> schoolRefList = schoolMapper.getSupervisorSchoolRef();
   		//Map<String, Object> schoolRefMap = MapUtil.listToMap(schoolMapper.getSupervisorSchoolRef(), "user_id", "school");
   		
   		Wrapper<SysUser> wrapperSys = new EntityWrapper<SysUser>();
   		wrapperSys.setSqlSelect(" id,name ");
   		wrapperSys.eq("status", "1");
   		wrapperSys.eq(StringUtils.isNotBlank(name), "name", name);
   		//List<Map<String, Object>> sysUserList = sysUserMapper.selectMaps(wrapperSys);
   		Map<String, Object> sysMaps = MapUtil.listToMap(sysUserMapper.selectMaps(wrapperSys), "id", "name");
   		
   		Wrapper<HhrUser> wrapperHhrUser = new EntityWrapper<HhrUser>();
   		wrapperHhrUser.setSqlSelect(" id ");
   		wrapperHhrUser.eq("position", "督导");
   		List<Map<String, Object>> hhrUserList = hhrUserMapper.selectMaps(wrapperHhrUser);
   		//Map<String, Object> hhrUserMap = MapUtil.listToMap(hhrUserMapper.selectMaps(wrapperHhrUser), "id", "id");
   		List<Map<String, Object>> managerSchoolList = new ArrayList<Map<String,Object>>();
   		
   		
   		for(Map<String, Object> map : hhrUserList) {
   			if(null != sysMaps.get(map.get("id"))) {
   				int userId = Integer.parseInt(map.get("id").toString());
   				String userName = sysMaps.get(map.get("id")).toString();
   				
   				for(Map<String, Object> schoolRefMap : schoolRefList) {
   					if((int)schoolRefMap.get("user_id") == userId ) {
   						String schoolId = schoolRefMap.get("school").toString();
   						String schoolName = schoolMaps.get(schoolId).toString();
   						Map<String, Object> managerSchoolMap = new HashMap<String, Object>();
   						managerSchoolMap.put("schoolName", schoolName);
   						managerSchoolMap.put("userName", userName);
   						managerSchoolList.add(managerSchoolMap);
   					}
   				}
   			}
   		}
   		
		double incomeSum = 0;
		double expenseSum = 0;
		double taxAmount = 0;
		DecimalFormat df = new DecimalFormat("#.00");
		List<Map<String, Object>> imcomeList = new ArrayList<Map<String,Object>>();
		List<Map<String, Object>> expenseList = new ArrayList<Map<String,Object>>();
		
   		Map<String, Object> managerSchoolMaps = MapUtil.listToMap(managerSchoolList, "schoolName", "userName");
   		for(Map<String, Object> map : incomeBillList) {
   			if(null != managerSchoolMaps.get(map.get("schoolName"))) {
   				map.put("userName", managerSchoolMaps.get(map.get("schoolName")).toString());
   				incomeSum += (double)map.get("amount");
   				imcomeList.add(map);
   			}
   		}
   		
   		for(Map<String, Object> map : expenseBillList) {
   			if(null != managerSchoolMaps.get(map.get("schoolName"))) {
   				map.put("userName", managerSchoolMaps.get(map.get("schoolName")).toString());
   				expenseSum += (double)map.get("amount");
   				expenseList.add(map);
   			}
   		}
		
		Map<String, Object> firstMap = new HashMap<>();
		firstMap.put("month", "总计");
		firstMap.put("substName","");
		firstMap.put("schoolName", "");
		firstMap.put("target", statisType);
		firstMap.put("billType", "");
		firstMap.put("billDetail", "");
		firstMap.put("amount", df.parse(Double.toString(incomeSum)));
		firstMap.put("billTypeExpense", "");
		firstMap.put("billDetailExpense", "");
		firstMap.put("amountExpense", df.parse(Double.toString(expenseSum)));
		firstMap.put("taxAmount", df.parse(Double.toString(taxAmount)));
		
		imcomeList = addGroupSum(imcomeList,statisType);
		expenseList = addGroupSum(expenseList,statisType);
		List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
		dataList = dealDataOther(imcomeList,expenseList,statisType);
		dataList.add(0,firstMap);
		
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		if(dataList.size() < pageNo*pageSize) {
			resultList = dataList.subList((pageNo-1)*pageSize, dataList.size());
		}else {
			resultList = dataList.subList((pageNo-1)*pageSize, pageNo*pageSize);
		}
		
		page.setRecords(resultList);
		page.setTotal(dataList.size());
   		
		return ResponseData.success(page, "查询成功");
   		

   	}
    
    public List<Map<String, Object>> addGroupSum(List<Map<String, Object>> list, String key){
		List<Map<String, Object>> resultList = new ArrayList<>();
		DecimalFormat df = new DecimalFormat("#.00");
		
		if(list.size() > 0) {
			// 取出第一个账单详情
			Map<String,Object> theMap = list.get(0);
			String theDetail = (String)theMap.get("billDetail");
			double groupSum = 0.0;
			double grouptTaxSum = 0.0;
			String target = (String)theMap.get(key);
			boolean existKey = "month".equals(key) ? false : true;
	
			
			for(Map<String,Object> map : list){
				String billDetail = (String)map.get("billDetail");
				Double amount = (Double)map.get("amount");
				Double taxAmount = (Double)map.get("taxAmount");
				String compareTarget = existKey ? map.get(key).toString() : "";
				
				if((!existKey && theDetail.equals(billDetail)) || (existKey && theDetail.equals(billDetail)  && compareTarget.equals(target)) ){
					groupSum += amount;
					grouptTaxSum += taxAmount;
					resultList.add(map);
				}else{
					Map<String, Object> sumMap = new HashMap<>();
					sumMap.put("billDetail", "小计");
					sumMap.put("amount", Double.parseDouble(df.format(groupSum)));
					sumMap.put("taxAmount", Double.parseDouble(df.format(grouptTaxSum)));		
					sumMap.put(key, target);
					if(existKey) {
						sumMap.put(key, target);
					}
						
					resultList.add(sumMap);
					resultList.add(map);
					
					target = compareTarget;
					theDetail = billDetail;
					groupSum = amount;
					grouptTaxSum = taxAmount;
				}
			}
			
			Map<String, Object> lastMap = new HashMap<>();
			lastMap.put("billDetail", "小计");
			lastMap.put("amount", groupSum);
			lastMap.put("taxAmount", grouptTaxSum);
			lastMap.put(key, target);
			if(existKey) {
				lastMap.put(key, target);
			}
			resultList.add(lastMap);
		}
		
		return resultList;
	}
    
    // 把支出和收入数据拼接
 	public List<Map<String, Object>> dealData(List<Map<String, Object>> list1,List<Map<String, Object>> list2,String target){
 		List<Map<String, Object>> resultList = new ArrayList<>();
 		int j = 0;
 		
 		for(int i=0;i<list1.size() ; i++){
 			Map<String, Object> map1 = list1.get(i);
 			Map<String, Object> map = new HashMap<>();
 			
 			if(i<list2.size()){
 				j = i;
 				Map<String, Object> map2 = list2.get(i);
 	
 					map.put("month", (String)map1.get("month"));
 					map.put("county", (String)map1.get("county"));
 					map.put("school", (String)map1.get("school"));
 					map.put(target, (String)map1.get(target));
 					map.put("billType", (String)map1.get("billType"));
 					map.put("billDetail", (String)map1.get("billDetail"));
 					map.put("amount", Double.toString((Double)map1.get("amount")));
 					map.put("monthExpense", (String)map2.get("month"));
// 					map.put("county2", (String)map2.get("county"));
 					map.put("billTypeExpense", (String)map2.get("billType"));
 					map.put("billDetailExpense", (String)map2.get("billDetail"));
 					map.put("amountExpense", Double.toString((Double)map2.get("amount")));
 					map.put("taxAmount", Double.toString((Double)map2.get("taxAmount")));
 					resultList.add(map);
 			}else{
 			
 				// 没找到，则支出项留空
 				map.put("month", (String)map1.get("month"));
 				map.put("county", (String)map1.get("county"));
 				map.put("school", (String)map1.get("school"));
 				map.put(target, (String)map1.get(target));
 				map.put("billType", (String)map1.get("billType"));
 				map.put("billDetail", (String)map1.get("billDetail"));
 				map.put("amount", Double.toString((Double)map1.get("amount")));
 				map.put("monthExpense", "");
 	//			map.put("county2", "");
 				map.put("billTypeExpense", "");
 				map.put("billDetailExpense", "");
 				map.put("amountExpense", "");
 				map.put("taxAmount", "");
 	
 				resultList.add(map);
 			}
 		}
 		
 		for(j++;j<list2.size() ; j++){
 			Map<String, Object> map = new HashMap<>();
 			Map<String, Object> map2 = list2.get(j);
 			map.put("month", "");
 			map.put("county", (String)map2.get("county"));
 			map.put("school", (String)map2.get("school"));
 			map.put("billType", "");
 			map.put("billDetail", "");
 			map.put("amount", "");
 			map.put("monthExpense", (String)map2.get("month"));
// 			map.put("county2", (String)map2.get("county"));
 			map.put("billTypeExpense", (String)map2.get("billType"));
 			map.put("billDetailExpense", (String)map2.get("billDetail"));
 			map.put("amountExpense", Double.toString((Double)map2.get("amount")));
 			map.put("taxAmount", Double.toString((Double)map2.get("taxAmount")));

 			resultList.add(map);
 		}
 		
 		return resultList;
 	}
 	
 	// 把支出和收入数据拼接
 	public List<Map<String, Object>> dealDataOther(List<Map<String, Object>> list1,List<Map<String, Object>> list2,String target){
 		List<Map<String, Object>> resultList = new ArrayList<>();
 		Map<String, Object> firstmap = list1.get(0);
 		String theTarget = (String)firstmap.get(target);
 		
 		for(int i=0;i<list1.size() ; i++){
 			Map<String, Object> map1 = list1.get(i);
 			Map<String, Object> map = new HashMap<>();
 			boolean isFind = false;
 			
 			String target1 = (String)map1.get(target);
 			
 			// 左边还有数据时，左边不存在匹配数据，右边存在匹配数据
 			if(!theTarget.equals(target1)){
 				for(int j=0;j<list2.size() ; j++){
 					Map<String, Object> rightmap = new HashMap<>();
 					Map<String, Object> map2 = list2.get(j);
 					String target2 = (String)map2.get(target);
 					if(theTarget.equals(target2) ){
 						rightmap.put("month", "");
 						rightmap.put("county", "");
 						rightmap.put("school", (String)map2.get("school"));
 						rightmap.put(target, "");
 						rightmap.put("billType", "");
 						rightmap.put("billDetail", "");
 						rightmap.put("amount", "");
 						rightmap.put("monthExpense", (String)map2.get("month"));
 						rightmap.put(target+"Expense", (String)map2.get(target));
 						rightmap.put("billTypeExpense", (String)map2.get("billType"));
 						rightmap.put("billDetailExpense", (String)map2.get("billDetail"));
 						rightmap.put("amountExpense", Double.toString((Double)map2.get("amount")));
 						rightmap.put("taxAmount", Double.toString((Double)map2.get("taxAmount")));
 	
 						resultList.add(rightmap);
 						list2.remove(j);
 						j--;
 					}else{
 						break;
 					}
 				}
 			}
 			
 			theTarget = target1;
 			
 			for(int j=0;j<list2.size(); j++){
 				Map<String, Object> map2 = list2.get(j);
 				String target2 = (String)map2.get(target);
 				
 				if(target1.equals(target2) ){
 					map.put("month", (String)map1.get("month"));
 					map.put("county", (String)map1.get("county"));
 					map.put("school", (String)map1.get("school"));
 					map.put(target, (String)map1.get(target));
 					map.put("billType", (String)map1.get("billType"));
 					map.put("billDetail", (String)map1.get("billDetail"));
 					map.put("amount", Double.toString((Double)map1.get("amount")));
 					map.put("monthExpense", (String)map2.get("month"));
 					map.put(target+"Expense", (String)map2.get(target));
 					map.put("billTypeExpense", (String)map2.get("billType"));
 					map.put("billDetailExpense", (String)map2.get("billDetail"));
 					map.put("amountExpense", Double.toString((Double)map2.get("amount")));
 					map.put("taxAmount", Double.toString((Double)map2.get("taxAmount")));
 					isFind = true;
 					resultList.add(map);
 					list2.remove(j);
 					
 					break;
 				}
 			}
 			
 			// 找到对应支出，则跳出本次循环
 			if(isFind)
 				continue;
 			
 			// 没找到，则支出项留空
 			map.put("month", (String)map1.get("month"));
 			map.put("county", (String)map1.get("county"));
 			map.put("school", (String)map1.get("school"));
 			map.put(target, (String)map1.get(target));
 			map.put("billType", (String)map1.get("billType"));
 			map.put("billDetail", (String)map1.get("billDetail"));
 			map.put("amount", Double.toString((Double)map1.get("amount")));
 			map.put("monthExpense", "");
 			map.put(target+"Expense", "");
 			map.put("billTypeExpense", "");
 			map.put("billDetailExpense", "");
 			map.put("amountExpense", "");
 			map.put("taxAmount", "");

 			resultList.add(map);
 		}
 		
 		for(int i=0;i<list2.size() ; i++){
 			Map<String, Object> map = new HashMap<>();
 			Map<String, Object> map2 = list2.get(i);
 			map.put("month", "");
 			map.put("county", (String)map2.get("county"));
 			map.put("school", (String)map2.get("school"));
 			map.put(target, "");
 			map.put("billType", "");
 			map.put("billDetail", "");
 			map.put("amount", "");
 			map.put("monthExpense", (String)map2.get("month"));
 			map.put(target+"Expense", (String)map2.get(target));
 			map.put("billTypeExpense", (String)map2.get("billType"));
 			map.put("billDetailExpense", (String)map2.get("billDetail"));
 			map.put("amountExpense", Double.toString((Double)map2.get("amount")));
 			map.put("taxAmount", Double.toString((Double)map2.get("taxAmount")));

 			resultList.add(map);
 		}
 		
 		return resultList;
 	}
	
	
}
