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