TEAMModelOS/TEAMModelBI/Models/CommonFind.cs

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

namespace TEAMModelBI.Models
{
    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>
        /// 通过醍摩豆账户查询关联学校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 List<string>();
            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 List<string>();

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


        public static async Task<List<LessonCount>> FindLessonCount(CosmosClient cosmosClient, string dateTime) 
        {
            List<LessonCount> lessonCounts =new List<LessonCount>();
            DateTimeOffset dto = DateTimeOffset.Parse(dateTime);
            if (dto.Month <= 8 && dto.Month >= 3) 
            {
            
            }



            return lessonCounts;
        }
    }
}