📄 balanceofpayment.cpp
字号:
sSQL = String::Concat(sSQL, S" rownum as EXCEL_ID," );
sSQL = String::Concat(sSQL, S" PROCESS_NO" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" CM1.PROCESS_NO" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" COST_MST CM1," );
sSQL = String::Concat(sSQL, S" COST_MST CM2," );
sSQL = String::Concat(sSQL, S" COST_COMPOSITION CC2" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" CM1.FACTORY_CODE = '", sFactory, S"' and" );
sSQL = String::Concat(sSQL, S" CM1.COST_DIVISION = 3 and" );
sSQL = String::Concat(sSQL, S" CM2.COST_DIVISION = 2 and" );
sSQL = String::Concat(sSQL, S" CM1.COST_NO = CM2.COST_NO(+) and" );
sSQL = String::Concat(sSQL, S" CM2.COST_NO = CC2.C_COST_NO(+) and" );
sSQL = String::Concat(sSQL, S" CM1.PROCESS_NO is not null" );
sSQL = String::Concat(sSQL, S" order by" );
sSQL = String::Concat(sSQL, S" CC2.P_COST_NO DESC, CM1.COST_NO, CM1.PROCESS_NO" );
sSQL = String::Concat(sSQL, S" )" );
sSQL = String::Concat(sSQL, S" ) EID " );
sSQL = String::Concat(sSQL, S"where" );
sSQL = String::Concat(sSQL, S" EID.PROCESS_NO = TOT.PROCESS_NO(+) " );
sSQL = String::Concat(sSQL, S"order by" );
sSQL = String::Concat(sSQL, S" EID.EXCEL_ID, TOT.PRODUCT_CODE" );
}
void BalanceOfPayment::OutputExcelSQL3(String* & sSQL)
{
String* sSYear;
String* sEYear;
String* sSMonth;
String* sEMonth;
String* saLang[] = {S"C", S"J", S"E"};
DateTime dtNow;
int iPT, iPe;
int iaSYe[4][12] = {{-1,-2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}};
int iaEYe[4][12] = {{ 0,-1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}};
int iaSMo[4][12] = {{12,12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{12, 6, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{12, 3, 6, 9, 1, 1, 1, 1, 1, 1, 1, 1},
{12, 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11}};
int iaEMo[4][12] = {{12,12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{ 6,12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
{ 3, 6, 9,12, 1, 1, 1, 1, 1, 1, 1, 1},
{ 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11,12}};
dtNow = DateTime::Now;
iPT = comboBoxPType->SelectedIndex;
iPe = comboBoxPeriod->SelectedIndex;
sSYear =String::Format("{0}", __box(dtNow.Year + iaSYe[iPT][iPe]));
sEYear =String::Format("{0}", __box(dtNow.Year + iaEYe[iPT][iPe]));
sSMonth =String::Format("{0}", __box(iaSMo[iPT][iPe]));
sEMonth =String::Format("{0}", __box(iaEMo[iPT][iPe]));
//***** begin **************************//
//* add by DongZhaoLiang in 2006/4/28 *//
//***** begin **************************//
/*if(sSMonth->Length==1)
{
sTemp=sSMonth;
sSMonth=S"0";
sSMonth = String::Concat(sSMonth,sTemp);
}
if(sEMonth->Length==1)
{
sTemp=sEMonth;
sEMonth=S"0";
sEMonth = String::Concat(sEMonth,sTemp);
}*/
//***** end ***************************//
//* add by DongZhaoLiang in 2006/4/28 *//
//***** end ***************************//
//Get Data
sSQL = S"";
sSQL = String::Concat(sSQL, S"select" );
sSQL = String::Concat(sSQL, S" TOT.PRODUCT_CODE," );
sSQL = String::Concat(sSQL, S" TOT.PARTS_NAME," );
sSQL = String::Concat(sSQL, S" nvl(sum(TOT.TOTAL_PRICE), 0) TOTAL_PRICE," );
sSQL = String::Concat(sSQL, S" nvl(sum(TOT.TOTAL_QTY), 0) TOTAL_QTY," );
sSQL = String::Concat(sSQL, S" nvl(sum(TOT.TOTAL_RMB), 0) TOTAL_RMB," );
sSQL = String::Concat(sSQL, S" nvl(sum(TOT.TOTAL_LOST), 0) TOTAL_LOST " );
sSQL = String::Concat(sSQL, S"from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" S_DAT.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" S_DAT.PRODUCT_CODE," );
sSQL = String::Concat(sSQL, S" S_DAT.PARTS_NAME," );
sSQL = String::Concat(sSQL, S" nvl(sum(S_DAT.TOTAL_PRICE), 0) TOTAL_PRICE," );
sSQL = String::Concat(sSQL, S" nvl(sum(S_DAT.TOTAL_QTY), 0) TOTAL_QTY," );
sSQL = String::Concat(sSQL, S" nvl(sum(S_DAT.TOTAL_RMB), 0) TOTAL_RMB," );
sSQL = String::Concat(sSQL, S" nvl(sum(S_DAT.TOTAL_LOST), 0) TOTAL_LOST " );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" PR.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" (to_char(PR.YMD, 'YYYYMMDD') || decode(PR.SHIFT, 'A', '1200', '0000')) as YMD," );
sSQL = String::Concat(sSQL, S" PR.PRODUCT_CODE," );
sSQL = String::Concat(sSQL, S" PM.PARTS_NAME_", saLang[iLanguage], S" PARTS_NAME," );
sSQL = String::Concat(sSQL, S" (PR.PRODUCT_QTY * SP.PRICE1_RMB) TOTAL_PRICE," );
sSQL = String::Concat(sSQL, S" PR.PRODUCT_QTY TOTAL_QTY," );
sSQL = String::Concat(sSQL, S" (PR.PRODUCT_QTY * SP.RMB) TOTAL_RMB," );
sSQL = String::Concat(sSQL, S" 0 TOTAL_LOST " );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" PRODUCT_RESULT_DATA PR," );
sSQL = String::Concat(sSQL, S" PARTS_MST PM," );
sSQL = String::Concat(sSQL, S" SALE_PRICE_MST SP" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" PR.FACTORY_CODE ='", sFactory, S"' and" );
sSQL = String::Concat(sSQL, S" PR.PRODUCT_CODE = PM.PRODUCT_CODE(+) and" );
sSQL = String::Concat(sSQL, S" PR.PRODUCT_CODE = SP.PRODUCT_CODE(+)" );
sSQL = String::Concat(sSQL, S" union" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" QC.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" to_char(QC.MANUFACT_YMD, 'YYYYMMDD') || decode(QC.SHIFT, 'A', '1200', '0000') YMD," );
sSQL = String::Concat(sSQL, S" QC.PRODUCT_CODE," );
sSQL = String::Concat(sSQL, S" PM.PARTS_NAME_J PARTS_NAME," );
sSQL = String::Concat(sSQL, S" 0 TOTAL_PRICE," );
sSQL = String::Concat(sSQL, S" 0 TOTAL_QTY," );
sSQL = String::Concat(sSQL, S" 0 TOTAL_RMB," );
sSQL = String::Concat(sSQL, S" QC.QUANTITY * SP.RMB TOTAL_LOST" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" QUALITY_CONTROL_DATA QC," );
sSQL = String::Concat(sSQL, S" PARTS_MST PM," );
sSQL = String::Concat(sSQL, S" SALE_PRICE_MST SP" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" QC.FACTORY_CODE ='", sFactory, S"' and" );
sSQL = String::Concat(sSQL, S" QC.PRODUCT_CODE = SP.PRODUCT_CODE(+) and" );
sSQL = String::Concat(sSQL, S" QC.PRODUCT_CODE = PM.PRODUCT_CODE(+) and" );
sSQL = String::Concat(sSQL, S" QC.REASON_DIV = 0" );
sSQL = String::Concat(sSQL, S" ) S_DAT," );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" CM.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" nvl(CD.START_YMD, '", sSYear, sSMonth->PadLeft(2, L'0'), S"990000') START_YMD," );
sSQL = String::Concat(sSQL, S" nvl(CD.END_YMD, '", sEYear, sEMonth->PadLeft(2, L'0'), S"990000') END_YMD" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" CDS.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" to_char(CDS.MONTH_", sSMonth, S", 'YYYYMMDD') ||" );
sSQL = String::Concat(sSQL, S" decode(CDS.SHIFT_", sSMonth, S", 'A', '1200', 'B', '0000') START_YMD," );
sSQL = String::Concat(sSQL, S" to_char(CDE.MONTH_", sEMonth, S", 'YYYYMMDD') ||" );
sSQL = String::Concat(sSQL, S" decode(CDE.SHIFT_", sEMonth, S", 'A', '1200', 'B', '0000') END_YMD" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" CUTOFF_DATE_MST CDS," );
sSQL = String::Concat(sSQL, S" CUTOFF_DATE_MST CDE" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" CDS.PROCESS_NO = CDE.PROCESS_NO and" );
sSQL = String::Concat(sSQL, S" CDS.YEAR = ", sSYear, S" and" );
sSQL = String::Concat(sSQL, S" CDE.YEAR = ", sEYear );
sSQL = String::Concat(sSQL, S" ) CD," );
sSQL = String::Concat(sSQL, S" COST_MST CM" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" CM.PROCESS_NO = CD.PROCESS_NO(+) and" );
sSQL = String::Concat(sSQL, S" CM.COST_DIVISION = 3" );
sSQL = String::Concat(sSQL, S" ) C_DAT" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" S_DAT.PROCESS_NO = C_DAT.PROCESS_NO and" );
sSQL = String::Concat(sSQL, S" S_DAT.YMD between C_DAT.START_YMD and C_DAT.END_YMD" );
sSQL = String::Concat(sSQL, S" group by" );
sSQL = String::Concat(sSQL, S" S_DAT.PROCESS_NO, S_DAT.PRODUCT_CODE, S_DAT.PARTS_NAME" );
sSQL = String::Concat(sSQL, S" )TOT " );
sSQL = String::Concat(sSQL, S"group by" );
sSQL = String::Concat(sSQL, S" TOT.PRODUCT_CODE, TOT.PARTS_NAME " );
sSQL = String::Concat(sSQL, S"order by" );
sSQL = String::Concat(sSQL, S" TOT.PRODUCT_CODE" );
}
void BalanceOfPayment::OutputExcelT(void)
{
String* sSQL;
String* sBuf = S"";
String* sPath;
String* sYear = comboBoxYear->Text;
DataRowCollection* oDataRC;
System::Object* oMissing;
Excel::ApplicationClass* pExcel;
Excel::Workbook* pWorkBook;
Excel::Worksheet* pActiveSheet;
DateTime dtNow;
int i;
int iRecordCount;
int iMonth;
int iaPos[12] = {5, 6, 7, 9,10,11,14,15,16,18,19,20};
//Get ExcelData
OutputExcelSQLT(sSQL);
oDataSet = oDB->dSetSQL_Select(sSQL, S"ExcelDataT", 0);
oDataTable = oDataSet->Tables->Item["ExcelDataT"];
oDataRC = oDataTable->Rows;
iRecordCount = oDataRC->Count;
if(iRecordCount <= 0) {
//Error Message
return;
}
try {
oMissing = System::Reflection::Missing::Value;
//Start Excel
pExcel = new Excel::ApplicationClass();
pExcel->Visible = true;
pWorkBook = pExcel->Workbooks->Add(oMissing);
//Copy
sPath = String::Concat(Application::StartupPath, S"\\収支表.xls");
pExcel->Workbooks->Open(sPath,
oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing);
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(1)])->Copy
(
pExcel->Workbooks->Item[__box(1)]->Sheets->Item[__box(1)], oMissing
);
static_cast<Excel::_Workbook*>(pExcel->Workbooks->Item[__box(1)])->Activate();
//Delete Sheets
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(4)])->Delete();
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(3)])->Delete();
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(2)])->Delete();
//Worksheet Rename
static_cast<Excel::Worksheet*>(pExcel->ActiveWorkbook->Sheets->Item[__box(1)])->Select(oMissing);
pActiveSheet = static_cast<Excel::Worksheet*>(pExcel->ActiveSheet);
pActiveSheet->Name = sYear;
//WorkSheet Init
dtNow = DateTime::Now;
sBuf = dtNow.ToString(L"yyyy/MM/dd");
// sBuf = dtNow.ToString(L"yyyy/MM/dd", DateTimeFormatInfo::InvariantInfo);
pActiveSheet->Cells->set_Item(__box(3) , __box(3) , sYear);
pActiveSheet->Cells->set_Item(__box(42), __box(21), sBuf);
//Edit
for(i = 0; i < iRecordCount; i++){
iMonth = Convert::ToInt32(oDa⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -