// type = USER_NAME, HR_ID_C, AGENT_ID_C
public bool checkUserExist(string type, string value, string id)
{
DynamicParameters param = new DynamicParameters();
String sql = "Select count(ID) from vwUSERS where ID != @id";
if (type == "USER_NAME")
{
sql += " AND USER_NAME = @username";
param.Add("@username", value);
}
if (type == "HR_ID_C")
{
sql += " AND HR_ID_C = @HR_ID_C ";
param.Add("@HR_ID_C", value);
}
if (type == "AGENT_ID_C")
{
sql += " AND AGENT_ID_C = @AGENT_ID_C ";
param.Add("@AGENT_ID_C", value);
}
param.Add("@id", id);
int user = DapperORM.Count(sql, param);
return(user > 0);
}
public bool IsTeamExist(string ID, string NAME)
{
DynamicParameters param = new DynamicParameters();
string conditionSql = "";
string sql = "Select count(*) from vwTEAMS where 1 = 1";
if (ID == null)
{
return(false);
}
conditionSql += " and ID = @ID";
param.Add("@ID", new Guid(ID));
if (NAME != null)
{
conditionSql += " and NAME = @NAME";
param.Add("@NAME", NAME);
}
sql += conditionSql;
int total = DapperORM.Count(sql, param);
if (total != 0)
{
return(true);
}
else
{
return(false);
}
}
public bool IsTeamExist(string id, string name)
{
DynamicParameters param = new DynamicParameters();
StringBuilder conditionSql = new StringBuilder();
StringBuilder sql = new StringBuilder("Select count(*) from vwTEAMS where 1 = 1");
//if (string.IsNullOrEmpty(id))
//{
// return false;
//}
//conditionSql.Append(" and ID = @ID");
//param.Add("@ID", new Guid(id));
if (!string.IsNullOrEmpty(name))
{
conditionSql.Append(" and NAME = @NAME");
param.Add("@NAME", name);
}
sql.Append(conditionSql);
int total = DapperORM.Count(sql.ToString(), param);
if (total > 0)
{
return(true);
}
else
{
return(false);
}
}
public ResponseForm <int> CheckNameBranch(string ID, string NAME, string REGION_ID)
{
try
{
DynamicParameters param = new DynamicParameters();
string query = "Select count(*) from vwBRANCHS where 1 = 1 ";
if (!string.IsNullOrWhiteSpace(ID))
{
param.Add("@ID", new Guid(ID));
query += "and ID != @ID ";
}
if (!string.IsNullOrWhiteSpace(REGION_ID))
{
query += "and REGION_ID = @REGION_ID ";
param.Add("@REGION_ID", REGION_ID);
}
if (!string.IsNullOrWhiteSpace(NAME))
{
query += "and NAME like @NAME";
param.Add("@NAME", Security.Encode(NAME));
}
var branch = DapperORM.Count(query, param);
ResponseForm <int> response = new ResponseForm <int>(branch, "ok", 200);
return(response);
}
catch (Exception e)
{
ResponseForm <int> response = new ResponseForm <int>(-1, e.Message, 600);
return(response);
}
}
public Object GetAssignedTsr(string campaignId, string assignedUserId, int limit, int offset, string sortField, int sortType)
{
try
{
if (limit == 0)
{
limit = 10;
}
DynamicParameters dynamicParameter = new DynamicParameters();
StringBuilder sql = new StringBuilder(@" SELECT ID, AGENT_ID_C, TSR_GROUP_NAME, FULL_NAME, TOTAL_ASSIGNED, STATUS FROM vwTSR_PHAN_CONG WHERE 1 = 1 ");
StringBuilder conditionSql = new StringBuilder();
StringBuilder countSql = new StringBuilder("Select count(ID) from vwTSR_PHAN_CONG WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(campaignId))
{
conditionSql.Append(" AND CAMPAIGNS_ID = @CAMPAIGN_ID");
dynamicParameter.Add("@CAMPAIGN_ID", campaignId);
}
if (!string.IsNullOrEmpty(assignedUserId))
{
conditionSql.Append(" AND ID != @assignedUserId");
dynamicParameter.Add("@ID", assignedUserId);
}
conditionSql.Append(" AND STATUS != @STATUS");
dynamicParameter.Add("@STATUS", "Active");
countSql.Append(conditionSql);
// count total
int total = DapperORM.Count(countSql.ToString(), dynamicParameter);
sql.Append(conditionSql);
// ORDER
if (!string.IsNullOrEmpty(sortField))
{
sql.Append(" ORDER BY " + sortField);
sql.Append(sortType == 1 ? " DESC" : " ASC");
}
else
{
sql.Append(" ORDER BY FULL_NAME ASC");
}
sql.Append(" OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY");
Console.WriteLine(sql);
dynamicParameter.Add("@OFFSET", offset);
dynamicParameter.Add("@SIZE", limit);
List <Object> data = (List <object>)DapperORM.ExcecuteReturnList <object>(sql.ToString(), dynamicParameter);
return(new ResponseListForm <object>(data, new Pagination(limit, offset, total), "ok", 200));
}
catch (Exception e)
{
Console.WriteLine(e);
return(new { message = "SQL fail", code = 600, data = "" });
}
}
public object GetCallStatus(string NAME, string CALL_TYPE, string CAMPAIGN_CATEGORY_ID, string STATUS, string REGION_ID_C, string ORDERBY)
{
try
{
DynamicParameters param = new DynamicParameters();
String sql = "Select NAME, NOTES, ID, LIST_ORDER_C, CALL_TYPE, CAMPAIGN_CATEGORY_NAME, STATUS from vwCALL_CATEGORY WHERE 1 = 1 ";
String conditionSql = "";
String countSql = "Select count(*) from vwCALL_CATEGORY WHERE 1 = 1";
if (!string.IsNullOrEmpty(NAME))
{
conditionSql += " and NAME LIKE N'%' + @Name + '%'";
param.Add("@Name", Security.Encode(NAME));
}
if (!string.IsNullOrEmpty(CALL_TYPE))
{
conditionSql += " and CALL_TYPE LIKE '%' + @Call_Type + '%'";
param.Add("@Call_Type", Security.Encode(CALL_TYPE));
}
if (!string.IsNullOrEmpty(CAMPAIGN_CATEGORY_ID))
{
conditionSql += " and Campaign_Category_ID = N''+ @Campaign_Category_ID + '' ";
param.Add("@Campaign_Category_ID", CAMPAIGN_CATEGORY_ID);
}
if (!string.IsNullOrEmpty(STATUS))
{
conditionSql += " and Status = N'' + @Status + ''";
param.Add("@Status", STATUS);
}
if (!string.IsNullOrEmpty(REGION_ID_C))
{
conditionSql += " and Region_ID_C = N'' + @Region_ID_C + ''";
param.Add("@Region_ID_C", REGION_ID_C);
}
sql += conditionSql;
sql += " ORDER BY " + (!string.IsNullOrEmpty(ORDERBY) ? ORDERBY : " DATE_ENTERED DESC");
countSql += conditionSql;
int total = DapperORM.Count(countSql, param);
//sql += " OFFSET @OFFSET ROWS " +
// " FETCH NEXT @SIZE ROWS ONLY";
//param.Add("@OFFSET", (PAGE - 1) * SIZE);
//param.Add("@SIZE", SIZE);
var callCategories = DapperORM.ExcecuteReturnList <CallCategoryDto>(sql, param);
Pagination pagination = new Pagination(1, total, total);
return(new { data = callCategories, pagination = pagination });
}
catch (Exception ex)
{
return(new { data = new { Message = ex.Message, ErrorCode = 600 } });
}
}
public bool CheckUserNameExist(string username)
{
DynamicParameters param = new DynamicParameters();
param.Add("@username", username);
String sql = "Select count(ID) from USERS where USER_NAME = @username";
int user = DapperORM.Count(sql, param);
return(user > 0);
}
public Object GetAssignedCustomer(string campaignId, string assignedUserId, int limit, int offset, string sortField, int sortType)
{
try
{
if (limit == 0)
{
limit = 10;
}
DynamicParameters dynamicParameter = new DynamicParameters();
StringBuilder sql = new StringBuilder(@" SELECT ID, TEN_KH_C, GIOI_TINH_C, PHONE_MOBILE, NGAY_SINH_C, CMND_C , DIA_CHI_C, VUNG_C, ASSIGNED_USER_ID FROM vwPHAN_CONG_CONTACT WHERE 1 = 1 ");
StringBuilder conditionSql = new StringBuilder();
StringBuilder countSql = new StringBuilder("Select count(ID) from vwPHAN_CONG_CONTACT WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(campaignId))
{
conditionSql.Append(" AND CAMPAIGN_ID = @CAMPAIGN_ID");
dynamicParameter.Add("@CAMPAIGN_ID", campaignId);
}
if (!string.IsNullOrEmpty(assignedUserId))
{
conditionSql.Append(" AND ASSIGNED_USER_ID = @assignedUserId");
dynamicParameter.Add("@ASSIGNED_USER_ID", assignedUserId);
}
conditionSql.Append(" AND ASSIGNED_TO IS NOT NULL");
countSql.Append(conditionSql);
// count total
int total = DapperORM.Count(countSql.ToString(), dynamicParameter);
sql.Append(conditionSql);
// ORDER
if (!string.IsNullOrEmpty(sortField))
{
sql.Append(" ORDER BY " + sortField);
sql.Append(sortType == 1 ? " DESC" : " ASC");
}
else
{
sql.Append(" ORDER BY DATE_ENTERED ASC");
}
sql.Append(" OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY");
Console.WriteLine(sql);
dynamicParameter.Add("@OFFSET", offset);
dynamicParameter.Add("@SIZE", limit);
List <Object> data = (List <object>)DapperORM.ExcecuteReturnList <object>(sql.ToString(), dynamicParameter);
return(new ResponseListForm <object>(data, new Pagination(limit, offset, total), "ok", 200));
}
catch (Exception e)
{
Console.WriteLine(e);
return(new { message = "SQL fail", code = 600, data = "" });
}
}
public Object FindTeam(string name, string campaignCategoryIdC, string regionIdC, int limit, int offset, string sortField, int sortType)
{
//1: desc ,
//default date enter desc
DynamicParameters param = new DynamicParameters();
StringBuilder sql = new StringBuilder("Select * from vwTEAMS where 1 = 1");
StringBuilder conditionSql = new StringBuilder();
StringBuilder countSql = new StringBuilder("Select count(*) from vwTEAMS WHERE 1 = 1");
if (!string.IsNullOrEmpty(regionIdC))
{
conditionSql.Append(" and REGION_ID_C = @REGION_ID_C");
param.Add("@REGION_ID_C", regionIdC);
}
if (!string.IsNullOrEmpty(name))
{
conditionSql.Append(" and NAME LIKE @NAME");
param.Add("@NAME", "%" + Security.Encode(name) + "%");
}
if (!string.IsNullOrEmpty(campaignCategoryIdC))
{
conditionSql.Append(" and CAMPAIGNS_CATEGORY_ID LIKE @CAMPAIGN_CATEGORY_ID_C");
param.Add("@CAMPAIGN_CATEGORY_ID_C", "%" + campaignCategoryIdC + "%");
}
countSql.Append(conditionSql);
sql.Append(conditionSql);
if (string.IsNullOrEmpty(sortField))
{
sql.Append(" order by DATE_ENTERED DESC");
}
else
{
sql.Append(" order by " + sortField + (sortType == 1 ? " DESC" : " ASC"));
}
sql.Append(" OFFSET @OFFSET ROWS FETCH NEXT @LIMIT ROWS ONLY");
param.Add("@OFFSET", offset);
param.Add("@LIMIT", limit);
int total = DapperORM.Count(countSql.ToString(), param);
var teams = DapperORM.ExcecuteReturnList <TeamDto>(sql.ToString(), param);
Pagination pagination = new Pagination(limit, offset, total);
return(new { message = "", data = teams, pagination = pagination });
}
// Api lấy danh sách chỉ tiêu nhóm gộp cùng api lấy chi tiết chỉ tiêu nhóm
public object GetTeamTargetList(GetTeamTargetListInput input)
{
try
{
DynamicParameters param = new DynamicParameters();
String sql = "Select ID, YEAR, NAME_CAMPAIGN_CATEGORY, TEAM_NAME, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9 ,MONTH_10 ,MONTH_11 ,MONTH_12, TOTAL, CAMPAIGN_CATEGORY_ID, TEAM_ID from vwTEAM_TARGET_List WHERE 1 = 1 ";
String conditionSql = "";
String countSql = "Select count(*) from vwTEAM_TARGET_List WHERE 1 = 1";
if (!string.IsNullOrEmpty(input.CAMPAIGN_CATEGORY_ID))
{
conditionSql += " and CAMPAIGN_CATEGORY_ID = @CAMPAIGN_CATEGORY_ID";
param.Add("@CAMPAIGN_CATEGORY_ID", input.CAMPAIGN_CATEGORY_ID);
}
if (!string.IsNullOrEmpty(input.NAME_CAMPAIGN_CATEGORY))
{
conditionSql += " and NAME_CAMPAIGN_CATEGORY = @NAME_CAMPAIGN_CATEGORY";
param.Add("@NAME_CAMPAIGN_CATEGORY", input.NAME_CAMPAIGN_CATEGORY);
}
if (!string.IsNullOrEmpty(input.TEAM_ID))
{
conditionSql += " and TEAM_ID = @TEAM_ID";
param.Add("@TEAM_ID", input.TEAM_ID);
}
if (!string.IsNullOrEmpty(input.TEAM_NAME))
{
conditionSql += " and TEAM_NAME = @TEAM_NAME";
param.Add("@TEAM_NAME", input.TEAM_NAME);
}
sql += conditionSql;
sql += " ORDER BY " + (!string.IsNullOrEmpty(input.ORDERBY) ? input.ORDERBY : " YEAR DESC");
countSql += conditionSql;
int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", (input.PAGE - 1) * input.SIZE);
param.Add("@SIZE", input.SIZE);
var callCategories = DapperORM.ExcecuteReturnList <TeamTargetList>(sql, param);
Pagination pagination = new Pagination(input.PAGE, input.SIZE, total);
return(new { data = callCategories, pagination = pagination });
}
catch (Exception ex)
{
return(new { data = new { Message = ex.Message, ErrorCode = 500 } });
}
}
public ResponseListForm <BranchDto> Search(string name, string short_name, string rank, string region_group_c, int limit, int offset)
{
DynamicParameters param = new DynamicParameters();
string sql = "Select ID, NAME, SHORT_NAME, REGION_GROUP_NAME, RANK, DATE_ENTERED, CREATED_BY from vwBRANCHS where 1 = 1 ";
string countSql = "Select count(*) from vwBRANCHS where 1 = 1 ";
if (!string.IsNullOrEmpty(name))
{
param.Add("@name", "%" + name + "%");
sql += "and NAME like @name ";
countSql += "and NAME like @name ";
}
if (!string.IsNullOrEmpty(short_name))
{
param.Add("@short_name", "%" + short_name + "%");
sql += "and SHORT_NAME like @short_name ";
countSql += "and SHORT_NAME like @short_name ";
}
if (!string.IsNullOrEmpty(rank))
{
param.Add("@rank", rank);
sql += "and RANK like @rank ";
countSql += "and RANK like @rank ";
}
if (!string.IsNullOrEmpty(region_group_c))
{
param.Add("@region_group_c", region_group_c);
countSql += "and REGION_GROUP_C like @region_group_c ";
sql += "and REGION_GROUP_C like @region_group_c ";
}
sql += "ORDER BY DATE_ENTERED DESC";
//int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@SIZE", limit);
//Console.WriteLine(sql);
//Console.WriteLine(countSql);
var branch = DapperORM.ExcecuteReturnList <BranchDto>(sql, param);
int total = DapperORM.Count(countSql, param);
Pagination pagination = new Pagination(limit, offset, total);
ResponseListForm <BranchDto> response = new ResponseListForm <BranchDto>(branch, pagination);
return(response);
}
public object GetCallReason(string CALL_RESULT_ID, string REGION_ID_C, string STATUS, string NAME, string ORDERBY)
{
try
{
DynamicParameters param = new DynamicParameters();
String sql = "SELECT NAME, DO_NOT_CALL_LIST, REASON_TYPE, CALLBACK, FOLLOW_UP, CIC, DURATION, NOTES, ID, NEXT_STEP_C, RECALL_TIME_C, STATUS, CAMPAIGN_CATEGORY_ID, CAMPAIGN_CATEGORY_NAME FROM vwCALL_REASON WHERE 1 = 1 ";
String conditionSql = "";
String countSql = "SELECT COUNT(*) FROM VWCALL_REASON WHERE 1 = 1";
if (!string.IsNullOrEmpty(CALL_RESULT_ID))
{
conditionSql += " and CALL_RESULT_ID = N'' + @CALL_RESULT_ID + ''";
param.Add("@CALL_RESULT_ID", CALL_RESULT_ID);
}
if (!string.IsNullOrEmpty(REGION_ID_C))
{
conditionSql += " and REGION_ID_C = N'' + @REGION_ID_C + ''";
param.Add("@REGION_ID_C", REGION_ID_C);
}
if (!string.IsNullOrEmpty(STATUS))
{
conditionSql += " and STATUS =N'' + @STATUS +''";
param.Add("@STATUS", STATUS);
}
if (!string.IsNullOrEmpty(NAME))
{
conditionSql += " and NAME =N'' + @NAME +''";
param.Add("@NAME", NAME);
}
sql += conditionSql;
sql += " ORDER BY " + (!string.IsNullOrEmpty(ORDERBY) ? ORDERBY : " DATE_ENTERED ASC");
countSql += conditionSql;
//sql += " OFFSET @OFFSET ROWS " +
// " FETCH NEXT @SIZE ROWS ONLY";
//param.Add("@OFFSET", (PAGE - 1) * SIZE);
//param.Add("@SIZE", SIZE);
var callCategories = DapperORM.ExcecuteReturnList <CallReasonDto>(sql, param);
int total = DapperORM.Count(countSql, param);
Pagination pagination = new Pagination(1, total, total);
return(new { data = callCategories, pagination = pagination });
}
catch (Exception ex)
{
return(new { data = new { Message = ex.Message, ErrorCode = 600 } });
}
}
//public ResponseListForm<TemplateListDto> GetCache(string LIST_NAME, string LANG)
//{
// try
// {
// DynamicParameters param = new DynamicParameters();
// String sql = "SELECT NAME,DISPLAY_NAME,LIST_ORDER FROM vwTERMINOLOGY_List ";
// if (!string.IsNullOrEmpty(LIST_NAME))
// {
// sql += "where LIST_NAME = @LIST_NAME ";
// param.Add("@LIST_NAME", LIST_NAME);
// }
// if (!string.IsNullOrEmpty(LANG))
// {
// sql += "where LANG = @LANG ";
// param.Add("@LANG", LANG);
// }
// sql += "order by STT asc";
// var data = DapperORM.ExcecuteReturnList<TemplateListDto>(sql, param);
// return new ResponseListForm<TemplateListDto>(data, new Pagination());
// }
// catch (Exception e)
// {
// return new ResponseListForm<TemplateListDto>(null, new Pagination(), e.Message, 600);
// }
//}
public ResponseForm <int> CheckCampaignProgress(string ID)
{
try
{
string query = "Select count(*) from vwCAMPAIGNS where STATUS = 'Active' and CALLMODE_C = 'Progressive' and CAMPAIGN_CATEGORY_ID_C = '" + ID + "'";
//Console.WriteLine(NAME + " " + ID + " " + REGION_ID);
int branch = DapperORM.Count(query);
ResponseForm <int> response = new ResponseForm <int>(branch, "ok", 200);
return(response);
}
catch (Exception e)
{
ResponseForm <int> response = new ResponseForm <int>(-1, e.Message, 600);
return(response);
}
}
public object GetCallResultFollow(string CALL_CATEGORY_ID, string REGION_ID_C, string STATUS, string ORDERBY)
{
try
{
DynamicParameters param = new DynamicParameters();
String sql = "Select ID, NAME, NOTES, EVENT_DATE_C, NEXT_RESULT_LIST_NAME, NEXT_RESULT_LIST_ID, STATUS from vwCALL_RESULT WHERE 1 = 1 ";
String conditionSql = "";
String countSql = "Select count(*) from vwCALL_RESULT WHERE 1 = 1";
if (!string.IsNullOrEmpty(CALL_CATEGORY_ID))
{
conditionSql += " and CALL_CATEGORY_ID = N'' + @CALL_CATEGORY_ID + ''";
param.Add("@CALL_CATEGORY_ID", CALL_CATEGORY_ID);
}
if (!string.IsNullOrEmpty(REGION_ID_C))
{
conditionSql += " and REGION_ID_C = N'' + @REGION_ID_C +''";
param.Add("@REGION_ID_C", REGION_ID_C);
}
if (!string.IsNullOrEmpty(STATUS))
{
conditionSql += " and STATUS = N'' + @STATUS + ''";
param.Add("@STATUS", STATUS);
}
sql += conditionSql;
sql += " ORDER BY " + (!string.IsNullOrEmpty(ORDERBY) ? ORDERBY : " DATE_ENTERED ASC");
countSql += conditionSql;
int total = DapperORM.Count(countSql, param);
//sql += " OFFSET @OFFSET ROWS " +
// " FETCH NEXT @SIZE ROWS ONLY";
//param.Add("@OFFSET", (PAGE - 1) * SIZE);
//param.Add("@SIZE", SIZE);
var callCategories = DapperORM.ExcecuteReturnList <CallReultFollowDto>(sql, param);
Pagination pagination = new Pagination(1, 10, total);
return(new { data = callCategories, pagination = pagination });
}
catch (Exception ex)
{
return(new { data = new { Message = ex.Message, ErrorCode = 600 } });
}
}
public bool CheckTsrInCharge(string id)
{
DynamicParameters param = new DynamicParameters();
param.Add("@ID", id);
String sql = "select count(vwPHAN_CONG.ID) " +
" from vwPHAN_CONG left join CAMPAIGNS " +
" on vwPHAN_CONG.CAMPAIGN_ID = CAMPAIGNS.ID " +
" where vwPHAN_CONG.ASSIGNED_USER_ID = @ID and " +
"(CAMPAIGNS.STATUS = 'Open' or CAMPAIGNS.STATUS = 'Active' or CAMPAIGNS.STATUS = 'Pause')" +
" and CAMPAIGNS.START_DATE < CURRENT_TIMESTAMP" +
" and(FIRST_INTERACTION_ID is null or IS_RECALL = 1); ";
int user = DapperORM.Count(sql, param);
Console.WriteLine("VBB" + user);
return(user > 0);
}
public Object GetListTsr(string tsrGroupId, string campaignId, int limit, int offset)
{
try
{
if (limit == 0)
{
limit = 10;
}
DynamicParameters parameters = new DynamicParameters();
StringBuilder sql = new StringBuilder(@"SELECT ID, AGENT_ID_C, TSR_GROUP_NAME, FULL_NAME,
TOTAL_ASSIGNED, STATUS FROM vwTSR_PHAN_CONG WHERE 1 = 1 ");
StringBuilder conditionSql = new StringBuilder();
StringBuilder countSql = new StringBuilder("Select count(ID) from vwTSR_PHAN_CONG WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(campaignId))
{
parameters.Add("@CAMPAIGNS_ID", new Guid(campaignId));
conditionSql.Append(" AND CAMPAIGNS_ID = @CAMPAIGNS_ID");
}
if (!string.IsNullOrEmpty(tsrGroupId))
{
parameters.Add("@TSR_GROUP_ID", tsrGroupId);
conditionSql.Append(" AND TSR_GROUP_ID = @TSR_GROUP_ID");
}
parameters.Add("@STATUS", "Active");
conditionSql.Append(" AND STATUS = @STATUS");
countSql.Append(conditionSql);
sql.Append(conditionSql);
int total = DapperORM.Count(countSql.ToString(), parameters);
sql.Append(" ORDER BY FULL_NAME ASC");
sql.Append(" OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY");
parameters.Add("@OFFSET", offset);
parameters.Add("@SIZE", limit);
List <Object> data = (List <object>)DapperORM.ExcecuteReturnList <object>(sql.ToString(), parameters);
return(new ResponseListForm <object>(data, new Pagination(limit, offset, total), "ok", 200));
} catch (Exception e)
{
Console.WriteLine(e);
return(new { message = "SQL fail", code = 600, data = "" });
}
}
public bool CheckTitleExistTsr(string userTypeC, string userTitleIdC)
{
string sql = "select count(ID) from vwUSERS where USER_TYPE_C = @userTypeC and USER_TITLE_ID_C = @userTitleIdC";
DynamicParameters param = new DynamicParameters();
param.Add("@userTypeC", userTypeC);
param.Add("@userTitleIdC", userTitleIdC);
int total = DapperORM.Count(sql, param);
Console.WriteLine(total);
if (total > 0)
{
return(true);
}
else
{
return(false);
}
}
public Object FindTeam(string NAME, string CAMPAIGN_CATEGORY_ID_C, string REGION_ID_C, int limit, int offset)
{
DynamicParameters param = new DynamicParameters();
string sql = "Select * from vwTEAMS where 1 = 1";
String conditionSql = "";
string countSql = "Select count(*) from vwTEAMS WHERE 1 = 1";
if (REGION_ID_C != null)
{
conditionSql += " and REGION_ID_C = @REGION_ID_C";
param.Add("@REGION_ID_C", REGION_ID_C);
}
if (NAME != null)
{
conditionSql += " and NAME LIKE @NAME";
param.Add("@NAME", NAME);
}
if (CAMPAIGN_CATEGORY_ID_C != null)
{
conditionSql += " and CAMPAIGN_CATEGORY_ID_C LIKE @CAMPAIGN_CATEGORY_ID_C";
param.Add("@CAMPAIGN_CATEGORY_ID_C", CAMPAIGN_CATEGORY_ID_C);
}
sql += conditionSql;
sql += " order by DATE_ENTERED ASC";
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @LIMIT ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@LIMIT", limit);
countSql += conditionSql;
int total = DapperORM.Count(countSql, param);
var teams = DapperORM.ExcecuteReturnList <TeamDto>(sql, param);
Pagination pagination = new Pagination(limit, offset, total);
return(new { message = "", data = teams, pagination = pagination });
}
public bool IsDuplicateNameTitle(string id, string name, string region)
{
DynamicParameters param = new DynamicParameters();
string sql = "Select count(ID) from vwTITLE where NAME = @name and REGION_ID_C = @region;";
param.Add("@id", new Guid(id));
param.Add("@name", name);
param.Add("@region", region);
int total = DapperORM.Count(sql.ToString(), param);
if (total > 0)
{
return(true);
}
else
{
return(false);
}
}
public ResponseForm <int> DeleteRole(string ID, string USER_ID)
{
try
{
DynamicParameters param = new DynamicParameters();
param.Add("@ID", ID);
string sql = @"update ROLES_TLS";
sql += " set DELETED=1, MODIFIED_USER_ID=@MODIFIED_USER_ID,DATE_MODIFIED=@DATE_MODIFIED where ID=@ID";
param.Add("@DATE_MODIFIED", DateTime.Now);
param.Add("@MODIFIED_USER_ID", USER_ID);
var role = DapperORM.Count(sql, param);
ResponseForm <int> response = new ResponseForm <int>(role, "ok", 200);
return(response);
}
catch
{
ResponseForm <int> response = new ResponseForm <int>(-1, "fail", 500);
return(response);
}
}
public ResponseForm <int> Delete(string ID, string USER_ID)
{
try
{
DynamicParameters param = new DynamicParameters();
param.Add("@ID", new Guid(ID));
string sql = @"update BRANCHS";
sql += " set DELETED=1, MODIFIED_USER_ID=@MODIFIED_USER_ID,DATE_MODIFIED=@DATE_MODIFIED where ID=@ID";
param.Add("@DATE_MODIFIED", DateTime.Now);
param.Add("@MODIFIED_USER_ID", USER_ID);
var branch = DapperORM.Count(sql, param);
ResponseForm <int> response = new ResponseForm <int>(branch, "ok", 200);
return(response);
}
catch (Exception e)
{
ResponseForm <int> response = new ResponseForm <int>(-1, e.Message, 600);
return(response);
}
}
// type = USER_NAME, HR_ID_C, AGENT_ID_C
public bool CheckUserExist(string type, string value, string id)
{
try
{
DynamicParameters param = new DynamicParameters();
String sql = "Select count(ID) from vwUSERS WHERE 1 = 1";
if (type == "USER_NAME")
{
sql += " AND USER_NAME = @username";
param.Add("@username", value);
}
if (type == "HR_ID_C")
{
sql += " AND HR_ID_C = @HR_ID_C ";
param.Add("@HR_ID_C", value);
}
if (type == "AGENT_ID_C")
{
sql += " AND AGENT_ID_C = @AGENT_ID_C ";
param.Add("@AGENT_ID_C", value);
}
if (!string.IsNullOrEmpty(id))
{
sql += " AND ID != @id";
param.Add("@id", new Guid(id));
}
Console.WriteLine(sql);
int user = DapperORM.Count(sql, param);
Console.WriteLine("user" + user);
return(user > 0);
}
catch (Exception e)
{
throw e;
}
}
public Object FindUserForTeam(string agentId, string username, string region, int limit, int offset, string sortField, int sortType)
{
try
{
DynamicParameters param = new DynamicParameters();
String sql = "Select ID,HR_ID_C, USER_TYPE_C, USER_NAME, EMAIL1, STATUS," +
" USER_TITLE_ID_C, IS_ADMIN, AGENT_ID_C, GROUP_ID_C, ACCOUNT_NUMBER_C, NAME_USERTITLE, " +
"NAME_TEAMS, FULL_NAME, PHONE_MOBILE, NAME_REGION, NAME_USER_TYPE_C,ROLE_NAME from vwUSERS where 1 = 1 ";
String conditionSql = "";
String countSql = "Select count(ID) from vwUSERS WHERE 1 = 1";
if (!string.IsNullOrEmpty(agentId))
{
conditionSql += " and AGENT_ID_C LIKE @AGENT_ID_C";
param.Add("@AGENT_ID_C", "%" + Security.Encode(agentId) + "%");
}
if (!string.IsNullOrEmpty(username))
{
conditionSql += " and USER_NAME LIKE @USER_NAME";
param.Add("@USER_NAME", "%" + Security.Encode(username) + "%");
}
if (!string.IsNullOrEmpty(region))
{
conditionSql += " and REGION_ID_C = @REGION";
param.Add("@REGION", region);
}
conditionSql += @" AND GROUP_ID_C IS NULL AND USER_TYPE_C IN(
@USER_TYPE1, @USER_TYPE2) AND STATUS = @STATUS";
param.Add("@USER_TYPE1", new Guid("CFB11D62-8358-434C-91E2-A0317EF32E15"));
param.Add("@USER_TYPE2", new Guid("DD330A41-F968-4D3F-A923-0F33C75C29D6"));
param.Add("@STATUS", "Active");
sql += conditionSql;
if (!string.IsNullOrEmpty(sortField))
{
string[] whiteListSort =
{
"ID", "HR_ID_C", "USER_TYPE_C", "USER_NAME", "EMAIL1", "STATUS",
"USER_TITLE_ID_C", "IS_ADMIN", "ACCOUNT_NUMBER_C", "NAME_USERTITLE", "NAME_TEAMS",
"FULL_NAME", "PHONE_MOBILE", "NAME_REGION", "NAME_USER_TYPE_C", "ROLE_NAME", "AGENT_ID_C"
};
if (Array.IndexOf(whiteListSort, sortField) > 0)
{
sql += " order by " + sortField;
sql += sortType == 1 ? " DESC" : " ASC";
}
}
else
{
sql += " order by DATE_ENTERED DESC";
}
countSql += conditionSql;
// count total
Console.WriteLine(sql);
int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@SIZE", limit);
// Get list user
Console.WriteLine(sql);
var user = DapperORM.ExcecuteReturnList <UserDetailDto>(sql, param);
Console.WriteLine("query done");
Pagination pagination = new Pagination(limit, offset, total);
return(new { data = user, pagination = pagination, message = "ok", code = 200 });
}
catch (Exception e)
{
Console.WriteLine(e);
return(new { data = "", code = 600, message = "ok" });
}
}
public Object findUser(string AGENT_ID_C, string STATUS, string GROUP_ID_C,
string USER_TITLE_ID_C, string FULL_NAME, string USER_NAME, string REGION, string EMAIL1, int limit, int offset)
{
try
{
Console.WriteLine("AAA" + ClaimTypes.NameIdentifier);
}
catch (Exception e)
{
Console.WriteLine("LOIX: " + e);
}
DynamicParameters param = new DynamicParameters();
String sql = "Select ID,HR_ID_C, USER_TYPE_C, USER_NAME, EMAIL1, STATUS," +
" USER_TITLE_ID_C, IS_ADMIN, AGENT_ID_C, GROUP_ID_C, ACCOUNT_NUMBER_C, NAME_USERTITLE, " +
"NAME_TEAMS, FULL_NAME, PHONE_MOBILE, NAME_REGION, NAME_USER_TYPE_C,ROLE_NAME from vwUSERS where 1 = 1 ";
String conditionSql = "";
String countSql = "Select count(ID) from vwUSERS WHERE 1 = 1";
if (AGENT_ID_C != null)
{
conditionSql += " and AGENT_ID_C LIKE @AGENT_ID_C";
param.Add("@AGENT_ID_C", "%" + AGENT_ID_C + "%");
}
if (STATUS != null)
{
conditionSql += " and STATUS = @STATUS";
param.Add("@STATUS", STATUS);
}
if (GROUP_ID_C != null)
{
conditionSql += " and GROUP_ID_C = @GROUP_ID_C";
param.Add("@GROUP_ID_C", GROUP_ID_C);
}
if (USER_TITLE_ID_C != null)
{
conditionSql += " and USER_TITLE_ID_C = @USER_TITLE_ID_C";
param.Add("@USER_TITLE_ID_C", USER_TITLE_ID_C);
}
if (FULL_NAME != null)
{
conditionSql += " and FULL_NAME LIKE @FULL_NAME";
param.Add("@FULL_NAME", "%" + FULL_NAME + "%");
}
if (USER_NAME != null)
{
conditionSql += " and USER_NAME LIKE @USER_NAME";
param.Add("@USER_NAME", "%" + USER_NAME + "%");
}
if (REGION != null)
{
conditionSql += " and REGION = @REGION";
param.Add("@REGION", REGION);
}
if (EMAIL1 != null)
{
conditionSql += " and EMAIL1 = @EMAIL1";
param.Add("@EMAIL1", EMAIL1);
}
sql += conditionSql;
sql += " order by DATE_ENTERED DESC";
countSql += conditionSql;
// count total
Console.WriteLine(countSql);
int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@SIZE", limit);
// Get list user
Console.WriteLine(sql);
var user = DapperORM.ExcecuteReturnList <UserDetailDto>(sql, param);
Console.WriteLine("query done");
Pagination pagination = new Pagination(limit, offset, total);
return(new { data = user, pagination = pagination });
}
public ResponseListForm <BranchDto> Search(string NAME, string SHORT_NAME, string RANK, string REGION_GROUP_C, string REGION_ID, string orderby, string sort, int limit = 10, int offset = 0)
{
try
{
if (limit <= 0)
{
limit = 10;
}
if (offset < 0)
{
offset = 0;
}
DynamicParameters param = new DynamicParameters();
string sql = "Select ID, NAME, SHORT_NAME, REGION_GROUP_NAME, RANK, DATE_ENTERED, CREATED_BY from vwBRANCHS where 1 = 1 ";
string countSql = "Select count(*) from vwBRANCHS where 1 = 1 ";
if (!string.IsNullOrEmpty(NAME))
{
param.Add("@NAME", "%" + Security.Encode(NAME) + "%");
sql += "and NAME like @NAME ";
countSql += "and NAME like @NAME ";
}
if (!string.IsNullOrEmpty(SHORT_NAME))
{
param.Add("@SHORT_NAME", "%" + Security.Encode(SHORT_NAME) + "%");
sql += "and SHORT_NAME like @SHORT_NAME ";
countSql += "and SHORT_NAME like @SHORT_NAME ";
}
if (!string.IsNullOrEmpty(RANK))
{
param.Add("@RANK", Security.Encode(RANK));
sql += "and RANK like @RANK ";
countSql += "and RANK like @RANK ";
}
if (!string.IsNullOrEmpty(REGION_GROUP_C))
{
param.Add("@REGION_GROUP_C", REGION_GROUP_C);
countSql += "and REGION_GROUP_C like @REGION_GROUP_C ";
sql += "and REGION_GROUP_C like @REGION_GROUP_C ";
}
if (!string.IsNullOrEmpty(REGION_ID))
{
param.Add("@REGION_ID", REGION_ID);
countSql += "and REGION_ID like @REGION_ID ";
sql += "and REGION_ID like @REGION_ID ";
}
if (!string.IsNullOrWhiteSpace(orderby))
{
sql += "ORDER BY " + orderby;
if (!string.IsNullOrWhiteSpace(sort))
{
sql += " " + sort;
}
}
else
{
sql += "ORDER BY DATE_ENTERED DESC";
}
//int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@SIZE", limit);
//Console.WriteLine(sql);
//Console.WriteLine(countSql);
var branch = DapperORM.ExcecuteReturnList <BranchDto>(sql, param);
int total = DapperORM.Count(countSql, param);
Pagination pagination = new Pagination(limit, offset, total);
ResponseListForm <BranchDto> response = new ResponseListForm <BranchDto>(branch, pagination);
return(response);
}
catch (Exception e)
{
ResponseListForm <BranchDto> response = new ResponseListForm <BranchDto>(null, new Pagination(), e.Message, 600);
return(response);
}
}
public ResponseListForm <object> SearchDoNotCallLists(string CAMPAIGN_CATEGORY_ID, string CUSTOMER_ID, string PHONE_NUMBER, string CALL_REASON_ID, string CUSTOMER_NAME, string orderby, string sort, int limit = 10, int offset = 0)
{
try
{
if (limit <= 0)
{
limit = 10;
}
if (offset < 0)
{
offset = 0;
}
DynamicParameters param = new DynamicParameters();
string sql = "Select ID, PHONE_NUMBER, CUSTOMER_ID, CUSTOMER_NAME, DATE_ENTERED, AGENT_NAME,NAME_CALL_REASON, CAMPAIGN_CATEGORY_NAME,DUE_DATE from vwDO_NOT_CALL_LIST where 1 = 1 ";
string countSql = "Select count(*) from vwDO_NOT_CALL_LIST where 1 = 1 ";
if (!string.IsNullOrWhiteSpace(CAMPAIGN_CATEGORY_ID))
{
param.Add("@CAMPAIGNS_CATEGORY_ID", CAMPAIGN_CATEGORY_ID);
sql += "AND CAMPAIGNS_CATEGORY_ID = @CAMPAIGNS_CATEGORY_ID ";
countSql += "and CAMPAIGNS_CATEGORY_ID = @CAMPAIGNS_CATEGORY_ID ";
}
if (!string.IsNullOrWhiteSpace(CUSTOMER_ID))
{
param.Add("@CUSTOMER_ID", CUSTOMER_ID);
sql += "and CUSTOMER_ID = @CUSTOMER_ID ";
countSql += "and CUSTOMER_ID = @CUSTOMER_ID ";
}
if (!string.IsNullOrWhiteSpace(PHONE_NUMBER))
{
param.Add("@PHONE_NUMBER", PHONE_NUMBER);
sql += "and PHONE_NUMBER = @PHONE_NUMBER ";
countSql += "and PHONE_NUMBER = @PHONE_NUMBER ";
}
if (!string.IsNullOrWhiteSpace(CALL_REASON_ID))
{
param.Add("@CALL_REASON_ID", CALL_REASON_ID);
sql += "and CALL_REASON_ID = @CALL_REASON_ID ";
countSql += "and CALL_REASON_ID = @CALL_REASON_ID ";
}
if (!string.IsNullOrWhiteSpace(CUSTOMER_NAME))
{
param.Add("@CUSTOMER_NAME", "%" + Security.Encode(CUSTOMER_NAME) + "%");
sql += "and CUSTOMER_NAME like @CUSTOMER_NAME ";
countSql += "and CUSTOMER_NAME like @CUSTOMER_NAME ";
}
if (!string.IsNullOrWhiteSpace(orderby))
{
sql += "ORDER BY " + orderby;
if (!string.IsNullOrWhiteSpace(sort))
{
sql += " " + sort;
}
}
else
{
sql += "ORDER BY DATE_ENTERED DESC";
}
//int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@SIZE", limit);
//Console.WriteLine(sql);
//Console.WriteLine(countSql);
var doNotCallList = DapperORM.ExcecuteReturnList <object>(sql, param);
int total = DapperORM.Count(countSql, param);
Pagination pagination = new Pagination(limit, offset, total);
ResponseListForm <object> response = new ResponseListForm <object>(doNotCallList, pagination);
return(response);
}
catch (Exception e)
{
return(new ResponseListForm <object>(null, new Pagination(), e.Message, 600));
}
}
public Object GetContact(string campaignId, string ageFrom, string ageTo, string branchId, string campaignCategoryId,
string customerType, string fromLevel, string toLevel, int limit, int offset, string sortField, string sortType)
{
try
{
DynamicParameters parameters = new DynamicParameters();
StringBuilder sql = new StringBuilder(@"SELECT ID, TEN_KH_C, GIOI_TINH_C, PHONE_MOBILE, NGAY_SINH_C, CMND_C , DIA_CHI_C, VUNG_C, ASSIGNED_USER_ID
FROM vwPHAN_CONG_CONTACT WHERE 1 = 1 ");
StringBuilder condition = new StringBuilder();
StringBuilder countSql = new StringBuilder("SELECT COUNT(ID) FROM vwPHAN_CONG_CONTACT WHERE 1= 1");
if (!string.IsNullOrEmpty(campaignId))
{
parameters.Add("@CAMPAIGN_ID", new Guid(campaignId));
condition.Append(" AND CAMPAIGN_ID = @CAMPAIGN_ID");
}
if (!string.IsNullOrEmpty(ageFrom))
{
parameters.Add("@ageFrom", Int32.Parse(ageFrom));
condition.Append(" AND TUOI_C >= @ageFrom");
}
if (!string.IsNullOrEmpty(ageFrom))
{
parameters.Add("@ageTo", Int32.Parse(ageTo));
condition.Append(" AND TUOI_C <= @ageTo");
}
if (!string.IsNullOrEmpty(branchId))
{
string[] branchs = branchId.Split('|');
parameters.Add("@BRANCHS", branchs);
condition.Append(" AND MA_CHI_NHANH_C IN @BRANCHS");
}
if (!string.IsNullOrEmpty(campaignCategoryId))
{
if (campaignCategoryId == Constants.Constants.CAMPAIGN_CATEGORY_ID.NND)
{
if (!string.IsNullOrEmpty(customerType))
{
condition.Append(" AND NND_CU_MOI = @CUSTOMER_TYPE");
parameters.Add("@CUSTOMER_TYPE", customerType);
}
if (!string.IsNullOrEmpty(fromLevel))
{
condition.Append(" AND HAN_MUC_TIN_DUNG >= @LEVEL_FROM");
parameters.Add("@LEVEL_FROM", Int32.Parse(fromLevel));
}
if (!string.IsNullOrEmpty(toLevel))
{
condition.Append(" AND HAN_MUC_TIN_DUNG <= @LEVEL_TO");
parameters.Add("@LEVEL_TO", Int32.Parse(toLevel));
}
}
if (campaignCategoryId == Constants.Constants.CAMPAIGN_CATEGORY_ID.LANDING)
{
if (!string.IsNullOrEmpty(customerType))
{
condition.Append(" AND LENDING_CU_MOI = @CUSTOMER_TYPE");
parameters.Add("@CUSTOMER_TYPE", customerType);
}
if (!string.IsNullOrEmpty(fromLevel))
{
condition.Append(" AND HM_THE_TELESALE >= @LEVEL_FROM");
parameters.Add("@LEVEL_FROM", Int32.Parse(fromLevel));
}
if (!string.IsNullOrEmpty(toLevel))
{
condition.Append(" AND HM_THE_TELESALE <= @LEVEL_TO");
parameters.Add("@LEVEL_TO", Int32.Parse(toLevel));
}
}
if (campaignCategoryId == Constants.Constants.CAMPAIGN_CATEGORY_ID.HET_HAN)
{
if (!string.IsNullOrEmpty(fromLevel))
{
condition.Append(" AND HAN_MUC_T24 >= @LEVEL_FROM");
parameters.Add("@LEVEL_FROM", Int32.Parse(fromLevel));
}
if (!string.IsNullOrEmpty(toLevel))
{
condition.Append(" AND HAN_MUC_T24 <= @LEVEL_TO");
parameters.Add("@LEVEL_TO", Int32.Parse(toLevel));
}
}
}
condition.Append(" AND ASSIGNED_TO IS NULL");
sql.Append(condition);
sql.Append(" ORDER BY SEQ_C ASC ");
countSql.Append(condition);
int count = DapperORM.Count(countSql.ToString(), parameters);
sql.Append(" OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY");
parameters.Add("@OFFSET", offset);
parameters.Add("@SIZE", limit);
Console.WriteLine(sql.ToString());
List <Object> data = (List <object>)DapperORM.ExcecuteReturnList <object>(sql.ToString(), parameters);
Console.WriteLine("QUERY DONE");
return(new ResponseListForm <object>(data, new Pagination(limit, offset, count), "ok", 200));
} catch (Exception e)
{
Console.WriteLine(e);
return(new { message = "SQL fail", code = 600, data = "" });
}
}
public async ValueTask <Object> FindUser(string agentIdC, string status, string groupIdC,
string userTitleIdC, string fullName, string userName, string region, string email1, int limit, int offset,
string sortField, int sortType)
{
return(await WithConnection(async conn =>
{
DynamicParameters param = new DynamicParameters();
String sql = "Select ID,HR_ID_C, USER_TYPE_C, USER_NAME, EMAIL1, STATUS," +
" USER_TITLE_ID_C, IS_ADMIN, AGENT_ID_C, GROUP_ID_C, ACCOUNT_NUMBER_C, NAME_USERTITLE, " +
"NAME_TEAMS, FULL_NAME, PHONE_MOBILE, NAME_REGION, NAME_USER_TYPE_C,ROLE_NAME from vwUSERS where 1 = 1 ";
String conditionSql = "";
String countSql = "Select count(ID) from vwUSERS WHERE 1 = 1";
if (!string.IsNullOrEmpty(agentIdC))
{
conditionSql += " and AGENT_ID_C LIKE @AGENT_ID_C";
param.Add("@AGENT_ID_C", "%" + Security.Encode(agentIdC) + "%");
}
if (!string.IsNullOrEmpty(status))
{
conditionSql += " and STATUS = @STATUS";
param.Add("@STATUS", status);
}
if (!string.IsNullOrEmpty(groupIdC))
{
conditionSql += " and GROUP_ID_C = @GROUP_ID_C";
param.Add("@GROUP_ID_C", groupIdC);
}
if (!string.IsNullOrEmpty(userTitleIdC))
{
conditionSql += " and USER_TITLE_ID_C = @USER_TITLE_ID_C";
param.Add("@USER_TITLE_ID_C", userTitleIdC);
}
if (!string.IsNullOrEmpty(fullName))
{
conditionSql += " and FULL_NAME LIKE @FULL_NAME";
param.Add("@FULL_NAME", "%" + Security.Encode(fullName) + "%");
}
if (!string.IsNullOrEmpty(userName))
{
conditionSql += " and USER_NAME LIKE @USER_NAME";
param.Add("@USER_NAME", "%" + Security.Encode(userName) + "%");
}
if (!string.IsNullOrEmpty(region))
{
conditionSql += " and REGION_ID_C = @REGION";
param.Add("@REGION", region);
}
if (!string.IsNullOrEmpty(email1))
{
conditionSql += " and EMAIL1 = @EMAIL1";
param.Add("@EMAIL1", email1);
}
sql += conditionSql;
if (!string.IsNullOrEmpty(sortField))
{
string[] whiteListSort =
{
"ID", "HR_ID_C", "USER_TYPE_C", "USER_NAME", "EMAIL1", "STATUS",
"USER_TITLE_ID_C", "IS_ADMIN", "ACCOUNT_NUMBER_C", "NAME_USERTITLE", "NAME_TEAMS",
"FULL_NAME", "PHONE_MOBILE", "NAME_REGION", "NAME_USER_TYPE_C", "ROLE_NAME", "AGENT_ID_C"
};
if (Array.IndexOf(whiteListSort, sortField) > 0)
{
sql += " order by " + sortField;
sql += sortType == 1 ? " DESC" : " ASC";
}
}
else
{
sql += " order by DATE_ENTERED DESC";
}
countSql += conditionSql;
// count total
Console.WriteLine(sql);
int total = DapperORM.Count(countSql, param);
sql += " OFFSET @OFFSET ROWS " +
" FETCH NEXT @SIZE ROWS ONLY";
param.Add("@OFFSET", offset);
param.Add("@SIZE", limit);
// Get list user
Console.WriteLine(sql);
var user = DapperORM.ExcecuteReturnList <UserDetailDto>(sql, param);
Console.WriteLine("query done");
Pagination pagination = new Pagination(limit, offset, total);
var query = await conn.QueryAsync <object>(sql, param);
return new { data = query, pagination = pagination, message = "ok", code = 200 };
}));
//try
//{
//}
//catch (Exception e)
//{
// Console.WriteLine(e);
// return new { data = "", code = 600, message = "ok" };
//}
}