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);
}
}
