package com.kingdee.shr.customer.gtiit.handler;

import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

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

import org.apache.log4j.Logger;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.metadata.entity.FilterInfo;
import com.kingdee.bos.metadata.entity.FilterItemInfo;
import com.kingdee.bos.metadata.query.util.CompareType;
import com.kingdee.bos.sql.ParserException;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.base.syssetting.context.SHRContext;
import com.kingdee.shr.base.syssetting.exception.SHRWebException;
import com.kingdee.shr.compensation.web.handler.CmpEmpFilesDynamicListHandler;
import com.kingdee.shr.customer.gtiit.util.DateTimeUtils;

/**
 * 薪酬提报名单
 * @author xiaoxin
 *
 */
public class PaySalaryDynamicListHandler extends CmpEmpFilesDynamicListHandler{

	private static Logger logger =
	        Logger.getLogger("com.kingdee.shr.customer.gtiit.handler.PaySalaryDynamicListHandler");
	
	@Override
	protected FilterInfo getDefaultFilter(HttpServletRequest request, HttpServletResponse response)
			throws SHRWebException {
		Context ctx = SHRContext.getInstance().getContext();
		FilterInfo defaultFilter = super.getDefaultFilter(request, response);
		String today  = DateTimeUtils.dateFormat(new Date(), "yyyy-MM-dd");
		
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.DAY_OF_MONTH, 1);
		calendar.add(Calendar.MONTH, -1);
		Date lastDate = calendar.getTime();
		calendar.add(Calendar.MONTH, 2);
		Date nextDate = calendar.getTime();
		String lastDateStr = DateTimeUtils.dateFormat(lastDate, "yyyy-MM-dd");
		String nextDateStr = DateTimeUtils.dateFormat(nextDate, "yyyy-MM-dd");
		
		try {
			//档案失效日期大于今天
			FilterInfo personLeffectDateFilter = new FilterInfo("personcmp.leffectdate > '"+today+"'");
			//发薪任职失效日期大于今天  获取离职日期在上月或者本月
			FilterInfo empOrgLeffectDateFilter = new FilterInfo("(cmpemporelation.leffectdate > '"+today+"' or (pp.leftdate >= '"+lastDateStr+"' and pp.leftdate < '"+nextDateStr+"'))");
			//任职类型为主要任职
			FilterInfo assingTypeFilter = new FilterInfo("emporgrelation.assigntype = '1'");
			defaultFilter.mergeFilter(personLeffectDateFilter, "and");
			defaultFilter.mergeFilter(empOrgLeffectDateFilter, "and");
			defaultFilter.mergeFilter(assingTypeFilter, "and");
			logger.error("档案花名册过滤条件："+defaultFilter.toSql());
			
			String sql = "/*dialect*/SELECT DISTINCT \"personcmp\".fid AS \"personcmp.id\", \"person\".fnumber AS \"person.number\", "
					+ "\"fullorpart\".fname_l2 AS \"fullorpart.name\", \"adminorgunit\".fnumber AS \"adminorgunit.number\", "
					+ "\"cmphrorgunit\".fid AS \"cmphrorgunit.id\", \"cmpemporelation\".fusedefault AS \"cmpemporelation.usedefault\", "
					+ "\"person\".fname_l2 AS \"person.name\", \"adminorgunit\".fsortcode AS \"adminorgunit.sortcode\", "
					+ "\"position\".fnumber AS \"position.number\", \"person\".findex AS \"person.indexof\", \"pp\".fleftdate AS \"pp.leftdate\", "
					+ "\"person\".fhistoryrelateid AS \"person.id\", \"emporgrelation\".fassigntype AS \"emporgrelation.assigntype\", "
					+ "\"personcmp\".fissendsalary AS \"personcmp.issendsalary\", \"laborrela\".fenterdate AS \"laborrela.enterdate\", "
					+ "\"adminorgunit\".fhistoryrelateid AS \"adminorgunit.id\", \"position\".findex AS \"position.index\", "
					+ "\"cmpemporelation\".fid AS \"cmpemporelation.id\", \"cmpemporelation\".fleffectdate AS cmpleffectdate  "
					+ "FROM t_bd_personhis \"person\" LEFT OUTER JOIN t_hr_spersoncmp \"personcmp\" ON \"personcmp\".fpersonid = \"person\".fhistoryrelateid "
					+ "LEFT OUTER JOIN ct_mp_fullorpart \"fullorpart\" ON \"fullorpart\".fid = \"person\".cfftorptid "
					+ "LEFT OUTER JOIN t_hr_scmpemporelation \"cmpemporelation\" ON \"cmpemporelation\".fpersoncmpid = \"personcmp\".fid "
					+ "LEFT OUTER JOIN t_hr_emporgrelation \"emporgrelation\" ON \"emporgrelation\".fid = \"cmpemporelation\".femporgrelationid "
					+ "LEFT OUTER JOIN t_org_adminhis \"adminorgunit\" ON (\"adminorgunit\".fhistoryrelateid = \"emporgrelation\".fadminorgid AND "
					+ "((\"adminorgunit\".feffdt <= \"emporgrelation\".fleffdt) AND (\"adminorgunit\".fleffdt >= \"emporgrelation\".fleffdt))) "
					+ "LEFT OUTER JOIN t_org_adminorgtreehis \"adminorgunitt\" ON ((\"adminorgunit\".fhistoryrelateid = \"adminorgunitt\".fadminorgid AND "
					+ "(\"adminorgunit\".fleffdt <= \"adminorgunitt\".fleffdt)) AND (\"adminorgunit\".fleffdt >= \"adminorgunitt\".feffdt)) "
					+ "LEFT OUTER JOIN t_org_hro \"cmphrorgunit\" ON \"cmphrorgunit\".fid = \"personcmp\".fhrorgunitid "
					+ "LEFT OUTER JOIN t_org_positionhis \"position\" ON (\"position\".fhistoryrelateid = \"emporgrelation\".fpositionid AND "
					+ "((\"position\".feffdt <= \"emporgrelation\".fleffdt) AND (\"position\".fleffdt >= \"emporgrelation\".fleffdt))) "
					+ "LEFT OUTER JOIN t_hr_personpositionhis \"pp\" ON (((\"pp\".fpersonid = \"emporgrelation\".fpersonid AND "
					+ "(\"pp\".fstartdatetime <= \"emporgrelation\".fleffdt)) AND (\"pp\".fenddatetime >= \"emporgrelation\".fleffdt)) AND "
					+ "((\"pp\".feffdt <= \"emporgrelation\".fleffdt) AND (\"pp\".fleffdt >= \"emporgrelation\".fleffdt))) "
					+ "LEFT OUTER JOIN t_hr_emplaborrelationhis \"laborrela\" ON (\"laborrela\".fhistoryrelateid = \"emporgrelation\".flaborrelationid AND "
					+ "((\"laborrela\".fstartdatetime <= \"emporgrelation\".fenddatetime) AND (\"laborrela\".fenddatetime >= \"emporgrelation\".fenddatetime))) "
					+ "WHERE ((((((\"cmphrorgunit\".fid IN ('00000000-0000-0000-0000-000000000000CCE7AED4') ) AND (\"personcmp\".fleffectdate > '"+today+"')) AND "
					+ "((\"cmpemporelation\".fleffectdate > '"+today+"') OR ((\"pp\".fleftdate >= '"+lastDateStr+"') AND (\"pp\".fleftdate < '"+nextDateStr+"')))) AND "
					+ "\"emporgrelation\".fassigntype = '1') AND ((\"adminorgunit\".fissealup = 0 AND \"cmpemporelation\".fusedefault = 1) OR "
					+ "(\"cmpemporelation\".fusedefault = 0 OR (\"cmpemporelation\".fusedefault IS NULL)))) AND ((\"person\".feffdt < '"+nextDateStr+"') AND "
					+ "(\"person\".fleffdt >= '"+today+"'))) ORDER BY  \"person.number\" ASC";
			logger.error("查询花名册信息SQL："+sql);
			IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
			StringBuffer strBuffer = new StringBuffer();
			Map<String, Date> map = new HashMap<>();
			Map<String, String> empMap = new HashMap<>();
			Set<String> set = new HashSet<>();
			while(rowSet.next()) {
				String personNumber = rowSet.getString("person.number");
				String cmpEmpRelationId = rowSet.getString("cmpemporelation.id");
				Date cmpLeffectdate = rowSet.getDate("cmpleffectdate");
				if(map.containsKey(personNumber)) {
					if(map.get(personNumber).compareTo(cmpLeffectdate)>0) {
						set.add(cmpEmpRelationId);
					}else {
						set.add(empMap.get(personNumber));
						map.put(personNumber, cmpLeffectdate);
						empMap.put(personNumber, cmpEmpRelationId);
					}
				}else {
					map.put(personNumber, cmpLeffectdate);
					empMap.put(personNumber, cmpEmpRelationId);
				}
			}
			logger.error("重复人员ID集合："+set);
			for(String id:set) {
				strBuffer.append("'"+id+"',");
			}
			if(strBuffer.length()>0) {
				strBuffer.deleteCharAt(strBuffer.length()-1);
				FilterInfo itemInfo = new FilterInfo("cmpemporelation.id not in ("+strBuffer+")");
				defaultFilter.mergeFilter(itemInfo, "and");;
			}
			logger.error("档案花名册过滤条件："+defaultFilter.toSql());
			
		} catch (ParserException e) {
			e.printStackTrace();
		} catch (BOSException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return defaultFilter;
	}
}