Example #1
0
        /// <summary>
        /// 會員活動列表查詢
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<MemberEventQuery> Query(MemberEventQuery query, out int totalCount)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strCondition = new StringBuilder();
            StringBuilder strSelect = new StringBuilder();
            StringBuilder strTemp = new StringBuilder();
            try
            {
                query.Replace4MySQL();
                totalCount = 0;
                strSelect.AppendFormat(@"select me.rowID,me.me_name,me.me_desc,me.me_startdate,me.me_enddate,me.et_id,me.me_birthday,me.event_id, me.me_big_banner,me.me_banner_link, ");
                strSelect.AppendFormat(@"me.me_bonus_onetime,me.ml_code,me.me_status,me.k_date,me.k_user,");
                strSelect.AppendFormat(@"me.m_date,me.m_user,et.et_name,et.et_date_parameter,et.et_starttime,et.et_endtime  ");
                strCondition.AppendFormat(@" from member_event me  ");
                strCondition.AppendFormat(@" left join event_type et on me.et_id=et.et_id ");
                strCondition.AppendFormat(@" where 1=1 ");
                if (query.rowID != 0)
                {
                    strCondition.AppendFormat(@" and me.rowID='{0}' ",query.rowID);
                }
                if (!string.IsNullOrEmpty(query.ml_name))
                {
                    strCondition.AppendFormat(@" AND (me.rowID LIKE  N'%{0}%' OR me.me_name LIKE '%{0}%' OR me.me_desc LIKE  N'%{0}%') ", query.ml_name);
                }
                if (!string.IsNullOrEmpty(query.timestart))
                {
                    strCondition.AppendFormat(" and me.k_date>='{0}' ",query.timestart);
                }
                if (!string.IsNullOrEmpty(query.timestart))
                {
                    strCondition.AppendFormat(" and me.k_date<='{0}' ", query.timeend);
                }
                if (query.IsPage)
                {
                    strSql.AppendFormat(@" select count(rowID) as totalCount ");
                    strSql.AppendFormat(strCondition.ToString());
                    System.Data.DataTable _dt = _access.getDataTable(strSql.ToString());
                    if (_dt != null && _dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                    }
                    strSql.AppendFormat(";");
                    strCondition.AppendFormat(@" order by rowID desc ");
                }
                strTemp.AppendFormat(strSelect.ToString());
                strTemp.AppendFormat(strCondition.ToString());
                strTemp.AppendFormat(@" limit {0},{1}", query.Start, query.Limit);
                strTemp.AppendFormat(";");
                strSql.AppendFormat(strTemp.ToString());
                return _access.getDataTableForObj<MemberEventQuery>(strTemp.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("MemberEventDao-->Query-->" + ex.Message + strSql.ToString(), ex);
            }
        }
Example #2
0
 /// <summary>
 /// 更新狀態
 /// </summary>
 /// <param name="meq"></param>
 /// <returns></returns>
 public int UpdateState(MemberEventQuery meq)
 {
     try
     {
         return _MemberEventDao.UpdateState(meq);
     }
     catch (Exception ex)
     {
         throw new Exception("MemberEventMgr-->UpdateState-->" + ex.Message, ex);
     }
 }
Example #3
0
        /// <summary>
        /// 會員活動新增編輯
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public int MemberEventSave(MemberEventQuery query)
        {
            try
            {
                return _MemberEventDao.MemberEventSave(query);
            }
            catch (Exception ex)
            {

                throw new Exception("MemberEventMgr-->MemberEventSave-->" + ex.Message, ex);
            }
        }
Example #4
0
        /// <summary>
        /// 會員活動列表查詢
        /// </summary>
        /// <param name="store"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<MemberEventQuery> Query(MemberEventQuery store, out int totalCount)
        {
            try
            {
                return _MemberEventDao.Query(store, out totalCount);
            }
            catch (Exception ex)
            {

                throw new Exception("MemberEventMgr-->Query-->" + ex.Message, ex);
            }
        }
Example #5
0
        /// <summary>
        /// 會員活動新增編輯
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public int MemberEventSave(MemberEventQuery meq)
        {
            MySqlCommand mySqlCmd = new MySqlCommand();
            MySqlConnection mySqlConn = new MySqlConnection(connStr);
            StringBuilder sql = new StringBuilder();
            meq.Replace4MySQL();
            int i = 0;
            try
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
                {
                    mySqlConn.Open();
                }

                mySqlCmd.Connection = mySqlConn;
                mySqlCmd.Transaction = mySqlConn.BeginTransaction();
                mySqlCmd.CommandType = System.Data.CommandType.Text;
                //新增
                if (meq.rowID == 0)
                {
                    //獲得自增列下一個ID
                    sql.AppendFormat(@"SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME = 'event_type';");
                    mySqlCmd.CommandText = sql.ToString();
                    object ob = mySqlCmd.ExecuteScalar();
                    if (ob != null)
                    {
                        meq.et_id = int.Parse(ob.ToString());
                    }
                    sql.AppendFormat(@"INSERT INTO member_event (me_name,me_desc,me_startdate,me_enddate,");
                    sql.AppendFormat(@" et_id,me_birthday,event_id,me_bonus_onetime,ml_code,me_status,");
                    sql.AppendFormat(@"  me_big_banner,me_banner_link,k_date,k_user,m_date,m_user) ");
                    sql.AppendFormat(@" VALUES('{0}','{1}','{2}','{3}',", meq.me_name, meq.me_desc, meq.me_startdate.ToString("yyyy-MM-dd"), meq.me_enddate.ToString("yyyy-MM-dd"));
                    sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}',", meq.et_id, meq.me_birthday, meq.event_id, meq.me_bonus_onetime, meq.ml_code, meq.me_status);
                    sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}'); ",meq.me_big_banner,meq.me_banner_link, meq.k_date.ToString("yyyy-MM-dd HH:mm:ss"), meq.k_user, meq.m_date.ToString("yyyy-MM-dd HH:mm:ss"), meq.m_user);
                    sql.AppendFormat(@"INSERT INTO event_type (et_name,et_date_parameter,et_starttime,et_endtime) ");
                    sql.AppendFormat(@"VALUES('{0}','{1}',", meq.et_name, meq.et_date_parameter);
                    sql.AppendFormat(@" '{0}','{1}');", meq.et_starttime, meq.et_endtime);

                }
                //編輯
                else
                {
                    sql.AppendFormat(@"UPDATE member_event SET me_name='{0}',me_desc='{1}',", meq.me_name, meq.me_desc);
                    sql.AppendFormat(@"me_startdate='{0}',me_enddate='{1}',", meq.me_startdate.ToString("yyyy-MM-dd"), meq.me_enddate.ToString("yyyy-MM-dd"));
                    sql.AppendFormat(@"me_birthday='{0}',event_id='{1}',", meq.me_birthday, meq.event_id);
                    sql.AppendFormat(@"me_bonus_onetime='{0}',ml_code='{1}',me_big_banner='{2}',me_banner_link='{3}',", meq.me_bonus_onetime, meq.ml_code, meq.me_big_banner,meq.me_banner_link);
                    sql.AppendFormat(@"m_date='{0}',m_user='******',me_status='{2}' ", meq.m_date.ToString("yyyy-MM-dd HH:mm:ss"), meq.m_user,0);
                    sql.AppendFormat(@" WHERE rowID={0};", meq.rowID);
                    sql.AppendFormat(@"UPDATE event_type SET et_name='{0}',et_date_parameter='{1}',", meq.et_name, meq.et_date_parameter);
                    sql.AppendFormat(@"et_starttime='{0}',et_endtime='{1}' ", meq.et_starttime, meq.et_endtime);
                    sql.AppendFormat(@" WHERE et_id='{0}';", meq.et_id);
                }

                mySqlCmd.CommandText = sql.ToString();
                i = mySqlCmd.ExecuteNonQuery();
                mySqlCmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlCmd.Transaction.Rollback();
                throw new Exception("MemberEventDao-->MemberEventSave-->" + ex.Message + sql.ToString(), ex);
            }
            finally
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
                {
                    mySqlConn.Close();
                }
            }
            return i;
        }
Example #6
0
 public DataTable UpdateRepeat(MemberEventQuery query)
 {
     StringBuilder sql = new StringBuilder();
     try
     {
         sql.AppendFormat("select me.rowID   from member_event me where me.event_id='{0}' and ml_code='{1}' and rowID!='{2}';",query.event_id,query.ml_code,query.rowID);
         return _access.getDataTable(sql.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("MemberEventDao-->UpdateRepeat" + ex.Message + sql.ToString(), ex);
     }
 }
Example #7
0
 /// <summary>
 /// 更新狀態
 /// </summary>
 /// <param name="meq"></param>
 /// <returns></returns>
 public int UpdateState(MemberEventQuery meq)
 {
     StringBuilder sql = new StringBuilder();
     sql.AppendFormat(@"UPDATE member_event SET me_status='{0}',m_date='{1}' ", meq.me_status, meq.m_date.ToString("yyyy-MM-dd HH:mm:ss"));
     sql.AppendFormat(@" ,m_user='******' WHERE rowID='{1}'; ", meq.m_user, meq.rowID);
     try
     {
         return _access.execCommand(sql.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("MemberEventDao-->UpdateState" + ex.Message + sql.ToString(), ex);
     }
 }
 /// <summary>
 /// 更改狀態 啟用或者禁用
 /// </summary>
 /// <returns></returns>
 public JsonResult UpdateState()
 {
     int id = 0;
     int activeValue = 0;
     _memberEvent = new MemberEventMgr(mySqlConnectionString);
     if (!string.IsNullOrEmpty(Request.Params["id"]))
     {
         id = int.Parse(Request.Params["id"]);
     }
     if (!string.IsNullOrEmpty(Request.Params["active"]))
     {
         activeValue = int.Parse(Request.Params["active"]);
     }
     MemberEventQuery meq = new MemberEventQuery();
     meq.rowID = id;
     meq.me_status = activeValue;
     meq.m_date = DateTime.Now;
     meq.m_user = (Session["caller"] as Caller).user_id;
     if (_memberEvent.UpdateState(meq) > 0)
     {
         return Json(new { success = "true", msg = "" });
     }
     else
     {
         return Json(new { success = "false", msg = "" });
     }
 }
        /// <summary>
        /// 會員活動新增編輯
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase SaveMemberEvent()
        {
            string json = string.Empty;
            try
            {
                MemberEventQuery query = new MemberEventQuery();
                _memberEvent = new MemberEventMgr(mySqlConnectionString);
                if (!string.IsNullOrEmpty(Request.Params["rowID"]))
                {
                    query.rowID = int.Parse(Request.Params["rowID"]);
                }
                query.me_name = Request.Params["me_name"];
                query.me_desc = Request.Params["me_desc"];
                query.event_id = Request.Params["event_id"];

                if (!string.IsNullOrEmpty(Request.Params["me_startdate"]))
                {
                    query.me_startdate = DateTime.Parse(Request.Params["me_startdate"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["me_enddate"]))
                {
                    query.me_enddate = DateTime.Parse(Request.Params["me_enddate"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["me_birthday"]))
                {
                    //query.me_birthday = int.Parse(Request.Params["me_birthday"]);
                    if (Request.Params["me_birthday"] == "1")
                    {
                        query.me_birthday = 1;
                    }
                    else
                    {
                        query.me_birthday = 0;
                    }
                }
                if (!string.IsNullOrEmpty(Request.Params["me_bonus_onetime"]))
                {
                    if (Request.Params["me_bonus_onetime"] == "1")
                    {
                        query.me_bonus_onetime = 1;
                    }
                    else
                    {
                        query.me_bonus_onetime = 0;
                    }
                }
                if (!string.IsNullOrEmpty(Request.Params["me_banner_link"]))
                {
                    query.me_banner_link = Request.Params["me_banner_link"];
                }
                if (!string.IsNullOrEmpty(Request.Params["code"]))
                {
                    query.ml_code = Request.Params["code"].TrimEnd(',');
                }
                else
                {
                    query.ml_code = "0";
                }
                if (!string.IsNullOrEmpty(Request.Params["et_id"]))
                {
                    query.et_id = int.Parse(Request.Params["et_id"]);
                }
                //query.et_name = Request.Params["et_name"];
                switch (Request.Params["et_name"])
                {
                    case "1":
                        query.et_name = "DD";
                        query.et_date_parameter = "";
                        break;
                    case "2":
                        query.et_name = "WW";
                        if (!string.IsNullOrEmpty(Request.Params["week"]))
                        {
                            query.et_date_parameter = Request.Params["week"].TrimEnd(',');
                        }
                        break;
                    case "3":
                        query.et_name = "MM";
                        if (!string.IsNullOrEmpty(Request.Params["month"]))
                        {
                            query.et_date_parameter = Request.Params["month"].TrimEnd(',');
                        }
                        break;
                }

                #region 處理每天的開始結束時間
                query.et_starttime = Request.Params["et_starttime"];
                query.et_endtime = Request.Params["et_endtime"];

                #endregion
                query.k_user = (Session["caller"] as Caller).user_id;
                query.m_user = (Session["caller"] as Caller).user_id;
                query.k_date = DateTime.Now;
                query.m_date = query.k_date;
                string event_json=_memberEvent.IsGetEventID(query.event_id);
                if (event_json.IndexOf("true") > 0)
                {
                    #region 判斷數據不能重複
                    if (_memberEvent.IsRepeat(query))//不重複
                    {

                        #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 localMemberEventPath = imgLocalPath + MemberEventPath;//圖片存儲地址
                        FileManagement fileLoad = new FileManagement();
                        int totalCount = 0;
                        MemberEventQuery oldQuery = _memberEvent.Query(new MemberEventQuery { rowID = query.rowID }, out totalCount).FirstOrDefault();
                        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[] dirPath = new string[2];
                                //dirPath[0] = NewFileName.Substring(0, 2)+"/";
                                //dirPath[1] = NewFileName.Substring(2, 2)+"/";
                                string ServerPath = string.Empty;
                                FTP f_cf = new FTP();
                                CreateFolder(localMemberEventPath, dirPath);
                                fileName = NewFileName + fileExtention;
                                NewFileName = localMemberEventPath + NewFileName + fileExtention;//絕對路徑
                                ServerPath = Server.MapPath(imgLocalServerPath + MemberEventPath);
                                string ErrorMsg = string.Empty;
                                //上傳之前刪除已有的圖片
                                if (query.rowID != 0)
                                {

                                    if (oldQuery.me_big_banner != "")
                                    {
                                        string oldFileName = oldQuery.me_big_banner;
                                        CommonFunction.DeletePicFile(ServerPath + oldFileName);//刪除本地圖片
                                        FTP ftp = new FTP(localMemberEventPath, ftpuser, ftppwd);
                                        List<string> tem = ftp.GetFileList();
                                        if (tem.Contains(oldFileName))
                                        {
                                            FTP ftps = new FTP(localMemberEventPath + oldFileName, ftpuser, ftppwd);
                                            ftps.DeleteFile(localMemberEventPath + 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.me_big_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();
                                    return this.Response;
                                }
                            }
                            else
                            {
                                query.me_big_banner = oldQuery.me_big_banner;
                            }
                        }
                        #endregion
                        if (_memberEvent.MemberEventSave(query) > 0)
                        {
                            //json = "{success:true}";
                            if (!string.IsNullOrEmpty(Request.Params["rowID"]))
                            {
                                json = "{success:true,msg:'修改成功!'}";
                            }
                            else
                            {
                                json = "{success:true,msg:'新增成功!'}";
                            }
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(Request.Params["rowID"]))
                            {
                                json = "{success:false,msg:'修改失敗!'}";
                            }
                            else
                            {
                                json = "{success:false,msg:'新增失敗!'}";
                            }
                        }

                    }
                    else
                    {
                        json = "{success:false,msg:'數據重複!'}";
                    }
                    #endregion
                }
                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;
        }
        /// <summary>
        /// 列表頁
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase GetMemberEventList()
        {
            MemberEventQuery query = new MemberEventQuery();
            List<MemberEventQuery> stores = new List<MemberEventQuery>();
            string json = string.Empty;
            try
            {

                query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
                query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
                _memberEvent = new MemberEventMgr(mySqlConnectionString);
                if (!string.IsNullOrEmpty(Request.Params["search_content"]))
                {
                    query.ml_name = Request.Params["search_content"];
                }
                if (!string.IsNullOrEmpty(Request.Params["timestart"]))
                {
                    query.timestart = Convert.ToDateTime(Request.Params["timestart"].ToString()).ToString("yyyy-MM-dd 00:00:00");
                }
                if (!string.IsNullOrEmpty(Request.Params["timeend"]))
                {
                    query.timeend = Convert.ToDateTime(Request.Params["timeend"].ToString()).ToString("yyyy-MM-dd 23:59:59");
                }
                int totalCount = 0;
                stores = _memberEvent.Query(query, out totalCount);
                foreach (var item in stores)
                {
                    item.s_me_banner_link = imgServerPath + MemberEventPath + item.me_big_banner;
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式     
                timeConverter.DateTimeFormat = "yyyy-MM-dd";
                //listUser是准备转换的对象
                json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, 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:true,totalCount:0,data:[]}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Example #11
0
        ///// <summary>
        ///// 群組名稱
        ///// </summary>
        ///// <param name="query"></param>
        ///// <returns></returns>
        //public string GetMlName(MemberEventQuery query)
        //{
        //    try
        //    {
        //        return _MemberEventDao.GetMlName(query);
        //    }
        //    catch (Exception ex)
        //    {

        //        throw new Exception("MemberEventMgr-->GetMlName-->" + ex.Message, ex);
        //    }
        //}

        public bool IsRepeat(MemberEventQuery query)
        {
            string json = string.Empty;
            bool result = true;
            DataTable _dt=new DataTable ();
            try
            {
                if (query.rowID != 0)
                {
                    _dt = _MemberEventDao.UpdateRepeat(query);
                 
                }
                else
                {
                    _dt = _MemberEventDao.InsertRepeat(query);

                }
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    result = false;//數據重複
                }
                else
                {
                    result = true;//不重複
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("MemberEventMgr-->IsRepeat-->" + ex.Message, ex);
            }
        }