public List<PromotionBannerQuery> GetPromotionBannerList(PromotionBannerQuery query, out int totalCount)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@" ");
if (query.brand_name != string.Empty)
{
VendorBrandQuery vb_query = new VendorBrandQuery();
vb_query.Brand_Name = query.brand_name;
string sql_getID = _vendorBrandDao.GetBrand_idByBrand_name(vb_query);
DataTable dt = _accessMySql.getDataTable(sql_getID);
string id = string.Empty;
if (dt.Rows.Count > 0 && dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
id += dt.Rows[i][0].ToString() + ",";
}
id = id.Substring(0, id.LastIndexOf(','));
strSql.AppendFormat(" AND pbr.brand_id in({0})", id);
}
else
{
strSql.AppendFormat(" AND pbr.brand_id in({0})", -1);
}
}
return _promotionBannerDao.GetPromotionBannerList(query, strSql.ToString(), out totalCount);
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->GetPromotionBannerList-->" + ex.Message, ex);
}
}
public bool UpdateImageInfo(PromotionBannerQuery query, out uint brand_id)
{
brand_id = 0;
ArrayList arr = new ArrayList();
string[] bids = null;
try
{
if (query.brandIDS != string.Empty)
{
bids = query.brandIDS.Split(',');
}
if (query.multi == 0)//不允許一個品牌多個促銷圖的話要檢查，否則不檢查
{
if (bids != null)
{
query.date_start = query.pb_startdate;
query.date_end = query.pb_enddate;
AllowShowOrNot(query, bids, out brand_id);
if (brand_id != 0)
{
return false;//品牌编号在該促銷圖片顯示期間已有其他促銷圖片 且已啟用
}
}
}
PromotionBannerRelationQuery pbr_query = new PromotionBannerRelationQuery();
pbr_query.pb_id = query.pb_id;
arr.Add(_promotionBannerRelationDao.DeleteBrand(pbr_query));
if (bids != null)
{
for (int i = 0; i < bids.Length; i++)
{
if (bids[i] != string.Empty)
{
pbr_query.brand_id = Convert.ToUInt32(bids[i]);
arr.Add(_promotionBannerRelationDao.AddBrand(pbr_query));
}
}
}
arr.Add(_promotionBannerDao.UpdateImageInfo(query));
return _mysqlDao.ExcuteSqls(arr);
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->UpdateImageInfo-->" + ex.Message, ex);
}
}
public int AllowMultiOrNot(PromotionBannerQuery query, out uint id)
{
int result = 0;
id = 0;
try
{
DataTable Using_Image = _promotionBannerDao.ShowUsingImage();
if (Using_Image==null||Using_Image.Rows.Count==0)
{
return 0;
}
StringBuilder bids = new StringBuilder();
string pb_ids = string.Empty;
for (int a = 0; a < Using_Image.Rows.Count; a++)
{
if (!string.IsNullOrEmpty(Using_Image.Rows[a]["pb_startdate"].ToString()))
{
query.date_start = Convert.ToDateTime(Using_Image.Rows[a]["pb_startdate"].ToString());
}
if (!string.IsNullOrEmpty(Using_Image.Rows[a]["pb_enddate"].ToString()))
{
query.date_end = Convert.ToDateTime(Using_Image.Rows[a]["pb_enddate"].ToString());
}
for (int b = Using_Image.Rows.Count - 1; b > a; b--)
{
DateTime pb_startdate = Convert.ToDateTime(Using_Image.Rows[b]["pb_startdate"].ToString());
DateTime pb_enddate = Convert.ToDateTime(Using_Image.Rows[b]["pb_enddate"].ToString());
if (pb_startdate <= query.date_start && pb_enddate >= query.date_end ||
pb_startdate <= query.date_start && pb_enddate >= query.date_start ||
pb_startdate >= query.date_start && pb_enddate <= query.date_end ||
pb_startdate >= query.date_start && pb_startdate <= query.date_end && pb_enddate >= query.date_end)
{
if (pb_ids == string.Empty)
{
pb_ids = Using_Image.Rows[b]["pb_id"].ToString();
}
else
{
pb_ids += "," + Convert.ToInt32(Using_Image.Rows[b]["pb_id"].ToString());
}
}
}
}
if (pb_ids != string.Empty)
{
List<PromotionBannerRelationQuery> pbr_store = _promotionBannerRelationDao.GetBrandIds(pb_ids, string.Empty);
if (pbr_store.Count > 0)
{
for (int i = 0; i < pbr_store.Count; i++)
{
List<PromotionBannerRelationQuery> pbr_store_two = _promotionBannerRelationDao.GetBrandIds(pb_ids, pbr_store[i].brand_id.ToString());
bool condition = false;
if (!string.IsNullOrEmpty(pbr_store[i].brand_id.ToString()))
{
condition = pbr_store_two.Count > 0 ? true : false;
}
else
{
condition = pbr_store_two.Count > 1 ? true : false;
}
if (condition)
{
query.singleBrand_id = pbr_store[i].brand_id;
List<PromotionBannerQuery> model = _promotionBannerDao.GetModelById(pbr_store[i].pb_id);
if (model != null)
{
query.date_start = model[0].pb_startdate;
query.date_end = model[0].pb_enddate;
query.pb_id = pbr_store[i].pb_id;
DataTable repet_dt = _promotionBannerDao.GetUsingImageInTimeArea(query);
if (repet_dt.Rows.Count > 0)
{
id = pbr_store[i].brand_id;
return result = 1; //品牌编号在該促銷圖片顯示期間已有其他促銷圖片 且已啟用
}
}
}
}
}
}
return result;
}
catch (Exception ex)
{
throw new Exception("PromotionBannerRelationMgr-->AllowMultiOrNot-->" + ex.Message, ex);
}
}
public string GetBrandName(PromotionBannerQuery query)
{
try
{
VendorBrandQuery vb_query = new VendorBrandQuery();
vb_query.Brand_Id = (uint)query.singleBrand_id;
List<VendorBrand> exits = _vendorBrandDao.GetVendorBrand(vb_query);//查詢是否有這個品牌編號
uint brand_id = 0;
string brand_name = string.Empty;
if (exits.Count != 0)
{
if (query.multi == 0)
{
string[] bids = query.singleBrand_id.ToString().Split(',');
AllowShowOrNot(query, bids, out brand_id);
if (brand_id != 0)
{
return "-2";//品牌编号在該促銷圖片顯示期間已有其他促銷圖片 且已啟用
}
}
List<VendorBrand> vb_store = _vendorBrandDao.GetBrandListByIds(string.Empty, query.singleBrand_id);
if (vb_store != null)
{
brand_name = vb_store[0].Brand_Name;
}
return brand_name;
}
else
{
return "-1";
}
}
catch (Exception ex)
{
throw new Exception("PromotionBannerRelationMgr-->GetBrandName-->" + ex.Message, ex);
}
}
public void AllowShowOrNot(PromotionBannerQuery query, string[] bids, out uint brand_id)
{
brand_id = 0;
try
{
string pb_ids = string.Empty;
DataTable dt = _promotionBannerDao.GetUsingImageInTimeArea(query);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
pb_ids += dt.Rows[i]["pb_id"] + ",";
}
pb_ids = pb_ids.Substring(0, pb_ids.LastIndexOf(","));
List<PromotionBannerRelationQuery> pbr_store = _promotionBannerRelationDao.GetBrandIds(pb_ids, string.Empty);
for (int a = 0; a < bids.Length; a++)
{
if (bids[a] != string.Empty)
{
query.singleBrand_id = Convert.ToUInt32(bids[a]);
}
for (int b = 0; b < pbr_store.Count; b++)
{
if (query.singleBrand_id == pbr_store[b].brand_id)
{
brand_id = (uint)query.singleBrand_id;
return;
//品牌编号在該促銷圖片顯示期間已有其他促銷圖片 且已啟用
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->AllowShowOrNot-->" + 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 HttpResponseBase IsModifiable()
{
string json = string.Empty;
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
PromotionBannerQuery query = new PromotionBannerQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["pb_id"]))
{
query.pb_id = Convert.ToInt32(Request.Params["pb_id"]);
}
bool i = _promotionBannerMgr.IsModifiable(query);
if (i)
{
json = "{success:true}";
}
else
{
json = "{success:false,msg:\"-1\"}";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public HttpResponseBase AddorEdit()
{
string json = string.Empty;
string errorInfo = string.Empty;
uint brand_id = 0;
bool result = false;
try
{
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
int i = 0;
PromotionBannerQuery query = new PromotionBannerQuery();
PromotionBannerQuery oldquery = new PromotionBannerQuery();
if (!string.IsNullOrEmpty(Request.Params["pb_id"]))
{
oldquery.pb_id = Convert.ToInt32(Request.Params["pb_id"]);
List<PromotionBannerQuery> oldModel = _promotionBannerMgr.GetModelById(oldquery.pb_id);
if (oldModel != null)
{
query.pb_image = oldModel[0].pb_image;
}
}
#region 上傳圖片
string path = Server.MapPath(xmlPath);
SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path);
SiteConfig extention_config = _siteConfigMgr.GetConfigByName("PIC_Extention_Format");
SiteConfig minValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MinValue");
SiteConfig maxValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MaxValue");
SiteConfig admin_userName = _siteConfigMgr.GetConfigByName("ADMIN_USERNAME");
SiteConfig admin_passwd = _siteConfigMgr.GetConfigByName("ADMIN_PASSWD");
//擴展名、最小值、最大值
string extention = extention_config.Value == "" ? extention_config.DefaultValue : extention_config.Value;
string minValue = minValue_config.Value == "" ? minValue_config.DefaultValue : minValue_config.Value;
string maxValue = maxValue_config.Value == "" ? maxValue_config.DefaultValue : maxValue_config.Value;
string localPromoPath = imgLocalPath + brandPath;//圖片存儲地址
FileManagement fileLoad = new FileManagement();
for (int j = 0; j < Request.Files.Count; j++)
{
string fileName = string.Empty;//當前文件名
HttpPostedFileBase file = Request.Files[j];
fileName = Path.GetFileName(file.FileName);
if (string.IsNullOrEmpty(fileName))
{
continue;
}
fileLoad = new FileManagement();
string oldFileName = string.Empty; //舊文件名
string fileExtention = string.Empty;//當前文件的擴展名
string NewFileName = string.Empty;
string ServerPath = string.Empty;
string newRand = string.Empty;
string ErrorMsg = string.Empty;
newRand = hash.Md5Encrypt(fileLoad.NewFileName(fileName) + DateTime.Now.ToString(), "32");
fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower();
NewFileName = newRand + fileExtention;
string folder1 = NewFileName.Substring(0, 2) + "/"; //圖片名前兩碼
string folder2 = NewFileName.Substring(2, 2) + "/"; //圖片名第三四碼
FTP f_cf = new FTP();
localPromoPath = imgLocalPath + brandPath + folder1 + folder2; //圖片存儲地址
string s = localPromoPath.Substring(0, localPromoPath.Length - (brandPath + folder1 + folder2).Length + 1);
f_cf.MakeMultiDirectory(s, (brandPath + folder1 + folder2).Substring(1, (brandPath + folder1 + folder2).Length - 2).Split('/'), ftpuser, ftppwd);
ServerPath = Server.MapPath(imgLocalServerPath + brandPath + folder1 + folder2);
fileName = NewFileName;
NewFileName = localPromoPath + NewFileName;//絕對路徑
Resource.CoreMessage = new CoreResource("Product");//尋找product.resx中的資源文件
//上傳
result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)//上傳成功
{
query.pb_image = fileName;
}
else
{
errorInfo += ErrorMsg;
}
}
#endregion
if (!string.IsNullOrEmpty(Request.Params["image_link"]))
{
query.pb_image_link = Request.Params["image_link"];
}
if (!string.IsNullOrEmpty(Request.Params["begin_time"]))
{
query.pb_startdate = Convert.ToDateTime(Request.Params["begin_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.pb_enddate = Convert.ToDateTime(Request.Params["end_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["vb_ids"]))
{
query.brandIDS = Request.Params["vb_ids"].Substring(0, Request.Params["vb_ids"].LastIndexOf(','));
}
if (!string.IsNullOrEmpty(Request.Params["multi"]))
{
query.multi = Convert.ToInt32(Request.Params["multi"]);
}
if (!string.IsNullOrEmpty(Request.Params["pb_id"]))
{
//編輯
query.pb_id = Convert.ToInt32(Request.Params["pb_id"]);
query.pb_muser = Convert.ToInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id);
result = _promotionBannerMgr.UpdateImageInfo(query, out brand_id);
if (result && string.IsNullOrEmpty(errorInfo))
{
json = "{success:true}";
}
else if (result == false && brand_id != 0)
{
json = "{success:false,msg:\"" + brand_id + "\"}";//brand_id重複
}
else if (result && !string.IsNullOrEmpty(errorInfo))
{
json = "{success:true,msg:\"數據保存成功<br/>但圖片保存失敗 <br/>" + errorInfo + "\"}";
}
else
{
json = "{success:false}";
}
}
else
{
//新增
query.pb_kuser = Convert.ToInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id);
query.pb_muser = Convert.ToInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id);
result = _promotionBannerMgr.AddImageInfo(query, out brand_id);
if (result && string.IsNullOrEmpty(errorInfo))
{
json = "{success:true}";
}
else if (result == false && brand_id != 0)
{
json = "{success:false,msg:\"" + brand_id + "\"}";//brand_id重複
}
else if (result && !string.IsNullOrEmpty(errorInfo))
{
json = "{success:true,msg:\"數據保存成功<br/>但圖片保存失敗 <br/>" + errorInfo + "\"}";
}
else
{
json = "{success:false}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public HttpResponseBase GetPromotionBannerList()
{
string json = string.Empty;
int totalCount = 0;
PromotionBannerQuery query = new PromotionBannerQuery();
List<PromotionBannerQuery> store = new List<PromotionBannerQuery>();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["dateCon"]))
{
query.dateCon = Convert.ToInt32(Request.Params["dateCon"]);
}
if (!string.IsNullOrEmpty(Request.Params["date_start"]))
{
query.date_start =Convert.ToDateTime( Request.Params["date_start"]);
query.date_start = Convert.ToDateTime(query.date_start.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["date_end"]))
{
query.date_end =Convert.ToDateTime( Request.Params["date_end"]);
query.date_end = Convert.ToDateTime(query.date_end.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["activeStatus"]))
{
query.pb_status = Convert.ToInt32(Request.Params["activeStatus"]);
}
if (!string.IsNullOrEmpty(Request.Params["showStatus"]))
{
query.showStatus = Convert.ToInt32(Request.Params["showStatus"]);
}
if (!string.IsNullOrEmpty(Request.Params["brand_name"]))
{
query.brand_name = Request.Params["brand_name"].Trim(); ;
}
if (!string.IsNullOrEmpty(Request.Params["brand_id"]))
{
query.singleBrand_id = Convert.ToUInt32(Request.Params["brand_id"]);
}
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
store = _promotionBannerMgr.GetPromotionBannerList(query, out totalCount);
foreach (var item in store)
{
if (!string.IsNullOrEmpty(item.pb_image))
{
string folder5 = item.pb_image.Substring(0, 2) + "/"; //圖片名前兩碼
string folder6 = item.pb_image.Substring(2, 2) + "/"; //圖片名第三四碼
item.pb_image = imgServerPath + brandPath + folder5 + folder6 + item.pb_image;
}
else
{
item.pb_image = defaultImg;
}
if (!string.IsNullOrEmpty(item.pb_image_link))
{
item.pb_image_link = item.pb_image_link.Replace("''", "'");
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public int UpdateStatus(PromotionBannerQuery query, out uint brand_id)
{
try
{
brand_id = 0;
bool canModify = IsModifiable(query);
if (canModify)
{
if (query.multi == 0)//不允許一個品牌多個促銷圖的話要檢查，否則不檢查
{
if (query.pb_status == 1)
{
List<PromotionBannerQuery> model = _promotionBannerDao.GetModelById(query.pb_id);
DataTable dt = new DataTable();
query.date_start = model[0].pb_startdate;
query.date_end = model[0].pb_enddate;
string brandIDS = string.Empty;
for (int i = 0; i < model.Count; i++)
{
brandIDS += model[i].singleBrand_id + ",";
}
brandIDS = brandIDS.Substring(0, brandIDS.LastIndexOf(','));
if (model != null && brandIDS != string.Empty)
{
string[] bids = brandIDS.Split(',');
AllowShowOrNot(query, bids, out brand_id);
if (brand_id != 0)
{
return -2;
}
}
}
}
return _promotionBannerDao.UpdateStatus(query);
}
else
{
return -1;//圖片已過期
}
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->UpdateStatus-->" + ex.Message, ex);
}
}
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 string DeleteImage(PromotionBannerQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@"DELETE FROM promotion_banner WHERE pb_id={0};", query.pb_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromotionBannerDao-->DeleteImage-->" + 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 HttpResponseBase AllowMultiOrNot()
{
string json = string.Empty;
PromotionBannerQuery query = new PromotionBannerQuery();
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
try
{
uint id = 0;
if (!string.IsNullOrEmpty(Request.Params["change"]))
{
if (Request.Params["change"] == "true")
{
query.changeMode = 1;
}
}
int result = _promotionBannerMgr.AllowMultiOrNot(query, out id);
if (id == 0)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
if (query.changeMode == 0)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
json = "{success:false,msg:\"" + result + "\",id:\"" + id + "\"}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public bool IsModifiable(PromotionBannerQuery query)
{
try
{
DateTime endTime = _promotionBannerDao.GetEndTime(query);
if (DateTime.Now < endTime)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->CanModify-->" + ex.Message, ex);
}
}
public JsonResult UpdateStatus()
{
string json = string.Empty;
uint brand_id;
PromotionBannerQuery query = new PromotionBannerQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
query.pb_id = Convert.ToInt32(Request.Params["id"]);
}
if (!string.IsNullOrEmpty(Request.Params["status"]))
{
query.pb_status = Convert.ToInt32(Request.Params["status"]);
}
if (!string.IsNullOrEmpty(Request.Params["multi"]))
{
query.multi = Convert.ToInt32(Request.Params["multi"]);
}
query.pb_muser = Convert.ToInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id);
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
int i = _promotionBannerMgr.UpdateStatus(query,out brand_id);
if (i > 0)
{
return Json(new { success = "true", error = "" });
}
else if (i == -1)
{
return Json(new { success = "false", error = "-1" }); //促銷圖片已過期，不可修改
}
else if (i == -2)
{
return Json(new { success = "false", error = "-2", id = brand_id }); //品牌編號已存在促銷圖，不可重複添加
}
else
{
return Json(new { success = "false", error = "0" });
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
return Json(new { success = "false", msg = "" });
}
}
public bool DeleteImage(PromotionBannerQuery query)
{
try
{
ArrayList arr = new ArrayList();
PromotionBannerRelationQuery pbr_query = new PromotionBannerRelationQuery();
pbr_query.pb_id = query.pb_id;
arr.Add(_promotionBannerRelationDao.DeleteBrand(pbr_query));
arr.Add(_promotionBannerDao.DeleteImage(query));
return _mysqlDao.ExcuteSqls(arr);
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->DeleteImage-->" + ex.Message, ex);
}
}
public HttpResponseBase DeleteImage()
{
string json = string.Empty;
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
PromotionBannerQuery query = new PromotionBannerQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["ids"]))
{
foreach (string item in Request.Params["ids"].Split('|'))
{
if (!string.IsNullOrEmpty(item))
{
query.pb_id = Convert.ToInt32(item);
bool result = _promotionBannerMgr.DeleteImage(query);
if (result)
{
continue;
}
else
{
json = "{success:false}";
break;
}
}
}
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public List<VendorBrand> GetBrandList(PromotionBannerQuery query)
{
try
{
List<PromotionBannerQuery> model = _promotionBannerDao.GetModelById(query.pb_id);
string brandIDS = string.Empty;
for (int i = 0; i < model.Count; i++)
{
brandIDS += model[i].singleBrand_id + ",";
}
brandIDS = brandIDS.Substring(0, brandIDS.LastIndexOf(','));
if (model != null && brandIDS != string.Empty)
{
uint id = 0;
List<VendorBrand> vb_store = _vendorBrandDao.GetBrandListByIds(brandIDS, id);
return vb_store;
}
else
{
return null;
}
}
catch (Exception ex)
{
throw new Exception("PromotionBannerMgr-->GetBrandList-->" + ex.Message, ex);
}
}
public HttpResponseBase GetBrandName()
{
string json = string.Empty;
_promotionBannerMgr = new PromotionBannerMgr(mySqlConnectionString);
PromotionBannerQuery query = new PromotionBannerQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["pb_id"]))
{
query.pb_id = Convert.ToInt32(Request.Params["pb_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["brand_id"]))
{
query.singleBrand_id = Convert.ToUInt32(Request.Params["brand_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
query.date_start = Convert.ToDateTime(Request.Params["startdate"]);
}
if (!string.IsNullOrEmpty(Request.Params["enddate"]))
{
query.date_end = Convert.ToDateTime(Request.Params["enddate"]);
}
if (!string.IsNullOrEmpty(Request.Params["multi"]))
{
query.multi = Convert.ToInt32(Request.Params["multi"]);
}
if (query.singleBrand_id == 0)
{
json = "{success:false,msg:\"-1\"}";
}
else
{
string name = _promotionBannerMgr.GetBrandName(query);
if (name == "-1")
{
json = "{success:false,msg:\"-1\"}";
}
else if (name == "-2")
{
json = "{success:false,msg:\"-2\"}";
}
else
{
json = "{success:true,msg:\""+ name +" \"}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
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);
}
}