// report By Container
public string GetReportDataByContainer(int packingListID, 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("PackingListMng_function_GetReportData", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@PackingListID", packingListID);
adap.TableMappings.Add("Table", "PackingListMng_PackingList_ReportView");
adap.TableMappings.Add("Table1", "PackingListMng_PackingListDetail_ReportView");
adap.TableMappings.Add("Table2", "PackingListMng_PackingListSparepartDetail_ReportView");
adap.TableMappings.Add("Table3", "PackingListMng_LoadingPlan_ReportView");
adap.Fill(ds);
//parese invoice
ReportDataObject.PackingListRow drPackingList = ds.PackingList.NewPackingListRow();
ReportDataObject.PackingListMng_PackingList_ReportViewRow drOrigin = ds.PackingListMng_PackingList_ReportView.FirstOrDefault();
drPackingList.InvoiceNo = drOrigin.InvoiceNo;
drPackingList.BLNo = drOrigin.IsBLNoNull() ? "" : drOrigin.BLNo;
drPackingList.POLName = drOrigin.IsPOLNameNull() ? "" : drOrigin.POLName;
drPackingList.PODName = drOrigin.IsPODNameNull() ? "" : drOrigin.PODName;
drPackingList.TotalQuantityShipped = drOrigin.IsTotalQuantityShippedNull() ? 0 : drOrigin.TotalQuantityShipped;
drPackingList.TotalPKGs = drOrigin.IsTotalPKGsNull() ? 0 : drOrigin.TotalPKGs;
drPackingList.TotalNettWeight = drOrigin.IsTotalNettWeightNull() ? 0 : drOrigin.TotalNettWeight;
drPackingList.TotalKGs = drOrigin.IsTotalKGsNull() ? 0 : drOrigin.TotalKGs;
drPackingList.TotalCBMs = drOrigin.IsTotalCBMsNull() ? 0 : drOrigin.TotalCBMs;
ds.PackingList.AddPackingListRow(drPackingList);
//parse detail data
ReportDataObject.PackingListDetailRow drPackingListDetail;
foreach (var loadingplan in ds.PackingListMng_LoadingPlan_ReportView)
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Description = loadingplan.ContainerInfo;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
foreach (var product in ds.PackingListMng_PackingListDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Order_Client = product.ProformaInvoiceNo + " / " + product.ClientUD;
drPackingListDetail.ArticleCode = product.ArticleCode;
drPackingListDetail.Description = product.Description;
drPackingListDetail.QuantityShipped = product.IsQuantityShippedNull() ? 0 : product.QuantityShipped;
drPackingListDetail.PKGs = product.IsPKGsNull() ? 0 : product.PKGs;
drPackingListDetail.NettWeight = product.IsNettWeightNull() ? 0 : product.NettWeight;
drPackingListDetail.KGs = product.IsKGsNull() ? 0 : product.KGs;
drPackingListDetail.CBMs = product.IsCBMsNull() ? 0 : product.CBMs;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
}
foreach (var sparepart in ds.PackingListMng_PackingListSparepartDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Order_Client = sparepart.ProformaInvoiceNo + " / " + sparepart.ClientUD;
drPackingListDetail.ArticleCode = sparepart.ArticleCode;
drPackingListDetail.Description = sparepart.Description;
drPackingListDetail.QuantityShipped = sparepart.IsQuantityShippedNull() ? 0 : sparepart.QuantityShipped;
drPackingListDetail.PKGs = sparepart.IsPKGsNull() ? 0 : sparepart.PKGs;
drPackingListDetail.NettWeight = sparepart.IsNettWeightNull() ? 0 : sparepart.NettWeight;
drPackingListDetail.KGs = sparepart.IsKGsNull() ? 0 : sparepart.KGs;
drPackingListDetail.CBMs = sparepart.IsCBMsNull() ? 0 : sparepart.CBMs;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
}
}
return(Library.Helper.CreateReportFileWithEPPlus(ds, "PackingListByContainer"));
}
catch (Exception ex)
{
notification = new Library.DTO.Notification()
{
Message = ex.Message
};
return(string.Empty);
}
}
public string GetReportData(int packingListID, 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("PackingListMng_function_GetReportData", new SqlConnection(DALBase.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@PackingListID", packingListID);
adap.TableMappings.Add("Table", "PackingListMng_PackingList_ReportView");
adap.TableMappings.Add("Table1", "PackingListMng_PackingListDetail_ReportView");
adap.TableMappings.Add("Table2", "PackingListMng_PackingListSparepartDetail_ReportView");
adap.TableMappings.Add("Table3", "PackingListMng_LoadingPlan_ReportView");
adap.TableMappings.Add("Table4", "PackingListMng_PackingListSampleDetail_ReportView");
adap.Fill(ds);
//parese invoice
ReportDataObject.PackingListRow drPackingList = ds.PackingList.NewPackingListRow();
ReportDataObject.PackingListMng_PackingList_ReportViewRow drOrigin = ds.PackingListMng_PackingList_ReportView.FirstOrDefault();
drPackingList.InvoiceNo = drOrigin.InvoiceNo;
drPackingList.BLNo = drOrigin.IsBLNoNull() ? "" : drOrigin.BLNo;
drPackingList.POLName = drOrigin.IsPOLNameNull() ? "" : drOrigin.POLName;
drPackingList.PODName = drOrigin.IsPODNameNull() ? "" : drOrigin.PODName;
drPackingList.TotalQuantityShipped = drOrigin.IsTotalQuantityShippedNull() ? 0 : drOrigin.TotalQuantityShipped;
drPackingList.TotalPKGs = drOrigin.IsTotalPKGsNull() ? 0 : drOrigin.TotalPKGs;
drPackingList.TotalNettWeight = drOrigin.IsTotalNettWeightNull() ? 0 : drOrigin.TotalNettWeight;
drPackingList.TotalKGs = drOrigin.IsTotalKGsNull() ? 0 : drOrigin.TotalKGs;
drPackingList.TotalCBMs = drOrigin.IsTotalCBMsNull() ? 0 : drOrigin.TotalCBMs;
drPackingList.ShippedDate = drOrigin.IsShipedDateNull() ? "" : drOrigin.ShipedDate.ToString("dd/MM/yyyy");
drPackingList.SupplierNM = drOrigin.IsSupplierNMNull() ? "" : drOrigin.SupplierNM;
drPackingList.Address = drOrigin.IsAddressNull() ? "" : drOrigin.Address;
drPackingList.InvoiceDate = drOrigin.IsInvoiceDateNull() ? "" : drOrigin.InvoiceDate.ToString("dd/MM/yyyy");
drPackingList.FSCCode = drOrigin.IsFSCCodeNull() ? "" : drOrigin.FSCCode;
ds.PackingList.AddPackingListRow(drPackingList);
//parse detail data
ReportDataObject.PackingListDetailRow drPackingListDetail;
foreach (var loadingplan in ds.PackingListMng_LoadingPlan_ReportView)
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Description = loadingplan.ContainerInfo;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
foreach (var product in ds.PackingListMng_PackingListDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Order_Client = product.ProformaInvoiceNo + " / " + product.ClientUD;
drPackingListDetail.ArticleCode = product.ArticleCode;
drPackingListDetail.Description = product.Description;
drPackingListDetail.QuantityShipped = product.IsQuantityShippedNull() ? 0 : product.QuantityShipped;
drPackingListDetail.PKGs = product.IsPKGsNull() ? 0 : product.PKGs;
drPackingListDetail.NettWeight = product.IsNettWeightNull() ? 0 : product.NettWeight;
drPackingListDetail.KGs = product.IsKGsNull() ? 0 : product.KGs;
drPackingListDetail.CBMs = product.IsCBMsNull() ? 0 : product.CBMs;
drPackingListDetail.HSCode = product.IsHSCodeNull() ? "" : product.HSCode;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
//ArtCode, ArtName...
if (!string.IsNullOrEmpty(product.ClientArticleCode))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.HSCode = "CLIENT ART CODE: ";
drPackingListDetail.Description = product.ClientArticleCode;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
}
if (!string.IsNullOrEmpty(product.ClientArticleName))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.HSCode = "CLIENT ART NAME: ";
drPackingListDetail.Description = product.ClientArticleName;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
}
}
foreach (var sparepart in ds.PackingListMng_PackingListSparepartDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Order_Client = sparepart.ProformaInvoiceNo + " / " + sparepart.ClientUD;
drPackingListDetail.ArticleCode = sparepart.ArticleCode;
drPackingListDetail.Description = sparepart.Description;
drPackingListDetail.QuantityShipped = sparepart.IsQuantityShippedNull() ? 0 : sparepart.QuantityShipped;
drPackingListDetail.PKGs = sparepart.IsPKGsNull() ? 0 : sparepart.PKGs;
drPackingListDetail.NettWeight = sparepart.IsNettWeightNull() ? 0 : sparepart.NettWeight;
drPackingListDetail.KGs = sparepart.IsKGsNull() ? 0 : sparepart.KGs;
drPackingListDetail.CBMs = sparepart.IsCBMsNull() ? 0 : sparepart.CBMs;
drPackingListDetail.HSCode = sparepart.IsHSCodeNull() ? "" : sparepart.HSCode;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
}
foreach (var sample in ds.PackingListMng_PackingListSampleDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo))
{
drPackingListDetail = ds.PackingListDetail.NewPackingListDetailRow();
drPackingListDetail.Order_Client = sample.ProformaInvoiceNo + " / " + sample.ClientUD;
drPackingListDetail.ArticleCode = sample.ArticleCode;
drPackingListDetail.Description = sample.Description;
drPackingListDetail.QuantityShipped = sample.IsQuantityShippedNull() ? 0 : sample.QuantityShipped;
drPackingListDetail.PKGs = sample.IsPKGsNull() ? 0 : sample.PKGs;
drPackingListDetail.NettWeight = sample.IsNettWeightNull() ? 0 : sample.NettWeight;
drPackingListDetail.KGs = sample.IsKGsNull() ? 0 : sample.KGs;
drPackingListDetail.CBMs = sample.IsCBMsNull() ? 0 : sample.CBMs;
drPackingListDetail.HSCode = sample.IsHSCodeNull() ? "" : sample.HSCode;
ds.PackingListDetail.AddPackingListDetailRow(drPackingListDetail);
}
}
//generate schema
//DALBase.Helper.DevCreateReportXMLSource(ds, "PackingList");
// generate xml file
string reportFile = DALBase.Helper.CreateReportFiles(ds, "PackingList");
return(reportFile = reportFile + ".xlsm");
}
catch (Exception ex)
{
notification.Type = Library.DTO.NotificationType.Error;
notification.Message = Library.Helper.HandleExceptionSingleLine(ex, ds);
return(string.Empty);
}
}
