/// <summary> /// 订单列表 /// </summary> /// <param name="queryModel"></param> /// <param name="jqgridparam">分页参数</param> /// <returns></returns> public List <SaleOrderViewModel> GetOrderList(QueryOrderViewModel queryModel, JqGridParam jqgridparam) { string userId = ManageProvider.Provider.Current().UserId; string userName = ManageProvider.Provider.Current().UserName; StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT SO.* , W.WarehouseName , M.FullName AS MerchantName , MM.MallName , S.ShipTypeName , S.Code AS ShipTypeCode, ProductDetail = STUFF(( SELECT ',' + product.ProductName + '(' + CONVERT(VARCHAR(20), item.Qty) + ')' + product.BaseUnit FROM dbo.SaleOrder_Item item INNER JOIN dbo.Product product ON item.ProductId = product.ProductId WHERE item.OrderNo = SO.OrderNo FOR XML PATH('') ), 1, 1, '') FROM SaleOrder SO LEFT JOIN dbo.Warehouse W ON SO.WarehouseId = W.WarehouseId LEFT JOIN dbo.Merchant M ON SO.MerchantId = M.MerchantId LEFT JOIN dbo.Merchant_Mall MM ON SO.MerchantMallId = MM.MallId LEFT JOIN dbo.ShipType S ON SO.ShipTypeId = S.ShipTypeId WHERE 1 = 1"); //查询非挂单锁单 if (queryModel.QueryType != "Suspend" && queryModel.QueryType != "Lock1" && queryModel.QueryType != "Lock2") { if (!string.IsNullOrEmpty(queryModel.WarehouseId)) { strSql.Append(" AND SO.WarehouseId = @WarehouseId "); parameter.Add(DbFactory.CreateDbParameter("@WarehouseId", queryModel.WarehouseId)); } if (!string.IsNullOrEmpty(queryModel.MerchantId)) { strSql.Append(" AND SO.MerchantId = @MerchantId "); parameter.Add(DbFactory.CreateDbParameter("@MerchantId", queryModel.MerchantId)); } if (!string.IsNullOrEmpty(queryModel.OrderNo)) { //strSql.Append(" AND SO.OrderNo = @OrderNo "); //parameter.Add(DbFactory.CreateDbParameter("@OrderNo", queryModel.OrderNo)); strSql.AppendFormat(@" AND SO.OrderNo LIKE '{0}'", '%' + queryModel.OrderNo + '%'); } if (!string.IsNullOrEmpty(queryModel.SourceOrderNo)) { //strSql.Append(" AND SO.SourceOrderNo = @SourceOrderNo "); //parameter.Add(DbFactory.CreateDbParameter("@SourceOrderNo", queryModel.SourceOrderNo)); strSql.AppendFormat(@" AND SO.SourceOrderNo LIKE '{0}'", '%' + queryModel.SourceOrderNo + '%'); } if (!string.IsNullOrEmpty(queryModel.ReceiveContact)) { queryModel.ReceiveContact = HttpUtility.UrlDecode(queryModel.ReceiveContact); strSql.AppendFormat(@" AND SO.ReceiveContact LIKE '{0}'", '%' + queryModel.ReceiveContact + '%'); } if (!string.IsNullOrEmpty(queryModel.ReceivePhone)) { strSql.AppendFormat(@" AND (SO.ReceivePhone LIKE '{0}' OR SO.ReceiveCellPhone LIKE '{0}')", '%' + queryModel.ReceivePhone + '%'); } if (!string.IsNullOrEmpty(queryModel.ShipTypeId)) { strSql.Append(" AND SO.ShipTypeId = @ShipTypeId "); parameter.Add(DbFactory.CreateDbParameter("@ShipTypeId", queryModel.ShipTypeId)); } if (!string.IsNullOrEmpty(queryModel.ExpressNum)) { //strSql.Append(" AND SO.ExpressNum = @ExpressNum "); //parameter.Add(DbFactory.CreateDbParameter("@ExpressNum", queryModel.ExpressNum)); strSql.AppendFormat(@" AND SO.ExpressNum LIKE '{0}'", '%' + queryModel.ExpressNum + '%'); } if (!string.IsNullOrEmpty(queryModel.StartTime)) { strSql.Append(" AND SO.OrderDate >= @StartTime "); parameter.Add(DbFactory.CreateDbParameter("@StartTime", CommonHelper.GetDateTime(queryModel.StartTime + " 00:00"))); } if (!string.IsNullOrEmpty(queryModel.EndTime)) { strSql.Append(" AND SO.OrderDate <= @EndTime "); parameter.Add(DbFactory.CreateDbParameter("@EndTime", CommonHelper.GetDateTime(queryModel.EndTime + " 23:59:59"))); } if (queryModel.Status.HasValue) { strSql.Append(" AND SO.Status = @Status "); parameter.Add(DbFactory.CreateDbParameter("@Status", queryModel.Status.Value)); } if (queryModel.StatusWithPrint) { strSql.AppendFormat(" AND SO.Status IN({0},{1}) ", (int)OrderStatus.WaitPick, (int)OrderStatus.WaitOutStock); parameter.Add(DbFactory.CreateDbParameter("@StatusWithPrint", queryModel.StatusWithPrint)); } if (queryModel.PrintStatus.HasValue) { strSql.Append(" AND SO.PrintStatus = @PrintStatus "); parameter.Add(DbFactory.CreateDbParameter("@PrintStatus", queryModel.PrintStatus.Value)); } if (!string.IsNullOrEmpty(queryModel.ProductCode)) { strSql.AppendFormat(@" AND EXISTS ( SELECT * FROM dbo.SaleOrder_Item si INNER JOIN dbo.Product product ON si.ProductId = product.ProductId WHERE si.OrderId = SO.OrderId AND product.Code LIKE '{0}')", '%' + queryModel.ProductCode + '%'); } if (!string.IsNullOrEmpty(queryModel.ProductName)) { strSql.AppendFormat(@" AND EXISTS ( SELECT * FROM dbo.SaleOrder_Item si INNER JOIN dbo.Product product ON si.ProductId = product.ProductId WHERE si.OrderId = SO.OrderId AND product.ProductName LIKE '{0}')", '%' + queryModel.ProductName + '%'); } if (!string.IsNullOrEmpty(queryModel.Province)) { strSql.AppendFormat(@" AND SO.Province LIKE '{0}'", '%' + queryModel.Province + '%'); } if (!string.IsNullOrEmpty(queryModel.City)) { strSql.AppendFormat(@" AND SO.City LIKE '{0}'", '%' + queryModel.City + '%'); } if (!string.IsNullOrEmpty(queryModel.ReceiveAddress)) { strSql.AppendFormat(@" AND SO.ReceiveAddress LIKE '{0}'", '%' + queryModel.ReceiveAddress + '%'); } if (!string.IsNullOrEmpty(queryModel.County)) { strSql.AppendFormat(@" AND SO.County LIKE '{0}'", '%' + queryModel.County + '%'); } if (queryModel.LockMinute == null) { queryModel.LockMinute = 0; } strSql.Append(" AND (SO.LockUserId='' OR SO.LockUserId IS NULL OR SO.UnLockTime < GETDATE()) "); parameter.Add(DbFactory.CreateDbParameter("@LockUserId", userId)); parameter.Add(DbFactory.CreateDbParameter("@LockUserName", userName)); parameter.Add(DbFactory.CreateDbParameter("@LockMinute", queryModel.LockMinute)); StringBuilder strSelectSql = new StringBuilder(); if (jqgridparam.page == 0) { jqgridparam.page = 1; } int num = (jqgridparam.page - 1) * jqgridparam.rows; int num1 = (jqgridparam.page) * jqgridparam.rows; string orderBy = ""; if (!string.IsNullOrEmpty(jqgridparam.sidx)) { orderBy = "Order By " + jqgridparam.sidx + " " + jqgridparam.sord + ""; } else { orderBy = "Order By (select 0)"; } strSelectSql.AppendFormat(@" BEGIN TRAN; UPDATE SaleOrder WITH ( TABLOCKX ) SET LockUserId = '' , LockUserName = '' , UnLockTime = DATEADD(MINUTE, -1, GETDATE()) WHERE LockUserId = @LockUserId; SELECT COUNT(1) AS TotalRows FROM ( {0} ) AS t; SELECT * INTO ##SaleOrder FROM ( SELECT ROW_NUMBER() OVER ( {1} ) AS rowNum , * FROM ( {0} ) AS T ) AS N WHERE rowNum > {2} AND rowNum <= {3};", strSql, orderBy, num, num1); if (queryModel.LockMinute > 0) { strSelectSql.Append(@" UPDATE SaleOrder SET LockUserId = @LockUserId, LockUserName = @LockUserName, UnLockTime = DATEADD(MINUTE, @LockMinute, GETDATE()) WHERE OrderId IN(SELECT OrderId FROM ##SaleOrder WHERE Status IN(-1, 0, 1, 2)); "); } strSelectSql.Append(@" SELECT * FROM ##SaleOrder; DROP TABLE ##SaleOrder; COMMIT;"); DataSet ds = DbHelper.GetDataSet(CommandType.Text, strSelectSql.ToString(), parameter.ToArray()); jqgridparam.records = Convert.ToInt32(ds.Tables[0].Rows[0][0]); return(DatabaseReader.DataTableToList <SaleOrderViewModel>(ds.Tables[1])); } else { if (queryModel.QueryType == "Suspend") { strSql.Append(" AND SO.IsSuspended = 1"); } if (queryModel.QueryType == "Lock1") { strSql.Append(" AND SO.LockUserId = @LockUserId "); parameter.Add(DbFactory.CreateDbParameter("@LockUserId", userId)); } if (queryModel.QueryType == "Lock2") { strSql.Append(" AND SO.LockUserId != @LockUserId AND SO.LockUserId > '' "); parameter.Add(DbFactory.CreateDbParameter("@LockUserId", userId)); } return(new Repository <SaleOrderViewModel>().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); } }