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