/// <summary>
/// Generate Invoice Xml File
/// </summary>
/// <param name="_templateName">Word template.dot file name</param>
/// <param name="_invoiceNumber">Invoice number for generation</param>
/// <returns>Xml file successfully written to XmlFolder</returns>
private bool LoadInvoice(dbTradeControlDataContext context, string _templateName, string _invoiceNumber)
{
try
{
xsInvoiceTask invoiceTask = new xsInvoiceTask();
invoiceTask.Clear();
xsInvoiceTask.InvoiceRow invoiceTaskRow = invoiceTask.Invoice.NewInvoiceRow();
vwDocCompany company = (from tb in context.vwDocCompanies
select tb).FirstOrDefault();
if (company != null)
{
invoiceTaskRow.CompanyName = company.CompanyName;
invoiceTaskRow.CompanyAddress = company.CompanyAddress;
invoiceTaskRow.CompanyNumber = company.CompanyNumber;
invoiceTaskRow.CompanyEmailAddress = company.CompanyEmailAddress;
invoiceTaskRow.CompanyWebsite = company.CompanyWebsite;
invoiceTaskRow.CompanyPhoneNumber = company.CompanyPhoneNumber;
invoiceTaskRow.CompanyNumber = company.CompanyNumber;
invoiceTaskRow.CompanyVatNumber = company.VatNumber;
invoiceTaskRow.BankAccountNumber = company.BankAccountNumber;
invoiceTaskRow.BankAccount = company.BankAccount;
invoiceTaskRow.BankSortCode = company.BankSortCode;
}
vwDocInvoice invoice = (from tb in context.vwDocInvoices
where tb.InvoiceNumber == _invoiceNumber
select tb).First();
documentFileName = $"{Repository}\\{invoice.AccountCode}_{_templateName}_{invoice.InvoiceNumber}";
invoiceTaskRow.AccountCode = invoice.AccountCode;
invoiceTaskRow.AccountName = invoice.AccountName;
if (invoice.EmailAddress == null)
{
invoiceTaskRow.EmailAddress = string.Empty;
}
else
{
invoiceTaskRow.EmailAddress = invoice.EmailAddress;
}
emailAddress = invoice.EmailAddress;
invoiceTaskRow.InvoiceNumber = invoice.InvoiceNumber;
invoiceTaskRow.InvoiceType = invoice.InvoiceType;
invoiceTaskRow.UserName = invoice.UserName;
invoiceTaskRow.InvoicedOn = invoice.InvoicedOn;
invoiceTaskRow.DueOn = invoice.DueOn;
invoiceTaskRow.Notes = invoice.Notes ?? string.Empty;
invoiceTaskRow.PaymentTerms = invoice.PaymentTerms ?? string.Empty;
invoiceTaskRow.InvoiceAddress = invoice.InvoiceAddress;
invoiceTaskRow.TotalInvoiceValue = invoice.InvoiceValue;
invoiceTaskRow.TotalTaxValue = invoice.TaxValue;
invoiceTaskRow.InvoiceCharge = invoice.InvoiceValue + invoice.TaxValue;
invoiceTask.Invoice.AddInvoiceRow(invoiceTaskRow);
//Add Invoice Items By Tasks >>>>
var invoiceTasks = from tb in context.vwDocInvoiceTasks
where tb.InvoiceNumber == _invoiceNumber
orderby tb.ActionedOn
select tb;
foreach (vwDocInvoiceTask taskRow in invoiceTasks)
{
xsInvoiceTask.InvoiceItemRow item = invoiceTask.InvoiceItem.NewInvoiceItemRow();
item.InvoiceNumber = _invoiceNumber;
item.ActionedOn = taskRow.ActionedOn.Value;
item.ActivityCode = taskRow.ActivityCode;
item.CashCode = taskRow.CashCode;
item.CashDescription = taskRow.CashDescription;
item.ItemDescription = taskRow.TaskTitle ?? string.Empty;
item.TaskCode = taskRow.TaskCode;
item.SecondReference = taskRow.SecondReference ?? string.Empty;
item.TaxCode = taskRow.TaxCode;
item.UnitOfMeasure = taskRow.UnitOfMeasure;
item.UnitValue = taskRow.InvoiceValue / (decimal)taskRow.Quantity;
item.Quantity = taskRow.Quantity;
item.InvoiceValue = taskRow.InvoiceValue;
item.TaxValue = taskRow.TaxValue;
item.ItemCharge = taskRow.InvoiceValue + taskRow.TaxValue;;
invoiceTask.InvoiceItem.AddInvoiceItemRow(item);
}
var invoiceItems = from tb in context.vwDocInvoiceItems
where tb.InvoiceNumber == _invoiceNumber
select tb;
foreach (vwDocInvoiceItem itemRow in invoiceItems)
{
xsInvoiceTask.InvoiceItemRow item = invoiceTask.InvoiceItem.NewInvoiceItemRow();
item.InvoiceNumber = _invoiceNumber;
item.ActionedOn = itemRow.ActionedOn;
item.ActivityCode = string.Empty;
item.CashCode = itemRow.CashCode;
item.CashDescription = itemRow.CashDescription;
item.ItemDescription = itemRow.ItemReference ?? string.Empty;
item.TaskCode = string.Empty;
item.SecondReference = string.Empty;
item.TaxCode = itemRow.TaxCode;
item.UnitOfMeasure = string.Empty;
item.UnitValue = 0;
item.Quantity = 0;
item.InvoiceValue = itemRow.InvoiceValue;
item.TaxValue = itemRow.TaxValue;
item.ItemCharge = itemRow.InvoiceValue + itemRow.TaxValue;;
invoiceTask.InvoiceItem.AddInvoiceItemRow(item);
}
var invoiceTax = from tb in context.vwTaxSummaries
where tb.InvoiceNumber == _invoiceNumber
orderby tb.TaxCode
select tb;
foreach (vwTaxSummary taxRow in invoiceTax)
{
xsInvoiceTask.InvoiceVatRow taxCode = invoiceTask.InvoiceVat.NewInvoiceVatRow();
taxCode.InvoiceNumber = _invoiceNumber;
taxCode.InvoiceValueTotal = (decimal)taxRow.InvoiceValueTotal;
taxCode.VatTaxCode = taxRow.TaxCode;
taxCode.VatTaxRate = (decimal)taxRow.TaxRate;
taxCode.TaxValueTotal = (decimal)taxRow.TaxValueTotal;
invoiceTask.InvoiceVat.AddInvoiceVatRow(taxCode);
}
invoiceTask.WriteXml(Schema.InvoiceTaskXmlFileName);
return(true);
}
catch (Exception err)
{
MessageBox.Show(err.Message, $"{err.Source}.{err.TargetSite.Name}", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
/// <summary>
/// Load Acknowledgement controls from xml file data
/// </summary>
/// <returns>Successfully loaded Word document</returns>
public bool LoadInvoice()
{
try
{
xsInvoiceTask xmlInvoice = new xsInvoiceTask();
xmlInvoice.ReadXml(Schema.InvoiceTaskXmlFileName);
xsInvoiceTask.InvoiceRow headerRow = (xsInvoiceTask.InvoiceRow)xmlInvoice.Invoice.Rows[0];
WriteControl("AccountName", headerRow.AccountName);
WriteControl("InvoiceAddress", headerRow.IsInvoiceAddressNull() ? string.Empty : headerRow.InvoiceAddress);
WriteControl("InvoicedOn", headerRow.InvoicedOn.ToString());
WriteControl("DueOn", headerRow.DueOn.ToString());
WriteControl("InvoiceNumber", headerRow.InvoiceNumber);
WriteControl("InvoiceType", headerRow.InvoiceType);
WriteControl("Notes", headerRow.IsNotesNull() ? string.Empty : headerRow.Notes);
WriteControl("PaymentTerms", headerRow.IsPaymentTermsNull() ? string.Empty : headerRow.PaymentTerms);
WriteControl("TotalInvoiceValue", headerRow.TotalInvoiceValue.ToCurrency());
WriteControl("TotalTaxValue", headerRow.TotalTaxValue.ToCurrency());
WriteControl("InvoiceCharge", headerRow.InvoiceCharge.ToCurrency());
WriteControl("CompanyName", headerRow.CompanyName);
WriteControl("CompanyNumber", headerRow.CompanyNumber);
WriteControl("CompanyVatNumber", headerRow.CompanyVatNumber);
WriteControl("BankAccount", headerRow.BankAccount);
WriteControl("BankAccountNumber", headerRow.BankAccountNumber);
WriteControl("BankSortCode", headerRow.BankSortCode);
#region Invoice Items
Word.Table itemTable = null;
Word.Row itemRow = null;
object itemObj = null;
int[] idxItem = new int[10];
for (int i = 0; i < idxItem.Length; i++)
{
idxItem[i] = -1;
}
foreach (Word.Table table in document.Tables)
{
foreach (Word.Row row in table.Rows)
{
foreach (Word.Cell cell in row.Range.Cells)
{
foreach (Word.ContentControl ctl in cell.Range.ContentControls)
{
switch (ctl.Tag)
{
case "ItemDescription":
itemTable = table;
itemObj = (object)row;
itemRow = row;
idxItem[0] = cell.ColumnIndex;
break;
case "TaskCode":
idxItem[1] = cell.ColumnIndex;
break;
case "ActionedOn":
idxItem[2] = cell.ColumnIndex;
break;
case "Quantity":
idxItem[3] = cell.ColumnIndex;
break;
case "UnitOfMeasure":
idxItem[4] = cell.ColumnIndex;
break;
case "InvoiceValue":
idxItem[5] = cell.ColumnIndex;
break;
case "TaxCode":
idxItem[6] = cell.ColumnIndex;
break;
case "TaxValue":
idxItem[7] = cell.ColumnIndex;
break;
case "ItemCharge":
idxItem[8] = cell.ColumnIndex;
break;
case "SecondReference":
idxItem[9] = cell.ColumnIndex;
break;
}
}
}
if (itemRow != null)
{
break;
}
}
if (itemRow != null)
{
break;
}
}
if (itemRow != null)
{
var items = from tb in xmlInvoice.InvoiceItem
orderby tb.TaskCode
select tb;
foreach (xsInvoiceTask.InvoiceItemRow row in items)
{
Word.Row newRow = itemTable.Rows.Add(ref itemObj);
if (idxItem[0] >= 0)
{
newRow.Cells[idxItem[0]].Range.Text = row.ItemDescription;
}
if (idxItem[1] >= 0)
{
newRow.Cells[idxItem[1]].Range.Text = row.TaskCode;
}
if (idxItem[2] >= 0)
{
newRow.Cells[idxItem[2]].Range.Text = row.ActionedOn.ToShortDateString();
}
if (idxItem[3] >= 0)
{
newRow.Cells[idxItem[3]].Range.Text = row.Quantity.ToQuantity();
}
if (idxItem[4] >= 0)
{
newRow.Cells[idxItem[4]].Range.Text = row.Quantity.ToQuantity() + ' ' + row.UnitOfMeasure;
}
if (idxItem[5] >= 0)
{
newRow.Cells[idxItem[5]].Range.Text = row.InvoiceValue.ToCurrency();
}
if (idxItem[6] >= 0)
{
newRow.Cells[idxItem[6]].Range.Text = row.TaxCode;
}
if (idxItem[7] >= 0)
{
newRow.Cells[idxItem[7]].Range.Text = row.TaxValue.ToCurrency();
}
if (idxItem[8] >= 0)
{
newRow.Cells[idxItem[8]].Range.Text = row.ItemCharge.ToCurrency();
}
if (idxItem[9] >= 0)
{
newRow.Cells[idxItem[9]].Range.Text = row.SecondReference;
}
}
itemRow.Delete();
}
#endregion
#region Vat Summary
Word.Table vatTable = null;
Word.Row vatRow = null;
object vatObj = null;
int[] idxVat = new int[] { 0, 1, 2, 3 };
foreach (Word.Table table in document.Tables)
{
foreach (Word.Row row in table.Rows)
{
foreach (Word.Cell cell in row.Range.Cells)
{
foreach (Word.ContentControl ctl in cell.Range.ContentControls)
{
switch (ctl.Tag)
{
case "VatTaxCode":
vatTable = table;
vatObj = (object)row;
vatRow = row;
idxVat[0] = cell.ColumnIndex;
break;
case "VatTaxRate":
idxVat[1] = cell.ColumnIndex;
break;
case "InvoiceValueTotal":
idxVat[2] = cell.ColumnIndex;
break;
case "TaxValueTotal":
idxVat[3] = cell.ColumnIndex;
break;
}
}
}
if (vatRow != null)
{
break;
}
}
if (vatRow != null)
{
break;
}
}
if (vatRow != null)
{
var vatCodes = from tb in xmlInvoice.InvoiceVat
orderby tb.VatTaxCode
select tb;
foreach (xsInvoiceTask.InvoiceVatRow row in vatCodes)
{
Word.Row newRow = vatTable.Rows.Add(ref vatObj);
newRow.Cells[idxVat[0]].Range.Text = row.VatTaxCode;
newRow.Cells[idxVat[1]].Range.Text = row.VatTaxRate.ToPercentage();
newRow.Cells[idxVat[2]].Range.Text = row.InvoiceValueTotal.ToCurrency();
newRow.Cells[idxVat[3]].Range.Text = row.TaxValueTotal.ToCurrency();
}
vatRow.Delete();
}
#endregion
return(true);
}
catch (Exception err)
{
MessageBox.Show(err.Message, $"{err.Source}.{err.TargetSite.Name}", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}