TEAMModelOS/TEAMModelBI/Controllers/BITmid/TmidController.cs

using Azure.Cosmos;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Cosmos.Table;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Services;

namespace TEAMModelBI.Controllers.BITmid
{
    [Route("tmid")]
    [ApiController]
    public class TmidController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly AzureRedisFactory _azureRedis;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly IConfiguration _configuration;
        private readonly HttpTrigger _httpTrigger;

        public TmidController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, AzureRedisFactory azureRedis, DingDing dingDing, IOptionsSnapshot<Option> option, IConfiguration configuration, HttpTrigger httpTrigger)
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _azureRedis = azureRedis;
            _dingDing = dingDing;
            _option = option?.Value;
            _configuration = configuration;
            _httpTrigger = httpTrigger;
        }

        /// <summary>
        /// 取得TMID綜合資料
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        //[AuthToken(Roles = "admin")]
        [HttpPost("get-tmidstics")]
        public async Task<IActionResult> GetTmidStics(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("tmids", out JsonElement tmidsJobj)) return BadRequest();//TMID(array)
                var tmids = tmidsJobj.Deserialize<List<string>>();
                if (tmids.Count is 0) return BadRequest();

                //服務Client端
                var cosmosClientIes5 = _azureCosmos.GetCosmosClient(); //CosmosDB IES5
                var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2"); //CosmosDB CSV2
                var storageClientCsv2 = _azureStorage.GetCloudTableClient(name: "CoreServiceV2"); //Storage CSV2
                var tableCouponClient = storageClientCsv2.GetTableReference("Coupon");
                var tablePointsClient = storageClientCsv2.GetTableReference("Points");
                var redisClient = _azureRedis.GetRedisClient(4);

                //存放user資料
                Dictionary<string, TmidStics> tmidDic = new();

                QueryDefinition query =
                       new QueryDefinition(@"SELECT c.id, c.name, c.mobile, c.mail FROM c WHERE (ARRAY_CONTAINS(@key, c.id) OR ARRAY_CONTAINS(@key, c.mobile))")
                       .WithParameter("@key", tmids);
                await foreach (var item in cosmosClientCsv2
                            .GetContainer("Core", "ID2")
                            .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string id = doc.GetProperty("id").GetString();
                            if(!tmids.Contains(id)) tmids.Add(id);
                            //基本資料
                            TmidStics tmidStics = (tmidDic.ContainsKey(id)) ? tmidDic[id] : new() { id = id };
                            string tmidName = doc.GetProperty("name").GetString();
                            string tmidMobile = GenDataMask(doc.GetProperty("mobile").GetString(), "mobile");
                            string tmidMail = GenDataMask(doc.GetProperty("mail").GetString(), "mail");

                            //票券
                            var usersCoupons = tableCouponClient.Get<DynamicTableEntity>(id);
                            foreach (var coupon in usersCoupons)
                            {
                                tmidStics.coupons.Add(
                                    new TmidCoupon()
                                    {
                                        code = coupon.RowKey,
                                        exchange = coupon.Properties["exchangeTime"].DateTimeOffsetValue.Value.ToUnixTimeSeconds(),
                                    });
                            }

                            //登入各服務的時間
                            var loginTime = await redisClient.HashGetAllAsync(id);
                            foreach (var t in loginTime)
                            {
                                if (!t.Name.StartsWith("HiTeach") && !_dicClientIds.ContainsKey(t.Name)) continue;
                                tmidStics.login.Add(
                                    new TmidLoginTime()
                                    {
                                        product = t.Name.StartsWith("HiTeach") ? t.Name.ToString().Split("-")[0] : _dicClientIds[t.Name],
                                        time = (long)t.Value
                                    });
                            }

                            //積分
                            var usersPoints = tablePointsClient.Get("Points", id);
                            tmidStics.points.points = (usersPoints != null) ? usersPoints.Properties["Points"].Int32Value.Value : 0;
                            tmidStics.points.level = (usersPoints != null) ? usersPoints.Properties["Level"].Int32Value.Value : 0;
                            tmidStics.points.balance = (usersPoints != null) ? usersPoints.Properties["Balance"].Int32Value.Value : 0;
                            //個人服務授權
                            tmidStics.prod = await getTMIDAuthService(cosmosClientCsv2, id, "", true);

                            tmidDic.Add(id, tmidStics);
                        }
                    }
                }

                //ID進階資料
                QueryDefinition queryex = new QueryDefinition(@"SELECT c.id, c.name, c.mobile, c.mail, c.country, c.province, c.city, c.schoolCode, c.schoolCodeW FROM c WHERE (ARRAY_CONTAINS(@key, c.id) OR ARRAY_CONTAINS(@key, c.mobile))")
                       .WithParameter("@key", tmids);
                await foreach (var item in cosmosClientCsv2
                            .GetContainer("Core", "ID2")
                            .GetItemQueryStreamIterator(queryex, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base-ex") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string id = doc.GetProperty("id").GetString();
                            TmidStics tmidStics = (tmidDic.ContainsKey(id)) ? tmidDic[id] : new() { id = id };
                            if (string.IsNullOrWhiteSpace(tmidStics.name)) tmidStics.name = doc.GetProperty("name").GetString();
                            if (string.IsNullOrWhiteSpace(tmidStics.mobile)) tmidStics.mobile = GenDataMask(doc.GetProperty("mobile").GetString(), "mobile");
                            if (string.IsNullOrWhiteSpace(tmidStics.mail)) tmidStics.mail = GenDataMask(doc.GetProperty("mail").GetString(), "mail");
                            tmidStics.country = doc.GetProperty("country").GetString();
                            tmidStics.province = doc.GetProperty("province").GetString();
                            tmidStics.city = doc.GetProperty("city").GetString();
                            tmidStics.schoolCode = (doc.TryGetProperty("schoolCode", out JsonElement schCode)) ? schCode.GetString() : string.Empty;
                            tmidStics.schoolCodeW = (doc.TryGetProperty("schoolCodeW", out JsonElement schCodeW)) ? schCodeW.GetString() : string.Empty;
                        }
                    }
                }

                //蘇格拉底資料
                query = new QueryDefinition(@"SELECT * FROM c WHERE (ARRAY_CONTAINS(@key, c.id))")
                      .WithParameter("@key", tmidDic.Keys.ToList());

                await foreach (var item in cosmosClientCsv2
                            .GetContainer("Core", "ID2")
                            .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("sokrates") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string id = doc.GetProperty("id").GetString();
                            if (doc.TryGetProperty("hiteach_data", out var elementHiteachData))
                            {
                                if (elementHiteachData.TryGetProperty("total", out var elementTotal))
                                {
                                    if (elementTotal.TryGetProperty("t_data", out var tDataValue)) tmidDic[id].sokrates.t_data = tDataValue.GetString();
                                    if (elementTotal.TryGetProperty("t_green", out var tGreenValue)) tmidDic[id].sokrates.t_green = tGreenValue.GetString();
                                    if (elementTotal.TryGetProperty("duration", out var durationValue)) tmidDic[id].sokrates.duration = durationValue.GetString();
                                    if (elementTotal.TryGetProperty("attendance", out var attendanceValue)) tmidDic[id].sokrates.attendance = attendanceValue.GetString();
                                    if (elementTotal.TryGetProperty("interaction", out var interactionValue)) tmidDic[id].sokrates.interaction = interactionValue.GetString();
                                    if (elementTotal.TryGetProperty("learning_duration", out var learningDurationValue)) tmidDic[id].sokrates.learning_duration = learningDurationValue.GetString();
                                }
                            }
                        }
                    }
                }

                //IES5
                ///個人空間使用狀況
                foreach (KeyValuePair<string, TmidStics> dicItem in tmidDic)
                {
                    string tmidNow = dicItem.Key;
                    var (usedSize, teach, total, surplus, catalog) = await BlobService.GetSurplusSpace(tmidNow, "private", _option.Location, _azureCosmos, _azureRedis, _azureStorage, _dingDing, _httpTrigger);
                    tmidDic[tmidNow].ies5.usedSize = usedSize;
                    tmidDic[tmidNow].ies5.teachSize = teach * 1073741824;
                    tmidDic[tmidNow].ies5.totalSize = total * 1073741824;
                    tmidDic[tmidNow].ies5.surplusSize = surplus;
                }

                //輸出
                List<object> data = new();
                foreach (KeyValuePair<string, TmidStics> dicItem in tmidDic)
                {
                    data.Add(dicItem.Value);
                }
                return Ok(new { data });
            }
            catch (Exception ex)
            {
                //await _dingDing.SendBotMsg($"BI，{_option.Location} /tmid/get-tmidstics \n {ex.Message}\n{ex.StackTrace}", GroupNames.台北開發測試群組);
                return BadRequest();
            }
        }

        //取得TMID服務授權週期
        public async Task<List<object>> getTMIDAuthService(CosmosClient cosmosClientCsv2, string tmid, string prodCode, bool validFlg)
        {
            long now = DateTimeOffset.UtcNow.ToUnixTimeSeconds();
            var qryOption = new QueryRequestOptions() { PartitionKey = new PartitionKey("servicePeriod") };
            string whereSql = $"c.saleClient.tmid = '{tmid}'";
            if (!string.IsNullOrWhiteSpace(prodCode)) whereSql += $" AND c.prodCode = '{prodCode}'";
            if (validFlg) whereSql += $" AND c.startDate <= {now} AND {now} <= c.endDate";
            string sql = $"SELECT c.id, c.prodCode, c.type, c.startDate, c.endDate, c.number, c.unit, c.aprule FROM c WHERE {whereSql}";
            var client = cosmosClientCsv2.GetContainer("Habb", "Auth");
            var result = new List<object>();
            await foreach (var item in client.GetItemQueryStreamIterator(queryText: sql, requestOptions: qryOption))
            {
                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())
                    {
                        result.Add(
                        new
                        {
                            id = obj.GetProperty("id").GetString(),
                            prodCode = obj.GetProperty("prodCode").GetString(),
                            type = obj.GetProperty("type").GetInt32(),
                            startDate = obj.GetProperty("startDate").GetInt64(),
                            endDate = obj.GetProperty("endDate").GetInt64(),
                            number = obj.GetProperty("number").GetInt32(),
                            aprule = obj.GetProperty("aprule")
                        });
                    }
                }
            }
            return result;
        }

        //取得TMID購買紀錄
        public async Task<List<Ies5OrderHis>> getTMIDAuthOrder(CosmosClient cosmosClientCsv2, string tmid, string prodCode)
        {
            SortedDictionary<string, Ies5OrderHis> OrderDic = new SortedDictionary<string, Ies5OrderHis>();
            var qryOption = new QueryRequestOptions() { PartitionKey = new PartitionKey("order") };
            //服務 ※序號、硬體 不列入購買紀錄
            string strQueryV = $"SELECT * FROM c WHERE IS_DEFINED(c.saleClient.tmid) AND c.saleClient.tmid = '{tmid}' AND c.prodType = 'service'";
            if (!string.IsNullOrWhiteSpace(prodCode))
            {
                strQueryV += $" AND c.prodCode = '{prodCode}'";
            }
            await foreach (OrderHisService orderService in cosmosClientCsv2.GetContainer("Habb", "Auth").GetItemQueryIterator<OrderHisService>(strQueryV, null, qryOption))
            {
                Ies5OrderHisService ies5OrderVRow = new Ies5OrderHisService();
                ies5OrderVRow.prodCode = orderService.prodCode;
                ies5OrderVRow.type = (orderService.contract.Equals(1)) ? "C" : (orderService.contract.Equals(2)) ? "G" : "N"; //授權方式 [FROM]contract契約型態 0:新約 1:續約 2:變更 [TO]type N:新約 C:續約 G:變更
                ies5OrderVRow.sdate = orderService.startDate;
                ies5OrderVRow.edate = orderService.endDate;
                ies5OrderVRow.number = orderService.number;
                ies5OrderVRow.unit = orderService.unit;
                //加入Order字典
                string OrderID = orderService.orderinfo.orderid.ToString();
                long OrderDate = orderService.orderinfo.createDate;
                if (OrderDic.ContainsKey(OrderID))
                {
                    OrderDic[OrderID].service.Add(ies5OrderVRow);
                }
                else
                {
                    Ies5OrderHis ies5OrderHisNew = new Ies5OrderHis();
                    ies5OrderHisNew.id = OrderID;
                    ies5OrderHisNew.date = OrderDate;
                    ies5OrderHisNew.service.Add(ies5OrderVRow);
                    OrderDic.Add(OrderID, ies5OrderHisNew);
                }
            }

            //輸出項
            List<Ies5OrderHis> Result = new List<Ies5OrderHis>();
            foreach (KeyValuePair<string, Ies5OrderHis> item in OrderDic)
            {
                Result.Add(item.Value);
            }
            return Result;
        }

        //Tool
        //資料遮罩
        ///規則：
        ///1.手機、電話：隱藏後4碼
        ///2.Mail：隱藏@前資料
        ///3.姓名：只顯示第一個字元
        ///4.身分證、護照：隱藏後4碼
        public string GenDataMask(string data, string type)
        {
            int length = 0;
            string subString = string.Empty;
            if (!string.IsNullOrWhiteSpace(data))
            {
                switch (type)
                {
                    case "mobile":
                    case "id":
                        length = 4;
                        subString = data.Substring(data.Length - length, length);
                        data = data.Replace(subString, "****");
                        break;
                    case "mail":
                        string[] dataList = data.Split("@");
                        subString = dataList.First();
                        data = data.Replace(subString, "****");
                        break;
                    case "name":
                        length = 2;
                        subString = data.Substring(data.Length - length, length);
                        data = data.Replace(subString, "〇〇");
                        break;
                }
            }
            return data;
        }

        //Model

        //TMID統計 基本資訊
        private class TmidStics
        {
            public string id { get; set; }
            public string name { get; set; }
            public string mobile { get; set; }
            public string mail { get; set; }
            public string country { get; set; }
            public string province { get; set; }
            public string city { get; set; }
            public string schoolCode { get; set; }
            public string schoolCodeW { get; set; }
            public TmidSticsIes5 ies5 { get; set; } = new(); //IES統計資料
            public TmidPoints points { get; set; } = new();
            public TmidSokrates sokrates { get; set; } = new();
            public List<TmidCoupon> coupons { get; set; } = new();
            public List<TmidLoginTime> login { get; set; } = new();
            public List<object> prod { get; set; } = new();
        }
        //TMID統計 IES5資訊
        private class TmidSticsIes5
        {
            public long usedSize { get; set; } //已使用的存儲空間(Byte)
            public long teachSize { get; set; } //分配給教師的空間(Byte)
            public long totalSize { get; set; } //總空間(Byte)
            public long surplusSize { get; set; } //剩余的空間(Byte)
        }
        private class TmidPoints
        {
            public int points { get; set; } = 0; //累積的點數(只加不減)
            public int balance { get; set; } = 0; //可用的點數
            public int level { get; set; } = 0; //等級
        }
        private class TmidCoupon
        {
            public string code { get; set; }
            public long exchange { get; set; }
        }
        private class TmidLoginTime
        {
            public string product { get; set; }
            public long time { get; set; }
        }
        private class TmidSokrates
        {
            public string t_data { get; set; }
            public string t_green { get; set; }
            public string duration { get; set; }
            public string attendance { get; set; }
            public string interaction { get; set; }
            public string learning_duration { get; set; }
        }

        //[API輸出] 訂單履歷 
        public class Ies5OrderHis
        {
            public Ies5OrderHis()
            {
                service = new List<Ies5OrderHisService>();
            }
            public string id { get; set; }
            public long date { get; set; }
            public List<Ies5OrderHisService> service { get; set; } = new ();
        }
        //[API輸出] 訂單履歷.服務型產品
        public class Ies5OrderHisService
        {
            public string prodCode { get; set; } //產品代碼
            public string type { get; set; } //授權方式 N:新約 C:續約
            public long sdate { get; set; } //授權起始時間
            public long edate { get; set; } //授權終止時間
            public int number { get; set; } //購買數量
            public string unit { get; set; } //購買單位 無單位者為null
        }

        //[承接DB] DB: Habb.Auth
        //[承接DB] 訂單履歷基本Class
        public class OrderHisBase
        {
            public string id { get; set; } //OPID
            public OrderHisOrderInfo orderinfo { get; set; } //訂單資訊
            public string prodCode { get; set; } //產品代碼
            public SerialSaleClient saleClient { get; set; } = null; //銷售終端
            public string prodType { get; set; } //產品類型 serial:序號 service:服務 hard:硬體
            public string dataType { get; set; } //資料類型
            public long operationTime { get; set; } //最新資料變更時間戳記
            public int ttl { get; set; } = -1; //過期刪除秒數

        }
        //[承接DB] 訂單履歷基本Class.銷售終端
        public class SerialSaleClient
        {
            public string name { get; set; } //[String]銷售終端姓名
            public string schoolCode { get; set; } = null; //[String]銷售終端學校代碼
            public string clientId { get; set; } = null; //[String]銷售終端客戶ID
            public string tmid { get; set; } = null; //[String]TMID
            public string type { get; set; } //[String]銷售終端資料類型  school:學校 client:經銷商客戶
            public string countryId { get; set; } //[String]國家代碼
            public string provinceId { get; set; } //[String]省代碼
            public string cityId { get; set; } //[String]市代碼
            public string schoolShortCode { get; set; } //[String]學校簡碼
            public string districtId { get; set; } //[String]區代碼
            public string dataCenter { get; set; } //[String]數據中心
        }

        //[承接DB] 訂單履歷基本Class.訂單資訊
        public class OrderHisOrderInfo
        {
            public string orderid { get; set; } //[String]訂單編號
            public int orderAudit { get; set; } //[Int]訂單審核狀態 0:待審, 1:通過, 2:否決, 3:問題
            public int orderProperty { get; set; } //[Int]訂單類型 0:銷售,1:展示申請 2:內部申請
            public long createDate { get; set; } //訂單創建時間
        }
        //[承接DB] 訂單履歷 服務類
        public class OrderHisService : OrderHisBase
        {
            public int type { get; set; } //[Int]授權類型 0:銷售 1:試用
            public int contract { get; set; } //[Int]契約型態 0:新約 1:續約
            public long startDate { get; set; } //[long]授權起始日期 Timestamp(UTC)
            public long endDate { get; set; } //[long]授權終止日期 Timestamp(UTC)
            public int number { get; set; } //[Int]數量
            public string unit { get; set; } //[String]單位 (無者為空)
        }

        //雲端服務ClientId列表
        private readonly Dictionary<string, string> _dicClientIds = new()
        {
            { "917d02f2-5b91-404e-a71d-7bdd926ddd81", "HiTeach" },
            { "c5328340-b8eb-4489-8650-8c8862d7acfe", "HiTeach" },
            { "118d2d4d-32a3-44c0-808a-792ca73d3706", "HiTeachCC" },
            { "227082f4-8db0-4517-b281-93dba9428bc7", "HiTeachCC" },
            { "371a99aa-7efd-4c3a-90a8-95b7db4f42c0", "HiTA" },
            { "0ed38cde-e7fe-4fa6-9eaa-33ad404eb532", "HiTA" },
            { "531fecd1-b1a5-469a-93ca-7984e1d392f2", "IES5" },
            { "c7317f88-7cea-4e48-ac57-a16071f7b884", "IES5" },
            { "c8de822b-3fcf-4831-adba-cdd14653bf7f", "Account" },
            { "516148eb-6a38-4657-ba98-a3699061937f", "Account" },
            { "d7193896-9b12-4046-ad44-c991dd48cc39", "Sokrates" },
            { "59009e38-6e30-4116-814b-7605939edc47", "Sokrates" },
            { "626c7285-15aa-4abe-8c0d-2c5ff1381538", "SokAPP" },
            { "5bcc16a5-7d20-4cc4-b5c2-8ed0416f32b6", "SokAPP" },
            { "044482b5-d024-4f23-9b55-906884243405", "IRS" },
            { "5e27b7e3-b36c-4ce9-b838-e94fd0cea080", "IRS" }
        };
    }
    
}