TEAMModelOS/TEAMModelOS.SDK/Models/Service/BI/CosmosQueryHelper.cs

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace TEAMModelOS.SDK.Models.Service.BI
{
    public class CosmosQueryHelper
    {
        /// <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").GetItemQueryStreamIteratorSql(queryText: managerSql, requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    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, string roles = "assist", bool isMany = false)
        {
            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.status = 'join' AND c.id='{tmdId}'";
            //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: schoolSql, requestOptions: new QueryRequestOptions() { }))
            //{
            //    using var json = await JsonDocument.ParseAsync(item.Content);
            //    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
            //    {
            //        schoolIds.Add(obj.GetProperty("schoolId").GetString());
            //    }
            //}

            StringBuilder schoolSql = new($"SELECT value(REPLACE(c.code, 'Teacher-', '')) FROM c where c.pk='Teacher' and c.id='{tmdId}'");
            if (isMany == true)
                schoolSql.Append($" and (array_contains(c.roles,'assist',true) or array_contains(c.roles,'sales',true))");
            else
                schoolSql.Append($" and array_contains(c.roles,'{roles}',true)");
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: schoolSql.ToString(), requestOptions: new QueryRequestOptions() { }))
            {
                schoolIds.Add(item);
            }

            return schoolIds;
        }

        /// <summary>
        /// 通过sql语句查询单列集合
        /// </summary>
        /// <param name="cosmosClient">连接字符</param>
        /// <param name="container">容器名称</param>
        /// <param name="sqlTxt">sql语句 带value</param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<List<string>> GetValueSingle(CosmosClient cosmosClient, string container, string sqlTxt, string code = null)
        {
            List<string> ids = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryIteratorSql<string>(queryText: sqlTxt, requestOptions: !string.IsNullOrEmpty(code) ? new QueryRequestOptions() { PartitionKey = new PartitionKey(code) } : new QueryRequestOptions() { }))
            {
                ids.Add(item);
            }

            return ids;
        }

        /// <summary>
        /// 通过sql语句查询单列集合
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="container"></param>
        /// <param name="sqlTxt"></param>
        /// <param name="single"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public async Task<List<string>> GetStreamSingle(CosmosClient cosmosClient, string container, string sqlTxt, string single = "id", string code = null)
        {
            List<string> ids = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryStreamIteratorSql(queryText: sqlTxt, requestOptions: !string.IsNullOrEmpty(code) ? new QueryRequestOptions() { PartitionKey = new PartitionKey(code) } : new QueryRequestOptions() { }))
            {
                using var json = await JsonDocument.ParseAsync(item.Content);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                {
                    ids.Add(obj.GetProperty(single).GetString());
                }
            }

            return ids;
        }

        /// <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>> FindScIds(CosmosClient cosmosClient, string sqlTxt, string code)
        {
            List<string> schoolIds = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            {
                schoolIds.Add(item);
            }

            //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            //{
            //    using var json = await JsonDocument.ParseAsync(item.Content);
            //    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="schools">学校列表</param>
        /// <param name="roles">不传默认教师角色</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 value(c.id) from c where ARRAY_CONTAINS(c.roles,'{rolesName}',true) and c.status = 'join'";
                await foreach (var itemTeac in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{school}") }))
                {
                    teachers.Add(itemTeac);
                }
                //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").GetItemQueryStreamIteratorSql(queryText: sqlTxt, requestOptions: new QueryRequestOptions() {PartitionKey =new PartitionKey($"Teacher-{school}") }))
                //{
                //    using var json = await JsonDocument.ParseAsync(itemTeac.Content);
                //    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                //    {
                //        teachers.Add(obj.GetProperty("id").GetString());
                //    }
                //}
            }
            return teachers;
        }

        /// <summary>
        /// 单个容器数据统计
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="container"></param>
        /// <param name="SqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<int> GetSqlValueCount(CosmosClient cosmosClient, string container, string SqlTxt, string code = null)
        {
            int totals = 0;
            try
            {
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryIteratorSql<int>(queryText: SqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey($"{code}") }))
                {
                    totals = item;
                }
            }
            catch { }

            return totals;
        }

        /// <summary>
        /// 多个容器数据统计
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="containers"></param>
        /// <param name="SqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<int> GetSqlValueCount(CosmosClient cosmosClient, List<string> containers, string SqlTxt, string code = null)
        {
            int totals = 0;
            foreach (var container in containers)
            {
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryIteratorSql<int>(queryText: SqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
                {
                    totals += item;
                }
            }

            return totals;
        }

        /// <summary>
        /// 单个容器数据统计  double
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="container"></param>
        /// <param name="SqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<double> GetSqlValueDoubleCounnt(CosmosClient cosmosClient, string container, string SqlTxt, string code = null)
        {
            double totals = 0;
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryIteratorSql<double>(queryText: SqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            {
                totals = item;
            }

            return totals;
        }

        /// <summary>
        /// 多个容器数据统计  double
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="container"></param>
        /// <param name="SqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<double> GetSqlValueDoubleCounnt(CosmosClient cosmosClient, List<string> containers, string SqlTxt, string code = null)
        {
            double totals = 0;
            foreach (var container in containers)
            {
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryIteratorSql<double>(queryText: SqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
                {
                    totals += item;
                }
            }

            return totals;
        }

        /// <summary>
        /// 通过SQL 语句返回实体信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cosmosClient"></param>
        /// <param name="containers"></param>
        /// <param name="sqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<List<T>> GetObject<T>(CosmosClient cosmosClient, List<string> containers, string sqlTxt, string code = null)
        {
            List<T> temps = new();
            foreach (var container in containers)
            {
                await foreach (var items in cosmosClient.GetContainer("TEAMModelOS", container).GetItemQueryIteratorSql<T>(queryText: sqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
                {
                    temps.Add(items);
                }
            }
            return temps;
        }

        /// <summary>
        /// 通过SQL 语句返回实体信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cosmosClient"></param>
        /// <param name="containers"></param>
        /// <param name="sqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<List<T>> GetObject<T>(CosmosClient cosmosClient, string containers, string sqlTxt, string code = null)
        {
            List<T> temps = new();
            await foreach (var items in cosmosClient.GetContainer("TEAMModelOS", containers).GetItemQueryIteratorSql<T>(queryText: sqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            {
                temps.Add(items);
            }
            return temps;
        }

        /// <summary>
        /// 查询某个字段信息
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="containers"></param>
        /// <param name="sqlTxt"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<string> GetStr(CosmosClient cosmosClient, string containers, string sqlTxt, string code = null)
        {
            string str = null;

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", containers).GetItemQueryIteratorSql<string>(queryText: sqlTxt, requestOptions: string.IsNullOrEmpty(code) ? new QueryRequestOptions() { } : new QueryRequestOptions() { PartitionKey = new PartitionKey(code) }))
            {
                str = item;
            }

            return str;
        }
    }
}