public static string GetInsertQuery_Batch(EbWebForm _this, IDatabase DataDB, string tblName)
{
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
EbDataPusherConfig conf = _this.DataPusherConfig;
string _qry;
if (tblName.Equals(_this.TableName))
{
_qry = $@"
INSERT INTO {tblName}
({{0}}
{ebs[SystemColumns.eb_created_by]},
{ebs[SystemColumns.eb_created_at]},
{ebs[SystemColumns.eb_loc_id]},
{ebs[SystemColumns.eb_ver_id]},
{ebs[SystemColumns.eb_src_ver_id]},
{ebs[SystemColumns.eb_src_id]},
{ebs[SystemColumns.eb_push_id]},
{ebs[SystemColumns.eb_lock]},
{ebs[SystemColumns.eb_signin_log_id]},
{ebs[SystemColumns.eb_ro]},
{conf.SourceTable}_id,
{conf.GridTableName}_id)
VALUES
({{1}}
@eb_createdby,
{DataDB.EB_CURRENT_TIMESTAMP},
@eb_loc_id,
@{_this.TableName}_eb_ver_id,
@{conf.SourceTable}_eb_ver_id,
{conf.SourceRecId},
{(conf.MultiPushId == null ? "null" : $"'{conf.MultiPushId}'")},
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);
}
//public static string GetCancelQuery(EbWebForm _this, IDatabase DataDB)
//{
// string query = string.Empty;
// foreach (TableSchema _table in _this.FormSchema.Tables)
// {
// string _id = "id";
// if (_table.TableName != _this.FormSchema.MasterTable)
// _id = _this.FormSchema.MasterTable + "_id";
// query += string.Format("UPDATE {0} SET eb_void='T',eb_lastmodified_by = @eb_lastmodified_by, eb_lastmodified_at = " + DataDB.EB_CURRENT_TIMESTAMP + " WHERE {1} = @id AND COALESCE(eb_void, 'F') = 'F' AND COALESCE(eb_del, 'F') = 'F';", _table.TableName, _id);
// }
// return query;
//}
public static string GetLockOrUnlockQuery(EbWebForm _this, IDatabase DataDB, bool Lock)
{
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
string Qry = string.Format("UPDATE {0} SET {1} = {6}, {2} = @eb_lastmodified_by, {3} = {4} WHERE id = @{0}_id AND COALESCE({1}, {5}) = {7};",
_this.TableName,
ebs[SystemColumns.eb_lock], //1
ebs[SystemColumns.eb_lastmodified_by], //2
ebs[SystemColumns.eb_lastmodified_at], //3
DataDB.EB_CURRENT_TIMESTAMP, //4
ebs.GetBoolFalse(SystemColumns.eb_lock), //5
Lock ? ebs.GetBoolTrue(SystemColumns.eb_lock) : ebs.GetBoolFalse(SystemColumns.eb_lock), //6
Lock ? ebs.GetBoolFalse(SystemColumns.eb_lock) : ebs.GetBoolTrue(SystemColumns.eb_lock)); //7
return(Qry);
}
public static string GetDynamicGridSelectQuery(EbWebForm _this, IDatabase DataDB, Service _service, string _prntTbl, string[] _targetTbls, out string _queryPs, out int _qryCount)
{
string query = string.Empty;
_queryPs = string.Empty;
_qryCount = 0;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
for (int i = 0; i < _targetTbls.Length; i++)
{
TableSchema _table = _this.FormSchema.Tables.Find(e => e.TableName == _targetTbls[i] && e.IsDynamic && e.TableType == WebFormTableTypes.Grid);
string _cols = string.Format("{0}, id, {1}",
ebs[SystemColumns.eb_loc_id],
ebs[SystemColumns.eb_row_num]);
IEnumerable <ColumnSchema> _columns = _table.Columns.Where(x => !x.Control.DoNotPersist || x.Control.IsSysControl || _table.TableType == WebFormTableTypes.Review);
if (_columns.Count() > 0)
{
_cols += ", " + string.Join(", ", _columns.Select(x => { return(x.Control.IsSysControl ? ebs[x.ColumnName] : x.ColumnName); }));
}
query += string.Format("SELECT {0} FROM {1} WHERE {2}_id = @{2}_id AND {3}_id = @{3}_id AND COALESCE({4}, {6}) = {6} {5}; ",
_cols,
_table.TableName,
_this.FormSchema.MasterTable,
_prntTbl,
ebs[SystemColumns.eb_del],
"ORDER BY " + ebs[SystemColumns.eb_row_num] + (_table.DescOdr ? " DESC" : string.Empty),
ebs.GetBoolFalse(SystemColumns.eb_del));
_qryCount++;
foreach (ColumnSchema Col in _table.Columns.FindAll(e => !e.Control.DoNotPersist && e.Control is EbDGPowerSelectColumn && !(e.Control as EbDGPowerSelectColumn).IsDataFromApi))
{
_queryPs += (Col.Control as EbDGPowerSelectColumn).GetSelectQuery123(DataDB, _service, _table.TableName, Col.ColumnName, _prntTbl, _this.FormSchema.MasterTable);
}
}
return(query);
}
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 GetUpdateQuery(EbWebForm _this, IDatabase DataDB, string tblName, bool isDel)
{
string _qry;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
EbDataPusherConfig conf = _this.DataPusherConfig;
if (conf == null)
{
if (tblName.Equals(_this.TableName))
{
_qry = string.Format("UPDATE {0} SET {7} {6}{1} = @eb_modified_by, {2} = {3} WHERE id = {8} AND COALESCE({4}, {5}) = {5}; ",
tblName, //0
ebs[SystemColumns.eb_lastmodified_by], //1
ebs[SystemColumns.eb_lastmodified_at], //2
DataDB.EB_CURRENT_TIMESTAMP, //3
ebs[SystemColumns.eb_del], //4
ebs.GetBoolFalse(SystemColumns.eb_del), //5
_this.LockOnSave ? $"{ebs[SystemColumns.eb_lock]} = {ebs.GetBoolTrue(SystemColumns.eb_lock)}, " : string.Empty,
"{0}",
"{1}");
}
else
{
_qry = string.Format("UPDATE {0} SET {7} {1} = @eb_modified_by, {2} = {3} WHERE id = {8} AND {4}_id = @{4}_id AND COALESCE({5}, {6}) = {6}; ",
tblName, //0
ebs[SystemColumns.eb_lastmodified_by], //1
ebs[SystemColumns.eb_lastmodified_at], //2
DataDB.EB_CURRENT_TIMESTAMP, //3
_this.TableName, //4
ebs[SystemColumns.eb_del], //5
ebs.GetBoolFalse(SystemColumns.eb_del), //6
isDel ? $"{ebs[SystemColumns.eb_del]} = {ebs.GetBoolTrue(SystemColumns.eb_del)}, " : "{0}",
"{1}");
}
}
else
{
// if isDel is true then consider lines table also
string parentTbl = _this.TableName;
string pushIdChk = string.Empty;
string cxt = _this.CrudContext;
if (tblName.Equals(_this.TableName))
{
cxt = string.Empty;
parentTbl = conf.SourceTable;
pushIdChk = conf.MultiPushId == null ? string.Empty : $"AND {ebs[SystemColumns.eb_push_id]} = '{conf.MultiPushId}'";
}
_qry = string.Format("UPDATE {0} SET {8} {1} = @eb_modified_by, {2} = {3} WHERE id = {10} AND {4}_id = @{4}_id{9} AND COALESCE({5}, {6}) = {6} {7}; ",
tblName, //0
ebs[SystemColumns.eb_lastmodified_by], //1
ebs[SystemColumns.eb_lastmodified_at], //2
DataDB.EB_CURRENT_TIMESTAMP, //3
parentTbl, //4
ebs[SystemColumns.eb_del], //5
ebs.GetBoolFalse(SystemColumns.eb_del), //6
pushIdChk, //7
isDel ? $"{ebs[SystemColumns.eb_del]} = {ebs.GetBoolTrue(SystemColumns.eb_del)}, " : "{0}", //8
cxt, //9
"{1}");
}
return(_qry);
}
public static string GetInsertQuery(EbWebForm _this, IDatabase DataDB, string tblName, bool isIns)
{
string _qry = string.Empty;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
EbDataPusherConfig conf = _this.DataPusherConfig;
string currencyCols = string.Empty, currencyVals = string.Empty;
TableSchema _table = _this.FormSchema.Tables.Find(e => e.TableName == tblName);
if (_table != null)
{
if (_table.Columns.Exists(e => !e.Control.DoNotPersist && !e.Control.IsSysControl &&
((e.Control is EbNumeric numCtrl && numCtrl.InputMode == NumInpMode.Currency) ||
(e.Control is EbDGNumericColumn numCol && numCol.InputMode == NumInpMode.Currency))))
{
currencyCols = $", {ebs[SystemColumns.eb_currency_id]}, {ebs[SystemColumns.eb_currency_xid]}, {ebs[SystemColumns.eb_xrate1]}, {ebs[SystemColumns.eb_xrate2]}";
currencyVals = $", 1, 'AED', 1, 1";
}
}
if (tblName.Equals(_this.TableName))
{
if (conf == null)
{
//if (_this.AutoId != null)
// _qry = $"LOCK TABLE ONLY {_this.AutoId.TableName} IN EXCLUSIVE MODE; ";
_qry += string.Format("INSERT INTO {0} ({18} {1}, {2}, {3}, {4}, {5}, {10}, {11}, {12}, {13}{8}) VALUES ({19} @eb_createdby, {6}, @eb_loc_id, @{7}_eb_ver_id, @eb_signin_log_id, {14}, {15}, {16}, {17}{9}); ",
tblName, //0
ebs[SystemColumns.eb_created_by], //1
ebs[SystemColumns.eb_created_at], //2
ebs[SystemColumns.eb_loc_id], //3
ebs[SystemColumns.eb_ver_id], //4
ebs[SystemColumns.eb_signin_log_id], //5
DataDB.EB_CURRENT_TIMESTAMP, //6
_this.TableName, //7
currencyCols, //8
currencyVals, //9
ebs[SystemColumns.eb_void], //10
ebs[SystemColumns.eb_del], //11
ebs[SystemColumns.eb_lock], //12
ebs[SystemColumns.eb_ro], //13
ebs.GetBoolFalse(SystemColumns.eb_void), //14
ebs.GetBoolFalse(SystemColumns.eb_del), //15
_this.LockOnSave ? ebs.GetBoolTrue(SystemColumns.eb_lock) : ebs.GetBoolFalse(SystemColumns.eb_lock), //16
ebs.GetBoolFalse(SystemColumns.eb_ro), //17
"{0}", //18
"{1}"); //19
}
else
{
string srcRef = conf.SourceRecId <= 0 ? $"(SELECT eb_currval('{conf.SourceTable}_id_seq'))" : $"@{conf.SourceTable}_id";
_qry = string.Format(@"INSERT INTO {0} ({24} {1}, {2}, {3}, {4}, {9}_id, {5}, {6}, {7}, {8}, {15}, {16}, {20}, {21}{18})
VALUES ({25} @eb_createdby, {10}, @eb_loc_id, @{11}_eb_ver_id, {12}, {12}, {13}, {14}, @eb_signin_log_id, @{9}_eb_ver_id, {17}, {22}, {23}{19}); ",
tblName, //0
ebs[SystemColumns.eb_created_by], //1
ebs[SystemColumns.eb_created_at], //2
ebs[SystemColumns.eb_loc_id], //3
ebs[SystemColumns.eb_ver_id], //4
ebs[SystemColumns.eb_src_id], //5
ebs[SystemColumns.eb_push_id], //6
ebs[SystemColumns.eb_lock], //7
ebs[SystemColumns.eb_signin_log_id], //8
conf.SourceTable, //9
DataDB.EB_CURRENT_TIMESTAMP, //10
_this.TableName, //11
srcRef, //12
conf.MultiPushId == null ? "null" : $"'{conf.MultiPushId}'", //13
ebs.GetBoolTrue(SystemColumns.eb_lock), //14
ebs[SystemColumns.eb_src_ver_id], //15
ebs[SystemColumns.eb_ro], //16
ebs.GetBoolTrue(SystemColumns.eb_ro), //17
currencyCols, //18
currencyVals, //19
ebs[SystemColumns.eb_void], //20
ebs[SystemColumns.eb_del], //21
ebs.GetBoolFalse(SystemColumns.eb_void), //22
ebs.GetBoolFalse(SystemColumns.eb_del), //23
"{0}", //24
"{1}"); //25
}
if (DataDB.Vendor == DatabaseVendors.MYSQL)
{
_qry += $"SELECT eb_persist_currval('{tblName}_id_seq'); ";
}
if (_this.IsLocEditable)
{
_qry = _qry.Replace($", {ebs[SystemColumns.eb_loc_id]}", string.Empty).Replace(", @eb_loc_id", string.Empty);
}
}
//else if (tblName.Equals("eb_approval_lines"))
//{
// _qry = $@"INSERT INTO eb_approval_lines ({{0}} eb_created_by, eb_created_at, eb_loc_id, eb_src_id, eb_ver_id, eb_signin_log_id)
// VALUES ({{1}} @eb_createdby, {DataDB.EB_CURRENT_TIMESTAMP}, @eb_loc_id, @{_this.TableName}_id, @{_this.TableName}_eb_ver_id, @eb_signin_log_id); ";
// if (DataDB.Vendor == DatabaseVendors.MYSQL)
// _qry += "SELECT eb_persist_currval('eb_approval_lines_id_seq'); ";
// // eb_approval - update eb_approval_lines_id
// _qry += $@"UPDATE eb_approval SET eb_approval_lines_id = (SELECT eb_currval('eb_approval_lines_id_seq')), eb_lastmodified_by = @eb_modified_by, eb_lastmodified_at = {DataDB.EB_CURRENT_TIMESTAMP}
// WHERE eb_src_id = @{_this.TableName}_id AND eb_ver_id = @{_this.TableName}_eb_ver_id AND COALESCE(eb_del, 'F') = 'F'; ";
//}
else
{
string srcRef = isIns ? $"(SELECT eb_currval('{_this.TableName}_id_seq'))" : $"@{_this.TableName}_id" + (conf == null ? string.Empty : _this.CrudContext);
_qry = $@"
INSERT INTO {tblName} (
{{0}}
{ebs[SystemColumns.eb_created_by]},
{ebs[SystemColumns.eb_created_at]},
{ebs[SystemColumns.eb_loc_id]},
{_this.TableName}_id,
{ebs[SystemColumns.eb_signin_log_id]},
{ebs[SystemColumns.eb_void]},
{ebs[SystemColumns.eb_del]}
{currencyCols}
)
VALUES (
{{1}}
@eb_createdby,
{DataDB.EB_CURRENT_TIMESTAMP},
@eb_loc_id ,
{srcRef},
@eb_signin_log_id,
{ebs.GetBoolFalse(SystemColumns.eb_void)},
{ebs.GetBoolFalse(SystemColumns.eb_del)}
{currencyVals}
); ";
if (isIns && DataDB.Vendor == DatabaseVendors.MYSQL)
{
_qry += $"SELECT eb_persist_currval('{tblName}_id_seq'); ";
}
}
return(_qry);
}
//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);
}
public static string GetSelectQuery_Batch(EbWebForm _this, out int _qryCount)
{
_qryCount = 0;
string query = string.Empty;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
EbDataPusherConfig conf = _this.DataPusherConfig;
string _pshId = conf.MultiPushId == null ? string.Empty : $"AND {ebs[SystemColumns.eb_push_id]} = '{conf.MultiPushId}'";
foreach (TableSchema _table in _this.FormSchema.Tables)
{
string _cols = string.Empty;
IEnumerable <ColumnSchema> _columns = _table.Columns.Where(x => !x.Control.DoNotPersist || x.Control.IsSysControl || _table.TableType == WebFormTableTypes.Review);
if (_columns.Count() > 0)
{
_cols = ", " + String.Join(", ", _columns.Select(x => { return(x.Control.IsSysControl ? ebs[x.ColumnName] : x.ColumnName); }));
IEnumerable <ColumnSchema> _em_ph_cols = _columns.Where(x => (x.Control is EbPhone _phCtrl && _phCtrl.Sendotp) || (x.Control is EbEmailControl _emCtrl && _emCtrl.Sendotp));
if (_em_ph_cols.Count() > 0)
{
_cols += ", " + String.Join(", ", _em_ph_cols.Select(x => x.ColumnName + FormConstants._verified));
}
}
if (_table.TableName == _this.FormSchema.MasterTable)
{
//imp: column order is same as normal primary table
query += $@"
SELECT
{ebs[SystemColumns.eb_loc_id]},
{ebs[SystemColumns.eb_ver_id]},
{ebs[SystemColumns.eb_lock]},
{ebs[SystemColumns.eb_push_id]},
{ebs[SystemColumns.eb_src_id]},
{ebs[SystemColumns.eb_created_by]},
{ebs[SystemColumns.eb_void]},
{ebs[SystemColumns.eb_created_at]},
{ebs[SystemColumns.eb_src_ver_id]},
{ebs[SystemColumns.eb_ro]},
{ebs[SystemColumns.eb_lastmodified_by]},
{ebs[SystemColumns.eb_lastmodified_at]},
id
{_cols},
{conf.GridTableName}_id
FROM
{_table.TableName}
WHERE
{conf.SourceTable}_id = {conf.SourceRecId} AND
COALESCE({ebs[SystemColumns.eb_del]}, {ebs.GetBoolFalse(SystemColumns.eb_del)}) = {ebs.GetBoolFalse(SystemColumns.eb_del)} {_pshId}; ";
}
else
{
if (_table.TableType == WebFormTableTypes.Grid)
{
_cols = $"{ebs[SystemColumns.eb_loc_id]}, id, {ebs[SystemColumns.eb_row_num]}" + _cols;
}
else
{
_cols = $"{ebs[SystemColumns.eb_loc_id]}, id" + _cols;
}
query += $@"
SELECT
{_cols},
{_this.FormSchema.MasterTable}_id
FROM
{_table.TableName}
WHERE
{_this.FormSchema.MasterTable}_id IN (
SELECT id FROM {_this.FormSchema.MasterTable}
WHERE {conf.SourceTable}_id = {conf.SourceRecId} AND
COALESCE({ebs[SystemColumns.eb_del]}, {ebs.GetBoolFalse(SystemColumns.eb_del)}) = {ebs.GetBoolFalse(SystemColumns.eb_del)}) AND
COALESCE({ebs[SystemColumns.eb_del]}, {ebs.GetBoolFalse(SystemColumns.eb_del)}) = {ebs.GetBoolFalse(SystemColumns.eb_del)} {_pshId}
{(_table.TableType == WebFormTableTypes.Grid ? ("ORDER BY " + ebs[SystemColumns.eb_row_num] + (_table.DescOdr ? " DESC" : "")) : "")}; ";
}
_qryCount++;
}
return(query);
}
public static string GetSelectQuery(EbWebForm _this, IDatabase DataDB, Service _service, out int _qryCount)
{
string query = string.Empty;
string extquery = string.Empty;
_qryCount = 0;
EbSystemColumns ebs = _this.SolutionObj.SolutionSettings.SystemColumns;
foreach (TableSchema _table in _this.FormSchema.Tables)
{
if (_table.IsDynamic || _table.DoNotPersist)
{
continue;
}
string _cols = $"{ebs[SystemColumns.eb_loc_id]}, id";
string _id = "id";
if (_table.TableName == _this.FormSchema.MasterTable)
{
_cols = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, id",
ebs[SystemColumns.eb_loc_id], //0
ebs[SystemColumns.eb_ver_id], //1
ebs[SystemColumns.eb_lock], //2
ebs[SystemColumns.eb_push_id], //3 //need only for slave forms in datapusher
ebs[SystemColumns.eb_src_id], //4
ebs[SystemColumns.eb_created_by], //5
ebs[SystemColumns.eb_void], //6
ebs[SystemColumns.eb_created_at], //7
ebs[SystemColumns.eb_src_ver_id], //8
ebs[SystemColumns.eb_ro], //9
ebs[SystemColumns.eb_lastmodified_by], //10
ebs[SystemColumns.eb_lastmodified_at]); //11
}
else if (_table.TableType == WebFormTableTypes.Review)
{
_id = $"eb_ver_id = @{_this.FormSchema.MasterTable}_eb_ver_id AND eb_src_id";
_cols = "eb_loc_id, id";
}
else
{
_id = _this.FormSchema.MasterTable + "_id";
}
if (_table.TableType == WebFormTableTypes.Grid)
{
_cols = $"{ebs[SystemColumns.eb_loc_id]}, id, {ebs[SystemColumns.eb_row_num]}";
}
IEnumerable <ColumnSchema> _columns = _table.Columns.Where(x => !x.Control.DoNotPersist || x.Control.IsSysControl || _table.TableType == WebFormTableTypes.Review);
if (_columns.Count() > 0)
{
_cols += ", " + String.Join(", ", _columns.Select(x => { return(x.Control.IsSysControl ? ebs[x.ColumnName] : x.ColumnName); }));
IEnumerable <ColumnSchema> _ph_em_cols =
_columns.Where(x => (x.Control is EbPhone _phCtrl && _phCtrl.Sendotp) || (x.Control is EbEmailControl _emCtrl && _emCtrl.Sendotp));
if (_ph_em_cols.Count() > 0)
{
_cols += ", " + String.Join(", ", _ph_em_cols.Select(x => x.ColumnName + FormConstants._verified));
}
}
if (_this.DataPusherConfig == null)// master form
{
query += string.Format("SELECT {0} FROM {1} WHERE {2} = @{3}_id AND COALESCE({5}, {6}) = {6} {4};",
_cols,
_table.TableName,
_id,
_this.FormSchema.MasterTable,
_table.TableType == WebFormTableTypes.Grid ? ("ORDER BY " + ebs[SystemColumns.eb_row_num] + (_table.DescOdr ? " DESC" : string.Empty)) : "ORDER BY id",
_table.TableType == WebFormTableTypes.Review ? SystemColumns.eb_del : ebs[SystemColumns.eb_del],
_table.TableType == WebFormTableTypes.Review ? "'F'" : ebs.GetBoolFalse(SystemColumns.eb_del));
}
else
{
string _pshId = _this.DataPusherConfig.MultiPushId == null ? string.Empty : $"AND {ebs[SystemColumns.eb_push_id]} = '{_this.DataPusherConfig.MultiPushId}'";
if (_table.TableName == _this.FormSchema.MasterTable)
{
query += string.Format("SELECT {0} FROM {1} WHERE {2}_id = @{2}_id {3} AND COALESCE({4}, {5}) = {5};",
_cols, //0
_table.TableName, //1
_this.DataPusherConfig.SourceTable, //2
_pshId, //3
ebs[SystemColumns.eb_del], //4
ebs.GetBoolFalse(SystemColumns.eb_del)); //5
}
else
{
query += string.Format("SELECT {0} FROM {1} WHERE {2}_id = (SELECT id FROM {2} WHERE {3}_id = @{3}_id {4} AND COALESCE({6}, {7}) = {7} LIMIT 1) AND COALESCE({8}, {9}) = {9} {5};",
_cols, //0
_table.TableName, //1
_this.FormSchema.MasterTable, //2
_this.DataPusherConfig.SourceTable, //3
_pshId, //4
_table.TableType == WebFormTableTypes.Grid ? ("ORDER BY " + ebs[SystemColumns.eb_row_num] + (_table.DescOdr ? " DESC" : string.Empty)) : "ORDER BY id", //5
ebs[SystemColumns.eb_del], //6
ebs.GetBoolFalse(SystemColumns.eb_del), //7
_table.TableType == WebFormTableTypes.Review ? SystemColumns.eb_del : ebs[SystemColumns.eb_del], //8
_table.TableType == WebFormTableTypes.Review ? "'F'" : ebs.GetBoolFalse(SystemColumns.eb_del)); //9
}
}
_qryCount++;
}
bool MuCtrlFound = false;//multiple ctrl found
foreach (EbControl Ctrl in _this.FormSchema.ExtendedControls)
{
if (Ctrl is EbProvisionUser)
{
if (!MuCtrlFound)
{
extquery += (Ctrl as IEbExtraQryCtrl).GetSelectQuery(DataDB, _this.FormSchema.MasterTable);
MuCtrlFound = true;
_qryCount++;
}
extquery += (Ctrl as EbProvisionUser).GetMappedUserQuery(_this.FormSchema.MasterTable, ebs[SystemColumns.eb_del], ebs.GetBoolFalse(SystemColumns.eb_del));
_qryCount++;
}
else if (Ctrl is IEbExtraQryCtrl)
{
extquery += (Ctrl as IEbExtraQryCtrl).GetSelectQuery(DataDB, _this.FormSchema.MasterTable);
_qryCount++;
}
}
return(query + extquery);
}
public void ExecUniqueCheck(IDatabase DataDB, DbConnection DbCon)
{
string fullQuery = string.Empty;
List <DbParameter> Dbparams = new List <DbParameter>();
List <EbControl> UniqueCtrls = new List <EbControl>();
int paramCounter = 0, mstrFormCtrls = 0;
EbSystemColumns SysCols = this.MasterForm.SolutionObj.SolutionSettings.SystemColumns;
foreach (EbWebForm WebForm in this)
{
if (WebForm.DataPusherConfig?.AllowPush == false)
{
continue;
}
foreach (TableSchema _table in WebForm.FormSchema.Tables.FindAll(e => e.TableType == WebFormTableTypes.Normal))
{
if (!(WebForm.FormData.MultipleTables.TryGetValue(_table.TableName, out SingleTable Table) && Table.Count > 0))
{
continue;
}
foreach (ColumnSchema _column in _table.Columns.FindAll(e => e.Control.Unique && !e.Control.BypassParameterization))
{
SingleColumn cField = Table[0].GetColumn(_column.ColumnName);
if (cField == null || cField.Value == null || (Double.TryParse(Convert.ToString(cField.Value), out double __val) && __val == 0))
{
continue;
}
if (WebForm.FormDataBackup != null)
{
if (WebForm.FormDataBackup.MultipleTables.TryGetValue(_table.TableName, out SingleTable TableBkUp) && TableBkUp.Count > 0)
{
SingleColumn ocField = TableBkUp[0].GetColumn(_column.ColumnName);
if (ocField != null && Convert.ToString(cField.Value).Trim().ToLower() == Convert.ToString(ocField.Value ?? string.Empty).Trim().ToLower())
{
continue;
}
}
}
fullQuery += string.Format("SELECT id FROM {0} WHERE {5}{1}{6} = {5}@{1}_{2}{6} AND COALESCE({3}, {4}) = {4} AND id <> {7};", //check eb_ver_id here!
_table.TableName, //0
_column.ColumnName, //1
paramCounter, //2
SysCols[SystemColumns.eb_del], //3
SysCols.GetBoolFalse(SystemColumns.eb_del), //4
_column.Control.EbDbType == EbDbTypes.String ? "LOWER(TRIM(" : string.Empty, //5
_column.Control.EbDbType == EbDbTypes.String ? "))" : string.Empty, //6
WebForm.TableRowId); //7
Dbparams.Add(DataDB.GetNewParameter($"{_column.ColumnName}_{paramCounter++}", _column.Control.EbDbType, cField.Value));
UniqueCtrls.Add(_column.Control);
if (WebForm == MasterForm)
{
mstrFormCtrls++;
}
}
}
}
if (fullQuery != string.Empty)
{
EbDataSet ds;
if (DbCon == null)
{
ds = DataDB.DoQueries(fullQuery, Dbparams.ToArray());
}
else
{
ds = DataDB.DoQueries(DbCon, fullQuery, Dbparams.ToArray());
}
for (int i = 0; i < ds.Tables.Count; i++)
{
if (ds.Tables[i].Rows.Count > 0)
{
if (mstrFormCtrls > i)
{
throw new FormException($"{UniqueCtrls[i].Label} must be unique", (int)HttpStatusCode.BadRequest, $"Value of {UniqueCtrls[i].Label} is not unique. Control name: {UniqueCtrls[i].Name}", "EbWebFormCollection -> ExecUniqueCheck");
}
else
{
throw new FormException($"{UniqueCtrls[i].Label} in data pusher must be unique", (int)HttpStatusCode.BadRequest, $"Value of {UniqueCtrls[i].Label} in data pusher is not unique. Control name: {UniqueCtrls[i].Name}", "EbWebFormCollection -> ExecUniqueCheck");
}
}
}
}
}
public static string UpdateIndexes(IDatabase DataDB, EbWebForm _webForm, int limit)
{
DateTime startdt = DateTime.Now;
string Message = $"Service started at {startdt}.";
Console.WriteLine("Update all indexes start: " + startdt);
EbSystemColumns ebs = _webForm.SolutionObj.SolutionSettings.SystemColumns;
string qCols = $"mtbl.id, mtbl.{ebs[SystemColumns.eb_created_by]}, mtbl.{ebs[SystemColumns.eb_created_at]}, mtbl.{ebs[SystemColumns.eb_lastmodified_by]}, mtbl.{ebs[SystemColumns.eb_lastmodified_at]}",
qJoin = string.Empty,
qCdtn = $"COALESCE(mtbl.{ebs[SystemColumns.eb_del]}, {ebs.GetBoolFalse(SystemColumns.eb_del)}) = {ebs.GetBoolFalse(SystemColumns.eb_del)}";
const int sysColCnt = 5;
int tblCnt = 1, colCnt = sysColCnt;//Count
List <string> labels = new List <string>();
bool deleteOld = false;
foreach (TableSchema _table in _webForm.FormSchema.Tables)
{
if (_table.TableType == WebFormTableTypes.Normal)
{
List <ColumnSchema> _columns = _table.Columns.FindAll(e => e.Control?.Index == true && !e.Control.DoNotPersist);
if (_columns.Count > 0)
{
if (_table.TableName == _webForm.FormSchema.MasterTable)
{
qCols += ", " + _columns.Select(e => "mtbl." + e.ColumnName).Join(", ");
}
else
{
qCols += ", " + _columns.Select(e => $"tbl{tblCnt}." + e.ColumnName).Join(", ");
qJoin += $" LEFT JOIN {_table.TableName} tbl{tblCnt} ON mtbl.id = tbl{tblCnt}.{_webForm.FormSchema.MasterTable}_id ";
qCdtn += $" AND COALESCE(tbl{tblCnt}.eb_del, 'F') = 'F'";
tblCnt++;
}
foreach (ColumnSchema _column in _columns)
{
labels.Add(_column.Control.Label);
colCnt++;
Message += $"\nIndexed {_column.Control.Label}.";
}
}
}
}
if (labels.Count > 0)
{
int FLUSH_LIMIT = limit == 0 ? 1000 : limit;
string countQry = $"SELECT COUNT(*) FROM {_webForm.FormSchema.MasterTable} mtbl {qJoin} WHERE {qCdtn};";
EbDataTable _dt = DataDB.DoQuery(countQry);
if (_dt.Rows.Count > 0)
{
int TOTAL_RECORDS = Convert.ToInt32(_dt.Rows[0][0]);
Console.WriteLine($"Existing {TOTAL_RECORDS} records.");
Message += $"\nExisting {TOTAL_RECORDS} records.";
for (int x = 0; x <= TOTAL_RECORDS / FLUSH_LIMIT; x++)
{
string fullQry = $"SELECT {qCols} FROM {_webForm.FormSchema.MasterTable} mtbl {qJoin} WHERE {qCdtn} OFFSET {x * FLUSH_LIMIT} LIMIT {FLUSH_LIMIT};";
EbDataTable dt = DataDB.DoQuery(fullQry);
int i, j;
StringBuilder idxSelQry = new StringBuilder();
for (i = 0; i < dt.Rows.Count; i++)
{
idxSelQry.Append("SELECT id FROM eb_index_table WHERE ref_id = @ref_id AND data_id = " + Convert.ToString(dt.Rows[i][0]) + " AND COALESCE(eb_del, 'F') = 'F'; ");
}
EbDataSet ds = DataDB.DoQueries(idxSelQry.ToString(), new DbParameter[] { DataDB.GetNewParameter("ref_id", EbDbTypes.String, _webForm.RefId) });
StringBuilder updateQry = new StringBuilder();
StringBuilder insertQry = new StringBuilder();
DbParameter[] parameters = new DbParameter[]
{
DataDB.GetNewParameter("ref_id", EbDbTypes.String, _webForm.RefId),
DataDB.GetNewParameter("disp_name", EbDbTypes.String, _webForm.DisplayName)
};
for (i = 0; i < dt.Rows.Count; i++)
{
EbDataRow dr = dt.Rows[i];
int id = ds.Tables[i].Rows.Count > 0 ? Convert.ToInt32(ds.Tables[i].Rows[0][0]) : 0;
Dictionary <string, string> JsonData = new Dictionary <string, string>();
for (j = sysColCnt; j < colCnt; j++)
{
string _val = Convert.ToString(dr[j]);
string _lbl = labels[j - sysColCnt];
if (!(dr.IsDBNull(j) || string.IsNullOrEmpty(_val) || JsonData.ContainsKey(_lbl)))
{
JsonData.Add(_lbl, _val);
}
}
if (JsonData.Count > 0)
{
string json = JsonConvert.SerializeObject(JsonData);
string dataId = Convert.ToString(dr[0]);
string creBy = Convert.ToString(dr[1]);
string creAt = Convert.ToDateTime(dr[2]).ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
string modBy = Convert.ToString(dr[3]);
string modAt = Convert.ToDateTime(dr[4]).ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
if (id > 0)
{
updateQry.Append("UPDATE eb_index_table SET display_name = @disp_name, data_json = '" + json +
"', modified_by = " + modBy + ", modified_at = '" + modAt + "' WHERE id = " + id + ";");
}
else
{
insertQry.Append("(@disp_name, '" + json + "', @ref_id, " + dataId + ", " + creBy + ", '" + creAt + "', " + modBy + ", '" + modAt + "', 'F'),");
}
}
else
{
if (id > 0)
{
updateQry.Append("UPDATE eb_index_table SET eb_del = 'T' WHERE id = " + id + ";");
}
}
}
if (insertQry.Length > 0)
{
insertQry.Length--;
insertQry.Insert(0, "INSERT INTO eb_index_table (display_name, data_json, ref_id, data_id, created_by, created_at, modified_by, modified_at, eb_del) VALUES");
insertQry.Append(";");
}
int UpserteRecords = DataDB.DoNonQuery(updateQry.ToString() + insertQry.ToString(), parameters);
string strMsg = $"Upserted {_webForm.FormSchema.MasterTable}: ({x * FLUSH_LIMIT} - {x * FLUSH_LIMIT + UpserteRecords}) records.";
Console.WriteLine(strMsg);
Message += "\n" + strMsg;
}
}
else
{
deleteOld = true;
}
}
else
{
deleteOld = true;
}
if (deleteOld)
{
string delQry = "UPDATE eb_index_table SET eb_del = 'T' WHERE ref_id = @ref_id AND COALESCE(eb_del, 'F') = 'F';";
int t = DataDB.DoNonQuery(delQry, new DbParameter[] { DataDB.GetNewParameter("ref_id", EbDbTypes.String, _webForm.RefId) });
Console.WriteLine($"Nothing to index. Deleted old {t} records.");
Message += $"\nDeleted {t} records.";
}
Console.WriteLine("Update all indexes end : Execution Time = " + (DateTime.Now - startdt).TotalMilliseconds);
Message += $"\nCompleted. Execution Time: {(DateTime.Now - startdt).TotalMilliseconds}.";
return(Message);
}
