totallosstable.cpp

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

#include "StdAfx.h"
#include "TotalLossTable.h"

#include "DBBase.h" 

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


void TotalLossTable::SetValueDataGridLossTable(String* sProcessNo,int selectedIndex)
{
	String* saLang[]   = {S"c", S"j", S"e"};
	
	String *sSQL = S"";
	InitGridDataGridLossTable(selectedIndex);
	switch (selectedIndex)
	{
		case 0: 
				sSQL = String::Concat(sSQL,	S"select quality_control_data.manufact_ymd as YMD, 	" 	                     	                           );
				sSQL = String::Concat(sSQL,	S"		 parts_mst.product_code, 	" 	                                     	              );
				sSQL = String::Concat(sSQL,	S"       parts_mst.parts_name_", saLang[iLanguage] ,S" as partName, 	" 		);
				sSQL = String::Concat(sSQL,	S"		 quality_control_data.lot,	" 				                            );
				sSQL = String::Concat(sSQL,	S"	     quality_control_data.quantity, " 				                        );
				sSQL = String::Concat(sSQL,	S"	     reason_mst.reason_", saLang[iLanguage] ,S" as reason, " 				);
				/*sSQL = String::Concat(sSQL,	S"	     quality_control_data.destination_code " 				                );*/
				sSQL = String::Concat(sSQL,	S"       cost_mst.name_", saLang[iLanguage] ,S" as destinationName  	" 		);
				sSQL = String::Concat(sSQL,	S"from   quality_control_data, parts_mst, reason_mst ,cost_mst " 		        );
				sSQL = String::Concat(sSQL,	S"where  quality_control_data.product_code = parts_mst.product_code(+)	" 		);
				sSQL = String::Concat(sSQL,	S"	and  quality_control_data.reason_code = REASON_MST.REASON_CODE(+) " 		);
				sSQL = String::Concat(sSQL,	S"	and  quality_control_data.destination_code=cost_mst.cost_no(+) " 		    );
				sSQL = String::Concat(sSQL,	S"	and  cost_mst.cost_division(+)=2  " 		                        	        );
				sSQL = String::Concat(sSQL,	S"	and  quality_control_data.PROCESS_NO='",sProcessNo,S"' " 			        );
				sSQL = String::Concat(sSQL,	S"	and  quality_control_data.manufact_ymd>=to_date('",dateTimePickerFrom->Text,S"','yyyy-mm-dd ') and quality_control_data.manufact_ymd<=to_date('",dateTimePickerTo->Text,S"','yyyy-mm-dd ')+1 " 	   );//if you don't specify hour:minute,the default value is 00:00
				break;
		case 1: 
			    sSQL = String::Concat(sSQL,	S"select to_char(quality_control_data.manufact_ymd,'yyyy-mm') as YMDchar, "         );
				sSQL = String::Concat(sSQL,	S"       parts_mst.parts_name_", saLang[iLanguage] ,S" as partName, 	" 		);
				sSQL = String::Concat(sSQL,	S"	     sum(quality_control_data.quantity) as quantity,  " 				    );
				sSQL = String::Concat(sSQL,	S"	     reason_mst.reason_", saLang[iLanguage] ,S" as reason  " 				);
				sSQL = String::Concat(sSQL,	S"from quality_control_data, parts_mst, reason_mst " 				            );
				sSQL = String::Concat(sSQL,	S"where quality_control_data.product_code = parts_mst.product_code(+)	" 		);
				sSQL = String::Concat(sSQL,	S"	and quality_control_data.reason_code = REASON_MST.REASON_CODE(+) " 			);
				sSQL = String::Concat(sSQL,	S"	and quality_control_data.PROCESS_NO='",sProcessNo,S"' " 			        );
				sSQL = String::Concat(sSQL,	S"group by to_char(quality_control_data.manufact_ymd,'yyyy-mm'), " 			    );
				sSQL = String::Concat(sSQL,	S"		   parts_mst.parts_name_", saLang[iLanguage] ,S", " 				    );
				sSQL = String::Concat(sSQL,	S"	       reason_mst.reason_", saLang[iLanguage] ,S" " 				        );
				
	            break;
		case 2:
			   sSQL = String::Concat(sSQL,	S"select '前期' as YMDchar,  "													 );
			   sSQL = String::Concat(sSQL,	S"		parts_mst.parts_name_", saLang[iLanguage] ,S",  "                        );
			   sSQL = String::Concat(sSQL,	S"      sum(quality_control_data.quantity) as quantity, "                        );
			   sSQL = String::Concat(sSQL,	S"      reason_mst.reason_", saLang[iLanguage] ,S"  "                            );
			   sSQL = String::Concat(sSQL,	S"from  quality_control_data, parts_mst, reason_mst "                            );
			   sSQL = String::Concat(sSQL,	S"where quality_control_data.product_code = parts_mst.product_code(+)  "         );
			   sSQL = String::Concat(sSQL,	S"	and quality_control_data.reason_code = REASON_MST.REASON_CODE(+)  "          );
			   sSQL = String::Concat(sSQL,	S"	and manufact_ymd >= add_months(TRUNC(current_date, 'YEAR'), 3)  "            );
			   sSQL = String::Concat(sSQL,	S"	and manufact_ymd < add_months(TRUNC(current_date, 'YEAR'), 9)  "             );
			   sSQL = String::Concat(sSQL,	S"group by  parts_mst.parts_name_", saLang[iLanguage] ,S",  "                    );
			   sSQL = String::Concat(sSQL,	S"      reason_mst.reason_", saLang[iLanguage] ,S" "                             );
			   sSQL = String::Concat(sSQL,	S"union   "																		 );
			   sSQL = String::Concat(sSQL,	S"select '后期' as YMDchar, "													 );
			   sSQL = String::Concat(sSQL,	S"		parts_mst.parts_name_", saLang[iLanguage] ,S", "						 );
			   sSQL = String::Concat(sSQL,	S"		sum(quality_control_data.quantity) as quantity, "						 );
			   sSQL = String::Concat(sSQL,	S"		reason_mst.reason_", saLang[iLanguage] ,S" "							 );
			   sSQL = String::Concat(sSQL,	S"from  quality_control_data, parts_mst, reason_mst  "							 );
			   sSQL = String::Concat(sSQL,	S"where quality_control_data.product_code = parts_mst.product_code(+)  "         );
			   sSQL = String::Concat(sSQL,	S"	and quality_control_data.reason_code = REASON_MST.REASON_CODE(+) "           );
			   sSQL = String::Concat(sSQL,	S"  and manufact_ymd >= add_months(TRUNC(current_date, 'YEAR'), 9) "             );
			   sSQL = String::Concat(sSQL,	S"	and manufact_ymd < add_months(TRUNC(current_date, 'YEAR'), 15)  "            );
			   sSQL = String::Concat(sSQL,	S"group by   parts_mst.parts_name_", saLang[iLanguage] ,S", "                    );
			   sSQL = String::Concat(sSQL,	S"      reason_mst.reason_", saLang[iLanguage] ,S" "                             );
			   break;
		case 3:
			   sSQL = String::Concat(sSQL,	S"select    "																	 );
			   sSQL = String::Concat(sSQL,	S"	    to_char(quality_control_data.manufact_ymd,'yyyy') as YMDchar,  "		 );
			   sSQL = String::Concat(sSQL,	S"	    parts_mst.parts_name_", saLang[iLanguage] ,S",  "						 );
			   sSQL = String::Concat(sSQL,	S"	    sum(quality_control_data.quantity) as quantity,  "						 );
			   sSQL = String::Concat(sSQL,	S"      reason_mst.reason_", saLang[iLanguage] ,S"  "							 );
			   sSQL = String::Concat(sSQL,	S"from  quality_control_data, parts_mst, reason_mst  "							 );
			   sSQL = String::Concat(sSQL,	S"where quality_control_data.product_code = parts_mst.product_code(+)  "		 );
			   sSQL = String::Concat(sSQL,	S"	and quality_control_data.reason_code = REASON_MST.REASON_CODE(+)    "		 );
			   sSQL = String::Concat(sSQL,	S"group by    "													                 );
			   sSQL = String::Concat(sSQL,	S"	to_char(quality_control_data.manufact_ymd,'yyyy'),    "						 );
			   sSQL = String::Concat(sSQL,	S"	parts_mst.parts_name_", saLang[iLanguage] ,S",    "							 );
			   sSQL = String::Concat(sSQL,	S"	reason_mst.reason_", saLang[iLanguage] ,S"    "								 );
			   break;



		default:return;
	}
	
	oDataSetLossTable = oDB->dSetSQL_Select(sSQL, S"LossTable", 0);
	oDataTableLossTable = oDataSetLossTable->Tables->Item["LossTable"];
	dataGridLossTable->DataSource=oDataTableLossTable;
}

void TotalLossTable::SetValueComboBoxProcess()
{
	//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 ,"'"			);


			oDataSetLossTable = oDB->dSetSQL_Select(sSQL, S"COST_MST", 0);
			comboBoxProcess->DataSource = oDataSetLossTable->Tables->Item[S"COST_MST"];
			comboBoxProcess->ValueMember  = S"PROCESS_NO";
			comboBoxProcess->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 = 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 is not null and"				);
			sSQL = String::Concat(sSQL, S"	sap_store_no between 9001 and 9007 "				);
			sSQL = String::Concat(sSQL, S"order by"									);
			sSQL = String::Concat(sSQL, S"	PROCESS_NO"								);

			oDataSetLossTable = oDB->dSetSQL_Select(sSQL, S"COST_MST", 0);
			comboBoxProcess->DataSource = oDataSetLossTable->Tables->Item[S"COST_MST"];
			comboBoxProcess->ValueMember  = S"PROCESS_NO";
			comboBoxProcess->DisplayMember = saLangName[iLanguage];
		}


}
void TotalLossTable::InitGridDataGridLossTable(int selectedIndex)//0 group by day, 1 group by month ,2 semeter, 3 group by year 
{
	DataGridTextBoxColumn *oDate;
	DataGridTextBoxColumn *oDateCharType;
	DataGridTextBoxColumn *oPartCode;
	DataGridTextBoxColumn *oPartName;
	DataGridTextBoxColumn *oLot;
	DataGridTextBoxColumn *oQuantity;
	DataGridTextBoxColumn *oReason;
	DataGridTextBoxColumn *oDestination_code;
	
	oTableStyleLossTable				= new DataGridTableStyle();
	oTableStyleLossTable->MappingName	= S"LossTable";

   if(selectedIndex==0)
   {
	oDate								= new DataGridTextBoxColumn();
	oDate->MappingName					=S"YMD";
	oDate->HeaderText					= String::Concat(S"         ",oInf->saG_DateLetter[iLanguage]);
	oDate->Alignment					= HorizontalAlignment::Left;
	oDate->Width						=125;
	oDate->NullText						=S"";
   }
   else
   {
	oDateCharType						= new DataGridTextBoxColumn();
	oDateCharType->MappingName			=S"YMDchar";
	oDateCharType->HeaderText			= String::Concat(S"       ",oInf->saG_DateLetter[iLanguage]);
	oDateCharType->Alignment			= HorizontalAlignment::Left;
	oDateCharType->Width				=125;
	oDateCharType->NullText				=S"";

   }
  /* saG_Code[0]		= S"产品号"  ; saG_Code[1]	   = S"品番"    ; saG_Code[2]     = S"Code" ;*/
    oPartCode							= new DataGridTextBoxColumn();
	oPartCode->MappingName				=S"product_code";
	oPartCode->HeaderText				= String::Concat(S"      ",oInf-> saG_Code[iLanguage]);
	oPartCode->Alignment				= HorizontalAlignment::Left;
	oPartCode->Width                    =100;
	oPartCode->NullText		            =S"";

	oPartName							= new DataGridTextBoxColumn();
	oPartName->MappingName				=S"partName";
	oPartName->HeaderText				= String::Concat(S"                    ",oInf->saG_StrArrayPartsName[iLanguage]);
	oPartName->Alignment				= HorizontalAlignment::Left;
	oPartName->Width                    =200;
	oPartName->NullText		            =S"";

	oLot								= new DataGridTextBoxColumn();
	oLot->MappingName					=S"lot";
	oLot->HeaderText					= String::Concat(S"     ",oInf->saG_StrArrayLotNum[iLanguage]);
	oLot->Alignment						= HorizontalAlignment::Left;
	oLot->Width							=100;
	oLot->NullText		                =S"";

	oQuantity							= new DataGridTextBoxColumn();
	oQuantity->MappingName				=S"quantity";
	oQuantity->HeaderText				= String::Concat(oInf->saG_StrArrayDeliverQuantity[iLanguage],S"      \0");
	oQuantity->Alignment				= HorizontalAlignment::Right;
	oQuantity->Width                    =100;
	oQuantity->NullText		            =S"";

	oReason								= new DataGridTextBoxColumn();
	oReason->MappingName				=S"reason";
	oReason->HeaderText					= oInf->saG_StrArrayLossReason[iLanguage];
	oReason->Alignment					= HorizontalAlignment::Center;
	oReason->Width						=200;
	oReason->NullText		            =S"";

    oDestination_code					= new DataGridTextBoxColumn();
	/*oDestination_code->MappingName		=S"destination_code";*/
	oDestination_code->MappingName		=S"destinationName";
	oDestination_code->HeaderText		= String::Concat(S"       ",oInf->saG_StrArrayDuty[iLanguage]);
	oDestination_code->Alignment		= HorizontalAlignment::Left;
	oDestination_code->Width            =135;
	oDestination_code->NullText		    =S"";
	
	if(selectedIndex==0)
	{
		oTableStyleLossTable->GridColumnStyles->Add(oDate);
	}
	else
	{
		oTableStyleLossTable->GridColumnStyles->Add(oDateCharType);
	}

	oTableStyleLossTable->GridColumnStyles->Add(oPartCode);	
	oTableStyleLossTable->GridColumnStyles->Add(oPartName);
	if(selectedIndex==0)
   {
	oTableStyleLossTable->GridColumnStyles->Add(oLot);
   }
	oTableStyleLossTable->GridColumnStyles->Add(oQuantity);
	oTableStyleLossTable->GridColumnStyles->Add(oReason);
	oTableStyleLossTable->GridColumnStyles->Add(oDestination_code);
	if(dataGridLossTable->TableStyles->Count!=0)
	{
		dataGridLossTable->TableStyles->RemoveAt(0);
	}
	

		
	
	dataGridLossTable->TableStyles->Add(oTableStyleLossTable);




}

void TotalLossTable::InitComboBoxTime()
{
	comboBoxTime->SelectedIndex=0;
}