Created
February 24, 2019 14:56
-
-
Save pradeepmurugan/05db4f9c2918493ab9e71949c0cc4a89 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using Biller.Common.Models; | |
using DocumentFormat.OpenXml.Packaging; | |
using DocumentFormat.OpenXml.Spreadsheet; | |
using LiteDB; | |
using MetroFramework; | |
using MetroFramework.Forms; | |
using System; | |
using System.Collections.Generic; | |
using System.ComponentModel; | |
using System.Data; | |
using System.IO; | |
using System.Linq; | |
using System.Windows.Forms; | |
namespace Biller.Client | |
{ | |
public partial class InvoiceForm : MetroForm | |
{ | |
List<Customer> _customers; | |
List<Product> _products; | |
LiteCollection<Customer> _customerCollection; | |
public Invoice CurrentInvoice { get; set; } | |
public Customer CurrentCustomer { get; set; } | |
public InvoiceForm(LiteCollection<Customer> customerCollection, List<Product> productList, int invoiceNumber) | |
{ | |
InitializeComponent(); | |
this.Text = $"Invoice #{invoiceNumber}"; | |
var dtoNow = DateTimeOffset.Now; | |
invoiceDateLabel.Text = dtoNow.ToString("dd/MM/yyyy hh:mm tt"); | |
_customerCollection = customerCollection; | |
_customers = _customerCollection.FindAll().ToList(); | |
_products = productList; | |
if (_customers.Count > 0 || _products.Count > 0) | |
{ | |
ConfigureCustomersTab(); | |
ConfigureProductsTab(); | |
} | |
CurrentInvoice = new Invoice | |
{ | |
Id = Guid.NewGuid(), | |
Number = invoiceNumber, | |
IsValid = true, | |
Products = new List<ProductListItem>(), | |
Date = dtoNow | |
}; | |
} | |
private void ConfigureProductsTab() | |
{ | |
ConfigureProductsGrid(); | |
var acsc = new AutoCompleteStringCollection(); | |
acsc.AddRange(_products.Select(x => x.Nickname).ToArray()); | |
productNicknameTextBox.AutoCompleteCustomSource = acsc; | |
productNicknameTextBox.AutoCompleteSource = AutoCompleteSource.CustomSource; | |
productNicknameTextBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend; | |
productNicknameTextBox.TextChanged += (object s, EventArgs e) => | |
{ | |
if (acsc.Contains(productNicknameTextBox.Text)) | |
{ | |
var product = _products.Where(x => x.Nickname == productNicknameTextBox.Text).First(); | |
AutoFillProductDetails(product); | |
} | |
}; | |
addProductButton.Click += (object sender, EventArgs e) => | |
{ | |
AddProduct(); | |
}; | |
void AddProduct() | |
{ | |
var currentProducts = new BindingList<ProductListItem>(productsGrid.DataSource as BindingList<ProductListItem>); | |
var productListItem = new ProductListItem | |
{ | |
Product = _products.Where(x => x.Nickname == productNicknameTextBox.Text).First(), | |
Quantity = 1 | |
}; | |
foreach (var item in currentProducts) | |
{ | |
if (item.Product.Name == productListItem.Product.Name) return; | |
} | |
CurrentInvoice.Products.Add(productListItem); | |
currentProducts.Add(productListItem); | |
productsGrid.DataSource = currentProducts; | |
} | |
void ConfigureProductsGrid() | |
{ | |
productsGrid.DataSource = new BindingList<ProductListItem>(); | |
productsGrid.RowsAdded += (object s, DataGridViewRowsAddedEventArgs e) => | |
{ | |
for (int i = 0; i < e.RowCount; i++) | |
{ | |
var row = productsGrid.Rows[e.RowIndex + i]; | |
foreach (DataGridViewCell cell in row.Cells) | |
{ | |
var rowItem = (row.DataBoundItem as ProductListItem); | |
if (cell.OwningColumn.HeaderText.Contains("Name")) | |
{ | |
cell.Value = rowItem?.Product.Name; | |
} | |
else if (cell.OwningColumn.HeaderText.Contains("Sr. No")) | |
{ | |
cell.Value = cell.RowIndex + 1; | |
} | |
else if (cell.OwningColumn.HeaderText.Contains("Amount")) | |
{ | |
cell.Value = (row.DataBoundItem as ProductListItem)?.Product.TotalPrice; | |
cell.Style.Format = "N2"; | |
} | |
else if (cell.OwningColumn.HeaderText.Contains("Discount Rate")) | |
{ | |
cell.Value = (row.DataBoundItem as ProductListItem)?.Product.DiscountRate; | |
cell.ValueType = typeof(decimal); | |
cell.Style.Format = "N2"; | |
} | |
} | |
} | |
}; | |
productsGrid.CellEndEdit += (object sender, DataGridViewCellEventArgs e) => | |
{ | |
var headerText = productsGrid.Columns[e.ColumnIndex].HeaderText; | |
var row = productsGrid.Rows[e.RowIndex].DataBoundItem as ProductListItem; | |
var invoiceIndex = CurrentInvoice.Products.FindIndex(x => x.Product.Name == row.Product.Name); | |
if (headerText.Contains("Quantity")) | |
{ | |
CurrentInvoice.Products[invoiceIndex].Quantity = row.Quantity; | |
foreach (DataGridViewCell cell in productsGrid.Rows[e.RowIndex].Cells) | |
{ | |
if (cell.OwningColumn.HeaderText == "Amount") | |
{ | |
cell.Value = row.Product.TotalPrice * row.Quantity; | |
} | |
} | |
} | |
else if (headerText.Contains("Discount Rate")) | |
{ | |
CurrentInvoice.Products[invoiceIndex].Product.DiscountRate = Decimal.Parse(productsGrid.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString()); | |
foreach (DataGridViewCell cell in productsGrid.Rows[e.RowIndex].Cells) | |
{ | |
if (cell.OwningColumn.HeaderText == "Amount") | |
{ | |
cell.Value = row.Product.TotalPrice * row.Quantity; | |
} | |
} | |
} | |
}; | |
productsGrid.UserDeletingRow += (object s, DataGridViewRowCancelEventArgs e) => | |
{ | |
CurrentInvoice.Products.Remove(e.Row.DataBoundItem as ProductListItem); | |
}; | |
} | |
} | |
private void ConfigureCustomersTab() | |
{ | |
var acsc = new AutoCompleteStringCollection(); | |
acsc.AddRange(_customers.Select(x => x.Nickname).ToArray()); | |
customerNicknameTextbox.AutoCompleteCustomSource = acsc; | |
customerNicknameTextbox.AutoCompleteSource = AutoCompleteSource.CustomSource; | |
customerNicknameTextbox.AutoCompleteMode = AutoCompleteMode.SuggestAppend; | |
customerNicknameTextbox.TextChanged += (object s, EventArgs e) => | |
{ | |
if (acsc.Contains(customerNicknameTextbox.Text)) | |
{ | |
var customer = _customers.Where(x => x.Nickname == customerNicknameTextbox.Text).First(); | |
CurrentCustomer = customer; | |
AutoFillCustomerDetails(customer); | |
} | |
}; | |
} | |
private void AutoFillProductDetails(Product product) | |
{ | |
productNameTextBox.Text = product.Name; | |
} | |
private void AutoFillCustomerDetails(Customer customer) | |
{ | |
customerNameTextBox.Text = customer.Name; | |
customerGstinTextBox.Text = customer.Gstin; | |
} | |
private void InvoiceSaveButton_Click(object sender, EventArgs e) | |
{ | |
if (this.CurrentCustomer != null && CurrentInvoice.Products.Count > 0) | |
{ | |
var customer = _customerCollection.FindById(CurrentCustomer.Id); | |
if (customer.Invoices == null) | |
{ | |
customer.Invoices = new List<Invoice>(); | |
} | |
customer.Invoices.Add(CurrentInvoice); | |
_customerCollection.Upsert(customer); | |
SaveInvoiceToXls(); | |
} | |
else | |
{ | |
MetroMessageBox.Show(this, "Invoice not valid without assigned customer or added products. Not saved."); | |
} | |
} | |
private void SaveInvoiceToXls() | |
{ | |
string templatePath = "", resultPath = ""; | |
var invoice = CurrentInvoice; | |
if (invoice.IsNonGst == false) | |
{ | |
templatePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Biller", "Templates", "TemplateGST.xlsx"); | |
DirectoryInfo di = Directory.CreateDirectory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Biller", "Invoices", DateTimeOffset.Now.ToString("dd-MM-yyyy"))); | |
resultPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Biller", "Invoices", DateTimeOffset.Now.ToString("dd-MM-yyyy"), invoice.Number.ToString() + ".xlsx"); | |
} | |
else | |
{ | |
templatePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Biller", "Templates", "TemplateNonGST.xlsx"); | |
DirectoryInfo di = Directory.CreateDirectory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Biller", "InvoicesGST", DateTimeOffset.Now.ToString("dd-MM-yyyy"))); | |
resultPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Biller", "InvoicesGST", DateTimeOffset.Now.ToString("dd-MM-yyyy"), invoice.Number.ToString() + ".xlsx"); | |
} | |
var customer = CurrentCustomer; | |
var kvPairs = new Dictionary<string, string>() | |
{ | |
{"NamePlaceholder",customer.Name }, | |
{"AddressPlaceholder", customer.Address }, | |
{"InvoiceNumberPlaceholder", invoice.Number.ToString() }, | |
{"DatePlaceholder", invoice.Date.ToString("dd MMMM yyyy") }, | |
{"SUBTOTALPL", invoice.TotalPriceNonTaxWithDiscount.ToString("N2") }, | |
{"FINALPL", invoice.TotalPriceWithTaxWithDiscount.ToString("N2") }, | |
}; | |
if (invoice.IsNonGst == false) | |
{ | |
kvPairs.Add("GstinPlaceholder", customer.Gstin); | |
kvPairs.Add("CGSTPL", ((invoice.TotalPriceWithTaxWithDiscount - invoice.TotalPriceNonTaxWithDiscount) / 2).ToString("N2")); | |
kvPairs.Add("SGSTPL", ((invoice.TotalPriceWithTaxWithDiscount - invoice.TotalPriceNonTaxWithDiscount) / 2).ToString("N2")); | |
} | |
for (int i = 0; i < 15; i++) | |
{ | |
if (i < invoice.Products.Count) | |
{ | |
kvPairs.Add($"S{i + 1}", (i + 1).ToString()); | |
kvPairs.Add($"ITEM_NAME{i + 1}", invoice.Products[i].Product.Name); | |
kvPairs.Add($"DISCOUNT{i + 1}", invoice.Products[i].Product.DiscountRate.ToString("N2")); | |
kvPairs.Add($"RATE{i + 1}", invoice.Products[i].Product.BasePrice.ToString("N2")); | |
kvPairs.Add($"QTY{i + 1}", invoice.Products[i].Quantity.ToString()); | |
kvPairs.Add($"AMT{i + 1}", (invoice.Products[i].Product.BasePrice * invoice.Products[i].Quantity).ToString("N2")); | |
if (!invoice.IsNonGst) | |
{ | |
kvPairs.Add($"HSN{i + 1}", invoice.Products[i].Product.Hsn.ToString()); | |
} | |
} | |
else | |
{ | |
kvPairs.Add($"S{i + 1}", ""); | |
kvPairs.Add($"ITEM_NAME{i + 1}", ""); | |
kvPairs.Add($"DISCOUNT{i + 1}", ""); | |
kvPairs.Add($"RATE{i + 1}", ""); | |
kvPairs.Add($"QTY{i + 1}", ""); | |
kvPairs.Add($"AMT{i + 1}", ""); | |
if (!invoice.IsNonGst) | |
{ | |
kvPairs.Add($"HSN{i + 1}", ""); | |
} | |
} | |
} | |
using (var xlsxStream = new MemoryStream()) | |
{ | |
// Read template from disk | |
using (var fileStream = File.OpenRead(templatePath)) | |
{ | |
fileStream.CopyTo(xlsxStream); | |
} | |
ProcessTemplate(xlsxStream, kvPairs); | |
xlsxStream.Seek(0L, SeekOrigin.Begin); | |
using (var resultFile = File.Create(resultPath)) | |
{ | |
xlsxStream.CopyTo(resultFile); | |
} | |
} | |
void ProcessTemplate(Stream template, Dictionary<string, string> keyValuePairs) | |
{ | |
using (var workbook = SpreadsheetDocument.Open(template, true)) | |
{ | |
// Replace shared strings | |
SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart; | |
IEnumerable<Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants<Text>(); | |
Replace(sharedStringTextElements, keyValuePairs); | |
// Replace inline strings | |
IEnumerable<WorksheetPart> worksheetParts = workbook.GetPartsOfType<WorksheetPart>(); | |
foreach (var worksheet in worksheetParts) | |
{ | |
var allTextElements = worksheet.Worksheet.Descendants<Text>(); | |
Replace(allTextElements, keyValuePairs); | |
} | |
} | |
} | |
void Replace(IEnumerable<Text> textElements, Dictionary<string, string> keyValuePairs) | |
{ | |
foreach (var key in keyValuePairs) | |
{ | |
if (textElements.Where(x => x.Text == key.Key).FirstOrDefault() != null) | |
{ | |
textElements.Where(s => s.Text == key.Key).FirstOrDefault().Text = key.Value; | |
} | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment