public string Export(LoggingSessionInfo pLoggingSessionInfo, string pUserID, string pCustomerID, string pPath)
{
string website_WWW = System.Configuration.ConfigurationManager.AppSettings["website_WWW"].ToString();
string customer_service_url = System.Configuration.ConfigurationManager.AppSettings["customer_service_url"].ToString();
pPath = pPath.Replace(website_WWW, "");
pPath = pPath.Replace(customer_service_url, "");
pPath = pPath.Replace("http://test.o2omarketing.cn:9100/", "");
#region 读取上传文件的excel数据
string mapPath = System.Web.HttpContext.Current.Server.MapPath("~/");
pPath = mapPath + pPath;
System.IO.FileInfo exporstFile = new System.IO.FileInfo(pPath);
if (!exporstFile.Exists)
{
return("0|文件不存在");
}
DataTable excelTable = new DataTable();
DataSet ds = new DataSet();
//Excel的连接
OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pPath + ";" + "Extended Properties=Excel 8.0;");
objConn.Open();
//DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是Sheet1$
//string strSql = "select * from [" + tableName + "]";
string tableName = "Sheet1$";
string strSql = "select * from [" + tableName + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
objConn.Close();
excelTable = ds.Tables[tableName];
//过滤空行
DataRow[] drs = excelTable.Select("邮箱<>''");
DataTable dtClone = excelTable.Clone();
foreach (var row in drs)
{
dtClone.ImportRow(row);
}
#endregion
#region 获取职衔、部门、角色
DataView _dv = dtClone.DefaultView;
DataTable jobFuncDT = _dv.ToTable(true, "职衔");
DataTable deptDT = _dv.ToTable(true, "部门");
DataTable roleDT = _dv.ToTable(true, "角色");
#endregion
#region 职衔验证
var jobFuncBll = new JobFunctionBLL(pLoggingSessionInfo);
var jobFuncList = jobFuncBll.Query(new IWhereCondition[]
{
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "CustomerID", Value = pCustomerID
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "IsDelete", Value = "0"
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "Status", Value = "1"
}
},
null);
foreach (DataRow row in jobFuncDT.Rows)
{
if (row["职衔"] == null || string.IsNullOrEmpty(row["职衔"].ToString()))
{
continue;
}
int count = jobFuncList.Count(job => job.Name == row["职衔"].ToString().Trim());
if (count <= 0)
{
return("0|职衔不存在");
}
}
#endregion
#region 部门验证
string typeID = System.Configuration.ConfigurationManager.AppSettings["TypeID"].ToString();
TUnitBLL deptBll = new TUnitBLL(pLoggingSessionInfo);
var deptList = deptBll.Query(new IWhereCondition[]
{
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "customer_id", Value = pCustomerID
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "type_id", Value = typeID
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "Status", Value = "1"
}
},
null);
foreach (DataRow row in deptDT.Rows)
{
if (row["部门"] == null || string.IsNullOrEmpty(row["部门"].ToString()))
{
continue;
}
int count = deptList.Count(dept => dept.UnitName == row["部门"].ToString().Trim());
if (count <= 0)
{
return("0|部门不存在");
}
}
#endregion
#region 角色验证
RoleService rs = new RoleService(pLoggingSessionInfo);
IList <RoleModel> roleList = rs.GetAllRoles();
foreach (DataRow row in roleDT.Rows)
{
if (row["角色"] == null || string.IsNullOrEmpty(row["角色"].ToString()))
{
continue;
}
int count = roleList.Count(role => role.Role_Name == row["角色"].ToString().Trim());
if (count <= 0)
{
return("0|角色不存在");
}
}
#endregion
return(ExportData(pCustomerID, pLoggingSessionInfo, dtClone, jobFuncList, deptList, roleList));
//return "1|成功";
}
public string Export(LoggingSessionInfo pLoggingSessionInfo, string pPath)
{
try
{
string customerID = pLoggingSessionInfo.ClientID;
string userID = pLoggingSessionInfo.CurrentUser.User_Id;
////下载保存获取本地路径
//string localPath = string.Empty;
//DownloadFile(pPath, "", out localPath);
//pPath = localPath;
string path = HttpContext.Current.Server.MapPath(@"~");
pPath = path + pPath;
#region 读取Excel数据并简单过滤
System.IO.FileInfo exporstFile = new System.IO.FileInfo(pPath);
if (!exporstFile.Exists)
{
return("0|文件不存在");
}
//读取Excel
DataTable excelTable = ExcelReader.ReadExcelToDataTable(pPath);
//return "0|" + excelTable.Rows[0][0];
//过滤空行
DataRow[] drs = excelTable.Select("邮箱<>''");
DataTable dtClone = excelTable.Clone();
foreach (var row in drs)
{
dtClone.ImportRow(row);
}
#endregion
#region 获取职衔、部门、角色
DataView _dv = dtClone.DefaultView;
DataTable jobFuncDT = _dv.ToTable(true, "职衔");
DataTable deptDT = _dv.ToTable(true, "部门");
DataTable roleDT = _dv.ToTable(true, "角色");
#endregion
#region 职衔验证
var jobFuncBll = new JobFunctionBLL(pLoggingSessionInfo);
var jobFuncList = jobFuncBll.Query(new IWhereCondition[]
{
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "CustomerID", Value = customerID
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "IsDelete", Value = "0"
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "Status", Value = "1"
}
},
null);
foreach (DataRow row in jobFuncDT.Rows)
{
if (row["职衔"] == null || string.IsNullOrEmpty(row["职衔"].ToString()))
{
continue;
}
int count = jobFuncList.Count(job => job.Name == row["职衔"].ToString().Trim());
if (count <= 0)
{
JobFunctionEntity entity = new JobFunctionEntity
{
Name = row["职衔"].ToString().Trim(),
Description = "",
JobFunctionID = Guid.NewGuid(),
Status = 1,
IsDelete = 0,
CustomerID = customerID
};
jobFuncBll.Create(entity);
//return "0|职衔不存在";
}
}
#endregion
#region 部门验证
string typeID = System.Configuration.ConfigurationManager.AppSettings["TypeID"].ToString();
TUnitBLL deptBll = new TUnitBLL(pLoggingSessionInfo);
var deptList = deptBll.Query(new IWhereCondition[]
{
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "customer_id", Value = customerID
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "type_id", Value = typeID
},
new EqualsCondition {
DateTimeAccuracy = null, FieldName = "Status", Value = "1"
}
},
null);
foreach (DataRow row in deptDT.Rows)
{
if (row["部门"] == null || string.IsNullOrEmpty(row["部门"].ToString()))
{
continue;
}
int count = deptList.Count(dept => dept.UnitName == row["部门"].ToString().Trim());
if (count <= 0)
{
TUnitEntity unitEntity = new TUnitEntity
{
UnitID = Guid.NewGuid().ToString().Replace("-", ""),
UnitName = row["部门"].ToString().Trim(),
UnitCode = "1",
UnitContact = "",
UnitRemark = "",
TypeID = typeID,
CreateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
CreateUserID = pLoggingSessionInfo.CurrentUser.User_Id,
ModifyTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
ModifyUserID = pLoggingSessionInfo.CurrentUser.User_Id,
CustomerID = pLoggingSessionInfo.CurrentUser.customer_id,
CUSTOMERLEVEL = 0,
Status = "1",
StatusDesc = "正常",
IfFlag = "1"
};
deptBll.Create(unitEntity);
//return "0|部门不存在";
}
}
#endregion
#region 角色验证
RoleService rs = new RoleService(pLoggingSessionInfo);
IList <RoleModel> roleList = rs.GetAllRoles();
foreach (DataRow row in roleDT.Rows)
{
if (row["角色"] == null || string.IsNullOrEmpty(row["角色"].ToString()))
{
continue;
}
int count = roleList.Count(role => role.Role_Name == row["角色"].ToString().Trim());
if (count <= 0)
{
return("0|角色不存在");
}
}
#endregion
return(ExportData(customerID, pLoggingSessionInfo, dtClone, jobFuncList, deptList, roleList));
}
catch (Exception ex)
{
return("0|" + ex.Message);
}
}
