using framework_library;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace framework_business
{
    public class xPurchases
    {
        /// <summary>
        /// Adds a note with an attachment after statement has been created
        /// </summary>
        /// <param name="noteEntityId"></param>
        /// <param name="noteTitle"></param>
        /// <param name="noteCaption"></param>
        /// <param name="statementPath"></param>
        /// <param name="statementFile"></param>
        public static void AddCreateStatementNote(int noteEntityId, string noteTitle, string noteCaption, string statementPath, string statementFile,
            int userId, string fieldName)
        {
            try
            {
                /* CVH 2016-04-20 Change from module Procedures to Surface, and type General to Statement */

                oNote note = new oNote();
                note.moduleId = pNums.Module.Surface.GetHashCode();
                note.typeId = pNums.NoteType.Statement.GetHashCode();
                note.entityId = noteEntityId;
                note.title = noteTitle;
                note.caption = noteCaption;
                note.dateSaved = DateTime.Now;
                note.userIdSaved = userId;
                note.isActive = true;
                note.fieldName = fieldName;
                note.recId = xData.SaveTyped("recId", typeof(oNote), note);

                //add statement as attachment to note
                oAttachment noteAtt = new oAttachment();
                noteAtt.dateSaved = DateTime.Now;
                noteAtt.display = note.title;
                noteAtt.entityId = note.recId;
                noteAtt.fileName = noteAtt.entityId + "_" + utils.RandomString(9, true) + statementFile.Substring(statementFile.IndexOf("."), statementFile.Length - statementFile.IndexOf("."));
                noteAtt.folderName = "note/" + note.recId;
                noteAtt.isActive = true;
                noteAtt.moduleId = pNums.Module.Notes.GetHashCode();
                noteAtt.typeId = pNums.AttachmentType.Document.GetHashCode();
                noteAtt.userIdSaved = note.userIdSaved;
                //CVH 2016-10-28 Save PDF to file folder, not straight in root of note item folder
                string copyTo = HttpContext.Current.Server.MapPath("~/upload/" + noteAtt.folderName + "/file/");
                utils.validateFolder(copyTo);
                System.IO.File.Copy(statementPath + statementFile, copyTo + noteAtt.fileName);
                noteAtt.recId = xData.SaveTyped("recId", typeof(oAttachment), noteAtt);
            }
            catch (Exception ex)
            {
                //log don't redirect
                exception.HandleException("enquiry:", MethodBase.GetCurrentMethod().Name, ex, userId);
            }
        }

        /// <summary>
        /// string to build the account lines
        /// </summary>
        /// <param name="accountList"></param>
        /// <param name="balforward">day brough forward</param>
        /// <param name="statementDate"></param>
        /// <param name="includeHidden"></param>
        /// <param name="lineCount"></param>
        /// <param name="fromZero"></param>
        /// <param name="accountMain"></param>
        /// <returns></returns>
        private static string BuildPurchasesStatementLines(oPurchases accountMain, ArrayList purchasesList, ovPurchasesView purchasesView, DateTime? statementDateFrom = null, DateTime? statementDateTo = null)
        {
            StringBuilder lineResult = new StringBuilder();
            try
            {
                decimal dOpening = 0M;

                if (statementDateFrom != null)
                {
                    foreach (ovPurchasesView purchasesViewItem in purchasesList)
                    {
                        if (purchasesViewItem.dateOfService < statementDateFrom)
                        {
                            dOpening = dOpening + purchasesViewItem.RunningBalance;
                        }
                    }
                }

                //set running balance based on records
                decimal bal = 0;
                foreach (ovPurchasesView purchasesViewItem in purchasesList)
                {
                    if (purchasesViewItem.itemType != "QT")
                    {
                        bal += purchasesViewItem.Invoiced;
                        bal += (Math.Abs(purchasesViewItem.Received) * -1);
                        purchasesViewItem.RunningBalance = bal;
                    }
                }

                lineResult.AppendLine("<table cellpadding='1' style='width:100%;'>");

                lineResult.AppendLine("<tr style='background-color: #F0F0F0;border-bottom: 1px solid #000000;'>");

                lineResult.AppendLine("<td style='width:15%;'><b>Date</b></td>");
                lineResult.AppendLine("<td style='width:25%;'><b>Activity</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:center;'><b>Reference</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Amount</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Payments</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Balance ZAR</b></td>");

                lineResult.AppendLine("</tr>");

                if (statementDateFrom != null)
                {
                    lineResult.AppendLine("<tr>");

                    lineResult.AppendLine($"<td>{((DateTime)statementDateFrom).ToString("dd-MMM-yy")}</td>");
                    lineResult.AppendLine("<td>Opening Balance</td>");
                    lineResult.AppendLine("<td style='text-align:center;'></td>");
                    lineResult.AppendLine("<td style='text-align:right;'></td>");
                    lineResult.AppendLine("<td style='text-align:right;'></td>");
                    lineResult.AppendLine($"<td style='text-align:right;'>{dOpening:F2}</td>");

                    lineResult.AppendLine("</tr>");
                }
                foreach (ovPurchasesView purchasesViewItem in purchasesList)
                {
                    /* CVH 2016-08-18 If printing statement on 2016-08-18, I want to see the invoice that was captured at 11am on that day. Exclude Time from date comparison */
                    //if (purchasesViewItem.itemType != "QT" && purchasesViewItem.dateOfService >= statementDateFrom && purchasesViewItem.dateOfService <= statementDateTo)
                    if (purchasesViewItem.itemType != "QT" && purchasesViewItem.dateOfService.Date >= statementDateFrom && purchasesViewItem.dateOfService.Date <= statementDateTo)
                    {
                        lineResult.AppendLine("<tr>");

                        lineResult.AppendLine($"<td>{purchasesViewItem.dateOfService.ToString("dd-MMM-yy")}</td>");
                        lineResult.AppendLine($"<td>{purchasesViewItem.Display}</td>");
                        lineResult.AppendLine($"<td style='text-align:center;'>{purchasesViewItem.reference}</td>");
                        if (purchasesViewItem.Invoiced > 0)
                            lineResult.AppendLine($"<td style='text-align:right;'>{purchasesViewItem.Invoiced:F2}</td>");
                        else
                            lineResult.AppendLine($"<td style='text-align:right;'></td>");
                        if (purchasesViewItem.Received > 0)
                            lineResult.AppendLine($"<td style='text-align:right;'>{purchasesViewItem.Received:F2}</td>");
                        else
                            lineResult.AppendLine($"<td style='text-align:right;'></td>");
                        lineResult.AppendLine($"<td style='text-align:right;'>{purchasesViewItem.RunningBalance:F2}</td>");

                        lineResult.AppendLine("</tr>");
                    }
                }

                lineResult.AppendLine("</table> ");
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
            return lineResult.ToString();
        }

        private static string BuildPurchasesReceiptLines(oPurchases accountMain, ArrayList purchasesList, ovPurchasesView purchasesView)
        {
            StringBuilder lineResult = new StringBuilder();
            try
            {
                lineResult.AppendLine("<table cellpadding='1' style='width:100%;'>");

                lineResult.AppendLine("<tr style='background-color: #F0F0F0;border-bottom: 1px solid #000000;'>");

                lineResult.AppendLine("<td style='width:15%;'><b>Date</b></td>");
                lineResult.AppendLine("<td style='width:55%;'><b>Description</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:center;'><b>Reference</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Payments</b></td>");

                lineResult.AppendLine("</tr>");

                foreach (ovPurchasesView purchasesViewItem in purchasesList)
                {
                    lineResult.AppendLine("<tr>");

                    lineResult.AppendLine($"<td>{purchasesViewItem.dateOfService.ToString("dd-MMM-yy")}</td>");
                    lineResult.AppendLine($"<td>{purchasesViewItem.Display}</td>");
                    lineResult.AppendLine($"<td style='text-align:center;'>{purchasesViewItem.reference}</td>");
                    lineResult.AppendLine($"<td style='text-align:right;'>{purchasesViewItem.Received:F2}</td>");

                    lineResult.AppendLine("</tr>");
                }

                lineResult.AppendLine("</table> ");
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
            return lineResult.ToString();
        }



        /// <summary>
        /// Create Statement
        /// </summary>
        /// <param name="accountMain"></param>
        /// <param name="webAddress"></param>
        /// <param name="balforward"></param>
        /// <param name="showHidden"></param>
        /// <param name="lineCount"></param>
        /// <param name="fromZero"></param>
        /// <param name="statementDate"></param>
        /// <param name="statementPath"></param>
        /// <param name="statementFile"></param>
        /// <returns></returns>
        public static bool CreatePurchasesDocument(oPurchases purchasesMain, string webAddress, DateTime documentDate, DateTime? statementDateFrom, DateTime? statementDateTo, ref string statementPath, ref string statementFile, pNums.DocumentType docType, string documentNo = "")
        {
            try
            {
                bool result = false;
                oSetup setup = handler.ReturnSetup();
                string headerData = String.Empty;
                string bodyData = String.Empty;
                ovPurchasesView purchasesView = new ovPurchasesView();
                ArrayList purchasesItemList = new ArrayList();

                switch (docType)
                {
                    case pNums.DocumentType.Statement:
                        purchasesItemList = xData.GetTypedByCriteriaSpecific("recId", typeof(ovPurchasesView), "surfaceItemId", purchasesMain.surfaceItemId.ToString(), "dateOfService, CASE WHEN invoiceNo > 0 THEN CAST(invoiceNo AS VARCHAR(20)) ELSE itemType END ", "pal_");
                        if (purchasesItemList.Count > 0)
                            purchasesView = (ovPurchasesView)purchasesItemList[0];
                        foreach (oPurchases purchase in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,itemType", purchasesMain.surfaceItemId.ToString() + ",~<>QT", "dateOfService DESC"))
                        {
                            purchasesMain = purchase;
                            break;
                        }
                        break;
                    case pNums.DocumentType.Invoice:
                        purchasesItemList = xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,invoiceNo,itemType", purchasesMain.surfaceItemId.ToString() + "," + documentNo + ",TI", "sequence");
                        if (purchasesItemList.Count > 0)
                            purchasesMain = (oPurchases)purchasesItemList[0];
                        break;
                    case pNums.DocumentType.Quote:
                        purchasesItemList = xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,invoiceNo,itemType", purchasesMain.surfaceItemId.ToString() + "," + documentNo + ",QT", "sequence");
                        if (purchasesItemList.Count > 0)
                            purchasesMain = (oPurchases)purchasesItemList[0];
                        break;
                    case pNums.DocumentType.Receipt:
                        //CVH 2016-09-20 Get purchasesView and purchasesMain for specific document number
                        //purchasesItemList = xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchasesView), "surfaceItemId,itemType", purchasesMain.surfaceItemId.ToString() + ",PM", "dateOfService, CASE WHEN invoiceNo > 0 THEN CAST(invoiceNo AS VARCHAR(20)) ELSE itemType END ", "pal_");
                        purchasesItemList = xData.GetTypedByCriteriaSpecific("recId", typeof(ovPurchasesView), "surfaceItemId,itemType,receiptNo", purchasesMain.surfaceItemId.ToString() + ",PM," + documentNo, "dateOfService, CASE WHEN invoiceNo > 0 THEN CAST(invoiceNo AS VARCHAR(20)) ELSE itemType END ", "pal_");
                        if (purchasesItemList.Count > 0)
                            purchasesView = (ovPurchasesView)purchasesItemList[0];
                        foreach (oPurchases purchase in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,itemType,receiptNo", purchasesMain.surfaceItemId.ToString() + ",PM," + documentNo, "dateOfService DESC"))
                        {
                            purchasesMain = purchase;
                            break;
                        }
                        break;
                    case pNums.DocumentType.CreditNote:
                        purchasesItemList = xData.GetTypedByCriteriaSpecific("recId", typeof(ovPurchasesView), "surfaceItemId,itemType,invoiceNo", purchasesMain.surfaceItemId.ToString() + ",CN," + documentNo, "dateOfService", "pal_");
                        if (purchasesItemList.Count > 0)
                            purchasesView = (ovPurchasesView)purchasesItemList[0];
                        foreach (oPurchases purchase in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,itemType,invoiceNo", purchasesMain.surfaceItemId.ToString() + ",CN," + documentNo, "dateOfService DESC"))
                        {
                            purchasesMain = purchase;
                            break;
                        }
                        break;
                    default:
                        break;
                }

                


                //get templates
                foreach (oTemplate template in xData.GetTypedByCriteriaSpecific("recId", typeof(oTemplate), "templateTypeId", pNums.TemplateType.Purchases.GetHashCode().ToString(), "templateName"))
                {
                    if (template.templateName.ToLower().Contains("header"))
                    {
                        headerData = template.templateContent;
                    }
                    else if (template.templateName.ToLower().Contains("body"))
                    {
                        bodyData = template.templateContent;
                    }
                }

                //set TO/FROM labels
                headerData = headerData.Replace("{TO}", docType == pNums.DocumentType.Receipt ? "FROM" : "TO");
                headerData = headerData.Replace("{FROM}", docType == pNums.DocumentType.Receipt ? "TO" : "FROM");

                //CVH 2016-09-14 Build transaction number with padded 0's 
                headerData = headerData.Replace("{QuoteNum}", docType == pNums.DocumentType.Quote ? "<b>Quote Number:</b><br/>" + BuildNextTransactionNumber(handler.ReturnSetup().purchaseQTEPrefix, handler.ReturnSetup().purchaseQTENumLength, purchasesMain.invoiceNo) + "<br/><br/>" : "");
                headerData = headerData.Replace("{QuoteDate}", docType == pNums.DocumentType.Quote ? "<b>Quote Date:</b><br/>" + DateTime.Now.ToShortDateString() + "<br/><br/>" : "");
                headerData = headerData.Replace("{ReceiptNum}", docType == pNums.DocumentType.Receipt ? "<b>Receipt Number:</b><br/>" + BuildNextTransactionNumber(handler.ReturnSetup().purchaseRCTPrefix, handler.ReturnSetup().purchaseRCTNumLength, purchasesMain.receiptNo) + "<br/><br/>" : "");
                headerData = headerData.Replace("{ReceiptDate}", docType == pNums.DocumentType.Receipt ? "<b>Receipt Date:</b><br/>" + DateTime.Now.ToShortDateString() + "<br/><br/>" : "");
                headerData = headerData.Replace("{InvoiceNum}", docType == pNums.DocumentType.Invoice ? "<b>Invoice Number:</b><br/>" + BuildNextTransactionNumber(handler.ReturnSetup().purchaseINVPrefix, handler.ReturnSetup().purchaseINVNumLength, purchasesMain.invoiceNo) + "<br/><br/>" : "");
                headerData = headerData.Replace("{InvoiceDate}", docType == pNums.DocumentType.Invoice ? "<b>Invoice Date:</b><br/>" + DateTime.Now.ToShortDateString() + "<br/><br/>" : "");
                headerData = headerData.Replace("{CreditNoteNum}", docType == pNums.DocumentType.CreditNote ? "<b>Credit Note Number:</b><br/>" + BuildNextTransactionNumber(handler.ReturnSetup().purchaseCRNPrefix, handler.ReturnSetup().purchaseCRNNumLength, purchasesMain.invoiceNo) + "<br/><br/>" : "");
                headerData = headerData.Replace("{CreditNoteDate}", docType == pNums.DocumentType.CreditNote ? "<b>Credit Note Date:</b><br/>" + DateTime.Now.ToShortDateString() + "<br/><br/>" : "");
                headerData = headerData.Replace("{StatementDate}", docType == pNums.DocumentType.Statement ? "<b>Statement Date:</b><br/>" + DateTime.Now.ToShortDateString() + "<br/><br/>" : "");
                headerData = headerData.Replace("{AccountNumber}", "<b>Account Number:</b><br/>" + purchasesMain.accountNo + "<br/><br/>");
                headerData = headerData.Replace("{Reference}", (docType == pNums.DocumentType.Invoice || docType == pNums.DocumentType.Quote) ? "<b>Reference:</b><br/>" + purchasesMain.reference + "<br/>" : "");

                /* CVH 2016-08-15 Add address line 5 & 6 (Province/State & Country in line 4 & 5, Postal Code line 6) */
                //replace address breaks
                purchasesMain.postalAddress = purchasesMain.postalAddress.Replace("\n", "<br/>").Replace("~|~", "<br/>").Replace("~1~", "").Replace("~2~", "").Replace("~3~", "").Replace("~4~", "").Replace("~5~", "").Replace("~6~", "");

                /* CVH 2016-09-13 Add trading as name to company name */
                string companyName = setup.customer;
                if (setup.tradingAs != String.Empty && !companyName.Contains(" t/a "))
                    companyName += " t/a " + setup.tradingAs;
                headerData = headerData.Replace("{oSetup.customer}", companyName);

                if (setup.vatRegistered)
                {
                    //merge header values
                    headerData = headerData.Replace("{SetupVATNumberLabel}", setup.vatRegistrationNumber != string.Empty ? "VAT Number:" : "");
                    headerData = headerData.Replace("{VATNumberLabel}", purchasesMain.vatNum != string.Empty ? "VAT Number:" : "");
                }
                else
                {
                    headerData = headerData.Replace("{SetupVATNumberLabel}", "");
                    headerData = headerData.Replace("{VATNumberLabel}", "");
                }


                utils.MergeHTMData(ref headerData, utils.BuildFieldCodeList(setup, true));
                utils.MergeHTMData(ref headerData, utils.BuildFieldCodeList(purchasesMain, true));
                //custom values
                headerData = headerData.Replace("{heading}", docType == pNums.DocumentType.Invoice ? "TAX INVOICE" : utils.SplitWords(Enum.GetName(typeof(pNums.DocumentType), docType.GetHashCode())).ToUpper());
                headerData = headerData.Replace("{Date}", documentDate.ToString("yyyy/MM/dd"));

                headerData = headerData.Replace("{WebAddress}", "");
                //cater for image paths
                headerData = headerData.Replace("/images/", webAddress + "/images/");
                headerData = headerData.Replace("/upload/image/", webAddress + "/upload/image/");

                //CVH 2016-09-15 If no logo uploaded, build company name in box
                if (setup.logoUploaded == String.Empty)
                    headerData = headerData.Replace("<td class=\"heading\">{logo}</td>", "<td class=\"heading\" valign=\"middle\"><h2><span style=\"font-family:'MS Sans Serif'; \">" + companyName + "</span></h2></td>");
                else
                    headerData = headerData.Replace("{logo}", "<img src='" + webAddress + "/upload/image/" + setup.logoUploaded + "' style=\"height:85px;\"/>");

                //merge body values
                switch (docType)
                {
                    case pNums.DocumentType.Statement:
                        bodyData = bodyData.Replace("{AccountLines}", BuildPurchasesStatementLines(purchasesMain, purchasesItemList, purchasesView, statementDateFrom, statementDateTo));
                        break;
                    case pNums.DocumentType.Invoice:
                        bodyData = bodyData.Replace("{AccountLines}", BuildPurchasesInvoiceLines(purchasesMain, purchasesItemList));
                        break;
                    case pNums.DocumentType.Quote:
                        bodyData = bodyData.Replace("{AccountLines}", BuildPurchasesInvoiceLines(purchasesMain, purchasesItemList));
                        break;
                    case pNums.DocumentType.Receipt:
                        bodyData = bodyData.Replace("{AccountLines}", BuildPurchasesReceiptLines(purchasesMain, purchasesItemList, purchasesView));
                        break;
                    case pNums.DocumentType.CreditNote:
                        bodyData = bodyData.Replace("{AccountLines}", BuildPurchasesReceiptLines(purchasesMain, purchasesItemList, purchasesView));
                        break;
                    default:
                        break;
                }
                bodyData = bodyData.Replace("{Totals}", BuildPurchasesTotals(purchasesItemList, docType, statementDateTo));
                bodyData = bodyData.Replace("{FootNotes}", BuildFootNotes(purchasesItemList, setup, docType));

                if (docType == pNums.DocumentType.Receipt)
                    bodyData = bodyData.Replace("{AdditionalNotes}", BuildAdditionalNotes(docType, purchasesMain.receiptNo));
                else
                    bodyData = bodyData.Replace("{AdditionalNotes}", BuildAdditionalNotes(docType, purchasesMain.invoiceNo));

                statementFile = Enum.GetName(typeof(pNums.DocumentType), docType.GetHashCode()) + "_" + purchasesMain.surfaceItemId + "_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm") + ".pdf";
                statementPath = HttpContext.Current.Server.MapPath("~/upload/documents/");

                utils.validateFolder(statementPath);

                result = utils.ConvertHTMLToPDFFile(bodyData, statementPath + statementFile, true, headerData, "");

                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
        }

        private static string BuildFootNotes(ArrayList purchasesItemList, oSetup setup, pNums.DocumentType docType)
        {
            string returnString = string.Empty;
            StringBuilder footBuilder = new StringBuilder();



            footBuilder.AppendLine("<table><tr><td>");

            /* CVH 2018-08-11 Bank details contained in setup.notesInvoices */
            switch (docType)
            {
                case pNums.DocumentType.Statement:
                    footBuilder.AppendLine(setup.notesStatements);
                    break;
                case pNums.DocumentType.Invoice:
                    DateTime dueDate = new DateTime();
                    foreach (oPurchases purchasesItem in purchasesItemList)
                    {
                        dueDate = purchasesItem.dateDue;
                    }
                    footBuilder.AppendLine($"<b>Due Date: {dueDate.ToShortDateString()}</b></br>");
                    footBuilder.AppendLine(setup.notesInvoices);
                    break;
                case pNums.DocumentType.Quote:
                    footBuilder.AppendLine(setup.notesQuotes);
                    break;
                case pNums.DocumentType.Receipt:
                    footBuilder.AppendLine(setup.notesReceipts);
                    break;
                case pNums.DocumentType.CreditNote:
                    footBuilder.AppendLine(setup.notesCreditNotes);
                    break;
                default:
                    break;
            }

            footBuilder.Replace("{oSetup.email}", setup.email);

            /*
            footBuilder.AppendLine("<br/><b>Payment Details</b><br/>");
            footBuilder.AppendLine($"Bank: {setup.bankName}<br/>");
            footBuilder.AppendLine($"A/c No: {setup.bankAccountNumber}<br/>");
            footBuilder.AppendLine($"Branch: {setup.bankBranch}<br/>");
            footBuilder.AppendLine($"Branch Code: {setup.bankBranchCode}<br/>");
            footBuilder.AppendLine($"Swift Code: {setup.bankSwift}<br/>");
            footBuilder.AppendLine("<br/><b>Use your invoice or account number as reference.</b>");
            footBuilder.AppendLine($"<br/><b>Please send proof of payment to {setup.email}</b>");
            */
            footBuilder.AppendLine("</td></tr></table>");
                      

            returnString = footBuilder.ToString();

            return returnString;
        }

        private static string BuildPurchasesInvoiceLines(oPurchases purchasesMain, ArrayList purchasesItemList)
        {
            StringBuilder lineResult = new StringBuilder();
            try
            {
                oSetup setup = handler.ReturnSetup();
                lineResult.AppendLine("<table cellpadding='1' style='width:100%;'>");

                lineResult.AppendLine("<tr style='background-color: #F0F0F0;border-bottom: 1px solid #000000;'>");

                lineResult.AppendLine("<td style='width:40%;'><b>Description</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:center;'><b>Quantity</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Unit Price</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Discount %</b></td>");
                lineResult.AppendLine("<td style='width:15%; text-align:right;'><b>Amount ZAR</b></td>");

                lineResult.AppendLine("</tr>");


                foreach (oPurchases purchasesItem in purchasesItemList)
                {
                    //show amounts as exclusive of VAT if there is a Vat Rate on the line
                    decimal unitFeeExclusive = purchasesItem.unitFee;
                    decimal amountExclusive = purchasesItem.amount;

                    if (setup.vatRegistered && setup.vatRate > 0)
                    {
                        unitFeeExclusive = (100.00m / (100.00m + setup.vatRate)) * purchasesItem.unitFee;
                        amountExclusive = unitFeeExclusive * purchasesItem.qty;
                    }

                    lineResult.AppendLine("<tr style='border-bottom: 0.5px solid #F0F0F0;'>");

                    //CVH 2016-09-21 Saved item description as main description, user description on second line 
                    //lineResult.AppendLine("<td>" + purchasesItem.itemDescription + "</td>");
                    string desc = purchasesItem.itemCode;
                    foreach (oPurchasesItem item in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchasesItem), "code,isDeleted", purchasesItem.itemCode + ",0"))
                    {
                        if (desc != String.Empty)
                            desc += " - ";
                        desc += item.description;
                    }
                    lineResult.AppendLine("<td>" + desc + "<br /><span style='font-size: 0.8em; '>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + purchasesItem.itemDescription + "</span></td>");

                    lineResult.AppendLine("<td style='text-align:center;'>" + purchasesItem.qty + "</td>");
                    lineResult.AppendLine("<td style='text-align:right;'>" + utils.returnFormattedDecimal(Convert.ToString(decimal.Round(unitFeeExclusive, 2))).Replace(" ", ",") + "</td>");
                    lineResult.AppendLine("<td style='text-align:right;'></td>");
                    lineResult.AppendLine("<td style='text-align:right;'>" + utils.returnFormattedDecimal(Convert.ToString(decimal.Round(amountExclusive, 2))).Replace(" ", ",") + "</td>");

                    lineResult.AppendLine("</tr>");

                }

                lineResult.AppendLine("</table> ");


            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
            return lineResult.ToString();
        }

        private static string BuildPurchasesTotals(ArrayList purchasesItemList, pNums.DocumentType docType, DateTime? statementDateTo)
        {
            StringBuilder totalsBuilder = new StringBuilder();
            try
            {                              

                string currency = "ZAR";
                decimal dSubTotal = 0M;
                decimal dVAT = 0M;
                decimal dTotalAmount = 0M;
                decimal dTotalPayments = 0M;
                decimal dAmountDue = 0M;
                int surfaceItemId = 0;

                oSetup setup = handler.ReturnSetup();

                switch (docType)
                {
                    case pNums.DocumentType.Statement:

                        if (purchasesItemList == null || purchasesItemList.Count <= 0)
                        {
                            decimal accBal = 0;
                            totalsBuilder.AppendLine("<table style=width:100%>");
                            totalsBuilder.AppendLine("<tr><td style='width: 10%'>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='width: 90%; border-top:solid 1px #777; text-align:right; font-size:medium;'>");
                            totalsBuilder.AppendLine($"<b>Balance Due: {accBal:F2}</b></td>");
                        }
                        else
                        {
                            purchasesItemList.Reverse();
                            ovPurchasesView purchasesView = (ovPurchasesView)purchasesItemList[0];
                            decimal accBal = xData.GetPurchasesBalance(purchasesView.surfaceItemId, statementDateTo);

                            totalsBuilder.AppendLine("<br/><table style=width:100%>");
                            totalsBuilder.AppendLine("<tr><td style='width: 10%'>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='width: 90%; border-top:solid 1px #777; text-align:right; font-size:medium;'>");
                            totalsBuilder.AppendLine($"<b>Balance Due: {accBal:F2}</b></td>");
                        }
                        break;

                    case pNums.DocumentType.Receipt:
                        totalsBuilder.AppendLine("<br/>");
                        break;

                    case pNums.DocumentType.Invoice:

                        decimal exclBalance = 0M;
                        foreach (oPurchases purchasesItem in purchasesItemList)
                        {
                            dSubTotal += purchasesItem.amount;
                            surfaceItemId = purchasesItem.surfaceItemId;
                        }


                        if (setup.vatRegistered && setup.vatRate > 0)
                        {
                            exclBalance = (100.00m / (100.00m + setup.vatRate)) * dSubTotal;
                            dVAT = dSubTotal - ((100.00m / (100.00m + setup.vatRate)) * dSubTotal);
                            dTotalAmount = exclBalance + dVAT;
                        }
                        else
                        {
                            exclBalance = dSubTotal;
                            dVAT = 0M;
                            dTotalAmount = exclBalance + dVAT;
                        }



                        //dVAT = (dSubTotal / 100) * vatRate;
                        //dTotalAmount = dSubTotal + dVAT;

                        foreach (oPurchases paymentItems in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,itemType", surfaceItemId.ToString() + ",PM"))
                        {
                            foreach (string paymentAllocId in paymentItems.allocatedReference.Split(','))
                            {
                                foreach (oPurchases purchasesItem in purchasesItemList)
                                {
                                    if (paymentAllocId.Contains(':'))
                                    {
                                        if (purchasesItem.recId.ToString() == paymentAllocId.Substring(0, paymentAllocId.IndexOf(':')))
                                            dTotalPayments += Convert.ToDecimal(paymentAllocId.Substring(paymentAllocId.IndexOf(':') + 1));
                                    }
                                    else if (purchasesItem.recId.ToString() == paymentAllocId)
                                        dTotalPayments += paymentItems.amount;
                                }
                            }
                        }

                        dAmountDue = dTotalAmount + dTotalPayments;

                        totalsBuilder.AppendLine("<br/><table style=width:100%>");
                        if (setup.vatRegistered && setup.vatRate > 0)
                        {
                            totalsBuilder.AppendLine("<tr><td style='width: 10%'>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='width: 45%; border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine("<b>Nett Amount</b></td>");
                            totalsBuilder.AppendLine("<td style='width: 45%; border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine($"<b>{exclBalance:F2}</b>");
                            totalsBuilder.AppendLine("</td></tr>");

                            totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine("VAT @ " + setup.vatRate + "%</td>");
                            totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine($"{dVAT:F2}");
                            totalsBuilder.AppendLine("</td></tr>");
                        }
                        totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine("<b>Total Amount " + currency + "</b></td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine($"<b>{dTotalAmount:F2}</b>");
                        totalsBuilder.AppendLine("</td></tr>");
                        totalsBuilder.AppendLine("<tr><td colspan='3'>&nbsp;</td></tr>");
                        totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine("Total Payments</td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine($"{dTotalPayments:F2}");
                        totalsBuilder.AppendLine("</td></tr>");
                        totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine("<b>Amount Due " + currency + "</b></td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine($"<b>{dAmountDue:F2}</b>");
                        totalsBuilder.AppendLine("</td></tr>");
                        totalsBuilder.AppendLine("</table>");
                        break;
                    case pNums.DocumentType.Quote:
                        foreach (oPurchases purchasesItem in purchasesItemList)
                        {
                            dSubTotal += purchasesItem.amount;
                            surfaceItemId = purchasesItem.surfaceItemId;
                        }

                        if (setup.vatRegistered && setup.vatRate > 0)
                        {
                            exclBalance = (100.00m / (100.00m + setup.vatRate)) * dSubTotal;
                            dVAT = dSubTotal - ((100.00m / (100.00m + setup.vatRate)) * dSubTotal);
                            dTotalAmount = exclBalance + dVAT;
                        }
                        else
                        {
                            exclBalance = dSubTotal;
                            dVAT = 0M;
                            dTotalAmount = exclBalance + dVAT;
                        }

                        //calculation to add Vat to Amounts Exclusive of VAT
                        //dVAT = (dSubTotal / 100) * vatRate;
                        //dTotalAmount = dSubTotal + dVAT;

                        foreach (oPurchases paymentItems in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchases), "surfaceItemId,itemType", surfaceItemId.ToString() + ",PM"))
                        {
                            foreach (string paymentAllocId in paymentItems.allocatedReference.Split(','))
                            {
                                foreach (oPurchases purchasesItem in purchasesItemList)
                                {
                                    if (paymentAllocId.Contains(':'))
                                    {
                                        if (purchasesItem.recId.ToString() == paymentAllocId.Substring(0, paymentAllocId.IndexOf(':')))
                                            dTotalPayments += Convert.ToDecimal(paymentAllocId.Substring(paymentAllocId.IndexOf(':') + 1));
                                    }
                                    else if (purchasesItem.recId.ToString() == paymentAllocId)
                                        dTotalPayments += purchasesItem.amount;
                                }
                            }
                        }

                        dAmountDue = dTotalAmount - dTotalPayments;

                        totalsBuilder.AppendLine("<br/><table style=width:100%>");
                        if (setup.vatRegistered && setup.vatRate > 0)
                        {
                            totalsBuilder.AppendLine("<tr><td style='width: 10%'>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='width: 45%; border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine("<b>Nett Amount</b></td>");
                            totalsBuilder.AppendLine("<td style='width: 45%; border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine($"<b>{exclBalance:F2}</b>");
                            totalsBuilder.AppendLine("</td></tr>");

                            totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine("VAT @ " + setup.vatRate + "%</td>");
                            totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine($"{dVAT:F2}");
                            totalsBuilder.AppendLine("</td></tr>");
                        }

                        totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine("<b>Total Amount " + currency + "</b></td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine($"<b>{dTotalAmount:F2}</b>");
                        totalsBuilder.AppendLine("</td></tr>");
                        totalsBuilder.AppendLine("</table>");
                        break;


                    case pNums.DocumentType.CreditNote:

                        decimal exclBalanceCN = 0M;
                        foreach (ovPurchasesView purchasesItem in purchasesItemList)
                        {
                            dSubTotal += purchasesItem.Received;
                            surfaceItemId = purchasesItem.surfaceItemId;
                        }


                        if (setup.vatRegistered && setup.vatRate > 0)
                        {
                            exclBalanceCN = (100.00m / (100.00m + setup.vatRate)) * dSubTotal;
                            dVAT = dSubTotal - ((100.00m / (100.00m + setup.vatRate)) * dSubTotal);
                            dTotalAmount = exclBalanceCN + dVAT;
                        }
                        else
                        {
                            exclBalanceCN = dSubTotal;
                            dVAT = 0M;
                            dTotalAmount = exclBalanceCN + dVAT;
                        }

                        dAmountDue = dTotalAmount + dTotalPayments;

                        totalsBuilder.AppendLine("<br/><table style=width:100%>");
                        if (setup.vatRegistered && setup.vatRate > 0)
                        {
                            totalsBuilder.AppendLine("<tr><td style='width: 10%'>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='width: 45%; border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine("<b>Nett Amount</b></td>");
                            totalsBuilder.AppendLine("<td style='width: 45%; border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine($"<b>{exclBalanceCN:F2}</b>");
                            totalsBuilder.AppendLine("</td></tr>");

                            totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                            totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine("VAT @ " + setup.vatRate + "%</td>");
                            totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                            totalsBuilder.AppendLine($"{dVAT:F2}");
                            totalsBuilder.AppendLine("</td></tr>");
                        };
                        totalsBuilder.AppendLine("<tr><td>&nbsp;</td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine("<b>Total Amount " + currency + "</b></td>");
                        totalsBuilder.AppendLine("<td style='border-top:solid 1px #777; text-align:right;'>");
                        totalsBuilder.AppendLine($"<b>{dTotalAmount:F2}</b>");
                        totalsBuilder.AppendLine("</td></tr>");
                        totalsBuilder.AppendLine("</table>");
                        break;
                    default:
                        break;
                }               
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
            return totalsBuilder.ToString();
        }

        private static string BuildAdditionalNotes(pNums.DocumentType docType, int number)
        {
            StringBuilder addBuilder = new StringBuilder();
            try
            {
                foreach (oPurchasesExtraNote note in xData.GetTypedByCriteriaSpecific("recId", typeof(oPurchasesExtraNote), "type,number", docType.GetHashCode().ToString() + "," + number))
                {
                    addBuilder.AppendLine(note.note);
                    break;
                }

            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
            return addBuilder.ToString();
        }

        /// <summary>
        /// Email purchases document
        /// </summary>
        /// <returns>Success</returns>
        public static bool EmailPurchasesDocument(oPurchases account,
            string configFrom, string subject, string emailTo, string emailCc, string emailBcc, string emailBody,
            string configWebAddress, int userId, DateTime documentDate,
            DateTime? statementDateFrom, DateTime? statementDateTo, pNums.DocumentType docType, string documentNo, 
            string fromDisplayName, string noteSurfaceFieldName)
        {
            try
            {
                string file = String.Empty;
                string path = string.Empty;

                if (CreatePurchasesDocument(account, configWebAddress, documentDate, statementDateFrom,
                    statementDateTo, ref path, ref file, docType, documentNo))
                {
                    /* CVH 2016-09-02 Use template from calling method, user allowed to edit before send. All {placeholder}'s have already been replaced before showing in modal */
                    string emailTemplate = emailBody;

                    oEmail mail = new oEmail();
                    mail.Body = emailTemplate;
                    mail.Attachments.Add(path + file);

                    mail.Subject = subject;

                    mail.fromAddress = configFrom;

                    mail.toAddress = emailTo;

                    mail.ccAddress = emailCc;

                    mail.bccAddress = emailBcc;

                    if (communication.SendAnEmail(mail, fromDisplayName))
                    {
                        //Add note with statement as attachment
                        AddCreateStatementNote(account.surfaceItemId, mail.Subject, mail.Body, path, file, userId, 
                            noteSurfaceFieldName);

                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                    return false;


            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
        }

        public static string BuildEmailTemplate(string emailTemplate, oPurchases account, oSetup setup, string configWebAddress)
        {
            try
            {
                string primaryContact = "";
                string emailDocNumber = "";
                string documentType = "";
                if (account != null)
                {
                    primaryContact = xPurchases.GetPurchasesPrimaryContact(account.surfaceItemId);
                    utils.MergeHTMData(ref emailTemplate, utils.BuildFieldCodeList(account, true));
                    //CVH 2016-09-14 Build transaction number with padded 0's 
                    switch (account.itemType)
                    {
                        case "CN":
                            emailDocNumber = BuildNextTransactionNumber(handler.ReturnSetup().purchaseCRNPrefix, handler.ReturnSetup().purchaseCRNNumLength, account.invoiceNo);
                            documentType = "Credit Note";
                            break;
                        case "TI":
                            emailDocNumber = BuildNextTransactionNumber(handler.ReturnSetup().purchaseINVPrefix, handler.ReturnSetup().purchaseINVNumLength, account.invoiceNo);
                            documentType = "Invoice";
                            break;
                        case "QT":
                            emailDocNumber = BuildNextTransactionNumber(handler.ReturnSetup().purchaseQTEPrefix, handler.ReturnSetup().purchaseQTENumLength, account.invoiceNo);
                            documentType = "Quote";
                            break;
                        case "PM":
                            emailDocNumber = BuildNextTransactionNumber(handler.ReturnSetup().purchaseRCTPrefix, handler.ReturnSetup().purchaseRCTNumLength, account.receiptNo);
                            documentType = "Receipt";
                            break;
                    }
                }

                /* CVH 2016-09-13 Add trading as name to company name */
                string companyName = setup.customer;
                if (setup.tradingAs != String.Empty && !companyName.Contains(" t/a "))
                    companyName += " t/a " + setup.tradingAs;

                emailTemplate = emailTemplate.Replace("{Date}", System.DateTime.Now.ToString("yyyy/MM/dd"));
                emailTemplate = emailTemplate.Replace("src=\"/images/", "src=\"" + configWebAddress + "/images/");
                emailTemplate = emailTemplate.Replace("src=\"/upload/image/", "src=\"" + configWebAddress + "/upload/image/");
                if (setup.logoUploaded == String.Empty)
                    emailTemplate = emailTemplate.Replace("{Logo Name}\"", "\" style='display:none'");
                else
                    emailTemplate = emailTemplate.Replace("{Logo Name}", setup.logoUploaded);
                emailTemplate = emailTemplate.Replace("{Primary Contact Name}", primaryContact);
                emailTemplate = emailTemplate.Replace("{Document Number}", emailDocNumber);
                emailTemplate = emailTemplate.Replace("{Document Type}", documentType);
                emailTemplate = emailTemplate.Replace("{Company Name}", companyName);
                emailTemplate = emailTemplate.Replace("{Company E-mail Address}", setup.email);
            }
            catch (Exception ex)
            {
                exception.HandleException("purchases:", MethodBase.GetCurrentMethod().Name, ex, 0);
            }

            return emailTemplate;
        }

        public static string GetPurchasesPrimaryContact(int contactSurfaceItemId)
        {
            string contact = "";
            try
            {
                /* CVH 2016-09-02 Primary contact no longer set on Child Surface. There is a surface field on the Contact surface called "Primary Person" */
                //List<oDynamicParam> list = new List<oDynamicParam>();
                //oDynamicParam par1 = new oDynamicParam();
                //par1.paramDisplayName = "parentSurfaceItemId";
                //par1.paramObject = contactSurfaceItemId;
                //list.Add(par1);
                //DataTable surfaceData = xData.GetTypedTableByProc("recId", typeof(oSurfaceFieldData), "sp_GetPurchasesPrimaryContact", list);
                //if (surfaceData != null && surfaceData.Rows.Count > 0)
                //    contact = surfaceData.Rows[0][0].ToString();

                ArrayList itemList = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceItem), "recId", contactSurfaceItemId.ToString());
                if (itemList == null || itemList.Count <= 0)
                    throw new Exception("Contact surface item not found.");
                oSurfaceItem item = (oSurfaceItem)itemList[0];

                DataTable surfaceItemData = xData.GetSurfaceItemData(item.surfaceId, contactSurfaceItemId);

                foreach (DataRow row in surfaceItemData.Rows)
                {
                    foreach (DataColumn col in surfaceItemData.Columns)
                    {
                        //name
                        if (col.ColumnName.EndsWith("_PrimaryPerson"))
                        {
                            contact = row[col].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }

            return contact;
        }

        public static string GetPurchasesCcEmailAddresses(int contactSurfaceItemId)
        {
            string cc = "";
            try
            {
                /* CVH 2016-08-16 Get CC email addresses */
                List<oDynamicParam> list = new List<oDynamicParam>();
                oDynamicParam par1 = new oDynamicParam();
                par1.paramDisplayName = "parentSurfaceItemId";
                par1.paramObject = contactSurfaceItemId;
                list.Add(par1);
                DataTable surfaceData = xData.GetTypedTableByProc("recId", typeof(oSurfaceFieldData), "sp_GetPurchasesCcEmailAddresses", list);
                foreach (DataRow row in surfaceData.Rows)
                {
                    string ccEmail = row[0].ToString();
                    if (utils.validateEmail(ccEmail))
                    {
                        if (cc == String.Empty)
                            cc = ccEmail;
                        else
                            cc += ";" + ccEmail;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }

            return cc;
        }

        public static oPurchases SetPurchasesContact(int itemId)
        {
            ArrayList itemList = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceItem), "recId", itemId.ToString());
            if (itemList == null || itemList.Count <= 0)
                throw new Exception("Contact surface item not found.");
            oSurfaceItem item = (oSurfaceItem)itemList[0];

            DataTable surfaceItemData = xData.GetSurfaceItemData(item.surfaceId, itemId);

            oPurchases purchases = new oPurchases();
            purchases.surfaceItemId = itemId;

            foreach (DataRow row in surfaceItemData.Rows)
            {
                foreach (DataColumn col in surfaceItemData.Columns)
                {
                    //name
                    if (col.ColumnName.EndsWith("_ContactName"))
                    {
                        purchases.name = row[col].ToString();
                        if (purchases.name.Length > 1)
                            purchases.name = purchases.name.Substring(0, 1).ToUpper() + purchases.name.Substring(1).ToLower();
                    }

                    //email
                    if (col.ColumnName.EndsWith("_Email"))
                        purchases.email = row[col].ToString();

                    //vat Number
                    if (col.ColumnName.EndsWith("_VATRegistered"))
                    {
                        purchases.vatNum = row[col].ToString();
                        if (purchases.vatNum == "Yes" || purchases.vatNum == "No")
                            purchases.vatNum = "";
                    }

                    //postal address
                    if (col.ColumnName.EndsWith("_PostalAddress"))
                        purchases.postalAddress = row[col].ToString();

                    /* CVH 2016-08-23 Contact Code no longer a field on surface, needs to be auto calculated based on Company Name, first 3 char + numeric */
                    /* CVH 2016-09-05 Account Code is now a field on the Contacts Surface. */
                    //account number
                    if (col.ColumnName.EndsWith("_AccountCode"))
                        purchases.accountNo = row[col].ToString();
                }
            }

            /* CVH 2016-08-16 Get CC email addresses */
            purchases.emailCC = GetPurchasesCcEmailAddresses(itemId);

            return purchases;
        }

        public static string BuildNextTransactionNumber(string prefix, int length, int nextNum)
        {
            try
            {
                if (nextNum <= 0)
                    nextNum = 1;
                string number = prefix;
                if (length > prefix.Length + nextNum.ToString().Length)
                    number = number.PadRight(length - nextNum.ToString().Length, '0');
                number += nextNum;
                return number;
            }
            catch (Exception ex)
            {
                throw new Exception(MethodBase.GetCurrentMethod().Name + ":" + ex.Message);
            }
        }
    }
}