public int DeleteItemsInfo()
{
string strWhere = "";
strWhere += " AND TimeEnd <" + SPublic.GetTimeByDateTime(DateTime.Now);
return(dal.Delete(strWhere, ConnStr));
}
public List <MItemsInfo> GetModelListByTitleANDTimeC(string date, string title, string catid)
{
string strWhere = "";
if (date.Trim() != "")
{
strWhere += " AND (a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date)) + " OR a.TimeEnd=0 )";
strWhere += " AND a.CreateTime >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(-7));
}
if (title.Trim() != "")
{
strWhere += " AND (";
string[] tarr = title.Split(',');
for (int i = 0; i < tarr.Length; i++)
{
if (i > 0)
{
strWhere += " OR ";
}
strWhere += " a.Title LIKE '%" + tarr[i] + "%' ";
}
strWhere += ") ";
}
if (catid.Trim() != "")
{
strWhere += " AND a.CatID IN (" + catid + ")";
}
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByOverdue()
{
string strWhere = "";
strWhere += " AND a.TimeEnd <" + SPublic.GetTimeByDateTime(DateTime.Now);
strWhere += " AND a.CouponMoney >0";
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
/// <summary>
/// 创建时间不超过7天和结束时间不是当前时间的天猫商品
/// </summary>
public List <MItemsInfo> GetModelListByCreateANDEndTime(string date)
{
string strWhere = "";
if (date.Trim() != "")
{
strWhere += " AND a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date));
strWhere += " AND a.CreateTime >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(-7));
}
//strWhere += " AND a.IsTmall = 1 ";
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByTime(string endtime, string creattime)
{
string strWhere = "";
if (endtime.Trim() != "")
{
strWhere += " AND a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(endtime));
}
if (creattime.Trim() != "")
{
strWhere += " AND a.CreateTime >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(creattime));
}
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
/// <summary>
/// 根据优惠时间和佣金比率获取数据
/// </summary>
public List <MItemsInfo> GetModelListByOther(string date, float CommissionRate)
{
string strWhere = "";
if (date != "")
{
strWhere += " AND a.TimeEnd>=" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date));
}
if (CommissionRate > 0)
{
strWhere += " AND a.CommissionRate>=" + CommissionRate;
}
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByNick(string date, string Nick)
{
string strWhere = "";
if (date.Trim() != "")
{
strWhere += " AND (a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(15)) + " OR a.TimeEnd=0) ";
strWhere += " AND a.Commission!=0 ";
}
if (Nick.Trim() != "")
{
strWhere += " AND a.Nick like '%" + Nick + "%'";
}
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByTitleANDCatStr(string date, string title, string catid)
{
string strWhere = "";
if (date.Trim() != "")
{
strWhere += " AND (a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(15)) + " OR a.TimeEnd=0) ";
strWhere += " AND a.Commission!=0 ";
}
if (title.Trim() != "")
{
string[] tarr = title.Split(',');
for (int i = 0; i < tarr.Length; i++)
{
if (i == 0)
{
strWhere += " AND ((";
}
else
{
strWhere += " OR ((";
}
char[] TitleStr = tarr[i].ToCharArray();
for (int a = 0; a <= TitleStr.Length - 1; a++)
{
if (a < TitleStr.Length - 1)
{
strWhere += " a.Title LIKE '%" + TitleStr[a] + "%' AND ";
}
if (a == TitleStr.Length - 1)
{
strWhere += " a.Title LIKE '%" + TitleStr[a] + "%' ";
}
}
strWhere += " )OR a.Title LIKE '%" + tarr[i] + "%' ) ";
}
strWhere += " AND a.Title not LIKE '%2017%' AND a.SalesCount>=10 ";
}
if (catid.Trim() != "")
{
strWhere += " AND a.CatID IN (" + catid + ")";
}
strWhere += " AND a.IsEnable = 1 ";
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByTitleANDTime(string date, string title, bool TitleDescribe)
{
string strWhere = "";
if (date.Trim() != "")
{
//strWhere += " AND (a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date)) + " OR a.TimeEnd=0 )";
strWhere += " AND (a.TimeEnd >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(15)) + " OR a.TimeEnd=0) ";
strWhere += " AND a.Commission!=0 ";
//strWhere += " AND (a.CreateTime >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(-7)) + " OR a.UpdateTime >" + SPublic.GetTimeByDateTime(Convert.ToDateTime(date).AddDays(-7))+") ";
}
if (title.Trim() != "")
{
//strWhere += " AND (";
string[] tarr = title.Split(',');
for (int i = 0; i < tarr.Length; i++)
{
//if (i > 0)
// strWhere += " OR ";
//strWhere += " a.Title LIKE '%" + tarr[i] + "%' AND a.Title not LIKE '%2017%'";
strWhere += " AND ((";
char[] TitleStr = tarr[i].ToCharArray();
for (int a = 0; a <= TitleStr.Length - 1; a++)
{
if (a < TitleStr.Length - 1)
{
strWhere += " a.Title LIKE '%" + TitleStr[a] + "%' AND ";
}
if (a == TitleStr.Length - 1)
{
strWhere += " a.Title LIKE '%" + TitleStr[a] + "%' ";
}
}
strWhere += " )OR a.Title LIKE '%" + tarr[i] + "%' AND a.Title not LIKE '%2017%' ) AND a.SalesCount>=10 ";
}
//strWhere += ") ";
}
if (TitleDescribe)
{
strWhere += " AND a.TitleDescribe!='' ";
}
strWhere += " AND a.IsEnable = 1 ";
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelRptList(decimal tkRateStart, decimal tkRateEnd, decimal persentStart, decimal persentEnd, decimal priceStart, decimal priceEnd, int saleStart, int saleEnd, int IsTmall, string catName)
{
string strWhere = "";
strWhere += " AND a.TimeEnd > " + SPublic.GetTimeByDateTime(DateTime.Now);
strWhere += " AND a.CouponMoney >0";
if (IsTmall > -1)
{
strWhere += " AND a.IsTmall =" + IsTmall;
}
if (catName != null)
{
if (catName == "全部")
{
}
else
{
strWhere += " AND b.CatName ='" + catName + "'";
}
}
if (tkRateStart >= 0 && tkRateEnd > 0)
{
strWhere += " AND a.Commission BETWEEN " + tkRateStart + " AND " + tkRateEnd;
}
if (persentStart >= 0 && persentEnd > 0)
{
strWhere += " AND a.CouponMoney BETWEEN " + persentStart + " AND " + persentEnd;
}
if (priceStart >= 0 && priceEnd > 0)
{
strWhere += " AND a.PriceNow BETWEEN " + priceStart + " AND " + priceEnd;
}
if (saleStart >= 0 && saleEnd > 0)
{
strWhere += " AND a.SalesCount BETWEEN " + saleStart + " AND " + saleEnd;
}
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByTime(long TimeStart1, long TimeStart2, long TimeEnd1, long TimeEnd2, long CreateTime1, long CreateTime2)
{
string strWhere = "";
strWhere += " AND a.TimeEnd > " + SPublic.GetTimeByDateTime(DateTime.Now);
strWhere += " AND a.CouponMoney >0";
if (TimeStart1 >= 0 && TimeStart2 > 0)
{
strWhere += " AND a.TimeStart BETWEEN " + TimeStart1 + " AND " + TimeStart2;
}
if (TimeEnd1 >= 0 && TimeEnd2 > 0)
{
strWhere += " AND a.TimeEnd BETWEEN " + TimeEnd1 + " AND " + TimeEnd2;
}
if (CreateTime1 >= 0 && CreateTime2 > 0)
{
strWhere += " AND a.CreateTime BETWEEN " + CreateTime1 + " AND " + CreateTime2;
}
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
public List <MItemsInfo> GetModelListByTitleANDTimeC(decimal tkRateStart, decimal tkRateEnd, string title, string catidstr, int tss)
{
string strWhere = "";
if (tss == 1)
{
strWhere += " AND a.TimeEnd >" + SPublic.GetTimeByDateTime(DateTime.Now.AddDays(15));
}
else
{
strWhere += " AND (a.TimeEnd >" + SPublic.GetTimeByDateTime(DateTime.Now.AddDays(15)) + " OR a.TimeEnd=0) ";
}
if (tkRateStart >= 0 && tkRateEnd > 0)
{
strWhere += " AND a.CommissionRate BETWEEN " + tkRateStart + " AND " + tkRateEnd;
}
if (title.Trim() != "")
{
strWhere += " AND (";
string[] tarr = title.Split(',');
for (int i = 0; i < tarr.Length; i++)
{
if (i > 0)
{
strWhere += " OR ";
}
strWhere += " a.Title LIKE '%" + tarr[i] + "%' ";
}
strWhere += ") ";
}
if (catidstr.Trim() != "")
{
strWhere += " AND a.CatID IN (" + catidstr + ")";
}
strWhere += " AND a.IsEnable = 1 ";
return(dal.GetModelList(0, 0, strWhere, "", ConnStr));
}
/// <summary>
/// 参数语句
/// </summary>
private SqlCommand GetCommand(MItemsInfo model)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(new SqlParameter(ID, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(NumIID, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(Title, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(KeyWordStr, SqlDbType.VarChar, -1));
cmd.Parameters.Add(new SqlParameter(TitleSub, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(TitleDescribe, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(IsPush, SqlDbType.TinyInt));
cmd.Parameters.Add(new SqlParameter(PushTime, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(CatID, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(Navigation, SqlDbType.TinyInt));
cmd.Parameters.Add(new SqlParameter(ImgUrl, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(ImgSmall, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(PriceNow, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(IsTmall, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(SalesCount, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(IsGood, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(CreateTime, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(UpdateTime, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(IsEnable, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(ActivityID, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(TimeStart, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(TimeEnd, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(ClickUrl, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(UrlShort, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(TotalCount, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(RemainCount, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(CommissionRate, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(Commission, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(CouponInfo, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(CouponMoney, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(TimeUpdate, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(CouponType, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(UseCount, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(Nick, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(SellerID, SqlDbType.BigInt));
//外表
cmd.Parameters.Add(new SqlParameter(Price, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(PID, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(TimeStartPID, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(TimeEndPID, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(ClickUrlPID, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(TotalCountPID, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(RemainCountPID, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(CommissionRatePID, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(CommissionPID, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(CouponInfoPID, SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter(CouponMoneyPID, SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter(TimeUpdatePID, SqlDbType.BigInt));
cmd.Parameters.Add(new SqlParameter(CouponTypePID, SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter(CatName, SqlDbType.VarChar));
cmd.Parameters[ID].Value = model.ID;
cmd.Parameters[NumIID].Value = model.NumIID;
cmd.Parameters[Title].Value = model.Title;
cmd.Parameters[KeyWordStr].Value = model.KeyWordStr;
cmd.Parameters[TitleSub].Value = model.TitleSub;
cmd.Parameters[TitleDescribe].Value = model.TitleDescribe;
cmd.Parameters[IsPush].Value = model.IsPush;
cmd.Parameters[PushTime].Value = model.PushTime == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.PushTime);
cmd.Parameters[CatID].Value = model.CatID;
cmd.Parameters[Navigation].Value = model.Navigation;
cmd.Parameters[ImgUrl].Value = model.ImgUrl;
cmd.Parameters[ImgSmall].Value = model.ImgSmall;
cmd.Parameters[PriceNow].Value = model.PriceNow;
cmd.Parameters[IsTmall].Value = model.IsTmall;
cmd.Parameters[SalesCount].Value = model.SalesCount;
cmd.Parameters[IsGood].Value = model.IsGood;
cmd.Parameters[CreateTime].Value = model.CreateTime == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.CreateTime);
cmd.Parameters[UpdateTime].Value = model.UpdateTime == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.UpdateTime);
cmd.Parameters[IsEnable].Value = model.IsEnable;
cmd.Parameters[ActivityID].Value = model.ActivityID;
cmd.Parameters[TimeStart].Value = model.TimeStart == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.TimeStart);
cmd.Parameters[TimeEnd].Value = model.TimeEnd == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.TimeEnd);
cmd.Parameters[ClickUrl].Value = model.ClickUrl;
cmd.Parameters[UrlShort].Value = model.UrlShort;
cmd.Parameters[TotalCount].Value = model.TotalCount;
cmd.Parameters[RemainCount].Value = model.RemainCount;
cmd.Parameters[CommissionRate].Value = model.CommissionRate;
cmd.Parameters[Commission].Value = model.Commission;
cmd.Parameters[CouponInfo].Value = model.CouponInfo;
cmd.Parameters[CouponMoney].Value = model.CouponMoney;
cmd.Parameters[TimeUpdate].Value = model.TimeUpdate == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.TimeUpdate);
cmd.Parameters[CouponType].Value = model.CouponType;
cmd.Parameters[UseCount].Value = model.UseCount;
cmd.Parameters[Nick].Value = model.Nick;
cmd.Parameters[SellerID].Value = model.SellerID;
//外表
cmd.Parameters[Price].Value = model.Price;
cmd.Parameters[PID].Value = model.PID;
cmd.Parameters[TimeStartPID].Value = model.TimeStartPID == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.TimeStartPID);
cmd.Parameters[TimeEndPID].Value = model.TimeEndPID == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.TimeEndPID);
cmd.Parameters[ClickUrlPID].Value = model.ClickUrlPID;
cmd.Parameters[TotalCountPID].Value = model.TotalCountPID;
cmd.Parameters[RemainCountPID].Value = model.RemainCountPID;
cmd.Parameters[CommissionRatePID].Value = model.CommissionRatePID;
cmd.Parameters[CommissionPID].Value = model.CommissionPID;
cmd.Parameters[CouponInfoPID].Value = model.CouponInfoPID;
cmd.Parameters[CouponMoneyPID].Value = model.CouponMoneyPID;
cmd.Parameters[TimeUpdatePID].Value = model.TimeUpdatePID == DateTime.Parse("2000-01-01") ? 0 : SPublic.GetTimeByDateTime(model.TimeUpdatePID);
cmd.Parameters[CouponTypePID].Value = model.CouponTypePID;
cmd.Parameters[CatName].Value = model.CatName;
return(cmd);
}
