public static DataTable UserQuery(UserQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Query_Vendor_User");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "a.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserName", DbType.String,
"@UserName", QueryConditionOperatorType.LeftLike,
filter.UserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserID", DbType.String,
"@UserID", QueryConditionOperatorType.LeftLike,
filter.UserID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal, filter.UserStatus);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.AnsiStringFixedLength, "@ManufacturerSysNo",
QueryConditionOperatorType.Equal,
filter.ManufacturerSysNo);
if (!string.IsNullOrEmpty(filter.SerialNum))
{
string[] serialNum = filter.SerialNum.Split('-');
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
serialNum[0]);
if (serialNum.Length > 1)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserNum", DbType.Int32,
"@UserNum", QueryConditionOperatorType.Equal,
Convert.ToInt32(serialNum[1]).ToString());
}
}
command.CommandText = builder.BuildQuerySql();
var dt = command.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(UserStatus));
command.ConvertEnumColumn(dt, enumColList);
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 导出节能补贴
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public DataTable QueryEnergySubsidyExport(RefundAdjustQueryFilter filter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_Get_EnergySubsidyExport");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command, "R.SysNo DESC"))
{
AddEnergySubsidyExportParameters(filter, command, sqlBuilder);
EnumColumnList colList = new EnumColumnList();
DataTable dt = command.ExecuteDataTable();
colList.Add("Status", typeof(RefundAdjustStatus));
command.ConvertEnumColumn(dt, colList);
return(dt);
}
}
/// <summary>
/// 以旧换新补贴款查询
/// </summary>
/// <param name="filter">查询条件集合</param>
/// <param name="totalCount">返回总记录数</param>
/// <returns></returns>
public DataTable OldChangeNewQuery(OldChangeNewQueryFilter filter, out int totalCount)
{
MapSortField(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_Query_OldChangeNew");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "result.SysNo desc"))
{
AddOldChangeNewParameters(filter, cmd, sb);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(OldChangeNewStatus));
cmd.ConvertEnumColumn(dt, enumColList);
dt.Columns.Add("StatusCode", typeof(string));
foreach (DataRow row in dt.Rows)
{
row["StatusCode"] = row["Status"].ToString();
}
#region 添加计算列
dt.Columns.Add("TotalRebate", typeof(decimal));
dt.Columns.Add("TotalReviseRebate", typeof(decimal));
dt.Columns.Add("TotalPassReviseRebate", typeof(decimal));
dt.Columns.Add("TotalReturnRebate", typeof(decimal));
dt.Columns.Add("TotalReturnReviseRebate", typeof(decimal));
if (dt != null &&
dt.Rows.Count > 0)
{
decimal TotalRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalRebate"));
decimal TotalReviseRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalReviseRebate"));
decimal TotalPassReviseRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalPassReviseRebate") == DBNull.Value ? 0m : cmd.GetParameterValue("TotalPassReviseRebate"));
decimal TotalReturnReviseRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalReturnReviseRebate") == DBNull.Value ? 0m : cmd.GetParameterValue("TotalReturnReviseRebate"));
dt.Rows[0]["TotalRebate"] = TotalRebate;
dt.Rows[0]["TotalReviseRebate"] = TotalReviseRebate;
dt.Rows[0]["TotalPassReviseRebate"] = TotalPassReviseRebate;
dt.Rows[0]["TotalReturnReviseRebate"] = TotalReturnReviseRebate;
}
#endregion
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable InvoiceInfoListQuery(EIMSInvoiceEntryQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("EIMS_Query_InvoiceInfoList");
MapSortField(filter);
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "ei.CreateDate DESC"))
{
AddInvoiceInfoListParameters(filter, cmd, sb);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(InvoiceStatus));
enumColList.Add("EIMSType", typeof(EIMSType));
enumColList.Add("ReceiveType", typeof(ReceiveType));
cmd.ConvertEnumColumn(dt, enumColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable UserQuery(VendorUserQueryFilter filter, out int dataCount)
{
if (filter.PagingInfo.SortBy != null)
{
string sortCondition = filter.PagingInfo.SortBy.Trim();
Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase);
if (match.Groups["SortColumn"].Success)
{
string sortColumn = match.Groups["SortColumn"].Value;
string sortType = match.Groups["SortType"].Success
? match.Groups["SortType"].Value
: "DESC";
#region switch
switch (sortColumn)
{
case "SysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.SysNo", sortType);
break;
case "RoleName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.RoleName", sortType);
break;
case "Status":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.Status", sortType);
break;
case "InUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.InUser", sortType);
break;
case "InDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.InDate", sortType);
break;
case "EditUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.EditUser", sortType);
break;
case "EditDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.EditDate", sortType);
break;
case "VendorSysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.VendorSysNo", sortType);
break;
case "VendorName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.VendorName", sortType);
break;
case "VendorStatus":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.Status", sortType);
break;
case "Rank":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.Rank", sortType);
break;
case "IsConsign":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.IsConsign", sortType);
break;
}
#endregion
}
}
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("External_Query_Vendor");
using (var builder = new DynamicQuerySqlBuilder(
command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.SysNo);
if (!string.IsNullOrEmpty(filter.SerialNum))
{
string[] serialNum = filter.SerialNum.Split('-');
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
serialNum[0]);
if (serialNum.Length > 1)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserNum", DbType.Int32,
"@UserNum", QueryConditionOperatorType.Equal,
Convert.ToInt32(serialNum[1]).ToString());
}
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserName", DbType.String,
"@UserName", QueryConditionOperatorType.LeftLike,
filter.UserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserID", DbType.String,
"@UserID", QueryConditionOperatorType.LeftLike,
filter.UserID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal,
((char?)filter.UserStatus).ToString());
if (filter.RoleSysNo.HasValue)
{
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null,
QueryConditionOperatorType.Exist,
@"SELECT TOP 1 1
FROM (SELECT
UserSysNo
FROM [IPP3].[dbo].[VendorUser_User_Role] WITH(NOLOCK)
WHERE
RoleSysNo=@RoleSysNo
) AS RESULT
WHERE
RESULT.[UserSysNo]=a.[SysNo]");
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.OR, null, QueryConditionOperatorType.Exist,
@"SELECT TOP 1 1
FROM (SELECT
UserSysNo
FROM [IPP3].[dbo].[VendorUser_RoleMapping] WITH(NOLOCK)
WHERE
RoleSysNo=@RoleSysNo
) AS RESULT
WHERE
RESULT.[UserSysNo]=a.[SysNo]");
command.AddInputParameter("@RoleSysNo", DbType.Int32, filter.RoleSysNo);
builder.ConditionConstructor.EndGroupCondition();
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
filter.VendorSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.VendorName", DbType.String,
"@VendorName", QueryConditionOperatorType.Like,
filter.VendorName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.ExpiredDate", DbType.DateTime,
"@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual,
filter.ExpiredDateFrom);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.ExpiredDate", DbType.DateTime,
"@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual,
filter.ExpiredDateTo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Contact", DbType.String,
"@Contact", QueryConditionOperatorType.LeftLike,
filter.Contact);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Phone", DbType.String,
"@Phone", QueryConditionOperatorType.LeftLike,
filter.Phone);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Address", DbType.String,
"@Address", QueryConditionOperatorType.LeftLike,
filter.Address);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Status", DbType.Int32,
"@VendorStatus", QueryConditionOperatorType.Equal,
filter.VendorStatus);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Rank", DbType.String,
"@Rank", QueryConditionOperatorType.Equal,
filter.Rank);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.IsConsign", DbType.Int32,
"@IsConsign", QueryConditionOperatorType.Equal,
filter.ConsignType);
if (filter.ManufacturerSysNo.HasValue ||
(!string.IsNullOrEmpty(filter.AgentLevel) &&
filter.AgentLevel.Trim() != "") ||
filter.C1SysNo.HasValue)
{
string subCond = @"SELECT distinct [VendorSysNo]
FROM dbo.Vendor_Manufacturer WITH(NOLOCK) ";
string strWhere = string.Empty;
//代理品牌 二级类 三级类 代理级别
if (filter.ManufacturerSysNo.HasValue)
{
strWhere += "ManufacturerSysNo=@ManufacturerSysNo";
command.AddInputParameter("@ManufacturerSysNo",
DbType.Int32, filter.ManufacturerSysNo);
}
if (!string.IsNullOrEmpty(filter.AgentLevel) &&
filter.AgentLevel.Trim() != "")
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "AgentLevel=@AgentLevel";
command.AddInputParameter("@AgentLevel",
DbType.String, filter.AgentLevel);
}
if (filter.C3SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C3SysNo=@C3SysNo";
command.AddInputParameter("@C3SysNo",
DbType.Int32, filter.C3SysNo);
}
else
{
if (filter.C2SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C2SysNo=@C2SysNo";
command.AddInputParameter("@C2SysNo",
DbType.Int32, filter.C2SysNo);
}
else
{
if (filter.C1SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += @"C2SysNo in (SELECT
[Category2Sysno]
FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo] WITH(NOLOCK)
WHERE
[Category1Sysno]=@C1SysNo)";
command.AddInputParameter("@C1SysNo",
DbType.Int32, filter.C1SysNo);
}
}
}
if (!string.IsNullOrEmpty(strWhere))
{
subCond += "WHERE status=0 and " + strWhere;
}
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.SysNo",
QueryConditionOperatorType.In, subCond);
}
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
var enumColList = new EnumColumnList();
enumColList.Add("IsConsign", typeof(VendorConsignFlag));
enumColList.Add("Status", typeof(ValidStatus));
enumColList.Add("VendorStatus", typeof(VendorStatus));
command.ConvertEnumColumn(dt, enumColList);
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataSet CouponUseedReportQuery(CouponUsedReportFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReport.CouponUsedReportQuery");
DataSet result = null;
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "OrderDate DESC"))
{
#region Set dynamic codition for where
if (filter.SODateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"OrderDate",
DbType.DateTime,
"@OrderDateFrom_query",
QueryConditionOperatorType.MoreThanOrEqual,
filter.SODateFrom.Value);
}
if (filter.SODateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"OrderDate",
DbType.DateTime,
"@SODateTo_query",
QueryConditionOperatorType.LessThan,
filter.SODateTo.Value);
}
if (filter.PayTypeSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"PayTypeSysNo",
DbType.Int32,
"@PayTypeSysNo_query",
QueryConditionOperatorType.Equal,
filter.PayTypeSysNo.Value);
}
if (filter.SoSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"SOSysNo",
DbType.Int32,
"@SOSysNo_query",
QueryConditionOperatorType.Equal,
filter.SoSysNo.Value);
}
if (filter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status.Value);
}
if (filter.SOPayStatus.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SOPayStatus",
DbType.Int32,
"@SOPayStatus",
QueryConditionOperatorType.Equal,
filter.SOPayStatus.Value);
}
if (filter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"VendorSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.MerchantSysNo.Value);
}
if (filter.CouponSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CouponSysNo",
DbType.Int32,
"@CouponSysNo",
QueryConditionOperatorType.Equal,
filter.CouponSysNo.Value);
}
#endregion
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(SOStatus));
enumColList.Add("NetPayStatus", typeof(ECCentral.BizEntity.Invoice.NetPayStatus));
enumColList.Add("SOIncomeStatus", typeof(ECCentral.BizEntity.Invoice.SOIncomeStatus));
enumColList.Add("SOPayStatus", typeof(ECCentral.BizEntity.Invoice.SOIncomeStatus));
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataSet();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
if (result.Tables[0] != null && result.Tables[0].Rows.Count > 0)
{
result.Tables[0].Columns.Add("SOIncomeStatusText", typeof(string));
foreach (DataRow dr in result.Tables[0].Rows)
{
//int ippStatus = dr["Status"] != null && dr["Status"] != DBNull.Value ? (int)dr["Status"] : int.MinValue;
//if (ippStatus != int.MinValue)
//{
// int isAutoRMA = dr["HaveAutoRMA"] != null && dr["HaveAutoRMA"] != DBNull.Value ? (int)dr["HaveAutoRMA"] : 0;
// int isCombine = dr["IsCombine"] != null && dr["IsCombine"] != DBNull.Value ? (int)dr["IsCombine"] : 0;
// int isMergeComplete = dr["IsMergeComplete"] != null && dr["IsMergeComplete"] != DBNull.Value ? (int)dr["IsMergeComplete"] : 0;
// SOStatus status = Mapping_SOStatus_IPPToThis(ippStatus, isAutoRMA != 0, isCombine == 1, isMergeComplete == 1);
// dr["Status"] = (int)status;
//}
string soIncomeStatusText = string.Empty;
// Set column value for SOIncomeStatusText
SOIncomeStatus?soIncomeStatus = dr.IsNull("SOPayStatus") ? new SOIncomeStatus?() : (SOIncomeStatus)dr["SOPayStatus"];
if ((int)dr["SOPayStatus"] == -1)
{
soIncomeStatusText = ECCentral.BizEntity.Enum.Resources.ResSOEnum.SOPayStatus__NotPay;
}
//NetPayStatus? netPayStatus = dr.IsNull("SOPayStatus") ? new NetPayStatus?() : (NetPayStatus)dr["SOPayStatus"];
else if ((int)dr["SOPayStatus"] == -999)
{
soIncomeStatusText = ECCentral.BizEntity.Enum.Resources.ResSOEnum.SOPayStatus__Paied;
}
else
{
soIncomeStatusText = EnumHelper.GetDescription(soIncomeStatus.Value);
}
dr["SOIncomeStatusText"] = soIncomeStatusText;
}
}
dataCommand.ConvertEnumColumn(result.Tables[0], enumColList);
}
return(result);
}
public DataSet IncomeCostReportQuery(IncomeCostReportQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReport.IncomeCostReportQuery");
DataSet result = null;
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "OrderDate DESC"))
{
#region Set dynamic codition for where
if (filter.SODateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"OrderDate",
DbType.DateTime,
"@OrderDateFrom_query",
QueryConditionOperatorType.MoreThanOrEqual,
filter.SODateFrom.Value);
}
if (filter.SODateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"OrderDate",
DbType.DateTime,
"@SODateTo_query",
QueryConditionOperatorType.LessThan,
filter.SODateTo.Value);
}
if (filter.PayTypeSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"PayTypeSysNo",
DbType.Int32,
"@PayTypeSysNo_query",
QueryConditionOperatorType.Equal,
filter.PayTypeSysNo.Value);
}
if (!string.IsNullOrWhiteSpace(filter.ChannelID))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ChannelID",
DbType.AnsiStringFixedLength,
"@ChannelID_query",
QueryConditionOperatorType.Equal,
filter.ChannelID);
}
if (filter.SOSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"SOSysNo",
DbType.Int32,
"@SOSysNo_query",
QueryConditionOperatorType.Equal,
filter.SOSysNo.Value);
}
if (filter.SOStatusList != null && filter.SOStatusList.Count > 0)
{
sqlBuilder.ConditionConstructor.AddInCondition <int>(
QueryConditionRelationType.AND,
"SOStatus",
System.Data.DbType.Int32,
filter.SOStatusList);
}
if (filter.VendorSysNoList != null && filter.VendorSysNoList.Count > 0)
{
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"VendorSysNo",
DbType.Int32,
filter.VendorSysNoList);
}
#endregion
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataSet();
dataCommand.ConvertEnumColumn(result.Tables[0], 18, typeof(SOStatus));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
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);
}
}