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