public void MergeFormData(WebformData FormData, WebFormSchema FormSchema)
{
if (FormData.MultipleTables.ContainsKey(this.TableName) && FormData.MultipleTables[this.TableName].Count > 0)
{
SingleTable rows = FormData.MultipleTables[this.TableName];
for (int i = 0; i < rows.Count; i++)
{
if (rows[i].RowId > 0)
{
rows.RemoveAt(i--);
}
}
if (rows.Count == 1)//one new entry// need to write code for 'AfterSaveRoutines'
{
foreach (TableSchema t in FormSchema.Tables)
{
if (t.TableName != this.TableName)
{
FormData.MultipleTables.Remove(t.TableName);// approval execution, hence removing other data if present
}
}
string[] str_t = { FormConstants.stage_unique_id, FormConstants.action_unique_id, FormConstants.eb_my_actions_id, FormConstants.comments };
for (int i = 0; i < str_t.Length; i++)
{
EbControl con = this.Controls.Find(e => e.Name == str_t[i]);
FormData.MultipleTables[this.TableName][0].SetEbDbType(con.Name, con.EbDbType);
FormData.MultipleTables[this.TableName][0].SetControl(con.Name, con);
}
}
}
}
public EbSqlFunction(WebFormSchema data, IEbConnectionFactory con)
{
this.FormSchema = data;
DisplayName = FormSchema.FormName + "_data_insert";
Name = DisplayName;
this.ConnectionFactory = con;
this.Sql = this.GenSqlFunc();
}
public static string GetDeleteQuery(EbWebForm _this, IDatabase DataDB)
{
string FullQry = string.Empty;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
foreach (EbWebForm ebWebForm in _this.FormCollection)
{
WebFormSchema _schema = ebWebForm.FormSchema;
foreach (TableSchema _table in _schema.Tables.FindAll(e => e.TableType != WebFormTableTypes.Review && !e.DoNotPersist))
{
string autoIdBckUp = string.Empty;
//if (ebWebForm.AutoId != null && ebWebForm.AutoId.TableName == _table.TableName) // uncomment this and check the autoid reassignment
// autoIdBckUp = string.Format(", {0}_ebbkup = {0}, {0} = CONCAT({0}, '_ebbkup')", ebWebForm.AutoId.Name);
string Qry = string.Format("UPDATE {0} SET {1} = {6}, {2} = @eb_lastmodified_by, {3} = {4} {5} ",
_table.TableName,
ebs[SystemColumns.eb_del],
ebs[SystemColumns.eb_lastmodified_by],
ebs[SystemColumns.eb_lastmodified_at],
DataDB.EB_CURRENT_TIMESTAMP,
autoIdBckUp,
ebs.GetBoolTrue(SystemColumns.eb_del));
if (ebWebForm.DataPusherConfig == null)
{
Qry += string.Format("WHERE {0} = @{1}_id AND COALESCE({2}, {3}) = {3};",
_table.TableName == _schema.MasterTable ? "id" : (_schema.MasterTable + "_id"),
_schema.MasterTable,
ebs[SystemColumns.eb_del],
ebs.GetBoolFalse(SystemColumns.eb_del));
}
else
{
EbDataPusherConfig _conf = ebWebForm.DataPusherConfig;
string _pshId = _conf.MultiPushId == null ? string.Empty : $"AND {ebs[SystemColumns.eb_push_id]} = '{_conf.MultiPushId}'";
if (_table.TableName == _schema.MasterTable)
{
Qry += string.Format("WHERE {0}_id = @{0}_id {1} AND COALESCE({2}, {3}) = {3};",
_conf.SourceTable,
_pshId,
ebs[SystemColumns.eb_del],
ebs.GetBoolFalse(SystemColumns.eb_del));
}
else
{
Qry += string.Format("WHERE {0}_id = (SELECT id FROM {0} WHERE {1}_id = @{1}_id {2} AND COALESCE({3}, {4}) = {4} LIMIT 1) AND COALESCE({3}, {4}) = {4};",
_schema.MasterTable,
_conf.SourceTable,
_pshId,
ebs[SystemColumns.eb_del],
ebs.GetBoolFalse(SystemColumns.eb_del));
}
}
FullQry = Qry + FullQry;
}
}
return(FullQry);
}
internal static WebFormSchema GetWebFormSchema(EbWebForm _this)
{
WebFormSchema _formSchema = new WebFormSchema();
_formSchema.FormName = _this.Name;
_formSchema.MasterTable = _this.TableName.ToLower();
EbSystemColumns ebs = _this.SolutionObj?.SolutionSettings?.SystemColumns;
_formSchema = GetWebFormSchemaRec(_this, _formSchema, _this, _this.TableName.ToLower(), ebs);
_this.FormSchema = _formSchema;
return(_formSchema);
}
private static WebFormSchema GetWebFormSchemaRec(EbWebForm _this, WebFormSchema _schema, EbControlContainer _container, string _parentTable, EbSystemColumns ebs)
{
IEnumerable <EbControl> _flatControls = _container.Controls.Get1stLvlControls();
string curTbl = _container.TableName.ToLower();
TableSchema _table = _schema.Tables.FirstOrDefault(tbl => tbl.TableName == curTbl);
if (_table == null)
{
if (_container is EbReview)
{
_table = new TableSchema {
TableName = curTbl, ParentTable = _parentTable, TableType = WebFormTableTypes.Review, Title = _container.Label, ContainerName = _container.Name
};
_schema.ExtendedControls.Add(_container);
}
else if (_container is EbDataGrid dg)
{
_table = new TableSchema {
TableName = curTbl, ParentTable = _parentTable, TableType = WebFormTableTypes.Grid, Title = _container.Label, ContainerName = _container.Name, IsDynamic = _container.IsDynamicTabChild, DescOdr = !dg.AscendingOrder, DoNotPersist = dg.DoNotPersist
}
}
;
else
{
_table = new TableSchema {
TableName = curTbl, ParentTable = _parentTable, TableType = WebFormTableTypes.Normal, ContainerName = _container.Name
}
};
_schema.Tables.Add(_table);
}
foreach (EbControl control in _flatControls)
{
//if (control.IsSysControl && ebs != null && !(_container is EbReview))
//{
// control.Name = ebs[control.Name];
//}
if (control is EbFileUploader)
{
_schema.ExtendedControls.Add(control);
}
else if (control is EbProvisionUser)
{
EbProvisionUser ctrl = control as EbProvisionUser;
ctrl.FuncParam.First(e => e.Name == "isolution_id").Value = _this.SolutionObj?.SolutionID ?? string.Empty;
int idx = _schema.ExtendedControls.FindIndex(e => e is EbProvisionLocation);
if (idx >= 0)
{
ctrl.AddLocConstraint = true;
}
_table.Columns.Add(new ColumnSchema {
ColumnName = control.Name, EbDbType = (int)control.EbDbType, Control = control
});
}
else if (control is EbProvisionLocation)
{
foreach (object temp in _schema.ExtendedControls.FindAll(e => e is EbProvisionUser))
{
(temp as EbProvisionUser).AddLocConstraint = true;
}
_table.Columns.Add(new ColumnSchema {
ColumnName = control.Name, EbDbType = (int)control.EbDbType, Control = control
});
}
else if (control is EbDGUserControlColumn)
{
foreach (EbControl _ctrl in (control as EbDGUserControlColumn).Columns)
{
_table.Columns.Add(new ColumnSchema {
ColumnName = _ctrl.Name, EbDbType = (int)_ctrl.EbDbType, Control = _ctrl
});
}
}
else if (control is EbSysLocation && !control.IsDisable)
{
_this.IsLocEditable = true;
_table.Columns.Add(new ColumnSchema {
ColumnName = control.Name, EbDbType = (int)control.EbDbType, Control = control
});
}
else
{
_table.Columns.Add(new ColumnSchema {
ColumnName = control.Name, EbDbType = (int)control.EbDbType, Control = control
});
if (control is EbAutoId)
{
_this.AutoId = control as EbAutoId;
_this.AutoId.TableName = _table.TableName;
}
}
if (control is IEbExtraQryCtrl)
{
(control as IEbExtraQryCtrl).TableName = curTbl;
_schema.ExtendedControls.Add(control);
}
else if (control is EbPhone _phCtrl && _phCtrl.Sendotp)
{
_phCtrl.FormRefId = _this.RefId;
_phCtrl.RedisClient = _this.RedisClient;
}
else if (control is EbEmailControl _emCtrl && _emCtrl.Sendotp)
{
_emCtrl.FormRefId = _this.RefId;
_emCtrl.RedisClient = _this.RedisClient;
}
//public static string GetDeleteQuery(EbWebForm _this, IDatabase DataDB)
//{
// string query = string.Empty;
// foreach (TableSchema _table in _this.FormSchema.Tables)
// {
// string _id = "id";
// string _dupcols = string.Empty;
// if (_table.TableName != _this.FormSchema.MasterTable)
// _id = _this.FormSchema.MasterTable + "_id";
// foreach (ColumnSchema _column in _table.Columns)
// {
// if (_column.Control is EbAutoId)
// {
// _dupcols += string.Format(", {0}_ebbkup = {0}, {0} = CONCAT({0}, '_ebbkup')", _column.ColumnName);
// }
// }
// query += string.Format("UPDATE {0} SET eb_del='T',eb_lastmodified_by = @eb_lastmodified_by, eb_lastmodified_at = " + DataDB.EB_CURRENT_TIMESTAMP + " {1} WHERE {2} = @id AND COALESCE(eb_del, 'F') = 'F';", _table.TableName, _dupcols, _id);
// }
// return query;
//}
public static string GetCancelQuery(EbWebForm _this, IDatabase DataDB, bool Cancel)
{
string FullQry = string.Empty;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
foreach (EbWebForm ebWebForm in _this.FormCollection)
{
WebFormSchema _schema = ebWebForm.FormSchema;
foreach (TableSchema _table in _schema.Tables.FindAll(e => e.TableType != WebFormTableTypes.Review && !e.DoNotPersist))
{
string Qry = string.Format("UPDATE {0} SET {1} = {5}, {2} = @eb_lastmodified_by, {3} = {4} ",
_table.TableName, //0
ebs[SystemColumns.eb_void], //1
ebs[SystemColumns.eb_lastmodified_by], //2
ebs[SystemColumns.eb_lastmodified_at], //3
DataDB.EB_CURRENT_TIMESTAMP, //4
Cancel ? ebs.GetBoolTrue(SystemColumns.eb_void) : ebs.GetBoolFalse(SystemColumns.eb_void)); //5
if (ebWebForm.DataPusherConfig == null)
{
Qry += string.Format("WHERE {0} = @{1}_id AND COALESCE({2}, {4}) = {4} AND COALESCE({3}, {5}) = {6};",
_table.TableName == _schema.MasterTable ? "id" : (_schema.MasterTable + "_id"), //0
_schema.MasterTable, //1
ebs[SystemColumns.eb_del], //2
ebs[SystemColumns.eb_void], //3
ebs.GetBoolFalse(SystemColumns.eb_del), //4
ebs.GetBoolFalse(SystemColumns.eb_void), //5
Cancel ? ebs.GetBoolFalse(SystemColumns.eb_void) : ebs.GetBoolTrue(SystemColumns.eb_void)); //6
}
else
{
EbDataPusherConfig _conf = ebWebForm.DataPusherConfig;
string _pshId = _conf.MultiPushId == null ? string.Empty : $"AND {ebs[SystemColumns.eb_push_id]} = '{_conf.MultiPushId}'";
if (_table.TableName == _schema.MasterTable)
{
Qry += string.Format("WHERE {0}_id = @{0}_id {1} AND COALESCE({2}, {4}) = {4} AND COALESCE({3}, {5}) = {6};",
_conf.SourceTable, //0
_pshId, //1
ebs[SystemColumns.eb_del], //2
ebs[SystemColumns.eb_void], //3
ebs.GetBoolFalse(SystemColumns.eb_del), //4
ebs.GetBoolFalse(SystemColumns.eb_void), //5
Cancel ? ebs.GetBoolFalse(SystemColumns.eb_void) : ebs.GetBoolTrue(SystemColumns.eb_void)); //6
}
else
{
Qry += string.Format("WHERE {0}_id = (SELECT id FROM {0} WHERE {1}_id = @{1}_id {2} AND COALESCE({3}, {5}) = {5} AND COALESCE({4}, {6}) = {7} LIMIT 1) AND COALESCE({3}, {5}) = {5} AND COALESCE({4}, {6}) = {7};",
_schema.MasterTable, //0
_conf.SourceTable, //1
_pshId, //2
ebs[SystemColumns.eb_del], //3
ebs[SystemColumns.eb_void], //4
ebs.GetBoolFalse(SystemColumns.eb_del), //5
ebs.GetBoolFalse(SystemColumns.eb_void), //6
Cancel ? ebs.GetBoolFalse(SystemColumns.eb_void) : ebs.GetBoolTrue(SystemColumns.eb_void)); //7
}
}
FullQry = Qry + FullQry;
}
}
return(FullQry);
}
