/// <summary>
/// 4. 쿼리 생성
/// </summary>
/// <returns></returns>
private string MakeSqlString()
{
StringBuilder strSqlString = new StringBuilder();
string QueryCond1;
string QueryCond2;
string QueryCond3;
string start_date;
string date;
string month;
string year;
udcTableForm tableForm = (udcTableForm)btnSort.BindingForm;
QueryCond1 = tableForm.SelectedValueToQueryContainNull;
QueryCond2 = tableForm.SelectedValue2ToQueryContainNull;
QueryCond3 = tableForm.SelectedValue3ToQueryContainNull;
date = cdvDate.SelectedValue();
DateTime Select_date;
Select_date = DateTime.Parse(cdvDate.Text.ToString());
year = Select_date.ToString("yyyy");
month = Select_date.ToString("yyyyMM");
start_date = month + "01";
strSqlString.Append("SELECT " + QueryCond1 + "\n");
strSqlString.Append(" , SUM(MON_PLN.PLN_MON) AS PLN_MON" + "\n");
strSqlString.Append(" , SUM(MON_PLN.PLN_MON_REV) AS PLN_MON_REV" + "\n");
strSqlString.Append(" , SUM(SHP.SHP_MONTH) AS SHP_MONTH" + "\n");
strSqlString.Append(" , DECODE(SUM(NVL(MON_PLN.PLN_MON_REV,0)), 0, 0, ROUND((SUM(NVL(SHP.SHP_MONTH,0)) / SUM(NVL(MON_PLN.PLN_MON_REV,0)))*100, 1)) AS JINDO" + "\n");
strSqlString.Append(" , SUM(NVL(SHP.SHP_MONTH,0)) - ROUND(((SUM(NVL(MON_PLN.PLN_MON_REV,0)) * " + jindoPer + ") / 100),1) AS DEF" + "\n");
if (lblRemain.Text != "0 day") // 잔여일이 0 일이 아닐때
{
strSqlString.Append(" , ROUND(((SUM(NVL(MON_PLN.PLN_MON_REV,0)) - SUM(NVL(SHP.SHP_MONTH,0))) /" + Convert.ToInt32(lblRemain.Text.Substring(0, 2)) + "), 1) AS TARGET_DAY " + "\n");
}
else // 잔여일이 0 일 일대
{
strSqlString.Append(" , 0 AS TARGET_DAY " + "\n");
}
strSqlString.Append(" , SUM(SHP.SHP_DAY) AS SHP_DAY" + "\n");
strSqlString.Append(" , SUM(WEEK_PLN.WEEK_PLAN) AS WEEK_PLAN" + "\n");
strSqlString.Append(" , SUM(SHP.SHP_WEEK) AS SHP_WEEK " + "\n");
strSqlString.Append(" , SUM(NVL(WEEK_PLN.WEEK_PLAN,0)) - SUM(NVL(SHP.SHP_WEEK,0)) AS WEEK_DEF" + "\n");
strSqlString.Append(" , SUM(HMK3B) AS HMK3B, SUM(PACKING) AS PACKING, SUM(OGI) AS OGI, SUM(AVI) AS AVI, SUM(SORT) AS SORT, SUM(FINAL_INSP) AS FINAL_INSP" + "\n");
strSqlString.Append(" , SUM(BUMP_REFLOW) AS BUMP_REFLOW, SUM(BUMP_BALL_MOUNT) AS BUMP_BALL_MOUNT, SUM(BUMP_ETCH) AS BUMP_ETCH, SUM(BUMP_SNAG_PLAT) AS BUMP_SNAG_PLAT" + "\n");
strSqlString.Append(" , SUM(BUMP_CU_PLAT) AS BUMP_CU_PLAT, SUM(BUMP_PHOTO) AS BUMP_PHOTO, SUM(BUMP_SPUTTER) AS BUMP_SPUTTER" + "\n");
strSqlString.Append(" , SUM(PSV3_PHOTO) AS PSV3_PHOTO, SUM(RDL3_ETCH) AS RDL3_ETCH, SUM(RDL3_PLAT) AS RDL3_PLAT, SUM(RDL3_PHOTO) AS RDL3_PHOTO, SUM(RDL3_SPUTTER) AS RDL3_SPUTTER" + "\n");
strSqlString.Append(" , SUM(PSV2_PHOTO) AS PSV2_PHOTO, SUM(RDL2_ETCH) AS RDL2_ETCH, SUM(RDL2_PLAT) AS RDL2_PLAT, SUM(RDL2_PHOTO) AS RDL2_PHOTO, SUM(RDL2_SPUTTER) AS RDL2_SPUTTER" + "\n");
strSqlString.Append(" , SUM(PSV1_PHOTO) AS PSV1_PHOTO, SUM(RDL1_ETCH) AS RDL1_ETCH, SUM(RDL1_PLAT) AS RDL1_PLAT, SUM(RDL1_PHOTO) AS RDL1_PHOTO, SUM(RDL1_SPUTTER) AS RDL1_SPUTTER" + "\n");
strSqlString.Append(" , SUM(RCF_PHOTO) AS RCF_PHOTO, SUM(I_STOCK) AS I_STOCK, SUM(IQC) AS IQC, SUM(HMK2B) AS HMK2B, SUM(TTL_QTY) AS TTL_QTY" + "\n");
strSqlString.Append(" FROM CWIPMATINF@RPTTOMES MAT" + "\n");
strSqlString.Append(" , ( " + "\n");
strSqlString.Append(" SELECT MAT_ID" + "\n");
strSqlString.Append(" , SUM(TO_NUMBER(DECODE(RESV_FIELD7, ' ', 0, RESV_FIELD7))) AS PLN_MON " + "\n");
strSqlString.Append(" , SUM(TO_NUMBER(DECODE(RESV_FIELD8, ' ', 0, RESV_FIELD8))) AS PLN_MON_REV" + "\n");
strSqlString.Append(" FROM CWIPPLNMON " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "'" + "\n");
strSqlString.Append(" AND PLAN_MONTH = '" + month + "'" + "\n");
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) MON_PLN" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID, SUM(WW_QTY) AS WEEK_PLAN " + "\n");
strSqlString.Append(" FROM RWIPPLNWEK " + "\n");
strSqlString.Append(" WHERE FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND PLAN_WEEK = '" + FindWeek.ThisWeek + "'" + "\n");
strSqlString.Append(" AND GUBUN = '3' " + "\n");
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) WEEK_PLN" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID " + "\n");
strSqlString.Append(" , SUM(CASE WHEN WORK_DATE = '" + date + "' THEN NVL(SHP_QTY_1, 0) END) AS SHP_DAY" + "\n");
strSqlString.Append(" , SUM(CASE WHEN WORK_DATE BETWEEN '" + FindWeek.StartDay_ThisWeek + "' AND '" + date + "' THEN NVL(SHP_QTY_1, 0) END) AS SHP_WEEK" + "\n");
strSqlString.Append(" , SUM(CASE WHEN WORK_DATE BETWEEN '" + start_date + "' AND '" + date + "' THEN NVL(SHP_QTY_1, 0) END) AS SHP_MONTH" + "\n");
strSqlString.Append(" FROM VSUMWIPOUT " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND WORK_DATE BETWEEN LEAST('" + FindWeek.StartDay_ThisWeek + "', '" + start_date + "') AND '" + date + "'" + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND LOT_TYPE = 'W' " + "\n");
strSqlString.Append(" AND CM_KEY_2 = 'PROD' " + "\n");
if (cdvType.Text != "ALL")
{
strSqlString.Append(" AND CM_KEY_3 LIKE '" + cdvType.Text + "' " + "\n");
}
strSqlString.Append(" GROUP BY MAT_ID " + "\n");
strSqlString.Append(" ) SHP" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'HMK3B', QTY_1, 0)) AS HMK3B" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'PACKING', QTY_1, 0)) AS PACKING" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'OGI', QTY_1, 0)) AS OGI" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'AVI', QTY_1, 0)) AS AVI" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'SORT', QTY_1, 0)) AS SORT" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'FINAL_INSP', QTY_1, 0)) AS FINAL_INSP" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_REFLOW', QTY_1, 0)) AS BUMP_REFLOW" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_BALL_MOUNT', QTY_1, 0)) AS BUMP_BALL_MOUNT" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_ETCH', QTY_1, 0)) AS BUMP_ETCH" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_SNAG_PLAT', QTY_1, 0)) AS BUMP_SNAG_PLAT" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_CU_PLAT', QTY_1, 0)) AS BUMP_CU_PLAT" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_PHOTO', QTY_1, 0)) AS BUMP_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'BUMP_SPUTTER', QTY_1, 0)) AS BUMP_SPUTTER" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'PSV3_PHOTO', QTY_1, 0)) AS PSV3_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL3_ETCH', QTY_1, 0)) AS RDL3_ETCH" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL3_PLAT', QTY_1, 0)) AS RDL3_PLAT " + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL3_PHOTO', QTY_1, 0)) AS RDL3_PHOTO " + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL3_SPUTTER', QTY_1, 0)) AS RDL3_SPUTTER" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'PSV2_PHOTO', QTY_1, 0)) AS PSV2_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL2_ETCH', QTY_1, 0)) AS RDL2_ETCH" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL2_PLAT', QTY_1, 0)) AS RDL2_PLAT" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL2_PHOTO', QTY_1, 0)) AS RDL2_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL2_SPUTTER', QTY_1, 0)) AS RDL2_SPUTTER" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'PSV1_PHOTO', QTY_1, 0)) AS PSV1_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL1_ETCH', QTY_1, 0)) AS RDL1_ETCH" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL1_PLAT', QTY_1, 0)) AS RDL1_PLAT" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL1_PHOTO', QTY_1, 0)) AS RDL1_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RDL1_SPUTTER', QTY_1, 0)) AS RDL1_SPUTTER" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'RCF_PHOTO', QTY_1, 0)) AS RCF_PHOTO" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'I-STOCK', QTY_1, 0)) AS I_STOCK" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'IQC', QTY_1, 0)) AS IQC" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER_GRP_1, 'HMK2B', QTY_1, 0)) AS HMK2B" + "\n");
strSqlString.Append(" , SUM(QTY_1) AS TTL_QTY" + "\n");
strSqlString.Append(" FROM ( " + "\n");
if (rdbWIP.Checked == true)
{
#region 1.재공기준
strSqlString.Append(" SELECT A.MAT_ID, B.OPER, B.OPER_GRP_1, A.LOT_CMF_5 AS LOT_TYPE, A.QTY_1 " + "\n");
if (DateTime.Now.ToString("yyyyMMdd") != cdvDate.SelectedValue())
{
strSqlString.Append(" FROM RWIPLOTSTS_BOH A" + "\n");
strSqlString.Append(" , MWIPOPRDEF B" + "\n");
strSqlString.Append(" WHERE A.CUTOFF_DT = '" + cdvDate.SelectedValue() + "22' " + "\n");
}
else if (DateTime.Now.ToString("yyyyMMdd") == cdvDate.SelectedValue())
{
strSqlString.Append(" FROM RWIPLOTSTS A" + "\n");
strSqlString.Append(" , MWIPOPRDEF B" + "\n");
strSqlString.Append(" WHERE 1 = 1 " + "\n");
}
strSqlString.Append(" AND A.FACTORY = B.FACTORY" + "\n");
strSqlString.Append(" AND A.OPER = B.OPER" + "\n");
strSqlString.Append(" AND A.FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND A.LOT_TYPE = 'W' " + "\n");
strSqlString.Append(" AND A.LOT_DEL_FLAG = ' '" + "\n");
#endregion
}
else
{
#region 2.실적기준
strSqlString.Append(" SELECT A.MAT_ID, A.OPER, B.OPER_GRP_1, A.CM_KEY_3 AS LOT_TYPE, (A.S1_END_QTY_1+A.S2_END_QTY_1+A.S3_END_QTY_1+A.S4_END_QTY_1) AS QTY_1 " + "\n");
strSqlString.Append(" FROM RSUMWIPMOV A" + "\n");
strSqlString.Append(" , MWIPOPRDEF B" + "\n");
strSqlString.Append(" WHERE 1 = 1 " + "\n");
strSqlString.Append(" AND A.FACTORY = B.FACTORY" + "\n");
strSqlString.Append(" AND A.OPER = B.OPER" + "\n");
strSqlString.Append(" AND A.WORK_DATE = '" + date + "' " + "\n");
strSqlString.Append(" AND A.FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND A.LOT_TYPE = 'W' " + "\n");
#endregion
}
strSqlString.Append(" ) " + "\n");
if (cdvType.Text != "ALL")
{
strSqlString.Append(" WHERE LOT_TYPE LIKE '" + cdvType.Text + "' " + "\n");
}
strSqlString.Append(" GROUP BY MAT_ID " + "\n");
strSqlString.Append(" ) WIP" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = MON_PLN.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = WEEK_PLN.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = SHP.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = WIP.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.FACTORY = '" + cdvFactory.Text + "'" + "\n");
strSqlString.Append(" AND MAT.DELETE_FLAG = ' '" + "\n");
strSqlString.Append(" AND MAT.MAT_TYPE = 'FG'" + "\n");
strSqlString.Append(" AND NVL(MON_PLN.PLN_MON,0) + NVL(MON_PLN.PLN_MON_REV,0) + NVL(SHP.SHP_MONTH,0) + NVL(WEEK_PLN.WEEK_PLAN,0) + NVL(SHP.SHP_WEEK,0) + NVL(WIP.TTL_QTY,0) > 0" + "\n");
// Product
if (txtProduct.Text.Trim() != "%" && txtProduct.Text.Trim() != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_ID LIKE '{0}'" + "\n", txtProduct.Text);
}
#region 상세 조회에 따른 SQL문 생성
if (udcBUMPCondition1.Text != "ALL" && udcBUMPCondition1.Text != "")
{
strSqlString.AppendFormat(" AND MAT.CUSTOMER_ID {0} " + "\n", udcBUMPCondition1.SelectedValueToQueryString);
}
if (udcBUMPCondition2.Text != "ALL" && udcBUMPCondition2.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_01 {0} " + "\n", udcBUMPCondition2.SelectedValueToQueryString);
}
if (udcBUMPCondition3.Text != "ALL" && udcBUMPCondition3.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_02 {0} " + "\n", udcBUMPCondition3.SelectedValueToQueryString);
}
if (udcBUMPCondition4.Text != "ALL" && udcBUMPCondition4.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_03 {0} " + "\n", udcBUMPCondition4.SelectedValueToQueryString);
}
if (udcBUMPCondition5.Text != "ALL" && udcBUMPCondition5.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_04 {0} " + "\n", udcBUMPCondition5.SelectedValueToQueryString);
}
if (udcBUMPCondition6.Text != "ALL" && udcBUMPCondition6.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_05 {0} " + "\n", udcBUMPCondition6.SelectedValueToQueryString);
}
if (udcBUMPCondition7.Text != "ALL" && udcBUMPCondition7.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_06 {0} " + "\n", udcBUMPCondition7.SelectedValueToQueryString);
}
if (udcBUMPCondition8.Text != "ALL" && udcBUMPCondition8.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_GRP_07 {0} " + "\n", udcBUMPCondition8.SelectedValueToQueryString);
}
if (udcBUMPCondition9.Text != "ALL" && udcBUMPCondition9.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_CMF_01 {0} " + "\n", udcBUMPCondition9.SelectedValueToQueryString);
}
if (udcBUMPCondition10.Text != "ALL" && udcBUMPCondition10.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_CMF_02 {0} " + "\n", udcBUMPCondition10.SelectedValueToQueryString);
}
if (udcBUMPCondition11.Text != "ALL" && udcBUMPCondition11.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_CMF_03 {0} " + "\n", udcBUMPCondition11.SelectedValueToQueryString);
}
if (udcBUMPCondition12.Text != "ALL" && udcBUMPCondition12.Text != "")
{
strSqlString.AppendFormat(" AND MAT.MAT_CMF_04 {0} " + "\n", udcBUMPCondition12.SelectedValueToQueryString);
}
#endregion
strSqlString.Append(" GROUP BY " + QueryCond2 + "\n");
strSqlString.Append(" ORDER BY " + QueryCond2 + "\n");
if (GlobalVariable.gsUserGroup == "ADMIN_GROUP" || GlobalVariable.gsUserGroup == "HANA_ADMIN_GROUP")
{
System.Windows.Forms.Clipboard.SetText(strSqlString.ToString());
}
return(strSqlString.ToString());
}
/// <summary>
/// 4. 쿼리 생성
/// </summary>
/// <returns></returns>
private string MakeSqlString()
{
StringBuilder strSqlString = new StringBuilder();
string QueryCond1;
string QueryCond2;
string start_date;
string yesterday;
string date;
string month;
string year;
string lastMonth;
udcTableForm tableForm = (udcTableForm)btnSort.BindingForm;
QueryCond1 = tableForm.SelectedValueToQueryContainNull;
QueryCond2 = tableForm.SelectedValue2ToQueryContainNull;
//int remain = Convert.ToInt32(lblLastDay.Text.Substring(0,2)) - Convert.ToInt32(lblToday.Text.Substring(0,2)) + 1;
date = cdvDate.SelectedValue();
DateTime Select_date;
Select_date = DateTime.Parse(cdvDate.Text.ToString());
year = Select_date.ToString("yyyy");
month = Select_date.ToString("yyyyMM");
start_date = month + "01";
yesterday = Select_date.AddDays(-1).ToString("yyyyMMdd");
lastMonth = Select_date.AddMonths(-1).ToString("yyyyMM"); // 지난달
// 지난달 마지막일 구하기
DataTable dt1 = null;
string Last_Month_Last_day = "(SELECT TO_CHAR(LAST_DAY(TO_DATE('" + lastMonth + "', 'YYYYMM')),'YYYYMMDD') FROM DUAL)";
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", Last_Month_Last_day);
Last_Month_Last_day = dt1.Rows[0][0].ToString();
// 달의 마지막일 구하기
DataTable dt2 = null;
string last_day = "(SELECT TO_CHAR(LAST_DAY(TO_DATE('" + month + "', 'YYYYMM')),'YYYYMMDD') FROM DUAL)";
dt2 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", last_day);
last_day = dt2.Rows[0][0].ToString();
// 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year, Select_date.ToString("yyyyMMdd")));
string Lastweek_lastday = dt1.Rows[0][0].ToString();
// -1일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year1, strDate1));
string Lastweek_lastday1 = dt1.Rows[0][0].ToString();
// -2일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year2, strDate2));
string Lastweek_lastday2 = dt1.Rows[0][0].ToString();
// -3일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year3, strDate3));
string Lastweek_lastday3 = dt1.Rows[0][0].ToString();
// -4일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year4, strDate4));
string Lastweek_lastday4 = dt1.Rows[0][0].ToString();
// -5일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year5, strDate5));
string Lastweek_lastday5 = dt1.Rows[0][0].ToString();
// -6일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year6, strDate6));
string Lastweek_lastday6 = dt1.Rows[0][0].ToString();
// -7일 지난주차의 마지막일 가져오기
dt1 = null;
dt1 = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", MakeSqlString2(year7, strDate7));
string Lastweek_lastday7 = dt1.Rows[0][0].ToString();
strSqlString.Append("SELECT A.CUSTOMER, TOTAL.JINDO, A.JINDO, B.JINDO, C.JINDO, D.JINDO, E.JINDO, F.JINDO, G.JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + cdvDate.SelectedValue() + "'" + "\n");
if (udcWIPCondition1.Text != "ALL" && udcWIPCondition1.Text != "")
{
strSqlString.AppendFormat(" AND CUSTOMER {0} " + "\n", udcWIPCondition1.SelectedValueToQueryString);
}
else
{
strSqlString.Append(" AND CUSTOMER IN ('SE','HX') " + "\n");
}
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) TOTAL , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer1 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate1 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) A , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer2 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate2 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) B , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer3 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate3 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) C , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer4 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate4 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) D , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer5 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate5 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) E , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer6 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate6 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) F , " + "\n");
strSqlString.Append(" ( SELECT CUSTOMER, ROUND((SUM(ASSY_MON)/SUM(TARGET_MON))* 100, 2) JINDO" + "\n");
strSqlString.Append(" FROM( " + "\n");
strSqlString.Append(" SELECT CUSTOMER, (SUM(PLAN_QTY) * " + jindoPer7 + " ) / 100 AS TARGET_MON ,SUM(ASSY_QTY) AS ASSY_MON" + "\n");
strSqlString.Append(" FROM RSUMTRNGOL " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + cdvFactory.Text + "' " + "\n");
strSqlString.Append(" AND WORK_DATE = '" + strDate7 + "'" + "\n");
strSqlString.Append(" GROUP BY CUSTOMER ) " + "\n");
strSqlString.Append(" WHERE TARGET_MON <> 0 " + "\n");
strSqlString.Append(" GROUP BY CUSTOMER " + "\n");
strSqlString.Append(" ) G " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND A.CUSTOMER = TOTAL.CUSTOMER " + "\n");
strSqlString.Append(" AND A.CUSTOMER = B.CUSTOMER " + "\n");
strSqlString.Append(" AND A.CUSTOMER = C.CUSTOMER " + "\n");
strSqlString.Append(" AND A.CUSTOMER = D.CUSTOMER " + "\n");
strSqlString.Append(" AND A.CUSTOMER = E.CUSTOMER " + "\n");
strSqlString.Append(" AND A.CUSTOMER = F.CUSTOMER " + "\n");
strSqlString.Append(" AND A.CUSTOMER = G.CUSTOMER " + "\n");
if (GlobalVariable.gsUserGroup == "ADMIN_GROUP" || GlobalVariable.gsUserGroup == "HANA_ADMIN_GROUP")
{
System.Windows.Forms.Clipboard.SetText(strSqlString.ToString());
}
return(strSqlString.ToString());
}
/// <summary>
/// 4. 쿼리 생성
/// </summary>
/// <returns></returns>
private string MakeSqlString()
{
StringBuilder strSqlString = new StringBuilder();
string QueryCond1;
string QueryCond2;
string QueryCond3;
string start_date;
string date;
string month;
string year;
string sKcpkValue;
udcTableForm tableForm = (udcTableForm)btnSort.BindingForm;
QueryCond1 = tableForm.SelectedValueToQueryContainNull;
QueryCond2 = tableForm.SelectedValue2ToQueryContainNull;
QueryCond3 = tableForm.SelectedValue3ToQueryContainNull;
date = cdvDate.SelectedValue();
DateTime Select_date;
Select_date = DateTime.Parse(cdvDate.Text.ToString());
year = Select_date.ToString("yyyy");
month = Select_date.ToString("yyyyMM");
start_date = month + "01";
if (ckbKpcs.Checked == true)
{
sKcpkValue = "1000";
}
else
{
sKcpkValue = "1";
}
strSqlString.AppendFormat("SELECT {0} " + "\n", QueryCond1);
strSqlString.Append(" , ROUND(SUM(MON_PLN) / " + sKcpkValue + ", 0) AS MON_PLN" + "\n");
strSqlString.Append(" , ROUND(SUM(SHP_TTL) / " + sKcpkValue + ", 0) AS SHP_TTL" + "\n");
strSqlString.Append(" , CASE WHEN SUM(MON_PLN) = 0 THEN 0" + "\n");
strSqlString.Append(" ELSE ROUND((SUM(SHP_TTL) / (SUM(MON_PLN) / " + iLastday + ")) - " + iToday + ", 1)" + "\n");
strSqlString.Append(" END AS SHP_JINDO" + "\n");
strSqlString.Append(" , ROUND((SUM(ONLY_SHP) + SUM(WIP_TTL)) / " + sKcpkValue + ", 0) AS RCV_TTL" + "\n");
strSqlString.Append(" , CASE WHEN SUM(MON_PLN) = 0 THEN 0" + "\n");
strSqlString.Append(" ELSE ROUND(( ((SUM(SHP_TTL) + SUM(WIP_TTL)) / SUM(MON_PLN)) - ((" + iToday + " + 2) / " + iLastday + ") ) * " + iLastday + ", 1)" + "\n");
strSqlString.Append(" END AS RCV_JINDO" + "\n");
strSqlString.Append(" , ROUND(SUM(WIP_TTL) / " + sKcpkValue + ", 0) AS WIP_TTL" + "\n");
strSqlString.Append(" , CASE WHEN SUM(MON_PLN) = 0 THEN 0" + "\n");
strSqlString.Append(" ELSE ROUND(SUM(WIP_TTL) / (SUM(MON_PLN) / " + iLastday + "), 1)" + "\n");
strSqlString.Append(" END AS WIP_DAY" + "\n");
if (iRemain != 0) // 잔여일이 0 일이 아닐때
{
strSqlString.Append(" , ROUND((SUM(MON_PLN) - SUM(SHP_TTL)) / " + iRemain + " / " + sKcpkValue + ", 0) AS TARGET_QTY" + "\n");
}
else // 잔여일이 0 일 일대
{
strSqlString.Append(" , 0 AS TARGET_QTY " + "\n");
}
strSqlString.Append(" , ROUND(SUM(RCV_TODAY) / " + sKcpkValue + ", 0) AS RCV_TODAY" + "\n");
strSqlString.Append(" , ROUND(SUM(T_OUT_TODAY) / " + sKcpkValue + ", 0) AS T_OUT_TODAY" + "\n");
strSqlString.Append(" , ROUND(SUM(SHP_TODAY) / " + sKcpkValue + ", 0) AS SHP_TODAY" + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT MAT.MAT_ID, MAT_GRP_1 AS CUSTOMER" + "\n");
strSqlString.Append(" , MAT_GRP_1, MAT_GRP_2, MAT_GRP_3, MAT_GRP_4, MAT_GRP_5" + "\n");
strSqlString.Append(" , MAT_GRP_6, MAT_GRP_7, MAT_GRP_8, MAT_GRP_9, MAT_GRP_10, MAT_CMF_10, MAT_CMF_11" + "\n");
strSqlString.Append(" , NVL(PLN.MON_PLN, 0) * PRICE AS MON_PLN" + "\n");
//strSqlString.Append(" , NVL(SHP.SHP_TTL, 0) * PRICE AS SHP_TTL" + "\n");
strSqlString.Append(" , (NVL(SHP.SHP_TTL, 0) + DECODE(SUBSTR('" + date + "', -2), '01', 0, NVL(WIP.WIP_TZ010, 0))) * PRICE AS SHP_TTL" + "\n");
strSqlString.Append(" , (NVL(SHP.SHP_TTL, 0)) * PRICE AS ONLY_SHP" + "\n"); //2020-09-22-이희석 : 생산운영그룹(이승희 대리) 요청으로 수정
strSqlString.Append(" , NVL(WIP.WIP_TTL,0) * PRICE AS WIP_TTL" + "\n");
strSqlString.Append(" , NVL(RCV_TODAY,0) * PRICE AS RCV_TODAY" + "\n");
strSqlString.Append(" , NVL(T_OUT_TODAY, 0) * PRICE AS T_OUT_TODAY" + "\n");
strSqlString.Append(" , NVL(SHP.SHP_TODAY, 0) * PRICE AS SHP_TODAY" + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT A.MAT_ID, MAT_GRP_1, MAT_GRP_2, MAT_GRP_3, MAT_GRP_4, MAT_GRP_5" + "\n");
strSqlString.Append(" , MAT_GRP_6, MAT_GRP_7, MAT_GRP_8, MAT_GRP_9, MAT_GRP_10, MAT_CMF_10, MAT_CMF_11" + "\n");
if (ckbConv.Checked == true)
{
strSqlString.Append(" , CASE WHEN MAT_GRP_4 IN ('-', 'FD', 'FU') THEN '1'" + "\n");
strSqlString.Append(" WHEN MAT_GRP_4 IN ('DDP') THEN '2'" + "\n");
strSqlString.Append(" WHEN MAT_GRP_4 IN ('QDP') THEN '4'" + "\n");
strSqlString.Append(" WHEN MAT_GRP_4 IN ('ODP') THEN '8'" + "\n");
strSqlString.Append(" ELSE REGEXP_REPLACE(MAT_GRP_4, '[^[:digit:]]')" + "\n");
strSqlString.Append(" END AS PRICE" + "\n");
//strSqlString.Append(" , CASE WHEN MAT_GRP_4 IN ('-', 'FD', 'FU') THEN '1' ELSE SUBSTR(MAT_GRP_4, 3) END AS PRICE " + "\n");
}
else
{
strSqlString.Append(" , 1 AS PRICE" + "\n");
}
strSqlString.Append(" FROM VWIPMATDEF A" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND A.FACTORY = '" + GlobalVariable.gsTestDefaultFactory + "'" + "\n");
strSqlString.Append(" AND A.DELETE_FLAG = ' '" + "\n");
strSqlString.Append(" AND A.MAT_TYPE = 'FG' " + "\n");
strSqlString.Append(" AND A.MAT_GRP_2 NOT IN ('-') " + "\n");
strSqlString.Append(" AND A.MAT_GRP_3 NOT IN ('COB') " + "\n");
if (txtProduct.Text.Trim() != "%" && txtProduct.Text.Trim() != "")
{
strSqlString.Append(" AND A.MAT_ID LIKE '" + txtProduct.Text + "'" + "\n");
}
#region 상세 조회에 따른 SQL문 생성
if (udcWIPCondition1.Text != "ALL" && udcWIPCondition1.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_1 {0}" + "\n", udcWIPCondition1.SelectedValueToQueryString);
}
if (udcWIPCondition2.Text != "ALL" && udcWIPCondition2.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_2 {0} " + "\n", udcWIPCondition2.SelectedValueToQueryString);
}
if (udcWIPCondition3.Text != "ALL" && udcWIPCondition3.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_3 {0} " + "\n", udcWIPCondition3.SelectedValueToQueryString);
}
if (udcWIPCondition4.Text != "ALL" && udcWIPCondition4.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_4 {0} " + "\n", udcWIPCondition4.SelectedValueToQueryString);
}
if (udcWIPCondition5.Text != "ALL" && udcWIPCondition5.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_5 {0} " + "\n", udcWIPCondition5.SelectedValueToQueryString);
}
if (udcWIPCondition6.Text != "ALL" && udcWIPCondition6.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_6 {0} " + "\n", udcWIPCondition6.SelectedValueToQueryString);
}
if (udcWIPCondition7.Text != "ALL" && udcWIPCondition7.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_7 {0} " + "\n", udcWIPCondition7.SelectedValueToQueryString);
}
if (udcWIPCondition8.Text != "ALL" && udcWIPCondition8.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_8 {0} " + "\n", udcWIPCondition8.SelectedValueToQueryString);
}
if (udcWIPCondition9.Text != "ALL" && udcWIPCondition9.Text != "")
{
strSqlString.AppendFormat(" AND A.MAT_GRP_9 {0} " + "\n", udcWIPCondition9.SelectedValueToQueryString);
}
#endregion
strSqlString.Append(" ) MAT" + "\n");
strSqlString.Append(" , ( " + "\n");
strSqlString.Append(" SELECT MAT_ID" + "\n");
if (ckbRevPlan.Checked == true)
{
strSqlString.Append(" , SUM(RESV_FIELD2) AS MON_PLN" + "\n");
}
else
{
strSqlString.Append(" , SUM(PLAN_QTY_TEST) AS MON_PLN" + "\n");
}
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT MAT_ID, SUM(PLAN_QTY_TEST) AS PLAN_QTY_TEST, SUM(TO_NUMBER(DECODE(RESV_FIELD2,' ',0,RESV_FIELD2))) AS RESV_FIELD2" + "\n");
strSqlString.Append(" FROM CWIPPLNMON " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND FACTORY = '" + GlobalVariable.gsTestDefaultFactory + "'" + "\n");
strSqlString.Append(" AND PLAN_MONTH = '" + month + "'" + "\n");
strSqlString.Append(" GROUP BY MAT_ID " + "\n");
strSqlString.Append(" ) " + "\n");
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) PLN" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID" + "\n");
strSqlString.Append(" , SUM(SHP_QTY_1) AS SHP_TTL " + "\n");
strSqlString.Append(" , SUM(DECODE(WORK_DATE, '" + date + "', SHP_QTY_1, 0)) AS SHP_TODAY" + "\n");
strSqlString.Append(" FROM VSUMWIPOUT" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND WORK_DATE BETWEEN '" + start_date + "' AND '" + date + "' " + "\n");
strSqlString.Append(" AND FACTORY IN ('" + GlobalVariable.gsTestDefaultFactory + "') " + "\n");
strSqlString.Append(" AND LOT_TYPE = 'W' " + "\n");
strSqlString.Append(" AND CM_KEY_2 = 'PROD' " + "\n");
if (cdvLotType.Text != "ALL")
{
strSqlString.Append(" AND CM_KEY_3 LIKE '" + cdvLotType.Text + "'" + "\n");
}
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) SHP" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID" + "\n");
strSqlString.Append(" , SUM(QTY) AS WIP_TTL" + "\n");
strSqlString.Append(" , SUM(DECODE(OPER, 'TZ010', QTY, 0)) AS WIP_TZ010" + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT MAT_ID, QTY_1 AS QTY, OPER" + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT A.MAT_ID, A.OPER, B.OPER_GRP_1, C.MAT_GRP_1, C.MAT_GRP_4, C.MAT_GRP_5" + "\n");
strSqlString.Append(" , QTY_1" + "\n");
strSqlString.Append(" , A.HOLD_CODE " + "\n");
strSqlString.Append(" , CASE WHEN A.HOLD_CODE = 'H72' AND A.OPER = 'T0000' THEN 'Y' ELSE 'N' END AS CK_STOCK_H72 " + "\n");
if (DateTime.Now.ToString("yyyyMMdd") == cdvDate.SelectedValue())
{
strSqlString.Append(" FROM RWIPLOTSTS A " + "\n");
strSqlString.Append(" , MWIPOPRDEF B " + "\n");
strSqlString.Append(" , VWIPMATDEF C" + "\n");
strSqlString.Append(" WHERE 1 = 1 " + "\n");
}
else
{
strSqlString.Append(" FROM RWIPLOTSTS_BOH A " + "\n");
strSqlString.Append(" , MWIPOPRDEF B " + "\n");
strSqlString.Append(" , VWIPMATDEF C " + "\n");
strSqlString.Append(" WHERE A.CUTOFF_DT = '" + cdvDate.SelectedValue() + "22' " + "\n");
}
strSqlString.Append(" AND A.FACTORY = B.FACTORY" + "\n");
strSqlString.Append(" AND A.FACTORY = C.FACTORY" + "\n");
strSqlString.Append(" AND A.OPER = B.OPER" + "\n");
strSqlString.Append(" AND A.MAT_ID = C.MAT_ID" + "\n");
strSqlString.Append(" AND A.FACTORY = '" + GlobalVariable.gsTestDefaultFactory + "' " + "\n");
strSqlString.Append(" AND A.LOT_TYPE = 'W' " + "\n");
if (cdvLotType.Text != "ALL")
{
strSqlString.Append(" AND A.LOT_CMF_5 LIKE '" + cdvLotType.Text + "' " + "\n");
}
strSqlString.Append(" AND A.LOT_DEL_FLAG = ' ' " + "\n");
strSqlString.Append(" AND C.MAT_GRP_2 <> '-'" + "\n");
strSqlString.Append(" AND C.DELETE_FLAG = ' '" + "\n");
strSqlString.Append(" AND A.HOLD_CODE NOT IN ('H71','H54') " + "\n");
strSqlString.Append(" )" + "\n");
strSqlString.Append(" WHERE CK_STOCK_H72 = 'N'" + "\n");
strSqlString.Append(" )" + "\n");
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) WIP" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID" + "\n");
strSqlString.Append(" , SUM(CASE WHEN OPER = 'T0000' THEN S1_OPER_IN_QTY_1 + S2_OPER_IN_QTY_1 + S3_OPER_IN_QTY_1 ELSE 0 END) RCV_TODAY" + "\n");
strSqlString.Append(" , SUM(CASE WHEN T0100_CNT + T0960_CNT > 1 THEN (CASE WHEN OPER = 'T0960' THEN S1_END_QTY_1 + S2_END_QTY_1 + S3_END_QTY_1 + S1_END_RWK_QTY_1 + S2_END_RWK_QTY_1 + S3_END_RWK_QTY_1 ELSE 0 END)" + "\n");
strSqlString.Append(" WHEN T0500_CNT + T0960_CNT > 1 THEN (CASE WHEN OPER = 'T0960' THEN S1_END_QTY_1 + S2_END_QTY_1 + S3_END_QTY_1 + S1_END_RWK_QTY_1 + S2_END_RWK_QTY_1 + S3_END_RWK_QTY_1 ELSE 0 END)" + "\n");
strSqlString.Append(" WHEN OPER IN ('T0100', 'T0400', 'T0500', 'T0960', 'S0960') THEN S1_END_QTY_1 + S2_END_QTY_1 + S3_END_QTY_1 + S1_END_RWK_QTY_1 + S2_END_RWK_QTY_1 + S3_END_RWK_QTY_1" + "\n");
strSqlString.Append(" ELSE 0" + "\n");
strSqlString.Append(" END) T_OUT_TODAY" + "\n");
strSqlString.Append(" FROM ( " + "\n");
strSqlString.Append(" SELECT (SELECT COUNT(*) FROM MWIPFLWOPR@RPTTOMES FLW WHERE 1=1 AND FLW.OPER = 'T0100' AND FLW.FLOW = A.FLOW) AS T0100_CNT" + "\n");
strSqlString.Append(" , (SELECT COUNT(*) FROM MWIPFLWOPR@RPTTOMES FLW WHERE 1=1 AND FLW.OPER = 'T0500' AND FLW.FLOW = A.FLOW) AS T0500_CNT" + "\n");
strSqlString.Append(" , (SELECT COUNT(*) FROM MWIPFLWOPR@RPTTOMES FLW WHERE 1=1 AND FLW.OPER = 'T0960' AND FLW.FLOW = A.FLOW) AS T0960_CNT" + "\n");
strSqlString.Append(" , A.OPER AS OPER" + "\n");
strSqlString.Append(" , A.S1_OPER_IN_QTY_1, A.S2_OPER_IN_QTY_1, A.S3_OPER_IN_QTY_1" + "\n");
strSqlString.Append(" , A.S1_END_QTY_1, A.S2_END_QTY_1, A.S3_END_QTY_1" + "\n");
strSqlString.Append(" , A.S1_END_RWK_QTY_1, A.S2_END_RWK_QTY_1, A.S3_END_RWK_QTY_1" + "\n");
strSqlString.Append(" , B.*" + "\n");
strSqlString.Append(" FROM RSUMWIPMOV A" + "\n");
strSqlString.Append(" , VWIPMATDEF B" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND A.FACTORY = B.FACTORY" + "\n");
strSqlString.Append(" AND A.MAT_ID = B.MAT_ID" + "\n");
strSqlString.Append(" AND A.FACTORY = '" + GlobalVariable.gsTestDefaultFactory + "'" + "\n");
strSqlString.Append(" AND A.WORK_DATE = '" + date + "'" + "\n");
strSqlString.Append(" AND A.LOT_TYPE = 'W'" + "\n");
if (cdvLotType.Text != "ALL")
{
strSqlString.Append(" AND A.CM_KEY_3 LIKE '" + cdvLotType.Text + "' " + "\n");
}
strSqlString.Append(" AND REGEXP_LIKE(A.OPER, 'T0000|T0100|T0400|T0500|T0960|S0960')" + "\n");
strSqlString.Append(" )" + "\n");
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) MOV" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = PLN.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = SHP.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = WIP.MAT_ID(+)" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = MOV.MAT_ID(+)" + "\n");
strSqlString.Append(" ) A" + "\n");
strSqlString.Append(" GROUP BY " + QueryCond2 + "\n");
strSqlString.Append(" HAVING SUM(MON_PLN) + SUM(SHP_TTL) + SUM(WIP_TTL) + SUM(RCV_TODAY) + SUM(T_OUT_TODAY) + SUM(SHP_TODAY) <> 0" + "\n");
strSqlString.Append(" ORDER BY DECODE(MAT_GRP_1, 'SE', 1, 'IM', 2, 'FC', 3, 'AB', 4, 5), " + QueryCond3 + "\n");
if (GlobalVariable.gsUserGroup == "ADMIN_GROUP" || GlobalVariable.gsUserGroup == "HANA_ADMIN_GROUP")
{
System.Windows.Forms.Clipboard.SetText(strSqlString.ToString());
}
return(strSqlString.ToString());
}
/// <summary>
/// 4. 쿼리 생성
/// </summary>
/// <returns></returns>
private string MakeSqlString()
{
StringBuilder strSqlString = new StringBuilder();
string QueryCond1;
string start_date;
string yesterday;
string date;
string month;
string year;
string lastMonth;
string sKpcsValue;
udcTableForm tableForm = (udcTableForm)btnSort.BindingForm;
QueryCond1 = tableForm.SelectedValueToQueryContainNull;
GetWeek();
date = cdvDate.SelectedValue();
DateTime Select_date;
Select_date = DateTime.Parse(cdvDate.Text.ToString());
year = Select_date.ToString("yyyy");
month = Select_date.ToString("yyyyMM");
start_date = month + "01";
yesterday = Select_date.AddDays(-1).ToString("yyyyMMdd");
lastMonth = Select_date.AddMonths(-1).ToString("yyyyMM"); // 지난달
if (ckbKpcs.Checked == true)
{
sKpcsValue = "1000";
}
else
{
sKpcsValue = "1";
}
strSqlString.Append("SELECT MAT_GRP_10, MAT_CMF_11, MAT_GRP_5, CHIP, STACK" + "\n");
strSqlString.Append(" , KAJUNGCHI" + "\n");
strSqlString.Append(" , ROUND((WIP_2GROUP + WIP_SAW + WIP_BG) / " + sKpcsValue + ", 0) AS FRONT" + "\n");
strSqlString.Append(" , ROUND((WIP_2GROUP + WIP_SAW + WIP_BG) / STACK / " + sKpcsValue + ", 0) AS CONV_FRONT" + "\n");
strSqlString.Append(" , ROUND(WIP_BE / " + sKpcsValue + ", 0) AS WIP_BE" + "\n");
strSqlString.Append(" , ROUND((((WIP_2GROUP + WIP_SAW + WIP_BG) / STACK) + WIP_BE) / " + sKpcsValue + ", 0) AS WIP_TTL" + "\n");
strSqlString.Append(" , ROUND(PLN_WEEK2 / " + sKpcsValue + ", 0) AS PLN_WEEK2" + "\n");
strSqlString.Append(" , ROUND(PLN_WEEK4 / " + sKpcsValue + ", 0) AS PLN_WEEK4" + "\n");
strSqlString.Append(" , 0, 0" + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT A.MAT_GRP_1" + "\n");
strSqlString.Append(" , A.MAT_GRP_10" + "\n");
strSqlString.Append(" , A.MAT_CMF_11" + "\n");
strSqlString.Append(" , A.MAT_GRP_5 " + "\n");
strSqlString.Append(" , A.CHIP" + "\n");
strSqlString.Append(" , A.STACK" + "\n");
strSqlString.Append(" , ROUND(100 / A.STACK,0) AS KAJUNGCHI" + "\n");
strSqlString.Append(" , CASE WHEN A.MAT_GRP_5 IN ('-', '1st') THEN NVL(A.WIP_DA_WB,0) + NVL(B.WIP_MID_MER,0)" + "\n");
strSqlString.Append(" WHEN A.MAT_GRP_5 = '2nd' THEN NVL(A.WIP_DA_WB,0) + NVL(B.WIP_MID_MER,0)" + "\n");
strSqlString.Append(" WHEN A.MAT_GRP_5 = '3rd' THEN NVL(A.WIP_DA_WB,0) + NVL(B.WIP_MID_MER_3,0)" + "\n");
strSqlString.Append(" WHEN A.MAT_GRP_5 = '4th' THEN NVL(A.WIP_DA_WB,0) + NVL(B.WIP_MID_MER_4,0)" + "\n");
strSqlString.Append(" WHEN A.MAT_GRP_5 = '5th' THEN NVL(A.WIP_DA_WB,0) + NVL(B.WIP_MID_MER_5,0)" + "\n");
strSqlString.Append(" ELSE 0" + "\n");
strSqlString.Append(" END WIP_2GROUP" + "\n");
strSqlString.Append(" , NVL(A.WIP_SAW,0) AS WIP_SAW" + "\n");
strSqlString.Append(" , NVL(A.WIP_BG,0) AS WIP_BG" + "\n");
strSqlString.Append(" , CASE WHEN A.MAT_GRP_5 IN ('-', '1st') THEN NVL(B.WIP_BE,0) ELSE 0 END WIP_BE " + "\n");
strSqlString.Append(" , CASE WHEN A.MAT_GRP_5 IN ('-', '1st') THEN NVL(C.PLN_WEEK2,0) ELSE 0 END PLN_WEEK2 " + "\n");
strSqlString.Append(" , CASE WHEN A.MAT_GRP_5 IN ('-', '1st') THEN NVL(C.PLN_WEEK4,0) ELSE 0 END PLN_WEEK4 " + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT MAT_GRP_1, MAT_GRP_10, MAT_CMF_11, MAT_GRP_5" + "\n");
strSqlString.Append(" , CASE WHEN MAT_GRP_4 IN ('-', 'FD', 'FU') THEN '1'" + "\n");
strSqlString.Append(" WHEN MAT_GRP_4 IN ('DDP') THEN '2'" + "\n");
strSqlString.Append(" WHEN MAT_GRP_4 IN ('QDP') THEN '4'" + "\n");
strSqlString.Append(" WHEN MAT_GRP_4 IN ('ODP') THEN '8'" + "\n");
strSqlString.Append(" ELSE REGEXP_REPLACE(MAT_GRP_4, '[^[:digit:]]')" + "\n");
strSqlString.Append(" END AS STACK" + "\n");
//strSqlString.Append(" , CASE WHEN MAT_GRP_4 IN ('-','FD','FU') THEN '1'" + "\n");
//strSqlString.Append(" ELSE SUBSTR(MAT_GRP_4, 3)" + "\n");
//strSqlString.Append(" END AS STACK" + "\n");
strSqlString.Append(" , MAX(CASE WHEN MAT.MAT_ID LIKE 'SEKS3%' THEN SUBSTR(MAT.MAT_ID, INSTR(MAT.MAT_ID, '-')-3, 3)" + "\n");
strSqlString.Append(" WHEN MAT.MAT_ID LIKE 'SEK9%' THEN 'N'" + "\n");
strSqlString.Append(" WHEN MAT.MAT_ID LIKE 'SEKY%' THEN 'S'" + "\n");
strSqlString.Append(" WHEN MAT.MAT_ID LIKE 'SEK%' THEN 'D'" + "\n");
strSqlString.Append(" ELSE ' '" + "\n");
strSqlString.Append(" END) CHIP" + "\n");
strSqlString.Append(" , SUM(WIP_BG / COMP_CNT) AS WIP_BG" + "\n");
strSqlString.Append(" , SUM(WIP_SAW / COMP_CNT) AS WIP_SAW" + "\n");
strSqlString.Append(" , SUM(WIP_DA_WB) AS WIP_DA_WB" + "\n");
strSqlString.Append(" FROM (" + "\n");
strSqlString.Append(" SELECT A.*" + "\n");
strSqlString.Append(" FROM VWIPMATDEF A" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND FACTORY = '" + GlobalVariable.gsAssyDefaultFactory + "'" + "\n");
strSqlString.Append(" AND MAT_TYPE = 'FG'" + "\n");
strSqlString.Append(" AND DELETE_FLAG = ' '" + "\n");
strSqlString.Append(" AND MAT_GRP_5 <> 'Merge'" + "\n");
strSqlString.Append(" AND MAT_GRP_5 NOT LIKE 'Middle%'" + "\n");
strSqlString.Append(" AND MAT_ID LIKE 'SEK%'" + "\n");
if (txtProduct.Text.Trim() != "%" && txtProduct.Text.Trim() != "")
{
strSqlString.AppendFormat(" AND MAT_ID LIKE '{0}'" + "\n", txtProduct.Text);
}
#region 상세 조회에 따른 SQL문 생성
if (udcWIPCondition1.Text != "ALL" && udcWIPCondition1.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_1 {0}" + "\n", udcWIPCondition1.SelectedValueToQueryString);
}
if (udcWIPCondition2.Text != "ALL" && udcWIPCondition2.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_2 {0} " + "\n", udcWIPCondition2.SelectedValueToQueryString);
}
if (udcWIPCondition3.Text != "ALL" && udcWIPCondition3.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_3 {0} " + "\n", udcWIPCondition3.SelectedValueToQueryString);
}
if (udcWIPCondition4.Text != "ALL" && udcWIPCondition4.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_4 {0} " + "\n", udcWIPCondition4.SelectedValueToQueryString);
}
if (udcWIPCondition5.Text != "ALL" && udcWIPCondition5.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_5 {0} " + "\n", udcWIPCondition5.SelectedValueToQueryString);
}
if (udcWIPCondition6.Text != "ALL" && udcWIPCondition6.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_6 {0} " + "\n", udcWIPCondition6.SelectedValueToQueryString);
}
if (udcWIPCondition7.Text != "ALL" && udcWIPCondition7.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_7 {0} " + "\n", udcWIPCondition7.SelectedValueToQueryString);
}
if (udcWIPCondition8.Text != "ALL" && udcWIPCondition8.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_8 {0} " + "\n", udcWIPCondition8.SelectedValueToQueryString);
}
if (udcWIPCondition9.Text != "ALL" && udcWIPCondition9.Text != "")
{
strSqlString.AppendFormat(" AND MAT_GRP_9 {0} " + "\n", udcWIPCondition9.SelectedValueToQueryString);
}
#endregion
strSqlString.Append(" ) MAT" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT MAT_ID " + "\n");
strSqlString.Append(" , SUM(CASE WHEN OPER BETWEEN 'A0001' AND 'A0040' THEN QTY_1 ELSE 0 END) AS WIP_BG" + "\n");
strSqlString.Append(" , SUM(CASE WHEN OPER BETWEEN 'A0041' AND 'A0300' AND OPER <> 'A0250' THEN QTY_1 ELSE 0 END) AS WIP_SAW" + "\n");
strSqlString.Append(" , SUM(CASE WHEN OPER BETWEEN 'A0400' AND 'A0809' OR OPER = 'A0250' THEN QTY_1 ELSE 0 END) AS WIP_DA_WB" + "\n");
if (date == DateTime.Now.ToString("yyyyMMdd"))
{
strSqlString.Append(" FROM RWIPLOTSTS " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
}
else
{
strSqlString.Append(" FROM RWIPLOTSTS_BOH " + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND CUTOFF_DT = '" + date + "22' " + "\n");
}
if (cdvLotType.Text != "ALL")
{
strSqlString.Append(" AND LOT_CMF_5 LIKE '" + cdvLotType.Text + "'" + "\n");
}
strSqlString.Append(" AND FACTORY = '" + GlobalVariable.gsAssyDefaultFactory + "'" + "\n");
strSqlString.Append(" AND LOT_DEL_FLAG = ' '" + "\n");
strSqlString.Append(" AND LOT_TYPE = 'W'" + "\n");
strSqlString.Append(" GROUP BY MAT_ID" + "\n");
strSqlString.Append(" ) WIP" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND MAT.MAT_ID = WIP.MAT_ID(+) " + "\n");
strSqlString.Append(" GROUP BY MAT_GRP_1, MAT_GRP_10, MAT_CMF_11, MAT_GRP_5, MAT_GRP_4" + "\n");
strSqlString.Append(" ) A" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT A.MAT_GRP_1, A.MAT_GRP_10, A.MAT_CMF_11 " + "\n");
strSqlString.Append(" , SUM(CASE WHEN B.OPER BETWEEN 'A0400' AND 'A0609' AND MAT_GRP_5 NOT IN ('-') THEN QTY_1 ELSE 0 END) AS WIP_MID_MER" + "\n");
strSqlString.Append(" , SUM(CASE WHEN B.OPER BETWEEN 'A0400' AND 'A0609' AND MAT_GRP_5 NOT IN ('-', 'Middle') THEN QTY_1 ELSE 0 END) AS WIP_MID_MER_3" + "\n");
strSqlString.Append(" , SUM(CASE WHEN B.OPER BETWEEN 'A0400' AND 'A0609' AND MAT_GRP_5 NOT IN ('-', 'Middle', 'Middle 1') THEN QTY_1 ELSE 0 END) AS WIP_MID_MER_4" + "\n");
strSqlString.Append(" , SUM(CASE WHEN B.OPER BETWEEN 'A0400' AND 'A0609' AND MAT_GRP_5 NOT IN ('-', 'Middle', 'Middle 1', 'Middle 2') THEN QTY_1 ELSE 0 END) AS WIP_MID_MER_5" + "\n");
strSqlString.Append(" , SUM(CASE WHEN C.OPER_GRP_1 IN ('MOLD','CURE','M/K','TRIM','TIN','S/B/A','SIG','AVI','V/I','HMK3A') THEN QTY_1 ELSE 0 END) AS WIP_BE " + "\n");
if (date == DateTime.Now.ToString("yyyyMMdd"))
{
strSqlString.Append(" FROM MWIPMATDEF A" + "\n");
strSqlString.Append(" , RWIPLOTSTS B" + "\n");
strSqlString.Append(" , MWIPOPRDEF C" + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
}
else
{
strSqlString.Append(" FROM MWIPMATDEF A" + "\n");
strSqlString.Append(" , RWIPLOTSTS_BOH B" + "\n");
strSqlString.Append(" , MWIPOPRDEF C" + "\n");
strSqlString.Append(" WHERE 1=1 " + "\n");
strSqlString.Append(" AND B.CUTOFF_DT = '" + date + "22' " + "\n");
}
if (cdvLotType.Text != "ALL")
{
strSqlString.Append(" AND B.LOT_CMF_5 LIKE '" + cdvLotType.Text + "'" + "\n");
}
strSqlString.Append(" AND A.FACTORY = B.FACTORY" + "\n");
strSqlString.Append(" AND A.FACTORY = C.FACTORY" + "\n");
strSqlString.Append(" AND A.MAT_ID = B.MAT_ID" + "\n");
strSqlString.Append(" AND B.OPER = C.OPER" + "\n");
strSqlString.Append(" AND B.FACTORY = '" + GlobalVariable.gsAssyDefaultFactory + "'" + "\n");
strSqlString.Append(" AND B.OPER BETWEEN 'A0400' AND 'AZ010'" + "\n");
strSqlString.Append(" AND B.LOT_TYPE = 'W'" + "\n");
strSqlString.Append(" AND B.LOT_DEL_FLAG = ' '" + "\n");
strSqlString.Append(" AND B.MAT_ID LIKE 'SEK%'" + "\n");
strSqlString.Append(" AND A.DELETE_FLAG = ' '" + "\n");
strSqlString.Append(" AND REGEXP_LIKE(A.MAT_GRP_5, 'Middle*|Merge|-')" + "\n");
strSqlString.Append(" GROUP BY A.MAT_GRP_1, A.MAT_GRP_10, A.MAT_CMF_11 " + "\n");
strSqlString.Append(" ) B" + "\n");
strSqlString.Append(" , (" + "\n");
strSqlString.Append(" SELECT A.MAT_GRP_1, A.MAT_GRP_10, A.MAT_CMF_11" + "\n");
strSqlString.Append(" , SUM(CASE WHEN PLAN_WEEK IN ('" + dtWeek.Rows[0][0] + "', '" + dtWeek.Rows[1][0] + "') THEN WW_QTY ELSE 0 END) AS PLN_WEEK2" + "\n");
strSqlString.Append(" , SUM(WW_QTY) AS PLN_WEEK4" + "\n");
strSqlString.Append(" FROM MWIPMATDEF A" + "\n");
strSqlString.Append(" , RWIPPLNWEK B" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND A.FACTORY = B.FACTORY" + "\n");
strSqlString.Append(" AND A.MAT_ID = B.MAT_ID" + "\n");
strSqlString.Append(" AND A.DELETE_FLAG = ' '" + "\n");
strSqlString.Append(" AND B.FACTORY = '" + GlobalVariable.gsAssyDefaultFactory + "'" + "\n");
strSqlString.Append(" AND B.PLAN_WEEK IN ('" + dtWeek.Rows[0][0] + "','" + dtWeek.Rows[1][0] + "', '" + dtWeek.Rows[2][0] + "', '" + dtWeek.Rows[3][0] + "')" + "\n");
strSqlString.Append(" AND B.GUBUN = '0'" + "\n");
strSqlString.Append(" AND B.MAT_ID LIKE 'SEK%'" + "\n");
strSqlString.Append(" GROUP BY A.MAT_GRP_1, A.MAT_GRP_10, A.MAT_CMF_11" + "\n");
strSqlString.Append(" ) C" + "\n");
strSqlString.Append(" WHERE 1=1" + "\n");
strSqlString.Append(" AND A.MAT_GRP_1 = B.MAT_GRP_1(+)" + "\n");
strSqlString.Append(" AND A.MAT_GRP_1 = C.MAT_GRP_1(+)" + "\n");
strSqlString.Append(" AND A.MAT_GRP_10 = B.MAT_GRP_10(+)" + "\n");
strSqlString.Append(" AND A.MAT_GRP_10 = C.MAT_GRP_10(+)" + "\n");
strSqlString.Append(" AND A.MAT_CMF_11 = B.MAT_CMF_11(+)" + "\n");
strSqlString.Append(" AND A.MAT_CMF_11 = C.MAT_CMF_11(+)" + "\n");
strSqlString.Append(" AND NVL(A.WIP_DA_WB,0) + NVL(B.WIP_MID_MER,0) + NVL(B.WIP_BE,0) + NVL(A.WIP_SAW,0) +" + "\n");
strSqlString.Append(" NVL(A.WIP_BG,0) + NVL(C.PLN_WEEK2,0) + NVL(C.PLN_WEEK4,0) > 0" + "\n");
strSqlString.Append(" )" + "\n");
strSqlString.Append(" ORDER BY MAT_GRP_10, MAT_CMF_11, MAT_GRP_5" + "\n");
if (GlobalVariable.gsUserGroup == "ADMIN_GROUP" || GlobalVariable.gsUserGroup == "HANA_ADMIN_GROUP")
{
System.Windows.Forms.Clipboard.SetText(strSqlString.ToString());
}
return(strSqlString.ToString());
}