public int EventChinaTrustBagStatus(EventChinaTrustBagQuery query)
{
try
{
return _eventChinaTrustBag.EventChinaTrustBagStatus(query);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMgr-->EventChinaTrustBagStatus-->" + ex.Message);
}
}
public List<EventChinaTrustBag> GetBag(EventChinaTrustBagQuery q)
{
try
{
return _eventChinaTrustBag.GetBag(q);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMgr-->GetBag-->" + ex.Message);
}
}
public EventChinaTrustBagQuery GetSinggleData(EventChinaTrustBagQuery query)
{
try
{
return _eventChinaTrustBag.GetSinggleData(query);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMgr-->GetSinggleData-->" + ex.Message);
}
}
public List<EventChinaTrustBagQuery> EventChinaTrustBagList(EventChinaTrustBagQuery query, out int totalCount)
{
try
{
return _eventChinaTrustBag.EventChinaTrustBagList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagMgr-->EventChinaTrustBagList-->"+ex.Message);
}
}
public List<EventChinaTrustBagQuery> EventChinaTrustBagList(EventChinaTrustBagQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
sql.Append("select ecb.bag_id,ecb.bag_name,ecb.bag_desc, ecb.bag_banner, ecb.bag_start_time,ecb.bag_end_time, ecb.bag_active, ecb.bag_create_user, ecb.bag_update_user, ecb.bag_create_time,ecb.bag_update_time,ecb.bag_show_start_time, ecb.bag_show_end_time, ecb.event_id, ecb.product_number, mu1.user_username 'create_user' ,mu2.user_username 'update_user'");
sqlFrom.Append("from event_chinatrust_bag ecb ");
sqlFrom.Append(" LEFT JOIN manage_user mu1 on mu1.user_id=ecb.bag_create_user ");
sqlFrom.Append(" LEFT JOIN manage_user mu2 on mu2.user_id=ecb.bag_update_user ");
sqlWhere.Append(" where 1=1 ");
sqlcount.Append("select count(ecb.bag_id) as totalCount ");
if (!string.IsNullOrEmpty(query.event_id))
{
sqlWhere.AppendFormat("and ecb.event_id='{0}' ", query.event_id);
}
if (query.bag_active != -1)
{
sqlWhere.AppendFormat(" and ecb.bag_active = '{0}' ", query.bag_active);
}
if (!string.IsNullOrEmpty(query.bag_name))
{
sqlWhere.AppendFormat(" and ecb.bag_name like N'%{0}%' ", query.bag_name);
}
if (query.date !=0)
{
if (!string.IsNullOrEmpty(query.start_time))
{
if (query.end_time != "")
{
switch (query.date)
{
case 1:
sqlWhere.AppendFormat(" and ecb.bag_start_time between'{0}' and '{1}' ", CommonFunction.DateTimeToString(Convert.ToDateTime(query.start_time)),CommonFunction.DateTimeToString(Convert.ToDateTime(query.end_time)));
break;
case 2:
sqlWhere.AppendFormat(" and ecb.bag_end_time between'{0}' and '{1}' ", CommonFunction.DateTimeToString(Convert.ToDateTime(query.start_time)), CommonFunction.DateTimeToString(Convert.ToDateTime(query.end_time)));
break;
case 3:
sqlWhere.AppendFormat(" and ecb.bag_show_start_time between'{0}' and '{1}' ", CommonFunction.DateTimeToString(Convert.ToDateTime(query.start_time)), CommonFunction.DateTimeToString(Convert.ToDateTime(query.end_time)));
break;
case 4:
sqlWhere.AppendFormat(" and ecb.bag_show_end_time between'{0}' and '{1}' ", CommonFunction.DateTimeToString(Convert.ToDateTime(query.start_time)), CommonFunction.DateTimeToString(Convert.ToDateTime(query.end_time)));
break;
}
}
}
}
if (query.IsPage)
{
sqlcount.Append(sqlFrom.ToString()).Append(sqlWhere.ToString());
DataTable dt = _access.getDataTable(sqlcount.ToString());
if (dt != null && dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
}
sqlWhere.AppendFormat("order by ecb.bag_id desc limit {0},{1} ", query.Start, query.Limit);
sql.Append(sqlFrom.ToString()+sqlWhere.ToString());
return _access.getDataTableForObj<EventChinaTrustBagQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagDao-->EventChinaTrustBagList-->" + ex.Message + sql.ToString() + sqlcount.ToString(), ex);
}
}
public int EventChinaTrustBagSave(EventChinaTrustBagQuery query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
if (query.bag_id == 0)//新增
{
sql.Append("insert into event_chinatrust_bag (");
sql.Append("bag_name,bag_desc,bag_banner,bag_start_time,bag_end_time,");
sql.Append("bag_active,bag_create_user,bag_update_user,bag_create_time,bag_update_time,");
sql.Append("bag_show_start_time,bag_show_end_time,event_id,product_number) ");
sql.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}',", query.bag_name, query.bag_desc, query.bag_banner, CommonFunction.DateTimeToString(query.bag_start_time), CommonFunction.DateTimeToString(query.bag_end_time));
sql.AppendFormat(" '{0}','{1}','{2}','{3}','{4}',", query.bag_active, query.bag_create_user, query.bag_update_user, CommonFunction.DateTimeToString(query.bag_create_time), CommonFunction.DateTimeToString(query.bag_update_time));
sql.AppendFormat(" '{0}','{1}','{2}','{3}'); ", CommonFunction.DateTimeToString(query.bag_show_start_time), CommonFunction.DateTimeToString(query.bag_show_end_time), query.event_id, query.product_number);
}
else//編輯
{
sql.AppendFormat("update event_chinatrust_bag set bag_name='{0}',bag_desc='{1}',bag_banner='{2}',bag_start_time='{3}',bag_end_time='{4}', event_id='{5}',",query.bag_name,query.bag_desc,query.bag_banner,CommonFunction.DateTimeToString(query.bag_start_time),CommonFunction.DateTimeToString(query.bag_end_time),query.event_id);
sql.AppendFormat("bag_update_user='******',bag_update_time='{1}', bag_show_start_time='{2}',bag_show_end_time='{3}',product_number='{4}' where bag_id='{5}';", query.bag_update_user, CommonFunction.DateTimeToString(query.bag_update_time), CommonFunction.DateTimeToString(query.bag_show_start_time), CommonFunction.DateTimeToString(query.bag_show_end_time), query.product_number, query.bag_id);
}
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagDao-->EventChinaTrustBagList-->"+ex.Message+sql.ToString(),ex);
}
}
public int EventChinaTrustBagStatus(EventChinaTrustBagQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update event_chinatrust_bag set bag_active='{0}',bag_update_user='******',bag_update_time='{2}' where bag_id='{3}';",query.bag_active,query.bag_update_user,CommonFunction.DateTimeToString(query.bag_update_time),query.bag_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagDao-->EventChinaTrustBagStatus-->" + ex.Message + sql.ToString(),ex);
}
}
public List<EventChinaTrustBag> GetBag(EventChinaTrustBagQuery q)
{
StringBuilder str = new StringBuilder();
try
{
//str.AppendFormat("SELECT article_id,CONCAT(article_id,'-',article_title) as article_title FROM vote_article where article_status='1' order by article_id DESC; ");
str.AppendFormat("SELECT bag_id, CONCAT(bag_id,'-',bag_name) as bag_name FROM event_chinatrust_bag order by bag_id DESC; ");
return _access.getDataTableForObj<EventChinaTrustBag>(str.ToString());
}
catch (Exception ex)
{
throw new Exception(" VoteArticleDao-->GetBag-->" + ex.Message + "sql:" + str.ToString(), ex);
}
}
public EventChinaTrustBagQuery GetSinggleData(EventChinaTrustBagQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
try
{
sql.Append("select ecb.bag_id,ecb.bag_name,ecb.bag_desc, ecb.bag_banner, ecb.bag_start_time,ecb.bag_end_time, ecb.bag_active, ecb.bag_create_user, ecb.bag_update_user, ecb.bag_create_time,ecb.bag_update_time,ecb.bag_show_start_time, ecb.bag_show_end_time, ecb.event_id, ecb.product_number, mu1.user_username 'create_user' ,mu2.user_username 'update_user'");
sqlFrom.Append("from event_chinatrust_bag ecb LEFT JOIN manage_user mu1 on mu1.user_id=ecb.bag_create_user LEFT JOIN manage_user mu2 on mu2.user_id=ecb.bag_update_user ");
sqlWhere.AppendFormat(" where 1=1 and ecb.bag_id='{0}'", query.bag_id);
return _access.getSinggleObj<EventChinaTrustBagQuery>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EventChinaTrustBagDao-->GetSinggleData-->" + ex.Message + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString(), ex);
}
}
public HttpResponseBase GetBag()
{
List<EventChinaTrustBag> store = new List<EventChinaTrustBag>();
string json = string.Empty;
try
{
EventChinaTrustBagQuery query = new EventChinaTrustBagQuery();
_eventChinaTrustBag = new EventChinaTrustBagMgr(mySqlConnectionString);
//query.event_status = 1;
store = _eventChinaTrustBag.GetBag(query);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
json = "{success:true,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 JsonResult UpdateActive()
{
{
try
{
EventChinaTrustBagQuery query = new EventChinaTrustBagQuery();
if (!string.IsNullOrEmpty(Request.Params["bag_id"].ToString()))
{
query.bag_id = Convert.ToInt32(Request.Params["bag_id"].ToString());
}
query.bag_active = Convert.ToInt32(Request.Params["bag_active"] ?? "0");
_eventChinaTrustBag = new EventChinaTrustBagMgr(mySqlConnectionString);
query.bag_update_user = (Session["caller"] as Caller).user_id;
query.bag_update_time = DateTime.Now;
if (_eventChinaTrustBag.EventChinaTrustBagStatus(query) > 0)
{
return Json(new { success = "true" });
}
else
{
return Json(new { 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);
return Json(new { success = "false" });
}
}
}
public HttpResponseBase EventChinaTrustBagSave()
{
string json = string.Empty;
EventChinaTrustBagQuery query = new EventChinaTrustBagQuery();
EventChinaTrustBagQuery oldModel = new EventChinaTrustBagQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["event_id"]))
{
query.event_id = Request.Params["event_id"];
}
if (!string.IsNullOrEmpty(Request.Params["bag_name"]))
{
query.bag_name = Request.Params["bag_name"];
}
if (!string.IsNullOrEmpty(Request.Params["bag_desc"]))
{
query.bag_desc = Request.Params["bag_desc"];
}
if (!string.IsNullOrEmpty(Request.Params["bag_banner"]))
{
query.bag_banner = Request.Params["bag_banner"];
}
if (!string.IsNullOrEmpty(Request.Params["bag_start_time"]))
{
query.bag_start_time =Convert.ToDateTime( Request.Params["bag_start_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["bag_end_time"]))
{
query.bag_end_time = Convert.ToDateTime(Request.Params["bag_end_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["bag_show_start_time"]))
{
query.bag_show_start_time = Convert.ToDateTime(Request.Params["bag_show_start_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["bag_show_end_time"]))
{
query.bag_show_end_time =Convert.ToDateTime(Request.Params["bag_show_end_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["product_number"]))
{
query.product_number =Convert.ToInt32(Request.Params["product_number"]);
}
if (string.IsNullOrEmpty(Request.Params["bag_id"]))//新增
{
query.bag_create_user=(Session["caller"] as Caller).user_id;
query.bag_update_user = (Session["caller"] as Caller).user_id;
query.bag_create_time = DateTime.Now;
query.bag_update_time = query.bag_create_time;
}
else//編輯
{
query.bag_id = Convert.ToInt32(Request.Params["bag_id"]);
query.bag_update_time = DateTime.Now;
query.bag_update_user = (Session["caller"] as Caller).user_id;
}
_eventChinaTrustBag = new EventChinaTrustBagMgr(mySqlConnectionString);
#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_Min_Element");
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;//圖片存儲地址
if (Request.Files.Count > 0)
{
HttpPostedFileBase file = Request.Files[0];
string fileName = string.Empty;//當前文件名
string fileExtention = string.Empty;//當前文件的擴展名
FileManagement fileLoad = new FileManagement();
fileName = fileLoad.NewFileName(file.FileName);
if (fileName != "")
{
fileName = fileName.Substring(0, fileName.LastIndexOf("."));
fileExtention = file.FileName.Substring(file.FileName.LastIndexOf('.')).ToLower().ToString();
string NewFileName = string.Empty;
BLL.gigade.Common.HashEncrypt hash = new BLL.gigade.Common.HashEncrypt();
NewFileName = hash.Md5Encrypt(fileName, "32");
string ServerPath = string.Empty;
FTP f_cf = new FTP();
f_cf.MakeMultiDirectory(localPromoPath.Substring(0, localPromoPath.Length - PaperPath.Length + 1), PaperPath.Substring(1, PaperPath.Length - 2).Split('/'), ftpuser, ftppwd);
fileName = NewFileName + fileExtention;
NewFileName = localPromoPath + NewFileName + fileExtention;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + PaperPath);
string ErrorMsg = string.Empty;
if (query.bag_id != 0)
{
oldModel = _eventChinaTrustBag.GetSinggleData(query);
if (oldModel.bag_banner != "")
{
string oldFileName = oldModel.bag_banner;
CommonFunction.DeletePicFile(ServerPath + oldFileName);//刪除
FTP ftp = new FTP(localPromoPath, ftpuser, ftppwd);
List<string> tem = ftp.GetFileList();
if (tem.Contains(oldFileName))
{
FTP ftps = new FTP(localPromoPath + oldFileName, ftpuser, ftppwd);
ftps.DeleteFile(localPromoPath + oldFileName);
}
}
}
try
{
Resource.CoreMessage = new CoreResource("Product");
bool result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)
{
query.bag_banner = fileName;
}
}
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);
}
if (!string.IsNullOrEmpty(ErrorMsg))
{
string jsonStr = string.Empty;
json = "{success:true,msg:\"" + ErrorMsg + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
}
}
}
else
{
query.bag_banner = oldModel.bag_banner;
}
#endregion
if (_eventChinaTrustBag.EventChinaTrustBagSave(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}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase EventChinaTrustBagList()
{
EventChinaTrustBagQuery query = new EventChinaTrustBagQuery();
List<EventChinaTrustBagQuery> store = new List<EventChinaTrustBagQuery>();
string json = string.Empty;
int totalCount = 0;
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["eventId"]))
{
query.event_id = Request.Params["eventId"];
}
if (!string.IsNullOrEmpty(Request.Params["bag_status"]))
{
query.bag_active =Convert.ToInt32(Request.Params["bag_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["bag_search_name"]))
{
query.bag_name = Request.Params["bag_search_name"];
}
if (!string.IsNullOrEmpty(Request.Params["date"]))
{
query.date =Convert.ToInt32(Request.Params["date"]);
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd HH:mm:ss");
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd HH:mm:ss");
}
_eventChinaTrustBag = new EventChinaTrustBagMgr(mySqlConnectionString);
store=_eventChinaTrustBag.EventChinaTrustBagList(query, out totalCount);
foreach (var item in store)
{
item.start_time = CommonFunction.DateTimeToString(item.bag_start_time);
item.end_time = CommonFunction.DateTimeToString(item.bag_end_time);
item.show_start_time = CommonFunction.DateTimeToString(item.bag_show_start_time);
item.show_end_time = CommonFunction.DateTimeToString(item.bag_show_end_time);
item.create_time= CommonFunction.DateTimeToString(item.bag_create_time);
item.update_time = CommonFunction.DateTimeToString(item.bag_update_time);
if (item.bag_banner != "")
{
item.s_bag_banner=imgServerPath + PaperPath + item.bag_banner;
}
else
{
item.s_bag_banner = defaultImg;
}
}
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:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
