public IHttpActionResult GetInvoiceReportList(InvoiceReportModel _InvoiceRModel)
{
try
{
SqlParameter[] parameters = new SqlParameter[9];
parameters[0] = new SqlParameter("InvoiceFromDate", SqlDbType.VarChar, 200);
parameters[0].Value = "'" + _InvoiceRModel.InvoiceFromDate + "'";
parameters[1] = new SqlParameter("InvoiceToDate", SqlDbType.VarChar, 200);
parameters[1].Value = "'" + _InvoiceRModel.InvoiceToDate + "'";
parameters[2] = new SqlParameter("InvoiceNo", SqlDbType.VarChar, 200);
parameters[2].Value = "'" + _InvoiceRModel.InvoiceNo + "'";
parameters[3] = new SqlParameter("InvoiceValue", SqlDbType.VarChar, 200);
parameters[3].Value = "'" + _InvoiceRModel.InvoiceValue + "'";
parameters[4] = new SqlParameter("InvoiceStatus", SqlDbType.VarChar, 200);
parameters[4].Value = "'" + _InvoiceRModel.InvoiceStatus + "'";
parameters[5] = new SqlParameter("LicenseeName", SqlDbType.VarChar, 200);
parameters[5].Value = "'" + _InvoiceRModel.LicenseeName + "'";
parameters[6] = new SqlParameter("Country", SqlDbType.VarChar, 200);
parameters[6].Value = "'" + _InvoiceRModel.Country + "'";
parameters[7] = new SqlParameter("State", SqlDbType.VarChar, 200);
parameters[7].Value = "'" + _InvoiceRModel.State + "'";
parameters[8] = new SqlParameter("City", SqlDbType.VarChar, 200);
parameters[8].Value = "'" + _InvoiceRModel.City + "'";
var _GetInvoiceReport_Detail = _dbContext.ExecuteStoredProcedureListNewData <InvoiceReportModel>("Proc_InvoiceReport_get", parameters).ToList();
return(Json(_GetInvoiceReport_Detail));
}
catch (Exception ex)
{
return(null);
}
}
public InvoiceReportModel Report(string invoiceNo)
{
Invoice invoice = UnitOfWork.Invoices.Get().FirstOrDefault(x => x.InvoiceNo == invoiceNo);
InvoiceReportModel result = new InvoiceReportModel()
{
InvoiceNo = invoice.InvoiceNo,
InvoiceDate = invoice.Date,
CustomerId = invoice.Customer.Id,
CustomerName = invoice.Customer.Name,
CustomerAddress = invoice.Customer.Address,
ZipCode = invoice.Customer.Town.Zip,
Town = invoice.Customer.Town.Name,
Salesperson = invoice.Agent.Name,
OrderDate = invoice.CreatedOn,
ShippedDate = invoice.ShippedOn,
ShippedVia = invoice.Shipper.Name,
InvoiceSubtotal = invoice.SubTotal,
VatAmount = invoice.VatAmount,
Shipping = invoice.Shipping,
InvoiceTotal = invoice.Total
};
result.Products = UnitOfWork.Items.Get().Where(x => x.Invoice.Id == invoice.Id).ToList()
.Select(x => Factory.Create(x.Product.Id, x.Product.Name, x.Product.Unit, x.Quantity, x.Price, x.SubTotal)).ToList();
return(result);
}
public ActionResult Accounts()
{
InvoiceReportModel model = new InvoiceReportModel();
model.Id = 0;
model.ReportDataSource = new List <InvoiceDataset>();
return(View(model));
}
public ActionResult InvoiceReport(int id)
{
InvoiceReportModel model = new InvoiceReportModel();
model.Invoice = InvoiceServices.GetInvoice(id);
model.Account = AccountServices.GetAccount(model.Invoice.AccountName);
model.Header = new ReportHeaderModel("Invoice");
return(View(model));
}
public InvoiceReportModel Report(int InvoiceId)
{
InvoiceReportModel result = new InvoiceReportModel();
var Invoices = _unitOfWork.Invoices.Get().Where(x => x.Id == InvoiceId).ToList();
foreach (var item in Invoices)
{
result.InvoiceNo = item.InvoiceNo;
result.InvoiceDate = item.Date;
result.CustomerId = item.Customer.Id;
result.CustomerName = item.Customer.Name;
result.CustomerAddress = item.Customer.Address;
result.ZipCode = item.Customer.Town.Zip;
result.Town = item.Customer.Town.Name;
result.Salesperson = item.Agent.Name;
result.OrderDate = item.History[0].Date;
result.ShippedDate = item.ShippedOn;
result.ShippedVia = item.Shipper.Name;
result.InvoiceSubtotal = item.SubTotal;
result.VatAmount = item.VatAmount;
result.Shipping = item.Shipping;
result.InvoiceTotal = item.VatAmount + item.SubTotal;
}
result.Items = _unitOfWork.Items.Get().Where(x => x.Invoice.Id == InvoiceId)
.GroupBy(x => new InvoiceItems
{
ProductId = x.Id,
ProductName = x.Product.Name,
ProductUnit = x.Product.Unit,
Quantity = x.Quantity,
Price = x.Price,
Subtotal = x.Price * x.Quantity
})
.Select(x => new InvoiceItems
{
ProductId = x.Key.ProductId,
ProductName = x.Key.ProductName,
ProductUnit = x.Key.ProductUnit,
Quantity = x.Key.Quantity,
Price = x.Key.Price,
Subtotal = x.Key.Subtotal
})
.ToList();
return(result);
}
public IHttpActionResult GetLicenseeNameList(InvoiceReportModel _InvoiceRModel)
{
try
{
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("LicenseeName", SqlDbType.VarChar, 200);
parameters[0].Value = "'" + _InvoiceRModel.LicenseeName + "'";
var _GetLicenseeName = _dbContext.ExecuteStoredProcedureListNewData <InvoiceReportModel>("Proc_LicenseeName_ForInvoiceReport_get", parameters).ToList();
return(Json(_GetLicenseeName));
}
catch (Exception ex)
{
return(null);
}
}
public InvoiceReportModel Report(int id)
{
Invoice Invoice = UnitOfWork.Invoices.Get(id);
if (Invoice == null)
{
throw new Exception("Invoice not found");
}
InvoiceReportModel result = new InvoiceReportModel()
{
InvoiceId = id,
InvoiceNo = Invoice.InvoiceNo,
InvoiceDate = Invoice.Date,
CustomerId = Invoice.Customer.Id,
CustomerName = Invoice.Customer.Name,
CustomerAddress = Invoice.Customer.Address,
ZipCode = Invoice.Customer.Town.Zip,
Town = Invoice.Customer.Town.Name,
Salesperson = Invoice.Agent.Name,
ShippedDate = (Invoice.ShippedOn == null) ? DateTime.Now : Invoice.ShippedOn.Value,
ShippedVia = (Invoice.Shipper == null) ? "" : Invoice.Shipper.Name,
InvoiceSubtotal = Invoice.SubTotal,
VatAmount = Invoice.VatAmount,
Shipping = Invoice.Shipping,
InvoiceTotal = Invoice.Total
};
foreach (var item in Invoice.History)
{
DateTime Date;
if (item.Status == 0)
{
Date = item.Date;
result.OrderDate = Date;
break;
}
}
result.Products = UnitOfWork.Items.Get().Where(x => x.Invoice.Id == id).ToList()
.Select(x => Factory.Create(x.Product.Id, x.Product.Name, x.Product.Unit, x.Price, x.Quantity, x.SubTotal))
.ToList();
return(result);
}
public override string Populate()
{
InvoiceReportModel model = new InvoiceReportModel
{
Id = data.Id,
Customer = data.Customer,
TimeEntries = data.TimeEntries.Select(y => new TimeEntryFormViewModel()
{
Description = y.Description,
TotalPrice = y.TotalPrice
}).ToList(),
User = data.User,
Date = data.Date
};
var engine = new RazorLightEngineBuilder()
.UseEmbeddedResourcesProject(typeof(Program))
.UseMemoryCachingProvider()
.Build();
string content = TemplateLogic.GetTemplate(name);
return(engine.CompileRenderStringAsync(name, content, model).Result);
}
public async Task <byte[]> GenerateInvoice(InvoiceReportModel model)
{
using (var package = new ExcelPackage())
{
var workSheet = package.Workbook.Worksheets.Add($"Invoice: {model.InvoiceDate}");
workSheet.Cells.Style.Font.Size = 14;
ExcelRange rg = workSheet.Cells[1, 1, 6, 7];
rg.Style.Fill.PatternType = ExcelFillStyle.Solid;
rg.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.YellowGreen);
workSheet.Cells["B5"].Value = "Invoice";
workSheet.Cells["B5"].Style.Font.Size = 28;
workSheet.Cells["B5"].Style.Font.Color.SetColor(System.Drawing.Color.White);
workSheet.Cells["F1"].Value = "Veterinary clinic";
workSheet.Cells["F2"].Value = "7 Galycka street";
workSheet.Cells["F3"].Value = "Ivano-Frankivsk";
workSheet.Cells["F4"].Value = "Ukraine";
workSheet.Cells["F5"].Value = "564-555-1234";
workSheet.Cells["F1:F5"].Style.Font.Color.SetColor(System.Drawing.Color.White);
workSheet.Cells["F1:F5"].Style.Font.Size = 14;
workSheet.Cells["B7"].Value = "Bill to:";
workSheet.Cells["B8"].Value = $"{model.FirstName} {model.LastName}";
workSheet.Cells["B9"].Value = model.PhoneNumber;
workSheet.Cells["B10"].Value = model.EmailAddress;
workSheet.Cells["F7"].Value = "DATE";
workSheet.Row(7).Style.Font.Bold = true;
workSheet.Cells["F8"].Value = model.InvoiceDate;
workSheet.Cells["F9"].Value = "INVOICE DUE DATE";
workSheet.Cells["F10"].Value = model.InvoiceDueDate;
workSheet.Cells["F11"].Value = "1234-5678-9012-3456";
workSheet.Cells["F11"].Style.Font.Size = 14;
workSheet.Cells["A11:G11"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells["B12"].Value = "Performed procedures:";
workSheet.Cells["D12"].Value = "Description";
workSheet.Cells["F12"].Value = "PRICE";
workSheet.Row(12).Style.Font.Bold = true;
int procedureCount = 0;
if (model.Procedures != null && model.Procedures.Count > 0)
{
for (int i = 1; i <= model.Procedures.Count; i++)
{
workSheet.Cells["B" + (12 + i).ToString()].Value = $"{model.Procedures[i - 1].Name}";
workSheet.Cells["D" + (12 + i).ToString()].Value = $"{model.Procedures[i - 1].Description}";
workSheet.Cells["F" + (12 + i).ToString()].Value = $"{model.Procedures[i - 1].Price}";
workSheet.Row(12 + i).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
workSheet.Row(12 + i).Style.Font.Size = 12;
procedureCount++;
}
}
workSheet.Cells["A" + (12 + procedureCount).ToString() + ":G" + (12 + procedureCount).ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells["F" + (13 + procedureCount).ToString()].Value = "Total:";
workSheet.Cells["F" + (14 + procedureCount).ToString()].Value = (model.Procedures != null && model.Procedures.Count > 0) ? model.Procedures.Sum(p => p.Price) : 0;
workSheet.Cells["F" + (14 + procedureCount).ToString()].Style.Font.Bold = true;
workSheet.Cells["F1:F" + (14 + procedureCount).ToString()].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ExcelRange excelRange = workSheet.Cells[1, 1, workSheet.Dimension.End.Row, workSheet.Dimension.End.Column];
excelRange.Style.Border.BorderAround(ExcelBorderStyle.Thick);
double minimumSize = 7;
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns(minimumSize);
workSheet.Column(4).AutoFit(20, 60);
workSheet.Column(4).Style.WrapText = true;
byte[] excelFile = await package.GetAsByteArrayAsync();
SautinSoft.ExcelToPdf x = new SautinSoft.ExcelToPdf();
return(x.ConvertBytes(excelFile));
}
}
public void InitReport()
{
result = report.Report(InvoiceId);
}
