package com.coco.project.co.financereport.controller;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.coco.common.utils.LogUtils;
import com.coco.framework.aspectj.lang.annotation.Log;
import com.coco.framework.web.controller.BaseController;
import com.coco.project.coco.etl.service.IBranchesService;
import com.coco.project.coco.financereport.domain.FinanceReport;
import com.coco.project.coco.financereport.service.IFinanceReportService;
import com.coco.project.system.dept.domain.Dept;
import com.coco.project.system.user.domain.User;
/**
*报表
*
*/
@Controller
@RequestMapping("/co/financereport")
public class FinanceReportController extends BaseController{
private String prefix = "coco/financereport";
//注入服务层
@Autowired
private IBranchesService branchesService;
@Autowired
private IFinanceReportService iFinanceReportService;
//保存查询条件
private String firstdbareaname;
private String companyname;
private String dbareaname;
private String branchname;
private String gln;
private String payMemo;
private String starttime;
private String endtime;
private String userid;
private String endDate;
private String starDate;
private Map<String ,Object> resultmap; //前端没有分页，可以保存查询结果，用于导出
private String [] titles;
//0.页面初始化方法
@RequiresPermissions("coco:financereport:view")
@GetMapping()
public String Select(FinanceReport financeReport,Dept dept,Model model,HttpSession session){
User user = getUser();
//返回用户所属权限第一个区域,屏蔽掉上海总部的
dept.setUserId(user.getUserId());
List<Dept> dbareanameList = branchesService.selectDbareanameById(dept);
firstdbareaname = dbareanameList.get(0).getDeptName();
//返回分公司查询结果
List<Dept>companynamelist = branchesService.selectCompanynameList(dept);
if(companynamelist != null){
model.addAttribute("companynamelist",companynamelist);
}
//返回查询条件
if(financeReport != null){
model.addAttribute("financeReport",financeReport);
}
//处理初始化的日期值设定
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
endDate = sdf.format(new Date(new Date().getTime()-86400000L));//获取昨天日期
starDate = endDate.substring(0,8)+"01";//获取当月1日
userid = user.getUserId().toString();
companyname = financeReport.getCompanyname();
dbareaname = financeReport.getDbareaname();
branchname = financeReport.getBranchname();
gln = financeReport.getGln();
payMemo = financeReport.getPayMemo();
starttime = financeReport.getStarttime();
endtime = financeReport.getEndtime();
return prefix + "/financereport";
}
@RequiresPermissions("coco:financereport:select")
@Log(title = "报表中心",module = "BI财务报表",action = "报表查询")
@GetMapping("/financereportlist")
@ResponseBody
public Map<String ,Object>tabletitlelist(FinanceReport financeReport){
long startTime0 = System.currentTimeMillis();
if(starttime == null || starttime == ""){
starttime = starDate;
}
if(endtime==null || endtime==""){
endtime = endDate;
}
if(branchname != null && branchname != ""){
branchname = branchname.trim();
firstdbareaname = "";
}
if(gln != null && gln !=""){
gln = gln.trim();
firstdbareaname = "";
}
if(dbareaname == null || dbareaname == ""){
dbareaname = firstdbareaname;
}
financeReport.setUserid(userid);
financeReport.setCompanyname(companyname);
financeReport.setDbareaname(dbareaname);
financeReport.setBranchname(branchname);
financeReport.setPayMemo(payMemo);
financeReport.setGln(gln);
financeReport.setStarttime(starttime);
financeReport.setEndtime(endtime);
//封装查询条件为实体类，通过Mybatis调用存储过程，返回多个结果集List<List<?>>
List<List<?>> tablelistMap = iFinanceReportService.selectlistMap(financeReport);
resultmap = new HashMap<String ,Object>();
if(tablelistMap.get(0) == null){
resultmap.put("error","未获取到数据，请重新再试或者联系管理员！");
return resultmap;
}
//解析动态标题
titles = tablelistMap.get(0).toString().split(",");
titles[0]=titles[0].replace("[{title=","");
titles[titles.length-1] = titles[titles.length-1].replace("}]","");
//根据门店名称，计算每家门店的各项付款明细的合计项，
List<?> bodyList = tablelistMap.get(1); //各个门店的详细财务数据
List<?> totalList = tablelistMap.get(2);//各个门店营收数据
List<?> payTotalList= tablelistMap.get(3);//各个门店的付款明细合计
List<Map<String ,Object>> returnListMap = new ArrayList <>();
int rowmap = 0;
if (totalList.size() >0) {
//原始的营收数据比付款明细合计多的情况，单条营收数据也要显示
int j = 0;
for (int i = 0; i < totalList.size(); i++) {
//1.追加每家门店的原始营收数据
Map<String, Object> totalMap = (Map<String, Object>) totalList.get(i);
String oldgln = totalMap.get("全球编码").toString();
for (; j < bodyList.size(); j++) {
Map<String, Object> bodyMap = (Map<String, Object>) bodyList.get(j);
if (oldgln.equals(bodyMap.get("全球编码"))) {
returnListMap.add(rowmap++, bodyMap);
}else{
break;
}
}
returnListMap.add(rowmap++, totalMap);//门店的最后一行该门店的原始营收数据
//2.追加每家门店的差异金额
for (int k = 0; k < payTotalList.size(); k++) {
Map<String, Object> payMemoTotalMap = (Map<String, Object>) payTotalList.get(k);
String paygln = payMemoTotalMap.get("全球编码").toString();
if ( paygln.equals(oldgln)) {
Map<String, Object> returnMap = new HashMap<>();
returnMap.put(titles[0], totalMap.get("分公司").toString());
returnMap.put(titles[1], totalMap.get("区域").toString());
returnMap.put(titles[2], totalMap.get("门店名称").toString());
returnMap.put(titles[3], totalMap.get("全球编码").toString());
returnMap.put(titles[4], "差异金额");
for (int m = 5; m < titles.length; m++) { //循环计算每天的差异金
double totalMoney = Double.parseDouble(totalMap.get(titles[m]).toString());
double oldMoney = Double.parseDouble(payMemoTotalMap.get(titles[m]).toString());
returnMap.put(titles[m], oldMoney-totalMoney);//付款明细合计-原始营收额=差异金
}
returnListMap.add(rowmap++, returnMap);
break;
}
}
}
}
//3根据标题统计对应列的总和
List<Map<String ,Object>> listMap = new ArrayList <>();
Map<String ,Object> totalMap = new HashMap<>();
//查询部分
Map<String ,Object> maps = (Map<String ,Object>)tablelistMap.get(0).get(0);
if(maps.values()!= null){
if(titles.length>0){
for(int m = 5;m<titles.length;m++){
Double total = 0.00;
//处理内容
for(int n = 0;n<tablelistMap.get(1).size();n++){
Map<String ,Object>mapb = (Map<String ,Object>)tablelistMap.get(1).get(n);
for(Entry<String ,Object>entry:mapb.entrySet()){
if(titles[m].equals(entry.getKey())){
total += Double.parseDouble(entry.getValue().toString ());
}
}
}
totalMap.put(titles[m],total);
}
}
}
listMap.add(totalMap);
resultmap.put("title",tablelistMap.get(0));//1.返回标题
resultmap.put("body",returnListMap);//2.返回内容
resultmap.put("total",listMap);
long endTime0 = System.currentTimeMillis();
System.out.println("分店每日付款方式列表 查询数据总耗时:"+(endTime0 - startTime0)+" 毫秒");
return resultmap;
}
/**
*导出报表查询的结果，支持条件查询
**/
@RequestMapping("/exportExcel")
@Log(title = "报表中心",module = "BI财务报表",action = "报表导出")
public void ExportFreemudExcel(HttpServletRequest request,HttpServletResponse response,FinanceReport financeReport,Model model){
try {
long startTime0 = System.currentTimeMillis();
SimpleDateFormat parse = new SimpleDateFormat("yyyy-MM-dd");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/xls");
response.setHeader("Content-Disposition","inline;filename=" + URLEncoder.encode("BI财务报表"+parse.format(new Date())+".xlsx","utf-8"));
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet = workbook.createSheet("BI财务报表 "+dbareaname);
sheet.createFreezePane(5,1); //冻结表格左边两列，最上面一行
sheet.setDefaultColumnWidth(11);//设置表格
//1.设置标题样式-----给单元格设置样式
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
//1动态导出标题
SXSSFRow headRow = sheet.createRow(0);
for (int i = 0; i < titles.length; i++) {
CellUtil.createCell(headRow, i, titles[i],setStyleHeader(workbook));
}
//2.2导出内容
List<Map<String ,Object>> bodyListMap = (List<Map<String, Object>>) resultmap.get("body");
int rownum = 1;
if (bodyListMap.size() > 0) {
for (int i = 0; i < bodyListMap.size(); i++) {
Map<String,Object> map = bodyListMap.get(i);
SXSSFRow row = sheet.createRow(rownum++);
for (int j = 0; j < map.size(); j++) {
Object obj = map.get(titles[j]);
//判断数据结果的类型
if (obj.getClass().toString().equals("class java.lang.String")){
row.createCell(j).setCellValue(obj.toString());
}else if (obj.getClass().toString().equals("class java.lang.Integer")) {
row.createCell(j).setCellValue(Integer.parseInt(obj.toString()));
}else if (obj.getClass().toString().equals("class java.math.BigDecimal")||obj.getClass().toString().equals("class java.lang.Double")) {
row.createCell(j).setCellValue(Double.parseDouble(obj.toString()));
}
}
}
}
//3解析纵向统计Map，并导出
List<Map<String ,Object>> totalListMap = (List<Map<String, Object>>) resultmap.get("total");
Map<String ,Object> totalMap = totalListMap.get(0);//合计行只有一条数据
SXSSFRow row = sheet.createRow(rownum);
Cell cell1 = row.createCell(4);
cell1.setCellStyle(cellStyle);cell1.setCellValue("总合计");
for (int i = 5; i < totalMap.size()+5; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(Double.parseDouble(totalMap.get(titles[i]) == null ? "0":totalMap.get(titles[i]).toString()));
}
workbook.write(response.getOutputStream());
response.flushBuffer();
workbook.close();
long endTime0 = System.currentTimeMillis();
System.err.println("分店每日付款方式列表 导出数据总耗时:"+(endTime0 - startTime0)+" 毫秒");
}catch(Exception e){
e.printStackTrace();
LogUtils.logError("Excel:",e);
}
}
/**
* 获取并设置header样式
*/
private XSSFCellStyle setStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 12);
// 字体粗细
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.LEFT);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
xssfCellStyle.setFillForegroundColor(HSSFColorPredefined.BLUE_GREY.getIndex());
xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return xssfCellStyle;
}
/** 2003旧版Excel导出
*导出报表查询的结果，支持条件查询
**
@RequestMapping("/exportExcel")
@Log(title = "报表中心",module = "BI财务报表",action = "报表导出")
public void ExportFreemudExcel(HttpServletRequest request,HttpServletResponse response,FinanceReport financeReport,Model model){
try {
if(starttime == null || starttime == ""){
starttime = starDate;
}
if(endtime==null || endtime==""){
endtime = endDate;
}
if(branchname != null && branchname != ""){
branchname = branchname.trim();
firstdbareaname = "";
}
if(gln != null && gln !=""){
gln = gln.trim();
firstdbareaname = "";
}
if(dbareaname == null || dbareaname == ""){
dbareaname = firstdbareaname;
}
financeReport.setUserid(userid);
financeReport.setCompanyname(companyname);
financeReport.setDbareaname(dbareaname);
financeReport.setBranchname(branchname);
financeReport.setPayMemo(payMemo);
financeReport.setGln(gln);
financeReport.setStarttime(starttime);
financeReport.setEndtime(endtime);
//封装查询条件为实体类，通过Mybatis调用存储过程，返回多个结果集List<List<?>>
List<List<?>> tablelistMap = iFinanceReportService.selectlistMap(financeReport);
//3根据标题统计对应列的总和
String [] titleResult = new String [100];//声明解析后的动态标题
Map<Integer,Double>totalMap = new HashMap<>();//保存最后一行纵向统计结果
List<?>titleMap = tablelistMap.get(0);//获取标题内容
List<?>bodyMap = tablelistMap.get(1);//获取文本内容
if(titleMap!=null){
titleResult=titleMap.get(0).toString ().split(",");
if(titleResult.length>0){
titleResult[0]=titleResult[0].replace("{title=","");
titleResult[titleResult.length-1] = titleResult[titleResult.length-1].replace("}","");
for(int m=5;m<titleResult.length;m++){
Double total = 0.00;
//处理内容
for(int n=0;n<tablelistMap.get(1).size();n++){
for(Entry<String ,Object>entry:((Map<String ,Object>)tablelistMap.get(1).get(n)).entrySet()){
if(titleResult[m].equals(entry.getKey())){
total += Double.parseDouble(entry.getValue().toString ());
}
}
}
totalMap.put(m,total);
}
}
}
SimpleDateFormat parse = new SimpleDateFormat("yyyy-MM-dd");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/xls");
response.setHeader("Content-Disposition","inline;filename=" + URLEncoder.encode("BI财务报表"+parse.format(new Date())+".xlsx","utf-8"));
WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream());
WritableSheet sheet = ExportUtil.sheetSetting(book,1);//设置数据显示在第几个sheet
WritableFont font = ExportUtil.headerFont();
WritableCellFormat cFormat = ExportUtil.cellFormat(font);
//cFormat.setBackground(green);
//1动态导出标题
ExportUtil.creatHeader(sheet,cFormat,titleResult);
WritableFont fontBody = ExportUtil.bodyFont();
WritableCellFormat cFormatBody = ExportUtil.cellFormat(fontBody);
WritableCellFormat wcfN = new WritableCellFormat(new jxl.write.NumberFormat("#0.00"));
//2.1重新根据标题排序
List<Map<Integer,Object>>pxMap = new ArrayList<>();
for(int i = 0;i<bodyMap.size();i++){
Map<Integer,Object>map = new HashMap<>();//此声明的map是list集合内部map，存储每一行的数据，所以每循环一行都要重新清空声明，如果放在前面会导致导出结果全部一样。
for(int m = 0;m<titleResult.length;m++){
for(Entry<String ,Object>entry:((Map<String ,Object>)tablelistMap.get(1).get(i)).entrySet()){
if(titleResult[m].equals(entry.getKey())){
map.put(m,entry.getValue());
continue;
}
}
}
pxMap.add(i,map);//必须要加上i，如果只有map，集合里都是最后一行重复结果
}
//2.2导出内容
Iterator iterator = pxMap.iterator();//遍历外层map
int row = 1;//控制导出的行
while(iterator.hasNext()){
Map row_map = (Map)iterator.next();
Iterator row_iterator = row_map.entrySet().iterator();//解析内层ma'p
int colnum_b = 0;
while(row_iterator.hasNext()){
Map.Entry entry_column = (Map.Entry)row_iterator.next();
//System.out.println(row+"---"+colnum_b+"******"+entry_column); //测试哪一行哪一列数据有问题
String [] key_value = entry_column.toString ().split("=");
if(key_value.length != 2){//key和value是成对出现的，如果长度不是2数据有异常，作为空值处理
sheet.addCell(new Label(colnum_b,row,"",cFormatBody));
}else{
if(colnum_b <= 4){//前四列使用文本，后面的全部使用数字格式
sheet.addCell(new Label(colnum_b,row,key_value[1],cFormatBody));
}else{
Double total = Double.parseDouble(key_value[1]);
sheet.addCell(new jxl.write.Number(colnum_b,row,total != 0 ? total:0,wcfN));
}
}
colnum_b++;
}
row++;//因为要导出多行多列数据，所有行的控制是等该行数据所有列全部加载完后再加载下一行
}
//3解析纵向统计Map，并导出
Iterator iter = totalMap.entrySet().iterator();
int colnum = 5;
sheet.addCell(new Label(4,row,"合计",cFormatBody));
while(iter.hasNext()){
Map.Entry entry = (Map.Entry)iter.next();
String [] key_value = entry.toString().split("=");
Double total = Double.parseDouble(key_value[1]);
sheet.addCell(new jxl.write.Number(colnum++,row,total != 0?total:0,wcfN));
}
book.write();
book.close();
response.flushBuffer();
}catch(Exception e){
e.printStackTrace();
LogUtils.logError("Excel:",e);
}
}*/
//下拉查询分公司
@GetMapping("/company")
@ResponseBody
public List<Dept>arealist(Dept dept,String companyname,Model model){
//取区域信息
List<Dept>careanamelist = null;
if(companyname!=null&&companyname.equals("大陆地区")){
careanamelist = branchesService.selectBranchesIdList(dept);
model.addAttribute("careanamelist",careanamelist);
return careanamelist;
}else{
dept.setCompanyname(companyname);
careanamelist = branchesService.selectCareanameList(dept);
model.addAttribute("careanamelist",careanamelist);
}
return careanamelist;
}
}
