TEAMModelOS/TEAMModelBI/Tool/CommonFind.cs

using Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.Models;
using TEAMModelOS.SDK.Models.Cosmos.Common;

namespace TEAMModelBI.Tool
{
    public class CommonFind
    {
        /// <summary>
        /// 查询总数不带code
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="sqlTxt"></param>
        /// <param name="containerId">数据库表集合</param>
        /// <returns>返回总数</returns>
        public static async Task<long> FindTotals(CosmosClient cosmosClient, string sqlTxt, List<string> containerId)
        {
            long totals = 0;
            foreach (var container in containerId)
            {
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt32() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            totals += obj.GetProperty("totals").GetInt64();
                        }
                    }
                }
            }

            return totals;
        }

        /// <summary>
        /// 查询总数带code
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="sqlTxt">sql语句</param>
        /// <param name="containerId">数据库表</param>
        /// <param name="code">数据分区键</param>
        /// <returns>返回总数</returns>
        public static async Task<int> FindTotals(CosmosClient cosmosClient, string sqlTxt, string containerId, string code) 
        {
            int totals = 0;

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", containerId).GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        totals += obj.GetProperty("totals").GetInt32();
                    }
                }
            }

            return totals;
        }

        /// <summary>
        /// 查询学校教师角色列表
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="schoolId">学校Id</param>
        /// <param name="roles">查询的角色</param>
        /// <returns>返回学校角色列表</returns>
        public static async Task<List<SchoolTeacherRoles>> FindSchoolRoles(CosmosClient cosmosClient,string schoolId,string roles)
        {
            List<SchoolTeacherRoles> strs = new();
            try
            {
                string managerSql = $"SELECT DISTINCT REPLACE(c.code, 'Teacher-', '') AS schoolId, c.id, c.name FROM c WHERE ARRAY_CONTAINS(c.roles, '{roles}', true) AND c.pk = 'Teacher' AND c.status = 'join' AND c.code = 'Teacher-{schoolId}'";
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: managerSql, requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        SchoolTeacherRoles str = new()
                        {
                            tmdId = obj.GetProperty("id").GetString(),
                            tmdName = obj.GetProperty("name").GetString()
                        };

                        strs.Add(str);
                    }
                }
                return strs;
            }
            catch
            {
                return strs;
            }

        }

        /// <summary>
        /// 通过醍摩豆账户查询关联学校ID
        /// </summary>
        /// <param name="cosmosClient">cosmosDB连接</param>
        /// <param name="tmdId">醍摩豆账户</param>
        /// <returns>返回顾问相关的学校ID集合</returns>
        public static async Task<List<string>> FindSchoolIds(CosmosClient cosmosClient, string tmdId)
        {
            List<string> schoolIds = new();
            string schoolSql = $"SELECT DISTINCT REPLACE(c.code,'Teacher-','') AS schoolId,c.code,c.roles,c.id,c.name From c where ARRAY_CONTAINS(c.roles,'assist',true) AND c.pk = 'Teacher' AND c.status = 'join' AND c.id='{tmdId}'";

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: schoolSql, requestOptions: new QueryRequestOptions() { }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                {
                    schoolIds.Add(obj.GetProperty("schoolId").GetString());
                }
            }

            return schoolIds;
        }

        /// <summary>
        /// 通过语句查询学校ID
        /// </summary>
        /// <param name="cosmosClient">cosmosDB连接</param>
        /// <param name="sqlTxt">sql语句</param>
        /// <param name="code">数据分区键</param>
        /// <returns>返回学校ID的集合</returns>
        public static async Task<List<string>> FindSchoolIds(CosmosClient cosmosClient, string sqlTxt, string code)
        {
            List<string> schoolIds = new();

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                {
                    schoolIds.Add(obj.GetProperty("id").GetString());
                }
            }

            return schoolIds;
        }

        /// <summary>
        /// 依据学校编号查询学校信息和学校相关的信息
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="schoos"></param>
        /// <returns></returns>
        public static async Task<List<AssistSchool>> FindAssistSchools(CosmosClient cosmosClient, List<string> schoos)
        {
            List<AssistSchool> assistSchools = new();
            foreach (var id in schoos)
            {
                string sqlTxt = $"select c.id,c.code,c.schoolCode,c.name,c.region,c.province,c.city,c.dist,c.size,c.address,c.picture,c.type,c.scale,c.areaId,c.standard from c where c.id='{id}'";
                await foreach (var itemSchool in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<AssistSchool>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                {
                    assistSchools.Add(itemSchool);
                }
            }

            foreach (var school in assistSchools)
            {
                var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(school.id, new PartitionKey("ProductSum"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school.serial = json.RootElement.GetProperty("serial").GetArrayLength();
                    school.service = json.RootElement.GetProperty("service").GetArrayLength();
                    school.hard = json.RootElement.GetProperty("hard").GetArrayLength();
                }

                school.assists = await FindSchoolRoles(cosmosClient, school.id, "assist");
            }
            
            return assistSchools;
        }


        /// <summary>
        /// 依据学校查询教师列表
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="schools"></param>
        /// <returns></returns>
        public static async Task<List<string>> FindRolesId(CosmosClient cosmosClient, List<string> schools, string roles = null)
        {
            string rolesName = "teacher";
            if (roles != null) 
            {
                rolesName = roles;
            }

            List<string> teachers = new();
            foreach (var school in schools) 
            {
                string sqlTxt = $"select c.id from c where ARRAY_CONTAINS(c.roles,'{rolesName}',true) and c.status = 'join'";
                await foreach (var itemTeac in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() {PartitionKey =new PartitionKey($"Teacher-{school}") }))
                {
                    using var json = await JsonDocument.ParseAsync(itemTeac.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        teachers.Add(obj.GetProperty("id").GetString());
                    }
                }
            }
            return teachers;
        }

    }
}