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