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); } }
public int InsertVipUser(VipUser vu) { try { return _vipUserGroup.InsertVipUser(vu); } catch (Exception ex) { throw new Exception("VipUserGroupMgr.InsertVipUser-->" + ex.Message, ex); } }
public string SaveVipUserGroup(VipUserGroupQuery query,DataTable _dt) { string json = "{success:'true'}"; ArrayList arrList = new ArrayList(); VipUser vQuery = new VipUser(); BtobEmp bQuery=new BtobEmp (); int totalCount=0; VipUserGroupQuery oldQuery = new VipUserGroupQuery(); oldQuery = _vipUserGroupDao.GetVipUserGList(query, out totalCount).FirstOrDefault(); try { if (query.file_name == "" && oldQuery != null) { query.file_name = oldQuery.file_name; } query.m_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; query.group_committe_promotion = query.group_committe_promotion + query.group_committe_other; arrList.Add(_vipUserGroupDao.SaveVipUserGroup(query)); bQuery.group_id = query.group_id.ToString(); //excel檔匯入到btob_emp表 #region if (_dt != null) { for (int i = 0; i < _dt.Rows.Count; i++) { if (_dt.Rows[i][0].ToString() != "") { bQuery.emp_id = _dt.Rows[i][0].ToString(); bQuery.id = Convert.ToUInt32(_vipUserGroupDao.GetIdFromBtob(bQuery)); if (bQuery.id != 0) { arrList.Add(_vipUserGroupDao.GetUpdateBtobSql(bQuery)); } else { //emp_id是唯一索引,員工編號在整張表中是唯一的 if (!_vipUserGroupDao.IsEmpIdExist(bQuery)) { arrList.Add(_vipUserGroupDao.GetInsertBtobSql(bQuery)); } } } } } else { } #endregion if (arrList.Count > 0) { if (_vipUserGroupDao.execSql(arrList)) { json = "{success:'true'}"; } else { json = "{success:'false'}"; } } return json; } catch (Exception ex) { throw new Exception("VipUserGroupDao-->SaveVipUserGroup-->" + ex.Message, ex); } }
public DataTable GetVipUser(VipUser vu) { try { return _vipUserGroup.GetVipUser(vu); } catch (Exception ex) { throw new Exception("VipUserGroupMgr.GetVipUser-->" + ex.Message, ex); } }
public HttpResponseBase ImportCsv() { string json = ""; string errorMsg = ""; string repeatMsg = ""; _userGroupMgr = new VipUserGroupMgr(mySqlConnectionString); string sqlwhere = string.Empty; VipUser query = new VipUser(); int add = 0; int repeat = 0; int error = 0; string newCSVName = string.Empty; string check_iden = string.Empty; List<string> emp_id = new List<string>(); if (!string.IsNullOrEmpty(Request.Params["group_id"])) { query.group_id = Convert.ToUInt32(Request.Params["group_id"]); } if (!string.IsNullOrEmpty(Request.Params["check_iden"])) { check_iden = Request.Params["check_iden"]; } siteConfigMgr = new SiteConfigMgr(Server.MapPath(xmlPath)); try { if (Request.Files["ImportCsvFile"] != null && Request.Files["ImportCsvFile"].ContentLength > 0) { //一些文件的方法,包括获取文件的类型、长度、保存 HttpPostedFileBase excelFile = Request.Files["ImportCsvFile"]; //自定义的类,其中包括一些文件的处理方法 FileManagement fileManagement = new FileManagement(); //文件重命名,然後設置存儲的路徑 newCSVName = Server.MapPath(excelPath) + "user_io" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名 System.Data.DataTable _dt = new DataTable(); excelFile.SaveAs(newCSVName);// _dt = CsvHelper.ReadCsvToDataTable(newCSVName, true); //匯入員工編號 DataTable btopEmp = _userGroupMgr.BtobEmp(query.group_id.ToString()); for (int k = 0; k < btopEmp.Rows.Count; k++) { if (!string.IsNullOrEmpty(btopEmp.Rows[k]["emp_id"].ToString())) { emp_id.Add(btopEmp.Rows[k]["emp_id"].ToString()); } } //郵箱的格式 Regex num = new System.Text.RegularExpressions.Regex(@"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"); for (int i = 0; i < _dt.Rows.Count; i++) { //只讀第一列,其餘列忽略 for (int j = 0; j < 1; j++) { //匯入員工編號 if (!string.IsNullOrEmpty(check_iden)) { int u1 = _userGroupMgr.UpdateEmp(query.group_id.ToString(), _dt.Rows[i][j].ToString(), 1); if (emp_id.Contains(_dt.Rows[i][j].ToString())) { int u2 = _userGroupMgr.UpdateEmp(query.group_id.ToString(), _dt.Rows[i][j].ToString(), 2); json = "{success:true,msg:\"" + "上傳成功!" + "\"}"; } else { int h = _userGroupMgr.InsertEmp(query.group_id.ToString(), _dt.Rows[i][j].ToString()); json = "{success:true,msg:\"" + "上傳成功!" + "\"}"; } } //匯入郵箱 else { //郵箱的格式驗證 if (!num.IsMatch(_dt.Rows[i][j].ToString())) { errorMsg += "<br/>第" + (i + 1) + "條第" + (j + 1) + "列數據錯誤"; error++; } else { sqlwhere = _dt.Rows[i][j].ToString(); DataTable dtUser = _userGroupMgr.GetUser(sqlwhere); //判斷郵箱是否是存在于users表中 if (dtUser.Rows.Count > 0) { query.user_email = _dt.Rows[i][j].ToString(); DataTable VipUserExist = _userGroupMgr.GetVipUser(query); //根據郵箱和group_id來判斷vip_user表中是否存在此數據,若不存在,就添加,若存在,就不添加 if (VipUserExist.Rows.Count == 0) { query.vuser_email = _dt.Rows[i][j].ToString(); query.createdate = (uint)CommonFunction.GetPHPTime(DateTime.Now.ToString()); if (!string.IsNullOrEmpty(dtUser.Rows[0][0].ToString())) { query.User_Id = Convert.ToUInt32(dtUser.Rows[0][0]); } int k = _userGroupMgr.InsertVipUser(query); if (k > 0) { add++; } } else { //json = "{success:false,msg:\"" + "第" + i + 1 + "條第" + j + "列數據重複" + "\"}"; repeatMsg += "<br/>第" + (i + 1) + "條第" + (j + 1) + "列數據重複"; repeat++; } } else { //json = "{success:false,msg:\"" + "第" + i + 1 + "條第" + j + "列數據錯誤" + "\"}"; errorMsg += "<br/>第" + (i + 1) + "條第" + (j + 1) + "列數據錯誤"; error++; } } } } } //string[] colname = new string[_dt.Columns.Count]; //for (int i = 0; i < _dt.Columns.Count; i++) //{ // colname[i] = _dt.Columns[i].ColumnName; //} //CsvHelper.ExportDataTableToCsv(_dt, newExcelName, colname, true); json = "{success:true,msg:\"" + errorMsg + repeatMsg + "<br/>" + "上傳成功!<br/>新增:" + add + "<br/>重複:" + repeat + "<br/>錯誤:" + error + "<br/>總計:" + _dt.Rows.Count + "\"}"; } 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); //出現異常后,刪除上傳的文件 if (System.IO.File.Exists(newCSVName)) { //設置文件的屬性,以防刪除文件的時候因為文件的屬性造成無法刪除 System.IO.File.SetAttributes(newCSVName, FileAttributes.Normal); System.IO.File.Delete(newCSVName); } json = "{success:false,msg:\"" + errorMsg + repeatMsg + "上傳失敗!" + "\"}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
//public int ExistEmail(VipUserGroupQuery query) //{ // StringBuilder sql = new StringBuilder(); // try // { // query.Replace4MySQL(); // sql.AppendFormat("select group_committe_mail from vip_user_group where group_committe_mail='{0}' and group_committe_chairman !='' ;", query.group_committe_mail); // DataTable _dt = _accessMySql.getDataTable(sql.ToString()); // if (_dt != null && _dt.Rows.Count > 0) // { // return 1; // } // else // { // return 0; // } // } // catch (Exception ex) // { // throw new Exception("VipUserGroupDao->ExistEmail->" + sql.ToString() + ex.Message, ex); // } //} public int VIPUserExistEmail(VipUser query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat("select user_email from vip_user where group_id='{0}' and user_email ='{1}';", query.group_id,query.user_email); DataTable _dt = _accessMySql.getDataTable(sql.ToString()); if (_dt != null && _dt.Rows.Count > 0) { return 1; } else { return 0; } } catch (Exception ex) { throw new Exception("VipUserGroupDao->VIPUserExistEmail->" + sql.ToString() + ex.Message, ex); } }
/// <summary> /// 向vip_user中插入數據 /// </summary> /// <param name="vu"></param> /// <param name="sqlwhere"></param> /// <returns></returns> public int InsertVipUser(VipUser vu) { string sql = string.Format("insert into vip_user(user_email,group_id,user_id,createdate) values('{0}',{1},{2},{3}) ", vu.vuser_email, vu.group_id, vu.User_Id, vu.createdate); try { return _accessMySql.execCommand(sql); } catch (Exception ex) { throw new Exception("VipUserGroupDao.InsertVipUser-->" + ex.Message + sql, ex); } }
/// <summary> /// 通過group_id和user_email查詢vip_user表中是否存在這一條信息 /// </summary> /// <param name="vu"></param> /// <returns></returns> public DataTable GetVipUser(VipUser vu) { string sql = string.Format("select * from vip_user where 1=1 and user_email='{0}' and group_id={1}", vu.user_email, vu.group_id); try { return _accessMySql.getDataTable(sql); } catch (Exception ex) { throw new Exception("VipUserGroupDao.GetVipUser-->" + ex.Message + sql, ex); } }