public string GetReportData(int iRequesterID, int purchasingInvoiceID, out Library.DTO.Notification notification) { notification = new Library.DTO.Notification() { Type = Library.DTO.NotificationType.Success }; ReportDataObject ds = new ReportDataObject(); try { //check permission on invoice if (fwFactory.CheckPurchasingInvoicePermission(iRequesterID, purchasingInvoiceID) == 0) { throw new Exception("You do not have access permission on this invoice"); } int userOfficeID = fwFactory.GetUserOffice(iRequesterID); SqlDataAdapter adap = new SqlDataAdapter(); adap.SelectCommand = new SqlCommand("PurchasingInvoiceMng_function_GetReportData", new SqlConnection(DALBase.Helper.GetSQLConnectionString())); adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure; adap.SelectCommand.Parameters.AddWithValue("@PurchasingInvoiceID", purchasingInvoiceID); adap.TableMappings.Add("Table", "PurchasingInvoiceMng_PurchasingInvoice_ReportView"); adap.TableMappings.Add("Table1", "PurchasingInvoiceMng_PurchasingInvoiceDetail_ReportView"); adap.TableMappings.Add("Table2", "PurchasingInvoiceMng_PurchasingInvoiceSparepartDetail_ReportView"); adap.TableMappings.Add("Table3", "PurchasingInvoiceMng_LoadingPlan_ReportView"); adap.TableMappings.Add("Table4", "PurchasingInvoiceMng_PurchasingInvoiceSampleDetail_ReportView"); adap.Fill(ds); //parese invoice ReportDataObject.InvoiceRow drInvoice = ds.Invoice.NewInvoiceRow(); ReportDataObject.PurchasingInvoiceMng_PurchasingInvoice_ReportViewRow drOriginInvoice = ds.PurchasingInvoiceMng_PurchasingInvoice_ReportView.FirstOrDefault(); drInvoice.InvoiceNo = drOriginInvoice.IsInvoiceNoNull() ? "" : drOriginInvoice.InvoiceNo; drInvoice.BLNo = drOriginInvoice.IsBLNoNull() ? "" : drOriginInvoice.BLNo; drInvoice.POLName = drOriginInvoice.IsPOLNameNull() ? "" : drOriginInvoice.POLName; drInvoice.PODName = drOriginInvoice.IsPODNameNull() ? "" : drOriginInvoice.PODName; drInvoice.TotalQuantity = drOriginInvoice.IsTotalQuantityNull() ? 0 : drOriginInvoice.TotalQuantity; drInvoice.TotalAmount = drOriginInvoice.IsTotalQuantityNull() ? 0 : drOriginInvoice.TotalAmount; drInvoice.SupplierNM = drOriginInvoice.IsSupplierNMNull() ? "" : drOriginInvoice.SupplierNM; drInvoice.Address = drOriginInvoice.IsAddressNull() ? "" : drOriginInvoice.Address; drInvoice.Director = drOriginInvoice.IsDirectorNull() ? "" : drOriginInvoice.Director; drInvoice.BankName = drOriginInvoice.IsBankNameNull() ? "" : drOriginInvoice.BankName; drInvoice.BankAddress = drOriginInvoice.IsBankAddressNull() ? "" : drOriginInvoice.BankAddress; drInvoice.BankSwiftCode = drOriginInvoice.IsBankSwiftCodeNull() ? "" : drOriginInvoice.BankSwiftCode; drInvoice.BankAccountNo = drOriginInvoice.IsBankAccountNoNull() ? "" : drOriginInvoice.BankAccountNo; drInvoice.InvoiceDate = drOriginInvoice.IsInvoiceDateNull() ? "" : drOriginInvoice.InvoiceDate; drInvoice.IsBuyingOrangePie = drOriginInvoice.IsIsBuyingOrangePieNull() ? 0 : drOriginInvoice.IsBuyingOrangePie; drInvoice.ShippedDate = drOriginInvoice.IsShippedDateNull() ? "" : drOriginInvoice.ShippedDate; drInvoice.Remark = drOriginInvoice.IsRemarkNull() ? "" : drOriginInvoice.Remark; drInvoice.TotalDeposit = drOriginInvoice.IsTotalDepositNull() ? 0 : drOriginInvoice.TotalDeposit; drInvoice.FSCCode = drOriginInvoice.IsFSCCodeNull() ? "" : drOriginInvoice.FSCCode; ds.Invoice.AddInvoiceRow(drInvoice); //parse detail data ReportDataObject.InvoiceDetailRow drInvoiceDetail; foreach (var loadingplan in ds.PurchasingInvoiceMng_LoadingPlan_ReportView) { drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow(); drInvoiceDetail.Description = loadingplan.ContainerInfo; ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail); foreach (var product in ds.PurchasingInvoiceMng_PurchasingInvoiceDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo)) { drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow(); drInvoiceDetail.Order_Client = product.ProformaInvoiceNo + " / " + product.ClientUD; drInvoiceDetail.ArticleCode = product.ArticleCode; drInvoiceDetail.Description = product.Description; drInvoiceDetail.Quantity = product.IsQuantityNull() ? 0 : product.Quantity; drInvoiceDetail.HSCode = product.IsHSCodeNull() ? "" : product.HSCode; if (userOfficeID == 4) //user in factory { product.UnitPrice = 0; drInvoiceDetail.UnitPrice = product.IsFactoryPriceNull() ? 0 : product.FactoryPrice; } else // user in eurofar { if (drOriginInvoice.IsIssuesByFactory) { drInvoiceDetail.UnitPrice = product.IsFactoryPriceNull() ? 0 : product.FactoryPrice; } else if (drOriginInvoice.IsIssuesByFurnindo) { drInvoiceDetail.UnitPrice = product.IsUnitPriceNull() ? 0 : product.UnitPrice; } } drInvoiceDetail.Amount = drInvoiceDetail.UnitPrice * drInvoiceDetail.Quantity; ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail); //ArtCode, ArtName... if (!string.IsNullOrEmpty(product.ClientArticleCode)) { drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow(); drInvoiceDetail.HSCode = "CLIENT ART CODE: "; drInvoiceDetail.Description = product.ClientArticleCode; ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail); } if (!string.IsNullOrEmpty(product.ClientArticleName)) { drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow(); drInvoiceDetail.HSCode = "CLIENT ART NAME: "; drInvoiceDetail.Description = product.ClientArticleName; ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail); } } foreach (var sparepart in ds.PurchasingInvoiceMng_PurchasingInvoiceSparepartDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo)) { drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow(); drInvoiceDetail.Order_Client = sparepart.ProformaInvoiceNo + " / " + sparepart.ClientUD; drInvoiceDetail.ArticleCode = sparepart.ArticleCode; drInvoiceDetail.Description = sparepart.Description; drInvoiceDetail.Quantity = sparepart.IsQuantityNull() ? 0 : sparepart.Quantity; if (userOfficeID == 4) { sparepart.UnitPrice = 0; drInvoiceDetail.UnitPrice = sparepart.IsFactoryPriceNull() ? 0 : sparepart.FactoryPrice; } else { if (drOriginInvoice.IsIssuesByFactory) { drInvoiceDetail.UnitPrice = sparepart.IsFactoryPriceNull() ? 0 : sparepart.FactoryPrice; } else if (drOriginInvoice.IsIssuesByFurnindo) { drInvoiceDetail.UnitPrice = sparepart.IsUnitPriceNull() ? 0 : sparepart.UnitPrice; } } drInvoiceDetail.Amount = drInvoiceDetail.UnitPrice * drInvoiceDetail.Quantity; ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail); } foreach (var sample in ds.PurchasingInvoiceMng_PurchasingInvoiceSampleDetail_ReportView.Where(o => o.ContainerInfo == loadingplan.ContainerInfo)) { drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow(); drInvoiceDetail.Order_Client = sample.ProformaInvoiceNo + " / " + sample.ClientUD; drInvoiceDetail.ArticleCode = sample.ArticleCode; drInvoiceDetail.Description = sample.Description; drInvoiceDetail.Quantity = sample.IsQuantityNull() ? 0 : sample.Quantity; drInvoiceDetail.HSCode = sample.IsHSCodeNull() ? "" : sample.HSCode; if (userOfficeID == 4) //user in factory { sample.UnitPrice = 0; drInvoiceDetail.UnitPrice = sample.IsFactoryPriceNull() ? 0 : sample.FactoryPrice; } else // user in eurofar { if (drOriginInvoice.IsIssuesByFactory) { drInvoiceDetail.UnitPrice = sample.IsFactoryPriceNull() ? 0 : sample.FactoryPrice; } else if (drOriginInvoice.IsIssuesByFurnindo) { drInvoiceDetail.UnitPrice = sample.IsUnitPriceNull() ? 0 : sample.UnitPrice; } } drInvoiceDetail.Amount = drInvoiceDetail.UnitPrice * drInvoiceDetail.Quantity; ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail); } } //generate schema //DALBase.Helper.DevCreateReportXMLSource(ds, "PurchasingInvoice"); // generate xml file string reportName = string.Empty; if (drOriginInvoice.IsIssuesByFactory) { reportName = "PurchasingInvoiceFactory"; } else if (drOriginInvoice.IsIssuesByFurnindo) { reportName = "PurchasingInvoiceFurnindo"; } return(DALBase.Helper.CreateReportFiles(ds, reportName)); } catch (Exception ex) { notification.Type = Library.DTO.NotificationType.Error; notification.Message = Library.Helper.HandleExceptionSingleLine(ex, ds); return(string.Empty); } }