public DataTable GetEmailContentForUpdateVSPO(int sysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetMailContent");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, null, " sv.SysNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sv.SysNo",
DbType.Int32, "@svSysNo", QueryConditionOperatorType.Equal, sysNo);
command.CommandText = sqlBuilder.BuildQuerySql();
return(command.ExecuteDataSet().Tables[0]);
}
}
/// <summary>
/// 统计
/// </summary>
/// <param name="queryCriteria"></param>
/// <returns></returns>
public DataSet QueryCountData(ShiftRequestQueryFilter queryCriteria)
{
PagingInfoEntity pagingEntity = PageInfoToEntity(queryCriteria.PagingInfo);
pagingEntity.SortField = ShiftRequestSortFieldMapping(pagingEntity.SortField);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCountData");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC"))
{
BuildeCondition(queryCriteria, pagingEntity, cmd, sqlBuilder);
return(cmd.ExecuteDataSet());
}
}
public static QueryResult <Product_NoReviewOrderMaster> QueryCustomerNoReviewOrderProducts(int customerSysNo, int pageIndex, int pageSize)
{
QueryResult <Product_NoReviewOrderMaster> result = new QueryResult <Product_NoReviewOrderMaster>();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
StartRowIndex = pageIndex * pageSize,
MaximumRows = pageSize
};
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCustomerNoReviewOrderProducts");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "SoSysno DESC"))
{
command.SetParameterValue("@CustomerSysNo", customerSysNo);
command.CommandText = sqlBuilder.BuildQuerySql();
var ds = command.ExecuteDataSet();
if (ds != null && ds.Tables != null)
{
if (ds.Tables.Count > 0)
{
result.ResultList = DataMapper.GetEntityList <Product_NoReviewOrderMaster, List <Product_NoReviewOrderMaster> >(ds.Tables[0].Rows);
}
if (ds.Tables.Count > 1)
{
List <Product_ReviewSimpleProductInfo> products = DataMapper.GetEntityList <Product_ReviewSimpleProductInfo, List <Product_ReviewSimpleProductInfo> >(ds.Tables[1].Rows);
if (result.ResultList != null && products != null)
{
foreach (var item in result.ResultList)
{
item.NoReviewOrderProducts = products.FindAll(x => x.SOSysNo == item.SOSysNo);
}
}
}
}
result.PageInfo = new PageInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
TotalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"))
};
return(result);
}
}
public DataTable GetEmailContentForCreateVSPO(int soSysNo, int productSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetMailContent");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, null, " sv.SysNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sv.SOSysNo",
DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, soSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sv.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, productSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sv.status",
DbType.Int32, "@status", QueryConditionOperatorType.NotEqual, (int)VirtualPurchaseOrderStatus.Abandon); //-1表示Abandon
command.CommandText = sqlBuilder.BuildQuerySql();
return(command.ExecuteDataSet().Tables[0]);
}
}
public static string GetItemAccessoriesStringByPurchaseOrder(List <int?> productSysNoList, string companyCode)
{
if (productSysNoList.Count == 0)
{
return(string.Empty);
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductAccessoriesByProductSysNo");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, new PagingInfoEntity()
{
SortField = null,
StartRowIndex = 0,
MaximumRows = int.MaxValue
}, " Value"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product_Accessories.Status",
DbType.String, "@Status", QueryConditionOperatorType.Equal, 0);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product_Accessories.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, companyCode);
string condition = " AND (";
foreach (var sysNo in productSysNoList)
{
condition += "Product_Accessories.ProductSysno = " + sysNo.ToString() + " OR ";
}
condition = condition.Substring(0, condition.LastIndexOf("OR"));
condition += ")";
var sysNoList = productSysNoList.ConvertAll <string>(p => p.ToString()).ToArray();
string CustomCondition = "atc.ProductSysNo IN (" + string.Join(",", sysNoList) + ")";
dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("OtherCondition", condition).Replace("#CustomCondition#", CustomCondition);
}
DataTable dt = dataCommand.ExecuteDataSet().Tables[0];
string des = string.Empty;
foreach (DataRow dr in dt.Rows)
{
des += "【" + dr["ProductID"].ToString() + "】 " + dr["Description"].ToString();
}
return(des);
}
public decimal?JDPriceByProductSysNO(int productSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetProduct");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "v_ici.SysNo desc"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v_ici.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, productSysNo);
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0 && dt.Rows[0]["JDPrice"] != null && dt.Rows[0]["JDPrice"].ToString() != "")
{
return(Convert.ToDecimal(dt.Rows[0]["JDPrice"]));
}
else
{
return(null);
}
}
}
public List <SOInfo> GetSOList4Audit(int topCount, string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SO_Get_SOInfo");
StringBuilder sbCondition = new StringBuilder();
//Jin：增加了必须是有成功的支付记录的SO，才能通过自动审核，避免了未支付订单反复拿出来审核
sbCondition.Append(" INNER JOIN ipp3.dbo.Finance_NetPay netpay WITH(NOLOCK) ON m.SysNo=netpay.SOSysNo ");
sbCondition.Append("WHERE m.Status = 0 ");
//Jin Test数据
//sbCondition.Append(" AND m.SysNo in (130006599) ");
//这里获取的时候直接获取订单减少数据提取额，增加速度
sbCondition.Append(" AND m.SOAmt > 0 ");
sbCondition.Append(" AND m.ReceiveAreaSysNo > 0 ");
sbCondition.Append(" AND m.PayTypeSysNo > 0 ");
sbCondition.Append(" AND m.ShipTypeSysNo > 0 ");
sbCondition.Append(" AND (m.HoldMark IS NULL OR m.HoldMark <> 1) ");
sbCondition.Append(" AND c.IsFPCheck IS NOT NULL");
sbCondition.Append(" AND c.[IsCombine] IS NOT NULL");
sbCondition.Append(" AND AuditType IS NULL");
sbCondition.Append(" AND (c.IsBackOrder IS NULL OR c.IsBackOrder <> 1)");
sbCondition.Append(" AND (c.SOType IS NULL OR c.SOType NOT IN (1,4,5,10))");
sbCondition.Append(" AND (c.stockstatus IS NULL OR c.stockstatus <> 1)");
sbCondition.Append(" AND (c.HoldStatus IS NULL OR c.HoldStatus = 0)");
sbCondition.Append(" AND (c.SOType <> 7 OR (c.SOType=7 AND c.SettlementStatus='S'))");
sbCondition.AppendFormat(" AND m.CompanyCode='{0}'", companyCode);
//数据量过大不需要提取Item数据，单独循环提取
command.CommandText = command.CommandText.Replace("#SO_ConditionString#", sbCondition.ToString())
.Replace("#SOItem_ConditionString#", " WHERE 1=2 ")
.Replace("#Top#", " TOP " + topCount.ToString());
DataSet ds = command.ExecuteDataSet();
return(DataSetToSOList(ds, false));
}
public decimal GetExchangeRateByCurrencySysNo(int sysNo, string companyCode)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetCurrencyBySysNo");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "SysNo desc"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, sysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String,
"@CompanyCode", QueryConditionOperatorType.Equal, companyCode);
command.CommandText = builder.BuildQuerySql();
object getResult = command.ExecuteDataSet().Tables[0].Rows[0]["ExchangeRate"].ToString();
if (null != getResult)
{
return(Convert.ToDecimal(getResult.ToString()));
}
else
{
return(0);
}
}
}
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);
}
/// <summary>
/// Table[0]: Result,
/// Table[1]: Statistics
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataSet SalesStatisticsReportQuery(SalesStatisticsReportQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReport.SalesStatisticsReportQuery");
DataSet result = null;
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "B.ProductID DESC, A.PayTypeSysNo"))
{
#region Set dynamic codition for where
if (filter.SOStatusList != null && filter.SOStatusList.Count > 0)
{
sqlBuilder.ConditionConstructor.AddInCondition <int>(
QueryConditionRelationType.AND,
"SOStatus",
System.Data.DbType.Int32,
filter.SOStatusList);
}
if (filter.SODateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.OrderDate",
DbType.DateTime,
"@OrderDateFrom_query",
QueryConditionOperatorType.MoreThanOrEqual,
filter.SODateFrom.Value);
}
if (filter.SODateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.OrderDate",
DbType.DateTime,
"@SODateTo_query",
QueryConditionOperatorType.LessThan,
filter.SODateTo.Value);
}
if (filter.C1SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C1SysNo",
DbType.Int32,
"@C1SysNo_query",
QueryConditionOperatorType.Equal,
filter.C1SysNo);
}
if (filter.C2SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C2SysNo",
DbType.Int32,
"@C2SysNo_query",
QueryConditionOperatorType.Equal,
filter.C2SysNo);
}
if (filter.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C3SysNo",
DbType.Int32,
"@C3SysNo_query",
QueryConditionOperatorType.Equal,
filter.C3SysNo);
}
if (!string.IsNullOrWhiteSpace(filter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.ProductID",
DbType.StringFixedLength,
"@ProductID_query",
QueryConditionOperatorType.Equal,
filter.ProductID);
}
if (!string.IsNullOrWhiteSpace(filter.BrandName))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
string.Format(" (brand.BrandName_Ch = N'{0}' OR brand.BrandName_En = N'{0}') ", filter.BrandName.Replace("'", "''")));
}
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"rpt.VendorSysNo",
DbType.Int32,
filter.VendorSysNoList);
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"rpt.WarehouseNumber",
DbType.StringFixedLength,
filter.WarehouseNumberList);
#endregion
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataSet();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
/// <summary>
/// Table[0]: Result,
/// Table[1]: Statistics
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static SalesStatisticsReport SalesStatisticsReportQuery(SalesStatisticsReportQueryFilter filter)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReportSalesStatisticsReportQuery");
DataSet result = null;
int totalCount = 0;
SalesStatisticsReport adjustInfo = new SalesStatisticsReport();
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, filter, string.IsNullOrEmpty(filter.SortFields) ? "B.ProductID DESC, A.PayTypeSysNo" : filter.SortFields))
{
#region Set dynamic codition for where
if (filter.SOStatusList != null && filter.SOStatusList.Count > 0)
{
sqlBuilder.ConditionConstructor.AddInCondition <int>(
QueryConditionRelationType.AND,
"SOStatus",
System.Data.DbType.Int32,
filter.SOStatusList);
}
if (filter.SODateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.OrderDate",
DbType.DateTime,
"@OrderDateFrom_query",
QueryConditionOperatorType.MoreThanOrEqual,
filter.SODateFrom.Value);
}
if (filter.SODateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.OrderDate",
DbType.DateTime,
"@SODateTo_query",
QueryConditionOperatorType.LessThan,
filter.SODateTo.Value);
}
if (filter.C1SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C1SysNo",
DbType.Int32,
"@C1SysNo_query",
QueryConditionOperatorType.Equal,
filter.C1SysNo);
}
if (filter.C2SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C2SysNo",
DbType.Int32,
"@C2SysNo_query",
QueryConditionOperatorType.Equal,
filter.C2SysNo);
}
if (filter.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C3SysNo",
DbType.Int32,
"@C3SysNo_query",
QueryConditionOperatorType.Equal,
filter.C3SysNo);
}
if (!string.IsNullOrWhiteSpace(filter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.ProductID",
DbType.StringFixedLength,
"@ProductID_query",
QueryConditionOperatorType.Equal,
filter.ProductID);
}
if (!string.IsNullOrWhiteSpace(filter.BrandName))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
string.Format(" (brand.BrandName_Ch = N'{0}' OR brand.BrandName_En = N'{0}') ", filter.BrandName.Replace("'", "''")));
}
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"rpt.VendorSysNo",
DbType.Int32,
filter.VendorSysNoList);
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"rpt.WarehouseNumber",
DbType.StringFixedLength,
filter.WarehouseNumberList);
#endregion
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataSet();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
adjustInfo.SalesStatisticsResult.PageInfo = new PageInfo()
{
PageIndex = filter.PageIndex,
PageSize = filter.PageSize,
TotalCount = totalCount,
SortBy = filter.SortFields
};
adjustInfo.SalesStatisticsResult.ResultList = new List <SalesStatistics>();
if (null != result && result.Tables.Count > 0)
{
if (result.Tables.Count >= 1)
{
DataTable mainInfoDt = result.Tables[0];
if (mainInfoDt.Rows.Count > 0)
{
foreach (DataRow dr in mainInfoDt.Rows)
{
SalesStatistics aa = DataMapper.GetEntity <SalesStatistics>(dr);
adjustInfo.SalesStatisticsResult.ResultList.Add(aa);
}
}
}
if (result.Tables.Count >= 2)
{
DataTable itemsInfoDt = result.Tables[1];
if (itemsInfoDt.Rows.Count > 0)
{
foreach (DataRow dr in itemsInfoDt.Rows)
{
adjustInfo.CostReportStatisticList.Add(DataMapper.GetEntity <IncomeCostReportStatistic>(dr));
}
}
}
}
return(adjustInfo);
}
/// <summary>
/// 获取无效的发票号码
/// </summary>
/// <param name="invoiceList"></param>
/// <param name="entity"></param>
/// <returns></returns>
public virtual List <string> GetInvalidInvoiceNo(List <string> invoiceList, APInvoiceItemInputEntity entity)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvalidInvoiceNo");
List <string> result = new List <string>();
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "API.InvoiceNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "APM.VendorSysNo"
, DbType.Int32
, "@VendorSysNo"
, QueryConditionOperatorType.Equal
, entity.VendorSysNo);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "API.[Status]"
, DbType.String
, "@ItemStatus"
, QueryConditionOperatorType.Equal
, APInvoiceItemStatus.Active);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "API.[Status]"
, DbType.String
, "@ItemNStatus"
, QueryConditionOperatorType.Equal
, APInvoiceItemStatus.Deactive);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "APM.[Status]"
, DbType.Int32
, "@MasterStatus"
, QueryConditionOperatorType.MoreThanOrEqual
, APInvoiceMasterStatus.Origin);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND
, "API.InvoiceNo"
, DbType.String
, invoiceList);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"API.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
entity.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var dataSet = dataCommand.ExecuteDataSet();
if (dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
result.Add(dr["InvoiceNo"].ToString());
}
}
}
return(result);
}
