protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null)
{
//return base.GetSearchCommandTemplate(selectClause, count, tableAlias);
AssignedRolesModel _model = new AssignedRolesModel();
System.Text.StringBuilder Sql = new System.Text.StringBuilder();
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
return(base.GetSearchCommandTemplate(selectClause, count, tableAlias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT
C .*, t1.code,
t1.name_np,
t1.name_en
FROM
rbac_role_roles C
LEFT JOIN rbac_role_master t1 ON C .assigned_role_id = t1. ID
/**where**/
/**orderby**/"
, selectClause, _model.GetTableName(), alias));
}
return(cmd);
}
public virtual BangoCommand GetItemCommand(DynamicDictionary data_param, string tableAlias = null)
{
BangoCommand cmd = new BangoCommand();
TModel mdl = new TModel();
tableAlias = tableAlias == null ? MainTableAlias : tableAlias;
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT *
FROM {0} {1}
/**where**/
/**orderby**/"
, mdl.GetTableName(), tableAlias));
//PropertyInfo prop = mdl.GetKeyPropertyInfo();
//cmd.SqlBuilder.Where(string.Format("{0}=@{0}", prop.Name), DbServiceUtility.ToDynamicParameter(prop.Name, id, DbServiceUtility.TypeMap[prop.PropertyType]));
DbServiceUtility.BindDeleteParameter(cmd, mdl, tableAlias);
DbServiceUtility.BindParameters(cmd, mdl, data_param, tableAlias, SearchTypes.Equal);
if (CheckClientID)
{
PropertyInfo client_id = mdl.GetType().GetProperty("client_id");
if (client_id != null)
{
DbServiceUtility.BindClientIdParameter(cmd, mdl, tableAlias, DisplayMasterDataFromSystem);
//string col = DbServiceUtility.SetColumnAlias(tableAlias, "client_id");
//cmd.SqlBuilder.Where("(client_id = 1 OR client_id = @client_id)");
//DynamicParameters param = new DynamicParameters();
//param.Add("@client_id", SessionData.client_id, DbType.Int32);
//cmd.SqlBuilder.AddParameters(param);
}
}
return(cmd);
}
public virtual BangoCommand GetGridFilterItemsCommand(DbConnect con, DynamicDictionary data_param, int page = -1, int pageSize = 20, string sort_by = null, bool count = false)
{
sort_by = sort_by == null ? string.Empty : sort_by;
ComboFieldsAttribute comboAttrib = _model.GetComoFields();
if (sort_by.Trim().Length == 0 && comboAttrib.OrderBy.Length > 0)
{
sort_by = DbServiceUtility.SetColumnAlias(TableDetail.Alias, comboAttrib.OrderBy);
}
BangoCommand cmd = GetGridFilterItemsCommandTemplate(comboAttrib.ToString(), count, TableDetail.Alias);
cmd = GetSearchCommand(SearchScenario.GridFilter, con, cmd, data_param, comboAttrib.ToString()
, sort_by, page, pageSize, count, TableDetail.Alias);
//adding the query clause
string or_query = string.Empty;
Dapper.DynamicParameters param = new Dapper.DynamicParameters();
if (data_param.ContainsKey("q") || data_param.ContainsKey("query"))
{
string query = data_param.GetValueAsString("q");
if (query.Trim().Length == 0)
{
query = data_param.GetValueAsString("query");
}
AppendQueryAsOr(cmd, query, new string[] { "code", "name_np", "name_en" });
}
return(cmd);
}
public override BangoCommand GetComboItemsCommand(DbConnect con, DynamicDictionary data_param, int page = -1, int pageSize = 20, string sort_by = null, bool count = false)
{
sort_by = sort_by == null ? string.Empty : sort_by;
ComboFieldsAttribute comboAttrib = _model.GetComoFields();
if (sort_by.Trim().Length == 0 && comboAttrib.OrderBy.Length > 0)
{
sort_by = DbServiceUtility.SetColumnAlias(TableDetail.Alias, comboAttrib.OrderBy);
}
BangoCommand cmd = GetCombotItemsCommandTemplate(comboAttrib.ToString(), count, TableDetail.Alias);
if (comboAttrib.Status != null || comboAttrib.Status.Length > 0)
{
data_param.Add(comboAttrib.Status, true); //for Combo Status=True mains Active Recors load in ComboBOx
}
cmd = GetSearchCommand(SearchScenario.Combo, con, cmd, data_param, comboAttrib.ToString()
, sort_by, page, pageSize, count, TableDetail.Alias);
//adding the query clause
string or_query = string.Empty;
Dapper.DynamicParameters param = new Dapper.DynamicParameters();
return(cmd);
}
protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null)
{
System.Text.StringBuilder Sql = new System.Text.StringBuilder();
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
return(base.GetSearchCommandTemplate(selectClause, count, tableAlias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
select u.first_name || ' ' || u.last_name as full_name,u.contact_number,u.email,
dt.name_np as donation_type_name,
c.* from donation_details c
LEFT JOIN donation_request_links r on r.donation_id = c.id
LEFT JOIN rbac_user u on u.id=r.requested_user_id
LEFT JOIN donation_type dt on dt.id = c.donation_type_id
/**where**/
"
, selectClause, _model.GetTableName(), alias));
}
return(cmd);
}
protected virtual BangoCommand GetChildNodesCommandTemplate1(int parent_id, bool count = false, string tableAlias = null, string sort_by = null)
{
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias),
columnAlias = DbServiceUtility.GetTableAliasForColumn(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT count(1) total_records
FROM {0} {1}
/**where**/ "
, _model.GetTableName(), alias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT
{3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id, {3}code || ' - ' || {3}name_np || ' - ' || {3}name_en as text, true as leaf
FROM {0} {2}
/**where**/
/**orderby**/"
, _model.GetTableName(), string.Empty, alias, columnAlias));
}
return(cmd);
}
protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false,
string tableAlias = null)
{
//MyroCommand cmd = base.GetSearchCommandTemplate(selectClause, count, tableAlias);
//return cmd;
var _model = new UserModel();
var Sql = new StringBuilder();
var alias = DbServiceUtility.GetTableAliasForTable(tableAlias);
var cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
return(base.GetSearchCommandTemplate(selectClause, count, tableAlias));
}
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT c.*,
a.name_en as client_en, a.name_np as client_np, a.id as client_id
FROM rbac_user c
LEFT JOIN app_client a
ON c.client_id = a.id
/**where**/
/**orderby**/"
, selectClause, _model.GetTableName(), alias));
return(cmd);
}
public override BangoCommand GetItemCommand(DynamicDictionary data_param, string tableAlias = null)
{
BangoCommand cmd = new BangoCommand();
donation_request_links mdl = new donation_request_links();
tableAlias = tableAlias == null ? MainTableAlias : tableAlias;
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
select u.first_name || ' ' || u.last_name as full_name,u.contact_number,u.email from donation_details c
INNER JOIN donation_request_links r on r.donation_id = c.id
INNER JOIN rbac_user u on u.id=r.requested_user_id
/**where**/
/**orderby**/"
, mdl.GetTableName(), tableAlias));
//PropertyInfo prop = mdl.GetKeyPropertyInfo();
//cmd.SqlBuilder.Where(string.Format("{0}=@{0}", prop.Name), DbServiceUtility.ToDynamicParameter(prop.Name, id, DbServiceUtility.TypeMap[prop.PropertyType]));
DbServiceUtility.BindDeleteParameter(cmd, mdl, tableAlias);
DbServiceUtility.BindParameters(cmd, mdl, data_param, tableAlias, SearchTypes.Equal);
if (CheckClientID)
{
PropertyInfo client_id = mdl.GetType().GetProperty("client_id");
if (client_id != null)
{
DbServiceUtility.BindClientIdParameter(cmd, mdl, tableAlias, DisplayMasterDataFromSystem);
//string col = DbServiceUtility.SetColumnAlias(tableAlias, "client_id");
//cmd.SqlBuilder.Where("(client_id = 1 OR client_id = @client_id)");
//DynamicParameters param = new DynamicParameters();
//param.Add("@client_id", SessionData.client_id, DbType.Int32);
//cmd.SqlBuilder.AddParameters(param);
}
}
return(cmd);
}
//public bool CheckClientID { get; set; } = true;
protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null)
{
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT count(1) total_records
FROM {0} {1}
/**where**/ "
, _model.GetTableName(), alias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT {0}
FROM {1} {2}
LEFT JOIN {1} p on c.parent_id = p.id
/**where**/
/**orderby**/"
, selectClause, _model.GetTableName(), alias));
}
return(cmd);
}
public virtual BangoCommand GetSearchCommand(SearchScenario scenario, DbConnect con, BangoCommand cmd, DynamicDictionary data_param, string selectClause, string orderByClause, int page = -1, int pageSize = 20, bool count = false, string tableAlias = null, string scenarioOthers = null)
{
TableDetailAttribute tableDetail = _model.GetTableDetail();
//clear the params whic are empty or null
List <string> keys = new List <string>(data_param.KeyList.Cast <String>());
foreach (string key in keys)
{
object value = data_param.GetValue(key);
if (value == null || data_param.GetValueAsString(key).Length == 0)
{
data_param.Remove(key);
}
}
//BangoCommand cmd = GetSearchCommandTemplate(selectClause, count, tableAlias);
//cmd.Sql.AppendLine("FROM " + model.GetTableName());
IDbExpression dbExp = App.Container.GetInstance <IDbExpression>();
if (data_param.GetCount() == 0)
{
return(cmd);
}
string append = DbServiceUtility.GetTableAliasForColumn(tableAlias);
if (!(scenario == SearchScenario.TreeNode && count == false))
{
//check & adding delete flag check sql
DbServiceUtility.BindDeleteParameter(cmd, _model, tableAlias);
if (CheckClientID)
{
DbServiceUtility.BindClientIdParameter(cmd, _model, tableAlias, DisplayMasterDataFromSystem);
}
//add remaining default search criteria
cmd = BeforeBindingParameter(scenario, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindParameters(cmd, _model, data_param, tableAlias);
cmd = AfterBindingParameter(scenario, con, cmd, data_param, count, tableAlias);
//check & adding order by clause
if (count == false)
{
cmd = BeforeBindingOrderBy(scenario, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindOrderBy(cmd, orderByClause);
cmd = AfterBindingOrderBy(scenario, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindPagination(cmd, page, pageSize);
}
}
return(cmd);
}
public virtual BangoCommand GetChildNodesCommand(DbConnect con, DynamicDictionary data_param, int?parent_id, string sort_by = null)
{
sort_by = sort_by == null ? string.Empty : sort_by;
if (sort_by.Length == 0 && TableDetail.OrderByField.Length > 0)
{
sort_by = DbServiceUtility.GetTableAliasForColumn(TableDetail.Alias) + TableDetail.OrderByField;
}
BangoCommand cmd = GetTreeNodeCommandTemplate(Convert.ToInt32(parent_id), false, TableDetail.Alias, sort_by);
return(GetSearchCommand(SearchScenario.ChildNodes, con, cmd, data_param, GetAllFields()
, sort_by, -1, -1, false, TableDetail.Alias));
}
//Client Name End
public virtual List <String> LoadAssignedRoles(DbConnect con, int user_id)
{
IEnumerable <DynamicDictionary> list = DbServiceUtility.ExecuteList(con, GetAllAssignedRolesQuery(user_id).ToString());
List <string> rights = new List <string>();
if (list != null)
{
foreach (DynamicDictionary dict in list)
{
rights.Add(dict.GetValueAsString("role_id"));
}
}
return(rights);
}
public virtual BangoCommand GetSearchItemsCommand(DbConnect con, DynamicDictionary data_param, int page, int pageSize, string sort_by = null, bool count = false)
{
sort_by = sort_by == null ? string.Empty : sort_by;
TableDetailAttribute tableDetail = _model.GetTableDetail();
if (sort_by.Trim().Length == 0 && tableDetail.OrderByField != null && tableDetail.OrderByField.Length > 0)
{
sort_by = DbServiceUtility.SetColumnAlias(tableDetail.Alias, tableDetail.OrderByField);
}
BangoCommand cmd = GetSearchCommandTemplate(_model.GetAllFields(TableDetail.Alias, false), count, TableDetail.Alias);
return(GetSearchCommand(SearchScenario.Search, con, cmd, data_param, GetAllFields()
, sort_by, page, pageSize, count, TableDetail.Alias));
}
protected virtual BangoCommand GetGridFilterItemsCommandTemplate(string selectClause, bool count = false, string tableAlias = null)
{
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format("SELECT count(1) total_records FROM {0} {1} /**where**/ ", _model.GetTableName(), alias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format("SELECT {0} FROM {1} {2} /**where**/ /**orderby**/"
, selectClause, _model.GetTableName(), alias));
}
return(cmd);
}
//Client name start
public virtual string LoadClientName(DbConnect con, int client_id)
{
DynamicDictionary list = DbServiceUtility.ExecuteItem(con, GetClientNameQuery(client_id).ToString());
if (list == null)
{
return(null);
}
var clientName = list.GetValueAsString("client_name");
if (clientName == null)
{
return(null);
}
return(clientName);
}
public virtual BangoCommand GetComboItemsCommand(DbConnect con, DynamicDictionary data_param, int page = -1, int pageSize = 20, string sort_by = null, bool count = false)
{
sort_by = sort_by == null ? string.Empty : sort_by;
ComboFieldsAttribute comboAttrib = _model.GetComoFields();
if (sort_by.Trim().Length == 0 && comboAttrib.OrderBy.Length > 0)
{
sort_by = DbServiceUtility.SetColumnAlias(TableDetail.Alias, comboAttrib.OrderBy);
}
BangoCommand cmd = GetCombotItemsCommandTemplate(comboAttrib.ToString(), count, TableDetail.Alias);
if (comboAttrib.Status != null || comboAttrib.Status.Length > 0)
{
data_param.Add(comboAttrib.Status, true); //for Combo Status=True mains Active Recors load in ComboBOx
}
cmd = GetSearchCommand(SearchScenario.Combo, con, cmd, data_param, comboAttrib.ToString()
, sort_by, page, pageSize, count, TableDetail.Alias);
//adding the query clause
string or_query = string.Empty;
Dapper.DynamicParameters param = new Dapper.DynamicParameters();
if (data_param.ContainsKey("query"))
{
string query = data_param.GetValueAsString("query");
if (query != null && query.Trim().Length > 0)
{
var q = query;
string[] arr = q.Split('-');
string searchParams = arr[0].ToString().Trim();
//string name = arr[1].ToString();
AppendQueryAsOr(cmd, searchParams, new string[] { "code", "name_np", "name_en" });
}
else
{
AppendQueryAsOr(cmd, query, new string[] { "code", "name_np", "name_en" });
}
//query = data_param.GetValueAsString("query");
}
return(cmd);
}
public virtual string LoadPhotoURL(DbConnect con, int user_id)
{
DynamicDictionary list = DbServiceUtility.ExecuteItem(con, GetPhotoPathQuery(user_id).ToString());
if (list == null)
{
return(null);
}
var photoPath = list.GetValueAsString("name_photo_url");
if (photoPath == null)
{
return(null);
}
string[] formatedPhotoPath = photoPath.Split('\\');
string absolutePhotoPath = string.Join("/", formatedPhotoPath);
return(absolutePhotoPath);
}
public override BangoCommand GetItemCommand(DynamicDictionary data_param, string tableAlias = null)
{
CheckClientID = false;
BangoCommand cmd = new BangoCommand();
UserModel mdl = new UserModel();
tableAlias = tableAlias ?? MainTableAlias;
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"select c.*,t1.photo_url name_photo_url,t1.id photo_id,t1.file_name userfilename
from rbac_user c
LEFT JOIN tax_photos_files t1 on (c.id = t1.ref_id)
/**where**/
AND t1.ref_id = @id
/**orderby**/"
, mdl.GetTableName(), tableAlias));
//PropertyInfo prop = mdl.GetKeyPropertyInfo();
//cmd.SqlBuilder.Where(string.Format("{0}=@{0}", prop.Name), DbServiceUtility.ToDynamicParameter(prop.Name, data_param.GetValueAsInt("id"), DbServiceUtility.TypeMap[prop.PropertyType]));
DbServiceUtility.BindDeleteParameter(cmd, mdl, tableAlias);
DbServiceUtility.BindParameters(cmd, mdl, data_param, tableAlias, SearchTypes.Equal);
if (CheckClientID)
{
PropertyInfo client_id = mdl.GetType().GetProperty("client_id");
if (client_id != null)
{
DbServiceUtility.BindClientIdParameter(cmd, mdl, tableAlias, DisplayMasterDataFromSystem);
//string col = DbServiceUtility.SetColumnAlias(tableAlias, "client_id");
//cmd.SqlBuilder.Where("(client_id = 1 OR client_id = @client_id)");
//DynamicParameters param = new DynamicParameters();
//param.Add("@client_id", SessionData.client_id, DbType.Int32);
//cmd.SqlBuilder.AddParameters(param);
}
}
return(cmd);
}
public override BangoCommand AfterBindingParameter(SearchScenario searchFor, DbConnect con, BangoCommand cmd, DynamicDictionary data_param, bool count = false, string tableAlias = null)
{
cmd = base.AfterBindingParameter(searchFor, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindParameters(cmd, new donation_request_links(), data_param, "r");
return(cmd);
}
public virtual BangoCommand GetTreeNodeCommandTemplate(int start_node_id, bool count = false, string tableAlias = null, string sort_by = null)
{
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias),
columnAlias = DbServiceUtility.GetTableAliasForColumn(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
string start_condition = string.Empty;
TModel model = new TModel();
string secondary_condition = string.Empty;
PropertyInfo delFlag = Models.ModelService.GetDeleteFieldProperty(model);
if (delFlag != null)
{
start_condition = "c." + delFlag.Name + " = false";
}
PropertyInfo field_client_id = model.GetType().GetProperty("client_id");
if (field_client_id != null)
{
start_condition += string.Format(" AND (c.client_id = 1 OR c.client_id = {0})", SessionData.client_id);
}
secondary_condition = start_condition;
if (start_node_id == 0)
{
start_condition += string.Format(" AND COALESCE(c.parent_id,0) = {0}", start_node_id);
}
else
{
start_condition += string.Format(" AND c.id = {0}", start_node_id);
}
if (count)
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT count(1) total_records
FROM {0} {1}
/**where**/ "
, _model.GetTableName(), alias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
WITH RECURSIVE nodes(id, code, name_np, name_en, parent_id
, node_path, parent_node_path, node_level) as(
SELECT
{3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id
--, null::text parent_code, null::text parent_name_np, null::text parent_name_en
--, c.parent_id, p.code parent_code, p.name_np parent_name_np, p.name_en parent_name_en
, ARRAY[{3}id], ARRAY[{3}id], 1
FROM {0} {2}
WHERE
{1}
UNION ALL
SELECT
{3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id
--, p.code parent_code, p.name_np parent_name_np, p.name_en parent_name_en
, node_path || c.id, node_path, nd.node_level + 1
FROM {0} {2}
JOIN {0} p ON c.parent_id = p.id
JOIN nodes nd ON p.id = nd.id
WHERE
{4}
)
SELECT array_to_string(node_path , '-') node_path, array_to_string(parent_node_path , '-') parent_node_path, id node_id, code, name_np
, name_en, parent_id, node_level , code || ' - ' || name_np || ' - ' || name_en as text
FROM nodes
order by node_path
/**where**/
/**orderby**/"
, _model.GetTableName(), start_condition, alias, columnAlias, secondary_condition));
}
return(cmd);
}
public ResponseAuth AuthenticateUserNamePasword(DbConnect con, int client_id, string username, string password)
{
ResponseAuth resp = new ResponseAuth();
string template = @"
SELECT id, client_id, username, password, name_en, name_np,email, status
FROM rbac_user u
/**where**/
AND u.status=true AND u.is_deleted=false";
//creating command & preparing command
string alias = DbServiceUtility.GetTableAliasForTable("u");
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
cmd.Template = cmd.SqlBuilder.AddTemplate(template);
UserModel mdl = new UserModel();
DbServiceUtility.BindDeleteParameter(cmd, mdl, alias);
DynamicDictionary data_param = new DynamicDictionary();
data_param.Add("client_id", client_id);
data_param.Add("username", username);
DbServiceUtility.BindParameters(cmd, mdl, data_param, alias, SearchTypes.Equal);
//executing the command
string finalSql = cmd.FinalSql;
if (finalSql.Length > 0)
{
IEnumerable <SqlMapper.DapperRow> items = null;
try
{
items = con.DB.Query <SqlMapper.DapperRow>(finalSql, cmd.FinalParameters, true);
}
catch (Npgsql.NpgsqlException ex)
{
LogTrace.WriteErrorLog(ex.ToString());
LogTrace.WriteDebugLog(string.Format("Select SQL which gave exception:\r{0}", ex.Routine));
}
Errors = con.DB.GetErros();
if (items != null && items.Count() > 0)
{
DynamicDictionary data = Conversion.ToDynamicDictionary(items.FirstOrDefault());
if (data.GetValueAsString("password") == EncryptPassword(password))
{
resp.success = true;
resp.user_id = data.GetValueAsInt("id");
resp.email = data.GetValueAsString("email");
resp.message = "Login successfull";
}
else
{
resp.message = "Username and/or Password is invalid.";
}
}
else
{
if (Errors.Count > 0)
{
resp.message = "Technical Problem occurred.";
}
else
{
resp.message = "Please provide a valid Username.";
}
}
}
return(resp);
}
public virtual bool SoftDelete(DbConnect con, TKey id)
{
Is_Child_Records_Exists = false; //Default child record off
//pull old data
TModel oldData = new TModel();
ITable <TModel, TKey> tbl = con.GetModelTable <TModel, TKey>();
oldData = tbl.Get(id);
//checking if the data is editable by current login or not
if (CheckClientID)
{
if (ValidateForClientData(oldData) == false)
{
return(false);
}
}
TableDetailAttribute tableDatail = oldData.GetTableDetail();
//09 feb 2016 Foreign key refrence table data exists or not checke..
#region todo:shivahwor
StringBuilder Sql = new StringBuilder();
Sql.AppendFormat(@"
SELECT
--tc.table_schema, tc.constraint_name,
tc.table_name, kcu.column_name
--,ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
AND ccu.table_name='{0}' ", tableDatail.Name);
IEnumerable <DynamicDictionary> foreignkey_Table = DbServiceUtility.ExecuteList(con, Sql.ToString());
Sql.Length = 0;
DynamicDictionary rec = null;
if (foreignkey_Table != null)
{
foreach (DynamicDictionary item in foreignkey_Table)
{
object tbl_Name = item.GetValue("table_name");
object col_name = item.GetValue("column_name");
if (Sql.Length > 0)
{
Sql.AppendLine("\n\t UNION ALL ");
}
Sql.AppendFormat(" SELECT 1 from {0} Where {1}={2} AND is_deleted ='F' ", tbl_Name.ToString(), col_name.ToString(), id);
}
rec = DbServiceUtility.ExecuteItem(con, Sql.ToString());
}
if (rec != null)
{
if (rec.KeyList.Count > 0)
{
Is_Child_Records_Exists = true; //if child records exists than do not remove...
return(false);
}
}
#endregion
//data = new TModel();
DynamicDictionary data_param = new DynamicDictionary();
ChangeHistoryHelper <TModel> chngHlpr = null;
chngHlpr = new ChangeHistoryHelper <TModel>(AuditActivityTypes.SOFTDELETE);
//if CREATED_BY, CREATED_on field exists then update those fields
PropertyInfo by = oldData.GetType().GetProperty("deleted_by");
if (by != null)
{
data_param.SetValue("deleted_by", SessionData.user_id);
//by.SetValue(data, Bango.GetCurrentUserId());
}
PropertyInfo on = oldData.GetType().GetProperty("deleted_on");
if (on != null)
{
//on.SetValue(data, DateTime.Now);
data_param.SetValue("deleted_on", DateTime.Now);
}
PropertyInfo uq_code = oldData.GetType().GetProperty("deleted_uq_code");
if (on != null)
{
//on.SetValue(data, DateTime.Now);
data_param.SetValue("deleted_uq_code", DateTime.Now.ToString("MMddHHmmss"));
}
PropertyInfo is_deleted = oldData.GetType().GetProperty(tableDatail.DeleteFlagField);
if (is_deleted != null)
{
//is_deleted.SetValue(data, true);
data_param.SetValue(tableDatail.DeleteFlagField, true);
}
chngHlpr.CheckChangeChanges(oldData, data_param);
//chngHlpr.Diff.Add("id", id, DbType.Int32, ParameterDirection.Input);
DynamicParameters where = new DynamicParameters();
where.Add("id", id);
if (CheckClientID)
{
PropertyInfo client_id = oldData.GetType().GetProperty("client_id");
if (client_id != null)
{
where.Add("client_id", SessionData.client_id);
}
}
try
{
int?savedId = tbl.Update(where, chngHlpr.Diff);
if (TrackChanges)
{
//save the changes
chngHlpr.LogChanges(con);
}
}
catch (Npgsql.NpgsqlException ex)
{
LogTrace.WriteErrorLog(ex.ToString());
LogTrace.WriteDebugLog(string.Format("SQL which gave exception:\r{0}", ex.Routine));
throw ex;
}
return(true);
}
public virtual bool Update(DbConnect con, TKey id, DynamicDictionary data)
{
//pull old data
TModel oldData = new TModel();
ITable <TModel, TKey> tbl = con.GetModelTable <TModel, TKey>();
oldData = tbl.Get(id);
//checking if the data is editable by current login or not
if (CheckClientID)
{
if (ValidateForClientData(oldData) == false)
{
return(false);
}
}
ChangeHistoryHelper <TModel> chngHlpr = null;
chngHlpr = new ChangeHistoryHelper <TModel>(AuditActivityTypes.UPDATE);
//if CREATED_BY, CREATED_on field exists then update those fields
PropertyInfo by = oldData.GetType().GetProperty("updated_by");
if (by != null)
{
data.SetValue("updated_by", SessionData.user_id);
}
PropertyInfo on = oldData.GetType().GetProperty("updated_on");
if (on != null)
{
data.SetValue("updated_on", DateTime.Now);
}
dynamic cloned = data.Clone();
chngHlpr.CheckChangeChanges(oldData, data);
//if no changes then return true
if (chngHlpr.Diff.ParameterNames.Count() == 0)
{
return(true);
}
int?savedId = null;
//chngHlpr.Diff.Add("id", id, DbType.Int32, ParameterDirection.Input);
DynamicParameters where = new DynamicParameters();
where.Add("id", id, DbServiceUtility.GetDbType(typeof(TKey)));
if (CheckClientID)
{
PropertyInfo client_id = oldData.GetType().GetProperty("client_id");
if (client_id != null)
{
where.Add("client_id", SessionData.client_id, DbServiceUtility.GetDbType(client_id.PropertyType));
}
}
try
{
savedId = tbl.Update(where, chngHlpr.Diff);
if (TrackChanges)
{
//save the changes
chngHlpr.LogChanges(con);
}
}
catch (Npgsql.NpgsqlException ex)
{
LogTrace.WriteErrorLog(ex.ToString());
LogTrace.WriteDebugLog(string.Format("SQL which gave exception:\r{0}", ex.Routine));
throw ex;
}
if (savedId > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Performs validation of unique-constraints. Detail on the failure are stored in UniqueErrors Property
/// It will use the help of TModel for performing the unique-constraint check.
/// </summary>
/// <param name="item"></param>
/// <returns>Returns the status of the validation.</returns>
public virtual bool ValidateUniqueValue(DbConnect con, DynamicDictionary item, IModel validatorModel, bool skipFieldsNotProvided = false)
{
/**
* TODO
* 1) disable null check or check if unique constraint is not composite constraint
* 2) check unique constraint in edit mode, load all data
**/
PropertyInfo key = validatorModel.GetKeyPropertyInfo();
TKey id = item.GetValue <TKey>(key.Name);
//if (Conversion.ToInt32(id.ToString()) > 0)
// skipFieldsNotProvided = false; //if edit mode checke
if (validatorModel.GetType().GetProperty("deleted_uq_code") != null) //tod not equal..
{
item["deleted_uq_code"] = 1;
}
BangoCommand cmd = new BangoCommand(commandType: MyroCommandTypes.StringBuilder);
string union = string.Empty;
//List<PropertyInfo> uniqueFields = new List<PropertyInfo>();
DictionaryFx <string, PropertyInfo> uniqueFields = new DictionaryFx <string, PropertyInfo>();
//preparing sql
DynamicDictionary data_param = null;
Bango.Models.Attributes.TableDetailAttribute tabelDetail = validatorModel.GetTableDetail();
foreach (KeyValuePair <string, UniqueConstraint> unique in validatorModel.UniqueFields)
{
if (unique.Value.Fields.Count == 0)
{
continue;
}
bool value_not_provided = false;
foreach (string fld in unique.Value.Fields)
{
if (!item.ContainsKey(fld))
{
//1) disable null check or check if unique constraint is not composite constraint
///if (unique.Value.Fields.Count <= 1)
if (unique.Value.Fields.Count <= 1) //TODO:Shivashwor modify... for client duplicate data insert OFF...
{
value_not_provided = true;
break;
}
if (!skipFieldsNotProvided)
{
//If fld name not exists in validatorModel then
if (validatorModel.GetValue(fld) == null)
{
///item.Add(fld, null);
value_not_provided = true;
}
else
{
Type t = validatorModel.GetType().GetProperty(fld).PropertyType;
if (t.IsValueType)
{
item.Add(fld, Activator.CreateInstance(t));
}
else
{
item.Add(fld, null);
}
}
}
else
{
//TODO:Shivashwor modify... for client duplicate data insert OFF...
value_not_provided = true;
}
break;
}
}
if (value_not_provided)
{
continue;
}
data_param = (DynamicDictionary)item.Clone();
///TODO:SHIVASHWOR 15 nov 2015 for Unique value is empty or not...
object data_val = data_param.GetValue(unique.Key);
if (data_val != null)
{
if (data_val.ToString().Trim().Length == 0)
{
continue;
}
}
if (union.Length > 0)
{
cmd.SqlString.AppendLine(union);
}
string and = string.Empty;
cmd.SqlString.AppendLine(String.Format("SELECT distinct '{0}' unique_constraint, '{2}' error_message FROM {1} {3} WHERE 1=1 "
, DbServiceUtility.SafeDBString(unique.Value.Name), tabelDetail.Name
, DbServiceUtility.SafeDBString(unique.Value.ErrorMessage)
, tabelDetail.Alias));
//CHECKING In if client_id exists in the model for adding the client_id in unique contraint check if the developer has forgot to added
PropertyInfo prop_client_id = validatorModel.GetType().GetProperty("client_id");
if (prop_client_id != null)
{
if (!unique.Value.Fields.Contains("client_id"))
{
unique.Value.Fields.Add("client_id");
}
}
foreach (string fld in unique.Value.Fields)
{
if (validatorModel.GetType().GetProperty(fld) != null)
{
DbServiceUtility.BindParameter(cmd, fld, data_param, validatorModel.GetType().GetProperty(fld).PropertyType, tabelDetail.Alias, SearchTypes.Equal | SearchTypes.CaseSensetive, string.Empty, true, validatorModel.GetType().GetProperty(fld));
}
//cmd.SqlString.AppendFormat(" {1} {0} = @{0}", fld, and);//uniqueFields[fld] = validatorModel.GetType().GetProperty(fld);
}
if (key.Name.Trim().Length > 0)//PRIMARY KEY Check if n
{
if (id != null)
{
//var obj_updateBy = data_param.GetValue("updated_by");
//if (obj_updateBy!= null)
DbServiceUtility.BindParameter(cmd, key.Name, data_param, System.Data.DbType.Int32, tabelDetail.Alias, SearchTypes.NotEqual, string.Empty, true, key);
}
}
union = " UNION ALL";
}
string finalSql = cmd.FinalSql;
IEnumerable <dynamic> lst = null;
if (finalSql.Length > 0)
{
try
{
lst = con.DB.Query <dynamic>(finalSql, cmd.FinalParameters);
}
catch (NpgsqlException ex)
{
Errors.Add(ex.ToString());
LogTrace.WriteErrorLog(ex.ToString());
LogTrace.WriteDebugLog(string.Format("sql which gave exception:\r{0}", ex.Routine));
return(false);
}
catch (Exception ex)
{
}
//checking for the unique constraint
if (lst.Count() > 0)
{
foreach (DapperRow dr in lst)
{
DynamicDictionary dic = Conversion.ToDynamicDictionary(dr);
DynamicDictionary err = new DynamicDictionary();
err.Add(dic.GetValueAsString("unique_constraint"), dic.GetValue("error_message"));
ModelService.PushValidationErros(err, ValidationErrors);
}
return(false);
}
}
else
{
//TODO:Shivashwor 01 Nov 2015/
//if edit mode nothing changed after save data occurs
// throw new NoSqlStringProvidedException();
}
return(true);
}
