public string ExportExcel(int userId, System.Collections.Hashtable filters, 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("ShippingPerformanceRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (filters.ContainsKey("Season") && !string.IsNullOrEmpty(filters["Season"].ToString()))
{
adap.SelectCommand.Parameters.AddWithValue("@Season", filters["Season"].ToString().Replace("'", "''"));
}
adap.Fill(ds.ShippingPerformanceRpt);
ds.AcceptChanges();
return(Library.Helper.CreateReportFileWithEPPlus(ds, "ShippingPerformanceRpt", new List <string>()
{
"ShippingPerformanceRpt"
}));
}
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 id, 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("PSRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@FactoryOrderDetailID", id);
adap.SelectCommand.Parameters.AddWithValue("@ProductID", DBNull.Value);
adap.TableMappings.Add("Table", "PSRpt_PS_View");
adap.TableMappings.Add("Table1", "PSRpt_Piece_View");
adap.Fill(ds);
return(Library.Helper.CreateReportFileWithEPPlus(ds, "PS"));
}
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 PrintFactoryMaterialStock(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("FactoryMaterialMng_function_GetStock", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
//adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.Fill(ds.FactoryMaterialMng_Stock_View);
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "FactoryMaterialStock");
// generate xml file
return(Library.Helper.CreateReportFiles(ds, "FactoryMaterialStock"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
notification.DetailMessage.Add(ex.Message);
if (ex.GetBaseException() != null)
{
notification.DetailMessage.Add(ex.GetBaseException().Message);
}
return(string.Empty);
}
}
//
// 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("PriceComparisonRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.TableMappings.Add("Table", "PriceComparisonRpt_Factory_View");
adap.TableMappings.Add("Table1", "PriceComparisonRpt_LatestItemPrice_View");
adap.TableMappings.Add("Table2", "PriceComparisonRpt_Overview_View");
adap.Fill(ds);
foreach (DAL.ReportDataObject.PriceComparisonRpt_Factory_ViewRow fRow in ds.PriceComparisonRpt_Factory_View)
{
ds.PriceComparisonRpt_Overview_View.Columns.Add(fRow.FactoryUD.Replace("-", "_"), typeof(decimal));
}
DAL.ReportDataObject.PriceComparisonRpt_LatestItemPrice_ViewRow iRow = null;
string ClientUD;
string ArticleCode;
string FactoryUD;
foreach (DAL.ReportDataObject.PriceComparisonRpt_Overview_ViewRow oRow in ds.PriceComparisonRpt_Overview_View)
{
ClientUD = oRow.ClientUD;
ArticleCode = oRow.ArticleCode;
FactoryUD = oRow.FactoryUD;
iRow = ds.PriceComparisonRpt_LatestItemPrice_View.FirstOrDefault(o => o.ClientUD == ClientUD && o.ArticleCode == ArticleCode && o.FactoryUD != FactoryUD);
if (iRow != null && !iRow.IsSalePriceNull())
{
iRow[FactoryUD.Replace("-", "_")] = iRow.SalePrice;
}
}
// dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "MIEurofarPriceOverview");
//return string.Empty;
// generate xml file
//return Library.Helper.CreateReportFiles(ds, "MIEurofarPriceOverview");
//return Library.Helper.CreateReportFiles(ds, "MIEurofarPriceOverview2");
return(Library.Helper.CreateReportFileWithEPPlus(ds, "PriceComparisonRpt"));
}
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 object GetReportData(string season, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification();
notification.Type = Library.DTO.NotificationType.Success;
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("PurchasingPriceComparisonMng_function_GetPurchasingPriceComparison", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.TableMappings.Add("Table", "PurchasingPriceComparisonView");
adap.TableMappings.Add("Table1", "ReportHeader");
adap.Fill(ds);
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "PurchasingPriceComparison"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = Library.Helper.GetInnerException(ex).Message;
return("");
}
}
//
// CUSTOM FUNCTIONS
//
public string GetExcelReportData(System.Collections.Hashtable filters, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
System.Data.SqlClient.SqlDataAdapter adap = new System.Data.SqlClient.SqlDataAdapter();
adap.SelectCommand = new System.Data.SqlClient.SqlCommand("StandardPurchasingPriceMng_function_GetReportData", new System.Data.SqlClient.SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
foreach (string filterKey in filters.Keys)
{
if (filters[filterKey] != null && !string.IsNullOrEmpty(filters[filterKey].ToString()))
{
adap.SelectCommand.Parameters.AddWithValue("@" + filterKey, filters[filterKey].ToString());
}
}
adap.Fill(ds.StandardPurchasingPriceMng_ReportData_View);
ds.AcceptChanges();
return(Library.Helper.CreateReportFileWithEPPlus(ds, "StandardPurchasingPrice", new List <string>()
{
"StandardPurchasingPriceMng_ReportData_View"
}));
}
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, 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("ShipmentOverviewVNRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.Fill(ds.ShipmentOverviewVNRpt_function_GetReportData);
SqlDataAdapter adap2 = new SqlDataAdapter();
adap2.SelectCommand = new SqlCommand("ShipmentOverviewVNRpt_function_GetReportHeaderData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap2.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap2.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap2.Fill(ds.ReportHeader);
return(Library.Helper.CreateReportFileWithEPPlus(ds, "ShipmentOverviewVN"));
}
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 bookingID, 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("BookingMng_function_GetReportData", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@BookingID", bookingID);
adap.TableMappings.Add("Table", "BookingMng_Booking_ReportView");
adap.Fill(ds);
//generate schema
//DALBase.Helper.DevCreateReportXMLSource(ds, "Booking");
//generate xml file
return(DALBase.Helper.CreateReportFiles(ds, "Booking"));
}
catch (Exception ex)
{
notification = new Library.DTO.Notification()
{
Message = ex.Message
};
return(string.Empty);
}
}
public string GetPaymentPrintout(int paymentNoteprintID, 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("PaymentNoteMng_Function_Print", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@PaymentNoteID", paymentNoteprintID);
adap.TableMappings.Add("Table", "Receipt");
adap.TableMappings.Add("Table1", "ReceiptDetail");
adap.Fill(ds);
ds.Tables["Receipt"].Rows[0]["AmountToText"] = ConvertNumber2String(Convert.ToDecimal(ds.Tables["Receipt"].Rows[0]["Amount"]));
return(Library.Helper.CreateReceiptPrintout(ds.Tables["Receipt"], ds.Tables["ReceiptDetail"], "PaymentNote.rdlc"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = Library.Helper.GetInnerException(ex).Message;
return(string.Empty);
}
}
public string GetReceiptPrintout(int factoryMaterialReceiptID, 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("FactoryMaterialReceiptMng_function_GetReceiptPrintout", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@FactoryMaterialReceiptID", factoryMaterialReceiptID);
adap.TableMappings.Add("Table", "Receipt");
adap.TableMappings.Add("Table1", "ReceiptDetail");
adap.Fill(ds);
return(Library.Helper.CreateReceiptPrintout(ds.Tables["Receipt"], ds.Tables["ReceiptDetail"], "FactoryMaterialReceiptPrintout.rdlc"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
notification.DetailMessage.Add(ex.Message);
if (ex.GetBaseException() != null)
{
notification.DetailMessage.Add(ex.GetBaseException().Message);
}
return(string.Empty);
}
}
public string GetExcelReport(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("LedgerAccountMng_function_GetReportAccountData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.Fill(ds.LedgerAccountMng_function_GetReportAccountData);
// dev
//Library.Helper.DevCreateReportXMLSource(ds, "LedgerAccountOverview");
//generate xml file
return(Library.Helper.CreateReportFiles(ds, "LedgerAccountOverview"));
}
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 GetWarehouseSoldItem(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("WarehouseOrderOverviewRpt_function_GetReport", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
adap.TableMappings.Add("Table", "Param");
adap.TableMappings.Add("Table1", "WarehouseSoldItem");
adap.Fill(ds);
foreach (var item in ds.WarehouseSoldItem)
{
item.FileLocation = FrameworkSetting.Setting.MediaFullSizeUrl + item.FileLocation;
item.ThumbnailLocation = FrameworkSetting.Setting.MediaThumbnailUrl + item.ThumbnailLocation;
}
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "WarehouseOrderOverviewRpt"));
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = Library.Helper.GetInnerException(ex).Message;
return("");
}
}
public void MeasureDataElement(UIElement element, out double itemHeight)
{
ReportDataObject dataObj = element.To <ReportDataObject>();
if (dataObj.TextWrapping == TextWrapping.NoWrap && dataObj.Width != double.NaN)
{
itemHeight = dataObj.GetPropertyValue("TextHeight").ToDouble();
return;
}
Size size = dataObj.MeasureString();
dataObj.MaxWidth = size.Width;
dataObj.Measure(size);
dataObj.Height = dataObj.DesiredSize.Height;
dataObj.Arrange(new Rect(dataObj.DesiredSize));
dataObj.Height = dataObj.ActualHeight;
itemHeight = dataObj.Height;
}
public bool DeleteTransportConditionItem(int id, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
using (TransportOfferEntities context = CreateContext())
{
var x = context.TransportConditionItem.Where(o => o.TransportConditionItemID == id).FirstOrDefault();
context.TransportConditionItem.Remove(x);
context.SaveChanges();
}
return(true);
}
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(false);
}
}
public string GetExcelReportData(System.Collections.Hashtable filters, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
int? FactoryID = null;
string ModelUD = string.Empty;
string ModelNM = string.Empty;
string ClientUD = string.Empty;
string Season = string.Empty;
if (filters.ContainsKey("FactoryID") && !string.IsNullOrEmpty(filters["FactoryID"].ToString()))
{
FactoryID = Convert.ToInt32(filters["FactoryID"].ToString());
}
if (filters.ContainsKey("ModelUD"))
{
ModelUD = filters["ModelUD"].ToString();
}
if (filters.ContainsKey("ModelNM"))
{
ModelNM = filters["ModelNM"].ToString();
}
if (filters.ContainsKey("ClientUD"))
{
ClientUD = filters["ClientUD"].ToString();
}
if (filters.ContainsKey("Season"))
{
Season = filters["Season"].ToString();
}
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("PurchasingPriceOverviewRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@FactoryID", FactoryID);
adap.SelectCommand.Parameters.AddWithValue("@ModelUD", ModelUD);
adap.SelectCommand.Parameters.AddWithValue("@ModelNM", ModelNM);
adap.SelectCommand.Parameters.AddWithValue("@ClientUD", ClientUD);
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.Fill(ds.PurchasingPriceOverviewRpt_function_GetReportData);
return(Library.Helper.CreateReportFileWithEPPlus(ds, "PurchasingPriceOverview"));
}
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("LogisticsRpt_Function_LogisticsExport", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.TableMappings.Add("Table", "LogisticRpt_LogisticReports_View");
adap.Fill(ds);
return(Library.Helper.CreateReportFileWithEPPlus(ds, "LogisticsReport"));
}
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 ExportExcel(int userId, System.Collections.Hashtable filters, 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("MIDeltaByClientRpt_function_SearchData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (filters.ContainsKey("Season") && !string.IsNullOrEmpty(filters["Season"].ToString()))
{
adap.SelectCommand.Parameters.AddWithValue("@Season", filters["Season"].ToString().Replace("'", "''"));
}
int UserID = 0;
if (filters.ContainsKey("UserID") && filters["UserID"] != null && !string.IsNullOrEmpty(filters["UserID"].ToString()))
{
UserID = Convert.ToInt32(filters["UserID"].ToString());
}
if (!fwFactory.HasSpecialPermission(UserID, "ViewAllSalesData"))
{
filters["SaleID"] = UserID;
}
else
{
filters["SaleID"] = null;
//if (filters.ContainsKey("SaleID") && filters["SaleID"] != null && !string.IsNullOrEmpty(filters["SaleID"].ToString()))
//{
// if (Convert.ToInt32(filters["SaleID"].ToString()) == -1)
// {
// filters["SaleID"] = null;
// }
//}
}
if (filters.ContainsKey("SaleID") && filters["SaleID"] != null && !string.IsNullOrEmpty(filters["SaleID"].ToString()))
{
adap.SelectCommand.Parameters.AddWithValue("@SaleID", filters["SaleID"].ToString().Replace("'", "''"));
}
adap.Fill(ds.MIDeltaByClientRpt_DeltaByClient_View);
ds.AcceptChanges();
return(Library.Helper.CreateReportFileWithEPPlus(ds, "MIDeltaByClient", new List <string>()
{
"MIDeltaByClientRpt_DeltaByClient_View"
}));
}
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 override global::System.Data.DataSet Clone()
{
ReportDataObject cln = ((ReportDataObject)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
public string PrintShowroomOverview(out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success, Message = "Export success"
};
try
{
ReportDataObject ds = new ReportDataObject();
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("ReportMng_ShowroomOverview_function_PrintOverview", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.TableMappings.Add("Table", "ReportMng_ShowroomOverview_View");
adap.Fill(ds);
ds.AcceptChanges();
foreach (var item in ds.ReportMng_ShowroomOverview_View)
{
if (!item.IsShowroomItemThumbnailImageNull())
{
item.ShowroomItemThumbnailImage = FrameworkSetting.Setting.ThumbnailUrl + item.ShowroomItemThumbnailImage;
}
else
{
item.ShowroomItemThumbnailImage = "NONE";
}
if (!item.IsProductThumbnailImageNull())
{
item.ProductThumbnailImage = FrameworkSetting.Setting.ThumbnailUrl + item.ProductThumbnailImage;
}
else
{
item.ProductThumbnailImage = "NONE";
}
}
//dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "ShowroomOverview");
//return string.Empty;
// generate xml file
string result = DALBase.Helper.CreateReportFiles(ds, "ShowroomOverview");
return(result);
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = ex.Message;
notification.DetailMessage.Add(ex.Message);
if (ex.GetBaseException() != null)
{
notification.DetailMessage.Add(ex.GetBaseException().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("DDCRpt_function_GetData", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.SelectCommand.Parameters.AddWithValue("@UserID", userID);
adap.TableMappings.Add("Table", "DDCRpt_ClientInfo_View");
adap.TableMappings.Add("Table1", "DDCRpt_Sale_View");
adap.TableMappings.Add("Table2", "Report_Setting_View");
adap.Fill(ds);
// change string to numeric value
//foreach (ReportDataObject.Report_Setting_ViewRow sRow in ds.Report_Setting_View)
//{
// if (!sRow.IsSettingValueNull())
// {
// sRow.SettingValue = sRow.SettingValue.Replace(".", ",");
// }
//}
// add report param
ReportDataObject.ReportParamRow pRow = ds.ReportParam.NewReportParamRow();
pRow.Season = Season;
pRow.PreSeason = Library.OMSHelper.Helper.GetPrevSeason(Season);
ds.ReportParam.AddReportParamRow(pRow);
ds.AcceptChanges();
return(Library.Helper.CreateReportFileWithEPPlus(ds, "DDCReport", new List <string>()
{
"DDCRpt_ClientInfo_View", "DDCRpt_Sale_View", "Report_Setting_View", "ReportParam"
}));
// generate xml file
//return DALBase.Helper.CreateReportFiles(ds, "DDCReport");
//return Library.Helper.CreateCOMReportFileImportDataOnly(ds, "DDCReport");
}
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 ExportExcel(int userId, System.Collections.Hashtable filters, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
string ArticleCode = null;
string Description = null;
string Season = null;
if (filters.ContainsKey("ArticleCode"))
{
ArticleCode = filters["ArticleCode"].ToString().Replace("'", "''");
}
if (filters.ContainsKey("Description"))
{
Description = filters["Description"].ToString().Replace("'", "''");
}
if (filters.ContainsKey("Season") && filters["Season"] != null && !string.IsNullOrEmpty(filters["Season"].ToString()))
{
Season = filters["Season"].ToString().Replace("'", "''");
}
try
{
System.Data.SqlClient.SqlDataAdapter adap = new System.Data.SqlClient.SqlDataAdapter();
adap.SelectCommand = new System.Data.SqlClient.SqlCommand("StandardCostPriceOverviewRpt_function_ExportProduct", new System.Data.SqlClient.SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.SelectCommand.Parameters.AddWithValue("@ArticleCode", ArticleCode);
adap.SelectCommand.Parameters.AddWithValue("@Description", Description);
adap.TableMappings.Add("Table", "StandardCostPriceOverviewRpt_ProductSearchResult_View");
adap.Fill(ds);
ds.AcceptChanges();
return(Library.Helper.CreateReportFileWithEPPlus(ds, "StandardCostPriceOverviewRpt"));
}
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 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 GetFreeToSaleOverview(bool IsIncludeImage, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success, Message = "Get FTS report success"
};
ReportDataObject ds = new ReportDataObject();
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("ReportMng_action_GetFreeToSale", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@IsIncludeImage", IsIncludeImage);
adap.TableMappings.Add("Table", "ParamTable");
adap.TableMappings.Add("Table1", "ReportMng_FreeToSaleOverview_View");
adap.Fill(ds);
foreach (var item in ds.ReportMng_FreeToSaleOverview_View)
{
if (System.IO.File.Exists(FrameworkSetting.Setting.MediaFullSizeUrl + item.SelectedThumbnailImage))
{
item.SelectedThumbnailImage = FrameworkSetting.Setting.MediaThumbnailUrl + item.SelectedThumbnailImage;
}
else
{
item.SelectedThumbnailImage = "NONE";
}
}
// dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "ReportFreeToSaleOverview");
// generate xml file
string reportName = DALBase.Helper.CreateReportFiles(ds, "ReportFreeToSaleOverview");
if (string.IsNullOrEmpty(reportName))
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = "Can not create report";
return(string.Empty);
}
return(reportName);
}
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("OrderItemOverviewRpt_Function_getReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", season);
//adap.TableMappings.Add("Table", "OrderedItemOverview_ReportView");
adap.Fill(ds.OrderedItemOverview_ReportView);
var result1 = ds.OrderedItemOverview_ReportView;
SqlDataAdapter adap2 = new SqlDataAdapter();
adap2.SelectCommand = new SqlCommand("OrderedItemOverviewRpt_Function_getClienUDs", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap2.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap2.SelectCommand.Parameters.AddWithValue("@Season", season);
ds.EnforceConstraints = false;
adap2.Fill(ds.OrderedOverviewRpt_Client_View);
var result = ds.OrderedOverviewRpt_Client_View;
foreach (var item in result1)
{
string ClientUDs = null;
foreach (var item2 in result)
{
if (item.ModelID == item2.ModelID)
{
ClientUDs += item2.ClientUD + ",";
}
}
item.ClientUDs = ClientUDs.TrimEnd(',');
}
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "OrderedItemOverview"));
}
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 static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
ReportDataObject ds = new ReportDataObject();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
public string GetReportProformaInvoice(string Season, 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("Report_function_GetProformaInvoice", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandTimeout = 500;
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Season", Season);
adap.TableMappings.Add("Table", "ParamTable");
adap.TableMappings.Add("Table1", "Report_ProformaInvoice_View");
adap.TableMappings.Add("Table2", "Report_ProformaInvoice_ByClient_View");
adap.TableMappings.Add("Table3", "SaleOrder");
adap.Fill(ds);
foreach (var item in ds.SaleOrder)
{
int i = 1;
foreach (ReportDataObject.Report_ProformaInvoice_ViewRow rItem in ds.Report_ProformaInvoice_View.Where(o => !o.IsProformaInvoiceNoNull() && o.ProformaInvoiceNo == item.ProformaInvoiceNo && !o.IsPaymentAmountEURNull() && o.PaymentAmountEUR != 0))
{
if (i > 1)
{
rItem["PaymentAmountInUSD"] = DBNull.Value;
rItem["PaymentAmountInEUR"] = DBNull.Value;
rItem["PaymentAmountEUR"] = DBNull.Value;
}
i++;
}
}
// dev
//DALBase.Helper.DevCreateReportXMLSource(ds, "ProformaInvoiceOverview");
//return string.Empty;
// generate xml file
return(DALBase.Helper.CreateReportFiles(ds, "ProformaInvoiceOverview"));
}
catch (Exception ex)
{
notification = new Library.DTO.Notification()
{
Message = ex.Message
};
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(int userID, int?factoryWarsehouseID, int?productionItemTypeID, string validDate, out Notification notification)
{
notification = new Notification();
notification.Type = NotificationType.Success;
ReportDataObject ds = new ReportDataObject();
Framework.DAL.DataFactory frameFactory = new Framework.DAL.DataFactory();
int?companyID = frameFactory.GetCompanyID(userID);
DateTime?currentDate = validDate.ConvertStringToDateTime();
if (!currentDate.HasValue)
{
throw new Exception("Please input valid date!");
}
string startDate = currentDate.Value.ToString("dd/MM/yyyy");
string endDate = currentDate.Value.AddMonths(1).AddDays(-1).ToString("dd/MM/yyyy");
int?currentMonth = currentDate.Value.Month;
int?currentYear = currentDate.Value.Year;
try
{
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("InventoryCostRpt_function_InventoryCostSearchResult", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@FactoryWarehouseID", factoryWarsehouseID);
adap.SelectCommand.Parameters.AddWithValue("@StartDate", startDate);
adap.SelectCommand.Parameters.AddWithValue("@EndDate", DBNull.Value);
adap.SelectCommand.Parameters.AddWithValue("@BranchID", DBNull.Value);
adap.SelectCommand.Parameters.AddWithValue("@ProductionItemTypeID", productionItemTypeID);
adap.TableMappings.Add("Table", "InventoryCostReportDataView");
adap.Fill(ds);
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "InventoryCostRpt"));
}
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(System.Collections.Hashtable filters, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
string fromDate = filters.ContainsKey("fromDate") && filters["fromDate"] != null && !string.IsNullOrEmpty(filters["fromDate"].ToString().Trim()) ? filters["fromDate"].ToString().Trim() : null;
string toDate = filters.ContainsKey("toDate") && filters["toDate"] != null && !string.IsNullOrEmpty(filters["toDate"].ToString().Trim()) ? filters["toDate"].ToString().Trim() : null;
int? supplierID = filters.ContainsKey("supplierID") && filters["supplierID"] != null && !string.IsNullOrEmpty(filters["supplierID"].ToString().Trim()) ? (int?)Convert.ToInt32(filters["supplierID"].ToString().Trim()) : null;
string status = filters.ContainsKey("status") && filters["status"] != null && !string.IsNullOrEmpty(filters["status"].ToString().Trim()) ? filters["status"].ToString().Trim() : null;
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("PurchaseOrderTrackingRpt_function_PurchaseOrderTrackingSearchResult", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@fromDate", fromDate);
adap.SelectCommand.Parameters.AddWithValue("@toDate", toDate);
adap.SelectCommand.Parameters.AddWithValue("@supplierID", supplierID);
adap.SelectCommand.Parameters.AddWithValue("@status", status);
adap.TableMappings.Add("Table", "POTracking");
adap.Fill(ds.PurchaseOrderTracking);
SqlDataAdapter adap2 = new SqlDataAdapter();
adap2.SelectCommand = new SqlCommand("PurchaseOrderTrackingRpt_function_PurchaseOrderTrackingDetail", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap2.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap2.SelectCommand.Parameters.AddWithValue("@fromDate", fromDate);
adap2.SelectCommand.Parameters.AddWithValue("@toDate", toDate);
adap2.SelectCommand.Parameters.AddWithValue("@supplierID", supplierID);
adap2.SelectCommand.Parameters.AddWithValue("@status", status);
adap.TableMappings.Add("Table1", "POTrackingDetail");
adap2.Fill(ds.PurchaseOrderTrackingDetail);
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "PurchaseOrderTrackingRpt"));
}
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, int?factoryRawMaterialID, string startDate, string endDate, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
Framework.DAL.DataFactory fwFactory = new Framework.DAL.DataFactory();
int? companyID = fwFactory.GetCompanyID(userId);
decimal exchangeRate = 1;
try
{
using (RevenueCostingRptEntities context = CreateContext())
{
exchangeRate = context.MasterExchangeRateMng_function_GetExchangeRate(DateTime.Now, "USD").FirstOrDefault().ExchangeRate.Value;
}
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("RevenueCostingRpt_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@SupplierID", factoryRawMaterialID);
adap.SelectCommand.Parameters.AddWithValue("@StartDate", startDate);
adap.SelectCommand.Parameters.AddWithValue("@EndDate", endDate);
adap.TableMappings.Add("Table", "RevenueCosting_Report");
adap.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ReportDataObject.OtherDataRow dateRange = ds.OtherData.NewOtherDataRow();
dateRange.StartDate = startDate;
dateRange.EndDate = endDate;
dateRange.ExchangeRate = exchangeRate;
ds.OtherData.AddOtherDataRow(dateRange);
}
return(Library.Helper.CreateReportFileWithEPPlus2(ds, "RevenueCostingRpt"));
}
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);
}
}
