/// <summary> /// Thachnn: 28/10/2005 /// Preview the report for this form /// Using the "InventoryStatusSummaryByMasterLocationReport.xml" layout /// </summary> /// <history>Thachnn: 29/12/2005: Add parameter display to the report. Change USECASE.</history> /// <param name="sender"></param> /// <param name="e"></param> public DataTable ExecuteReport(string pstrCCNID, string pstrMasterLocationID, string pstrCategoryID, string pstrParameterModel) { #region Constants string mstrReportDefFolder = mstrReportDefinitionFolder; const string REPORT_LAYOUT_FILE = "InventoryStatusSummaryByMasterLocationReport.xml"; const string TABLE_NAME = "InventoryStatusSummaryByMasterLocationReport"; const string REPORT_NAME = "Inventory Status Summary By Master Location"; const string PAGE = "Page"; const string HEADER = "Header"; const string REPORTFLD_TITLE = "fldTitle"; const string REPORTFLD_COMPANY = "fldCompany"; const string REPORTFLD_ADDRESS = "fldAddress"; const string REPORTFLD_TEL = "fldTel"; const string REPORTFLD_FAX = "fldFax"; const string REPORTFLD_DAY = "fldDay"; const string REPORTFLD_MONTH = "fldMonth"; const string REPORTFLD_YEAR = "fldYear"; const string REPORTFLD_CATEGORY = "fldCategory"; const string REPORTFLD_PARTNUMBER = "fldPartNumber"; const string REPORTFLD_PARTNAME = "fldPartName"; const string REPORTFLD_MODEL = "fldModel"; const string REPORTFLD_STOCKUM = "fldStockUM"; const string REPORTFLD_LOCATION = "fldLocation"; const string REPORTFLD_OHQTY = "fldOHQty"; const string REPORTFLD_COMMITQTY = "fldCommitQty"; const string REPORTFLD_AVAILABLEQTY = "fldAvailableQty"; const string REPORTFLD_TYPE = "fldType"; const string REPORTFLD_SOURCE = "fldSource"; const string REPORTFLD_SAFETYSTOCK = "fldSafetyStock"; const string REPORTFLD_LOT = "fldLot"; const string REPORTFLD_WARNING = "fldWarning"; #region QUERY COLUMMS const string CATEGORY_COL = "[Category]"; const string PARTNUMBER_COL = "[Part No.]"; const string PARTNAME_COL = "[Part Name]"; const string MODEL_COL = "[Model]"; const string STOCKUM_COL = "[Stock UM]"; const string LOCATION_COL = "[Location]"; const string OHQTY_COL = "[OH Qty]"; const string COMMITQTY_COL = "[Commit Qty]"; const string AVAILABLEQTY_COL = "[Available Qty]"; const string TYPE_COL = "[Type]"; const string SOURCE_COL = "[Source]"; const string SAFETYSTOCK_COL = "[Safety Stock]"; const string LOT_COL = "[Lot]"; const string WARNING_COL = "[Warning]"; #endregion #endregion #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(); string strCCN = boUtil.GetCCNCodeFromID(int.Parse(pstrCCNID)); string strMasterLocation = objBO.GetMasterLocationCodeFromID(int.Parse(pstrMasterLocationID)) + ": " + objBO.GetMasterLocationNameFromID(int.Parse(pstrMasterLocationID)); string strCategory = string.Empty; try { strCategory = objBO.GetCategoryCodeFromID(pstrCategoryID); } catch {} #endregion float fActualPageSize = 9000f; #region Build dtbResult DataTable DataTable dtbResult; try { dtbResult = GetInventoryStatusData(pstrCCNID, pstrMasterLocationID, pstrCategoryID, pstrParameterModel); } catch { dtbResult = new DataTable(); } #endregion ReportBuilder objRB; objRB = new ReportBuilder(); objRB.ReportName = REPORT_NAME; objRB.SourceDataTable = dtbResult; #region INIT REPORT BUIDER OBJECT try { objRB.ReportDefinitionFolder = mstrReportDefinitionFolder; objRB.ReportLayoutFile = REPORT_LAYOUT_FILE; if (objRB.AnalyseLayoutFile() == false) { // PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND, MessageBoxIcon.Error); return(new DataTable()); } //objRB.UseLayoutFile = objRB.AnalyseLayoutFile(); // use layout file if any , auto drawing if not found layout file objRB.UseLayoutFile = true; // always use layout file } catch { objRB.UseLayoutFile = false; // PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND,MessageBoxIcon.Error); } C1.C1Report.Layout objLayout = objRB.Report.Layout; fActualPageSize = objLayout.PageSize.Width - (float)objLayout.MarginLeft - (float)objLayout.MarginRight; #endregion objRB.MakeDataTableForRender(); //grid.DataSource = objRB.RenderDataTable; #region RENDER TO PRINT PREVIEW // and show it in preview dialog PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog(); printPreview.FormTitle = REPORT_NAME; objRB.ReportViewer = printPreview.ReportViewer; objRB.RenderReport(); #region COMPANY INFO header information get from system params // try // { // objRB.DrawPredefinedField(REPORTFLD_COMPANY,SystemProperty.SytemParams.Get(SystemParam.COMPANY_NAME)); // } // catch{} // try // { // objRB.DrawPredefinedField(REPORTFLD_ADDRESS,SystemProperty.SytemParams.Get(SystemParam.ADDRESS)); // } // catch{} // try // { // objRB.DrawPredefinedField(REPORTFLD_TEL,SystemProperty.SytemParams.Get(SystemParam.TEL)); // } // catch{} // try // { // objRB.DrawPredefinedField(REPORTFLD_FAX,SystemProperty.SytemParams.Get(SystemParam.FAX)); // } // catch{} #endregion #region DRAW Parameters const string CCN = "CCN"; const string MASTER_LOCATION = "Master Location"; const string LOCATION = "Location"; const string CATEGORY = "Category"; const string MODEL = "Model"; System.Collections.Specialized.NameValueCollection arrParamAndValue = new System.Collections.Specialized.NameValueCollection(); arrParamAndValue.Add(CCN, strCCN); arrParamAndValue.Add(MASTER_LOCATION, strMasterLocation); if (pstrCategoryID.Trim() != string.Empty) { arrParamAndValue.Add(CATEGORY, strCategory); } if (pstrParameterModel.Trim() != string.Empty) { arrParamAndValue.Add(MODEL, pstrParameterModel); } /// anchor the Parameter drawing canvas cordinate to the fldTitle C1.C1Report.Field fldTitle = objRB.GetFieldByName(REPORTFLD_TITLE); double dblStartX = fldTitle.Left; double dblStartY = fldTitle.Top + 1.3 * fldTitle.RenderHeight; objRB.GetSectionByName(PAGE + HEADER).CanGrow = true; objRB.DrawParameters(objRB.GetSectionByName(PAGE + HEADER), dblStartX, dblStartY, arrParamAndValue, objRB.Report.Font.Size); #endregion /// there are some hardcode numbers here /// but these numbers are use ONLY ONE and ONLY USED HERE, so we don't need to define constant for it. objRB.DrawBoxGroup_Madeby_Checkedby_Approvedby(objRB.GetSectionByName(PAGE + HEADER), 16005 - 1400 - 1400 - 1400, 600, 1400, 1300, 200); #region DAY--MONTH--YEAR INFO DateTime dtm; try { dtm = objUtilBO.GetDBDate(); } catch { dtm = DateTime.Now; } try { objRB.DrawPredefinedField(REPORTFLD_DAY, dtm.Day.ToString("00")); } catch {} try { objRB.DrawPredefinedField(REPORTFLD_MONTH, dtm.Month.ToString("00")); } catch {} try { objRB.DrawPredefinedField(REPORTFLD_YEAR, dtm.Year.ToString("0000")); } catch {} #endregion try // mapping report field with table column { objRB.DrawPredefinedField(REPORTFLD_CATEGORY, CATEGORY_COL); objRB.DrawPredefinedField(REPORTFLD_PARTNUMBER, PARTNUMBER_COL); objRB.DrawPredefinedField(REPORTFLD_PARTNAME, PARTNAME_COL); objRB.DrawPredefinedField(REPORTFLD_MODEL, MODEL_COL); objRB.DrawPredefinedField(REPORTFLD_STOCKUM, STOCKUM_COL); objRB.DrawPredefinedField(REPORTFLD_LOCATION, LOCATION_COL); objRB.DrawPredefinedField(REPORTFLD_OHQTY, OHQTY_COL); objRB.DrawPredefinedField(REPORTFLD_COMMITQTY, COMMITQTY_COL); objRB.DrawPredefinedField(REPORTFLD_AVAILABLEQTY, AVAILABLEQTY_COL); objRB.DrawPredefinedField(REPORTFLD_TYPE, TYPE_COL); objRB.DrawPredefinedField(REPORTFLD_SOURCE, SOURCE_COL); objRB.DrawPredefinedField(REPORTFLD_SAFETYSTOCK, SAFETYSTOCK_COL); objRB.DrawPredefinedField(REPORTFLD_LOT, LOT_COL); objRB.DrawPredefinedField(REPORTFLD_WARNING, WARNING_COL); } catch {} objRB.RefreshReport(); printPreview.Show(); #endregion UseReportViewerRenderEngine = false; mResult = dtbResult; return(dtbResult); }
/// <summary> /// Main function, generate the result data Table for the REPORT VIEWER /// </summary> /// <param name="pstrCCNID"></param> /// <returns></returns> public DataTable ExecuteReport(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 = "WorkingSchemeReport.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] = decSumOfRequireCapacity - (decimal)drow[STANDARDCAPACITY]; if ((decimal)drow[STANDARDCAPACITY] != Decimal.Zero) { decimal decPercentValue = (decSumOfRequireCapacity * 100) / ((decimal)drow[STANDARDCAPACITY]); /// 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(); //Attach report viewer objRB.ReportViewer = printPreview.ReportViewer; objRB.RenderReport(); /* #region INIT REPORT BUIDER OBJECT * try * { * objRB.ReportDefinitionFolder = mstrReportDefinitionFolder; * objRB.ReportLayoutFile = REPORT_LAYOUT_FILE; * if(objRB.AnalyseLayoutFile() == false) * { * return new DataTable(); * } * //objRB.UseLayoutFile = objRB.AnalyseLayoutFile(); // use layout file if any , auto drawing if not found layout file * objRB.UseLayoutFile = true; // always use layout file * } * catch * { * objRB.UseLayoutFile = false; * } #endregion */ // objRB.MakeDataTableForRender(); // and show it in preview dialog // PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog(); // printPreview.FormTitle = REPORT_NAME; // objRB.ReportViewer = printPreview.ReportViewer; // objRB.RenderReport(); /// render the parent report // /// render the subreport // try // { // C1.C1Report.Field fldSubReport = objRB.GetFieldByName(SUB_REPORT_NAME); // // fldSubReport.Subreport.Load(mstrReportDefinitionFolder + "\\" + REPORT_LAYOUT_FILE, SUB_REPORT_NAME); // fldSubReport.Subreport.DataSource.Recordset = dtbSubReportData; // fldSubReport.Subreport.Render(); // } // catch // { // System.Windows.Forms.MessageBox.Show("Can't get field " + SUB_REPORT_NAME); // } #region MODIFY THE REPORT LAYOUT 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(); /// force the copies number printPreview.FormTitle = objRB.GetFieldByName("fldTitle").Text; printPreview.Show(); #endregion UseReportViewerRenderEngine = false; mResult = dtbSourceData; return(dtbSourceData); }
/// <summary> /// Thachnn: 28/10/2005 /// Preview the report for this form /// Using the "InventoryDetailReport.xml" layout /// </summary> /// <history>Thachnn: 29/12/2005: Add parameter display to the report. Change USECASE.</history> /// <param name="sender"></param> /// <param name="e"></param> public DataTable ExecuteReport(string pstrCCNID, string pstrMasterLocationID, string pstrLocationID, string pstrBinID, string pstrCategoryID, string pstrParameterModel) { #region Constants string mstrReportDefFolder = mstrReportDefinitionFolder; const string REPORT_LAYOUT_FILE = "InventoryDetailReport.xml"; const string TABLE_NAME = "InventoryDetailReport"; const string REPORT_NAME = "Inventory Detail"; const string PAGE = "Page"; const string HEADER = "Header"; const string REPORTFLD_TITLE = "fldTitle"; const string REPORTFLD_COMPANY = "fldCompany"; const string REPORTFLD_ADDRESS = "fldAddress"; const string REPORTFLD_TEL = "fldTel"; const string REPORTFLD_FAX = "fldFax"; const string REPORTFLD_DATETIME = "fldDateTime"; #endregion #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(); string strCCN = boUtil.GetCCNCodeFromID(int.Parse(pstrCCNID)); string strMasterLocation = objBO.GetMasterLocationCodeFromID(int.Parse(pstrMasterLocationID)) + ": " + objBO.GetMasterLocationNameFromID(int.Parse(pstrMasterLocationID)); string strLocation = string.Empty; try { strLocation = objBO.GetLocationCodeFromID(int.Parse(pstrLocationID)); } catch {} string strBin = string.Empty; try { strBin = objBO.GetBinCodeFromID(int.Parse(pstrBinID)); } catch {} string strCategory = string.Empty; try { strCategory = objBO.GetCategoryCodeFromID(int.Parse(pstrCategoryID)); } catch {} #endregion float fActualPageSize = 9000f; #region Build dtbResult DataTable DataTable dtbResult; try { dtbResult = GetInventoryDetailData(pstrCCNID, pstrMasterLocationID, pstrLocationID, pstrBinID, pstrCategoryID, pstrParameterModel); } catch { dtbResult = new DataTable(); } #endregion ReportBuilder objRB; objRB = new ReportBuilder(); objRB.ReportName = REPORT_NAME; objRB.SourceDataTable = dtbResult; #region INIT REPORT BUIDER OBJECT try { objRB.ReportDefinitionFolder = mstrReportDefinitionFolder; objRB.ReportLayoutFile = REPORT_LAYOUT_FILE; if (objRB.AnalyseLayoutFile() == false) { // PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND, MessageBoxIcon.Error); return(new DataTable()); } //objRB.UseLayoutFile = objRB.AnalyseLayoutFile(); // use layout file if any , auto drawing if not found layout file objRB.UseLayoutFile = true; // always use layout file } catch { objRB.UseLayoutFile = false; // PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND,MessageBoxIcon.Error); } C1.C1Report.Layout objLayout = objRB.Report.Layout; fActualPageSize = objLayout.PageSize.Width - (float)objLayout.MarginLeft - (float)objLayout.MarginRight; #endregion objRB.MakeDataTableForRender(); //grid.DataSource = objRB.RenderDataTable; #region RENDER TO PRINT PREVIEW // and show it in preview dialog PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog(); printPreview.FormTitle = REPORT_NAME; objRB.ReportViewer = printPreview.ReportViewer; objRB.RenderReport(); #region COMPANY INFO header information get from system params // try // { // objRB.DrawPredefinedField(REPORTFLD_COMPANY,SystemProperty.SytemParams.Get(SystemParam.COMPANY_NAME)); // } // catch{} // try // { // objRB.DrawPredefinedField(REPORTFLD_ADDRESS,SystemProperty.SytemParams.Get(SystemParam.ADDRESS)); // } // catch{} // try // { // objRB.DrawPredefinedField(REPORTFLD_TEL,SystemProperty.SytemParams.Get(SystemParam.TEL)); // } // catch{} // try // { // objRB.DrawPredefinedField(REPORTFLD_FAX,SystemProperty.SytemParams.Get(SystemParam.FAX)); // } // catch{} #endregion #region DRAW Parameters const string CCN = "CCN"; const string MASTER_LOCATION = "Master Location"; const string LOCATION = "Location"; const string BIN = "Bin"; const string CATEGORY = "Category"; const string MODEL = "Model"; System.Collections.Specialized.NameValueCollection arrParamAndValue = new System.Collections.Specialized.NameValueCollection(); arrParamAndValue.Add(CCN, strCCN); arrParamAndValue.Add(MASTER_LOCATION, strMasterLocation); if (pstrLocationID.Trim() != string.Empty) { arrParamAndValue.Add(LOCATION, strLocation); } if (pstrBinID.Trim() != string.Empty) { arrParamAndValue.Add(BIN, strBin); } if (pstrCategoryID.Trim() != string.Empty) { arrParamAndValue.Add(CATEGORY, strCategory); } if (pstrParameterModel.Trim() != string.Empty) { arrParamAndValue.Add(MODEL, pstrParameterModel); } /// anchor the Parameter drawing canvas cordinate to the fldTitle C1.C1Report.Field fldTitle = objRB.GetFieldByName(REPORTFLD_TITLE); double dblStartX = fldTitle.Left; double dblStartY = fldTitle.Top + 1.3 * fldTitle.RenderHeight; objRB.GetSectionByName(PAGE + HEADER).CanGrow = true; objRB.DrawParameters(objRB.GetSectionByName(PAGE + HEADER), dblStartX, dblStartY, arrParamAndValue, objRB.Report.Font.Size); #endregion /// there are some hardcode numbers here /// but these numbers are use ONLY ONE and ONLY USED HERE, so we don't need to define constant for it. objRB.DrawBoxGroup_Madeby_Checkedby_Approvedby(objRB.GetSectionByName(PAGE + HEADER), 15945 - 1400 - 1400 - 1400, 600, 1400, 1300, 200); #region DAY--MONTH--YEAR INFO DateTime dtm; try { dtm = objUtilBO.GetDBDate(); } catch { dtm = DateTime.Now; } try { objRB.DrawPredefinedField(REPORTFLD_DATETIME, dtm.ToString("dd-MM-yyyy hh:mm")); } catch {} #endregion objRB.RefreshReport(); printPreview.Show(); #endregion UseReportViewerRenderEngine = false; mResult = dtbResult; return(dtbResult); }
/// <summary> /// Main function, generate the result data Table for the REPORT VIEWER /// Modify the REPORT VIEWER to display the report /// ------------------------------ MAIN FLOW OF THIS REPORT ---------------------------------- /// BUILD THE PO LIST TABLE /// /// GETTING RAW DATA USING DataHelper Class /// FILL TO THE DTB TABLE /// /// RENDER TO REPORT /// /// SHOW THE REPORT /// END -------------------------- MAIN FLOW OF THIS REPORT ---------------------------------- /// </summary> /// <param name="pstrYear"></param> /// <param name="pstrMonth"></param> /// <param name="pstrPPMDefault"></param> /// <param name="pstrPPMDefault">Base Item</param> /// <returns></returns> public DataTable ExecuteReport(string pstrCCNID, string pstrPurchaseOrderID_LIST, string pstrPartyID_LIST, string pstrProductID_LIST) { #region My variables /// Report layout file constant const string REPORT_LAYOUT_FILE = "PurchaseOrderManagementReport.xml"; const string REPORT_NAME = "PurchaseOrderManagementReport"; short COPIES = 1; /// all parameter are Mandatory const string REPORTFLD_PARAMETER_CCN = "lblCCN"; const string REPORTFLD_PARAMETER_PURCHASEORDER = "lblPurchaseOrder"; const string REPORTFLD_PARAMETER_MAKER = "lblMaker"; const string REPORTFLD_PARAMETER_PRODUCT = "lblProduct"; PurchaseOrderManagementReport.pnCCNID = int.Parse(pstrCCNID); PurchaseOrderManagementReport.pstrPurchaseOrderID_LIST = pstrPurchaseOrderID_LIST; PurchaseOrderManagementReport.pstrPartyID_LIST = pstrPartyID_LIST; PurchaseOrderManagementReport.pstrProductID_LIST = pstrProductID_LIST; string strCCN = string.Empty; string strPurchaseOrderLIST = string.Empty; string strPartyLIST = string.Empty; string strProductLIST = string.Empty; const string REPORTFLD_TITLE = "fldTitle"; float fActualPageSize = 9000.0f; /// custom object to access and modify the dtbPurchaseOrderList PLReportDataHelper objPLTable = new PLReportDataHelper(); #endregion /// ------------------------------ MAIN FLOW OF THIS REPORT ---------------------------------- /// ##1## BUILD THE PRODUCTION LINE LIST TABLE #region BUILD some RAW DATA TABLE and Array objPLTable.GetDataAndCache(); #endregion BUILD some RAW DATA TABLE #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(PurchaseOrderManagementReport.pnCCNID); strPurchaseOrderLIST = GetListFromDataTable(objPLTable.GetDataTable(1)); strPartyLIST = GetListFromDataTable(objPLTable.GetDataTable(2)); strProductLIST = GetListFromDataTable(objPLTable.GetDataTable(3)); #endregion #region BUILD DATA OF REPORT #endregion BUILD DATA OF REPORT /// #### RENDER TO REPORT #region RENDER REPORT ReportBuilder objRB = mReportBuilder; objRB.ReportName = REPORT_NAME; objRB.SourceDataTable = objPLTable.PurchaseOrderList; // we build report base on HashTable, not DataTable, so we put new empty DataTable in to ReportBuilder to avoid error of outside processing //return objPLTable.PurchaseOrderList; #region INIT REPORT BUIDER OBJECT try { objRB.ReportDefinitionFolder = mstrReportDefinitionFolder; objRB.ReportLayoutFile = REPORT_LAYOUT_FILE; if (objRB.AnalyseLayoutFile() == false) { // PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND, MessageBoxIcon.Error); return(new DataTable()); } //objRB.UseLayoutFile = objRB.AnalyseLayoutFile(); // use layout file if any , auto drawing if not found layout file objRB.UseLayoutFile = true; // always use layout file } catch { objRB.UseLayoutFile = false; // PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND,MessageBoxIcon.Error); } C1.C1Report.Layout objLayout = objRB.Report.Layout; fActualPageSize = objLayout.PageSize.Width - (float)objLayout.MarginLeft - (float)objLayout.MarginRight; #endregion objRB.MakeDataTableForRender(); // and show it in preview dialog PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog(); printPreview.FormTitle = REPORT_NAME; objRB.ReportViewer = printPreview.ReportViewer; objRB.RenderReport(); #region MODIFY THE REPORT LAYOUT #region DRAW Parameters const int MAX_PARA_LENGTH_TO_SHOW = 150; System.Collections.Specialized.NameValueCollection arrParamAndValue = new System.Collections.Specialized.NameValueCollection(); arrParamAndValue.Add(objRB.GetFieldByName(REPORTFLD_PARAMETER_CCN).Text, strCCN); if (strPurchaseOrderLIST.Length > 100) { strPurchaseOrderLIST = strPurchaseOrderLIST.Substring(0, MAX_PARA_LENGTH_TO_SHOW) + " ..."; } if (strPartyLIST.Length > 100) { strPartyLIST = strPartyLIST.Substring(0, MAX_PARA_LENGTH_TO_SHOW) + " ..."; } if (strProductLIST.Length > 100) { strProductLIST = strProductLIST.Substring(0, MAX_PARA_LENGTH_TO_SHOW) + " ..."; } if (pstrPurchaseOrderID_LIST != string.Empty) { arrParamAndValue.Add(objRB.GetFieldByName(REPORTFLD_PARAMETER_PURCHASEORDER).Text, strPurchaseOrderLIST); } if (pstrPartyID_LIST != string.Empty) { arrParamAndValue.Add(objRB.GetFieldByName(REPORTFLD_PARAMETER_MAKER).Text, strPartyLIST); } if (pstrProductID_LIST != string.Empty) { arrParamAndValue.Add(objRB.GetFieldByName(REPORTFLD_PARAMETER_PRODUCT).Text, strProductLIST); } /// anchor the Parameter drawing canvas cordinate to the fldTitle C1.C1Report.Field fldTitle = objRB.GetFieldByName(REPORTFLD_TITLE); double dblStartX = fldTitle.Left; double dblStartY = fldTitle.Top + 1.3 * fldTitle.RenderHeight; objRB.GetSectionByName(PAGE + HEADER).CanGrow = true; objRB.DrawParameters(objRB.GetSectionByName(PAGE + HEADER), dblStartX, dblStartY, arrParamAndValue, objRB.Report.Font.Size); #endregion #endregion // ReportBuilder.ReformatNumberInC1Report(objRB.Report); objRB.MarkRedToNegativeNumberField(); objRB.RefreshReport(); /// #### SHOW THE REPORT /// force the copies number printPreview.FormTitle = objRB.GetFieldByName(FLD + TITLE).Text; printPreview.Show(); #endregion /// END ----------------------- MAIN FLOW OF THIS REPORT ---------------------------------- UseReportViewerRenderEngine = false; mResult = objPLTable.PurchaseOrderList; return(objPLTable.PurchaseOrderList); }