/// <summary>
/// 订单查询
/// </summary>
/// <param name="fBillNo"></param>
/// <param name="fEmpName">业务员</param>
/// <param name="page"></param>
/// <returns></returns>
public ActionResult QueryOrderList(QueryOrderViewModel viewModel)
{
var model = new ReturnModel();
if (!apiService.IsExistsUserGroupFieldDisplayed(viewModel.userGroupNumber))
{
model.Code = -5;
model.Message = "用户所在分组，未设置显示字段";
return(Json(model));
}
var list = apiService.QueryOrderList(viewModel);
if (list == null || list.Count == 0)
{
model.Code = 0;
}
model.Data = list;
return(Json(model));
}
public ActionResult GetOrderList(QueryOrderViewModel queryModel, JqGridParam jqgridparam)
{
try
{
Stopwatch watch = CommonHelper.TimerStart();
List <SaleOrderViewModel> listData = null;
if (queryModel.QueryType == "PrintBatch")
{
if (string.IsNullOrWhiteSpace(queryModel.PrintBatchId))
{
throw new Exception("打印批次号为空");
}
listData = _orderBll.GetOrderListByPrintBatch(queryModel.PrintBatchId, jqgridparam);
}
else if (!string.IsNullOrWhiteSpace(queryModel.QueryType))
{
queryModel.Status = (int)OrderStatus.WaitPick;
listData = _orderBll.GetOrderList(queryModel, jqgridparam);
}
else
{
listData = new List <SaleOrderViewModel>();
}
var jsonData = new
{
total = jqgridparam.total,
page = jqgridparam.page,
records = jqgridparam.records,
costtime = CommonHelper.TimerEnd(watch),
rows = listData,
};
return(Content(jsonData.ToJson()));
}
catch (Exception ex)
{
BaseSysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误：" + ex.Message);
return(null);
}
}
/// <summary>
/// 查询订单
/// </summary>
/// <param name="fBillNo"></param>
/// <param name="page"></param>
/// <returns></returns>
public List <SalesOrder> QueryOrderList(QueryOrderViewModel model)
{
try
{
int pageSize = 10;
string queryValue = "";// 是否
var sqlParameter = new List <SqlParameter>()
{
new SqlParameter("@PageIndex", (model.page - 1) * pageSize),
new SqlParameter("@PageSize", model.page * pageSize)
};
string sql = "select ROW_NUMBER() over(order by FDate desc) rownumber, FBillNo,FCustName,FName,FDeptName from tm_v_SEOrderQuery where 1=1";
if (!string.IsNullOrEmpty(model.fBillNo))
{
sql += " and (FBillNo like @FBillNo or FEmpName like @FBillNo or FCustName like @FBillNo)";
sqlParameter.Add(new SqlParameter {
ParameterName = "@FBillNo", Value = "%" + model.fBillNo + "%", SqlDbType = SqlDbType.NVarChar
});
}
if (!string.IsNullOrEmpty(model.fEmpName)) //根据业务人员名称查询
{
sql += " and FEmpName like @FEmpName";
sqlParameter.Add(new SqlParameter {
ParameterName = "@FEmpName", Value = "%" + model.fEmpName + "%", SqlDbType = SqlDbType.NVarChar
});
}
if (!model.isConfirm.Equals("-1")) // 是否确认
{
sql += "and FConfirmStatus=@FConfirmStatus";
//总经理未审核通过的为特批未通过
if (model.isConfirm.Equals("2"))
{
queryValue = "特批未通过";
}
else if (model.isConfirm.Equals("0"))
{
queryValue = "否";
}
else if (model.isConfirm.Equals("1"))
{
queryValue = "是";
}
sqlParameter.Add(new SqlParameter {
ParameterName = "@FConfirmStatus", Value = queryValue, SqlDbType = SqlDbType.NVarChar
});
}
if (!model.isStock.Equals("-1")) //是否发货
{
sql += " and FStockStatus=@FStockStatus";
queryValue = model.isStock == "1" ? "是" : "否";
sqlParameter.Add(new SqlParameter {
ParameterName = "@FStockStatus", Value = queryValue, SqlDbType = SqlDbType.NVarChar
});
}
if (!model.isInvoice.Equals("-1")) //是否开票
{
sql += " and FInvoiceStatus=@FInvoiceStatus";
queryValue = model.isInvoice == "1" ? "是" : "否";
sqlParameter.Add(new SqlParameter {
ParameterName = "@FInvoiceStatus", Value = queryValue, SqlDbType = SqlDbType.NVarChar
});
}
if (!model.isReceive.Equals("-1")) //是否收款
{
sql += " and FReceiveStatus=@FReceiveStatus";
queryValue = model.isReceive == "1" ? "是" : "否";
sqlParameter.Add(new SqlParameter {
ParameterName = "@FReceiveStatus", Value = queryValue, SqlDbType = SqlDbType.NVarChar
});
}
string cmdText = "select* from(" + sql + ") as t"
+ " where rownumber>@PageIndex and rownumber<=@PageSize";
var res = SqlHelper.ExecuteDataTable(CommandType.Text, cmdText, sqlParameter.ToArray());
var list = res.AsEnumerable().Select(row => new SalesOrder
{
Id = Convert.ToInt32(row["rownumber"]),
FBillNo = row["FBillNo"].ToString(),
FCustName = row["FCustName"].ToString(),
FName = row["FName"].ToString(),
FDeptName = row["FDeptName"].ToString()
}).ToList();
return(list);
}
catch (Exception)
{
throw;
}
}
/// <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));
}
}
