/// <summary>
/// 获得前几行数据
/// </summary>
public IList <Wuyiju.Model.ProductFrontend> GetListWithAttr(Wuyiju.Model.Product.Query query, int?limit = null)
{
using (var db = new DataContext())
{
var records = GetDao(db).GetList(query, limit);
var attrDao = unity.GetInstance <IProductAttrDAL>(db);
var lst = new List <ProductFrontend>();
foreach (var record in records)
{
var product = new ProductFrontend(record);
var attrDict = new Dictionary <int, string>();
product.Attrs = attrDao.GetList(new ProductAttr.Query {
Product_Id = product.Id
});
lst.Add(product);
}
return(lst);
}
}
public int GetCount(Wuyiju.Model.Product.Query query)
{
StringBuilder sql = new StringBuilder(@"SELECT count(*) FROM ec_product t
INNER JOIN ec_category n on t.category_id = n.id
where 1 = 1 ");
DynamicParameters param = new DynamicParameters();
sql.AndEquals("t.category_id", "cat_id")
.AndEquals("t.status", "status")
.AndEquals("t.type", "type")
.AndEquals("t.pay_status", "pay_status")
.AndEquals("t.hot", "hot")
.AndEquals("t.best", "best")
.AndEquals("t.new", "new")
.AndEquals("t.trademark_type", "trademark_type")
.AndEquals("t.mall_type", "malltype")
.AndEquals("t.seller_id", "seller_id")
.AndBetween("t.price", "StartPrice", "EndPrice")
.AndLike("t.name", "keyword");
sql.AndDateBetween("t.add_time", "startdate", "enddate");
if (query != null)
{
param.AddDynamicParams(query);
}
return(db.ExecuteScalar <int>(sql.ToString(), param));
}
public int GetCount(Wuyiju.Model.Product.Query query)
{
using (var db = new DataContext())
{
return(this.GetDao(db).GetCount(query));
}
}
/// <summary>
/// 获得前几行数据
/// </summary>
public IList <Wuyiju.Model.Product> GetList(Wuyiju.Model.Product.Query query, int?limit = null)
{
using (var db = new DataContext())
{
return(this.GetDao(db).GetList(query, limit));
}
}
/// <summary>
/// 构造属性筛选条件
/// </summary>
/// <param name="query"></param>
/// <param name="param"></param>
/// <returns></returns>
private string AttributeQuery(Wuyiju.Model.Product.Query Filter, ref DynamicParameters param)
{
var sql = new StringBuilder();
if (Filter != null && Filter.Attrs != null && Filter.Attrs.Count > 0)
{
sql.Append(" and t.id in (select a.product_id from ec_product_attr a where ");
int i = 0;
foreach (var attr in Filter.Attrs)
{
if (attr.Key > 0)
{
if (attr.Key == 101)
{
sql.AppendFormat(" {0} (a.attr_id = @attr_key{1} ", i > 0 ? "and" : "", i);
param.Add(string.Format("attr_key{0}", i), attr.Key);
if (!string.IsNullOrWhiteSpace(attr.Value))
{
sql.AppendFormat(" and a.attr_value like CONCAT('%',@attr_val{0},'%') ", i);
param.Add(string.Format("attr_val{0}", i), attr.Value);
}
sql.Append(" ) ");
}
else
{
sql.AppendFormat(" {0} (a.attr_id = @attr_key{1} ", i > 0 ? "and" : "", i);
param.Add(string.Format("attr_key{0}", i), attr.Key);
if (!string.IsNullOrWhiteSpace(attr.Value))
{
sql.AppendFormat(" and a.attr_value = @attr_val{0} ", i);
param.Add(string.Format("attr_val{0}", i), attr.Value);
}
sql.Append(" ) ");
}
}
i++;
}
sql.Append(" ) ");
}
return(sql.ToString());
}
public int TodayNewCount()
{
var today = DateTime.Now;
var query = new Wuyiju.Model.Product.Query
{
StartDate = new DateTime(today.Year, today.Month, today.Day, 0, 0, 0),
EndDate = new DateTime(today.Year, today.Month, today.Day, 23, 59, 59)
};
using (var db = new DataContext())
{
return(this.GetDao(db).GetCount(query));
}
}
/// <summary>
/// 获得前几行数据
/// </summary>
public IList <Wuyiju.Model.Product> GetList(Wuyiju.Model.Product.Query query, int?limit = null)
{
StringBuilder sql = new StringBuilder(@"SELECT t.id,t.brief,t.admin_id,t.name,t.subname,t.sn,t.category_id,t.market_price,t.price,t.member_price,t.promote_price,t.intro,t.integration,t.integration_buy,t.promote,t.promote_start,t.promote_end,t.recommend,t.click,t.stock,t.pay_status,t.warn_nums,t.sales,t.status,t.hot,t.new,t.best,t.sort,t.keywords,t.seo_title,t.seo_keys,t.seo_desc,t.url,t.address_id,t.buyer_id,t.add_time,t.start_time,t.log,t.del_time,t.filename,t.seller_id,t.user_return,t.type,t.praise_rate,t.collection_popularity,t.seller_credit,t.annual_turnover,t.protection_deposit,t.tech_fee,t.whether_goods,t.buyer_protection,t.virtual_proportion,t.old_customer_number,t.area,t.mall_type,t.trademark_type,t.trademark_no,t.tax_qualification,t.score,t.smallarea,t.company_level,t.guanlian_id,t.weiscore,t.reason,n.name category_name,attr101.attr_value as categories,attr290.attr_value as trademark FROM ec_product t
INNER JOIN ec_category n on t.category_id = n.id
LEFT JOIN (select * from ec_product_attr where attr_id = 101) attr101 on attr101.product_id = t.id
LEFT JOIN (select * from ec_product_attr where attr_id = 290) attr290 on attr290.product_id = t.id
where 1 = 1 ");
DynamicParameters param = new DynamicParameters();
sql.AndEquals("t.category_id", "cat_id")
.AndEquals("t.status", "status")
.AndEquals("t.type", "type")
.AndEquals("t.pay_status", "pay_status")
.AndEquals("t.hot", "hot")
.AndEquals("t.best", "best")
.AndEquals("t.new", "new")
.AndEquals("t.trademark_type", "trademark_type")
.AndEquals("t.mall_type", "malltype")
.AndEquals("t.seller_id", "seller_id")
.AndEquals("t.sales", "sales")
.AndEquals("t.recommend", "recommend")
.AndBetween("t.price", "StartPrice", "EndPrice")
.AndLike("t.name", "keyword");
if (query != null)
{
param.AddDynamicParams(query);
sql.Append(AttributeQuery(query, ref param));
}
// sql.Append(" order by t.add_time desc , t.sort desc ");
if (limit != null)
{
sql.Append(" limit @rows ");
}
if (limit != null)
{
param.Add("rows", limit);
}
return(db.GetList <Wuyiju.Model.Product>(sql, param));
}
/// <summary>
/// 获得数据列表
/// </summary>
public IList <Wuyiju.Model.Product> GetList(Wuyiju.Model.Product.Query query)
{
StringBuilder sql = new StringBuilder(@"SELECT t.id,t.brief,t.admin_id,t.name,t.subname,t.sn,t.category_id,t.market_price,t.price,t.member_price,t.promote_price,t.intro,t.integration,t.integration_buy,t.promote,t.promote_start,t.promote_end,t.recommend,t.click,t.stock,t.pay_status,t.warn_nums,t.sales,t.status,t.hot,t.new,t.best,t.sort,t.keywords,t.seo_title,t.seo_keys,t.seo_desc,t.url,t.address_id,t.buyer_id,t.add_time,t.start_time,t.log,t.del_time,t.filename,t.seller_id,t.user_return,t.type,t.praise_rate,t.collection_popularity,t.seller_credit,t.annual_turnover,t.protection_deposit,t.tech_fee,t.whether_goods,t.buyer_protection,t.virtual_proportion,t.old_customer_number,t.area,t.mall_type,t.trademark_type,t.trademark_no,t.tax_qualification,t.score,t.smallarea,t.company_level,t.guanlian_id,t.weiscore,t.reason,n.name category_name FROM ec_product t
INNER JOIN ec_category n INNER JOIN ec_admin m on t.category_id = n.id and t.guanlian_id=m.id and t.status!=-2
where 1 = 1 ");
DynamicParameters param = new DynamicParameters();
sql.AndEquals("t.category_id", "cat_id")
.AndEquals("t.status", "status")
.AndEquals("t.type", "type")
.AndEquals("t.pay_status", "pay_status")
.AndEquals("t.hot", "hot")
.AndEquals("t.best", "best")
.AndEquals("m.parent_id", "parent_id")
.AndEquals("t.trademark_type", "trademark_type")
.AndEquals("t.mall_type", "malltype")
.AndEquals("t.seller_id", "seller_id")
.AndEquals("t.smallarea", "smallarea")
.AndEquals("t.area", "area")
.AndEquals("t.sales", "sales")
.AndEquals("t.guanlian_id", "guanlian_id")
.AndEquals("t.company_level", "company_level")
.AndEquals("t.subname", "subname")
.AndEquals("t.admin_id", "admin_id")
.AndEquals("t.recommend", "recommend")
.AndBetween("t.price", "StartPrice", "EndPrice")
.AndBetween("t.start_time", "starttime1", "starttime2")
.AndLike("t.name", "keyword");
if (query != null)
{
param.AddDynamicParams(query);
sql.Append(AttributeQuery(query, ref param));
}
return(db.GetList <Wuyiju.Model.Product>(sql, param));
}
