package org.springblade.capcancel.controller;


import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springblade.capcancel.service.ICapCancelListService;
import org.springblade.capcancel.service.ICapCancelNumService;
import org.springblade.capcancel.vo.CapCancelListVO;
import org.springblade.core.tool.api.R;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

/**
 * <p>
 * CAP统一失效清单 前端控制器
 * </p>
 *
 * @author zbc
 * @since 2021-08-25
 */
@RestController
@RequestMapping("/capCancelList")
@Api(value = "CAP统一失效清单", tags = "CAP统一失效清单")
public class CapCancelListController {

	@Autowired
	private ICapCancelListService capCancelListService;

	@Autowired
	private ICapCancelNumService capCancelNumService;


	@Value("${blade.file.uploadPath}")
	private String picDir;


	@PostMapping("/export")
	@ApiOperation(value = "导出")
	public R export(String month) {
		if(StringUtils.isBlank(month)){
			return R.fail("参数异常");
		}
		String tempFileName = "统一失效量明细" + month + ".xlsx";
		//String filePath = "D:/boyuan/bladexmaster/jar/" + tempFileName;
		String filePath = picDir + tempFileName;
		//动态表头
		List<String> spnameList = capCancelNumService.getSpnameList();
		List<List<String>> headList = new ArrayList<>();
		List<String> province = new ArrayList<>();
		province.add("省份");
		headList.add(province);
		for (String s : spnameList) {
			List<String> title = new ArrayList<>();
			title.add(s);
			headList.add(title);
		}
		ExcelWriter excelWriter = EasyExcel.write(filePath).build();
		//内容
		List<List<Object>> lists = contentList();
		WriteSheet writeSheet = EasyExcel.writerSheet(0, "统一失效量").head(headList).build();
		excelWriter.write(lists,writeSheet);

		//CAP统一失效清单
		List<CapCancelListVO> list = capCancelListService.getListByMonth(month);
		WriteSheet writeSheet1 = EasyExcel.writerSheet(1,"统一失效量明细").head(CapCancelListVO.class).build();
		excelWriter.write(list,writeSheet1);
		excelWriter.finish();
		return R.success("uploads/" + tempFileName);
	}


	private List<List<Object>> contentList (){
		StringBuffer buffer = new StringBuffer("SELECT\n" +
			"    *\n" +
			"FROM (SELECT SPNAME, PROVINCE ,CANCELNUM FROM CAP_CANCEL_NUM)\n" +
			"PIVOT\n" +
			"(\n" +
			"SUM(CANCELNUM)\n" +
			"FOR SPNAME IN (");
		List<String> spnameList = capCancelNumService.getSpnameList();
		if(spnameList != null && !spnameList.isEmpty()){
			for (int i = 0; i < spnameList.size(); i++) {
				if(spnameList.size() - i != 1){
					buffer.append( "\'" + spnameList.get(i) + "\'" + "spname" + (i + 1) + ",");
				}else {
					buffer.append( "\'" + spnameList.get(i) + "\'" + "spname" + (i + 1) + "))");
				}
			}
		}
		List<LinkedHashMap<String,Object>> mapList = capCancelNumService.getPivotList(buffer);
		List<List<Object>> list = new ArrayList<List<Object>>();
		for (LinkedHashMap<String, Object> map : mapList) {
			List<Object> data = new ArrayList<Object>();
			for (Map.Entry<String, Object> entry : map.entrySet()) {
				data.add(entry.getValue());
			}
			list.add(data);
		}
		return list;
	}

}

