package com.swfarm.biz.product.web;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;

import com.swfarm.biz.product.bo.ProductDescription;
import com.swfarm.biz.product.bo.StockKeepingUnit;
import com.swfarm.biz.product.srv.ProductService;
import com.swfarm.pub.utils.SpringUtils;

public class ImportProductDescriptionsController extends AbstractController {

	private ProductService productService;

	public void setProductService(ProductService productService) {
		this.productService = productService;
	}

	protected ModelAndView handleRequestInternal(HttpServletRequest req,
			HttpServletResponse res) throws Exception {
		importProductDescriptions(new File("E:\\70000-Beschreibung.xls"));
		return new ModelAndView(
				"redirect:/product/search_stockkeepingunits.shtml");
	}

	protected void importProductDescriptions(File file) {
		try {
			InputStream is = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
			HSSFSheet sheet = wb.getSheetAt(0);
			List<String[]> productDescriptionList = new ArrayList<String[]>();
			int rowIndex = 0;
			for (Row row : sheet) {
				if (rowIndex++ > 0) {
					String[] productDescription = new String[10];
					Cell articleNumberCell = row.getCell(0);
					Object cellValue = getCellValue(articleNumberCell);
					if (cellValue != null) {
						if (articleNumberCell != null) {
							productDescription[0] = cellValue.toString();
						}
						Cell germanTitleCell = row.getCell(1);
						if (germanTitleCell != null) {
							cellValue = getCellValue(germanTitleCell);
							productDescription[1] = cellValue.toString();
						}
						Cell germanDescriptionCell = row.getCell(2);
						if (germanDescriptionCell != null) {
							cellValue = getCellValue(germanDescriptionCell);
							productDescription[2] = cellValue.toString();
						}
						Cell englishTitleCell = row.getCell(3);
						if (englishTitleCell != null) {
							cellValue = getCellValue(englishTitleCell);
							productDescription[3] = cellValue.toString();
						}
						Cell englishDescriptionCell = row.getCell(4);
						if (englishDescriptionCell != null) {
							cellValue = getCellValue(englishDescriptionCell);
							productDescription[4] = cellValue.toString();
						}
						Cell franceTitleCell = row.getCell(5);
						if (franceTitleCell != null) {
							cellValue = getCellValue(franceTitleCell);
							productDescription[5] = cellValue.toString();
						}
						Cell franceDescriptionCell = row.getCell(6);
						if (franceDescriptionCell != null) {
							cellValue = getCellValue(franceDescriptionCell);
							productDescription[6] = cellValue.toString();
						}
						if (StringUtils.isNotEmpty(productDescription[0])) {
							productDescriptionList.add(productDescription);
						}
					}
				}
			}
			for (String[] productDescription : productDescriptionList) {
				System.err.println("*************************************");
				System.err.println(productDescription[0]);
				System.err.println(productDescription[1]);
				System.err.println(productDescription[2]);
				System.err.println(productDescription[3]);
				System.err.println(productDescription[4]);
				System.err.println(productDescription[5]);
				System.err.println(productDescription[6]);
				System.err.println("*************************************");
			}
			importProductDescriptions(productDescriptionList);
		} catch (FileNotFoundException e) {
			logger.warn(e.getMessage(), e);
		} catch (IOException e) {
			logger.warn(e.getMessage(), e);
		}
	}

	private Object getCellValue(Cell cell) {
		Object cellValue = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			cellValue = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				cellValue = cell.getDateCellValue();
			} else {
				NumberFormat numberFormat = NumberFormat.getNumberInstance();
				numberFormat.setMinimumFractionDigits(0);
				numberFormat.setGroupingUsed(false);
				cellValue = numberFormat.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			cellValue = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			cellValue = cell.getCellFormula();
			break;
		default:
			cellValue = null;
		}
		return cellValue;
	}

	private void importProductDescriptions(List<String[]> productDescriptionList) {
		for (Iterator<String[]> iter = productDescriptionList.iterator(); iter
				.hasNext();) {
			String[] productDescription = iter.next();
			String articleNumber = productDescription[0];
			StockKeepingUnit stockKeepingUnit = this.productService
					.findStockKeepingUnitByArticleNumber(articleNumber);
			if (stockKeepingUnit != null) {
				if (StringUtils.isNotEmpty(productDescription[1])) {
					ProductDescription productDesc = this.productService
							.findProductDescriptionByArticleNumberAndLanguage(
									articleNumber, "de");
					if (productDesc == null) {
						productDesc = new ProductDescription();
						productDesc.setLanguage("de");
						productDesc.setTitle(productDescription[1]);
						productDesc.setDescription(productDescription[2]);
						this.productService.saveProductDescription(productDesc);
					}
				}
				if (StringUtils.isNotEmpty(productDescription[3])) {
					ProductDescription productDesc = this.productService
							.findProductDescriptionByArticleNumberAndLanguage(
									articleNumber, "en");
					if (productDesc == null) {
						productDesc = new ProductDescription();
						productDesc.setLanguage("en");
						productDesc.setTitle(productDescription[3]);
						productDesc.setDescription(productDescription[4]);
						this.productService.saveProductDescription(productDesc);
					}
				}
				if (StringUtils.isNotEmpty(productDescription[5])) {
					ProductDescription productDesc = this.productService
							.findProductDescriptionByArticleNumberAndLanguage(
									articleNumber, "fr");
					if (productDesc == null) {
						productDesc = new ProductDescription();
						productDesc.setLanguage("fr");
						productDesc.setTitle(productDescription[5]);
						productDesc.setDescription(productDescription[6]);
						this.productService.saveProductDescription(productDesc);
					}
				}
			}
		}
	}

	public static void main(String[] args) {
		FileSystemXmlApplicationContext context = new FileSystemXmlApplicationContext(
				new String[] {
						"E:\\Development\\projects\\pms_v3\\server\\pms\\ROOT\\WEB-INF\\dataAccessContext.xml",
						"E:\\Development\\projects\\pms_v3\\server\\pms\\ROOT\\WEB-INF\\serviceContext.xml",
						"E:\\Development\\projects\\pms_v3\\server\\pms\\ROOT\\WEB-INF\\hsClientContext.xml" });
		SpringUtils.setApplicationContext(context);
		ProductService productService = (ProductService) SpringUtils
				.getBean("productService");
		ImportProductDescriptionsController importer = new ImportProductDescriptionsController();
		importer.setProductService(productService);
		importer.importProductDescriptions(new File(
				"E:\\70000-Beschreibung.xls"));

	}
}
