/// <summary> /// Main function, generate the result data Table for the REPORT VIEWER /// </summary> /// <param name="pstrCCNID"></param> /// <returns></returns> public DataTable ExecuteReportOLD(string pstrCCNID, string pstrYear, string pstrMonth, string pstrMPSCycleID, string pstrProductionLineID) { //const string METHOD_NAME = ".ExecuteReport()"; const string TABLE_NAME = "WorkingSchemeReport"; const string SUB_TABLE_NAME = "ShiftTotalReport"; string REPORT_NAME = "WorkingSchemeReport"; const string SUB_REPORT_NAME = "ShiftTotalReport"; string REPORT_LAYOUT_FILE = "InventoryStatusReport.xml"; short COPIES = 1; const string ENDSTOCK = "EndStock"; const string CHANGECATEGORY = "Change Category"; const string LEADTIME = "Lead Time"; const string REQUIRECAPACITY = "Require Capacity"; const string STANDARDCAPACITY = "Standard Capacity"; const string COMPARESECOND = "Compare Second"; const string COMPAREPERCENT = "Compare Percent"; string strFromDate = string.Format("{0}-{1}-01", pstrYear, pstrMonth); // begin date of the selected month const string REPORTFLD_WORKINGDAYS = "fldParameterWorkingdays"; const string REPORTFLD_OFFDAYS = "fldParameterOffdays"; const string REPORTFLD_PARAMETER_CCN = "fldParameterCCN"; const string REPORTFLD_PARAMETER_YEAR = "fldParameterYear"; const string REPORTFLD_PARAMETER_MONTH = "fldParameterMonth"; const string REPORTFLD_PARAMETER_CYCLE = "fldParameterCycle"; const string REPORTFLD_PARAMETER_PRODUCTIONLINE = "fldParameterProductionLine"; int nCCNID = int.Parse(pstrCCNID); int nYear = int.Parse(pstrYear); int nMonth = int.Parse(pstrMonth); int nCycle = int.Parse(pstrMPSCycleID); int nProductionLineID = int.Parse(pstrProductionLineID); int nWorkingDays; int nOffDays; string strCCN = string.Empty; string strCycle = string.Empty; string strProductionLine = string.Empty; #region GETTING THE PARAMETER PCSComUtils.Common.BO.UtilsBO boUtil = new PCSComUtils.Common.BO.UtilsBO(); PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO objBO = new PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO(); strCCN = boUtil.GetCCNCodeFromID(nCCNID); strCycle = objBO.GetMPSCycleFromID(nCycle) + "-" + objBO.GetMPSCycleDescriptionFromID(nCycle); strProductionLine = objBO.GetProductLineCodeFromID(nProductionLineID) + "-" + objBO.GetProductLineNameFromID(nProductionLineID); // refer to mr.TuanTQ to get WOrkingDayInMonth nWorkingDays = GetWorkingDayInMonth(nMonth, nYear); nOffDays = DateTime.DaysInMonth(nYear, nMonth) - nWorkingDays; #endregion System.Data.DataTable dtbSourceData; System.Data.DataTable dtbSubReportData; OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; string strSql = string.Empty; #region BUILD THE DATA TABLE try { DataSet dstPCS = new DataSet(); oconPCS = null; ocmdPCS = null; #region SQL Query strSql = " Declare @strFromDate smalldatetime " + " Declare @pstrCCNID int " + " Declare @pstrProductionLineID int " + " Declare @pstrMPSCycleID int " + " Declare @pstrMonth char(2) " + " Declare @pstrYear char(4) " + " " + " Declare @pstrInArray varchar(40) " + " Declare @pstrOutArray varchar(40) " + " " + " " + " Set @strFromDate = '" + pstrYear + "-" + nMonth.ToString("00") + "-01' " + " Set @pstrCCNID = " + pstrCCNID + " " + " Set @pstrProductionLineID = " + pstrProductionLineID + " " + " Set @pstrMPSCycleID = " + pstrMPSCycleID + " " + " Set @pstrYear = '" + pstrYear + "' " + " Set @pstrMonth = '" + nMonth.ToString("00") + "' " + " " + " " + " select " + " P.ProductID as [ProductID], " + " ITM_Category.Code as [Category], " + " P.Code as [Part Number], " + " P.Description as [Part Name], " + " P.Revision as [Model], " + " IsNull(CPOTable.Quantity,0) as [Plan], " + " IsNull(BeginStockTable.[Begin Stock],0.00) as [Begin Stock], " + " (IsNull(BeginStockTable.[Begin Stock],0) - IsNull(CPOTable.Quantity,0)) As [EndStock], " + " " + " " + " CAST(IsNull((ChangeTimeTable.ChangeTime) , 0.00) as decimal(20,5) ) as [Change Category], " + " " + " [Lead Time] = " + " CASE " + " WHEN ITM_Routing.Pacer = 'L' THEN ITM_Routing.LaborSetupTime + ITM_Routing.LaborRunTime " + " WHEN ITM_Routing.Pacer = 'M' THEN ITM_Routing.MachineSetupTime + ITM_Routing.MachineRunTime " + " WHEN ITM_Routing.Pacer = 'B' THEN ITM_Routing.MachineRunTime + ITM_Routing.LaborRunTime " + " END, " + " " + " 0.00 as [Require Capacity] " + " " + " from " + " MST_WorkCenter join ITM_Routing " + " on MST_WorkCenter.WorkCenterID = ITM_Routing.WorkCenterID and ITM_Routing.Type = 0 " + " left join PRO_ProductionLine " + " on MST_WOrkCenter.ProductionLineID = PRO_ProductionLine.ProductionLineID " + " join ITM_Product as P " + " on P.ProductID = ITM_Routing.ProductID " + " join ITM_Category " + " on P.CategoryID = ITM_Category.CategoryID " + " join PRO_WCCapacity " + " on MST_WorkCenter.WorkCenterID = PRO_WCCapacity.WorkCenterID " + " join PRO_ShiftCapacity " + " on PRO_WCCapacity.WCCapacityID = PRO_ShiftCapacity.WCCapacityID " + " join PRO_Shift " + " on PRO_ShiftCapacity.ShiftID = PRO_Shift.ShiftID " + " " + " " + " /* --######-- BEGIN Get Begin Quantity of Product */ " + " left join " + " ( " + " SELECT DISTINCT "+ " ITM_Product.ProductID, "+ " " + " ( "+ " ISNULL(IV_MasLocCache.OHQuantity, 0) "+ " " + " - (SELECT ISNULL(SUM(TransQuantity), 0) "+ " FROM v_TransactionHistory inFrom_Today_TransHis "+ " WHERE ProductID = ITM_Product.ProductID "+ " AND CCNID = @pstrCCNID "+ " AND PostDate BETWEEN @strFromDate AND GetDate() "+ " AND TranTypeID IN (8, 11, 13, 19, 20, 16, 17) "+ " ) "+ " " + " + (SELECT ISNULL(SUM(TransQuantity), 0) "+ " FROM v_TransactionHistory "+ " WHERE ProductID = ITM_Product.ProductID "+ " AND CCNID = @pstrCCNID "+ " AND PostDate BETWEEN @strFromDate AND GetDate() "+ " AND TranTypeID IN (12, 14, 15) "+ " ) "+ " ) "+ " as [Begin Stock] "+ " " + " FROM ITM_Product "+ " INNER JOIN IV_MasLocCache ON ITM_Product.ProductID = IV_MasLocCache.ProductID "+ " WHERE ITM_Product.CCNID = @pstrCCNID "+ " " + " GROUP BY "+ " ITM_Product.ProductID, "+ " IV_MasLocCache.OHQuantity "+ " " + " ) " + " as BeginStockTable " + " on P.ProductID = BeginStockTable.ProductID " + " /* ######-- END Get Begin Quantity of Product */ " + " " + " " + " /* BEGIN: Getting the Change time of Product */ " + " left join " + " ( " + " select "+ " CCMatrix.DestProductID as [ProductID], "+ " Sum(CCMatrix.ChangeTime) as [ChangeTime] "+ " "+ " from PRO_ChangeCategoryMatrix as CCMatrix "+ " "+ " /*BEGIN: Join to get condition on parameter: ProductionLineID*/ "+ " join PRO_ChangeCategoryMaster CCMaster "+ " on CCMaster.ChangeCategoryMasterID = CCMatrix.ChangeCategoryMasterID "+ " join MST_WorkCenter "+ " on MST_WorkCenter.WorkCenterID = CCMaster.WorkCenterID "+ " and MST_WorkCenter.ProductionLineID = @pstrProductionLineID "+ " and MST_WorkCenter.CCNID = @pstrCCNID "+ " /*END: Join to get condition on parameter: CCNID, ProductionLineID*/ "+ " "+ " group by "+ " DestProductID "+ " ) " + " as ChangeTimeTable " + " on P.ProductID = ChangeTimeTable.ProductID " + " /*-- END: Getting the Change time of Product */ " + " " + " " + " " + " /* BEGIN GETTING CPO PLAN QUANTITY */ " + " left join " + " ( " + " SELECT "+ " MTR_CPO.MPSCycleOptionMasterID, "+ " ITM_Product.ProductID, "+ " ITM_Product.Revision AS [Model], "+ " ITM_Product.Code AS [Part Number], "+ " /* --DATEPART(yyyy, MTR_CPO.DueDate) as [Year], */ "+ " /* --DATEPART(mm, MTR_CPO.DueDate)as [Month], */ "+ " SUM(MTR_CPO.Quantity) AS [Quantity] "+ " "+ " FROM "+ " MTR_CPO "+ " INNER JOIN MST_CCN "+ " ON MTR_CPO.CCNID = MST_CCN.CCNID "+ " INNER JOIN ITM_Product "+ " ON MTR_CPO.ProductID = ITM_Product.ProductID "+ " left outer JOIN ITM_Category "+ " ON ITM_Product.CategoryID = ITM_Category.CategoryID "+ " "+ " WHERE "+ " MTR_CPO.CCNID = @pstrCCNID AND "+ " MTR_CPO.MPSCycleOptionMasterID = @pstrMPSCycleID AND "+ " DATEPART(yyyy, MTR_CPO.DueDate) = @pstrYear AND "+ " DATEPART(mm, MTR_CPO.DueDate) = @pstrMonth "+ " "+ " GROUP BY "+ " MTR_CPO.MPSCycleOptionMasterID, "+ " MST_CCN.Code, "+ " ITM_Category.Code, "+ " ITM_Product.ProductID, "+ " ITM_Product.Code, "+ " ITM_Product.Description, "+ " ITM_Product.Revision, "+ " DatePart(yyyy,MTR_CPO.DueDate), "+ " DatePart(mm,MTR_CPO.DueDate) "+ " ) " + " as CPOTable " + " on P.ProductID = CPOTable.ProductID " + " /* END GETTING CPO PLAN QUANTITY */ " + " " + " " + " " + " " + " WHERE " + " PRO_ProductionLine.ProductionLineID = @pstrProductionLineID " + " and DATEPART(yyyy, PRO_WCCapacity.BeginDate ) <= @pstrYear " + " and DATEPART(yyyy, PRO_WCCapacity.EndDate ) >= @pstrYear " + " and DATEPART(mm, PRO_WCCapacity.BeginDate ) <= @pstrMonth " + " and DATEPART(mm, PRO_WCCapacity.EndDate ) >= @pstrMonth " + " and MST_WOrkCenter.IsMain = 1 " + " " + " " + " /* GROUP BY of outside query*/ " + " group by "+ " P.ProductID, " + " ITM_Category.Code, " + " P.Code, " + " P.Description, " + " P.Revision, " + " CPOTable.Quantity, " + " BeginStockTable.[Begin Stock], " + " " + " (IsNull(CPOTable.Quantity,0) - IsNull(BeginStockTable.[Begin Stock],0)), " + " " + " ITM_Routing.Pacer, " + " ITM_Routing.LaborSetupTime, " + " ITM_Routing.LaborRunTime, " + " ITM_Routing.MachineSetupTime, " + " ITM_Routing.MachineRunTime, " + " CAST(IsNull((ChangeTimeTable.ChangeTime) , 0.00) as decimal(20,5) ) " + " " + " Order by [Category],[Part Number],[Model] " + " " + " " + " " + " /**********************************************************************************/ " + " /**********************************************************************************/ " + " /**********************************************************************************/ " + " " + " " + " /* BEGIN StandardCapacity caculate */ " + " select " + " PRO_Shift.ShiftDesc as [Shift], " + " sum(PRO_WCCapacity.Capacity) as [Standard Capacity] " + " " + " from " + " MST_WorkCenter " + " join PRO_WCCapacity " + " on MST_WorkCenter.WorkCenterID = PRO_WCCapacity.WorkCenterID " + " join PRO_ShiftCapacity " + " on PRO_WCCapacity.WCCapacityID = PRO_ShiftCapacity.WCCapacityID " + " join PRO_Shift " + " on PRO_ShiftCapacity.ShiftID = PRO_Shift.ShiftID " + " " + " /* left join MTR_CPO " + " on MTR_CPO.ProductID = ITM_Product.ProductID " + " and MTR_CPO.MPSCycleOptionMasterID = @pstrMPSCycleID " + " */ " + " " + " WHERE " + " MST_WorkCenter.ProductionLineID = @pstrProductionLineID " + " and PRO_WCCapacity.CCNID = @pstrCCNID " + " and MST_WOrkCenter.IsMain = 1 " + " " + " and DATEPART(yyyy, PRO_WCCapacity.BeginDate ) <= @pstrYear " + " and DATEPART(yyyy, PRO_WCCapacity.EndDate ) >= @pstrYear " + " and DATEPART(mm, PRO_WCCapacity.BeginDate ) <= @pstrMonth " + " and DATEPART(mm, PRO_WCCapacity.EndDate ) >= @pstrMonth " + " " + " " + " group by " + " PRO_Shift.ShiftDesc " + " " + " /* END StandardCapacity caculate */ " + " " + " "; #endregion oconPCS = new OleDbConnection(mConnectionString); ocmdPCS = new OleDbCommand(strSql, oconPCS); ocmdPCS.Connection.Open(); OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS); odadPCS.Fill(dstPCS); if (dstPCS.Tables.Count > 0) { dtbSourceData = dstPCS.Tables[0].Copy(); dtbSubReportData = dstPCS.Tables[1].Copy(); } else { dtbSourceData = new DataTable(); dtbSubReportData = new DataTable(); } } catch (OleDbException ex) { throw new Exception(strSql, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } #endregion #region TRANSFORM DATATABLE FOR REPORT // only re-calculate Require Capacity, Compare Second, and Compare Percent column /// Calculate the Require Capacity foreach (DataRow drow in dtbSourceData.Rows) { drow[REQUIRECAPACITY] = (decimal)drow[ENDSTOCK] * (decimal)drow[LEADTIME] + (decimal)drow[CHANGECATEGORY]; } decimal decSumOfRequireCapacity = 0; /// calculate the Sum of Require Capacity foreach (DataRow drow in dtbSourceData.Rows) { decSumOfRequireCapacity += (decimal)drow[REQUIRECAPACITY]; } dtbSubReportData.Columns.Add(COMPARESECOND, typeof(System.Decimal)); dtbSubReportData.Columns.Add(COMPAREPERCENT); /// calculate the 2 Compare Column foreach (DataRow drow in dtbSubReportData.Rows) { drow[COMPARESECOND] = (decimal)drow[STANDARDCAPACITY] - decSumOfRequireCapacity; if (decSumOfRequireCapacity != Decimal.Zero) { decimal decPercentValue = ((decimal)drow[STANDARDCAPACITY] * 100) / decSumOfRequireCapacity; /// Percent drow[COMPAREPERCENT] = decPercentValue.ToString("#,##0.00") + "%"; } } #endregion #region RENDER REPORT ReportWithSubReportBuilder objRB = new ReportWithSubReportBuilder(); objRB.ReportName = REPORT_NAME; objRB.SourceDataTable = dtbSourceData; objRB.SubReportDataSources.Add(SUB_REPORT_NAME, dtbSubReportData); objRB.ReportDefinitionFolder = mstrReportDefinitionFolder; objRB.ReportLayoutFile = REPORT_LAYOUT_FILE; objRB.UseLayoutFile = true; objRB.MakeDataTableForRender(); // and show it in preview dialog PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog(); printPreview.FormTitle = REPORT_NAME; //Attach report viewer objRB.ReportViewer = printPreview.ReportViewer; objRB.RenderReport(); #region MODIFY THE REPORT LAYOUT objRB.DrawBoxGroup_Madeby_Checkedby_Approvedby(objRB.GetSectionByName("Header"), 100, 100, 1300, 1500, 200); objRB.DrawPredefinedField(REPORTFLD_WORKINGDAYS, nWorkingDays.ToString()); objRB.DrawPredefinedField(REPORTFLD_OFFDAYS, nOffDays.ToString()); #region PUSH PARAMETER VALUE objRB.DrawPredefinedField(REPORTFLD_PARAMETER_CCN, strCCN); objRB.DrawPredefinedField(REPORTFLD_PARAMETER_YEAR, nYear.ToString("0000")); objRB.DrawPredefinedField(REPORTFLD_PARAMETER_MONTH, nMonth.ToString("00")); objRB.DrawPredefinedField(REPORTFLD_PARAMETER_CYCLE, strCycle); objRB.DrawPredefinedField(REPORTFLD_PARAMETER_PRODUCTIONLINE, strProductionLine); #endregion #endregion objRB.RefreshReport(); printPreview.Show(); #endregion UseReportViewerRenderEngine = false; mResult = dtbSourceData; return(dtbSourceData); }