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