public string uploadCSSHOPInfo(string filePath)
{
string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\出售商户模板.xls"; //原始文件
string path = filePath; //原始文件
string mes = "";
DataTable dt = new DataTable();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
if (dt == null || dt.Rows.Count == 0)
{
return("空数据，导入失败！");
}
try
{
string b = db.UpLoadCSShopInfo(dt);
if (b == "")
{
return("");
}
else
{
return(b);
}
}
catch (Exception e)
{
return(e.Message);
}
}
public string UploadHouseInfo(string filePath, Dictionary <string, object> userinfo)
{
Dictionary <string, object> r = new Dictionary <string, object>();
List <string> list = new List <string>();
string modePath = System.IO.Directory.GetCurrentDirectory() + "/WY_API/ExcelModel/房屋档案表模板.xls";//原始文件
modePath = modePath.Replace("//", "/");
string path = filePath;//原始文件
string mes = "";
DataTable dt = new DataTable();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
if (dt == null || dt.Rows.Count == 0)
{
return("空数据，导入失败！");
}
try
{
string b = db.UpLoadHouseInfo(dt, userinfo);
if (b == "")
{
return("");
}
else
{
return(b);
}
}
catch (Exception e)
{
return(e.Message);
}
}
[Consumes("multipart/form-data")]//此处为新增
public IActionResult UploadExcelFiles([FromForm] IFormCollection formCollection)
{
Dictionary <string, object> r = new Dictionary <string, object>();
try
{
FormFileCollection fileCollection = (FormFileCollection)formCollection.Files;
IFormFileCollection dd = Request.Form.Files;
IFormFile file = fileCollection[0];
string path = file.FileName;
//HttpFileCollection
Stream aa = file.OpenReadStream();
Stream BB = dd[0].OpenReadStream();
string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\组织结构模板.xlsx";//原始文件
string mes = "";
DataTable dt = new DataTable();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(aa, path, modePath, ref mes, ref dt);
return(Json(r));
}
catch (Exception ex)
{
r["code"] = -1;
r["message"] = ex.Message;
}
return(Json(r));
}
//public string truck(int PageSize, List<string> lst)
//{
// string str = "";
// try
// {
// int truckNum = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(lst.Count / PageSize)));
// for (int PageIndex = 1; PageIndex <= truckNum; PageIndex++)
// {
// int rowbegin = (PageIndex - 1) * PageSize;
// int rowend = PageIndex * PageSize;
// List<string> tempLst = new List<string>();
// if (rowend > lst.Count)
// rowend = lst.Count;
// for (int i = rowbegin; i < rowend; i++)
// {
// tempLst.Add(lst[i]);
// }
// str+=db.UploadOrgFileList(tempLst);
// }
// return str;
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
//}
public string UploadOrgFile(string filePath)
{
string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\组织结构模板.xlsx"; //原始文件
string path = filePath; //原始文件
string mes = "";
DataTable dt = new DataTable();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
if (dt == null || dt.Rows.Count == 0)
{
return("空数据，导入失败！");
}
DataView dv = new DataView(dt);
if (dt.Rows.Count != dv.ToTable(true, "组织机构编码").Rows.Count)
{
return("组织机构编码存在重复数据，导入失败！");
}
List <string> list = new List <string>();
foreach (DataRow row in dt.Rows)
{
StringBuilder sb = new StringBuilder("insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK,DW_CODE) values ");
sb.Append("('" + Guid.NewGuid().ToString() + "',");
sb.Append("'" + getString(row["组织机构编码"]) + "',");
sb.Append("'" + getString(row["组织机构名称"]) + "',");
sb.Append("'" + getString(row["组织机构简称"]) + "',");
sb.Append("'" + getString(row["上级组织机构编码"]) + "',");
if (row["是否有效"] != null && row["是否有效"].ToString() == "是")
{
sb.Append("'1',");
}
else
{
sb.Append("'0',");
}
sb.Append("'1',");
sb.Append("'" + getString(row["备注"]) + "','");
sb.Append(getString(row["单位编码"]) + "')");
list.Add(sb.ToString());
}
return(db.UploadOrgFileList(list));
}
public string UploadUserFile(string filePath)
{
List <string> list = new List <string>();
string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\用户.xlsx"; //原始文件
string path = filePath; //原始文件
string mes = "";
DataTable dt = new DataTable();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
if (dt == null || dt.Rows.Count == 0)
{
return("空数据，导入失败！");
}
//DataView dv = new DataView(dt);
//String[] str = { "组织机构编码", "组织机构名称", "账号", "姓名", "员工编号", "性别", "办公电话", "手机", "邮箱", "访问IP", "账号类型","备注" };
//dt = dv.ToTable(true, str);
//if (dt.Rows.Count != dv.ToTable(true, "账号").Rows.Count)
//{
// return "账号列存在重复数据，导入失败！";
//}
string error = GetDistinctSelf(dt, "账号");
if (error != null && error.Length > 0)
{
return(error);
}
int truckNum = Convert.ToInt32(Convert.ToDecimal(dt.Rows.Count / 500));
int yushu = dt.Rows.Count % 500;
if (yushu > 0)
{
truckNum++;
}
for (int j = 1; j < truckNum + 1; j++)
{
string fengefu = "";
StringBuilder sb = new StringBuilder();
StringBuilder sbOrgUser = new StringBuilder();
sbOrgUser.Append("insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
sb.Append(" INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
"USER_EMAIL,USER_IP,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,REMARK) values ");
OrgDB orgDB = new OrgDB();
DataTable dtOrg = orgDB.fetchOrgList();
string result = "";
string fengefu2 = "";
int rowbegin = (j - 1) * 500;
int rowend = j * 500;
if (rowend > dt.Rows.Count)
{
rowend = dt.Rows.Count;
}
for (int i = rowbegin; i < rowend; i++)
{
if (dt.Rows[i]["组织机构编码"] == null || dt.Rows[i]["账号"] == null)
{
result += fengefu2 + "第" + (i + 2) + "行，组织机构编码或者账号不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
if (dt.Rows[i]["组织机构编码"].ToString() == "" || dt.Rows[i]["账号"].ToString() == "")
{
result += fengefu2 + "第" + (i + 2) + "行，组织机构编码或者账号不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
DataRow[] OrgRow = dtOrg.Select("ORG_CODE='" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "'");
if (OrgRow.Length <= 0)
{
result += fengefu2 + "第" + (i + 2) + "行，系统中不存在此组织机构编码！，导入失败！";
fengefu2 = ",";
continue;
}
string id = Guid.NewGuid().ToString();
sbOrgUser.Append(fengefu + "('" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "','" + id + "')");
sb.Append(fengefu + "('" + id + "',");
sb.Append("'" + getString(dt.Rows[i]["账号"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["员工编号"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["姓名"]) + "',");
sb.Append("'123456',");
sb.Append("'" + getString(dt.Rows[i]["手机"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["办公电话"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["邮箱"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["访问IP"]) + "',");
if (dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男")
{
sb.Append("1,");
}
else
{
sb.Append("0,");
}
if (dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号")
{
sb.Append("'1',");
}
else
{
sb.Append("'0',");
}
sb.Append("1,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
sb.Append("'" + getString(dt.Rows[i]["备注"]) + "')");
fengefu = ",";
}
if (sbOrgUser != null && sbOrgUser.Length > 0)
{
list.Add(sbOrgUser.ToString());
}
if (sb != null && sb.Length > 0)
{
list.Add(sb.ToString());
}
}
//string sqlUpdate = " update a set a.ORG_ID=b.ORG_ID from ts_uidp_org_user a ,ts_uidp_org b where a.ORG_ID=b.ORG_CODE ";
//string sqlUpdate = "update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
if (db.GetDBType() == "MYSQL")
{
string sqlUpdate = " update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
list.Add(sqlUpdate);
}
else if (db.GetDBType() == "SQLSERVER")
{
string sqlUpdate = " update a set a.ORG_ID=b.ORG_ID from ts_uidp_org_user a ,ts_uidp_org b where a.ORG_ID=b.ORG_CODE ";
list.Add(sqlUpdate);
}
else if (db.GetDBType() == "ORACLE")
{
string sqlUpdate = " update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
list.Add(sqlUpdate);
}
return(db.UploadUserFile(list));
}
public string UploadUserFileNew(string filePath)
{
//string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\用户.xlsx";//原始文件
//string path = filePath;//原始文件
//string mes = "";
//
//DataTable dt = new DataTable();
//UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
//tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
List <string> list = new List <string>();
string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\用户.xls"; //原始文件
string path = filePath; //原始文件
string mes = "";
string result = "";
DataTable dt = new DataTable();
DataTable userdt = db.fetchUserList();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
if (dt == null || dt.Rows.Count == 0)
{
return("空数据，导入失败！");
}
string error = GetDistinctSelf(dt, "账号");
//if (error != null && error.Length > 0)
//{
// return error;
//}
int truckNum = Convert.ToInt32(Convert.ToDecimal(dt.Rows.Count / 500));
int yushu = dt.Rows.Count % 500;
if (yushu > 0)
{
truckNum++;
}
for (int j = 1; j < truckNum + 1; j++)
{
string fengefu = "";
//StringBuilder sb = new StringBuilder();
//StringBuilder sbOrgUser = new StringBuilder();
//sbOrgUser.Append("insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
//sb.Append(" INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
// "USER_EMAIL,USER_IP,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,REMARK) values ");
OrgDB orgDB = new OrgDB();
DataTable dtOrg = orgDB.fetchOrgList();
string fengefu2 = "";
int rowbegin = (j - 1) * 500;
int rowend = j * 500;
if (rowend > dt.Rows.Count)
{
rowend = dt.Rows.Count;
}
for (int i = rowbegin; i < rowend; i++)
{
var usercode = getString(dt.Rows[i]["账号"]);
DataRow[] rows = userdt.Select("USER_DOMAIN='" + usercode + "'");
if (dt.Rows[i]["组织机构编码"] == null || dt.Rows[i]["账号"] == null)
{
result += fengefu2 + "第" + (i + 2) + "行，组织机构编码或者账号不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
if (dt.Rows[i]["组织机构编码"].ToString() == "" || dt.Rows[i]["账号"].ToString() == "")
{
result += fengefu2 + "第" + (i + 2) + "行，组织机构编码或者账号不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
if (dt.Rows[i]["账号类型"] == null || dt.Rows[i]["账号类型"].ToString() == "")
{
result += fengefu2 + "第" + (i + 2) + "行，账号类型不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
if (dt.Rows[i]["用户类型"] == null || dt.Rows[i]["用户类型"].ToString() == "")
{
result += fengefu2 + "第" + (i + 2) + "行，用户类型不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
if (dt.Rows[i]["员工姓名"] == null || dt.Rows[i]["员工姓名"].ToString() == "")
{
result += fengefu2 + "第" + (i + 2) + "行，员工姓名不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
if (dt.Rows[i]["性别"] == null || dt.Rows[i]["性别"].ToString() == "")
{
result += fengefu2 + "第" + (i + 2) + "行，性别不能为空！，导入失败！";
fengefu2 = ",";
continue;
}
DataRow[] OrgRow = dtOrg.Select("ORG_CODE='" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "'");
if (OrgRow.Length <= 0)
{
result += fengefu2 + "第" + (i + 2) + "行，系统中不存在此组织机构编码！，导入失败！";
fengefu2 = ",";
continue;
}
if (rows.Length == 0)
{
StringBuilder sbOrgUser = new StringBuilder("insert into ts_uidp_org_user(ORG_ID,USER_ID)values");
string id = Guid.NewGuid().ToString();
sbOrgUser.Append("('" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "','" + id + "')");
list.Add(sbOrgUser.ToString());
StringBuilder sb = new StringBuilder(" INSERT INTO ts_uidp_userinfo(USER_ID,AUTHENTICATION_TYPE,USER_DOMAIN,USER_TYPE,USER_PASS,USER_NAME,USER_CODE,USER_SEX,PHONE_OFFICE,PHONE_MOBILE," +
"USER_EMAIL,FLAG,REG_TIME,REMARK) values ");
sb.Append(fengefu + "('" + id + "',");
if (dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号")
{
sb.Append("1,");
}
else
{
sb.Append("0,");
}
sb.Append("'" + getString(dt.Rows[i]["账号"]) + "',");
if (dt.Rows[i]["用户类型"] != null && dt.Rows[i]["用户类型"].ToString() == "普通用户")
{
sb.Append("1,");
}
else
{
sb.Append("0,");
}
sb.Append("'");
sb.Append(getString(dt.Rows[i]["用户密码"]) == "" ? UIDP.Security.SecurityHelper.StringToMD5Hash("123456") : UIDP.Security.SecurityHelper.StringToMD5Hash(getString(dt.Rows[i]["用户密码"])));
sb.Append("',");
sb.Append("'" + getString(dt.Rows[i]["员工姓名"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["员工编号"]) + "',");
if (dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男")
{
sb.Append("1,");
}
else
{
sb.Append("0,");
}
sb.Append("'" + getString(dt.Rows[i]["办公电话"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["手机"]) + "',");
sb.Append("'" + getString(dt.Rows[i]["电子邮箱"]) + "',");
if (dt.Rows[i]["账号状态"] != null && dt.Rows[i]["账号状态"].ToString() == "禁用")
{
sb.Append("0,");
}
else
{
sb.Append("1,");
}
sb.Append("TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),");
sb.Append("'" + getString(dt.Rows[i]["备注"]) + "')");
//fengefu = ",";
list.Add(sb.ToString());
}
else
{
foreach (var item in rows)
{
string sql = "update ts_uidp_userinfo set ";
sql += " AUTHENTICATION_TYPE=";
sql += getString((dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号") ? 1 : 0) + ",";
sql += " USER_DOMAIN='" + getString(dt.Rows[i]["账号"]) + "',";
sql += " USER_TYPE=";
sql += getString((dt.Rows[i]["用户类型"] != null && dt.Rows[i]["用户类型"].ToString() == "普通用户") ? 1 : 0) + ",";
sql += " USER_PASS='******'," : UIDP.Security.SecurityHelper.StringToMD5Hash(getString(dt.Rows[i]["用户密码"])) + "',";
sql += " USER_NAME='" + getString(dt.Rows[i]["员工姓名"]) + "',";
sql += " USER_CODE='" + getString(dt.Rows[i]["员工编号"]) + "',";
sql += " USER_SEX=";
sql += getString((dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男") ? 1 : 0) + ",";
sql += " PHONE_MOBILE='" + getString(dt.Rows[i]["手机"]) + "',";
sql += " PHONE_OFFICE='" + getString(dt.Rows[i]["办公电话"]) + "',";
sql += " USER_EMAIL='" + getString(dt.Rows[i]["电子邮箱"]) + "',";
sql += " FLAG=";
sql += getString((dt.Rows[i]["账号状态"] != null && dt.Rows[i]["账号状态"].ToString() == "禁用") ? 0: 1) + ",";
sql += " REG_TIME=TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),";
sql += " REMARK='" + getString(dt.Rows[i]["备注"]) + "'";
sql += " where USER_ID='" + item["USER_ID"].ToString() + "'";
list.Add(sql);
string sql2 = "update ts_uidp_org_user set ORG_ID=(SELECT ORG_ID FROM ts_uidp_org where ORG_CODE='" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "') where USER_ID='" + item["USER_ID"].ToString() + "'";
list.Add(sql2);
}
}
//if (sbOrgUser != null && sbOrgUser.Length > 0)
//{
// list.Add(sbOrgUser.ToString());
//}
//if (sb != null && sb.Length > 0)
//{
// list.Add(sb.ToString());
//}
}
//if (sb.Length > 0)
//{
// sb.Insert(0, " INSERT INTO ts_uidp_userinfo(USER_ID,AUTHENTICATION_TYPE,USER_DOMAIN,USER_TYPE,USER_PASS,USER_NAME,USER_CODE,USER_SEX,PHONE_OFFICE,PHONE_MOBILE," +
// "USER_EMAIL,FLAG,REG_TIME,REMARK) values ");
//}
//if (sbOrgUser.Length > 0)
//{
// sbOrgUser.Insert(0, " insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
//}
//if (sb != null && sb.Length > 0)
//{
// list.Add(sb.ToString());
//}
//if (sbOrgUser != null && sbOrgUser.Length > 0)
//{
// list.Add(sbOrgUser.ToString());
//}
}
if (db.GetDBType() == "MYSQL")
{
string sqlUpdate = " update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
list.Add(sqlUpdate);
}
else if (db.GetDBType() == "SQLSERVER")
{
string sqlUpdate = " update a set a.ORG_ID=b.ORG_ID from ts_uidp_org_user a ,ts_uidp_org b where a.ORG_ID=b.ORG_CODE ";
list.Add(sqlUpdate);
}
else if (db.GetDBType() == "ORACLE")
{
//string sqlUpdate = " update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
//string sqlUpdate = "UPDATE TS_UIDP_ORG_USER a SET a.ORG_ID = (SELECT ORG_ID FROM TS_UIDP_ORG b WHERE a.ORG_ID = b.ORG_CODE)";
//list.Add(sqlUpdate);
}
if (result != "")
{
return(result);
}
else
{
return(db.UploadUserFile(list));
}
//DataView dv = new DataView(dt);
//if (dt.Rows.Count != dv.ToTable(true, "账号").Rows.Count)
//{
// return "账号列存在重复数据，导入失败！";
//}
//List<string> list = new List<string>();
//string fengefu = "";
//StringBuilder sb = new StringBuilder();
//StringBuilder sbOrgUser = new StringBuilder();
//string result = "";
//string fengefu2 = "";
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// var usercode = getString(dt.Rows[i]["账号"]);
// var dtt = userdt;
// DataRow[] rows = userdt.Select("USER_DOMAIN='" + usercode + "'");
// if (rows.Length == 0)
// {
// //sb.Append(" insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK) values ");
// string id = Guid.NewGuid().ToString();
// sbOrgUser.Append(fengefu + "('" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "','" + id + "')");
// sb.Append(fengefu + "('" + id + "',");
// sb.Append("'" + getString(dt.Rows[i]["账号"]) + "',");
// sb.Append("'" + getString(dt.Rows[i]["员工编号"]) + "',");
// sb.Append("'" + getString(dt.Rows[i]["姓名"]) + "',");
// sb.Append("'123456',");
// sb.Append("'" + getString(dt.Rows[i]["手机"]) + "',");
// sb.Append("'" + getString(dt.Rows[i]["办公电话"]) + "',");
// sb.Append("'" + getString(dt.Rows[i]["邮箱"]) + "',");
// sb.Append("'" + getString(dt.Rows[i]["访问IP"]) + "',");
// if (dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男")
// {
// sb.Append("1,");
// }
// else
// {
// sb.Append("0,");
// }
// if (dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号")
// {
// sb.Append("'1',");
// }
// else
// {
// sb.Append("'0',");
// }
// sb.Append("1,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
// sb.Append("'" + getString(dt.Rows[i]["备注"]) + "')");
// fengefu = ",";
// }
// else
// {
// foreach (var item in rows)
// {
// string sql = "update ts_uidp_org set ";
// sql += " USER_DOMAIN='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
// sql += " USER_CODE='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
// sql += " USER_NAME='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
// sql += " USER_PASS='******',";
// sql += " PHONE_MOBILE='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
// sql += " PHONE_OFFICE='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
// sql += " USER_EMAIL='" + getString(dt.Rows[i]["组织机构名称"]) + "',";
// sql += " USER_IP='" + getString(dt.Rows[i]["组织机构简称"]) + "',";
// sql += " USER_SEX='" + getString(dt.Rows[i]["上级组织机构编码"]) + "',";
// sql += " AUTHENTICATION_TYPE='" + getString((row["是否有效"] != null && row["是否有效"].ToString() == "是") ? 1 : 0) + "',";
// sql += " FLAG='" + getString(row["上级组织机构编码"]) + "',";
// sql += " REG_TIME='" + getString(row["上级组织机构编码"]) + "',";
// sql += " REMARK='" + getString(row["备注"]) + "'";
// sql += " where USER_ID='" + item["USER_ID"].ToString() + "' ;";
// list.Add(sql);
// }
// }
// //sqllst.Add(sb.ToString());
//}
//if (sb.Length > 0)
//{
// sb.Insert(0, " INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
// "USER_EMAIL,USER_IP,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,REMARK) values ");
// sqllst.Add(sb.ToString());
//}
//if (sbOrgUser.Length > 0)
//{
// sb.Insert(0, " insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
// sqllst.Add(sb.ToString());
//}
//string sqlUpdate = "update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
//list.Add(sbOrgUser.ToString());
//list.Add(sb.ToString());
//list.Add(sqlUpdate);
//return db.UploadUserFile(list);
}
public string UploadOrgFileNew(string filePath)
{
string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\组织机构.xls"; //原始文件
string path = filePath; //原始文件
string mes = "";
DataTable orgdt = db.fetchOrgList();
DataTable dt = new DataTable();
UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
if (dt == null || dt.Rows.Count == 0)
{
return("空数据，导入失败！");
}
DataView dv = new DataView(dt);
if (dt.Rows.Count != dv.ToTable(true, "组织机构编码").Rows.Count)
{
return("组织机构编码存在重复数据，导入失败！");
}
List <string> sqllst = new List <string>();
int truckNum = Convert.ToInt32(Convert.ToDecimal(dt.Rows.Count / 500));
int yushu = dt.Rows.Count % 500;
if (yushu > 0)
{
truckNum++;
}
for (int i = 1; i < truckNum + 1; i++)
{
//StringBuilder sb = new StringBuilder();
string str = "insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK,DW_CODE) values ";
string fengefu = "";
int rowbegin = (i - 1) * 500;
int rowend = i * 500;
if (rowend > dt.Rows.Count)
{
rowend = dt.Rows.Count;
}
for (int j = rowbegin; j < rowend; j++)
//foreach (DataRow row in dt.Rows)
{
var orgname = getString(dt.Rows[j]["组织机构简称"]);
var allorgname = getString(dt.Rows[j]["组织机构名称"]);
var dtt = orgdt;
//DataRow[] rows = orgdt.Select("ORG_SHORT_NAME='" + getString(dt.Rows[j]["组织机构简称"])+ "'");
DataRow[] rows = orgdt.Select("ORG_CODE='" + getString(dt.Rows[j]["组织机构编码"]) + "' and ORG_CODE_UPPER='" + getString(dt.Rows[j]["上级组织机构编码"]) + "'");
if (rows.Length == 0)
{
StringBuilder sb = new StringBuilder(str);
sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
sb.Append("'" + getString(dt.Rows[j]["组织机构编码"]) + "',");
sb.Append("'" + getString(dt.Rows[j]["组织机构名称"]) + "',");
sb.Append("'" + getString(dt.Rows[j]["组织机构简称"]) + "',");
sb.Append("'" + getString(dt.Rows[j]["上级组织机构编码"]) + "',");
if (dt.Rows[j]["是否有效"] != null && dt.Rows[j]["是否有效"].ToString() == "是")
{
sb.Append("'1',");
}
else
{
sb.Append("'0',");
}
sb.Append("'1',");
sb.Append("'" + getString(dt.Rows[j]["备注"]) + "','");
sb.Append(getString(dt.Rows[j]["单位编码"]) + "')");
//fengefu = ",";
sqllst.Add(sb.ToString());
}
else
{
foreach (var item in rows)
{
string sql = "update ts_uidp_org set ";
sql += " ORG_CODE='" + getString(dt.Rows[j]["组织机构编码"]) + "',";
sql += " ORG_NAME='" + getString(dt.Rows[j]["组织机构名称"]) + "',";
sql += " ORG_SHORT_NAME='" + getString(dt.Rows[j]["组织机构简称"]) + "',";
//sql += " ORG_ID_UPPER='" + getString(d["parentId"]) + "',";
sql += " ORG_CODE_UPPER='" + getString(dt.Rows[j]["上级组织机构编码"]) + "',";
sql += " ISINVALID='" + getString((dt.Rows[j]["是否有效"] != null && dt.Rows[j]["是否有效"].ToString() == "是") ? 1 : 0) + "',";
sql += " REMARK='" + getString(dt.Rows[j]["备注"]) + "',";
sql += " DW_CODE='" + getString(dt.Rows[j]["单位编码"]) + "'";
sql += " where ORG_ID='" + item["ORG_ID"].ToString() + "'";
sqllst.Add(sql);
}
}
//sqllst.Add(sb.ToString());
}
//if (sb.Length > 0)
//{
// sb.Insert(0, " insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK) values ");
//}
////sb.Append(tempsb);
//if (sb != null && sb.Length > 0)
//{
// sqllst.Add(sb.ToString());
//}
}
if (db.GetDBType() == "MYSQL")
{
string sql = @"update ts_uidp_org a,ts_uidp_org b set a.ORG_ID_UPPER=b.ORG_ID
where a.ORG_CODE_UPPER=b.ORG_CODE";
sqllst.Add(sql);
}
else if (db.GetDBType() == "SQLSERVER")
{
string sql = @"update a set a.ORG_ID_UPPER = b.ORG_ID from ts_uidp_org a,ts_uidp_org b
where a.ORG_CODE_UPPER = b.ORG_CODE";
sqllst.Add(sql);
}
else if (db.GetDBType() == "ORACLE")
{
string sql = @"UPDATE TS_UIDP_ORG a SET a.ORG_ID_UPPER =(SELECT ORG_ID FROM TS_UIDP_ORG b WHERE a.ORG_CODE_UPPER=b.ORG_CODE)";
sqllst.Add(sql);
}
//sqllst.Add(sb.ToString());
//return truck(1000, sqllst);
return(db.UploadOrgFileList(sqllst));
}