public int selectNormalChildrenDeptById(long deptId) { StringBuilder sql = new StringBuilder(); sql.Append(@" select count(*) as Tcount from sys_dept where status = 0 and del_flag = '0' and find_in_set(@deptId, ancestors)"); MySqlParameter[] parametera = new MySqlParameter[1] { new MySqlParameter("userId", MySqlDbType.Int64) }; parametera[0].Value = deptId; DBCount models = _dataContext.GetDatabase().SqlQuery <DBCount>(sql.ToString(), parametera).FirstOrDefault(); return((models != null)? models.Tcount : 0); }
public int checkDeptExistUser(long deptId) { StringBuilder sql = new StringBuilder(); sql.Append(@" select count(1) as Tcount from sys_user where dept_id = @deptId and del_flag = '0'"); MySqlParameter[] parameters = new MySqlParameter[1] { new MySqlParameter("deptId", MySqlDbType.Int64) }; parameters[0].Value = deptId; DBCount models = _dataContext.GetDatabase().SqlQuery <DBCount>(sql.ToString(), parameters).FirstOrDefault(); return((models != null) ? models.Tcount : 0); }
public List <CountNum> GetCountTable(List <string> levels, List <string> componentsCode) { tableList = new List <CountNum>(); dbList = new List <DBCount>(); string strFloor = ""; for (int i = 0; i < levels.Count; i++) { if (levels.Count == 0) { break; } if (levels.Count == 1) { strFloor = "'" + levels[i] + "'"; break; } if (i == levels.Count - 1) { strFloor = strFloor + "'" + levels[i] + "'"; break; } else { strFloor = strFloor + "'" + levels[i] + "',"; } } string strCode = ""; for (int j = 0; j < componentsCode.Count; j++) { if (componentsCode.Count == 0) { break; } if (componentsCode.Count == 1) { strCode = "'" + componentsCode[j] + "'"; break; } if (j == componentsCode.Count - 1) { strCode = strCode + "'" + componentsCode[j] + "'"; break; } else { strCode = strCode + "'" + componentsCode[j] + "',"; } } SqliteConnection conn2 = Tools.Instance.SqlConnection(); SqliteCommand cmd2 = conn2.CreateCommand(); cmd2.CommandText = "select * from InventoryDB where Code in(" + strCode + ") and ID in( Select ID from ElementDB where Layer In(" + strFloor + "))"; // Debug.Log(cmd2.CommandText); SqliteDataReader dr2 = cmd2.ExecuteReader(); //xmlNodePathDic.Clear(); if (dr2.HasRows) { while (dr2.Read()) { DBCount dbc = new DBCount(); dbc.Code = dr2.GetString(1); dbc.Detail = dr2.GetString(2); dbc.Quantity = dr2.GetDouble(3); dbList.Add(dbc); } } var groupDB = dbList.GroupBy(g => g.Code); foreach (var item in groupDB) { string codestr = item.First().Code; string projectName = xmldoc.SelectSingleNode(xmlNodePathDic[item.First().Code.Substring(0, 9)]).Name; XmlNodeList nodeList = xmldoc.SelectSingleNode(xmlNodePathDic[item.First().Code.Substring(0, 9)]).ChildNodes; string myDetail = ""; string detail = item.First().Detail.Replace("'", ""); string[] detailArray = detail.Split('$'); foreach (XmlNode node in nodeList) { if (node.Name == "项目特征") { for (int i = 0; i < node.ChildNodes.Count; i++) { int index = node.ChildNodes[i].InnerText.IndexOf('['); if (index != -1) { if (i != node.ChildNodes.Count - 1) { myDetail += node.ChildNodes[i].InnerText.Substring(0, index) + detailArray[i] + "\n"; } else { myDetail += node.ChildNodes[i].InnerText.Substring(0, index) + detailArray[i]; } // Debug.Log("得到的项目特征:" + myDetail); } } } } string strUnit = xmldoc.SelectSingleNode(xmlNodePathDic[item.First().Code.Substring(0, 9)] + "//计量单位").InnerText; double total = item.Sum(s => s.Quantity); //添加数据到表 CountNum cn = new CountNum(); cn.ProjectNum = codestr; cn.ProjectName = projectName; cn.ProjectFeature = myDetail; cn.ProjectUnit = strUnit; cn.ProjectQuantities = total.ToString("0.00"); tableList.Add(cn); } return(tableList); }