productresultlist.cpp

这是一个工厂的生产线的仓库管理.有计划,实绩,库存等

			sSQL = String::Concat(sSQL,	S"		select  ");
			sSQL = String::Concat(sSQL,	S"			YMD,  ");
			sSQL = String::Concat(sSQL,	S"			FACTORY_CODE, ");
			sSQL = String::Concat(sSQL,	S"			LOT, ");
			sSQL = String::Concat(sSQL,	S"			PROCESS_NO, ");
			sSQL = String::Concat(sSQL,	S"			PRODUCT_CODE, ");
			sSQL = String::Concat(sSQL,	S"			sum(PRODUCT_QTY) as TOTAL ");
			sSQL = String::Concat(sSQL,	S"		from  ");
			sSQL = String::Concat(sSQL,	S"			PRODUCT_SCHEDULE  ");
			sSQL = String::Concat(sSQL,	S"		group by  ");
			sSQL = String::Concat(sSQL,	S"			YMD,  ");
			sSQL = String::Concat(sSQL,	S"			FACTORY_CODE,  ");
			sSQL = String::Concat(sSQL,	S"			LOT,  ");
			sSQL = String::Concat(sSQL,	S"			LINE_CODE, ");
			sSQL = String::Concat(sSQL,	S"			PROCESS_NO, ");
			sSQL = String::Concat(sSQL,	S"			PRODUCT_CODE  ");
			sSQL = String::Concat(sSQL,	S"	) SCHE,  "                                  );
			sSQL = String::Concat(sSQL,	S"	PRODUCT_RESULT_DATA SR_A,  ");
			sSQL = String::Concat(sSQL,	S"	PRODUCT_RESULT_DATA SR_B  ");
			sSQL = String::Concat(sSQL,	S"where  "   );
			sSQL = String::Concat(sSQL,	S"	CALE.FACTORY_CODE = '", sFactory, S"' and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.PROCESS_NO = '", sProcessNo, S"' and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.FACTORY_CODE = SCHE.FACTORY_CODE(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.PROCESS_NO = SCHE.PROCESS_NO(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.LOT = SCHE.LOT(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.YMD = SCHE.YMD(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.FACTORY_CODE = SR_A.FACTORY_CODE(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.PROCESS_NO = SR_A.PROCESS_NO(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.LOT = SR_A.LOT(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.YMD = SR_A.YMD(+) and  ");
			sSQL = String::Concat(sSQL,	S"	SR_A.SHIFT(+) = 'A' and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.FACTORY_CODE = SR_B.FACTORY_CODE(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.PROCESS_NO = SR_B.PROCESS_NO(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.LOT = SR_B.LOT(+) and  ");
			sSQL = String::Concat(sSQL,	S"	CALE.YMD = SR_B.YMD(+) and  ");
			sSQL = String::Concat(sSQL,	S"	SR_B.SHIFT(+) = 'B'         ");
			//sSQL = String::Concat(sSQL,	S"	SR_B.SHIFT(+) = 'B' and ");
			//sSQL = String::Concat(sSQL,	S"	CALE.YMD>=TO_DATE(ABEF.YMD,'yyyy-mm') and					 " 												);
			//sSQL = String::Concat(sSQL,	S"	CALE.YMD<ADD_MONTHS(TO_DATE(ABEF.YMD,'yyyy-mm'),1)						  " 		               	);
			sSQL = String::Concat(sSQL,	S"order by  ");
			sSQL = String::Concat(sSQL,	S"	CALE.YMD  ");
///////////////////////////////////////开始//////////////////////////////


	sMySQLMonth2=sSQL;


	//For monthtime select 

	//////////////////////////////////////////////////////////////////////////////////////////////

	
	
	String * sAllSQL=S"";

	sAllSQL=String::Concat(sAllSQL,S"select ABEF.YMD,ABEF.PRODUCT_CODE,ABEF.PS_QTY,ABEF.PR_QTY,ABEF.PERCENTAGE,\n");
    sAllSQL=String::Concat(sAllSQL,S"CKSH.YMD_DATE,CKSH.TOTAL,CKSH.NAME_B,CKSH.QTY_B,CKSH.NAME_A,CKSH.QTY_A\n");
sAllSQL=String::Concat(sAllSQL,S" from (\n");
 sAllSQL=String::Concat(sAllSQL,sMySQLMonth);

					   sAllSQL=String::Concat(sAllSQL,S"\n)ABEF left outer join(\n");
					   sAllSQL=String::Concat(sAllSQL,sMySQLMonth2);

						   sAllSQL=String::Concat(sAllSQL,S"\n)CKSH \n");

sAllSQL=String::Concat(sAllSQL,S"on ABEF.PRODUCT_CODE=CKSH.PRODUCT_CODE and CKSH.YMD>=TO_DATE(ABEF.YMD,'yyyy-mm') and CKSH.YMD<ADD_MONTHS(TO_DATE(ABEF.YMD,'yyyy-mm'),1) order by ABEF.YMD");


String * tempSQL;
tempSQL=S"";
tempSQL=sAllSQL;

////////////////////////////////////////////////////////////////
String * finalSQL;
//
//取品名
//switch(iLanguage)
String* ssaLang[]   = {S"C", S"J", S"E"};
finalSQL=String::Concat(finalSQL,S"select BBTABLE.product_name,KKTABLE.YMD,KKTABLE.PRODUCT_CODE,KKTABLE.PS_QTY,KKTABLE.PR_QTY,KKTABLE.PERCENTAGE,\n");
finalSQL=String::Concat(finalSQL,S"KKTABLE.YMD_DATE,KKTABLE.TOTAL,KKTABLE.NAME_B,KKTABLE.QTY_B,KKTABLE.NAME_A,KKTABLE.QTY_A\n");
finalSQL=String::Concat(finalSQL,S" from (\n");
finalSQL=String::Concat(finalSQL,tempSQL);

finalSQL=String::Concat(finalSQL,S"\n)KKTABLE left outer join(\n");

finalSQL=String::Concat(finalSQL,S" select parts_name_");
finalSQL=String::Concat(finalSQL,ssaLang[iLanguage]);


finalSQL=String::Concat(finalSQL,S" as product_name,product_code from parts_mst");

finalSQL=String::Concat(finalSQL,S"\n)BBTABLE \n");

finalSQL=String::Concat(finalSQL,S" on KKTABLE.PRODUCT_CODE=BBTABLE.PRODUCT_CODE");
tempSQL=S"";
tempSQL=finalSQL;
finalSQL=S"";

//取LINE
finalSQL=String::Concat(finalSQL,S"select BBTABLE.line_name,KKTABLE.product_name,KKTABLE.YMD,KKTABLE.PRODUCT_CODE,KKTABLE.PS_QTY,KKTABLE.PR_QTY,KKTABLE.PERCENTAGE,\n");
finalSQL=String::Concat(finalSQL,S"KKTABLE.YMD_DATE,KKTABLE.TOTAL,KKTABLE.NAME_B,KKTABLE.QTY_B,KKTABLE.NAME_A,KKTABLE.QTY_A\n");
finalSQL=String::Concat(finalSQL,S" from (\n");
finalSQL=String::Concat(finalSQL,tempSQL);

finalSQL=String::Concat(finalSQL,S"\n)KKTABLE left outer join(\n");
finalSQL=String::Concat(finalSQL,S" select product_no as product_code ,(case line_no when 'L001' then 'R' when 'L002' then 'V' end) as line_name from process_pro");

finalSQL=String::Concat(finalSQL,S"\n)BBTABLE \n");

finalSQL=String::Concat(finalSQL,S" on KKTABLE.PRODUCT_CODE=BBTABLE.PRODUCT_CODE order by KKTABLE.YMD");




////////////////////////////////////////////////////////


///////////////////////////////////////结束////////////////////////////////
		

	//oDataSetMinor = oDB->dSetSQL_Select(sSQL, S"GridDataMinor", 0);
	//oDataTableMinor = oDataSetMinor->Tables->Item["GridDataMinor"];
	//dataGridMinor->DataSource = oDataTableMinor;

	//oDataSetMain = oDB->dSetSQL_Select(finalSQL, S"GridDataMainForMonth", 0);

oDataSetMain = oDB->dSetSQL_Select(sMySQLMonth, S"GridDataMainForMonth", 0);
	oDataTableMain = oDataSetMain->Tables->Item["GridDataMainForMonth"];
	//合计开始
///begin 
///add a sum row to the end
	/*double sumTotal;*/
	long sumTotalPlan;
	long sumTotalResult;
	//long sumTotalDiff;//总差异
	sumTotalPlan=0;
	sumTotalResult=0;
	//sumTotalDiff=0;
	IEnumerator* tempRow = oDataTableMain->Rows->GetEnumerator();
	tempRow->Reset();
	while (tempRow->MoveNext())
	{
		DataRow *dRowTmp = __try_cast<DataRow*>(tempRow->Current);
		/*sumTotal=sumTotal+Convert::ToDouble(dRowTmp->Item[S"PS_QTY"]);*/
		sumTotalPlan     =sumTotalPlan+Convert::ToInt32(dRowTmp->Item[S"PS_QTY"]);
		sumTotalResult   =sumTotalResult+Convert::ToInt32(dRowTmp->Item[S"PR_QTY"]);
		//sumTotalDiff	=sumTotalDiff+Convert::ToInt32(dRowTmp->Item[S"PR_QTY"]);
			
	}
		DataRow* sumRow=oDataTableMain->NewRow();
		sumRow->Item["YMD"]= S" ";
		switch (iLanguage)//0 is chinese,1 is jap ,2 is english
		{
			case 0: 
				sumRow->Item["PRODUCT_CODE"]= S"合计"; 
				break;
			case 1:
				sumRow->Item["PRODUCT_CODE"]= S"合計";
				break;
			case 2:
				sumRow->Item["PRODUCT_CODE"]= S"Sum";
				break;
			default:   break;
		}
		
		sumRow->Item["PS_QTY"]=Convert::ToString(sumTotalPlan);
		sumRow->Item["PR_QTY"]=Convert::ToString(sumTotalResult);
		//sumRow->Item["PERCENTAGE"]=Convert::ToString((sumTotalResult)/(sumTotalPlan)*100);
		sumRow->Item["PERCENTAGE"]=Convert::ToString((sumTotalResult)-(sumTotalPlan));
		//sumRow->Item["TOTAL"]=Convert::ToString(0);
		//sumRow->Item["NAME_A"]=S"";
		//sumRow->Item["result_ymd"]=S"";
		//sumRow->Item["NAME_B"]=S"";
		//sumRow->Item["QTY_B"]=S"";

		//sumRow->Item["QTY_B"]=S"";
		oDataTableMain->Rows->Add(sumRow);
	
	///end
///add a sum row to the end
	//合计结束
	//dataGridMain->DataSource = oDataTableMain;
	dataGridMainForMonth->DataSource=oDataTableMain;
}
void ProductResultList::SetValueComboBoxProcessNum()
{
	//initializing the comboBoxProcessNum 
	String*		sSQL = S"";
	String*		saLangName[] = {S"NAME_C",S"NAME_J",S"NAME_E"};

	if (insiderank==0)//insiderank give information about which  person of rank enter into this form,value 0 is for worker,1,2 is sequently for mananger and administrator 
		{
			sSQL = String::Concat(sSQL, S"select"									);//these code use table COST_MST to recreate a new datatable in the dataset object,this new table's mapping name is still COST_MST
			sSQL = String::Concat(sSQL, S"	PROCESS_NO,"							);//the new table use additional column to represent same date in  respective version of language.
			sSQL = String::Concat(sSQL, S"	PROCESS_NO || ' ' || NAME_J as NAME_J,"	);//then you use iLanguage variable  in the saLangName[] like  saLangName[iLanguage] to get which column to be selected for display
			sSQL = String::Concat(sSQL, S"	PROCESS_NO || ' ' || NAME_C as NAME_C,"	);
			sSQL = String::Concat(sSQL, S"	PROCESS_NO || ' ' || NAME_E as NAME_E "	);
			sSQL = String::Concat(sSQL, S"from"										);
			sSQL = String::Concat(sSQL, S"	COST_MST "								);
			sSQL = String::Concat(sSQL, S"where"									);
			sSQL = String::Concat(sSQL, S"	FACTORY_CODE = '", sFactory ,"' and"	);//sFactory is set in ini file
			sSQL = String::Concat(sSQL, S"	PROCESS_NO = '",sProcess ,"'"			);


			oDataSet = oDB->dSetSQL_Select(sSQL, S"COST_MST", 0);
			comboBoxProcessNum->DataSource = oDataSet->Tables->Item[S"COST_MST"];
			comboBoxProcessNum->ValueMember  = S"PROCESS_NO";
			comboBoxProcessNum->DisplayMember = saLangName[iLanguage];
		}
		else
		{
        	sSQL = String::Concat(sSQL, S"select"									);//these code use table COST_MST to recreate a new datatable in the dataset object,this new table's mapping name is still COST_MST
	        sSQL = S