package com.taover.bazhuayun.analysis.web.service;

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

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import cn.hutool.poi.excel.ExcelUtil;

@Service
public class ExportCenterService {
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	public File wareOrderFile(Integer tenantId, Date startDate, Date endDate) throws IOException {
		//查询agentID
		String agentIdQuery = "select agent_id from wxorder_agent_pc where tenant_id="+tenantId+" limit 1 ";
		Integer agentId = this.jdbcTemplate.queryForObject(agentIdQuery, Integer.class);
		
		//查询仓库对应群列表
		String wareGroupQuery = "select wx_group_ssid,wx_group_nickname,name from wxorder_ware where tenant_id="+tenantId;
		List<Map<String, Object>> groupData = this.jdbcTemplate.queryForList(wareGroupQuery);
		
		//依据群id，获取数据列表
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String startDateStr = sdf.format(startDate)+" 00:00:00";
		String endDateStr = sdf.format(endDate)+" 00:00:00";
		List<Map<String, Object>> messageData = new ArrayList<Map<String, Object>>();
		String messageQuery = "select room_wxid,sender_nickname,created_at,file_path from bzyun_wechat.messages where created_at>'"+startDateStr+"' and created_at<'"+endDateStr+"' and agent_id="+agentId+" and kind='FILE' and file_path not like '%【异常物流信息】%' and recall=0 and room_wxid=? ";
		for(Map<String, Object> item: groupData) {
			String wxGroupSsid = item.get("wx_group_ssid").toString();
			List<Map<String, Object>> itemData = this.jdbcTemplate.queryForList(messageQuery, wxGroupSsid);
			for(Map<String, Object> messageItem: itemData) {
				messageItem.putAll(item);
			}
			messageData.addAll(itemData);
		}
		
		//生成本地excel文件
		List<List<Object>> data = new ArrayList<List<Object>>();
		List<Object> headerData = new ArrayList<Object>();
		headerData.add("群名称");
		headerData.add("room_wxid");
		headerData.add("创建时间");
		headerData.add("发送人昵称");
		headerData.add("文件路径");
		headerData.add("仓库名称");
		data.add(headerData);
		for(Map<String, Object> item: messageData) {
			List<Object> itemData = new ArrayList<Object>();
			itemData.add(item.get("wx_group_nickname"));
			itemData.add(item.get("room_wxid"));
			itemData.add(item.get("created_at"));
			itemData.add(item.get("sender_nickname"));
			itemData.add(item.get("file_path"));
			itemData.add(item.get("name"));
			data.add(itemData);
		}
		File excelFile = File.createTempFile("wareorderfile", ".xlsx");
		ExcelUtil.getWriter(true).write(data).flush(excelFile);		
		return excelFile;
	}

}