dotnet/molilian.core/Plus/Alimama/crawler.cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc.Controllers;
using Microsoft.AspNetCore.Mvc.Filters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using dodohold.core;
using Dataoke;
using System.Text.RegularExpressions;
using Org.BouncyCastle.Ocsp;
using System.Diagnostics;
using TencentCloud.Fmu.V20191213.Models;
using Microsoft.VisualBasic;
using System.Text.Json;
using static System.Runtime.InteropServices.JavaScript.JSType;
using TencentCloud.Cdn.V20180606.Models;
using System.Net;
using TencentCloud.Teo.V20220901.Models;
using MySqlX.XDevAPI;


namespace molilian.core
{
    public partial class AlimamaPlus
    {

        /// <summary>
        /// 更新API调用日志（1天一次）
        /// </summary>
        /// <param name="intervalDay"></param>
        public static void DailyLogs(int intervalDay, string prefix = "")
        {

            DateTime log_date = DateTime.Now.AddDays(-intervalDay).Date;

            var accounts = TkPoolCore.List();

            foreach (var account in accounts)
            {
                int accountId = account.id;
                string accountName = $"{TkChannelEnum.tb}_{account.id}";

                int total_count = TkLogCore.GetTotal($":{prefix}total:{accountName}:{log_date:yyyyMMdd}");
                if (total_count == 0) accountName = account.name;

                total_count = TkLogCore.GetTotal($":{prefix}total:{accountName}:{log_date:yyyyMMdd}");
                if (total_count == 0) continue;

                var success_count = TkLogCore.GetTotal($":{prefix}total:{accountName}:success:{log_date:yyyyMMdd}");
                var abandon_count = TkLogCore.GetTotal($":{prefix}total:{accountName}:放弃转链:{log_date:yyyyMMdd}");
                string success_percentage = string.Empty;
                string abandon_percentage = string.Empty;

                if (total_count > 0)
                {
                    success_percentage = $"{success_count / (double)total_count * 100:f2}%";
                    abandon_percentage = $"{abandon_count / (double)total_count * 100:f2}%";
                }

                var exist = new DBContext.Table("daily_logs")
                     .Fields("id,log_date")
                     .Get<dynamic>("log_date=@log_date AND accountId=@accountId", new { log_date, accountId });

                var createArgs = new DBContext.Table("daily_logs")

                    .Add($"{prefix}total_count", total_count)
                    .Add($"{prefix}abandon_count", abandon_count)
                    .Add($"{prefix}abandon_percentage", abandon_percentage)
                    .Add($"{prefix}success_count", success_count)
                    .Add($"{prefix}success_percentage", success_percentage)
                    .Add("last_time", DateTime.Now);

                if (exist == null)
                {
                    createArgs.Add("channel", 0)
                        .Add("accountId", accountId)
                        .Add("accountName", account.company)
                        .Add("log_date", log_date)
                        .Add("create_time", DateTime.Now)
                        .Create();
                }
                else
                {
                    createArgs.Where("id=@id", new { exist.id }).Update();
                }
            }


            int all_total_count = TkLogCore.GetTotal($":{prefix}total:all:{log_date:yyyyMMdd}");
            var all_success_count = TkLogCore.GetTotal($":{prefix}total:all:success:{log_date:yyyyMMdd}");
            var all_abandon_count = TkLogCore.GetTotal($":{prefix}total:all:放弃转链:{log_date:yyyyMMdd}");

            string all_success_percentage = string.Empty;
            string all_abandon_percentage = string.Empty;
            if (all_total_count > 0)
            {
                all_success_percentage = $"{all_success_count / (double)all_total_count * 100:f2}%";
                all_abandon_percentage = $"{all_abandon_count / (double)all_total_count * 100:f2}%";
            }

            var exist2 = new DBContext.Table("daily_logs")
                 .Fields("id,log_date")
                 .Get<dynamic>("log_date=@log_date AND accountId=@accountId", new { log_date, accountId = 0 });

            var createArgs2 = new DBContext.Table("daily_logs")
                .Add($"{prefix}total_count", all_total_count)
                .Add($"{prefix}abandon_count", all_abandon_count)
                .Add($"{prefix}abandon_percentage", all_abandon_percentage)
                .Add($"{prefix}success_count", all_success_count)
                .Add($"{prefix}success_percentage", all_success_percentage)
                .Add("last_time", DateTime.Now);

            if (exist2 == null)
            {
                createArgs2.Add("channel", 0)
                    .Add("accountId", 0)
                    .Add("accountName", "all")
                    .Add("log_date", log_date)
                    .Add("create_time", DateTime.Now)
                    .Create();
            }
            else
            {
                createArgs2.Where("id=@id", new { exist2.id }).Update();
            }


            string sql = @$"
INSERT INTO daily_logs (channel, accountId, accountName, log_date, total_count, abandon_count, abandon_percentage, success_count, success_percentage, create_time, last_time)
SELECT
    0 AS channel, 0 AS accountId, 'all' AS accountName,
    DATE_SUB(CURDATE(), INTERVAL {intervalDay} DAY) AS log_date,
    COUNT(*) AS total_count,
    COALESCE(SUM(CASE WHEN message = '放弃转链' THEN 1 ELSE 0 END), 0) AS abandon_count,
    COALESCE(CONCAT(ROUND((SUM(CASE WHEN message = '放弃转链' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%'), 0) AS abandon_percentage,
    COALESCE(SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END), 0) AS success_count,
    COALESCE(CONCAT(ROUND((SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%'), 0) AS success_percentage,
    NOW() AS create_time,
    NOW() AS last_time
FROM tk_logs
WHERE DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL {intervalDay} DAY)
ON DUPLICATE KEY UPDATE
    log_date = VALUES(log_date),
    total_count = VALUES(total_count),
    abandon_count = VALUES(abandon_count),
    abandon_percentage = VALUES(abandon_percentage),
    success_count = VALUES(success_count),
    success_percentage = VALUES(success_percentage),
    last_time = NOW();
";

            string sql2 = @$"
INSERT INTO daily_logs (channel, accountId, accountName, log_date, total_count, abandon_count, abandon_percentage, success_count, success_percentage, create_time, last_time)
SELECT
    channel, accountId, accountName, DATE_SUB(CURDATE(), INTERVAL {intervalDay} DAY) AS log_date,
    COUNT(*) AS total_count,
    COALESCE(SUM(CASE WHEN message = '放弃转链' THEN 1 ELSE 0 END), 0) AS abandon_count,
    COALESCE(CONCAT(ROUND((SUM(CASE WHEN message = '放弃转链' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%'), 0) AS abandon_percentage,
    COALESCE(SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END), 0) AS success_count,
    COALESCE(CONCAT(ROUND((SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%'), 0) AS success_percentage,
    NOW() AS create_time,
    NOW() AS last_time
FROM tk_logs
WHERE DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL {intervalDay} DAY)
GROUP BY channel, accountId, accountName
ON DUPLICATE KEY UPDATE
    log_date = VALUES(log_date),
    total_count = VALUES(total_count),
    abandon_count = VALUES(abandon_count),
    abandon_percentage = VALUES(abandon_percentage),
    success_count = VALUES(success_count),
    success_percentage = VALUES(success_percentage),
    last_time = NOW();
";

            //DBContext.Execute(sql, new { });
            //DBContext.Execute(sql2, new { });
        }


        /// <summary>
        /// 事实动态接口（5分钟一次）
        /// </summary>
        /// <param name="date"></param>
        public void SaveReportOverview()
        {
            DateTime date = DateTime.Now;
            TkReportDTO result = new TkReportDTO();
            try
            {
                string t = $"{date.Convert2UnixTimestamp(true)}";
                string dt = date.ToString("yyyy-MM-dd");

                string url = "https://pub.alimama.com/openapi/param2/1/gateway.unionpub/data.lens.report.overview.json?" +
                    $"t={t}&_tb_token_={_tb_token}&ruleEnum=pub_overview&startDate={dt}&endDate={dt}&fixPeriod=1d";

                var client = new WebClientUtility().SetContentType("application/json;charset=utf-8")
                            .AddHeaders("X-Requested-With", "XMLHttpRequest")
                            .AddHeaders("Cookie", _cookies);
#if DEBUG
#else
            client.Proxy = _proxy;
#endif
                if (!string.IsNullOrEmpty(_user_agent)) client.UserAgent = _user_agent;
                var response = client.Request(url);

                var body = response.Body();
                var root = body.Convert2JsonElement();

                int resultCode = root.Read<int>("resultCode", 0);
                bool success = root.Read("success", false);
                if (!success)
                {
                    _ = new LoggerLibrary("report", "error")
                        .Info(url, body)
                        .SaveAsync();
                    throw new Exception(body);
                }

                var data = root.ElementRead("data").ElementRead("data");

                result.eff_ord_num = data.Read<int>("eff_ord_num", 0);
                result.eff_ord_amt = data.Read<decimal>("eff_ord_amt", 0);
                result.eff_tk_disp_tfee = data.Read<decimal>("eff_tk_disp_tfee", 0);

                result.uclk_uv_56 = data.Read<int>("uclk_uv_56", 0);
                result.uclk_pv_56 = data.Read<int>("uclk_pv_56", 0);
                result.uclt_uv_56 = data.Read<int>("uclt_uv_56", 0);
                result.ucart_uv_56 = data.Read<int>("ucart_uv_56", 0);
                result.dep_ord_num_56 = data.Read<int>("dep_ord_num_56", 0);
                result.dep_ord_dep_amt_56 = data.Read<decimal>("dep_ord_dep_amt_56", 0);
                result.pay_ord_num_56 = data.Read<int>("pay_ord_num_56", 0);
                result.pay_ord_amt_56 = data.Read<decimal>("pay_ord_amt_56", 0);
                result.pay_tk_disp_tfee_56 = data.Read<decimal>("pay_tk_disp_tfee_56", 0);
                result.pay_ord_uv_56 = data.Read<int>("pay_ord_uv_56", 0);
                result.sett_ord_num_56 = data.Read<int>("sett_ord_num_56", 0);
                result.sett_ord_amt_56 = data.Read<decimal>("sett_ord_amt_56", 0);
                result.sett_tk_disp_tfee_56 = data.Read<decimal>("sett_tk_disp_tfee_56", 0);

                DateTime report_date = date.Date;
                if (report_date == DateTime.Now.Date)
                {
                    TkPoolCore.SaveIncomeAmt(_accountId.ToString(), result.eff_tk_disp_tfee);
                    TkPoolCore.SaveIncomeAmt(_accountName, result.eff_tk_disp_tfee);
                    EachSaveReportOverview(report_date.AddHours(DateTime.Now.Hour), "tk_report_hourtrend", result);
                    EachSaveReportOverview(report_date, "tk_report", result);
                }
            }
            catch (Exception ex) { }
        }
        private void EachSaveReportOverview(DateTime report_date, string tableName, TkReportDTO data)
        {
            var exist = new DBContext.Table(tableName)
                 .Fields("id,report_date")
                 .Get<dynamic>("report_date=@report_date AND accountId=@accountId",
                                 new { report_date, accountId = _accountId });

            var createArgs = new DBContext.Table(tableName)
                .Add("uclk_uv_56", data.uclk_uv_56)
                .Add("uclk_pv_56", data.uclk_pv_56)
                .Add("uclt_uv_56", data.uclt_uv_56)
                .Add("ucart_uv_56", data.ucart_uv_56)
                .Add("dep_ord_num_56", data.dep_ord_num_56)
                .Add("dep_ord_dep_amt_56", data.dep_ord_dep_amt_56)
                .Add("pay_ord_num_56", data.pay_ord_num_56)
                .Add("pay_ord_amt_56", data.pay_ord_amt_56)
                .Add("pay_tk_disp_tfee_56", data.pay_tk_disp_tfee_56)
                .Add("pay_ord_uv_56", data.pay_ord_uv_56)
                .Add("sett_ord_num_56", data.sett_ord_num_56)
                .Add("sett_ord_amt_56", data.sett_ord_amt_56)
                .Add("sett_tk_disp_tfee_56", data.sett_tk_disp_tfee_56)
                .Add("eff_ord_num", data.eff_ord_num)
                .Add("eff_ord_amt", data.eff_ord_amt)
                .Add("eff_tk_disp_tfee", data.eff_tk_disp_tfee);

            if (exist == null)
            {
                createArgs.Add("accountId", _accountId)
                    .Add("accountName", _company)
                    .Add("report_date", report_date)
                    .Add("create_time", DateTime.Now)
                    .Add("last_time", DateTime.Now)
                    .Create();
            }
            else
            {
                createArgs.Add("last_time", DateTime.Now)
                    .Where("id=@id", new { exist.id })
                    .Update();
            }

        }


        /// <summary>
        /// 修正点击数
        /// </summary>
        public void FixReport(DateTime date)
        {
            TkReportDTO result = new TkReportDTO();
            try
            {
                string t = $"{date.Convert2UnixTimestamp(true)}";
                string dt = date.ToString("yyyy-MM-dd");

                string url = "https://pub.alimama.com/openapi/param2/1/gateway.unionpub/data.lens.report.overview.json?" +
                    $"t={t}&_tb_token_={_tb_token}&ruleEnum=pub_overview&startDate={dt}&endDate={dt}&fixPeriod=1d";

                var client = new WebClientUtility().SetContentType("application/json;charset=utf-8")
                            .AddHeaders("X-Requested-With", "XMLHttpRequest")
                            .AddHeaders("Cookie", _cookies);
#if DEBUG
#else
            client.Proxy = _proxy;
#endif
                if (!string.IsNullOrEmpty(_user_agent)) client.UserAgent = _user_agent;
                var response = client.Request(url);

                var body = response.Body();
                var root = body.Convert2JsonElement();

                int resultCode = root.Read<int>("resultCode", 0);
                bool success = root.Read("success", false);
                if (!success)
                {
                    _ = new LoggerLibrary("report", "error")
                        .Info(url, body)
                        .SaveAsync();
                    throw new Exception(body);
                }

                var data = root.ElementRead("data").ElementRead("data");

                result.eff_ord_num = data.Read<int>("eff_ord_num", 0);
                result.eff_ord_amt = data.Read<decimal>("eff_ord_amt", 0);
                result.eff_tk_disp_tfee = data.Read<decimal>("eff_tk_disp_tfee", 0);

                result.uclk_uv_56 = data.Read<int>("uclk_uv_56", 0);
                result.uclk_pv_56 = data.Read<int>("uclk_pv_56", 0);
                result.uclt_uv_56 = data.Read<int>("uclt_uv_56", 0);
                result.ucart_uv_56 = data.Read<int>("ucart_uv_56", 0);
                result.dep_ord_num_56 = data.Read<int>("dep_ord_num_56", 0);
                result.dep_ord_dep_amt_56 = data.Read<decimal>("dep_ord_dep_amt_56", 0);
                result.pay_ord_num_56 = data.Read<int>("pay_ord_num_56", 0);
                result.pay_ord_amt_56 = data.Read<decimal>("pay_ord_amt_56", 0);
                result.pay_tk_disp_tfee_56 = data.Read<decimal>("pay_tk_disp_tfee_56", 0);
                result.pay_ord_uv_56 = data.Read<int>("pay_ord_uv_56", 0);
                result.sett_ord_num_56 = data.Read<int>("sett_ord_num_56", 0);
                result.sett_ord_amt_56 = data.Read<decimal>("sett_ord_amt_56", 0);
                result.sett_tk_disp_tfee_56 = data.Read<decimal>("sett_tk_disp_tfee_56", 0);

                DateTime report_date = date.Date;
                EachSaveReportOverview(report_date, "tk_report", result);


                //var exist = new DBContext.Table("tk_report")
                //     .Fields("id,report_date")
                //     .Get<dynamic>("report_date=@report_date AND accountId=@accountId",
                //                     new { report_date, accountId = _accountId });
                //var createArgs = new DBContext.Table("tk_report")
                //    .Add("fix_uclk_pv_56", result.fix_uclk_pv_56);

                //if (exist == null)
                //{
                //    createArgs.Add("accountId", _accountId)
                //        .Add("accountName", _company)
                //        .Add("report_date", report_date)
                //        .Add("create_time", DateTime.Now)
                //        .Add("last_time", DateTime.Now)
                //        .Create();
                //}
                //else
                //{
                //    createArgs.Add("last_time", DateTime.Now)
                //        .Where("id=@id", new { exist.id })
                //        .Update();
                //}
            }
            catch (Exception ex) { }
        }


        /// <summary>
        /// 1小时更新一次
        /// </summary>
        public void SaveReportHourtrend(DateTime date)
        {
            string t = $"{DateTime.Now.Convert2UnixTimestamp(true)}";
            string dt = date.ToString("yyyy-MM-dd");

            string url = "https://pub.alimama.com/openapi/param2/1/gateway.unionpub/data.lens.report.hourtrend.dod.json?" +
                $"t={t}&_tb_token_={_tb_token}&ruleEnum=pub_homepage&startDate={dt}&endDate={dt}&fixPeriod=realTime";


            var client = new WebClientUtility().SetContentType("application/json;charset=utf-8")
                        .AddHeaders("X-Requested-With", "XMLHttpRequest")
                        .AddHeaders("Cookie", _cookies);
#if DEBUG
#else
            client.Proxy = _proxy;
#endif
            if (!string.IsNullOrEmpty(_user_agent)) client.UserAgent = _user_agent;
            var response = client.Request(url);

            var body = response.Body();
            var root = body.Convert2JsonElement();

            int resultCode = root.Read<int>("resultCode", 0);
            bool success = root.Read("success", false);
            if (!success)
            {
                _ = new LoggerLibrary("report", "error")
                    .Info(url, body)
                    .SaveAsync();
                throw new Exception(body);
            }

            var data_list = root.ElementRead("data").ElementRead("data");
            foreach (var data in data_list.EnumerateArray())
            {
                string xAxis = data.Read<string>("xAxis");

                string _report_date = $"{dt} {xAxis}";
                EachSaveReport(_report_date, "tk_report_hourtrend", data);
                if ("23:00".Equals(xAxis))
                {
                    EachSaveReport(dt, "tk_report", data);
                }
            }
        }

        private void EachSaveReport(string _report_date, string tableName, JsonElement data)
        {
            _ = DateTime.TryParse(_report_date, out DateTime report_date);

            var exist = new DBContext.Table(tableName)
                 .Fields("id,report_date")
                 .Get<dynamic>("report_date=@report_date AND accountId=@accountId",
                                 new { report_date, accountId = _accountId });
            if (exist == null)
            {
                new DBContext.Table(tableName)
                    .Fill(data)
                    .Loader<int>("uclk_uv_56", 0)
                    .Loader<int>("uclk_pv_56", 0)
                    .Loader<int>("uclt_uv_56", 0)
                    .Loader<int>("ucart_uv_56", 0)
                    .Loader<int>("dep_ord_num_56", 0)
                    .Loader<decimal>("dep_ord_dep_amt_56", 0)
                    .Loader<int>("pay_ord_num_56", 0)
                    .Loader<decimal>("pay_ord_amt_56", 0)
                    .Loader<decimal>("pay_tk_disp_tfee_56", 0)
                    .Loader<int>("pay_ord_uv_56", 0)
                    .Loader<int>("sett_ord_num_56", 0)
                    .Loader<decimal>("sett_ord_amt_56", 0)
                    .Loader<decimal>("sett_tk_disp_tfee_56", 0)
                    .Add("accountId", _accountId)
                    .Add("accountName", _company)
                    .Add("report_date", report_date)
                    .Add("create_time", DateTime.Now)
                    .Add("last_time", DateTime.Now)
                    .Create();
            }
            else
            {
                new DBContext.Table(tableName)
                    .Fill(data)
                    .Loader<int>("uclk_uv_56", 0)
                    .Loader<int>("uclk_pv_56", 0)
                    .Loader<int>("uclt_uv_56", 0)
                    .Loader<int>("ucart_uv_56", 0)
                    .Loader<int>("dep_ord_num_56", 0)
                    .Loader<decimal>("dep_ord_dep_amt_56", 0)
                    .Loader<int>("pay_ord_num_56", 0)
                    .Loader<decimal>("pay_ord_amt_56", 0)
                    .Loader<decimal>("pay_tk_disp_tfee_56", 0)
                    .Loader<int>("pay_ord_uv_56", 0)
                    .Loader<int>("sett_ord_num_56", 0)
                    .Loader<decimal>("sett_ord_amt_56", 0)
                    .Loader<decimal>("sett_tk_disp_tfee_56", 0)
                    .Add("last_time", DateTime.Now)
                    .Where("id=@id", new { exist.id })
                    .Update();
            }
        }


        /// <summary>
        /// 警告消息
        /// </summary>
        public void GetViolationuWarning()
        {
            try
            {
                string url = "https://media.alimama.com/violationu2/warning_instance_page_v2.json?r=mx_208&toPage=1&pageSize=40&params=";
                var client = new WebClientUtility().SetContentType("application/json;charset=utf-8")
                            .AddHeaders("X-Requested-With", "XMLHttpRequest")
                            .AddHeaders("Cookie", _cookies);
#if DEBUG
#else
            client.Proxy = _proxy;
#endif
                if (!string.IsNullOrEmpty(_user_agent)) client.UserAgent = _user_agent;
                var response = client.Request(url);

                var body = response.Body();
                var root = body.Convert2JsonElement();

                int resultCode = root.Read<int>("resultCode", 0);
                int totalCount = root.PathRead<int>("data.totalCount", 0);
                bool success = root.Read("success", false);

                if (!success)
                {
                    _ = new LoggerLibrary("violationu", "warning_error")
                        .Info(url, body)
                        .SaveAsync();
                    throw new Exception(body);
                }
                if (totalCount == 0) return;
                _ = new LoggerLibrary("violationu", "warning").Info(url, body).SaveAsync();

                var list = root.ElementRead("data").ElementRead("result").EnumerateArray();
                foreach (var data in list)
                {
                    var violation_commission = data.PathRead<decimal>("mediaInfo.订单虚假交易违规佣金", 0);
                    new DBContext.Table("alimama_warning")
                        .Fill(data)
                        .Loader<int>("filterRuleId", 0)
                        .Loader<long>("gmtCreate", 0)
                        .Loader<long>("id", 0, "ali_id")
                        .Loader<string>("idStr", string.Empty)
                        .Loader<int>("mediaDimension", 0)
                        .Loader<long>("mediaId", 0)
                        .Loader<int>("mediaInfo.abn_cnt", 0, "abn_cnt")
                        .Add("violation_commission", violation_commission)
                        .Loader<int>("mediaInfo.warning_cnt", 0, "warning_cnt")
                        .Loader<string>("mediaName", string.Empty)
                        .Loader<long>("memberId", 0)

                        .Loader<string>("noticeCategory", string.Empty)
                        .Loader<int>("noticeTemplateId", 0)
                        .Loader<string>("noticeTemplateName", string.Empty)

                        .Loader<string>("orderFile", string.Empty)
                        .Loader<string>("pid", string.Empty)
                        .Loader<int>("punishRuleId", 0)
                        .Loader<string>("riskType", string.Empty)
                        .Loader<string>("roleName", string.Empty)
                        .Loader<string>("ruleLink", string.Empty)
                        .Loader<bool>("showOrderDetail", false)
                        .Loader<int>("status", 0, "ali_status")
                        .Loader<string>("warningText", string.Empty)

                        .Add("accountName", _company)
                        .Add("status", true)
                        .Add("create_time", DateTime.Now)
                        .Add("last_time", DateTime.Now)
                        .Create(DBContext.InsertType.REPLACE);
                }
            }
            catch (Exception ex) { }
        }


    }
}