/// <summary>
/// 獲取EventChinaTrustBagMap數據
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<EventChinaTrustBagMapQuery> GetChinaTrustBagMapList(EventChinaTrustBagMapQuery query, out int totalCount)
{
StringBuilder str = new StringBuilder();
StringBuilder strall = new StringBuilder();
StringBuilder strcounts = new StringBuilder();
try
{
strcounts.AppendFormat("select count(ec.map_id) as totalcounts FROM event_chinatrust_bag_map ec ");
strall.AppendFormat("SELECT ec.map_id,ec.bag_id,ec.product_id,ec.linkurl,ec.product_forbid_banner,ec.linkurl,ec.ad_product_id,ec.product_desc, ec.product_active_banner,ec.map_active,p.product_name,ec.map_sort, ect.event_name,ecb.bag_name FROM event_chinatrust_bag_map ec ");
//str.AppendFormat(" LEFT JOIN vote_event ve ON va.event_id=ve.event_id ");
str.AppendFormat(" LEFT JOIN product p ON ec.product_id=p.product_id ");
str.AppendFormat(" LEFT JOIN event_chinatrust_bag ecb on ecb.bag_id=ec.bag_id ");
str.AppendFormat(" LEFT JOIN event_chinatrust ect on ect.event_id=ecb.event_id ");
//str.AppendFormat(" LEFT JOIN users m ON va.user_id=m.user_id ");
str.AppendFormat(" where 1=1 ");
totalCount = 0;
if (query.bag_id > 0)
{
str.AppendFormat(" and ec.bag_id='{0}' ", query.bag_id);
}
if (query.map_id > 0)//活動編號
{
str.AppendFormat(" and ec.map_id='{0}' ", query.map_id);
}
if (query.search_con ==2)
{
if (query.con != "")
{
str.AppendFormat(" and ecb.bag_name like N'%{0}%' ", query.con);
}
}
else if (query.search_con == 1)
{
if (query.con != "")
{
str.AppendFormat(" and ect.event_name like N'%{0}%' ", query.con);
}
}
totalCount = 0;
if (query.IsPage)
{
strcounts.Append(str.ToString());
System.Data.DataTable _dt = _access.getDataTable(strcounts.ToString() );
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]);
}
str.AppendFormat("order by ec.bag_id DESC,ec.map_sort DESC limit {0},{1} ", query.Start, query.Limit);
}
strall.Append(str.ToString());
return _access.getDataTableForObj<EventChinaTrustBagMapQuery>(strall.ToString() );
}
catch (Exception ex)
{
throw new Exception(" EventChinaTrustBagMapDao-->GetChinaTrustBagMapList-->" + ex.Message + "sql:" + strall.ToString() + strcounts.ToString(), ex);
}
}
public int UpdateStatus(EventChinaTrustBagMapQuery query)
{
try
{
return _chinaTrustDao.UpdateStatus(query);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMapMgr-->UpdateStatus-->" + ex.Message, ex);
}
}
public DataTable GetMapSort(EventChinaTrustBagMapQuery query)
{
try
{
return _chinaTrustDao.GetMapSort(query);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMapMgr-->GetMapSort-->" + ex.Message, ex);
}
}
public int Save(EventChinaTrustBagMapQuery q)
{
try
{
return _chinaTrustDao.Save(q);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMapMgr-->Save-->" + ex.Message, ex);
}
}
public List<EventChinaTrustBagMapQuery> GetChinaTrustBagMapList(EventChinaTrustBagMapQuery query, out int totalCount)
{
try
{
return _chinaTrustDao.GetChinaTrustBagMapList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMapMgr-->GetChinaTrustBagMapList-->" + ex.Message, ex);
}
}
public int Save(EventChinaTrustBagMapQuery q)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("insert into event_chinatrust_bag_map (bag_id,product_id,linkurl,product_forbid_banner,product_active_banner,map_active,map_sort,ad_product_id,product_desc) Value");
sb.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", q.bag_id, q.product_id, q.linkurl, q.product_forbid_banner, q.product_active_banner, q.map_active, q.map_sort,q.ad_product_id,q.product_desc);
return _access.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception(" EventChinaTrustBagMapDao-->Save-->" + ex.Message + "sql:" + sb.ToString(), ex);
}
}
public DataTable GetMapSort(EventChinaTrustBagMapQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.bag_id > 0)
{
sql.AppendFormat("SELECT map_sort from event_chinatrust_bag_map WHERE bag_id='{0}' Order by map_sort DESC;", query.bag_id);
return _access.getDataTable(sql.ToString());
}
else
{
return null;
}
}
catch (Exception ex)
{
throw new Exception(" EventChinaTrustBagMapDao-->GetMapSort-->" + ex.Message + "sql:" + sql.ToString(), ex);
}
}
public int UpdateStatus(EventChinaTrustBagMapQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.map_id > 0)
{
sql.AppendFormat("update event_chinatrust_bag_map set map_active='{0}' ", query.map_active);
sql.AppendFormat(" where map_id='{0}';", query.map_id);
return _access.execCommand(sql.ToString());
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw new Exception(" EventChinaTrustBagMapDao-->UpdateStatus-->" + ex.Message + "sql:" + sql.ToString(), ex);
}
}
public int Update(EventChinaTrustBagMapQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.map_id > 0)
{
sql.AppendFormat("update event_chinatrust_bag_map set bag_id='{0}',product_id='{1}',linkurl='{2}',product_forbid_banner='{3}',product_active_banner='{4}',map_active='{5}',map_sort='{6}', ad_product_id='{7}',product_desc='{8}' ", query.bag_id, query.product_id, query.linkurl, query.product_forbid_banner, query.product_active_banner, query.map_active, query.map_sort,query.ad_product_id, query.product_desc);
sql.AppendFormat(" where map_id='{0}';", query.map_id);
return _access.execCommand(sql.ToString());
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw new Exception(" EventChinaTrustBagMapDao-->Update-->" + ex.Message + "sql:" + sql.ToString(), ex);
}
}
public HttpResponseBase SaveChinaTrustBagMap()
{
string json = string.Empty;
bool pic = true;
DataTable dt = new DataTable();
try
{
_ChinatrustBMMgr = new EventChinaTrustBagMapMgr(mySqlConnectionString);
EventChinaTrustBagMapQuery store = new EventChinaTrustBagMapQuery();
EventChinaTrustBagMapQuery query = new EventChinaTrustBagMapQuery();
if (!string.IsNullOrEmpty(Request.Params["id"]))
{//如果是編輯獲取該id數據
int totalCount = 0;
query.IsPage = false;
query.map_id = int.Parse(Request.Params["id"]);
store = _ChinatrustBMMgr.GetChinaTrustBagMapList(query, out totalCount).FirstOrDefault();
query = store;
}
//product_id
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
query.product_id = Convert.ToUInt32(Request.Params["product_id"]);
}
else
{
query.product_id = 0;
}
if (!string.IsNullOrEmpty(Request.Params["bag_id"]))
{
query.bag_id = Convert.ToInt32(Request.Params["bag_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["linkurl"]))
{
query.linkurl = Request.Params["linkurl"];
}
if (!string.IsNullOrEmpty(Request.Params["product_forbid_banner"]))
{
query.product_forbid_banner = Request.Params["product_forbid_banner"];
}
if (!string.IsNullOrEmpty(Request.Params["product_active_banner"]))
{
query.product_active_banner = Request.Params["product_active_banner"];
}
if (!string.IsNullOrEmpty(Request.Params["ad_product_id"]))
{
string p_id = Request.Params["ad_product_id"];
RegexOptions options = RegexOptions.None;
Regex regex = new Regex(@"[ ]{2,}", options);
p_id = regex.Replace(p_id, @" ");
p_id = p_id.Replace(" ", ",");
for (int i = p_id.Length - 1; i > 0; i--)
{
if (p_id.Substring(p_id.Length - 1, 1) == ",")
{
p_id = p_id.Substring(0, p_id.Length - 1);
}
}
string[] pr_id = p_id.Split(',').Distinct().ToArray(); ;
string pro_id = "";
for (int i = 0; i < pr_id.Length; i++)
{
if (_ChinatrustBMMgr.IsProductId(pr_id[i].ToString()))
{
pro_id += pr_id[i].ToString() + ",";
}
}
if (pro_id.Length > 0)
{
query.ad_product_id = pro_id.Substring(0, pro_id.Length - 1);
}
else
{
query.ad_product_id = null;
}
}
else
{
query.ad_product_id = null;
}
if (!string.IsNullOrEmpty(Request.Params["product_desc"]))
{
query.product_desc = Request.Params["product_desc"];
}
else
{
query.product_desc = null;
}
#region 上傳圖片
try
{
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 + PaperPath;//圖片存儲地址
Random rand = new Random();
FileManagement fileLoad = new FileManagement();
for (int iFile = 0; iFile < Request.Files.Count; iFile++)
{
//int newRand = rand.Next(1000, 9999);
HttpPostedFileBase file = Request.Files[iFile];
string fileName = string.Empty;//當前文件名
string fileExtention = string.Empty;//當前文件的擴展名
bool result = false;
string NewFileName = string.Empty;
string ServerPath = string.Empty;
fileName = Path.GetFileName(file.FileName);
string newRand = hash.Md5Encrypt(fileLoad.NewFileName(fileName), "32");
string ErrorMsg = string.Empty;
if (iFile == 0)
{
if (string.IsNullOrEmpty(file.FileName))
{
query.product_forbid_banner = store.product_forbid_banner;
}
else
{
fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower();
NewFileName = newRand + fileExtention;
//判斷目錄是否存在，不存在則創建
string[] mapPath = new string[1];
mapPath[0] = PaperPath.Substring(1, PaperPath.Length - 1);
string s = localPromoPath.Substring(0, localPromoPath.Length - PaperPath.Length + 1);
CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - PaperPath.Length + 1), mapPath);
// returnName += PaperPath + NewFileName;
fileName = NewFileName;
NewFileName = localPromoPath + NewFileName;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + PaperPath);
string oldFileName = "";
oldFileName = store.product_forbid_banner;
if (!string.IsNullOrEmpty(oldFileName))
{
DeletePicFile(ServerPath + oldFileName);//刪除本地圖片
}
try
{ //上傳
result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)//上傳成功
{
query.product_forbid_banner = fileName;
}
else
{
pic = false;
}
}
catch (Exception)
{
query.product_forbid_banner = store.product_forbid_banner;
query.product_active_banner = store.product_active_banner;
}
}
}
if (iFile == 1)
{
if (string.IsNullOrEmpty(file.FileName))
{
query.product_active_banner = store.product_active_banner;
}
else
{
fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower();
NewFileName = newRand + fileExtention;
//判斷目錄是否存在，不存在則創建
string[] mapPath = new string[1];
mapPath[0] = PaperPath.Substring(1, PaperPath.Length - 1);
string s = localPromoPath.Substring(0, localPromoPath.Length - PaperPath.Length + 1);
CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - PaperPath.Length + 1), mapPath);
// returnName += PaperPath + NewFileName;
fileName = NewFileName;
NewFileName = localPromoPath + NewFileName;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + PaperPath);
string oldFileName = "";
oldFileName = store.product_active_banner;
if (!string.IsNullOrEmpty(oldFileName))
{
DeletePicFile(ServerPath + oldFileName);//刪除本地圖片
}
try
{
//上傳
result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)//上傳成功
{
query.product_active_banner = fileName;
}
else
{
pic = false;
}
}
catch (Exception)
{
query.product_forbid_banner = store.product_forbid_banner;
query.product_active_banner = store.product_active_banner;
}
}
}
}
}
catch (Exception)
{
query.product_forbid_banner = store.product_forbid_banner;
query.product_active_banner = store.product_active_banner;
}
#endregion
if (pic)
{
if (query.map_id > 0)
{//編輯
if (!string.IsNullOrEmpty(Request.Params["map_sort"]))
{
query.map_sort = Convert.ToInt32(Request.Params["map_sort"]);
}
if (_ChinatrustBMMgr.Update(query) > 0)
{
json = "{success:true,msg:'修改成功!'}";
}
else
{
json = "{success:false,msg:'修改失敗!'}";
}
}
else
{//新增
dt = _ChinatrustBMMgr.GetMapSort(query);
if (dt.Rows.Count > 0)
{
query.map_sort = int.Parse(dt.Rows[0]["map_sort"].ToString()) + 1;
}
else
{
query.map_sort = 0;
}
query.map_active = 0;//默認不啟用
if (_ChinatrustBMMgr.Save(query) > 0)
{
json = "{success:true,msg:'新增成功!'}";
}
else
{
json = "{success:false,msg:'新增失敗!'}";
}
}
}
else
{
json = "{success:false,msg:'圖片上傳失敗!'}";
}
}
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,msg:'操作失敗!'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetChinaTrustBagMapList()
{
List<EventChinaTrustBagMapQuery> store = new List<EventChinaTrustBagMapQuery>();
string json = string.Empty;
int totalCount = 0;
try
{
_ChinatrustBMMgr = new EventChinaTrustBagMapMgr(mySqlConnectionString);
EventChinaTrustBagMapQuery query = new EventChinaTrustBagMapQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
if (!string.IsNullOrEmpty(Request.Params["limit"]))
{
query.Limit = Convert.ToInt32(Request.Params["limit"]);
}
//if (!string.IsNullOrEmpty(Request.Params["bag_id"]))
//{
// query.bag_id = Convert.ToInt32(Request.Params["bag_id"]);
//}
if (!string.IsNullOrEmpty(Request.Params["bagid"]))
{
query.bag_id = Convert.ToInt32(Request.Params["bagid"]);
}
if (!string.IsNullOrEmpty(Request.Params["search_con"]))
{
query.search_con = Convert.ToInt32(Request.Params["search_con"]);
}
if (!string.IsNullOrEmpty(Request.Params["con"]))
{
query.con = Request.Params["con"];
}
store = _ChinatrustBMMgr.GetChinaTrustBagMapList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
foreach (var item in store)
{
item.link = link;
if (item.product_forbid_banner != "")
{
item.forbid_banner = imgServerPath + PaperPath + item.product_forbid_banner;
}
else
{
item.forbid_banner = defaultImg;
}
if (item.product_active_banner != "")
{
item.active_banner = imgServerPath + PaperPath + item.product_active_banner;
}
else
{
item.active_banner = defaultImg;
}
}
//listUser是准备转换的对象
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase UpdateStats()
{
List<EventChinaTrustBagMapQuery> store = new List<EventChinaTrustBagMapQuery>();
string json = string.Empty;
try
{
_ChinatrustBMMgr = new EventChinaTrustBagMapMgr(mySqlConnectionString);
EventChinaTrustBagMapQuery query = new EventChinaTrustBagMapQuery();
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
query.map_id = Convert.ToInt32(Request.Params["id"]);
}
if (!string.IsNullOrEmpty(Request.Params["status"]))
{
query.map_active = Convert.ToInt32(Request.Params["status"]);
}
if (_ChinatrustBMMgr.UpdateStatus(query) > 0)
{
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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
