public static string ExportToSage(DateTime startDate, DateTime endDate, ExportToSageType type)
{
StringBuilder sb = new StringBuilder();
startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day);
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day).AddDays(1);
if (type.Equals(ExportToSageType.SalesInvoices))
{
#region SalesInvoices
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.IsImmediateCreditCardPayment, false)));
InvoiceQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending),
new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
string invoiceItemVatCodeToString = "";
string itemDescription = "";
// Sales Invoices
foreach (Invoice invoice in invoiceSet)
{
foreach (InvoiceItem invoiceItem in invoice.Items)
{
invoiceItemVatCodeToString = invoiceItem.VatCode.ToString();
itemDescription = GetAsciiOnly(invoiceItem.Description, GetAsciiOptions.SageApprovedChars).Replace(DELIMITER, ";");
if (!invoice.VatCode.Equals(Invoice.VATCodes.T1))
invoiceItemVatCodeToString = invoice.VatCode.ToString();
sb.Append(INVOICE_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(invoice.PromoterK);
sb.Append(DELIMITER);
sb.Append(invoiceItem.NominalCode);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(invoice.TaxDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(INVOICE_K_PREFIX); // Prefix + K.ToString() <= 8 characters
sb.Append(invoice.K.ToString("000000")); // Up to 6 digit K number. for K of 10^6 or greater, reduce the prefix size
sb.Append(DELIMITER);
sb.Append(CropString(itemDescription, DESC_MAX_LENGTH));
sb.Append(DELIMITER);
sb.Append(invoiceItem.Price.ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(invoiceItemVatCodeToString);
sb.Append(DELIMITER);
sb.Append(invoiceItem.Vat);
sb.Append(NEWLINE);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.SalesCredits))
{
#region SalesCredits
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query CreditQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Type, Invoice.Types.Credit)));
CreditQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending),
new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending));
InvoiceSet creditSet = new InvoiceSet(CreditQuery);
string creditItemVatCodeToString = "";
string itemDescription = "";
// Sales Invoices
foreach (Invoice credit in creditSet)
{
foreach (InvoiceItem creditItem in credit.Items)
{
creditItemVatCodeToString = creditItem.VatCode.ToString();
itemDescription = GetAsciiOnly(creditItem.Description, GetAsciiOptions.SageApprovedChars).Replace(DELIMITER, ";");
if (!credit.VatCode.Equals(Invoice.VATCodes.T1))
creditItemVatCodeToString = credit.VatCode.ToString();
sb.Append(CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(credit.PromoterK);
sb.Append(DELIMITER);
sb.Append(creditItem.NominalCode);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(credit.TaxDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(CREDIT_K_PREFIX); // Prefix + K.ToString() <= 8 characters
sb.Append(credit.K.ToString("000000")); // Up to 6 digit K number. for K of 10^6 or greater, reduce the prefix size
sb.Append(DELIMITER);
sb.Append(CropString(itemDescription, DESC_MAX_LENGTH));
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(creditItem.Price)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(creditItemVatCodeToString);
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(creditItem.Vat)).ToString("0.00"));
sb.Append(NEWLINE);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.BankReceipts))
{
#region BankReceipts
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate),
new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.IsImmediateCreditCardPayment, true)));
InvoiceQuery.OrderBy = new OrderBy(new OrderBy(Invoice.Columns.TaxDateTime, OrderBy.OrderDirection.Ascending),
new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
string invoiceItemVatCodeToString = "";
string itemDescription = "";
// Sales Invoices
foreach (Invoice invoice in invoiceSet)
{
foreach (InvoiceItem invoiceItem in invoice.Items)
{
invoiceItemVatCodeToString = invoiceItem.VatCode.ToString();
itemDescription = GetAsciiOnly(invoiceItem.Description, GetAsciiOptions.SageApprovedChars).Replace(DELIMITER, ";");
if (!invoice.VatCode.Equals(Invoice.VATCodes.T1))
invoiceItemVatCodeToString = invoice.VatCode.ToString();
sb.Append(BANK_RECEIPT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append("1220");
sb.Append(DELIMITER);
sb.Append(invoiceItem.NominalCode);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
sb.Append(invoice.TaxDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(BANK_RECEIPT_K_PREFIX); // Prefix + K.ToString() <= 8 characters
sb.Append(invoice.K.ToString("000000")); // Up to 6 digit K number. for K of 10^6 or greater, reduce the prefix size
sb.Append(DELIMITER);
sb.Append(CropString(itemDescription, DESC_MAX_LENGTH));
sb.Append(DELIMITER);
sb.Append(invoiceItem.Price.ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(invoiceItemVatCodeToString);
sb.Append(DELIMITER);
sb.Append(invoiceItem.Vat);
sb.Append(NEWLINE);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.PrepaidIncome))
{
#region PrepaidIncome
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
Query InvoiceQuery = new Query(new And(new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, endDate)));
InvoiceSet invoiceSet = new InvoiceSet(InvoiceQuery);
List<NameValueCollection> MonthsNominalcodesMoney = new List<NameValueCollection>();
MonthsNominalcodesMoney.Add(new NameValueCollection());
decimal sumPriceAll = 0;
NameValueCollection sumPriceNominalCode = new NameValueCollection();
foreach (Invoice invoice in invoiceSet)
{
// Sum for each month, but dividing item price over its revenue period
foreach (InvoiceItem invoiceItem in invoice.Items)
{
sumPriceAll += Math.Round(invoiceItem.Price, 2);
sumPriceNominalCode.Set(invoiceItem.NominalCode.ToString(), ((decimal)(invoiceItem.Price + Convert.ToDecimal(sumPriceNominalCode[invoiceItem.NominalCode.ToString()]))).ToString());
var revenueMonthSpread = SpreadRevenueOverMonths(invoiceItem);
DateTime sageStartDate = startDate;
DateTime monthOfInvoiceItemRevenueStartDate = new DateTime(invoiceItem.RevenueStartDate.Year, invoiceItem.RevenueStartDate.Month, 1);
DateTime monthOfInvoiceItemRevenueEndDate = new DateTime(invoiceItem.RevenueEndDate.Year, invoiceItem.RevenueEndDate.Month, 1);
DateTime monthOfReportStartDate = new DateTime(startDate.Year, startDate.Month, 1);
DateTime earliestMonth = monthOfReportStartDate < monthOfInvoiceItemRevenueStartDate ? monthOfReportStartDate : monthOfInvoiceItemRevenueStartDate;
DateTime latestMonth = monthOfReportStartDate > monthOfInvoiceItemRevenueEndDate ? monthOfReportStartDate : monthOfInvoiceItemRevenueEndDate;
int outputCounter = 0;
int revenueMonthSpreadCounter = 0;
for (DateTime thisMonth = earliestMonth; thisMonth <= latestMonth; thisMonth = thisMonth.AddMonths(1))
{
if (MonthsNominalcodesMoney.Count <= outputCounter || MonthsNominalcodesMoney[outputCounter] == null)
MonthsNominalcodesMoney.Add(new NameValueCollection());
if (thisMonth >= monthOfInvoiceItemRevenueStartDate && thisMonth <= monthOfInvoiceItemRevenueEndDate)
{
MonthsNominalcodesMoney[outputCounter].Set(invoiceItem.NominalCode.ToString(), ((double)((double)revenueMonthSpread[revenueMonthSpreadCounter] + Convert.ToDouble(MonthsNominalcodesMoney[outputCounter][invoiceItem.NominalCode.ToString()]))).ToString());
revenueMonthSpreadCounter++;
}
if (thisMonth >= monthOfReportStartDate)
outputCounter++;
}
//while (invoiceItem.RevenueStartDate.Month > sageStartDate.Month || invoiceItem.RevenueStartDate.Year > sageStartDate.Year)
//{
// sageStartDate = sageStartDate.AddMonths(1);
// if (MonthsNominalcodesMoney.Count <= counter || MonthsNominalcodesMoney[counter] == null)
// MonthsNominalcodesMoney.Add(new NameValueCollection());
// counter++;
//}
//foreach (double d in revenueMonthSpread)
//{
// if (MonthsNominalcodesMoney.Count <= counter || MonthsNominalcodesMoney[counter] == null)
// MonthsNominalcodesMoney.Add(new NameValueCollection());
// MonthsNominalcodesMoney[counter].Set(invoiceItem.NominalCode.ToString(), ((double)(d + Convert.ToDouble(MonthsNominalcodesMoney[counter][invoiceItem.NominalCode.ToString()]))).ToString());
// counter++;
//}
}
}
// First JC credit the code 2110 for sum of all invoices created in the month.
if (Math.Round(sumPriceAll, 2) >= 0)
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append("2110");
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.Month.ToString("00"));
sb.Append(startDate.ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.ToString("yy"));
sb.Append(" - 2110");
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(sumPriceAll)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
// Then JD debit the sum of each nominal code for all invoices created in the month
string[] sumPriceNominalCodeAllKeys = sumPriceNominalCode.AllKeys;
// Sort by Nominal Code Ascending
Array.Sort(sumPriceNominalCodeAllKeys);
for (int i = 0; i < sumPriceNominalCodeAllKeys.Length; i++)
{
double sumPrice = Convert.ToDouble(sumPriceNominalCode[sumPriceNominalCodeAllKeys[i]]);
if (Math.Round(sumPrice, 2) >= 0)
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append(sumPriceNominalCodeAllKeys[i]);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.Month.ToString("00"));
sb.Append(startDate.ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.ToString("yy"));
sb.Append(" - ");
sb.Append(sumPriceNominalCodeAllKeys[i]);
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(sumPrice)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
}
// Now JD debit the code 2110 for sum of all revenues for each month individually.
for (int i = 0; i < MonthsNominalcodesMoney.Count; i++)
{
double sum = 0;
for (int j = 0; j < MonthsNominalcodesMoney[i].Count; j++)
{
sum += Convert.ToDouble(MonthsNominalcodesMoney[i][j]);
}
if (Math.Round(sum, 2) != 0)
{
if (Math.Round(sum, 2) > 0)
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append("2110");
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).Month.ToString("00"));
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.AddMonths(i).ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(" - 2110");
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(sum)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
}
// Then JC credit each nominal code sum prices for that nominal codes for each month individually.
string[] MonthsNominalcodesMoneyAllKeys = MonthsNominalcodesMoney[i].AllKeys;
// Sort by Nominal Code Ascending
Array.Sort(MonthsNominalcodesMoneyAllKeys);
double monthNominalCodeAmount = 0;
for (int j = 0; j < MonthsNominalcodesMoneyAllKeys.Length; j++)
{
monthNominalCodeAmount = Convert.ToDouble(MonthsNominalcodesMoney[i][MonthsNominalcodesMoneyAllKeys[j]]);
if (Math.Round(monthNominalCodeAmount, 2) != 0)
{
if (Math.Round(monthNominalCodeAmount, 2) > 0)
sb.Append(JOURNAL_CREDIT_TRANSACTION_TYPE);
else
sb.Append(JOURNAL_DEBIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(DELIMITER); // null between delimiters
sb.Append(MonthsNominalcodesMoneyAllKeys[j]);
sb.Append(DELIMITER);
sb.Append(DEPT_NBR);
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(startDate.AddMonths(i).Month.ToString("00"));
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(DELIMITER);
sb.Append(PREPAID_INCOME_PREFIX);
sb.Append(startDate.AddMonths(i).ToString("MM"));
sb.Append(@"/");
sb.Append(startDate.AddMonths(i).ToString("yy"));
sb.Append(" - ");
sb.Append(MonthsNominalcodesMoneyAllKeys[j]);
sb.Append(DELIMITER);
sb.Append(Convert.ToDouble(Math.Abs(monthNominalCodeAmount)).ToString("0.00"));
sb.Append(DELIMITER);
sb.Append(InvoiceItem.VATCodes.T9.ToString());
sb.Append(DELIMITER);
sb.Append("0");
sb.Append(NEWLINE);
}
}
}
#endregion
}
else if (type.Equals(ExportToSageType.SalesReceipts))
{
#region SalesReceipts (InvoiceTransfer)
Query TransferQuery = new Query(new And(new Q(Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.LessThan, endDate),
new Q(Transfer.Columns.PromoterK, QueryOperator.GreaterThan, 0),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)));
TransferQuery.OrderBy = new OrderBy(Transfer.Columns.DateTimeComplete, OrderBy.OrderDirection.Ascending);
//TransferQuery.TableElement = new JoinLeft(Transfer.Columns.K, InvoiceTransfer.Columns.TransferK);
TransferSet transferSet = new TransferSet(TransferQuery);
// Transfers
foreach (Transfer t in transferSet)
{
if (t.InvoiceTransfers.Count == 0)
appendTransferAndInvoiceTransfer(sb, t, null);
else
{
foreach (InvoiceTransfer it in t.InvoiceTransfers)
appendTransferAndInvoiceTransfer(sb, t, it);
}
}
#endregion
}
else if (type.Equals(ExportToSageType.CampaignCredits))
{
#region CampaignCredit
Query q = new Query(new And(new Q(CampaignCredit.Columns.ActionDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(CampaignCredit.Columns.ActionDateTime, QueryOperator.LessThan, endDate),
new Q(CampaignCredit.Columns.Enabled, true)));
q.OrderBy = new OrderBy(
new OrderBy(CampaignCredit.Columns.ActionDateTime,OrderBy.OrderDirection.Ascending),
new OrderBy(CampaignCredit.Columns.DisplayOrder,OrderBy.OrderDirection.Ascending),
new OrderBy(CampaignCredit.Columns.K, OrderBy.OrderDirection.Ascending));
CampaignCreditSet ccs = new CampaignCreditSet(q);
// Transfers
foreach (CampaignCredit cc in ccs)
{
sb.Append(CAMPAIGN_CREDIT_TRANSACTION_TYPE);
sb.Append(DELIMITER);
sb.Append(cc.PromoterK);
sb.Append(DELIMITER);
sb.Append(cc.BuyableObjectType.ToString());
//Event
//Banner
//GuestlistCredit
//CampaignCredit
//Invoice
sb.Append(DELIMITER);
sb.Append(cc.InvoiceItemType.ToString());
//Misc
//EventDonate
//BannerTop
//BannerHotbox
//BannerPhoto
//BannerEmail
//BannerSkyscraper
//DesignBannerJpg
//DesignBannerAnimatedGif
//DesignBannerFlash
//CampaignCredits
//Combinations:
//Event - EventDonate (event hilights) [-ve]
//Banner - Misc (campaign credit refunds for underperforming banners) [+ve]
//Banner - BannerTop, BannerHotbox, BannerPhoto, BannerEmail or BannerSkyscraper (booking banners, occasionally adjustments) [-ve, occasionally +ve]
//Banner - DesignBannerJpg, DesignBannerAnimatedGif or DesignBannerFlash (booking banner design) [-ve]
//GuestlistCredit - Misc (refunds of old guestlist credit balances as campaign credits) [+ve]
//CampaignCredits - Misc (occasional adjustmants) [+ve and -ve]
//Invoice - Misc (bulk purchased campaign credits or adjustmants) [+ve and -ve]
//Invoice - CampaignCredits (instant banner purchases, 2-for-1 offer on ticket funds, manual invoices) [+ve and -ve]
sb.Append(DELIMITER);
if (cc.BuyableObjectType == Model.Entities.ObjectType.Invoice)
{
try
{
if (cc.BuyableObject != null)
{
sb.Append(INVOICE_K_PREFIX);
sb.Append(cc.BuyableObjectK.ToString("000000"));
}
}
catch { }
}
sb.Append(DELIMITER);
sb.Append(cc.ActionDateTime.ToString("dd/MM/yy"));
sb.Append(DELIMITER);
sb.Append(cc.Credits.ToString());
sb.Append(NEWLINE);
}
#endregion
}
return sb.ToString();
}
public static string ExportToSage(int month, int year, ExportToSageType type)
{
DateTime startDate = new DateTime(year, month, 1);
DateTime endDate = new DateTime(year, month + 1, 1).AddMilliseconds(-1);
return ExportToSage(startDate, endDate, type);
}