frmdelivercost.cpp

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

#include "stdafx.h"
#include "frmDeliverCost.h"
#include "infClass.h"

#include "DBBase.h" 

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

void frmDeliverCost::InitGridGPartsMst(void)
{
	DataGridTextBoxColumn *oLot;
	DataGridTextBoxColumn *oProductCode;
	DataGridTextBoxColumn *oQty;
	DataGridTextBoxColumn *oYmd;

	DataGridTableStyle *oTableStyle = new DataGridTableStyle();

	oTableStyle->MappingName	= S"dDeliverCOSTMST";

	oLot				= new DataGridTextBoxColumn();
	oLot->MappingName	= S"lot";
	oLot->HeaderText	= oInf->saG_Lot[iLanguage];
	oLot->Alignment		= HorizontalAlignment::Center;
	oLot->Width			= 200;
	oLot->NullText		= S"";

	//ProductCode
	oProductCode				= new DataGridTextBoxColumn();
	oProductCode->MappingName	= S"product_code";
	oProductCode->HeaderText	= oInf->sa_product[iLanguage];
	oProductCode->Alignment		= HorizontalAlignment::Center;
	oProductCode->Width			= 200;
	oProductCode->NullText		= S"";
	//end kasenhoo

	//money
	oQty						= new DataGridTextBoxColumn();
	oQty->MappingName			= S"STOCK_QTY";
	oQty->HeaderText			= oInf->saG_QTY[iLanguage];
	oQty->Alignment				= HorizontalAlignment::Center;
	oQty->Width					= 200;
	oQty->NullText				= S"";
	//end kasenhoo

	//Product Qty
	oYmd						= new DataGridTextBoxColumn();
	oYmd->MappingName			= S"ymd";
	oYmd->HeaderText			= oInf->saG_DeliYMD[iLanguage];
	oYmd->Alignment				= HorizontalAlignment::Center;
	oYmd->Width					= 330;
	oYmd->NullText				= S"";
	//end kasenhoo
	
	oTableStyle->GridColumnStyles->Add(oLot);
	oTableStyle->GridColumnStyles->Add(oProductCode);
	oTableStyle->GridColumnStyles->Add(oQty);
	oTableStyle->GridColumnStyles->Add(oYmd);
	dataGrid1->TableStyles->Add(oTableStyle);
}

bool frmDeliverCost::StrProduct()
{
	String *strSQL;
	OleDbDataReader *dReadTmp;
	strSQL = String::Concat(strSQL,S" select * from process_pro where product_no='",cmbProCode->Text,S"' and process_no='",oInf->strProcess,S"'");
	oDB->blnCnnOpen();
	dReadTmp = oDB->dReadSQL_Select(strSQL);
	if (dReadTmp->HasRows==false)
	{
		return false;
	}
	else
	{
		return true;
	}
	return true;
}

//返回批次号,采用先进先出(FiFo)
void  frmDeliverCost::strGetGData(DBBase * DBTmp,String * strproductcode)
{
	String * strSQL;
	DataSet * dSetTmp ;
    DataView * dViewTmp;
	DataTable * dTblTmp;
	strSQL=S"";
	
	strSQL=String::Concat(strSQL,S"  select aa.lot as lot,aa.product_code as product_code,aa.stock_qty - nvl(cc.deliver_qty,0) as STOCK_QTY,");
	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													");

	DBTmp->blnCnnOpen();

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

	dTblTmp= dSetTmp->Tables->Item[S"dDeliverCOSTMST"];

	DBTmp->blnCnnClose();
	
	dViewTmp = new DataView(dTblTmp);

	dViewTmp->AllowNew= false ;

	dViewTmp->AllowDelete = false ;
	
	//oDataView= dSetTmp->Tables->Item[S"dDeliverCOSTMST"]->DefaultView;
	//oDataView->AllowNew	= false; 
	//oDataView->AllowEdit= false; 
	
	dataGrid1->DataSource = dViewTmp;

	return ;
}

//返回原材料,采用先进先出(FiFo)
void  frmDeliverCost::strGetYGData(DBBase * DBTmp,String * strproductcode)
{
	String * strSQL;
	DataSet * dSetTmp ;
    DataView * dViewTmp;
	DataTable * dTblTmp;

	strSQL=S"";
	strSQL=String::Concat(strSQL,S" select													");
	strSQL=String::Concat(strSQL,S"			a.lot as lot,a.product_code as product_code,STOCK_QTY as STOCK_QTY");
	//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 = '",oInf->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.stock_qty <> 0							");
	strSQL=String::Concat(strSQL,S"			AND a.LOT IS NOT NULL							");
	strSQL=String::Concat(strSQL,S"			AND a.LOT IS NOT NULL	 order by rownum asc	");

	DBTmp->blnCnnOpen();

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

	//dSetTmp=DBTmp->dSetSQL_Select(strSQL,"dDeliverCOSTMST", 0);

	dTblTmp= dSetTmp->Tables->Item[S"dDeliverCOSTMST"];

	DBTmp->blnCnnClose();
	
	dViewTmp = new DataView(dTblTmp);

	dViewTmp->AllowNew= false ;

	dViewTmp->AllowDelete = false ;
	
	//oDataView= dSetTmp->Tables->Item[S"dDeliverCOSTMST"]->DefaultView;
	//oDataView->AllowNew	= false; 
	//oDataView->AllowEdit= false; 
	
	dataGrid1->DataSource = dViewTmp;
	return ;
}

bool frmDeliverCost::DateCell()
{
	DataGridCell oCell;
	

	oCell = dataGrid1->CurrentCell;
	oCell.ColumnNumber=0;
	txtLot->Text = dataGrid1->Item[oCell]->ToString();

	oCell.ColumnNumber=2;
	txtNum->Text = dataGrid1->Item[oCell]->ToString();

	return true;
}


void frmDeliverCost::strDeliverCostMst(DBBase * DBTmp,String * check0,String * check1,String * check2)
{
	String * strSQL;
	OleDbDataReader *dReadTmp;
	//DataSet * dsetTmp;
	String * strFdName;

	switch(oInf->intLang) {
		case FapLan_EN:
			strFdName = S"NAME_E";			
			break;
		case FapLan_JP:
			strFdName = S"NAME_J";
			break;
		case FapLan_CN:
			strFdName = S"NAME_C";
			break;
		default:
			strFdName = S"NAME_C";

	}


	strSQL = S" select ";
	strSQL = String::Concat(strSQL,strFdName,S" from cost_mst where ");
	if (Convert::ToInt32(check0)>=0 && Convert::ToInt32(check1)>=0 && Convert::ToInt32(check2)>=0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 0 or cost_division = 1 or cost_division = 2");
	}
	else if (Convert::ToInt32(check0)>=0 && Convert::ToInt32(check1)<0 && Convert::ToInt32(check2)<0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 0 ");
	}
	else if (Convert::ToInt32(check0)<0 && Convert::ToInt32(check1)>=0 && Convert::ToInt32(check2)<0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 1 ");
	}
	else if (Convert::ToInt32(check0)<0 && Convert::ToInt32(check1)<0 && Convert::ToInt32(check2)>=0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 2 ");
	}
	else if (Convert::ToInt32(check0)>=0 && Convert::ToInt32(check1)>=0 && Convert::ToInt32(check2)<0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 0 or cost_division = 1 ");
	}
	else if (Convert::ToInt32(check0)>=0 && Convert::ToInt32(check1)<0 && Convert::ToInt32(check2)>=0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 0 or cost_division = 2 ");
	}
	else if (Convert::ToInt32(check0)<0 && Convert::ToInt32(check1)>=0 && Convert::ToInt32(check2)>=0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 1 or cost_division = 2 ");
	}
	if (Convert::ToInt32(check0)<0 && Convert::ToInt32(check1)<0 && Convert::ToInt32(check2)<0)
	{
		strSQL = String::Concat(strSQL,S" cost_division = 10 ");
	}

	DBTmp->blnCnnOpen();

	//dsetTmp = DBTmp->dSetSQL_Select(strSQL,S"TMP",0);
	
	dReadTmp = oDB->dReadSQL_Select(strSQL);
	if (dReadTmp->HasRows==false)
	{
		return ;
	}
	else
	{
		while(dReadTmp->Read())
		{
			cboReason->Items->Add(dReadTmp->GetString(0));
		}
	}

	DBTmp->blnCnnClose();
}


bool frmDeliverCost::updatecostmst(int istocktype)
{
	String * strSQL;

	oDB->blnCnnOpen();
	
	oDB->blnBeginTrans();	

	strSQL = S"update stock_data set STOCK_QTY = STOCK_QTY -";
	strSQL = String::Concat(strSQL,txtNum->Text);
	strSQL = String::Concat(strSQL,S" where factory_code = '",oInf->strFactoryCode,S"'");
	strSQL = String::Concat(strSQL,S" and STOCK_POS = '",oInf->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 = '",Convert::ToString(istocktype),S"'");

	if (oDB->blnSQL_Execute(strSQL)) 
	{
		
	}
	else
	{
		return false;
	}

	strSQL = S" insert into f_delver_cost_data(factory_code,origin_code,destination_code,product_code,rank,deliver_qty,parts_div,deliver_cost_ymd,shift,lot) VALUES (";
	strSQL = String::Concat(strSQL,S"'",oInf->strFactoryCode,S"',");
	strSQL = String::Concat(strSQL,S"'",oInf->strProcess,S"',");
	strSQL = String::Concat(strSQL,S"(select distinct cost_no from cost_mst where ",strCdName,S"='",cboReason->Text,S"'),");
	strSQL = String::Concat(strSQL,S"'",cmbProCode->Text,S"',");
	strSQL = String::Concat(strSQL,S"NULL,");
	strSQL = String::Concat(strSQL,txtNum->Text,S",");
	strSQL = String::Concat(strSQL,S"'0',");
	strSQL = String::Concat(strSQL,S"to_date('",DateTime::Now.ToString(),S"','yyyy-mm-dd hh24-mi-ss'),");
	strSQL = String::Concat(strSQL,S"'",cShift,S"',");
	strSQL = String::Concat(strSQL,S"'",txtLot->Text,S"')");

	if (oDB->blnSQL_Execute(strSQL)) 
	{
		
	}
	else
	{
		return false;
	}

	return true;
}



String * frmDeliverCost::CostMstName()
{
	String * strSQL;
	System::Data::DataRow *dRowTmp;
	strSQL=S"";
	strSQL=String::Concat(S"select distinct cost_no from cost_mst where ",strCdName,S"='",cboReason->Text,S"'");
	
	if (oDB->blnCnnOpen()==false)
	{
		MessageBox::Show(S"Open Oracle Failed!!",S"Deliver",MessageBoxButtons::OK, MessageBoxIcon::Exclamation);
		return S"";
	}


	try
	{
		oDB->blnCnnClose();
	}
	catch (...) {
		return S"";
	}

	dRowTmp = oDB->dRowSQL_Select(strSQL);

	try
	{
		return dRowTmp->Item[S"cost_no"]->ToString();
	}
	catch (Exception *ex)
	{
		MessageBox::Show(ex->Message);
		return S"";
	}

	return S"";

}