SaveInvoice.cs

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)
            {
                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() },
                {"FINALPL", invoice.TotalPriceWithTaxWithDiscount.ToString() },
                              
            };
            if (!invoice.IsNonGst)
            {
                kvPairs.Add("GstinPlaceholder", customer.Gstin ?null);
                kvPairs.Add("CGSTPL", ((invoice.TotalPriceWithTaxWithDiscount - invoice.TotalPriceNonTaxWithDiscount) / 2).ToString());
                kvPairs.Add("SGSTPL", ((invoice.TotalPriceWithTaxWithDiscount - invoice.TotalPriceNonTaxWithDiscount) / 2).ToString());
            }
            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());
                    kvPairs.Add($"RATE{i + 1}", invoice.Products[i].Product.BasePrice.ToString());
                    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());
                    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;
                    }
                }
            }
        }
    }
}