Exemple #1
0
        public DataTable GetBlackList(VipUserQuery query, out int totalCount)
        {
            try
            {
                return _vipUserDao.GetBlackList(query, out totalCount);
            }
            catch (Exception ex)
            {

                throw new Exception("VipUserMgr-->GetBlackList-->" + ex.Message, ex);
            }
        }
Exemple #2
0
        public string UpdateState(VipUserQuery query)
        {
            StringBuilder strSql = new StringBuilder();
            try
            {
                strSql.AppendFormat(@"UPDATE vip_user set status={0},update_id={1},updatedate={2},source=2 WHERE v_id={3};", query.status, query.update_id, query.updatedate, query.v_id);
                return strSql.ToString();
            }
            catch (Exception ex)
            {

                throw new Exception("VipUserDao-->UpdateState-->" + ex.Message + strSql.ToString(), ex);
            }
        }
Exemple #3
0
        public int UpdateState(VipUserQuery query)
        {
            string sql = string.Empty;
            int result = 0;
            try
            {
                query.updatedate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
                sql = _vipUserDao.UpdateState(query);
                if (query.user_id == 0)
                {
                    VipUser vimodel = new VipUser();
                    vimodel = _dbAccess.getSinggleObj<VipUser>(_vipUserDao.GetSingleByID(Convert.ToInt32(query.v_id)));
                    if (vimodel != null)
                    {
                        query.user_id = vimodel.user_id;
                    }
                }
                if (query.user_id != 0)
                {
                    UserLifeDao _userLifeDao = new UserLifeDao(conn);

                    if (query.status == 1)//z狀態改為鎖定時記錄會員取消電子報的時間和操作人
                    {
                        //將取消電子報的時間和人員加入會員生活信息表(user_life)edit by shuangshuang0420j 20150814 11:00
                        UserLife modelUF = new UserLife();
                        modelUF = _userLifeDao.GetSingle(query.user_id, "cancel_edm_time");
                        if (modelUF != null)
                        {
                            sql = sql + _userLifeDao.UpdateEdmTime(query.user_id, 0, (int)query.update_id);
                        }
                        sql = sql + _userLifeDao.UpdateEdmTime(query.user_id, query.updatedate, (int)query.update_id);
                    }
                    else if (query.user_status == 0)//狀態改為解鎖時清空會員取消電子報的信息
                    {
                        sql = sql + _userLifeDao.UpdateEdmTime(query.user_id, 0, (int)query.update_id);
                    }
                }
                result = _dbAccess.execCommand(sql);
                return result;
            }
            catch (Exception ex)
            {

                throw new Exception("VipUserMgr-->UpdateState-->" + ex.Message, ex);
            }
        }
Exemple #4
0
 public int DeleVipUser(VipUserQuery query)//add by chaojie1124j 添加于2015/9/21用於實現刪除會員至群組
 {
     query.Replace4MySQL();
     StringBuilder strSql = new StringBuilder();
     try
     {
         strSql.AppendFormat("DELETE from vip_user where v_id='{0}';", query.v_id);
         return _dbAccess.execCommand(strSql.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("VipUserDao-->AddVipUser-->" + ex.Message + strSql.ToString(), ex);
     }
 }
Exemple #5
0
 public int AddVipUser(VipUserQuery query)//add by chaojie1124j 添加于2015/9/21用於實現添加會員至群組
 {
     query.Replace4MySQL();
     StringBuilder strSql = new StringBuilder();
     try
     {
         strSql.AppendFormat(" insert into vip_user(user_email,user_id,status,group_id,create_id,update_id,createdate,updatedate,source) values('{0}','{1}',1,'{2}',{3},'{4}','{5}','{6}',2);", query.user_email,query.user_id,query.group_id,query.create_id, query.update_id, query.createdate, query.updatedate);
         return _dbAccess.execCommand(strSql.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("VipUserDao-->AddVipUser-->" + ex.Message + strSql.ToString(), ex);
     }
 }
Exemple #6
0
 public string UpdateBlackList(VipUserQuery query)
 {
     query.Replace4MySQL();
     StringBuilder strSql = new StringBuilder();
     try
     {
         strSql.AppendFormat(" insert into vip_user(user_email,user_id,status,group_id,create_id,update_id,createdate,updatedate,source) values('{0}',{1},1,48,{2},{3},{4},{5},2);", query.vuser_email, query.v_id, query.create_id, query.update_id, query.createdate, query.updatedate);
         return strSql.ToString();
     }
     catch (Exception ex)
     {
         throw new Exception("VipUserDao-->UpdateBlackList-->" + ex.Message + strSql.ToString(), ex);
     }
 }
Exemple #7
0
        public int UpdateBlackList(string email)
        {
            string sqlEmail = string.Empty;
            string sqlUserID = string.Empty;
            string sqlUpdate = string.Empty;
            uint userID = 0;
            int sum = 0;
            try
            {
                sqlUserID = _usersDao.GetUserIDbyEmail(email);
                DataTable users = _dbAccess.getDataTable(sqlUserID);
                if (users != null && users.Rows.Count > 0)
                {
                    userID = Convert.ToUInt32(users.Rows[0][0]);
                }
                if (string.IsNullOrEmpty(userID.ToString()) || userID == 0)
                {
                    return -1;//郵箱不存在對應的用戶
                }
                else
                {
                    sqlEmail = _vipUserDao.SelectEmail(userID);
                    DataTable temail = _dbAccess.getDataTable(sqlEmail);
                    if (temail != null && temail.Rows.Count > 0)
                    {
                        sum = Convert.ToInt32(temail.Rows[0][0]);
                    }
                    if (sum > 0)
                    {
                        return -2;//該用戶已加入黑名單
                    }
                    else
                    {
                        uint update_id = Convert.ToUInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
                        uint phpTime = (uint)Common.CommonFunction.GetPHPTime();
                        VipUserQuery query = new VipUserQuery();
                        query.vuser_email = email;
                        query.v_id = userID;
                        query.create_id = update_id;
                        query.update_id = update_id;
                        query.createdate = phpTime;
                        query.updatedate = phpTime;
                        sqlUpdate = _vipUserDao.UpdateBlackList(query);
                        //將取消電子報的時間和人員加入會員生活信息表(user_life)edit by shuangshuang0420j 20150814 11:00
                        UserLifeDao _userLifeDao = new UserLifeDao(conn);

                        sqlUpdate = sqlUpdate + _userLifeDao.UpdateEdmTime(userID, phpTime, (int)update_id);

                        return _dbAccess.execCommand(sqlUpdate);
                    }
                }

            }
            catch (Exception ex)
            {
                throw new Exception("VipUserMgr-->UpdateBlackList-->" + ex.Message, ex);
            }
        }
Exemple #8
0
        public HttpResponseBase GetBlackList()
        {
            string json = string.Empty;
            int totalCount = 0;
            _vipuserMgr = new VipUserMgr(mySqlConnectionString);
            try
            {
                DataTable store = new DataTable();
                VipUserQuery query = new VipUserQuery();
                query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
                query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
                if (!string.IsNullOrEmpty(Request.Params["searchState"]))
                {
                    query.search_state = Convert.ToInt32(Request.Params["searchState"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["search_text"]))
                {
                    query.serchtype = Request.Params["search_text"].ToString().Replace("\\", "\\\\").Trim();
                }
                if (!string.IsNullOrEmpty(Request.Params["start_date"]))
                {
                    query.start = Convert.ToDateTime(Request.Params["start_date"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["end_date"]))
                {
                    query.end = Convert.ToDateTime(Request.Params["end_date"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["source"]))
                {
                    query.source = Convert.ToUInt32(Request.Params["source"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["uid"]))
                {
                    query.User_Id = Convert.ToUInt32(Request.Params["uid"]);
                }
                store = _vipuserMgr.GetBlackList(query, out totalCount);

                foreach (DataRow item in store.Rows)
                {
                    item["user_email"] = item["user_email"].ToString().Split('@')[0] + "@***";
                }
                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);
            this.Response.End();
            return this.Response;
        }
 public List<VipUserQuery> GetVipUserList(VipUserQuery vu, out int totalCount)
 {
     try
     {
         return _vipUserGroup.GetVipUserList(vu, out totalCount);
     }
     catch (Exception ex)
     {
         throw new Exception("VipUserGroupMgr.GetVipUserList-->" + ex.Message, ex);
     }
 }
        /// <summary>
        /// 刪除會員至群組
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase DeleVipUser()
        {
            string jsonStr = "";
            VipUserQuery vip = new VipUserQuery();
            if (!string.IsNullOrEmpty(Request.Params["vid"]))
            {
                vip.v_id = uint.Parse(Request.Params["vid"]);
            }

            try
            {
                _vipuserMgr = new VipUserMgr(mySqlConnectionString);
                if (_vipuserMgr.DeleVipUser(vip) > 0)
                {
                    jsonStr = "{success:true}";
                }
                else
                {
                    jsonStr = "{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);
                jsonStr = "{success:false}";
            }
            this.Response.Clear();
            this.Response.Write(jsonStr);
            this.Response.End();
            return this.Response;
        }
        /// <summary>
        /// 新增會員至群組
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase SaveVipUser()
        {
            string jsonStr = "";
            VipUserQuery vip = new VipUserQuery();
            if (!string.IsNullOrEmpty(Request.Params["usermail"]))
            {
                vip.user_email = Request.Params["usermail"];
            }
            if (!string.IsNullOrEmpty(Request.Params["user_id"]))
            {
                vip.user_id = uint.Parse(Request.Params["user_id"]);
            }
            if (!string.IsNullOrEmpty(Request.Params["group_id"]))
            {
                vip.group_id = uint.Parse(Request.Params["group_id"]);
            }

            vip.create_id = uint.Parse((Session["caller"] as Caller).user_id.ToString());
            vip.update_id = uint.Parse((Session["caller"] as Caller).user_id.ToString());
            vip.createdate = uint.Parse(CommonFunction.GetPHPTime().ToString());
            vip.updatedate = vip.createdate;
            try
            {
                _vipuserMgr = new VipUserMgr(mySqlConnectionString);
                if (_vipuserMgr.AddVipUser(vip) > 0)
                {
                    jsonStr = "{success:true}";
                }
                else
                {
                    jsonStr = "{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);
                jsonStr = "{success:false}";
            }
            this.Response.Clear();
            this.Response.Write(jsonStr);
            this.Response.End();
            return this.Response;
        }
 public HttpResponseBase GetVipUserList()
 {
     List<VipUserQuery> stores = new List<VipUserQuery>();
     string json = string.Empty;
     try
     {
         int totalCount = 0;
         VipUserQuery query = new VipUserQuery();
         query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
         query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
         if (!string.IsNullOrEmpty(Request.QueryString["groupid"]))
         {
             query.group_id = Convert.ToUInt32(Request.QueryString["groupid"]);
         }
         if (!string.IsNullOrEmpty(Request.Params["serchs"]))
         {
             query.serchtype = Request.Params["serchs"];
         }
         query.content = Request.Params["serchcontent"];
         _userGroupMgr = new VipUserGroupMgr(mySqlConnectionString);
         stores = _userGroupMgr.GetVipUserList(query, out totalCount);
         foreach (var item in stores)
         {
             if (!string.IsNullOrEmpty(item.createdate.ToString()))
             {
                 item.screatedate = CommonFunction.GetNetTime(item.createdate).ToString("yyyy/MM/dd");
             }
             else
             {
                 item.screatedate = CommonFunction.GetNetTime(0).ToString("yyyy/MM/dd");
             }
             if (!string.IsNullOrEmpty(item.user_reg_date.ToString()))
             {
                 item.reg_date = CommonFunction.GetNetTime(item.user_reg_date);
             }
             else
             {
                 item.reg_date = CommonFunction.GetNetTime(0);
             }
             if (!string.IsNullOrEmpty(item.user_name))
             {
                 item.user_name = item.user_name.Substring(0, 1) + "**";
             }
             item.vuser_email = item.vuser_email.Split('@')[0] + "@***";
             item.user_email = item.user_email.Split('@')[0] + "@***";
         }
         IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
         //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式     
         timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
         //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;
 }
Exemple #13
0
        public int CancelEdm(string mail, uint update_id, out uint vid)
        {
            int result = 0;
            string sqlEmail = string.Empty;
            string sqlUpdate = string.Empty;
            uint userID = 0;
            vid = 0;
            int sum = 0;
            uint status = 0;
            VipUserQuery query = new VipUserQuery();
            try
            {
                if (_edmEmailDao.GetModel(mail) == null)//郵箱不存在
                {
                    result = -1;
                    return result;
                }
                else
                {
                    result = _IEdmContentDao.CancelEdm(mail);
                    int num = updateGroupCount(mail);
                    if (result > 0 && num > 0)
                    {
                        DataTable dt = _accessMySql.getDataTable(_usersDao.GetUserIDbyEmail(mail));//判斷郵箱是否有對應的用戶id
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            userID = Convert.ToUInt32(dt.Rows[0][0]);
                        }
                        if (string.IsNullOrEmpty(userID.ToString()) || userID == 0)
                        {
                            result = -2;
                            return result;//郵箱不存在對應的用戶
                        }
                        else
                        {
                            sqlEmail = _vipUserDao.SelectEmail(userID);//根據用戶id查詢郵箱是否已經加入黑名單
                            DataTable temail = _accessMySql.getDataTable(sqlEmail);
                            uint phpTime = (uint)Common.CommonFunction.GetPHPTime();
                            #region 將取消電子報的時間和人員加入會員生活信息表(user_life)edit by shuangshuang0420j 20150814 11:00
                            //將取消電子報的時間和人員加入會員生活信息表(user_life)edit by shuangshuang0420j 20150814 11:00
                            UserLifeDao _userLifeDao = new UserLifeDao(conn);
                            UserLife modelUF = new UserLife();
                            modelUF = _userLifeDao.GetSingle(userID, "cancel_edm_time");
                            if (modelUF != null)
                            {
                                _accessMySql.execCommand(_userLifeDao.UpdateEdmTime(userID, 0, (int)update_id));
                            }
                            _accessMySql.execCommand(_userLifeDao.UpdateEdmTime(userID, phpTime, (int)update_id));
                            #endregion
                            if (temail != null && temail.Rows.Count > 0)
                            {
                                sum = Convert.ToInt32(temail.Rows[0][0]);
                            }
                            if (sum > 0)
                            {
                                status = Convert.ToUInt32(temail.Rows[0][1]);
                                vid = Convert.ToUInt32(temail.Rows[0][2]);
                                if (status == 0)
                                {
                                    result = -3;
                                    return result;//該用戶已加入黑名單且狀態為解鎖
                                }
                                else if (status == 1)
                                {
                                    result = -4;
                                    return result;//該用戶已加入黑名單,狀態為鎖定
                                }
                            }
                            else
                            {
                                query.vuser_email = mail;
                                query.v_id = userID;
                                query.create_id = update_id;
                                query.update_id = update_id;
                                query.createdate = phpTime;
                                query.updatedate = phpTime;
                                sqlUpdate = _vipUserDao.UpdateBlackList(query);
                                result = _accessMySql.execCommand(sqlUpdate);
                                return result;
                            }

                        }
                    }
                    else
                    {
                        result = -5;
                    }
                    return result;
                }


            }
            catch (Exception ex)
            {

                throw new Exception("EdmContentMgr-->CancelEdm-->" + ex.Message, ex);
            }
        }
Exemple #14
0
        public HttpResponseBase ExportFile()
        {
            string json = string.Empty;
            int totalCount = 0;
            _vipuserMgr = new VipUserMgr(mySqlConnectionString);
            try
            {
                DataTable store = new DataTable();
                VipUserQuery query = new VipUserQuery();
                query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
                query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
                if (!string.IsNullOrEmpty(Request.Params["searchState"]))
                {
                    query.search_state = Convert.ToInt32(Request.Params["searchState"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["search_text"]))
                {
                    query.serchtype = Request.Params["search_text"].ToString();
                }
                if (!string.IsNullOrEmpty(Request.Params["start_date"]))
                {
                    query.start = Convert.ToDateTime(Request.Params["start_date"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["end_date"]))
                {
                    query.end = Convert.ToDateTime(Request.Params["end_date"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["source"]))
                {
                    query.source = Convert.ToUInt32(Request.Params["source"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["uid"]))
                {
                    query.User_Id = Convert.ToUInt32(Request.Params["uid"]);
                }
                query.IsPage = false;
                DataTable dtHZ = new DataTable();
                string newExcelName = string.Empty;
                dtHZ.Columns.Add("黑名單編號", typeof(int));
                dtHZ.Columns.Add("會員郵箱", typeof(String));
                dtHZ.Columns.Add("添加來源", typeof(String));
                dtHZ.Columns.Add("狀態", typeof(String));
                dtHZ.Columns.Add("會員編號", typeof(int));
                dtHZ.Columns.Add("創建者", typeof(String));
                dtHZ.Columns.Add("列入黑名單日期", typeof(String));
                dtHZ.Columns.Add("修改者", typeof(String));
                dtHZ.Columns.Add("修改時間", typeof(String));
                store = _vipuserMgr.GetBlackList(query, out totalCount);
                foreach (DataRow dr_v in store.Rows)
                {
                    DataRow dr = dtHZ.NewRow();
                    dr[0] = dr_v["v_id"].ToString();
                    dr[1] = dr_v["user_email"].ToString();
                    dr[2] = dr_v["source"].ToString() == "2" ? "客服" : "用戶";
                    dr[3] = dr_v["status"].ToString() == "0" ? "解除" : "鎖定";
                    dr[4] = dr_v["user_id"].ToString();
                    dr[5] = dr_v["createUsername"].ToString();
                    dr[6] = dr_v["create"].ToString();
                    dr[7] = dr_v["updateUsername"].ToString();
                    dr[8] = dr_v["updatedate"].ToString();
                    dtHZ.Rows.Add(dr);
                }
                string[] colname = new string[dtHZ.Columns.Count];
                string filename = "BlackList" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                newExcelName = Server.MapPath(excelPath_export) + filename;
                for (int i = 0; i < dtHZ.Columns.Count; i++)
                {
                    colname[i] = dtHZ.Columns[i].ColumnName;
                }

                if (System.IO.File.Exists(newExcelName))
                {
                    System.IO.File.Delete(newExcelName);
                }
                ExcelHelperXhf.ExportDTtoExcel(dtHZ, "", newExcelName);
                json = "{success:true,ExcelName:\'" + 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);
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Exemple #15
0
        /// <summary>
        /// 更改狀態 啟用或者禁用
        /// </summary>
        /// <returns></returns>
        public JsonResult UpdateState()
        {
            uint id = 0;
            uint activeValue = 0;
            _vipuserMgr = new VipUserMgr(mySqlConnectionString);
            VipUserQuery query = new VipUserQuery();
            if (!string.IsNullOrEmpty(Request.Params["id"]))
            {
                id = Convert.ToUInt32(Request.Params["id"]);
            }
            if (!string.IsNullOrEmpty(Request.Params["active"]))
            {
                activeValue = Convert.ToUInt32(Request.Params["active"]);
            }
            query.v_id = id;

            if (!string.IsNullOrEmpty(Request.Params["uid"]))
            {
                query.user_id = Convert.ToUInt32(Request.Params["uid"]);
            }
            query.status = activeValue;
            query.update_id = Convert.ToUInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
            if (_vipuserMgr.UpdateState(query) > 0)
            {
                return Json(new { success = "true", msg = "1" });
            }
            else
            {
                return Json(new { success = "false", msg = "" });
            }

        }
Exemple #16
0
        public DataTable GetBlackList(VipUserQuery query, out int totalCount)
        {
            query.Replace4MySQL();
            StringBuilder strSql = new StringBuilder();
            StringBuilder count = new StringBuilder();
            totalCount = 0;
            try
            {
                strSql.AppendFormat(@"SELECT vu.v_id,vu.user_email,vu.user_id,source,status,(SELECT user_username FROM manage_user WHERE manage_user.user_id=vu.create_id ) as createUsername,(SELECT user_username FROM manage_user WHERE manage_user.user_id=vu.update_id ) as updateUsername,FROM_UNIXTIME(updatedate) as updatedate,FROM_UNIXTIME(createdate) as 'create' FROM vip_user vu  WHERE vu.group_id=48 ");
                count.AppendFormat(@"SELECT count(v_id) as totalCount FROM vip_user where group_id=48");
                if (query.search_state != 2)
                {
                    strSql.AppendFormat(" and status={0}", query.search_state);
                    count.AppendFormat(" and status={0}", query.search_state);
                }
                if (!string.IsNullOrEmpty(query.serchtype))
                {
                    strSql.AppendFormat(" AND vu.user_email like N'%{0}%'", query.serchtype);
                    count.AppendFormat(" AND user_email like N'%{0}%'", query.serchtype);
                }
                if (query.start != DateTime.MinValue && query.end != DateTime.MinValue)
                {
                    strSql.AppendFormat(" AND createdate BETWEEN '{0}' AND '{1}'", CommonFunction.GetPHPTime(query.start.ToString()), CommonFunction.GetPHPTime(query.end.ToString()));
                    count.AppendFormat(" AND createdate BETWEEN '{0}' AND '{1}'", CommonFunction.GetPHPTime(query.start.ToString()), CommonFunction.GetPHPTime(query.end.ToString()));
                }
                if (query.source != 0)
                {
                    if (query.source == 1)
                    {
                        strSql.AppendFormat(" AND source !=2 ");
                        count.AppendFormat(" AND source!=2");
                    }
                    else {
                        strSql.AppendFormat(" AND source={0}", query.source);
                        count.AppendFormat(" AND source={0}", query.source);
                    }
                    
                }
                if (query.User_Id != 0)
                {
                    strSql.AppendFormat(" and vu.user_id={0}", query.User_Id);
                    count.AppendFormat(" and user_id={0}", query.User_Id);
                }
                if (query.IsPage)
                {
                    DataTable dt = _dbAccess.getDataTable(count.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(dt.Rows[0][totalCount]);
                    }
                    strSql.AppendFormat(" order by v_id limit {0},{1} ", query.Start, query.Limit);
                }
                DataTable store = _dbAccess.getDataTable(strSql.ToString());
                return store;
            }
            catch (Exception ex)
            {

                throw new Exception("VipUserDao-->GetBlackList-->" + ex.Message + strSql.ToString(), ex);
            }
        }
Exemple #17
0
 public int DeleVipUser(VipUserQuery query)//add by chaojie1124j 添加于2015/9/21用於實現刪除會員至群組
 {
     try
     {
         return _vipUserDao.DeleVipUser(query);
     }
     catch (Exception ex)
     {
         throw new Exception("VipUserMgr-->DeleVipUser-->" + ex.Message, ex);
     }
 }
Exemple #18
0
        /// <summary>
        ///根據group_id獲取群組的組員列表
        /// </summary>
        /// <param name="vu"></param>
        /// <param name="totalCount"></param>
        /// <param name="group_id"></param>
        /// <returns></returns>
        public List<VipUserQuery> GetVipUserList(VipUserQuery vu, out int totalCount)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendFormat(@"select v.v_id,v.user_email as vuser_email,v.user_id ,v.status,v.group_id,emp_id, ");
                sql.AppendFormat(@"v.createdate,u.user_password,u.user_name,u.user_email,u.user_gender,");
                sql.AppendFormat(@"concat(u.user_birthday_year,'/',u.user_birthday_month,'/',u.user_birthday_day) as birthday,");
                sql.AppendFormat(@"u.user_mobile,u.user_phone,u.user_zip,u.user_address,u.user_reg_date,u.user_source,  ");
                sql.AppendFormat(@"case u.user_type WHEN  1 then '網絡會員'  when 2 then '電話會員' ELSE  '賣場(通路)代表會員' end as mytype ,u.send_sms_ad,u.paper_invoice,u.adm_note ");
                sql.AppendFormat(@" from vip_user v left join users u on v.user_id =u.user_id where 1=1 ");
                sql.AppendFormat(@" and v.group_id={0}", vu.group_id);
                totalCount = 0;
                try
                {
                    if (!string.IsNullOrEmpty(vu.serchtype))
                    {
                        if (vu.serchtype.ToString() == "0")
                        {
                            sql.Append(" ");
                        }
                        else if (vu.serchtype.ToString() == "1")
                        {
                            sql.AppendFormat(" and v.user_email like'%{0}%' ", vu.content.Trim());
                        }
                        else if (vu.serchtype.ToString() == "2")//by zhaozhi0623j添加,用於會員管理--會員群組管理群組會員的查詢
                        {
                            sql.AppendFormat(" and u.user_name like'%{0}%' ", vu.content.Trim());
                        }
                        sql.Append("order by u.user_id");//by zhaozhi0623j添加
                    }
                    else
                    {
                        sql.AppendFormat(" ");
                    }
                }
                catch
                {
                    sql.Append(" ");
                }

                if (vu.IsPage)
                {
                    System.Data.DataTable dt = _accessMySql.getDataTable(sql.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        totalCount = dt.Rows.Count;
                    }
                    sql.AppendFormat(" limit {0},{1}", vu.Start, vu.Limit);

                }
                return _accessMySql.getDataTableForObj<VipUserQuery>(sql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("VipUserGroupDao.GetVipUserList-->" + ex.Message + sql.ToString(), ex);
            }

        }