public string GetReportData(int userId, int purchasingCreditNoteID, out Library.DTO.Notification notification)
{
notification = new Library.DTO.Notification()
{
Type = Library.DTO.NotificationType.Success
};
ReportDataObject ds = new ReportDataObject();
try
{
//check permission on booking
if (fwFactory.CheckPurchasingCreditNotePermission(userId, purchasingCreditNoteID) == 0)
{
throw new Exception("You do not have access permission on this invoice");
}
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = new SqlCommand("PurchasingCreditNoteMng_function_GetReportData", new SqlConnection(Library.Helper.GetSQLConnectionString()));
adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@PurchasingCreditNoteID", purchasingCreditNoteID);
adap.TableMappings.Add("Table", "PurchasingCreditNoteMng_PurchasingCreditNote_ReportView");
adap.TableMappings.Add("Table1", "PurchasingCreditNoteMng_PurchasingCreditNoteDetail_ReportView");
adap.TableMappings.Add("Table2", "PurchasingCreditNoteMng_PurchasingCreditNoteSparepartDetail_ReportView");
adap.TableMappings.Add("Table3", "PurchasingCreditNoteMng_PurchasingCreditNoteExtendDetail_ReportView");
adap.TableMappings.Add("Table4", "PurchasingCreditNoteMng_LoadingPlan_ReportView");
adap.Fill(ds);
//parese invoice
ReportDataObject.InvoiceRow drInvoice = ds.Invoice.NewInvoiceRow();
ReportDataObject.PurchasingCreditNoteMng_PurchasingCreditNote_ReportViewRow drOriginInvoice = ds.PurchasingCreditNoteMng_PurchasingCreditNote_ReportView.FirstOrDefault();
drInvoice.InvoiceNo = drOriginInvoice.CreditNoteNo;
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;
ds.Invoice.AddInvoiceRow(drInvoice);
//parse detail data
ReportDataObject.InvoiceDetailRow drInvoiceDetail;
foreach (var loadingplan in ds.PurchasingCreditNoteMng_LoadingPlan_ReportView)
{
drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow();
drInvoiceDetail.Description = loadingplan.ContainerInfo;
ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail);
foreach (var product in ds.PurchasingCreditNoteMng_PurchasingCreditNoteDetail_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.UnitPrice = product.IsUnitPriceNull() ? 0 : product.UnitPrice;
drInvoiceDetail.Amount = product.IsAmountNull() ? 0 : product.Amount;
ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail);
}
foreach (var sparepart in ds.PurchasingCreditNoteMng_PurchasingCreditNoteSparepartDetail_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;
drInvoiceDetail.UnitPrice = sparepart.IsUnitPriceNull() ? 0 : sparepart.UnitPrice;
drInvoiceDetail.Amount = sparepart.IsAmountNull() ? 0 : sparepart.Amount;
ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail);
}
}
foreach (var item in ds.PurchasingCreditNoteMng_PurchasingCreditNoteExtendDetail_ReportView)
{
drInvoiceDetail = ds.InvoiceDetail.NewInvoiceDetailRow();
drInvoiceDetail.Order_Client = "";
drInvoiceDetail.ArticleCode = "";
drInvoiceDetail.Description = item.Description;
drInvoiceDetail.Quantity = 0;
drInvoiceDetail.UnitPrice = 0;
drInvoiceDetail.Amount = item.IsAmountNull() ? 0 : item.Amount;
ds.InvoiceDetail.AddInvoiceDetailRow(drInvoiceDetail);
}
//generate schema
//Library.Helper.DevCreateReportXMLSource(ds, "PurchasingCreditNote");
// generate xml file
string reportName = string.Empty;
if (drOriginInvoice.IsIssuesByFactory)
{
reportName = "PurchasingCreditNoteFactory";
}
else if (drOriginInvoice.IsIssuesByFurnindo)
{
reportName = "PurchasingCreditNoteFurnindo";
}
return(Library.Helper.CreateReportFiles(ds, 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 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);
}
}