/// <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 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());
}