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)); }