/// <summary> /// 获取订单列表 /// </summary> /// <param name="pParems">条件 字典</param> /// <param name="pageIndex">页数</param> /// <param name="pageSize">个数</param> /// <returns></returns> public PagedQueryResult <TInoutViewEntity> GetOrdersList(Dictionary <string, object> pParems, int pageIndex, int pageSize) { #region 接收参数 string strWhere = ""; //订单号 if (pParems.ContainsKey("pOrdersNo")) { strWhere += string.Format(" and inout.order_no like '%{0}%'", pParems["pOrdersNo"]); } if (pParems.ContainsKey("pStartDate") && pParems.ContainsKey("pEndDate")) { strWhere += string.Format(" and (inout_detail.Field1 <= cast('{0}' as datetime) and inout_detail.Field1 >= cast('{1}' as datetime))", pParems["pStartDate"], pParems["pEndDate"]); } //门店 if (pParems.ContainsKey("pStoreName")) { strWhere += string.Format(" and unit.unit_name like '%{0}%'", pParems["pStoreName"]); } //房型 if (pParems.ContainsKey("pItemName")) { strWhere += string.Format(" and item.item_name like '%{0}%'", pParems["pItemName"]); } //客人姓名 if (pParems.ContainsKey("pUserName")) { strWhere += string.Format(" and inout.field3 like '%{0}%'", pParems["pUserName"]); } //订单状态 if (pParems.ContainsKey("pStatus")) { if (pParems["pStatus"].ToString() != "0") { if (pParems["pStatus"].ToString() == "400") { strWhere += string.Format(" and inout.status in ('400','800')", pParems["pStatus"]); } else { strWhere += string.Format(" and inout.status = '{0}'", pParems["pStatus"]); } } } #endregion #region 拼接SQL StringBuilder strSql = new StringBuilder(); strSql.AppendFormat(@" /*获取订单列表*/ select ROW_NUMBER() over(order by inout.create_time desc) ID ,inout.order_id OrdersID --订单ID ,inout.order_no OrdersNo --订单编号 ,inout.order_date OrdersDate --订单日期 ,del.DeliveryName Payment --付款方式 ,inout.Field11 PayType --付款类型 ,inout.status OrdersStatus --订单状态 ,opt.OptionText OrdersStatusText --订单状态文本 ,unit.unit_name StoreName --门店 ,item.item_name RoomTypeName --房型名称 ,inout.field14 GuestName --客人名称 ,inout.vip_no VipID --用户ID ,inout_detail.Field1 StartDate --入住日期 ,inout_detail.Field2 EndDate --离店日期 ,ISNULL(inout.remark,'') Remark --备注 ,cast(inout_detail.order_qty as int) RoomCount --房间数 , vip.VipRealName --下单人 , vip.Phone --下单人电话 into #Result from T_Inout inout --订单 left join Vip on inout.vip_no = vip.VipID and vip.isdelete = 0 inner join T_Inout_Detail inout_detail on inout.order_id=inout_detail.order_id --订单明细 left join t_unit unit on inout.purchase_unit_id=unit.unit_id and unit.customer_id=inout.customer_id --门店 left join T_Sku sku on inout_detail.sku_id=sku.sku_id left join T_Item item on sku.item_id=item.item_id --房型名称 left join Delivery del on inout.Field11=del.DeliveryId and del.IsDelete=0 --付款方式 inner join Options opt on inout.status=opt.OptionValue and opt.OptionName='OrdersStatus' and opt.CustomerID='{0}' and opt.IsDelete=0 --订单状态 where inout.customer_id='{0}' and inout.status !=-1 {1} select * from #Result where ID between {2} and {3} select count(*) from #Result drop table #Result", CurrentUserInfo.ClientID, strWhere, (pageSize * (pageIndex - 1)) + 1, pageSize * pageIndex); #endregion DataSet dsSource = this.SQLHelper.ExecuteDataset(strSql.ToString()); PagedQueryResult <TInoutViewEntity> pageQuery = new PagedQueryResult <TInoutViewEntity>(); pageQuery.Entities = ConvertHelper <TInoutViewEntity> .ConvertToList(dsSource.Tables[0]).ToArray(); int pageCount = 0; int.TryParse(dsSource.Tables[1].Rows[0][0] + "", out pageCount); pageQuery.PageCount = pageCount; return(pageQuery); }
/// <summary> /// 订单审批 /// </summary> /// <param name="pParams"></param> public void OrdersApprove(Dictionary <string, string> pParams) { TInoutEntity orderEntity = new TInoutEntity(); TInoutStatusEntity statusEntity = new TInoutStatusEntity(); TInoutDetailEntity[] orderDetailEntity = null; #region 拼接查询SQL StringBuilder strSearchSql = new StringBuilder(); strSearchSql.AppendFormat(@" select inout.order_id OrdersID ,inout.order_no OrdersNo ,ISNULL(store.UsedAmount,0) UsedAmount ,ISNULL(store.StockAmount,0) StockAmount ,store.* from T_Inout inout inner join T_Inout_Detail inout_detail on inout.order_id=inout_detail.order_id inner join StoreItemDailyStatus store on inout_detail.sku_id=store.SkuID where inout.customer_id='{0}' and inout.order_id='{1}' and store.StatusDate between cast(inout_detail.Field1 as datetime) and convert(char(10),dateadd(dd,-1,inout_detail.Field2),120) order by store.StatusDate", CurrentUserInfo.ClientID, pParams["pOrdersID"]); #endregion DataSet dsSource = this.SQLHelper.ExecuteDataset(strSearchSql.ToString()); StoreItemDailyStatusEntity[] storeEntity = ConvertHelper <StoreItemDailyStatusEntity> .ConvertToList(dsSource.Tables[0]).ToArray(); SqlTransaction tran = this.SQLHelper.CreateTransaction(); using (tran.Connection) { try { //更新订单主表信息 if (pParams.ContainsKey("pOrdersID")) { orderDetailEntity = new TInoutDetailDAO(CurrentUserInfo).QueryByEntity(new TInoutDetailEntity { order_id = pParams["pOrdersID"] }, null); orderEntity = GetByID(pParams["pOrdersID"]); orderEntity.Status = pParams["pOrdersStatus"]; orderEntity.StatusDesc = pParams["pOrdersDesc"]; orderEntity.Field7 = orderEntity.Status; orderEntity.Field10 = orderEntity.StatusDesc; if (pParams["pRemark"] != null && pParams["pRemark"] != "") { orderEntity.Remark = pParams["pRemark"]; } orderEntity.ModifyTime = DateTime.Now.ToString(); orderEntity.ModifyUserID = CurrentUserInfo.UserID; if (orderEntity.Status == "500" || orderEntity.Status == "400") { T_InoutEntity[] entity = new T_InoutDAO(CurrentUserInfo).QueryByEntity(new T_InoutEntity { order_id = pParams["pOrdersID"] }, null); new VipDAO(this.CurrentUserInfo).ProcSetCancelOrder(this.CurrentUserInfo.ClientID, pParams["pOrdersID"], entity[0].vip_no); } Update(orderEntity); } //更新房态 库存数量 if (storeEntity != null && storeEntity.Length > 0) { for (int i = 0; i < storeEntity.Length; i++) { if (pParams["pOrdersType"].ToString() == "1") { //1.审核通过 if (storeEntity[i].StockAmount != 0 && storeEntity[i].StockAmount > 0) { storeEntity[i].StockAmount = storeEntity[i].StockAmount - (int)Math.Round((decimal)orderDetailEntity[0].OrderQty, 0); } if (storeEntity[i].UsedAmount != 100 && storeEntity[i].UsedAmount < 100) { storeEntity[i].UsedAmount = storeEntity[i].UsedAmount + (int)Math.Round((decimal)orderDetailEntity[0].OrderQty, 0); } storeEntity[i].LastUpdateBy = CurrentUserInfo.UserID; storeEntity[i].LastUpdateTime = DateTime.Now; } else { //2.审核不通过 if (storeEntity[i].StockAmount != 100 && storeEntity[i].StockAmount < 100) { storeEntity[i].StockAmount += (int)Math.Round((decimal)orderDetailEntity[0].OrderQty, 0); } if (storeEntity[i].UsedAmount != 0 && storeEntity[i].UsedAmount > 0) { storeEntity[i].UsedAmount -= (int)Math.Round((decimal)orderDetailEntity[0].OrderQty, 0); } storeEntity[i].LastUpdateBy = CurrentUserInfo.UserID; storeEntity[i].LastUpdateTime = DateTime.Now; } new StoreItemDailyStatusDAO(CurrentUserInfo).Update(storeEntity[i]); } } //添加订单操作流水 statusEntity.OrderID = pParams["pOrdersID"]; statusEntity.OrderStatus = int.Parse(pParams["pOrdersStatus"]); if (pParams["pCheckResult"] != null && pParams["pCheckResult"] != "") { statusEntity.CheckResult = int.Parse(pParams["pCheckResult"]); } statusEntity.CustomerID = CurrentUserInfo.ClientID; statusEntity.CreateBy = CurrentUserInfo.UserID; new TInoutStatusDAO(CurrentUserInfo).Create(statusEntity); tran.Commit(); } catch (Exception ex) { Loggers.Exception(new ExceptionLogInfo() { ClientID = CurrentUserInfo.ClientID, UserID = CurrentUserInfo.UserID, ErrorMessage = ex.Message }); tran.Rollback(); } } }