public string GetExcelReportData(string fromDate, string toDate, out Library.DTO.Notification notification)
{
DateTime begin, end;
string season;
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
if (ValidatePeriod(fromDate, toDate, out season, out begin, out end))
{
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("GrossMarginSummaryRpt_function_getGrossMarginExcel", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Begin", begin);
adap.SelectCommand.Parameters.AddWithValue("@End", end);
adap.Fill(ds.GrossMarginSummaryRpt_GrossMarginSummaryExcel_View);
// add report param
ReportDataObject.ReportHeaderRow pRow = ds.ReportHeader.NewReportHeaderRow();
pRow.Season = season;
pRow.ExRate = Convert.ToDecimal(supportFactory.GetSettingValue(season, "ExRate-EUR-USD"), new System.Globalization.CultureInfo("en-US"));
ds.ReportHeader.AddReportHeaderRow(pRow);
// dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "GrossMarginSummary");
// generate xml file
//return DALBase.Helper.CreateReportFiles(ds, "GrossMarginSummary");
return(Library.Helper.CreateReportFileWithEPPlus(ds, "GrossMarginSummary", new List <string> {
"GrossMarginSummaryRpt_GrossMarginSummaryExcel_View", "ReportHeader"
}));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
else
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = "Report date range is invalid!";
return(string.Empty);
}
}
public string GetExcelReportData(int?factoryId, string clientUd, string season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("PurchasingPriceOverview2Rpt_function_GetExcelReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (factoryId.HasValue)
{
adap.SelectCommand.Parameters.AddWithValue("@FactoryID", factoryId);
}
if (!string.IsNullOrEmpty(clientUd))
{
adap.SelectCommand.Parameters.AddWithValue("@ClientUD", clientUd);
}
if (!string.IsNullOrEmpty(season))
{
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
}
adap.Fill(ds.PurchasingPriceOverview2Rpt_ExcelReportData_View);
ReportDataObject.ReportHeaderRow row = ds.ReportHeader.NewReportHeaderRow();
row.Season = season;
ds.ReportHeader.AddReportHeaderRow(row);
ds.AcceptChanges();
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "MissingShippingInfo");
// generate xml file
return(Library.Helper.CreateReportFileWithEPPlus(ds, "PurchasingPriceOverview2"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(string From, string To, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
DateTime fromDt = DateTime.ParseExact(From, "d/M/yyyy", CultureInfo.InvariantCulture);
string fromDate = fromDt.ToString("MM/dd/yyyy");
DateTime toDt = DateTime.ParseExact(To, "d/M/yyyy", CultureInfo.InvariantCulture);
string toDate = toDt.ToString("MM/dd/yyyy");
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("AVFPurchasingInvoiceMng_function_getExcelOverview", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@From", fromDate);
adap.SelectCommand.Parameters.AddWithValue("@To", toDate);
adap.Fill(ds);
// add report param
ReportDataObject.ReportHeaderRow pRow = ds.ReportHeader.NewReportHeaderRow();
pRow.From = From;
pRow.To = To;
ds.ReportHeader.AddReportHeaderRow(pRow);
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "AVFPurchasingInvoiceRptOverview");
// generate xml file
return(Library.Helper.CreateReportFiles(ds, "AVFPurchasingInvoiceRptOverview"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(string month, string year, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("AttendanceRpt_function_GetReportOverview", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Month", month);
adap.SelectCommand.Parameters.AddWithValue("@Year", year);
adap.Fill(ds.AttendanceRpt_function_GetReportOverview);
SqlDataAdapter adap2 = new SqlDataAdapter();
adap2.SelectCommand = new SqlCommand("SELECT * FROM AttendanceRpt_EmployeeDataReport_View", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap2.SelectCommand.CommandType = System.Data.CommandType.Text;
adap2.Fill(ds.AttendanceRpt_EmployeeDataReport_View);
// add report param
ReportDataObject.ReportHeaderRow pRow = ds.ReportHeader.NewReportHeaderRow();
pRow.Month = month;
pRow.Year = year;
ds.ReportHeader.AddReportHeaderRow(pRow);
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "AttendanceRptOverview");
// generate xml file
return(Library.Helper.CreateReportFiles(ds, "AttendanceRptOverview"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(int clientId, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("ClientPaymentOverviewRpt_function_GetReport", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@ClientID", clientId);
adap.TableMappings.Add("Table", "ClientPaymentOverviewRpt_DownPayment_View");
adap.TableMappings.Add("Table1", "ClientPaymentOverviewRpt_Invoice_View");
adap.Fill(ds);
// add report param
ReportDataObject.ReportHeaderRow pRow = ds.ReportHeader.NewReportHeaderRow();
pRow.Season = string.Empty;
pRow.ClientNM = supportFactory.GetClient().FirstOrDefault(o => o.ClientID == clientId).ClientNM;
pRow.ExRate = Convert.ToDecimal(supportFactory.GetSettingValue(Library.OMSHelper.Helper.GetCurrentSeason(), "ExRate-EUR-USD"), new System.Globalization.CultureInfo("en-US"));
ds.ReportHeader.AddReportHeaderRow(pRow);
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "ClientPaymentOverview");
// generate xml file
//return Library.Helper.CreateCOMReportFile(ds, "ClientPaymentOverview");
return(Library.Helper.CreateReportFiles(ds, "ClientPaymentOverview"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetReportData(int userId, string Season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("QuotationOverviewRpt_function_GetReportData", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandTimeout = 500;
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.SelectCommand.Parameters.AddWithValue("@UserID", userId);
adap.Fill(ds.QuotationOverviewRpt_function_GetReportData);
ReportDataObject.ReportHeaderRow hRow = ds.ReportHeader.NewReportHeaderRow();
hRow.Season = Season;
ds.ReportHeader.AddReportHeaderRow(hRow);
// generate xml file
//return Library.Helper.CreateReportFileWithEPPlus(ds, "QuotationOverview");
return(Library.Helper.CreateReportFileWithEPPlus(ds, "QuotationOverview", new List <string>()
{
"QuotationOverviewRpt_function_GetReportData", "ReportHeader"
}));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(string Season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("AccPIOverviewRpt_function_getExcelOverview", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.Fill(ds.AccPIOverviewRpt_ExcelOverview_View);
// add report param
ReportDataObject.ReportHeaderRow pRow = ds.ReportHeader.NewReportHeaderRow();
pRow.Season = Season;
pRow.ExRate = Convert.ToDecimal(supportFactory.GetSettingValue(Season, "ExRate-EUR-USD"), new System.Globalization.CultureInfo("en-US"));
ds.ReportHeader.AddReportHeaderRow(pRow);
// dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "AccPIOverview");
//return string.Empty;
// generate xml file
return(DALBase.Helper.CreateReportFiles(ds, "AccPIOverview"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetReportData(string Season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("FAQuotationOverviewRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.TableMappings.Add("Table", "GetReportData");
adap.Fill(ds);
ReportDataObject.ReportHeaderRow hRow = ds.ReportHeader.NewReportHeaderRow();
hRow.Season = Season;
ds.ReportHeader.AddReportHeaderRow(hRow);
// dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "QuotationOverview");
//return string.Empty;
// generate xml file
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "QuotationOverview"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
//
// CUSTOM FUNCTION
//
//
// CUSTOM FUNCTION
//
public string GetExcelReportData(string season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("FactoryPaymentSummaryRpt_function_getReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.Fill(ds.FactoryPaymentSummaryRpt_function_getReportData);
ReportDataObject.ReportHeaderRow row = ds.ReportHeader.NewReportHeaderRow();
row.Season = season;
ds.ReportHeader.AddReportHeaderRow(row);
ds.AcceptChanges();
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "FactoryPaymentOverview");
//return string.Empty;
// generate xml file
return(Library.Helper.CreateCOMReportFile(ds, "FactoryPaymentSummary"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(string Season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("ClientConditionRpt_function_getExcelData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.Fill(ds.ClientConditionRpt_CheckListExcel_View);
// add report param
ReportDataObject.ReportHeaderRow pRow = ds.ReportHeader.NewReportHeaderRow();
pRow.Season = Season;
ds.ReportHeader.AddReportHeaderRow(pRow);
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "ClientCondition");
// generate xml file
return(Library.Helper.CreateReportFiles(ds, "ClientCondition"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(int userId, string Season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("IncommingCashFlowPlanningRpt_function_GetData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandTimeout = 180;
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.TableMappings.Add("Table", "IncommingCashFlowPlanningRpt_Planning_View");
adap.TableMappings.Add("Table1", "IncommingCashFlowPlanningRpt_WeekInfo_View");
adap.Fill(ds);
ReportDataObject.ReportHeaderRow hRow = ds.ReportHeader.NewReportHeaderRow();
hRow.Season = Season;
hRow.ExRate = Convert.ToDecimal(supportFactory.GetSettingValue(Season, "ExRate-EUR-USD"), new System.Globalization.CultureInfo("en-US"));
ds.ReportHeader.AddReportHeaderRow(hRow);
return(Library.Helper.CreateReportFileWithEPPlus(ds, "IncommingCashFlowPlanning", new List <string>()
{
"IncommingCashFlowPlanningRpt_Planning_View", "IncommingCashFlowPlanningRpt_WeekInfo_View", "ReportHeader"
}));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = Library.Helper.GetInnerException(ex).Message;
return(string.Empty);
}
}
//
// CUSTOM FUNCTION
//
public string GetExcelReportData(string Season, string clientId, int?vnFactoryId, int?sampleProductStatusID, int?sampleOrderStatusID, int?sampleOrderID, bool canEdit, bool canRead, int userId, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("SampleOrderOverviewRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.SelectCommand.Parameters.AddWithValue("@ClientID", clientId);
// Add filter to export data
// Can edit to see all items.
// Can read only to see item in VN suggested factory of end-user manage
adap.SelectCommand.Parameters.AddWithValue("@CanEdit", canEdit);
adap.SelectCommand.Parameters.AddWithValue("@CanRead", canRead);
adap.SelectCommand.Parameters.AddWithValue("@UserID", userId);
if (vnFactoryId.HasValue)
{
adap.SelectCommand.Parameters.AddWithValue("@VNSuggestedFactoryID", vnFactoryId.Value);
}
if (sampleProductStatusID.HasValue)
{
adap.SelectCommand.Parameters.AddWithValue("@SampleProductStatusID", sampleProductStatusID.Value);
}
if (sampleOrderStatusID.HasValue)
{
adap.SelectCommand.Parameters.AddWithValue("@SampleOrderStatusID", sampleOrderStatusID.Value);
}
if (sampleOrderID.HasValue)
{
adap.SelectCommand.Parameters.AddWithValue("@SampleOrderID", sampleOrderID.Value);
}
adap.TableMappings.Add("Table", "SampleOrderOverviewRpt_ReportData_View");
adap.TableMappings.Add("Table1", "SampleOrderOverviewRpt_ReportMinuteData_View");
adap.TableMappings.Add("Table2", "SampleOrderOverviewRpt_ReportRemarkData_View");
adap.TableMappings.Add("Table3", "SampleOrderOverviewRpt_ReportSampleOrder_View");
adap.TableMappings.Add("Table4", "SampleOrderOverviewRpt_ReportQARemarkData_View");
adap.Fill(ds);
ReportDataObject.ReportHeaderRow hRow = ds.ReportHeader.NewReportHeaderRow();
hRow.Season = Season;
ds.ReportHeader.AddReportHeaderRow(hRow);
// processing data and cleanup
int sampleProductID = -1;
string bigRemark = string.Empty;
foreach (DAL.ReportDataObject.SampleOrderOverviewRpt_ReportData_ViewRow row in ds.SampleOrderOverviewRpt_ReportData_View)
{
sampleProductID = row.SampleProductID;
bigRemark = string.Empty;
foreach (DAL.ReportDataObject.SampleOrderOverviewRpt_ReportQARemarkData_ViewRow mRow in ds.SampleOrderOverviewRpt_ReportQARemarkData_View.Where(o => o.SampleProductID == sampleProductID))
{
bigRemark += mRow.IndexNumber.ToString() + ". " + mRow.UpdatorName + "(" + mRow.DisplayUpdatedDate + ")";
bigRemark += Environment.NewLine + mRow.Remark + Environment.NewLine + Environment.NewLine;
}
row.LastMinuteTableRemark = bigRemark;
bigRemark = string.Empty;
foreach (DAL.ReportDataObject.SampleOrderOverviewRpt_ReportRemarkData_ViewRow rRow in ds.SampleOrderOverviewRpt_ReportRemarkData_View.Where(o => o.SampleProductID == sampleProductID))
{
bigRemark += rRow.IndexNumber.ToString() + ". " + rRow.UpdatorName + "(" + rRow.DisplayUpdatedDate + ")";
bigRemark += Environment.NewLine + rRow.Remark + Environment.NewLine + Environment.NewLine;
}
row.InternRemark = bigRemark;
row.Barcode = Library.Helper.QRCodeImageFile(row.SampleProductUD);
}
ds.Tables.Remove("SampleOrderOverviewRpt_ReportMinuteData_View");
ds.Tables.Remove("SampleOrderOverviewRpt_ReportRemarkData_View");
ds.Tables.Remove("SampleOrderOverviewRpt_ReportQARemarkData_View");
ds.AcceptChanges();
// generate xml file
//return Library.Helper.CreateReportFileWithEPPlus(ds, "SampleOrderOverview");
return(Library.Helper.CreateReportFileWithEPPlus(ds, "SampleOrderOverview", new List <string>()
{
"SampleOrderOverviewRpt_ReportData_View", "SampleOrderOverviewRpt_ReportSampleOrder_View", "ReportHeader"
}));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
public string GetExcelReportData(int SampleOrderID, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("SampleOrderOverviewRpt_function_GetPrintData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@SampleOrderID", SampleOrderID);
adap.TableMappings.Add("Table", "SampleOrderOverviewRpt_ReportData_View");
adap.TableMappings.Add("Table1", "SampleOrderOverviewRpt_ReportMinuteData_View");
adap.TableMappings.Add("Table2", "SampleOrderOverviewRpt_ReportRemarkData_View");
adap.TableMappings.Add("Table3", "SampleOrderOverviewRpt_ReportSampleOrder_View");
adap.TableMappings.Add("Table4", "SampleOrderOverviewRpt_ReportQARemarkData_View");
adap.Fill(ds);
ReportDataObject.ReportHeaderRow hRow = ds.ReportHeader.NewReportHeaderRow();
hRow.Season = "";
if (ds.SampleOrderOverviewRpt_ReportData_View.FirstOrDefault() != null)
{
hRow.Season = ds.SampleOrderOverviewRpt_ReportData_View.FirstOrDefault().Season;
}
ds.ReportHeader.AddReportHeaderRow(hRow);
// processing data and cleanup
int sampleProductID = -1;
string bigRemark = string.Empty;
foreach (DAL.ReportDataObject.SampleOrderOverviewRpt_ReportData_ViewRow row in ds.SampleOrderOverviewRpt_ReportData_View)
{
sampleProductID = row.SampleProductID;
bigRemark = string.Empty;
foreach (DAL.ReportDataObject.SampleOrderOverviewRpt_ReportQARemarkData_ViewRow mRow in ds.SampleOrderOverviewRpt_ReportQARemarkData_View.Where(o => o.SampleProductID == sampleProductID))
{
bigRemark += mRow.IndexNumber.ToString() + ". " + mRow.UpdatorName + "(" + mRow.DisplayUpdatedDate + ")";
bigRemark += Environment.NewLine + mRow.Remark + Environment.NewLine + Environment.NewLine;
}
row.LastMinuteTableRemark = bigRemark;
bigRemark = string.Empty;
foreach (DAL.ReportDataObject.SampleOrderOverviewRpt_ReportRemarkData_ViewRow rRow in ds.SampleOrderOverviewRpt_ReportRemarkData_View.Where(o => o.SampleProductID == sampleProductID))
{
bigRemark += rRow.IndexNumber.ToString() + ". " + rRow.UpdatorName + "(" + rRow.DisplayUpdatedDate + ")";
bigRemark += Environment.NewLine + rRow.Remark + Environment.NewLine + Environment.NewLine;
}
row.InternRemark = bigRemark;
}
ds.Tables.Remove("SampleOrderOverviewRpt_ReportMinuteData_View");
ds.Tables.Remove("SampleOrderOverviewRpt_ReportRemarkData_View");
ds.Tables.Remove("SampleOrderOverviewRpt_ReportQARemarkData_View");
ds.AcceptChanges();
// generate xml file
return(Library.Helper.CreateReportFileWithEPPlus(ds, "SampleOrderOverview", new List <string>()
{
"SampleOrderOverviewRpt_ReportData_View", "SampleOrderOverviewRpt_ReportSampleOrder_View", "ReportHeader"
}));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
//
// CUSTOM FUNCTION
//
public string GetExcelReportData(int userId, int supplierId, string season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
// check permission
if (fwFactory.CheckSupplierPermission(userId, supplierId) == 0)
{
throw new Exception("Current user don't have access permission for the selected supplier data");
}
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("FactoryPaymentOverviewRpt_function_getDetailData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@SupplierID", supplierId);
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.Fill(ds.FactoryPaymentOverviewRpt_function_getDetailData);
adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("FactoryPaymentOverviewRpt_function_getInvoiceData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@SupplierID", supplierId);
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.Fill(ds.FactoryPaymentOverviewRpt_function_getInvoiceData);
adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("FactoryPaymentOverviewRpt_function_getTotalData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@SupplierID", supplierId);
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.Fill(ds.FactoryPaymentOverviewRpt_function_getTotalData);
adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("FactoryPaymentOverviewRpt_function_getTotalExtraData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@SupplierID", supplierId);
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.Fill(ds.FactoryPaymentOverviewRpt_function_getTotalExtraData);
ReportDataObject.ReportHeaderRow row = ds.ReportHeader.NewReportHeaderRow();
row.Season = season;
Support.DTO.Supplier dtoSupplier = supportFactory.GetSupplier(userId).FirstOrDefault(o => o.SupplierID == supplierId);
if (dtoSupplier != null)
{
row.SupplierUD = dtoSupplier.SupplierUD;
row.SupplierNM = dtoSupplier.SupplierNM;
}
ds.ReportHeader.AddReportHeaderRow(row);
ds.AcceptChanges();
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "FactoryPaymentOverview");
//return string.Empty;
// generate xml file
return(Library.Helper.CreateCOMReportFile(ds, "FactoryPaymentOverview"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
notification.DetailMessage.Add(ex.InnerException.Message);
}
return(string.Empty);
}
}
