public DataTable GetUsingImageInTimeArea(PromotionBannerQuery query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat(@"SELECT DISTINCT pb.pb_id,pb_status,pb_startdate,pb_enddate from promotion_banner pb INNER JOIN promotion_banner_relation pbr on pb.pb_id=pbr.pb_id WHERE pb_status=1 ");
if (query.pb_id != 0)
{
sql.AppendFormat(" AND pbr.pb_id!={0}", query.pb_id);
}
if (query.singleBrand_id != 0)
{
sql.AppendFormat(" AND pbr.brand_id={0}", query.singleBrand_id);
}
if (CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_start)) < CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(DateTime.Now)))
{
sql.AppendFormat(" AND((pb_enddate>='{0}' AND pb_enddate <= '{1}')", CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(query.date_end));
sql.AppendFormat(" OR( pb_startdate>='{0}' AND pb_startdate <= '{1}' AND pb_enddate>='{0}' AND pb_enddate <= '{1}')", CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(query.date_end));
sql.AppendFormat(" OR( pb_startdate>='{0}' AND pb_startdate <= '{1}' AND pb_enddate >= '{1}')", CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(query.date_end));
sql.AppendFormat(" OR( pb_startdate<='{0}' AND pb_enddate >= '{1}'))", CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(query.date_end));
}
else
{
sql.AppendFormat(" AND((pb_startdate <= '{0}' AND pb_enddate>='{0}' AND pb_enddate <= '{1}' )", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
sql.AppendFormat(" OR( pb_startdate>='{0}' AND pb_startdate <= '{1}' AND pb_enddate>='{0}' AND pb_enddate <= '{1}')", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
sql.AppendFormat(" OR( pb_startdate>='{0}' AND pb_startdate <= '{1}' AND pb_enddate >= '{1}')", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
sql.AppendFormat(" OR( pb_startdate<='{0}' AND pb_enddate >= '{1}'))", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
}
sql.AppendFormat(" ORDER BY pb.pb_id");
return _accessMySql.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->GetUsingImage-->" + sql.ToString() + ex.Message, ex);
}
}
public List<PromotionBannerQuery> GetPromotionBannerList(PromotionBannerQuery query, string strSql, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
totalCount = 0;
DataTable dt = new DataTable();
string now = CommonFunction.DateTimeToString(DateTime.Now);
try
{
sql.AppendFormat(@"SELECT DISTINCT pb.pb_id,pb_image,pb_image_link,pb_startdate,pb_enddate,pb_status,pb_kdate,(SELECT user_username FROM manage_user WHERE manage_user.user_id=pb.pb_kuser ) as createusername,pb_mdate,(SELECT user_username FROM manage_user WHERE manage_user.user_id=pb.pb_muser ) as updateusername FROM promotion_banner pb LEFT JOIN promotion_banner_relation pbr ON pb.pb_id=pbr.pb_id WHERE 1=1 ");
if (query.dateCon != 0)
{
if (query.date_start != DateTime.MinValue)
{
if (query.date_end != DateTime.MinValue)
{
switch (query.dateCon)
{
case 1:
sql.AppendFormat(" AND pb_startdate BETWEEN '{0}' AND '{1}'", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
break;
case 2:
sql.AppendFormat(" AND pb_enddate BETWEEN '{0}' AND '{1}'", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
break;
case 3:
sql.AppendFormat(" AND pb_kdate BETWEEN '{0}' AND '{1}'", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
break;
case 4:
sql.AppendFormat(" AND pb_mdate BETWEEN '{0}' AND '{1}'", CommonFunction.DateTimeToString(query.date_start), CommonFunction.DateTimeToString(query.date_end));
break;
default:
break;
}
}
}
}
switch (query.pb_status)
{
case 0:
sql.AppendFormat(" AND pb_status={0}", query.pb_status);
break;
case 1:
sql.AppendFormat(" AND pb_status={0}", query.pb_status);
break;
default:
sql.AppendFormat(" ");
break;
}
if (query.singleBrand_id != 0)
{
sql.AppendFormat(" AND pbr.brand_id = '{0}' ", query.singleBrand_id);
}
if (query.brandIDS != string.Empty)
{
sql.AppendFormat(" AND pbr.brand_id in '{0}' ", query.brandIDS);
}
if (query.brand_name != string.Empty)
{
sql.AppendFormat(strSql);
}
if (query.showStatus != 0)
{
switch (query.showStatus)
{
case 1://1是未過期
sql.AppendFormat(" AND (pb_enddate>='{0}')", now);
break;
case 2://2是已過期
sql.AppendFormat(" AND (pb_enddate<'{0}')", now);
break;
default:
sql.AppendFormat(" ");
break;
}
}
sql.AppendFormat(" ORDER BY pb_id DESC");
if (query.IsPage)
{
dt = _accessMySql.getDataTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
totalCount = dt.Rows.Count;
}
sql.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
return _accessMySql.getDataTableForObj<PromotionBannerQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->GetPromotionBannerList-->" + sql.ToString() + ex.Message, ex);
}
}
public DateTime GetEndTime(PromotionBannerQuery query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat(@"SELECT pb_enddate from promotion_banner WHERE pb_id={0}", query.pb_id);
PromotionBannerQuery model = _accessMySql.getSinggleObj<PromotionBannerQuery>(sql.ToString());
if (model != null)
{
return model.pb_enddate;
}
else
{
return DateTime.MinValue;
}
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->GetEndTime-->" + sql.ToString() + ex.Message, ex);
}
}
public int AddImageInfo(PromotionBannerQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
string now = CommonFunction.DateTimeToString(DateTime.Now);
try
{
sql.AppendFormat(@"INSERT into promotion_banner(pb_image,pb_image_link,pb_startdate,pb_enddate,pb_status,pb_kdate,pb_kuser,pb_mdate,pb_muser) VALUES('{0}','{1}','{2}','{3}',1,'{4}',{5},'{4}',{6})", query.pb_image, query.pb_image_link, CommonFunction.DateTimeToString(query.pb_startdate), CommonFunction.DateTimeToString(query.pb_enddate), now, query.pb_kuser, query.pb_muser);
return _accessMySql.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->AddImageInfo-->" + sql.ToString() + ex.Message, ex);
}
}
public string UpdateImageInfo(PromotionBannerQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
string now = CommonFunction.DateTimeToString(DateTime.Now);
try
{
sql.AppendFormat(@"UPDATE promotion_banner SET pb_image='{0}',pb_image_link='{1}',pb_startdate='{2}',pb_enddate='{3}',pb_mdate='{4}',pb_muser={5} WHERE pb_id={6};", query.pb_image, query.pb_image_link, CommonFunction.DateTimeToString(query.pb_startdate), CommonFunction.DateTimeToString(query.pb_enddate), now, query.pb_muser, query.pb_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->UpdateImageInfo-->" + sql.ToString() + ex.Message, ex);
}
}
public int UpdateStatus(PromotionBannerQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
string now = CommonFunction.DateTimeToString(DateTime.Now);
try
{
sql.AppendFormat(@"UPDATE promotion_banner SET pb_status={0},pb_mdate='{1}',pb_muser={2} WHERE pb_id={3}", query.pb_status, now, query.pb_muser, query.pb_id);
return _accessMySql.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->UpdateStatus-->" + sql.ToString() + ex.Message, ex);
}
}