public DataTable NewPromoCarnetList(NewPromoCarnetQuery query, out int totalCount)
{
try
{
return _INewPromoCarnet.NewPromoCarnetList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetMgr-->NewPromoCarnetList-->" + ex.Message, ex);
}
}
public NewPromoCarnetQuery GetModel(NewPromoCarnetQuery query)
{
try
{
return _access.getSinggleObj<NewPromoCarnetQuery>(_INewPromoCarnet.GetModel(query));
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetMgr-->GetModel-->" + ex.Message, ex);
}
}
public int InsertNewPromoCarnet(NewPromoCarnetQuery query)
{
try
{
return _access.execCommand(_INewPromoCarnet.InsertNewPromoCarnet(query));
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetMgr-->InsertNewPromoCarnet-->" + ex.Message, ex);
}
}
public DataTable NewPromoCarnetList(NewPromoCarnetQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
sql.Append("select npc.row_id,npc.event_name,npc.event_desc,npc.message_mode,npc.message_content,npc.group_id,");
sql.Append("npc.link_url,npc.promo_image, npc.promo_image as s_promo_image,npc.event_id,npc.device,npc.count_by,npc.count,case when npc.active_now=0 then '否' else '是' end as active_now , case when npc.new_user=0 then '否' else '是' end as new_user,npc.new_user_date,");
sql.Append("npc.`start`,npc.`end`,npc.active,npc.kuser,muser,npc.created,npc.modified");
sql.Append(",vug.group_name as group_name,npc.present_event_id ");
sqlWhere.Append(" from new_promo_carnet npc ");
sqlWhere.Append("LEFT JOIN vip_user_group vug on npc.group_id=vug.group_id ");
sqlWhere.Append(" where npc.event_id!='' ");
sqlcount.Append(" select count(npc.row_id) as totalCount ");
if (query.event_id != "")
{
sqlWhere.AppendFormat(" and npc.event_id='{0}' ", query.event_id);
}
else
{
if (query.condition == 0)//已過期
{
sqlWhere.AppendFormat(" and npc.end<'{0}'", CommonFunction.DateTimeToString(DateTime.Now));
}
else
{
sqlWhere.AppendFormat(" and npc.end>'{0}'", CommonFunction.DateTimeToString(DateTime.Now));
}
}
sqlWhere.Append(" order by npc.row_id desc ");
if (query.IsPage)
{
sqlcount.Append(sqlWhere.ToString());
DataTable _dt = _access.getDataTable(sqlcount.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlWhere.AppendFormat("limit {0},{1};", query.Start, query.Limit);
}
sql.Append(sqlWhere.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetDao-->NewPromoCarnetList-->" + ex.Message + sql.ToString() + sqlcount.ToString(), ex);
}
}
public bool UpdateNewPromoCarnet(NewPromoCarnetQuery query)
{
try
{
if (_access.execCommand(_INewPromoCarnet.UpdateNewPromoCarnet(query)) > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetMgr-->UpdateNewPromoCarnet-->" + ex.Message, ex);
}
}
public string InsertNewPromoCarnet(NewPromoCarnetQuery store)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(@"insert into new_promo_carnet (event_name,event_desc,event_id,group_id,row_id,present_event_id,
link_url,promo_image,device,count_by,
count,active_now,new_user,new_user_date,
start,end,active,kuser,
muser,created,modified,message_mode,message_content )");
sql.AppendFormat(@" values('{0}','{1}','{2}','{3}','{4}','{5}'", store.event_name, store.event_desc, store.event_id, store.group_id, store.row_id, store.present_event_id);
sql.AppendFormat(@",'{0}','{1}','{2}','{3}'", store.link_url, store.promo_image, store.device, store.count_by);
sql.AppendFormat(@",'{0}','{1}','{2}','{3}'", store.count, store.active_now, store.new_user, Common.CommonFunction.DateTimeToString(store.new_user_date));
sql.AppendFormat(@",'{0}','{1}','{2}','{3}'", Common.CommonFunction.DateTimeToString(store.start), Common.CommonFunction.DateTimeToString(store.end), store.active, store.kuser);
sql.AppendFormat(@",'{0}','{1}','{2}','{3}','{4}');", store.muser, Common.CommonFunction.DateTimeToString(store.created), Common.CommonFunction.DateTimeToString(store.modified), store.message_mode, store.message_content);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetDao.InsertNewPromoCarnet-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 更改活動使用狀態
/// </summary>
/// <returns>數據庫操作結果</returns>
public JsonResult UpdateActiveCarnet()
{
try
{
NewPromoCarnetQuery query = new NewPromoCarnetQuery();
query.row_id = Convert.ToInt32(Request.Params["row_id"].ToString());
query.active = Convert.ToInt32(Request.Params["active"] ?? "0");
_INewPromoCarnetMgr = new NewPromoCarnetMgr(mySqlConnectionString);
query.muser = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
query.modified = DateTime.Now;
if (_INewPromoCarnetMgr.UpdateActive(query))
{
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 InsertNewPromoCarnet()
{
NewPromoCarnetQuery query = new NewPromoCarnetQuery();
NewPromoCarnetQuery oldModel = new NewPromoCarnetQuery();
_INewPromoCarnetMgr = new NewPromoCarnetMgr(mySqlConnectionString);
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["row_id"]))
{
query.row_id = Convert.ToInt32(Request.Params["row_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["present_event_id"]))
{
query.present_event_id = Request.Params["present_event_id"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["new_user"]))
{
query.new_user = Convert.ToInt32(Request.Params["new_user"]);
}
if (!string.IsNullOrEmpty(Request.Params["new_user_date"]))
{
query.new_user_date = Convert.ToDateTime(Request.Params["new_user_date"]);
}
if (!string.IsNullOrEmpty(Request.Params["count_by"]))
{
query.count_by = Convert.ToInt32(Request.Params["count_by"]);
}
if (!string.IsNullOrEmpty(Request.Params["count"]))
{
query.count = Convert.ToInt32(Request.Params["count"]);
}
if (!string.IsNullOrEmpty(Request.Params["device"]))
{
query.device = (Request.Params["device"]);
}
if (!string.IsNullOrEmpty(Request.Params["message_mode"]))
{
query.message_mode = Convert.ToInt32(Request.Params["message_mode"]);
}
if (!string.IsNullOrEmpty(Request.Params["message_content"]))
{
query.message_content = (Request.Params["message_content"]);
}
if (!string.IsNullOrEmpty(Request.Params["link_url"]))
{
query.link_url = (Request.Params["link_url"]);
}
if (!string.IsNullOrEmpty(Request.Params["promo_image"]))
{
query.promo_image = Request.Params["promo_image"];
}
if (!string.IsNullOrEmpty(Request.Params["active_now"]))
{
query.active_now = Convert.ToInt32(Request.Params["active_now"]);
}
#region 上傳圖片
string ErrorMsg = string.Empty;
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 + NewPromoPath;//圖片存儲地址
FileManagement fileLoad = new FileManagement();
if (Request.Files.Count > 0)
{
HttpPostedFileBase file = Request.Files[0];
string fileName = string.Empty;//當前文件名
string fileExtention = string.Empty;//當前文件的擴展名
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 - NewPromoPath.Length + 1), NewPromoPath.Substring(1, NewPromoPath.Length - 2).Split('/'), ftpuser, ftppwd);
fileName = NewFileName + fileExtention;
NewFileName = localPromoPath + NewFileName + fileExtention;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + NewPromoPath);
//上傳之前刪除已有的圖片
if (query.row_id != 0)
{
oldModel = _INewPromoCarnetMgr.GetModel(query);
if (oldModel.promo_image != "")
{
string oldFileName = oldModel.promo_image;
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");//尋找product.resx中的資源文件
bool result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)
{
query.promo_image = 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();
return this.Response;
}
}
}
else
{
query.promo_image = oldModel.promo_image;
}
#endregion
if (!string.IsNullOrEmpty(Request.Params["event_name"]))
{
query.event_name = Request.Params["event_name"];
}
if (!string.IsNullOrEmpty(Request.Params["event_desc"]))
{
query.event_desc = Request.Params["event_desc"];
}
if (!string.IsNullOrEmpty(Request.Params["start"]))
{
query.start = Convert.ToDateTime(Request.Params["start"]);
}
if (!string.IsNullOrEmpty(Request.Params["end"]))
{
query.end = Convert.ToDateTime(Request.Params["end"]);
}
if (query.row_id == 0)
{
query.kuser = (Session["caller"] as Caller).user_id;
query.muser = query.kuser;
query.created = DateTime.Now;
query.modified = query.created;
query.row_id = _INewPromoCarnetMgr.GetNewPromoCarnetMaxId();
query.event_id = BLL.gigade.Common.CommonFunction.GetEventId("F2", query.row_id.ToString());
if (_INewPromoCarnetMgr.InsertNewPromoCarnet(query) > 0)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
else
{
query.muser = (Session["caller"] as Caller).user_id;
query.modified = DateTime.Now;
if (_INewPromoCarnetMgr.UpdateNewPromoCarnet(query))
{
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 GetNewPromoCarnetList()
{
string json = string.Empty;
List<NewPromoCarnetQuery> store = new List<NewPromoCarnetQuery>();
NewPromoCarnetQuery query = new NewPromoCarnetQuery();
NewPromoPresentQuery queryPresent = new NewPromoPresentQuery();
queryPresent.IsPage = false;
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["condition"]))
{
query.condition = Convert.ToInt32(Request.Params["condition"]);
}
_INewPromoCarnetMgr = new NewPromoCarnetMgr(mySqlConnectionString);
DataTable _dt = _INewPromoCarnetMgr.NewPromoCarnetList(query, out totalCount);
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (_dt.Rows[i]["s_promo_image"].ToString() != "")
{
_dt.Rows[i]["s_promo_image"] = imgServerPath + NewPromoPath + _dt.Rows[i]["promo_image"];
}
else
{
_dt.Rows[i]["s_promo_image"] = defaultImg;
}
if (_dt.Rows[i]["group_name"].ToString() == "")
{
_dt.Rows[i]["group_name"] = "不分";
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, 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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public string UpdateNewPromoCarnet(NewPromoCarnetQuery store)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@"set sql_safe_updates=0;update new_promo_carnet set event_name='{0}',event_desc='{1}',present_event_id='{2}',group_id='{3}'", store.event_name, store.event_desc, store.present_event_id, store.group_id);
sql.AppendFormat(@",link_url='{0}',promo_image='{1}',device='{2}',count_by='{3}'", store.link_url, store.promo_image, store.device, store.count_by);
sql.AppendFormat(@",count='{0}',active_now='{1}',new_user='******',new_user_date='{3}'", store.count, store.active_now, store.new_user, Common.CommonFunction.DateTimeToString(store.new_user_date));
sql.AppendFormat(@",start='{0}',end='{1}'", Common.CommonFunction.DateTimeToString(store.start), Common.CommonFunction.DateTimeToString(store.end));
sql.AppendFormat(@",muser='******',modified='{1}',message_mode='{2}',message_content='{3}' where row_id='{4}';set sql_safe_updates=1;", store.muser, Common.CommonFunction.DateTimeToString(store.modified), store.message_mode, store.message_content, store.row_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetDao.UpdateNewPromoCarnet-->" + ex.Message + sql.ToString(), ex);
}
}
public string GetModel(NewPromoCarnetQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select npc.row_id,npc.present_event_id,npc.event_name,npc.event_desc,npc.message_mode,npc.message_content,npc.group_id,npc.link_url,npc.promo_image,npc.event_id,npc.device,npc.count_by,npc.count,npc.active_now,npc.new_user,npc.new_user_date,npc.`start`,npc.`end`,npc.active,npc.kuser,muser,npc.created,npc.modified from new_promo_carnet npc where npc.row_id={0};", query.row_id);
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetDao.GetModel-->" + ex.Message + sql.ToString(), ex);
}
return sql.ToString();
}
public string UpdateActive(NewPromoCarnetQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("set sql_safe_updates=0;update new_promo_carnet set active='{0}' where row_id='{1}';set sql_safe_updates=1;", query.active, query.row_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("NewPromoCarnetDao.UpdateActive-->" + ex.Message + sql.ToString(), ex);
}
}