public IEnumerable <MstGroupArea> SearchGroupAreatList(DataTableModel dt, GroupAreaModel model, out int total_row) { StringBuilder sql = new StringBuilder(); sql.Append(@" SELECT A. * FROM MstGroupArea A WHERE A.DEL_FLG = @DEL_FLG AND A.FOR_USER = @FOR_USER"); if (!string.IsNullOrEmpty(model.GROUP_NAME)) { sql.Append(" AND (A.GROUP_NAME LIKE @GROUP_NAME)"); } if (!string.IsNullOrEmpty(model.GROUP_CD_LIST)) { sql.Append(" AND A.GROUP_CD IN ('" + model.GROUP_CD_LIST + "')"); } else if (model.CITY_CD > 0 || model.DISTRICT_CD > 0 || model.TOWN_CD > 0) { sql.Append(" AND A.GROUP_CD IN ('')"); } sql.Append(" ORDER BY A.GROUP_NAME ASC, A.UPD_DATE DESC "); int lower = dt.iDisplayStart + 1; int upper = dt.iDisplayStart + dt.iDisplayLength; PagingHelper.SQLParts parts; PagingHelper.SplitSQL(sql.ToString(), out parts); string sqlpage = PagingHelper.BuildPageQuery(lower, dt.iDisplayLength, parts); string sqlcount = parts.sqlCount; var dataList = base.Query <MstGroupArea>(sqlpage, new { DEL_FLG = model.DEL_FLG, GROUP_NAME = '%' + model.GROUP_NAME + '%', FOR_USER = model.FOR_USER, pageindex = lower, pagesize = upper }).ToList(); total_row = base.Query <int>(sqlcount, new { DEL_FLG = model.DEL_FLG, GROUP_NAME = '%' + model.GROUP_NAME + '%', FOR_USER = model.FOR_USER, pageindex = lower, pagesize = upper }).FirstOrDefault(); return(dataList); }
public IEnumerable <MstDistrictEx> SearchDistrictList(DataTableModel dt, DistrictModel model, out int total_row) { StringBuilder sql = new StringBuilder(); sql.Append(@" SELECT A. *, B.CITY_NAME FROM MstDistrict A LEFT JOIN MstCity B ON A.CITY_CD = B.CITY_CD WHERE A.DEL_FLG = @DEL_FLG AND A.INSIDE = @INSIDE"); if (model.CITY_CD > 0) { sql.Append(" AND (A.CITY_CD LIKE @CITY_CD)"); } if (!string.IsNullOrEmpty(model.DISTRICT_NAME)) { sql.Append(" AND (A.DISTRICT_NAME LIKE @DISTRICT_NAME)"); } int lower = dt.iDisplayStart + 1; int upper = dt.iDisplayStart + dt.iDisplayLength; PagingHelper.SQLParts parts; PagingHelper.SplitSQL(sql.ToString(), out parts); string sqlpage = PagingHelper.BuildPageQuery(lower, dt.iDisplayLength, parts); string sqlcount = parts.sqlCount; var dataList = base.Query <MstDistrictEx>(sqlpage, new { DEL_FLG = model.DEL_FLG, INSIDE = model.INSIDE, CITY_CD = model.CITY_CD, DISTRICT_NAME = '%' + model.DISTRICT_NAME + '%', pageindex = lower, pagesize = upper }).ToList(); total_row = base.Query <int>(sqlcount, new { DEL_FLG = model.DEL_FLG, INSIDE = model.INSIDE, CITY_CD = model.CITY_CD, DISTRICT_NAME = '%' + model.DISTRICT_NAME + '%', pageindex = lower, pagesize = upper }).FirstOrDefault(); return(dataList); }
public IEnumerable <MstCity> SearchCityList(DataTableModel dt, CityModel model, out int total_row) { StringBuilder sql = new StringBuilder(); sql.Append(@" SELECT * FROM MstCity WHERE DEL_FLG = @DEL_FLG "); if (!string.IsNullOrEmpty(model.CITY_NAME)) { sql.Append(" AND (CITY_NAME LIKE @CITY_NAME)"); } int lower = dt.iDisplayStart + 1; int upper = dt.iDisplayStart + dt.iDisplayLength; PagingHelper.SQLParts parts; PagingHelper.SplitSQL(sql.ToString(), out parts); string sqlpage = PagingHelper.BuildPageQuery(lower, dt.iDisplayLength, parts); string sqlcount = parts.sqlCount; var dataList = base.Query <MstCity>(sqlpage, new { DEL_FLG = model.DEL_FLG, CITY_NAME = '%' + model.CITY_NAME + '%', pageindex = lower, pagesize = upper }).ToList(); total_row = base.Query <int>(sqlcount, new { DEL_FLG = model.DEL_FLG, CITY_NAME = '%' + model.CITY_NAME + '%', pageindex = lower, pagesize = upper }).FirstOrDefault(); return(dataList); }
public IEnumerable <TblUserAccountEx> SearchUserList(DataTableModel dt, UserAccountModel model, out int total_row) { StringBuilder sql = new StringBuilder(); sql.Append(@" SELECT A. *, B.CITY_NAME, C.DISTRICT_NAME, T.TOWN_NAME FROM TblUserAccount A LEFT JOIN MstCity B ON A.USER_CITY = B.CITY_CD LEFT JOIN MstDistrict C ON A.USER_CITY = C.CITY_CD AND A.USER_DISTRICT = C.DISTRICT_CD LEFT JOIN MstTown T ON A.USER_CITY = T.CITY_CD AND A.USER_DISTRICT = T.DISTRICT_CD AND A.USER_TOWN = T.TOWN_CD WHERE A.DEL_FLG = @DEL_FLG AND A.LOGIN_LOCK_FLG = @LOGIN_LOCK_FLG"); if (model.CITY_CD_SEARCH > 0) { sql.Append(" AND (A.USER_CITY LIKE @CITY_CD_SEARCH)"); } if (model.DISTRICT_CD_SEARCH > 0) { sql.Append(" AND (A.USER_DISTRICT LIKE @DISTRICT_CD_SEARCH)"); } if (model.TOWN_CD_SEARCH > 0) { sql.Append(" AND (A.USER_TOWN LIKE @TOWN_CD_SEARCH)"); } if (!string.IsNullOrEmpty(model.USER_ADDRESS)) { sql.Append(" AND (A.USER_ADDRESS LIKE @USER_ADDRESS)"); } if (!string.IsNullOrEmpty(model.USER_EMAIL)) { sql.Append(" AND (A.USER_EMAIL LIKE @USER_EMAIL)"); } if (!string.IsNullOrEmpty(model.USER_NAME)) { sql.Append(" AND (A.USER_NAME LIKE @USER_NAME)"); } if (!string.IsNullOrEmpty(model.SHOP_NAME)) { sql.Append(" AND (A.SHOP_NAME LIKE @SHOP_NAME)"); } if (!string.IsNullOrEmpty(model.USER_PHONE)) { sql.Append(" AND (A.USER_PHONE LIKE @USER_PHONE)"); } if (model.AREA > 0) { sql.Append(" AND (A.AREA = @AREA)"); } sql.Append(" AND (A.USER_AUTHORITY = @USER_AUTHORITY)"); sql.Append(" ORDER BY A.USER_EMAIL asc, A.USER_NAME asc, A.UPD_DATE desc"); int lower = dt.iDisplayStart + 1; int upper = dt.iDisplayStart + dt.iDisplayLength; PagingHelper.SQLParts parts; PagingHelper.SplitSQL(sql.ToString(), out parts); string sqlpage = PagingHelper.BuildPageQuery(lower, dt.iDisplayLength, parts); string sqlcount = parts.sqlCount; var dataList = base.Query <TblUserAccountEx>(sqlpage, new { DEL_FLG = model.DEL_FLG, LOGIN_LOCK_FLG = model.LOGIN_LOCK_FLG, CITY_CD_SEARCH = model.CITY_CD_SEARCH, DISTRICT_CD_SEARCH = model.DISTRICT_CD_SEARCH, TOWN_CD_SEARCH = model.TOWN_CD_SEARCH, USER_ADDRESS = '%' + model.USER_ADDRESS + '%', USER_NAME = '%' + model.USER_NAME + '%', SHOP_NAME = '%' + model.SHOP_NAME + '%', AREA = model.AREA, USER_PHONE = model.USER_PHONE + '%', USER_EMAIL = '%' + model.USER_EMAIL + '%', USER_AUTHORITY = model.USER_AUTHORITY, pageindex = lower, pagesize = upper }).ToList(); total_row = base.Query <int>(sqlcount, new { DEL_FLG = model.DEL_FLG, LOGIN_LOCK_FLG = model.LOGIN_LOCK_FLG, CITY_CD_SEARCH = model.CITY_CD_SEARCH, DISTRICT_CD_SEARCH = model.DISTRICT_CD_SEARCH, TOWN_CD_SEARCH = model.TOWN_CD_SEARCH, USER_ADDRESS = '%' + model.USER_ADDRESS + '%', USER_NAME = '%' + model.USER_NAME + '%', SHOP_NAME = '%' + model.SHOP_NAME + '%', AREA = model.AREA, USER_PHONE = model.USER_PHONE + '%', USER_EMAIL = '%' + model.USER_EMAIL + '%', USER_AUTHORITY = model.USER_AUTHORITY, pageindex = lower, pagesize = upper }).FirstOrDefault(); return(dataList); }
/// <summary> /// List display paging /// </summary> /// <param name="dt"></param> /// <param name="model"></param> /// <param name="total_row"></param> /// <returns></returns> public IEnumerable <OrderShipModel> SearchOrderShipList(DataTableModel dt, AdminOrderList model, out int total_row) { StringBuilder sql = new StringBuilder(); DateTime fromDate = new DateTime(); DateTime toDate = new DateTime(); sql.Append(@" SELECT A. * FROM TblOrder A WHERE A.USER_ID = @USER_ID AND A.DEL_FLG = @DEL_FLG"); if (!string.IsNullOrEmpty(model.ORDER_STATUS_LIST)) { sql.Append(" AND A.ORDER_STATUS IN ('" + model.ORDER_STATUS_LIST + "')"); } if (!string.IsNullOrEmpty(model.TEXT_SEARCH)) { sql.Append(" AND (A.SHIP_CODE LIKE @TEXT_SEARCH ESCAPE '|' ) OR ( A.RECEIVED_NAME LIKE @TEXT_SEARCH ESCAPE '|') OR ( A.RECEIVED_PHONE LIKE @TEXT_SEARCH ESCAPE '|') "); } if (!string.IsNullOrEmpty(model.FROM_DATE)) { fromDate = DateTime.ParseExact(model.FROM_DATE, "dd/MM/yyyy", CultureInfo.InvariantCulture); sql.Append(" AND CAST(A.CREATE_DATE AS DATE) >= @FROM_DATE "); } if (!string.IsNullOrEmpty(model.TO_DATE)) { toDate = DateTime.ParseExact(model.TO_DATE, "dd/MM/yyyy", CultureInfo.InvariantCulture); sql.Append(" AND CAST(A.CREATE_DATE AS DATE) <= @TO_DATE "); } sql.Append(" ORDER BY CREATE_DATE DESC, SHIP_CODE DESC, RECEIVED_NAME ASC"); int lower = dt.iDisplayStart + 1; int upper = dt.iDisplayStart + dt.iDisplayLength; PagingHelper.SQLParts parts; PagingHelper.SplitSQL(sql.ToString(), out parts); string sqlpage = PagingHelper.BuildPageQuery(lower, dt.iDisplayLength, parts); string sqlcount = parts.sqlCount; var dataList = base.Query <OrderShipModel>(sqlpage, new { USER_ID = CmnEntityModel.USER_ID, DEL_FLG = DeleteFlag.NON_DELETE, TEXT_SEARCH = '%' + UtilityServices.UtilityServices.replaceWildcardCharacters(model.TEXT_SEARCH) + '%', TO_DATE = toDate, FROM_DATE = fromDate, pageindex = lower, pagesize = upper }).ToList(); total_row = base.Query <int>(sqlcount, new { USER_ID = CmnEntityModel.USER_ID, DEL_FLG = DeleteFlag.NON_DELETE, TEXT_SEARCH = '%' + UtilityServices.UtilityServices.replaceWildcardCharacters(model.TEXT_SEARCH) + '%', TO_DATE = toDate, FROM_DATE = fromDate, pageindex = lower, pagesize = upper }).FirstOrDefault(); return(dataList); }