PageRenderTime 4ms CodeModel.GetById 2ms app.highlight 15ms RepoModel.GetById 1ms app.codeStats 0ms

/CmsData/QueryFunctions.cs

https://bitbucket.org/rbhakser/bvcms
C# | 315 lines | 259 code | 13 blank | 43 comment | 59 complexity | 3755e82546a3b6a7d6ac88ff1a54e619 MD5 | raw file
  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using UtilityExtensions;
  6using System.Text.RegularExpressions;
  7using System.Data.Linq;
  8using System.Xml.Linq;
  9using System.Data.Linq.SqlClient;
 10using IronPython.Hosting;
 11using System.IO;
 12using CmsData.Codes;
 13using System.Web;
 14using CmsData.API;
 15
 16namespace CmsData
 17{
 18    public class QueryFunctions
 19    {
 20        private CMSDataContext Db;
 21
 22        public QueryFunctions()
 23        {
 24            Db = new CMSDataContext("Data Source=.;Initial Catalog=CMS_bellevue;Integrated Security=True");
 25        }
 26        public QueryFunctions(CMSDataContext Db)
 27        {
 28            this.Db = Db;
 29        }
 30        public static string VitalStats(CMSDataContext Db)
 31        {
 32            var qf = new QueryFunctions(Db);
 33            var script = DbUtil.Content("VitalStats");
 34            if (script == null)
 35                return "no VitalStats script";
 36#if DEBUG2
 37            var options = new Dictionary<string, object>();
 38            options["Debug"] = true;
 39            var engine = Python.CreateEngine(options);
 40            var paths = engine.GetSearchPaths();
 41            paths.Add(path);
 42            engine.SetSearchPaths(paths);
 43            var sc = engine.CreateScriptSourceFromFile(HttpContext.Current.Server.MapPath("/MembershipAutomation2.py"));
 44#else
 45            var engine = Python.CreateEngine();
 46            var sc = engine.CreateScriptSourceFromString(script.Body);
 47#endif
 48
 49            try
 50            {
 51                var code = sc.Compile();
 52                var scope = engine.CreateScope();
 53                code.Execute(scope);
 54
 55                dynamic VitalStats = scope.GetVariable("VitalStats");
 56                dynamic m = VitalStats();
 57                return m.Run(qf);
 58            }
 59            catch (Exception ex)
 60            {
 61                return "VitalStats script error: " + ex.Message;
 62            }
 63        }
 64        public int MeetingCount(int days, int progid, int divid, int orgid)
 65        {
 66            var dt = DateTime.Now.AddDays(-days);
 67            var q = from m in Db.Meetings
 68                    where m.MeetingDate >= dt
 69                    where orgid == 0 || m.OrganizationId == orgid
 70                    where divid == 0 || m.Organization.DivOrgs.Any(t => t.DivId == divid)
 71                    where progid == 0 || m.Organization.DivOrgs.Any(t => t.Division.ProgDivs.Any(d => d.ProgId == progid))
 72                    select m;
 73            return q.Count();
 74        }
 75        public int NumPresent(int days, int progid, int divid, int orgid)
 76        {
 77            var dt = DateTime.Now.AddDays(-days);
 78            var q = from m in Db.Meetings
 79                    where m.MeetingDate >= dt
 80                    where orgid == 0 || m.OrganizationId == orgid
 81                    where divid == 0 || m.Organization.DivOrgs.Any(t => t.DivId == divid)
 82                    where progid == 0 || m.Organization.DivOrgs.Any(t => t.Division.ProgDivs.Any(d => d.ProgId == progid))
 83                    select m;
 84			if (!q.Any())
 85				return 0;
 86			return q.Sum(mm => mm.NumPresent);
 87        }
 88
 89        public int RegistrationCount(int days, int progid, int divid, int orgid)
 90        {
 91            var dt = DateTime.Now.AddDays(-days);
 92            var q = from m in Db.OrganizationMembers
 93                    where m.EnrollmentDate >= dt
 94                    where m.Organization.RegistrationTypeId > 0
 95                    where orgid == 0 || m.OrganizationId == orgid
 96                    where divid == 0 || m.Organization.DivOrgs.Any(t => t.DivId == divid)
 97                    where progid == 0 || m.Organization.DivOrgs.Any(t => t.Division.ProgDivs.Any(d => d.ProgId == progid))
 98                    select m;
 99            return q.Count();
100        }
101        public decimal ContributionTotals(int days1, int days2, int fundid)
102        {
103            return ContributionTotals(days1, days2, fundid.ToString());
104        }
105
106        public int ContributionCount(int days1, int days2, int fundid)
107        {
108            return ContributionCount(days1, days2, fundid.ToString());
109        }
110
111        public int ContributionCount(int days, int fundid)
112        {
113            return ContributionCount(days, fundid.ToString());
114        }
115        public int QueryCount(string s)
116        {
117            var qB = Db.QueryBuilderClauses.FirstOrDefault(c => c.Description == s);
118            if (qB == null)
119                return 0;
120            var q = Db.People.Where(qB.Predicate(Db));
121            return q.Count();
122        }
123        public decimal ContributionTotals(int days1, int days2, string funds)
124        {
125            var fundids = (from f in funds.Split(',')
126                           let i = f.ToInt()
127                           where i > 0
128                           select i).ToArray();
129            var exfundids = (from f in funds.Split(',')
130                             let i = f.ToInt()
131                             where i < 0
132                             select -i).ToArray();
133
134            var dt1 = DateTime.Now.AddDays(-days1);
135            var dt2 = DateTime.Now.AddDays(-days2);
136            var typs = new int[] { 6, 7 };
137            var q = from c in Db.Contributions
138                    where c.ContributionDate >= dt1
139                    where days2 == 0 || c.ContributionDate <= dt2
140                    where c.ContributionTypeId != ContributionTypeCode.Pledge
141                    where fundids.Length == 0 || fundids.Contains(c.FundId)
142                    where exfundids.Length == 0 || !exfundids.Contains(c.FundId)
143                    where !typs.Contains(c.ContributionTypeId)
144                    select c;
145            return q.Sum(c => c.ContributionAmount) ?? 0;
146        }
147
148        public int ContributionCount(int days1, int days2, string funds)
149        {
150            var fundids = (from f in funds.Split(',')
151                           let i = f.ToInt()
152                           where i > 0
153                           select i).ToArray();
154            var exfundids = (from f in funds.Split(',')
155                             let i = f.ToInt()
156                             where i < 0
157                             select -i).ToArray();
158
159            var dt1 = DateTime.Now.AddDays(-days1);
160            var dt2 = DateTime.Now.AddDays(-days2);
161            var typs = new int[] { 6, 7 };
162            var q = from c in Db.Contributions
163                    where c.ContributionDate >= dt1
164                    where days2 == 0 || c.ContributionDate <= dt2
165                    where c.ContributionTypeId != ContributionTypeCode.Pledge
166                    where c.ContributionAmount > 0
167                    where fundids.Length == 0 || fundids.Contains(c.FundId)
168                    where exfundids.Length == 0 || !exfundids.Contains(c.FundId)
169                    where !typs.Contains(c.ContributionTypeId)
170                    select c;
171            return q.Count();
172        }
173
174        public int ContributionCount(int days, string funds)
175        {
176            var fundids = (from f in funds.Split(',')
177                           let i = f.ToInt()
178                           where i > 0
179                           select i).ToArray();
180            var exfundids = (from f in funds.Split(',')
181                             let i = f.ToInt()
182                             where i < 0
183                             select -i).ToArray();
184
185            var dt = DateTime.Now.AddDays(-days);
186            var typs = new int[] { 6, 7 };
187            var q = from c in Db.Contributions
188                    where c.ContributionDate >= dt
189                    where c.ContributionTypeId != ContributionTypeCode.Pledge
190                    where c.ContributionAmount > 0
191                    where fundids.Length == 0 || fundids.Contains(c.FundId)
192                    where exfundids.Length == 0 || !exfundids.Contains(c.FundId)
193                    where !typs.Contains(c.ContributionTypeId)
194                    select c;
195            return q.Count();
196        }
197        public static int Import(CMSDataContext Db, string text, string name)
198		{
199			var x = XDocument.Parse(text);
200			QueryBuilderClause c = null;
201			foreach (var xc in x.Root.Elements())
202			{
203				if (name.HasValue())
204					c = InsertClause(Db, xc, null, name);
205				else
206					c = InsertClause(Db, xc, null, null);
207			}
208			return c.QueryId;
209		}
210        public string Export(int id, string name)
211        {
212            var clause = DbUtil.Db.LoadQueryById(id);
213            var w = new APIWriter();
214            w.Start("Search");
215			w.Attr("Description", name);
216
217            //var settings = new XmlWriterSettings();
218            //settings.Encoding = new System.Text.UTF8Encoding(false);
219            //using (w = XmlWriter.Create(context.HttpContext.Response.OutputStream, settings))
220            //{
221            //    w.WriteStartElement("Search");
222            //    WriteClause(clause);
223            //    w.WriteEndElement();
224            //}
225			return "";
226        }
227        private void WriteClause(QueryBuilderClause clause, API.APIWriter w)
228        {
229            //w.WriteStartElement("Condition");
230            //w.WriteAttributeString("ClauseOrder", clause.ClauseOrder.ToString());
231            //w.WriteAttributeString("Field", clause.Field);
232            //if (clause.Description.HasValue())
233            //    w.WriteAttributeString("Description", clause.Description);
234            //w.WriteAttributeString("Comparison", clause.Comparison);
235            //if (clause.TextValue.HasValue())
236            //    w.WriteAttributeString("TextValue", clause.TextValue);
237            //if (clause.DateValue.HasValue)
238            //    w.WriteAttributeString("DateValue", clause.DateValue.ToString());
239            //if (clause.CodeIdValue.HasValue())
240            //    w.WriteAttributeString("CodeIdValue", clause.CodeIdValue);
241            //if (clause.StartDate.HasValue)
242            //    w.WriteAttributeString("StartDate", clause.StartDate.ToString());
243            //if (clause.EndDate.HasValue)
244            //    w.WriteAttributeString("EndDate", clause.EndDate.ToString());
245            //if (clause.Program > 0)
246            //    w.WriteAttributeString("Program", clause.Program.ToString());
247            //if (clause.Division > 0)
248            //    w.WriteAttributeString("Division", clause.Division.ToString());
249            //if (clause.Organization > 0)
250            //    w.WriteAttributeString("Organization", clause.Organization.ToString());
251            //if (clause.Days > 0)
252            //    w.WriteAttributeString("Days", clause.Days.ToString());
253            //if (clause.Quarters.HasValue())
254            //    w.WriteAttributeString("Quarters", clause.Quarters);
255            //if (clause.Tags.HasValue())
256            //    w.WriteAttributeString("Tags", clause.Tags);
257            //if (clause.Schedule > 0)
258            //    w.WriteAttributeString("Schedule", clause.Schedule.ToString());
259            //if (clause.Age.HasValue)
260            //    w.WriteAttributeString("Age", clause.Age.ToString());
261            //foreach (var qc in clause.Clauses)
262            //    WriteClause(qc);
263            //w.WriteEndElement();
264        }
265        private static QueryBuilderClause InsertClause(CMSDataContext Db, XElement r, int? parent, string name=null)
266        {
267            var c = new QueryBuilderClause
268            {
269                Field = Attribute(r, "Field"),
270                GroupId = parent,
271                ClauseOrder = Attribute(r, "ClauseOrder").ToInt(),
272                Comparison = Attribute(r, "Comparison"),
273                TextValue = Attribute(r, "TextValue"),
274                DateValue = AttributeDate(r, "DateValue"),
275                CodeIdValue = Attribute(r, "CodeIdValue"),
276                StartDate = AttributeDate(r, "StartDate"),
277                EndDate = AttributeDate(r, "EndDate"),
278                Program = Attribute(r, "Program").ToInt(),
279                Division = Attribute(r, "Division").ToInt(),
280                Organization = Attribute(r, "Organization").ToInt(),
281                Days = Attribute(r, "Days").ToInt(),
282                Quarters = Attribute(r, "Quarters"),
283                Tags = Attribute(r, "Tags"),
284                Schedule = Attribute(r, "Schedule").ToInt(),
285                Age = Attribute(r, "Age").ToInt(),
286                Description = name,
287                SavedBy = Util.UserName
288            };
289            Db.QueryBuilderClauses.InsertOnSubmit(c);
290            Db.SubmitChanges();
291            if(c.Field == "Group")
292                foreach (var rr in r.Elements())
293                    InsertClause(Db, rr, c.QueryId);
294            return c;
295        }
296        private static string Attribute(XElement r, string attr)
297        {
298            return Attribute(r, attr, null);
299        }
300        private static string Attribute(XElement r, string attr, string def)
301        {
302            var a = r.Attributes(attr).FirstOrDefault();
303            if (a == null)
304                return def;
305            return a.Value;
306        }
307        private static DateTime? AttributeDate(XElement r, string attr)
308        {
309            var a = r.Attributes(attr).FirstOrDefault();
310            if (a == null)
311                return null;
312            return a.Value.ToDate();
313        }
314    }
315}