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);
}
