public IEnumerable <OrderShipModel> SearchOrderShipList(DataTableModel dt, OrderShipCondition condition, out int total_row)
{
OrderShipDa dataAccess = new OrderShipDa();
IEnumerable <OrderShipModel> results = dataAccess.SearchOrderShipList(dt, condition, out total_row);
return(results);
}
public IList <OrderShipModel> getDataForCSV(OrderShipCondition condition)
{
StringBuilder sql = new StringBuilder();
DateTime fromDate = new DateTime();
DateTime toDate = new DateTime();
sql.Append(@"
SELECT A.*, B.CITY_NAME as RECEIVE_CITY_NAME, C.DISTRICT_NAME as RECEIVE_DISTRICT_NAME, D.TOWN_NAME as RECEIVE_TOWN_NAME,
E.USER_NAME as ACCEPTANCE_NAME
FROM TblOrder A
LEFT JOIN MstCity B
ON A.RECEIVED_CITY = B.CITY_CD
LEFT JOIN MstDistrict C
ON A.RECEIVED_CITY = C.CITY_CD AND A.RECEIVED_DISTRICT = C.DISTRICT_CD
LEFT JOIN MstTown D
ON A.RECEIVED_CITY = D.CITY_CD AND A.RECEIVED_DISTRICT = D.DISTRICT_CD AND A.RECEIVED_TOWN = D.TOWN_CD
LEFT JOIN TblUserAccount E
ON A.ACCEPTANCE_ID = E.USER_ID
WHERE A.USER_ID = @USER_ID
AND A.DEL_FLG = @DEL_FLG");
if (!string.IsNullOrEmpty(condition.ORDER_STATUS_LIST))
{
sql.Append(" AND A.ORDER_STATUS IN ('" + condition.ORDER_STATUS_LIST + "')");
}
if (!string.IsNullOrEmpty(condition.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(condition.FROM_DATE))
{
fromDate = DateTime.ParseExact(condition.FROM_DATE, "dd/MM/yyyy", CultureInfo.InvariantCulture);
sql.Append(" AND A.CREATE_DATE >= @FROM_DATE ");
}
if (!string.IsNullOrEmpty(condition.TO_DATE))
{
toDate = DateTime.ParseExact(condition.TO_DATE, "dd/MM/yyyy", CultureInfo.InvariantCulture);
sql.Append(" AND A.CREATE_DATE <= @TO_DATE ");
}
sql.Append(" ORDER BY CREATE_DATE DESC, SHIP_CODE DESC, RECEIVED_NAME ASC");
return(base.Query <OrderShipModel>(sql.ToString(), new
{
USER_ID = CmnEntityModel.USER_ID,
DEL_FLG = DeleteFlag.NON_DELETE,
TEXT_SEARCH = '%' + UtilityServices.UtilityServices.replaceWildcardCharacters(condition.TEXT_SEARCH) + '%',
TO_DATE = toDate,
FROM_DATE = fromDate
}).ToList());
}
public ActionResult OrderShipList()
{
CmnEntityModel currentUser = Session["CmnEntityModel"] as CmnEntityModel;
var authorityList = currentUser != null ? currentUser.USER_AUTHORITY : 0;
if (currentUser == null || currentUser.USER_ID == 0)
{
return(RedirectToAction("Login", "UserAccount"));
}
OrderShipCondition model = new OrderShipCondition();
CommonService comService = new CommonService();
return(View(model));
}
public ActionResult List(DataTableModel dt, OrderShipCondition condition)
{
if (ModelState.IsValid)
{
using (OrderShipService service = new OrderShipService())
{
int total_row = 0;
var OrderSummaryInfo = new OrderShipCondition();
if (!string.IsNullOrEmpty(condition.ORDER_STATUS_LIST))
{
List <long> statusList = condition.ORDER_STATUS_LIST.Split(',').Select(long.Parse).ToList();
if (statusList.Count > 0)
{
condition.ORDER_STATUS_LIST = string.Join("','", statusList.ToArray());
}
}
var totaldataList = service.SearchOrderShipListTotal(dt, condition);
var dataList = service.SearchOrderShipList(dt, condition, out total_row);
var dataByStatusWaitCOD = service.SearchOrderShipByStatus(OrderStatus.WaitCOD);
var totalDataNoneStatus = service.SearchOrderListTotalNoneStatus(dt, condition);
OrderSummaryInfo.TotalStatus_0 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.Create).ToList().Count();
OrderSummaryInfo.TotalStatus_1 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.TakingOrder).ToList().Count();
OrderSummaryInfo.TotalStatus_2 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.TakedOrder).ToList().Count();
OrderSummaryInfo.TotalStatus_3 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.Shiping).ToList().Count();
OrderSummaryInfo.TotalStatus_4 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.Delivery).ToList().Count();
OrderSummaryInfo.TotalStatus_5 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.ReShip).ToList().Count();
OrderSummaryInfo.TotalStatus_6 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.ReturnOrder).ToList().Count();
OrderSummaryInfo.TotalStatus_7 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.ReturnOrderSuccess).ToList().Count();
OrderSummaryInfo.TotalStatus_8 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.Finished).ToList().Count();
OrderSummaryInfo.TotalStatus_9 = totalDataNoneStatus.Where(i => i.ORDER_STATUS == OrderStatus.WaitCOD).ToList().Count();
OrderSummaryInfo.TotalStatusAll = totalDataNoneStatus.Count();
//var dataListWaitCOD = totaldataList.Where(i => i.ORDER_STATUS == OrderStatus.WaitCOD).ToList();
//var OrderMonth = totaldataList.Where(i => i.CREATE_DATE.ToString("MM/yyyy").b);
foreach (var data in totaldataList)
{
OrderSummaryInfo.TotalPriceProduct += data.PRICE_PRODUCT.Value;
if (data.USER_PAYMENT.Equals(User_Pay.Sender))
{
OrderSummaryInfo.TotalPriceShip += data.PRICE_SHIP;
}
}
OrderSummaryInfo.TotalMoney = OrderSummaryInfo.TotalPriceProduct - OrderSummaryInfo.TotalPriceShip;
decimal totalPriceWaitCOD = 0;
decimal totalShipWaitCOD = 0;
foreach (var data in dataByStatusWaitCOD)
{
totalPriceWaitCOD += data.PRICE_PRODUCT.Value;
if (data.USER_PAYMENT.Equals(User_Pay.Sender))
{
totalShipWaitCOD += data.PRICE_SHIP;
}
}
OrderSummaryInfo.TotalWaitCOD = totalPriceWaitCOD - totalShipWaitCOD;
OrderSummaryInfo.thisMonth = Utility.GetCurrentDateOnly().Month;
var thismonth = Utility.GetCurrentDateOnly().ToString("MM/yyyy");
var dataThisMonth = service.SearchOrderByMonth(thismonth);
OrderSummaryInfo.TotalOrderthisMonth = dataThisMonth.Count();
int totalRowCount = dataList.Count();
int lastItem = dt.iDisplayLength + dt.iDisplayStart;
var tableData = (from i in dataList
select new object[]
{
i.ORDER_ID,
i.CREATE_DATE.ToString("dd/MM/yyyy"),
i.SHIP_CODE,
i.RECEIVED_NAME,
i.RECEIVED_PHONE,
i.PRODUCT_NAME,
i.PRICE_PRODUCT,
i.USER_PAYMENT == User_Pay.Sender ?i.PRICE_SHIP : 0,
i.RECEIVE_HOUR_FROM + "h - " + i.RECEIVE_HOUR_TO + "h Ngày:" + i.RECEIVE_TIME_DATE.ToString("dd/MM/yyyy"),
OrderStatus.Items[i.ORDER_STATUS].ToString()
});
var result = Json(new
{
sEcho = dt.sEcho,
iTotalRecords = total_row,
iTotalDisplayRecords = total_row,
objOrderSummaryInfo = OrderSummaryInfo,
aaData = tableData
});
Session["SearchOrderShipList"] = condition;
result.MaxJsonLength = Int32.MaxValue;
return(result);
}
}
else
{
var ErrorMessages = ModelState.Where(x => x.Value.Errors.Count > 0).Select(x => new { x.Key, x.Value.Errors }).ToArray();
}
return(new EmptyResult());
}
/// <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, OrderShipCondition 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);
}
/// <summary>
/// Select list have status all
/// </summary>
/// <param name="dt"></param>
/// <param name="model"></param>
/// <returns></returns>
public IEnumerable <OrderShipModel> SearchOrderShipListTotal(DataTableModel dt, OrderShipCondition model)
{
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");
var dataList = base.Query <OrderShipModel>(sql.ToString(),
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
}).ToList();
return(dataList);
}
public IEnumerable <OrderShipModel> SearchOrderListTotalNoneStatus(DataTableModel dt, OrderShipCondition condition)
{
OrderShipDa dataAccess = new OrderShipDa();
IEnumerable <OrderShipModel> results = dataAccess.SearchOrderListTotalNoneStatus(dt, condition);
return(results);
}
