public void ParseSettings()
{
var list = new Dictionary<int, Settings>();
if (masterorgid.HasValue)
{
foreach (var o in UserSelectClasses(masterorg))
list[o.OrganizationId] = DbUtil.Db.CreateRegistrationSettings(o.OrganizationId);
list[masterorg.OrganizationId] = DbUtil.Db.CreateRegistrationSettings(masterorg.OrganizationId);
}
else if (_orgid == null)
return;
else if (org != null)
list[_orgid.Value] = DbUtil.Db.CreateRegistrationSettings(_orgid.Value);
HttpContext.Current.Items["RegSettings"] = list;
if (org == null || !org.AddToSmallGroupScript.HasValue())
return;
var script = DbUtil.Db.Content(org.AddToSmallGroupScript);
if (script == null || !script.Body.HasValue())
return;
Log("Script:" + org.AddToSmallGroupScript);
try
{
var pe = new PythonModel(Util.Host, "RegisterEvent", script.Body);
HttpContext.Current.Items["PythonEvents"] = pe;
}
catch (Exception ex)
{
Log("PythonError");
org.AddToExtraText("Python.errors", ex.Message);
throw;
}
}
public void RunPyScript()
{
var content = DbUtil.Db.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(Util.Host);
pe.DictionaryAdd("BlueToolbarGuid", Id.ToCode());
foreach (var key in HttpContext.Current.Request.QueryString.AllKeys)
pe.DictionaryAdd(key, HttpContext.Current.Request.QueryString[key]);
pe.RunScript(content);
Results = pe.Output;
}
public static string ExecutePython(string script, PythonModel model, bool fromFile = false)
{
ScriptEngine engine = CreateEngine(fromFile);
using (var ms = new MemoryStream())
using (var sw = new StreamWriter(ms))
{
engine.Runtime.IO.SetOutput(ms, sw);
engine.Runtime.IO.SetErrorOutput(ms, sw);
try
{
var sc = fromFile
? engine.CreateScriptSourceFromFile(script)
: engine.CreateScriptSourceFromString(script);
var code = sc.Compile();
var scope = engine.CreateScope();
scope.SetVariable("model", model);
scope.SetVariable("Data", model.Data);
var qf = new QueryFunctions(model.db, model.dictionary);
scope.SetVariable("q", qf);
code.Execute(scope);
ms.Position = 0;
using (var sr = new StreamReader(ms))
{
var s = sr.ReadToEnd();
return(s.Replace("\r\r\n", "\n"));
}
}
catch (Exception ex)
{
var err = engine.GetService <ExceptionOperations>().FormatException(ex);
throw new Exception(err);
}
}
}
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 = FetchPyScriptForm(pe.Data.pyscript);
return Content(pe.RunScript(script));
}
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 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 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);
}
public static IHandlebars RegisterHelpers(CMSDataContext db, PythonModel pm = null, IHandlebars handlebars = null)
{
handlebars = handlebars ?? Handlebars.Create();
handlebars.RegisterHelper("BottomBorder", (writer, context, args) => { writer.Write(CssStyle.BottomBorder); });
handlebars.RegisterHelper("AlignTop", (writer, context, args) => { writer.Write(CssStyle.AlignTop); });
handlebars.RegisterHelper("AlignRight", (writer, context, args) => { writer.Write(CssStyle.AlignRight); });
handlebars.RegisterHelper("DataLabelStyle", (writer, context, args) => { writer.Write(CssStyle.DataLabelStyle); });
handlebars.RegisterHelper("LabelStyle", (writer, context, args) => { writer.Write(CssStyle.LabelStyle); });
handlebars.RegisterHelper("DataStyle", (writer, context, args) => { writer.Write(CssStyle.DataStyle); });
handlebars.RegisterHelper("ServerLink", (writer, context, args) => { writer.Write(db.ServerLink().TrimEnd('/')); });
handlebars.RegisterHelper("FmtZip", (writer, context, args) => { writer.Write(args[0].ToString().FmtZip()); });
handlebars.RegisterHelper("HtmlComment", (writer, context, args) =>
{
#if DEBUG
writer.Write($"<h6>{args[0].ToString()} {args[1].ToString()}</h6>");
#else
writer.Write($"<!--{args[0].ToString()} {args[1].ToString()}-->");
#endif
});
handlebars.RegisterHelper("IfEqual", (writer, options, context, args) =>
{
if (IsEqual(args))
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("IfNotEqual", (writer, options, context, args) =>
{
if (!IsEqual(args))
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("IfCond", (writer, options, context, args) =>
{
var op = HttpUtility.HtmlDecode(args[1].ToString());
bool b = false;
switch (op)
{
case "==":
b = Compare(args) == 0;
break;
case "!=":
b = Compare(args) != 0;
break;
case "<":
b = Compare(args) < 0;
break;
case ">":
b = Compare(args) > 0;
break;
case ">=":
b = Compare(args) >= 0;
break;
case "<=":
b = Compare(args) <= 0;
break;
case "&&":
b = NumTrue(args) == 2;
break;
case "||":
b = NumTrue(args) >= 1;
break;
}
if (b)
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("IfGT", (writer, options, context, args) =>
{
if (Compare2(args) > 0)
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("IfLT", (writer, options, context, args) =>
{
if (Compare2(args) < 0)
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("IfGE", (writer, options, context, args) =>
{
if (Compare2(args) <= 0)
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("IfLE", (writer, options, context, args) =>
{
if (Compare2(args) <= 0)
{
options.Template(writer, (object)context);
}
else
{
options.Inverse(writer, (object)context);
}
});
handlebars.RegisterHelper("GetToken", (writer, context, args) =>
{
var s = args[0].ToString();
var n = args[1].ToInt();
var ntoks = args.Length > 2 ? args[2].ToInt() : 2;
var sep = args.Length > 3 ? args[3].ToString() : " ";
var a = s.SplitStr(sep, ntoks);
writer.Write(a[n]?.Trim() ?? "");
});
handlebars.RegisterHelper("FmtMDY", (writer, context, args) =>
{
DateTime dt;
var s = args[0].ToString();
if (DateTime.TryParse(s, out dt))
{
writer.Write(dt.ToShortDateString());
}
});
handlebars.RegisterHelper("FmtDate", (writer, context, args) =>
{
DateTime dt;
var s = args[0].ToString();
if (DateTime.TryParse(s, out dt))
{
writer.Write(dt.ToShortDateString());
}
});
handlebars.RegisterHelper("FmtMoney", (writer, context, args) =>
{
decimal d;
var s = args[0].ToString();
if (decimal.TryParse(s, out d))
{
writer.Write(d.ToString("C"));
}
});
// Format helper in form of: {{Fmt value "fmt"}}
// ex. {{Fmt Total "C"}}
// fmt is required. Uses standard/custom dotnet format strings
handlebars.RegisterHelper("Fmt", (writer, context, args) =>
{
var fmt = $"{{0:{args[1]}}}";
writer.Write(fmt, args[0]);
});
// FmtPhone helper in form of: {{FmtPhone phone# "prefix"}}
handlebars.RegisterHelper("FmtPhone", (writer, context, args) => { writer.Write(args[0].ToString().FmtFone($"{args[1]}")); });
handlebars.RegisterHelper("ReplaceCode", (writer, context, args) =>
{
EmailReplacements r = context.Replacements as EmailReplacements
?? (context.Replacements = new EmailReplacements(db));
var code = args[0].ToString();
var p = db.LoadPersonById(args[1].ToInt());
int?oid = null;
if (args.Length == 3)
{
oid = args[2].ToInt2();
}
writer.Write(r.RenderCode(code, p, oid));
});
handlebars.RegisterHelper("Json", (writer, options, context, args) =>
{
dynamic a = JsonDeserialize2(args[0].ToString());
foreach (var item in a)
{
options.Template(writer, item);
}
});
handlebars.RegisterHelper("Calc", (writer, context, args) =>
{
var calcAmt = args[0].ToDouble() - args[1].ToDouble();
var calcAmtfmt = $"{{0:{'c'}}}";
writer.Write(calcAmtfmt, calcAmt);
});
handlebars.RegisterHelper("ThrowError", (writer, context, args) =>
{
throw new Exception("ThrowError called in Handlebars Helper");
});
return(handlebars);
}
public static ExcelWorksheet AddSheet(this ExcelPackage ep, DataTable dt, string filename, bool useTable = false)
{
var sheetname = Path.GetFileNameWithoutExtension(filename);
var ws = ep.Workbook.Worksheets.Add(sheetname);
ws.Cells["A1"].LoadFromDataTable(dt, true);
var count = dt.Rows.Count;
using (var header = ws.Cells[1, 1, 1, dt.Columns.Count])
{
header.Style.Font.Bold = true;
header.Style.Fill.PatternType = ExcelFillStyle.Solid;
header.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(91, 154, 212));
header.Style.Font.Color.SetColor(Color.White);
}
ExcelTable table = null;
if (useTable)
{
var range = ws.Cells[1, 1, count + 1, dt.Columns.Count];
table = ws.Tables.Add(range, sheetname);
table.TableStyle = TableStyles.Light9;
table.ShowFilter = false;
}
for (var i = 0; i < dt.Columns.Count; i++)
{
var col = i + 1;
var name = dt.Columns[i].ColumnName;
var type = dt.Columns[i].DataType;
if (table != null)
{
table.Columns[i].Name = name;
}
var colrange = ws.Cells[1, col, count + 2, col];
if (name.Contains("Info") || name.Contains("Classes") || name == "Questions")
{
colrange.Style.WrapText = true;
ws.Column(col).Width = 40.0;
}
else if (!name.ToLower().EndsWith("id") && type == typeof(int))
{
colrange.Style.Numberformat.Format = "#,##0";
colrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
colrange.AutoFitColumns();
}
else if (type == typeof(decimal))
{
colrange.Style.Numberformat.Format = "#,##0.00";
colrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
colrange.AutoFitColumns();
}
else if ((type == typeof(float) || type == typeof(double)) && PythonModel.StartsEndsWith("Pct", name))
{
colrange.Style.Numberformat.Format = "#####0.0";
colrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
colrange.AutoFitColumns();
}
else if (type == typeof(DateTime))
{
if (name.EndsWith("Time"))
{
colrange.Style.Numberformat.Format = "m/d/yy h:mm AM/PM";
ws.Column(col).Width = 16;
}
else
{
colrange.Style.Numberformat.Format = "m/d/yy";
ws.Column(col).Width = 12;
}
colrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
else
{
colrange.AutoFitColumns();
}
}
return(ws);
}
private string OtherLinkReplacement(string code)
{
return(PythonModel.CreateTinyUrl(code));
}
public bool TryRunPython(int pid)
{
var ev = Organization.GetExtra(DbUtil.Db, OrgId, "OrgMembersPageScript");
if (!ev.HasValue())
return false;
var script = DbUtil.Db.ContentOfTypePythonScript(ev);
if (!script.HasValue())
return false;
var pe = new PythonModel(Util.Host);
pe.Data.OrgId = OrgId;
pe.Data.PeopleId = pid;
Results = pe.RunScript(script);
return true;
}
private static string ExecutePython(string scriptContent, PythonModel model)
{
var engine = Python.CreateEngine();
using (var ms = new MemoryStream())
using (var sw = new StreamWriter(ms))
{
engine.Runtime.IO.SetOutput(ms, sw);
engine.Runtime.IO.SetErrorOutput(ms, sw);
try
{
var sc = engine.CreateScriptSourceFromString(scriptContent);
var code = sc.Compile();
var scope = engine.CreateScope();
scope.SetVariable("model", model);
scope.SetVariable("Data", model.Data);
var qf = new QueryFunctions(model.db, model.dictionary);
scope.SetVariable("q", qf);
code.Execute(scope);
ms.Position = 0;
using (var sr = new StreamReader(ms))
{
var s = sr.ReadToEnd();
return s;
}
}
catch (Exception ex)
{
var err = engine.GetService<ExceptionOperations>().FormatException(ex);
throw new Exception(err);
}
}
}
public static string RunScript(string dbname, string script, DateTime time)
{
var pe = new PythonModel(dbname) {ScheduledTime = time.ToString("HHmm")};
return ExecutePython(script, pe);
}
public ActionResult VoteLinkSg(string id, string message, bool? confirm, FormCollection formCollection)
{
var li = new LinkInfo(votelinkSTR, confirmSTR, id);
if (li.error.HasValue())
return Message(li.error);
try
{
var smallgroup = li.a[4];
if (!li.oid.HasValue)
throw new Exception("orgid missing");
if (!li.pid.HasValue)
throw new Exception("peopleid missing");
var q = (from pp in DbUtil.Db.People
where pp.PeopleId == li.pid
let org = DbUtil.Db.Organizations.SingleOrDefault(oo => oo.OrganizationId == li.oid)
let om = DbUtil.Db.OrganizationMembers.SingleOrDefault(oo => oo.OrganizationId == li.oid && oo.PeopleId == li.pid)
select new {p = pp, org, om}).Single();
if (q.org == null && DbUtil.Db.Host == "trialdb")
{
var oid = li.oid + Util.TrialDbOffset;
q = (from pp in DbUtil.Db.People
where pp.PeopleId == li.pid
let org = DbUtil.Db.Organizations.SingleOrDefault(oo => oo.OrganizationId == oid)
let om = DbUtil.Db.OrganizationMembers.SingleOrDefault(oo => oo.OrganizationId == oid && oo.PeopleId == li.pid)
select new {p = pp, org, om}).Single();
}
if (q.org == null)
{
throw new Exception("org missing, bad link");
}
if ((q.org.RegistrationTypeId ?? RegistrationTypeCode.None) == RegistrationTypeCode.None)
throw new Exception("votelink is no longer active");
if (q.om == null && q.org.Limit <= q.org.RegLimitCount(DbUtil.Db))
throw new Exception("sorry, maximum limit has been reached");
if (q.om == null &&
(q.org.RegistrationClosed == true || q.org.OrganizationStatusId == OrgStatusCode.Inactive))
throw new Exception("sorry, registration has been closed");
var setting = DbUtil.Db.CreateRegistrationSettings(li.oid.Value);
if (IsSmallGroupFilled(setting, li.oid.Value, smallgroup))
throw new Exception("sorry, maximum limit has been reached for " + smallgroup);
var omb = OrganizationMember.Load(DbUtil.Db, li.pid.Value, li.oid.Value) ??
OrganizationMember.InsertOrgMembers(DbUtil.Db,
li.oid.Value, li.pid.Value, MemberTypeCode.Member, DateTime.Now, null, false);
if (q.org.AddToSmallGroupScript.HasValue())
{
var script = DbUtil.Db.Content(q.org.AddToSmallGroupScript);
if (script != null && script.Body.HasValue())
{
try
{
var pe = new PythonModel(Util.Host, "RegisterEvent", script.Body);
pe.instance.AddToSmallGroup(smallgroup, omb);
}
catch (Exception)
{
}
}
}
omb.AddToGroup(DbUtil.Db, smallgroup);
li.ot.Used = true;
DbUtil.Db.SubmitChanges();
DbUtil.LogActivity($"{votelinkSTR}{confirmSTR}: {smallgroup}", li.oid, li.pid);
if (confirm == true)
{
var subject = Util.PickFirst(setting.Subject, "no subject");
var msg = Util.PickFirst(setting.Body, "no message");
msg = APIOrganization.MessageReplacements(DbUtil.Db, q.p, q.org.DivisionName, q.org.OrganizationId, q.org.OrganizationName, q.org.Location, msg);
msg = msg.Replace("{details}", smallgroup);
var NotifyIds = DbUtil.Db.StaffPeopleForOrg(q.org.OrganizationId);
try
{
DbUtil.Db.Email(NotifyIds[0].FromEmail, q.p, subject, msg); // send confirmation
}
catch (Exception ex)
{
DbUtil.Db.Email(q.p.FromEmail, NotifyIds,
q.org.OrganizationName,
"There was a problem sending confirmation from org: " + ex.Message);
}
DbUtil.Db.Email(q.p.FromEmail, NotifyIds,
q.org.OrganizationName,
$"{q.p.Name} has registered for {q.org.OrganizationName}<br>{smallgroup}<br>(from votelink)");
}
}
catch (Exception ex)
{
DbUtil.LogActivity($"{votelinkSTR}{confirmSTR}Error: {ex.Message}", li.oid, li.pid);
return Message(ex.Message);
}
return Message(message);
}
private string SpecialLinkReplacement(string code, SMSItem item)
{
// remove the non url parts and reformat
code = code.Replace("\"", string.Empty);
code = code.Replace("&", "&");
code = HttpUtility.UrlDecode(code);
// parse the special link url to get the component parts
Uri specialLink = new Uri(code);
string type = specialLink.Host;
var querystring = HttpUtility.ParseQueryString(specialLink.Query);
string orgId = querystring.Get("org");
string meetingId = querystring.Get("meeting");
string groupId = querystring.Get("group");
string confirm = querystring.Get("confirm");
string message = querystring.Get("msg");
// result variables
string qs; // the unique link combination for the db
// set some defaults for any missing properties
bool showfamily = false;
if (!message.HasValue())
{
message = "Thank you for responding.";
}
if (!confirm.HasValue())
{
confirm = "false";
}
// generate the one time link code and update any vars based on the link type
switch (type)
{
case "rsvplink":
case "regretslink":
qs = $"{meetingId},{item.PeopleID},{item.Id},{groupId}";
break;
case "registerlink":
case "registerlink2":
showfamily = (type == "registerlink2");
qs = $"{orgId},{item.PeopleID},{item.Id}";
break;
case "sendlink":
case "sendlink2":
showfamily = (type == "sendlink2");
qs = $"{orgId},{item.PeopleID},{item.Id},{(showfamily ? "registerlink2" : "registerlink")}";
break;
case "votelink":
string pre = "";
var a = groupId.SplitStr(":");
if (a.Length > 1)
{
pre = a[0];
}
qs = $"{orgId},{item.PeopleID},{item.Id},{pre},{groupId}";
break;
default:
return(code);
}
var ot = CreateOrFetchOneTimeLink(qs, oneTimeLinkList);
var url = CreateUrlForLink(type, ot, confirm, message, showfamily);
return(PythonModel.CreateTinyUrl(url));
}
public void AddToSmallGroup(CMSDataContext Db, OrganizationMember om, PythonModel pe)
{
if (om == null)
return;
if (pe != null)
pe.instance.AddToSmallGroup(SmallGroup, om);
om.AddToGroup(Db, SmallGroup);
if (MeetingTime.HasValue)
Attend.MarkRegistered(Db, om.OrganizationId, om.PeopleId, MeetingTime.Value, 1);
}
public static string ExecutePythonFilex(string dbname, string file)
{
var model = new PythonModel(dbname);
return(ExecutePython(file, model, fromFile: true));
}
public static string ExecutePythonFile(string dbname, string file)
{
var model = new PythonModel(dbname);
return(ExecutePythonFile(file, model));
}