package com.swfarm.biz.chain.web;

import java.io.OutputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;

import com.swfarm.biz.chain.bo.SaleStatistic;
import com.swfarm.biz.chain.bo.SkuSaleStatisticReport;
import com.swfarm.biz.chain.bo.SkuSaleStatisticReportItem;
import com.swfarm.biz.chain.srv.ChainService;
import com.swfarm.pub.utils.DateUtils;
import com.swfarm.pub.utils.ImageUtils;

public class DownloadSkuSaleStatisticReportController extends
		AbstractController {

	private static final double MAGIC_FACTOR = 1.112877583;

	private ChainService chainService;

	public void setChainService(ChainService chainService) {
		this.chainService = chainService;
	}

	protected ModelAndView handleRequestInternal(HttpServletRequest req,
			HttpServletResponse res) throws Exception {
		Long skuSaleStatisticReportId = new Long(req.getParameter("id"));
		SkuSaleStatisticReport skuSaleStatisticReport = this.chainService
				.findSkuSaleStatisticReport(skuSaleStatisticReportId);
		List titles = new ArrayList();
		CollectionUtils.addAll(titles, new String[] { "货号", "状态", "录入时间", "账户",
				"国家", "产品经理", "供应商" });
		for (Iterator iter = skuSaleStatisticReport
				.getSkuSaleStatisticReportItems().iterator(); iter.hasNext();) {
			SkuSaleStatisticReportItem skuSaleStatisticReportItem = (SkuSaleStatisticReportItem) iter
					.next();
			skuSaleStatisticReportItem.populateSaleStatistics();
		}
		if (CollectionUtils.isNotEmpty(skuSaleStatisticReport
				.getSkuSaleStatisticReportItems())) {
			SkuSaleStatisticReportItem skuSaleStatisticReportItem = (SkuSaleStatisticReportItem) skuSaleStatisticReport
					.getSkuSaleStatisticReportItems().iterator().next();
			for (Iterator iter = skuSaleStatisticReportItem
					.getSaleStatisticList().iterator(); iter.hasNext();) {
				SaleStatistic saleStatistic = (SaleStatistic) iter.next();
				titles.add(DateUtils.dateToString(saleStatistic.getStartDate(),
						"yyyy/MM/dd")
						+ "~"
						+ DateUtils.dateToString(saleStatistic.getEndDate(),
								"yyyy/MM/dd"));
			}
		}
		titles.add("增长数量");
		titles.add("增长率");
		String productManagerNo = req.getParameter("productManagerNo");
		String vendorCode = req.getParameter("vendorCode");
		String lowerFluctuationQuantityStr = req
				.getParameter("lowerFluctuationQuantity");
		String higherFluctuationQuantityStr = req
				.getParameter("higherFluctuationQuantity");
		String lowerFluctuationRateStr = req
				.getParameter("lowerFluctuationRate");
		String higherFluctuationRateStr = req
				.getParameter("higherFluctuationRate");
		skuSaleStatisticReport.setProductManagerNo(productManagerNo);
		skuSaleStatisticReport.setVendorCode(vendorCode);
		if (StringUtils.isNotEmpty(lowerFluctuationQuantityStr)) {
			skuSaleStatisticReport.setLowerFluctuationQuantity(new Double(
					lowerFluctuationQuantityStr));
		}
		if (StringUtils.isNotEmpty(higherFluctuationQuantityStr)) {
			skuSaleStatisticReport.setHigherFluctuationQuantity(new Double(
					higherFluctuationQuantityStr));
		}
		if (StringUtils.isNotEmpty(lowerFluctuationRateStr)) {
			skuSaleStatisticReport.setLowerFluctuationRate(new Double(
					lowerFluctuationRateStr));
		}
		if (StringUtils.isNotEmpty(higherFluctuationRateStr)) {
			skuSaleStatisticReport.setHigherFluctuationRate(new Double(
					higherFluctuationRateStr));
		}
		XSSFWorkbook wb = new XSSFWorkbook();
		Map<String, CellStyle> styles = createStyles(wb);
		XSSFSheet sheet = wb.createSheet("单品销量统计");
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(false);
		sheet.setAutobreaks(true);
		printSetup.setFitHeight((short) 1);
		printSetup.setFitWidth((short) 1);
		XSSFRow row = null;
		XSSFCell cell = null;
		int index = 0;
		for (Iterator iter = titles.iterator(); iter.hasNext();) {
			iter.next();
			sheet.setColumnWidth(index++, (int) (20 * 256 * MAGIC_FACTOR));
		}
		int rowCounter = 0;
		XSSFRow headerRow = sheet.createRow(rowCounter++);
		headerRow.setHeightInPoints(28f);
		CellStyle style = wb.createCellStyle();
		index = 0;
		for (Iterator iter = titles.iterator(); iter.hasNext();) {
			String title = (String) iter.next();
			cell = headerRow.createCell(index);
			cell.setCellValue(title);
			cell.setCellStyle(styles.get("header"));
			index++;
		}
		for (Iterator itemIter = skuSaleStatisticReport
				.getSkuSaleStatisticReportItemList().iterator(); itemIter
				.hasNext();) {
			SkuSaleStatisticReportItem skuSaleStatisticReportItem = (SkuSaleStatisticReportItem) itemIter
					.next();
			String styleName = "cell_normal";
			if ((skuSaleStatisticReport.getHigherFluctuationQuantity() != null
					&& skuSaleStatisticReportItem.getFluctuationQuantity() != null && skuSaleStatisticReportItem
					.getFluctuationQuantity() > skuSaleStatisticReport
					.getHigherFluctuationQuantity())
					&& (skuSaleStatisticReport.getHigherFluctuationRate() != null
							&& skuSaleStatisticReportItem.getFluctuationRate() != null && skuSaleStatisticReportItem
							.getFluctuationRate() > skuSaleStatisticReport
							.getHigherFluctuationRate())) {
				styleName = "cell_normal_red";
			} else if ((skuSaleStatisticReport.getLowerFluctuationQuantity() != null
					&& skuSaleStatisticReportItem.getFluctuationQuantity() != null && skuSaleStatisticReportItem
					.getFluctuationQuantity() < skuSaleStatisticReport
					.getLowerFluctuationQuantity())
					&& (skuSaleStatisticReport.getLowerFluctuationRate() != null
							&& skuSaleStatisticReportItem.getFluctuationRate() != null && skuSaleStatisticReportItem
							.getFluctuationRate() < skuSaleStatisticReport
							.getLowerFluctuationRate())) {
				styleName = "cell_normal_green";
			}
			int rowIndex = rowCounter++;
			row = sheet.createRow(rowIndex);
			int cellCounter = 0;
			cell = row.createCell(cellCounter++);
			cell.setCellValue(skuSaleStatisticReportItem.getArticleNumber());
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			cell
					.setCellValue(skuSaleStatisticReportItem
							.getSkuLifeCyclePhase());
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			if (skuSaleStatisticReportItem.getEnrollDate() != null) {
				cell.setCellValue(DateUtils.dateToString(
						skuSaleStatisticReportItem.getEnrollDate(),
						"yyyy/MM/dd"));
			}
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			cell.setCellValue(skuSaleStatisticReportItem.getAccountNumber());
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			cell.setCellValue(skuSaleStatisticReportItem.getCountry());
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			cell.setCellValue(skuSaleStatisticReportItem.getProductManagerNo());
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			cell.setCellValue(skuSaleStatisticReportItem.getVendorCode());
			cell.setCellStyle(styles.get(styleName));
			for (Iterator statisticIter = skuSaleStatisticReportItem
					.getSaleStatisticList().iterator(); statisticIter.hasNext();) {
				SaleStatistic saleStatistic = (SaleStatistic) statisticIter
						.next();
				cell = row.createCell(cellCounter++);
				cell.setCellValue(saleStatistic.getQuantity());
				cell.setCellStyle(styles.get(styleName));
			}
			cell = row.createCell(cellCounter++);
			cell.setCellValue(skuSaleStatisticReportItem
					.getFluctuationQuantity());
			cell.setCellStyle(styles.get(styleName));
			cell = row.createCell(cellCounter++);
			if (skuSaleStatisticReportItem.getFluctuationRate() != null) {
				NumberFormat percentageFormat = NumberFormat
						.getPercentInstance();
				percentageFormat.setMinimumFractionDigits(1);
				cell
						.setCellValue(percentageFormat
								.format(skuSaleStatisticReportItem
										.getFluctuationRate()));
			}
			cell.setCellStyle(styles.get(styleName));
		}
		res.setContentType("application/ms-excel");
		String fileName = "单品销售统计.xls";
		res.setHeader("Content-Disposition", "attachment; filename="
				+ java.net.URLEncoder.encode(fileName, "UTF-8"));
		OutputStream out = res.getOutputStream();
		wb.write(out);
		return null;
	}

	private static Map<String, CellStyle> createStyles(XSSFWorkbook wb) {
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		DataFormat df = wb.createDataFormat();

		CellStyle style;
		Font headerFont = wb.createFont();
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short) 10);
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setFont(headerFont);
		styles.put("header", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setFont(headerFont);
		style.setDataFormat(df.getFormat("d-mmm"));
		styles.put("header_date", style);

		Font font1 = wb.createFont();
		font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font1.setFontHeightInPoints((short) 10);
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setFont(font1);
		styles.put("cell_b", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setFont(font1);
		styles.put("cell_b_centered", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setFont(font1);
		style.setDataFormat(df.getFormat("d-mmm"));
		styles.put("cell_b_date", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setFont(font1);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setDataFormat(df.getFormat("d-mmm"));
		styles.put("cell_g", style);

		Font font2 = wb.createFont();
		font2.setColor(IndexedColors.BLUE.getIndex());
		font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setFont(font2);
		styles.put("cell_bb", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setFont(font1);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setDataFormat(df.getFormat("d-mmm"));
		styles.put("cell_bg", style);

		Font font3 = wb.createFont();
		font3.setFontHeightInPoints((short) 10);
		font3.setColor(IndexedColors.DARK_BLUE.getIndex());
		font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setFont(font3);
		style.setWrapText(true);
		styles.put("cell_h", style);

		Font normalFont = wb.createFont();
		normalFont.setFontHeightInPoints((short) 10);
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setWrapText(true);
		style.setFont(normalFont);
		styles.put("cell_normal", style);

		Font redNormalFont = wb.createFont();
		redNormalFont.setFontHeightInPoints((short) 10);
		redNormalFont.setColor(IndexedColors.RED.getIndex());
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setWrapText(true);
		style.setFont(redNormalFont);
		styles.put("cell_normal_red", style);

		Font greenNormalFont = wb.createFont();
		greenNormalFont.setFontHeightInPoints((short) 10);
		greenNormalFont.setColor(IndexedColors.GREEN.getIndex());
		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setWrapText(true);
		style.setFont(greenNormalFont);
		styles.put("cell_normal_green", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		styles.put("cell_normal_centered", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setWrapText(true);
		style.setDataFormat(df.getFormat("d-mmm"));
		styles.put("cell_normal_date", style);

		style = createBorderedStyle(wb);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setIndention((short) 1);
		style.setWrapText(true);
		styles.put("cell_indented", style);

		style = createBorderedStyle(wb);
		style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		styles.put("cell_blue", style);

		return styles;
	}

	private static CellStyle createBorderedStyle(XSSFWorkbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		return style;
	}

	private static byte[] getImage(String url) {
		return ImageUtils.generateImage(url);
	}
}
