TEAMModelOS/TEAMModelBI/Controllers/Census/ProductStatisController.cs

using Azure.Cosmos;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.Tool;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.Context.BI;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;

namespace TEAMModelBI.Controllers.Census
{
    [Route("product")]
    [ApiController]
    public class ProductStatisController : ControllerBase
    {
        //数据容器
        private readonly AzureCosmosFactory _azureCosmos;
        //钉钉提示信息
        private readonly DingDing _dingDing;
        private readonly Option _option;

        /// <summary>
        /// 软体产品
        /// </summary>
        private readonly Dictionary<string, string> _serials = new() { { "3222NIYD", "ezStation 2" }, { "J223IZ6M", "HiTeach STD" }, { "3222C6D2", "HiTeach TBL" }, { "J223IZAM", "HiTeach PRO" }, { "J2236ZCX", "HiTeach Lite" }, { "3222DNG2", "HiTeach Mobile" }, { "3222IAVN", "HiTeach Premium" }, { "BYJ6LZ6Z", "HiTeach5" } };

        /// <summary>
        /// 服务产品
        /// </summary>
        private readonly Dictionary<string, string> _services = new() { { "YMPCVCIM", "学情分析模组" }, { "IPDYZYLC", "智慧学校管理服务" }, { "3CLYJ6NP", "AClass ONE智慧学伴" }, { "IPALJ6NY", "数据储存服务空间" }, { "VABAJ6NV", "卷卡合一阅卷系统" } };

        public ProductStatisController(AzureCosmosFactory azureCosmos, DingDing dingDing, IOptionsSnapshot<Option> option) 
        {
            _azureCosmos = azureCosmos;
            _dingDing = dingDing;
            _option = option?.Value;
        }

        /// <summary>
        /// 统计模组数量   已对接
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-allcount")]
        public async Task<IActionResult> GetProductSum(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<ProductStatis> productStatis = new List<ProductStatis>();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.serial,c.service FROM c",requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProductSum") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var itemCount in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        ////所有的产品
                        //List<SchoolProductSumProdInfo> prodInfo = itemCount.GetProperty("prodinfo").ToObject<List<SchoolProductSumProdInfo>>();
                        //foreach (var tempProdInfo in prodInfo)
                        //{
                        //    ProductStatis tempPerod = productStatis.Find(x => x.prodCode.Equals(tempProdInfo.prodCode));
                        //    if (!string.IsNullOrEmpty($"{tempPerod}"))
                        //    {
                        //        tempPerod.Count += 1;
                        //    }
                        //    else
                        //    {
                        //        ProductStatis tempProd = new ProductStatis()
                        //        {
                        //            prodCode = tempProdInfo.prodCode,
                        //            prodName = tempProdInfo.prodName,
                        //            //dataType = tempProdInfo.dataType,
                        //            Count = 1,
                        //        };
                        //        productStatis.Add(tempProd);
                        //    }
                        //}

                        //软体产品
                        List<SchoolProductSumData> tempSerials = itemCount.GetProperty("serial").ToObject<List<SchoolProductSumData>>();
                        foreach (var serial in tempSerials)
                        {
                            ProductStatis tempPerod = productStatis.Find(x => x.prodCode.Equals(serial.prodCode));
                            if (!string.IsNullOrEmpty($"{tempPerod}"))
                            {
                                tempPerod.Count += 1;
                            }
                            else
                            {
                                ProductStatis tempProd = new ProductStatis()
                                {
                                    prodCode = serial.prodCode,
                                    prodName = _serials[serial.prodCode],
                                    //dataType = "",
                                    Count = 1,
                                };
                                productStatis.Add(tempProd);
                            }
                        }


                        //服务产品
                        List<SchoolProductSumData> service = itemCount.GetProperty("service").ToObject<List<SchoolProductSumData>>();
                        foreach (var ser in service)
                        {
                            ProductStatis tempPerod = productStatis.Find(x => x.prodCode.Equals(ser.prodCode));
                            if (!string.IsNullOrEmpty($"{tempPerod}"))
                            {
                                tempPerod.Count += 1;
                            }
                            else
                            {
                                ProductStatis tempProd = new ProductStatis()
                                {
                                    prodCode = ser.prodCode,
                                    prodName = _services[ser.prodCode],
                                    //dataType = "",
                                    Count = 1,
                                };
                                productStatis.Add(tempProd);
                            }
                        }
                    }
                }

                //if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                //{
                //    var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //    while (accounts.MoveNext())
                //    {
                //        JsonElement account = accounts.Current;
                //        List<SchoolProductSumProdInfo> prodInfo = account.GetProperty("prodinfo").ToObject<List<SchoolProductSumProdInfo>>();
                //        foreach (var tempProdInfo in prodInfo)
                //        {
                //            ProductStatis tempPerod = productStatis.Find(x => x.prodName.Equals(tempProdInfo.prodName));
                //            if (!string.IsNullOrEmpty($"{tempPerod}"))
                //            {
                //                tempPerod.Count += 1;
                //            }
                //            else
                //            {
                //                ProductStatis tempProd = new ProductStatis()
                //                {
                //                    prodCode = tempProdInfo.prodCode,
                //                    prodName = tempProdInfo.prodName,
                //                    dataType = tempProdInfo.dataType,
                //                    Count = 1,
                //                };
                //                productStatis.Add(tempProd);
                //            }
                //        }
                //    }
                //}
            }
            return Ok(new { state = 200, productStatis });
        }

        /// <summary>
        /// 依据学校ID查询产品信息   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-school")]
        public async Task<IActionResult> GetSchoolSum(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("schoolCode", out JsonElement schoolCode)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var clientContainer = _azureCosmos.GetCosmosClient();

            if ($"{site}".Equals(BIConst.Global))
                clientContainer = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<SchoolProductSerial> serials = new(); //软体
            List<SchoolProductService> services = new(); //服务
            List<SchoolProductHard> hards = new(); //硬体

            SchoolProductSum productSum = new(); //产品状态
            List<SchoolProductSumProdInfo> prodinfo = new(); //学校的产品信息
            List<SchoolProductSumData> serialRecord = new(); //软体购买记录
            List<SchoolProductSumDataService> serviceRecord = new(); //服务购买记录
            List<SchoolProductSumDataHard> hardRecord = new(); //硬体购买记录


            //取产品的数量
            var response = await clientContainer.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolCode.ToString(), new PartitionKey($"ProductSum"));
            if (response.Status == 200)
            {
                using var json = await JsonDocument.ParseAsync(response.ContentStream);
                productSum = json.ToObject<SchoolProductSum>();
                prodinfo = productSum.prodinfo;
                serialRecord = productSum.serial;
                serviceRecord = productSum.service;
                hardRecord = productSum.hard;
            }

            //软体
            await foreach (var item in clientContainer.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: "SELECT * FROM c WHERE c.dataType='serial'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{schoolCode}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        serials.Add(obj.ToObject<SchoolProductSerial>());
                    }
                }
            }

            //服务
            await foreach (var item in clientContainer.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: "SELECT * FROM c WHERE c.dataType='service'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{schoolCode}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        services.Add(obj.ToObject<SchoolProductService>());
                    }
                }
            }

            //硬体
            await foreach (var item in clientContainer.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: "SELECT * FROM c WHERE c.dataType='hard'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{schoolCode}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        hards.Add(obj.ToObject<SchoolProductHard>());
                    }
                }
            }

            //学校教室
            List<Room> rooms = new();
            await foreach (var item in clientContainer.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<Room>(queryText: $"select value(c) from c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Room-{schoolCode}") }))
            {
                rooms.Add(item);
            }

            return Ok(new { state = 200, serials, services, hards, productSum, rooms });
        }

        /// <summary>
        /// 顾问单个学校的模组
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assistschool")]
        public async Task<IActionResult> GetAssistSchoolId(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
            List<SchoolProduct> schoolProducts = new();
            foreach (var scid in schoolIds)
            {
                School school = new();

                var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(scid, new PartitionKey("Base"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school = json.ToObject<School>();
                }

                SchoolProduct sProduct = new() { id = scid, name = school != null ? school.name : scid };

                List<ProductStatis> productStatis = new();

                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.serial,c.service FROM c where c.id='{scid}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProductSum") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var itemCount in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        ////所有的产品信息
                        //List<SchoolProductSumProdInfo> prodInfo = itemCount.GetProperty("prodinfo").ToObject<List<SchoolProductSumProdInfo>>();
                        //foreach (var tempProdInfo in prodInfo)
                        //{
                        //    ProductStatis tempPerod = productStatis.Find(x => x.prodCode.Equals(tempProdInfo.prodCode));
                        //    if (!string.IsNullOrEmpty($"{tempPerod}"))
                        //    {
                        //        tempPerod.Count += 1;
                        //    }
                        //    else
                        //    {
                        //        ProductStatis tempProd = new ProductStatis()
                        //        {
                        //            prodCode = tempProdInfo.prodCode,
                        //            prodName = tempProdInfo.prodName,
                        //            //dataType = tempProdInfo.dataType,
                        //            Count = 1,
                        //        };
                        //        productStatis.Add(tempProd);
                        //    }
                        //}

                        //软体产品
                        List<SchoolProductSumData> tempSerials = itemCount.GetProperty("serial").ToObject<List<SchoolProductSumData>>();
                        foreach (var serial in tempSerials)
                        {
                            ProductStatis tempPerod = productStatis.Find(x => x.prodCode.Equals(serial.prodCode));
                            if (!string.IsNullOrEmpty($"{tempPerod}"))
                            {
                                tempPerod.Count += 1;
                            }
                            else
                            {
                                ProductStatis tempProd = new()
                                {
                                    prodCode = serial.prodCode,
                                    prodName = _serials[serial.prodCode],
                                    //dataType = "",
                                    Count = 1,
                                };
                                productStatis.Add(tempProd);
                            }
                        }

                        //服务产品
                        List<SchoolProductSumData> service = itemCount.GetProperty("service").ToObject<List<SchoolProductSumData>>();
                        foreach (var ser in service)
                        {
                            ProductStatis tempPerod = productStatis.Find(x => x.prodCode.Equals(ser.prodCode));
                            if (!string.IsNullOrEmpty($"{tempPerod}"))
                            {
                                tempPerod.Count += 1;
                            }
                            else
                            {
                                ProductStatis tempProd = new()
                                {
                                    prodCode = ser.prodCode,
                                    prodName = _services[ser.prodCode],
                                    //dataType = "",
                                    Count = 1,
                                };
                                productStatis.Add(tempProd);
                            }
                        }
                    }
                }

                sProduct.product = productStatis;
                schoolProducts.Add(sProduct);
            }

            return Ok(new { state = 200, schoolProducts }) ;
        }

        /// <summary>
        /// 所有模组统计
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-moduleanalys")]
        public async Task<IActionResult> GetModuleAnalys(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            long buyServiceCount = 0; //所有购买服务数量
            long buySerialsCount = 0; //所有购买软体数量

            long lastYearServiceCount = 0;   //去年购买服务数量
            long yearServiceCount = 0;    //今年购买服务数量

            long lastYearSerialsCount = 0;  //去年购买软体数量
            long yearSerialsCount = 0;     //今年购买软体数量

            var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year", false); //计算去年开始/结束时间
            var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year", false);                                     //计算今年开始/结束时间

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<RecSchoolAnalys> schoolAnalys = new();
                List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                foreach (var scId in schoolIds)
                {
                    //软体产品
                    List<SchoolProductSumData> Serials = new();
                    //软体产品
                    List<SchoolProductSumData> Services = new();
                    School school = new();
                    var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(scId, new PartitionKey("Base"));
                    if (response.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        school = json.ToObject<School>();
                    }

                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.serial,c.service FROM c where c.id='{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProductSum") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        foreach (var itemCount in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            //软体产品
                            Serials.AddRange(itemCount.GetProperty("serial").ToObject<List<SchoolProductSumData>>().Where(x => x.avaliable > 0).ToList());

                            //服务产品
                            Services.AddRange(itemCount.GetProperty("service").ToObject<List<SchoolProductSumData>>().Where(x => x.avaliable > 0).ToList());
                        }
                    }

                    long tempBuySerc = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c where c.dataType='service' and c.pk='Product'", $"Product-{scId}");
                    long tempBuySeri = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c where c.dataType='serial' and c.pk='Product'", $"Product-{scId}");

                    string sqlTxt = "select value(count(c.id)) from c where c.pk='Product' and c.dataType='{0}' and c.regDate>={1} and c.regDate<={2}";
                    long tempLastYearSerc = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "service", lastYearStart, lastYearEnd), $"Product-{scId}");  //计算去年购买的服务数量
                    long tempyearSerc = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "service", yearStart, yearEnd),  $"Product-{scId}");    //今年购买的服务数量

                    long tempLastYearSeri = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "serial", lastYearStart, lastYearEnd), $"Product-{scId}");  //计算去年购买的软体数量
                    long tempYearSeri = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "serial", yearStart, yearEnd), $"Product-{scId}");    //今年购买的软体数量

                    schoolAnalys.Add(new RecSchoolAnalys { id = school.id, name = school.name, picture = school.picture, size = school.size, scale = school.scale, buySerc = tempBuySerc, lastYearSerc = tempLastYearSerc, yearSerc = tempyearSerc, buySeri = tempBuySeri, lastYearSeri = tempLastYearSeri, yearSeri = tempYearSeri });

                    lastYearServiceCount += tempLastYearSerc;
                    yearServiceCount += tempyearSerc;
                    buyServiceCount += tempBuySerc;

                    lastYearSerialsCount += tempLastYearSeri;
                    yearSerialsCount += tempYearSeri;
                    buySerialsCount += tempBuySeri;
                }

                return Ok(new { state = 200, buyServiceCount, buySerialsCount, lastYearServiceCount, yearServiceCount, lastYearSerialsCount, yearSerialsCount, schoolAnalys });
            }
            else
            {

                //软体产品
                List<SchoolProductSumData> Serials = new();
                //软体产品
                List<SchoolProductSumData> Services = new();

                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.serial,c.service FROM c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProductSum") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var itemCount in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        //软体产品
                        Serials.AddRange(itemCount.GetProperty("serial").ToObject<List<SchoolProductSumData>>().Where(x => x.avaliable > 0).ToList());

                        //服务产品
                        Services.AddRange(itemCount.GetProperty("service").ToObject<List<SchoolProductSumData>>().Where(x => x.avaliable > 0).ToList());
                    }
                }

                var serialsCout = Serials.GroupBy(kv => new { kv.prodCode }).Select(y => new { key = y.Key.prodCode, value = y.Count() }).ToList();
                var servicesCout = Services.GroupBy(kv => new { kv.prodCode }).Select(y => new { key = y.Key.prodCode, value = y.Count() }).ToList();

                string containers = "School";
                buyServiceCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, "select value(count(c.id)) from c where c.dataType='service' and c.pk='Product'");
                buySerialsCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, "select value(count(c.id)) from c where c.dataType='serial' and c.pk='Product'");

                string sqlTxt = "select count(c.id) as totals from c where c.pk='Product' and c.dataType='{0}' and c.regDate>={1} and c.regDate<={2}";
                lastYearServiceCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(sqlTxt, "service", lastYearStart, lastYearEnd));  //计算去年购买的服务数量
                yearServiceCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(sqlTxt, "service", yearStart, yearEnd));    //今年购买的服务数量

                lastYearSerialsCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(sqlTxt, "serial", lastYearStart, lastYearEnd));  //计算去年购买的软体数量
                yearSerialsCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(sqlTxt, "serial", yearStart, yearEnd));    //今年购买的软体数量

                return Ok(new { state = 200, buyServiceCount, buySerialsCount, lastYearServiceCount, yearServiceCount, lastYearSerialsCount, yearSerialsCount, serialsCout, servicesCout });
            }
        }

        /// <summary>
        /// 顾问关联的学校模组
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-adviserschool")]
        public async Task<IActionResult> GetAdviserSchool(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            long buyServiceCount = 0;  //购买服务数量
            long buySerialsCount = 0;  //购买软体数量

            long lastYearServiceCount = 0;   //去年购买服务数量
            long yearServiceCount = 0;    //今年购买服务数量

            long lastYearSerialsCount = 0;  //去年购买软体数量
            long yearSerialsCount = 0;     //今年购买软体数量
            List<RecSchoolAnalys> schoolAnalys = new();
            var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year", false); //计算去年开始/结束时间
            var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year", false);                                     //计算今年开始/结束时间

            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

            foreach (var scId in schoolIds)
            {
                //软体产品
                List<SchoolProductSumData> Serials = new();
                //软体产品
                List<SchoolProductSumData> Services = new();
                School school = new();
                var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(scId, new PartitionKey("Base"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school = json.ToObject<School>();
                }

                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.serial,c.service FROM c where c.id='{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProductSum") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var itemCount in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        //软体产品
                        Serials.AddRange(itemCount.GetProperty("serial").ToObject<List<SchoolProductSumData>>().Where(x => x.avaliable > 0).ToList());

                        //服务产品
                        Services.AddRange(itemCount.GetProperty("service").ToObject<List<SchoolProductSumData>>().Where(x => x.avaliable > 0).ToList());
                    }
                }

                long tempBuySerc = await CommonFind.GetSqlValueCount(cosmosClient, "select value(count(c.id)) from c where c.dataType='service' and c.pk='Product'", "School", $"Product-{scId}");
                long  tempBuySeri = await CommonFind.GetSqlValueCount(cosmosClient, "select value(count(c.id)) from c where c.dataType='serial' and c.pk='Product'", "School", $"Product-{scId}");

                string sqlTxt = "select value(count(c.id)) from c where c.pk='Product' and c.dataType='{0}' and c.regDate>={1} and c.regDate<={2}";
                long  tempLastYearSerc = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "service", lastYearStart, lastYearEnd), $"Product-{scId}");  //计算去年购买的服务数量
                long tempyearSerc = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "service", yearStart, yearEnd), $"Product-{scId}");    //今年购买的服务数量

                long tempLastYearSeri = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "serial", lastYearStart, lastYearEnd),  $"Product-{scId}");  //计算去年购买的软体数量
                long tempYearSeri = await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(sqlTxt, "serial", yearStart, yearEnd), $"Product-{scId}");    //今年购买的软体数量

                schoolAnalys.Add(new RecSchoolAnalys { id = school.id, name = school.name, picture = school.picture, size = school.size, scale = school.scale, buySerc = tempBuySerc, lastYearSerc = tempLastYearSerc, yearSerc = tempyearSerc, buySeri = tempBuySeri, lastYearSeri = tempLastYearSeri, yearSeri = tempYearSeri });

                lastYearServiceCount += tempLastYearSerc;
                yearServiceCount += tempyearSerc;
                buyServiceCount += tempBuySerc;

                lastYearSerialsCount += tempLastYearSeri;
                yearSerialsCount += tempYearSeri;
                buySerialsCount += tempBuySeri;
            }

            return Ok(new { state = 200, buyServiceCount, buySerialsCount, lastYearServiceCount, yearServiceCount, lastYearSerialsCount, yearSerialsCount, schoolAnalys });
        }

        /// <summary>
        /// 显示学校的模组分析
        /// </summary>
        public record RecSchoolAnalys 
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public int size { get; set; }
            public int scale { get; set; }
            public long buySerc { get; set; }
            public long lastYearSerc { get; set; }
            public long yearSerc { get; set; }
            public long buySeri { get; set; }
            public long lastYearSeri { get; set; }
            public long yearSeri { get; set; }
        }

        public record SchoolProduct 
        {
            public string id { get; set; }

            public string name { get; set; }

            public List<ProductStatis> product { get; set; }
        }

        public record ProductStatis 
        {
            public string prodCode { get; set; }

            public string prodName { get; set; }

            //public string dataType { get; set; }

            public long Count { get; set; }
        }
         
    }
}