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