ZQHApi.git - Gitblit

			@@ -20,20 +20,14 @@
			code = "";
			List<DataTable> excelTable = new List<DataTable>();
			excelTable = ImportExcel.ExcelToTableList(FileCode);
			if (excelTable.Count != 4)
			if (excelTable.Count != 2)
			{
			code = "300";
			Message = "导入模板不符合规范,请检查sheet数";
			return Message;

			}
			else if (excelTable[0].Columns.Count != 5)
			{
			code = "300";
			Message = "用户班组模板不符合规范,请检查列名字段数";
			return Message;
			}
			else if (excelTable[1].Columns.Count != 10)
			else if (excelTable[0].Columns.Count != 9)
			{
			code = "300";
			Message = "用户清单模板不符合规范,请检查列名字段数";
			@@ -42,91 +36,55 @@
			else if (excelTable[0].Columns[0].ColumnName != "序号")
			{
			code = "300";
			Message = "用户班组模板：表头信息不符合规范,第1列应为{序号}";
			return Message;
			}
			else if (excelTable[0].Columns[1].ColumnName != "组织编码")
			{
			code = "300";
			Message = "用户班组模板：表头信息不符合规范,第2列应为{组织编码}";
			return Message;
			}
			else if (excelTable[0].Columns[2].ColumnName != "班组编号(唯一)")
			{
			code = "300";
			Message = "用户班组模板：表头信息不符合规范,第2列应为{班组编号(唯一)}";
			return Message;
			}
			else if (excelTable[0].Columns[3].ColumnName != "班组名称")
			{
			code = "300";
			Message = "用户班组模板：表头信息不符合规范,第3列应为{班组名称}";
			return Message;
			}
			else if (excelTable[0].Columns[4].ColumnName != "班组描述")
			{
			code = "300";
			Message = "用户班组模板：表头信息不符合规范,第4列应为{班组描述}";
			return Message;
			}
			else if (excelTable[1].Columns[0].ColumnName != "序号")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第1列应为{序号}";
			return Message;
			}
			else if (excelTable[1].Columns[1].ColumnName != "用户编号(唯一)")
			else if (excelTable[0].Columns[1].ColumnName != "用户编号(唯一)")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第2列应为{用户编号(唯一)}";
			return Message;
			}
			else if (excelTable[1].Columns[2].ColumnName != "用户姓名")
			else if (excelTable[0].Columns[2].ColumnName != "用户姓名")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第3列应为{用户姓名}";
			return Message;
			}
			else if (excelTable[1].Columns[3].ColumnName != "在职状态")
			else if (excelTable[0].Columns[3].ColumnName != "在职状态")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第4列应为{在职状态}";
			return Message;
			}
			else if (excelTable[1].Columns[4].ColumnName != "密码")
			else if (excelTable[0].Columns[4].ColumnName != "密码")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第5列应为{密码}";
			return Message;
			}
			else if (excelTable[1].Columns[5].ColumnName != "手机号")
			else if (excelTable[0].Columns[5].ColumnName != "手机号")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第6列应为{手机号}";
			return Message;
			}
			else if (excelTable[1].Columns[6].ColumnName != "邮箱")
			else if (excelTable[0].Columns[6].ColumnName != "邮箱")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第7列应为{邮箱}";
			return Message;
			}
			else if (excelTable[1].Columns[7].ColumnName != "组织编码")
			else if (excelTable[0].Columns[7].ColumnName != "组织编码")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第8列应为{组织编码}";
			return Message;
			}
			else if (excelTable[1].Columns[8].ColumnName != "工资类型")
			else if (excelTable[0].Columns[8].ColumnName != "工资类型")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第9列应为{工资类型}";
			return Message;
			}
			else if (excelTable[1].Columns[9].ColumnName != "用户组编码")
			{
			code = "300";
			Message = "用户清单模板：表头信息不符合规范,第10列应为{用户组编码}";
			return Message;
			}
			else
			@@ -1864,106 +1822,39 @@
			var dynamicParams = new DynamicParameters();
			DataTable dt;
			List<ExcelErro> list = new List<ExcelErro>();
			List<DataTable> excelTable = new List<DataTable>();
			list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
			excelTable = ImportExcel.ExcelToTableList(FileCode); //获取Excel数据
			//主表
			for (int j = 0; j < excelTable[0].Rows.Count; j++)
			DataTable excelTable = new DataTable();
			list = ImportExcel.ExcelToTableErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
			excelTable = ImportExcel.ExcelToTable(FileCode); //获取Excel数据
			for (int k = 0; k < excelTable.Rows.Count; k++)
			{
			if (excelTable[0].Rows[j][2].ToString().Trim() != null && excelTable[0].Rows[j][2].ToString().Trim() != "")
			{
			sql = @"select * from TGroup where group_code=@group_code";
			dynamicParams.Add("@group_code", excelTable[0].Rows[j][2].ToString().Trim());
			dt = DapperHelper.selectdata(sql, dynamicParams);
			if (dt.Rows.Count > 0)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{班组编码(唯一)}";
			erro.ErrorCont = "班组表:{班组编码(唯一)}字段" + excelTable[0].Rows[j][2].ToString().Trim() + "已存在";
			list.Add(erro);
			}
			}
			}
			//子表
			for (int k = 0; k < excelTable[1].Rows.Count; k++)
			{
			if (excelTable[1].Rows[k][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
			if (excelTable.Rows[k][1].ToString().Trim() != null && excelTable.Rows[k][1].ToString().Trim() != "")
			{
			sql = @"select * from TUser where usercode=@usercode";
			dynamicParams.Add("@usercode", excelTable[1].Rows[k][1].ToString().Trim());
			dynamicParams.Add("@usercode", excelTable.Rows[k][1].ToString().Trim());
			dt = DapperHelper.selectdata(sql, dynamicParams);
			if (dt.Rows.Count > 0)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{用户编码(唯一)}";
			erro.ErrorCont = "用户表:{用户编码(唯一)}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
			erro.ErrorCont = "用户表:{用户编码(唯一)}字段" + excelTable.Rows[k][1].ToString().Trim() + "已存在";
			list.Add(erro);
			}
			}
			if (excelTable[1].Rows[k][7].ToString().Trim() != null && excelTable[1].Rows[k][7].ToString().Trim() != "")
			if (excelTable.Rows[k][7].ToString().Trim() != null && excelTable.Rows[k][7].ToString().Trim() != "")
			{
			sql = @"select * from TOrganization where org_code=@org_code";
			dynamicParams.Add("@org_code", excelTable[1].Rows[k][7].ToString().Trim());
			dynamicParams.Add("@org_code", excelTable.Rows[k][7].ToString().Trim());
			dt = DapperHelper.selectdata(sql, dynamicParams);
			if (dt == null \|\| dt.Rows.Count <= 0)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{组织编码}";
			erro.ErrorCont = "用户表:{组织编码}字段" + excelTable[1].Rows[k][7].ToString().Trim() + "不存在";
			erro.ErrorCont = "用户表:{组织编码}字段" + excelTable.Rows[k][7].ToString().Trim() + "不存在";
			list.Add(erro);
			}
			}
			if (excelTable[1].Rows[k][9].ToString().Trim() != null && excelTable[1].Rows[k][9].ToString().Trim() != "")
			{
			sql = @"select torg_code from TGroup where group_code=@group_code";
			dynamicParams.Add("@group_code", excelTable[1].Rows[k][9].ToString().Trim());
			dt = DapperHelper.selectdata(sql, dynamicParams);
			if (dt != null && dt.Rows.Count>0)
			{
			if (dt.Rows[0]["torg_code"].ToString()!= excelTable[1].Rows[k][7].ToString().Trim())
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{用户组编码}";
			erro.ErrorCont = "用户表:{用户组编码}字段" + excelTable[1].Rows[k][9].ToString().Trim() + " 不是组织编码:" + excelTable[1].Rows[k][7].ToString().Trim() + "下的用户组";
			list.Add(erro);
			} }
			}
			}
			//判断子表组织编码不存在于主表组织编码中的数据
			var dt2 = from r in excelTable[1].AsEnumerable()
			where !(
			from rr in excelTable[0].AsEnumerable()
			select rr.Field<string>("组织编码")
			).Contains(r.Field<string>("组织编码"))
			select r;
			List<DataRow> listRow2 = dt2.ToList();
			if (listRow2.Count > 0)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{组织编码}";
			erro.ErrorCont = "用户表:{组织编码}字段中有值在班组表:{组织编码}中不存在";
			list.Add(erro);
			}
			//判断子表外键不存在于主表主键中的数据
			var dt3 = from r in excelTable[1].AsEnumerable()
			where !(
			from rr in excelTable[0].AsEnumerable()
			select rr.Field<string>("班组编号(唯一)")
			).Contains(r.Field<string>("用户组编码"))
			select r;
			List<DataRow> listRow = dt3.ToList();
			if (listRow.Count > 0)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{用户组编码}";
			erro.ErrorCont = "用户表:{用户组编码}字段中有值在班组表:{班组编号(唯一)}中不存在";
			list.Add(erro);
			}
			if (list.Count > 0)
			{
			@@ -1980,7 +1871,7 @@
			{
			StuCode = "200";
			message = "数据验证成功";
			count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
			count = excelTable.Rows.Count;
			}
			return list;
			}
			@@ -3233,41 +3124,12 @@
			try
			{
			list.Clear();
			List<DataTable> excelTable = new List<DataTable>();
			excelTable = ImportExcel.ExcelToTableList(FileCode);
			//导入班组
			for (int k = 0; k < excelTable[0].Rows.Count; k++)
			{
			sql = @"select * from TGroup where group_code=@group_code";
			dynamicParams.Add("@group_code", excelTable[0].Rows[k][2].ToString().Trim());
			dt = DapperHelper.selectdata(sql, dynamicParams);
			if (dt.Rows.Count > 0)
			{
			continue;
			}
			else
			{
			sql = @"insert into TGroup(group_code,group_name,description,lm_user,lm_date,torg_code)
			values(@group_code,@group_name,@description,@Operator,@CreateDate,@torg_code)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			group_code = excelTable[0].Rows[k][2].ToString().Trim(),
			group_name = excelTable[0].Rows[k][3].ToString().Trim(),
			description = excelTable[0].Rows[k][4].ToString().Trim(),
			CreateDate = DateTime.Now.ToString(),
			Operator = User,
			torg_code= excelTable[0].Rows[k][1].ToString().Trim()
			}
			});
			}
			}
			DataTable excelTable = new DataTable();
			excelTable = ImportExcel.ExcelToTable(FileCode);
			//导入用户
			for (int i = 0; i < excelTable[1].Rows.Count; i++)
			for (int i = 0; i < excelTable.Rows.Count; i++)
			{
			if (excelTable[1].Rows[i][3].ToString().Trim() == "在职")
			if (excelTable.Rows[i][3].ToString().Trim() == "在职")
			{
			Enable = "Y";
			}
			@@ -3276,7 +3138,7 @@
			Enable = "N";
			}
			//工资类型
			switch (excelTable[1].Rows[i][8].ToString().Trim())
			switch (excelTable.Rows[i][8].ToString().Trim())
			{
			case "计件制":
			WageType = "2";
			@@ -3288,7 +3150,7 @@
			break;
			}
			sql = @"select * from TUser where usercode=@usercode";
			dynamicParams.Add("@usercode", excelTable[1].Rows[i][1].ToString().Trim());
			dynamicParams.Add("@usercode", excelTable.Rows[i][1].ToString().Trim());
			dt = DapperHelper.selectdata(sql, dynamicParams);
			if (dt.Rows.Count > 0)
			{
			@@ -3301,17 +3163,17 @@
			str = sql,
			parm = new
			{
			UserCode = excelTable[1].Rows[i][1].ToString().Trim(),
			UserName = excelTable[1].Rows[i][2].ToString().Trim(),
			password = excelTable[1].Rows[i][4].ToString().Trim(),
			UserCode = excelTable.Rows[i][1].ToString().Trim(),
			UserName = excelTable.Rows[i][2].ToString().Trim(),
			password = excelTable.Rows[i][4].ToString().Trim(),
			Enable = Enable,
			Mobile = excelTable[1].Rows[i][5].ToString().Trim(),
			Mobile = excelTable.Rows[i][5].ToString().Trim(),
			CreateDate = DateTime.Now.ToString(),
			Email = excelTable[1].Rows[i][6].ToString().Trim(),
			Email = excelTable.Rows[i][6].ToString().Trim(),
			Operator = User,
			StuOrg = excelTable[1].Rows[i][7].ToString().Trim(),
			StuOrg = excelTable.Rows[i][7].ToString().Trim(),
			wagetype = WageType,
			usergroup_code = excelTable[1].Rows[i][9].ToString().Trim()
			usergroup_code = excelTable.Rows[i][9].ToString().Trim()
			}
			});
			}