deliver.cpp

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

}

//返回批次号,采用先进先出(FiFo)
void FAP:: Deliver::strGetSplitLot(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 aa.stock_qty - nvl(cc.deliver_qty,0) as STOCK_QTY,					");
	strSQL=String::Concat(strSQL,S"         aa.product_code,													");
	strSQL=String::Concat(strSQL,S"			aa.lot as lot,														");
	strSQL=String::Concat(strSQL,S"			aa.ymd	as ymd														");
	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"'						");
	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"					AND b.stock_ymd IS NULL										");
	strSQL=String::Concat(strSQL,S"					group by b.lot,b.RESLUT_YMD									");
	strSQL=String::Concat(strSQL,S"					order by b.RESLUT_YMD,lot asc								");
	strSQL=String::Concat(strSQL,S"			) cc on																");
	strSQL=String::Concat(strSQL,S"					aa.lot=cc.lot												");
	strSQL=String::Concat(strSQL,S"					and aa.ymd=cc.RESLUT_YMD									");
	strSQL=String::Concat(strSQL,S" where																		");
	//如果移动实绩=生产实际就表示数量全部出掉,这条数据不进行显示
	strSQL=String::Concat(strSQL,S"   aa.stock_qty - nvl(cc.deliver_qty,0)<>0									");
	strSQL=String::Concat(strSQL,S" order by aa.ymd,aa.lot asc													");

	dSetTmp = DBTmp->dSetSQL_Select(strSQL,S"stock_data",0);

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

	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;
			
			labnumber->Text=Convert::ToString(intsumstockqty + (intsumstockqty * (-2)));
		}
		else
		{
			sumstrPartChCode=String::Concat(sumstrPartChCode,strPartChCode);
			
			txtLot->Text = sumstrPartChCode;
			
			labnumber->Text=Convert::ToString(intsumstockqty + (intsumstockqty * (-2)));

			return ;
		}

		
	}
	return ;
}


//检查数量数据库是不是够
bool FAP:: Deliver::strchecklotnumber(int strstockqty,DBBase * DBTmp,String * strproductcode)
{
	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"'						");
	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"					AND b.stock_ymd IS NULL										");
	strSQL=String::Concat(strSQL,S"					group by b.lot,b.RESLUT_YMD									");
	strSQL=String::Concat(strSQL,S"					order by b.RESLUT_YMD,lot asc								");
	strSQL=String::Concat(strSQL,S"			) cc on																");
	strSQL=String::Concat(strSQL,S"					aa.lot=cc.lot												");
	strSQL=String::Concat(strSQL,S"					and aa.ymd=cc.RESLUT_YMD									");
	strSQL=String::Concat(strSQL,S" where																		");
	//如果移动实绩=生产实际就表示数量全部出掉,这条数据不进行显示
	strSQL=String::Concat(strSQL,S"   aa.stock_qty - nvl(cc.deliver_qty,0)<>0									");
	
	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;

}


bool Deliver::CreateViewFAP(String * strFilename)
{
	FileInfo *strAppfileName;
	strAppfileName = new FileInfo("FAP");
	
	pstrCsnPath=String::Concat(strAppfileName->DirectoryName,S"\\Excel\\");

	vstrYMDHIS=String::Concat(pstrCsnPath,vstrYMDHIS);

	vstrDateTime=String::Concat(pstrCsnPath,S"物料转储单",vstrDateTime,S".xls");

	////查找是否有相同文件,有就执行删除.
	//if (File::Exists(vstrYMDHIS)==true)
	//{
	//	File::Delete(vstrYMDHIS);
	//	return true;
	//}
	//else
	//{

		File::Copy(vstrYMDHIS,vstrDateTime,true);
	//}

	return true;

}

void Deliver::OutputExcel(void)
{
	System::Object			* oMissing;
	Excel::ApplicationClass	* pExcel;
	Excel::Workbook			* pWorkBook;
	Excel::Worksheet		* pActiveSheet;

	System::Object			* MissingValue;

	//Excel行
	int						 ERowi;
	//Excel列
	int						 EComi;	
	
	//Excel列
	int						 EComi1;

	//Excel列
	int						 EComi2;

	//Excel列
	int						 EComi3;

	//分割
	String *Stris=",";

	Char chars[]={','};

	String * strnum[];

	String *strlotnum;

	strlotnum=txtLot->Text->TrimEnd(Stris->ToCharArray());

	strnum=strlotnum->Split(chars);

	String * strlotnull;

	MissingValue=Type::Missing;

	String * strStock_qty;

	String * strStock_ymd;

	try {
		oMissing = System::Reflection::Missing::Value;

		pExcel = new Excel::ApplicationClass();
		pExcel->Visible = true;

		pWorkBook  = pExcel->Workbooks->Open(vstrDateTime,MissingValue,
		MissingValue,MissingValue,MissingValue,
		MissingValue,MissingValue,MissingValue,
		MissingValue,MissingValue,MissingValue,
		MissingValue,MissingValue,MissingValue,
		MissingValue);

		
		//Worksheet Rename
		static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(1)])->Select(oMissing);
		pActiveSheet = static_cast<Excel::Worksheet*>(pExcel->ActiveSheet);
		pActiveSheet->Name = S"Test";

		//Cell Edit
//		static_cast<Excel::Range*>(pSheet1->Cells->get_Item(1,1))->Value2 = str; 
		pActiveSheet->Cells->set_Item(__box(3), __box(3), infClsDeliver->strFactoryCode);

		pActiveSheet->Cells->set_Item(__box(2), __box(9), txtDate->Text);

		pActiveSheet->Cells->set_Item(__box(4), __box(4), S"311");

		pActiveSheet->Cells->set_Item(__box(4), __box(6), txtFromNo->Text);

		pActiveSheet->Cells->set_Item(__box(4), __box(8), comboBox1->Text);

		ERowi=6;
		EComi=4;
		EComi1=7;
		EComi2=8;
		EComi3=10;

		for (int ix=0;ix<strnum->Length;ix++)
		{
			ERowi++;
			
			strlotnull=strnum[ix];

			//材料编码
			pActiveSheet->Cells->set_Item(__box(ERowi), __box(EComi), txtProCode->Text);
			
			//批号
			pActiveSheet->Cells->set_Item(__box(ERowi), __box(EComi1), strlotnull);

			if (ix==strnum->Length - 1)
			{
				//数量
				strStock_qty=Convert::ToString(Int32::Parse(saG_stock_qty[ix])-(System::Int32::Parse(labnumber->Text)));

				//strStock_qty=Convert::ToString(intGetStockNum(txtFromNo->Text,txtProCode->Text,DBDeliver,strlotnull,1) - (System::Int32::Parse(labnumber->Text)));
			}
			else
			{
				//数量
				strStock_qty=saG_stock_qty[ix];

				//strStock_qty=Convert::ToString(intGetStockNum(txtFromNo->Text,txtProCode->Text,DBDeliver,strlotnull,1));

			}

			strStock_ymd=saG_stock_ymd[ix];

			pActiveSheet->Cells->set_Item(__box(ERowi), __box(EComi2), strStock_qty);

			pActiveSheet->Cells->set_Item(__box(ERowi), __box(EComi3), strStock_ymd);

		}

		//WorkSheet Add

/*
		//Save File
		pWorkBook->Save();
*/
		//Quit Excel
   		//pWorkBook->Close(false, oMissing, oMissing);
   		System::Runtime::InteropServices::Marshal::ReleaseComObject(pWorkBook);
   		//pExcel->Quit();
	 	System::Runtime::InteropServices::Marshal::ReleaseComObject(pExcel);
   		GC::Collect();
   	}
   	catch(Exception* e)	{
		//	;
	}


}
void Deliver::messageShow()
{			 String *confirmMsg[]={S"确认",S"確認",S"Confirm"};
String* message1[]={String::Concat(S"是否出库数量",txtQuanity->Text,S"？"),String::Concat(S"数量",txtQuanity->Text,S"を出庫していいですか？"),S"？？？"};
			 //String* message = txtQuanity->Text;
            String* caption = confirmMsg[iLanguage];
            MessageBoxButtons buttons = MessageBoxButtons::OKCancel;//MessageBoxButtons::YesNo;
            //System::Windows::Forms::DialogResult aaaresult;

            // Displays the MessageBox.
			if (DialogResult::Cancel ==MessageBox::Show(this, message1[iLanguage], caption, buttons,MessageBoxIcon::Question, MessageBoxDefaultButton::Button1)){}else{return;}
iflag=true;
			//if (aaaresult == DialogResult::Yes) {}else{return;}}
}