/// <summary>
/// 返回系统用户列表
/// </summary>
/// <param name="filter">过滤条件集合</param>
/// <returns></returns>
//[Caching("local", new string[] { "filter" }, ExpiryType = ExpirationType.SlidingTime, ExpireTime = "02:00:00")]
public virtual List <UserInfo> GetUserInfoList(UserInfoQueryFilter filter)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetUserInfoList");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, ToPagingInfo(filter.PagingInfo), "[DisplayName] ASC"))
{
//商品ID
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"DepartmentCode",
DbType.Int32,
"@DepartmentCode",
QueryConditionOperatorType.Equal,
filter.DepartmentId
);
//公司编码
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"CompanyCode",
DbType.String,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode
);
if (filter.Status.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status
);
}
cmd.CommandText = sb.BuildQuerySql();
return(cmd.ExecuteEntityList <UserInfo>());
}
}
/// <summary>
/// 查询跳转关键字
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryAdvancedKeywords(AdvancedKeywordsQueryFilter 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("Keywords_QueryAdvancedKeywordsList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Keywords", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.Keywords);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LinkUrl", DbType.String, "@LinkUrl", QueryConditionOperatorType.Equal, filter.LinkUrl);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "BeginDate", DbType.DateTime, "@BeginDate", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDate);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.DateTime, "@EndDate", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Priority", DbType.Int32, "@Priority", QueryConditionOperatorType.Equal, string.IsNullOrEmpty(filter.Priority) ? null : filter.Priority);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(ECCentral.BizEntity.MKT.ADStatus));
enumList.Add("AutoRedirectSwitch", typeof(ECCentral.BizEntity.MKT.NYNStatus));
var dt = cmd.ExecuteDataTable(enumList);
//var dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询产品页面关键字
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductPageKeywords(ProductKeywordsQueryFilter 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.GetDataCommand("ProductKeywords_QueryProductKeywordsList");
cmd.SetParameterValue("@EditDateTo", filter.EditDateTo);
cmd.SetParameterValue("@EditDateFrom", filter.EditDateFrom);
cmd.SetParameterValue("@ProductMode", filter.ProductMode);
cmd.SetParameterValue("@C1SysNo", filter.Category1SysNo);
cmd.SetParameterValue("@C2SysNo", filter.Category2SysNo);
cmd.SetParameterValue("@C3SysNo", filter.Category3SysNo);
cmd.SetParameterValue("@VendorName", filter.VendorName);
cmd.SetParameterValue("@SelectedManufacturerSysNo", filter.SelectedManufacturerSysNo);
cmd.SetParameterValue("@Keywords", filter.Keywords);
cmd.SetParameterValue("@EditUser", filter.EditUser);
cmd.SetParameterValue("@UserSysNo", filter.PMUserSysNo);
cmd.SetParameterValue("@ProductID", filter.ProductID);
cmd.SetParameterValue("@Status", filter.Status);
cmd.SetParameterValue("@PropertyValueSysNo", filter.PropertyValueSysNo);
cmd.SetParameterValue("@PropertySysNo", filter.PropertySysNo);
cmd.SetParameterValue("@ManualInput", filter.InputValue);
cmd.SetParameterValue("@StartNumber", pagingEntity.StartRowIndex);
cmd.SetParameterValue("@EndNumber", pagingEntity.StartRowIndex + pagingEntity.MaximumRows);
cmd.SetParameterValue("@SortField", pagingEntity.SortField);
DataTable dt = new DataTable();
dt = cmd.ExecuteDataTable();
totalCount = (int)cmd.GetParameterValue("@TotalCount");
return(dt);
}
/// <summary>
/// 查询数据并分页
/// </summary>
/// <typeparam name="Q"></typeparam>
/// <typeparam name="T"></typeparam>
/// <param name="command"></param>
/// <param name="filter"></param>
/// <param name="needRealTime"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sortField"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <T> QueryData <Q, T>(CustomDataCommand command, Q filter, string configName,
PagingInfoEntity pagingInfo, out int totalCount) where T : class, new()
{
pagingInfo.SortField = "[SOMaster.SOSysNo]";
if (string.IsNullOrEmpty(pagingInfo.SortField))
{
throw new ApplicationException("You must specified one sort field at least.");
}
var config = ConfigHelper.GetRealTimeConfig(configName);
string xmlFields;
List <string> columns;
BuildColumns(config.ReturnFields, out xmlFields, out columns);
var cmd = DataCommandManager.CreateCustomDataCommandFromSql(loadPagingDataSql, command.DatabaseAliasName);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd, pagingInfo, pagingInfo.SortField))
{
BuilCondition <Q>(filter, config.DataType, config.FilterFields, sqlBuilder);
cmd.CommandText = sqlBuilder.BuildQuerySql();
//把传入的参数添加到组合后的DataCommand中
//command.DbParameterList.ForEach(p =>
//{
// var param = cmd.DbParameterList.FirstOrDefault(k => k.ParameterName.Trim().ToUpper() == p.ParameterName.Trim().ToUpper());
// if (param == null)
// {
// cmd.AddInputParameter(p.ParameterName, p.DbType, p.Value);
// }
//});
cmd.CommandText = cmd.CommandText.Replace("#Columns#", columns.Join(","));
cmd.CommandText = cmd.CommandText.Replace("#XmlFields#", xmlFields.ToString());
cmd.CommandText = cmd.CommandText.Replace("#InputSql#", command.CommandText);
var list = cmd.ExecuteEntityList <T>();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(list);
}
}
/// <summary>
/// Mapping old IPP system method:SendAddPointEmail
/// </summary>
private void SendAddPointEmail(JobContext context)
{
// 对3天前出库的订单发送评论送积分的邮件，
DataCommand command = DataCommandManager.GetDataCommand("AddPointSO");
command.SetParameterValue("SpecialSOType", ((int)CommonEnum.SpecialSOType.AZ).ToString());
command.SetParameterValue("Status", ((int)CommonEnum.SOStatus.OutStock).ToString());
command.SetParameterValue("CompanyCode", CompanyCode);
var ds = command.ExecuteDataSet();
if (!Util.HasMoreRow(ds))
{
context.Message += StringResource.SendAddPoindMailJobNoResultMsg + "\r\n";
return;
}
context.Message += string.Format(StringResource.SendAddPointMailJobWrokingCount,
ds.Tables[0].Rows.Count) + "\r\n";
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
var soSysNo = Convert.ToInt32(dr["SysNo"]);
Util.SendTheAddPointEmail(soSysNo, context);
context.Message += string.Format(StringResource.SendAddPointMailJobMailSendSuccessful, soSysNo)
+ "\r\n";
successed++;
}
catch (Exception ex)
{
Util.WriteTheExcetpionLog(string.Format(StringResource.SendAddPointMailJobExceptionMsg, dr["SysNo"])
+ "：\r\n" + ex.ToString());
context.Message += string.Format(StringResource.SendAddPointMailJobExceptionMsg, dr["SysNo"]) + ex.ToString() + "\r\n";
failed++;
}
}
context.Message += string.Format(StringResource.JobExecuteDone + "\r\n",
successed, failed);
}
/// <summary>
/// 分页查询商品列表Demo
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult <ProductInfo> QueryProductList(ProductListQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "p.SysNo ASC" : queryFilter.SortFields))
{
//Set SQL WHERE Condition:
#region Build Condition:
if (!string.IsNullOrEmpty(queryFilter.SysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo);
}
if (!string.IsNullOrEmpty(queryFilter.StartDateString))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.InDate", DbType.DateTime, "@InDateFrom", QueryConditionOperatorType.MoreThanOrEqual, Convert.ToDateTime(queryFilter.StartDateString));
}
if (!string.IsNullOrEmpty(queryFilter.EndDateString))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.InDate", DbType.DateTime, "@InDateTo", QueryConditionOperatorType.LessThan, Convert.ToDateTime(queryFilter.EndDateString).AddDays(1));
}
if (!string.IsNullOrEmpty(queryFilter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.Status", DbType.StringFixedLength, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
}
#endregion
command.CommandText = sqlBuilder.BuildQuerySql();
List <ProductInfo> resultList = command.ExecuteEntityList <ProductInfo>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <ProductInfo>()
{
PageInfo = new PageInfo()
{
PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
}, ResultList = resultList
});
}
}
public DataTable Query(HotSaleCategoryQueryFilter query, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = query.PageInfo.SortBy;
pagingEntity.MaximumRows = query.PageInfo.PageSize;
pagingEntity.StartRowIndex = query.PageInfo.PageIndex * query.PageInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("WebSite_QueryHotSaleCategory");
var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "h.TransactionNumber desc");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.Status",
DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal,
query.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.Position",
DbType.Int32, "@Position", QueryConditionOperatorType.Equal,
query.Position);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.GroupName",
DbType.String, "@GroupName", QueryConditionOperatorType.Like,
query.GroupName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.PageType",
DbType.Int32, "@PageType", QueryConditionOperatorType.Equal,
query.PageType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal,
query.CompanyCode);
//TODO:添加ChannelID参数
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumConfig = new EnumColumnList();
enumConfig.Add("Status", typeof(ADStatus));
var dt = cmd.ExecuteDataTable(enumConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 优惠券使用记录查询
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public static QueryResult QueryCouponCodeRedeemLog(CouponCodeRedeemLogFilter filter)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCouponCodeRedeemLog");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, filter, string.IsNullOrEmpty(filter.SortFields) ? "Coupon.SysNo ASC" : filter.SortFields))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Coupon.MerchantSysNo", DbType.Int32,
"@MerchantSysNo", QueryConditionOperatorType.Equal, filter.MerchantSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Coupon.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, filter.CouponSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Coupon.CouponName", DbType.String,
"@CouponName", QueryConditionOperatorType.Equal, filter.CouponName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RedeemLog.CouponCode", DbType.String,
"@CouponCode", QueryConditionOperatorType.Equal, filter.CouponCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cs.CustomerID", DbType.String,
"@CustomerID", QueryConditionOperatorType.Equal, filter.CustomerID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.SysNo", DbType.String,
"@SOID", QueryConditionOperatorType.Equal, filter.SOID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RedeemLog.Status", DbType.String,
"@CouponCodeStatus", QueryConditionOperatorType.Equal, filter.CouponCodeStatus);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "RedeemLog.InDate", DbType.DateTime,
"@InDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan
, filter.BeginUseDate.HasValue ? filter.BeginUseDate.Value.Date : filter.BeginUseDate
, filter.EndUseDate.HasValue ? filter.EndUseDate.Value.Date : filter.EndUseDate);
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable resultList = cmd.ExecuteDataTable(new EnumColumnList {
{ "SOStatus", typeof(SOStatus) }, { "RedeemLogStatus", typeof(CouponCodeUsedStatus) }
});
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(new QueryResult(resultList, filter, totalCount));// { PageInfo = new PageInfo() { PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields }, ResultList = resultList };
}
}
/// <summary>
/// NoBizQuery RMA商品库存
/// </summary>
/// <param name="filter">查询条件</param>
/// <param name="totalCount">out 数据数量</param>
/// <param name="totalMisCost">out 未税（增值税）成本</param>
/// <returns>DataTable</returns>
public virtual DataTable QueryRMAProductInventory(RMAInventoryQueryFilter filter, out int totalCount, out decimal totalMisCost)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRMAProductsInventory");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " p.SysNo "))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, RMARequestStatus.Handling);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMA_Request.RecvTime",
DbType.DateTime, "@RecvTime", QueryConditionOperatorType.IsNotNull, DBNull.Value);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.SysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OwnBy",
DbType.Int32, "@OWnBy", QueryConditionOperatorType.Equal, filter.RMAOwnBy);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LocationWarehouse",
DbType.AnsiStringFixedLength, "@LocationWarehouse", QueryConditionOperatorType.Equal, filter.LocationWarehouse);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OwnByWarehouse",
DbType.AnsiStringFixedLength, "@OwnByWarehouse", QueryConditionOperatorType.Equal, filter.OwnByWarehouse);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.CompanyCode",
DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
cmd.AddOutParameter("@TotleMisCost", DbType.String, 20);
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
object temp = cmd.GetParameterValue("@TotleMisCost");
decimal.TryParse(temp != null ? temp.ToString() : string.Empty, out totalMisCost);
return(dt);
}
}
public List <ProductShiftDetailEntity> GetProductShiftDetail(ProductShiftDetailQueryEntity productShiftDetailQueryEntity)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductShiftDetails");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "st.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.StockSysNoA",
DbType.Int32, "@StockSysNoA", QueryConditionOperatorType.Equal, productShiftDetailQueryEntity.StockSysNoA);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.StockSysNoB",
DbType.Int32, "@StockSysNoB", QueryConditionOperatorType.Equal, productShiftDetailQueryEntity.StockSysNoB);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.OutTime",
DbType.DateTime, "@OutTimeEnd", QueryConditionOperatorType.LessThanOrEqual, productShiftDetailQueryEntity.OutTimeEnd);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.OutTime",
DbType.DateTime, "@OutTimeBegin", QueryConditionOperatorType.MoreThanOrEqual, productShiftDetailQueryEntity.OutTimeBegin);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "stItem.UnitCost",
DbType.Decimal, "@UnitCost", QueryConditionOperatorType.NotEqual, 0M);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, productShiftDetailQueryEntity.CompanyCode);
if (productShiftDetailQueryEntity.GoldenTaxNo != "")
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st.GoldenTaxNo",
DbType.String, "@GoldenTaxNo", QueryConditionOperatorType.Equal, productShiftDetailQueryEntity.GoldenTaxNo);
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "st.ShiftType",
QueryConditionOperatorType.In, "0,2");
if (productShiftDetailQueryEntity.StItemSysNos.Count > 0)
{
string strs = "-99999";
foreach (int data in productShiftDetailQueryEntity.StItemSysNos)
{
strs = strs + "," + data.ToString();
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "stItem.SysNo", QueryConditionOperatorType.In, strs);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var result = dataCommand.ExecuteEntityList <ProductShiftDetailEntity>();
return(result);
}
}
public SaleGiftInfo Load(int?sysNo)
{
SaleGiftInfo info = null;
DataCommand cmd = DataCommandManager.GetDataCommand("SaleGift_LoadSaleGift");
cmd.SetParameterValue("@SysNo", sysNo);
DataSet ds = cmd.ExecuteDataSet();
DataTable dtMaster = ds.Tables[0];
if (dtMaster.Rows.Count == 0)
{
return(info);
}
info = DataMapper.GetEntity <SaleGiftInfo>(dtMaster.Rows[0], (row, entity) =>
{
entity.Title = new LanguageContent(row["PromotionName"].ToString().Trim());
entity.Description = new LanguageContent(row["PromotionDesc"] != null ? row["PromotionDesc"].ToString().Trim() : "");
entity.OrderCondition = new PSOrderCondition();
if (row["AmountLimit"] != null && !string.IsNullOrEmpty(row["AmountLimit"].ToString().Trim()))
{
entity.OrderCondition.OrderMinAmount = Math.Round(decimal.Parse(row["AmountLimit"].ToString()), 2);
}
entity.IsGlobalProduct = row["IsGlobal"] != null ? (row["IsGlobal"].ToString().Trim() == "Y" ? true : false) : false;
});
info.ProductCondition = new List <SaleGift_RuleSetting>();
DataTable dt2 = ds.Tables[1];
if (dt2 != null && dt2.Rows.Count > 0)
{
info.ProductCondition = DataMapper.GetEntityList <SaleGift_RuleSetting, List <SaleGift_RuleSetting> >(dt2.Rows);
}
DataTable dt3 = ds.Tables[2];
if (dt3 != null && dt3.Rows.Count > 0)
{
info.GiftItemList = DataMapper.GetEntityList <RelProductAndQty, List <RelProductAndQty> >(dt3.Rows);
}
return(info);
}
private void InsertProductPrice(ProductInfo productInfo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("InsertProductPrice");
cmd.SetParameterValue("@ProductSysNo", productInfo.SysNo);
cmd.SetParameterValue("@BasicPrice", productInfo.ProductPriceInfo.BasicPrice);
cmd.SetParameterValue("@CurrentPrice", productInfo.ProductPriceInfo.CurrentPrice);
cmd.SetParameterValue("@UnitCost", productInfo.ProductPriceInfo.UnitCost);
cmd.SetParameterValue("@Discount", productInfo.ProductPriceInfo.DiscountAmount);
cmd.SetParameterValue("@PointType", productInfo.ProductPriceInfo.PayType);
cmd.SetParameterValue("@MaxPerOrder", productInfo.ProductPriceInfo.MaxCountPerDay);
cmd.SetParameterValue("@IsWholeSale", 0);
cmd.SetParameterValue("@Q1", null);
cmd.SetParameterValue("@P1", null);
cmd.SetParameterValue("@Q2", null);
cmd.SetParameterValue("@P2", null);
cmd.SetParameterValue("@Q3", null);
cmd.SetParameterValue("@P3", null);
cmd.SetParameterValue("@CashRebate", productInfo.ProductPriceInfo.CashRebate);
cmd.SetParameterValue("@Point", productInfo.ProductPriceInfo.Point);
cmd.SetParameterValue("@ClearanceSale", 0);
cmd.SetParameterValue("@CreateTime", DateTime.Now);
cmd.SetParameterValue("@UnitCostWithoutTax", productInfo.ProductPriceInfo.UnitCostWithoutTax);
cmd.SetParameterValue("@IsExistRankPrice", 0);
cmd.SetParameterValue("@PMMemo", null);
cmd.SetParameterValue("@TLMemo", null);
cmd.SetParameterValue("@LastOnSaleTime", null);
cmd.SetParameterValue("@CompanyCode", productInfo.CompanyCode);
cmd.SetParameterValue("@CurrencySysNo", 1);
cmd.SetParameterValue("@LanguageCode", productInfo.LanguageCode);
cmd.SetParameterValue("@StoreCompanyCode", productInfo.CompanyCode);
cmd.SetParameterValue("@ReservedMaxPerOrder", null);
cmd.SetParameterValue("@VirtualPrice", productInfo.ProductPriceInfo.VirtualPrice);
cmd.SetParameterValue("@SupplyMemo", null);
cmd.SetParameterValue("@SupplyPrice", null);
cmd.SetParameterValue("@MinCommission", null);
cmd.SetParameterValue("@IsUseAlipayVipPrice", null);
cmd.SetParameterValue("@AlipayVipPrice", null);
cmd.SetParameterValue("@MinCountPerOrder", 1);
cmd.ExecuteNonQuery();
}
public PriceChangeMaster GetPriceChangeBySysNo(int sysno)
{
DataCommand cmd = DataCommandManager.GetDataCommand("PriceChange_GetByPriceChangeBySysNo");
cmd.SetParameterValue("@SysNo", sysno);
DataSet ds = cmd.ExecuteDataSet();
PriceChangeMaster master = new PriceChangeMaster();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow row = ds.Tables[0].Rows[0];
master = DataMapper.GetEntity <PriceChangeMaster>(row);
master.ItemList = new List <PriceChangeItem>();
master.ItemList = DataMapper.GetEntityList <PriceChangeItem, List <PriceChangeItem> >(ds.Tables[1].Rows);
}
return(master);
}
public DataTable GetProductShowByQuery(ProductShowQueryFilter query, out int totalCount)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductShowList");
cmd.SetParameterValue("@FirstOnlineTimeFrom", query.FirstOnlineTimeFrom);
cmd.SetParameterValue("@FirstOnlineTimeTo", query.FirstOnlineTimeTo);
cmd.SetParameterValue("@EditDateFrom", query.EditDateFrom);
cmd.SetParameterValue("@EditDateTo", query.EditDateTo);
cmd.SetParameterValue("@Status", query.Status);
cmd.SetParameterValue("@C1SysNo", query.Category1SysNo);
cmd.SetParameterValue("@C2SysNo", query.Category2SysNo);
cmd.SetParameterValue("@C3SysNo", query.Category3SysNo);
cmd.SetParameterValue("@SortField", query.PageInfo.SortBy);
cmd.SetParameterValue("@PageSize", query.PageInfo.PageSize);
cmd.SetParameterValue("@PageCurrent", query.PageInfo.PageIndex);
DataTable dt = new DataTable();
dt = cmd.ExecuteDataTable(3, typeof(ProductStatus));
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
//private SettleGatherItemMsg CreateSettleItem(SettleGatherItemMsg entity)
private GatherSettlementItemInfo CreateSettleItem(GatherSettlementItemInfo entity)
{
DataCommand command = null;
command = DataCommandManager.GetDataCommand("CreateVendorSettleGatherItem");
command.SetParameterValue("@OrderNo", entity.SONumber);
command.SetParameterValue("@OrderType", entity.SettleType);
command.SetParameterValue("@SettleSysNo", entity.SettleSysNo);
command.SetParameterValue("@ProductSysNo", entity.ProductSysNo);
command.SetParameterValue("@Qty", entity.Quantity);
command.SetParameterValue("@SalesAmt", entity.OriginalPrice);
command.SetParameterValue("@InUser", GlobalSettings.UserName);
command.SetParameterValue("@InDate", DateTime.Now);
command.SetParameterValue("@EditUser", GlobalSettings.UserName);
command.SetParameterValue("@EditDate", DateTime.Now);
command.SetParameterValue("@ItemType", entity.ProductSysNo.Value <= 0 ? "SHP" : "PRD");
command.SetParameterValue("@CompanyCode", GlobalSettings.CompanyCode);
command.SetParameterValue("@StoreCompanyCode", GlobalSettings.StoreCompanyCode);
System.Convert.ToInt32(command.ExecuteNonQuery());
return(entity);
}
/// <summary>
/// 检查团购冲突
/// </summary>
/// <param name="excludeSysNo"></param>
/// <param name="productSysNos"></param>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public bool CheckConflict(int excludeSysNo, List <int> productSysNos, DateTime beginDate, DateTime endDate)
{
if (productSysNos == null || productSysNos.Count == 0)
{
throw new ArgumentException("productSysNos");
}
string inProductSysNos = productSysNos[0].ToString();
for (int i = 1; i < productSysNos.Count; i++)
{
inProductSysNos += "," + productSysNos[i].ToString();
}
DataCommand command = DataCommandManager.GetDataCommand("CheckProductInGBByDateTime");
command.ReplaceParameterValue("#ProductSysNos#", inProductSysNos);
command.SetParameterValue("@ExcludeSysNo", excludeSysNo);
command.SetParameterValue("@BeginDate", beginDate);
command.SetParameterValue("@EndDate", endDate);
return(command.ExecuteScalar <int>() > 0);
}
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);
}
}
}
/// <summary>
/// 根据SysNO获取PM组信息
/// </summary>
/// <param name="productManagerGroupInfoSysNo"></param>
/// <returns></returns>
public ProductManagerGroupInfo GetProductManagerGroupInfoBySysNo(int productManagerGroupInfoSysNo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetProductManagerGroupInfoBySysNo");
cmd.SetParameterValue("@SysNo", productManagerGroupInfoSysNo);
var sourceEntity = cmd.ExecuteEntity <ProductManagerGroupInfo>();
if (sourceEntity == null)
{
sourceEntity = new ProductManagerGroupInfo {
SysNo = productManagerGroupInfoSysNo
};
}
else
{
var productmanagergroupQueryDA = ObjectFactory <IProductManagerGroupQueryDA> .Instance;
var pms = productmanagergroupQueryDA.QueryAllProductManagerInfoByPMGroupSysNo(productManagerGroupInfoSysNo);
sourceEntity.ProductManagerInfoList = pms;
}
return(sourceEntity);
}
public ConsignAdjustInfo Create(ConsignAdjustInfo request)
{
DataCommand command = DataCommandManager.GetDataCommand("CreateConsignAdjust");
command.SetParameterValue("@VendorSysNo", request.VenderSysNo);
command.SetParameterValue("@PMSysNo", request.PMSysNo);
command.SetParameterValue("@SettleSysNo", request.SettleSysNo);
command.SetParameterValue("@SettleRange", request.SettleRangeDate.Value.ToString("yyyy-MM"));
command.SetParameterValue("@TotalAmt", request.TotalAmt);
command.SetParameterValue("@Status", request.Status);
command.SetParameterValueAsCurrentUserSysNo("@InUser");
request.SysNo = command.ExecuteScalar <int>();
if (request.SysNo.HasValue && request.SysNo.Value > 0)
{
return(request);
}
else
{
return(null);
}
}
public List <GiftCardInfo> GetGiftCardsByCodeList(List <string> codeList)
{
string condition = "";
codeList.ForEach(item => condition += ", " + "'" + item + "'");
if (condition.Length > 0)
{
condition = condition.TrimStart(',', ' ');
}
else
{
condition = "0";
}
DataCommand command = DataCommandManager.GetDataCommand("GetGiftCardsByCodeList");
command.ReplaceParameterValue("#CodeList#", condition);
return(command.ExecuteEntityList <GiftCardInfo>());
}
public CostChangeBasicInfo LoadCostChangeBasicInfo(int ccSysNo)
{
CostChangeBasicInfo returnEntity = new CostChangeBasicInfo();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetCostChangeBySysNo");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, null, "CC.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"CC.SysNo",
DbType.Int32,
"@CostChangeSysNo",
QueryConditionOperatorType.Equal,
ccSysNo);
command.CommandText = builder.BuildQuerySql();
returnEntity = command.ExecuteEntity <CostChangeBasicInfo>();
}
return(returnEntity);
}
public static VendorAgentInfo CreateVendorManufacturerInfo(VendorAgentInfo vendorAgentInfo, int vendorSysNo)
{
DataCommand command = DataCommandManager.GetDataCommand("CreateVendorManufacturer");
command.SetParameterValue("@VendorSysNo", vendorSysNo);
command.SetParameterValue("@C2SysNo", vendorAgentInfo.C2SysNo);
command.SetParameterValue("@AgentLevel", vendorAgentInfo.AgentLevel);
command.SetParameterValue("@Status", vendorAgentInfo.Status);
command.SetParameterValue("@C3SysNo", vendorAgentInfo.C3SysNo.Value);
if (vendorAgentInfo.BrandInfo != null && vendorAgentInfo.BrandInfo.SysNo.HasValue)
{
command.SetParameterValue("@BrandSysNo", vendorAgentInfo.BrandInfo.SysNo.Value);
}
else
{
command.SetParameterValue("@BrandSysNo", DBNull.Value);
}
command.SetParameterValue("@CompanyCode", vendorAgentInfo.CompanyCode);
return(command.ExecuteEntity <VendorAgentInfo>());
}
public static VendorBasicInfo LoadVendorInfo(int vendorSysNo)
{
VendorBasicInfo vendorInfo = null;
DataCommand cmd = DataCommandManager.GetDataCommand("LoadVendorInfo");
cmd.SetParameterValue("@VendorSysNo", vendorSysNo);
DataSet ds = cmd.ExecuteDataSet();
if (ds != null && ds.Tables != null)
{
if (ds.Tables.Count > 0)
{
vendorInfo = DataMapper.GetEntity <VendorBasicInfo>(ds.Tables[0].Rows[0]);
}
if (vendorInfo != null && ds.Tables.Count > 1)
{
vendorInfo.ExtendedInfo = DataMapper.GetEntity <VendorExtendInfo>(ds.Tables[1].Rows[0]);
}
}
return(vendorInfo);
}
/// <summary>
/// 更新类别2的基本指标信息
/// </summary>
/// <param name="categoryBasicInfo"></param>
/// <returns></returns>
public void UpdateCategory2Basic(CategoryBasic categoryBasicInfo)
{
DataCommand cmd = DataCommandManager.GetDataCommand("UpdateCategoryKpi2");
cmd.SetParameterValue("@SysNo", categoryBasicInfo.PropertySysNO);
cmd.SetParameterValue("@AvgDailySalesCycle", categoryBasicInfo.AvgDailySalesCycle);
cmd.SetParameterValue("@IsValuableProduct", categoryBasicInfo.IsValuableProduct);
cmd.SetParameterValue("@CategorySysNo", categoryBasicInfo.Category2SysNo);
cmd.SetParameterValue("@InStockDays", categoryBasicInfo.InStockDays);
categoryBasicInfo.VirtualRate = categoryBasicInfo.VirtualRate / 100;
cmd.SetParameterValue("@VirtualRate", categoryBasicInfo.VirtualRate);
cmd.SetParameterValue("@VirtualCount", categoryBasicInfo.VirtualCount);
cmd.SetParameterValue("@CheapenRisk", categoryBasicInfo.CheapenRiskInfo);
cmd.SetParameterValue("@OOSQty", categoryBasicInfo.OOSQty);
categoryBasicInfo.OOSRate = categoryBasicInfo.OOSRate / 100;
cmd.SetParameterValue("@OOSRate", categoryBasicInfo.OOSRate);
cmd.SetParameterValue("@LogUserName", "IPPSystemAdmin");
cmd.SetParameterValue("@Quota", categoryBasicInfo.Quota);
cmd.SetParameterValue("@MinCommission", categoryBasicInfo.MinCommission);
cmd.ExecuteNonQuery();
}
public void CreateRmaPolicy(RmaPolicyInfo info)
{
DataCommand cmd = DataCommandManager.GetDataCommand("CreateRmaPolicy");
cmd.SetParameterValue("@RMAPolicyName", info.RMAPolicyName);
cmd.SetParameterValue("@Priority", info.Priority);
cmd.SetParameterValue("@Type", info.RmaType);
cmd.SetParameterValue("@Status", info.Status);
cmd.SetParameterValue("@ECDisplayName", info.ECDisplayName);
cmd.SetParameterValue("@ECDisplayDesc", info.ECDisplayDesc);
cmd.SetParameterValue("@ECDisplayMoreDesc", info.ECDisplayMoreDesc);
cmd.SetParameterValue("@ReturnDate", info.ReturnDate);
cmd.SetParameterValue("@ChangeDate", info.ChangeDate);
cmd.SetParameterValue("@IsOnlineRequst", info.IsOnlineRequest);
cmd.SetParameterValue("@InUser", info.User.UserDisplayName);
cmd.SetParameterValue("@CompanyCode", info.CompanyCode);
cmd.SetParameterValue("@StoreCompanyCode", info.CompanyCode);
cmd.SetParameterValue("@LanguageCode", info.LanguageCode);
cmd.ExecuteNonQuery();
info.SysNo = (int)cmd.GetParameterValue("@SysNo");
}
public static QueryResultList <ArticleData> GetItems(QueryRequest <ArticleQueryData> query)
{
var result = new QueryResultList <ArticleData>();
DataCommand cmd = DataCommandManager.GetDataCommand("Article_GetItems");
cmd.SetParameterValue("@ArtStatus", query.Body.ArtStatus);
cmd.SetParameterValue("@Publisher", query.Body.Publisher + "%");
cmd.SetParameterValue("@Publisher", query.Body.EndDate);
cmd.SetParameterValue("@Title", query.Body.Title + "%");
cmd.SetParameterValue("@ArtType", query.Body.ArtType);
cmd.SetParameterValue("@StartDate", query.Body.StartDate);
cmd.SetParameterValue("@EndDate", query.Body.EndDate);
cmd.SetParameterValue("@PageCurrent", query.PageInfo.PageIndex);
cmd.SetParameterValue("@PageSize", query.PageInfo.PageSize);
cmd.SetParameterValue("@SortType", query.PageInfo.SortType);
cmd.SetParameterValue("@SortField", query.PageInfo.SortField);
result.Body = cmd.ExecuteEntityList <ArticleData>();
result.TotalCount = (int)cmd.GetParameterValue("@TotalCount");
return(result);
}
/// <summary>
/// 调整顾客积分
/// </summary>
/// <param name="adujstInfo"></param>
/// <returns></returns>
public static object Adjust(AdjustPointRequest adujstInfo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SendCommentPoints");
cmd.AddInputParameter("@CustomerSysno", DbType.Int32, adujstInfo.CustomerSysNo);
cmd.AddInputParameter("@Point", DbType.Int32, adujstInfo.Point);
cmd.AddInputParameter("@PointType", DbType.Int32, adujstInfo.PointType);
cmd.AddInputParameter("@Source", DbType.String, adujstInfo.Source);
cmd.AddInputParameter("@Memo", DbType.String, adujstInfo.Memo);
cmd.AddInputParameter("@InUser", DbType.String, "JOBUser");
//cmd.SetParameterValue("@InUser",123);
cmd.AddInputParameter("@OperationType", DbType.Int32, adujstInfo.OperationType);
cmd.AddInputParameter("@SoSysNo", DbType.Int32, adujstInfo.SOSysNo);
cmd.AddInputParameter("@ExpireDate", DbType.DateTime, adujstInfo.PointExpiringDate);
cmd.AddOutParameter("@returnCode", DbType.Int32, 0);
cmd.CommandTimeout = 120;
cmd.ExecuteNonQuery();
object obj = cmd.GetParameterValue("@returnCode");
return(obj);
}
/// <summary>
/// 修改三级延保信息
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public CategoryExtendWarranty UpdateCategoryExtendWarranty(CategoryExtendWarranty entity)
{
DataCommand cmd = DataCommandManager.GetDataCommand("UpdateCategoryExtendWarranty");
cmd.SetParameterValue("@BrandSysNo", entity.Brand.SysNo ?? -1);
cmd.SetParameterValue("@C3SysNo", entity.CategoryInfo.SysNo);
cmd.SetParameterValue("@EditUser", entity.EditUser.UserDisplayName);
cmd.SetParameterValue("@ProductCode", entity.ProductCode);
cmd.SetParameterValue("@Status", entity.Status);
cmd.SetParameterValue("@Years", entity.Years);
cmd.SetParameterValue("@MinUnitPrice", entity.MinUnitPrice);
cmd.SetParameterValue("@MaxUnitPrice", entity.MaxUnitPrice);
cmd.SetParameterValue("@UnitPrice", entity.UnitPrice);
cmd.SetParameterValue("@Cost", entity.Cost);
cmd.SetParameterValue("@SysNo", entity.SysNo);
cmd.SetParameterValue("@CompanyCode", entity.CompanyCode);
cmd.SetParameterValue("@IsECSelected", entity.IsECSelected);
cmd.ExecuteNonQuery();
return(entity);
}
/// <summary>
/// 根据系统编号列表取得收款单列表
/// </summary>
/// <param name="soSysNoList">订单系统编号列表</param>
/// <returns></returns>
public List <SOIncomeInfo> GetListBySOSysNoList(List <int> soSysNoList)
{
List <SOIncomeInfo> result = new List <SOIncomeInfo>();
if (soSysNoList == null || soSysNoList.Count == 0)
{
return(result);
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetSOIncomeList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, null, "SysNo"))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format("OrderType=1"));
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format("OrderSysNo IN({0})", soSysNoList.ToListString()));
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteEntityList <SOIncomeInfo>();
}
return(result);
}
/// <summary>
/// 创建损益单
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual AdjustRequestInfo CreateAdjustRequest(AdjustRequestInfo entity)
{
DataCommand command = DataCommandManager.GetDataCommand("Inventory_CreateAdjustRequest");
command.SetParameterValue("@RequestSysNo", entity.SysNo.Value);
command.SetParameterValue("@RequestID", entity.RequestID);
command.SetParameterValue("@StockSysNo", entity.Stock.SysNo);
command.SetParameterValue("@CreateDate", entity.CreateDate);
command.SetParameterValue("@CreateUserSysNo", entity.CreateUser.SysNo);
command.SetParameterValue("@AuditDate", entity.AuditDate);
command.SetParameterValue("@AuditUserSysNo", entity.AuditUser.SysNo);
command.SetParameterValue("@OutStockDate", entity.OutStockDate);
command.SetParameterValue("@OutStockUserSysNo", entity.OutStockUser.SysNo);
command.SetParameterValue("@RequestStatus", (int?)entity.RequestStatus);
command.SetParameterValue("@AdjustProperty", (int?)entity.AdjustProperty);
command.SetParameterValue("@ConsignFlag", (int?)entity.ConsignFlag);
command.SetParameterValue("@Note", entity.Note);
command.SetParameterValue("@CompanyCode", "8601");//[Mark][Alan.X.Luo 硬编码]
command.SetParameterValue("@ProductLineSysno", entity.ProductLineSysno);
return(command.ExecuteEntity <AdjustRequestInfo>());
}
