/// <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>
/// 查询大于等于30天没登录过的用户
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static StatementResult<ArrayList> QueryThirtyDayNotLoginCustomers(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("QueryThirtyDayNotLoginCustomers");
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);
}
}
/// <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);
}
}
private void AddReceiveByVendorParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
if (filter.VendorSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.VendorNumber",
DbType.Int32,
"@VendorSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo);
}
if (filter.PMUserSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ruleinfo.PM",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo);
}
if (!string.IsNullOrEmpty(filter.EIMSType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (filter.ExpiredDays.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"DATEDIFF(DAY,invoice.DueDate,GETDATE())",
DbType.Int32,
"@ExpriedDays",
QueryConditionOperatorType.MoreThan,
filter.ExpiredDays);
}
cmd.AddOutParameter("@SumNoReceivedAmount", DbType.Double, sizeof(double));
cmd.AddOutParameter("@SumOverDueAmount", DbType.Double, sizeof(double));
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
cmd.CommandText = sb.BuildQuerySql();
}
private void AddReceiveByYearParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"accbyrule.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
if (filter.VendorSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"accbyrule.VendorNumber",
DbType.Int32,
"@VendorSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo);
}
if (!string.IsNullOrEmpty(filter.EIMSType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"accbyrule.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (filter.Year.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"substring(accbyrule.AccruePeriod,0,5)",
DbType.String,
"@Year",
QueryConditionOperatorType.Equal,
filter.Year);
}
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
cmd.CommandText = sb.BuildQuerySql();
}
public DataTable Query(PostIncomeQueryFilter query, out int totalCount)
{
DataTable result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (query.PagingInfo != null)
{
MapSortField(query.PagingInfo);
pagingInfo.MaximumRows = query.PagingInfo.PageSize;
pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
pagingInfo.SortField = query.PagingInfo.SortBy;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPostIncomeList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "a.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SOSysNo",
DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, query.SOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeAmt",
DbType.Decimal, "@IncomeAmt", QueryConditionOperatorType.Equal, query.IncomeAmt);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate",
DbType.Date, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.CreateDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate",
DbType.Date, "@CreateDateTo", QueryConditionOperatorType.LessThan, query.CreateDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate",
DbType.Date, "@IncomeDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.IncomeDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate",
DbType.Date, "@IncomeDateTo", QueryConditionOperatorType.LessThan, query.IncomeDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayBank",
DbType.String, "@PayBank", QueryConditionOperatorType.Like, query.PayBank);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeBank",
DbType.String, "@IncomeBank", QueryConditionOperatorType.Like, query.IncomeBank);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.DisplayName",
DbType.String, "@CreateUser", QueryConditionOperatorType.Like, query.CreateUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "e.DisplayName",
DbType.String, "@AuditUser", QueryConditionOperatorType.Like, query.AuditUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayUser",
DbType.String, "@PayUser", QueryConditionOperatorType.Like, query.PayUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
if (!string.IsNullOrEmpty(query.ConfirmedSOSysNoList))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.SysNo",
QueryConditionOperatorType.In, string.Format(@"SELECT a.PostIncomeSysNo FROM
[OverseaInvoiceReceiptManagement].[dbo].[PostIncomeConfirm] a WITH(NOLOCK)
WHERE a.ConfirmedSoSysNo IN ({0}) AND a.Status <> 'C'" , query.ConfirmedSOSysNoList.Replace('.', ',')));
}
if (query.HandleStatus != null)
{
switch (query.HandleStatus.Value)
{
case PostIncomeHandleStatusUI.UnConfirmed:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus",
DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.NotEqual, PostIncomeStatus.Confirmed);
break;
case PostIncomeHandleStatusUI.UnHandled:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus",
DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.WaitingHandle);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus",
DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.Equal, PostIncomeStatus.Confirmed);
break;
case PostIncomeHandleStatusUI.Handled:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus",
DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.Handled);
break;
default:
break;
}
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("HandleStatus", typeof(PostIncomeHandleStatus));
enumColumns.Add("ConfirmStatus", typeof(PostIncomeStatus));
result = dataCommand.ExecuteDataTable(enumColumns);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
public static List <ProductCategoryQueryBasicInfo> QueryProductCategory(ProductCategoryQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductCategory");
var pagingInfo = new PagingInfoEntity
{
StartRowIndex = queryCriteria.PageIndex * queryCriteria.PageSize,
MaximumRows = queryCriteria.PageSize,
SortField = queryCriteria.SortFields,
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, string.IsNullOrEmpty(pagingInfo.SortField) ? pagingInfo.SortField : "P.ProductSysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.ProductSysNo))
{
dataCommand.AddInputParameter("@ProductID", DbType.String, queryCriteria.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.ProductSysNo",
DbType.String, "@ProductID",
QueryConditionOperatorType.Like,
queryCriteria.ProductSysNo);
}
if (!String.IsNullOrEmpty(queryCriteria.ProductName))
{
dataCommand.AddInputParameter("@ProductName", DbType.String, queryCriteria.ProductName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.ProductName",
DbType.String, "@ProductName",
QueryConditionOperatorType.Like,
queryCriteria.ProductName);
}
if (!String.IsNullOrEmpty(queryCriteria.ProductAttachmentSysNo))
{
dataCommand.AddInputParameter("@AttachmentID", DbType.String, queryCriteria.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.AttachmentSysNo",
DbType.String, "@ProductAttachmentSysNo",
QueryConditionOperatorType.Like,
queryCriteria.ProductAttachmentSysNo);
}
//if (!String.IsNullOrEmpty(queryCriteria.ProductAttachmentSysNo))
//{
// dataCommand.AddInputParameter("@AttachmentName", DbType.String, queryCriteria.AttachmentName);
// sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "P.AttachmentName",
// DbType.String, "@AttachmentName",
// QueryConditionOperatorType.Like,
// queryCriteria.AttachmentName);
//}
if (!String.IsNullOrEmpty(queryCriteria.EditUser))
{
dataCommand.AddInputParameter("@EditUser", DbType.String, queryCriteria.EditUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.InUser",
DbType.String, "@EditUser",
QueryConditionOperatorType.Like,
queryCriteria.EditUser);
}
//sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.LessThanOrEqual, QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateEnd, queryCriteria.InDateStart);
if (queryCriteria.InDateStart != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@InDateStart",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.InDateStart
);
}
if (queryCriteria.InDateEnd != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"P.InDate",
DbType.DateTime,
"@InDateEnd",
QueryConditionOperatorType.LessThanOrEqual,
queryCriteria.InDateEnd
);
}
if (queryCriteria.SellerSysNo.HasValue)
{
dataCommand.AddInputParameter("@SellerSysNo", DbType.String, queryCriteria.SellerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.SellerID",
DbType.String, "@SellerSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SellerSysNo);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
List <ProductCategoryQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductCategoryQueryBasicInfo>();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(list);
}
}
/// <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);
}
}
private void AddParameter4GiftInvoiceDetailReportQuery(GiftInvoiceDetaiReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "a.OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.OutDateFrom);
if (filter.OutDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "a.OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.OutDateTo.Value.AddDays(1).AddMinutes(-1));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sl.InvoiceCreateDate"
, DbType.DateTime
, "@InvoiceCreateTimeFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.InvoiceDateFrom);
if (filter.InvoiceDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sl.InvoiceCreateDate"
, DbType.DateTime
, "@InvoiceCreateTimeTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1));
}
if (!string.IsNullOrEmpty(filter.OrderID))
{
List <string> orderIDList = new List <string>();
foreach (string item in filter.OrderID.Split('.'))
{
orderIDList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "a.SOID", DbType.String, orderIDList);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sl.InvoiceNo"
, DbType.String
, "@InvoiceNumber"
, QueryConditionOperatorType.Like
, filter.InvoiceNumber);
int intTry;
decimal decimalTry;
if (!string.IsNullOrEmpty(filter.CustomerName))
{
if (int.TryParse(filter.CustomerName, out intTry) ||
decimal.TryParse(filter.CustomerName, out decimalTry))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "ct.CustomerName"
, DbType.String
, "@CustomerName"
, QueryConditionOperatorType.Equal
, filter.CustomerName);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "ct.CustomerName"
, DbType.String
, "@CustomerName"
, QueryConditionOperatorType.LeftLike
, filter.CustomerName);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.SOType"
, DbType.Int32
, "@SOType"
, QueryConditionOperatorType.Equal
, filter.SOType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "a.CompanyCode"
, DbType.AnsiStringFixedLength
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
}
/// <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
});
}
}
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 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);
}
/// <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
});
}
}
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(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 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
});
}
}
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);
}
}
}
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);
}
}
private static void BuilCondition <Q>(Q filter, string dataType, List <FilterField> filterFields, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "r.BusinessDataType", DbType.String, "@BusinessDataType", QueryConditionOperatorType.Equal, dataType);
List <string> changeTypes = new List <string>()
{
"A", "U"
};
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "r.ChangeType", DbType.String, changeTypes);
int index = 0;
filterFields.ForEach(p =>
{
object parameterValue = Invoker.PropertyGet(filter, p.Name);
string[] pNames = p.ValuePath.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
string path = string.Format("(//{0}/text())[1]", pNames.Join("/"));
DbType dbType = GetDbType(p.DBType);
string field = string.Format("r.BusinessData.value('{0}','{1}')", path, p.DBType);
QueryConditionOperatorType operatorType = GetEnum <QueryConditionOperatorType>(p.OperatorType);
QueryConditionRelationType relationType = GetEnum <QueryConditionRelationType>(p.RelationType);
sqlBuilder.ConditionConstructor.AddCondition(relationType,
field, dbType, "@Parameter" + index.ToString(), operatorType, parameterValue);
index++;
});
}
/// <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);
}
}
private void AddParameter4InvoiceDetailReportQuery(InvoiceDetailReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.OutDateFrom);
if (filter.OutDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.OutDateTo.Value.AddDays(1).AddMinutes(-1));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "InvoiceDate"
, DbType.DateTime
, "@InvoiceDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.InvoiceDateFrom);
if (filter.InvoiceDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "InvoiceDate"
, DbType.DateTime
, "@InvoiceDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1));
}
if (!string.IsNullOrEmpty(filter.OrderID))
{
List <string> orderIDList = new List <string>();
foreach (string item in filter.OrderID.Split('.'))
{
orderIDList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "OrderID", DbType.String, orderIDList);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "InvoiceNumber"
, DbType.String
, "@InvoiceNumber"
, QueryConditionOperatorType.Like
, filter.InvoiceNumber);
var customerNameString = filter.CustomerName;
int intTry;
decimal decimalTry;
if (!string.IsNullOrEmpty(customerNameString))
{
if (int.TryParse(customerNameString, out intTry) ||
decimal.TryParse(customerNameString, out decimalTry))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "CustomerName"
, DbType.String
, "@CustomerName"
, QueryConditionOperatorType.Equal
, filter.CustomerName);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "CustomerName"
, DbType.String
, "@CustomerName"
, QueryConditionOperatorType.Like
, filter.CustomerName);
}
}
}
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);
}
}
private void AddParameter4InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
//SOSysNo
var sosysNoString = filter.SOSysNo;
if (!string.IsNullOrEmpty(sosysNoString))
{
List <int> sosysNOList = new List <int>();
foreach (string item in sosysNoString.Split('.'))
{
sosysNOList.Add(int.Parse(item.Trim()));
}
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "so.SysNo", DbType.Int32, sosysNOList);
}
//SOStatusCode(已出库)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.Status"
, DbType.Int32
, "@SOStatusCode"
, QueryConditionOperatorType.Equal
, ECCentral.BizEntity.SO.SOStatus.OutStock);
//IsVAT是否增票
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.IsVAT"
, DbType.Int32
, "@IsVAT"
, QueryConditionOperatorType.Equal
, filter.IsVAT);
//CreateDateFrom
var createdateFrom = filter.CreateDateFrom;
if (createdateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OrderDate"
, DbType.DateTime
, "@CreateDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, createdateFrom.Value);
}
//CreateDateTo
var createdateTo = filter.CreateDateTo;
if (createdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OrderDate"
, DbType.DateTime
, "@CreateDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, createdateTo.Value.AddDays(1).AddSeconds(-1));
}
//OutDateFrom
var outdateFrom = filter.OutDateFrom;
if (outdateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, outdateFrom.Value);
}
//OutDateTo
var outdateTo = filter.OutDateTo;
if (outdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, outdateTo.Value.AddDays(1).AddSeconds(-1));
}
//AuditDateFrom
var auditDateFrom = filter.AuditDateFrom;
if (auditDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.AuditTime"
, DbType.DateTime
, "@AuditDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, auditDateFrom.Value);
}
//AuditDateTo
var auditdateTo = filter.AuditDateTo;
if (auditdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.AuditTime"
, DbType.DateTime
, "@AuditDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, auditdateTo.Value.AddDays(1).AddSeconds(-1));
}
//StockSysNo
var stockSysNOString = filter.StockSysNo;
if (string.IsNullOrEmpty(stockSysNOString))
{
//1:全部查询:只查询“发票打印”支持的分仓,从配置文件中取值，即StockSysNO="51,59,90,99"；
string invoicePrintstocks = this.GetInvoicePrintStocks(filter.CompanyCode);
List <string> stockNOList = new List <string>();
foreach (string item in invoicePrintstocks.Split(','))
{
stockNOList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND
, "sb.WareHouseNumber"
, DbType.AnsiStringFixedLength
, stockNOList);
}
else
{
//非全部查询
if (stockSysNOString.IndexOf(',') > 0)
{
//2:上海仓:实际查询为“嘉定仓”+“宝山仓”,即StockSysNO="51,59"
List <string> stockNOList = new List <string>();
foreach (string item in stockSysNOString.Split(','))
{
stockNOList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND
, "sb.WareHouseNumber"
, DbType.AnsiStringFixedLength
, stockNOList);
}
else
{
//3: 其他仓:即StockSysNO="90" 或者 StockSysNo="99"
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sb.WareHouseNumber"
, DbType.AnsiStringFixedLength
, "@StockSysNo"
, QueryConditionOperatorType.Equal
, stockSysNOString);
}
}
//MerchantSysNo
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.MerchantSysNo"
, DbType.Int32
, "@MerchantSysNo"
, QueryConditionOperatorType.Equal
, filter.VendorSysNo);
//InvoiceType
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.InvoiceType"
, DbType.AnsiStringFixedLength
, "@InvoiceType"
, QueryConditionOperatorType.Equal
, filter.InvoiceType);
//StockType
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.StockType"
, DbType.AnsiStringFixedLength
, "@StockType"
, QueryConditionOperatorType.Equal
, filter.StockType);
//ShippingType
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.ShippingType"
, DbType.AnsiStringFixedLength
, "@ShippingType"
, QueryConditionOperatorType.Equal
, filter.ShippingType);
//CompanyCode
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sb.CompanyCode"
, DbType.AnsiStringFixedLength
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
}
private void AddARReceiveDetialsParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ruleinfo.Status",
DbType.String,
"@RuleStatus",
QueryConditionOperatorType.Equal,
"O");
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.Status",
DbType.String,
"@InvoiceStatus",
QueryConditionOperatorType.Equal,
"O");
if (filter.VendorSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.VendorNumber",
DbType.Int32,
"@VendorSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo);
}
if (!string.IsNullOrEmpty(filter.EIMSType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"invoice.EIMSType",
DbType.String,
"@EIMSType",
QueryConditionOperatorType.Equal,
filter.EIMSType);
}
if (filter.ProductCategory1.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"item.ItemCategory1",
DbType.Int32,
"@ProductCategory1",
QueryConditionOperatorType.Equal,
filter.ProductCategory1);
}
if (filter.ProductCategory2.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"item.ItemCategory2",
DbType.Int32,
"@ProductCategory2",
QueryConditionOperatorType.Equal,
filter.ProductCategory2);
}
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
cmd.CommandText = sb.BuildQuerySql();
}
public static StorePage QueryStorePage(StorePageFilter filter)
{
CustomDataCommand cmd;
if (filter.IsPreview)
{
//preview
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageWithPreview");
using (var sqlBuild = new DynamicQuerySqlBuilder(cmd))
{
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"page.[SellerSysNo]", DbType.Int32, "@SellerSysNo",
QueryConditionOperatorType.Equal, filter.SellerSysNo);
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"page.[SysNo]", DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal, filter.PublishPageSysNo);
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"page.[PageTypeKey]", DbType.String, "@PageType",
QueryConditionOperatorType.Equal, filter.PageType);
cmd.CommandText = sqlBuild.BuildQuerySql();
}
}
else
{
//not preview
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageWithPublish");
#region build condition
using (var sqlBuild = new DynamicQuerySqlBuilder(cmd))
{
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"p.[SellerSysNo]", DbType.Int32, "@SellerSysNo",
QueryConditionOperatorType.Equal, filter.SellerSysNo);
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"p.[SysNo]", DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal, filter.PublishPageSysNo);
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.[PageTypeKey]", DbType.String, "@PageType",
QueryConditionOperatorType.Equal, filter.PageType);
sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"p.[Status]", DbType.Int32, "@Status",
QueryConditionOperatorType.Equal, 1);
cmd.CommandText = sqlBuild.BuildQuerySql();
}
#endregion
}
#if DEBUG
Console.WriteLine("Query Store Page Command is : {0}", cmd.CommandText);
#endif
var result = cmd.ExecuteEntity <StorePage>();
if (result == null)
{
return(null);
}
var page = SerializationUtility.JsonDeserialize2 <StorePage>(result.DataValue);
page.SysNo = result.SysNo.Value;
page.SellerSysNo = filter.SellerSysNo.Value;
var pageTheme = QueryStorePageTheme(page.StorePageTemplate.StorePageThemeSysNo);
if (pageTheme != null)
{
page.StorePageTemplate.StorePageThemeCssUrl = pageTheme.CssResUrl;
}
page.StorePageType = QueryStorePageType(page.StorePageType.Key);
return(page);
}
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);
}
/// <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 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);
}
/// <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);
}
}
private void AddSAPDOCHeaderParameters(HeaderDataQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
if (filter.UploadDateFrom.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"[PSTNG_DATE]",
DbType.DateTime,
"@UploadDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.UploadDateFrom);
}
if (filter.UploadDateTo.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"[PSTNG_DATE]",
DbType.DateTime,
"@UploadDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.UploadDateTo);
}
if (!string.IsNullOrEmpty(filter.DocType))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"[DOC_TYPE]",
DbType.String,
"@DOC_TYPE",
QueryConditionOperatorType.Equal,
filter.DocType);
}
if (filter.SapCoCode != ResCommonEnum.Enum_All)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"[COMP_CODE]",
DbType.String,
"@COMP_CODE",
QueryConditionOperatorType.Equal,
filter.SapCoCode);
}
cmd.CommandText = sb.BuildQuerySql();
}
private void AddEIMSEventMemoQueryParameters(EIMSEventMemoQueryFilter filter, DynamicQuerySqlBuilder sb, CustomDataCommand cmd)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventType", DbType.Int32, "@EventType", QueryConditionOperatorType.Equal, 195);
string tempMemo = string.Empty;
if (!string.IsNullOrEmpty(filter.Memo))
{
tempMemo = filter.Memo.Replace(" ", "%");
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventMemo", DbType.String, "@EventMemo", QueryConditionOperatorType.Like, "%" + tempMemo + "%");
}
if (filter.BeginDate.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventDate", DbType.String, "@BDate", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDate);
}
if (filter.EndDate.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventDate", DbType.String, "@EDate", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate.Value.ToShortDateString() + " 23:59:59");
}
cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
cmd.CommandText = sb.BuildQuerySql();
}
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);
}
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();
}
private void AddARReceiveParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
{
string strSql = " WHERE ruleinfo.Status = 'O' AND invoice.Status = 'O' AND invoice.CompanyCode = @CompanyCode";
cmd.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, filter.CompanyCode);
if (filter.VendorSysNo.HasValue)
{
strSql += " AND invoice.VendorNumber = @VendorSysNo ";
cmd.AddInputParameter("@VendorSysNo", DbType.Int32, filter.VendorSysNo);
}
if (!string.IsNullOrEmpty(filter.EIMSType))
{
strSql += " AND invoice.EIMSType = @EIMSType ";
cmd.AddInputParameter("@EIMSType", DbType.String, filter.EIMSType);
}
if (filter.ProductCategory1.HasValue)
{
strSql += " AND item.ItemCategory1 = @ItemCategory1 ";
cmd.AddInputParameter("@ItemCategory1", DbType.Int32, filter.ProductCategory1);
}
if (filter.ProductCategory2.HasValue)
{
strSql += " AND item.ItemCategory2 = @ItemCategory2 ";
cmd.AddInputParameter("@ItemCategory2", DbType.Int32, (filter.ProductCategory2));
}
cmd.CommandText = cmd.CommandText.Replace("#WhereStr1#", strSql);
strSql += " AND item.AccruePeriod = @AccruePeriod";
cmd.AddInputParameter("@AccruePeriod", DbType.String, GetEffectiveAccruePeriod());
cmd.CommandText = cmd.CommandText.Replace("#WhereStr2#", strSql);
cmd.AddOutParameter("@TotalCount", DbType.Int32, 4);
}
