XinYu_RZMES/.svn/pristine/d7/d7c6f7c182e63cb0e73e13857b4028dfc5513fdd.svn-base

package xin.glue.ui.B.B02;

import com.posdata.glue.biz.activity.PosActivity;
import com.posdata.glue.biz.constants.PosBizControlConstants;
import com.posdata.glue.context.PosContext;
import com.posdata.glue.dao.PosGenericDao;
import com.posdata.glue.dao.PosJdbcDao;
import com.posdata.glue.dao.manager.PosQueryDefinition;
import com.posdata.glue.dao.manager.PosQueryManager;
import com.posdata.glue.dao.vo.PosParameter;
import com.posdata.glue.dao.vo.PosRow;
import com.posdata.glue.dao.vo.PosRowSet;

/**
 * 试样材质实绩查询，材质动态生成
 * @Description  {界面ID:UIB020120}
 * @author       梅贵平{meiguiping}
 * @date         2008-11-5 下午02:38:51
 * @JDK Version  jdk1.4.2
 */
public class FindLotQltyAchievement extends PosActivity
{
	/*
	 * 首先从材质表及试样信息中获取有实绩值的材质项目代码，
	 * 然后动态生成所需要的SQL语句（动态部分就是材质实绩值、TYPE、材质名称处理）
	 */
	public String runActivity(PosContext context)
	{
		///readay
		PosGenericDao dao = this.getDao("mesdao");
		PosParameter param = null;
		
		///GET REQUEST DATA
		String[] SMP = (String[])context.get("SMP");//试样号
//		String[] SEQ = (String[])context.get("SEQ");//序号
		String[] STime     = (String[])context.get("startTime");
		String[] ETime     = (String[])context.get("endTime");
		String[] PORGCD	   = (String[])context.get("PORGCD");//试样进程代码
		String[] COILNO1   = (String[])context.get("COILNO1");
		String[] COILNO2   = (String[])context.get("COILNO2");
		String[] STLGRD	   = (String[])context.get("STLGRD");//牌号	
		///find qlty and qlty's name
		param	      		= new PosParameter();
		StringBuffer	sbb	=	getManagerSql("UIB020120_01.SELECT");
		int	jj		  		=	0;
		if(!"".equals(COILNO1[0]))
		{
			param.setWhereClauseParameter(jj++, COILNO1[0]);
			param.setWhereClauseParameter(jj++, COILNO2[0]);
			sbb.append("\n  AND  CCM.OLD_SAMPL_NO BETWEEN ? || '000' AND ? || '999' ");
//			sbb.append("\n  AND    SUBSTR(CCM.OLD_SAMPL_NO , 1, 10 )  BETWEEN ? AND ?");
		}
		if(!"".equals(PORGCD[0]))
		{
			param.setWhereClauseParameter(jj++, PORGCD[0]);
			sbb.append("\n AND    LCM.SMP_PROG_CD   =   ?");
		}
		if(!"0000".equals(STime[0]) && !"9999".equals(ETime[0]))
		{
			param.setWhereClauseParameter(jj++, STime[0]);
			param.setWhereClauseParameter(jj++, ETime[0]);
			sbb.append("\n AND LCM.SMP_WORK_DTIME  BETWEEN ? AND ?");
		}
		
		sbb.append("\n ORDER BY SQ.DISPLAY_SEQ");
		PosRowSet prs = dao.findByQueryStatement(sbb.toString(), param);
		context.put("Grid1QltyNM", prs);
		
		if(prs.count()==0)	return PosBizControlConstants.SUCCESS;
//		System.out.println("###################################"+prs.count());
		///生成最终的SQL
		StringBuffer	sBuffer	=	new StringBuffer(10000);
		if(prs != null && prs.count() > 0)
		{
			int count = prs.count();//获得材质项目的个数
			PosRow  row = null;
			int i= 1;
			String start = getManagerSql("UIB020120_02START.SELECT").toString();
			String mid   = getManagerSql("UIB020120_0201MID.SELECT").toString();
			String end   = getManagerSql("UIB020120_0201END.SELECT").toString();
			String sql   = "";//用于replace后的存储
			StringBuffer  sb = new StringBuffer(8000);//sb.append(sql)
			
			while(prs.hasNext())
			{
				row = prs.next();	
				sql = mid.replaceAll("[{]+[0]+[}]+", row.getAttribute("QLTY_CD").toString());//将{0}处替换为查询出来的材质项目
				sql	= sql.replaceAll("[{]+[1]+[}]+", new Integer(i++).toString()); 
				sb.append(sql);
			}		

			StringBuffer	ss01	=	getManagerSql("UIB020120_03.SELECT");
			sql	=	start+"\n"+sb.toString()+"\n"+end;//TBB02_WORK_QLTY部分生成
			//无查询条件的最终SQL生成，自然连接方式的SQL
			sBuffer.append(ss01+" ( "+sql+ " ) Y WHERE X.SMP_NO = Y.SMP_NO");
		}
		
		param	=	new PosParameter();
		int j	=	0;
//		if(!"0000".equals(startTime[0]) && !"9999".equals(endTime[0]))//若检验时间不为空
//		{
//			sBuffer.append("\n AND X.SMP_WORK_DTIME BETWEEN ? AND ?");
//			param.setWhereClauseParameter(j++, startTime[0]);
//			param.setWhereClauseParameter(j++, endTime[0]);
//		}
//		
		if("".equals(PORGCD[0]))//试样进程代码
		{
			sBuffer.append("\n AND ( X.SMP_PROG_CD = 'C' OR X.SMP_PROG_CD = 'D' OR X.SMP_PROG_CD = 'E')");
		}
		else if("C".equals(PORGCD[0]))//检验待机
		{
			sBuffer.append("\n AND X.SMP_PROG_CD = 'C'");
		}
		else if("D".equals(PORGCD[0]))//判定待机
		{
			sBuffer.append("\n AND X.SMP_PROG_CD = 'D'");
		}
		else if("E".equals(PORGCD[0]))//判定完成
		{
			sBuffer.append("\n AND X.SMP_PROG_CD = 'E'");
		}
		
		if(!"".equals(COILNO1[0]))
		{
			param.setWhereClauseParameter(j++, COILNO1[0]);
			param.setWhereClauseParameter(j++, COILNO2[0]);
			sBuffer.append("\n  AND  X.OLD_SAMPL_NO BETWEEN ? || '000' AND ? || '999' ");
//			sBuffer.append("\n  AND    SUBSTR(X.OLD_SAMPL_NO , 1, 10 )  BETWEEN ? AND ?");
		}
		
		if(!"0000".equals(STime[0]) && !"9999".equals(ETime[0]))
		{
			param.setWhereClauseParameter(j++, STime[0]);
			param.setWhereClauseParameter(j++, ETime[0]);
			sBuffer.append("\n AND X.SMP_WORK_DTIME  BETWEEN ? AND ?");
		}
		sBuffer.append("\n AND X.SPEC_STL_GRD LIKE ?||'%' ");
		param.setWhereClauseParameter(j++, STLGRD[0]);
		sBuffer.append(" ORDER BY X.OLD_SAMPL_NO");
		PosRowSet rowset = dao.findByQueryStatement(sBuffer.toString() , param);
		context.put("LotAchievementResult", rowset);
		return PosBizControlConstants.SUCCESS;
	}
		
	public StringBuffer getManagerSql(String id)
	{
		PosJdbcDao  dao      = (PosJdbcDao)this.getDao("mesdao");
		PosQueryManager ppt  = dao.getQueryManager();
		PosQueryDefinition x = ppt.getQueryDefinition(id);
		return new StringBuffer(x.getQueryStatement());
	}
}