📄 balanceofpayment.cpp
字号:
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" LABOR_COST LC," );
sSQL = String::Concat(sSQL, S" LABOR_COST_RANK LR," );
sSQL = String::Concat(sSQL, S" WORK_CALENDER WC" );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" LC.RANK = LR.RANK(+) and" );
sSQL = String::Concat(sSQL, S" LC.PROCESS_NO = WC.PROCESS_NO(+) and" );
sSQL = String::Concat(sSQL, S" LC.WORK_YMD = WC.WORK_YMD(+)" );
sSQL = String::Concat(sSQL, S" ) TOT," );
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" ) COD " );
sSQL = String::Concat(sSQL, S" where" );
sSQL = String::Concat(sSQL, S" TOT.PROCESS_NO = COD.PROCESS_NO and" );
sSQL = String::Concat(sSQL, S" TOT.YMD between COD.START_YMD and COD.END_YMD" );
sSQL = String::Concat(sSQL, S" group by" );
sSQL = String::Concat(sSQL, S" TOT.PROCESS_NO, TOT.TEAM" );
sSQL = String::Concat(sSQL, S" ) TOT," );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" EID.EXCEL_ID," );
sSQL = String::Concat(sSQL, S" EID.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" TMT.TEAM" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select distinct" );
sSQL = String::Concat(sSQL, S" PROCESS_NO," );
sSQL = String::Concat(sSQL, S" TEAM" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" PROCESS_NO," );
sSQL = String::Concat(sSQL, S" TEAM" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" TEAM_MST TMT" );
sSQL = String::Concat(sSQL, S" union all" );
sSQL = String::Concat(sSQL, S" select" );
sSQL = String::Concat(sSQL, S" PROCESS_NO," );
sSQL = String::Concat(sSQL, S" TEAM" );
sSQL = String::Concat(sSQL, S" from" );
sSQL = String::Concat(sSQL, S" LABOR_COST" );
sSQL = String::Concat(sSQL, S" )" );
sSQL = String::Concat(sSQL, S" ) TMT," );
sSQL = String::Concat(sSQL, S" (" );
sSQL = String::Concat(sSQL, S" select" );
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" TMT.PROCESS_NO(+) = EID.PROCESS_NO" );
sSQL = String::Concat(sSQL, S" order by" );
sSQL = String::Concat(sSQL, S" EID.EXCEL_ID, TMT.TEAM" );
sSQL = String::Concat(sSQL, S" ) EID " );
sSQL = String::Concat(sSQL, S"where" );
sSQL = String::Concat(sSQL, S" EID.PROCESS_NO = TOT.PROCESS_NO(+) and" );
sSQL = String::Concat(sSQL, S" EID.TEAM = TOT.TEAM(+) " );
sSQL = String::Concat(sSQL, S"order by" );
sSQL = String::Concat(sSQL, S" EID.EXCEL_ID, EID.TEAM" );
}
void BalanceOfPayment::OutputExcelSQL2(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" EID.EXCEL_ID," );
sSQL = String::Concat(sSQL, S" EID.PROCESS_NO," );
sSQL = String::Concat(sSQL, S" TOT.PRODUCT_CODE," );
sSQL = String::Concat(sSQL, S" TOT.PARTS_NAME," );
sSQL = String::Concat(sSQL, S" TOT.TOTAL_PRICE," );
sSQL = String::Concat(sSQL, S" TOT.TOTAL_QTY," );
sSQL = String::Concat(sSQL, S" TOT.TOTAL_RMB," );
sSQL = String::Concat(sSQL, S" TOT.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" (" );
sSQL = String::Concat(sSQL, S" select" );
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -