package nc.impl.wa.rpt;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;

import nc.bs.dao.BaseDAO;
import nc.hr.utils.SQLHelper;
import nc.itf.hr.wa.IClassitemQry;
import nc.itf.org.IOrgConst;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.ResultSetProcessor;
import nc.vo.hr.pub.FormatVO;
import nc.vo.pub.BusinessException;
import nc.vo.wa.item.WaItemVO;
import nc.vo.wa.paydata.WaPaydataDspVO;
import nc.wa.smartmodel.provider.util.WaGlobalVO;
import nccloud.commons.lang.StringUtils;
/**
 * 薪资项目 查询 实现类
 * 
 * @author suihang
 * @version 最后修改日期 2011-1-4
 * @see  
 * @since  
 */
public class ClassitemImpl implements IClassitemQry{
	
	private final BaseDAO dao = new BaseDAO();
	
	/**
	 * 根据单条薪资类别参数查询薪资项目
	 * @param waGlobalVO 薪资类别参数
	 * @return ItemVO[] 薪资项目
	 * @throws BusinessException
	 */
	public  WaItemVO[] queryItemInfoWithPower(WaGlobalVO waGlobalVO,int type) throws BusinessException{

		if(waGlobalVO==null){
			//当薪资类别参数为空时，不进行查询。即默认薪资项目集合也为空。
			return null;
		}
	 	StringBuffer sqlB = new StringBuffer();
		sqlB.append(" select distinct wa_item.pk_wa_item, ");  
		sqlB.append("  "+ SQLHelper.getMultiLangNameColumn("wa_classitem.name")+ " , "); 
		sqlB.append(" wa_item.code, "); 
		sqlB.append(" wa_item.itemkey, ");
		sqlB.append(" wa_classitemdsp.displayseq, ");
		sqlB.append(" wa_classitem.idisplayseq ");
		
		sqlB.append(" from wa_classitem ");
		sqlB.append(" left outer join wa_classitemdsp ");
		sqlB.append(" on wa_classitem.pk_wa_class = wa_classitemdsp.pk_wa_class  ");
		sqlB.append(" and wa_classitem.cyear = wa_classitemdsp.cyear  ");
		sqlB.append(" and wa_classitem.cperiod = wa_classitemdsp.cperiod  ");
		sqlB.append(" and wa_classitem.pk_wa_classitem = wa_classitemdsp.pk_wa_classitem  ");
		sqlB.append(" and wa_classitemdsp.pk_user = '" + waGlobalVO.getCurUserid() + "' ,");
		sqlB.append(" wa_item ");
			
		sqlB.append(" where wa_classitem.pk_wa_item = wa_item.pk_wa_item");
		sqlB.append(" and  wa_classitem.pk_wa_class =  '" + waGlobalVO.getWaClassPK() + "' ");
		sqlB.append(" and  wa_classitem.cyear = '" + waGlobalVO.getWaYear() + "' ");
		sqlB.append(" and  wa_classitem.cperiod = '" + waGlobalVO.getWaPeriod() + "' ");

		sqlB.append(" and wa_item.pk_wa_item in (");
		// 根据薪资期间、薪资类别、所选组织以及登录人权限筛选
		sqlB.append(getItemPkSqlByUseridClassid( new WaGlobalVO[] { waGlobalVO }));
		sqlB.append(" )");
		if(type==0){
			sqlB.append(" and  wa_item.iitemtype = 0 ");
		}
		// 与薪资发放项目顺序保持一致20200225
		//sqlB.append(" order by wa_classitemdsp.displayseq asc");
		sqlB.append(" order by wa_classitem.idisplayseq ");
		
		
		return (WaItemVO[]) dao.executeQuery(sqlB.toString(), new ResultSetProcessor() {
			private static final long serialVersionUID = 3623741235217336426L;
			/** 
			 * 封装结果集，返回WaItemVO[]形式结果 
			 */
			public Object handleResultSet(ResultSet rs) throws SQLException {
	
				Vector<WaItemVO> vector = new Vector<WaItemVO>();
				while (rs.next()) {
					WaItemVO itemVO = new WaItemVO();
					itemVO.setPk_wa_item(rs.getString(1));
					itemVO.setName(rs.getString(2));
					itemVO.setCode(rs.getString(3));
					itemVO.setItemkey(rs.getString(4));
					vector.add(itemVO);
				}
				return vector.size() > 0 ? vector.toArray(new WaItemVO[vector.size()]) : null;
			}
		});
	}

	@Override
	public WaItemVO[] queryItemInfoWithCondition(WaGlobalVO waGlobalVO,String condition)
			throws BusinessException {

		if(waGlobalVO==null){
			//当薪资类别参数为空时，不进行查询。即默认薪资项目集合也为空。
			return null;
		}
	 	StringBuffer sqlB = new StringBuffer();
		sqlB.append(" select distinct wa_item.pk_wa_item, ");  
		sqlB.append("  "+ SQLHelper.getMultiLangNameColumn("wa_classitem.name")+ " , "); 
		sqlB.append(" wa_item.code, "); 
		sqlB.append(" wa_item.itemkey, ");
		sqlB.append(" wa_item.idisplayseq ");
		sqlB.append(" from wa_classitem ,wa_item");
		sqlB.append(" where wa_classitem.pk_wa_item = wa_item.pk_wa_item");
		sqlB.append(" and  wa_classitem.pk_wa_class =  '" + waGlobalVO.getWaClassPK() + "' ");
		sqlB.append(" and  wa_classitem.cyear = '" + waGlobalVO.getWaYear() + "' ");
		sqlB.append(" and  wa_classitem.cperiod = '" + waGlobalVO.getWaPeriod() + "' ");
		if(!StringUtils.isEmpty(condition)){
			sqlB.append(" and  wa_item."+condition);
		}
		sqlB.append(" order by wa_item.idisplayseq ,code");
		
		return (WaItemVO[]) dao.executeQuery(sqlB.toString(), new ResultSetProcessor() {
			private static final long serialVersionUID = 3623741235217336426L;
			/** 
			 * 封装结果集，返回WaItemVO[]形式结果 
			 */
			public Object handleResultSet(ResultSet rs) throws SQLException {
	
				Vector<WaItemVO> vector = new Vector<WaItemVO>();
				while (rs.next()) {
					WaItemVO itemVO = new WaItemVO();
					itemVO.setPk_wa_item(rs.getString(1));
					itemVO.setName(rs.getString(2));
					itemVO.setCode(rs.getString(3));
					itemVO.setItemkey(rs.getString(4));
					vector.add(itemVO);
				}
				return vector.size() > 0 ? vector.toArray(new WaItemVO[vector.size()]) : null;
			}
		});
	}
	
	@Override
	public WaItemVO[] queryItemInfoWithClass(WaGlobalVO waGlobalVO,int type)
			throws BusinessException {

		if(waGlobalVO==null){
			//当薪资类别参数为空时，不进行查询。即默认薪资项目集合也为空。
			return null;
		}
	 	StringBuffer sqlB = new StringBuffer();
		sqlB.append(" select distinct wa_item.pk_wa_item, ");  
		sqlB.append("  "+ SQLHelper.getMultiLangNameColumn("wa_item.name")+ " , "); 
		sqlB.append(" wa_item.code, "); 
		sqlB.append(" wa_item.itemkey, ");
		sqlB.append(" wa_item.idisplayseq ");
		sqlB.append(" from wa_classitem ,wa_item");
		sqlB.append(" where wa_classitem.pk_wa_item = wa_item.pk_wa_item");
		sqlB.append(" and  wa_classitem.pk_wa_class =  '" + waGlobalVO.getWaClassPK() + "' ");
		sqlB.append(" and  wa_classitem.cyear = '" + waGlobalVO.getWaYear() + "' ");
		if(type==0){
			sqlB.append(" and  wa_item.iitemtype = 0 ");
		}
		sqlB.append(" order by wa_item.idisplayseq asc");
		
		return (WaItemVO[]) dao.executeQuery(sqlB.toString(), new ResultSetProcessor() {
			private static final long serialVersionUID = 3623741235217336426L;
			/** 
			 * 封装结果集，返回WaItemVO[]形式结果 
			 */
			public Object handleResultSet(ResultSet rs) throws SQLException {
	
				Vector<WaItemVO> vector = new Vector<WaItemVO>();
				while (rs.next()) {
					WaItemVO itemVO = new WaItemVO();
					itemVO.setPk_wa_item(rs.getString(1));
					itemVO.setName(rs.getString(2));
					itemVO.setCode(rs.getString(3));
					itemVO.setItemkey(rs.getString(4));
					vector.add(itemVO);
				}
				return vector.size() > 0 ? vector.toArray(new WaItemVO[vector.size()]) : null;
			}
		});
	}

	/**
	 * 根据多条薪资类别参数查询薪资项目
	 * @param waGlobalVOs 薪资类别参数
	 * @return ItemVO[] 薪资项目
	 * @throws BusinessException
	 */
	public  WaItemVO[] queryItemInfoWithPowerForMutiClasses(WaGlobalVO[] waGlobalVOs,int type) throws BusinessException{
		if(waGlobalVOs==null){
			return null;
		}
		WaGlobalVO waGlobalVO = waGlobalVOs[0];
	 	StringBuffer sqlB = new StringBuffer();
		sqlB.append(" select distinct wa_item.pk_wa_item, "); 
		sqlB.append("  "+ SQLHelper.getMultiLangNameColumn("wa_item.name")+ " , ");  
		sqlB.append(" wa_item.code, ");   
		sqlB.append(" wa_item.itemkey ");
		sqlB.append(" from wa_item  where  ");

		if (waGlobalVO.getWaClassPK() == null) {
			//没有薪资类别 就显示所选组织的所有薪资项目
			sqlB.append("  (wa_item.pk_org= '"+waGlobalVO.getCurPk_org()+"' or wa_item.pk_org='"+waGlobalVO.getCurPk_group()+"' or wa_item.pk_org='"+IOrgConst.GLOBEORGTYPE+"' ) " );
		}else{
			//如果有薪资类别，根据薪资期间、薪资类别、所选组织以及登录人权限筛选
			sqlB.append("   wa_item.pk_wa_item in (");
			sqlB.append(getItemPkSqlByUseridClassid(waGlobalVOs));
			sqlB.append(" )");
		}
		if(type==0){
			sqlB.append(" and  wa_item.iitemtype = 0 ");
		}
		
		return (WaItemVO[]) dao.executeQuery(sqlB.toString(), new ResultSetProcessor() {
			private static final long serialVersionUID = 3623741235217336426L;
			/** 
			 * 封装结果集，返回WaItemVO[]形式结果 
			 */
			public Object handleResultSet(ResultSet rs) throws SQLException {

				Vector<WaItemVO> vector = new Vector<WaItemVO>();
				while (rs.next()) {
					WaItemVO itemVO = new WaItemVO();
					itemVO.setPk_wa_item(rs.getString(1));
					itemVO.setName(rs.getString(2));
					itemVO.setCode(rs.getString(3));
					itemVO.setItemkey(rs.getString(4));
					vector.add(itemVO);
				}
				return vector.size() > 0 ? vector.toArray(new WaItemVO[vector.size()]) : null;
			}
		});
	}
	
	/**
	 * 根据薪资期间和薪资类别找出登录人的项目权限 suihang 2010-12-23
	 *
	 * @param waGlobalVOs
	 * @return
	 */

	private String getItemPkSqlByUseridClassid(WaGlobalVO[] waGlobalVOs) {
		String userid = waGlobalVOs[0].getCurUserid();
		String pk_org = waGlobalVOs[0].getCurPk_org();
		String cyear = waGlobalVOs[0].getWaYear();
		String cperiod = waGlobalVOs[0].getWaPeriod();
		StringBuffer sqlB = new StringBuffer();
		sqlB.append(" select  wa_item.pk_wa_item "); 
		sqlB.append(" from wa_item ");
		sqlB.append(" where wa_item.pk_wa_item in ");
		/**
		 * 关联wa_itempower表（即与登录人员角色关联）
		 */
		sqlB.append(" ( select distinct wa_itempower.pk_wa_item from wa_itempower ,wa_classitem where  wa_itempower.pk_wa_item = wa_classitem.pk_wa_item ");
		sqlB.append(" and ( wa_itempower.pk_subject in ( select pk_role from sm_user_role where cuserid = '"+userid+"' ) or pk_subject = '"+userid+"' )");
		sqlB.append(" and  wa_classitem.pk_org = '"+pk_org+"' ");
		sqlB.append(" and  wa_classitem.pk_wa_class in  (" + FormatVO.formatArrayToString(waGlobalVOs, WaGlobalVO.WACLASSPK) + ")  ");
		sqlB.append(" and  wa_classitem.cyear = '" + cyear + "' ");
		sqlB.append(" and  wa_classitem.cperiod = '" + cperiod + "' )");
		/*sqlB.append(" ( select distinct wa_classitem.pk_wa_item from wa_classitem where  wa_item.pk_wa_item = wa_classitem.pk_wa_item  ");
		sqlB.append(" and  wa_classitem.pk_wa_class in  (" + FormatVO.formatArrayToString(waGlobalVOs, WaGlobalVO.WACLASSPK) + ") ");
		sqlB.append(" and  wa_classitem.pk_org = '"+pk_org+"' ");
		sqlB.append(" and  wa_classitem.cyear = '" + cyear + "' ");
		sqlB.append(" and  wa_classitem.cperiod = '" + cperiod + "' )");*/
		
		return sqlB.toString();
	}

	/**
	 * 根据方案主键查询薪资发放设置为通用项目的数据
	 * @param pk_wa_class
	 * @return
	 * @throws BusinessException
	 */
	@Override
	public List<WaPaydataDspVO> queryWaPaydataDspVOByPkWaClass(String pk_wa_class) throws BusinessException {
		String sql = "select * from wa_paydatadsp where type = 0 and pk_wa_class = '"+pk_wa_class+"' and bshow = 'Y' and iswaitem = 'Y' order by displayseq";
		List<WaPaydataDspVO> list = (List<WaPaydataDspVO>) dao.executeQuery(sql, new BeanListProcessor(WaPaydataDspVO.class));
		return list;
	}
}