private object[] LoadDataSource() { int dateAdjust = 0; bool roundDate = false; string byTimeType = UCGroupConditions1.UserSelectByTimeType; string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); ReportSQLHelper sqlHelper = new ReportSQLHelper(this.DataProvider); string sql = sqlHelper.GetPerformanceReportSQL( UCGroupConditions1.FacCodeChecked, UCGroupConditions1.FacCodeChecked || !UCGroupConditions1.SSChecked, UCGroupConditions1.ExcludeReworkOutputChecked, UCGroupConditions1.ExcludeLostManHourChecked, UCGroupConditions1.ShowIncludeIndirectManHour && UCGroupConditions1.IncludeIndirectManHourChecked, UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, roundDate, dateAdjust), groupFieldsX, groupFieldsY, sqlHelper.GetFormularForManHourPerProduct(UCGroupConditions1.ExcludeLostManHourChecked, UCGroupConditions1.ShowIncludeIndirectManHour && UCGroupConditions1.IncludeIndirectManHourChecked) ); ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); return(engine.GetReportDataSource(sql, byTimeType, dateAdjust)); }
public object[] QueryProudctDataByDateAndSSCodeList(int shiftDate, string bigSSCodeList) { bigSSCodeList = bigSSCodeList.Trim().ToUpper().Replace(",", "','"); string Sql = string.Empty; Sql = " SELECT nvl(daydate.PASSRATE,0) as PASSRATE,nvl(daydate.MTYPE,D.MTYPE) as MTYPE,nvl(daydate.MANCOUNT,0) as MANCOUNT,nvl(daydate.UPPH,0) as UPPH, D.MONTHPRODUCTQTY FROM ("; Sql += " SELECT A.PASSRATE, A.MTYPE, B.MANCOUNT, C.UPPH "; Sql += " FROM (SELECT SUM(RPT.MOLINEOUTPUTCOUNT) AS MOLINEOUTPUTCOUNT,"; Sql += " SUM(RPT.MOOUTPUTWHITECARDCOUNT) AS MOOUTPUTWHITECARDCOUNT,"; Sql += " DECODE(NVL(SUM(RPT.MOLINEOUTPUTCOUNT), 0),0,0,SUM(RPT.MOOUTPUTWHITECARDCOUNT) /SUM(RPT.MOLINEOUTPUTCOUNT)) AS PASSRATE,"; Sql += " RPT.SHIFTDAY AS SHIFTDATE,MA.MTYPE FROM TBLRPTSOQTY RPT, TBLMESENTITYLIST T, TBLMATERIAL MA"; Sql += " WHERE RPT.TBLMESENTITYLIST_SERIAL = T.SERIAL AND RPT.ITEMCODE = MA.MCODE"; Sql += " AND RPT.SHIFTDAY = " + shiftDate + ""; Sql += " AND T.BIGSSCODE IN ('" + bigSSCodeList + "')"; Sql += " GROUP BY RPT.SHIFTDAY, MA.MTYPE ORDER BY RPT.SHIFTDAY, MA.MTYPE) A FULL JOIN"; Sql += " (SELECT COUNT(1) AS MANCOUNT, SHIFTDATE, TBLMATERIAL.MTYPE"; Sql += " FROM TBLLINE2MANDETAIL, TBLMATERIAL, TBLMO,TBLSS"; Sql += " WHERE TBLLINE2MANDETAIL.MOCODE = TBLMO.MOCODE AND TBLMO.ITEMCODE = TBLMATERIAL.MCODE"; Sql += " AND TBLLINE2MANDETAIL.Sscode=TBLSS.Sscode"; Sql += " AND SHIFTDATE = " + shiftDate + ""; Sql += " AND TBLSS.Bigsscode IN ('" + bigSSCodeList + "')"; Sql += " AND STATUS IN ('" + Line2ManDetailStatus.Line2ManDetailStatus_On + "', '" + Line2ManDetailStatus.Line2ManDetailStatus_AutoOn + "')"; Sql += " GROUP BY SHIFTDATE, TBLMATERIAL.MTYPE) B ON A.MTYPE =B.MTYPE FULL JOIN "; ReportSQLHelper sqlHelper = new ReportSQLHelper(this.DataProvider); string sqlUPPH = sqlHelper.GetPerformanceReportSQL( false, true, false, false, false, "1 = 1 AND **.bigsscode in ('" + bigSSCodeList + "') AND **.shiftday =" + shiftDate + " ", "**.shiftday AS shiftday", "tblmaterial.mtype", "DECODE(SUM(manhour), 0, 0, SUM(realqty) / (SUM(manhour))) AS upph, SUM(realqty) AS realqty, SUM(manhour) AS manhoursum" ); Sql += " (" + sqlUPPH + ") C ON A.MTYPE = C.MTYPE) daydate FULL JOIN "; Sql += " (" + GetOutPutQtyGroupByItemTypeSql(bigSSCodeList, shiftDate, true) + ") D"; Sql += " ON daydate.MTYPE=D.MTYPE"; return(this.DataProvider.CustomQuery(typeof(watchPanelProductDate), new SQLCondition(Sql))); }
public object[] QueryProductData(string bigSSCode, int shiftDate, ArrayList shiftCodeList, string timePeriodCode) { string beginCondition = string.Empty; string mainCondition = string.Empty; string leftJoinCondition = string.Empty; string bodyBSsql = string.Empty; string shiftLineOutColunmsSql = string.Empty; string shiftLineOutMainSql = string.Empty; //Modified By Nettie Chen 2009/09/23 ADD MM.MNAME //beginCondition = "SELECT tt.ITEMCODE AS ITEMCODE,MM.Mmodelcode AS MATERIALMODELCODE,"; beginCondition = "SELECT MM.MNAME AS ItemName,tt.ITEMCODE AS ITEMCODE,MM.Mmodelcode AS MATERIALMODELCODE,"; //End Modified beginCondition += " TT.PLANQTY AS DAYPLANQTY,NVL(SS.LOUT, 0) AS PERTIMEOUTPUTQTY,AA.PASSRCARDRATE AS PASSRATE,"; beginCondition += " CY.MANHOURPERPRODUCT AS ONENEEDTIME,UP.UPPH AS UPPH"; if (shiftCodeList.Count > 0) { for (int i = 1; i <= shiftCodeList.Count; i++) { beginCondition += ",tt.SHIFTLOUT" + i.ToString(); } } beginCondition += " FROM (SELECT NVL(A.PLANDATE, B.BSHIFTDAY) SHIFTDAY,NVL(A.ITEMCODE, B.ITEMCODE) ITEMCODE,NVL(A.PLANQTY, 0) PLANQTY"; if (shiftCodeList.Count > 0) { for (int i = 1; i <= shiftCodeList.Count; i++) { beginCondition += ",NVL(B.LOUT" + i.ToString() + ", 0) SHIFTLOUT" + i.ToString(); } } beginCondition += " FROM (SELECT SUM(PLANQTY) AS PLANQTY, BIGSSCODE, ITEMCODE, PLANDATE FROM TBLWORKPLAN"; beginCondition += " WHERE BIGSSCODE = '" + bigSSCode.Trim().ToUpper() + "' AND PLANDATE = " + shiftDate + ""; beginCondition += " GROUP BY BIGSSCODE, ITEMCODE, PLANDATE) A FULL JOIN ("; shiftLineOutColunmsSql = "SELECT BSHIFTDAY,ITEMCODE "; if (shiftCodeList.Count > 0) { for (int i = 1; i <= shiftCodeList.Count; i++) { shiftLineOutColunmsSql += ",SUM(LOUT" + i.ToString() + ") AS LOUT" + i.ToString(); } shiftLineOutColunmsSql += " FROM ("; } shiftLineOutColunmsSql += " SELECT "; if (shiftCodeList.Count > 0) { string shiftDayString = "SHIFT1.SHIFTDAY"; string itemCodeString = "SHIFT1.ITEMCODE"; string shiftCodeString = "SHIFT1.SHIFTCODE"; for (int i = 1; i <= shiftCodeList.Count; i++) { shiftDayString = " NVL(" + shiftDayString + ",SHIFT" + i.ToString() + ".SHIFTDAY)"; itemCodeString = " NVL(" + itemCodeString + ",SHIFT" + i.ToString() + ".ITEMCODE) "; shiftCodeString = " NVL(" + shiftCodeString + ",SHIFT" + i.ToString() + ".SHIFTCODE)"; } shiftLineOutColunmsSql += shiftDayString + " AS BSHIFTDAY," + itemCodeString + "AS ITEMCODE," + shiftCodeString + "AS BSHIFTCODE"; for (int i = 0; i < shiftCodeList.Count; i++) { shiftLineOutColunmsSql += ",NVL(SHIFT" + Convert.ToString(i + 1) + ".LOUT, 0) LOUT" + Convert.ToString(i + 1) + ""; if (i > 0) { shiftLineOutMainSql += " FULL JOIN ("; } shiftLineOutMainSql += "SELECT Y.SHIFTDAY,Y.ITEMCODE,E.SHIFTCODE,SUM(MOLineOutputCount) LOUT"; shiftLineOutMainSql += " FROM TBLRPTSOQTY Y, TBLMESENTITYLIST E, TBLSS S"; shiftLineOutMainSql += " WHERE Y.TBLMESENTITYLIST_SERIAL = E.SERIAL"; shiftLineOutMainSql += " AND E.SSCODE = S.SSCODE AND Y.SHIFTDAY = " + shiftDate + ""; shiftLineOutMainSql += " AND S.BIGSSCODE = '" + bigSSCode.Trim().ToUpper() + "' AND E.SHIFTCODE = '" + ((Shift)shiftCodeList[i]).ShiftCode + "'"; shiftLineOutMainSql += " GROUP BY Y.ITEMCODE, Y.SHIFTDAY, E.SHIFTCODE) SHIFT" + Convert.ToString(i + 1); if (i > 0) { shiftLineOutMainSql += " ON SHIFT1.SHIFTDAY =SHIFT" + Convert.ToString(i + 1) + ".SHIFTDAY "; shiftLineOutMainSql += " AND SHIFT1.ITEMCODE =SHIFT" + Convert.ToString(i + 1) + ".ITEMCODE"; shiftLineOutMainSql += " AND SHIFT1.SHIFTCODE =SHIFT" + Convert.ToString(i + 1) + ".SHIFTCODE"; } } shiftLineOutColunmsSql += " FROM ("; shiftLineOutMainSql += ") GROUP BY BSHIFTDAY, ITEMCODE) B ON A.ITEMCODE = B.ITEMCODE AND A.PLANDATE = B.BSHIFTDAY) TT"; } bodyBSsql = shiftLineOutColunmsSql + shiftLineOutMainSql; mainCondition = beginCondition + bodyBSsql; leftJoinCondition = " LEFT JOIN (SELECT Y.SHIFTDAY,Y.ITEMCODE,E.SHIFTCODE,E.TPCODE,SUM(MOLineOutputCount) LOUT"; leftJoinCondition += " FROM TBLRPTSOQTY Y, TBLMESENTITYLIST E, TBLSS S WHERE Y.TBLMESENTITYLIST_SERIAL = E.SERIAL"; leftJoinCondition += " AND E.SSCODE = S.SSCODE AND Y.SHIFTDAY = " + shiftDate + ""; leftJoinCondition += " AND S.BIGSSCODE = '" + bigSSCode.Trim().ToUpper() + "' AND E.TPCODE = '" + timePeriodCode.Trim().ToUpper() + "'"; leftJoinCondition += " GROUP BY Y.ITEMCODE, Y.SHIFTDAY, E.SHIFTCODE, E.TPCODE) SS "; leftJoinCondition += " ON TT.SHIFTDAY =SS.SHIFTDAY AND TT.ITEMCODE =SS.ITEMCODE"; leftJoinCondition += " LEFT JOIN TBLMATERIAL MM ON TT.ITEMCODE = MM.MCODE"; leftJoinCondition += " LEFT JOIN (SELECT SUM(RPT.MOLINEOUTPUTCOUNT) AS MOLINEOUTPUTCOUNT,SUM(RPT.MOOUTPUTWHITECARDCOUNT) AS MOOUTPUTWHITECARDCOUNT,"; leftJoinCondition += " DECODE(NVL(SUM(RPT.MOLINEOUTPUTCOUNT), 0),0,0,SUM(RPT.MOOUTPUTWHITECARDCOUNT) /SUM(RPT.MOLINEOUTPUTCOUNT)) AS PASSRCARDRATE,"; leftJoinCondition += " T.BIGSSCODE,RPT.ITEMCODE FROM TBLRPTSOQTY RPT, TBLMESENTITYLIST T"; leftJoinCondition += " WHERE RPT.TBLMESENTITYLIST_SERIAL = T.SERIAL AND RPT.SHIFTDAY = " + shiftDate + ""; leftJoinCondition += " AND T.BIGSSCODE = '" + bigSSCode.Trim().ToUpper() + "' GROUP BY T.BIGSSCODE, RPT.ITEMCODE) AA ON TT.ITEMCODE =AA.ITEMCODE"; ReportSQLHelper sqlHelper = new ReportSQLHelper(this.DataProvider); string sqlUPPH = sqlHelper.GetPerformanceReportSQL( false, true, false, false, false, "1 = 1 AND **.bigsscode = '" + bigSSCode.Trim().ToUpper() + "' AND **.shiftday = " + shiftDate + "", "**.shiftday AS shiftday", "**.itemcode || ' - ' || tblmaterial.mdesc AS itemcode,**.bigsscode AS bigsscode", "DECODE(SUM(manhour), 0, 0, SUM(realqty) / (SUM(manhour))) AS upph, SUM(realqty) AS realqty, SUM(manhour) AS manhoursum" ); sqlUPPH = sqlUPPH.Replace("ct.itemcode || ' - ' || tblmaterial.mdesc", "ct.itemcode"); leftJoinCondition += " LEFT JOIN (" + sqlUPPH + ") UP ON TT.ITEMCODE =UP.ITEMCODE"; string manHourPerProductSql = sqlHelper.GetPerformanceReportSQL( false, true, false, false, false, "1 = 1 AND **.bigsscode = '" + bigSSCode.Trim().ToUpper() + "' AND **.shiftday = " + shiftDate + " ", "**.shiftday AS shiftday", "**.itemcode || ' - ' || tblmaterial.mdesc AS itemcode,**.bigsscode AS bigsscode", "DECODE(SUM(standardqty), 0, 0, (SUM(manhour)) / SUM(standardqty)) AS manhourperproduct, SUM(standardqty) AS standardqty, SUM(manhour) AS manhoursum" ); manHourPerProductSql = manHourPerProductSql.Replace("ct.itemcode || ' - ' || tblmaterial.mdesc", "ct.itemcode"); leftJoinCondition += " LEFT JOIN (" + manHourPerProductSql + ") CY ON TT.ITEMCODE =CY.ITEMCODE"; mainCondition += leftJoinCondition; return(this.DataProvider.CustomQuery(typeof(watchPanelProductDate), new SQLCondition(mainCondition))); }