/// <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);
}
/// <summary>
/// Build and show Detai lItem Price By PO Receipt
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <author> Tuan TQ, 11 Apr, 2006</author>
public DataTable ExecuteReport(string pstrCCNID,
string pstrMasLocID,
string pstrFromDate,
string pstrToDate,
string pstrCurrencyID,
string pstrExchangeRate,
string pstrProductIDList,
string pstrVendorType,
string pstrVendorIDList
)
{
string strPOTypeID = Convert.ToString((int)PCSComUtils.Common.POReceiptTypeEnum.ByInvoice);
const string DATE_HOUR_FORMAT = "dd-MM-yyyy HH:mm";
const string NUMERIC_FORMAT = "#,##0.00";
const string REPORT_TEMPLATE_LOCAL = "ReceivingMaterialReport_Local.xml";
const string REPORT_TEMPLATE = "ReceivingMaterialReport.xml";
const string RPT_PAGE_HEADER = "PageHeader";
const string REPORT_NAME = "ReceivingMaterialReport";
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";
const string RPT_VENDOR_TYPE_FLD = "Vendor Type";
DataTable dtbDataSource = null;
string strHomeCurrency = GetHomeCurrency(pstrCCNID);
dtbDataSource = GetReceivingMaterialReportData(pstrCCNID,
pstrMasLocID,
pstrFromDate,
pstrToDate,
pstrProductIDList,
pstrCurrencyID,
pstrExchangeRate,
strHomeCurrency,
pstrVendorType,
pstrVendorIDList);
//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;
if (pstrVendorType != null && pstrVendorType != string.Empty)
{
if (pstrVendorType.ToUpper() == LOCAL_STRING.ToUpper())
{
reportBuilder.ReportLayoutFile = REPORT_TEMPLATE_LOCAL;
}
else
{
reportBuilder.ReportLayoutFile = REPORT_TEMPLATE;
}
}
else
{
reportBuilder.ReportLayoutFile = REPORT_TEMPLATE;
}
reportBuilder.UseLayoutFile = true;
reportBuilder.MakeDataTableForRender();
// and show it in preview dialog
PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog();
//Attach report viewer
reportBuilder.ReportViewer = printPreview.ReportViewer;
reportBuilder.RenderReport();
reportBuilder.DrawPredefinedField(RPT_COMPANY_FLD, GetCompanyFullName());
//Draw parameters
System.Collections.Specialized.NameValueCollection arrParamAndValue = new System.Collections.Specialized.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 (pstrVendorType != null && pstrVendorType != string.Empty)
{
arrParamAndValue.Add(RPT_VENDOR_TYPE_FLD, pstrVendorType);
}
if (pstrVendorIDList != null && pstrVendorIDList != string.Empty)
{
arrParamAndValue.Add(RPT_VENDOR_FLD, GetPartyInfo(pstrVendorIDList));
}
//Anchor the Parameter drawing canvas cordinate to the fldTitle
C1.C1Report.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);
}
/// <summary>
/// Build and show Detai lItem Price By PO Receipt
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <author> Tuan TQ, 11 Apr, 2006</author>
public DataTable ExecuteReport(string pstrCCNID,
string pstrFromDate,
string pstrToDate,
string pstrCurrencyIDList
)
{
try
{
string strPOTypeID = Convert.ToString((int)PCSComUtils.Common.POReceiptTypeEnum.ByInvoice);
const string DATE_HOUR_FORMAT = "dd-MM-yyyy HH:mm";
const string NUMERIC_FORMAT = "#,##0.00";
const string REPORT_TEMPLATE = "ImportMaterialReport.xml";
const string RPT_PAGE_HEADER = "PageHeader";
const string REPORT_NAME = "ImportMaterialReport";
const string RPT_TITLE_FLD = "fldTitle";
const string RPT_COMPANY_FLD = "fldCompany";
const string RPT_CCN_FLD = "CCN";
const string RPT_FROM_DATE_FLD = "From Date";
const string RPT_TO_DATE_FLD = "To Date";
const string RPT_CURRENCY_FLD = "Currency";
DataTable dtbDataSource = null;
dtbDataSource = GetImportMaterialReport(pstrCCNID, pstrFromDate, pstrToDate, pstrCurrencyIDList);
//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
PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog();
//Attach report viewer
reportBuilder.ReportViewer = printPreview.ReportViewer;
reportBuilder.RenderReport();
reportBuilder.DrawPredefinedField(RPT_COMPANY_FLD, GetCompanyFullName());
//Draw parameters
System.Collections.Specialized.NameValueCollection arrParamAndValue = new System.Collections.Specialized.NameValueCollection();
arrParamAndValue.Add(RPT_CCN_FLD, GetCCNInfoByID(pstrCCNID));
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 (pstrCurrencyIDList != null && pstrCurrencyIDList != string.Empty)
{
arrParamAndValue.Add(RPT_CURRENCY_FLD, GetCurrencyInfo(pstrCurrencyIDList));
}
//Anchor the Parameter drawing canvas cordinate to the fldTitle
C1.C1Report.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);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable ExecuteReport(string pstrCCN, string pstrCategory, string pstrModel, string pstrSource, string pstrType, string pstrMakeItem)
{
try
{
//const char UNCHECK_SQUARE_CHAR = (char)111;
//const char CHECK_SQUARE_CHAR = (char)120;
//const char SPACE_CHAR = (char)32;
//Report name
const string REPORT_NAME = "ItemListReport";
const string REPORT_LAYOUT = "ItemListReport.xml";
const string RPT_HEADER = "Header";
//Report field names
const string RPT_TITLE_FIELD = "fldTitle";
//Report field names
const string RPT_CCN = "CCN";
const string RPT_CATEGORY = "Category";
const string RPT_MODEL = "Model";
const string RPT_SOURCE = "Source";
const string RPT_TYPE = "Type";
const string RPT_MAKE_ITEM = "Make";
DataTable dtbItemList = GetItemListData(pstrCCN, pstrCategory, pstrModel, pstrSource, pstrType, pstrMakeItem);
//Create builder object
ReportWithSubReportBuilder reportBuilder = new ReportWithSubReportBuilder();
//Set report name
reportBuilder.ReportName = REPORT_NAME;
//Set Datasource
reportBuilder.SourceDataTable = dtbItemList;
//Set report layout location
reportBuilder.ReportDefinitionFolder = mstrReportDefinitionFolder;
reportBuilder.ReportLayoutFile = REPORT_LAYOUT;
reportBuilder.UseLayoutFile = true;
reportBuilder.MakeDataTableForRender();
// and show it in preview dialog
PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog();
//Attach report viewer
reportBuilder.ReportViewer = printPreview.ReportViewer;
reportBuilder.RenderReport();
//Draw parameters
System.Collections.Specialized.NameValueCollection arrParamAndValue = new System.Collections.Specialized.NameValueCollection();
arrParamAndValue.Add(RPT_CCN, pstrCCN);
if (pstrCategory != string.Empty)
{
arrParamAndValue.Add(RPT_CATEGORY, pstrCategory);
}
if (pstrModel != string.Empty)
{
arrParamAndValue.Add(RPT_MODEL, pstrModel);
}
if (pstrSource != string.Empty)
{
arrParamAndValue.Add(RPT_SOURCE, pstrSource);
}
if (pstrType != string.Empty)
{
arrParamAndValue.Add(RPT_TYPE, pstrType);
}
if (pstrMakeItem.ToUpper().Equals("TRUE") || pstrMakeItem.Equals("1"))
{
arrParamAndValue.Add(RPT_MAKE_ITEM, "x");
}
//Anchor the Parameter drawing canvas cordinate to the fldTitle
C1.C1Report.Field fldTitle = reportBuilder.GetFieldByName(RPT_TITLE_FIELD);
double dblStartX = fldTitle.Left;
double dblStartY = fldTitle.Top + 1.3 * fldTitle.RenderHeight;
reportBuilder.GetSectionByName(RPT_HEADER).CanGrow = true;
reportBuilder.DrawParameters(reportBuilder.GetSectionByName(RPT_HEADER), dblStartX, dblStartY, arrParamAndValue, reportBuilder.Report.Font.Size);
try
{
printPreview.FormTitle = reportBuilder.GetFieldByName(RPT_TITLE_FIELD).Text;
}
catch {}
//Show report
reportBuilder.RefreshReport();
printPreview.Show();
return(dtbItemList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Main function, generate the result data Table for the REPORT VIEWER
/// </summary>
/// <param name="pstrCCNID"></param>
/// <returns></returns>
public DataTable ExecuteReportOLD(string pstrCCNID, string pstrYear, string pstrMonth, string pstrMPSCycleID, string pstrProductionLineID)
{
//const string METHOD_NAME = ".ExecuteReport()";
const string TABLE_NAME = "WorkingSchemeReport";
const string SUB_TABLE_NAME = "ShiftTotalReport";
string REPORT_NAME = "WorkingSchemeReport";
const string SUB_REPORT_NAME = "ShiftTotalReport";
string REPORT_LAYOUT_FILE = "InventoryStatusReport.xml";
short COPIES = 1;
const string ENDSTOCK = "EndStock";
const string CHANGECATEGORY = "Change Category";
const string LEADTIME = "Lead Time";
const string REQUIRECAPACITY = "Require Capacity";
const string STANDARDCAPACITY = "Standard Capacity";
const string COMPARESECOND = "Compare Second";
const string COMPAREPERCENT = "Compare Percent";
string strFromDate = string.Format("{0}-{1}-01", pstrYear, pstrMonth); // begin date of the selected month
const string REPORTFLD_WORKINGDAYS = "fldParameterWorkingdays";
const string REPORTFLD_OFFDAYS = "fldParameterOffdays";
const string REPORTFLD_PARAMETER_CCN = "fldParameterCCN";
const string REPORTFLD_PARAMETER_YEAR = "fldParameterYear";
const string REPORTFLD_PARAMETER_MONTH = "fldParameterMonth";
const string REPORTFLD_PARAMETER_CYCLE = "fldParameterCycle";
const string REPORTFLD_PARAMETER_PRODUCTIONLINE = "fldParameterProductionLine";
int nCCNID = int.Parse(pstrCCNID);
int nYear = int.Parse(pstrYear);
int nMonth = int.Parse(pstrMonth);
int nCycle = int.Parse(pstrMPSCycleID);
int nProductionLineID = int.Parse(pstrProductionLineID);
int nWorkingDays;
int nOffDays;
string strCCN = string.Empty;
string strCycle = string.Empty;
string strProductionLine = string.Empty;
#region GETTING THE PARAMETER
PCSComUtils.Common.BO.UtilsBO boUtil = new PCSComUtils.Common.BO.UtilsBO();
PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO objBO = new PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO();
strCCN = boUtil.GetCCNCodeFromID(nCCNID);
strCycle = objBO.GetMPSCycleFromID(nCycle) + "-" + objBO.GetMPSCycleDescriptionFromID(nCycle);
strProductionLine = objBO.GetProductLineCodeFromID(nProductionLineID) + "-" + objBO.GetProductLineNameFromID(nProductionLineID);
// refer to mr.TuanTQ to get WOrkingDayInMonth
nWorkingDays = GetWorkingDayInMonth(nMonth, nYear);
nOffDays = DateTime.DaysInMonth(nYear, nMonth) - nWorkingDays;
#endregion
System.Data.DataTable dtbSourceData;
System.Data.DataTable dtbSubReportData;
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
string strSql = string.Empty;
#region BUILD THE DATA TABLE
try
{
DataSet dstPCS = new DataSet();
oconPCS = null;
ocmdPCS = null;
#region SQL Query
strSql =
" Declare @strFromDate smalldatetime " +
" Declare @pstrCCNID int " +
" Declare @pstrProductionLineID int " +
" Declare @pstrMPSCycleID int " +
" Declare @pstrMonth char(2) " +
" Declare @pstrYear char(4) " +
" " +
" Declare @pstrInArray varchar(40) " +
" Declare @pstrOutArray varchar(40) " +
" " +
" " +
" Set @strFromDate = '" + pstrYear + "-" + nMonth.ToString("00") + "-01' " +
" Set @pstrCCNID = " + pstrCCNID + " " +
" Set @pstrProductionLineID = " + pstrProductionLineID + " " +
" Set @pstrMPSCycleID = " + pstrMPSCycleID + " " +
" Set @pstrYear = '" + pstrYear + "' " +
" Set @pstrMonth = '" + nMonth.ToString("00") + "' " +
" " +
" " +
" select " +
" P.ProductID as [ProductID], " +
" ITM_Category.Code as [Category], " +
" P.Code as [Part Number], " +
" P.Description as [Part Name], " +
" P.Revision as [Model], " +
" IsNull(CPOTable.Quantity,0) as [Plan], " +
" IsNull(BeginStockTable.[Begin Stock],0.00) as [Begin Stock], " +
" (IsNull(BeginStockTable.[Begin Stock],0) - IsNull(CPOTable.Quantity,0)) As [EndStock], " +
" " +
" " +
" CAST(IsNull((ChangeTimeTable.ChangeTime) , 0.00) as decimal(20,5) ) as [Change Category], " +
" " +
" [Lead Time] = " +
" CASE " +
" WHEN ITM_Routing.Pacer = 'L' THEN ITM_Routing.LaborSetupTime + ITM_Routing.LaborRunTime " +
" WHEN ITM_Routing.Pacer = 'M' THEN ITM_Routing.MachineSetupTime + ITM_Routing.MachineRunTime " +
" WHEN ITM_Routing.Pacer = 'B' THEN ITM_Routing.MachineRunTime + ITM_Routing.LaborRunTime " +
" END, " +
" " +
" 0.00 as [Require Capacity] " +
" " +
" from " +
" MST_WorkCenter join ITM_Routing " +
" on MST_WorkCenter.WorkCenterID = ITM_Routing.WorkCenterID and ITM_Routing.Type = 0 " +
" left join PRO_ProductionLine " +
" on MST_WOrkCenter.ProductionLineID = PRO_ProductionLine.ProductionLineID " +
" join ITM_Product as P " +
" on P.ProductID = ITM_Routing.ProductID " +
" join ITM_Category " +
" on P.CategoryID = ITM_Category.CategoryID " +
" join PRO_WCCapacity " +
" on MST_WorkCenter.WorkCenterID = PRO_WCCapacity.WorkCenterID " +
" join PRO_ShiftCapacity " +
" on PRO_WCCapacity.WCCapacityID = PRO_ShiftCapacity.WCCapacityID " +
" join PRO_Shift " +
" on PRO_ShiftCapacity.ShiftID = PRO_Shift.ShiftID " +
" " +
" " +
" /* --######-- BEGIN Get Begin Quantity of Product */ " +
" left join " +
" ( " +
" SELECT DISTINCT "+
" ITM_Product.ProductID, "+
" " +
" ( "+
" ISNULL(IV_MasLocCache.OHQuantity, 0) "+
" " +
" - (SELECT ISNULL(SUM(TransQuantity), 0) "+
" FROM v_TransactionHistory inFrom_Today_TransHis "+
" WHERE ProductID = ITM_Product.ProductID "+
" AND CCNID = @pstrCCNID "+
" AND PostDate BETWEEN @strFromDate AND GetDate() "+
" AND TranTypeID IN (8, 11, 13, 19, 20, 16, 17) "+
" ) "+
" " +
" + (SELECT ISNULL(SUM(TransQuantity), 0) "+
" FROM v_TransactionHistory "+
" WHERE ProductID = ITM_Product.ProductID "+
" AND CCNID = @pstrCCNID "+
" AND PostDate BETWEEN @strFromDate AND GetDate() "+
" AND TranTypeID IN (12, 14, 15) "+
" ) "+
" ) "+
" as [Begin Stock] "+
" " +
" FROM ITM_Product "+
" INNER JOIN IV_MasLocCache ON ITM_Product.ProductID = IV_MasLocCache.ProductID "+
" WHERE ITM_Product.CCNID = @pstrCCNID "+
" " +
" GROUP BY "+
" ITM_Product.ProductID, "+
" IV_MasLocCache.OHQuantity "+
" " +
" ) " +
" as BeginStockTable " +
" on P.ProductID = BeginStockTable.ProductID " +
" /* ######-- END Get Begin Quantity of Product */ " +
" " +
" " +
" /* BEGIN: Getting the Change time of Product */ " +
" left join " +
" ( " +
" select "+
" CCMatrix.DestProductID as [ProductID], "+
" Sum(CCMatrix.ChangeTime) as [ChangeTime] "+
" "+
" from PRO_ChangeCategoryMatrix as CCMatrix "+
" "+
" /*BEGIN: Join to get condition on parameter: ProductionLineID*/ "+
" join PRO_ChangeCategoryMaster CCMaster "+
" on CCMaster.ChangeCategoryMasterID = CCMatrix.ChangeCategoryMasterID "+
" join MST_WorkCenter "+
" on MST_WorkCenter.WorkCenterID = CCMaster.WorkCenterID "+
" and MST_WorkCenter.ProductionLineID = @pstrProductionLineID "+
" and MST_WorkCenter.CCNID = @pstrCCNID "+
" /*END: Join to get condition on parameter: CCNID, ProductionLineID*/ "+
" "+
" group by "+
" DestProductID "+
" ) " +
" as ChangeTimeTable " +
" on P.ProductID = ChangeTimeTable.ProductID " +
" /*-- END: Getting the Change time of Product */ " +
" " +
" " +
" " +
" /* BEGIN GETTING CPO PLAN QUANTITY */ " +
" left join " +
" ( " +
" SELECT "+
" MTR_CPO.MPSCycleOptionMasterID, "+
" ITM_Product.ProductID, "+
" ITM_Product.Revision AS [Model], "+
" ITM_Product.Code AS [Part Number], "+
" /* --DATEPART(yyyy, MTR_CPO.DueDate) as [Year], */ "+
" /* --DATEPART(mm, MTR_CPO.DueDate)as [Month], */ "+
" SUM(MTR_CPO.Quantity) AS [Quantity] "+
" "+
" FROM "+
" MTR_CPO "+
" INNER JOIN MST_CCN "+
" ON MTR_CPO.CCNID = MST_CCN.CCNID "+
" INNER JOIN ITM_Product "+
" ON MTR_CPO.ProductID = ITM_Product.ProductID "+
" left outer JOIN ITM_Category "+
" ON ITM_Product.CategoryID = ITM_Category.CategoryID "+
" "+
" WHERE "+
" MTR_CPO.CCNID = @pstrCCNID AND "+
" MTR_CPO.MPSCycleOptionMasterID = @pstrMPSCycleID AND "+
" DATEPART(yyyy, MTR_CPO.DueDate) = @pstrYear AND "+
" DATEPART(mm, MTR_CPO.DueDate) = @pstrMonth "+
" "+
" GROUP BY "+
" MTR_CPO.MPSCycleOptionMasterID, "+
" MST_CCN.Code, "+
" ITM_Category.Code, "+
" ITM_Product.ProductID, "+
" ITM_Product.Code, "+
" ITM_Product.Description, "+
" ITM_Product.Revision, "+
" DatePart(yyyy,MTR_CPO.DueDate), "+
" DatePart(mm,MTR_CPO.DueDate) "+
" ) " +
" as CPOTable " +
" on P.ProductID = CPOTable.ProductID " +
" /* END GETTING CPO PLAN QUANTITY */ " +
" " +
" " +
" " +
" " +
" WHERE " +
" PRO_ProductionLine.ProductionLineID = @pstrProductionLineID " +
" and DATEPART(yyyy, PRO_WCCapacity.BeginDate ) <= @pstrYear " +
" and DATEPART(yyyy, PRO_WCCapacity.EndDate ) >= @pstrYear " +
" and DATEPART(mm, PRO_WCCapacity.BeginDate ) <= @pstrMonth " +
" and DATEPART(mm, PRO_WCCapacity.EndDate ) >= @pstrMonth " +
" and MST_WOrkCenter.IsMain = 1 " +
" " +
" " +
" /* GROUP BY of outside query*/ " +
" group by "+
" P.ProductID, " +
" ITM_Category.Code, " +
" P.Code, " +
" P.Description, " +
" P.Revision, " +
" CPOTable.Quantity, " +
" BeginStockTable.[Begin Stock], " +
" " +
" (IsNull(CPOTable.Quantity,0) - IsNull(BeginStockTable.[Begin Stock],0)), " +
" " +
" ITM_Routing.Pacer, " +
" ITM_Routing.LaborSetupTime, " +
" ITM_Routing.LaborRunTime, " +
" ITM_Routing.MachineSetupTime, " +
" ITM_Routing.MachineRunTime, " +
" CAST(IsNull((ChangeTimeTable.ChangeTime) , 0.00) as decimal(20,5) ) " +
" " +
" Order by [Category],[Part Number],[Model] " +
" " +
" " +
" " +
" /**********************************************************************************/ " +
" /**********************************************************************************/ " +
" /**********************************************************************************/ " +
" " +
" " +
" /* BEGIN StandardCapacity caculate */ " +
" select " +
" PRO_Shift.ShiftDesc as [Shift], " +
" sum(PRO_WCCapacity.Capacity) as [Standard Capacity] " +
" " +
" from " +
" MST_WorkCenter " +
" join PRO_WCCapacity " +
" on MST_WorkCenter.WorkCenterID = PRO_WCCapacity.WorkCenterID " +
" join PRO_ShiftCapacity " +
" on PRO_WCCapacity.WCCapacityID = PRO_ShiftCapacity.WCCapacityID " +
" join PRO_Shift " +
" on PRO_ShiftCapacity.ShiftID = PRO_Shift.ShiftID " +
" " +
" /* left join MTR_CPO " +
" on MTR_CPO.ProductID = ITM_Product.ProductID " +
" and MTR_CPO.MPSCycleOptionMasterID = @pstrMPSCycleID " +
" */ " +
" " +
" WHERE " +
" MST_WorkCenter.ProductionLineID = @pstrProductionLineID " +
" and PRO_WCCapacity.CCNID = @pstrCCNID " +
" and MST_WOrkCenter.IsMain = 1 " +
" " +
" and DATEPART(yyyy, PRO_WCCapacity.BeginDate ) <= @pstrYear " +
" and DATEPART(yyyy, PRO_WCCapacity.EndDate ) >= @pstrYear " +
" and DATEPART(mm, PRO_WCCapacity.BeginDate ) <= @pstrMonth " +
" and DATEPART(mm, PRO_WCCapacity.EndDate ) >= @pstrMonth " +
" " +
" " +
" group by " +
" PRO_Shift.ShiftDesc " +
" " +
" /* END StandardCapacity caculate */ " +
" " +
" ";
#endregion
oconPCS = new OleDbConnection(mConnectionString);
ocmdPCS = new OleDbCommand(strSql, oconPCS);
ocmdPCS.Connection.Open();
OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
odadPCS.Fill(dstPCS);
if (dstPCS.Tables.Count > 0)
{
dtbSourceData = dstPCS.Tables[0].Copy();
dtbSubReportData = dstPCS.Tables[1].Copy();
}
else
{
dtbSourceData = new DataTable();
dtbSubReportData = new DataTable();
}
}
catch (OleDbException ex)
{
throw new Exception(strSql, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
#endregion
#region TRANSFORM DATATABLE FOR REPORT
// only re-calculate Require Capacity, Compare Second, and Compare Percent column
/// Calculate the Require Capacity
foreach (DataRow drow in dtbSourceData.Rows)
{
drow[REQUIRECAPACITY] = (decimal)drow[ENDSTOCK] * (decimal)drow[LEADTIME] + (decimal)drow[CHANGECATEGORY];
}
decimal decSumOfRequireCapacity = 0;
/// calculate the Sum of Require Capacity
foreach (DataRow drow in dtbSourceData.Rows)
{
decSumOfRequireCapacity += (decimal)drow[REQUIRECAPACITY];
}
dtbSubReportData.Columns.Add(COMPARESECOND, typeof(System.Decimal));
dtbSubReportData.Columns.Add(COMPAREPERCENT);
/// calculate the 2 Compare Column
foreach (DataRow drow in dtbSubReportData.Rows)
{
drow[COMPARESECOND] = (decimal)drow[STANDARDCAPACITY] - decSumOfRequireCapacity;
if (decSumOfRequireCapacity != Decimal.Zero)
{
decimal decPercentValue = ((decimal)drow[STANDARDCAPACITY] * 100) / decSumOfRequireCapacity; /// Percent
drow[COMPAREPERCENT] = decPercentValue.ToString("#,##0.00") + "%";
}
}
#endregion
#region RENDER REPORT
ReportWithSubReportBuilder objRB = new ReportWithSubReportBuilder();
objRB.ReportName = REPORT_NAME;
objRB.SourceDataTable = dtbSourceData;
objRB.SubReportDataSources.Add(SUB_REPORT_NAME, dtbSubReportData);
objRB.ReportDefinitionFolder = mstrReportDefinitionFolder;
objRB.ReportLayoutFile = REPORT_LAYOUT_FILE;
objRB.UseLayoutFile = true;
objRB.MakeDataTableForRender();
// and show it in preview dialog
PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog();
printPreview.FormTitle = REPORT_NAME;
//Attach report viewer
objRB.ReportViewer = printPreview.ReportViewer;
objRB.RenderReport();
#region MODIFY THE REPORT LAYOUT
objRB.DrawBoxGroup_Madeby_Checkedby_Approvedby(objRB.GetSectionByName("Header"), 100, 100, 1300, 1500, 200);
objRB.DrawPredefinedField(REPORTFLD_WORKINGDAYS, nWorkingDays.ToString());
objRB.DrawPredefinedField(REPORTFLD_OFFDAYS, nOffDays.ToString());
#region PUSH PARAMETER VALUE
objRB.DrawPredefinedField(REPORTFLD_PARAMETER_CCN, strCCN);
objRB.DrawPredefinedField(REPORTFLD_PARAMETER_YEAR, nYear.ToString("0000"));
objRB.DrawPredefinedField(REPORTFLD_PARAMETER_MONTH, nMonth.ToString("00"));
objRB.DrawPredefinedField(REPORTFLD_PARAMETER_CYCLE, strCycle);
objRB.DrawPredefinedField(REPORTFLD_PARAMETER_PRODUCTIONLINE, strProductionLine);
#endregion
#endregion
objRB.RefreshReport();
printPreview.Show();
#endregion
UseReportViewerRenderEngine = false;
mResult = dtbSourceData;
return(dtbSourceData);
}