示例#1
0
        /// <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());
        }
示例#2
0
        /// <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());
        }
示例#3
0
        /// <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());
        }
示例#4
0
        /// <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());
        }