package com.winsun.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.util.*;

public class ExcelDealUtils {
	
	
	@SuppressWarnings("unchecked")
	public static XSSFWorkbook getWorkBook(Map<String, Object> headData,List<Map<String,Object>> list) {
		String sheetName = headData.get("sheetName").toString();
		List<String> names = (List<String>)headData.get("names");
		List<String> columns =  (List<String>)headData.get("columns");

		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建工作表
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //添加表头
        final XSSFRow row = sheet.createRow(0);
        //设置列宽度
		for (int i=1 ;i <= columns.size();i++ ){
			sheet.setColumnWidth(i, 2500);
		}
//        sheet.setColumnWidth(1, 6500);
//        sheet.setColumnWidth(2, 6500);

        //第一行字段名称
        names.forEach(column -> {
            XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
            cell.setCellValue(column);
        });
        //数据
        int rowIndex = row.getRowNum();
        for (Map<String, Object> maps: list) {
            final XSSFRow rowdata = sheet.createRow(++rowIndex);
            for (String column : columns) {
                XSSFCell cell = rowdata.createCell(Math.max(rowdata.getLastCellNum(), 0));
                if (maps.get(column) != null) {
                    cell.setCellValue(maps.get(column).toString());
                }
            }
        }
        
        return workbook;
	}

	@SuppressWarnings("unchecked")
	public static XSSFWorkbook getWorkBook2(Map<String, Object> headData,List<Map<String,Object>> list,List<Integer> list1) {
		String sheetName = headData.get("sheetName").toString();
		List<String> names = (List<String>)headData.get("names");
		List<String> columns =  (List<String>)headData.get("columns");

		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建工作表
		XSSFSheet sheet = workbook.createSheet(sheetName);
		//添加表头
		final XSSFRow row = sheet.createRow(0);
		//设置列宽度
		for (int i=0 ;i < list1.size();i++ ){
			sheet.setColumnWidth(i, list1.get(i));
		}
		//第一行字段名称
		names.forEach(column -> {
			XSSFCell cell = row.createCell(Math.max(row.getLastCellNum(), 0));
			cell.setCellValue(column);
		});
		//数据
		int rowIndex = row.getRowNum();
		for (Map<String, Object> maps: list) {
			final XSSFRow rowdata = sheet.createRow(++rowIndex);
			for (String column : columns) {
				XSSFCell cell = rowdata.createCell(Math.max(rowdata.getLastCellNum(), 0));
				if (maps.get(column) != null) {
					cell.setCellValue(maps.get(column).toString());
				}
			}
		}

		return workbook;
	}
	private static void setCellDefaultStyle(String value, CellStyle style, XSSFCell cell) {
		cell.setCellStyle(style);
		cell.setCellValue(value);
	}
	public static XSSFWorkbook getWorkBook3(Map<String, Object> headData,List<Map<String,Object>> list) {

		String sheetName = headData.get("sheetName").toString();
		List<String> names = (List<String>)headData.get("names");
		List<String> columns =  (List<String>)headData.get("columns");

		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建工作表
		XSSFSheet sheet = workbook.createSheet(sheetName);
		// 表头样式
		XSSFCellStyle headStyle = workbook.createCellStyle();
		// 字体样式
		XSSFFont xssfFont = workbook.createFont();
		// 加粗
		xssfFont.setBold(true);
		// 字体名称
		xssfFont.setFontName("楷体");
		// 字体大小
		xssfFont.setFontHeight(16);
		// 设置字体css
		headStyle.setFont(xssfFont);
		// 竖向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 横向居中
		headStyle.setAlignment(HorizontalAlignment.CENTER);
		// 边框
		headStyle.setBorderBottom(BorderStyle.THIN);
		headStyle.setBorderLeft(BorderStyle.THIN);
		headStyle.setBorderRight(BorderStyle.THIN);
		headStyle.setBorderTop(BorderStyle.THIN);
		headStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		//设置列宽度
		for (int i=0 ;i < columns.size();i++ ){
			sheet.setColumnWidth(i, 3000);
		}

//		sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 18));
//		sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 26));

		final XSSFRow row2 = sheet.createRow(2);
		for (int i = 0; i< names.size();i++){
			XSSFCell cell = row2.createCell(i);
			cell.setCellValue(names.get(i));
			cell.setCellStyle(headStyle);
		}
		//数据
		int rowIndex = row2.getRowNum();
		for (Map<String, Object> maps: list) {
			final XSSFRow rowdata = sheet.createRow(++rowIndex);
			for (String column : columns) {
				XSSFCell cell = rowdata.createCell(Math.max(rowdata.getLastCellNum(), 0));
				if (maps.get(column) != null) {
					cell.setCellValue(maps.get(column).toString());
				}
			}
		}

		return workbook;
	}
	
	public static Map<String, Object> dealHeadMap(Map<String, Object> map){
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<String> names = new ArrayList<>();
		List<String> columns = new ArrayList<>();
		
		for(Map.Entry<String, Object> theMap : map.entrySet()) {
			names.add(theMap.getKey());
			columns.add(theMap.getValue().toString());
		}
		
		resultMap.put("names", names);
		resultMap.put("columns", columns);
		
		return resultMap;
	}
	
	// 活动导出excel表头
	public static Map<String, Object> activityExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("姓名", "name");
		headMap.put("账号", "account");
		headMap.put("所得积分", "recordIntegral");
		headMap.put("积分类型", "activityType");
		headMap.put("积分小类", "subclass");
		headMap.put("县分", "substName");
		headMap.put("学校", "schoolName");
		headMap.put("创建时间", "createTime");
	
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		
		return resultMap;
	}
	// kpi管理导出excel表头
	public static Map<String, Object> kpiExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("kpiId", "id");
		headMap.put("用户名(必填)", "sysuName");
		headMap.put("账号(必填)", "account");
		headMap.put("县分", "substName");
		headMap.put("学校", "schoolName");
		headMap.put("汇总任务量", "reportDaily");
		headMap.put("参与集中营销", "collectiveSales");
		headMap.put("组织周例会", "weeklyMeeting");
		headMap.put("定制扫楼营销方案", "organTraining");
		headMap.put("组织培训", "designPlan");
		headMap.put("KPI总值", "kpi");
		headMap.put("KPI月份", "month");
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 院线通订单导出excel表头
	public static Map<String, Object> yxtOrderExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("订单号", "orderNum");
		headMap.put("金额", "totalPrice");
		headMap.put("订单状态", "state");
		headMap.put("订单类型", "orderType");
		headMap.put("账号", "account");
		headMap.put("姓名", "sysuName");
		headMap.put("学校", "schoolName");
		headMap.put("县分", "substName");
		headMap.put("创建时间", "updateDate");
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 兑换券导出excel表头
	public static Map<String, Object> exchangeCouponExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("兑换券账号", "cardNumber");
		headMap.put("密码", "password");
		headMap.put("出售状态(1:未售出,2:已售出,3:售出中)", "state");
		headMap.put("上架状态(1:已上架,2:已下架)", "shelfState");
		headMap.put("有效截止日期", "validityPeriod");
		headMap.put("上传时间", "updateDate");
		headMap.put("是否删除(0:未删除，1:已删除)", "delFlag");
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 管理导出excel表头
	public static Map<String, Object> wageExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("KPI月份", "month");
		headMap.put("县分", "substName");
		headMap.put("学校", "schoolName");
		headMap.put("姓名(必填)", "sysuName");
		headMap.put("账号(必填)", "account");
		headMap.put("放号充值数", "rechargeNum");
		headMap.put("放号薪酬", "rechargeWage");
		headMap.put("kpi总值", "kpi");
		headMap.put("kpi薪酬", "kpiWage");
		headMap.put("总薪酬", "wage");

		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 导出放号充值达标清单excel表头
	public static Map<String, Object> salesOrderExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("放号号码", "orderPhone");
		headMap.put("放号学校", "salesSchool");
		headMap.put("放号县分", "salesSubst");
		headMap.put("放号人", "name");
		headMap.put("放号人账号", "account");
		headMap.put("职位", "position");
		headMap.put("上级领导", "leader");
		headMap.put("总经理", "president");
		headMap.put("上传时间", "createTime");
		headMap.put("激活时间", "activateTime");
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 导出学子公司销量统计清单excel表头
	public static Map<String, Object> salesCensusExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("放号人", "name");
		headMap.put("放号人账号", "account");
		headMap.put("放号县分", "salesSubst");
		headMap.put("放号学校", "salesSchool");
		headMap.put("达标充值数", "recentlyNum");
		headMap.put("职位", "position");
		headMap.put("上级领导", "leader");
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}

	// 导出学一人一码统计报表excel表头
	public static Map<String, Object> yrymExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("县分", "subName");
		headMap.put("学校数", "schoolNum");
		headMap.put("学生数", "studentNum");
		headMap.put("新用户", "newUserNumWeek");
		headMap.put("单宽带", "newAloneNumWeek");
		headMap.put("融合宽带", "newAloneNumWeek");
		headMap.put("老用户办理", "oldUserNumWeek");
		headMap.put("老用户续约", "oldContinueNumWeek");
		headMap.put("新增", "finishNewNumWeek");
		headMap.put("存量", "finishOldNumWeek");
		headMap.put("总计", "sumWeek");
		headMap.put("新用户A", "newUserNumMonth");
		headMap.put("单宽带A", "newAloneNumMonth");
		headMap.put("融合宽带A", "newMixNumMonth");
		headMap.put("老用户办理A", "oldUserNumMonth");
		headMap.put("老用户续约A", "oldContinueNumMonth");
		headMap.put("新增A", "finishNewNumMonth");
		headMap.put("存量A", "finishOldNumMonth");
		headMap.put("总计A", "sumMonth");
		headMap.put("新用户B", "newUserNumAll");
		headMap.put("单宽带B", "newAloneNumAll");
		headMap.put("融合宽带B", "newMixNumAll");
		headMap.put("老用户办理B", "oldUserNumAll");
		headMap.put("老用户续约B", "oldContinueNumAll");
		headMap.put("新增B", "finishNewNumAll");
		headMap.put("存量B", "finishOldNumAll");
		headMap.put("总计B", "sumAll");
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}

	// 总经理薪酬管理导出excel表头
	public static Map<String, Object> zjlWageExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("序号", "id");
		headMap.put("月份", "month");
		headMap.put("归属", "department");
		headMap.put("姓名", "name");
		headMap.put("身份证", "idCard");
		headMap.put("分公司", "company");
		headMap.put("属性", "attribute");
		headMap.put("岗位工资", "positionWage");
		headMap.put("绩效奖金\n（绩效工资+提成）", "performanceBonus");
		headMap.put("专项奖", "specialBonus");
		headMap.put("年终奖", "yearEndBonus");
		headMap.put("节日费(元旦,春节,五一,十一)", "festivalFee");
		headMap.put("高温补贴(6-10月)", "highSubsidy");
		headMap.put("通讯补贴", "correspondenceSubsidy");
		headMap.put("餐费补贴", "tableMoneySubsidy");
		headMap.put("交通补贴", "trafficSubsidy");
		headMap.put("专项奖（秋营奖励）", "specialBonusAutumn");
		headMap.put("其他补扣补退", "otherSubsidy");
		headMap.put("应发合计", "totalYf");
		headMap.put("个人养老保险", "endowmentInsurancePersonal");
		headMap.put("个人缴纳失业保险", "unenploymentInsurancePersonal");
		headMap.put("个人缴纳医疗保险", "medicarePersonal");
		headMap.put("个人保险小计", "totalInsurance");
		headMap.put("个人缴纳住房公积金", "reservedFund");
		headMap.put("个税", "reservedFundTax");
		headMap.put("年终奖个税", "yearEndBonusTax");
		headMap.put("个人其他补交补退", "otherFeePersonal");
		headMap.put("个人工会费", "duesPersonal");
		headMap.put("个人缴交小计", "totalFeePersonal");
		headMap.put("实发合计", "totalSf");
		headMap.put("单位养老保险", "endowmentInsuranceUnit");
		headMap.put("单位失业保险", "unenploymentInsuranceUnit");
		headMap.put("单位医疗保险", "medicareUnit");
		headMap.put("单位工伤保险", "employmentInjuryInsuranceUnit");
		headMap.put("单位生育保险", "birthInsuranceUnit");
		headMap.put("重大疾病保险", "illnessInsuranceUnit");
		headMap.put("单位公积金", "reservedFundUnit");
		headMap.put("工会计提", "laborUnionFeeUnit");
		headMap.put("商业意外险", "accidentInsuranceUnit");
		headMap.put("残保金", "disabilityInsuranceUnit");
		headMap.put("单位其他补交补退", "otherFeeUnit");
		headMap.put("单位缴交小计", "totalFeeUnit");
		headMap.put("总个人人工成本合计", "totalCastPersonal");
		headMap.put("附加信息", "additionMsg");
		headMap.put("邮电人才服务管理费（200/人）", "manageFee");
		headMap.put("单位总成本（含支出税点）", "totalCastUnit");
		headMap.put("个人缴纳养老保险2", "endowmentInsurancePersonal2");
		headMap.put("个人缴纳失业保险2", "unenploymentInsurancePersonal2");
		headMap.put("个人缴纳医疗保险2", "medicarePersonal2");
		headMap.put("个人缴纳住房公积金2", "resveredFundPersonal2");
		headMap.put("单位养老保险2", "endowmentInsuranceUnit2");
		headMap.put("单位失业保险2", "unenploymentInsuranceUnit2");
		headMap.put("单位医疗保险2", "medicareUnit2");
		headMap.put("单位工伤保险2", "employmentInjuryInsuranceUnit2");
		headMap.put("单位生育保险2", "birthInsuranceUnit2");
		headMap.put("重大疾病保险企业缴纳2", "illnessInsuranceUnit2");
		headMap.put("单位住房公积金2", "reservedInsuranceUnit2");
		headMap.put("合并计税", "totalInsuranceUnit2");
		headMap.put("本月计税工资", "wage");
		headMap.put("本月抵税扣除", "wageTax");
		headMap.put("本月工资抵税", "wageTaxAfter");
		headMap.put("年终奖应税", "lastYearEndBonus");
		headMap.put("年终奖个税计算", "lastYearEndBonusTax");
		headMap.put("银行账号", "bankAccount");
		headMap.put("督导成本", "managerCast");
		headMap.put("学子公司成本", "xzCompanyCast");

		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);

		return resultMap;
	}

	// 总经理kpi管理导出excel表头
	public static Map<String, Object> managerKpiExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("月份", "month");
		headMap.put("县分", "county");
		headMap.put("校园督导", "name");
		headMap.put("绩效基数", "performanceBase");
		headMap.put("移动入网\n（30%）", "rwKpi");
		headMap.put("存量维系\n（30%）", "wxKpi");
		headMap.put("团队运营\n（25%）", "yyKpi");
		headMap.put("每月工作\n（15%）", "workKpi");
		headMap.put("18年5月至今累计佣金", "totalBonus");
		headMap.put("18年5月至今月累计分成", "totalFund");
		headMap.put("秋营达量奖", "autumnBonus");

		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 大王卡证件稽核清单导出excel表头s
	public static Map<String, Object> studentCertExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("姓名", "userName");
		headMap.put("办理号码", "orderPhone");
		headMap.put("联系号码", "linkPhone");
		headMap.put("审核人姓名", "auditName");
		headMap.put("审核时间", "auditDate");
		headMap.put("是否通过审核", "auditState");
		headMap.put("上传时间", "uploadDate");
		headMap.put("代理商", "agentBusinessman");

		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	// 学生不限量证件清单导出excel表头s
	public static Map<String, Object> studentCardExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("姓名", "userName");
		headMap.put("办理号码", "orderPhone");
		headMap.put("联系号码", "linkPhone");
		headMap.put("审核人姓名", "auditName");
		headMap.put("审核时间", "auditDate");
		headMap.put("是否通过审核", "auditState");
		headMap.put("县分", "county");
		headMap.put("学校", "school");
		headMap.put("办理号码是否新49", "isComboMoney49");
		headMap.put("是否已上传学生证照片、学校等必填信息", "isFull");
		headMap.put("学生证照片是否清晰", "isClear");
		headMap.put("学生年龄是否25岁及以下", "isUnderYears");
		headMap.put("号码被实名人员与上传学生证用户是否一致", "isStudentAccordance");
		headMap.put("学生证所属学校与号码所属学校是否一致", "isSchoolAccordance");
		headMap.put("资料上传时间是否早于CRM系统激活时间", "isUploadBeforeActivate");
		headMap.put("上传时间", "uploadDate");
		headMap.put("crm激活时间", "crnActivateDate");
		headMap.put("是否首次上传", "isFirstUpload");
		headMap.put("代理商", "agentBusinessman");

		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		return resultMap;
	}
	
	// 账单导出excel表头
	public static Map<String, Object> billExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("月份", "month");
		headMap.put("项目类型", "billType");
		headMap.put("收支详情", "billDetail");
		headMap.put("县分", "substName");
		headMap.put("学校", "schoolName");
		headMap.put("校区", "campus");
		headMap.put("网点名称", "networkName");
		headMap.put("网点编码", "networkCode");
		headMap.put("上传时间", "createTime");
		headMap.put("不含税金额", "amount");
		headMap.put("含税金额", "taxAmount");
		headMap.put("税点", "taxPoint");
	
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		
		return resultMap;
	}
	

	// 账单汇总导出excel表头
	public static Map<String, Object> billStatisExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("项目类型", "billType");
		headMap.put("月份", "month");
		headMap.put("收支详情", "billDetail");
		headMap.put("金额", "amount");
		headMap.put("项目类型", "billTypeExpense");
		headMap.put("月份", "monthExpense");
		headMap.put("收支详情", "billDetailExpense");
		headMap.put("不含税金额", "amountExpense");
		headMap.put("含税金额", "taxAmount");
	
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		
		return resultMap;
	}
	
	// 奖励结算导出excel表头
	public static Map<String, Object> bonusExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("账号", "account");
		headMap.put("姓名", "userName");
		headMap.put("县分", "substName");
		headMap.put("学校", "schoolName");
		headMap.put("金额", "bonus");
		headMap.put("佣金状态", "state");
	
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		
		return resultMap;
	}
	
	// 合伙人管理导出excel表头
	public static Map<String, Object> hhrUserExcel(String sheetName){
		Map<String, Object> headMap = new LinkedHashMap<String, Object>();
		headMap.put("姓名", "name");
		headMap.put("电话", "phone");
		headMap.put("学校", "school");
		headMap.put("身份证", "idCard");
		headMap.put("账号", "account");
		headMap.put("县分", "substName");
		headMap.put("角色", "roleName");
		headMap.put("状态", "status");
		headMap.put("活跃状态", "isActive");
		headMap.put("性别", "sex");
	
		Map<String, Object> resultMap = dealHeadMap(headMap);
		resultMap.put("sheetName", sheetName);
		
		return resultMap;
	}
}
