/*******************************************************************************
 * Copyright 2Fi Business Solutions Ltd.
 *
 * This code is copyrighted. Under no circumstances should any party, people,
 * or organization should redistribute any portions of this code in any form,
 * either verbatim or through electronic media, to any third parties, unless
 * under explicit written permission by 2Fi Business Solutions Ltd.
 ******************************************************************************/
package com.ffii.tbms.common.service;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ffii.core.dao.JdbcDao;
import com.ffii.core.utils.ExcelUtils;
import com.ffii.core.utils.MapUtils;
import com.ffii.core.utils.SecurityUtils;
import com.ffii.core.web.AbstractService;
import com.ffii.tbms.common.MaterialBand;
import com.ffii.tbms.common.MaterialItem;
import com.ffii.tbms.common.dao.MaterialBandDao;
import com.ffii.tbms.common.dao.MaterialItemDao;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;


@Service
public class MaterialService extends AbstractService {

	@Autowired
	private JdbcDao jdbcDao;

	@Autowired
	private MaterialBandDao bandDao;
	
	@Autowired
	private MaterialItemDao itemDao;

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdateBand(MaterialBand instance) {
		return bandDao.saveOrUpdate(instance);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdateBandItem(MaterialItem instance) {
		return itemDao.saveOrUpdate(instance);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public MaterialBand findBand(Integer id) {
		return bandDao.find(id);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public MaterialItem findItem(Integer id) {
		return itemDao.find(id);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchBands() {
		Map<String, Object> args = new HashMap<>();
		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());

		StringBuilder sql = new StringBuilder(" SELECT mb.*, f.filename, f.skey "
		+" FROM material_band mb "
		+" LEFT JOIN files f ON f.id = mb.fileId "
		+" WHERE mb.deleted = 0 "
		+" AND mb.sysGroupId = :sysGroupId ");
		
		return jdbcDao.queryForList(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> loadBand(int id) {

		Map<String, Object> args = new HashMap<>();
		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		args.put("id", id);

		StringBuilder sql = new StringBuilder(" SELECT "
		+" mb.* "
		+" FROM material_band mb "
		+" WHERE id = :id "
		+" AND mb.sysGroupId = :sysGroupId ");

		return jdbcDao.queryForMap(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchBandItems(int bandId) {

		Map<String, Object> args = new HashMap<>();
		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		args.put("bandId", bandId);

		StringBuilder sql = new StringBuilder(
			" SELECT mi.* "
			+" FROM material_item mi "
			+" WHERE mi.deleted =0 "
			+" AND mi.bandId = :bandId "
			+" AND mi.sysGroupId = :sysGroupId "
			+" ORDER BY mi.orderIdx, mi.id ");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	
	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> loadBandItem(int id) {

		Map<String, Object> args = new HashMap<>();
		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		args.put("id", id);

		StringBuilder sql = new StringBuilder(" SELECT "
		+" mi.* "
		+" FROM material_item mi "
		+" WHERE mi.id = :id"
		+" AND mi.sysGroupId = :sysGroupId " );

		return jdbcDao.queryForMap(sql.toString(), args);
	}

	
	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchBandIcon(int bandId) {

		Map<String, Object> args = new HashMap<>();
		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		args.put("bandId", bandId);

		StringBuilder sql = new StringBuilder(
			" SELECT f.filename, f.id, f.skey, f.imageHeight, f.imageWidth "
			+" FROM files_ref fr "
			+" LEFT JOIN files f ON fr.fileId = f.id "
			+" WHERE fr.refType = 'bandIcon' AND fr.refId = :bandId "
			+" AND fr.sysGroupId = :sysGroupId ");
			
		return jdbcDao.queryForList(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public boolean updateItemOrderIdx(int id, int orderIdx) {
		return jdbcDao.executeUpdate("UPDATE material_item SET orderIdx = :orderIdx WHERE id = :id",
				MapUtils.toHashMap("id", id, "orderIdx", orderIdx)) > 0;
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public boolean importDataFromExcel(Workbook wb) {
		Sheet sheet = wb.getSheetAt(0);
		int bankRow = 0;
		int formulaRow = 1;
		int itemnameRow = 2;
		int itemname2Row = 3;
		int bunchRow = 4;
		int costRow = 5;
		int suitRow = 6;
		int jacketRow = 7;
		int overcoatRow = 8;
		int pantsRow = 9;
		int otherRow = 10;

		int rownum = 1;
		boolean next = true;

		int bandId = 0;
		while(next){
			Row row = sheet.getRow(rownum);
			if (row == null){
				next = false;
				break;
			}

			String bankName=null;
			if(row.getCell(bankRow)!=null){
				bankName = ExcelUtils.getStringValue(row.getCell(bankRow));
			}

			String formula=null;
			if(row.getCell(formulaRow)!=null){
				formula = ExcelUtils.getStringValue(row.getCell(formulaRow));
			}

			if(bankName != null){
				MaterialBand band = new MaterialBand();
				band.setName(bankName);
				band.setExpectedPriceFormula(formula);
				bandId = saveOrUpdateBand(band);
			}

			String itemName=null;
			if(row.getCell(itemnameRow)!=null){
				itemName = ExcelUtils.getStringValue(row.getCell(itemnameRow));
			}

			if(row.getCell(itemname2Row)!=null){
				String name2 = ExcelUtils.getStringValue(row.getCell(itemname2Row));
				if(name2.length()>0)
					itemName = itemName==null?name2:itemName+" ("+name2+")";
			}

			String bunch = null;
			if(row.getCell(bunchRow)!=null){
				bunch = ExcelUtils.getStringValue(row.getCell(bunchRow));
			}

			BigDecimal cost = null;
			if(row.getCell(costRow)!=null){
				cost = ExcelUtils.getDecimalValue(row.getCell(costRow),null);
			}

			BigDecimal suit = null;
			if(row.getCell(suitRow)!=null){
				suit = ExcelUtils.getDecimalValue(row.getCell(suitRow),null);
			}

			BigDecimal jacket = null;
			if(row.getCell(jacketRow)!=null){
				jacket = ExcelUtils.getDecimalValue(row.getCell(jacketRow),null);
			}

			BigDecimal overcoat = null;
			if(row.getCell(overcoatRow)!=null){
				overcoat = ExcelUtils.getDecimalValue(row.getCell(overcoatRow),null);
			}

			BigDecimal pants = null;
			if(row.getCell(pantsRow)!=null){
				pants = ExcelUtils.getDecimalValue(row.getCell(pantsRow),null);
			}

			BigDecimal other = null;
			if(row.getCell(otherRow)!=null){
				other = ExcelUtils.getDecimalValue(row.getCell(otherRow),null);
			}

			MaterialItem item = new MaterialItem();
			item.setBandId(bandId);
			item.setName(itemName);
			item.setBunch(bunch);
			item.setCost(cost);
			item.setSuitPrice(suit);
			item.setJacketPrice(jacket);
			item.setOvercoatPrice(overcoat);
			item.setPantsPrice(pants);
			item.setOtherPrice(other);

			saveOrUpdateBandItem(item);

			rownum++;

		}
		return true;
	}

}
