/// <summary>
/// 查询邮件短信模板
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static QueryResult<EmailAndSMSTemplates> QueryEmailAndSMSTemplates(EmailAndSMSTemplatesQueryFilter filter)
{
QueryResult<EmailAndSMSTemplates> result = new QueryResult<EmailAndSMSTemplates>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryEmailAndSMSTemplates");
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, "Category", DbType.String,
"@Category", QueryConditionOperatorType.Like, filter.Category);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Templates", DbType.String,
"@Templates", QueryConditionOperatorType.Like, filter.Templates);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
"@Status", QueryConditionOperatorType.Equal, filter.Status);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList<EmailAndSMSTemplates>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return result;
}
}
/// <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>
/// 查询奖品
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static QueryResult<Gift> QueryGifts(GiftQueryFilter filter)
{
QueryResult<Gift> result = new QueryResult<Gift>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGifts");
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, "GiftName", DbType.String,
"@GiftName", QueryConditionOperatorType.Like, filter.GiftName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GiftID", DbType.String,
"@GiftID", QueryConditionOperatorType.Like, filter.GiftID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Descriptions", DbType.String,
"@Descriptions", QueryConditionOperatorType.Like, filter.Descriptions);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "MarketPrice", DbType.Decimal,
"@MarketPrice", QueryConditionOperatorType.Like, filter.MarketPrice);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
"@Status", QueryConditionOperatorType.Equal, filter.Status);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList<Gift>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return result;
}
}
/// <summary>
/// 查询公告
/// </summary>
/// <param name="filter">查询条件</param>
public static QueryResult<Notices> QueryNotices(NoticesQueryFilter filter)
{
QueryResult<Notices> result = new QueryResult<Notices>();
PagingInfoEntity page = new PagingInfoEntity();
page.SortField = (filter.SortList == null || filter.SortList.Count == 0) ? null : filter.SortListToString();
page.MaximumRows = filter.PageSize;
page.StartRowIndex = (filter.PageIndex - 1) * filter.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryNotices");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Contents", DbType.String, "@Contents",
QueryConditionOperatorType.Like, filter.Contents);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status",
QueryConditionOperatorType.Equal, filter.Status);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList<Notices>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
result.PagingInfo = new PagingInfo() { PageIndex = filter.PageIndex, PageSize = filter.PageSize, TotalCount = totalCount };
return result;
}
}
/// <summary>
/// 日销售情况报表
/// </summary>
/// <param name="filter">条件</param>
/// <returns></returns>
public static StatementResult<ArrayList> DaySalesData(DaySalesFilter filter)
{
StatementResult<ArrayList> result = new StatementResult<ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("DaySalesData");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate DESC"))
{
DateTime beginDate = DateTime.Parse(filter.Date);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@BeginLogDate", QueryConditionOperatorType.MoreThanOrEqual, beginDate);
DateTime endDate = beginDate.AddDays(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;
}
}
/// <summary>
/// 查询近7天未下过订单的用户
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static StatementResult<ArrayList> QuerySevenDayNotHaveOrder(QueryCategoryCustomersFilter filter)
{
StatementResult<ArrayList> result = new StatementResult<ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySevenDayNotHaveOrder");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "CustomerID DESC"))
{
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return result;
}
}
/// <summary>
/// 用户流失分析报表
/// </summary>
/// <param name="filter">条件</param>
/// <returns></returns>
public static StatementResult<ArrayList> CustomersLoseAnalyze(CustomersLoseAnalyzeFilter filter)
{
StatementResult<ArrayList> result = new StatementResult<ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("CustomersLoseAnalyze");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate ASC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@LogDate", QueryConditionOperatorType.LessThan, DateTime.Parse(DateTime.Now.AddDays(-1 * filter.Day).ToShortDateString()));
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return result;
}
}
/// <summary>
/// 查询用户
/// </summary>
/// <param name="pageIndex">取第几页</param>
/// <param name="pageSize">每页显示几条</param>
/// <param name="userID">用户登录名</param>
/// <param name="status">状态</param>
/// <param name="payUserBeginTime">充值使用时间起</param>
/// <param name="payUseEndTime">充值使用时间止</param>
/// <returns></returns>
public static PageList<List<User>> QueryUser(int pageIndex, int pageSize, string userID, int status, DateTime payUserBeginTime, DateTime payUseEndTime)
{
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = pageSize;
page.StartRowIndex = pageIndex * pageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Users_Query");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "UserID", DbType.String, "@UserID", QueryConditionOperatorType.Like, userID);
if (status >= 0)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayUseBeginTime", DbType.DateTime, "@PayUseBeginTime", QueryConditionOperatorType.MoreThanOrEqual, payUserBeginTime);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayUseEndTime", DbType.DateTime, "@PayUseEndTime", QueryConditionOperatorType.LessThanOrEqual, payUseEndTime);
cmd.CommandText = sqlBuilder.BuildQuerySql();
List<User> result = cmd.ExecuteEntityList<User>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return new PageList<List<User>>(result, pageIndex, pageSize, totalCount);
}
}
public static QueryResponseDTO <bool> DeleteProductInfo(ProductDTO dto)
{
QueryResponseDTO <bool> response = new QueryResponseDTO <bool>();
response.ResultEntity = false;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveProductInfo");
foreach (var model in dto.ProductModel)
{
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
{
if (null != dto)
{
if (!string.IsNullOrEmpty(model.ProductID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ProductID", DbType.AnsiString, "@ProductID", QueryConditionOperatorType.Equal, model.ProductID);
}
if (model.Stock > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Stock", DbType.Int32, "@Stock", QueryConditionOperatorType.MoreThanOrEqual, model.Stock);
}
if (!string.IsNullOrEmpty(model.CategoryID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "CategoryID", DbType.AnsiString, "@CategoryID", QueryConditionOperatorType.Equal, model.CategoryID);
}
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "IsPublish", DbType.Boolean, "@IsPublish", QueryConditionOperatorType.Equal, model.IsPublish);
if (model.OriginalPrice > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "OriginalPrice", DbType.AnsiString, "@OriginalPrice", QueryConditionOperatorType.MoreThanOrEqual, model.OriginalPrice);
}
if (dto.LastEditDate != null)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "LastEditDate", DbType.DateTime, "@LastEditDate", QueryConditionOperatorType.MoreThanOrEqual, model.LastEditDate);
}
if (model.Discount > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Discount", DbType.Int32, "@Discount", QueryConditionOperatorType.MoreThanOrEqual, model.Discount);
}
}
//QueryData
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var updateRows = dataCommand.ExecuteNonQuery();
if (updateRows > 0)
{
response.ResultEntity = true;
response.Code = ResponseStaticModel.Code.OK;
response.Message = ResponseStaticModel.Message.OK;
}
else
{
response.ResultEntity = false;
response.Code = ResponseStaticModel.Code.FAILED;
response.Message = ResponseStaticModel.Message.FAILED;
}
}
}
return(response);
}
public static QueryResult <CouponCode> QueryCouponCodeList(CouponCodeQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCouponCode");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "c.SysNo ASC" : queryFilter.SortFields))
{
//Set SQL WHERE Condition:
if (queryFilter.CouponSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponSysNo", DbType.Int32, "@CouponSysNo", QueryConditionOperatorType.Equal, queryFilter.CouponSysNo);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponCode))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponCode", DbType.String, "@CouponCode", QueryConditionOperatorType.Like, queryFilter.CouponCode);
}
if (queryFilter.InDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.InDate", DbType.DateTime, "@InDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InDateFrom.Value.Date);
}
if (queryFilter.InDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.InDate", DbType.DateTime, "@InDateTo", QueryConditionOperatorType.LessThan, queryFilter.InDateTo.Value.Date.AddDays(1));
}
if (queryFilter.BeginDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.BeginDateFrom.Value.Date);
}
if (queryFilter.BeginDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, queryFilter.BeginDateTo.Value.Date.AddDays(1));
}
if (queryFilter.EndDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.EndDateFrom.Value.Date);
}
if (queryFilter.EndDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDateTo.Value.Date.AddDays(1));
}
command.CommandText = sqlBuilder.BuildQuerySql();
List <CouponCode> resultList = command.ExecuteEntityList <CouponCode>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <CouponCode>()
{
PageInfo = new PageInfo()
{
PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
}, ResultList = resultList
});
}
}
/// <summary>
/// 查询投票列表
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryPollList(PollQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
if (filter.PageInfo == null)
{
pagingEntity = null;
}
else
{
pagingEntity.SortField = filter.PageInfo.SortBy;
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
}
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Poll_QueryPollList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "P.SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType);
if (filter.PageType.HasValue && filter.PageID.HasValue)
{
if (filter.PageType == 2 && filter.PageID.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.PageID IN ( SELECT DISTINCT C2SysNo FROM OverseaECommerceManagement.dbo.V_EM_EC_Category WHERE (C2SysNo IS NOT NULL OR C3SysNo IS NOT NULL) AND (C1SysNo =" + filter.PageID + " OR C2SysNo = " + filter.PageID + ") AND CompanyCode=" + filter.CompanyCode + " )");
}
else if (filter.PageType == 3 && filter.PageID.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.PageID IN ( SELECT DISTINCT C3SysNo FROM OverseaECommerceManagement.dbo.V_EM_EC_Category WHERE (C2SysNo IS NOT NULL OR C3SysNo IS NOT NULL) AND (C1SysNo =" + filter.PageID + " OR C2SysNo = " + filter.PageID + " OR C3SysNo = " + filter.PageID + ") AND CompanyCode=" + filter.CompanyCode + " )");
}
else if (filter.PageID > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.PageID", DbType.String, "@PageID", QueryConditionOperatorType.Equal, filter.PageID);
}
}
if (filter.UserDefined == YNStatus.Yes)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.SysNo in (SELECT distinct PollSysno FROM OverseaECommerceManagement.dbo.Poll_ItemGroup with (nolock) WHERE CompanyCode=" + filter.CompanyCode + " AND Type = 'A')");
}
else if (filter.UserDefined == YNStatus.No)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.SysNo not in (SELECT distinct PollSysno FROM OverseaECommerceManagement.dbo.Poll_ItemGroup with (nolock) WHERE CompanyCode=" + filter.CompanyCode + " AND Type = 'A')");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.PollName", DbType.String, "@PollName", QueryConditionOperatorType.Like, filter.PollName);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(ADStatus));
enumList.Add("UserDefined", typeof(YNStatus));
var dt = cmd.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public static QueryResponseDTO <bool> DeleteTransactionInfo(TransactionDTO dto)
{
QueryResponseDTO <bool> response = new QueryResponseDTO <bool>();
response.ResultEntity = false;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveTransactionInfo");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
{
if (null != dto)
{
if (!string.IsNullOrEmpty(dto.TransactionNumber))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "TransactionNumber", DbType.AnsiString, "@TransactionNumber", QueryConditionOperatorType.Equal, dto.TransactionNumber);
}
if (!string.IsNullOrEmpty(dto.FromUser))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "FromUser", DbType.AnsiString, "@FromUser", QueryConditionOperatorType.Equal, dto.FromUser);
}
if (!string.IsNullOrEmpty(dto.ToUser))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ToUser", DbType.AnsiString, "@ToUser", QueryConditionOperatorType.Equal, dto.ToUser);
}
if (dto.State > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "State", DbType.Int32, "@State", QueryConditionOperatorType.Equal, dto.State);
}
if (dto.Status > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, dto.Status);
}
if (dto.Number > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Number", DbType.Int32, "@Number", QueryConditionOperatorType.Equal, dto.Number);
}
}
//QueryData
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var updateRows = dataCommand.ExecuteNonQuery();
if (updateRows > 0)
{
response.ResultEntity = true;
response.Code = ResponseStaticModel.Code.OK;
response.Message = ResponseStaticModel.Message.OK;
}
else
{
response.ResultEntity = false;
response.Code = ResponseStaticModel.Code.FAILED;
response.Message = ResponseStaticModel.Message.FAILED;
}
}
return(response);
}
public virtual DataTable Query(OrderCheckItemQueryFilter queryCriteria, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = queryCriteria.PagingInfo.SortBy;
pagingEntity.MaximumRows = queryCriteria.PagingInfo.PageSize;
pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetOrderCheckItem");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC"))
{
if (string.Compare(queryCriteria.ReferenceType, "PC3") == 0)
{
if (queryCriteria.C1SysNo.HasValue)
{
string subQuerySQLC1 = @"SELECT [Category3Sysno]
FROM OverseaContentManagement.dbo.V_CM_CategoryInfo with(nolock)
WHERE [Category1Sysno] = " + queryCriteria.C1SysNo;
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"a.Description",
QueryConditionOperatorType.In,
subQuerySQLC1
);
}
else if (queryCriteria.C2SysNo.HasValue)
{
string subQuerySQLC1 = @"SELECT [Category3Sysno]
FROM OverseaContentManagement.dbo.V_CM_CategoryInfo with(nolock)
WHERE [Category2Sysno] = " + queryCriteria.C2SysNo;
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"a.Description",
QueryConditionOperatorType.In,
subQuerySQLC1
);
}
}
if (!string.IsNullOrEmpty(queryCriteria.ReferenceTypeIn) &&
string.Compare(queryCriteria.ReferenceTypeIn, "'DT11','DT12'") == 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.sysno",
DbType.Int32, "@sysno",
QueryConditionOperatorType.Equal,
queryCriteria.SysNo);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ReferenceType",
DbType.String, "@ReferenceType",
QueryConditionOperatorType.Equal,
queryCriteria.ReferenceType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ReferenceContent",
DbType.String, "@ReferenceContent",
QueryConditionOperatorType.Equal,
queryCriteria.ReferenceContent);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Description",
DbType.String, "@Description",
QueryConditionOperatorType.Equal,
queryCriteria.Description);
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"a.ReferenceType",
QueryConditionOperatorType.In,
queryCriteria.ReferenceTypeIn);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
queryCriteria.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = cmd.ExecuteDataTable(4, typeof(OrderCheckStatus));
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable UserQuery(VendorUserQueryFilter filter, out int dataCount)
{
if (filter.PagingInfo.SortBy != null)
{
string sortCondition = filter.PagingInfo.SortBy.Trim();
Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase);
if (match.Groups["SortColumn"].Success)
{
string sortColumn = match.Groups["SortColumn"].Value;
string sortType = match.Groups["SortType"].Success ?
match.Groups["SortType"].Value : "DESC";
#region switch
switch (sortColumn)
{
case "SysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.SysNo", sortType);
break;
case "RoleName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.RoleName", sortType);
break;
case "Status":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.Status", sortType);
break;
case "InUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.InUser", sortType);
break;
case "InDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.InDate", sortType);
break;
case "EditUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.EditUser", sortType);
break;
case "EditDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.EditDate", sortType);
break;
case "VendorSysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.VendorSysNo", sortType);
break;
case "VendorName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.VendorName", sortType);
break;
case "VendorStatus":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.Status", sortType);
break;
case "Rank":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.Rank", sortType);
break;
case "IsConsign":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.IsConsign", sortType);
break;
}
#endregion
}
}
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("External_Query_Vendor");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.SysNo);
if (!string.IsNullOrEmpty(filter.SerialNum))
{
string[] serialNum = filter.SerialNum.Split('-');
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
serialNum[0]);
if (serialNum.Length > 1)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserNum", DbType.Int32,
"@UserNum", QueryConditionOperatorType.Equal,
Convert.ToInt32(serialNum[1]).ToString());
}
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserName", DbType.String,
"@UserName", QueryConditionOperatorType.LeftLike,
filter.UserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserID", DbType.String,
"@UserID", QueryConditionOperatorType.LeftLike,
filter.UserID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal,
LegacyEnumMapper.ConvertValidStatus(filter.UserStatus));
if (filter.RoleSysNo.HasValue)
{
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
@"SELECT TOP 1 1
FROM (SELECT
UserSysNo
FROM [IPP3].[dbo].[VendorUser_User_Role] WITH(NOLOCK)
WHERE
RoleSysNo=@RoleSysNo
) AS RESULT
WHERE
RESULT.[UserSysNo]=a.[SysNo]");
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.OR, null, QueryConditionOperatorType.Exist,
@"SELECT TOP 1 1
FROM (SELECT
UserSysNo
FROM [IPP3].[dbo].[VendorUser_RoleMapping] WITH(NOLOCK)
WHERE
RoleSysNo=@RoleSysNo
) AS RESULT
WHERE
RESULT.[UserSysNo]=a.[SysNo]");
command.AddInputParameter("@RoleSysNo", DbType.Int32, filter.RoleSysNo);
builder.ConditionConstructor.EndGroupCondition();
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
filter.VendorSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.VendorName", DbType.String,
"@VendorName", QueryConditionOperatorType.Like,
filter.VendorName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.ExpiredDate", DbType.DateTime,
"@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual,
filter.ExpiredDateFrom);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.ExpiredDate", DbType.DateTime,
"@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual,
filter.ExpiredDateTo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Contact", DbType.String,
"@Contact", QueryConditionOperatorType.LeftLike,
filter.Contact);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Phone", DbType.String,
"@Phone", QueryConditionOperatorType.LeftLike,
filter.Phone);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Address", DbType.String,
"@Address", QueryConditionOperatorType.LeftLike,
filter.Address);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Status", DbType.Int32,
"@VendorStatus", QueryConditionOperatorType.Equal,
filter.VendorStatus);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Rank", DbType.String,
"@Rank", QueryConditionOperatorType.Equal,
filter.Rank);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.IsConsign", DbType.Int32,
"@IsConsign", QueryConditionOperatorType.Equal,
filter.ConsignType);
if (filter.ManufacturerSysNo.HasValue ||
(!string.IsNullOrEmpty(filter.AgentLevel) &&
filter.AgentLevel.Trim() != "") ||
filter.C1SysNo.HasValue)
{
string subCond = @"SELECT distinct [VendorSysNo]
FROM dbo.Vendor_Manufacturer WITH(NOLOCK) ";
string strWhere = string.Empty;
//代理品牌 二级类 三级类 代理级别
if (filter.ManufacturerSysNo.HasValue)
{
strWhere += "ManufacturerSysNo=@ManufacturerSysNo";
command.AddInputParameter("@ManufacturerSysNo",
DbType.Int32, filter.ManufacturerSysNo);
}
if (!string.IsNullOrEmpty(filter.AgentLevel) &&
filter.AgentLevel.Trim() != "")
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "AgentLevel=@AgentLevel";
command.AddInputParameter("@AgentLevel",
DbType.String, filter.AgentLevel);
}
if (filter.C3SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C3SysNo=@C3SysNo";
command.AddInputParameter("@C3SysNo",
DbType.Int32, filter.C3SysNo);
}
else
{
if (filter.C2SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C2SysNo=@C2SysNo";
command.AddInputParameter("@C2SysNo",
DbType.Int32, filter.C2SysNo);
}
else
{
if (filter.C1SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += @"C2SysNo in (SELECT
[Category2Sysno]
FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo] WITH(NOLOCK)
WHERE
[Category1Sysno]=@C1SysNo)";
command.AddInputParameter("@C1SysNo",
DbType.Int32, filter.C1SysNo);
}
}
}
if (!string.IsNullOrEmpty(strWhere))
{
subCond += "WHERE status=0 and " + strWhere;
}
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.SysNo",
QueryConditionOperatorType.In, subCond);
}
command.CommandText = builder.BuildQuerySql();
var dt = command.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("IsConsign", typeof(ECCentral.BizEntity.PO.VendorConsignFlag));
enumColList.Add("Status", typeof(ECCentral.BizEntity.ExternalSYS.ValidStatus));
enumColList.Add("VendorStatus", typeof(VendorStatus));
command.ConvertEnumColumn(dt, enumColList);
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QueryAllocatedItemOrdersRelated(QueryFilter.Inventory.InventoryAllocatedCardQueryFilter 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("QueryItemAllocatedCardReLatedOrder");
dataCommand.SetParameterValue("@WarehouseSysNumber", queryFilter.StockSysNo.Value);
}
else
{
dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryItemAllocatedCardReLatedOrderWithOutWarehouseNumber");
}
dataCommand.SetParameterValue("@ItemSysNumber", queryFilter.ProductSysNo.Value);
dataCommand.SetParameterValue("@CompanyCode", queryFilter.CompanyCode);
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--)
{
//OrderCode means po-调价(instock)(PO调价单)
if (dt.Rows[i]["OrderCode"] != null && dt.Rows[i]["OrderCode"].ToString() != "3")
{
orderThenQty += (dt.Rows[i]["OrderQty"] != null ? int.Parse(dt.Rows[i]["OrderQty"].ToString()) : 0);
}
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);
}
private static void BuildSearchPOCondition(ProductPurchaseQueryFilter queryFilter, CustomDataCommand dataCommand, PagingInfoEntity pagingInfo)
{
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "po.sysno desc"))
{
#region build search condition
string replaceSQL1 = "";
string replaceSQL2 = "where 1=1";
string replaceSQL3 = "where 1=1";
if (queryFilter.CreateTimeBegin.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime",
DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.Value);
}
if (queryFilter.CreateTimeTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime",
DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.AuditUser))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%') ");
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status);
}
if (!string.IsNullOrEmpty(queryFilter.StatusList))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 999);
}
if (queryFilter.IsConsign.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsConsign",
DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, (int)queryFilter.IsConsign);
}
if (!string.IsNullOrEmpty(queryFilter.VendorSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.VendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
}
#region 权限筛选
//#region 添加供应商（创建PO单的对象）
//int vendorCreateUserSysNo = int.Parse(AppSettingManager.GetSetting("PO", "VendorCreateUserSysNo"));
//queryFilter.PMAuthorizedList.Add(vendorCreateUserSysNo);
//#endregion 添加供应商（创建PO单的对象）
//by Jack.W.Wang 2012-11-8 CRL21776--------------------------BEGIN
// if (!(queryFilter.IsManagerPM ?? false))
// {
// string sqlStr = @"Select
// ProductLineSysNo
// FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " +
// "WHERE PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0";
// sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po.ProductLineSysNo", QueryConditionOperatorType.In, sqlStr);
// }
var createUserSysNo = queryFilter.PMSysNo;
if (!string.IsNullOrEmpty(createUserSysNo))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"po.PMSysNo in(" + createUserSysNo + ")");
}
#endregion
if (string.IsNullOrEmpty(queryFilter.POSysNoExtention))
{
if (queryFilter.POSysNo != "" && queryFilter.POSysNo != null && Regex.IsMatch(queryFilter.POSysNo, "^[0-9]+$"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.poid",
DbType.String, "@poid", QueryConditionOperatorType.Like, queryFilter.POSysNo.Trim());
}
}
else
{
string[] poSysNoStr = queryFilter.POSysNoExtention.Split('.');
List <string> poSysNoList = new List <string>();
foreach (string s in poSysNoStr)
{
poSysNoList.Add(s);
}
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "po.poid",
DbType.String, poSysNoList);
}
if (!string.IsNullOrEmpty(queryFilter.IsApportion))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsApportion",
DbType.Int32, "@IsApportion", QueryConditionOperatorType.Equal, queryFilter.IsApportion);
}
if (!string.IsNullOrEmpty(queryFilter.CreatePOSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateUserSysNo",
DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreatePOSysNo);
}
if (queryFilter.PaySettleCompany.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PaySettleCompany",
DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, (int)queryFilter.PaySettleCompany);
}
if (!string.IsNullOrEmpty(queryFilter.CurrencySysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CurrencySysNo",
DbType.Int32, "@CurrencySysNo", QueryConditionOperatorType.Equal, queryFilter.CurrencySysNo);
}
if (!string.IsNullOrEmpty(queryFilter.IsStockStatus))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PartlyReceiveStatus",
DbType.Int32, "@PartlyReceiveStatus", QueryConditionOperatorType.Equal, queryFilter.IsStockStatus);
}
if (queryFilter.InStockFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime",
DbType.DateTime, "@InTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InStockFrom.Value);
}
if (queryFilter.InStockTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime",
DbType.DateTime, "@InStockTo", QueryConditionOperatorType.LessThan, queryFilter.InStockTo.Value.AddDays(1));
}
if (queryFilter.POType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.POType",
DbType.Int32, "@POType", QueryConditionOperatorType.Equal, (int)queryFilter.POType);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
if (queryFilter.LeaseFlag.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.LeaseFlag",
DbType.Int32, "@LeaseFlag", QueryConditionOperatorType.Equal, queryFilter.LeaseFlag.Value == true ? 1 : 0);
}
if (!string.IsNullOrEmpty(queryFilter.VerifyStatus))
{
if (queryFilter.VerifyStatus == "1")
{
replaceSQL1 += " and po.TPStatus ='1' ";
replaceSQL3 += " and po.TPStatus ='1' ";
}
if (queryFilter.VerifyStatus == "2")
{
replaceSQL1 += " and po.TPStatus = '2' ";
replaceSQL3 += " and po.TPStatus = '2' ";
}
}
if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) &&
Regex.IsMatch(queryFilter.ProductSysNo, "^[0-9]+$"))
{
replaceSQL1 += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo);
replaceSQL3 += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo);
}
if (!string.IsNullOrEmpty(queryFilter.StockSysNo))
{
//添加在途商品查询
if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value)
{
//获取仓库数据
string[] stockArray = queryFilter.QueryStock.Split(',');
List <int> stockList = new List <int>(stockArray.Length);
for (int i = 0; i < stockArray.Length; i++)
{
stockList.Add(int.Parse(stockArray[i]));
}
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.ITStockSysNo",
DbType.Int32, stockList);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.OR, "po.StockSysNo",
DbType.Int32, stockList);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
else
{
if (queryFilter.StockSysNo == "50")
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ITStockSysNo",
DbType.Int32, "@TransferMiddleStock", QueryConditionOperatorType.Equal, queryFilter.TranferStock);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
}
}
}
if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value && !string.IsNullOrEmpty(queryFilter.QueryStatus))
{
string[] statusArray = queryFilter.QueryStatus.Split(',');
List <int> statusList = new List <int>(statusArray.Length);
for (int i = 0; i < statusArray.Length; i++)
{
statusList.Add(int.Parse(statusArray[i]));
}
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.Status",
DbType.Int32, statusList);
}
#endregion
if (queryFilter.PrintTime.HasValue)
{
replaceSQL2 += string.Format(" and exists (select top 1 1 from scm.dbo.poLog with(nolock) GROUP BY Purno having scm.dbo.poLog.purno= po.sysno and convert(varchar(10),MAX(recdate),120) = convert(varchar(10),'{0}',120))", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd"));
replaceSQL3 += string.Format(" and convert(varchar(10),a.MaxTastDate,120) = convert(varchar(10),'{0}',120)", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrEmpty(queryFilter.AuditUser))
{
replaceSQL2 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'";
replaceSQL3 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'";
}
if (queryFilter.BrandSysNo.HasValue)
{
replaceSQL1 += string.Format(@" AND EXISTS(SELECT TOP 1 1
FROM IPP3.dbo.PO_Item POI
INNER JOIN IPP3.dbo.Product PRO
ON POI.ProductSysNo = PRO.SysNo
INNER JOIN [OverseaContentManagement].dbo.Brand BRA
ON BRA.SysNo = PRO.BrandSysNo
WHERE POI.POSysNo = PO.SysNO
AND BRA.SysNo = {0}) ", queryFilter.BrandSysNo.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("replaceSQL1", replaceSQL1).Replace("replaceSQL2", replaceSQL2).Replace("replaceSQL3", replaceSQL3);
if (pagingInfo != null)
{
dataCommand.SetParameterValue("@StartNumber", queryFilter.PageSize * queryFilter.PageIndex);
dataCommand.SetParameterValue("@EndNumber", queryFilter.PageSize * queryFilter.PageIndex + queryFilter.PageSize);
}
}
}
/// <summary>
/// 查询仓库
/// </summary>
/// <returns></returns>
public virtual DataTable QueryWarehouse(WarehouseQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = SortFieldMapping(filter.PageInfo.SortBy);
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryWarehouseListByCondition");//说明：EC_Central产品化时 调用的 SQL为：Inventory_QueryWarehouse 现在是中蛋定制化 不存在渠道仓库所以保持同IPP一致
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo ASC"))
{
bool isSysNo = false;
//编号格式验证
if (filter.SysNo != null && Regex.IsMatch(filter.SysNo, @"^[,\. ]*\d+[\d,\. ]*$"))
{
filter.SysNo = String.Join(",", filter.SysNo.Split(new char[] { '.', ',', ' ' }, StringSplitOptions.RemoveEmptyEntries));
isSysNo = true;
}
else
{
filter.SysNo = null;
}
if (filter.WarehouseID != null && Regex.IsMatch(filter.WarehouseID, @"^[, ]*\w+[\w-#, ]*$"))
{
filter.WarehouseID = "'" + String.Join("','", filter.WarehouseID.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)) + "'";
isSysNo = true;
}
else
{
filter.WarehouseID = null;
}
if (isSysNo)
{
sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"a.SysNo", QueryConditionOperatorType.In, filter.SysNo);
sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"a.StockID", QueryConditionOperatorType.In, filter.WarehouseID);
sb.ConditionConstructor.EndGroupCondition();
}
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.StockName", DbType.String, "@WarehouseName",
QueryConditionOperatorType.LeftLike, filter.WarehouseName);
//sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "a.WarehouseOwnerSysNo", DbType.Int32, "@WarehouseOwnerSysNo",
// QueryConditionOperatorType.Equal, filter.OwnerSysNo);
//if (!filter.OwnerSysNo.HasValue && filter.OwnerSysNo!=0)
//{
// sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND
// , " ((a.WarehouseOwnerSysNo IN ( SELECT a.SysNo FROM OverseaInventoryManagement.dbo.WarehouseOwner a WITH(NOLOCK)WHERE a.OwnerType=1)) OR a.WarehouseOwnerSysNo IS NULL)");
//}
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.[Status]", DbType.Int32, "@WarehouseStatus",
QueryConditionOperatorType.Equal, filter.WarehouseStatus);
//sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "a.[WarehouseType]", DbType.Int32, "@WarehouseType",
// QueryConditionOperatorType.Equal, filter.WarehouseType);
//sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "a.[WarehouseArea]", DbType.Int32, "@WarehouseArea",
// QueryConditionOperatorType.Equal, filter.WarehouseArea);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal, filter.CompanyCode);
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add("WarehouseStatus", typeof(ValidStatus));
cmd.CommandText = sb.BuildQuerySql();
var resultData = cmd.ExecuteDataTable(enumColumnList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(resultData);
}
}
public static QueryResponseDTO <List <ReviewModel> > GetReviewInfo(ReviewDTO dto)
{
QueryResponseDTO <List <ReviewModel> > responseDTO = new QueryResponseDTO <List <ReviewModel> >();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetReviewInfo");
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.ReviewID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ReviewID", DbType.AnsiString, "@ReviewID", QueryConditionOperatorType.Equal, dto.ReviewID);
}
if (!string.IsNullOrEmpty(dto.ProductOROrderID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ProductOROrderID", DbType.AnsiString, "@ProductOROrderID", QueryConditionOperatorType.Equal, dto.ProductOROrderID);
}
if (null != dto.ReviewDate)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ReviewDate", DbType.DateTime, "@ReviewDate", QueryConditionOperatorType.Equal, dto.ReviewDate);
}
if (dto.Score > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Score", DbType.Int32, "@Score", QueryConditionOperatorType.Equal, dto.Score);
}
if (dto.FavourCount > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "FavourCount", DbType.Int32, "@FavourCount", QueryConditionOperatorType.Equal, dto.FavourCount);
}
if (dto.OpposeCount > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "OpposeCount", DbType.Int32, "@OpposeCount", QueryConditionOperatorType.Equal, dto.OpposeCount);
}
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "CheckPassed", DbType.Boolean, "@CheckPassed", QueryConditionOperatorType.Equal, dto.CheckPassed);
}
//QueryData
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var result = dataCommand.ExecuteEntityList <ReviewModel>();
responseDTO.ResultEntity = result;
}
responseDTO.TotalCount = dto.PagingInfo.TotalCount;
responseDTO.Code = ResponseStaticModel.Code.OK;
responseDTO.Message = ResponseStaticModel.Message.OK;
return(responseDTO);
}
public static QueryResponseDTO <bool> DeleteReviewInfo(ReviewDTO dto)
{
QueryResponseDTO <bool> response = new QueryResponseDTO <bool>();
response.ResultEntity = false;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveReviewInfo");
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.ReviewID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ReviewID", DbType.AnsiString, "@ReviewID", QueryConditionOperatorType.Equal, dto.ReviewID);
}
if (!string.IsNullOrEmpty(dto.ProductOROrderID))
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ProductOROrderID", DbType.AnsiString, "@ProductOROrderID", QueryConditionOperatorType.Equal, dto.ProductOROrderID);
}
if (null != dto.ReviewDate)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "ReviewDate", DbType.DateTime, "@ReviewDate", QueryConditionOperatorType.Equal, dto.ReviewDate);
}
if (dto.Score > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "Score", DbType.Int32, "@Score", QueryConditionOperatorType.Equal, dto.Score);
}
if (dto.FavourCount > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "FavourCount", DbType.Int32, "@FavourCount", QueryConditionOperatorType.Equal, dto.FavourCount);
}
if (dto.OpposeCount > 0)
{
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "OpposeCount", DbType.Int32, "@OpposeCount", QueryConditionOperatorType.Equal, dto.OpposeCount);
}
sqlBuilder.ConditionConstructor
.AddCondition(QueryConditionRelationType.AND, "CheckPassed", DbType.Boolean, "@CheckPassed", QueryConditionOperatorType.Equal, dto.CheckPassed);
}
//QueryData
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var updateRows = dataCommand.ExecuteNonQuery();
if (updateRows > 0)
{
response.ResultEntity = true;
response.Code = ResponseStaticModel.Code.OK;
response.Message = ResponseStaticModel.Message.OK;
}
else
{
response.ResultEntity = false;
response.Code = ResponseStaticModel.Code.FAILED;
response.Message = ResponseStaticModel.Message.FAILED;
}
}
return(response);
}
private void AddInvoiceInfoListParameters(EIMSInvoiceEntryQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
List <Object> receiveTypeList = new List <Object>();
receiveTypeList.Add(1);
receiveTypeList.Add(2);
List <Object> status = new List <Object>();
status.Add("O");
status.Add("F");
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.ReceiveType",
DbType.Int32,
"@MyReceiveType",
QueryConditionOperatorType.In,
receiveTypeList);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.[STATUS]",
DbType.String,
"@MySTATUS",
QueryConditionOperatorType.In,
status);
if (!string.IsNullOrEmpty(filter.AssignedCode))
{
filter.AssignedCode = filter.AssignedCode.Replace(" ", string.Empty);
string[] spiltResults = filter.AssignedCode.Split('.');
string invoiceNumbers = string.Empty;
string assignedCodes = string.Empty;
int invoiceNumber = 0;
if (spiltResults.Length > 1)
{
for (int i = 0; i < spiltResults.Length; i++)
{
if (int.TryParse(spiltResults[i], out invoiceNumber))
{
if (invoiceNumbers.Length > 0)
{
invoiceNumbers += ",";
}
invoiceNumbers += spiltResults[i];
}
else
{
if (assignedCodes.Length > 0)
{
assignedCodes += ",";
}
assignedCodes += "'" + spiltResults[i] + "'";
}
}
if (invoiceNumbers.Length > 0 && assignedCodes.Length == 0)
{
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"ei.InvoiceNumber",
QueryConditionOperatorType.In,
invoiceNumbers);
}
if (assignedCodes.Length > 0 && invoiceNumbers.Length == 0)
{
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"ei.AssignedCode",
QueryConditionOperatorType.In,
assignedCodes);
}
if (invoiceNumbers.Length > 0 && assignedCodes.Length > 0)
{
sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"ei.InvoiceNumber",
QueryConditionOperatorType.In,
invoiceNumbers);
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.OR,
"ei.AssignedCode",
QueryConditionOperatorType.In,
assignedCodes);
sb.ConditionConstructor.EndGroupCondition();
}
}
else
{
if (int.TryParse(filter.AssignedCode, out invoiceNumber))
{
sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.AssignedCode",
DbType.String,
"@AssignedCode",
QueryConditionOperatorType.Equal,
filter.AssignedCode);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"ei.InvoiceNumber",
DbType.String,
"@InvoiceNumber",
QueryConditionOperatorType.Equal,
invoiceNumber);
sb.ConditionConstructor.EndGroupCondition();
}
else
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.AssignedCode",
DbType.String,
"@AssignedCode",
QueryConditionOperatorType.Equal,
filter.AssignedCode);
}
}
}
if (filter.EIMSType.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (!string.IsNullOrEmpty(filter.InvoiceInputStatus))
{
string subSql = "SELECT COUNT(1) " +
"FROM eims.dbo.EIMSInvoiceInput iei WITH(NOLOCK) " +
"INNER JOIN eims.dbo.EIMSInvoiceInput_Ex iex WITH(NOLOCK) " +
"ON iei.SysNo = iex.InvoiceInputSysNO AND iei.[status]=0 AND iex.[status]=0 " +
"WHERE iex.InvoiceNumber = ei.InvoiceNumber ";
if (filter.InvoiceInputStatus == "已录入")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"(" + subSql + ")",
DbType.Int32,
"@InvoiceInputStatusSql",
QueryConditionOperatorType.MoreThan,
0);
}
else if (filter.InvoiceInputStatus == "未录入")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"(" + subSql + ")",
DbType.Int32,
"@InvoiceInputStatusSql",
QueryConditionOperatorType.Equal,
0);
}
}
if (filter.ReceiveType.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.ReceiveType",
DbType.String,
"@ReceiveType",
QueryConditionOperatorType.Equal,
filter.ReceiveType);
}
if (!string.IsNullOrEmpty(filter.RuleAssignedCode))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"er.AssignedCode",
DbType.String,
"@RuleAssignedCode",
QueryConditionOperatorType.Equal,
filter.RuleAssignedCode);
}
if (!string.IsNullOrEmpty(filter.Status))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.[Status]",
DbType.String,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status);
}
if (filter.VendorNumber.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.VendorNumber",
DbType.String,
"@VendorNumber",
QueryConditionOperatorType.Equal,
filter.VendorNumber);
}
if (!string.IsNullOrEmpty(filter.IsSAPImported))
{
if (filter.IsSAPImported == "Y")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"fp.IsSAPImported",
DbType.String,
"@IsSAPImported",
QueryConditionOperatorType.Equal,
filter.IsSAPImported);
}
else if (filter.IsSAPImported == "N")
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"fp.IsSAPImported",
DbType.String,
"@IsSAPImported",
QueryConditionOperatorType.Equal,
filter.IsSAPImported);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"fp.IsSAPImported",
DbType.String,
"@InvoiceNumber",
QueryConditionOperatorType.IsNull,
null);
}
}
if (!string.IsNullOrEmpty(filter.InvoiceNumber))
{
string[] arr = filter.InvoiceNumber.Split('.');
List <Object> invoiceNumberList = new List <Object>();
string invoiceNumber = string.Empty;
for (int i = 0; i < arr.Length; i++)
{
invoiceNumberList.Add(arr[i]);
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ei.InvoiceNumber",
DbType.String,
"@InvoiceNumber",
QueryConditionOperatorType.In,
invoiceNumberList);
}
if (!string.IsNullOrEmpty(filter.InvoiceInputNo))
{
string subSql = "EXISTS(SELECT 1 " +
"FROM EIMS.dbo.EIMSInvoiceInput_Ex eiiex WITH(NOLOCK) " +
"INNER JOIN eims.dbo.EIMSInvoiceInput eii WITH(NOLOCK) " +
" ON eiiex.InvoiceInputSysNO = eii.SysNO "+
"WHERE eii.Status=0 " +
" AND eiiex.Status=0 " +
" AND eii.InvoiceInputNo="+ filter.InvoiceInputNo + " " +
" AND eiiex.InvoiceNumber=ei.InvoiceNumber)";
cmd.CommandText += subSql;
sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, subSql);
}
cmd.CommandText = sb.BuildQuerySql();
}
/// <summary>
/// 扣款项维护列表
/// </summary>
/// <param name="queryFilter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable LoadDeductInfo(DeductQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = -1;
return(null);
}
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryDeductList");
if (null != queryFilter.PageInfo.SortBy)
{
if (queryFilter.PageInfo.SortBy.IndexOf("[Status]") > -1)
{
queryFilter.PageInfo.SortBy = "D." + queryFilter.PageInfo.SortBy;
}
}
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Indate DESC"))
{
if (!string.IsNullOrEmpty(queryFilter.Name))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Name",
DbType.String, "@Name", QueryConditionOperatorType.Like, queryFilter.Name);
}
if (queryFilter.DeductType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "DeductType",
DbType.Int32, "@DeductType", QueryConditionOperatorType.Equal, queryFilter.DeductType.Value);
}
if (queryFilter.AccountType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AccountType",
DbType.Int32, "@AccountType", QueryConditionOperatorType.Equal, queryFilter.AccountType.Value);
}
if (queryFilter.DeductMethod.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "DeductMethod",
DbType.Int32, "@DeductMethod", QueryConditionOperatorType.Equal, queryFilter.DeductMethod.Value);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status.Value);
}
command.CommandText = sqlBuilder.BuildQuerySql();
command.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
command.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
EnumColumnList columnEnums = new EnumColumnList();
columnEnums.Add("DeductType", typeof(DeductType));
columnEnums.Add("AccountType", typeof(AccountType));
columnEnums.Add("DeductMethod", typeof(DeductMethod));
columnEnums.Add("Status", typeof(Status));
DataTable dt = command.ExecuteDataTable(columnEnums);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public static DataTable SettleQuery(SettleQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("Invocie_SearchCommissionSettle");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SysNo", DbType.String,
"@SysNo", QueryConditionOperatorType.LeftLike,
filter.SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"MerchantSysNo", DbType.String,
"@MerchantSysNo", QueryConditionOperatorType.LeftLike,
filter.MerchantSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Type", DbType.AnsiStringFixedLength,
"@Type", QueryConditionOperatorType.Equal,
filter.Type);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SettledDateTime", DbType.AnsiStringFixedLength,
"@SettledBeginDateTime", QueryConditionOperatorType.MoreThanOrEqual,
filter.SettledBeginDateTime);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SettledDateTime", DbType.AnsiStringFixedLength,
"@SettledEndDateTime", QueryConditionOperatorType.LessThanOrEqual,
filter.SettledEndDateTime);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"IssuingDateTime", DbType.AnsiStringFixedLength,
"@IssuingBeginDateTime", QueryConditionOperatorType.MoreThanOrEqual,
filter.IssuingBeginDateTime);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"IssuingDateTime", DbType.AnsiStringFixedLength,
"@IssuingEndDateTime", QueryConditionOperatorType.LessThanOrEqual,
filter.IssuingEndDateTime);
command.CommandText = builder.BuildQuerySql();
string typeStr = string.Empty;
if (filter.Type != null)
{
typeStr = string.Format("Where Type={0}", ((int)filter.Type.Value).ToString());
}
command.CommandText = command.CommandText.Replace("#StrWhere2#", typeStr);
var dt = command.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(SettleOrderStatus));
enumColList.Add("Type", typeof(SettleOrderType));
command.ConvertEnumColumn(dt, enumColList);
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public static QueryResult AOQuery(AOQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("AOQuery");
using (
var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter,
string.IsNullOrEmpty(queryFilter.SortFields) ? "bank.SysNo ASC" : queryFilter.SortFields))
{
if (queryFilter.SOSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
string.Format("(so.SysNo = {0} or sc.SoSplitMaster = {0})", queryFilter.SOSysNo));
}
if (queryFilter.OrderDateBegin.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate",
DbType.DateTime, "@OrderDateBegin",
QueryConditionOperatorType.MoreThanOrEqual, queryFilter.OrderDateBegin);
}
if (queryFilter.OrderDateEnd.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate",
DbType.DateTime, "@OrderDateEnd",
QueryConditionOperatorType.LessThan, queryFilter.OrderDateEnd.Value.Date.AddDays(1));
}
if (!string.IsNullOrWhiteSpace(queryFilter.CustomerID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cust.CustomerID",
DbType.String, "@CustomerID",
QueryConditionOperatorType.Equal, queryFilter.CustomerID);
}
if (queryFilter.PayTypeSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.PayTypeSysNo",
DbType.Int32, "@PayTypeSysNo",
QueryConditionOperatorType.Equal, queryFilter.PayTypeSysNo);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "bank.Status",
DbType.Int32, "@SOStatus",
QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
}
if (queryFilter.RefundPayType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RefundPayType",
DbType.Int32, "@RefundPayType",
QueryConditionOperatorType.Equal, (int)queryFilter.RefundPayType.Value);
}
if (queryFilter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sc.MerchantSysNo",
DbType.Int32, "@MerchantSysNo",
QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo);
}
if (string.IsNullOrWhiteSpace(queryFilter.SortFields))
{
queryFilter.SortFields = "bank.SysNo ASC";
}
command.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("Status", typeof(RefundStatus));
enumColumns.Add("RefundPayType", typeof(RefundPayType));
DataTable result = command.ExecuteDataTable(enumColumns);
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult(result, queryFilter, totalCount));
}
}
/// <summary>
/// 查询商品价格变动申请单据
/// </summary>
/// <param name="queryFilter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductPriceRequesList(ProductPriceRequestQueryFilter queryFilter, out int totalCount)
{
CustomDataCommand dataCommand =
DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductPriceRequesList");
var pagingInfo = new PagingInfoEntity
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
if (ComparisonMapping.Count == 0)
{
SetComparisonMapping();
}
using (
var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo,
"P.ProductSysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Status",
DbType.String, "@Status",
QueryConditionOperatorType.In,
new List <object> {
ProductPriceRequestStatus.Origin, ProductPriceRequestStatus.NeedSeniorApprove
});
if (!String.IsNullOrEmpty(queryFilter.ProductID))
{
dataCommand.AddInputParameter("@ProductID", DbType.String, queryFilter.ProductID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PP.ProductID",
DbType.String, "@ProductID",
QueryConditionOperatorType.Equal,
queryFilter.ProductID);
}
if (queryFilter.ProductStatus != null)
{
dataCommand.AddInputParameter("@ProductStatus", DbType.Int32, queryFilter.ProductStatus);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PP.Status",
DbType.Int32, "@ProductStatus",
QueryConditionOperatorType.Equal,
queryFilter.ProductStatus);
}
if (queryFilter.ManufacturerSysNo != null && queryFilter.ManufacturerSysNo > 0)
{
dataCommand.AddInputParameter("@ManufacturerSysNo", DbType.Int32, queryFilter.ManufacturerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PP.ManufacturerSysNo",
DbType.Int32, "@ManufacturerSysNo",
QueryConditionOperatorType.Equal,
queryFilter.ManufacturerSysNo);
}
if (queryFilter.Category3 != null && queryFilter.Category3.Value > 0)
{
dataCommand.AddInputParameter("@C3SysNo", DbType.Int32, queryFilter.Category3.Value);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PP.C3SysNo",
DbType.Int32, "@C3SysNo",
QueryConditionOperatorType.Equal,
queryFilter.Category3.Value);
}
if (queryFilter.Category2 != null && queryFilter.Category2.Value > 0)
{
dataCommand.AddInputParameter("@C2SysNo", DbType.Int32, queryFilter.Category2.Value);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"A.C2SysNo",
DbType.Int32, "@C2SysNo",
QueryConditionOperatorType.Equal,
queryFilter.Category2.Value);
}
if (queryFilter.Category1 != null && queryFilter.Category1.Value > 0)
{
dataCommand.AddInputParameter("@C1SysNo", DbType.Int32, queryFilter.Category1.Value);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.C1SysNo",
DbType.Int32, "@C1SysNo",
QueryConditionOperatorType.Equal,
queryFilter.Category1.Value);
}
dataCommand.AddInputParameter("@PMRole", DbType.Int32, queryFilter.PMRole);
if (queryFilter.PMRole == 1)//初级权限
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " PP.PMUserSysNo IN (SELECT PMSysNo FROM #temp1 t1) ");
}
else if (queryFilter.PMRole == 3)//高级权限
{
}
else//无权限
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " 1<>1 ");
}
if (queryFilter.AuditType != null)
{
switch (queryFilter.AuditType)
{
case QueryProductPriceRequestAuditType.Audit:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Type",
DbType.String, "@AuditType",
ComparisonMapping[queryFilter.ComparisonOperators],
ProductPriceRequestAuditType.Audit);
break;
case QueryProductPriceRequestAuditType.SeniorAudit:
if (queryFilter.ComparisonOperators == Comparison.Equal)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Type",
DbType.String, "@AuditType",
ComparisonMapping[queryFilter.ComparisonOperators],
ProductPriceRequestAuditType.SeniorAudit);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Status",
DbType.String, "@OtherStatus",
QueryConditionOperatorType.Equal,
ProductPriceRequestStatus.NeedSeniorApprove
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Status",
DbType.String, "@OtherStatus",
QueryConditionOperatorType.Equal,
ProductPriceRequestStatus.Origin
);
}
break;
case QueryProductPriceRequestAuditType.Submit:
if (queryFilter.ComparisonOperators == Comparison.Equal)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Type",
DbType.String, "@AuditType",
ComparisonMapping[queryFilter.ComparisonOperators],
ProductPriceRequestAuditType.SeniorAudit);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.Status",
DbType.String, "@OtherStatus",
QueryConditionOperatorType.Equal,
ProductPriceRequestStatus.Origin
);
}
else
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR,
"P.Type",
DbType.String, "@AuditType",
ComparisonMapping[queryFilter.ComparisonOperators],
ProductPriceRequestAuditType.SeniorAudit);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR,
"P.Status",
DbType.String, "@OtherStatus",
ComparisonMapping[queryFilter.ComparisonOperators],
ProductPriceRequestStatus.Origin
);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
break;
}
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList
{
{ "AuditType", typeof(ProductPriceRequestAuditType) },
{ "Status", typeof(ProductPriceRequestStatus) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QueryRMARequest(RMARequestQueryFilter filter, out int totalCount)
{
if (filter == null)
{
totalCount = -1;
return(null);
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRequests");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "request.SysNo DESC"))
{
#region build query conditions
if (filter.CreateDateFrom != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CreateTime", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CreateDateFrom);
}
if (filter.CreateDateTo != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CreateTime", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThan,
filter.CreateDateTo);
}
if (filter.ReceivedDateFrom != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvTime", DbType.DateTime, "@RecvTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.ReceivedDateFrom);
}
if (filter.ReceivedDateTo != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvTime", DbType.DateTime, "@RecvTimeTo",
QueryConditionOperatorType.LessThan,
filter.ReceivedDateTo);
}
if (filter.ETakeDateFrom != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.ETakeDate", DbType.DateTime, "@ETakeDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.ETakeDateFrom);
}
if (filter.ETakeDateTo != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.ETakeDate", DbType.DateTime, "@ETakeDateTo",
QueryConditionOperatorType.LessThan,
filter.ETakeDateTo);
}
if (!string.IsNullOrEmpty(filter.SOID))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"so.SOID", DbType.String, "@SOID",
QueryConditionOperatorType.Equal,
filter.SOID);
}
if (!string.IsNullOrEmpty(filter.RequestID))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RequestID", DbType.String, "@RequestID",
QueryConditionOperatorType.Equal,
filter.RequestID);
}
if (filter.IsVIP.HasValue)
{
using (GroupCondition group = new GroupCondition(builder, QueryConditionRelationType.AND))
{
if (filter.IsVIP.Value)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.VIPRank",
DbType.Int32,
"@AutoVIP",
QueryConditionOperatorType.Equal,
2 // VIPRank.AutoVIP
);
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"customer.VIPRank",
DbType.Int32,
"@ManualVIP",
QueryConditionOperatorType.Equal,
4 // VIPRank.ManualVIP
);
}
else
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.VIPRank",
DbType.Int32,
"@AutoNonVIP",
QueryConditionOperatorType.Equal,
1 // VIPRank.AutoNonVIP
);
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"customer.VIPRank",
DbType.Int32,
"@ManualNonVIP",
QueryConditionOperatorType.Equal,
3 // VIPRank.ManualNonVIP
);
}
}
}
if (filter.CustomerSysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.SysNo", DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal,
filter.CustomerSysNo);
}
if (!string.IsNullOrEmpty(filter.CustomerID))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.CustomerID", DbType.String, "@CustomerID",
QueryConditionOperatorType.Equal,
filter.CustomerID);
}
if (filter.ReceiveUserSysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvUserSysNo", DbType.Int32, "@RecvUserSysNo",
QueryConditionOperatorType.Equal,
filter.ReceiveUserSysNo);
}
if (filter.IsSubmit.HasValue)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.IsSubmit",
DbType.Int32,
"@IsSubmit",
QueryConditionOperatorType.Equal,
filter.IsSubmit);
}
if (filter.Status.HasValue)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status
);
}
if (filter.SysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.SysNo",
DbType.Int32,
"@SysNo",
QueryConditionOperatorType.Equal,
filter.SysNo
);
}
if (filter.SOSysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.SOSysNo",
DbType.Int32,
"@SOSysNo",
QueryConditionOperatorType.Equal,
filter.SOSysNo
);
}
if (!string.IsNullOrEmpty(filter.CompanyCode))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode
);
}
if (filter.IsLabelPrinted != null)
{
string strIsLabelPrinted = "N";
if (filter.IsLabelPrinted.Value)
{
strIsLabelPrinted = "Y";
}
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.IsLabelPrinted",
DbType.AnsiStringFixedLength,
"@IsLabelPrinted",
QueryConditionOperatorType.Equal,
strIsLabelPrinted
);
}
if (filter.SellersType != null)
{
if (filter.SellersType == SellersType.Self)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.InvoiceType",
DbType.AnsiStringFixedLength,
"@InvoiceType",
QueryConditionOperatorType.Equal,
0
);
}
else
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.InvoiceType",
DbType.AnsiStringFixedLength,
"@InvoiceType",
QueryConditionOperatorType.Equal,
1
);
}
}
//服务编码 add by norton 2012.11.21
if (!string.IsNullOrEmpty(filter.ServiceCode))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.ServiceCode",
DbType.String,
"@ServiceCode",
QueryConditionOperatorType.Equal,
filter.ServiceCode);
}
if (filter.AuditUserSysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"request.AuditUserSysNo",
DbType.Int32,
"@AuditUserSysNo",
QueryConditionOperatorType.Equal,
filter.AuditUserSysNo);
}
if (filter.AuditDateFrom != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.AuditTime", DbType.DateTime, "@AuditDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.AuditDateFrom);
}
if (filter.AuditDateTo != null)
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.AuditTime", DbType.DateTime, "@AuditDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.AuditDateTo);
}
#endregion
dataCommand.CommandText = builder.BuildQuerySql();
#region 扩展字段读取
string columnExtendColumnRMAItemSysNos = string.Empty;
//需要读取额外的单件连接数据
if (filter.IsReadRMAItemSysNos)
{
columnExtendColumnRMAItemSysNos = string.Format(",{0}", "(SELECT [OverseaOrderManagement].[dbo].[UF_GetRMAItemSysNos] (request.SysNo)) as RMAItemSysNos");
}
dataCommand.CommandText = dataCommand.CommandText.Replace("#ExtendColRMAItemSysNos#", columnExtendColumnRMAItemSysNos);
#endregion
dataCommand.SetParameterValue("@StartNumber", filter.PagingInfo.PageSize * filter.PagingInfo.PageIndex);
dataCommand.SetParameterValue("@EndNumber", filter.PagingInfo.PageSize * filter.PagingInfo.PageIndex + filter.PagingInfo.PageSize);
DataTable dt = dataCommand.ExecuteDataTable("Status", typeof(RMARequestStatus));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataSet Query(BalanceAccountQueryFilter filter, out int totalCount)
{
DataSet result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (filter.PagingInfo != null)
{
MapSortField(filter);
pagingInfo.MaximumRows = filter.PagingInfo.PageSize;
pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
pagingInfo.SortField = filter.PagingInfo.SortBy;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryBalanceAccount");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "CP.SysNo desc"))
{
StringBuilder t1 = new StringBuilder();
StringBuilder t2 = new StringBuilder();
StringBuilder t3 = new StringBuilder();
t1.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode ");
t2.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode ");
t3.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode ");
if (filter.CustomerSysNo.HasValue)
{
t1.Append(" AND PL.CustomerSysNo = @CustomerSysNo ");
t2.Append(" AND PL.CustomerSysNo = @CustomerSysNo ");
t3.Append(" AND PL.CustomerSysNo = @CustomerSysNo ");
}
if (!string.IsNullOrEmpty(filter.CustomerID))
{
t1.Append(" AND C.CustomerID = @CustomerID ");
t2.Append(" AND C.CustomerID = @CustomerID ");
t3.Append(" AND C.CustomerID = @CustomerID ");
}
if (filter.CreateTimeFrom.HasValue)
{
t1.Append(" AND PL.CreateTime < @CreateDateFrom ");
t3.Append(" AND PL.CreateTime > @CreateDateFrom ");
}
if (filter.CreateTimeTo.HasValue)
{
t2.Append(" AND PL.CreateTime <= @CreateDateTo ");
t3.Append(" AND PL.CreateTime <= @CreateDateTo ");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CustomerSysNo",
DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerID",
DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, filter.CustomerID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CreateTime",
DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateTimeFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CreateTime",
DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.Status",
DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, 'A');
if (filter.DetailType.HasValue)
{
if (filter.DetailType.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cp.PrepayAmt", DbType.Decimal, "@PrepayAmt", QueryConditionOperatorType.MoreThanOrEqual, 0);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cp.PrepayAmt", DbType.Decimal, "@PrepayAmt", QueryConditionOperatorType.LessThan, 0);
}
}
dataCommand.AddOutParameter("@StartBalance", DbType.Double, 50);
dataCommand.AddOutParameter("@EndBalance", DbType.Double, 50);
dataCommand.AddOutParameter("@PayedIn", DbType.Double, 50);
dataCommand.AddOutParameter("@PayedOut", DbType.Double, 50);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE01#", t1.ToString());
dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE02#", t2.ToString());
dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE03#", t3.ToString());
result = ExecuteDataTable(dataCommand, filter, out totalCount);
}
return(result);
}
/// <summary>
/// 查询VendorProdcut
/// </summary>
/// <returns></returns>
public DataTable VendorProductQuery(VendorProductQueryFilter filter, out int TotalCount)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("External_Query_VendorProduct");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ProductType", DbType.Int32,
"@ProductType", QueryConditionOperatorType.Equal,
0);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.Int32,
"@Status", QueryConditionOperatorType.MoreThanOrEqual,
0);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ManufacturerSysNo", DbType.Int32,
"@ManufacturerSysNo", QueryConditionOperatorType.Equal,
filter.VendorManufacturerSysNo);
command.AddInputParameter("@UserSysNo", DbType.Int32, filter.UserSysNo);
if (filter.C3SysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.C3SysNo", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal,
filter.C3SysNo);
}
else
{
if (filter.C2SysNo.HasValue)
{
ConditionConstructor subQueryBuilder = sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"a.C3SysNo",
QueryConditionOperatorType.In,
@"SELECT [Category3Sysno]
FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo]
WHERE [Category2Sysno]=@C2SysNo");
command.AddInputParameter("@C2SysNo", DbType.Int32, filter.C2SysNo);
}
}
if (!filter.IsAuto)
{
QueryConditionOperatorType ot = QueryConditionOperatorType.Exist;
if (!filter.IsMapping)
{
ot = QueryConditionOperatorType.NotExist;
}
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
ot,
@"SELECT TOP 1 1
FROM [IPP3].[dbo].VendorUser_ProductMapping
WHERE
ProductSysNo=a.sysno
AND UserSysNo=@UserSysNo
AND VendorExSysNo=@VendorExSysNo");
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal, filter.VendorSysNo);
command.AddInputParameter(
"@VendorManufacturerSysNo",
DbType.Int32,
filter.ManufacturerSysNo);
command.CommandText = sb.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
TotalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询商品库存调整单列表
/// </summary>
/// <param name="queryCriteria"></param>
/// <returns></returns>
public static QueryResult <ProductStockAdjustViewInfo> QueryProductStockAdjustmentList(ProductStockAdjustListQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductStockAdjustmentList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "ps.SysNo DESC" : queryFilter.SortFields))
{
//Set SQL WHERE Condition:
#region Build Condition:
if (queryFilter.VendorSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value);
}
var sysno = 0;
if (!string.IsNullOrEmpty(queryFilter.SysNo) && int.TryParse(queryFilter.SysNo, out sysno))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, sysno);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.Status", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
}
if (queryFilter.StockSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.StockSysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo.Value);
}
int productSysNo = 0;
if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) && int.TryParse(queryFilter.ProductSysNo, out productSysNo))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(" Exists(SELECT TOP 1 1 FROM ECStore.dbo.Product_StockAdjustDetail (NOLOCK) WHERE ProductSysNo = {0} AND AdjustSysNo = ps.SysNo)", productSysNo));
}
if (!string.IsNullOrEmpty(queryFilter.CreateDateFrom))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.InDate", DbType.DateTime, "@DateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, Convert.ToDateTime(queryFilter.CreateDateFrom));
}
if (!string.IsNullOrEmpty(queryFilter.CreateDateTo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.InDate", DbType.DateTime, "@DateTimeTo", QueryConditionOperatorType.LessThan, Convert.ToDateTime(queryFilter.CreateDateTo).AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.AuditDateFrom))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.AuditDate", DbType.DateTime, "@AuditTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, Convert.ToDateTime(queryFilter.AuditDateFrom));
}
if (!string.IsNullOrEmpty(queryFilter.AuditDateTo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.AuditDate", DbType.DateTime, "@AuditTimeTo", QueryConditionOperatorType.LessThan, Convert.ToDateTime(queryFilter.AuditDateTo).AddDays(1));
}
#endregion
command.CommandText = sqlBuilder.BuildQuerySql();
List <ProductStockAdjustViewInfo> resultList = command.ExecuteEntityList <ProductStockAdjustViewInfo>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <ProductStockAdjustViewInfo>()
{
PageInfo = new PageInfo()
{
PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
}, ResultList = resultList
});
}
}
/// <summary>
/// 按PM统计
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable ExportAllByPM(LendRequestQueryFilter queryCriteria, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.MaximumRows = queryCriteria.PagingInfo.PageSize;
pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryLendItemforExport");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " userInfo.DisplayName ASC"))
{
if (queryCriteria.SysNo.HasValue && queryCriteria.SysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lend.SysNo", DbType.Int32, "@RequestSysNo",
QueryConditionOperatorType.Equal, queryCriteria.SysNo);
}
if (!string.IsNullOrEmpty(queryCriteria.RequestID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lend.LendID", DbType.String, "@RequestID",
QueryConditionOperatorType.Like, queryCriteria.RequestID);
}
if (queryCriteria.LendUserSysNo.HasValue && queryCriteria.LendUserSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lendUser.IPPUserSysNo", DbType.String, "@LendUserSysNo",
QueryConditionOperatorType.LeftLike, queryCriteria.LendUserSysNo);
}
if (queryCriteria.CreateDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lend.CreateTime", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.CreateDateFrom);
}
if (queryCriteria.CreateDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lend.CreateTime", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThanOrEqual, queryCriteria.CreateDateTo);
}
if (queryCriteria.RequestStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lend.Status", DbType.Int32, "@RequestStatus",
QueryConditionOperatorType.Equal, queryCriteria.RequestStatus);
}
if (queryCriteria.StockSysNo.HasValue && queryCriteria.StockSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"stock.SysNo", DbType.Int32, "@StockSysNo",
QueryConditionOperatorType.Equal, queryCriteria.StockSysNo);
}
if (queryCriteria.ProductSysNo.HasValue && queryCriteria.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lendItem.productsysno", DbType.Int32, "@ProductSysNo",
QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo);
}
if (queryCriteria.PMUserSysNo.HasValue && queryCriteria.PMUserSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"itemBasic.PMUserSysNo", DbType.Int32, "@PMUserSysNo",
QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo);
}
if (!string.IsNullOrEmpty(queryCriteria.CompanyCode))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"lendItem.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal, queryCriteria.CompanyCode);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable resultData = cmd.ExecuteDataTable();
totalCount = 10000;
return(resultData);
}
}
public System.Data.DataTable QueryBasketList(QueryFilter.PO.PurchaseOrderBasketQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryBasket");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "Basket.SysNo DESC"))
{
if (queryFilter != null)
{
//by Jack.W.Wang 2012-12-6 CRL21776--------------------------BEGIN
if (!queryFilter.IsManagerPM.HasValue || !queryFilter.IsManagerPM.Value)
{
string tSubSqlTxt = @"Select
ProductLineSysNo
FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " +
"WHERE PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0";
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "line.ProductLineSysNo", QueryConditionOperatorType.In, tSubSqlTxt);
}
//by Jack.W.Wang 2012-12-6 CRL21776--------------------------END
//TODO:获取PM List;
#region 权限筛选
//var pmHasRight = AuthorizedPMs();
//var createUserSysNo = query.Condition.CreateUserSysNo;
//if (!string.IsNullOrEmpty(createUserSysNo))
//{
// var createIsValid = pmHasRight.Exists(
// x => x == Convert.ToInt32(createUserSysNo));
// if (createIsValid)
// {
// builder.ConditionConstructor.AddCustomCondition(
// QueryConditionRelationType.AND,
// "Basket.CreateUserSysNo in(" + createUserSysNo + ")");
// }
// else
// {
// builder.ConditionConstructor.AddInCondition(
// QueryConditionRelationType.AND,
// "Basket.CreateUserSysNo",
// DbType.Int32,
// new List<int> { -999 });
// }
//}
//else
//{
// builder.ConditionConstructor.AddInCondition(
// QueryConditionRelationType.AND,
// "Basket.CreateUserSysNo",
// DbType.Int32,
// pmHasRight);
//}
#endregion
if (queryFilter.StockSysNo.HasValue)
{
string stockStr = queryFilter.StockSysNo.ToString();
//有中转仓
if (stockStr.Length > 2)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, Int32.Parse(stockStr.Substring(2)));
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.IsTransfer", DbType.Int32,
"@IsTransfer", QueryConditionOperatorType.Equal, 1);
}
else
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
}
}
if (!string.IsNullOrEmpty(queryFilter.CreateUserSysNo))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Basket.CreateUserSysNo",
DbType.Int32,
"@CreateUserSysNo",
QueryConditionOperatorType.Equal,
Int32.Parse(queryFilter.CreateUserSysNo));
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
}
command.CommandText = builder.BuildQuerySql();
}
EnumColumnList enumList = new EnumColumnList();
enumList.Add("PaySettleCompany", typeof(PaySettleCompany));
dt = command.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
public DataTable Query(AdvertisingQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PageInfo.SortBy;
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("AdvertisingQuery");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "product.InDate DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.SysNo",
DbType.Int32,
"@ProductLineCategorySysNo",
QueryConditionOperatorType.Equal,
filter.ProductLineCategorySysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Link.ProductLineSysNo",
DbType.Int32,
"@ProductLineSysNo",
QueryConditionOperatorType.Equal,
filter.ProductLineSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Link.InUser",
DbType.String,
"@InUser",
QueryConditionOperatorType.Like,
filter.InUser);
string sql = string.Empty;
if (filter.OperateDateFrom.HasValue)
{
sql = string.Format(@" Link.InDate >= '{0}'", filter.OperateDateFrom);
}
if (filter.OperateDateTo.HasValue)
{
sql = string.Format(@" Link.InDate <= '{0}' or Link.EditDate <='{0}'",
filter.OperateDateTo.Value.AddDays(1).AddSeconds(-1));
}
if (filter.OperateDateFrom.HasValue && filter.OperateDateTo.HasValue)
{
sql = string.Format(@" ((Link.InDate >= '{0}' AND Link.InDate <='{1}') OR (Link.EditDate >= '{0}' AND Link.EditDate <='{1}'))",
filter.OperateDateFrom, filter.OperateDateTo.Value.AddDays(1).AddSeconds(-1));
}
if (!string.IsNullOrEmpty(sql))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, sql);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Link.ImageWidth",
DbType.Int32,
"@ImageWidth",
QueryConditionOperatorType.Equal,
filter.ImageWidth);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Link.ImageHeight",
DbType.Int32,
"@ImageHeight",
QueryConditionOperatorType.Equal,
filter.ImageHeight);
object _type;
if (filter.Type != null && EnumCodeMapper.TryGetCode(filter.Type, out _type))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Link.Type",
DbType.String,
"@Type",
QueryConditionOperatorType.Like,
_type);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Type", typeof(AdvertisingType));
enumList.Add("Status", typeof(ValidStatus));
var dt = cmd.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public virtual DataTable Query(QueryFilter.Customer.RefundRequestQueryFilter queryCriteria, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = queryCriteria.PagingInfo.SortBy;
pagingEntity.MaximumRows = queryCriteria.PagingInfo.PageSize;
pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRefundRequest");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " R.SysNo DESC "))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.RequestType",
DbType.String, "@RequestType",
QueryConditionOperatorType.Equal,
queryCriteria.RequestType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.SysNo",
DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.SOSysNo",
DbType.Int32, "@SOSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.RefundType",
DbType.String, "@RefundType",
QueryConditionOperatorType.Equal,
queryCriteria.RefundType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.CustomerID",
DbType.String, "@CustomerID",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerId);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.EditUser",
DbType.String, "@EditUser",
QueryConditionOperatorType.Equal,
queryCriteria.EditUserName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.InDate",
DbType.DateTime, "@DateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.InDate",
DbType.DateTime, "@DateTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.EditDate",
DbType.DateTime, "@EditDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.EditFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.EditDate",
DbType.DateTime, "@EditDateTo",
QueryConditionOperatorType.LessThan,
queryCriteria.EditTo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"R.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
queryCriteria.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add(4, typeof(RefundRequestType));
enumList.Add(12, typeof(RefundRequestStatus));
CodeNamePairColumnList cpList = new CodeNamePairColumnList();
cpList.Add(5, "Customer", "RefundType");
DataTable dt = cmd.ExecuteDataTable(enumList, cpList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <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 DataTable RoleQuery(VendorRoleQueryFilter filter, out int dataCount)
{
if (filter.PagingInfo != null && filter.PagingInfo.SortBy != null)
{
string sortCondition = filter.PagingInfo.SortBy.Trim();
Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase);
if (match.Groups["SortColumn"].Success)
{
string sortColumn = match.Groups["SortColumn"].Value;
string sortType = match.Groups["SortType"].Success ?
match.Groups["SortType"].Value : "DESC";
#region switch
switch (sortColumn)
{
case "SysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "SysNo", sortType);
break;
case "RoleName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "RoleName", sortType);
break;
case "Status":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "Status", sortType);
break;
case "InUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "InUser", sortType);
break;
case "InDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "InDate", sortType);
break;
case "EditUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "EditUser", sortType);
break;
case "EditDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "EditDate", sortType);
break;
}
#endregion
}
}
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("ExternalSYS_Query_VendorRole");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.RoleSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"RoleName", DbType.String,
"@RoleName", QueryConditionOperatorType.LeftLike,
filter.RoleName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal,
filter.Status);
if (!string.IsNullOrEmpty(filter.PrivilegeName))
{
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
@"SELECT 1
FROM (
SELECT
[RoleSysNo]
FROM [IPP3].[dbo].[VendorUser_Role_Privilege] WITH(NOLOCK)
WHERE
[PrivilegeSysNo] IN (
SELECT
[SysNo]
FROM [IPP3].[dbo].[VendorUser_Privilege] WITH(NOLOCK)
WHERE
[Memo] LIKE @PrivilegeName
)
) AS RESULT
WHERE
RESULT.[RoleSysNo]=[VendorUser_Role].[SysNo]");
command.AddInputParameter("@PrivilegeName", DbType.String,
filter.PrivilegeName + "%");
}
command.CommandText = builder.BuildQuerySql();
var dt = command.ExecuteDataTable("Status", typeof(ValidStatus));
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询充值卡
/// </summary>
/// <param name="pageIndex">取第几页</param>
/// <param name="pageSize">每页显示几条</param>
/// <param name="category">卡类型</param>
/// <param name="status">状态</param>
/// <param name="beginTime">有效期起</param>
/// <param name="endTime">有效期止</param>
/// <returns></returns>
public static PageList<List<PayCard>> QueryPayCard(int pageIndex, int pageSize, int category, int status, DateTime beginTime, DateTime endTime)
{
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = pageSize;
page.StartRowIndex = pageIndex * pageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("PayCard_Query");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
if (category > 0)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CategorySysNo", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, category);
if (status >= 0)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "BeginTime", DbType.DateTime, "@BeginTime", QueryConditionOperatorType.MoreThanOrEqual, beginTime);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndTime", DbType.DateTime, "@EndTime", QueryConditionOperatorType.LessThanOrEqual, endTime);
cmd.CommandText = sqlBuilder.BuildQuerySql();
List<PayCard> result = cmd.ExecuteEntityList<PayCard>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return new PageList<List<PayCard>>(result, pageIndex, pageSize, totalCount);
}
}
public static QueryResult <SOInfo> SOQuery(SOQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SOQuery");
using (
var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter,
string.IsNullOrEmpty(queryFilter.SortFields) ? "s.SysNo ASC" : queryFilter.SortFields))
{
if (queryFilter.SOSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
string.Format("(so.SysNo = {0} or sc.SoSplitMaster = {0})", queryFilter.SOSysNo));
}
if (queryFilter.OrderDateBegin.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate",
DbType.DateTime, "@OrderDateBegin",
QueryConditionOperatorType.MoreThanOrEqual, queryFilter.OrderDateBegin);
}
if (queryFilter.OrderDateEnd.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate",
DbType.DateTime, "@OrderDateEnd",
QueryConditionOperatorType.LessThan, queryFilter.OrderDateEnd.Value.Date.AddDays(1));
}
if (!string.IsNullOrWhiteSpace(queryFilter.CustomerID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cust.CustomerID",
DbType.String, "@CustomerID",
QueryConditionOperatorType.Equal, queryFilter.CustomerID);
}
if (queryFilter.ProductSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
string.Format(@"(so.SysNo in (SELECT si.sosysno
from ipp3..so_item si(nolock)
WHERE si.ProductSysNo = {0}))" , queryFilter.ProductSysNo));
}
if (!string.IsNullOrWhiteSpace(queryFilter.ReceivePhone))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"(so.ReceivePhone = @ReceivePhone OR so.ReceiveCellPhone = @ReceivePhone)");
command.AddInputParameter("@ReceivePhone", DbType.String, queryFilter.ReceivePhone);
}
if (queryFilter.SOStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.Status",
DbType.Int32, "@SOStatus",
QueryConditionOperatorType.Equal, (int)queryFilter.SOStatus.Value);
}
if (queryFilter.PayTypeSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.PayTypeSysNo",
DbType.Int32, "@PayTypeSysNo",
QueryConditionOperatorType.Equal, queryFilter.PayTypeSysNo);
}
if (queryFilter.ShipTypeSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.ShipTypeSysNo",
DbType.Int32, "@ShipTypeSysNo",
QueryConditionOperatorType.Equal, queryFilter.ShipTypeSysNo);
}
if (queryFilter.ConsolidatedPaymentStatus.HasValue)
{
//已支付未审核
if (queryFilter.ConsolidatedPaymentStatus == ConsolidatedPaymentStatus.PaymentWaitApprove)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"(netpay.Status = 0)");
}
//未支付
else if (queryFilter.ConsolidatedPaymentStatus == ConsolidatedPaymentStatus.PaymentNone)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"(netpay.SysNo IS NULL OR netpay.Status = -1)");
}
// 匹配SOIncome状态
else
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
"fs.Status = " + (int)queryFilter.ConsolidatedPaymentStatus.Value);
}
}
if (queryFilter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sc.MerchantSysNo",
DbType.Int32, "@MerchantSysNo",
QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo);
}
if (string.IsNullOrWhiteSpace(queryFilter.SortFields))
{
queryFilter.SortFields = "so.[OrderDate] DESC";
}
command.CommandText = sqlBuilder.BuildQuerySql();
List <SOInfo> resultList = command.ExecuteEntityList <SOInfo>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <SOInfo>
{
PageInfo = new PageInfo
{
PageIndex = queryFilter.PageIndex,
PageSize = queryFilter.PageSize,
TotalCount = totalCount,
SortBy = queryFilter.SortFields
},
ResultList = resultList
});
}
}
public static QueryResult <RMARequestQueryResultInfo> QueryList(RMARequestQueryFilter filter)
{
QueryResult <RMARequestQueryResultInfo> result = new QueryResult <RMARequestQueryResultInfo>();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRMARequestList");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "SysNo DESC"))
{
//订单编号
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOSysNo", DbType.Int32
, "@SOSysNo", QueryConditionOperatorType.Equal, filter.SOSysNo);
//订单日期
DateTime orderDateFrom, orderDateTo;
if (!string.IsNullOrWhiteSpace(filter.OrderDateFrom) && DateTime.TryParse(filter.OrderDateFrom, out orderDateFrom))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrderDate", DbType.DateTime
, "@OrderDateFrom", QueryConditionOperatorType.MoreThanOrEqual, orderDateFrom);
}
if (!string.IsNullOrWhiteSpace(filter.OrderDateTo) && DateTime.TryParse(filter.OrderDateTo, out orderDateTo))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrderDate", DbType.DateTime
, "@OrderDateTo", QueryConditionOperatorType.LessThan, orderDateTo.Date.AddDays(1));
}
//顾客帐号
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CustomerID", DbType.String
, "@CustomerID", QueryConditionOperatorType.Like, filter.CustomerID);
//退货单编号
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RequestID", DbType.String
, "@RequestID", QueryConditionOperatorType.Equal, filter.RequestID);
//申请日期
DateTime requestDateFrom, requestDateTo;
if (!string.IsNullOrWhiteSpace(filter.RequestDateFrom) && DateTime.TryParse(filter.RequestDateFrom, out requestDateFrom))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.CreateTime", DbType.DateTime
, "@RequestDateFrom", QueryConditionOperatorType.MoreThanOrEqual, requestDateFrom);
}
if (!string.IsNullOrWhiteSpace(filter.RequestDateTo) && DateTime.TryParse(filter.RequestDateTo, out requestDateTo))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.CreateTime", DbType.DateTime
, "@RequestDateTo", QueryConditionOperatorType.LessThan, requestDateTo.Date.AddDays(1));
}
//退换货状态
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.Status", DbType.Int32
, "@Status", QueryConditionOperatorType.Equal, filter.Status);
//商品编号
if (!string.IsNullOrWhiteSpace(filter.ProductSysNo))
{
builder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, String.Format(@"
EXISTS (SELECT TOP 1 1 FROM dbo.RMA_Request_Item WITH(NOLOCK)
INNER JOIN dbo.RMA_Register WITH(NOLOCK) ON RMA_Request_Item.RegisterSysno = RMA_Register.Sysno
INNER JOIN dbo.Product WITH(NOLOCK) ON Product.Sysno = RMA_Register.ProductSysNo
WHERE RMA_Request_Item.RequestSysno =request.Sysno AND RMA_Register.ProductSysNo= {0})", filter.ProductSysNo));
}
//电话号码
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.Phone", DbType.String
, "@Phone", QueryConditionOperatorType.Equal, filter.ReceiverPhone);
//商家编号
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.MerchantSysno", DbType.Int32
, "@MerchantSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo);
command.CommandText = builder.BuildQuerySql();
StringBuilder sb = new StringBuilder();
List <RMARequestQueryResultInfo> resultList = command.ExecuteEntityList <RMARequestQueryResultInfo>((s, t) =>
{
sb.Clear();
if (!String.IsNullOrWhiteSpace((string)s["RequestType"]))
{
string[] types = s["RequestType"].ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < types.Length; i++)
{
sb.AppendFormat("{0}/", EnumHelper.GetDescription((RMARequestType)Enum.Parse(typeof(RMARequestType), types[i])));
}
t.RequestType = sb.ToString().TrimEnd('/');
}
t.Status = EnumHelper.GetDescription((RMARequestStatus)Enum.Parse(typeof(RMARequestStatus), s["Status"].ToString()));
});
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
result.PageInfo = new PageInfo()
{
PageIndex = filter.PageIndex,
PageSize = filter.PageSize,
TotalCount = totalCount,
SortBy = filter.SortFields
};
result.ResultList = resultList;
}
return(result);
}
/// <summary>
/// 分页查询商品
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult <ProductCommonInfo> QueryCommonProduct(ProducCommonQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCommonProduct");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "P.[SysNo] ASC" : queryFilter.SortFields))
{
if (!string.IsNullOrEmpty(queryFilter.VendorSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.[SysNo]", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
}
if (!string.IsNullOrEmpty(queryFilter.BrandSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[BrandSysNo]", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, queryFilter.BrandSysNo);
}
if (!string.IsNullOrEmpty(queryFilter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductID]", DbType.String, "@ProductID", QueryConditionOperatorType.Like, queryFilter.ProductID);
}
if (!string.IsNullOrEmpty(queryFilter.ProductTitle))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductTitle]", DbType.String, "@ProductTitle", QueryConditionOperatorType.Like, queryFilter.ProductTitle);
}
if (!string.IsNullOrEmpty(queryFilter.FrontCategorySysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[FrontCategorySysNo]", DbType.Int32
, "@FrontCategorySysNo", QueryConditionOperatorType.Like, Convert.ToInt32(queryFilter.FrontCategorySysNo));
}
if (!string.IsNullOrEmpty(queryFilter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
}
if (queryFilter.CreateTimeBegin != null && queryFilter.CreateTimeBegin != new DateTime())
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeBegin", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.ToString("yyyy-MM-dd"));
}
if (queryFilter.CreateTimeEnd != null && queryFilter.CreateTimeEnd != new DateTime())
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeEnd", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeEnd.AddDays(1).ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrEmpty(queryFilter.GroupName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PG.[ProductName]", DbType.String, "@GroupName", QueryConditionOperatorType.Like, queryFilter.GroupName);
}
if (!string.IsNullOrEmpty(queryFilter.ProductTradeType))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[ProductTradeType]", DbType.String, "@ProductTradeType", QueryConditionOperatorType.Equal, queryFilter.ProductTradeType);
}
command.CommandText = sqlBuilder.BuildQuerySql();
List <ProductCommonInfo> resultList = command.ExecuteEntityList <ProductCommonInfo>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <ProductCommonInfo>()
{
PageInfo = new PageInfo()
{
PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
}, ResultList = resultList
});
}
}
/// <summary>
/// 产品咨询查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <ProductMatchedTradingQueryBasicInfo> QueryProductMatchedTradingBasicInfoList(ProductMatchedTradingQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.SortFields;
pagingEntity.MaximumRows = filter.PageSize;
pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductMatchedTrading_GetProductMatchedTradingDetailList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, string.IsNullOrEmpty(pagingEntity.SortField) ? pagingEntity.SortField : "A.SysNo DESC"))
{
if (filter.SellerSysNo.HasValue)
{
dataCommand.AddInputParameter("@SellerSysNo", DbType.String, filter.SellerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.MerchantSysNo",
DbType.String, "@SellerSysNo",
QueryConditionOperatorType.Equal,
filter.SellerSysNo);
}
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
//中蛋故有
if (filter.VendorType == 1)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "VD.VendorType=0");
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VD.SysNo", DbType.Int32, "@VendorType", QueryConditionOperatorType.Equal, filter.VendorType);
}
if (filter.ProductGroupNo != 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.ProductGroupSysno", DbType.Int32, "@GroupID", QueryConditionOperatorType.Equal, filter.ProductGroupNo);
}
if (filter.IsByGroup)
{
if (filter.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "B.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))");
}
}
else
{
if (!string.IsNullOrEmpty(filter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE ProductID = '" + filter.ProductID + "')");
}
}
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.InDateFrom, filter.InDateTo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.EditDateFrom, filter.EditDateTo);
//商品类别
if (filter.Category1SysNo != null && filter.Category2SysNo != null && filter.Category3SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE C3SysNo = " + filter.Category3SysNo + " AND CompanyCode=" + filter.CompanyCode + ")");
}
else if (filter.Category1SysNo != null && filter.Category2SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT p.SysNo FROM ipp3.dbo.Product p WITH (NOLOCK),ipp3.dbo.Category3 c WITH (NOLOCK) WHERE p.C3SysNo = c.SysNo AND c.C2SysNo =" + filter.Category2SysNo + " AND P.CompanyCode=" + filter.CompanyCode + " AND C.CompanyCode=" + filter.CompanyCode + ")");
}
else if (filter.Category1SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT p.SysNo FROM ipp3.dbo.Product p WITH (NOLOCK),ipp3.dbo.Category3 c3 WITH (NOLOCK),ipp3.dbo.Category2 c2 WITH (NOLOCK) WHERE p.C3SysNo = c3.SysNo AND c3.C2SysNo = c2.SysNo AND c2.C1SysNo = " + filter.Category1SysNo + " AND P.companycode=" + filter.CompanyCode + " AND c2.companycode=" + filter.CompanyCode + " AND c3.companycode=" + filter.CompanyCode + ")");
}
if (filter.PMUserSysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE PMUserSysNo = " + filter.PMUserSysNo + ")");
}
if (filter.ProductStatus != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE Status = " + filter.ProductStatus + ")");
}
if (!string.IsNullOrEmpty(filter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status.Substring(0, 1));
if (filter.Status.Length > 1)
{
if (filter.Status.Substring(2, 1) == "1")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.EditUser='******'");
}
else if (filter.Status.Substring(2, 1) == "2")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.EditUser<>'System'");
}
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type", DbType.String, "@Type", QueryConditionOperatorType.Equal, filter.Type);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Content", DbType.String, "@Content", QueryConditionOperatorType.Like, filter.Content);
if (filter.CustomerSysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.CustomerSysNo=" + filter.CustomerSysNo + "");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EditUser", DbType.String, "@EditUser", QueryConditionOperatorType.Like, filter.EditUser);
if (filter.CustomerCategory != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.CustomerSysNo IN ( SELECT SysNo FROM ipp3.dbo.Customer WITH (NOLOCK) WHERE CompanyCustomer = " + filter.CustomerCategory + ")");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
//CodeNamePairColumnList pairList = new CodeNamePairColumnList();
//pairList.Add("Status", "MKT", "ReplyStatus");//咨询状态
////pairList.Add("ReferenceType", "MKT", "CommentsCategory");
//var dt = cmd.ExecuteDataTable(pairList);
//totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
List <ProductMatchedTradingQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductMatchedTradingQueryBasicInfo>();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(list);
}
}
public DataTable Query(FreeShippingChargeRuleQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryFreeShippingChargeRule");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, null, "[SysNo] DESC"))
{
if (filter.StartDate.HasValue || filter.EndDate.HasValue)
{
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
#region 构造时间条件
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
// s1 ≤ start && t1 ≥ start
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date,
"@StartDate1", QueryConditionOperatorType.MoreThanOrEqual, filter.StartDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date,
"@EndDate1", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate);
builder.ConditionConstructor.EndGroupCondition();
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
// s1 ≥ start && t1 ≤ end
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date,
"@StartDate2", QueryConditionOperatorType.LessThanOrEqual, filter.StartDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date,
"@EndDate2", QueryConditionOperatorType.MoreThanOrEqual, filter.EndDate);
builder.ConditionConstructor.EndGroupCondition();
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
// s1 ≤ end && t1 ≥ end
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date,
"@StartDate3", QueryConditionOperatorType.MoreThanOrEqual, filter.StartDate);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date,
"@EndDate3", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate);
builder.ConditionConstructor.EndGroupCondition();
#endregion
builder.ConditionConstructor.EndGroupCondition();
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingValue", DbType.Decimal,
"@AmtFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AmtFrom);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingValue", DbType.Decimal,
"@AmtTo", QueryConditionOperatorType.LessThanOrEqual, filter.AmtTo);
if (filter.PayTypeSysNo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayTypeSettingValue", DbType.AnsiString,
"@PayTypeSettingValueLike", QueryConditionOperatorType.Like, string.Format(",{0},", filter.PayTypeSysNo));
}
if (filter.ShipAreaID.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ShipAreaSettingValue", DbType.AnsiString,
"@ShipAreaSettingValueLike", QueryConditionOperatorType.Like, string.Format(",{0},", filter.ShipAreaID));
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal, filter.Status);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingType", DbType.Int32,
"@AmountSettingType", QueryConditionOperatorType.Equal, filter.AmountSettingType);
command.CommandText = builder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("AmountSettingType", typeof(FreeShippingAmountSettingType));
enumList.Add("Status", typeof(FreeShippingAmountSettingStatus));
DataTable dt = command.ExecuteDataTable(enumList);
if (dt != null && dt.Rows.Count > 0)
{
string areaSettingName = String.Empty;
string[] areaNameParts;
foreach (DataRow row in dt.Rows)
{
if (!(row["AreaSettingName"] is DBNull) && row["AreaSettingName"] != null)
{
areaSettingName = Convert.ToString(row["AreaSettingName"]);
areaNameParts = areaSettingName.Split(new char[] { ',' }, StringSplitOptions.None);
if (areaNameParts.Length > 4)
{
row["AreaSettingName"] = string.Format("{0}等{1}个地区",
string.Join(",", areaNameParts.ToList().GetRange(0, 4)), areaNameParts.Length);
}
}
}
}
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
