package project.web.admin.impl.report;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;

import kernel.util.Arith;
import kernel.util.DateUtils;
import kernel.util.StringUtils;
import kernel.web.Page;
import kernel.web.PagedQueryDao;
import project.Constants;
import project.party.recom.UserRecomService;
import project.web.admin.service.report.AdminAllStatisticsService;

public class AdminAllStatisticsServiceImpl implements AdminAllStatisticsService {

	private PagedQueryDao pagedQueryDao;
	
	private UserRecomService userRecomService;
	
	private NamedParameterJdbcOperations namedParameterJdbcTemplate;
	
	public Page pagedQuery(int pageNo, int pageSize,String startTime,String endTime,String loginPartyId) {
		Map<String,Object> parameters = new HashMap<String,Object>();
		StringBuffer queryString = new StringBuffer("SELECT ");
		queryString.append("DATE_FORMAT(ud.CREATE_TIME,\"%Y-%m-%d\") AS date, ");//日期
		queryString.append("SUM(ud.RECHARGE_DAPP) AS recharge_dapp,SUM(ud.RECHARGE) AS recharge,SUM(ud.RECHARGE_USDT) AS recharge_usdt,SUM(ud.RECHARGE_USDC) AS recharge_usdc,SUM(ud.RECHARGE_ETH) AS recharge_eth,SUM(ud.RECHARGE_BTC) AS recharge_btc,IFNULL(SUM(ud.RECHARGE_HT),0) AS recharge_ht,IFNULL(SUM(ud.RECHARGE_LTC),0) AS recharge_ltc,"
				+ "SUM(ud.WITHDRAW_DAPP) AS withdraw_dapp,SUM(ud.WITHDRAW) AS withdraw,IFNULL(SUM(ud.WITHDRAW_ETH),0) AS withdraw_eth,IFNULL(SUM(ud.WITHDRAW_BTC),0) AS withdraw_btc,"
				+ "SUM(ud.RECHARGE_WITHDRAWAL_FEE) AS recharge_withdrawal_fee,SUM(ud.GIFT_MONEY) AS gift_money,SUM(ud.RECHARGE)-SUM(ud.WITHDRAW) AS balance_amount,IFNULL((recharge_usdt-withdraw),0) as difference,");//充提
		queryString.append("SUM(ud.AMOUNT) AS amount,SUM(ud.FEE) AS fee,SUM(ud.ORDER_INCOME) AS order_income, ");//永续
		queryString.append("SUM(ud.FINANCE_AMOUNT) AS finance_amount,SUM(ud.FINANCE_INCOME) AS finance_income, ");//理财
		queryString.append("SUM(ud.EXCHANGE_AMOUNT) AS exchange_amount,SUM(ud.EXCHANGE_FEE) AS exchange_fee,SUM(ud.EXCHANGE_INCOME) AS exchange_income,SUM(ud.COIN_INCOME) AS coin_income, ");//币币
		queryString.append("SUM(ud.FURTURES_AMOUNT) AS furtures_amount,SUM(ud.FURTURES_FEE) AS furtures_fee,SUM(ud.FURTURES_INCOME) AS furtures_income, ");//交割
		queryString.append("IFNULL(SUM(ud.MINER_AMOUNT),0) AS miner_amount,IFNULL(SUM(ud.MINER_INCOME),0) AS miner_income, ");//矿机
		queryString.append("IFNULL(SUM(ud.THIRD_RECHARGE_AMOUNT),0) AS third_recharge_amount, ");//三方充值
		queryString.append("IFNULL(SUM(ud.EXCHANGE_LEVER_AMOUNT),0) AS exchange_lever_amount,IFNULL(SUM(ud.EXCHANGE_LEVER_FEE),0) AS exchange_lever_fee,IFNULL(SUM(ud.EXCHANGE_LEVER_ORDER_INCOME),0) AS exchange_lever_order_income ");//币币杠杆
		queryString.append("FROM T_USERDATA ud ");
		
		queryString.append("WHERE 1=1 ");
		queryString.append("AND ud.ROLENAME ='"+Constants.SECURITY_ROLE_MEMBER+"' ");
		if (!StringUtils.isNullOrEmpty(loginPartyId)) {
			List children = this.userRecomService.findChildren(loginPartyId);
			if (children.size() == 0) {
				return new Page();
			}
			queryString.append(" and ud.PARTY_ID in (:children) ");
			parameters.put("children", children);
		}
		
		if (!StringUtils.isNullOrEmpty(startTime)) {
			queryString.append("AND DATE(ud.CREATE_TIME) >= DATE('" + startTime + " 00:00:00')  ");
		}
		if (!StringUtils.isNullOrEmpty(endTime)) {
			queryString.append("AND DATE(ud.CREATE_TIME) <= DATE('" + endTime + " 23:59:59')  ");
		}
		
		queryString.append("GROUP BY DATE(ud.CREATE_TIME) ");
		queryString.append("ORDER BY DATE(ud.CREATE_TIME) DESC ");
		Page page = this.pagedQueryDao.pagedQuerySQL(pageNo, pageSize, queryString.toString(), parameters);
		compute(page.getElements(),false);
		return page;
	}
	
	public Map<String,Object> daySumData(String loginPartyId,String day){
		Map<String,Object> parameters = new HashMap<String,Object>();
		StringBuffer queryString = new StringBuffer("SELECT ");
		queryString.append("IFNULL(SUM(ud.RECHARGE),0) AS recharge,IFNULL(SUM(ud.RECHARGE_USDT),0) AS recharge_usdt,IFNULL(SUM(ud.RECHARGE_USDC),0) AS recharge_usdc,IFNULL(SUM(ud.RECHARGE_ETH),0) AS recharge_eth,IFNULL(SUM(ud.RECHARGE_BTC),0) AS recharge_btc,IFNULL(SUM(ud.RECHARGE_HT),0) AS recharge_ht,IFNULL(SUM(ud.RECHARGE_LTC),0) AS recharge_ltc,"
				+ "IFNULL(SUM(ud.WITHDRAW),0) AS withdraw,IFNULL(SUM(ud.RECHARGE_WITHDRAWAL_FEE),0) AS recharge_withdrawal_fee,IFNULL(SUM(ud.RECHARGE_USDT)-SUM(ud.WITHDRAW),0) AS balance_amount, ");//充提
		queryString.append("IFNULL(SUM(ud.FEE),0) AS fee,IFNULL(SUM(ud.ORDER_INCOME),0) AS order_income, ");//永续 
		queryString.append("IFNULL(SUM(ud.FINANCE_AMOUNT),0) AS finance_amount,IFNULL(SUM(ud.FINANCE_INCOME),0) AS finance_income, ");//理财 
		queryString.append("IFNULL(SUM(ud.EXCHANGE_FEE),0) AS exchange_fee,IFNULL(SUM(ud.EXCHANGE_INCOME),0) AS exchange_income, ");//币币
		queryString.append("IFNULL(SUM(ud.FURTURES_FEE),0) AS furtures_fee,IFNULL(SUM(ud.FURTURES_INCOME),0) AS furtures_income, ");//交割 
		queryString.append("IFNULL(SUM(ud.MINER_AMOUNT),0) AS miner_amount,IFNULL(SUM(ud.MINER_INCOME),0) AS miner_income, ");//矿机
		queryString.append("IFNULL(SUM(ud.THIRD_RECHARGE_AMOUNT),0) AS third_recharge_amount, ");//三方充值
		queryString.append("IFNULL(SUM(ud.EXCHANGE_LEVER_AMOUNT),0) AS exchange_lever_amount,IFNULL(SUM(ud.EXCHANGE_LEVER_FEE),0) AS exchange_lever_fee,IFNULL(SUM(ud.EXCHANGE_LEVER_ORDER_INCOME),0) AS exchange_lever_order_income ");//币币杠杆
		queryString.append("FROM T_USERDATA ud ");
		queryString.append("WHERE 1=1 ");
		queryString.append("AND ud.ROLENAME ='"+Constants.SECURITY_ROLE_MEMBER+"' ");
		if (!StringUtils.isNullOrEmpty(loginPartyId)) {
			List children = this.userRecomService.findChildren(loginPartyId);
			if (children.size() == 0) {
				return new HashMap<String,Object>();
			}
			queryString.append(" and ud.PARTY_ID in (:children) ");
			parameters.put("children", children);
		}
		if (!StringUtils.isNullOrEmpty(day)) {
			queryString.append("AND DATE(ud.CREATE_TIME) = DATE(:day)  ");
			parameters.put("day", DateUtils.toDate(day));
		}
		List<Map<String, Object>> queryForList = this.namedParameterJdbcTemplate.queryForList( queryString.toString(), parameters);
		compute(queryForList,false);
		return queryForList.get(0);
	}
	/**
	 * 计算 统计时
	 * @param datas
	 * @param isSum
	 */
	private void compute(List<Map<String,Object>> datas,boolean isSum) {
		if(CollectionUtils.isEmpty(datas)) return;
		Double totle_income=0d;
		Double totle_fee = 0d;
		Double business_profit = 0d;//交易盈亏
		Double fin_miner_amount = 0d;//理财 矿机 交易额
		Double fin_miner_income = 0d;//理财 矿机 收益
		for(Map<String,Object> data:datas) {
			
			totle_income=0d;
			totle_fee = 0d;
			business_profit = 0d;
			fin_miner_amount = 0d;
			fin_miner_income = 0d;
			if(null != data.get("order_income"))
			data.put("order_income", Arith.sub(0, new Double(data.get("order_income").toString())));//订单收益负数
			if(null != data.get("finance_income"))
			data.put("finance_income", Arith.sub(0, new Double(data.get("finance_income").toString())));//理财收益负数
			if(null != data.get("exchange_income"))
//			data.put("exchange_income", Arith.sub(0, new Double(data.get("exchange_income").toString())));//币币收益负数
				data.put("exchange_income",0);//币币收益负数
			if(null != data.get("furtures_income"))
			data.put("furtures_income", Arith.sub(0, new Double(data.get("furtures_income").toString())));//交割收益负数
			if (null != data.get("miner_income"))
				data.put("miner_income", Arith.sub(0, new Double(data.get("miner_income").toString())));// 矿机收益负数
			if (null != data.get("exchange_lever_order_income"))
				data.put("exchange_lever_order_income", Arith.sub(0, new Double(data.get("exchange_lever_order_income").toString())));// 币币收益负数
			
			if(!dataExistNull(data)) continue;
			totle_income = Arith.add(totle_income,new Double(data.get("recharge_withdrawal_fee").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("order_income").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("fee").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("finance_income").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("exchange_fee").toString()));
			totle_income = Arith.add(totle_income,new Double(0));
//			totle_income = Arith.add(totle_income,new Double(data.get("exchange_income").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("furtures_fee").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("furtures_income").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("miner_income").toString()));
			totle_income = Arith.add(totle_income,new Double(data.get("exchange_lever_order_income").toString()));
			data.put("totle_income", totle_income);
			
			totle_fee = Arith.add(totle_fee, new Double(data.get("recharge_withdrawal_fee").toString()));
			totle_fee = Arith.add(totle_fee, new Double(data.get("fee").toString()));
			totle_fee = Arith.add(totle_fee, new Double(data.get("exchange_fee").toString()));
			totle_fee = Arith.add(totle_fee, new Double(data.get("furtures_fee").toString()));
			totle_fee = Arith.add(totle_fee, new Double(data.get("exchange_lever_fee").toString()));
			data.put("totle_fee", totle_fee);
			
			business_profit = Arith.add(business_profit, new Double(data.get("order_income").toString()));
			business_profit = Arith.add(business_profit, new Double(data.get("exchange_income").toString()));
			business_profit = Arith.add(business_profit, new Double(data.get("furtures_income").toString()));
			business_profit = Arith.add(business_profit, new Double(data.get("exchange_lever_order_income").toString()));
			data.put("business_profit", business_profit);
			
			fin_miner_amount = Arith.add(fin_miner_amount, new Double(data.get("finance_amount").toString()));
			fin_miner_amount = Arith.add(fin_miner_amount, new Double(data.get("miner_amount").toString()));
			data.put("fin_miner_amount", fin_miner_amount);
			
			fin_miner_income = Arith.add(fin_miner_income, new Double(data.get("finance_income").toString()));
			fin_miner_income = Arith.add(fin_miner_income, new Double(data.get("miner_income").toString()));
			data.put("fin_miner_income",  fin_miner_income);

			data.put("recharge_btc", new BigDecimal(data.get("recharge_btc").toString()).setScale(8, RoundingMode.FLOOR).toPlainString());//订单收益负数
		}
	}
	
	/**
	 * 统计的数据存在空时，不统计总额
	 * @param data
	 * @return
	 */
	private boolean dataExistNull(Map<String,Object> data) {
		if(null == data.get("recharge_withdrawal_fee")) return false;
		if(null == data.get("order_income")) return false;
		if(null == data.get("fee")) return false;
		if(null == data.get("finance_income")) return false;
		if(null == data.get("exchange_fee")) return false;
		if(null == data.get("exchange_income")) return false;
		if(null == data.get("furtures_fee")) return false;
		if(null == data.get("furtures_income")) return false;
		return true;
	}
	
	public Map<String,Object> sumDatas(String startTime,String endTime,String loginPartyId){
		Map<String,Object> parameters = new HashMap<String,Object>();
		StringBuffer queryString = new StringBuffer("SELECT ");
		queryString.append("IFNULL(SUM(ud.RECHARGE_DAPP),0) AS recharge_dapp,IFNULL(SUM(ud.RECHARGE),0) AS recharge,IFNULL(SUM(ud.RECHARGE_USDT),0) AS recharge_usdt,IFNULL(SUM(ud.RECHARGE_USDC),0) AS recharge_usdc,IFNULL(SUM(ud.RECHARGE_ETH),0) AS recharge_eth,IFNULL(SUM(ud.RECHARGE_BTC),0) AS recharge_btc,IFNULL(SUM(ud.RECHARGE_HT),0) AS recharge_ht,IFNULL(SUM(ud.RECHARGE_LTC),0) AS recharge_ltc,"
				+ "IFNULL(SUM(ud.WITHDRAW_DAPP),0) AS withdraw_dapp,IFNULL(SUM(ud.WITHDRAW),0) AS withdraw,IFNULL(SUM(ud.WITHDRAW_ETH),0) AS withdraw_eth,IFNULL(SUM(ud.WITHDRAW_BTC),0) AS withdraw_btc,"
				+ "IFNULL(SUM(ud.RECHARGE_WITHDRAWAL_FEE),0) AS recharge_withdrawal_fee,IFNULL(SUM(ud.GIFT_MONEY),0) AS gift_money,IFNULL(SUM(ud.RECHARGE)-SUM(ud.WITHDRAW),0) AS balance_amount, IFNULL((recharge_usdt-withdraw),0) as difference,");//充提
		queryString.append("IFNULL(SUM(ud.AMOUNT),0) AS amount,IFNULL(SUM(ud.FEE),0) AS fee,IFNULL(SUM(ud.ORDER_INCOME),0) AS order_income, ");//永续
		queryString.append("IFNULL(SUM(ud.FINANCE_AMOUNT),0) AS finance_amount,IFNULL(SUM(ud.FINANCE_INCOME),0) AS finance_income, ");//理财
		queryString.append("IFNULL(SUM(ud.EXCHANGE_AMOUNT),0) AS exchange_amount,IFNULL(SUM(ud.EXCHANGE_FEE),0) AS exchange_fee,IFNULL(SUM(ud.EXCHANGE_INCOME),0) AS exchange_income,IFNULL(SUM(ud.COIN_INCOME),0) AS coin_income, ");//币币
		queryString.append("IFNULL(SUM(ud.FURTURES_AMOUNT),0) AS furtures_amount,IFNULL(SUM(ud.FURTURES_FEE),0) AS furtures_fee,IFNULL(SUM(ud.FURTURES_INCOME),0) AS furtures_income, ");//交割
		queryString.append("IFNULL(SUM(ud.MINER_AMOUNT),0) AS miner_amount,IFNULL(SUM(ud.MINER_INCOME),0) AS miner_income, ");//矿机
		queryString.append("IFNULL(SUM(ud.THIRD_RECHARGE_AMOUNT),0) AS third_recharge_amount, ");//三方充值
		queryString.append("IFNULL(SUM(ud.EXCHANGE_LEVER_AMOUNT),0) AS exchange_lever_amount,IFNULL(SUM(ud.EXCHANGE_LEVER_FEE),0) AS exchange_lever_fee,IFNULL(SUM(ud.EXCHANGE_LEVER_ORDER_INCOME),0) AS exchange_lever_order_income ");//币币杠杆
		queryString.append("FROM T_USERDATA ud ");
		queryString.append("WHERE 1=1 ");
		queryString.append("AND ud.ROLENAME ='"+Constants.SECURITY_ROLE_MEMBER+"' ");
		if (!StringUtils.isNullOrEmpty(loginPartyId)) {
			List children = this.userRecomService.findChildren(loginPartyId);
			if (children.size() == 0) {
				return new HashMap<String, Object>();
			}
			queryString.append(" and ud.PARTY_ID in (:children) ");
			parameters.put("children", children);
		}
		if (!StringUtils.isNullOrEmpty(startTime)) {
			queryString.append("AND DATE(ud.CREATE_TIME) >= DATE('" + startTime + " 00:00:00')  ");
		}
		if (!StringUtils.isNullOrEmpty(endTime)) {
			queryString.append("AND DATE(ud.CREATE_TIME) <= DATE('" + endTime + " 23:59:59')  ");
		}
		queryString.append("ORDER BY DATE(ud.CREATE_TIME) DESC ");
		List<Map<String, Object>> queryForList = this.namedParameterJdbcTemplate.queryForList( queryString.toString(), parameters);
		compute(queryForList,true);
		return queryForList.get(0);
	}
	
	
	
	public String loadExportData(HttpServletResponse response, int pageSize,String startTime,String endTime,String loginPartyId) throws IOException {
		//生成数据信息
		int sheetNum = 0;
		// 生成表头
		Integer i = 0;
		// 在内存中保持100行，超过100行将被刷新到磁盘
		SXSSFWorkbook wb = new SXSSFWorkbook(100);
		Sheet sheet = wb.createSheet(); // 表，一个表包含多个行
		Drawing patriarch = sheet.createDrawingPatriarch();
		CellStyle style = wb.createCellStyle();
		Row row = null;// 行，一行包括多个单元格
		Cell cell = null;// 单元格
		Page page = null;
		int pageNo =1;
		
		Map<String,Integer[]> headMap = new LinkedHashMap<String,Integer[]>();
		headMap.put("日期", new Integer[] {1,1});
		headMap.put("充提", new Integer[] {0,9});
		headMap.put("永续合约", new Integer[] {0,2});
		headMap.put("理财收益", new Integer[] {1,1});
		headMap.put("币币", new Integer[] {0,2});
		headMap.put("交割合约", new Integer[] {0,2});
		headMap.put("收益", new Integer[] {1,1});
		
		createMergedHead(wb, sheet,headMap,i++);
		return "";
	}
	
	private void createMergedHead(SXSSFWorkbook wb,Sheet sheet,Map<String,Integer[]> headMap,int i) {
		Font font = wb.createFont();   
		font.setFontHeightInPoints((short) 10);   
		font.setFontName("Courier New");
		CellStyle style = wb.createCellStyle();
		style.setFont(font);   
		style.setWrapText(true);   
		style.setAlignment(HorizontalAlignment.CENTER);   
		style.setVerticalAlignment(VerticalAlignment.CENTER);  
		Row row = sheet.createRow(i);
		int rowPosition = 0;//行坐标
		int cellPosition = 0;//列坐标
		for(Entry<String, Integer[]> entry:headMap.entrySet()) {
			Cell cell = row.createCell(cellPosition);
			cell.setCellStyle(style);
			cell.setCellValue(entry.getKey());
			CellRangeAddress region = new CellRangeAddress(rowPosition, rowPosition+entry.getValue()[0], cellPosition, cellPosition+entry.getValue()[1]-1);
			sheet.addMergedRegion(region);
			cellPosition+=entry.getValue()[1];
		}
	}
	
	public List<Object[]> dataBachHandel(List<Map<String,Object>> list){
		List<Object[]> result = new ArrayList<Object[]>();
		int i = 0;
		for(Map<String,Object> data:list) {
			i = 0;
			Object[] objs = new Object[18];
			objs[i++] = data.get("date");
			
			
			objs[i++] = data.get("recharge_usdt");
			objs[i++] = data.get("recharge_eth");
			objs[i++] = data.get("recharge_btc");
			objs[i++] = data.get("recharge");
			objs[i++] = data.get("gift_money");
			objs[i++] = data.get("withdraw");
			objs[i++] = data.get("recharge_withdrawal_fee");//TODO
			double recharge_usdt = 0D;
			double withdraw  = 0D;
			if (null != data.get("recharge_usdt")) {
				recharge_usdt = Double.parseDouble(data.get("recharge_usdt").toString());
			}
			if (null != data.get("withdraw")) {
				withdraw = Double.parseDouble(data.get("withdraw").toString());
			}
			objs[i++] = Arith.sub(recharge_usdt, withdraw);
			objs[i++] = data.get("balance_amount");

//			objs[i++] = data.get("amount");
			objs[i++] = data.get("fee");
			objs[i++] = data.get("order_income");

//			objs[i++] = data.get("finance_amount");
			objs[i++] = data.get("finance_income");

//			objs[i++] = data.get("exchange_amount");
			objs[i++] = data.get("exchange_fee");
//			objs[i++] = data.get("exchange_income");
			objs[i++] = 0;

//			objs[i++] = data.get("furtures_amount");
			objs[i++] = data.get("furtures_fee");
			objs[i++] = data.get("furtures_income");
			
			objs[i++] = data.get("totle_income");
			
			result.add(objs);
		}
		return result;
	}
	
	
	public PagedQueryDao getPagedQueryDao() {
		return pagedQueryDao;
	}

	public void setPagedQueryDao(PagedQueryDao pagedQueryDao) {
		this.pagedQueryDao = pagedQueryDao;
	}

	public NamedParameterJdbcOperations getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setNamedParameterJdbcTemplate(NamedParameterJdbcOperations namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}
	public void setUserRecomService(UserRecomService userRecomService) {
		this.userRecomService = userRecomService;
	}
}