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