public excelInReturnInfo modifications()
{
excelInReturnInfo returnR = new excelInReturnInfo();
try
{
HttpPostedFile file = HttpContext.Current.Request.Files["file"];
string uploadpath = HttpContext.Current.Server.MapPath("system\\excelExportIn\\");
string strSuiji = System.DateTime.Now.ToString("yyyyMMddHHmmss") + "_";//随机数
if (file != null)
{
if (!Directory.Exists(uploadpath))
{
Directory.CreateDirectory(uploadpath);
}
Random r = new Random();
int i = r.Next();
file.SaveAs(uploadpath + strSuiji + file.FileName);
InitializeWorkbook(uploadpath + strSuiji + file.FileName);
returnR = importExcels();
}
}
catch (Exception ex)
{
}
return(returnR);
}
private excelInReturnInfo ImportToDataBase()
{
string pl_no = public_method.getRadNum("dr");
int isheet = 0;
string msg = "";
string sql = "";
List <string> l_distinct_hrz = new List <string>();
excelInReturnInfo ri = new excelInReturnInfo();
DataTable dt_public_moduelDt = new DataTable();
sqlHelper sh = new sqlHelper();
SqlSugarClient db = sh.dbClient();
int iAllCount = 0;
int iExportSuccessCount = 0;//成功导入的条数
string defaultPsw = System.Configuration.ConfigurationManager.AppSettings["defaultPsw"];
if (string.IsNullOrEmpty(defaultPsw))
{
defaultPsw = "123456";
}
defaultPsw = DES_En_De.UserMd5(defaultPsw);
foreach (ISheet sheet in hssfworkbook)
{
if (isheet > 0)
{
msg = "请上传正确模板，必须是12列";
break;
}
isheet++;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
if (row.RowNum == 0)//行数大于1行
{
if (row.Cells.Count != 7)
{
msg = "请上传正确模板，必须是7列!";
break;
}
}
else
{
//这里做循环 //第一列和第二列都为空的时候不起作用 row.GetCell(0).ToString().Trim()
//这里进行导入
iAllCount++;
try
{
string level1 = row.GetCell(0).ToString().Trim();
string level2 = row.GetCell(1).ToString().Trim();
string level3 = row.GetCell(2).ToString().Trim();
string departName = "";
if (!string.IsNullOrEmpty(level1))
{
departName = level1;
}
if (!string.IsNullOrEmpty(level2))
{
departName = level2;
}
if (!string.IsNullOrEmpty(level3))
{
departName = level3;
}
string departId = "";
//通过这个查询de
sql = "select departmentId from public_department where departmentName='" + departName + "' and flag=1";
DataTable dt = db.Ado.GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
departId = dt.Rows[0]["departmentId"].ToString();
}
if (string.IsNullOrEmpty(departId))
{
msg = "所属部门必填!"; continue;
}
string managerFlag = row.GetCell(3).ToString().Trim();
if (string.IsNullOrEmpty(managerFlag))
{
msg = "负责人必填!"; continue;
}
if (managerFlag == "是")
{
managerFlag = "1";
}
else
{
managerFlag = "0";
}
string userName = row.GetCell(4).ToString().Trim();
if (string.IsNullOrEmpty(userName))
{
msg = "姓名必填!"; continue;
}
string account = row.GetCell(5).ToString().Trim();
if (string.IsNullOrEmpty(account))
{
msg = "登录账号必填!"; continue;
}
string biaoshi = row.GetCell(6).ToString().Trim();
if (string.IsNullOrEmpty(biaoshi))
{
msg = "机器标识必填!"; continue;
}
//这里导入到数据库中
sql = " if(select COUNT(*) from tb_Machine_user where flag=1 and account='" + account + "')>0 begin";
sql += " update tb_Machine_user set userName='******',cpuId='" + biaoshi + "',managerFlag='" + managerFlag + "',belongsId='" + departId + "',updateDate=GETDATE() where account='" + account + "' end";
sql += " else begin";
sql += " insert into tb_Machine_user(account,psw,cpuId,userName,managerFlag,belongsId,flag,createDate)values('" + account
+ "','" + defaultPsw + "','" + biaoshi + "','" + userName + "','" + managerFlag + "','" + departId + "',1,GETDATE()) end";
int iReturn = db.Ado.ExecuteCommand(sql);
if (iReturn > 0)
{
iExportSuccessCount++;
}
}
catch (Exception ex)
{
msg = "发生异常:" + ex.Message;
}
}
}
}
if (iAllCount > 0)
{
if (iExportSuccessCount > 0)
{
ri.code = 100;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条数据，共影响[" + iExportSuccessCount + "]条数据！";
}
else
{
ri.code = 0;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条,没有影响到数据！";
}
}
else
{
ri.code = -10;
ri.allCount = iAllCount;
ri.msg = "没有数据可以导入！";
}
return(ri);
}
private excelInReturnInfo importExcels()
{
string pl_no = public_method.getRadNum("dr");
int isheet = 0;
string msg = "";
string sql = "";
List <string> l_distinct_hrz = new List <string>();
string loginPersonId = public_method.getLoginInObject().personId.ToString(); //获取获取到当前登录人的personId
excelInReturnInfo ri = new excelInReturnInfo();
DataTable dt_public_moduelDt = new DataTable();
sqlHelper sh = new sqlHelper();
SqlSugarClient db = sh.dbClient();
int iAllCount = 0;
int iExportSuccessCount = 0;//成功导入的条数
string defaultPsw = System.Configuration.ConfigurationManager.AppSettings["defaultPsw"];
if (string.IsNullOrEmpty(defaultPsw))
{
defaultPsw = "123456";
}
defaultPsw = DES_En_De.UserMd5(defaultPsw);
int count = 0; //授权的cout
int departmentCount = 0; //当前公司的人员数量
string tempSql = "select (select listen_count from sys_listen) as listen_count,count(1) as deoartment_count from public_department";
DataTable table = db.SqlQueryable <object>(tempSql).ToDataTable();
count = int.Parse(DES_En_De.DesDecrypt(table.Rows[0]["listen_count"].ToString()));
departmentCount = int.Parse(table.Rows[0]["deoartment_count"].ToString());
foreach (ISheet sheet in hssfworkbook)
{
if (isheet > 0)
{
msg = "请上传正确模板，必须是7列";
break;
}
isheet++;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
if (row.RowNum == 0)//行数大于1行
{
if (row.Cells.Count != 7)
{
msg = "请上传正确模板，必须是7列!";
break;
}
}
else
{
//这里做循环 //第一列和第二列都为空的时候不起作用 row.GetCell(0).ToString().Trim()
//这里进行导入
try
{
importExcelTempPojo tempPojo = new importExcelTempPojo(row);
if (string.IsNullOrEmpty(tempPojo.department))
{
msg = "部门名称必填！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.leadingCadre))
{
msg = "负责状态必填！"; continue;
}
if ((!"1".Equals(tempPojo.leadingCadre)) && (!"0".Equals(tempPojo.leadingCadre)))
{
msg = "负责状态不合法：请检查：1是负责人，0不是负责人！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.loginAccount))
{
msg = "登录帐号必填！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.identifying))
{
msg = "机器标识必填！"; continue;
}
if ((!"1".Equals(tempPojo.sex)) && (!"0".Equals(tempPojo.sex)))
{
msg = "性别不合法：请检查：1是男，0是女！"; continue;
}
string departId = ""; //当前的部门id
string parentId = "0"; //父级的部门id默认为0 如果为空的情况下
sql = "select departmentId,isnull((select departmentId from public_department where departmentName='" +
tempPojo.superiorDepartment + "' and flag =1),'0') as parentId from public_department where departmentName='" + tempPojo.department + "' and flag=1";
DataTable dt = db.Ado.GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
departId = dt.Rows[0]["departmentId"].ToString();
parentId = dt.Rows[0]["parentId"].ToString();
}
//如果部门id不存在与对应的部门当中
public_department pdtt = new public_department();
pdtt.belongsId = int.Parse(parentId);
pdtt.departmentName = tempPojo.department;
pdtt.createDate = DateTime.Now;
pdtt.updateDate = DateTime.Now;
pdtt.updateUserId = int.Parse(loginPersonId);
pdtt.flag = 1;
pdtt.createUserId = int.Parse(loginPersonId);
if (string.IsNullOrEmpty(departId))
{
departId = db.Insertable(pdtt).IgnoreColumns(it => new { it.updateUserId, it.updateDate }).ExecuteReturnIdentity().ToString(); //执行插入并且返回对应的当前部门id
}
else
{
pdtt.belongsId = int.Parse(parentId);
db.Updateable(pdtt).IgnoreColumns(it => new { it.createDate, it.createUserId, it.remarks }).Where(it => it.departmentId == int.Parse(departId)).ExecuteCommand();
}
string findLeadingCadreSql = " select count(1) rownumber from tb_machine_user where account = '" + tempPojo.loginAccount + "'";
DataTable machineUserList = db.SqlQueryable <object>(findLeadingCadreSql).ToDataTable(); //如果当前人员存在的情况下
tb_Machine_user tempMachineUser = new tb_Machine_user();
tempMachineUser.updateDate = DateTime.Now;
tempMachineUser.updateUserId = int.Parse(loginPersonId);
tempMachineUser.createUserId = int.Parse(loginPersonId);
tempMachineUser.createDate = DateTime.Now;
tempMachineUser.userName = tempPojo.name;
tempMachineUser.flag = 1;
tempMachineUser.managerFlag = int.Parse(tempPojo.leadingCadre);
tempMachineUser.belongsId = int.Parse(departId);
tempMachineUser.cpuId = tempPojo.identifying;
tempMachineUser.psw = defaultPsw;
tempMachineUser.sex = int.Parse(tempPojo.sex);
tempMachineUser.account = tempPojo.loginAccount;
if (int.Parse(machineUserList.Rows[0]["rownumber"].ToString()) > 0)
{
db.Updateable(tempMachineUser).UpdateColumns(it => new { it.updateUserId, it.updateDate, it.userName, it.managerFlag, it.cpuId, it.belongsId }).Where(it => tempPojo.loginAccount.Equals(it.account)).ExecuteCommand();
iExportSuccessCount++;
}
else
{
db.Insertable(tempMachineUser).ExecuteCommand();
departmentCount++;
iExportSuccessCount++;
}
}
catch (Exception ex)
{
msg = "发生异常:" + ex.Message;
}
iAllCount++;
}
}
}
/**
* 将所有的部门登记全部进行筛选然后将部门等级全部更改掉
* */
var departmentLeveDataTable = db.Ado.UseStoredProcedure().GetDataTable("eve_department_leve");
List <public_department> departmentLeveList = new List <public_department>();
for (int i = 0; i < departmentLeveDataTable.Rows.Count; i++)
{
public_department pd = new public_department();
pd.departmentId = int.Parse(departmentLeveDataTable.Rows[i]["departmentId"].ToString());
pd.level = int.Parse(departmentLeveDataTable.Rows[i]["departmentLevel"].ToString());
departmentLeveList.Add(pd);
}
db.Updateable(departmentLeveList).UpdateColumns(it => new { it.level }).ExecuteCommand();
if (iAllCount > 0)
{
if (iExportSuccessCount > 0)
{
ri.code = 100;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条数据，共影响[" + iExportSuccessCount + "]条数据！";
}
else
{
ri.code = 0;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条,没有影响到数据！";
}
}
else
{
ri.code = -10;
ri.allCount = iAllCount;
ri.msg = "没有数据可以导入！";
}
return(ri);
}
