private FacadeClass Create(JObject fields, NfDbConnection connection, NfDbTransaction transaction)
{
var facade = FacadeObjectFactory.CreateIndividual();
// Copy the JObject fields into the FacadeClass
facade.Merge(fields);
// Try to add them to the database
var error = facade.Insert(connection, transaction);
// Throw on error
if (error != null && error.HasError)
{
throw new ApplicationException(error.Message);
}
// Tell NetForum we are done with this operation so it can do any background tasks
// Neccessity of this varies by Facade Object
facade.ProcessRoundTripEvents(connection, transaction);
// Reload it so all the computed/denormalized fields are present
facade.CurrentKey = facade.GetValue("cst_key");
facade.SelectByKey(connection, transaction);
return(facade);
}
public IHttpActionResult Put(Guid id, [FromBody] JObject fields)
{
JObject result;
using (var connection = DataUtils.GetConnection())
using (var transaction = connection.BeginTransaction())
using (var facade = FacadeObjectFactory.CreateIndividual())
{
facade.CurrentKey = id.ToString();
facade.SelectByKey(connection, transaction);
facade.Merge(fields);
facade.Update(connection, transaction);
facade.LoadRelatedData(connection, transaction);
facade.ProcessRoundTripEvents(connection, transaction);
result = GetFields(facade);
transaction.Commit();
}
return(Json(result));
}
// GET api/<controller>
public IEnumerable <Hashtable> Get()
{
// Make sure the user is logged in
if (CustomerKey == null || Individual == null || string.IsNullOrEmpty(Individual.CurrentKey))
{
throw new HttpResponseException(HttpStatusCode.Unauthorized);
}
// This could be passed in via a query string (compared against a whitelist), or derived from the object model
var columns = new string[] { "cst_recno", "ind_first_name", "ind_last_name", "ind_badge_name", "eml_address", "adr_city", "adr_line1", "adr_post_code", "adr_state" };
// Using a real data model is preferable
var result = new List <Hashtable>();
using (FacadeClass facade = FacadeObjectFactory.CreateIndividual()) // Re-use the facade to reduce memory usage
using (var connection = DataUtils.GetConnection())
using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
// In a real-world application, you wouldn't dump your whole database
// Ideally a high-level filter would be applied (i.e. adr_state in this case), with pagination
// Any more specific filtering (city, zip, radius) could be done client-side
// Adding a caching mechanism would increase performance
using (var cmd = new NfDbCommand(@"
SELECT
ind_cst_key
FROM
co_customer (NOLOCK)
JOIN co_individual (NOLOCK) ON ind_cst_key = cst_key AND ind_delete_flag = 0
WHERE
cst_delete_flag = 0
", connection, transaction))
using (var reader = cmd.ExecuteReader())
{
while (reader.HasRows && reader.Read())
{
facade.CurrentKey = ((Guid)reader.GetValue(0)).ToString();
facade.SelectByKey(connection, transaction);
// Using a real data model is preferable
var record = new Hashtable();
foreach (var col in columns)
{
// Using GetValueNative so booleans and numeric data types come over to JSON as their proper data types
record[col] = facade.GetValueNative(col);
}
result.Add(record);
// Clear facade values between loop iterations
facade.ClearValues();
}
}
return(result);
}
// GET: api/mb_membership/5
public IHttpActionResult Get(Guid id)
{
using (var connection = DataUtils.GetConnection())
using (var transaction = connection.BeginTransaction())
using (var facade = FacadeObjectFactory.CreateMembership())
{
facade.CurrentKey = id.ToString();
facade.SelectByKey(connection, transaction);
var fields = GetFields(facade);
return(Json(fields));
}
}
public IHttpActionResult Get()
{
var currentUserName = Config.CurrentUserName;
var userOptions = Config.UserOptions;
var currentUserKey = Config.CurrentUserKey;
var session = Config.Session;
var identity = (ClaimsIdentity)User.Identity;
var usr_code = identity.Claims.FirstOrDefault(claim => claim.Type == "usr_code")?.Value;
if (string.IsNullOrEmpty(usr_code))
{
return(BadRequest("Cannot find usr_code"));
}
var results = new JArray();
using (var connection = DataUtils.GetConnection())
using (var transaction = connection.BeginTransaction())
using (var cmd = new NfDbCommand("SELECT usr_cst_key FROM fw_user WHERE usr_code = @usr_code AND usr_delete_flag = 0", connection, transaction))
{
cmd.Parameters.AddWithValue("@usr_code", usr_code);
var result = cmd.ExecuteScalar();
if (result is Guid cst_key)
{
using (var facade = FacadeObjectFactory.CreateIndividual())
{
facade.CurrentKey = cst_key.ToString();
facade.SelectByKey(connection, transaction);
// Suggest using a facade-to-model mapping solution here instead
var fields = GetFields(facade);
results.Add(fields);
}
}
}
return(Json(results));
}
private FacadeClass Existing(string eml_address, NfDbConnection connection, NfDbTransaction transaction)
{
// Quick and dirty duplicate check by email address
using (var cmd = new NfDbCommand("SELECT cst_key FROM co_customer WHERE cst_delete_flag = 0 AND cst_type = 'Individual' AND cst_eml_address_dn = @eml_address", connection, transaction))
{
cmd.Parameters.AddWithValue("@eml_address", eml_address);
var result = cmd.ExecuteScalar();
// If a match was found
if (result is Guid cst_key)
{
// Instanciate a facade and pull down their record
var facade = FacadeObjectFactory.CreateIndividual();
facade.CurrentKey = cst_key.ToString();
facade.SelectByKey(connection, transaction);
return(facade);
}
}
return(null);
}
// GET: api/mb_membership
/// <summary>
/// Get a list of memberships, filtered by a where condition
/// </summary>
/// <param name="where">JSON encoded list of fields to search the database for</param>
/// <remarks>
/// ?where={ "field1": "value", "field2": { "eq": "value }, "field3": { "gt": 5 }, "field4": "IS NULL" }
/// </remarks>
/// <returns>List of memberships</returns>
public IHttpActionResult Get([FromUri] string where)
{
JArray results = new JArray();
JObject whereFields = null;
Dictionary <string, object> searchFields = new Dictionary <string, object>();
try
{
whereFields = JObject.Parse(where);
}
catch (Exception)
{
}
if (whereFields != null)
{
using (var facade = FacadeObjectFactory.CreateMembership())
{
// Quick and dirty field validation
var verify = new Regex(@"^[a-z0-9_]+$");
foreach (var pair in whereFields)
{
if (!verify.IsMatch(pair.Key))
{
continue;
}
if (pair.Value == null)
{
continue;
}
var field = facade.GetField(pair.Key);
var parts = pair.Key.Split('_');
if (field == null)
{
continue;
}
if (parts[0] != "mbr" && parts[0] != "mbt")
{
continue;
}
searchFields.Add(pair.Key, pair.Value);
}
}
}
// Add top just for sanity reasons
var SQL = @"SELECT TOP 100 mbr_key FROM mb_membership JOIN mb_member_type ON mbt_key = mbr_mbt_key AND mbt_delete_flag = 0 WHERE mbr_delete_flag = 0";
using (var connection = DataUtils.GetConnection())
using (var transaction = connection.BeginTransaction())
using (var cmd = new NfDbCommand(SQL, connection, transaction))
{
foreach (var field in searchFields.Keys)
{
cmd.CommandText += Environment.NewLine + $" AND [{field}]";
var search = searchFields[field];
if (search is JObject searchOperation)
{
if (searchOperation.Count != 1)
{
continue;
}
var op = (JProperty)searchOperation.First;
switch (op.Name)
{
case "eq":
SearchUtils.JTokenParse(cmd, op.Value, field, "=");
break;
case "ne":
SearchUtils.JTokenParse(cmd, op.Value, field, "!=");
break;
case "gt":
SearchUtils.JTokenParse(cmd, op.Value, field, ">");
break;
case "ge":
SearchUtils.JTokenParse(cmd, op.Value, field, ">=");
break;
case "lt":
SearchUtils.JTokenParse(cmd, op.Value, field, "<");
break;
case "le":
SearchUtils.JTokenParse(cmd, op.Value, field, "<=");
break;
}
}
else if (search is JToken searchValue)
{
SearchUtils.JTokenParse(cmd, searchValue, field, "=");
}
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Guid mbr_key = reader.GetGuid(0);
using (var facade = FacadeObjectFactory.CreateMembership())
{
facade.CurrentKey = mbr_key.ToString();
facade.SelectByKey(connection, transaction);
// Suggest using a facade-to-model mapping solution here instead
var fields = GetFields(facade);
results.Add(fields);
}
}
}
}
return(Json(results));
}
