//上传审核人信息
public JsonResult UploadExpenseAuditFile()
{
var retModel = new JsonReturnModel();
try
{
if (Request.Files == null || Request.Files.Count == 0)
{
retModel.AddError("errorMessage", Common.GetLanguageValueByParam("请选择您要上传的附件！", "PRCommon", "PRItemType", Userinfo.language));
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
HttpPostedFileBase prfile = Request.Files[0];
string fileName = prfile.FileName.Substring(prfile.FileName.LastIndexOf("\\") + 1, prfile.FileName.Length - (prfile.FileName.LastIndexOf("\\")) - 1);
if (!fileName.ToLower().Contains(".xls") && !fileName.ToLower().Contains(".xlsx"))
{
retModel.AddError("errorMessage", "只能上传Excel文件！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
string filePath = ConfigurationManager.AppSettings["UploadPath"] + fileName;
prfile.SaveAs(filePath);
//获取数据库审核人信息
List <USER> allUser = UserBll.GetAllUserInfo();
List <B_EXPENSEAUDITCONFIGURATION> list = new List <B_EXPENSEAUDITCONFIGURATION>();
using (FileStream fs = new FileStream(filePath, FileMode.Open))
{
IWorkbook workbook = null;
if (fileName.ToLower().Contains(".xlsx"))
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);
int rowNum = sheet.PhysicalNumberOfRows;
for (int i = 0; i < rowNum; i++)
{
IRow row = sheet.GetRow(i);
if (i != 0)
{
B_EXPENSEAUDITCONFIGURATION model = new B_EXPENSEAUDITCONFIGURATION();
model.B_ROLENAME = row.GetCell(0) != null?row.GetCell(0).ToString().Trim() : "";
model.B_TYPE = row.GetCell(1) != null?row.GetCell(1).ToString().Trim() : "";
model.B_COMPANYCODE = row.GetCell(2) != null?row.GetCell(2).ToString().Trim() : "";
model.B_PROJECTNAME = row.GetCell(3) != null?row.GetCell(3).ToString().Trim() : "";
model.B_COSTCENTERS = row.GetCell(4) != null?row.GetCell(4).ToString().Trim() : "";
model.B_HANDLEPERSONS = row.GetCell(5) != null?row.GetCell(5).ToString().Trim() : "";
if (model.B_TYPE == "非项目")
{
model.B_TYPE = "Non Project";
}
else if (model.B_TYPE == "项目")
{
model.B_TYPE = "Project";
}
else
{
retModel.AddError("errorMessage", i + 1 + "行2列输入的项目类型错误！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
//判断输入的处理人是否存在
if (!string.IsNullOrEmpty(model.B_HANDLEPERSONS))
{
var name = allUser.Where(x => x.LOGIN_NAME.ToUpper() == model.B_HANDLEPERSONS.ToUpper()).FirstOrDefault();
model.B_HANDLEPERSONS.Split(';').Where(x => x != "").Distinct().ToList();
if (name == null)
{
retModel.AddError("errorMessage", i + 1 + "行6列上传的人员在系统中不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
else
{
model.B_HANDLEPERSONS = name.FIRST_NAME;
}
}
if (model.B_TYPE == "Non Project")
{
if (string.IsNullOrEmpty(model.B_COSTCENTERS))
{
retModel.AddError("errorMessage", i + 1 + "当为非项目时，必须选择成本中心！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
//验证输入的成本中心代码是否正确
List <string> listcon = model.B_COSTCENTERS.Split(',').Where(x => x != "").ToList();
if (listcon != null && listcon.Count > 0)
{
foreach (var item in listcon)
{
var OrganizationalStructure = OrganizationalStructureBll.GetOrganizationalStructureByCostCenter(item);
if (OrganizationalStructure == null)
{
retModel.AddError("errorMessage", i + 1 + "行选择的成本中心" + item + "代码不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
var obj = list.Where(x => x.B_ROLENAME == model.B_ROLENAME && x.B_TYPE == model.B_TYPE && x.B_COMPANYCODE == model.B_COMPANYCODE && x.B_COSTCENTERS.Split(',').Contains(item)).FirstOrDefault();
if (obj != null)
{
string errorStr = i + 1 + "行角色名称：" + model.B_ROLENAME + "、项目类型：" + (model.B_TYPE == "Project" ? "项目" : "非项目") + "、公司代码：" + model.B_COMPANYCODE + "、成本中心代码：" + item + ",导入时重复!";
retModel.AddError("errorMessage", errorStr);
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
}
}
}
else
{
//验证输入的项目名称是否正确
if (string.IsNullOrEmpty(model.B_PROJECTNAME))
{
retModel.AddError("errorMessage", i + 1 + "行当为项目时必须选择项目名称！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
var projectItem = ProjectManageDA.GetProjectManageByName(inn, model.B_PROJECTNAME);
if (projectItem.isError() || projectItem.getItemCount() == 0)
{
retModel.AddError("errorMessage", i + 1 + "行4列输入的项目名称不存在！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
var obj = list.Where(x => x.B_ROLENAME == model.B_ROLENAME && x.B_TYPE == model.B_TYPE && x.B_COMPANYCODE == model.B_COMPANYCODE && x.B_PROJECTNAME == model.B_PROJECTNAME).FirstOrDefault();
if (obj != null)
{
string errorStr = i + 1 + "行角色名称：" + model.B_ROLENAME + "、项目类型：" + (model.B_TYPE == "Project" ? "项目" : "非项目") + "、公司代码：" + model.B_COMPANYCODE + "、项目名称：" + model.B_PROJECTNAME + ",导入时重复!";
retModel.AddError("errorMessage", errorStr);
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
}
list.Add(model);
}
}
list = list.Distinct().ToList();
//把数据添加到数据库中
if (list != null && list.Count > 0)
{
//删除审核人配置表数据
var result = ExpenseAuditConfigurationBll.DeleteExpenseAuditConfiguration(inn);
if (result.isError())
{
retModel.AddError("errorMessage", "上传发生错误！");
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
var ExpAudit = inn.newItem("B_EXPENSEAUDITCONFIGURATION", "add");
ExpAudit.setProperty("b_rolename", item.B_ROLENAME);
ExpAudit.setProperty("b_type", item.B_TYPE);
if (item.B_TYPE == "Non Project")
{
ExpAudit.setProperty("b_projectname", "");
ExpAudit.setProperty("b_costcenters", item.B_COSTCENTERS);
}
else
{
ExpAudit.setProperty("b_projectname", item.B_PROJECTNAME);
ExpAudit.setProperty("b_costcenters", "");
}
ExpAudit.setProperty("b_companycode", item.B_COMPANYCODE);
string companyName = CompanyInfoBll.GetCompanyNameByCode(inn, item.B_COMPANYCODE);
string value = item.B_COMPANYCODE + " (" + companyName + ")";
ExpAudit.setProperty("b_companycode", item.B_COMPANYCODE = value);
ExpAudit.setProperty("b_handlepersons", item.B_HANDLEPERSONS);
var resultAdd = ExpAudit.apply();
}
}
}
}
catch (Exception ex)
{
retModel.AddError("errorMessage", ex.Message);
}
return(Json(retModel, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 根据名称获取用户信息
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public JsonResult GetUserByName(string name)
{
var retModel = new JsonReturnModel();
try
{
using (InnovatorSolutionsEntities db = new InnovatorSolutionsEntities())
{
USER item = db.USER.Where(x => x.FIRST_NAME == name).FirstOrDefault();
if (item != null)
{
UserModel model = new UserModel();
model.Id = item.ID;
model.first_Name = item.FIRST_NAME;
model.Email = item.EMAIL;
model.Telephone = item.TELEPHONE;
model.b_JobNumber = item.B_JOBNUMBER;
model.b_ChineseName = item.B_CHINESENAME;
model.b_EnglishName = item.B_ENGLISHNAME;
model.b_Centre = item.B_CENTRE;
model.b_Department = item.B_DEPARTMENT;
model.b_SeniorManager = item.B_SENIORMANAGER;
model.b_Director = item.B_DIRECTOR;
model.b_VP = item.B_VP;
if (!string.IsNullOrEmpty(model.b_Centre))
{
//根据中心名称获取中心领导
var structure = OrganizationalStructureDA.GetOrganizationalStructureByParam(inn, model.b_Centre, 2);
if (!structure.isError() && structure.getItemCount() > 0)
{
string b_nodepersonname = structure.getItemByIndex(0).getProperty("b_nodepersonname");
string b_NodeCode = structure.getItemByIndex(0).getProperty("b_nodecode");
string b_ParentNodeCode = structure.getItemByIndex(0).getProperty("b_parentnodecode");
string b_CompanyCode = structure.getItemByIndex(0).getProperty("b_companycode");
string b_CostCenter = structure.getItemByIndex(0).getProperty("b_costcenter");
USER centreUser = UserDA.GetUserByLoginName(b_nodepersonname);
if (centreUser != null && centreUser.FIRST_NAME != name)
{
model.b_CentreLeader = centreUser.FIRST_NAME;
}
else if (centreUser != null && centreUser.FIRST_NAME == name)
{
//获取上级节点
var parentNode = OrganizationalStructureDA.GetOrganizationalStructureByNodeCode(inn, b_ParentNodeCode);
if (!parentNode.isError() && parentNode.getItemCount() > 0)
{
string lineLeader = parentNode.getItemByIndex(0).getProperty("b_nodepersonname");
USER lineLeaderUser = UserDA.GetUserByLoginName(lineLeader);
if (lineLeaderUser != null && lineLeaderUser.FIRST_NAME != name)
{
model.b_LineLeader = lineLeaderUser.FIRST_NAME;
}
}
}
if (!string.IsNullOrEmpty(model.b_Department))
{
//获取结构数据
List <B_ORGANIZATIONALSTRUCTURE> list = OrganizationalStructureBll.GetOrganizationalStructureList();
//获取中心下的组织结构
List <B_ORGANIZATIONALSTRUCTURE> childNodeList = new List <B_ORGANIZATIONALSTRUCTURE>();
OrganizationalStructureBll.GetChildByParent(inn, b_NodeCode, childNodeList, list);
if (childNodeList != null && childNodeList.Count > 0)
{
B_ORGANIZATIONALSTRUCTURE departObj = childNodeList.Where(x => x.B_NODENAME == model.b_Department).FirstOrDefault();
if (departObj != null)
{
b_CompanyCode = departObj.B_COMPANYCODE;
b_CostCenter = departObj.B_COSTCENTER;
USER departUser = UserDA.GetUserByLoginName(departObj.B_NODEPERSONNAME);
if (departUser != null && departUser.FIRST_NAME != name)
{
model.b_DepartmentLeader = departUser.FIRST_NAME;
}
}
}
}
model.b_CompanyCode = b_CompanyCode;
model.b_CostCenter = b_CostCenter;
if (!string.IsNullOrEmpty(model.b_CompanyCode))
{
//根据公司代码 获取公司信息
string companyName = CompanyInfoBll.GetCompanyNameByCode(inn, model.b_CompanyCode);
if (!string.IsNullOrEmpty(companyName))
{
model.b_CompanyCode = model.b_CompanyCode + " (" + companyName + ")";
}
}
}
}
retModel.data = model;
}
}
}
catch (Exception ex)
{
retModel.AddError("errorMessage", ex.Message);
}
return(Json(retModel, JsonRequestBehavior.AllowGet));
}