productresultlist.cpp

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

#include "StdAfx.h"
#include "ProductResultList.h"


#include "DBBase.h" 

using namespace  FAP;
using namespace System::Data;
using namespace System::Data::OleDb;
using namespace System::Collections;


void ProductResultList::InitProductResultList()
{
	SetValueComboBoxProcessNum();
	SetValueComboBoxUnit();

	InitGridMainForDay();
	InitGridMainForMonth();
	//InitGridMinor();
	
	
}

void ProductResultList::GetDataGridMainForMonth(String* sProcessNo)
{
	String *sSQL = S"";
	//sSQL = String::Concat(sSQL,	S"	" 				                                         	);
	sSQL = String::Concat(sSQL,	S"select	" 				                                         	);
	sSQL = String::Concat(sSQL,	S"	RESULTLIST.YMD,  " 				                                         	);
	sSQL = String::Concat(sSQL,	S"	RESULTLIST.PRODUCT_CODE, " 				                                         	);
	sSQL = String::Concat(sSQL,	S"	sum(PS_QTY) PS_QTY, " 				                                         	);
	sSQL = String::Concat(sSQL,	S"	sum(PR_QTY) PR_QTY, " 				                                         	);
	//sSQL = String::Concat(sSQL,	S"	NVL(trunc((sum(PR_QTY))/( sum(PS_QTY))*100),0)  PERCENTAGE " 				                                         	);
	sSQL = String::Concat(sSQL,	S"	case when sum(PS_QTY)!=0 then NVL(trunc((sum(PR_QTY))-( sum(PS_QTY))),0)  else  0 end as PERCENTAGE " 	);
	/*sSQL = String::Concat(sSQL,	S"	case when sum(PS_QTY)!=0 then NVL(trunc((sum(PR_QTY))/( sum(PS_QTY))*100),0)  else  0 end as PERCENTAGE " 	);*/
	sSQL = String::Concat(sSQL,	S"from   	" 				                                         	);
	sSQL = String::Concat(sSQL,	S"	( " 				                                         	);
	sSQL = String::Concat(sSQL,	S"		 select	 " 				                                         	);
	sSQL = String::Concat(sSQL,	S"			to_char(LST.YMD,'yyyy-mm') as YMD, " 				                                         	);
	sSQL = String::Concat(sSQL,	S"			LST.PRODUCT_CODE,"															);
	sSQL = String::Concat(sSQL,	S"			LST.LOT, "																	);
	sSQL = String::Concat(sSQL,	S"			NVL(PS.QTY,0) PS_QTY,"														);
	sSQL = String::Concat(sSQL,	S"			NVL(PR.QTY,0) PR_QTY  "														);
	sSQL = String::Concat(sSQL,	S"		 from "																		);
	sSQL = String::Concat(sSQL,	S"			( "																			);
	sSQL = String::Concat(sSQL,	S"				select distinct "														);
	sSQL = String::Concat(sSQL,	S"					YMD, " 				                                            	);
	sSQL = String::Concat(sSQL,	S"					FACTORY_CODE,"														);
	sSQL = String::Concat(sSQL,	S"					PROCESS_NO,"														);
	sSQL = String::Concat(sSQL,	S"					PRODUCT_CODE, "														);
	sSQL = String::Concat(sSQL,	S"					LOT "																);
	sSQL = String::Concat(sSQL,	S"				from "																	);
	sSQL = String::Concat(sSQL,	S"					PRODUCT_SCHEDULE "													);
	sSQL = String::Concat(sSQL,	S"				where 	" 																);
	sSQL = String::Concat(sSQL,	S"					PROCESS_NO='",sProcessNo,S"' and "				                       	);
	sSQL = String::Concat(sSQL,	S"					ymd>=to_date('",dateTimePickerFrom->Text,S"','yyyy-mm') and ymd< ADD_MONTHS(to_date('",dateTimePickerTo->Text,S"','yyyy-mm'),1) " 	   );
	sSQL = String::Concat(sSQL,	S"				union "																	);
	sSQL = String::Concat(sSQL,	S"				select distinct "														);
	sSQL = String::Concat(sSQL,	S"					YMD, " 				                                            	);
	sSQL = String::Concat(sSQL,	S"					FACTORY_CODE,"														);
	sSQL = String::Concat(sSQL,	S"					PROCESS_NO,"														);
	sSQL = String::Concat(sSQL,	S"					PRODUCT_CODE,"														);
	sSQL = String::Concat(sSQL,	S"					LOT "																);
	sSQL = String::Concat(sSQL,	S"				from "																	);
	sSQL = String::Concat(sSQL,	S"					PRODUCT_RESULT_DATA "												);
	sSQL = String::Concat(sSQL,	S"				where 	" 														       	);
	sSQL = String::Concat(sSQL,	S"					PROCESS_NO='",sProcessNo,S"' and "				                      	);//condition from comobox
	sSQL = String::Concat(sSQL,	S"					ymd>=to_date('",dateTimePickerFrom->Text,S"','yyyy-mm') and ymd<= ADD_MONTHS(to_date('",dateTimePickerTo->Text,S"','yyyy-mm'),1) " 	   );
	sSQL = String::Concat(sSQL,	S"			) LST,"																		);
	sSQL = String::Concat(sSQL,	S"			( " 																		);
	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"					PRODUCT_QTY as QTY " 				                          	);
	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"					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"			) PS," 														              	);
	sSQL = String::Concat(sSQL,	S"			(" 																         	);
	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"					PRODUCT_QTY as QTY 	" 				                       	);
	sSQL = String::Concat(sSQL,	S"				from " 													              	);
	sSQL = String::Concat(sSQL,	S"					PRODUCT_RESULT_DATA " 				                               	);
	//sSQL = String::Concat(sSQL,	S"				group by	" 				                                         	);
	//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"			) PR " 															           	);
	sSQL = String::Concat(sSQL,	S"		where 	" 													               	    );
	sSQL = String::Concat(sSQL,	S"			LST.FACTORY_CODE = PS.FACTORY_CODE(+) and " 		                    	);
	sSQL = String::Concat(sSQL,	S"			LST.FACTORY_CODE = PR.FACTORY_CODE(+) and " 					               	);
	sSQL = String::Concat(sSQL,	S"			LST.PROCESS_NO = PS.PROCESS_NO(+) and " 						               	);
	sSQL = String::Concat(sSQL,	S"			LST.PROCESS_NO = PR.PROCESS_NO(+) and " 				                    	);
	sSQL = String::Concat(sSQL,	S"			LST.PRODUCT_CODE = PS.PRODUCT_CODE(+) and " 				                   	);
	sSQL = String::Concat(sSQL,	S"			LST.PRODUCT_CODE = PR.PRODUCT_CODE(+) and " 				                  	);
	sSQL = String::Concat(sSQL,	S"			LST.LOT = PS.LOT(+) and " 								                	);
	sSQL = String::Concat(sSQL,	S"			LST.LOT = PR.LOT(+) " 										              	);
	sSQL = String::Concat(sSQL,	S"			 and lst.YMD = ps.ymd(+)  " 										              	);
	sSQL = String::Concat(sSQL,	S"			 and lst.YMD = pr.ymd(+)   " 										              	);
	sSQL = String::Concat(sSQL,	S"		order by	" 													               	);
	sSQL = String::Concat(sSQL,	S"			LST.YMD, LST.PRODUCT_CODE, LST.LOT " 				                               	);
	sSQL = String::Concat(sSQL,	S"	 ) RESULTLIST " 				                                         	);
	sSQL = String::Concat(sSQL,	S"group by 	" 				                                         	);
	sSQL = String::Concat(sSQL,	S"	RESULTLIST.YMD, " 				                                         	);
	sSQL = String::Concat(sSQL,	S"	RESULTLIST.PRODUCT_CODE " 				                                         	);
	sSQL = String::Concat(sSQL,	S"order by 	" 				                                         	);
	sSQL = String::Concat(sSQL,	S"	RESULTLIST.YMD " 				                                         	);
	

	sMySQLMonth=sSQL;
	//oDataSetMain = oDB->dSetSQL_Select(sSQL, S"GridDataMainForMonth", 0);
	//oDataTableMain = oDataSetMain->Tables->Item["GridDataMainForMonth"];
	////dataGridMain->DataSource = oDataTableMain;
	//dataGridMainForMonth->DataSource=oDataTableMain;


}

void ProductResultList::GetDataGridMainForDay(String* sProcessNo)
{
	String *sSQL = S"";
	String * nameChoose[]={S"c",S"j",S"e"};

	sSQL=String::Concat(sSQL,S" select a.schedule_ymd,a.product_code,a.lot,");
sSQL=String::Concat(sSQL,S"nvl(a.schedule_product_qty,0) as ps_qty,");
sSQL=String::Concat(sSQL,S"a.shift,a.result_ymd,nvl(a.result_product_qty,0) as pr_qty,");
sSQL=String::Concat(sSQL,S"NVL(trunc((a.result_product_qty)-(a.schedule_product_qty)),-(a.schedule_product_qty)) as diff_qty,a.pr_shift as pr_shift,a.pr_team as pr_team,");
sSQL=String::Concat(sSQL,S"b.name_");
sSQL=String::Concat(sSQL,nameChoose[iLanguage]);
sSQL=String::Concat(sSQL,S" AS bc_name from ( ");

sSQL=String::Concat(sSQL,S" select a.schedule_ymd,a.factory_code,a.lot, ");
sSQL=String::Concat(sSQL,S" a.line_code,a.process_no,a.product_code,a.schedule_product_qty,a.shift,a.result_product_qty,a.result_ymd, ");
sSQL=String::Concat(sSQL,S" a.pr_shift,a.pr_team,CASE WHEN a.shift='A' THEN b.day_shift WHEN a.shift='B' THEN b.night_shift END AS TEAMWORK from ( ");

sSQL=String::Concat(sSQL,S" select a.ymd as schedule_ymd,a.factory_code,a.lot,a.line_code,a.process_no, ");
sSQL=String::Concat(sSQL,S" a.product_code,a.product_qty as schedule_product_qty,a.shift,b.product_qty as result_product_qty,b.shift as pr_shift,b.pr_team as pr_team,");
sSQL=String::Concat(sSQL,S" b.ymd as result_ymd from product_schedule a left outer join (\n");
					//product_result_data
sSQL=String::Concat(sSQL,S" select a.ymd,a.factory_code,a.lot,a.line_code,a.process_no,a.product_code,a.product_qty,a.shift,");
sSQL=String::Concat(sSQL,S" b.name_");
sSQL=String::Concat(sSQL,nameChoose[iLanguage]);
sSQL=String::Concat(sSQL,S" as pr_team");
sSQL=String::Concat(sSQL,S" from product_result_data a ");
sSQL=String::Concat(sSQL,S" left outer join team_mst b ");
sSQL=String::Concat(sSQL,S" on a.team=b.team and a.process_no = b.process_no ");
/////
sSQL=String::Concat(sSQL,S"\n) b on ");
sSQL=String::Concat(sSQL,S" a.factory_code=b.factory_code and a.lot = b.lot and a.line_code=b.line_code ");
sSQL=String::Concat(sSQL,S" and a.process_no=b.process_no and a.product_code=b.product_code ");

   
   sSQL=String::Concat(sSQL,S" where a.ymd>=to_date('",dateTimePickerFrom->Text,S"','yyyy-mm-dd') ");
      sSQL=String::Concat(sSQL,S" and a.ymd<=to_date('",dateTimePickerTo->Text,S"','yyyy-mm-dd') ");
      sSQL=String::Concat(sSQL,S" and a.process_no='",sProcessNo,S"' ");
      sSQL=String::Concat(sSQL,S" and a.factory_code='",sFactory,S"' "); 
sSQL=String::Concat(sSQL,S" ) a left outer join work_calender b ");
sSQL=String::Concat(sSQL,S" on a.factory_code=b.factory_code and a.line_code=b.line_code and a.process_no=b.process_no and a.schedule_ymd=b.work_ymd ");
sSQL=String::Concat(sSQL,S" ) a left outer join team_mst b on a.teamwork = b.team and a.process_no=b.process_no order by a.schedule_ymd ");

//合计查询
String * hejiSQL=S"";
hejiSQL=String::Concat(hejiSQL,S"select sum(product_qty) as allproqty from product_schedule a where");
hejiSQL=String::Concat(hejiSQL,S" a.ymd>=to_date('",dateTimePickerFrom->Text,S"','yyyy-mm-dd') ");
hejiSQL=String::Concat(hejiSQL,S" and a.ymd<=to_date('",dateTimePickerTo->Text,S"','yyyy-mm-dd') ");
hejiSQL=String::Concat(hejiSQL,S" and a.process_no='",sProcessNo,S"' ");
hejiSQL=String::Concat(hejiSQL,S" and a.factory_code='",sFactory,S"' "); 

//查询数据

hejiDS=oDB->dSetSQL_Select(hejiSQL, S"HeJiTable", 0);
hejiDT = hejiDS->Tables->Item[S"HeJiTable"];
//hejiDS->Tables


	//long sumTotalPlan1;
	sumTotalPlan1=0;

	IEnumerator* tempRow = hejiDT->Rows->GetEnumerator();
	tempRow->Reset();
	while (tempRow->MoveNext())
	{
		DataRow *dRowTmp = __try_cast<DataRow*>(tempRow->Current);
		//dRowTmp->Item[S"allproqty"]
		if(dRowTmp->IsNull(0))
		{
		}else{
			sumTotalPlan1    =sumTotalPlan1+Convert::ToInt32(dRowTmp->Item[S"allproqty"]);}
			
	}


//allpqty=S"";
//OleDbDataReader * reader;
//OleDbCommand * cmmd;
//cmProducts->CommandType = CommandType::Text;
//		cmProducts->Connection = cnNwind;
//cmmd->CommandText=hejiSQL;
////if (oDB->connBase->State==System::Data::ConnectionState::Closed){oDB->connBase->Open();}
////oDB->blnCnnOpen();
////reader=oDB->dReadSQL_Select(hejiSQL);
//
//while(reader->Read()){