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 }); }