public DataTable QueryStockShiftConfig(StockShiftConfigFilter filter, out int totalCount)
{
totalCount = 0;
if (filter == null)
{
return(null);
}
PagingInfoEntity pagingEntity = PageInfoToEntity(filter.PagingInfo);
pagingEntity.SortField = ShiftConfigSortFieldMapping(pagingEntity.SortField);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_Query_StockShiftConfig");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, "a.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.Int32, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockASysno",
DbType.Int32, "@WarehouseASysNumber", QueryConditionOperatorType.Equal, filter.OutStockSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockBSysno",
DbType.Int32, "@WarehouseBSysNumber", QueryConditionOperatorType.Equal, filter.InStockSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ShiftType",
DbType.Int32, "@TransferType", QueryConditionOperatorType.Equal, filter.ShiftType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SPLTimeInterval",
DbType.Int32, "@SPLInterval", QueryConditionOperatorType.Equal, filter.SPLInterval);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ShipTimeInterval",
DbType.Int32, "@ShipInterval", QueryConditionOperatorType.Equal, filter.ShipInterval);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["ShiftTypeString"] = CodeNamePairManager.GetName("Inventory", "StockShiftConfigShippingType", dr["ShiftType"].ToString());
}
}
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QueryVirtualInventoryLastVerifiedRequest(VirtualRequestQueryFilter queryFilter)
{
DataTable dt = new DataTable();
if (queryFilter.ProductSysNo.HasValue)
{
DataCommand dataCommand = DataCommandManager.GetDataCommand("GetLastVerifiedRequest");
dataCommand.SetParameterValue("@ProductSysNo", queryFilter.ProductSysNo.Value);
dataCommand.SetParameterValue("@CompanyCode", queryFilter.CompanyCode);
EnumColumnList list = new EnumColumnList();
list.Add("Status", typeof(VirtualRequestStatus));
dt = dataCommand.ExecuteDataTable(list);
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString());
}
}
}
return(dt);
}
public DataTable QueryCardItemOrdersRelated(InventoryItemCardQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
if (queryFilter == null || queryFilter.ProductSysNo == null || !queryFilter.ProductSysNo.HasValue)
{
totalCount = 0;
return(null);
}
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
CustomDataCommand dataCommand;
if (queryFilter.StockSysNo.HasValue)
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryItemsCardReLatedOrder");
dataCommand.SetParameterValue("@WarehouseSysNo", queryFilter.StockSysNo.Value);
}
else
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryItemsCardReLatedOrderWithOutWarehouseNumber");
}
dataCommand.SetParameterValue("@ItemSysNo", queryFilter.ProductSysNo.Value);
if (queryFilter.RMAInventoryOnlineDate.HasValue)
{
dataCommand.AddInputParameter("@RevertTime", DbType.DateTime, queryFilter.RMAInventoryOnlineDate.Value);
}
dataCommand.SetParameterValue("@CompanyCode", "8601");//[Mark][Alan.X.Luo 硬编码]
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "OrderTime DESC"))
{
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
#region 计算结存数量
if (null != dt)
{
dt.Columns.Add("OrderThenQty", typeof(int));
}
var orderThenQty = 0;
for (var i = dt.Rows.Count - 1; i >= 0; i--)
{
//OrderCodeFromDB=3 means po-调价(instock)(PO调价单)
if (dt.Rows[i]["OrderCode"] != null && dt.Rows[i]["OrderCode"].ToString() != "3")
{
orderThenQty += (dt.Rows[i]["OrderQty"] == null ? 0 : int.Parse(dt.Rows[i]["OrderQty"].ToString()));
}
dt.Rows[i]["OrderThenQty"] = orderThenQty;
}
#endregion
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["OrderNameString"] = CodeNamePairManager.GetName("Inventory", "InventoryCardOrderType", dr["OrderName"].ToString());
}
}
return(dt);
}
/// <summary>
/// 现金转积分 的 积分历史记录
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
private DataTable QueryCustomerPointLogByForOther(ECCentral.QueryFilter.Customer.CustomerPointLogQueryFilter queryCriteria, out int totalCount)
{
totalCount = 0;
var customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryOtherPointLog");
PagingInfoEntity pagingInfo = new PagingInfoEntity();
pagingInfo.SortField = queryCriteria.PagingInfo.SortBy;
pagingInfo.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
pagingInfo.MaximumRows = queryCriteria.PagingInfo.PageSize;
using (var sqlBuilder = new DynamicQuerySqlBuilder(customCommand.CommandText, customCommand,
pagingInfo,
"a.SysNo asc"))
{
if (queryCriteria.CustomerSysNo.HasValue && queryCriteria.CustomerSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerSysNo);
}
if (queryCriteria.PointType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ObtainType",
DbType.Int32,
"@ObtainType",
QueryConditionOperatorType.Equal,
queryCriteria.PointType);
}
if (queryCriteria.IsUseCreateDate)
{
if (queryCriteria.CreateTimeFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateTimeFrom);
}
if (queryCriteria.CreateTimeTo.HasValue)
{
//选择的DateTime只有日期部分，查询包含当日的信息所以日期加1
//2012-1-1 --- 2012-1-2 则为 >= 2012-1-1 0:0:0 and < 2012-1-3 0:0:0
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateTimeTo.Value);
}
}
//现金转积分
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, @"
(([ObtainType] = 19
AND (memo ='客户多汇款转积分'
OR memo = '客户多付款－产品调价'
OR memo = '客户多付款－拆单/运费转积分'
OR memo = '客户多付款－客户作废/更改订单需要转积分'))
OR [ObtainType] IN (39,37,44)) " );
//if (!string.IsNullOrEmpty(queryCriteria.CompanyCode))
//{
// sqlBuilder.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "a.CompanyCode",
// DbType.AnsiStringFixedLength,
// "@CompanyCode",
// QueryConditionOperatorType.Equal,
// queryCriteria.CompanyCode);
//}
customCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = customCommand.ExecuteDataTable();
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["PointLogTypeName"] = CodeNamePairManager.GetName("Customer", "AdjustPointType", dr["pointlogtype"].ToString());
}
}
totalCount = int.Parse(customCommand.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
/// <summary>
/// 积分历史记录
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
private DataTable QueryCustomerPointLogForAuto(ECCentral.QueryFilter.Customer.CustomerPointLogQueryFilter queryCriteria, out int totalCount)
{
totalCount = 0;
CustomDataCommand customCommand = null;
string orderField = string.Empty;
if (queryCriteria.ResultType == 1)
{
//获取历史
customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPointObtainLog");
orderField = "a.SysNo asc";
}
else
{
//消费历史
customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPointConsumeLog");
orderField = "a.SysNo desc";
}
PagingInfoEntity pagingInfo = new PagingInfoEntity();
pagingInfo.SortField = queryCriteria.PagingInfo.SortBy;
pagingInfo.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
pagingInfo.MaximumRows = queryCriteria.PagingInfo.PageSize;
using (var sqlBuilder = new DynamicQuerySqlBuilder(customCommand.CommandText, customCommand,
pagingInfo,
orderField))
{
if (queryCriteria.CustomerSysNo.HasValue && queryCriteria.CustomerSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerSysNo);
}
if (queryCriteria.OrderSysNo.HasValue && queryCriteria.OrderSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.SoSysNo",
DbType.Int32,
"@SoSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.OrderSysNo);
}
if (queryCriteria.PointType.HasValue)
{
string fieldName = queryCriteria.ResultType == 1 ? "a.ObtainType" : "a.ConsumeType";
string parameterName = queryCriteria.ResultType == 1 ? "@ObtainType" : "@ConsumeType";
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
fieldName,
DbType.Int32,
parameterName,
QueryConditionOperatorType.Equal,
queryCriteria.PointType);
}
if (queryCriteria.IsUseCreateDate)
{
if (queryCriteria.CreateTimeFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateTimeFrom);
}
if (queryCriteria.CreateTimeTo.HasValue)
{
//选择的DateTime只有日期部分，查询包含当日的信息所以日期加1
//2012-1-1 --- 2012-1-2 则为 >= 2012-1-1 0:0:0 and < 2012-1-3 0:0:0
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateTimeTo.Value);
}
}
//if (!string.IsNullOrEmpty(queryCriteria.CompanyCode))
//{
// sqlBuilder.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "a.CompanyCode",
// DbType.AnsiStringFixedLength,
// "@CompanyCode",
// QueryConditionOperatorType.Equal,
// queryCriteria.CompanyCode);
//}
customCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = customCommand.ExecuteDataTable();
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["PointLogTypeName"] = CodeNamePairManager.GetName("Customer", "AdjustPointType", dr["pointlogtype"].ToString());
}
}
totalCount = int.Parse(customCommand.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
/// <summary>
/// 查询虚库申请单
/// </summary>
/// <returns></returns>
public DataTable QueryVirtualRequest(VirtualRequestQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
DataTable dt = new DataTable();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualRequest");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText,
dataCommand, pagingInfo, "a.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.RequestStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.RequestStatus);
}
if (queryFilter.ProductSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo.Value);
}
if (queryFilter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo.Value);
}
if (queryFilter.StockSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo.Value);
}
if (queryFilter.CreateUserSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo",
DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreateUserSysNo.Value);
}
if (queryFilter.VirtualRequestType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VirtualType",
DbType.Int32, "@VirtualType", QueryConditionOperatorType.Equal, (int)queryFilter.VirtualRequestType);
}
if (queryFilter.StartDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.StartDate.Value);
}
if (queryFilter.EndDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDate.Value.AddDays(1));
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList list = new EnumColumnList();
list.Add("RequestStatus", typeof(VirtualRequestStatus));
dt = dataCommand.ExecuteDataTable(list);
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString());
}
}
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(dt);
}
public DataTable QueryModifiedVirtualRequest(VirtualRequestQueryFilter queryCriteria, out int totalCount)
{
DataTable dt = new DataTable();
if (queryCriteria == null)
{
totalCount = 0;
return(null);
}
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
StartRowIndex = 0,
MaximumRows = int.MaxValue
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVirtualRequest");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText,
dataCommand, null, "a.CreateTime DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode);
if (queryCriteria.RequestStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryCriteria.RequestStatus);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryCriteria.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryCriteria.StockSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo",
DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryCriteria.CreateUserSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VirtualType",
DbType.Int32, "@VirtualType", QueryConditionOperatorType.Equal, queryCriteria.VirtualRequestType);
if (queryCriteria.StartDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.StartDate);
}
if (queryCriteria.EndDate.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime",
DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryCriteria.EndDate.Value.AddDays(1));
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dataCommand.CommandText = dataCommand.CommandText.Replace("#BY#", "CreateDate DESC");
dt = dataCommand.ExecuteDataTable(new EnumColumnList()
{
{ "RequestStatus", typeof(ECCentral.BizEntity.Inventory.VirtualRequestStatus) }
});
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString());
}
}
totalCount = dt.Rows.Count;
}
return(dt);
}
