public List <GetingeProductPrants> GetGetingeDeptChildrenList(int deptId)
{
List <GetingeProductPrants> list = new List <GetingeProductPrants>();
string sql = @"WITH ProductTree
AS ( SELECT [dept_id]
,[parent_id]
,[dept_name]
,[product_id]
FROM [wxtest_db].[dbo].[Getinge_Dept_Product]
WHERE [dept_id] = " + deptId + @" --需要查找的父节点
UNION ALL
SELECT [wxtest_db].[dbo].[Getinge_Dept_Product].*
FROM ProductTree
JOIN [wxtest_db].[dbo].[Getinge_Dept_Product] ON ProductTree.[dept_id]= [wxtest_db].[dbo].[Getinge_Dept_Product].parent_id
)
SELECT t.*,b.[product_name],b.[product_mark],b.[product_level]
FROM ProductTree t
LEFT JOIN
[wxtest_db].[dbo].[Getinge_Product] b
ON t.[product_id]= b.[product_id]
ORDER BY [parent_id];";
using (SqlDataReader reader = DBHerlper.GetReader(sql))
{
list = GetEcmoUserListByRow(reader);
}
return(list);
}
public List <GetingeProductPrants> GetGetingeProductPrantsList(int productId)
{
List <GetingeProductPrants> list = new List <GetingeProductPrants>();
string sql = @"WITH ProductTree
AS ( SELECT [product_id] ,
[parent_id] ,
[dept_id] ,
[product_name] ,
[product_mark] ,
[product_level]
FROM [wxtest_db].[dbo].[Getinge_Product]
WHERE [product_id] = " + productId + @" --需要查找的子节点
UNION ALL
SELECT [wxtest_db].[dbo].[Getinge_Product].*
FROM ProductTree
JOIN [wxtest_db].[dbo].[Getinge_Product] ON ProductTree.parent_id = [wxtest_db].[dbo].[Getinge_Product].[product_id]
)
SELECT [product_id] ,
[parent_id] ,
[dept_id] ,
[product_name] ,
[product_mark] ,
[product_level]
FROM ProductTree
ORDER BY [parent_id];";
using (SqlDataReader reader = DBHerlper.GetReader(sql))
{
list = GetEcmoUserListByRow(reader);
}
return(list);
}
/// <summary>
/// 查询指定序号后N条
/// </summary>
/// <param name="count">条数</param>
/// <param name="index">序号</param>
/// <returns></returns>
public List <EcmoUser> GetEcmoUserList()
{
List <EcmoUser> list = new List <EcmoUser>();
string sql = @" SELECT d.* ,
enroll_name
FROM [wxtest_db].[dbo].Ecmo_User d
LEFT JOIN ( SELECT [openid] ,
STUFF(( SELECT ',' + enroll_name
FROM [wxtest_db].[dbo].[Ecmo_Enroll] a
WHERE b.[openid] = a.[openid]
FOR
XML PATH('')
), 1, 1, '') enroll_name
FROM [wxtest_db].[dbo].[Ecmo_Enroll] b
GROUP BY [openid]
) c ON d.[openid] = c.[openid]";
using (SqlDataReader reader = DBHerlper.GetReader(sql))
{
list = GetEcmoUserListByRow(reader);
}
return(list);
}
