PageRenderTime 52ms CodeModel.GetById 2ms app.highlight 42ms RepoModel.GetById 1ms app.codeStats 1ms

/CmsWeb/Areas/Main/Models/Other/QueryModel.cs

https://bitbucket.org/mahalowe/bvcms
C# | 971 lines | 946 code | 18 blank | 7 comment | 102 complexity | cbe41b2285d2dd6232ca31c16f699fce MD5 | raw file
  1/* Author: David Carroll
  2 * Copyright (c) 2008, 2009 Bellevue Baptist Church 
  3 * Licensed under the GNU General Public License (GPL v2)
  4 * you may not use this code except in compliance with the License.
  5 * You may obtain a copy of the License at http://bvcms.codeplex.com/license 
  6 */
  7using System;
  8using System.Collections.Generic;
  9using System.Linq;
 10using System.Data.Linq;
 11using System.Web;
 12using System.Web.Mvc;
 13using CmsData;
 14using CmsWeb.Code;
 15using UtilityExtensions;
 16using System.Data.Linq.SqlClient;
 17using System.Web.UI.WebControls;
 18using System.Transactions;
 19using System.Xml.Linq;
 20
 21namespace CmsWeb.Models
 22{
 23    interface IQBUpdateable
 24    {
 25        int? SelectedId { get; set; }
 26        string ConditionName { get; set; }
 27        int? Program { get; set; }
 28        int? Division { get; set; }
 29        int? Organization { get; set; }
 30        int? Schedule { get; set; }
 31        int? Campus { get; set; }
 32        int? OrgType { get; set; }
 33        string Days { get; set; }
 34        string Age { get; set; }
 35        string Quarters { get; set; }
 36        string StartDate { get; set; }
 37        string EndDate { get; set; }
 38        string Comparison { get; set; }
 39        string[] Tags { get; set; }
 40        string CodeValue { get; set; }
 41        string[] CodeValues { get; set; }
 42        string View { get; set; }
 43        string TextValue { get; set; }
 44        string DateValue { get; set; }
 45        string NumberValue { get; set; }
 46        string IntegerValue { get; set; }
 47        int? Ministry { get; set; }
 48        string SavedQueryDesc { get; set; }
 49        string Sort { get; set; }
 50        string Direction { get; set; }
 51        int? Page { get; set; }
 52        int? PageSize { get; set; }
 53    }
 54    public class QueryModel : IQBUpdateable
 55    {
 56        private CMSDataContext Db;
 57        private QueryBuilderClause Qb;
 58        private int TagTypeId { get; set; }
 59        private string TagName { get; set; }
 60        private int? TagOwner { get; set; }
 61
 62        public QueryModel()
 63        {
 64            Db = DbUtil.Db;
 65            ConditionName = "Group";
 66            Direction = "asc";
 67            TagTypeId = DbUtil.TagTypeId_Personal;
 68            TagName = Util2.CurrentTagName;
 69            TagOwner = Util2.CurrentTagOwnerId;
 70            Errors = new Dictionary<string, string>();
 71        }
 72        public string Description { get; set; }
 73        public int? QueryId { get; set; }
 74
 75        public void LoadScratchPad()
 76        {
 77            Qb = Db.QueryBuilderScratchPad();
 78            if (QueryId.HasValue && QueryId.Value != Qb.QueryId)
 79            {
 80                var existing = Db.LoadQueryById(QueryId.Value);
 81                if (existing != null)
 82                {
 83                    Qb.CopyFromAll(existing, DbUtil.Db);
 84                    Description = Qb.Description;
 85                    SavedQueryDesc = Qb.Description;
 86                    Qb.Description = Util.ScratchPad;
 87                    Db.SubmitChanges();
 88                    SelectedId = Qb.QueryId;
 89                    EditCondition();
 90                }
 91            }
 92            QueryId = Qb.QueryId;
 93            if (SelectedId == null)
 94                SelectedId = Qb.QueryId;
 95        }
 96
 97        public int? SelectedId { get; set; }
 98
 99        public bool RightPanelVisible { get; set; }
100        public bool TextVisible { get; set; }
101        public bool NumberVisible { get; set; }
102        public bool IntegerVisible { get; set; }
103        public bool CodeVisible { get; set; }
104        public bool CodesVisible { get; set; }
105        public bool DateVisible { get; set; }
106        public bool ProgramVisible { get; set; }
107        public bool DivisionVisible { get; set; }
108        public bool EndDateVisible { get; set; }
109        public bool StartDateVisible { get; set; }
110        public bool OrganizationVisible { get; set; }
111        public bool ScheduleVisible { get; set; }
112        public bool CampusVisible { get; set; }
113        public bool OrgTypeVisible { get; set; }
114        public bool DaysVisible { get; set; }
115        public bool AgeVisible { get; set; }
116        public bool SavedQueryVisible { get; set; }
117        public bool MinistryVisible { get; set; }
118        public bool QuartersVisible { get; set; }
119        public bool TagsVisible { get; set; }
120
121        public List<SelectListItem> TagData { get; set; }
122        public IEnumerable<SelectListItem> CodeData { get; set; }
123        public List<SelectListItem> CompareData { get; set; }
124        public List<SelectListItem> ProgramData { get; set; }
125        public List<SelectListItem> DivisionData { get; set; }
126        public List<SelectListItem> OrganizationData { get; set; }
127        public List<SelectListItem> ViewData { get; set; }
128        public int? Program { get; set; }
129        public int? Division { get; set; }
130        public int? Organization { get; set; }
131        public int? Schedule { get; set; }
132        public int? Campus { get; set; }
133        public int? OrgType { get; set; }
134        public string Days { get; set; }
135        public string Age { get; set; }
136        public string Quarters { get; set; }
137        public string QuartersLabel { get; set; }
138        public string View { get; set; }
139        public string StartDate { get; set; }
140        public string EndDate { get; set; }
141        public string Comparison { get; set; }
142        public string[] Tags { get; set; }
143        public int? Ministry { get; set; }
144        public string SavedQueryDesc { get; set; }
145        public bool IsPublic { get; set; }
146
147        public string CodeValue { get; set; }
148
149        public string[] CodeValues { get; set; }
150
151        public string TextValue { get; set; }
152        public string DateValue { get; set; }
153        public string NumberValue { get; set; }
154        public string IntegerValue { get; set; }
155
156        public bool UpdateEnabled { get; set; }
157        public bool AddToGroupEnabled { get; set; }
158        public bool AddEnabled { get; set; }
159        public bool RemoveEnabled { get; set; }
160        public bool SelectMultiple { get; set; }
161
162        private FieldClass fieldMap;
163        private string _ConditionName;
164        public string ConditionName
165        {
166            get { return _ConditionName; }
167            set
168            {
169                _ConditionName = value;
170                fieldMap = FieldClass.Fields[value];
171            }
172        }
173        public string ConditionText { get { return fieldMap.Title; } }
174
175        private static List<CodeValueItem> BitCodes =
176            new List<CodeValueItem> 
177            { 
178                new CodeValueItem { Id = 1, Value = "True", Code = "T" }, 
179                new CodeValueItem { Id = 0, Value = "False", Code = "F" }, 
180            };
181        public void SetVisibility()
182        {
183            RightPanelVisible = true;
184            TextVisible = false;
185            NumberVisible = false;
186            CodeVisible = false;
187            CodesVisible = false;
188            DateVisible = false;
189            CodeData = null;
190            ConditionName = ConditionName;
191            CompareData = Comparisons().ToList();
192            DivisionVisible = fieldMap.HasParam("Division");
193            ProgramVisible = fieldMap.HasParam("Program");
194            OrganizationVisible = fieldMap.HasParam("Organization");
195            ScheduleVisible = fieldMap.HasParam("Schedule");
196            CampusVisible = fieldMap.HasParam("Campus");
197            OrgTypeVisible = fieldMap.HasParam("OrgType");
198            DaysVisible = fieldMap.HasParam("Days");
199            AgeVisible = fieldMap.HasParam("Age");
200            SavedQueryVisible = fieldMap.HasParam("SavedQueryIdDesc");
201            MinistryVisible = fieldMap.HasParam("Ministry");
202            QuartersVisible = fieldMap.HasParam("Quarters");
203            if (QuartersVisible)
204                QuartersLabel = fieldMap.QuartersTitle;
205            TagsVisible = fieldMap.HasParam("Tags");
206            if (TagsVisible)
207            {
208                var cv = new CodeValueModel();
209                TagData = ConvertToSelect(cv.UserTags(Util.UserPeopleId), "Code");
210            }
211            StartDateVisible = fieldMap.HasParam("StartDate");
212            EndDateVisible = fieldMap.HasParam("EndDate");
213
214            var cvctl = new CodeValueModel();
215            switch (fieldMap.Type)
216            {
217                case FieldType.Bit:
218                case FieldType.NullBit:
219                    CodeVisible = true;
220                    CodeData = ConvertToSelect(BitCodes, fieldMap.DataValueField);
221                    break;
222                case FieldType.String:
223                case FieldType.StringEqual:
224                    TextVisible = true;
225                    break;
226                case FieldType.NullNumber:
227                case FieldType.Number:
228                    NumberVisible = true;
229                    break;
230                case FieldType.NullInteger:
231                case FieldType.Integer:
232                case FieldType.IntegerSimple:
233                case FieldType.IntegerEqual:
234                    IntegerVisible = true;
235                    break;
236                case FieldType.Code:
237                case FieldType.CodeStr:
238                    CodeVisible = true;
239                    if (fieldMap.DataSource == "ExtraValues")
240                        CodeData = StandardExtraValues.ExtraValueCodes();
241                    else if (fieldMap.DataSource == "Campuses")
242                        CodeData = Campuses();
243                    else
244                        CodeData = ConvertToSelect(Util.CallMethod(cvctl, fieldMap.DataSource), fieldMap.DataValueField);
245                    break;
246                case FieldType.Date:
247                case FieldType.DateSimple:
248                    DateVisible = true;
249                    break;
250                case FieldType.DateField:
251                    CodeVisible = true;
252                    CodeData = ConvertToSelect(Util.CallMethod(cvctl, fieldMap.DataSource), fieldMap.DataValueField);
253                    break;
254            }
255            var cc = Db.LoadQueryById(SelectedId);
256            if (cc == null)
257                return;
258
259            if (fieldMap.Type == FieldType.Group)
260            {
261                CompareData = Comparisons().ToList();
262                RightPanelVisible = false;
263                UpdateEnabled = cc.IsGroup;
264                return;
265            }
266            UpdateEnabled = !cc.IsGroup && !cc.IsFirst;
267            AddToGroupEnabled = cc.IsGroup;
268            AddEnabled = !cc.IsFirst;
269            RemoveEnabled = !cc.IsFirst;
270        }
271        public static List<SelectListItem> ConvertToSelect(object items, string valuefield)
272        {
273            var list = items as IEnumerable<CodeValueItem>;
274            List<SelectListItem> list2;
275            switch (valuefield)
276            {
277                case "IdCode":
278                    list2 = list.Select(c => new SelectListItem { Text = c.Value, Value = c.IdCode }).ToList();
279                    break;
280                case "Id":
281                    list2 = list.Select(c => new SelectListItem { Text = c.Value, Value = c.Id.ToString() }).ToList();
282                    break;
283                case "Code":
284                    list2 = list.Select(c => new SelectListItem { Text = c.Value, Value = c.Code }).ToList();
285                    break;
286                default:
287                    list2 = list.Select(c => new SelectListItem { Text = c.Value, Value = c.Value }).ToList();
288                    break;
289            }
290            if (list2.Count > 0)
291                list2[0].Selected = true;
292            return list2;
293        }
294        public static string IdCode(object items, int id)
295        {
296            var list = items as IEnumerable<CodeValueItem>;
297            var ret = (from v in list
298                       where v.Id == id
299                       select v.IdCode).Single();
300            return ret;
301        }
302        DateTime? DateParse(string s)
303        {
304            DateTime dt;
305            if (DateTime.TryParse(s, out dt))
306                return dt;
307            return null;
308        }
309        int? IntParse(string s)
310        {
311            int i;
312            if (int.TryParse(s, out i))
313                return i;
314            return null;
315        }
316        string DateString(DateTime? dt)
317        {
318            if (dt.HasValue)
319                return dt.Value.ToShortDateString();
320            return "";
321        }
322        private void UpdateCondition(QueryBuilderClause c)
323        {
324            c.Field = ConditionName;
325            c.Comparison = Comparison;
326            switch (c.FieldInfo.Type)
327            {
328                case FieldType.String:
329                case FieldType.StringEqual:
330                    c.TextValue = TextValue;
331                    break;
332                case FieldType.Integer:
333                case FieldType.IntegerSimple:
334                case FieldType.IntegerEqual:
335                case FieldType.NullInteger:
336                    c.TextValue = IntegerValue;
337                    break;
338                case FieldType.Number:
339                case FieldType.NullNumber:
340                    c.TextValue = NumberValue;
341                    break;
342                case FieldType.Date:
343                case FieldType.DateSimple:
344                    c.DateValue = DateParse(DateValue);
345                    break;
346                case FieldType.Code:
347                case FieldType.CodeStr:
348                case FieldType.DateField:
349                case FieldType.Bit:
350                case FieldType.NullBit:
351                    if (c.HasMultipleCodes && CodeValues != null)
352                        c.CodeIdValue = string.Join(";", CodeValues);
353                    else
354                        c.CodeIdValue = CodeValue;
355                    break;
356            }
357            c.Program = Program ?? 0;
358            c.Division = Division ?? 0;
359            c.Organization = Organization ?? 0;
360            if (MinistryVisible)
361                c.Program = Ministry ?? 0;
362            c.Schedule = Schedule ?? 0;
363            c.Campus = Campus ?? 0;
364            c.OrgType = OrgType ?? 0;
365            c.StartDate = DateParse(StartDate);
366            c.EndDate = DateParse(EndDate);
367            c.Days = Days.ToInt();
368            c.Age = Age.ToInt();
369            c.Quarters = Quarters;
370            if (Tags != null)
371                c.Tags = string.Join(";", Tags);
372            c.SavedQueryIdDesc = SavedQueryDesc;
373            Db.SubmitChanges();
374        }
375        public void EditCondition()
376        {
377            var c = Db.LoadQueryById(SelectedId);
378            if (c == null)
379                return;
380            ConditionName = c.FieldInfo.Name;
381            SetVisibility();
382            Comparison = c.Comparison;
383            switch (c.FieldInfo.Type)
384            {
385                case FieldType.String:
386                case FieldType.StringEqual:
387                    TextValue = c.TextValue;
388                    break;
389                case FieldType.Integer:
390                case FieldType.IntegerSimple:
391                case FieldType.IntegerEqual:
392                case FieldType.NullInteger:
393                    IntegerValue = c.TextValue;
394                    break;
395                case FieldType.Number:
396                case FieldType.NullNumber:
397                    NumberValue = c.TextValue;
398                    break;
399                case FieldType.Date:
400                case FieldType.DateSimple:
401                    DateValue = DateString(c.DateValue);
402                    break;
403                case FieldType.Code:
404                case FieldType.CodeStr:
405                case FieldType.DateField:
406                case FieldType.Bit:
407                case FieldType.NullBit:
408                    CodeValue = c.CodeIdValue;
409                    if (c.HasMultipleCodes && CodeValue.HasValue())
410                    {
411                        CodeValues = c.CodeIdValue.Split(';');
412                        foreach (var i in CodeData)
413                            i.Selected = CodeValues.Contains(i.Value);
414                    }
415                    break;
416            }
417            Program = c.Program;
418            DivisionData = Divisions(Program).ToList();
419            Division = c.Division;
420            OrganizationData = Organizations(Division).ToList();
421            Organization = c.Organization;
422            Schedule = c.Schedule;
423            Campus = c.Campus;
424            OrgType = c.OrgType;
425            StartDate = DateString(c.StartDate);
426            EndDate = DateString(c.EndDate);
427            SelectMultiple = c.HasMultipleCodes;
428            AddToGroupEnabled = c.IsGroup;
429            AddEnabled = !c.IsFirst;
430            RemoveEnabled = !c.IsFirst;
431            Days = c.Days.ToString();
432            Age = c.Age.ToString();
433            Quarters = c.Quarters;
434            if (TagsVisible)
435            {
436                if (c.Tags != null)
437                    Tags = c.Tags.Split(';');
438                var cv = new CodeValueModel();
439                TagData = ConvertToSelect(cv.UserTags(Util.UserPeopleId), "Code");
440                foreach (var i in TagData)
441                    i.Selected = Tags.Contains(i.Value);
442            }
443            if (MinistryVisible)
444                Ministry = c.Program;
445            SavedQueryDesc = c.SavedQueryIdDesc;
446        }
447        public void SetCodes()
448        {
449            SetVisibility();
450            SelectMultiple = Comparison.EndsWith("OneOf");
451        }
452        private void NewCondition(QueryBuilderClause gc, int order)
453        {
454            var c = new QueryBuilderClause();
455            c.ClauseOrder = order;
456            gc.Clauses.Add(c);
457            gc.ReorderClauses();
458            UpdateCondition(c);
459        }
460        public void SaveQuery()
461        {
462            var saveto = Db.QueryBuilderClauses.FirstOrDefault(c =>
463                (c.SavedBy == Util.UserName || c.SavedBy == "public") && c.Description == SavedQueryDesc);
464            if (saveto == null)
465            {
466                saveto = new QueryBuilderClause();
467                Db.QueryBuilderClauses.InsertOnSubmit(saveto);
468            }
469            saveto.CopyFromAll(Qb, DbUtil.Db); // save Qb on top of existing
470            if (saveto.SavedBy != "public")
471                saveto.SavedBy = Util.UserName;
472            saveto.Description = SavedQueryDesc;
473            saveto.IsPublic = IsPublic;
474            Db.SubmitChanges();
475            Description = SavedQueryDesc;
476        }
477        public void AddConditionToGroup()
478        {
479            var c = Db.LoadQueryById(SelectedId);
480            NewCondition(c, c.MaxClauseOrder() + 1);
481        }
482        public void AddConditionAfterCurrent()
483        {
484            var c = Db.LoadQueryById(SelectedId);
485            NewCondition(c.Parent, c.ClauseOrder + 1);
486        }
487        public void DeleteCondition()
488        {
489            var c = Db.LoadQueryById(SelectedId);
490            if (c == null)
491                return;
492            SelectedId = c.Parent.QueryId;
493            Db.DeleteQueryBuilderClauseOnSubmit(c);
494            Db.SubmitChanges();
495            EditCondition();
496        }
497        public void UpdateCondition()
498        {
499            var c = Db.LoadQueryById(SelectedId);
500            if (c == null)
501                return;
502            UpdateCondition(c);
503        }
504        public void CopyAsNew()
505        {
506            var Qb = Db.LoadQueryById(SelectedId).Clone(DbUtil.Db);
507            if (!Qb.IsGroup)
508            {
509                var g = new QueryBuilderClause();
510                g.SetQueryType(QueryType.Group);
511                g.SetComparisonType(CompareType.AllTrue);
512                Qb.Parent = g;
513                Qb = g;
514            }
515            Db.SubmitChanges();
516            QueryId = Qb.QueryId;
517        }
518        public void InsertGroupAbove()
519        {
520            var cc = Db.LoadQueryById(SelectedId);
521            var g = new QueryBuilderClause();
522            g.SetQueryType(QueryType.Group);
523            g.SetComparisonType(CompareType.AllTrue);
524            g.ClauseOrder = cc.ClauseOrder;
525            if (cc.IsFirst)
526                cc.Parent = g;
527            else
528            {
529                var currParent = cc.Parent;
530                // find all clauses from cc down at same level
531                var q = from c in cc.Parent.Clauses
532                        orderby c.ClauseOrder
533                        where c.ClauseOrder >= cc.ClauseOrder
534                        select c;
535                foreach (var c in q)
536                    c.Parent = g;   // change to new parent
537                g.Parent = currParent;
538            }
539            if (cc.SavedBy.HasValue())
540            {
541                g.SavedBy = Util.UserName;
542                g.Description = cc.Description;
543                g.CreatedOn = cc.CreatedOn;
544                cc.IsPublic = false;
545                cc.Description = null;
546                cc.SavedBy = null;
547            }
548            Db.SubmitChanges();
549            if (g.IsFirst)
550            {
551                Qb = g;
552                QueryId = g.QueryId;
553            }
554        }
555        public IEnumerable<SelectListItem> Comparisons()
556        {
557            return from c in CompareClass.Comparisons
558                   where c.FieldType == fieldMap.Type
559                   select new SelectListItem { Text = c.CompType.ToString(), Value = c.CompType.ToString() };
560        }
561        public IEnumerable<SelectListItem> Schedules()
562        {
563            var q = from o in DbUtil.Db.Organizations
564                    let sc = o.OrgSchedules.FirstOrDefault() // SCHED
565                    where sc != null
566                    group o by new { ScheduleId = sc.ScheduleId ?? 10800, sc.MeetingTime } into g
567                    orderby g.Key.ScheduleId
568                    select new SelectListItem
569                    {
570                        Value = g.Key.ScheduleId.ToString(),
571                        Text = DbUtil.Db.GetScheduleDesc(g.Key.MeetingTime)
572                    };
573            var list = q.ToList();
574            list.Insert(0, new SelectListItem { Text = "(None)", Value = "-1" });
575            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
576            return list;
577        }
578        public IEnumerable<SelectListItem> Campuses()
579        {
580            var q = from o in DbUtil.Db.Organizations
581                    where o.CampusId != null
582                    group o by o.CampusId into g
583                    orderby g.Key
584                    select new SelectListItem
585                    {
586                        Value = g.Key.ToString(),
587                        Text = g.First().Campu.Description
588                    };
589            var list = q.ToList();
590            list.Insert(0, new SelectListItem { Text = "(None)", Value = "-1" });
591            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
592            return list;
593        }
594        public IEnumerable<SelectListItem> OrgTypes()
595        {
596            var q = from t in Db.OrganizationTypes
597                    orderby t.Code
598                    select new SelectListItem
599                    {
600                        Value = t.Id.ToString(),
601                        Text = t.Description
602                    };
603            var list = q.ToList();
604            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
605            return list;
606        }
607        public IEnumerable<SelectListItem> Programs()
608        {
609            var q = from t in Db.Programs
610                    orderby t.Name
611                    select new SelectListItem
612                    {
613                        Value = t.Id.ToString(),
614                        Text = t.Name
615                    };
616            var list = q.ToList();
617            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
618            return list;
619        }
620        public IEnumerable<SelectListItem> Divisions(int? progid)
621        {
622            var q = from div in Db.Divisions
623                    where div.ProgDivs.Any(d => d.ProgId == progid)
624                    orderby div.Name
625                    select new SelectListItem
626                    {
627                        Value = div.Id.ToString(),
628                        Text = div.Name
629                    };
630            var list = q.ToList();
631            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
632            return list;
633        }
634        public IEnumerable<SelectListItem> Organizations(int? divid)
635        {
636            var roles = Db.CurrentRoles();
637            var q = from ot in Db.DivOrgs
638                    where ot.Organization.LimitToRole == null || roles.Contains(ot.Organization.LimitToRole)
639                    where ot.DivId == divid
640                    && (SqlMethods.DateDiffMonth(ot.Organization.OrganizationClosedDate, Util.Now) < 14
641                        || ot.Organization.OrganizationStatusId == 30)
642                    where (Util2.OrgMembersOnly == false && Util2.OrgLeadersOnly == false) || (ot.Organization.SecurityTypeId != 3)
643                    orderby ot.Organization.OrganizationStatusId, ot.Organization.OrganizationName
644                    select new SelectListItem
645                    {
646                        Value = ot.OrgId.ToString(),
647                        Text = CmsData.Organization.FormatOrgName(ot.Organization.OrganizationName,
648                           ot.Organization.LeaderName, ot.Organization.Location)
649                    };
650            var list = q.ToList();
651            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
652            return list;
653        }
654        private int level;
655        public List<QueryClauseDisplay> ConditionList()
656        {
657            if (Qb == null)
658                LoadScratchPad();
659            level = 0;
660            return ClauseAndSubs(new List<QueryClauseDisplay>(), Qb);
661        }
662        private List<QueryClauseDisplay> ClauseAndSubs(List<QueryClauseDisplay> list, QueryBuilderClause qc)
663        {
664            list.Add(new QueryClauseDisplay { Level = (level * 25), Clause = qc });
665            level++;
666            var q = qc.Clauses.OrderBy(c => c.ClauseOrder);
667            foreach (var c in q)
668                list = ClauseAndSubs(list, c);
669            level--;
670            return list;
671        }
672        public IEnumerable<CategoryClass> FieldCategories()
673        {
674            var q = from c in CategoryClass.Categories
675                    where c.Title != "Grouping"
676                    select c;
677            return q;
678        }
679        public List<SelectListItem> SavedQueries()
680        {
681            var cv = new CodeValueModel();
682            return ConvertToSelect(cv.UserQueries(), "Code");
683        }
684        public List<SelectListItem> Ministries()
685        {
686            var q = from t in Db.Ministries
687                    orderby t.MinistryDescription
688                    select new SelectListItem
689                    {
690                        Value = t.MinistryId.ToString(),
691                        Text = t.MinistryName
692                    };
693            var list = q.ToList();
694            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
695            return list;
696        }
697
698        private IQueryable<Person> query;
699        public int FetchCount()
700        {
701            Db.SetNoLock();
702            query = PersonQuery();
703            count = query.Count();
704            return count ?? 0;
705        }
706        public List<PeopleInfo> Results;
707        public void PopulateResults()
708        {
709            query = PersonQuery();
710            count = query.Count();
711            query = ApplySort(query);
712            query = query.Skip(StartRow).Take(PageSize.Value);
713            Results = FetchPeopleList(query).ToList();
714        }
715        public IEnumerable<PeopleInfo> FetchPeopleList()
716        {
717            query = ApplySort(query);
718            query = query.Skip(StartRow).Take(PageSize.Value);
719            return FetchPeopleList(query);
720        }
721        public class MyClass
722        {
723            public int Id { get; set; }
724            public int PeopleId { get; set; }
725        }
726        public Tag TagAllIds()
727        {
728            query = PersonQuery();
729            var tag = Db.FetchOrCreateTag(Util.SessionId, Util.UserPeopleId, DbUtil.TagTypeId_Query);
730            Db.TagAll(query, tag);
731            return tag;
732        }
733        private IQueryable<Person> PersonQuery()
734        {
735            if (Qb == null)
736                LoadScratchPad();
737            Db.SetNoLock();
738            var q = Db.People.Where(Qb.Predicate(Db));
739            if (Qb.ParentsOf)
740                return Db.PersonQueryParents(q);
741            return q;
742        }
743        public void TagAll(Tag tag = null)
744        {
745            if (Qb == null)
746                LoadScratchPad();
747            Db.SetNoLock();
748            var q = Db.People.Where(Qb.Predicate(Db));
749            if (Qb.ParentsOf)
750                q = Db.PersonQueryParents(q);
751            if (tag != null)
752                Db.TagAll(q, tag);
753            else
754                Db.TagAll(q);
755        }
756        public void UnTagAll()
757        {
758            if (Qb == null)
759                LoadScratchPad();
760            Db.SetNoLock();
761            var q = Db.People.Where(Qb.Predicate(Db));
762            if (Qb.ParentsOf)
763                q = Db.PersonQueryParents(q);
764            Db.UnTagAll(q);
765        }
766        private IEnumerable<PeopleInfo> FetchPeopleList(IQueryable<Person> query)
767        {
768            if (query == null)
769            {
770                Db.SetNoLock();
771                query = PersonQuery();
772                count = query.Count();
773            }
774            var q = from p in query
775                    select new PeopleInfo
776                    {
777                        PeopleId = p.PeopleId,
778                        Name = p.Name,
779                        BirthDate = Util.FormatBirthday(p.BirthYear, p.BirthMonth, p.BirthDay),
780                        Address = p.PrimaryAddress,
781                        Address2 = p.PrimaryAddress2,
782                        CityStateZip = Util.FormatCSZ(p.PrimaryCity, p.PrimaryState, p.PrimaryZip),
783                        HomePhone = p.HomePhone,
784                        CellPhone = p.CellPhone,
785                        WorkPhone = p.WorkPhone,
786                        PhonePref = p.PhonePrefId,
787                        MemberStatus = p.MemberStatus.Description,
788                        Email = p.EmailAddress,
789                        BFTeacher = p.BFClass.LeaderName,
790                        BFTeacherId = p.BFClass.LeaderId,
791                        Employer = p.EmployerOther,
792                        Age = p.Age.ToString(),
793                        HasTag = p.Tags.Any(t => t.Tag.Name == TagName && t.Tag.PeopleId == TagOwner && t.Tag.TypeId == TagTypeId),
794                    };
795            return q;
796        }
797        private IQueryable<Person> ApplySort(IQueryable<Person> q)
798        {
799            if (Sort == null)
800                Sort = "Name";
801            if (Direction != "desc")
802                switch (Sort)
803                {
804                    case "Name":
805                        q = from p in q
806                            orderby p.LastName,
807                            p.FirstName,
808                            p.PeopleId
809                            select p;
810                        break;
811                    case "Status":
812                        q = from p in q
813                            orderby p.MemberStatus.Code,
814                            p.LastName,
815                            p.FirstName,
816                            p.PeopleId
817                            select p;
818                        break;
819                    case "Address":
820                        q = from p in q
821                            orderby p.PrimaryState,
822                            p.PrimaryCity,
823                            p.PrimaryAddress,
824                            p.PeopleId
825                            select p;
826                        break;
827                    case "Fellowship Leader":
828                        q = from p in q
829                            orderby p.BFClass.LeaderName,
830                            p.LastName,
831                            p.FirstName,
832                            p.PeopleId
833                            select p;
834                        break;
835                    case "Employer":
836                        q = from p in q
837                            orderby p.EmployerOther,
838                            p.LastName,
839                            p.FirstName,
840                            p.PeopleId
841                            select p;
842                        break;
843                    case "Communication":
844                        q = from p in q
845                            orderby p.EmailAddress,
846                            p.LastName,
847                            p.FirstName,
848                            p.PeopleId
849                            select p;
850                        break;
851                    case "DOB":
852                        q = from p in q
853                            orderby p.BirthMonth, p.BirthDay,
854                            p.LastName, p.FirstName
855                            select p;
856                        break;
857                }
858            else
859                switch (Sort)
860                {
861                    case "Status":
862                        q = from p in q
863                            orderby p.MemberStatus.Code descending,
864                            p.LastName descending,
865                            p.FirstName descending,
866                            p.PeopleId descending
867                            select p;
868                        break;
869                    case "Address":
870                        q = from p in q
871                            orderby p.PrimaryState descending,
872                            p.PrimaryCity descending,
873                            p.PrimaryAddress descending,
874                            p.PeopleId descending
875                            select p;
876                        break;
877                    case "Name":
878                        q = from p in q
879                            orderby p.LastName descending,
880                            p.LastName descending,
881                            p.PeopleId descending
882                            select p;
883                        break;
884                    case "Fellowship Leader":
885                        q = from p in q
886                            orderby p.BFClass.LeaderName descending,
887                            p.LastName descending,
888                            p.FirstName descending,
889                            p.PeopleId descending
890                            select p;
891                        break;
892                    case "Employer":
893                        q = from p in q
894                            orderby p.EmployerOther descending,
895                            p.LastName descending,
896                            p.FirstName descending,
897                            p.PeopleId descending
898                            select p;
899                        break;
900                    case "Communication":
901                        q = from p in q
902                            orderby p.EmailAddress descending,
903                            p.LastName descending,
904                            p.FirstName descending,
905                            p.PeopleId descending
906                            select p;
907                        break;
908                    case "DOB":
909                        q = from p in q
910                            orderby p.BirthMonth descending, p.BirthDay descending,
911                            p.LastName descending, p.FirstName descending
912                            select p;
913                        break;
914                }
915            return q;
916        }
917        public Dictionary<string, string> Errors;
918
919
920        #region Paging
921        public bool ShowResults { get; set; }
922        public string Sort { get; set; }
923        public string Direction { get; set; }
924
925        private int? _Page;
926        public int? Page
927        {
928            get { return _Page ?? 1; }
929            set { _Page = value; }
930        }
931        private int StartRow
932        {
933            get { return (Page.Value - 1) * PageSize.Value; }
934        }
935        public int? PageSize
936        {
937            get { return DbUtil.Db.UserPreference("PageSize", "10").ToInt(); }
938            set
939            {
940                if (value.HasValue)
941                    DbUtil.Db.SetUserPreference("PageSize", value);
942            }
943        }
944        private int? count;
945        public int Count
946        {
947            get
948            {
949                return count ?? 0;
950            }
951        }
952        public PagerModel pagerModel()
953        {
954            return new PagerModel
955            {
956                Page = Page.Value,
957                PageSize = PageSize.Value,
958                Action = "List",
959                Controller = "Task",
960                Count = Count,
961                ToggleTarget = true
962            };
963        }
964        #endregion
965    }
966    public class QueryClauseDisplay
967    {
968        public Unit Level { get; set; }
969        public QueryBuilderClause Clause;
970    }
971}