public override bool ParameterizeControl(ParameterizeCtrl_Params args, string crudContext)
{
string Qry = string.Empty;
List <Ques_Confi> Ques = JsonConvert.DeserializeObject <List <Ques_Confi> >(Convert.ToString(args.cField.Value));
int Ctrli = args.i++;
args.param.Add(args.DataDB.GetNewParameter("ctrlid_" + Ctrli, EbDbTypes.String, this.Name));
if (args.ins)
{
foreach (Ques_Confi Que in Ques)
{
Qry += this.GetSaveQuery(args, Que, Ctrli, 1); //insert
}
}
else
{
List <Ques_Confi> QuesOld = JsonConvert.DeserializeObject <List <Ques_Confi> >(Convert.ToString(args.ocF.Value));
foreach (Ques_Confi Que in Ques)
{
if (Que.id > 0)
{
if (QuesOld.RemoveAll(e => e.id == Que.id) > 0)
{
Qry += this.GetSaveQuery(args, Que, Ctrli, 2); //update
}
else
{
Qry += this.GetSaveQuery(args, Que, Ctrli, 1); //insert
}
}
else
{
Qry += this.GetSaveQuery(args, Que, Ctrli, 1); //insert
}
}
foreach (Ques_Confi QueOld in QuesOld)
{
Qry += this.GetSaveQuery(args, QueOld, Ctrli, 3); //delete
}
}
args._extqry += Qry;
return(true);
}
//for system/mobile notification
public DbParameter[] GetParameters(EbWebForm _this, IDatabase DataDB, Dictionary <string, string> QryParams)
{
List <DbParameter> _p = new List <DbParameter>();
int _idx = 0;
foreach (KeyValuePair <string, string> p in QryParams)
{
SingleTable Table = null;
if (_this.FormData.MultipleTables.ContainsKey(p.Value))
{
Table = _this.FormData.MultipleTables[p.Value];
}
else if (_this.FormDataBackup?.MultipleTables.ContainsKey(p.Value) == true)
{
Table = _this.FormDataBackup.MultipleTables[p.Value];
}
else
{
throw new FormException("Bad Request", (int)HttpStatusCode.BadRequest, $"SendNotifications: Notify by UserId parameter {p.Key} is not idetified", $"{p.Value} not found in MultipleTables");
}
TableSchema _table = _this.FormSchema.Tables.Find(e => e.TableName == p.Value);
if (_table.TableType != WebFormTableTypes.Normal)
{
throw new FormException("Bad Request", (int)HttpStatusCode.BadRequest, $"SendNotifications: Notify by UserId parameter {p.Key} is not idetified", $"{p.Value} found in MultipleTables but it is not a normal table");
}
if (Table.Count != 1)
{
throw new FormException("Bad Request", (int)HttpStatusCode.BadRequest, $"SendNotifications: Notify by UserId parameter {p.Key} is not idetified", $"{p.Value} found in MultipleTables but table is empty");
}
SingleColumn Column = Table[0].Columns.Find(e => e.Control?.Name == p.Key);
if (Column?.Control == null)
{
throw new FormException("Bad Request", (int)HttpStatusCode.BadRequest, $"SendNotifications: Notify by UserId parameter {p.Key} is not idetified", $"{p.Value} found in MultipleTables but data not available");
}
ParameterizeCtrl_Params args = new ParameterizeCtrl_Params(DataDB, _p, Column, _idx, _this.UserObj);
Column.Control.ParameterizeControl(args, _this.CrudContext);
_idx = args.i;
_p[_idx - 1].ParameterName = p.Key;
}
return(_p.ToArray());
}
private string GetSaveQuery(ParameterizeCtrl_Params args, Ques_Confi Que, int ctrli, int op)
{
string Qry = string.Empty;
if (op == 1) //insert
{
Qry += string.Format("INSERT INTO eb_ques_config (ques_id, ext_props, form_refid, form_data_id, control_id, eb_created_by, eb_created_at) " +
"VALUES (@ques_id_{0}, @ext_props_{0}, @{1}_refid, {2}, @ctrlid_{3}, @eb_createdby, {4});",
args.i,
args.tbl,
args.ins ? $"eb_currval('{args.tbl}_id_seq')" : $"@{args.tbl}_id",
ctrli,
args.DataDB.EB_CURRENT_TIMESTAMP);
args.param.Add(args.DataDB.GetNewParameter("ques_id_" + args.i, EbDbTypes.Int32, Que.ques_id));
args.param.Add(args.DataDB.GetNewParameter("ext_props_" + args.i, EbDbTypes.String, JsonConvert.SerializeObject(Que.ext_props)));
}
else if (op == 2) //update
{
Qry += string.Format("UPDATE eb_ques_config SET ques_id = @ques_id_{0}, ext_props = @ext_props_{0}, control_id = @ctrlid_{1}, " +
"eb_lastmodified_by = @eb_modified_by, eb_lastmodified_at = {2} WHERE id = @id_{0};",
args.i,
ctrli,
args.DataDB.EB_CURRENT_TIMESTAMP);
args.param.Add(args.DataDB.GetNewParameter("id_" + args.i, EbDbTypes.Int32, Que.id));
args.param.Add(args.DataDB.GetNewParameter("ques_id_" + args.i, EbDbTypes.Int32, Que.ques_id));
args.param.Add(args.DataDB.GetNewParameter("ext_props_" + args.i, EbDbTypes.String, JsonConvert.SerializeObject(Que.ext_props)));
}
else if (op == 3) //delete
{
Qry += string.Format("UPDATE eb_ques_config SET eb_del = 'T', eb_lastmodified_by = @eb_modified_by, eb_lastmodified_at = {1} WHERE id = @id_{0};",
args.i,
args.DataDB.EB_CURRENT_TIMESTAMP);
args.param.Add(args.DataDB.GetNewParameter("id_" + args.i, EbDbTypes.Int32, Que.id));
}
args.i++;
return(Qry);
}
public override bool ParameterizeControl(ParameterizeCtrl_Params args, string crudContext)
{
Dictionary <string, string> _d = JsonConvert.DeserializeObject <Dictionary <string, string> >(Convert.ToString(args.cField.F));
Dictionary <string, string> _od = args.ocF == null ? null : JsonConvert.DeserializeObject <Dictionary <string, string> >(Convert.ToString(args.ocF.F));
string paramName = args.cField.Name + crudContext;
if (string.IsNullOrEmpty(_d[FormConstants.role_name]))
{
return(false);
}
string selQry = "SELECT id FROM eb_roles WHERE LOWER(role_name) LIKE LOWER(@role_name) AND COALESCE(eb_del, 'F') = 'F';";
EbDataTable dt = args.DataDB.DoQuery(selQry, new DbParameter[] { args.DataDB.GetNewParameter(FormConstants.role_name, EbDbTypes.String, _d[FormConstants.role_name]) });
int nProvRolId = 0;
if (dt.Rows.Count > 0)
{
nProvRolId = Convert.ToInt32(dt.Rows[0][0]);
}
string temp = string.Empty;
bool InsertEbRole = false;
if (args.ins)
{
if (nProvRolId > 0)
{
if (!this.AllowExistingRole)
{
throw new FormException(_d[FormConstants.role_name] + " is not unique.", (int)HttpStatusCode.BadRequest, "Given role_name is already exists in eb_roles", "EbProvisionRole -> ParameterizeControl");
}
else
{
args._cols += args.cField.Name + CharConstants.COMMA + CharConstants.SPACE;
args._vals += CharConstants.AT + paramName + CharConstants.COMMA + CharConstants.SPACE;
args.param.Add(args.DataDB.GetNewParameter(paramName, (EbDbTypes)args.cField.Type, nProvRolId));
}
}
else
{
InsertEbRole = true;
temp = $"eb_currval('{args.tbl}_id_seq')";
}
}
else
{
int oProvRolId = args.ocF == null ? 0 : Convert.ToInt32(args.ocF.Value);
if (_od != null && _od.ContainsKey(FormConstants.id) && Convert.ToInt32(_od[FormConstants.id]) == oProvRolId)// role created by this ctrl
{
temp = $"UPDATE eb_roles SET role_name = @role_name_{args.i}, applicationid = @applicationid_{args.i}, description = @description_{args.i}, is_primary = @is_primary_{args.i} WHERE id=@{paramName}; ";
args.param.Add(args.DataDB.GetNewParameter(paramName, (EbDbTypes)args.cField.Type, oProvRolId));
}
else if (nProvRolId > 0)
{
if (!this.AllowExistingRole)
{
throw new FormException(_d[FormConstants.role_name] + " is not unique.", (int)HttpStatusCode.BadRequest, "Given role_name is already exists in eb_roles", "EbProvisionRole -> ParameterizeControl");
}
else
{
args._colvals += args.cField.Name + CharConstants.EQUALS + CharConstants.AT + paramName + CharConstants.COMMA + CharConstants.SPACE;
args.param.Add(args.DataDB.GetNewParameter(paramName, (EbDbTypes)args.cField.Type, nProvRolId));
}
}
else
{
InsertEbRole = true;
temp = $"@{args.tbl}_id";
}
}
if (InsertEbRole)
{
temp = $"INSERT INTO eb_roles(role_name, applicationid, description,is_primary, eb_ver_id, eb_data_id, eb_del) " +
$"VALUES(@role_name_{args.i}, @applicationid_{args.i}, @description_{args.i}, @is_primary_{args.i}, @{args.tbl}_eb_ver_id, {temp}, 'F'); " +
$"UPDATE {this.TableName} SET {this.Name} = {"eb_currval('eb_roles_id_seq')"} WHERE {(this.TableName == args.tbl ? "id" : (args.tbl + "_id"))} = {temp};";
}
args.param.Add(args.DataDB.GetNewParameter("role_name_" + args.i, EbDbTypes.String, _d[FormConstants.role_name]));
AddParam(args.DataDB, args.param, args.i, EbDbTypes.Int32, _d, _od, FormConstants.applicationid, 0);
AddParam(args.DataDB, args.param, args.i, EbDbTypes.String, _d, _od, FormConstants.description, string.Empty);
AddParam(args.DataDB, args.param, args.i, EbDbTypes.String, _d, _od, FormConstants.is_primary, "F");
args._extqry += temp;
args.i++;
return(true);
}
public override bool ParameterizeControl(ParameterizeCtrl_Params args, string crudContext)
{
return(this.ParameterizeControl(args, false, crudContext));
}
public bool ParameterizeControl(ParameterizeCtrl_Params args, bool randomize, string crudContext)
{
string paramName = randomize ? (args.cField.Name + "_" + args.i) : (args.cField.Name + crudContext);
try
{
if (string.IsNullOrWhiteSpace(Convert.ToString(args.cField.Value)) && this.IsNullable)
{
DbParameter p = args.DataDB.GetNewParameter(paramName, (EbDbTypes)args.cField.Type);
p.Value = DBNull.Value;
args.param.Add(p);
}
else
{
string strDate = Convert.ToString(args.cField.Value);
if (string.IsNullOrWhiteSpace(strDate))
{
throw new FormException($"Unable to process [Date: {this.Label ?? this.Name}]", (int)HttpStatusCode.InternalServerError, "Null or empty string for Date: " + args.cField.Name, "EbDate => ParameterizeControl");
}
if (this.EbDateType == EbDateType.Date)
{
if (this.ShowDateAs_ == DateShowFormat.Year)
{
args.cField.Value = DateTime.ParseExact(strDate, "yyyy", CultureInfo.InvariantCulture);
}
else if (this.ShowDateAs_ == DateShowFormat.Year_Month)
{
args.cField.Value = DateTime.ParseExact(strDate, "MM/yyyy", CultureInfo.InvariantCulture);
}
else
{
args.cField.Value = DateTime.ParseExact(strDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
}
if (this.RestrictionRule == DateRestrictionRule.FinancialYear || this.RestrictionRule == DateRestrictionRule.ActivePeriod)
{
EbFinancialYears fys = (args.webForm as EbWebForm)?.SolutionObj?.FinancialYears;
if (fys != null && fys.List.Count > 0)
{
DateTime Date = Convert.ToDateTime(args.cField.Value);
bool DateIsOk = false, IsSysUser = args.usr.RoleIds.Exists(e => e < 100);
string finStartEnd = null, shtDtPtn = args.usr.Preference.GetShortDatePattern();
foreach (EbFinancialYear fy in fys.List)
{
if (finStartEnd == null && !fy.Locked)
{
finStartEnd = GetFyDate(this.RestrictionRule, fy, true).ToString(shtDtPtn, CultureInfo.InvariantCulture) + " and " + GetFyDate(this.RestrictionRule, fy, false).ToString(shtDtPtn, CultureInfo.InvariantCulture);
}
if ((!fy.Locked || IsSysUser) && GetFyDate(this.RestrictionRule, fy, true) <= Date && GetFyDate(this.RestrictionRule, fy, false) >= Date)
{
DateIsOk = true;
break;
}
}
if (!DateIsOk)
{
throw new FormException($"{this.Label ?? this.Name} must be between {finStartEnd ?? "Financial years"}", (int)HttpStatusCode.BadRequest, $"Financial year check failed. Ctrl Name: {this.Name}", "EbDate -> ParameterizeControl");
}
}
}
}
else
{
if (this.EbDateType == EbDateType.DateTime)
{
DateTime dt = DateTime.ParseExact(strDate, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
args.cField.Value = dt.ConvertToUtc(args.usr.Preference.TimeZone);
}
else//EbDateType.Time
{
args.cField.Value = DateTime.ParseExact(strDate, "HH:mm:ss", CultureInfo.InvariantCulture);
}
}
args.param.Add(args.DataDB.GetNewParameter(paramName, EbDbTypes.DateTime, args.cField.Value));
}
}
catch (Exception e)
{
Console.WriteLine($"Found unexpected value for EbDate control field...\nName : {args.cField.Name}\nValue : {args.cField.Value}\nMessage : {e.Message}");;
throw new FormException($"Unable to process {this.Name}(date control) with value: {args.cField.Value}", (int)HttpStatusCode.InternalServerError, e.Message, "From EbDate.ParameterizeControl()\n" + e.StackTrace);
}
if (args.ins)
{
args._cols += string.Concat(args.cField.Name, ", ");
args._vals += string.Concat("@", paramName, ", ");
}
else
{
args._colvals += string.Concat(args.cField.Name, "=@", paramName, ", ");
}
args.i++;
return(true);
}
public override bool ParameterizeControl(ParameterizeCtrl_Params args, string crudContext)
{
Dictionary <string, string> _d = JsonConvert.DeserializeObject <Dictionary <string, string> >(Convert.ToString(args.cField.F));
Dictionary <string, string> _od = args.ocF == null ? null : JsonConvert.DeserializeObject <Dictionary <string, string> >(Convert.ToString(args.ocF.F));
if (string.IsNullOrEmpty(_d[FormConstants.longname]))
{
return(false);
}
string selQry = "SELECT id FROM eb_locations WHERE LOWER(longname) LIKE LOWER(@longname) AND COALESCE(eb_del, 'F') = 'F';";
EbDataTable dt = args.DataDB.DoQuery(selQry, new DbParameter[] { args.DataDB.GetNewParameter(FormConstants.longname, EbDbTypes.String, _d[FormConstants.longname]) });
int nProvLocId = 0;
if (dt.Rows.Count > 0)
{
nProvLocId = Convert.ToInt32(dt.Rows[0][0]);
}
string temp;
if (args.ins)
{
if (nProvLocId > 0)
{
throw new FormException(_d[FormConstants.longname] + " is not unique.", (int)HttpStatusCode.BadRequest, "Given longname is already exists in eb_locations", "EbProvisionLocation -> ParameterizeControl");
}
temp = $@"INSERT INTO eb_locations(longname, shortname, image, meta_json, is_group, parent_id, eb_location_types_id, eb_ver_id, eb_data_id, eb_created_by, eb_created_at, eb_lastmodified_by, eb_lastmodified_at, eb_del)
VALUES(@longname_{args.i}, @shortname_{args.i}, @image_{args.i}, @meta_json_{args.i}, @is_group_{args.i}, @parent_id_{args.i}, @eb_location_types_id_{args.i}, @{args.tbl}_eb_ver_id, eb_currval('{args.tbl}_id_seq'), @{FormConstants.eb_createdby}, {args.DataDB.EB_CURRENT_TIMESTAMP}, @{FormConstants.eb_createdby}, {args.DataDB.EB_CURRENT_TIMESTAMP}, 'F');";
if (args.DataDB.Vendor == DatabaseVendors.MYSQL)
{
temp += "SELECT eb_persist_currval('eb_locations_id_seq');";
}
temp += $"UPDATE {this.TableName} SET {this.Name} = eb_currval('eb_locations_id_seq') WHERE {(this.TableName == args.tbl ? "id" : (args.tbl + "_id"))} = eb_currval('{args.tbl}_id_seq'); ";
this.IsLocationCreated = true;
}
else
{
int oProvLocId = args.ocF == null ? 0 : Convert.ToInt32(args.ocF.Value);
if (nProvLocId > 0 && nProvLocId != oProvLocId)
{
throw new FormException(_d[FormConstants.longname] + " is not unique.", (int)HttpStatusCode.BadRequest, "Given longname is already exists in eb_locations", "EbProvisionLocation -> ParameterizeControl");
}
temp = $@"UPDATE eb_locations SET longname = @longname_{args.i}, shortname = @shortname_{args.i}, image = @image_{args.i}, meta_json = @meta_json_{args.i},
is_group = @is_group_{args.i}, parent_id = @parent_id_{args.i}, eb_location_types_id = @eb_location_types_id_{args.i}, eb_lastmodified_by = @{FormConstants.eb_modified_by}, eb_lastmodified_at = {args.DataDB.EB_CURRENT_TIMESTAMP}
WHERE eb_ver_id = :{args.tbl}_eb_ver_id AND eb_data_id = :{args.tbl}_id AND COALESCE(eb_del, 'F') = 'F';";
}
args.param.Add(args.DataDB.GetNewParameter("longname_" + args.i, EbDbTypes.String, _d[FormConstants.longname]));
AddParam(args.DataDB, args.param, args.i, EbDbTypes.String, _d, _od, FormConstants.shortname, _d[FormConstants.longname]);
AddParam(args.DataDB, args.param, args.i, EbDbTypes.String, _d, _od, FormConstants.image, "../img");
AddParam(args.DataDB, args.param, args.i, EbDbTypes.String, _d, _od, FormConstants.meta_json, "{}");
AddParam(args.DataDB, args.param, args.i, EbDbTypes.String, _d, _od, FormConstants.is_group, "T");
AddParam(args.DataDB, args.param, args.i, EbDbTypes.Decimal, _d, _od, FormConstants.parent_id, 0);
AddParam(args.DataDB, args.param, args.i, EbDbTypes.Int32, _d, _od, FormConstants.eb_location_types_id, 1);
args._extqry = temp + args._extqry; //location must be created before user creation
args.i++;
return(true);
}
public void Update(IDatabase DataDB, List <DbParameter> param, ref string fullqry, ref string _extqry, ref int i)
{
ParameterizeCtrl_Params args = new ParameterizeCtrl_Params(DataDB, param, i, _extqry);
foreach (EbWebForm WebForm in this)
{
args.SetFormRelated(WebForm.TableName, WebForm.UserObj, WebForm);
WebForm.DoRequiredCheck(WebForm == MasterForm);
foreach (TableSchema _table in WebForm.FormSchema.Tables.FindAll(e => e.TableType != WebFormTableTypes.Review && !e.DoNotPersist))
{
if (!WebForm.FormData.MultipleTables.ContainsKey(_table.TableName))
{
continue;
}
foreach (SingleRow row in WebForm.FormData.MultipleTables[_table.TableName])
{
args.ResetColVals();
if (row.RowId > 0)
{
SingleRow bkup_Row = WebForm.FormDataBackup.MultipleTables[_table.TableName].Find(e => e.RowId == row.RowId);
if (bkup_Row == null)
{
Console.WriteLine($"Row edit request ignored(Row not in backup table). \nTable name: {_table.TableName}, RowId: {row.RowId}, RefId: {WebForm.RefId}");
continue;
}
string t = string.Empty;
if (!row.IsDelete)
{
foreach (SingleColumn cField in row.Columns)
{
if (cField.Control != null)
{
SingleColumn ocF = bkup_Row.Columns.Find(e => e.Name.Equals(cField.Name));
args.UpdateSet(cField, ocF);
cField.Control.ParameterizeControl(args, WebForm.CrudContext);
}
else
{
args.UpdateSet(cField);
WebForm.ParameterizeUnknown(args);
}
}
}
else if (WebForm.DataPusherConfig == null && !_table.TableName.Equals(WebForm.TableName))
{
List <TableSchema> _tables = WebForm.FormSchema.Tables.FindAll(e => e.IsDynamic && e.TableType == WebFormTableTypes.Grid);
foreach (TableSchema _tbl in _tables)
{
t += $@"UPDATE {_tbl.TableName} SET eb_del = 'T', eb_lastmodified_by = @eb_modified_by, eb_lastmodified_at = {DataDB.EB_CURRENT_TIMESTAMP} WHERE
{_table.TableName}_id = @{_table.TableName}_id_{args.i} AND {WebForm.TableName}_id = @{WebForm.TableName}_id AND COALESCE(eb_del, 'F') = 'F'; ";
param.Add(DataDB.GetNewParameter(_table.TableName + "_id_" + args.i, EbDbTypes.Int32, row.RowId));
args.i++;
}
}
string _qry = QueryGetter.GetUpdateQuery(WebForm, DataDB, _table.TableName, row.IsDelete);
fullqry += string.Format(_qry, args._colvals, row.RowId);
fullqry += t;
}
else
{
args.ResetColsAndVals();
foreach (SingleColumn cField in row.Columns)
{
args.InsertSet(cField);
if (cField.Control != null)
{
cField.Control.ParameterizeControl(args, WebForm.CrudContext);
}
else
{
WebForm.ParameterizeUnknown(args);
}
}
string _qry = QueryGetter.GetInsertQuery(WebForm, DataDB, _table.TableName, WebForm.TableRowId == 0);
fullqry += string.Format(_qry, args._cols, args._vals);
}
fullqry += WebForm.InsertUpdateLines(_table.TableName, row, args);
}
}
string IdParamName = WebForm.TableName + FormConstants._id + (WebForm.DataPusherConfig != null ? WebForm.CrudContext : string.Empty);
param.Add(DataDB.GetNewParameter(IdParamName, EbDbTypes.Int32, WebForm.TableRowId));
if (!param.Exists(e => e.ParameterName == WebForm.TableName + FormConstants._eb_ver_id))
{
param.Add(DataDB.GetNewParameter(WebForm.TableName + FormConstants._eb_ver_id, EbDbTypes.Int32, WebForm.RefId.Split(CharConstants.DASH)[4]));
param.Add(DataDB.GetNewParameter(WebForm.TableName + FormConstants._refid, EbDbTypes.String, WebForm.RefId));
}
}
args.CopyBack(ref _extqry, ref i);
}
public void Insert(IDatabase DataDB, List <DbParameter> param, ref string fullqry, ref string _extqry, ref int i, bool pushAuditTrail)
{
ParameterizeCtrl_Params args = new ParameterizeCtrl_Params(DataDB, param, i, _extqry);
foreach (EbWebForm WebForm in this)
{
args.SetFormRelated(WebForm.TableName, WebForm.UserObj, WebForm);
if (WebForm.DataPusherConfig?.AllowPush == false)
{
continue;
}
WebForm.DoRequiredCheck(WebForm == MasterForm);
if (!(WebForm.FormData.MultipleTables.ContainsKey(WebForm.FormSchema.MasterTable) && WebForm.FormData.MultipleTables[WebForm.FormSchema.MasterTable].Count > 0))
{
string _q = QueryGetter.GetInsertQuery(WebForm, DataDB, WebForm.FormSchema.MasterTable, true);
fullqry += string.Format(_q, string.Empty, string.Empty);
}
foreach (TableSchema _table in WebForm.FormSchema.Tables.FindAll(e => e.TableType != WebFormTableTypes.Review && !e.DoNotPersist))
{
if (!WebForm.FormData.MultipleTables.ContainsKey(_table.TableName))
{
continue;
}
foreach (SingleRow row in WebForm.FormData.MultipleTables[_table.TableName])
{
args.ResetColsAndVals();
foreach (SingleColumn cField in row.Columns)
{
args.InsertSet(cField);
if (cField.Control != null)
{
cField.Control.ParameterizeControl(args, WebForm.CrudContext);
}
else
{
WebForm.ParameterizeUnknown(args);
}
}
string _qry = QueryGetter.GetInsertQuery(WebForm, DataDB, _table.TableName, true);
fullqry += string.Format(_qry, args._cols, args._vals);
fullqry += WebForm.InsertUpdateLines(_table.TableName, row, args);
}
}
if (!param.Exists(e => e.ParameterName == WebForm.TableName + FormConstants._eb_ver_id))
{
param.Add(DataDB.GetNewParameter(WebForm.TableName + FormConstants._eb_ver_id, EbDbTypes.Int32, WebForm.RefId.Split(CharConstants.DASH)[4]));
param.Add(DataDB.GetNewParameter(WebForm.TableName + FormConstants._refid, EbDbTypes.String, WebForm.RefId));
}
if (pushAuditTrail)
{
fullqry += EbAuditTrail.GetInsertModeQuery(DataDB, WebForm.RefId, WebForm.TableName);
}
}
args.CopyBack(ref _extqry, ref i);
}
public void Update_Batch(IDatabase DataDB, List <DbParameter> param, ref string fullqry, ref string _extqry, ref int i)
{
ParameterizeCtrl_Params args = new ParameterizeCtrl_Params(DataDB, param, i, _extqry);
foreach (EbWebForm WebForm in this)
{
args.SetFormRelated(WebForm.TableName, WebForm.UserObj, WebForm);
EbDataPusherConfig conf = WebForm.DataPusherConfig;
foreach (KeyValuePair <string, SingleTable> entry in WebForm.FormData.MultipleTables)
{
foreach (SingleRow row in entry.Value)
{
args.ResetColVals();
if (row.RowId > 0)
{
//SingleRow bkup_Row = WebForm.FormDataBackup.MultipleTables[entry.Key].Find(e => e.RowId == row.RowId);
//if (bkup_Row == null)
//{
// Console.WriteLine($"Row edit request ignored(Row not in backup table). \nTable name: {entry.Key}, RowId: {row.RowId}, RefId: {WebForm.RefId}");
// continue;
//}
string t = string.Empty;
if (!row.IsDelete)
{
foreach (SingleColumn cField in row.Columns)
{
if (cField.Control != null)
{
SingleColumn ocF = null;// bkup_Row.Columns.Find(e => e.Name.Equals(cField.Name));
args.UpdateSet(cField, ocF);
cField.Control.ParameterizeControl(args, WebForm.CrudContext);
}
else
{
args.UpdateSet(cField);
WebForm.ParameterizeUnknown(args);
}
}
}
string _qry = QueryGetter.GetUpdateQuery_Batch(WebForm, DataDB, entry.Key, row.IsDelete, row.RowId);
fullqry += string.Format(_qry, args._colvals);
fullqry += t;
}
else
{
args.ResetColsAndVals();
foreach (SingleColumn cField in row.Columns)
{
args.InsertSet(cField);
if (cField.Control != null)
{
cField.Control.ParameterizeControl(args, WebForm.CrudContext);
}
else
{
WebForm.ParameterizeUnknown(args);
}
}
string _qry = QueryGetter.GetInsertQuery_Batch(WebForm, DataDB, entry.Key);
fullqry += string.Format(_qry, args._cols, args._vals);
}
fullqry += WebForm.InsertUpdateLines(entry.Key, row, args);
}
}
if (!param.Exists(e => e.ParameterName == WebForm.TableName + FormConstants._eb_ver_id))
{
param.Add(DataDB.GetNewParameter(WebForm.TableName + FormConstants._eb_ver_id, EbDbTypes.Int32, WebForm.RefId.Split(CharConstants.DASH)[4]));
param.Add(DataDB.GetNewParameter(WebForm.TableName + FormConstants._refid, EbDbTypes.String, WebForm.RefId));
}
}
args.CopyBack(ref _extqry, ref i);
}
public override bool ParameterizeControl(ParameterizeCtrl_Params args, string crudContext)
{
if (!this.CreateOnlyIf_b)
{
return(false);
}
string c = string.Empty;
bool doNotUpdate = false;
bool insertOnUpdate = false;
Dictionary <string, string> _d = JsonConvert.DeserializeObject <Dictionary <string, string> >(Convert.ToString(args.cField.F));
int nProvUserId = 0;
int flag = 0;
if ((_d.ContainsKey(FormConstants.email) && _d[FormConstants.email].Trim() != string.Empty) || (_d.ContainsKey(FormConstants.phprimary) && _d[FormConstants.phprimary].Trim() != string.Empty))
{
nProvUserId = this.GetUserIdByEmailOrPhone(args.DataDB, _d, ref flag, args.ins, args.ocF);
}
else
{
return(false);
}
if (args.ins)
{
if (nProvUserId > 0)// user already exists
{
if (!this.AllowExistingUser)
{
this.ThrowExistingUserException(_d, flag);
}
args._cols += args.cField.Name + CharConstants.COMMA + CharConstants.SPACE;
args._vals += CharConstants.AT + args.cField.Name + CharConstants.UNDERSCORE + args.i + CharConstants.COMMA + CharConstants.SPACE;
args.param.Add(args.DataDB.GetNewParameter(args.cField.Name + CharConstants.UNDERSCORE + args.i, (EbDbTypes)args.cField.Type, nProvUserId));
args.i++;
doNotUpdate = true;
}
else
{
this.SetUserType_Role_Status(_d, nProvUserId);
}
}
else
{
int oProvUserId = Convert.ToInt32(args.ocF.Value);
Dictionary <string, string> _od = JsonConvert.DeserializeObject <Dictionary <string, string> >(Convert.ToString(args.ocF.F));
if (_od.ContainsKey(FormConstants.id) && (oProvUserId == nProvUserId))// means user created by this control
{
this.AddOrChange(_d, FormConstants.id, _od[FormConstants.id]);
int oCreUserId = Convert.ToInt32(_od[FormConstants.id]);
if (oCreUserId != nProvUserId || !_d.ContainsKey(FormConstants.email))
{
this.AddOrChange(_d, FormConstants.email, _od[FormConstants.email]);// remove this line if you want to edit email via prov user ctrl
}
if (oCreUserId != nProvUserId || !_d.ContainsKey(FormConstants.phprimary))
{
this.AddOrChange(_d, FormConstants.phprimary, _od[FormConstants.phprimary]);
}
this.AddOrChange(_d, FormConstants.usertype, _od[FormConstants.usertype]);
int oldStatus = Convert.ToInt32(_od[FormConstants.statusid]);
if (!(_d.TryGetValue(FormConstants.statusid, out string newStatus_s) && int.TryParse(newStatus_s, out int newStatus_i) && newStatus_i != oldStatus))//if not status changed
{
this.AddOrChange(_d, FormConstants.statusid, Convert.ToString(oldStatus + 100));
}
if (_od.ContainsKey(FormConstants.locConstraint))
{
this.AddOrChange(_d, FormConstants.locConstraint, _od[FormConstants.locConstraint]);
}
if (oldStatus == (int)EbUserStatus.Unapproved)
{
if (args.usr.Roles.Contains(SystemRoles.SolutionOwner.ToString()) || args.usr.Roles.Contains(SystemRoles.SolutionAdmin.ToString()))
{
int u_type = Convert.ToInt32(_od[FormConstants.usertype]);
EbUserType ebTyp = this.UserTypeToRole.Find(e => e.iValue == u_type && e.bVisible);
if (ebTyp != null && ebTyp.Roles != null && ebTyp.Roles.Count > 0)
{
if (_d.TryGetValue(FormConstants.primary_role, out string priRole_s) && int.TryParse(priRole_s, out int priRole_i) && !ebTyp.Roles.Contains(priRole_i) && priRole_i > 100)
{
ebTyp.Roles.Add(priRole_i);
}
this.AddOrChange(_d, FormConstants.statusid, ((int)EbUserStatus.Active).ToString());
this.AddOrChange(_d, FormConstants.roles, string.Join(CharConstants.COMMA, ebTyp.Roles));
}
}
}
foreach (KeyValuePair <string, string> item in _od)
{
if (!_d.ContainsKey(item.Key))
{
string val = item.Value;
if (item.Key == FormConstants.roles)
{
if (_d.TryGetValue(FormConstants.primary_role, out string priRole_s) && int.TryParse(priRole_s, out int priRole_i) && priRole_i > 100)
{
List <string> st = string.IsNullOrWhiteSpace(item.Value) ? new List <string>() : item.Value.Split(",").ToList();
if (!st.Contains(priRole_s))
{
if (st.Count > 0)// remove old primary roles
{
string Qry = $"SELECT id FROM eb_roles WHERE is_primary='T' AND COALESCE(eb_del, 'F')='F' AND id IN(SELECT UNNEST(STRING_TO_ARRAY('{item.Value}', ',')::INTEGER[]));";
EbDataTable dt = args.DataDB.DoQuery(Qry);
foreach (EbDataRow dr in dt.Rows)
{
string old_rid = Convert.ToString(dr[0]);
if (st.Contains(old_rid))
{
st.Remove(old_rid);
}
}
}
st.Add(priRole_s);// add new primary role
val = st.Join(",");
}
}
}
_d.Add(item.Key, val);
}
}
}
else
{
doNotUpdate = true;
if (nProvUserId > 1 && oProvUserId != nProvUserId)
{
if (!this.AllowExistingUser)
{
this.ThrowExistingUserException(_d, flag);
}
args._colvals += string.Concat(args.cField.Name, CharConstants.EQUALS, CharConstants.AT, args.cField.Name, CharConstants.UNDERSCORE, args.i, CharConstants.COMMA, CharConstants.SPACE);
args.param.Add(args.DataDB.GetNewParameter(args.cField.Name + CharConstants.UNDERSCORE + args.i, (EbDbTypes)args.cField.Type, nProvUserId));
args.i++;
}
else if (nProvUserId <= 0)
{
insertOnUpdate = true;
this.SetUserType_Role_Status(_d, nProvUserId);
}
}
}
if (!doNotUpdate || insertOnUpdate)
{
this.MergeConstraints(_d, nProvUserId);
string p_email = string.Empty, p_phone = string.Empty;
for (int k = 0; k < this.FuncParam.Length; k++, args.i++)
{
object _value = this.FuncParam[k].Value;
if (_d.ContainsKey(this.FuncParam[k].Name) && !string.IsNullOrEmpty(_d[this.FuncParam[k].Name]))
{
_value = _d[this.FuncParam[k].Name];
if (this.FuncParam[k].Name.Equals(FormConstants.email))
{
p_email = string.Concat(CharConstants.COLON, this.FuncParam[k].Name, CharConstants.UNDERSCORE, args.i);
}
if (this.FuncParam[k].Name.Equals(FormConstants.phprimary))
{
p_phone = string.Concat(CharConstants.COLON, this.FuncParam[k].Name, CharConstants.UNDERSCORE, args.i);
}
}
c += string.Concat(CharConstants.COLON, this.FuncParam[k].Name, CharConstants.UNDERSCORE, args.i, CharConstants.COMMA, CharConstants.SPACE);
if (_value == DBNull.Value)
{
var p = args.DataDB.GetNewParameter(this.FuncParam[k].Name + CharConstants.UNDERSCORE + args.i, this.FuncParam[k].Type);
p.Value = _value;
args.param.Add(p);
}
else
{
args.param.Add(args.DataDB.GetNewParameter(this.FuncParam[k].Name + CharConstants.UNDERSCORE + args.i, this.FuncParam[k].Type, _value));
}
}
args._extqry += this.GetSaveQuery(args.ins, c.Substring(0, c.Length - 2), args.tbl, p_email, p_phone, insertOnUpdate);
}
return(true);
}
private string[] GetApproverEntityValues(ref int i, EbReviewStage nextStage, out bool hasPerm)
{
string _col = string.Empty, _val = string.Empty;
this.webForm.MyActNotification = new MyActionNotification()
{
ApproverEntity = nextStage.ApproverEntity,
SendPushNotification = !nextStage.HideNotification
};
if (nextStage.ApproverEntity == ApproverEntityTypes.Role)
{
_col = "role_ids";
_val = $"@role_ids_{i}";
string roles = nextStage.ApproverRoles == null ? string.Empty : nextStage.ApproverRoles.Join(",");
this.param.Add(this.DataDB.GetNewParameter($"role_ids_{i++}", EbDbTypes.String, roles));
this.webForm.MyActNotification.RoleIds = nextStage.ApproverRoles;
hasPerm = this.webForm.UserObj.RoleIds.Any(e => nextStage.ApproverRoles.Contains(e));
}
else if (nextStage.ApproverEntity == ApproverEntityTypes.UserGroup)
{
_col = "usergroup_id";
_val = $"@usergroup_id_{i}";
this.param.Add(this.DataDB.GetNewParameter($"usergroup_id_{i++}", EbDbTypes.Int32, nextStage.ApproverUserGroup));
this.webForm.MyActNotification.UserGroupId = nextStage.ApproverUserGroup;
hasPerm = this.webForm.UserObj.UserGroupIds.Any(e => e == nextStage.ApproverUserGroup);
}
else if (nextStage.ApproverEntity == ApproverEntityTypes.Users || nextStage.ApproverEntity == ApproverEntityTypes.DynamicRole)
{
string t1 = string.Empty, t2 = string.Empty, t3 = string.Empty;
List <DbParameter> _params = new List <DbParameter>();
int _idx = 0, ErrCod = (int)HttpStatusCode.BadRequest;
string ErrMsg = "GetFirstMyActionInsertQuery: Review control parameter {0} is not idetified";
foreach (KeyValuePair <string, string> p in nextStage.QryParams)
{
if (EbFormHelper.IsExtraSqlParam(p.Key, this.webForm.TableName))
{
continue;
}
SingleTable _Table = null;
if (this.webForm.FormData.MultipleTables.ContainsKey(p.Value))
{
_Table = this.webForm.FormData.MultipleTables[p.Value];
}
else if (this.webForm.FormDataBackup != null && this.webForm.FormDataBackup.MultipleTables.ContainsKey(p.Value))
{
_Table = this.webForm.FormDataBackup.MultipleTables[p.Value];
}
else
{
throw new FormException($"Bad Request", ErrCod, string.Format(ErrMsg, p.Key), $"{p.Value} not found in MultipleTables");
}
TableSchema _table = this.webForm.FormSchema.Tables.Find(e => e.TableName == p.Value);
if (_table.TableType != WebFormTableTypes.Normal)
{
throw new FormException($"Bad Request", ErrCod, string.Format(ErrMsg, p.Key), $"{p.Value} found in MultipleTables but it is not a normal table");
}
if (_Table.Count != 1)
{
throw new FormException($"Bad Request", ErrCod, string.Format(ErrMsg, p.Key), $"{p.Value} found in MultipleTables but table is empty");
}
SingleColumn Column = _Table[0].Columns.Find(e => e.Control?.Name == p.Key);
if (Column == null || Column.Control == null)
{
throw new FormException($"Bad Request", ErrCod, string.Format(ErrMsg, p.Key), $"{p.Value} found in MultipleTables but data not available");
}
ParameterizeCtrl_Params args = new ParameterizeCtrl_Params(this.DataDB, _params, Column, _idx, this.webForm.UserObj);
Column.Control.ParameterizeControl(args, this.webForm.CrudContext);
_idx = args.i;
_params[_idx - 1].ParameterName = p.Key;
}
List <int> ids = new List <int>();
EbFormHelper.AddExtraSqlParams(_params, this.DataDB, this.webForm.TableName, this.webForm.TableRowId, this.webForm.LocationId, this.webForm.UserObj.UserId);
string qry = nextStage.ApproverEntity == ApproverEntityTypes.Users ? nextStage.ApproverUsers.Code : nextStage.ApproverRoleQuery.Code;
EbDataTable dt = this.DataDB.DoQuery(qry, _params.ToArray());
foreach (EbDataRow dr in dt.Rows)
{
int.TryParse(dr[0].ToString(), out int temp);
if (!ids.Contains(temp))
{
ids.Add(temp);
}
}
_val = $"'{ids.Join(",")}'";
if (nextStage.ApproverEntity == ApproverEntityTypes.Users)
{
_col = "user_ids";
this.webForm.MyActNotification.UserIds = ids;
hasPerm = ids.Any(e => e == this.webForm.UserObj.UserId);
}
else
{
_col = "role_ids";
this.webForm.MyActNotification.RoleIds = ids;
hasPerm = this.webForm.UserObj.RoleIds.Any(e => ids.Contains(e));
}
}
else
{
throw new FormException("Unable to process review control", (int)HttpStatusCode.InternalServerError, "Invalid value for ApproverEntity : " + nextStage.ApproverEntity, "From GetMyActionInsertUpdateQuery");
}
if (this.webForm.UserObj.Roles.Contains(SystemRoles.SolutionOwner.ToString()) || this.webForm.UserObj.Roles.Contains(SystemRoles.SolutionAdmin.ToString()))
{
hasPerm = true;
}
return(new string[] { _col, _val });
}
