public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrMonth, string pstrCurrencyID, string pstrExRate) { // start of month DateTime dtmStartOfMonth = new DateTime(Convert.ToInt32(pstrYear), Convert.ToInt32(pstrMonth), 1); // end of month DateTime dtmEndOfMonth = dtmStartOfMonth.AddMonths(1).AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(59); DataTable dtbData = GetInvoice(pstrCCNID, dtmStartOfMonth, dtmEndOfMonth, pstrExRate); #region report C1Report rptReport = new C1Report(); mLayoutFile = "PurchaseReportImportPartByMaker.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A4; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldMonth"].Text = dtmStartOfMonth.ToString("MMM-yyyy"); } catch {} try { rptReport.Fields["fldCurrency"].Text = GetCurrency(pstrCurrencyID); } catch {} try { rptReport.Fields["fldExRate"].Text = pstrExRate; } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Purchase Report Import Part By Maker"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
public DataTable ExecuteReport(string pstrPeriodID, string pstrMasterLocationID, string pstrLocationID, string pstrBinID) { #region report table DataTable dtbData = GetReportData(pstrPeriodID, pstrLocationID, pstrBinID); #endregion #region build report data DataTable dtbStockTaking = GetStockTakingData(pstrPeriodID, pstrLocationID, pstrBinID); dtbData.Columns.Add(new DataColumn(DIFPOS_COL, typeof(decimal))); dtbData.Columns.Add(new DataColumn(DIFNEV_COL, typeof(decimal))); dtbData.Columns.Add(new DataColumn(METHOD_COL, typeof(string))); dtbData.Columns.Add(new DataColumn(SLIPCODE_COL, typeof(string))); int intCountPos = 0, intCountNev = 0; decimal decQtyCheck = 0, intNumPos = 0, intNumNev = 0; // calculate different and fill counting method foreach (DataRow drowData in dtbData.Rows) { string strLocationID = drowData["LocationID"].ToString(); string strBinID = drowData["BinID"].ToString(); string strProductID = drowData["ProductID"].ToString(); string strSlipCode; string strMethod = GetCountingMethod(strLocationID, strBinID, strProductID, dtbStockTaking, out strSlipCode); drowData[METHOD_COL] = strMethod; drowData[SLIPCODE_COL] = strSlipCode; decimal decOHQuantity = 0, decActual = 0; try { decOHQuantity = Convert.ToDecimal(drowData[OH_COL]); } catch {} try { decActual = Convert.ToDecimal(drowData[ACTUAL_COL]); } catch {} if (decActual - decOHQuantity > 0) { drowData[DIFPOS_COL] = decActual - decOHQuantity; intCountPos++; intNumPos += decActual - decOHQuantity; } else if (decActual - decOHQuantity < 0) { drowData[DIFNEV_COL] = decActual - decOHQuantity; intCountNev++; intNumNev += decActual - decOHQuantity; } decQtyCheck += decOHQuantity; } #endregion #region report C1Report rptReport = new C1Report(); mLayoutFile = "SuperviseReport.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A3; #region Report constant const string PARAM_PERIOD = "fldParamPeriod"; const string PARAM_MASLOC = "fldParamMasLoc"; const string PARAM_LOC = "fldParamLocation"; const string PARAM_BIN = "fldParamBin"; const string COUNTPOS_FLD = "fldA"; const string COUNTNEV_FLD = "fldB"; const string NUMPOS_FLD = "fldC"; const string NUMNEV_FLD = "fldD"; const string NUMCHECK_FLD = "fldNumCheck"; const string QTYCHECK_FLD = "fldQtyCheck"; const string RATECOUNT_FLD = "fldRateCount"; const string RATEQTY_FLD = "fldRateQuantity"; #endregion #region report parameter DataRow drowPeriodInfo = GetPeriod(pstrPeriodID); try { rptReport.Fields[PARAM_PERIOD].Text = drowPeriodInfo["Description"].ToString(); } catch {} try { rptReport.Fields[STOCKTAKINGDATE_FLD].Text = Convert.ToDateTime(drowPeriodInfo["FromDate"]).ToString("dd-MM-yyyy"); } catch {} try { rptReport.Fields[PARAM_MASLOC].Text = GetMasLoc(pstrMasterLocationID); } catch {} try { rptReport.Fields[PARAM_LOC].Text = GetLocation(pstrLocationID); } catch {} try { rptReport.Fields[PARAM_BIN].Text = GetBin(pstrBinID); } catch {} try { rptReport.Fields[COUNTPOS_FLD].Text = intCountPos.ToString(); } catch {} try { rptReport.Fields[COUNTNEV_FLD].Text = intCountNev.ToString(); } catch {} try { rptReport.Fields[NUMPOS_FLD].Text = intNumPos.ToString(); } catch {} try { rptReport.Fields[NUMNEV_FLD].Text = intNumNev.ToString(); } catch {} try { rptReport.Fields[NUMCHECK_FLD].Text = dtbData.Rows.Count.ToString(); } catch {} try { rptReport.Fields[QTYCHECK_FLD].Text = decQtyCheck.ToString(); } catch {} try { // rate count = num dif/num check if (dtbData.Rows.Count > 0) { rptReport.Fields[RATECOUNT_FLD].Text = "fldNumDif / fldNumCheck"; } } catch {} try { if (decQtyCheck != 0) { rptReport.Fields[RATEQTY_FLD].Text = "fldQtyDif / fldQtyCheck"; } } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Supervise Report"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
/// <summary> /// Build and show Local Receiving Material /// </summary> /// <author> Tuan TQ, 11 Jun, 2006</author> public DataTable ExecuteReport(string pstrCCNID, string pstrMasLocID, string pstrFromDate, string pstrToDate, string pstrCurrencyID, string pstrExchangeRate, string pstrProductIDList, string pstrVendorIDList ) { const string DATE_HOUR_FORMAT = "dd-MM-yyyy HH:mm"; const string NUMERIC_FORMAT = "#,##0.00"; const string REPORT_TEMPLATE = "LocalReceivingMaterialReport.xml"; const string RPT_PAGE_HEADER = "PageHeader"; const string REPORT_NAME = "LocalReceivingMaterialReport"; const string RPT_TITLE_FLD = "fldTitle"; const string RPT_COMPANY_FLD = "fldCompany"; const string RPT_CCN_FLD = "CCN"; const string RPT_MASTER_LOCATION_FLD = "Master Location"; const string RPT_FROM_DATE_FLD = "From Date"; const string RPT_TO_DATE_FLD = "To Date"; const string RPT_PART_NUMBER_FLD = "Part Number"; const string RPT_CURRENCY_FLD = "Currency"; const string RPT_EXCHANGE_RATE_FLD = "Exchange Rate"; const string RPT_VENDOR_FLD = "Vendor"; DataTable dtbDataSource = null; string strHomeCurrency = GetHomeCurrency(pstrCCNID); DateTime dtmFromDate = DateTime.MinValue; DateTime dtmToDate = DateTime.MinValue; try { dtmFromDate = Convert.ToDateTime(pstrFromDate); } catch {} try { dtmToDate = Convert.ToDateTime(pstrToDate); } catch {} #region build table schema dtbDataSource = new DataTable(); dtbDataSource.Columns.Add(new DataColumn("PartyCode", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("PartyName", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("ProductID", typeof(int))); dtbDataSource.Columns.Add(new DataColumn("PartNo", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("PartName", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("PartModel", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("BuyingUM", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("CategoryCode", typeof(string))); dtbDataSource.Columns.Add(new DataColumn("ReceiveQuantity", typeof(decimal))); dtbDataSource.Columns.Add(new DataColumn("Amount", typeof(decimal))); dtbDataSource.Columns.Add(new DataColumn("VATAmount", typeof(decimal))); dtbDataSource.Columns.Add(new DataColumn("ReturnedQuantity", typeof(decimal))); dtbDataSource.Columns["ReturnedQuantity"].AllowDBNull = true; dtbDataSource.Columns.Add(new DataColumn("ReturnedAmount", typeof(decimal))); dtbDataSource.Columns["ReturnedAmount"].AllowDBNull = true; dtbDataSource.Columns.Add(new DataColumn("ReturnedVATAmount", typeof(decimal))); dtbDataSource.Columns["ReturnedVATAmount"].AllowDBNull = true; #endregion // get receive data DataTable dtbReceipt = GetReceiptData(pstrCCNID, pstrMasLocID, dtmFromDate, dtmToDate, pstrProductIDList, pstrCurrencyID, pstrExchangeRate, pstrVendorIDList); // get return data DataTable dtbReturn = GetReturnData(pstrCCNID, pstrMasLocID, dtmFromDate, dtmToDate, pstrProductIDList, pstrCurrencyID, pstrExchangeRate, pstrVendorIDList); // list of party ArrayList arrParty = GetPartyIDs(pstrCCNID, pstrMasLocID, dtmFromDate, dtmToDate, pstrProductIDList, pstrVendorIDList); foreach (string strPartyID in arrParty) { // item of current party DataRow[] drowsItem = dtbReceipt.Select("PartyID = " + strPartyID, "ProductID ASC"); string strLastProductID = string.Empty; foreach (DataRow drowItem in drowsItem) { if (strLastProductID != drowItem["ProductID"].ToString()) { strLastProductID = drowItem["ProductID"].ToString(); string strFilter = "PartyID = " + strPartyID + " AND ProductID = " + strLastProductID; #region general information DataRow drowResult = dtbDataSource.NewRow(); drowResult["PartyCode"] = drowItem["PartyCode"]; drowResult["PartyName"] = drowItem["PartyName"]; drowResult["PartNo"] = drowItem["PartNo"]; drowResult["PartName"] = drowItem["PartName"]; drowResult["PartModel"] = drowItem["PartModel"]; drowResult["BuyingUM"] = drowItem["BuyingUM"]; drowResult["CategoryCode"] = drowItem["CategoryCode"]; drowResult["ProductID"] = drowItem["ProductID"]; #endregion #region receive data decimal decReceiveQuantity = 0, decAmount = 0, decVATAmount = 0; try { decReceiveQuantity = Convert.ToDecimal(dtbReceipt.Compute("SUM(ReceiveQuantity)", strFilter)); } catch {} try { decAmount = Convert.ToDecimal(dtbReceipt.Compute("SUM(Amount)", strFilter)); } catch {} try { decVATAmount = Convert.ToDecimal(dtbReceipt.Compute("SUM(VATAmount)", strFilter)); } catch {} drowResult["ReceiveQuantity"] = decReceiveQuantity; drowResult["Amount"] = decAmount; drowResult["VATAmount"] = decVATAmount; #endregion #region return data decimal decReturnedQuantity = 0, decReturnedAmount = 0, decReturnedVATAmount = 0; try { decReturnedQuantity = Convert.ToDecimal(dtbReturn.Compute("SUM(Quantity)", strFilter)); } catch {} try { decReturnedAmount = Convert.ToDecimal(dtbReturn.Compute("SUM(Amount)", strFilter)); } catch {} try { decReturnedVATAmount = Convert.ToDecimal(dtbReturn.Compute("SUM(VATAmount)", strFilter)); } catch {} if (decReturnedQuantity != 0) { drowResult["ReturnedQuantity"] = decReturnedQuantity; } if (decReturnedAmount != 0) { drowResult["ReturnedAmount"] = decReturnedAmount; } if (decReturnedVATAmount != 0) { drowResult["ReturnedVATAmount"] = decReturnedVATAmount; } #endregion // insert to result table dtbDataSource.Rows.Add(drowResult); } } } //Create builder object ReportWithSubReportBuilder reportBuilder = new ReportWithSubReportBuilder(); //Set report name reportBuilder.ReportName = REPORT_NAME; //Set Datasource reportBuilder.SourceDataTable = dtbDataSource; //Set report layout location reportBuilder.ReportDefinitionFolder = mstrReportDefinitionFolder; reportBuilder.ReportLayoutFile = REPORT_TEMPLATE; reportBuilder.UseLayoutFile = true; reportBuilder.MakeDataTableForRender(); // and show it in preview dialog C1PrintPreviewDialog printPreview = new C1PrintPreviewDialog(); //Attach report viewer reportBuilder.ReportViewer = printPreview.ReportViewer; reportBuilder.RenderReport(); reportBuilder.DrawPredefinedField(RPT_COMPANY_FLD, GetCompanyFullName()); //Draw parameters NameValueCollection arrParamAndValue = new NameValueCollection(); arrParamAndValue.Add(RPT_CCN_FLD, GetCCNInfoByID(pstrCCNID)); arrParamAndValue.Add(RPT_MASTER_LOCATION_FLD, GetMasterLocationInfoByID(pstrMasLocID)); Hashtable htbCurrency = GetCurrencyInfo(pstrCurrencyID); if (htbCurrency != null) { arrParamAndValue.Add(RPT_CURRENCY_FLD, htbCurrency[PRODUCT_CODE_FLD].ToString()); if (strHomeCurrency == pstrCurrencyID) { arrParamAndValue.Add(RPT_EXCHANGE_RATE_FLD, decimal.One.ToString(NUMERIC_FORMAT)); } else { if (pstrExchangeRate != string.Empty && pstrExchangeRate != null) { arrParamAndValue.Add(RPT_EXCHANGE_RATE_FLD, decimal.Parse(pstrExchangeRate).ToString(NUMERIC_FORMAT)); } else { arrParamAndValue.Add(RPT_EXCHANGE_RATE_FLD, decimal.Parse(htbCurrency[EXCHANGE_RATE_FLD].ToString()).ToString(NUMERIC_FORMAT)); } } } if (pstrFromDate != null && pstrFromDate != string.Empty) { arrParamAndValue.Add(RPT_FROM_DATE_FLD, DateTime.Parse(pstrFromDate).ToString(DATE_HOUR_FORMAT)); } if (pstrToDate != null && pstrToDate != string.Empty) { arrParamAndValue.Add(RPT_TO_DATE_FLD, DateTime.Parse(pstrToDate).ToString(DATE_HOUR_FORMAT)); } if (pstrProductIDList != null && pstrProductIDList != string.Empty) { arrParamAndValue.Add(RPT_PART_NUMBER_FLD, GetProductInfo(pstrProductIDList)); } if (pstrVendorIDList != null && pstrVendorIDList != string.Empty) { arrParamAndValue.Add(RPT_VENDOR_FLD, GetPartyInfo(pstrVendorIDList)); } //Anchor the Parameter drawing canvas cordinate to the fldTitle Field fldTitle = reportBuilder.GetFieldByName(RPT_TITLE_FLD); double dblStartX = fldTitle.Left; double dblStartY = fldTitle.Top + 1.3 * fldTitle.RenderHeight; reportBuilder.GetSectionByName(RPT_PAGE_HEADER).CanGrow = true; reportBuilder.DrawParameters(reportBuilder.GetSectionByName(RPT_PAGE_HEADER), dblStartX, dblStartY, arrParamAndValue, reportBuilder.Report.Font.Size); try { printPreview.FormTitle = reportBuilder.GetFieldByName(RPT_TITLE_FLD).Text; } catch { } reportBuilder.RefreshReport(); printPreview.Show(); //return table return(dtbDataSource); }
public DataTable ExecuteReport(string pstrMonth, string pstrProductID, string pstrMakeItem, string pstrProductType) { int intMakeItem = -1; if (pstrMakeItem != null && pstrMakeItem != string.Empty) { intMakeItem = Convert.ToInt32(Convert.ToBoolean(pstrMakeItem)); } DataTable dtbReportData = GetReportData(pstrMonth, pstrProductID, intMakeItem, pstrProductType); C1Report rptReport = new C1Report(); if (mLayoutFile == string.Empty) { mLayoutFile = "CostingDescription.xml"; } rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A3; #region report parameter DateTime dtmDate = Convert.ToDateTime(pstrMonth); try { rptReport.Fields["fldMonth"].Text = dtmDate.ToString("MMMM-yyyy"); } catch {} if (pstrProductID != null && pstrProductID.Length > 0) { string strPartNo = string.Empty, strPartName = string.Empty; DataTable dtbItem = GetItemsInfo(pstrProductID); foreach (DataRow drowItem in dtbItem.Rows) { strPartNo += drowItem["Code"].ToString() + ", "; strPartName += drowItem["Description"].ToString() + ", "; } // remove the last "," if (strPartNo.IndexOf(",") >= 0) { strPartNo = strPartNo.Substring(0, strPartNo.Length - 2); } if (strPartName.IndexOf(",") >= 0) { strPartName = strPartName.Substring(0, strPartName.Length - 2); } try { rptReport.Fields["fldPartParam"].Text = strPartNo; rptReport.Fields["fldPartNameParam"].Text = strPartName; } catch {} } #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbReportData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Costing Description " + dtmDate.ToString("MMMM-yyyy"); ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); return(dtbReportData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrCurrencyID, string pstrExRate) { DataTable dtbInvoice = GetInvoice(pstrCCNID, pstrYear); DataTable dtbCharge = GetAdditionalCharge(pstrCCNID, pstrYear); decimal decExRate = Convert.ToDecimal(pstrExRate); #region report table DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn("MyMonth", typeof(string))); dtbData.Columns.Add(new DataColumn("Quantity", typeof(decimal))); dtbData.Columns.Add(new DataColumn("EXGO", typeof(decimal))); dtbData.Columns.Add(new DataColumn("CIP", typeof(decimal))); dtbData.Columns.Add(new DataColumn("ImportTax", typeof(decimal))); #endregion #region data for (int i = 1; i <= 12; i++) { DateTime dtmDate = new DateTime(Convert.ToInt32(pstrYear), i, 1); DataRow drowReport = dtbData.NewRow(); string strFilter = "SMonth = '" + i.ToString() + "'"; decimal decCIP = 0; try { decCIP = Convert.ToDecimal(dtbInvoice.Compute("SUM(CIP)", strFilter)); } catch {} drowReport["CIP"] = decimal.Round(decCIP / decExRate, 2); // quantity from po invoice decimal decQuantity = 0; try { decQuantity = Convert.ToDecimal(dtbInvoice.Compute("SUM(Quantity)", strFilter)); } catch {} drowReport["Quantity"] = decQuantity; // EX-GO from po invoice decimal decEXGO = 0; try { decEXGO = Convert.ToDecimal(dtbInvoice.Compute("SUM(EXGO)", strFilter)); } catch {} drowReport["EXGO"] = decimal.Round(decEXGO / decExRate, 2); // Import Tax from invoice decimal decImportTax = 0; try { decImportTax = Convert.ToDecimal(dtbInvoice.Compute("SUM(ImportTaxAmount)", strFilter)); } catch {} // import tax from additional charge try { decImportTax += Convert.ToDecimal(dtbCharge.Compute("SUM(ImportTaxAmount)", strFilter)); } catch {} drowReport["ImportTax"] = decimal.Round(decImportTax / decExRate, 2); // Month drowReport["MyMonth"] = dtmDate.ToString("MMMM"); // insert to result table dtbData.Rows.Add(drowReport); } #endregion #region report C1Report rptReport = new C1Report(); mLayoutFile = "PurchaseReportImportPartByMonth.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A4; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldMonth"].Text = pstrYear; } catch {} try { rptReport.Fields["fldCurrency"].Text = GetCurrency(pstrCurrencyID); } catch {} try { rptReport.Fields["fldExRate"].Text = pstrExRate; } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Purchase Report Import Part By Month"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrMonth, string pstrMapTarget, string pstrTolerance) { // start of month DateTime dtmStartOfMonth = new DateTime(Convert.ToInt32(pstrYear), Convert.ToInt32(pstrMonth), 1); // end of month DateTime dtmEndOfMonth = dtmStartOfMonth.AddMonths(1).AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(59); // delivery times of all makers DataTable dtbDeliveryTimes = GetDeliveryTimesOfAllMakers(pstrCCNID, dtmStartOfMonth, dtmEndOfMonth); // wrong times of all makers DataTable dtbWrongTimes = GetWrongTimesOfAllMakers(pstrCCNID, pstrTolerance, dtmStartOfMonth, dtmEndOfMonth); #region report table DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn("Supplier", typeof(string))); dtbData.Columns.Add(new DataColumn(DELIVERY_TIMES, typeof(int))); dtbData.Columns.Add(new DataColumn(WRONG_TIMES, typeof(int))); dtbData.Columns.Add(new DataColumn("Quantity", typeof(decimal))); dtbData.Columns.Add(new DataColumn("MapTarget", typeof(decimal))); dtbData.Columns["MapTarget"].DefaultValue = Convert.ToDecimal(pstrMapTarget); dtbData.Columns.Add(new DataColumn("PPM", typeof(decimal))); dtbData.Columns["PPM"].DefaultValue = decimal.Zero; #endregion string strLastMakerID = string.Empty; // each maker will be one row in table foreach (DataRow drowData in dtbDeliveryTimes.Rows) { if (strLastMakerID == drowData[MAKER_ID].ToString()) { continue; } strLastMakerID = drowData[MAKER_ID].ToString(); DataRow drowReportData = dtbData.NewRow(); string strFilter = MAKER_ID + "='" + strLastMakerID + "'"; // delivery times DataRow[] drowDeliveryTimes = dtbDeliveryTimes.Select(strFilter); drowReportData[DELIVERY_TIMES] = drowDeliveryTimes.Length; // wrong times DataRow[] drowWrongTimes = dtbWrongTimes.Select(strFilter); drowReportData[WRONG_TIMES] = drowWrongTimes.Length; // supplier code drowReportData["Supplier"] = drowData["Supplier"]; // quantity try { drowReportData["Quantity"] = Convert.ToDecimal(dtbDeliveryTimes.Compute("SUM(Quantity)", strFilter)); } catch {} // ppm = wrong / delivery * 1000 try { double dPPM = (Convert.ToDouble(drowWrongTimes.Length) / Convert.ToDouble(drowDeliveryTimes.Length)) * 1000000; drowReportData["PPM"] = dPPM; } catch {} dtbData.Rows.Add(drowReportData); } #region report C1Report rptReport = new C1Report(); mLayoutFile = "DeliveryAsSupplier.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A4; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldMonth"].Text = dtmStartOfMonth.ToString("MMM-yyyy"); } catch {} try { rptReport.Fields["fldMapTarget"].Text = pstrMapTarget; } catch {} try { rptReport.Fields["fldTolerance"].Text = pstrTolerance; } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "DELIVERY EVALUATION AS SUPPLIER"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrCategoryID, string pstrDepartmentID, string pstrProductionLineID, string pstrModel, string pstrProductID) { #region create table schema DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn(CATEGORY_FLD, typeof(string))); dtbData.Columns.Add(new DataColumn(MODEL_FLD, typeof(string))); dtbData.Columns.Add(new DataColumn(PART_NO_FLD, typeof(string))); dtbData.Columns.Add(new DataColumn(PART_NAME_FLD, typeof(string))); dtbData.Columns.Add(new DataColumn(UM_FLD, typeof(string))); dtbData.Columns.Add(new DataColumn(COST_ELEMENT_FLD, typeof(string))); dtbData.Columns.Add(new DataColumn(PRODUCTID_FLD, typeof(string))); // create 3 columns for each month for (int i = 1; i <= 12; i++) { dtbData.Columns.Add(new DataColumn(STDCOST_FLD + i.ToString("00"), typeof(decimal))); dtbData.Columns.Add(new DataColumn(ACTUAL_COST_FLD + i.ToString("00"), typeof(decimal))); dtbData.Columns.Add(new DataColumn(DIFFERENCE_FLD + i.ToString("00"), typeof(decimal))); } #endregion // list all periods DataTable dtbAllPeriod = GetAllPeriod(pstrCCNID); // list all elements DataTable dtbElements = GetCostElements(); // list all products DataTable dtbAllItems = ListItems(pstrCCNID); // build the periods id which pass thru selected year StringBuilder sbPeriods = new StringBuilder(); sbPeriods.Append("0"); foreach (DataRow drowData in dtbAllPeriod.Rows) { DateTime dtmFromDate = DateTime.Parse(drowData[FROMDATE_FLD].ToString()); DateTime dtmToDate = DateTime.Parse(drowData[TODATE_FLD].ToString()); for (DateTime dtmDate = dtmFromDate; dtmDate <= dtmToDate; dtmDate = dtmDate.AddYears(1)) { if (dtmDate.Year == int.Parse(pstrYear)) { sbPeriods.Append("," + drowData[PERIODID_FLD].ToString()); break; } } } // gets temp report data DataTable dtbReportData = GetReportData(pstrCCNID, sbPeriods.ToString(), pstrCategoryID, pstrDepartmentID, pstrProductionLineID, pstrProductID, pstrModel); // cost center rate data DataTable dtbRate = GetCostCenterRate(pstrCCNID); // standard cost DataTable dtbSTDCost = GetSTDCost(); // allocation DataTable dtbAllocation = GetAllocation(); // get list of product in report data ArrayList arrProducts = new ArrayList(); foreach (DataRow drowData in dtbReportData.Rows) { string strProductID = drowData[PRODUCTID_FLD].ToString(); if (!arrProducts.Contains(strProductID)) { arrProducts.Add(strProductID); } } // loops thru the product list to build original data foreach (string strProductID in arrProducts) { DataRow drowProductInfo = GetItemInfo(strProductID, dtbAllItems); foreach (DataRow drowElement in dtbElements.Rows) { // each element will appears as one row in report DataRow drowReport = dtbData.NewRow(); drowReport[PRODUCTID_FLD] = strProductID; string strElementID = drowElement["CostElementID"].ToString(); int intElementType = Convert.ToInt32(drowElement["TypeCode"]); decimal decCostRate = 0; string strFilterRate = "CostCenterRateMasterID = " + drowProductInfo["CostCenterRateMasterID"].ToString() + " AND CostElementID = " + strElementID; try { decCostRate = Convert.ToDecimal(dtbRate.Compute("SUM(Cost)", strFilterRate)); } catch {} for (int i = 1; i <= 12; i++) { DateTime dtmMonth = new DateTime(Convert.ToInt32(pstrYear), i, 1); // find the period if of current month string strCurrentPeriodID = FindPeriodOfMonth(dtmMonth, dtbAllPeriod); // current month does not have any period if (strCurrentPeriodID == string.Empty) { drowReport[CATEGORY_FLD] = drowProductInfo[CATEGORY_FLD]; drowReport[MODEL_FLD] = drowProductInfo[MODEL_FLD]; drowReport[PART_NO_FLD] = drowProductInfo[PART_NO_FLD]; drowReport[PART_NAME_FLD] = drowProductInfo[PART_NAME_FLD]; drowReport[UM_FLD] = drowProductInfo[UM_FLD]; drowReport[COST_ELEMENT_FLD] = drowElement["Name"]; #region standard cost decimal decSTDCost = 0; switch (intElementType) { case (int)CostElementType.Material: // item standard cost string strFilterSTD = "ProductID = " + strProductID; try { decSTDCost = Convert.ToDecimal(dtbSTDCost.Compute("SUM(Cost)", strFilterSTD)); } catch {} decSTDCost = decSTDCost + decCostRate; break; default: // std cost = cost rate decSTDCost = decCostRate; break; } if (decSTDCost != 0) { drowReport[STDCOST_FLD + i.ToString("00")] = decSTDCost; drowReport[DIFFERENCE_FLD + i.ToString("00")] = decSTDCost; } else { drowReport[STDCOST_FLD + i.ToString("00")] = DBNull.Value; drowReport[DIFFERENCE_FLD + i.ToString("00")] = DBNull.Value; } #endregion drowReport[ACTUAL_COST_FLD + i.ToString("00")] = DBNull.Value; drowReport[DIFFERENCE_FLD + i.ToString("00")] = DBNull.Value; } else { string strFilter = PRODUCTID_FLD + "='" + strProductID + "'" + " AND ActCostAllocationMasterID ='" + strCurrentPeriodID + "'" + " AND CostElementID = '" + strElementID + "'"; DataRow[] drowsElement = dtbReportData.Select(strFilter); if (drowsElement.Length > 0) { drowReport[CATEGORY_FLD] = drowsElement[0][CATEGORY_FLD]; drowReport[MODEL_FLD] = drowsElement[0][MODEL_FLD]; drowReport[PART_NO_FLD] = drowsElement[0][PART_NO_FLD]; drowReport[PART_NAME_FLD] = drowsElement[0][PART_NAME_FLD]; drowReport[UM_FLD] = drowsElement[0][UM_FLD]; drowReport[COST_ELEMENT_FLD] = drowsElement[0][COST_ELEMENT_FLD]; decimal decSTDCost = 0, decActualCost = 0, decQuantity = 0; decimal decBeginQuantity = 0, decPreviousActCost = 0, decAlloc = 0; decimal decSumComponentValue = 0, decComponentValue = 0; #region standard cost switch (intElementType) { case (int)CostElementType.Material: // item standard cost string strFilterSTD = "ProductID = " + strProductID; try { decSTDCost = Convert.ToDecimal(dtbSTDCost.Compute("SUM(Cost)", strFilterSTD)); } catch {} decSTDCost = decSTDCost + decCostRate; break; default: // std cost = cost rate decSTDCost = decCostRate; break; } if (decSTDCost != 0) { drowReport[STDCOST_FLD + i.ToString("00")] = decSTDCost; drowReport[DIFFERENCE_FLD + i.ToString("00")] = decSTDCost; } else { drowReport[STDCOST_FLD + i.ToString("00")] = DBNull.Value; drowReport[DIFFERENCE_FLD + i.ToString("00")] = DBNull.Value; } #endregion #region Calculate actual cost // quantity of current period try { decQuantity = Convert.ToDecimal(drowsElement[0]["Quantity"]); } catch {} // begin quantity of current period try { decBeginQuantity = Convert.ToDecimal(drowsElement[0]["BeginQuantity"]); } catch {} // allocation amount string strAllocFilter = "ProductID = " + strProductID + " AND CostElementID = " + strElementID; try { decAlloc = Convert.ToDecimal(dtbAllocation.Compute("SUM(Amount)", strAllocFilter)); } catch {} switch (intElementType) { case (int)CostElementType.Material: // // actual cost of current preiod // try // { // decActualCost = Convert.ToDecimal(drowsElement[0]["ActualCost"]); // } // catch{} // // #region actual cost of previous period // try // { // decPreviousActCost = Convert.ToDecimal(drowsElement[0]["BeginCost"]); // } // catch{} // #endregion // // // (CST_ActualCostHistory.ElementType.ActualCost* CST_ActualCostHistory.Qty- // // PreviousPeriod.CST_ActualCostHistory.ElementType.ActualCost* CST_ActualCostHistory.BeginQty )/ // // (CST_ActualCostHistory.Qty- CST_ActualCostHistory.BeginQty) // try // { // decActualCost = (decActualCost*decQuantity - decPreviousActCost*decBeginQuantity)/ // (decQuantity - decBeginQuantity); // } // catch (DivideByZeroException) // { // decActualCost = 0; // } // if (decQuantity == 0) // decActualCost = 0; string strComFilter = "ProductID = " + strProductID + " AND ActCostAllocationMasterID = " + strCurrentPeriodID; try { decSumComponentValue = Convert.ToDecimal(dtbReportData.Compute("SUM(ComponentValue)", strComFilter)); } catch {} // component value of current preiod try { decComponentValue = Convert.ToDecimal(drowsElement[0]["ComponentValue"]); } catch {} // Actual Cost = CST_ActualCostHistory.Material+ Sum(CST_ActualCostHistory.ElementType.ComponentValue) // - CST_ActualCostHistory. Material .ComponentValue- //decActualCost = decActualCost + decSumComponentValue - decComponentValue; decActualCost = decSumComponentValue; break; default: if (decQuantity == 0) { decActualCost = 0; } else { try { decActualCost = decAlloc / (decQuantity - decBeginQuantity); } catch (DivideByZeroException) { decActualCost = 0; } } break; } #endregion if (decActualCost != 0) { drowReport[ACTUAL_COST_FLD + i.ToString("00")] = decActualCost; } else { drowReport[ACTUAL_COST_FLD + i.ToString("00")] = DBNull.Value; } if ((decSTDCost - decActualCost) != 0) { drowReport[DIFFERENCE_FLD + i.ToString("00")] = decSTDCost - decActualCost; } else { drowReport[DIFFERENCE_FLD + i.ToString("00")] = DBNull.Value; } } else { drowReport[CATEGORY_FLD] = drowProductInfo[CATEGORY_FLD]; drowReport[MODEL_FLD] = drowProductInfo[MODEL_FLD]; drowReport[PART_NO_FLD] = drowProductInfo[PART_NO_FLD]; drowReport[PART_NAME_FLD] = drowProductInfo[PART_NAME_FLD]; drowReport[UM_FLD] = drowProductInfo[UM_FLD]; drowReport[COST_ELEMENT_FLD] = drowElement["Name"]; #region standard cost decimal decSTDCost = 0; switch (intElementType) { case (int)CostElementType.Material: // item standard cost string strFilterSTD = "ProductID = " + strProductID + " AND CostElementID = " + strElementID; try { decSTDCost = Convert.ToDecimal(dtbSTDCost.Compute("SUM(Cost)", strFilterSTD)); } catch {} decSTDCost = decSTDCost + decCostRate; break; default: // std cost = cost rate decSTDCost = decCostRate; break; } if (decSTDCost != 0) { drowReport[STDCOST_FLD + i.ToString("00")] = decSTDCost; drowReport[DIFFERENCE_FLD + i.ToString("00")] = decSTDCost; } else { drowReport[STDCOST_FLD + i.ToString("00")] = DBNull.Value; drowReport[DIFFERENCE_FLD + i.ToString("00")] = DBNull.Value; } #endregion drowReport[ACTUAL_COST_FLD + i.ToString("00")] = DBNull.Value; } } } // loop year // insert to report data dtbData.Rows.Add(drowReport); } // loop elements } //loop products // report layout C1Report rptReport = new C1Report(); if (mLayoutFile == null || mLayoutFile.Trim() == string.Empty) { mLayoutFile = "StdActCostComparision.xml"; } string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mDefFolder + "\\" + mLayoutFile); rptReport.Load(mDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]); //arrstrReportInDefinitionFile = null; rptReport.Layout.PaperSize = PaperKind.A3; #region PUSH PARAMETER VALUE #region General information try { rptReport.Fields["fldCompany"].Text = SystemProperty.SytemParams.Get("CompanyFullName"); } catch {} try { rptReport.Fields["fldCCN"].Text = GetCCNCode(pstrCCNID);; } catch {} try { rptReport.Fields["fldYear"].Text = pstrYear; } catch {} #endregion #region Category if (pstrCategoryID != null && pstrCategoryID.Trim().Length > 0) { try { rptReport.Fields["fldCategoryParam"].Text = GetCategory(pstrCategoryID); } catch {} } #endregion #region Department if (pstrDepartmentID != null && pstrDepartmentID.Trim().Length > 0) { try { rptReport.Fields["fldDepartmentParam"].Text = GetDepartment(pstrDepartmentID); } catch {} } #endregion #region Production Line if (pstrProductionLineID != null && pstrProductionLineID.Trim().Length > 0) { try { rptReport.Fields["fldProductionLine"].Text = GetProductionLineCode(pstrProductionLineID); } catch {} } #endregion #region Part if (pstrProductID != null && pstrProductID.Trim().Length > 0) { try { string strPartNo = string.Empty, strPartName = string.Empty; DataRow[] drowItems = GetItemsInfo(pstrProductID, dtbAllItems); foreach (DataRow drowItem in drowItems) { strPartNo += drowItem[PART_NO_FLD].ToString() + ","; strPartName += drowItem[PART_NAME_FLD].ToString() + ","; } // remove the last "," if (strPartNo.IndexOf(",") >= 0) { strPartNo = strPartNo.Substring(0, strPartNo.Length - 1); } if (strPartName.IndexOf(",") >= 0) { strPartName = strPartName.Substring(0, strPartName.Length - 1); } rptReport.Fields["fldPartNoParam"].Text = strPartNo; rptReport.Fields["fldPartNameParam"].Text = strPartName; } catch {} } #endregion #region Model if (pstrModel != null && pstrModel.Trim().Length > 0) { try { // refine Model string pstrModel = pstrModel.Replace("'", string.Empty); rptReport.Fields["fldModelParam"].Text = pstrModel; } catch {} } #endregion #region Home Currency try { rptReport.Fields["lblCurrency"].Text = rptReport.Fields["lblCurrency"].Text + GetHomeCurrency(pstrCCNID); } catch {} #endregion #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = " STANDARD COST & ACTUAL COST COMPARISON "; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); return(dtbData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrVendorID, string pstrProductID, string pstrPOMasterID, string pstrOrderDate) { DateTime dtmOrderDate = DateTime.MinValue; try { dtmOrderDate = Convert.ToDateTime(pstrOrderDate); dtmOrderDate = new DateTime(dtmOrderDate.Year, dtmOrderDate.Month, dtmOrderDate.Day); } catch {} #region Report table DataTable dtbReportData = new DataTable(); dtbReportData = GetReportData(pstrCCNID, pstrVendorID, pstrProductID, pstrPOMasterID, dtmOrderDate); #region calculate total order, total delivery and total balance quantity decimal decTotalOrder = 0, decTotalDelivery = 0, decTotalBalance = 0, decTotalInvoice = 0; int intDeliveryScheduleID = 0; foreach (DataRow drowData in dtbReportData.Rows) { try { decTotalInvoice += Convert.ToDecimal(drowData["InvoiceQuantity"]); } catch {} if (intDeliveryScheduleID == Convert.ToInt32(drowData["DeliveryScheduleID"])) { continue; } intDeliveryScheduleID = Convert.ToInt32(drowData["DeliveryScheduleID"]); try { decTotalOrder += Convert.ToDecimal(drowData["OrderQuantity"]); } catch {} try { decTotalDelivery += Convert.ToDecimal(drowData["DeliveryQuantity"]); } catch {} } decTotalBalance = decTotalInvoice - decTotalOrder; #endregion #endregion #region report C1Report rptReport = new C1Report(); mLayoutFile = "OrderBalanceForImportParts.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A3; #region total row try { rptReport.Fields["fldTotalOrder"].Text = decTotalOrder.ToString(); } catch {} try { rptReport.Fields["fldTotalDelivery"].Text = decTotalDelivery.ToString(); } catch {} try { rptReport.Fields["fldTotalInvoice"].Text = decTotalInvoice.ToString(); } catch {} try { rptReport.Fields["fldTotalBalance"].Text = decTotalBalance.ToString(); } catch {} #endregion #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { string strCode = string.Empty; rptReport.Fields["fldMonth"].Text = GetVendor(pstrVendorID, out strCode); try { rptReport.Fields["lblETA"].Text = rptReport.Fields["lblETA"].Text + " " + strCode; } catch {} } catch {} try { if (pstrProductID.Length > 0 && pstrProductID.Split(",".ToCharArray()).Length > 0) { rptReport.Fields["fldPartParam"].Text = "Multi-Selection"; } else if (pstrProductID.Length > 0) { rptReport.Fields["fldPartParam"].Text = GetItem(pstrProductID); } } catch {} try { if (pstrPOMasterID.Length > 0 && pstrPOMasterID.Split(",".ToCharArray()).Length > 0) { rptReport.Fields["fldPO"].Text = "Multi-Selection"; } else if (pstrPOMasterID.Length > 0) { rptReport.Fields["fldPO"].Text = GetPO(pstrPOMasterID); } } catch {} try { if (pstrOrderDate.Length > 0) { rptReport.Fields["fldOrderDate"].Text = dtmOrderDate.ToString("dd-MMM-yyyy"); } } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbReportData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Order Balance For Import Parts"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbReportData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrFromDate, string pstrToDate, string pstrLicensorID, string pstrMakeItem) { int intMakeItem = -1; if (pstrMakeItem != null && pstrMakeItem != string.Empty) { intMakeItem = Convert.ToInt32(Convert.ToBoolean(pstrMakeItem)); } DateTime dtmFromDate = Convert.ToDateTime(pstrFromDate); DateTime dtmToDate = Convert.ToDateTime(pstrToDate); #region report table DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn("Licensor", typeof(string))); dtbData.Columns.Add(new DataColumn("Model", typeof(string))); dtbData.Columns.Add(new DataColumn("PartNo", typeof(string))); dtbData.Columns.Add(new DataColumn("PartName", typeof(string))); dtbData.Columns.Add(new DataColumn("Quantity", typeof(decimal))); dtbData.Columns.Add(new DataColumn("Amount", typeof(decimal))); dtbData.Columns.Add(new DataColumn(CostElementType.Material.ToString(), typeof(decimal))); dtbData.Columns.Add(new DataColumn(CostElementType.SubMaterial.ToString(), typeof(decimal))); dtbData.Columns.Add(new DataColumn(CostElementType.Labor.ToString(), typeof(decimal))); dtbData.Columns.Add(new DataColumn(CostElementType.Machine.ToString(), typeof(decimal))); dtbData.Columns.Add(new DataColumn(CostElementType.OverHead.ToString(), typeof(decimal))); dtbData.Columns.Add(new DataColumn("CostOfGoodsSold", typeof(decimal))); #endregion #region build report data DataTable dtbReportData = GetReportData(pstrCCNID, dtmFromDate, dtmToDate, pstrLicensorID, intMakeItem); DataTable dtbActualCost = GetActualCost(pstrCCNID); DataTable dtbStdCost = GetStdCost(); DataTable dtbChargeAllocation = GetChargeAllocation(pstrCCNID); string strLastProductID = string.Empty; string strLastPartyID = string.Empty; foreach (DataRow drowData in dtbReportData.Rows) { // party id string strPartyID = drowData["PartyID"].ToString(); // product id string strProductID = drowData["ProductID"].ToString(); if (strLastProductID == strProductID && strLastPartyID == strPartyID) { continue; } strLastProductID = strProductID; strLastPartyID = strPartyID; DataRow drowReport = dtbData.NewRow(); drowReport["Licensor"] = drowData["Licensor"]; drowReport["Model"] = drowData["Model"]; drowReport["PartNo"] = drowData["PartNo"]; drowReport["PartName"] = drowData["PartName"]; string strFilter = "PartyID = '" + strPartyID + "' AND ProductID = '" + strProductID + "'"; decimal decQuantity = 0, decAmount = 0, decDSAmount = 0; decimal decRecycleAmount = 0, decAdjustAmount = 0; decimal decOHDSAmount = 0, decOHRecAmount = 0, decOHAdjAmount = 0; decimal decSumQuantity = 0, decCharge = 0, decRate = 0; decimal decRawCost = 0, decSubCost = 0, decLaborCost = 0, decMachineCost = 0, decOverCost = 0; try { decQuantity = Convert.ToDecimal(dtbReportData.Compute("SUM(Quantity)", strFilter)); } catch {} try { decSumQuantity = Convert.ToDecimal(dtbReportData.Compute("SUM(Quantity)", "ProductID = " + strProductID)); } catch {} decRate = decQuantity / decSumQuantity; try { decAmount = Convert.ToDecimal(dtbReportData.Compute("SUM(Amount)", strFilter)); } catch {} drowReport["Quantity"] = decQuantity; drowReport["Amount"] = decAmount; #region calculate cost of goods sold for each item // shipped date to determine which cost period will take effect here DateTime dtmShippedDate = (DateTime)drowData["ShippedDate"]; dtmShippedDate = new DateTime(dtmShippedDate.Year, dtmShippedDate.Month, dtmShippedDate.Day); // filter condition string strFilterCondition = "ProductID = '" + strProductID + "'" + " AND FromDate <= '" + dtmShippedDate.ToString("G") + "'" + " AND ToDate >= '" + dtmShippedDate.ToString("G") + "'"; #region Cost Of Goods Sold - Raw Material #region DS Amount try { decDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'")); } catch { decDSAmount = 0; } #endregion #region Recycle Amount try { decRecycleAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'")); } catch { decRecycleAmount = 0; } #endregion #region Adjust Amount try { decAdjustAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'")); } catch { decAdjustAmount = 0; } #endregion #region OH DS Amount try { decOHDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'")); } catch { decOHDSAmount = 0; } #endregion #region OH Recycle Amount try { decOHRecAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'")); } catch { decOHRecAmount = 0; } #endregion #region OH Adjust Amount try { decOHAdjAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'")); } catch { decOHAdjAmount = 0; } #endregion decCharge = (decDSAmount - decRecycleAmount - decAdjustAmount) + (decOHDSAmount - decOHRecAmount - decOHAdjAmount); DataRow[] drowActualCost = dtbActualCost.Select(strFilterCondition); if (drowActualCost.Length > 0) { try { string strFilterDetail = strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'"; decimal decActualCost = Convert.ToDecimal(dtbActualCost.Compute("SUM(ActualCost)", strFilterDetail)); decRawCost = decQuantity * decActualCost + decCharge * decRate; drowReport[CostElementType.Material.ToString()] = decRawCost; } catch {} } else { // if item not yet rollup actual cost, try to get standard cost string strFilterDetail = "ProductID = '" + strProductID + "' AND CostElementType = '" + ((int)CostElementType.Material).ToString() + "'"; DataRow[] drowStdCost = dtbStdCost.Select(strFilterDetail); if (drowStdCost.Length > 0) { try { decimal decStdCost = Convert.ToDecimal(dtbStdCost.Compute("SUM(Cost)", strFilterDetail)); decRawCost = decQuantity * decStdCost + decCharge * decRate; drowReport[CostElementType.Material.ToString()] = decRawCost; } catch {} } else // if item dot have any cost { drowReport[CostElementType.Material.ToString()] = decimal.Zero; } } #endregion #region Cost Of Goods Sold - Sub Material #region DS Amount try { decDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'")); } catch { decDSAmount = 0; } #endregion #region Recycle Amount try { decRecycleAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'")); } catch { decRecycleAmount = 0; } #endregion #region Adjust Amount try { decAdjustAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'")); } catch { decAdjustAmount = 0; } #endregion #region OH DS Amount try { decOHDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'")); } catch { decOHDSAmount = 0; } #endregion #region OH Recycle Amount try { decOHRecAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'")); } catch { decOHRecAmount = 0; } #endregion #region OH Adjust Amount try { decOHAdjAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'")); } catch { decOHAdjAmount = 0; } #endregion decCharge = (decDSAmount - decRecycleAmount - decAdjustAmount) + (decOHDSAmount - decOHRecAmount - decOHAdjAmount); if (drowActualCost.Length > 0) { try { string strFilterDetail = strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'"; decimal decActualCost = Convert.ToDecimal(dtbActualCost.Compute("SUM(ActualCost)", strFilterDetail)); decSubCost = decQuantity * decActualCost + decCharge * decRate; drowReport[CostElementType.SubMaterial.ToString()] = decSubCost; } catch {} } else { // if item not yet rollup actual cost, try to get standard cost string strFilterDetail = "ProductID = '" + strProductID + "' AND CostElementType = '" + ((int)CostElementType.SubMaterial).ToString() + "'"; DataRow[] drowStdCost = dtbStdCost.Select(strFilterDetail); if (drowStdCost.Length > 0) { try { decimal decStdCost = Convert.ToDecimal(dtbStdCost.Compute("SUM(Cost)", strFilterDetail)); decSubCost = decQuantity * decStdCost + decCharge * decRate; drowReport[CostElementType.SubMaterial.ToString()] = decSubCost; } catch {} } else // if item dot have any cost { drowReport[CostElementType.SubMaterial.ToString()] = decimal.Zero; } } #endregion #region Cost Of Goods Sold - Labor #region DS Amount try { decDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'")); } catch { decDSAmount = 0; } #endregion #region Recycle Amount try { decRecycleAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'")); } catch { decRecycleAmount = 0; } #endregion #region Adjust Amount try { decAdjustAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'")); } catch { decAdjustAmount = 0; } #endregion #region OH DS Amount try { decOHDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'")); } catch { decOHDSAmount = 0; } #endregion #region OH Recycle Amount try { decOHRecAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'")); } catch { decOHRecAmount = 0; } #endregion #region OH Adjust Amount try { decOHAdjAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'")); } catch { decOHAdjAmount = 0; } #endregion decCharge = (decDSAmount - decRecycleAmount - decAdjustAmount) + (decOHDSAmount - decOHRecAmount - decOHAdjAmount); if (drowActualCost.Length > 0) { try { string strFilterDetail = strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'"; decimal decActualCost = Convert.ToDecimal(dtbActualCost.Compute("SUM(ActualCost)", strFilterDetail)); decLaborCost = decQuantity * decActualCost + decCharge * decRate; drowReport[CostElementType.Labor.ToString()] = decLaborCost; } catch {} } else { // if item not yet rollup actual cost, try to get standard cost string strFilterDetail = "ProductID = '" + strProductID + "' AND CostElementType = '" + ((int)CostElementType.Labor).ToString() + "'"; DataRow[] drowStdCost = dtbStdCost.Select(strFilterDetail); if (drowStdCost.Length > 0) { try { decimal decStdCost = Convert.ToDecimal(dtbStdCost.Compute("SUM(Cost)", strFilterDetail)); decLaborCost = decQuantity * decStdCost + decCharge * decRate; drowReport[CostElementType.Labor.ToString()] = decLaborCost; } catch {} } else // if item dot have any cost { drowReport[CostElementType.Labor.ToString()] = decimal.Zero; } } #endregion #region Cost Of Goods Sold - Machine #region DS Amount try { decDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'")); } catch { decDSAmount = 0; } #endregion #region Recycle Amount try { decRecycleAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'")); } catch { decRecycleAmount = 0; } #endregion #region Adjust Amount try { decAdjustAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'")); } catch { decAdjustAmount = 0; } #endregion #region OH DS Amount try { decOHDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'")); } catch { decOHDSAmount = 0; } #endregion #region OH Recycle Amount try { decOHRecAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'")); } catch { decOHRecAmount = 0; } #endregion #region OH Adjust Amount try { decOHAdjAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'")); } catch { decOHAdjAmount = 0; } #endregion decCharge = (decDSAmount - decRecycleAmount - decAdjustAmount) + (decOHDSAmount - decOHRecAmount - decOHAdjAmount); if (drowActualCost.Length > 0) { try { string strFilterDetail = strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'"; decimal decActualCost = Convert.ToDecimal(dtbActualCost.Compute("SUM(ActualCost)", strFilterDetail)); decMachineCost = decQuantity * decActualCost + decCharge * decRate; drowReport[CostElementType.Machine.ToString()] = decMachineCost; } catch {} } else { // if item not yet rollup actual cost, try to get standard cost string strFilterDetail = "ProductID = '" + strProductID + "' AND CostElementType = '" + ((int)CostElementType.Machine).ToString() + "'"; DataRow[] drowStdCost = dtbStdCost.Select(strFilterDetail); if (drowStdCost.Length > 0) { try { decimal decStdCost = Convert.ToDecimal(dtbStdCost.Compute("SUM(Cost)", strFilterDetail)); decMachineCost = decQuantity * decStdCost + decCharge * decRate; drowReport[CostElementType.Machine.ToString()] = decMachineCost; } catch {} } else // if item dot have any cost { drowReport[CostElementType.Machine.ToString()] = decimal.Zero; } } #endregion #region Cost Of Goods Sold - Overhead #region DS Amount try { decDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'")); } catch { decDSAmount = 0; } #endregion #region Recycle Amount try { decRecycleAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'")); } catch { decRecycleAmount = 0; } #endregion #region Adjust Amount try { decAdjustAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'")); } catch { decAdjustAmount = 0; } #endregion #region OH DS Amount try { decOHDSAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_DSAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'")); } catch { decOHDSAmount = 0; } #endregion #region OH Recycle Amount try { decOHRecAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_RecycleAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'")); } catch { decOHRecAmount = 0; } #endregion #region OH Adjust Amount try { decOHAdjAmount = Convert.ToDecimal(dtbChargeAllocation.Compute("SUM(OH_AdjustAmount)", strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'")); } catch { decOHAdjAmount = 0; } #endregion decCharge = (decDSAmount - decRecycleAmount - decAdjustAmount) + (decOHDSAmount - decOHRecAmount - decOHAdjAmount); if (drowActualCost.Length > 0) { try { string strFilterDetail = strFilterCondition + " AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'"; decimal decActualCost = Convert.ToDecimal(dtbActualCost.Compute("SUM(ActualCost)", strFilterDetail)); decOverCost = decQuantity * decActualCost + decCharge * decRate; drowReport[CostElementType.OverHead.ToString()] = decOverCost; } catch {} } else { // if item not yet rollup actual cost, try to get standard cost string strFilterDetail = "ProductID = '" + strProductID + "' AND CostElementType = '" + ((int)CostElementType.OverHead).ToString() + "'"; DataRow[] drowStdCost = dtbStdCost.Select(strFilterDetail); if (drowStdCost.Length > 0) { try { decimal decStdCost = Convert.ToDecimal(dtbStdCost.Compute("SUM(Cost)", strFilterDetail)); decOverCost = decQuantity * decStdCost + decCharge * decRate; drowReport[CostElementType.OverHead.ToString()] = decOverCost; } catch {} } else // if item dot have any cost { drowReport[CostElementType.OverHead.ToString()] = decimal.Zero; } } #endregion #region Total Cost Of Goods Sold try { drowReport["CostOfGoodsSold"] = decRawCost + decSubCost + decMachineCost + decLaborCost + decOverCost; } catch {} #endregion #endregion dtbData.Rows.Add(drowReport); } #endregion #region report C1Report rptReport = new C1Report(); mLayoutFile = "SaleByLicensorBreakDown.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A3; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldFromDate"].Text = dtmFromDate.ToString("dd-MM-yyyy HH:mm"); } catch {} try { rptReport.Fields["fldToDate"].Text = dtmToDate.ToString("dd-MM-yyyy HH:mm"); } catch {} try { if (pstrLicensorID.Split(",".ToCharArray()).Length > 1) { rptReport.Fields["fldLic"].Text = "Multi-Selection"; } else if (pstrLicensorID.Length > 0) { rptReport.Fields["fldLic"].Text = GetCustomerInfo(pstrLicensorID); } } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Sale revenue and cost of goods sold break-down report (Classified By Licensors)"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrMakerID, string pstrYear, string pstrMonth, string pstrTolerance) { // start of month DateTime dtmStartOfMonth = new DateTime(Convert.ToInt32(pstrYear), Convert.ToInt32(pstrMonth), 1); // end of month DateTime dtmEndOfMonth = dtmStartOfMonth.AddMonths(1).AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(59); // makers DataTable dtbMaker = ListMaker(pstrCCNID); // delivery times of all makers DataTable dtbDeliveryTimes = GetDeliveryTimesOfAllMakers(pstrCCNID, dtmStartOfMonth, dtmEndOfMonth); // wrong times of all makers DataTable dtbWrongTimes = GetWrongTimesOfAllMakers(pstrCCNID, pstrTolerance, dtmStartOfMonth, dtmEndOfMonth); // seperate maker list string[] arrMakerList = pstrMakerID.Split(",".ToCharArray()); #region report table DataTable dtbReport = new DataTable(); DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn(PRODUCT_ID, typeof(int))); dtbData.Columns.Add(new DataColumn(PART_NO, typeof(string))); dtbData.Columns.Add(new DataColumn(PART_NAME, typeof(string))); dtbData.Columns.Add(new DataColumn(MODEL, typeof(string))); dtbData.Columns.Add(new DataColumn(CATEGORY, typeof(string))); dtbData.Columns.Add(new DataColumn(DELIVERY_TIMES, typeof(int))); dtbData.Columns.Add(new DataColumn(WRONG_TIMES, typeof(int))); dtbData.Columns.Add(new DataColumn("PPM", typeof(decimal))); dtbData.Columns["PPM"].DefaultValue = decimal.Zero; #endregion // each maker will appears as one report foreach (string strMakerID in arrMakerList) { #region data // list of item by maker string strMakerFilter = MAKER_ID + " = '" + strMakerID + "'"; DataRow[] drowsItem = dtbDeliveryTimes.Select(strMakerFilter, "PartNo ASC"); string strMakerCode = dtbMaker.Select(strMakerFilter)[0]["Code"].ToString() + " (" + dtbMaker.Select(strMakerFilter)[0]["Name"].ToString() + ")"; string strLastProductID = string.Empty; foreach (DataRow drowItem in drowsItem) { string strProductID = drowItem[PRODUCT_ID].ToString(); if (strLastProductID == strProductID) { continue; } strLastProductID = drowItem[PRODUCT_ID].ToString(); // each item will be one row in report DataRow drowReportData = dtbData.NewRow(); string strItemFilter = strMakerFilter + " AND ProductID = '" + drowItem[PRODUCT_ID].ToString() + "'"; // delivery times DataRow[] drowDeliveryTimes = dtbDeliveryTimes.Select(strItemFilter); drowReportData[DELIVERY_TIMES] = drowDeliveryTimes.Length; // wrong times #region Deleted by duongna //DataRow[] drowWrongTimes = dtbWrongTimes.Select(strItemFilter); //drowReportData[WRONG_TIMES] = drowWrongTimes.Length; #endregion // HACKED : duongna int intWrongTime = 0; foreach (DataRow drowDelivery in drowDeliveryTimes) { string strWrongTimeFilter = strItemFilter + " AND DeliveryScheduleID = " + drowDelivery["DeliveryScheduleID"]; DataRow[] arrWrongTimes = dtbWrongTimes.Select(strWrongTimeFilter); intWrongTime += (arrWrongTimes.Length > 0) ? 1 : 0; } drowReportData[WRONG_TIMES] = intWrongTime; // ppm = wrong / delivery * 1000 try { double dPPM = (Convert.ToDouble(intWrongTime) / Convert.ToDouble(drowDeliveryTimes.Length)) * 1000000; drowReportData["PPM"] = dPPM; } catch {} // general information // part id drowReportData[PRODUCT_ID] = drowItem[PRODUCT_ID]; // part no drowReportData[PART_NO] = drowDeliveryTimes[0][PART_NO]; // part name drowReportData[PART_NAME] = drowDeliveryTimes[0][PART_NAME]; // Model drowReportData[MODEL] = drowDeliveryTimes[0][MODEL]; // Category drowReportData[CATEGORY] = drowDeliveryTimes[0][CATEGORY]; dtbData.Rows.Add(drowReportData); } #endregion #region report C1Report rptReport = new C1Report(); mLayoutFile = "DeliveryAsItemReport.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A4; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldSupplier"].Text = strMakerCode; } catch {} try { rptReport.Fields["fldMonth"].Text = dtmStartOfMonth.ToString("MMM-yyyy"); } catch {} try { rptReport.Fields["fldTolerance"].Text = pstrTolerance; } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "DELIVERY EVALUATION AS ITEMS"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); dtbReport = dtbData.Copy(); dtbData.Clear(); #endregion } return(dtbReport); }
public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrMapTarget, string pstrTolerance) { // delivery times of all makers in a year DataTable dtbDeliveryTimes = GetDeliveryTimesOfAllMakers(pstrCCNID, pstrYear); // wrong times of all makers DataTable dtbWrongTimes = GetWrongTimesOfAllMakers(pstrCCNID, pstrTolerance, pstrYear); #region report table DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn("ScheduleMonth", typeof(string))); dtbData.Columns.Add(new DataColumn(DELIVERY_TIMES, typeof(int))); dtbData.Columns.Add(new DataColumn(WRONG_TIMES, typeof(int))); dtbData.Columns.Add(new DataColumn("MapTarget", typeof(decimal))); dtbData.Columns["MapTarget"].DefaultValue = Convert.ToDecimal(pstrMapTarget); dtbData.Columns.Add(new DataColumn("PPM", typeof(decimal))); dtbData.Columns["PPM"].DefaultValue = decimal.Zero; #endregion // each maker will be one row in table for (int i = 1; i <= 12; i++) { DateTime dtmDate = new DateTime(Convert.ToInt32(pstrYear), i, 1); DataRow drowReportData = dtbData.NewRow(); string strFilter = "ScheduleMonth = '" + i.ToString() + "'"; // delivery times DataRow[] drowDeliveryTimes = dtbDeliveryTimes.Select(strFilter); drowReportData[DELIVERY_TIMES] = drowDeliveryTimes.Length; // wrong times //DataRow[] drowWrongTimes = dtbWrongTimes.Select(strFilter); //drowReportData[WRONG_TIMES] = drowWrongTimes.Length; int intWrongTime = 0; foreach (DataRow drowDelivery in drowDeliveryTimes) { string strWrongTimeFilter = strFilter + " AND DeliveryScheduleID = " + drowDelivery["DeliveryScheduleID"]; DataRow[] arrWrongTimes = dtbWrongTimes.Select(strWrongTimeFilter); intWrongTime += (arrWrongTimes.Length > 0) ? 1 : 0; } drowReportData[WRONG_TIMES] = intWrongTime; // ppm = wrong / delivery * 1000 try { double dPPM = (Convert.ToDouble(intWrongTime) / Convert.ToDouble(drowDeliveryTimes.Length)) * 1000000; drowReportData["PPM"] = dPPM; } catch {} // Month drowReportData["ScheduleMonth"] = dtmDate.ToString("MMMM"); // insert to datasource dtbData.Rows.Add(drowReportData); } #region report C1Report rptReport = new C1Report(); mLayoutFile = "DeliveryAsMonth.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A4; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldMonth"].Text = pstrYear.ToString(); } catch {} try { rptReport.Fields["fldMapTarget"].Text = pstrMapTarget; } catch {} try { rptReport.Fields["fldTolerance"].Text = pstrTolerance; } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "DELIVERY EVALUATION AS MONTH"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrProductID) { int intYear = Convert.ToInt32(pstrYear); #region report table DataTable dtbData = new DataTable(); dtbData.Columns.Add(new DataColumn("Category", typeof(string))); dtbData.Columns.Add(new DataColumn("PartNo", typeof(string))); dtbData.Columns.Add(new DataColumn("PartName", typeof(string))); dtbData.Columns.Add(new DataColumn("Model", typeof(string))); for (int i = 1; i <= 12; i++) { DateTime dtmDate = new DateTime(intYear, i, 1); string strColName = "M" + dtmDate.Month.ToString(); dtbData.Columns.Add(new DataColumn(strColName, typeof(decimal))); } #endregion #region data DataTable dtbReportData = GetReportData(pstrCCNID, pstrYear, pstrProductID); string strLastProductID = string.Empty; foreach (DataRow drowData in dtbReportData.Rows) { string strProductID = drowData["ProductID"].ToString(); if (strLastProductID == strProductID) { continue; } strLastProductID = strProductID; string strFilter = "ProductID = '" + strProductID + "'"; DataRow[] drowProducts = dtbReportData.Select(strFilter, "SMonth ASC"); foreach (DataRow drowItem in drowProducts) { DataRow drowReport = dtbData.NewRow(); // general information drowReport["Category"] = drowItem["Category"]; drowReport["PartNo"] = drowItem["PartNo"]; drowReport["PartName"] = drowItem["PartName"]; drowReport["Model"] = drowItem["Model"]; int intMonth = Convert.ToInt32(drowItem["SMonth"]); DateTime dtmDate = new DateTime(intYear, intMonth, 1); string strColName = "M" + dtmDate.Month.ToString(); try { drowReport[strColName] = Convert.ToDecimal(drowItem["Price"]); } catch {} // insert to result table dtbData.Rows.Add(drowReport); } } #endregion #region report C1Report rptReport = new C1Report(); mLayoutFile = "PurchasingPriceTrendInYear.xml"; rptReport.Load(mReportFolder + "\\" + mLayoutFile, rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile)[0]); rptReport.Layout.PaperSize = PaperKind.A3; #region report parameter try { rptReport.Fields["fldCCN"].Text = GetCCN(pstrCCNID); } catch {} try { rptReport.Fields["fldMonth"].Text = pstrYear; } catch {} try { if (pstrProductID.Length > 0 && pstrProductID.Split(",".ToCharArray()).Length > 0) { rptReport.Fields["fldPartParam"].Text = "Multi-Selection"; } else if (pstrProductID.Length > 0) { rptReport.Fields["fldPartParam"].Text = GetPartNo(pstrProductID); } } catch {} #endregion // set datasource object that provides data to report. rptReport.DataSource.Recordset = dtbData; // render report rptReport.Render(); // render the report into the PrintPreviewControl C1PrintPreviewDialog ppvViewer = new C1PrintPreviewDialog(); ppvViewer.FormTitle = "Purchase Report Import Part By Month"; ppvViewer.ReportViewer.Document = rptReport.Document; ppvViewer.Show(); #endregion return(dtbData); }
public DataTable ExecuteReport(string pstrYear, string pstrMonth, string pstrPartyID, string pstrMakerID, string pstrCategoryID, string pstrProductID) { const string DATE_FLD = "lblD"; const string DAY_FLD = "lblDay"; const double FIELD_WIDTH = 500; DataTable dtbReportData = GetReportData(pstrYear, pstrMonth, pstrPartyID, pstrMakerID, pstrProductID, pstrCategoryID); DateTime dtmFromDate = new DateTime(Convert.ToInt32(pstrYear), Convert.ToInt32(pstrMonth), 1); DateTime dtmToDate = dtmFromDate.AddMonths(1).AddDays(-1); ArrayList arrOffDay = GetWorkingDayByYear(int.Parse(pstrYear)); ArrayList arrHolidays = GetHolidaysInYear(int.Parse(pstrYear)); #region Report Layout C1Report rptReport = new C1Report(); if (mLayoutFile == null || mLayoutFile.Trim() == string.Empty) { mLayoutFile = "DeliveryInMonth.xml"; } string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mReportFolder + "\\" + mLayoutFile); rptReport.Load(mReportFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]); rptReport.Layout.PaperSize = PaperKind.A3; for (DateTime dtmDay = dtmFromDate; dtmDay <= dtmToDate; dtmDay = dtmDay.AddDays(1)) { string strDate = DATE_FLD + dtmDay.Day.ToString("00"); string strDay = DAY_FLD + dtmDay.Day.ToString("00"); try { rptReport.Fields[strDate].Text = dtmDay.ToString("dd-MMM"); } catch {} try { rptReport.Fields[strDay].Text = dtmDay.DayOfWeek.ToString().Substring(0, 3); } catch {} if (arrOffDay.Contains(dtmDay.DayOfWeek) || arrHolidays.Contains(dtmDay)) { try { if (dtmDay.DayOfWeek == DayOfWeek.Saturday) { rptReport.Fields[strDate].ForeColor = Color.Blue; rptReport.Fields[strDate].BackColor = Color.Yellow; } else { rptReport.Fields[strDate].ForeColor = Color.Red; rptReport.Fields[strDate].BackColor = Color.Yellow; } } catch {} try { if (dtmDay.DayOfWeek == DayOfWeek.Saturday) { rptReport.Fields[strDay].ForeColor = Color.Blue; rptReport.Fields[strDay].BackColor = Color.Yellow; } else { rptReport.Fields[strDay].ForeColor = Color.Red; rptReport.Fields[strDay].BackColor = Color.Yellow; } } catch {} } } #endregion #region refine the report based on days in month int intDaysInMonth = DateTime.DaysInMonth(dtmFromDate.Year, dtmFromDate.Month); if (intDaysInMonth < 31) { for (int i = intDaysInMonth + 1; i <= 31; i++) { #region hide fields string strDate = "lblD" + i.ToString("00"); string strDayOfWeek = "lblDay" + i.ToString("00"); string strDiv = "div" + i.ToString("00"); string strDivDetail = "divData" + i.ToString("00"); string strDetail = "fldD" + i.ToString("00"); string strSum = "sum" + i.ToString("00"); string strDivMaker = "divMaker" + i.ToString("00"); string strSumVendor = "sumVendor" + i.ToString("00"); string strDivVendor = "divVendor" + i.ToString("00"); string strDivFtr = "divFtr" + i.ToString("00"); string strFtr = "fldFtr" + i.ToString("00"); try { rptReport.Fields[strSum].Visible = false; } catch {} try { rptReport.Fields[strDivMaker].Visible = false; } catch {} try { rptReport.Fields[strDate].Visible = false; } catch {} try { rptReport.Fields[strDayOfWeek].Visible = false; } catch {} try { rptReport.Fields[strDiv].Visible = false; } catch {} try { rptReport.Fields[strDivDetail].Visible = false; } catch {} try { rptReport.Fields[strDetail].Visible = false; } catch {} try { rptReport.Fields[strDivFtr].Visible = false; } catch {} try { rptReport.Fields[strFtr].Visible = false; } catch {} try { rptReport.Fields[strSumVendor].Visible = false; } catch {} try { rptReport.Fields[strDivVendor].Visible = false; } catch {} #endregion } try { #region resize and moving for (int i = 1; i < 8; i++) { rptReport.Fields["line" + i].Width = rptReport.Fields["line" + i].Width - (31 - intDaysInMonth) * FIELD_WIDTH; } double dLeft = rptReport.Fields["div" + dtmToDate.Day.ToString("00")].Left; // move the total field rptReport.Fields["lblTotal"].Left = dLeft; rptReport.Fields["divTotal"].Left = dLeft + rptReport.Fields["lblTotal"].Width; rptReport.Fields["fldTotal"].Left = dLeft; rptReport.Fields["divDataTotal"].Left = dLeft + rptReport.Fields["fldTotal"].Width; rptReport.Fields["sumTotal"].Left = dLeft; rptReport.Fields["divMakerTotal"].Left = dLeft + rptReport.Fields["sumTotal"].Width; rptReport.Fields["sumVendorTotal"].Left = dLeft; rptReport.Fields["divVendorTotal"].Left = dLeft + rptReport.Fields["sumVendorTotal"].Width; rptReport.Fields["fldFtrTotal"].Left = dLeft; rptReport.Fields["divFtrTotal"].Left = dLeft + rptReport.Fields["fldFtrTotal"].Width; #endregion } catch {} } #endregion rptReport.DataSource.Recordset = dtbReportData; C1PrintPreviewDialog printPreview = new C1PrintPreviewDialog(); const string REPORTFLD_PARAMETER_MONTH = "fldMonth"; const string REPORTFLD_PARAMETER_YEAR = "fldYear"; const string TITLE_FLD = "fldTitle"; try { rptReport.Fields[REPORTFLD_PARAMETER_MONTH].Text = dtmFromDate.ToString("MMM"); } catch {} try { rptReport.Fields[REPORTFLD_PARAMETER_YEAR].Text = pstrYear; } catch {} try { rptReport.Fields[TITLE_FLD].Text = rptReport.Fields[TITLE_FLD].Text + " " + int.Parse(pstrMonth).ToString("00") + " - " + pstrYear; } catch {} rptReport.Render(); printPreview.ReportViewer.Document = rptReport.Document; printPreview.Show(); return(dtbReportData); }
/// <summary> /// Build and show Detai lItem Price By PO Receipt /// </summary> public DataTable ExecuteReport(string pstrYear, string pstrMonth, string pstrCategoryIDList, string pstrProductIDList, string pstrMakeItem, string pstrProductType) { const string REPORT_TEMPLATE = "InOutReportWithCost.xml"; const string REPORT_NAME = "InOutReportWithCost"; const string RPT_TITLE_FLD = "fldTitle"; const string RPT_COMPANY_FLD = "fldCompany"; const string RPT_YEAR_FLD = "fldYear"; const string RPT_MONTH_FLD = "fldMonth"; const string RPT_CATEGORY_FLD = "fldCategory"; const string RPT_PART_NO_FLD = "fldProduct"; int intMakeItem = -1; if (pstrMakeItem != null && pstrMakeItem != string.Empty) { intMakeItem = Convert.ToInt32(Convert.ToBoolean(pstrMakeItem)); } DataTable dtbDataSource = GetInOutStockWithCostData(pstrYear, pstrMonth, pstrCategoryIDList, pstrProductIDList, intMakeItem, pstrProductType); // update end quantity & end value foreach (DataRow drowData in dtbDataSource.Rows) { decimal decBeginQuantity = 0, decBeginValue = 0, decInQuantity = 0, decInValue = 0; decimal decOutQuantity = 0, decOutValue = 0, decEndQuantity = 0, decEndValue = 0; try { decBeginQuantity = Convert.ToDecimal(drowData["BeginQuantity"]); } catch {} try { decBeginValue = Convert.ToDecimal(drowData["BeginValue"]); } catch {} try { decInQuantity = Convert.ToDecimal(drowData["InQuantity"]); } catch {} try { decInValue = Convert.ToDecimal(drowData["InValue"]); } catch {} try { decOutQuantity = Convert.ToDecimal(drowData["OutQuantity"]); } catch {} try { decOutValue = Convert.ToDecimal(drowData["OutValue"]); } catch {} decEndQuantity = decBeginQuantity + decInQuantity - decOutQuantity; decEndValue = decBeginValue + decInValue - decOutValue; drowData["EndQuantity"] = decEndQuantity; drowData["EndValue"] = decEndValue; } //Create builder object ReportWithSubReportBuilder reportBuilder = new ReportWithSubReportBuilder(); //Set report name reportBuilder.ReportName = REPORT_NAME; //Set Datasource reportBuilder.SourceDataTable = dtbDataSource; //Set report layout location reportBuilder.ReportDefinitionFolder = mstrReportDefinitionFolder; reportBuilder.ReportLayoutFile = REPORT_TEMPLATE; reportBuilder.UseLayoutFile = true; reportBuilder.MakeDataTableForRender(); // and show it in preview dialog C1PrintPreviewDialog printPreview = new C1PrintPreviewDialog(); //Attach report viewer reportBuilder.ReportViewer = printPreview.ReportViewer; reportBuilder.RenderReport(); reportBuilder.DrawPredefinedField(RPT_COMPANY_FLD, GetCompanyFullName()); reportBuilder.DrawPredefinedField(RPT_YEAR_FLD, pstrYear); reportBuilder.DrawPredefinedField(RPT_MONTH_FLD, pstrMonth); //Draw parameters NameValueCollection arrParamAndValue = new NameValueCollection(); arrParamAndValue.Add(RPT_YEAR_FLD, pstrYear); arrParamAndValue.Add(RPT_MONTH_FLD, pstrMonth); if (pstrCategoryIDList != null && pstrCategoryIDList != string.Empty) { reportBuilder.DrawPredefinedField(RPT_CATEGORY_FLD, GetCategoryInfo(pstrCategoryIDList)); } if (pstrProductIDList != null && pstrProductIDList != string.Empty) { reportBuilder.DrawPredefinedField(RPT_PART_NO_FLD, GetProductInfo(pstrProductIDList)); } try { printPreview.FormTitle = reportBuilder.GetFieldByName(RPT_TITLE_FLD).Text; } catch {} reportBuilder.RefreshReport(); printPreview.Show(); //return table return(dtbDataSource); }