context: using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.SqlClient;
  using System.IO;
  using System.Linq;
  using System.Text;
  using System.Web;
  using System.Web.Mvc;
  using CmsData;
  using CmsData.Codes;
  using CmsData.Registration;
  using CmsData.View;
  using CmsWeb.Areas.Manage.Controllers;
  using CmsWeb.Areas.Reports.Controllers;
  using CmsWeb.Constants;
  using CmsWeb.Models;
  using CmsWeb.Properties;
  using Dapper;
  using MoreLinq;
  using Newtonsoft.Json;
  using UtilityExtensions;
  using Settings = CmsData.Registration.Settings;
  
  namespace CmsWeb.Areas.Search.Models
  {
      public class OrgSearchModel : IDbBinder
      {
          private int? _count;
          internal string noticelist;
          private List<OrgSearch> _organizations;
  
          private CMSDataContext _db;
          public CMSDataContext CurrentDatabase
          {
              get => _db;
              set
              {
                  _db = value;
                  Init();
              }
          }
  
          protected virtual void Init()
          {
              Pager = new PagerModel2(CurrentDatabase)
              {
                  GetCount = Count
              };
          }
  
          [Obsolete(Errors.ModelBindingConstructorError, true)]
          public OrgSearchModel() { }
  
          public OrgSearchModel(CMSDataContext db)
          {
              CurrentDatabase = db;
          }
  
          public string Name { get; set; }
          public int? ProgramId { get; set; }
          public int? DivisionId { get; set; }
          public int? TagProgramId { get; set; }
          public int? TagDiv { get; set; }
          public int? ScheduleId { get; set; }
          public int? CampusId { get; set; }
          public int? StatusId { get; set; }
          public int? TypeId { get; set; }
          public string tagstr { get; set; }
          public int? OnlineReg { get; set; }
          public bool FromWeekAtAGlance { get; set; }
          public bool PublicView { get; set; }
  
          public string ExtraValues
          {
              set
              {
                  ExtraValuesDict = new Dictionary<string, string>();
                  if (value == null)
                      return;
                  var list = value.Split(',').Select(x => x.Trim()).Where(x => !string.IsNullOrEmpty(x));
                  foreach (var item in list)
                  {
                      var parts = item.Split('=');
                      if (parts.Length != 2 || string.IsNullOrEmpty(parts[0]) || string.IsNullOrEmpty(parts[1])) continue;
                      ExtraValuesDict.Add(parts[0], parts[1]);
                  }
              }
              get { return ExtraValuesDict != null ? string.Join(",", ExtraValuesDict?.Select(x => $"{x.Key}={x.Value}")) : ""; }
          }
          public Dictionary<string, string> ExtraValuesDict { get; set; }
  
          [JsonIgnore]
          public PagerModel2 Pager { get; set; }
  
          public Division Division()
          {
              var d = CurrentDatabase.Divisions.SingleOrDefault(dd => dd.Id == DivisionId);
              return d;
          }
  
          public IEnumerable<OrganizationInfo> OrganizationList()
          {
              var organizations = FetchOrgsList();
              if (!_count.HasValue)
                  _count = organizations.Count;
  
              organizations = ApplySort(organizations).Skip(Pager.StartRow).Take(Pager.PageSize).ToList();
              return OrganizationList(organizations, TagProgramId, TagDiv);
          }
  
          public static IEnumerable<OrganizationInfo> OrganizationList(List<OrgSearch> query, int? TagProgramId, int? TagDiv)
          {
              var q = from os in query
                      select new OrganizationInfo
                      {
                          Id = os.OrganizationId,
                          OrganizationName = os.OrganizationName,
                          LeaderName = os.LeaderName,
                          OrganizationStatus = os.OrganizationStatusId,
                          MemberCount = os.MemberCount,
                          RegClosed = os.RegistrationClosed ?? false,
                          RegTypeId = os.RegistrationTypeId,
                          ClassFilled = os.ClassFilled ?? false,
                          AppCategory = os.AppCategory ?? "Other",
                          PublicSortOrder = os.PublicSortOrder,
                          ProgramName = os.Program,
                          ProgramId = os.ProgramId,
                          DivisionName = os.Division,
                          Divisions = os.Divisions,
                          FirstMeetingDate = os.FirstMeetingDate.FormatDate(),
                          LastMeetingDate = os.LastMeetingDate.FormatDate(),
                          RegStart = os.RegStart.FormatDate(),
                          RegEnd = os.RegEnd.FormatDate(),
                          Schedule = os.ScheduleDescription,
                          Location = os.Location,
                          AllowSelfCheckIn = os.CanSelfCheckin ?? false,
                          Tag = os.Tag,
                          ChangeMain = os.ChangeMain == 1,
                          LeaderId = os.LeaderId,
                          PrevMemberCount = os.PrevMemberCount ?? 0,
                          ProspectCount = os.ProspectCount ?? 0,
                          Description = os.Description,
                          UseRegisterLink2 = os.UseRegisterLink2,
                          DivisionId = os.DivisionId,
                          BDayStart = os.BirthDayStart.FormatDate(),
                          BDayEnd = os.BirthDayEnd.FormatDate()
                      };
              return q;
          }
  
          public EpplusResult OrganizationExcelList()
          {
              var q = FetchOrgs();
              var q2 = from os in q
                       join o in CurrentDatabase.Organizations on os.OrganizationId equals o.OrganizationId
                       select new
                       {
                           OrgId = os.OrganizationId,
                           Name = os.OrganizationName,
                           os.Description,
                           Leader = os.LeaderName,
                           Members = os.MemberCount ?? 0,
                           os.Division,
                           FirstMeeting = os.FirstMeetingDate.FormatDate(),
                           LastMeeting = os.LastMeetingDate.FormatDate(),
                           Schedule = os.ScheduleDescription,
                           os.Campus,
                           os.Location,
                           RegStart = os.RegStart.FormatDate(),
                           RegEnd = os.RegEnd.FormatDate(),
                           RollSheetVisitorWks = o.RollSheetVisitorWks ?? 0,
                           Limit = o.Limit.ToString(),
                           o.LimitToRole,
                           CanSelfCheckin = os.CanSelfCheckin ?? false,
                           BirthDayStart = os.BirthDayStart.FormatDate(),
                           BirthDayEnd = os.BirthDayEnd.FormatDate(),
                           Gender = o.Gender.Description,
                           Grade = o.GradeAgeStart ?? 0,
                           LastDayBeforeExtra = o.LastDayBeforeExtra.FormatDate(),
                           NoSecurityLabel = o.NoSecurityLabel ?? false,
                           NumCheckInLabels = o.NumCheckInLabels ?? 0,
                           NumWorkerCheckInLabels = o.NumWorkerCheckInLabels ?? 0,
                           o.PhoneNumber,
                           MainFellowshipOrg = o.IsBibleFellowshipOrg ?? false,
                           EntryPoint = o.EntryPoint.Description,
                           o.OnLineCatalogSort,
                           os.LeaderType,
                           os.OrganizationStatusId,
                           os.AppCategory,
                           os.PublicSortOrder,
                           os.UseRegisterLink2
                       };
              return q2.ToDataTable().ToExcel("Organizations.xlsx");
          }
  
          public EpplusResult OrgsMemberList()
          {
              var q = FetchOrgs();
              if (Util2.OrgLeadersOnly)
              {
                  var oids = CurrentDatabase.GetLeaderOrgIds(CurrentDatabase.UserPeopleId);
                  q = q.Where(oo => oids.Contains(oo.OrganizationId));
              }
              return CurrentDatabase.CurrOrgMembers(string.Join(",", q.OrderBy(mm => mm.OrganizationName).Select(mm => mm.OrganizationId)))
                           .ToDataTable().ToExcel("OrgsMembers.xlsx");
          }
  
          public EpplusResult RegOptionsList()
          {
              var q = FetchOrgs();
              var q2 = from os in q
                       join op in CurrentDatabase.ViewRegsettingOptions on os.OrganizationId equals op.OrganizationId
                       select op;
              return q2.ToDataTable().ToExcel("RegOptions.xlsx");
          }
  
          public EpplusResult RegQuestionsUsage()
          {
              var q = FetchOrgs();
              var q2 = from os in q
                       join op in CurrentDatabase.ViewRegsettingCounts on os.OrganizationId equals op.OrganizationId
                       select op;
              return q2.ToDataTable().ToExcel("RegQuestionsUsage.xlsx");
          }
  
          public EpplusResult RegSettingUsages()
          {
              var q = FetchOrgs();
              var q2 = from os in q
                       join op in CurrentDatabase.ViewRegsettingUsages on os.OrganizationId equals op.OrganizationId
                       select op;
              return q2.ToDataTable().ToExcel("RegQuestionsUsage.xlsx");
          }
  
          public void RegSettingsXml(Stream stream)
          {
              var q = FetchOrgs();
              var w = new CmsData.API.APIWriter(stream);
              w.Start("OrgSearch");
              foreach (var o in q)
              {
                  var os = CurrentDatabase.CreateRegistrationSettings(o.OrganizationId);
                  Util.Serialize(os, w.writer);
              }
              w.End();
              w.writer.Flush();
          }
  
          public void RegMessagesXml(Stream stream, Settings.Messages messages)
          {
              var q = FetchOrgs();
              var w = new CmsData.API.APIWriter(stream);
              w.Start("OrgSearch");
              foreach (var o in q)
              {
                  var os = CurrentDatabase.CreateRegistrationSettings(o.OrganizationId);
                  os.WriteXmlMessages(w.writer, messages);
              }
              w.End();
              w.writer.Flush();
          }
  
          public int Count()
          {
              if (!_count.HasValue)
                  _count = FetchOrgsList().Count;
              return _count.Value;
          }
  
          public string FilteredCount()
          {
              return $"{Count():N0} {(IsFiltered() ? " (filtered)" : "")}";
          }
  
          public bool IsFiltered()
          {
              return Name.HasValue()
                     || ProgramId > 0
                     || (TypeId ?? 0) != 0
                     || CampusId > 0
                     || ScheduleId > 0
                     || StatusId != 30
                     || OnlineReg > 0;
          }
  
          private List<OrgSearch> FetchOrgsList()
          {
              if (_organizations == null)
              {
                  _organizations = FetchOrgs().ToList();
              }
  
              return _organizations;
          }
  
          public IQueryable<OrgSearch> FetchOrgs()
          {
              IQueryable<OrgSearch> queryable = null;
  
              if(CampusId == -1)
              {
                  queryable = CurrentDatabase.OrgSearch(null, Name, ProgramId, DivisionId, TypeId, 0, ScheduleId, StatusId, OnlineReg,
                   CurrentDatabase.CurrentUser.UserId, TagDiv).Where(x => x.Campus == null);
              }
              else
              {
                  queryable = CurrentDatabase.OrgSearch(null, Name, ProgramId, DivisionId, TypeId, CampusId, ScheduleId, StatusId, OnlineReg, CurrentDatabase.CurrentUser.UserId, TagDiv);
              }
  
              if (ExtraValuesDict != null && ExtraValuesDict.Any())
              {
                  var orgIds = queryable.Select(x => x.OrganizationId).ToList();
  
                  foreach (var ev in ExtraValuesDict)
                  {
                      orgIds = CurrentDatabase.OrganizationExtras
                          .Where(x => orgIds.Contains(x.OrganizationId) && x.Field == ev.Key &&
                              (
                               x.Type.Equals("Code") ? x.StrValue.ToLower().Equals(ev.Value) : x.StrValue.ToLower().Contains(ev.Value) ||
                               x.Type.Equals("Code") ? x.Data.ToLower().Equals(ev.Value) : x.Data.ToLower().Contains(ev.Value)) ||
                               x.DateValue != null && x.DateValue.ToString().Contains(ev.Value) ||
                               x.IntValue != null && x.IntValue.ToString().Contains(ev.Value) ||
                               x.BitValue != null && x.BitValue.ToString().Contains(ev.Value)
                               )
                          .Select(x => x.OrganizationId).ToList();
                  }
  
                  queryable = queryable.Where(x => orgIds.Contains(x.OrganizationId));
              }
  
              return queryable;
          }
  
          public static IQueryable<OrgSearch> FetchOrgs(int orgId)
          {
              return DbUtil.Db.OrgSearch(orgId, orgId.ToString(), null, null, null, null, null, null, null, DbUtil.Db.CurrentUser.UserId, null);
          }
  
          // ReSharper disable once FunctionComplexityOverflow
          public List<OrgSearch> ApplySort(List<OrgSearch> query)
          {
              if (!Pager.Sort.HasValue())
                  Pager.Sort = "Name";
              if (!Pager.Direction.HasValue())
                  Pager.Direction = "asc";
              var regdt = DateTime.Today.AddYears(5);
              IEnumerable<OrgSearch> list = query;
              if (Pager.Direction == "asc")
                  switch (Pager.Sort)
                  {
                      case "ID":
                          list = from o in query
                                 orderby o.OrganizationId
                                 select o;
                          break;
                      case "Division":
                      case "Program/Division":
                          list = from o in query
                                 orderby o.Program, o.Division, o.OrganizationName
                                 select o;
                          break;
                      case "Name":
                          list = from o in query
                                 orderby o.OrganizationName
                                 select o;
                          break;
                      case "Location":
                          list = from o in query
                                 orderby o.Location
                                 select o;
                          break;
                      case "Schedule":
                          list = from o in query
                                 orderby o.ScheduleId
                                 select o;
                          break;
                      case "Self CheckIn":
                          list = from o in query
                                 orderby (o.CanSelfCheckin ?? false)
                                 select o;
                          break;
                      case "Leader":
                          list = from o in query
                                 orderby o.LeaderName,
                                     o.OrganizationName
                                 select o;
                          break;
                      case "Filled":
                          list = from o in query
                                 orderby o.ClassFilled, o.OrganizationName
                                 select o;
                          break;
                      case "Closed":
                          list = from o in query
                                 orderby o.RegistrationClosed, o.OrganizationName
                                 select o;
                          break;
                      case "RegType":
                          list = from o in query
                                 orderby o.RegistrationTypeId, o.OrganizationName
                                 select o;
                          break;
                      case "Category":
                          list = from o in query
                                 orderby o.AppCategory ?? "zzz", o.PublicSortOrder ?? "zzz", o.OrganizationName
                                 select o;
                          break;
                      case "Members":
                      case "Current":
                          list = from o in query
                                 orderby o.MemberCount, o.OrganizationName
                                 select o;
                          break;
                      case "FirstDate":
                          list = from o in query
                                 orderby o.FirstMeetingDate, o.LastMeetingDate
                                 select o;
                          break;
                      case "RegStart":
                          list = from o in query
                                 orderby o.RegStart ?? regdt
                                 select o;
                          break;
                      case "RegEnd":
                          list = from o in query
                                 orderby o.RegEnd ?? regdt
                                 select o;
                          break;
                      case "LastMeetingDate":
                          list = from o in query
                                 orderby o.LastMeetingDate, o.FirstMeetingDate
                                 select o;
                          break;
                  }
              else
                  switch (Pager.Sort)
                  {
                      case "ID":
                          list = from o in query
                                 orderby o.OrganizationId descending
                                 select o;
                          break;
                      case "Program/Division":
                      case "Division":
                          list = from o in query
                                 orderby o.Program descending, o.Division descending,
                                     o.OrganizationName descending
                                 select o;
                          break;
                      case "Name":
                          list = from o in query
                                 orderby o.OrganizationName descending
                                 select o;
                          break;
                      case "Location":
                          list = from o in query
                                 orderby o.Location descending
                                 select o;
                          break;
                      case "Schedule":
                          list = from o in query
                                 orderby o.ScheduleId descending
                                 select o;
                          break;
                      case "Self CheckIn":
                          list = from o in query
                                 orderby (o.CanSelfCheckin ?? false) descending
                                 select o;
                          break;
                      case "Leader":
                          list = from o in query
                                 orderby o.LeaderName descending,
                                     o.OrganizationName descending
                                 select o;
                          break;
                      case "Filled":
                          list = from o in query
                                 orderby o.ClassFilled descending,
                                     o.OrganizationName descending
                                 select o;
                          break;
                      case "Closed":
                          list = from o in query
                                 orderby o.RegistrationClosed descending,
                                     o.OrganizationName descending
                                 select o;
                          break;
                      case "RegType":
                          list = from o in query
                                 orderby o.RegistrationTypeId descending,
                                     o.OrganizationName descending
                                 select o;
                          break;
                      case "Category":
                          list = from o in query
                                 orderby o.AppCategory ?? "zzz", o.PublicSortOrder ?? "zzz", o.OrganizationName
                                 select o;
                          break;
                      case "Members":
                      case "Current":
                          list = from o in query
                                 orderby o.MemberCount descending,
                                     o.OrganizationName descending
                                 select o;
                          break;
                      case "FirstDate":
                          list = from o in query
                                 orderby o.FirstMeetingDate descending,
                                     o.LastMeetingDate descending
                                 select o;
                          break;
                      case "RegStart":
                          list = from o in query
                                 orderby o.RegStart descending
                                 select o;
                          break;
                      case "RegEnd":
                          list = from o in query
                                 orderby o.RegEnd descending
                                 select o;
                          break;
                      case "LastMeetingDate":
                          list = from o in query
                                 orderby o.LastMeetingDate descending,
                                     o.FirstMeetingDate descending
                                 select o;
                          break;
                  }
              return list.ToList();
          }
  
          public static IEnumerable<SelectListItem> StatusIds()
          {
              var q = from s in DbUtil.Db.OrganizationStatuses
                      select new SelectListItem
                      {
                          Value = s.Id.ToString(),
                          Text = s.Description
                      };
              var list = q.ToList();
              list.Insert(0, new SelectListItem {Value = "0", Text = "(not specified)"});
              return list;
          }
  
          public IEnumerable<SelectListItem> CampusIds()
          {
              var qc = CurrentDatabase.Campus.AsQueryable();
              qc = CurrentDatabase.Setting("SortCampusByCode")
                  ? qc.OrderBy(cc => cc.Code)
                  : qc.OrderBy(cc => cc.Description);
              var list = (from c in qc
                          select new SelectListItem()
                          {
                              Value = c.Id.ToString(),
                              Text = c.Description,
                          }).ToList();
              list.Insert(0, new SelectListItem
              {
                  Value = "-1",
                  Text = "(not assigned)"
              });
              list.Insert(0, new SelectListItem
              {
                  Value = "0",
                  Text = "(not specified)"
              });
              return list;
          }
  
          public IEnumerable<SelectListItem> ProgramIds()
          {
              var q = from c in CurrentDatabase.Programs
                      orderby c.Name
                      select new SelectListItem
                      {
                          Value = c.Id.ToString(),
                          Text = c.Name
                      };
              var list = q.ToList();
              list.Insert(0, new SelectListItem
              {
                  Value = "0",
                  Text = "(not specified)"
              });
              return list;
          }
  
          public IEnumerable<SelectListItem> DivisionIds()
          {
              return DivisionIds(ProgramId ?? 0);
          }
  
          public static IEnumerable<SelectListItem> DivisionIds(int ProgId)
          {
              var q = from d in DbUtil.Db.Divisions
                      where d.ProgId == ProgId || d.ProgDivs.Any(p => p.ProgId == ProgId)
                      orderby d.Name
                      select new SelectListItem
                      {
                          Value = d.Id.ToString(),
                          Text = d.Name
                      };
              var list = q.ToList();
              list.Insert(0, new SelectListItem
              {
                  Value = "0",
                  Text = ProgId == 0 ? "(select a program)" : "(not specified)"
              });
              return list;
          }
  
          public IEnumerable<SelectListItem> ScheduleIds()
          {
              var q = from sc in CurrentDatabase.OrgSchedules
                      group sc by new {sc.ScheduleId, sc.MeetingTime}
                      into g
                      orderby g.Key.ScheduleId
                      where g.Key.ScheduleId != null
                      select new SelectListItem
                      {
                          Value = g.Key.ScheduleId.Value.ToString(),
                          Text = CurrentDatabase.GetScheduleDesc(g.Key.MeetingTime)
                      };
              var list = q.ToList();
              list.Insert(0, new SelectListItem
              {
                  Value = "-1",
                  Text = "(None)"
              });
              list.Insert(0, new SelectListItem
              {
                  Value = "0",
                  Text = "(not specified)"
              });
              return list;
          }
  
          public static IEnumerable<SelectListItem> OrgTypeFilters()
          {
              var list = OrgTypes().ToList();
              list.Insert(0, new SelectListItem {Text = "Suspended Checkin", Value = OrgType.SuspendedCheckin.ToString()});
              list.Insert(0, new SelectListItem {Text = "Main Fellowship", Value = OrgType.MainFellowship.ToString()});
              list.Insert(0, new SelectListItem {Text = "Not Main Fellowship", Value = OrgType.NotMainFellowship.ToString()});
              list.Insert(0, new SelectListItem {Text = "Parent Org", Value = OrgType.ParentOrg.ToString()});
              list.Insert(0, new SelectListItem {Text = "Child Org", Value = OrgType.ChildOrg.ToString()});
              list.Insert(0, new SelectListItem {Text = "Orgs Without Type", Value = OrgType.NoOrgType.ToString()});
              list.Insert(0, new SelectListItem {Text = "Orgs With Fees", Value = OrgType.Fees.ToString()});
              list.Insert(0, new SelectListItem {Text = "Orgs Without Fees", Value = OrgType.NoFees.ToString()});
              list.Insert(0, new SelectListItem {Text = "(not specified)", Value = "0"});
              return list;
          }
  
          public static IEnumerable<SelectListItem> OrgTypes()
          {
              var q = from t in DbUtil.Db.OrganizationTypes
                      orderby t.Code
                      select new SelectListItem
                      {
                          Value = t.Id.ToString(),
                          Text = t.Description
                      };
              return q;
          }
  
          public static IEnumerable<SelectListItem> RegistrationTypeIds()
          {
              var list = new List<SelectListItem>();
  
gt:             var spec = new Dictionary<int, string>