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