package com.yizhi.application.report.course;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.yizhi.core.application.context.TaskContext;
import com.yizhi.core.application.file.constant.FileConstant;
import com.yizhi.core.application.file.task.AbstractDefaultTask;
import com.yizhi.core.application.file.util.OssUpload;
import com.yizhi.course.application.feign.CourseReportClient;
import com.yizhi.course.application.vo.ReportStudyCourseAccountDetailsVo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.baomidou.mybatisplus.toolkit.CollectionUtils;



/**
 * 按照用户统计---（后边的按钮）---导出excel
 * @author wangfeida
 *
 */

@Component
public class CourseAccountGroupDetailsExportExcel extends AbstractDefaultTask<String, Map<String, Object>> {

	private static final Logger logger = LoggerFactory.getLogger(CourseAccountGroupDetailsExportExcel.class);

	@Autowired
	private CourseReportClient courseReportClient;

	@Override
	protected String execute(Map<String, Object> map) {
		// TODO Auto-generated method stub
		String sheetName="在线课程学习统计-按用户统计-下载学习明细";
		String upLoadUrl = null;
		Long accountId = (Long) map.get("accountId");
		Long companyId = (Long) map.get("companyId");
		Long orgId = (Long) map.get("orgId");
		Long siteId = (Long) map.get("siteId");
		String startDate = (String) map.get("startDate");
		String endDate = (String) map.get("endDate");
		Long taskId=(Long) map.get("taskId");
		Date submitTime=(Date) map.get("submitTime");
		String serialNo=(String) map.get("serialNo");
		String taskName=(String) map.get("taskName");
		String orgKwd = (String) map.get("orgKwd");
		String accountKwd = (String) map.get("accountKwd");

		TaskContext taskContext = new TaskContext(taskId,serialNo, taskName,accountId, submitTime, siteId, companyId);// 任务id 任务编号 任务名字      操作人id   操作时间
		working(taskContext);
		
		
		Integer pageSize=200;
		//数据总量
		Integer dateTotal= courseReportClient.courseAccountDetailsDataTotal(accountKwd, orgKwd, startDate, endDate, companyId, orgId, siteId);
		System.out.println("总数据量："+dateTotal);
		//分页查询数据/整合数据
		List<ReportStudyCourseAccountDetailsVo> list =new ArrayList<ReportStudyCourseAccountDetailsVo>();
		List<ReportStudyCourseAccountDetailsVo> listPage=null;
		for (int i = 0; i <dateTotal/pageSize+1; i++) {
			Date d1=new Date();
			listPage = courseReportClient.courseAccountDetails(accountKwd, orgKwd, startDate, endDate, companyId, orgId, siteId,i,pageSize);
			Date d2=new Date();
			System.out.println("总数据量:"+dateTotal+"。每次查询"+pageSize+"条。当前查询"+(i*pageSize)+"。。。。第"+(i+1)+"次查询花费时间(ms):"+(d2.getTime()-d1.getTime()));
			if(CollectionUtils.isNotEmpty(listPage)) {
				list.addAll(listPage);
			}
		}
		
		
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat df2 = new SimpleDateFormat("yyyyMMddHHmmss");
		
		Integer maxNumOneSheet=50000;   //一个sheet最多有多少数据量
		Integer numSheet=1;             //有多少个sheet
		
		
		

		try {
			// excel生成过程: excel-->sheet-->row-->cell
			// 第一步，创建一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			// 最大限度是50000
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			if(CollectionUtils.isNotEmpty(list)) {
				numSheet=dateTotal/maxNumOneSheet+1;
				for (int i = 1; i <= numSheet; i++) {
					
					//创建不同的sheet
					HSSFSheet sheet = wb.createSheet(sheetName+i);
					// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
					HSSFRow row = sheet.createRow((int) 0);
					// 第四步，创建单元格，并设置值表头 设置表头居中
					HSSFCellStyle style = wb.createCellStyle();
					style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
					// 合并单元格
					CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 12); // 起始行, 终止行, 起始列, 终止列
					sheet.addMergedRegion(cra);
					HSSFCell cell = row.createCell((short) 0);
					cell.setCellValue("在线课程学习统计");
					cell.setCellStyle(style);

					// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
					HSSFRow row2 = sheet.createRow((int) 1);
					// 第四步，创建单元格
					CellRangeAddress cra2 = new CellRangeAddress(1, 1, 0, 3);
					sheet.addMergedRegion(cra2);
					HSSFCell cell2 = row2.createCell((short) 0);
					cell2.setCellValue("统计周期： " + startDate + "~" + endDate);

					HSSFRow row3 = sheet.createRow((int) 2);
					row3.createCell((short) 0).setCellValue("用户名");
					row3.createCell((short) 1).setCellValue("姓名");
					row3.createCell((short) 2).setCellValue("工号");
					row3.createCell((short) 3).setCellValue("课程名称");
					row3.createCell((short) 4).setCellValue("总学习时长(分钟)");
					row3.createCell((short) 5).setCellValue("学习开始时间");
					row3.createCell((short) 6).setCellValue("最后一次学习时间");
					row3.createCell((short) 7).setCellValue("首次完成时间");
					row3.createCell((short) 8).setCellValue("学习次数");
					row3.createCell((short) 9).setCellValue("学习状态");
					row3.createCell((short) 10).setCellValue("用户当前状态");
					row3.createCell((short) 11).setCellValue("所在部门");


					// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
					if(list!=null&&list.size()>0) {
					ReportStudyCourseAccountDetailsVo rscv =null;
					for (int j = 0; j < maxNumOneSheet; j++) {
						row = sheet.createRow(j + 3);
						int a=j+(i-1)*maxNumOneSheet;
						if(a>=list.size()) {
							break;
						}
						rscv=list.get(a);
						if(rscv!=null) {
							// 第四步，创建单元格，并设置值
							row.createCell((short) 0).setCellValue(rscv.getAccountName());
							row.createCell((short) 1).setCellValue(rscv.getAccountFullName());
							System.out.println(rscv.getWorkNum());
							if(rscv.getWorkNum()!=null&&!rscv.getWorkNum().equals("null")) {
								row.createCell((short) 2).setCellValue(rscv.getWorkNum());
							}
							row.createCell((short) 3).setCellValue(rscv.getCourseName());
							row.createCell((short) 4).setCellValue(rscv.getTotalDuration());
							if(rscv!=null&&rscv.getLastTime()!=null) {
								row.createCell((short) 6).setCellValue(df.format(rscv.getLastTime()));
							}
							if(rscv!=null&&rscv.getFilstFinishTime()!=null) {
								row.createCell((short) 7).setCellValue(df.format(rscv.getFilstFinishTime()));
							}
							if(rscv.getStudyNum()!=null) {
								row.createCell((short) 8).setCellValue(rscv.getStudyNum());
							}
							else {
								row.createCell((short) 8).setCellValue(0);
							}
							if(rscv.getStudystate()==0) {
								row.createCell((short) 9).setCellValue("未开始");
							}
							if(rscv.getStudystate()==1) {
								row.createCell((short) 9).setCellValue("学习中");
							}
							if(rscv.getStudystate()==2) {
								row.createCell((short) 9).setCellValue("已结束");
							}
							if (rscv.getAccountState()!=null){
			                    row.createCell((short) 10).setCellValue(rscv.getAccountState()==0?"禁用":"启用");
			                }
							row.createCell((short) 11).setCellValue(rscv.getOrgName());
							//taskDetail(taskContext.getTaskId(), "按个人统计---下载学习明细数据导出数据第+" + i + "+行成功");
						}
					   }
					}
				}
			}
			
			String fileName = "按个人统计---下载学习明细数据" + df2.format(submitTime) + ".xls";
			String requestPath = FileConstant.SAVE_PATH;
			//String requestPath = "E:\\home";
			File fileDir = new File(requestPath);
			if (!fileDir.exists()) {
				fileDir.mkdir();
			}
			String path = requestPath + fileName;
			FileOutputStream os = null;
			File file = null;
			try {
				os = new FileOutputStream(path);
				wb.write(os);
				// 阿里云返回url
				upLoadUrl = OssUpload.upload(path, fileName);
				file = new File(path);
				success(taskContext, "成功", upLoadUrl);
			} catch (Exception e1) {
				e1.printStackTrace();
				fail(taskContext, "写入过程中发生错误");
				logger.error("上传过程中发生错误，请查看日志");
			} finally {
				if (os != null) {
					os.close();
				}
				if (wb != null) {
					wb.close();
				}
				if (file != null) {
					file.delete();
				}
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			fail(taskContext, "按个人统计---下载学习明细数据导出过程中发生错误，请查看日志");
			logger.error("按个人统计---下载学习明细数据导出过程中发生错误，请查看日志");
		}
		return upLoadUrl;
	}
}
