stockprocess.cpp

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

////
////	}
//	
//	strSQL = String::Concat(strSQL,intStockType.ToString(),S",");
//
//	strSQL = String::Concat(strSQL,S"'",txtLot->Text,S"'");
//
//
//	strSQL = String::Concat(strSQL,S" )");
//
//
//	DBbStockProcess->blnCnnOpen();
//
//	DBbStockProcess->blnBeginTrans();
//
//	if (DBbStockProcess->blnSQL_Execute(strSQL)) {
////		MessageBox::Show(S"处理成功!!");
////		DBbStockProcess->blnCommit();
//	}
//	else{
//		MessageBox::Show(S"处理失败!!");
//		DBbStockProcess->blnRollBack();
//		MessageBox::Show(strSQL);
//		return;
//
//	}

	//update stock_data

	String * StrStock_type;
	
	if (StrProduct()==false)
	{
		StrStock_type=S"0";
	}
	else
	{
		StrStock_type=S"1";
	}

	strSQL = S"update stock_data set STOCK_QTY = STOCK_QTY +";
	strSQL = String::Concat(strSQL, intNum.ToString());
	strSQL = String::Concat(strSQL,S" where factory_code = '",infStockProcess->strFactoryCode,S"'");
	strSQL = String::Concat(strSQL,S" and STOCK_POS = '",infStockProcess->strProcess,S"'");
	strSQL = String::Concat(strSQL,S" and PRODUCT_CODE = '",cmbProCode->Text,S"'");
	strSQL = String::Concat(strSQL,S" and LOT = '",txtLot->Text,S"'");
	strSQL = String::Concat(strSQL,S" and stock_type =",StrStock_type);

	int intExect;

	DBbStockProcess->blnCnnOpen();

	DBbStockProcess->blnBeginTrans();

	if (!DBbStockProcess->blnSQL_Execute(strSQL,&intExect)){
		return false;
	}
	
	if (intExect <= 0 ) {
		strSQL = S"insert into"								;
		strSQL = String::Concat(strSQL,		S"	STOCK_DATA "							);
		strSQL = String::Concat(strSQL,		S"("										);
		strSQL = String::Concat(strSQL,		S"	FACTORY_CODE,"							);
		strSQL = String::Concat(strSQL,		S"	STOCK_POS,"								);
		strSQL = String::Concat(strSQL,		S"	PRODUCT_CODE,"							);
		//strSQL = String::Concat(strSQL,		S"	RANK,"									);
		strSQL = String::Concat(strSQL,		S"	STOCK_TYPE,"							);
		strSQL = String::Concat(strSQL,		S"	STOCK_QTY,"								);
		strSQL = String::Concat(strSQL,		S"	LOT"									);
		strSQL = String::Concat(strSQL,		S")"										);
		strSQL = String::Concat(strSQL,		S"values"									);
		strSQL = String::Concat(strSQL,		S"("										);
		strSQL = String::Concat(strSQL,		S"	'",	infStockProcess->strFactoryCode,	S"',"	);
		strSQL = String::Concat(strSQL,		S"	'",	infStockProcess->strProcess,		S"',"	);
		strSQL = String::Concat(strSQL,		S"	'",	cmbProCode->Text,					S"',"	);
		//strSQL = String::Concat(strSQL, 	S"	'", sRank,						S"',"	);
		strSQL = String::Concat(strSQL, StrStock_type,	S","							); //Stock Type
		strSQL = String::Concat(strSQL,				intNum.ToString(),	S","			);
		strSQL = String::Concat(strSQL,		S"	'", txtLot->Text,				S"'"	);
		strSQL = String::Concat(strSQL,		S")"										);

		if (!DBbStockProcess->blnSQL_Execute(strSQL))
		{
			return false;
		}
		
	}

	//update stock_data
	strSQL = S"update stock_data set STOCK_QTY = STOCK_QTY -";
	strSQL = String::Concat(strSQL, intNum.ToString());
	strSQL = String::Concat(strSQL,S" where factory_code = '",infStockProcess->strFactoryCode,S"'");
	strSQL = String::Concat(strSQL,S" and STOCK_POS = '",infStockProcess->strProcess,S"'");
	strSQL = String::Concat(strSQL,S" and PRODUCT_CODE = '",cmbProCode->Text,S"'");
	strSQL = String::Concat(strSQL,S" and LOT = '",txtLot->Text,S"'");
	strSQL = String::Concat(strSQL,S" and stock_type = 2 ");

	if (!DBbStockProcess->blnSQL_Execute(strSQL)){
		return false;
	}
	
	return true;
}

int StockProcess::intGetStockType(String* strFac,String * strProcess,String * strPro,String * strLot)
{
	String * strSQL;
	OleDbDataReader *dReadTmp;

	strSQL = S" select STOCK_TYPE from stock_data where ";
	strSQL = String::Concat(strSQL,S" FACTORY_CODE = '",strFac,S"'");
	strSQL = String::Concat(strSQL,S" and STOCK_POS = '",strProcess,S"'");
	strSQL = String::Concat(strSQL,S" and PRODUCT_CODE = '",strPro,S"'");
	strSQL = String::Concat(strSQL,S" and LOT = '",strLot,S"' and rownum < 2");

	DBbStockProcess->blnCnnOpen();
	
	dReadTmp = DBbStockProcess->dReadSQL_Select(strSQL);

	while (dReadTmp->Read()) {
		try {
			dReadTmp->GetDecimal(0);
		}
		catch (...) {
			return 0;
		}
	}
	return 0;

}


DataTable * StockProcess::SetReasonj(int inReason,DBBase * dbTmp,int iLanguage)
{
	DataTable *dtblTmp;
	DataSet * dsetTmp;

	String *strFdName ;

	String * strSQL;

	switch(iLanguage) {
	case FapLan_EN:
		strFdName = S"REASON_E";			
		break;
	case FapLan_JP:
		strFdName = S"REASON_J";
		break;
	case FapLan_CN:
		strFdName = S"REASON_C";
		break;
	default:
		strFdName = S"REASON_E";		
		}

	if (dbTmp->blnCnnOpen()==false) 
	{
		MessageBox::Show(S"Open Oracle Failed!!",S"Deliver",MessageBoxButtons::OK, MessageBoxIcon::Exclamation);
	}
	
	strSQL=S"";
	strSQL=String::Concat(strSQL,S" Select ",strFdName,S" from reason_mst where Reason_div =",inReason,S"");

	dsetTmp = dbTmp->dSetSQL_Select(strSQL,S"TMP",0);

	dbTmp->blnCnnClose();

	try
	{
		dtblTmp = dsetTmp->Tables->Item[S"TMP"];
	}
	
    catch (...) {
		return NULL;		
	}

	return dtblTmp;

}


//检查原材料库存
bool FAP::StockProcess::strchecklotnumber2(int strstockqty,DBBase * DBTmp,String * strproductcode,String * strprocess)
{
	String * strSQL;
	DataRow* dRowTmp;
	int intstockqty;
	String * strTmp1;

	strSQL=S"";
	strSQL=String::Concat(strSQL,S" select									");
	strSQL=String::Concat(strSQL,S"			sum(STOCK_QTY) as STOCK_QTY,	");
	strSQL=String::Concat(strSQL,S"			product_code,					");
	//strSQL=String::Concat(strSQL,S"			lot,							");
	strSQL=String::Concat(strSQL,S"			99991231 as ymd					");
	strSQL=String::Concat(strSQL,S" from									");
	strSQL=String::Concat(strSQL,S"			stock_data a					");
	strSQL=String::Concat(strSQL,S"       where a.product_code = '",strproductcode,S"'	");
	strSQL=String::Concat(strSQL,S"			and a.stock_pos = '",infStockProcess->strProcess,S"'		");
	strSQL=String::Concat(strSQL,S"		  and a.stock_type <> 2				");
	strSQL=String::Concat(strSQL,S"			and a.stock_qty <> 0			");
	strSQL=String::Concat(strSQL,S"			AND a.LOT IS NOT NULL	 and stock_qty>0		");
	strSQL=String::Concat(strSQL,S"			AND a.LOT IS NOT NULL	 group by product_code	");

	
	if (!DBTmp->blnCnnOpen()) {
		MessageBox::Show(S"Open Oracle Error!! ",S"lot");
		return false;
	}
	dRowTmp = DBTmp->dRowSQL_Select(strSQL);

	try
	{
		strTmp1=String::Format("{0:######}",dRowTmp->Item[S"STOCK_QTY"]);

		intstockqty=System::Int32::Parse(strTmp1);
		
		if ((strstockqty>intstockqty)==true)
		{
			return false;
		}
		else
		{
			return true;
		}
		
	}
	catch (...) {
		
		return false;

	}

	return true;

}

//返回批次号,采用先进先出(FiFo)
void FAP::StockProcess::strGetSplitLot2(int strstockqty,DBBase * DBTmp,String * strproductcode)
{
	String * strSQL;
	DataSet * dSetTmp ;
	DataTable * dTableTmp;

	int intstockqty;
	int intsumstockqty;
	String * sumstrPartChCode;
	String * strPartChCode;
	String * strTmp1;
	int inc;

	sumstrPartChCode=S"";

	intsumstockqty=strstockqty;

	strSQL=S"";
	strSQL=String::Concat(strSQL,S" select									");
	strSQL=String::Concat(strSQL,S"			STOCK_QTY,						");
	strSQL=String::Concat(strSQL,S"			product_code,					");
	strSQL=String::Concat(strSQL,S"			lot,							");
	strSQL=String::Concat(strSQL,S"			99991231 as ymd					");
	strSQL=String::Concat(strSQL,S" from									");
	strSQL=String::Concat(strSQL,S"			stock_data a					");
	strSQL=String::Concat(strSQL,S"       where a.product_code = '",strproductcode,S"'	");
	strSQL=String::Concat(strSQL,S"			and a.stock_pos = '",infStockProcess->strProcess,S"'		");
	strSQL=String::Concat(strSQL,S"		  and a.stock_type <> 2				");
	strSQL=String::Concat(strSQL,S"			and a.stock_qty <> 0			");
	strSQL=String::Concat(strSQL,S"			AND a.LOT IS NOT NULL	and stock_qty>0		");
	strSQL=String::Concat(strSQL,S"			AND a.LOT IS NOT NULL	 order by rownum desc	");
 
	dSetTmp = DBTmp->dSetSQL_Select(strSQL,S"stock_data1",0);

	dTableTmp = dSetTmp->Tables->Item[S"stock_data1"];

	DBTmp->blnCnnClose();

	System::Collections::IEnumerator* emnFapRow = dTableTmp->Rows->GetEnumerator();

	emnFapRow->Reset();

	inc=0;

	saG_stock_qty = new String __gc * [8];

	saG_stock_ymd = new String __gc * [8];

	while (emnFapRow->MoveNext())
	{

		inc++;


		DataRow *dRowTmp = __try_cast<DataRow*>(emnFapRow->Current);

		strPartChCode=dRowTmp->Item[S"lot"]->ToString();

		strTmp1 = String::Format("{0:######}",dRowTmp->Item[S"STOCK_QTY"]);
		
		saG_stock_qty[inc-1]=strTmp1;

		saG_stock_ymd[inc-1]=String::Format("{0:yyyyMMdd}",dRowTmp->Item[S"ymd"]);

		intstockqty=System::Int32::Parse(strTmp1);

		intsumstockqty=intsumstockqty-intstockqty;

		if (intsumstockqty>0)
		{
			sumstrPartChCode=String::Concat(sumstrPartChCode,strPartChCode,S",");

			txtLot->Text = sumstrPartChCode;
			
			txtProCode->Text=Convert::ToString(intsumstockqty + (intsumstockqty * (-2)));
		}
		else
		{
			sumstrPartChCode=String::Concat(sumstrPartChCode,strPartChCode);
			
			txtLot->Text = sumstrPartChCode;
			
			txtProCode->Text=Convert::ToString(intsumstockqty + (intsumstockqty * (-2)));

			return ;
		}

		
	}
	return ;
}


//检查数量数据库是不是够
bool FAP::StockProcess::strchecklotnumber1(int strstockqty,DBBase * DBTmp,String * strproductcode,String * strprocess)
{
	String * strSQL;
	DataRow* dRowTmp;
	int intstockqty;
	String * strTmp1;

	strSQL=S"";
	strSQL=String::Concat(strSQL,S"  select sum(aa.stock_qty - nvl(cc.deliver_qty,0))	as STOCK_QTY    		");
	strSQL=String::Concat(strSQL,S" from																		");
	//查找生产实际的日期,按生产日期进行先进先出，但查出来的是生产实绩的数量
	strSQL=String::Concat(strSQL,S"			(Select																");
	strSQL=String::Concat(strSQL,S"						distinct												");
	//strSQL=String::Concat(strSQL,S"					b.product_QTY,												");
	strSQL=String::Concat(strSQL,S"					a.stock_qty,												");
	strSQL=String::Concat(strSQL,S"					a.product_code,												");
	strSQL=String::Concat(strSQL,S"					a.lot as lot,												");
	strSQL=String::Concat(strSQL,S"			nvl(to_char(b.YMD,'yyyymmdd'),'9999-12-31') as YMD					");
	//strSQL=String::Concat(strSQL,S"					b.YMD														");
	strSQL=String::Concat(strSQL,S"			from																");
	strSQL=String::Concat(strSQL,S"					stock_data a												");
	strSQL=String::Concat(strSQL,S"			left outer join product_result_data b on							");
	strSQL=String::Concat(strSQL,S"					a.lot=b.lot													");
	strSQL=String::Concat(strSQL,S"			where																");
	strSQL=String::Concat(strSQL,S"					a.product_code='",strproductcode,S"'						");
	strSQL=String::Concat(strSQL,S"					and a.stock_type='1' 										");
	strSQL=String::Concat(strSQL,S"					and a.stock_qty<>0											");
	strSQL=String::Concat(strSQL,S"					AND a.LOT IS NOT NULL										");
	strSQL=String::Concat(strSQL,S"					order by b.YMD,lot asc										");
	strSQL=String::Concat(strSQL,S"			) aa																");
	strSQL=String::Concat(strSQL,S" left outer join																");
		//查找出库的移动实际(生产实际-移动实际=库存)
	strSQL=String::Concat(strSQL,S"			(																	");
	strSQL=String::Concat(strSQL,S"			select																");
	strSQL=String::Concat(strSQL,S"					sum(b.deliver_qty) as deliver_qty,							");
	strSQL=String::Concat(strSQL,S"					b.lot,														");
	strSQL=String::Concat(strSQL,S"				nvl(to_char(b.RESLUT_YMD,'yyyymmdd'),'9999-12-31') as RESLUT_YMD");
	//strSQL=String::Concat(strSQL,S"					b.RESLUT_YMD												");
	strSQL=String::Concat(strSQL,S"			from																");
	strSQL=String::Concat(strSQL,S"					stock_data a												");
	strSQL=String::Concat(strSQL,S"			left outer join deliver_data b on									");
	strSQL=String::Concat(strSQL,S"					a.lot=b.lot													");
	strSQL=String::Concat(strSQL,S"			where																");
	strSQL=String::Concat(strSQL,S"					a.product_code='",strproductcode,S"'						");