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