public static bool Filter(this Member m, QParaOptions options, IEnumerable <ColumnMetadata> filter)
{
try
{
var r = true;
foreach (var cd in filter.Where(x => x.Filter != null && x.Filter.Enabled))
{
switch (cd.Name)
{
case Names.ZoneNumber:
//r = (cd.Filter as NumberFilter).Match(m.ZoneNumber);
var val = new EnumValue {
Value = m.ZoneNumber, Name = m.Zone.Description
};
r = (cd.Filter as ZoneFilter).Match(val);
break;
case Names.JoinedOn:
if (m.JoinedOn == null)
{
// member has no joined on t so cannot be included in a joined on date filter
r = false;
}
else
{
r = (cd.Filter as DateFilter).Match(m.JoinedOn.Value);
}
break;
case Names.SubscriptionType:
r = (cd.Filter as SubscriptionTypeFilter).Match(m.SubscriptionType);
break;
case Names.SubscriptionPeriod:
r = (cd.Filter as SubscriptionPeriodFilter).Match(m.SubscriptionPeriod);
break;
case Names.PaymentMethod:
r = (cd.Filter as PaymentMethodFilter).Match(m.PaymentMethod);
break;
case Names.MinutesDeliveryMethod:
r = (cd.Filter as MinutesDeliveryMethodFilter).Match(m.MinutesDeliveryMethod);
break;
case Names.IsSuspended:
r = (cd.Filter as BoolFilter).Match(m.IsSuspended);
break;
case Names.HasLeft:
r = (cd.Filter as BoolFilter).Match(m.HasLeft);
break;
case Names.LeftOn:
// true means member passes the filter and is retained
if (m.LeftOn == null)
{
// member has not left so cannot be included in a left on date filter
r = false;
}
else
{
r = (cd.Filter as DateFilter).Match(m.LeftOn.Value);
}
break;
case Names.PaymentIsOutstanding:
r = (cd.Filter as BoolFilter).Match(m.PaymentIsOutstanding) && !(cd.Filter as BoolFilter).Match(m.GetIsPaid(options));
break;
case Names.IsPaid:
//r = (cd.Filter as BoolFilter).Match(m.IsPaid);
r = (cd.Filter as BoolFilter).Match(m.GetIsPaid(options));
break;
case Names.MemberCount:
r = (cd.Filter as NumberFilter).Match(m.MemberCount);
break;
}
if (r == false)
{
break;
}
}
return(r);
}
catch (Exception xe)
{
Debugger.Break();
throw;
}
}
private static void CreateSheet(Stream stream, QParaOptions options, IEnumerable <Member> memberList,
string sheetName,
string[] titlelines,
ColumnMetadata[] columnList)
{
var requiredColumns = columnList.Where(x => x.Show);
var ukTime = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
int sr = 1, sc = 1;
int r = 0;
int c = 0;
//MemoryStream ms = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
var qparaPink = Color.FromArgb(255, 127, 10, 73);
var workSheet = package.Workbook.Worksheets.Add(sheetName);
workSheet.Cells.Style.Font.Size = 10;
r = sr; c = sc;
int index = 0;
foreach (string line in titlelines)
{
if (index == 0)
{
workSheet.Cells[r, c].Style.Font.Bold = true;
workSheet.Cells[r, c].Style.Font.Size = 12;
}
workSheet.Cells[r, c].Value = line;
++r;
++index;
}
++r; c = sc;
workSheet.Row(r).Style.Font.Bold = true;
workSheet.Row(r).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//workSheet.Row(r).Style.WrapText = true;
foreach (var column in requiredColumns)
{
switch (column.Name)
{
case Names.FirstName:
case Names.LastName:
workSheet.Column(c).Width = 21;
break;
case Names.Name:
workSheet.Column(c).Width = 28;
break;
case Names.Address:
workSheet.Column(c).Width = 24;
break;
case Names.Email:
workSheet.Column(c).Width = 32;
//workSheet.Column(c).Style.WrapText = true;
break;
case Names.PhoneNumber:
case Names.MobileNumber:
workSheet.Column(c).Width = 14;
break;
case Names.MemberCount:
case Names.ZoneNumber:
case Names.AmountDue:
case Names.AmountReceived:
workSheet.Column(c).Width = 8;
workSheet.Column(c).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
break;
case Names.PaymentMethod:
case Names.IsPaid:
case Names.IsSuspended:
case Names.PaymentIsOutstanding:
case Names.HasEmail:
workSheet.Column(c).Width = 8;
workSheet.Column(c).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
break;
case Names.SubscriptionPeriod:
case Names.SubscriptionType:
case Names.MinutesDeliveryMethod:
case Names.LeavingReason:
workSheet.Column(c).Width = 15;
workSheet.Column(c).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
break;
}
workSheet.Column(c).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
workSheet.Cells[r, c].Style.WrapText = true;
//workSheet.Cells[r, c].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
workSheet.Cells[r, c].Style.Font.Color.SetColor(Color.White);
workSheet.Cells[r, c].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[r, c].Style.Fill.BackgroundColor.SetColor(qparaPink);
workSheet.Cells[r, c].Value = column.Name.ToDescription();
++c;
}
foreach (var m in memberList)
{
c = sc;
++r;
foreach (var column in requiredColumns)
{
dynamic datum = default;
switch (column.Name)
{
case Names.Name:
datum = m.Name;
workSheet.Cells[r, c].Style.WrapText = true;
break;
case Names.FirstName:
datum = m.FirstName;
break;
case Names.LastName:
datum = m.LastName;
break;
case Names.Email:
datum = string.Join(" ", m.GetEmailAddresses());
workSheet.Cells[r, c].Style.WrapText = true;
break;
case Names.HasEmail:
//datum = string.IsNullOrWhiteSpace(m.Email) ? "" : "Y";
datum = m.GetEmailAddresses().Count() > 0 ? "" : "Y";
break;
case Names.Address:
datum = m.FullAddress.Trim(); // FormatAddress(m.FullAddress);
workSheet.Cells[r, c].Style.WrapText = true;
break;
case Names.PostCode:
datum = m.PostCode;
break;
case Names.PhoneNumber:
datum = m.PhoneNumber;
break;
case Names.MobileNumber:
datum = m.MobileNumber;
break;
case Names.MemberCount:
if (m.MemberCount > 1)
{
datum = m.MemberCount; //.ToString();
}
break;
case Names.ZoneNumber:
datum = m.ZoneNumber; //.ToString();
break;
case Names.JoinedOn:
datum = m.JoinedOn.HasValue ? TimeZoneInfo.ConvertTime(m.JoinedOn.Value, ukTime).ToDefault() : "";
break;
case Names.SubscriptionType:
if (m.SubscriptionType != SubscriptionType.Standard)
{
datum = m.SubscriptionType.ToDescription();
}
break;
case Names.SubscriptionPeriod:
if (m.SubscriptionPeriod == SubscriptionPeriod.Life)
{
datum = m.SubscriptionPeriod.ToDescription();
}
break;
case Names.PaymentMethod:
if (m.PaymentMethod == PaymentMethod.Regular)
{
datum = m.PaymentMethod.ToDescription();
}
break;
case Names.MinutesDeliveryMethod:
datum = m.MinutesDeliveryMethod.ToDescription();
break;
case Names.DeliveryNote:
datum = m.DeliveryNote;
break;
case Names.IsSuspended:
datum = m.IsSuspended ? "Y" : "";
break;
case Names.HasLeft:
datum = m.HasLeft ? "Y" : "";
break;
case Names.LeftOn:
datum = m.LeftOn.HasValue ? TimeZoneInfo.ConvertTime(m.LeftOn.Value, ukTime).ToDefault() : "";
break;
case Names.LeavingReason:
datum = m.LeavingReason.ToDescription();
break;
case Names.MonthDue:
datum = m.MonthDue.ToMonthName();
break;
case Names.AmountDue:
if (m.ShouldMakePayments())
{
datum = m.AmountDue;
workSheet.Cells[r, c].Style.Numberformat.Format = "£0";
}
break;
case Names.AmountReceived:
if (m.ShouldMakePayments())
{
datum = m.AmountReceived;
workSheet.Cells[r, c].Style.Numberformat.Format = "£0";
}
break;
case Names.IsPaid:
if (m.ShouldMakePayments())
{
//datum = m.IsPaid ? "Y" : "";
datum = m.GetIsPaid(options) ? "Y" : "";
}
break;
case Names.PaymentIsOutstanding:
if (m.ShouldMakePayments())
{
datum = m.PaymentIsOutstanding ? "Y" : "";
}
break;
}
workSheet.Cells[r, c].Value = datum;
++c;
}
}
// cols are from 2 to columndata.length + 2
// rows are from 5 to r
//var sr = new ExcelAddress(5, 2, r, requiredColumns.Count() + 2);
//workSheet.Select(sr);
//workSheet.SelectedRange.AutoFitColumns();
//Debug.WriteLine($"autofit columns from {2} to {c - 1}");
//for(var i = 2; i < c;++i)
//{
// workSheet.Column(i).AutoFit();
//}
workSheet.PrinterSettings.Orientation = eOrientation.Landscape;
workSheet.PrinterSettings.HorizontalCentered = true;
workSheet.PrinterSettings.TopMargin = 0;
workSheet.PrinterSettings.BottomMargin = 0;
workSheet.PrinterSettings.LeftMargin = 0;
workSheet.PrinterSettings.RightMargin = 0;
workSheet.PrinterSettings.HeaderMargin = 0;
workSheet.PrinterSettings.FooterMargin = 0;
workSheet.PrinterSettings.ShowGridLines = true;
workSheet.PrinterSettings.FitToPage = true;
workSheet.PrinterSettings.RepeatRows = new ExcelAddress("1:3");
package.Save();
}
//return stream;
}
public static string GetSubscriptionYear(this QParaOptions options, DateTimeOffset date)
{
var fd = options.GetFirstDayOfYear(date);
return(options.GetSubscriptionYear(fd.Year));
}
public static string GetSubscriptionYear(this QParaOptions options, int year)
{
return($"{year}/{(year + 1) % 2000}");
}
public static string GetCurrentSubscriptionYear(this QParaOptions options)
{
var year = options.GetFirstDayOfYear(DateTimeOffset.Now).Year;
return(options.GetSubscriptionYear(year));
}
