public void RunPyScript()
{
var content = CurrentDatabase.ContentOfTypePythonScript(Report);
if (content == null)
{
throw new Exception("no script named " + Report);
}
if (!CanRunScript(content))
{
throw new Exception("Not Authorized to run this script");
}
if (!content.Contains("BlueToolbarReport") && !content.Contains("@BlueToolbarTagId"))
{
throw new Exception("Missing Call to Query Function 'BlueToolbarReport'");
}
if (Id == Guid.Empty)
{
throw new Exception("Must be run from the BlueToolbar");
}
var pe = new PythonModel(CurrentDatabase.Host);
pe.DictionaryAdd("BlueToolbarGuid", Id.ToCode());
foreach (var key in HttpContextFactory.Current.Request.QueryString.AllKeys)
{
pe.DictionaryAdd(key, HttpContextFactory.Current.Request.QueryString[key]);
}
pe.RunScript(content);
Results = pe.Output;
}
public ActionResult PyScript(Guid id, string report)
{
var content = DbUtil.Db.ContentOfTypePythonScript(report);
if (content == null)
{
return(Content("no script named " + report));
}
if (!CanRunScript(content))
{
return(Message("Not Authorized to run this script"));
}
if (!content.Contains("BlueToolbarReport") && !content.Contains("@BlueToolbarTagId"))
{
return(Content("Missing Call to Query Function 'BlueToolbarReport'"));
}
if (id == Guid.Empty)
{
return(Content("Must be run from the BlueToolbar"));
}
var pe = new PythonModel(Util.Host);
pe.DictionaryAdd("BlueToolbarGuid", id.ToCode());
foreach (var key in Request.QueryString.AllKeys)
{
pe.DictionaryAdd(key, Request.QueryString[key]);
}
pe.RunScript(content);
return(View(pe));
}
private ActionResult RunProgressInBackground(string script)
{
var logFile = $"RunPythonScriptInBackground.{DateTime.Now:yyyyMMddHHmmss}";
ViewBag.LogFile = logFile;
var qs = Request.Url?.Query;
HostingEnvironment.QueueBackgroundWorkItem(ct =>
{
var qsa = HttpUtility.ParseQueryString(qs ?? "");
var pm = new PythonModel(CurrentDatabase);
pm.DictionaryAdd("LogFile", logFile);
foreach (string key in qsa)
{
pm.DictionaryAdd(key, qsa[key]);
}
string result = pm.RunScript(script);
if (result.HasValue())
{
pm.LogToContent(logFile, result);
}
});
return(View("RunPythonScriptProgress"));
}
public ActionResult PyScript(string name, string p1, string p2, string v1, string v2)
{
try
{
var script = DbUtil.Db.ContentOfTypePythonScript(name);
if (!script.HasValue())
{
return(Message("no script named " + name));
}
if (!CanRunScript(script))
{
return(Message("Not Authorized to run this script"));
}
if (script.Contains("model.Form"))
{
return(Redirect("/PyScriptForm/" + name));
}
script = script.Replace("@P1", p1 ?? "NULL")
.Replace("@P2", p2 ?? "NULL")
.Replace("V1", v1 ?? "None")
.Replace("V2", v2 ?? "None");
if (script.Contains("@qtagid"))
{
var id = DbUtil.Db.FetchLastQuery().Id;
var tag = DbUtil.Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query);
script = script.Replace("@qtagid", tag.Id.ToString());
}
var pe = new PythonModel(Util.Host);
if (script.Contains("@BlueToolbarTagId"))
{
var id = DbUtil.Db.FetchLastQuery().Id;
pe.DictionaryAdd("BlueToolbarGuid", id.ToCode());
}
foreach (var key in Request.QueryString.AllKeys)
{
pe.DictionaryAdd(key, Request.QueryString[key]);
}
pe.RunScript(script);
ViewBag.report = name;
ViewBag.url = Request.Url?.PathAndQuery;
return(View(pe));
}
catch (Exception ex)
{
return(RedirectShowError(ex.Message));
}
}
public ActionResult PyScriptForm(string name)
{
try
{
var script = FetchPyScriptForm(name);
if (!script.HasValue())
{
return(Message("no script named " + name));
}
var pe = new PythonModel(Util.Host);
foreach (var key in Request.QueryString.AllKeys)
{
pe.DictionaryAdd(key, Request.QueryString[key]);
}
pe.Data.pyscript = name;
pe.HttpMethod = "get";
pe.RunScript(script);
return(View(pe));
}
catch (Exception ex)
{
return(RedirectShowError(ex.Message));
}
}
public ActionResult TestScript()
{
//var id = DbUtil.Db.ScratchPadQuery(@"MemberStatusId = 10[Member] AND LastName = 'C*'");
var file = Server.MapPath("~/test.py");
var logFile = $"RunPythonScriptInBackground.{DateTime.Now:yyyyMMddHHmmss}";
string host = Util.Host;
var background = true;
if (background)
{
HostingEnvironment.QueueBackgroundWorkItem(ct =>
{
var pe = new PythonModel(host);
//pe.DictionaryAdd("OrgId", "89658");
pe.DictionaryAdd("LogFile", logFile);
PythonModel.ExecutePythonFile(file, pe);
});
return(View("RunPythonScriptProgress"));
}
else
{
var pe = new PythonModel(host);
pe.DictionaryAdd("LogFile", logFile);
ViewBag.Text = PythonModel.ExecutePythonFile(file, pe);
return(View("Test"));
}
}
private ActionResult PyScriptFormPost(string name)
{
try
{
var pe = new PythonModel(Util.Host);
ScriptModel.GetFilesContent(pe);
foreach (var key in Request.Form.AllKeys)
{
pe.DictionaryAdd(key, Request.Form[key]);
}
pe.HttpMethod = "post";
var ret = ScriptModel.Run(name, pe);
if (ret.StartsWith("REDIRECT="))
{
return(Redirect(ret.Substring(9).Trim()));
}
return(Content(ret));
}
catch (Exception ex)
{
return(RedirectShowError(ex.Message));
}
}
internal static void GetFilesContent(PythonModel pe)
{
var files = HttpContext.Current.Request.Files;
var a = files.AllKeys;
for (var i = 0; i < a.Length; i++)
{
var file = files[i];
var buffer = new byte[file.ContentLength];
file.InputStream.Read(buffer, 0, file.ContentLength);
System.Text.Encoding enc;
string s = null;
if (buffer[0] == 0xEF && buffer[1] == 0xBB && buffer[2] == 0xBF)
{
enc = new System.Text.ASCIIEncoding();
s = enc.GetString(buffer, 3, buffer.Length - 3);
}
else if (buffer[0] == 0xFF && buffer[1] == 0xFE)
{
enc = new System.Text.UnicodeEncoding();
s = enc.GetString(buffer, 2, buffer.Length - 2);
}
else
{
enc = new System.Text.ASCIIEncoding();
s = enc.GetString(buffer);
}
pe.DictionaryAdd(a[i], s);
}
}
public void Python_Result_Should_Have_All_SQL_Results()
{
var db = CMSDataContext.Create(DatabaseFixture.Host);
var model = new PythonModel(db);
var id = db.FetchLastQuery().Id;
model.DictionaryAdd("BlueToolbarGuid", id.ToCode());
string sql = "\n;WITH givingunits AS (\n SELECT p.PeopleId FROM dbo.People p JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @BlueToolbarTagId\n UNION\n SELECT p.SpouseId FROM dbo.People p JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @BlueToolbarTagId\n WHERE ISNULL(p.ContributionOptionsId, IIF(p.MaritalStatusId = 2, 2, 1)) = 2\n)\nSELECT CreditGiverId,\n SpouseId,\n Amount,\n DATEPART(YEAR, Date) Y,\n c.PeopleId,\n Date,\n c.FundId\n INTO #t\n FROM dbo.Contributions2('1/1/2016','12/31/2018',0,0,NULL,NULL, NULL) c\n WHERE EXISTS(SELECT NULL FROM givingunits WHERE PeopleId IN (c.CreditGiverId, c.CreditGiverId2))\n AND Amount > 0\n\n;WITH giving AS (\n SELECT\n CreditGiverId, SpouseId\n , ISNULL((SELECT SUM(Amount)\n FROM #t\n WHERE CreditGiverId = tt.CreditGiverId\n AND Y = 2016), 0) Tot2016\n , ISNULL((SELECT SUM(Amount)\n FROM #t\n WHERE CreditGiverId = tt.CreditGiverId\n AND Y = 2017), 0) Tot2017\n , ISNULL((SELECT SUM(Amount)\n FROM #t\n WHERE CreditGiverId = tt.CreditGiverId\n AND Y = 2018), 0) Tot2018\n FROM #t tt\n GROUP BY tt.CreditGiverId, tt.SpouseId\n)\nSELECT\n p.PeopleId,\n Head = p.Name2,\n Spouse = sp.PreferredName,\n g.Tot2016,\n g.Tot2017,\n g.Tot2018\nFROM giving g\nJOIN dbo.People p ON p.PeopleId = g.CreditGiverId\nLEFT JOIN dbo.People sp ON sp.PeopleId = g.SpouseId\nORDER BY p.Name2\n\nDROP TABLE #t\n";
var result = model.SqlGrid(sql);
result.Length.ShouldBeGreaterThan(0);
}
private void AddYesNo(PythonModel pe)
{
if (pe.DataHas("YesNoQuestion"))
{
return;
}
if (YesNoQuestion == null || YesNoQuestion.Count == 0)
{
return;
}
var yesnoquestions = new DynamicData();
pe.DictionaryAdd("YesNoQuestion", yesnoquestions);
foreach (var dict in YesNoQuestion)
{
yesnoquestions.AddValue(dict.Key, dict.Value ?? false);
}
}
private void AddDropDownOptions(PythonModel pe)
{
if (pe.DataHas("DropdownOption"))
{
return;
}
if (option == null)
{
return;
}
var options = new DynamicData();
pe.DictionaryAdd("DropdownOption", options);
foreach (var o in option)
{
options.AddValue(o, true);
}
}
private void AddCheckboxes(PythonModel pe)
{
if (pe.DataHas("Checkbox"))
{
return;
}
if (Checkbox == null)
{
return;
}
var checkboxes = new DynamicData();
pe.DictionaryAdd("Checkbox", checkboxes);
foreach (var c in Checkbox)
{
checkboxes.AddValue(c, true);
}
}
public ActionResult PyScriptForm()
{
try
{
var pe = new PythonModel(Util.Host);
foreach (var key in Request.Form.AllKeys)
{
pe.DictionaryAdd(key, Request.Form[key]);
}
pe.HttpMethod = "post";
var script = DbUtil.Db.ContentOfTypePythonScript(pe.Data.pyscript);
return(Content(pe.RunScript(script)));
}
catch (Exception ex)
{
return(RedirectShowError(ex.Message));
}
}
private ActionResult PyScriptFormGet(string name)
{
try
{
var pe = new PythonModel(Util.Host);
foreach (var key in Request.QueryString.AllKeys)
{
pe.DictionaryAdd(key, Request.QueryString[key]);
}
pe.Data.pyscript = name;
pe.HttpMethod = "get";
ScriptModel.Run(name, pe);
return(View(pe));
}
catch (Exception ex)
{
return(RedirectShowError(ex.Message));
}
}
private void AddQuestions(PythonModel pe)
{
if (pe.DataHas("ExtraQuestion"))
{
return;
}
if (ExtraQuestion == null || ExtraQuestion.Count == 0)
{
return;
}
var questions = new DynamicData();
pe.DictionaryAdd("ExtraQuestion", questions);
foreach (var dict in ExtraQuestion)
{
foreach (var q in dict)
{
questions.AddValue(q.Key, q.Value);
}
}
}
private void AddMenuItems(PythonModel pe)
{
if (pe.DataHas("MenuItem"))
{
return;
}
if (MenuItem == null)
{
return;
}
var menuitems = new DynamicData();
pe.DictionaryAdd("MenuItem", menuitems);
foreach (var dict in MenuItem)
{
foreach (var q in dict)
{
menuitems.AddValue(q.Key, q.Value ?? 0);
}
}
}
private void AddText(PythonModel pe)
{
if (pe.DataHas("TextQuestion"))
{
return;
}
if (Text == null || Text.Count == 0)
{
return;
}
var textquestions = new DynamicData();
pe.DictionaryAdd("TextQuestion", textquestions);
foreach (var dict in Text)
{
foreach (var q in dict)
{
textquestions.AddValue(q.Key, q.Value);
}
}
}
private string RunScript()
{
if (person == null)
{
return(GetNoPersonMessage());
}
var m = new PythonModel(CurrentDatabase);
var script = CurrentDatabase.ContentOfTypePythonScript(action.ScriptName);
if (!script.HasValue())
{
return(GetError($"Script name {action.ScriptName} not found"));
}
m.DictionaryAdd("ToNumber", To);
m.DictionaryAdd("ToGroupId", row.ToGroupId);
m.DictionaryAdd("FromNumber", From);
m.DictionaryAdd("Message", Body);
m.DictionaryAdd("PeopleId", row.FromPeopleId);
m.DictionaryAdd("Name", person.Name);
m.DictionaryAdd("First", person.FirstName);
m.DictionaryAdd("Last", person.LastName);
var msg = Util.PickFirst(
m.RunScript(script).Trim(),
action.ReplyMessage,
action.DefaultMessage);
row.ActionResponse = DoReplacments(msg);
CurrentDatabase.SmsReceiveds.InsertOnSubmit(row);
CurrentDatabase.SubmitChanges();
SendNotices();
if (msg.Equal("NONE"))
{
return(String.Empty);
}
return(row.ActionResponse);
}
private void BuildDynamicData(PythonModel pe, OrganizationMember om)
{
pe.DictionaryAdd("PeopleId", PeopleId ?? 0);
pe.DictionaryAdd("OrganizationId", om.OrganizationId);
var notifyIds = DbUtil.Db.StaffPeopleForOrg(om.OrganizationId);
pe.DictionaryAdd("OnlineNotifyId", notifyIds[0].PeopleId);
pe.DictionaryAdd("OnlineNotifyEmail", notifyIds[0].EmailAddress);
pe.DictionaryAdd("OnlineNotifyName", notifyIds[0].Name);
var props = typeof(OnlineRegPersonModel).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var pi in props.Where(vv => vv.CanRead && vv.CanWrite))
{
switch (pi.Name)
{
case "ExtraQuestion":
AddQuestions(pe);
break;
case "Text":
AddText(pe);
break;
case "YesNoQuestion":
AddYesNo(pe);
break;
case "option":
AddDropDownOptions(pe);
break;
case "Checkbox":
AddCheckboxes(pe);
break;
case "MenuItem":
AddMenuItems(pe);
break;
case "ScriptResults":
if (ScriptResults.HasValue())
{
pe.DictionaryAdd(pi.Name, ScriptResults);
}
break;
case "memberus":
if (memberus)
{
pe.DictionaryAdd("MemberUs", memberus);
}
break;
case "otherchurch":
if (otherchurch)
{
pe.DictionaryAdd("OtherChurch", otherchurch);
}
break;
case "LoggedIn":
pe.DictionaryAdd(pi.Name, LoggedIn);
break;
case "FirstName":
pe.DictionaryAdd(pi.Name, FirstName);
break;
case "LastName":
pe.DictionaryAdd(pi.Name, LastName);
break;
}
}
}
public ActionResult PyScript(string name, string p1, string p2, string v1, string v2)
{
#if DEBUG
#else
try
{
#endif
var script = CurrentDatabase.ContentOfTypePythonScript(name);
if (!script.HasValue())
{
return(Message("no script named " + name));
}
if (!ScriptModel.CanRunScript(script))
{
return(Message("Not Authorized to run this script"));
}
if (Regex.IsMatch(script, @"model\.Form\b"))
{
return(Redirect("/PyScriptForm/" + name));
}
script = script.Replace("@P1", p1 ?? "NULL")
.Replace("@P2", p2 ?? "NULL")
.Replace("V1", v1 ?? "None")
.Replace("V2", v2 ?? "None");
if (script.Contains("@qtagid"))
{
var id = CurrentDatabase.FetchLastQuery().Id;
var tag = CurrentDatabase.PopulateSpecialTag(id, DbUtil.TagTypeId_Query);
script = script.Replace("@qtagid", tag.Id.ToString());
}
ViewBag.report = name;
ViewBag.url = Request.Url?.PathAndQuery;
if (script.Contains("Background Process Completed"))
{
var logFile = $"RunPythonScriptInBackground.{DateTime.Now:yyyyMMddHHmmss}";
ViewBag.LogFile = logFile;
var qs = Request.Url?.Query;
var host = Util.Host;
HostingEnvironment.QueueBackgroundWorkItem(ct =>
{
var qsa = HttpUtility.ParseQueryString(qs ?? "");
var pm = new PythonModel(CurrentDatabase);
pm.DictionaryAdd("LogFile", logFile);
foreach (string key in qsa)
{
pm.DictionaryAdd(key, qsa[key]);
}
string result = pm.RunScript(script);
if (result.HasValue())
{
pm.LogToContent(logFile, result);
}
});
return(View("RunPythonScriptProgress"));
}
var pe = new PythonModel(CurrentDatabase);
if (script.Contains("@BlueToolbarTagId"))
{
var id = CurrentDatabase.FetchLastQuery().Id;
pe.DictionaryAdd("BlueToolbarGuid", id.ToCode());
}
foreach (var key in Request.QueryString.AllKeys)
{
pe.DictionaryAdd(key, Request.QueryString[key]);
}
pe.Output = ScriptModel.Run(name, pe);
if (pe.Output.StartsWith("REDIRECT="))
{
var a = pe.Output.SplitStr("=", 2);
return(Redirect(a[1].TrimEnd()));
}
return(View(pe));
#if DEBUG
#else
}
catch (Exception ex)
{
return(RedirectShowError(ex.Message));
}
#endif
}