public System.Data.DataTable QueryPurchaseOrderList(QueryFilter.PO.PurchaseOrderQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
#region
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPOList");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
BuildSearchPOCondition(queryFilter, dataCommand, pagingInfo);
if (!string.IsNullOrEmpty(queryFilter.StatusList))
{
dataCommand.CommandText = dataCommand.CommandText.Replace("@Status", " @Status OR PO.Status IN (1,2,3,-2,5,6)");
}
EnumColumnList enumList = new EnumColumnList();
enumList.Add("status", typeof(PurchaseOrderStatus));
enumList.Add("PaySettleCompany", typeof(PaySettleCompany));
dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
#endregion
}
/// <summary>
/// 获取商家新订单数量
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static int GetNewOrderCount(DashboardQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Dashboard_GetNewOrderCount");
command.SetParameterValue(queryFilter);
return(command.ExecuteScalar <int>());
}
/// <summary>
/// SAP确认人查询
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryIPPUser(SAPIPPUserQueryFilter query, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = query.PagingInfo.SortBy;
pagingEntity.MaximumRows = query.PagingInfo.PageSize;
pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySAPIPPUser");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingEntity, "I.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.PayType",
DbType.Int32, "@PayType", QueryConditionOperatorType.Equal, query.PayType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.CustID",
DbType.String, "@CustID", QueryConditionOperatorType.Equal, query.CustID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.SystemConfirmID",
DbType.String, "@SystemConfirmID", QueryConditionOperatorType.Equal, query.SystemConfirmID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.CompanyCode",
DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add("Status", typeof(SAPStatus));
DataTable dt = dataCommand.ExecuteDataTable(enumColumnList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
private List <ShiftSysnoProduct> GetStockAToStockB(ProductShiftDetailReportQueryFilter query, string prodtuctSysnos)
{
List <ShiftSysnoProduct> result = null;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductShiftDetailCompanyProductInfoStackAToStackB");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, ToPagingInfo(query.PagingInfo), "ProductSysNo asc"))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "1=1");
dataCommand.AddInputParameter("@SapCoCodeFrom", DbType.String, query.OutCompany);
dataCommand.AddInputParameter("@SapCoCodeTo", DbType.String, query.EnterCompany);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.OutTime",
DbType.DateTime, "@OutTimeStart", QueryConditionOperatorType.MoreThanOrEqual, query.OutTimeStart);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.OutTime",
DbType.DateTime, "@OutTimeEnd", QueryConditionOperatorType.LessThanOrEqual, query.OutTimeEnd);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.CompanyCode",
DbType.String, "@CompanyCode", QueryConditionOperatorType.LessThanOrEqual, query.CompanyCode);
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " stItem.GoldenTaxNo is null ");
if (prodtuctSysnos.Length < 1000)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " stItem.ProductSysNo in (" + prodtuctSysnos + ") ");
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteEntityList <ShiftSysnoProduct>();
}
return(result);
}
public virtual DataTable QueryCallsEventLog(CustomerCallsEventLogFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCallsEventLog");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.Customer_CallingSysNo",
DbType.Int32,
"@CallsEventsSysNo ",
QueryConditionOperatorType.Equal,
filter.CallsEventsSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enunlist = new EnumColumnList();
enunlist.Add(3, typeof(CallsEventsStatus));
enunlist.Add(7, typeof(CustomerCallReason));
CodeNamePairColumnList cpList = new CodeNamePairColumnList();
cpList.Add(2, "Customer", "RecordOrigion");
DataTable dt = cmd.ExecuteDataTable(enunlist, cpList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public List <GatherSettleInfo> GetConsginToAccLogList(List <int> payPeriodTypes, DateTime maxOrderEndData, int merchantSysNo)
{
if (payPeriodTypes == null || payPeriodTypes.Count == 0)
{
return(new List <GatherSettleInfo>());
}
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetGatherSettleList");
command.CommandTimeout = 120;
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "Consign.SysNo desc"))
{
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32, payPeriodTypes);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "main.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, GlobalSettings.CompanyCode);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "s.ReferenceType", DbType.String, "@ReferenceType", QueryConditionOperatorType.IsNull, DBNull.Value);
if (merchantSysNo > 0)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ship.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, merchantSysNo);
}
command.CommandText = builder.BuildQuerySql();
command.CommandText = command.CommandText.Replace("/*#RelaceWhere#*/", " Where OutOrRefundDateTime < '" + maxOrderEndData.ToString() + "'");
return(command.ExecuteEntityList <GatherSettleInfo>());
}
}
/// <summary>
/// 查询品牌
/// </summary>
/// <returns></returns>
public virtual DataTable QueryBrand(BrandQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetBrandListByQuery");
dataCommand.SetParameterValue("@BrandName", string.IsNullOrEmpty(queryCriteria.BrandNameLocal)?null:string.Format("%{0}%", queryCriteria.BrandNameLocal));
dataCommand.SetParameterValue("@ManufacturerName", string.IsNullOrEmpty(queryCriteria.ManufacturerName) ? null : string.Format("%{0}%", queryCriteria.ManufacturerName));
dataCommand.SetParameterValue("@Status", queryCriteria.Status);
dataCommand.SetParameterValue("@BrandSysNo", queryCriteria.BrandSysNo);
dataCommand.SetParameterValue("@ManufacturerSysNo", queryCriteria.ManufacturerSysNo);
dataCommand.SetParameterValue("@Category1SysNo", queryCriteria.Category1SysNo);
dataCommand.SetParameterValue("@Category2SysNo", queryCriteria.Category2SysNo);
dataCommand.SetParameterValue("@Category3SysNo", queryCriteria.Category3SysNo);
dataCommand.SetParameterValue("@IsAuthorized", queryCriteria.IsAuthorized);
dataCommand.SetParameterValue("@AuthorizedStatus", queryCriteria.AuthorizedStatus);
dataCommand.SetParameterValue("@BrandStory", queryCriteria.IsBrandStory);
dataCommand.SetParameterValue("@SortField", queryCriteria.PagingInfo.SortBy);
dataCommand.SetParameterValue("@PageSize", queryCriteria.PagingInfo.PageSize);
dataCommand.SetParameterValue("@PageCurrent", queryCriteria.PagingInfo.PageIndex);
dataCommand.SetParameterValue("@Priority", queryCriteria.Priority == "" ? null : queryCriteria.Priority);
EnumColumnList enumList = new EnumColumnList
{
{ "Status", typeof(ValidStatus) },
};
DataTable dt = new DataTable();
dt = dataCommand.ExecuteDataTable(enumList);
totalCount = (int)dataCommand.GetParameterValue("@TotalCount");
return(dt);
}
/// <summary>
/// 月销售情况报表
/// </summary>
/// <param name="filter">条件</param>
/// <returns></returns>
public static StatementResult <ArrayList> MonthSalesData(MonthSalesFilter filter)
{
StatementResult <ArrayList> result = new StatementResult <ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("MonthSalesData");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate DESC"))
{
DateTime beginDate = DateTime.Parse(string.Format("{0}-{1}-01", filter.Year, filter.Month));
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@BeginLogDate", QueryConditionOperatorType.MoreThanOrEqual, beginDate);
DateTime endDate = beginDate.AddMonths(1);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@EndLogDate", QueryConditionOperatorType.LessThan, endDate);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
result.TotalValue = Convert.ToDecimal(cmd.GetParameterValue("@TotalAmount"));
return(result);
}
}
private void CreateROAdjustItem(GatherSettlementInfo entity, string solist)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("InsertConsignSettleGatherRO_Adjust");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "b.SONumber DESC"))
{
if (entity.VendorInfo.SysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ship.MerchantSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, entity.VendorInfo.SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "main.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, entity.CompanyCode);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "s.ReferenceType", DbType.AnsiStringFixedLength, "@ReferenceType", QueryConditionOperatorType.IsNull, DBNull.Value);
}
command.CommandText = builder.BuildQuerySql();
string strSQL3 = string.Empty;
// ##OrderDate3##
if (entity.CreateDateFrom.HasValue)
{
strSQL3 += " AND main.CreateTime>'" + entity.CreateDateFrom.Value.ToShortDateString() + "'";
}
if (entity.CreateDateTo.HasValue)
{
strSQL3 += " AND main.CreateTime<'" + entity.CreateDateTo.Value.AddDays(1).ToShortDateString() + "'";
}
command.CommandText = command.CommandText.Replace("##OrderDate3##", strSQL3);
}
command.CommandText = command.CommandText.Replace("#SONumber#", solist)
.Replace("#StoreCompanyCode#", "'" + entity.CompanyCode + "'")
.Replace("#CompanyCode#", "'" + entity.CompanyCode + "'")
.Replace("#InUser#", "'" + entity.CreateUserName + "'")
.Replace("#SettlementSysNo#", entity.SysNo.Value.ToString());
command.ExecuteNonQuery();
}
public DataTable QuerySOLog(QueryFilter.Common.LogQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetSOLog");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "[LOG].[SysNo] DESC"))
{
SetTheSearchCondition(builder, filter);
command.CommandText = builder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(LogSOStatus));
enumList.Add("TicketType", typeof(c.BizLogType));
DataTable dt = command.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询日志
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static QueryResult <Logs> QueryLogs(LogsQueryFilter filter)
{
QueryResult <Logs> result = new QueryResult <Logs>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryLogs");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Classes", DbType.Int32,
"@Classes", QueryConditionOperatorType.Like, filter.Classes);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Section", DbType.Int32,
"@Section", QueryConditionOperatorType.Like, filter.Section);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Family", DbType.Int32,
"@Family", QueryConditionOperatorType.Like, filter.Family);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RefenceSysNo", DbType.Int32,
"@RefenceSysNo", QueryConditionOperatorType.Like, filter.RefenceSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Contents", DbType.String,
"@Contents", QueryConditionOperatorType.Like, filter.Contents);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <Logs>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <summary>
/// 根据PaySysNo查询单据状态
/// </summary>
/// <param name="sysNo">PaySysNo</param>
/// <returns></returns>
public string QueryInvoiceStatusByPaySysNo(int sysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_GetInvoiceStatusByPaySysNo");
cmd.SetParameterValue("@PaySysNo", sysNo);
return(cmd.ExecuteScalar <string>());
}
public DataTable QueryPurchaseOrderBatchNumberList(PurchaseOrderBatchNumberQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchPOBatchNumberList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "batch.ExpDate desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "batch.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "batch.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
if (pagingInfo != null)
{
dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
}
}
dt = dataCommand.ExecuteDataTable("Status", typeof(PurchaseOrderBatchInfoStatus));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
public DataTable QueryRMAList(PurchaseOrderQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetRMA");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "VendorName,OutTime,PMUserSysNo"))
{
string sql = "WHERE 1=1 ";
if (queryFilter.PMList != null && queryFilter.PMList.Count > 0)
{
sql += "AND Product.PMUserSysNo IN (";
foreach (var pm in queryFilter.PMList)
{
sql += pm.ToString() + " , ";
}
sql = sql.Substring(0, sql.LastIndexOf(",") - 1) + ")";
}
sql += " AND RMA_Register.ResponseDesc IS NOT NULL AND DateAdd(Day,15,RMA_OutBound.OutTime)< '" + DateTime.Now + "'";
sql += " AND RMA_Register.Status=1 AND RMA_Register.OutBoundStatus = 1 ";
sql += " AND RMA_OutBound.CompanyCode= '" + queryFilter.CompanyCode + "'";
dataCommand.CommandText = builder.BuildQuerySql().Replace("StrWhere", sql);
dt = dataCommand.ExecuteDataTable();
totalCount = (int)dataCommand.GetParameterValue("@TotalCount");
}
return(dt);
}
public List <BizEntity.Customer.QCSubject> GetAllQCSubject(QueryFilter.Customer.QCSubjectFilter filter)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QCSubjectQuery");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "OrderNum ASC "))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"[Status]",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
return(cmd.ExecuteEntityList <QCSubject>());
}
#endregion
}
public virtual List <CustomerBasicInfo> GetCustomerBasicInfoBySysNoList(string sysNos)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetCustomerBasicInfoList");
cmd.CommandText = cmd.CommandText.Replace("@CustomerSysno", sysNos);
return(cmd.ExecuteEntityList <CustomerBasicInfo>());
}
public static QueryResponseDTO <List <ShoppingCartModel> > GetShoppingCartInfo(ShoppingCartDTO dto)
{
QueryResponseDTO <List <ShoppingCartModel> > responseDTO = new QueryResponseDTO <List <ShoppingCartModel> >();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetShoppingCartInfo");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
{
if (null != dto)
{
if (!string.IsNullOrEmpty(dto.UserID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "UserID", DbType.AnsiString, "@UserID", QueryConditionOperatorType.Equal, dto.UserID);
}
if (!string.IsNullOrEmpty(dto.SellerID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "SellerID", DbType.AnsiString, "@SellerID", QueryConditionOperatorType.Equal, dto.SellerID);
}
}
//QueryData
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var result = dataCommand.ExecuteEntityList <ShoppingCartModel>();
responseDTO.ResultEntity = result;
}
responseDTO.TotalCount = dto.PagingInfo.TotalCount;
responseDTO.Code = ResponseStaticModel.Code.OK;
responseDTO.Message = ResponseStaticModel.Message.OK;
return(responseDTO);
}
/// <summary>
/// 查询奖品发放记录
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static QueryResult <GiftsGrantRecord> QueryGiftsGrantRecord(GiftsGrantRecordQueryFilter filter)
{
QueryResult <GiftsGrantRecord> result = new QueryResult <GiftsGrantRecord>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftsGrantRecord");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "M.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GiftSysNo", DbType.Int32,
"@GiftSysNo", QueryConditionOperatorType.Equal, filter.GiftSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantCounts", DbType.Int64,
"@GrantCounts", QueryConditionOperatorType.Like, filter.GrantCounts);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantDate", DbType.DateTime,
"@GrantDate", QueryConditionOperatorType.Like, filter.GrantDate);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPerson", DbType.String,
"@GrantPerson", QueryConditionOperatorType.Like, filter.GrantPerson);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPlace", DbType.String,
"@GrantPlace", QueryConditionOperatorType.Like, filter.GrantPlace);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <GiftsGrantRecord>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
public void SetMultiLanguageBizEntity(MultiLanguageBizEntity entity)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SetMultiLanguageTemplate");
string tableName = entity.MappingTable;
string pkcriteria = string.Format(" SysNo={0} AND LanguageCode='{1}'", entity.SysNo, entity.LanguageCode.Replace("'", "''"));
string insertColumns = string.Empty;
string insertValues = string.Empty;
string updateColumns = string.Empty;
insertColumns = " LanguageCode,SysNo,";
insertValues = " @LanguageCode,@SysNo,";
cmd.AddInputParameter("@LanguageCode", System.Data.DbType.AnsiStringFixedLength);
cmd.SetParameterValue("@LanguageCode", entity.LanguageCode);
cmd.AddInputParameter("@SysNo", System.Data.DbType.AnsiStringFixedLength);
cmd.SetParameterValue("@SysNo", entity.SysNo);
foreach (PropertyItem propItem in entity.PropertyItemList)
{
insertColumns += " " + propItem.Field + ",";
insertValues += " @" + propItem.Field + ",";
updateColumns += string.Format(" {0}=@{0},", propItem.Field);
cmd.AddInputParameter("@" + propItem.Field, System.Data.DbType.AnsiStringFixedLength);
cmd.SetParameterValue("@" + propItem.Field, propItem.Value);
}
insertColumns = insertColumns.TrimEnd(',');
insertValues = insertValues.TrimEnd(',');
updateColumns = updateColumns.TrimEnd(',');
cmd.CommandText = cmd.CommandText.Replace("#tableName", tableName).Replace("#pkcriteria", pkcriteria).Replace("#insertColumns", insertColumns).Replace("#insertValues", insertValues).Replace("#updateColumns", updateColumns);
cmd.ExecuteNonQuery();
}
private DataTable GetAllCanbePayList(CanBePayOrderQueryFilter filter, out int totalCount)
{
DataTable result = null;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetAllCanBePayList");
PagingInfoEntity pagingInfo = CreatePagingInfo(filter);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "OrderSysNo desc"))
{
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
StringBuilder sb1 = new StringBuilder();
sb1.Append(" WHERE 1=1");
sb1.Append(" AND po.IsConsign <> 1");
StringBuilder sb2 = new StringBuilder();
sb2.Append(" WHERE 1=1");
sb1.Append(" AND po.CompanyCode = @CompanyCode");
sb2.Append(" AND vendorsettle.CompanyCode = @CompanyCode");
dataCommand.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, filter.CompanyCode);
if (!string.IsNullOrEmpty(filter.OrderID))
{
sb1.Append(" AND po.SysNo LIKE @OrderID");
sb2.Append(" AND vendorsettle.SettleID LIKE @OrderID");
dataCommand.AddInputParameter("@OrderID", DbType.String, filter.OrderID.Trim() + "%");
}
dataCommand.CommandText = dataCommand.CommandText
.Replace("#StrWhere1#", sb1.ToString())
.Replace("#StrWhere2#", sb2.ToString());
result = ExecuteDataTable(dataCommand, out totalCount);
}
return(result);
}
/// <summary>
/// 查询权限
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static QueryResult <Functions> QueryFunctions(FunctionsQueryFilter filter)
{
QueryResult <Functions> result = new QueryResult <Functions>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryFunctions");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
"@Status1", QueryConditionOperatorType.NotEqual, CommonStatus.Deleted);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "FunctionName", DbType.String,
"@FunctionName", QueryConditionOperatorType.Like, filter.FunctionName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "FunctionKey", DbType.String,
"@FunctionKey", QueryConditionOperatorType.Like, filter.FunctionKey);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Description", DbType.String,
"@Description", QueryConditionOperatorType.Like, filter.Description);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
"@Status", QueryConditionOperatorType.Equal, filter.Status);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <Functions>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
private DataTable GetPOCanbePayList(CanBePayOrderQueryFilter filter, out int totalCount)
{
DataTable result = null;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetPOCanBePayList");
PagingInfoEntity pagingInfo = CreatePagingInfo(filter);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "po.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsConsign",
DbType.Int32, "@IsConsign", QueryConditionOperatorType.NotEqual, 1);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.POID",
DbType.String, "@POID", QueryConditionOperatorType.Like, filter.OrderID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ETP",
DbType.DateTime, "@ETPFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.POETPFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ETP",
DbType.DateTime, "@ETPTo", QueryConditionOperatorType.LessThan, filter.POETPTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.POStatus);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = ExecuteDataTable(dataCommand, out totalCount);
}
return(result);
}
//验证同级分类名称是否重复
public bool CheckNameDuplicate(string name, int excludeSysNo, ECCategoryLevel level, string companyCode, string channelID, string parentSysnoList)
{
//DataCommand cmd = DataCommandManager.GetDataCommand("ECCategory_CheckNameDuplicate");
//cmd.SetParameterValue("@ExcludeSysNo", excludeSysNo);
//cmd.SetParameterValue("@CategoryName", name);
//cmd.SetParameterValue("@Level", level);
//cmd.SetParameterValue("@CompanyCode", companyCode);
////TODO:添加渠道参数
//cmd.SetParameterValue("@parentSysnoList", parentSysnoList);
//return cmd.ExecuteScalar<int>() > 0;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ECCategory_CheckNameDuplicate");
string expendStr = string.Format("AND cr.ParentSysno in ({0})", parentSysnoList);
if (level != ECCategoryLevel.Category1)
{
cmd.CommandText = cmd.CommandText.Replace("#parentSysnoList", expendStr);
}
else
{
cmd.CommandText = cmd.CommandText.Replace("#parentSysnoList", "");
}
cmd.SetParameterValue("@ExcludeSysNo", excludeSysNo);
cmd.SetParameterValue("@CategoryName", name);
cmd.SetParameterValue("@Level", level);
cmd.SetParameterValue("@CompanyCode", companyCode);
//TODO:添加渠道参数
return(cmd.ExecuteScalar <int>() > 0);
}
/// <summary>
/// 查询配件
/// </summary>
/// <returns></returns>
public virtual DataTable QueryAccessory(AccessoryQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAccessory");
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PagingInfo.SortBy,
StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
MaximumRows = queryCriteria.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "AccessoryInfo.SysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.AccessoryName))
{
dataCommand.AddInputParameter("@AccessoryName", DbType.String, queryCriteria.AccessoryName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"AccessoriesName",
DbType.String, "@AccessoryName",
QueryConditionOperatorType.Like,
queryCriteria.AccessoryName);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public static DataTable QueryStockShipType(StockShipTypeQueryFilter filter, out int count)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("Query_Stock_QueryStockShipType");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "ss.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ss.SellerSysNo", DbType.String,
"@SellerSysNo", QueryConditionOperatorType.Equal,
filter.SellerSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ss.StockSysNo", DbType.AnsiStringFixedLength,
"@StockSysNo", QueryConditionOperatorType.Equal,
filter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ss.ShipTypeSysNo", DbType.AnsiStringFixedLength,
"@ShipTypeSysNo", QueryConditionOperatorType.Equal,
filter.ShipTypeSysNo);
command.CommandText = builder.BuildQuerySql();
count = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(command.ExecuteDataTable());
}
}
public List <BasketItemsInfo> LoadGiftItemByBasketItem(List <int> productSysNoList)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySaleGiftByBasketItem");
command.CommandText = command.CommandText.Replace("@ItemList", productSysNoList.ToListString());
return(command.ExecuteEntityList <BasketItemsInfo>());
}
public DataTable QueryWPMessageByUserSysNo(WPMessageQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("WPMessage_QueryMessage");
using (DynamicQuerySqlBuilder queryBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, ToPagingInfo(filter.PageIndex, filter.PageSize, filter.SortField), "m.CategorySysNo DESC,m.SysNo"))
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CreateTime]", DbType.DateTime, "@BeginCreateTime", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginCreateTime);
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CreateTime]", DbType.DateTime, "@EndCreateTime", QueryConditionOperatorType.LessThan, filter.EndCreateTime);
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CategorySysNo]", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, filter.CategorySysNo);
if (filter.WPMessageStatus.HasValue)
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.WPMessageStatus);
}
else
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.NotEqual, WPMessageStatus.Completed);
}
queryBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " wc.[Status] = 1");
command.CommandText = queryBuilder.BuildQuerySql();
}
command.SetParameterValue("@UserSysNo", filter.UserSysNo);
DataTable dt = command.ExecuteDataTable();
object o = command.GetParameterValue("@TotalCount");
dataCount = o == null ? 0 : (int)o;
return(dt);
}
public BasketItemsInfo QueryGiftInfo(int productSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftInfo");
command.CommandText = command.CommandText.Replace("@ProductSysNo", productSysNo.ToString());
return(command.ExecuteEntity <BasketItemsInfo>());
}
/// <summary>
/// 查询配送方式-产品
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryShipTypeProductList(ShipTypeProductQueryFilter filter, out int totalCount)
{
totalCount = 0;
object _itemRange = null;;
object _type = null;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ShipTypeProduct_QueryShipTypeProduct");
PagingInfoEntity pagingInfo = ToPagingInfo(filter.PagingInfo);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SPI.SysNo DESC"))
{
if (filter.ShipTypeProductType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.[Type]", DbType.String, "@Type", QueryConditionOperatorType.Equal, EnumCodeMapper.TryGetCode(filter.ShipTypeProductType, out _type));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.Description", DbType.String, "@Description", QueryConditionOperatorType.Like, filter.Description);
if (filter.ProductRange.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.ItemRange", DbType.String, "@ItemRange", QueryConditionOperatorType.Equal, EnumCodeMapper.TryGetCode(filter.ProductRange, out _itemRange));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPM.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShippingType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPM.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, filter.WareHouse);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList EnumList = new EnumColumnList();
EnumList.Add("ItemRange", typeof(ECCentral.BizEntity.Common.ProductRange));
EnumList.Add("Type", typeof(ECCentral.BizEntity.Common.ShipTypeProductType));
DataTable dt = cmd.ExecuteDataTable(EnumList);
totalCount = int.Parse(cmd.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
public DataTable QueryShipTypePayType(QueryFilter.Common.ShipTypePayTypeQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryShipTypePayType");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "sp.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ShipTypeSysNo", DbType.Int32,
"@ShipTypeSysNo", QueryConditionOperatorType.Equal,
filter.ShipTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PayTypeSysNo", DbType.Int32,
"@PayTypeSysNo", QueryConditionOperatorType.Equal,
filter.PayTypeSysNo);
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
