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