/// <summary>
/// 通过订单系统编号(多个订单系统编号通过逗号分隔)取得订单确认关联信息列表
/// </summary>
/// <param name="SOSysNos">订单系统编号(多个订单系统编号通过逗号分隔)</param>
/// <returns></returns>
public List <PostIncomeConfirmInfo> GetConfirmedListBySOSysNo(string SOSysNos)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetPostIncomeConfirmedListBySONo");
dataCommand.ReplaceParameterValue("#ConfirmedSoSysNo", SOSysNos);
return(dataCommand.ExecuteEntityList <PostIncomeConfirmInfo>());
}
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 <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);
}
}
public virtual List <CustomerBasicInfo> GetCustomerBasicInfoBySysNoList(string sysNos)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetCustomerBasicInfoList");
cmd.CommandText = cmd.CommandText.Replace("@CustomerSysno", sysNos);
return(cmd.ExecuteEntityList <CustomerBasicInfo>());
}
public List <BasketItemsInfo> LoadGiftItemByBasketItem(List <int> productSysNoList)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySaleGiftByBasketItem");
command.CommandText = command.CommandText.Replace("@ItemList", productSysNoList.ToListString());
return(command.ExecuteEntityList <BasketItemsInfo>());
}
/// <summary>
/// 根据输入的PO单据号获取应付款中的PO单列表
/// </summary>
/// <param name="condition"></param>
/// <param name="entity"></param>
/// <returns></returns>
public virtual List <POItemCheckEntity> GetPOCheckList(List <int> condition, APInvoiceItemInputEntity entity)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("POInputCheck");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "POM.VendorSysNo"))
{
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"pay.OrderSysNo",
DbType.Int32,
condition);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"pay.OrderType",
DbType.Int32,
"@OrderType",
QueryConditionOperatorType.Equal,
entity.OrderType);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"pay.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
entity.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
return(dataCommand.ExecuteEntityList <POItemCheckEntity>());
}
}
public static List <int> GetSOList4Audit2SendMessage(int topCount, string companyCode)
{
List <int> result = new List <int>();
List <SingleValueEntity> queryResult = new List <SingleValueEntity>();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetSOList4Audit2SendMessage");
command.CommandText = command.CommandText.Replace("#TOPCOUNT#", topCount.ToString());
command.AddInputParameter("@CompanyCode", System.Data.DbType.StringFixedLength, companyCode);
queryResult = command.ExecuteEntityList <SingleValueEntity>();
if (queryResult != null &&
queryResult.Count > 0)
{
queryResult.ForEach(x => result.Add(x.Int32Value));
}
queryResult = null;
GC.Collect();
if (result == null)
{
result = new List <int>();
}
return(result);
}
public List <UserInfo> GetAllAuthSystemUser(string companyCode)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAllAuthSystemUser");
cmd.SetParameterValue("@CompanyCode", companyCode);
return(cmd.ExecuteEntityList <UserInfo>());
}
/// <summary>
/// 生成AR or AP科目报表数据
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public List <SAPInterfaceExchangeInfo> CreateOtherExcel(DateTime?begin, DateTime?end)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_ComBineOtherReportData");
command.SetParameterValue("@PostingDate", end);
return(AddOtherMTD(command.ExecuteEntityList <SAPInterfaceExchangeInfo>(), begin.Value, end.Value));
}
/// <summary>
/// 自印刷发票系统查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <CodeNamePair> InvoiceSelfStockQuery()
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintStocks");
object stocks = dataCommand.ExecuteScalar();
if (stocks != null)
{
List <CodeNamePair> result = new List <CodeNamePair>();
CustomDataCommand Command = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintStocksName");
Dictionary <string, string> dir = new Dictionary <string, string>();
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(Command.CommandText, Command, null, "s.SysNo ASC"))
{
List <string> stockSysNo = new List <string>();
foreach (string item in stocks.ToString().Split(','))
{
stockSysNo.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "s.SysNo", DbType.String, stockSysNo);
Command.CommandText = sqlBuilder.BuildQuerySql();
result = Command.ExecuteEntityList <CodeNamePair>();
}
return(result);
}
else
{
return(null);
}
}
public List <DepartmentInfo> GetDepartmentList(string companyCode, string languageCode)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAllDepartment");
cmd.AddInputParameter("@LanguageCode", DbType.AnsiStringFixedLength, languageCode);
return(cmd.ExecuteEntityList <DepartmentInfo>());
}
public static List <ProductPurchaseQueryBasicInfo> QueryProductPurchase(ProductPurchaseQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
#region
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductPurchase");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.SortFields,
StartRowIndex = queryFilter.PageIndex * queryFilter.PageSize,
MaximumRows = queryFilter.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));
List <ProductPurchaseQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductPurchaseQueryBasicInfo>();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(list);
#endregion
}
public List <ConsginToAccLogEntity> GetConsginToAccLogList(List <int> payPeriodTypes)
{
if (payPeriodTypes == null || payPeriodTypes.Count == 0)
{
return(new List <ConsginToAccLogEntity>());
}
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetConsginToAccLogList");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "Consign.SysNo desc"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "consign.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 0);
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "ex.SettlePeriodType", DbType.Int32, payPeriodTypes);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "consign.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, GlobalSettings.CompanyCode);
var lastdate = DateTime.Today.AddMonths(-1);
//欧亚强制为自然月
DateTime begin = new DateTime(lastdate.Year, lastdate.Month, 1);
DateTime End = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "consign.[CreateTime]", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, begin);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "consign.[CreateTime]", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, End);
command.CommandText = builder.BuildQuerySql();
return(command.ExecuteEntityList <ConsginToAccLogEntity>());
}
}
public static List <OrderAttachment> GetAttachmentListByProductSysNoList(List <int> productSysNoList)
{
if (productSysNoList == null || productSysNoList.Count <= 0)
{
return(new List <OrderAttachment>(0));
}
string strWhere = " pa.ProductSysNo IN (";
foreach (int num in productSysNoList)
{
strWhere += num.ToString() + ",";
}
strWhere = strWhere.TrimEnd(",".ToCharArray());
strWhere += ")";
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Promotion_GetAttachmentListByProductSysNoList");
cmd.CommandText = cmd.CommandText.Replace("#StrWhere#", strWhere);
List <OrderAttachment> list = cmd.ExecuteEntityList <OrderAttachment>((dr, entity) => {
entity["ProductStoreType"] = dr["ProductStoreType"] == null || string.IsNullOrWhiteSpace(dr["ProductStoreType"].ToString()) ? (int)ProductStoreType.Narmal : int.Parse(dr["ProductStoreType"].ToString().Trim());
//entity["TariffRate"] = dr["TariffRate"] == null || string.IsNullOrWhiteSpace(dr["TariffRate"].ToString()) ? 0m : decimal.Parse(dr["TariffRate"].ToString().Trim());
entity["Warranty"] = dr["Warranty"];
});
return(list);
}
/// <summary>
/// 分页查询新闻公告
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static QueryResult <NewsInfo> QueryNewsInfo(NewsQueryFilter filter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Content_QueryTopic");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, filter.ConvertToPaging(), "TopMost DESC,SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 1);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ReferenceSysNo", DbType.Int32, "@ReferenceSysNo", QueryConditionOperatorType.Equal, filter.ReferenceSysNo);
if (filter.NewsType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NewsType", DbType.Int32, "@NewsType", QueryConditionOperatorType.Equal, (int)filter.NewsType.Value);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageShowInheritance", DbType.Int32, "@PageShowInheritance", QueryConditionOperatorType.Equal, filter.PageShowInheritance);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ExpireDate", DbType.Time, "@ExpireDateBegin", QueryConditionOperatorType.MoreThan, DateTime.Now);
command.CommandText = sqlBuilder.BuildQuerySql();
var newsList = command.ExecuteEntityList <NewsInfo>();
var totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
QueryResult <NewsInfo> result = new QueryResult <NewsInfo>();
result.ResultList = newsList;
result.PageInfo = filter.ConvertToPageInfo(totalCount);
return(result);
}
}
public List <SubInvoiceInfo> GetListByCriteria(SubInvoiceInfo query)
{
List <SubInvoiceInfo> result = new List <SubInvoiceInfo>();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetSubInvoiceList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "SysNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOSysNo",
DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, query.SOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "WarehouseNo",
DbType.Int32, "@WarehouseNo", QueryConditionOperatorType.Equal, query.StockSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteEntityList <SubInvoiceInfo>((r, s) =>
{
string masterProductSysNoStr = r["MasterProductSysNoStr"].ToString();
if (!string.IsNullOrEmpty(masterProductSysNoStr))
{
s.MasterProductSysNo = masterProductSysNoStr
.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.ToList().ConvertAll(sysNoStr => int.Parse(sysNoStr));
}
}, true, true);
}
return(result);
}
public List <CSInfo> GetSOLogUpdate(string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Query_AllUpdaterLogUser");
command.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, companyCode);
return(command.ExecuteEntityList <CSInfo>());
}
/// <summary>
/// 查询我的团购券
/// </summary>
/// <param name="pageInfo"></param>
/// <param name="customerSysNo"></param>
/// <returns></returns>
public static QueryResult <GroupBuyingTicketInfo> QueryGroupBuyingTicketInfo(PageInfo pageInfo, int customerSysNo)
{
QueryResult <GroupBuyingTicketInfo> result = new QueryResult <GroupBuyingTicketInfo>();
result.PageInfo = pageInfo;
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = pageInfo.PageSize;
page.StartRowIndex = pageInfo.PageIndex * pageInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GroupBuying_QueryGroupBuyingTicketInfo");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "Ticket.OrderSysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Ticket.[CustomerSysNo]", DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, customerSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <GroupBuyingTicketInfo>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
result.PageInfo.TotalCount = totalCount;
return(result);
}
}
public List <ConsginToAccLogEntity> GetConsginToAccLogList(List <int> payPeriodTypes)
{
if (payPeriodTypes == null || payPeriodTypes.Count == 0)
{
return(new List <ConsginToAccLogEntity>());
}
var NewCosignAcctLogOnlineDate = GetNewCosignAcctLogOnlineDate();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetConsginToAccLogList");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "Consign.SysNo desc"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "consign.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 0);
//builder.ConditionConstructor.AddInCondition<int>(QueryConditionRelationType.AND, "ex.SettlePeriodType", DbType.Int32, payPeriodTypes);
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32, payPeriodTypes);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "consign.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, GlobalSettings.CompanyCode);
if (!string.IsNullOrEmpty(NewCosignAcctLogOnlineDate))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime ", DbType.DateTime, "@OnlineDate", QueryConditionOperatorType.LessThan, NewCosignAcctLogOnlineDate);
}
command.CommandText = builder.BuildQuerySql();
return(command.ExecuteEntityList <ConsginToAccLogEntity>());
}
}
/// <summary>
/// 获取历史记录
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public List <SOComplaintReplyInfo> GetHistory(SOComplaintInfo info)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Query_ComplainHistory");
cmd.AddInputParameter("@ComplainSysNo", DbType.Int32, info.SysNo.Value);
return(cmd.ExecuteEntityList <SOComplaintReplyInfo>());
}
/// <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);
}
}
public List <PublishedStorePageInfo> GetPublishedStorePageInfoListBySeller(int sellerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetPublishedStorePageInfoListBySeller");
command.SetParameterValue("@SellerSysNo", sellerSysNo);
return(command.ExecuteEntityList <PublishedStorePageInfo>());
}
/// <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);
}
}
/// <summary>
/// 获取在一定时间段物流拒收的订单
/// </summary>
/// <param name="companyCode">公司编码</param>
/// <returns>订单列表</returns>
public List <SOInfo> GetAutoRMASOInfoListInTime(string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_AutoRMACustomerSysNos");
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteEntityList <SOInfo>());
}
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 List <SOInfo> GetCPSList(string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_CPSSOList");
command.SetParameterValue("@CompanyCode", companyCode);
return(command.ExecuteEntityList <SOInfo>());
}
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>());
}
}
public List <PayItemInfo> QueryPayItems(PayableItemCriteriaInfo query)
{
int?orderType = null;
if (query.OrderType.HasValue)
{
orderType = (int)query.OrderType;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPayItems");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayStyle",
DbType.Int32, "@PayStyle", QueryConditionOperatorType.Equal, query.PayStyle);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PaySysNo",
DbType.Int32, "@PaySysNo", QueryConditionOperatorType.Equal, query.PaySysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, query.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, query.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrderType",
DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, orderType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrderSysNo",
DbType.Int32, "@OrderSysNo", QueryConditionOperatorType.Equal, query.OrderSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var result = dataCommand.ExecuteEntityList <PayItemInfo>();
return(result);
}
}
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);
}
/// <summary>
/// GetUserInfo
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public static QueryResponseDTO <List <ProductModel> > GetProductInfo(ProductDTO dto)
{
QueryResponseDTO <List <ProductModel> > responseDTO = new QueryResponseDTO <List <ProductModel> >();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductInfo");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
{
if (null != dto)
{
if (!string.IsNullOrEmpty(dto.ProductID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ProductID", DbType.AnsiString, "@ProductID", QueryConditionOperatorType.Equal, dto.ProductID);
}
if (dto.Stock > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Stock", DbType.Int32, "@Stock", QueryConditionOperatorType.MoreThanOrEqual, dto.Stock);
}
if (!string.IsNullOrEmpty(dto.CategoryID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "CategoryID", DbType.AnsiString, "@CategoryID", QueryConditionOperatorType.Equal, dto.CategoryID);
}
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "IsPublish", DbType.Boolean, "@IsPublish", QueryConditionOperatorType.Equal, dto.IsPublish);
if (dto.OriginalPrice > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "OriginalPrice", DbType.AnsiString, "@OriginalPrice", QueryConditionOperatorType.MoreThanOrEqual, dto.OriginalPrice);
}
if (dto.LastEditDate != null)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "LastEditDate", DbType.DateTime, "@LastEditDate", QueryConditionOperatorType.MoreThanOrEqual, dto.LastEditDate);
}
if (dto.Discount > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Discount", DbType.Int32, "@Discount", QueryConditionOperatorType.MoreThanOrEqual, dto.Discount);
}
}
//QueryData
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var result = dataCommand.ExecuteEntityList <ProductModel>();
responseDTO.ResultEntity = result;
}
responseDTO.TotalCount = dto.PagingInfo.TotalCount;
responseDTO.Code = ResponseStaticModel.Code.OK;
responseDTO.Message = ResponseStaticModel.Message.OK;
return(responseDTO);
}