HR/hrrpt/nccloud/src/private/nc/impl/wa/rpt/ClassitemImpl.java

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.PubEnv;
import nc.hr.utils.SQLHelper;
import nc.itf.hr.wa.IClassitemQry;
import nc.itf.org.IOrgConst;
import nc.jdbc.framework.SQLParameter;
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.classitem.WaClassItemVO;
import nc.vo.wa.func.WherePartUtil;
import nc.vo.wa.item.WaItemVO;
import nc.vo.wa.paydata.WaPaydataDspVO;
import nc.vo.wa.pub.WaLoginContext;
import nc.vo.wa.pub.WaLoginVO;
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;
	}
	
	/**
	 * 查询用户设置显示的项目
	 * 
	 * wa_classitemdsp 该表已经不用了
	 *
	 * @author liangxr on 2010-7-2
	 * @param loginVO
	 * @return
	 * @throws BusinessException
	 */
	public WaClassItemVO[] getUserShowClassItemVOs(WaLoginContext loginVO) throws BusinessException {
		String condition = " wa_classitem.pk_wa_classitem not in (select pk_wa_classitem from wa_classitemdsp "
				+ "where pk_wa_class = '" + loginVO.getWaLoginVO().getPk_wa_class() + "' and cyear = '" + loginVO.getWaLoginVO().getCyear()
				+ "' and cperiod = '" + loginVO.getWaLoginVO().getCperiod()+ "' and pk_user = '" + PubEnv.getPk_user()
				+ "' and bshow = 'N' )";
		WaClassItemVO[] classitems = testgetRoleClassItemVOs(loginVO.getWaLoginVO(), condition);
		return classitems;
	}
	
	/**
	 * 获取薪资发放项目
	 *
	 * @author liangxr on 2010-5-13
	 * @param waLoginVO
	 * @return
	 * @throws BusinessException
	 */
	private WaClassItemVO[] testgetRoleClassItemVOs(WaLoginVO waLoginVO, String condition) throws BusinessException {

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" select wa_item.itemkey, wa_item.iitemtype,wa_item.defaultflag, ");
		sqlBuffer.append(" wa_item.ifldwidth,wa_item.category_id, ");
		sqlBuffer.append(" wa_classitem.*, 'Y' editflag,");
		sqlBuffer.append(" 'Y' as showflag,");
		sqlBuffer.append(" wa_classitem.idisplayseq as idisplayseq,  ");
		sqlBuffer.append("itempower.editflag ");
		sqlBuffer.append("from wa_classitem , wa_item,");
		sqlBuffer.append("(SELECT pk_wa_item,MAX(editflag) as editflag");
		sqlBuffer.append("   FROM wa_itempower ");
		// 判断是否是多次发放
		// WaClassVO parentvo = NCLocator.getInstance().lookup(IWaClass.class)
		// .queryParentClass(waLoginVO.getPk_wa_class(),
		// waLoginVO.getCyear(), waLoginVO.getCperiod());

		sqlBuffer.append("  WHERE pk_wa_class = '" + waLoginVO.getPk_prnt_class() + "'");
		sqlBuffer.append("    AND pk_group ='" + waLoginVO.getPk_group() + "'");

		// 20160104 shenliangc NCdp205568081 已审批通过的发放申请通知消息，打开单据后，单据子表的审批项目丢失
		// 历史遗留问题，通知消息双击打开发放申请节点，查询审批项目时waLoginVO.getPk_org()得到的是系统默认主组织，而不是正确的方案主组织。
		sqlBuffer.append("    AND pk_org = (select pk_org from wa_waclass where pk_wa_class = '"
				+ waLoginVO.getPk_prnt_class() + "')");
		// sqlBuffer.append(" AND pk_org = '"+waLoginVO.getPk_org()+"'");

		sqlBuffer.append("    AND ( pk_subject IN(SELECT pk_role ");
		sqlBuffer.append("				       FROM sm_user_role ");
		sqlBuffer.append("				      WHERE cuserid = '" + PubEnv.getPk_user() + "'");
		sqlBuffer.append("                   ) or pk_subject = '" + PubEnv.getPk_user() + "') ");
		sqlBuffer.append("  GROUP BY pk_wa_item ) as itempower");
		sqlBuffer.append(" where wa_classitem.pk_wa_item = wa_item.pk_wa_item ");
		sqlBuffer.append(" and wa_classitem.pk_wa_item = itempower.pk_wa_item ");
		sqlBuffer.append(" and wa_classitem.pk_wa_class = ? ");
		sqlBuffer.append(" and wa_classitem.cyear = ?  and wa_classitem.cperiod = ? ");
		sqlBuffer.append(WherePartUtil.formatAddtionalWhere(condition));
		sqlBuffer.append(" order by wa_classitem.idisplayseq");

		SQLParameter parameter = this.getCommonParameter(waLoginVO);
		List<WaClassItemVO> list = (List<WaClassItemVO>) dao.executeQuery(sqlBuffer.toString(), parameter, new BeanListProcessor(WaClassItemVO.class));
		return list.toArray(new WaClassItemVO[0]);
	}
	
	/**
	 * pk_wa_class year, period
	 *
	 * @author zhangg on 2009-12-2
	 * @param waLoginVO
	 * @return
	 */
	public SQLParameter getCommonParameter(WaLoginVO waLoginVO) {
		SQLParameter parameter = new SQLParameter();
		parameter.addParam(waLoginVO.getPk_wa_class());
		parameter.addParam(waLoginVO.getCyear());
		parameter.addParam(waLoginVO.getCperiod());
		return parameter;
	}
}