public static string GetJson(bool IsLoadUser, string argType, out OrganizationTree Deparment, out List <OrganizationTree> AllDeparment, bool IsMultiSelect = false, bool IsSelectCompany = false)
{
Deparment = new OrganizationTree();
AllDeparment = new List <OrganizationTree>();
string sql = " ";
if (argType == "common")
{
sql = "select type='部门',id,pid,name,EMP_ID='',show_order from CMS_DEPARTMENT union all select type='员工',id,host_id as pid,emp_name as name,EMP_ID,show_order from dbo.CMS_EMPLOYEE where host_id<>0 order by show_order ";
}
else if (argType == "HasEmployeeAccount")
{
sql = " select type='部门',id,pid,name,EMP_ID='',show_order from CMS_DEPARTMENT union all select type='员工',id,DeparmentID as pid,EmployeeName as name,EmployeeAccount,0 from dbo.EmployeeInfo where EmployeeCode IS NOT NULL AND EmployeeCode <> '' and ( EmployeeAccount IS NOT NULL and EmployeeAccount <> '' ) order by show_order ";
}
else
{
sql = " select type='部门',id,pid,name,EMP_ID='',show_order from CMS_DEPARTMENT union all select type='员工',id,DeparmentID as pid,EmployeeName as name,EmployeeCode,0 from dbo.EmployeeInfo where EmployeeCode IS NOT NULL order by show_order ";
}
if (!IsLoadUser)
{
sql = "select type=(CASE WHEN IsCompany=1 THEN '公司' ELSE '部门' END ),id,pid,name,EMP_ID='',show_order from CMS_DEPARTMENT ";
}
string[] changePassWord = Common.getChangePassWord();
DataTable dt = Common.Resource.Query(sql, changePassWord[0], changePassWord[1], changePassWord[2]).Tables[0];
DataRow[] ParentRows = dt.Select(" pid=-1");
string json = "[" + GetParentJson(ParentRows[0], dt, out Deparment, ref AllDeparment, IsMultiSelect, IsSelectCompany) + "]";
return(json);
}
private static string GetParentJson(DataRow row, DataTable dt, out OrganizationTree Deparment, ref List <OrganizationTree> AllDeparment, bool IsMultiSelect = false, bool IsSelectCompany = false)
{
Deparment = new OrganizationTree();
StringBuilder sb = new StringBuilder();
sb.Append("{");
sb.Append("\"id\":" + row["id"].ToString() + ",");
string UserID = row["EMP_ID"].ToString();
if (UserID == "")
{
UserID = row["ID"].ToString();
}
sb.Append("\"attributes\":{\"url\":\"QXGrid.aspx\",\"UserID\":\"" + UserID + "\",\"Type\":\"" + row["Type"].ToString() + "\"},");
sb.Append("\"text\":\"" + row["name"].ToString() + "\"");
Deparment.DeparmentID = row["id"].ToString();
Deparment.DeparmentName = row["name"].ToString();
AllDeparment.Add(Deparment);
if (row["id"].ToString() == "0")
{
sb.Append(",\"iconCls\":\"tree-folder-brand\"");
}
else if (row["Type"].ToString() == "公司")
{
sb.Append(",\"iconCls\":\"tree-folder-Company\"");
}
else if (row["Type"].ToString() == "部门")
{
sb.Append(",\"iconCls\":\"tree-folder-Dept\"");
}
else
{
sb.Append(",\"iconCls\":\"tree-folder-Emp\"");
}
DataRow[] ChildRows = dt.Select("pid=" + row["id"].ToString());
if (ChildRows.Length > 0)
{
if (row["id"].ToString() == "0")
{
sb.Append(",\"state\":\"open\"");
}
else
{
sb.Append(",\"state\":\"closed\"");
}
List <OrganizationTree> DeparmentChild = new List <OrganizationTree>();
sb.Append(",\"children\":" + GetChildJson(ChildRows, dt, out DeparmentChild, ref AllDeparment, IsMultiSelect, IsSelectCompany) + "");
Deparment.DeparmentChild = DeparmentChild;
}
sb.Append("}");
return(sb.ToString());
}
public async Task AddAsync(OrganizationTree entity)
{
if (string.IsNullOrWhiteSpace(entity.ParentObjId))
{
entity.SetLValue(1);
entity.SetRValue(2);
entity.SetGroup(GuidGen.NewGUID());
}
else
{
var parentNode = _context.Set <OrganizationTree>().FirstOrDefault(x => x.ObjId == entity.ParentObjId);
if (parentNode == null)
{
throw new Exception($"找不到ObjId为{entity.ParentObjId}的上级节点");
}
var referenceNodes = _context.Set <OrganizationTree>().Where(x => x.Group == parentNode.Group).ToList();
//新节点
entity.SetLValue(parentNode.RValue);
entity.SetRValue(parentNode.RValue + 1);
entity.SetGroup(parentNode.Group);
entity.SetParentId(parentNode.Id);
for (int idx = referenceNodes.Count - 1; idx >= 0; idx--)
{
var node = referenceNodes[idx];
//1.父节点
//只是右值加2
if (node.Id == parentNode.Id)
{
node.SetRValue(node.RValue + 2);
continue;
}
//2.顶级节点
//只是右值加2
if (node.LValue == 1)
{
node.SetRValue(node.RValue + 2);
continue;
}
//3.沿线节点
//左右值都加2
if (node.LValue > parentNode.LValue && node.RValue > parentNode.RValue)
{
node.SetLValue(node.LValue + 2);
node.SetRValue(node.RValue + 2);
continue;
}
}
_context.Set <OrganizationTree>().UpdateRange(referenceNodes);
}
_context.Set <OrganizationTree>().Add(entity);
await _context.SaveEntitiesAsync();
}
public static string GetOrganizationTree()
{
//获取数据授权
List <string> oganizationIds = WebStyleBaseForEnergy.webStyleBase.GetDataValidIdGroup("ProductionOrganization");
IList <string> levelCodes = WebUserControls.Service.OrganizationSelector.OrganisationTree.GetOrganisationLevelCodeById(oganizationIds);
DataTable table = OrganizationTree.CreatOrganizationTree(levelCodes.ToArray());
string[] otherColumns = { "OrganizationID", "LevelType", "state" };
string json = EasyUIJsonParser.TreeJsonParser.DataTableToJsonByLevelCode(table, "LevelCode", "Name", otherColumns);
return(json);
}
public static void GetAllDeparmentID(ref List <OrganizationTree> AllDeparmentID, OrganizationTree Deparment)
{
if (Deparment.DeparmentChild != null)
{
foreach (OrganizationTree d in Deparment.DeparmentChild)
{
if (d.DeparmentChild == null || d.DeparmentChild.Count == 0)
{
AllDeparmentID.Add(d);
}
GetAllDeparmentID(ref AllDeparmentID, d);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
OrganizationTree.CreatOrganizationTree(new string[] { "O03", "O02" });
//string test = AutoCreatHtmlStrSrevice.GetHtml("","");
//string endTime = DateTime.Parse("2014-01-23").AddDays(1).ToString();
//ISqlServerDataFactory _dataFactory = new SqlServerDataFactory("Data Source=QH-20140815HAUR;Initial Catalog=Db_02_01;Integrated Security=True");
//string sql = @"SELECT * FROM Db_02_01.[dbo].[HistoyFormulaValue] WHERE vDate>='2015/1/13 0:00:00' AND vDate<'2015/1/14 0:00:00'";
////SqlParameter parameters = new SqlParameter("factoryOrganizationID", factoryOrganizationID);
//DataTable table= _dataFactory.Query(sql);
//DataRow[] rows = table.Select("vDate>#2015-01-13 15:00:00# AND vDate<#2015-01-13 16:00:00#");
////DataRow[] rows1 = table.Select("vDate>#2015-01-13 15:00# AND vDate<#2015-01-13 16:00");
//object temp = table.Compute("Sum(FormulaValue)", "vDate>#2015-01-13 15:00:00# AND vDate<#2015-01-13 16:00:00#");
}
private static string GetChildJson(DataRow[] rows, DataTable dt, out List <OrganizationTree> DeparmentChild, ref List <OrganizationTree> AllDeparment, bool IsMultiSelect = false, bool IsSelectCompany = false)
{
StringBuilder sb = new StringBuilder();
sb.Append("[");
DeparmentChild = new List <OrganizationTree>();
for (int i = 0; i < rows.Length; i++)
{
OrganizationTree Deparment = new OrganizationTree();
sb.Append(GetParentJson(rows[i], dt, out Deparment, ref AllDeparment, IsMultiSelect, IsSelectCompany));
DeparmentChild.Add(Deparment);
if (i < rows.Length - 1)
{
sb.Append(",");
}
}
sb.Append("]");
return(sb.ToString());
}
public static List <string> GetChildDeparmentID(string DeparmentID, bool IsGetParentDeparment = false)
{
List <string> ChildDeparmentID = new List <string>();
List <OrganizationTree> AllDeparment = new List <OrganizationTree>();
List <OrganizationTree> AllDeparmentID = new List <OrganizationTree>();
OrganizationTree Deparment = AllDeparment.FirstOrDefault(p => p.DeparmentID.Equals(DeparmentID));
GetJson(false, "", out Deparment, out AllDeparment, false);
if (AllDeparment.Count > 0)
{
OrganizationTree MyDeparment = AllDeparment.FirstOrDefault(p => p.DeparmentID.Equals(DeparmentID));
if (MyDeparment != null && MyDeparment.DeparmentChild != null && MyDeparment.DeparmentChild.Count > 0)
{
GetAllDeparmentID(ref AllDeparmentID, MyDeparment);
ChildDeparmentID = AllDeparmentID.Select(p => p.DeparmentID).ToList();
}
}
if (IsGetParentDeparment)
{
ChildDeparmentID.Add(DeparmentID);
}
return(ChildDeparmentID.Distinct().ToList());
}
public async Task Handle(OrganizationCreatedEvent notification, CancellationToken cancellationToken)
{
var treeNode = new OrganizationTree(notification.Organization.Id, notification.Organization.Name, notification.Organization.OrganizationTypeId.ToString(), notification.Organization.ParentId);
await organTreeRepositoy.AddAsync(treeNode);
}
protected void Page_Load(object sender, EventArgs e)
{
string json = "";
string typeValue = Request["typeValue"];
int PageSize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
int PageNumber = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
string _UserID = CurrentUser.ID;
switch (typeValue)
{
case "SaveHstListByJArray":
{
string JArray = Request["JArray"] == null ? "" : Request["JArray"].ToString();
string SaveTableName = Request["SaveTableName"] == null ? "" : Request["SaveTableName"].ToString();
string UserID = CurrentUser.ID;
int type = Request["type"] == null ? 0 : Convert.ToInt32(Request["type"].ToString());
string UpdateCon = Request["UpdateCon"] == null ? "" : Request["UpdateCon"].ToString();
bool CreateNewHst = Request["CreateNewHst"] == null ? false : true;
bool SaveSameTable = Request["SaveSameTable"] == null ? true : false;
List <Hashtable> hsts = UnionsoftExcelHelper.CommonGetHstListByJArray(JArray, "", SaveTableName, UserID, false, true, true, true);
int o = UnionsoftExcelHelper.CommonSaveHstList(hsts, "", SaveTableName, UserID, UpdateCon);
json = o.ToString();
break;
}
case "CommonSelect":
{
string Sql = Request["Sql"] == null ? "" : Request["Sql"].ToString();
Services Resource = new Services();
string[] changePassWord = Common.getChangePassWord();
DataTable dt = Resource.Query(Sql, changePassWord[0], changePassWord[1], changePassWord[2]).Tables[0];
Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd";
json = Newtonsoft.Json.JsonConvert.SerializeObject(Common.SplitDataTable(dt, PageNumber, PageSize), timeConverter);
}
break;
case "GetDepartOrder":
{
string DepartID = Request["DepartID"] == null ? "" : Request["DepartID"].ToString();
string MyDepartID = Request["MyDepartID"] == null ? "" : Request["MyDepartID"].ToString();
json = Common.GetDepartOrder(DepartID, MyDepartID).ToString();
}
break;
case "QueryDepartment":
{
string DeptID = Request["DeptID"] == null ? "" : Request["DeptID"].ToString();
json = Common.QueryDepartment(DeptID).ToString();
}
break;
case "GetDataBySystemEmployeeInfo":
{
string argResID = Request["ResID"] == null ? "" : Request["ResID"].ToString();
//string UserDefinedSql = "select u.* ,b.EMP_ID,( CASE WHEN b.EMP_ID IS NULL OR b.EMP_ID = '' THEN 0 ELSE 1 end) 是否分配账号 from ( " + UnionsoftExcelHelper.SetBaseSql("", argResID, true) + " ) u left JOIN dbo.CMS_EMPLOYEE AS b ON u.登录账户 = b.EMP_ID where 1=1 ";
Services Resource = new Services();
string UserDefinedSql = " SELECT * FROM ( select ISNULL(u.ID,b.ID) ID ,u.RESID,b.EMP_ID,( CASE WHEN b.EMP_ID IS NULL OR b.EMP_ID = '' THEN 0 ELSE 1 end) 是否分配账号,( CASE WHEN u.员工编号 IS NULL OR u.员工编号 = '' THEN b.EMP_ID ELSE u.登录账户 end) 登录账户 ,( CASE WHEN u.员工编号 IS NULL OR u.员工编号 = '' THEN b.EMP_NAME ELSE u.员工名称 end) 员工名称,( CASE WHEN u.员工编号 IS NULL OR u.员工编号 = '' THEN b.EMP_EMAIL ELSE u.邮箱 end) 邮箱,( CASE WHEN u.员工编号 IS NULL OR u.员工编号 = '' THEN b.Domain_mobile ELSE u.手机 end) 手机,( CASE WHEN u.员工编号 IS NULL OR u.员工编号 = '' THEN '' ELSE u.员工编号 end) 员工编号,( CASE WHEN u.员工编号 IS NULL OR u.员工编号 = '' THEN b.HOST_ID ELSE u.部门ID end) 部门ID from ( " + Resource.GetSqlByResourceID(argResID, "", "") + " ) u full JOIN dbo.CMS_EMPLOYEE AS b ON u.登录账户 = b.EMP_ID ) h where 1=1 ";
string SortField = "";
string SortBy = "";
string Condition = Request["Condition"] == null ? "" : Request["Condition"].ToString();
if (!string.IsNullOrEmpty(Request["page"]))
{
PageNumber = Convert.ToInt32(Request["page"]);
}
if (Request["sort"] != null && !string.IsNullOrWhiteSpace(Request["sort"]))
{
SortField = " order by " + Request["sort"].ToString();
}
if (Request["order"] != null)
{
SortBy = Request["order"].ToString();
}
string[] changePassWord = Common.getChangePassWord();
DataTable dt = Resource.Query(UserDefinedSql + Condition + SortField + " " + SortBy, changePassWord[0], changePassWord[1], changePassWord[2]).Tables[0];
if (dt == null)
{
json = "";
}
else
{
int RowCount = dt.Rows.Count;
Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd";
string str = Newtonsoft.Json.JsonConvert.SerializeObject(Common.SplitDataTable(dt, PageNumber, PageSize), timeConverter);
json = "{\"total\":" + RowCount + ",\"rows\":" + str + "}";
}
}
break;
case "GetCompanyAllDepartment":
{
string CompanyID = Request["CompanyID"] == null ? "" : Request["CompanyID"].ToString();
string GetSqlStr = Request["GetSqlStr"] == null ? "" : Request["GetSqlStr"].ToString();
List <string> ChildDeparmentID = new List <string>();
ChildDeparmentID = OrganizationTree.GetChildDeparmentID(CompanyID);
json = Newtonsoft.Json.JsonConvert.SerializeObject(ChildDeparmentID);
}
break;
case "GetOrganizationTree":
{
string IsMultiSelect = Request["IsMultiSelect"] == null ? "" : Request["IsMultiSelect"].ToString();
string IsLoadUser = Request["IsLoadUser"] == null ? "" : Request["IsLoadUser"].ToString();
string IsSelectCompany = Request["IsSelectCompany"] == null ? "" : Request["IsSelectCompany"].ToString();
string argType = Request["argType"] == null ? "" : Request["argType"].ToString();
OrganizationTree Deparment = new OrganizationTree();
List <OrganizationTree> AllDeparment = new List <OrganizationTree>();
//GetDeparmentID = "523551382099";
//List<string> ChildDeparmentID = new List<string>();
//ChildDeparmentID = OrganizationTree.GetChildDeparmentID(GetDeparmentID);
json = OrganizationTree.GetJson(IsLoadUser == "1", argType, out Deparment, out AllDeparment, IsMultiSelect == "1", IsSelectCompany == "1");
}
break;
case "CommonGetData":
{
string argSql = Request["argSql"] == null ? "" : Request["argSql"].ToString();
string[] changePassWord = Common.getChangePassWord();
DataTable dt = CommonGetInfo.Resource.Query(argSql, changePassWord[0], changePassWord[1], changePassWord[2]).Tables[0];
if (dt == null)
{
json = "";
}
else
{
int RowCount = dt.Rows.Count;
Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd";
string str = Newtonsoft.Json.JsonConvert.SerializeObject(Common.SplitDataTable(dt, PageNumber, PageSize), timeConverter);
json = "{\"total\":" + RowCount + ",\"rows\":" + str + "}";
}
}
break;
case "ExecSql":
{
string Sql = Request["Sql"] == null ? "" : Request["Sql"].ToString();
string[] changePassWord = Common.getChangePassWord();
json = CommonGetInfo.Resource.ExecuteSql(Sql, changePassWord[0], changePassWord[1], changePassWord[2]).ToString();
}
break;
case "getSortDynamicFieldRecords":
{
string argCondition = Request["argCondition"] == null ? "" : Request["argCondition"].ToString();
string argResid = Request["argResid"] == null ? "" : Request["argResid"].ToString();
string argRecID = Request["argRecID"] == null ? "" : Request["argRecID"].ToString();
json = Common.getSortDynamicFieldRecords(argRecID, argResid, argCondition);
break;
}
}
Response.Write(json);
}
public async Task UpdateAsync(OrganizationTree entity)
{
_context.Set <OrganizationTree>().Update(entity);
await _context.SaveEntitiesAsync();
}
