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;
}
/*修改會員狀態，啟用或者禁用 */
#region 修改會員狀態，啟用或者禁用
public JsonResult UpdateUserState()
{
int id = Convert.ToInt32(Request.Params["id"]);
int activeValue = Convert.ToInt32(Request.Params["active"]);
_userGroupMgr = new VipUserGroupMgr(mySqlConnectionString);
if (_userGroupMgr.UpdateUserState(activeValue, id) > 0)
{
return Json(new { success = "true", msg = "" });
}
else
{
return Json(new { success = "false", msg = "" });
}
}
// [HttpPost]
public HttpResponseBase SaveVipUserGroup()
{
NameValueCollection param = Request.Params;
VipUserGroup userGroup = new VipUserGroup();
string json = string.Empty;
bool size = true;
Serial serial = new Serial();
_userGroupMgr = new VipUserGroupMgr(mySqlConnectionString);
_ISerImplMgr = new SerialMgr(mySqlConnectionString);
serial = _ISerImplMgr.GetSerialById(72);
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_MinValue");
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 + promoPath;//圖片存儲地址
Random rand = new Random();
int newRand = rand.Next(1000, 9999);
string NewName = string.Empty;//當前文件名
string fileExtention = string.Empty;//當前文件的擴展名
string NewFileName = string.Empty;
FileManagement fileLoad = new FileManagement();
try
{
#region 新增
if (String.IsNullOrEmpty(param["group_id"]))
{
if (!string.IsNullOrEmpty(Request.Form["group_name"]))
{
userGroup.group_name = Request.Form["group_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Form["tax_id"]))
{
userGroup.tax_id = Request.Form["tax_id"].ToString();
}
if (!string.IsNullOrEmpty(Request.Form["eng_name"]))
{
userGroup.eng_name = Request.Form["eng_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Form["gift_bonus"]))
{
userGroup.gift_bonus = Convert.ToUInt32(Request.Form["gift_bonus"]);
}
if (!string.IsNullOrEmpty(Request.Form["group_category"]))
{
userGroup.group_category = Convert.ToUInt32(Request.Form["group_category"]);
}
siteConfigMgr = new SiteConfigMgr(Server.MapPath(xmlPath));
try
{
if (Request.Files["image_name"] != null && Request.Files["image_name"].ContentLength > 0)
{
HttpPostedFileBase file = Request.Files["image_name"];
if (file.ContentLength > int.Parse(minValue) * 1024 && file.ContentLength < int.Parse(maxValue) * 1024)
{
NewName = Path.GetFileName(file.FileName);
bool result = false;
//獲得文件的後綴名
fileExtention = NewName.Substring(NewName.LastIndexOf(".")).ToLower();
//新的文件名是隨機數字
BLL.gigade.Common.HashEncrypt hash = new BLL.gigade.Common.HashEncrypt();
NewFileName = hash.Md5Encrypt(newRand.ToString(), "32") + fileExtention;
NewName = NewFileName;
string ServerPath = string.Empty;
//判斷目錄是否存在，不存在則創建
string[] mapPath = new string[1];
mapPath[0] = promoPath.Substring(1, promoPath.Length - 2);
string jian = localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1);
CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1), mapPath);
NewFileName = localPromoPath + NewFileName;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + promoPath);
string ErrorMsg = string.Empty;
//上傳
result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)
{
userGroup.image_name = NewName;
}
}
else
{
size = false;
}
}
}
catch (Exception)
{
userGroup.image_name = string.Empty;
}
if (!string.IsNullOrEmpty(Request.Params["check_iden"]))
{
userGroup.check_iden = int.Parse(Request.Params["check_iden"]);
}
userGroup.createdate = (uint)CommonFunction.GetPHPTime(DateTime.Now.ToString());
userGroup.group_id = uint.Parse((serial.Serial_Value + 1).ToString());
if (_userGroupMgr.Insert(userGroup) > 0)
{
serial.Serial_Value = serial.Serial_Value + 1;/*所在操作表的列增加*/
_ISerImplMgr.Update(serial);/*修改所在的表的列對應的值*/
if (size)
{
json = "{success:true,msg:\"" + "" + "\"}";
}
else
{
json = "{success:true,msg:\"" + " 文件大小應大於1KB小於100KB！" + "\"}";
}
}
else
{
json = "{success:false,msg:\"" + "新增失敗!" + "\"}";
}
}
#endregion
#region 編輯
else
{
_userGroupMgr = new VipUserGroupMgr(mySqlConnectionString);
userGroup.group_id = Convert.ToUInt32(param["group_id"]);
VipUserGroup oldUserGroup = _userGroupMgr.GetModelById(userGroup.group_id);
if (!string.IsNullOrEmpty(Request.Form["group_name"]))
{
userGroup.group_name = Request.Form["group_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Form["tax_id"]))
{
userGroup.tax_id = Request.Form["tax_id"].ToString();
}
if (!string.IsNullOrEmpty(Request.Form["eng_name"]))
{
userGroup.eng_name = Request.Form["eng_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Form["gift_bonus"]))
{
userGroup.gift_bonus = Convert.ToUInt32(Request.Form["gift_bonus"]);
}
if (!string.IsNullOrEmpty(Request.Form["group_category"]))
{
userGroup.group_category = Convert.ToUInt32(Request.Form["group_category"]);
}
try
{
//如果圖片沒有改變
if (Request.Form["image_name"] == oldUserGroup.image_name)
{
userGroup.image_name = Request.Form["image_name"];
}
else
{
//圖片改變了
if (Request.Files["image_name"] != null && Request.Files["image_name"].ContentLength > 0)
{
HttpPostedFileBase file = Request.Files["image_name"];
if (file.ContentLength > int.Parse(minValue) * 1024 && file.ContentLength < int.Parse(maxValue) * 1024)
{
NewName = Path.GetFileName(file.FileName);
bool result = false;
//獲得文件的後綴名
fileExtention = NewName.Substring(NewName.LastIndexOf(".")).ToLower();
//新的文件名是隨機數字
BLL.gigade.Common.HashEncrypt hash = new BLL.gigade.Common.HashEncrypt();
NewFileName = hash.Md5Encrypt(newRand.ToString(), "32") + fileExtention;
NewName = NewFileName;
string ServerPath = string.Empty;
//判斷目錄是否存在，不存在則創建
string[] mapPath = new string[1];
mapPath[0] = promoPath.Substring(1, promoPath.Length - 2);
string jian = localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1);
CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1), mapPath);
// returnName += promoPath + NewFileName;
NewFileName = localPromoPath + NewFileName;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + promoPath);
string ErrorMsg = string.Empty;
//上傳之前刪除已有的圖片
string oldFileName = oldUserGroup.image_name;
//FTP ftp = new FTP(localPromoPath, ftpuser, ftppwd);
//List<string> tem = ftp.GetFileList();
//上傳
result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)
{
userGroup.image_name = NewName;
if (System.IO.File.Exists(ServerPath + oldFileName))
{
FTP ftps = new FTP(localPromoPath + oldFileName, ftpuser, ftppwd);
if (System.IO.File.Exists(localPromoPath + oldFileName))
{
ftps.DeleteFile(localPromoPath + oldFileName);//刪除ftp:71.159上的舊圖片
}
DeletePicFile(ServerPath + oldFileName);//刪除本地圖片
}
}
else
{
userGroup.image_name = oldUserGroup.image_name;
}
}
else
{
size = false;
userGroup.image_name = oldUserGroup.image_name;
}
}
}
}
catch (Exception)
{
userGroup.image_name = oldUserGroup.image_name;
}
try
{
userGroup.check_iden = int.Parse(Request.Params["check_iden"]);
}
catch (Exception)
{
userGroup.check_iden = 0;
}
if (_userGroupMgr.Update(userGroup) > 0)
{
if (size)
{
json = "{success:true,msg:\"" + "" + "\"}";
}
else
{
json = "{success:true,msg:\"" + " 文件大小應大於1KB小於100KB！" + "\"}";
}
}
else
{
json = "{success:false,msg:\"" + "修改失敗!" + "\"}";
}
}
#endregion
}
catch (Exception ex)
{
json = "{success:false,msg:\"" + "異常" + "\"}";
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;
}
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;
}
public HttpResponseBase GetVipUserGroupList()
{
/********************************************************/
List<VipUserGroupQuery> stores = new List<VipUserGroupQuery>();
string json = string.Empty;
try
{
VipUserGroupQuery query = new VipUserGroupQuery();
int totalCount = 0;
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
_userGroupMgr = new VipUserGroupMgr(mySqlConnectionString);
string group_id_or_group_name = Request.Params["group_id_or_group_name"];
string gName = string.Empty;
string gNameSubString = string.Empty;
char[] specialChar = { '[', '_', '%' };
int n = 0;
//if (!string.IsNullOrEmpty(Request.Params["dateOne"]))
//{
// query.create_dateOne = (uint)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["dateOne"]).ToString("yyyy-MM-dd 00:00:00"));
//}
//if (!string.IsNullOrEmpty(Request.Params["dateTwo"]))
//{
// query.create_dateTwo = (uint)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["dateTwo"]).ToString("yyyy-MM-dd 23:59:59"));
//}
//用於判斷是查詢條件是群組編號/群組名稱，
// by zhaozhi0623j，2015/09/22
if (!string.IsNullOrEmpty(group_id_or_group_name))
{
uint result = 0;
if (uint.TryParse(group_id_or_group_name, out result))
{
query.group_id = result;
}
//查詢條件為群組名稱時，判斷字符串中是否含有"%","_","[" 如有為其前方添加轉意符號"\",便於查詢
else
{
query.group_name = group_id_or_group_name;
gName = group_id_or_group_name;
n = gName.IndexOfAny(specialChar);
if (n >= 0)
{
gNameSubString = gName.Substring(n, gName.Length - n);
query.group_name = gName.Replace(gNameSubString, "\\" + gNameSubString);
}
}
}
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
string ip = string.Empty;
if (addlist.Length > 0)
{
ip = addlist[0].ToString();
}
stores = _userGroupMgr.QueryAll(query, out totalCount);
foreach (var item in stores)
{
if (item.image_name != "")
{
item.image_name = imgServerPath + promoPath + item.image_name;
}
else
{
item.image_name = defaultImg;
}
item.screatedate = CommonFunction.GetNetTime(item.createdate).ToString("yyyy/MM/dd");
item.list = _userGroupMgr.GetVuserCount(item);
item.ip = ip;
}
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;
}
