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