public async Task <int> GetAllMemberInfoCountAsync(GetMemberListCmd cmd)
{
using (var conn = CreateConnection())
{
conn.Open();
var sql = "SELECT COUNT(*) FROM member_info WHERE ";
var demo = AllMemberInfoSql(cmd);
sql += demo.Sql;
return(await conn.ExecuteScalarAsync <int>(sql, demo.Dps).ConfigureAwait(false));
}
}
public async Task <IEnumerable <MemberInfo> > GetAllMemberInfoAsync(GetMemberListCmd cmd, PagedListQuery plQuery)
{
using (var conn = CreateConnection())
{
conn.Open();
var sql = "SELECT * FROM member_info WHERE ";
var demo = AllMemberInfoSql(cmd);
sql += demo.Sql;
sql += " ORDER BY created_on DESC ";
sql += plQuery.PostgresLimitPartialSql();
return(await conn.QueryAsync <MemberInfo>(sql, demo.Dps).ConfigureAwait(false));
}
}
private SqlAndDps AllMemberInfoSql(GetMemberListCmd cmd)
{
var paras = new DynamicParameters();
var sql = " 1=1 ";
if (cmd.Type.HasValue && (int)cmd.Type.Value != -1)
{
sql += " AND type = @type ";
paras.Add("type", cmd.Type.Value);
}
if (cmd.Level.HasValue)
{
sql += " AND levle = @level ";
paras.Add("level", cmd.Level.Value);
}
if (!cmd.RefereeId.IsNullOrWhiteSpace())
{
sql += " AND referee_id=@refereeId";
paras.Add("refereeId", cmd.RefereeId);
}
//if (cmd.AdministrativeDivisionsId.HasValue)
//{
// sql += " AND administrative_divisions_id=@administrativeDivisionsId";
// paras.Add("administrativeDivisionsId", cmd.AdministrativeDivisionsId);
//}
if (!cmd.Realname.IsNullOrEmpty())
{
sql += " AND realname LIKE @realname ";
paras.Add("realname", "%" + cmd.Realname + "%");
}
if (!cmd.MobilePhone.IsNullOrEmpty())
{
sql += " AND mobile_phone LIKE @mobilePhone ";
paras.Add("mobilePhone", "%" + cmd.MobilePhone + "%");
}
return(new SqlAndDps {
Sql = sql, Dps = paras
});
}
