PageRenderTime 74ms CodeModel.GetById 29ms app.highlight 37ms RepoModel.GetById 1ms app.codeStats 1ms

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

https://bitbucket.org/rbhakser/bvcms
C# | 960 lines | 935 code | 18 blank | 7 comment | 99 complexity | b43d2b69fef3d9bd584e7388b2203348 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        DateTime? DateParse(string s)
295        {
296            DateTime dt;
297            if (DateTime.TryParse(s, out dt))
298                return dt;
299            return null;
300        }
301        int? IntParse(string s)
302        {
303            int i;
304            if (int.TryParse(s, out i))
305                return i;
306            return null;
307        }
308        string DateString(DateTime? dt)
309        {
310            if (dt.HasValue)
311                return dt.Value.ToShortDateString();
312            return "";
313        }
314        private void UpdateCondition(QueryBuilderClause c)
315        {
316            c.Field = ConditionName;
317            c.Comparison = Comparison;
318            switch (c.FieldInfo.Type)
319            {
320                case FieldType.String:
321                case FieldType.StringEqual:
322                    c.TextValue = TextValue;
323                    break;
324                case FieldType.Integer:
325                case FieldType.IntegerSimple:
326                case FieldType.IntegerEqual:
327                case FieldType.NullInteger:
328                    c.TextValue = IntegerValue;
329                    break;
330                case FieldType.Number:
331                case FieldType.NullNumber:
332                    c.TextValue = NumberValue;
333                    break;
334                case FieldType.Date:
335                case FieldType.DateSimple:
336                    c.DateValue = DateParse(DateValue);
337                    break;
338                case FieldType.Code:
339                case FieldType.CodeStr:
340                case FieldType.DateField:
341                case FieldType.Bit:
342                case FieldType.NullBit:
343                    if (c.HasMultipleCodes && CodeValues != null)
344                        c.CodeIdValue = string.Join(";", CodeValues);
345                    else
346                        c.CodeIdValue = CodeValue;
347                    break;
348            }
349            c.Program = Program ?? 0;
350            c.Division = Division ?? 0;
351            c.Organization = Organization ?? 0;
352            if (MinistryVisible)
353                c.Program = Ministry ?? 0;
354            c.Schedule = Schedule ?? 0;
355			c.Campus = Campus ?? 0;
356			c.OrgType = OrgType ?? 0;
357            c.StartDate = DateParse(StartDate);
358            c.EndDate = DateParse(EndDate);
359            c.Days = Days.ToInt();
360            c.Age = Age.ToInt();
361            c.Quarters = Quarters;
362            if (Tags != null)
363                c.Tags = string.Join(";", Tags);
364            c.SavedQueryIdDesc = SavedQueryDesc;
365            Db.SubmitChanges();
366        }
367        public void EditCondition()
368        {
369            var c = Db.LoadQueryById(SelectedId);
370            if (c == null)
371                return;
372            ConditionName = c.FieldInfo.Name;
373            SetVisibility();
374            Comparison = c.Comparison;
375            switch (c.FieldInfo.Type)
376            {
377                case FieldType.String:
378                case FieldType.StringEqual:
379                    TextValue = c.TextValue;
380                    break;
381                case FieldType.Integer:
382                case FieldType.IntegerSimple:
383                case FieldType.IntegerEqual:
384                case FieldType.NullInteger:
385                    IntegerValue = c.TextValue;
386                    break;
387                case FieldType.Number:
388                case FieldType.NullNumber:
389                    NumberValue = c.TextValue;
390                    break;
391                case FieldType.Date:
392                case FieldType.DateSimple:
393                    DateValue = DateString(c.DateValue);
394                    break;
395                case FieldType.Code:
396                case FieldType.CodeStr:
397                case FieldType.DateField:
398                case FieldType.Bit:
399                case FieldType.NullBit:
400                    CodeValue = c.CodeIdValue;
401                    if (c.HasMultipleCodes && CodeValue.HasValue())
402                    {
403                        CodeValues = c.CodeIdValue.Split(';');
404                        foreach (var i in CodeData)
405                            i.Selected = CodeValues.Contains(i.Value);
406                    }
407                    break;
408            }
409            Program = c.Program;
410            DivisionData = Divisions(Program).ToList();
411            Division = c.Division;
412            OrganizationData = Organizations(Division).ToList();
413            Organization = c.Organization;
414            Schedule = c.Schedule;
415			Campus = c.Campus;
416			OrgType = c.OrgType;
417            StartDate = DateString(c.StartDate);
418            EndDate = DateString(c.EndDate);
419            SelectMultiple = c.HasMultipleCodes;
420            AddToGroupEnabled = c.IsGroup;
421            AddEnabled = !c.IsFirst;
422            RemoveEnabled = !c.IsFirst;
423            Days = c.Days.ToString();
424            Age = c.Age.ToString();
425            Quarters = c.Quarters;
426            if (TagsVisible)
427            {
428                if (c.Tags != null)
429                    Tags = c.Tags.Split(';');
430                var cv = new CodeValueModel();
431                TagData = ConvertToSelect(cv.UserTags(Util.UserPeopleId), "Code");
432                foreach (var i in TagData)
433                    i.Selected = Tags.Contains(i.Value);
434            }
435            if (MinistryVisible)
436                Ministry = c.Program;
437            SavedQueryDesc = c.SavedQueryIdDesc;
438        }
439        public void SetCodes()
440        {
441            SetVisibility();
442            SelectMultiple = Comparison.EndsWith("OneOf");
443        }
444        private void NewCondition(QueryBuilderClause gc, int order)
445        {
446            var c = new QueryBuilderClause();
447            c.ClauseOrder = order;
448            gc.Clauses.Add(c);
449            gc.ReorderClauses();
450            UpdateCondition(c);
451        }
452        public void SaveQuery()
453        {
454            var saveto = Db.QueryBuilderClauses.FirstOrDefault(c =>
455                (c.SavedBy == Util.UserName || c.SavedBy == "public") && c.Description == SavedQueryDesc);
456            if (saveto == null)
457            {
458                saveto = new QueryBuilderClause();
459                Db.QueryBuilderClauses.InsertOnSubmit(saveto);
460            }
461            saveto.CopyFromAll(Qb, DbUtil.Db); // save Qb on top of existing
462            if (saveto.SavedBy != "public")
463                saveto.SavedBy = Util.UserName;
464            saveto.Description = SavedQueryDesc;
465            saveto.IsPublic = IsPublic;
466            Db.SubmitChanges();
467            Description = SavedQueryDesc;
468        }
469        public void AddConditionToGroup()
470        {
471            var c = Db.LoadQueryById(SelectedId);
472            NewCondition(c, c.MaxClauseOrder() + 1);
473        }
474        public void AddConditionAfterCurrent()
475        {
476            var c = Db.LoadQueryById(SelectedId);
477            NewCondition(c.Parent, c.ClauseOrder + 1);
478        }
479        public void DeleteCondition()
480        {
481            var c = Db.LoadQueryById(SelectedId);
482			if (c == null)
483				return;
484            SelectedId = c.Parent.QueryId;
485            Db.DeleteQueryBuilderClauseOnSubmit(c);
486            Db.SubmitChanges();
487            EditCondition();
488        }
489        public void UpdateCondition()
490        {
491            var c = Db.LoadQueryById(SelectedId);
492            if (c == null)
493                return;
494            UpdateCondition(c);
495        }
496        public void CopyAsNew()
497        {
498            var Qb = Db.LoadQueryById(SelectedId).Clone(DbUtil.Db);
499            if (!Qb.IsGroup)
500            {
501                var g = new QueryBuilderClause();
502                g.SetQueryType(QueryType.Group);
503                g.SetComparisonType(CompareType.AllTrue);
504                Qb.Parent = g;
505                Qb = g;
506            }
507            Db.SubmitChanges();
508            QueryId = Qb.QueryId;
509        }
510        public void InsertGroupAbove()
511        {
512            var cc = Db.LoadQueryById(SelectedId);
513            var g = new QueryBuilderClause();
514            g.SetQueryType(QueryType.Group);
515            g.SetComparisonType(CompareType.AllTrue);
516            g.ClauseOrder = cc.ClauseOrder;
517            if (cc.IsFirst)
518                cc.Parent = g;
519            else
520            {
521                var currParent = cc.Parent;
522                // find all clauses from cc down at same level
523                var q = from c in cc.Parent.Clauses
524                        orderby c.ClauseOrder
525                        where c.ClauseOrder >= cc.ClauseOrder
526                        select c;
527                foreach (var c in q)
528                    c.Parent = g;   // change to new parent
529                g.Parent = currParent;
530            }
531            if (cc.SavedBy.HasValue())
532            {
533                g.SavedBy = Util.UserName;
534                g.Description = cc.Description;
535                g.CreatedOn = cc.CreatedOn;
536                cc.IsPublic = false;
537                cc.Description = null;
538                cc.SavedBy = null;
539            }
540            Db.SubmitChanges();
541            if (g.IsFirst)
542            {
543                Qb = g;
544                QueryId = g.QueryId;
545            }
546        }
547        public IEnumerable<SelectListItem> Comparisons()
548        {
549            return from c in CompareClass.Comparisons
550                   where c.FieldType == fieldMap.Type
551                   select new SelectListItem { Text = c.CompType.ToString(), Value = c.CompType.ToString() };
552        }
553        public IEnumerable<SelectListItem> Schedules()
554        {
555            var q = from o in DbUtil.Db.Organizations
556                    let sc = o.OrgSchedules.FirstOrDefault() // SCHED
557                    where sc != null
558                    group o by new { ScheduleId = sc.ScheduleId ?? 10800, sc.MeetingTime } into g
559                    orderby g.Key.ScheduleId
560                    select new SelectListItem
561                    {
562                        Value = g.Key.ScheduleId.ToString(),
563                        Text = DbUtil.Db.GetScheduleDesc(g.Key.MeetingTime)
564                    };
565            var list = q.ToList();
566            list.Insert(0, new SelectListItem { Text = "(None)", Value = "-1" });
567            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
568            return list;
569        }
570        public IEnumerable<SelectListItem> Campuses()
571        {
572            var q = from o in DbUtil.Db.Organizations
573					where o.CampusId != null
574                    group o by o.CampusId into g
575                    orderby g.Key
576                    select new SelectListItem
577                    {
578                        Value = g.Key.ToString(),
579                        Text = g.First().Campu.Description
580                    };
581            var list = q.ToList();
582            list.Insert(0, new SelectListItem { Text = "(None)", Value = "-1" });
583            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
584            return list;
585        }
586        public IEnumerable<SelectListItem> OrgTypes()
587        {
588            var q = from t in Db.OrganizationTypes
589                    orderby t.Code
590                    select new SelectListItem
591                    {
592                        Value = t.Id.ToString(),
593                        Text = t.Description
594                    };
595            var list = q.ToList();
596            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
597            return list;
598        }
599        public IEnumerable<SelectListItem> Programs()
600        {
601            var q = from t in Db.Programs
602                    orderby t.Name
603                    select new SelectListItem
604                    {
605                        Value = t.Id.ToString(),
606                        Text = t.Name
607                    };
608            var list = q.ToList();
609            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
610            return list;
611        }
612        public IEnumerable<SelectListItem> Divisions(int? progid)
613        {
614            var q = from div in Db.Divisions
615                    where div.ProgDivs.Any(d => d.ProgId == progid)
616                    orderby div.Name
617                    select new SelectListItem
618                    {
619                        Value = div.Id.ToString(),
620                        Text = div.Name
621                    };
622            var list = q.ToList();
623            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
624            return list;
625        }
626        public IEnumerable<SelectListItem> Organizations(int? divid)
627        {
628        	var roles = Db.CurrentRoles();
629            var q = from ot in Db.DivOrgs
630        	        where ot.Organization.LimitToRole == null || roles.Contains(ot.Organization.LimitToRole)
631                    where ot.DivId == divid
632                    && (SqlMethods.DateDiffMonth(ot.Organization.OrganizationClosedDate, Util.Now) < 14
633                        || ot.Organization.OrganizationStatusId == 30)
634                    where (Util2.OrgMembersOnly == false && Util2.OrgLeadersOnly == false) || (ot.Organization.SecurityTypeId != 3)
635                    orderby ot.Organization.OrganizationStatusId, ot.Organization.OrganizationName
636                    select new SelectListItem
637                    {
638                        Value = ot.OrgId.ToString(),
639                        Text = CmsData.Organization.FormatOrgName(ot.Organization.OrganizationName,
640                           ot.Organization.LeaderName, ot.Organization.Location)
641                    };
642            var list = q.ToList();
643            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
644            return list;
645        }
646        private int level;
647        public List<QueryClauseDisplay> ConditionList()
648        {
649            if (Qb == null)
650                LoadScratchPad();
651            level = 0;
652            return ClauseAndSubs(new List<QueryClauseDisplay>(), Qb);
653        }
654        private List<QueryClauseDisplay> ClauseAndSubs(List<QueryClauseDisplay> list, QueryBuilderClause qc)
655        {
656            list.Add(new QueryClauseDisplay { Level = (level * 25), Clause = qc });
657            level++;
658            var q = qc.Clauses.OrderBy(c => c.ClauseOrder);
659            foreach (var c in q)
660                list = ClauseAndSubs(list, c);
661            level--;
662            return list;
663        }
664        public IEnumerable<CategoryClass> FieldCategories()
665        {
666            var q = from c in CategoryClass.Categories
667                    where c.Title != "Grouping"
668                    select c;
669            return q;
670        }
671        public List<SelectListItem> SavedQueries()
672        {
673            var cv = new CodeValueModel();
674            return ConvertToSelect(cv.UserQueries(), "Code");
675        }
676        public List<SelectListItem> Ministries()
677        {
678            var q = from t in Db.Ministries
679                    orderby t.MinistryDescription
680                    select new SelectListItem
681                    {
682                        Value = t.MinistryId.ToString(),
683                        Text = t.MinistryName
684                    };
685            var list = q.ToList();
686            list.Insert(0, new SelectListItem { Text = "(not specified)", Value = "0" });
687            return list;
688        }
689
690        private IQueryable<Person> query;
691        public int FetchCount()
692        {
693            Db.SetNoLock();
694            query = PersonQuery();
695            count = query.Count();
696            return count ?? 0;
697        }
698		public List<PeopleInfo> Results;
699		public void PopulateResults()
700		{
701			query = PersonQuery();
702			count = query.Count();
703			query = ApplySort(query);
704            query = query.Skip(StartRow).Take(PageSize.Value);
705            Results = FetchPeopleList(query).ToList();
706		}
707        public IEnumerable<PeopleInfo> FetchPeopleList()
708        {
709            query = ApplySort(query);
710            query = query.Skip(StartRow).Take(PageSize.Value);
711            return FetchPeopleList(query);
712        }
713		public class MyClass
714		{
715			public int Id { get; set; }
716			public int PeopleId { get; set; }
717		}
718        public Tag TagAllIds()
719        {
720            query = PersonQuery();
721			var tag = Db.FetchOrCreateTag(Util.SessionId, Util.UserPeopleId, DbUtil.TagTypeId_Query);
722			Db.TagAll(query, tag);
723        	return tag;
724        }
725		private IQueryable<Person> PersonQuery()
726        {
727            if (Qb == null)
728                LoadScratchPad();
729            Db.SetNoLock();
730            var q = Db.People.Where(Qb.Predicate(Db));
731            if (Qb.ParentsOf)
732				return Db.PersonQueryParents(q);
733            return q;
734        }
735        public void TagAll()
736        {
737            if (Qb == null)
738                LoadScratchPad();
739            Db.SetNoLock();
740            var q = Db.People.Where(Qb.Predicate(Db));
741            if (Qb.ParentsOf)
742				q = Db.PersonQueryParents(q);
743            Db.TagAll(q);
744        }
745        public void UnTagAll()
746        {
747            if (Qb == null)
748                LoadScratchPad();
749            Db.SetNoLock();
750            var q = Db.People.Where(Qb.Predicate(Db));
751            if (Qb.ParentsOf)
752				q = Db.PersonQueryParents(q);
753            Db.UnTagAll(q);
754        }
755        private IEnumerable<PeopleInfo> FetchPeopleList(IQueryable<Person> query)
756        {
757			if (query == null)
758			{
759				Db.SetNoLock();
760				query = PersonQuery();
761				count = query.Count();
762			}
763            var q = from p in query
764                    select new PeopleInfo
765                    {
766                        PeopleId = p.PeopleId,
767                        Name = p.Name,
768                        BirthDate = Util.FormatBirthday(p.BirthYear, p.BirthMonth, p.BirthDay),
769                        Address = p.PrimaryAddress,
770                        Address2 = p.PrimaryAddress2,
771                        CityStateZip = Util.FormatCSZ(p.PrimaryCity, p.PrimaryState, p.PrimaryZip),
772                        HomePhone = p.HomePhone,
773                        CellPhone = p.CellPhone,
774                        WorkPhone = p.WorkPhone,
775                        PhonePref = p.PhonePrefId,
776                        MemberStatus = p.MemberStatus.Description,
777                        Email = p.EmailAddress,
778                        BFTeacher = p.BFClass.LeaderName,
779                        BFTeacherId = p.BFClass.LeaderId,
780                        Employer = p.EmployerOther,
781                        Age = p.Age.ToString(),
782                        HasTag = p.Tags.Any(t => t.Tag.Name == TagName && t.Tag.PeopleId == TagOwner && t.Tag.TypeId == TagTypeId),
783                    };
784            return q;
785        }
786        private IQueryable<Person> ApplySort(IQueryable<Person> q)
787        {
788            if (Sort == null)
789                Sort = "Name";
790            if (Direction != "desc")
791                switch (Sort)
792                {
793                    case "Name":
794                        q = from p in q
795                            orderby p.LastName,
796                            p.FirstName,
797                            p.PeopleId
798                            select p;
799                        break;
800                    case "Status":
801                        q = from p in q
802                            orderby p.MemberStatus.Code,
803                            p.LastName,
804                            p.FirstName,
805                            p.PeopleId
806                            select p;
807                        break;
808                    case "Address":
809                        q = from p in q
810                            orderby p.PrimaryState,
811                            p.PrimaryCity,
812                            p.PrimaryAddress,
813                            p.PeopleId
814                            select p;
815                        break;
816                    case "Fellowship Leader":
817                        q = from p in q
818                            orderby p.BFClass.LeaderName,
819                            p.LastName,
820                            p.FirstName,
821                            p.PeopleId
822                            select p;
823                        break;
824                    case "Employer":
825                        q = from p in q
826                            orderby p.EmployerOther,
827                            p.LastName,
828                            p.FirstName,
829                            p.PeopleId
830                            select p;
831                        break;
832                    case "Communication":
833                        q = from p in q
834                            orderby p.EmailAddress,
835                            p.LastName,
836                            p.FirstName,
837                            p.PeopleId
838                            select p;
839                        break;
840                    case "DOB":
841                        q = from p in q
842                            orderby p.BirthMonth, p.BirthDay,
843                            p.LastName, p.FirstName
844                            select p;
845                        break;
846                }
847            else
848                switch (Sort)
849                {
850                    case "Status":
851                        q = from p in q
852                            orderby p.MemberStatus.Code descending,
853                            p.LastName descending,
854                            p.FirstName descending,
855                            p.PeopleId descending
856                            select p;
857                        break;
858                    case "Address":
859                        q = from p in q
860                            orderby p.PrimaryState descending,
861                            p.PrimaryCity descending,
862                            p.PrimaryAddress descending,
863                            p.PeopleId descending
864                            select p;
865                        break;
866                    case "Name":
867                        q = from p in q
868                            orderby p.LastName descending,
869                            p.LastName descending,
870                            p.PeopleId descending
871                            select p;
872                        break;
873                    case "Fellowship Leader":
874                        q = from p in q
875                            orderby p.BFClass.LeaderName descending,
876                            p.LastName descending,
877                            p.FirstName descending,
878                            p.PeopleId descending
879                            select p;
880                        break;
881                    case "Employer":
882                        q = from p in q
883                            orderby p.EmployerOther descending,
884                            p.LastName descending,
885                            p.FirstName descending,
886                            p.PeopleId descending
887                            select p;
888                        break;
889                    case "Communication":
890                        q = from p in q
891                            orderby p.EmailAddress descending,
892                            p.LastName descending,
893                            p.FirstName descending,
894                            p.PeopleId descending
895                            select p;
896                        break;
897                    case "DOB":
898                        q = from p in q
899                            orderby p.BirthMonth descending, p.BirthDay descending,
900                            p.LastName descending, p.FirstName descending
901                            select p;
902                        break;
903                }
904            return q;
905        }
906        public Dictionary<string, string> Errors;
907
908
909        #region Paging
910        public bool ShowResults { get; set; }
911        public string Sort { get; set; }
912        public string Direction { get; set; }
913
914        private int? _Page;
915        public int? Page
916        {
917            get { return _Page ?? 1; }
918            set { _Page = value; }
919        }
920        private int StartRow
921        {
922            get { return (Page.Value - 1) * PageSize.Value; }
923        }
924        public int? PageSize
925        {
926            get { return DbUtil.Db.UserPreference("PageSize", "10").ToInt(); }
927            set
928            {
929                if (value.HasValue)
930                    DbUtil.Db.SetUserPreference("PageSize", value);
931            }
932        }
933        private int? count;
934        public int Count
935        {
936            get
937            {
938                return count ?? 0;
939            }
940        }
941        public PagerModel pagerModel()
942        {
943            return new PagerModel
944            {
945                Page = Page.Value,
946                PageSize = PageSize.Value,
947                Action = "List",
948                Controller = "Task",
949                Count = Count,
950				ToggleTarget = true
951            };
952        }
953        #endregion
954    }
955    public class QueryClauseDisplay
956    {
957        public Unit Level { get; set; }
958        public QueryBuilderClause Clause;
959    }
960}