data-transfer/SnapshotWinFormsApp/Application/OrderSnapshotApplication.cs

using Abp;
using DataTransmission.Entity;
using DataTransmission.Joint;
using Mapster.Utils;
using SnapshotWinFormsApp.Application.Dtos;
using SnapshotWinFormsApp.Application.Interfaces;
using SnapshotWinFormsApp.Entities.NewHotline;
using SnapshotWinFormsApp.Entities.OldHotline;
using SnapshotWinFormsApp.Repository;
using SnapshotWinFormsApp.Repository.Enum;
using SnapshotWinFormsApp.Repository.Interfaces;
using SnapshotWinFormsApp.Tools;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SnapshotWinFormsApp.Application;

[Description("随手拍工单")]
public class OrderSnapshotApplication : IImportApplication
{
    private readonly ITargetRepository<OrderSnapshot> _orderSnapshotRepo;
    private readonly ISourceRepository<Flow03_SearchEntity, int> _oldOrderRepo;
    private readonly DbSqlServer _dbSqlServer;
    private Config config = new Config();
    private readonly ITargetRepository<Citizen> _snapshotUserInfoRepo;
    private readonly ITargetRepository<Industry> _industryRepo;
    private readonly List<Industry> industries;
    private readonly ITargetRepository<CommunityInfo> _communityInfoRepo;
    private readonly List<CommunityInfo> communityInfos;
    private readonly ITargetRepository<Entities.NewHotline.User> _userRepo;
    private readonly List<Entities.NewHotline.User> _users;
    private readonly ITargetRepository<SystemDicData> _systemDicDataRepo;
    private readonly List<SystemDicData> snapshotOrderLabels;
    public OrderSnapshotApplication(CreateInstanceInDto inDto)
    {
        _dbSqlServer = inDto.DbSqlServer;
        _orderSnapshotRepo = new TargetRepository<OrderSnapshot>(inDto);
        _oldOrderRepo = new SourceRepository<Flow03_SearchEntity, int>(inDto);
        _snapshotUserInfoRepo = new TargetRepository<Citizen>(inDto);
        _industryRepo = new TargetRepository<Industry>(inDto);
        industries = _industryRepo.Queryable().ToList();
        _communityInfoRepo = new TargetRepository<CommunityInfo>(inDto);
        communityInfos = _communityInfoRepo.Queryable().ToList();
        _userRepo = new TargetRepository<Entities.NewHotline.User>(inDto);
        _users = _userRepo.Queryable().ToList();
        _systemDicDataRepo = new TargetRepository<SystemDicData>(inDto);
        snapshotOrderLabels = _systemDicDataRepo.Queryable().Where(m => m.DicTypeCode == "SnapshotOrderLabel").ToList();

        config.Name = "自贡市";
        config.CenterId = "2";
        config.Paseword = "AQAAAAIAAYagAAAAEJZNauXWsvzHDvSCLEwNjA3qVJcQqEHAexlWDg6ONJJtK5hDNnL8gwwXxwZW5YszfA==";
        config.ModuleId = "2430d69a-59df-46a1-b155-85e69cfba010";
    }
    public async Task ImportAsync(Action<string> log, CancellationToken token)
    {
        var order = new DataTransmission.Joint.Order();
        DataTransmission.Joint.Workflow workflow = new DataTransmission.Joint.Workflow();
        Public _public = new Public();
        Visit visit = new Visit();
        Delay delay = new Delay();

        var sql = GetOrderSql();
        var items =  _oldOrderRepo.GetDataTable(sql.sql, sql.parameters);
        var data = items.AsEnumerable().CopyToDataTable();

        DataTable org = GetPgOrganize();
        DataTable user = GetPgUser();

        foreach (DataRow row in data.Rows)
        {
            log(row["FSH_Title_ys"].ToString() + " " + row["受理内容"] + " " + row["FSH_LinkTel"]);
            var pgOrder = _orderSnapshotRepo.GetDataTable($@" SELECT * from ""order"" where ""No"" ='{row["FSH_Code"]}'; ");
            if (pgOrder != null && pgOrder.Rows.Count > 0)
            {
                continue;
            }
            var orderId = SequentialGuidGenerator.Instance.Create().ToString("D");
            var VisitId = SequentialGuidGenerator.Instance.Create().ToString("D");

            var module = _orderSnapshotRepo.GetDataTable($@" SELECT * from ""workflow_module"" ; ");
            var definition = _orderSnapshotRepo.GetDataTable($@"SELECT * FROM workflow_definition WHERE ""Status"" =1 ; ");


            //工单
            var pgOrderSql = order.GetOrderISql(row, orderId, org, user, config);
            await InsertOrderSnapshotAsync(row, orderId, token);
            //工单流程
            var orderModule = module.AsEnumerable().FirstOrDefault(x => x.Field<string>("Code").ToString() == "OrderHandle");
            var pgOrderWorkflow = GetWorkflow(row["FSH_FlowID"].ToString(), "CityHotline");
            var pgOrderWorkflowSql = workflow.GetWorkflowSql(pgOrderWorkflow, row, orderId, org, user, orderModule, null, config);
            //发布
            var publicTable = GetOldPublic(row["FSH_FlowID"].ToString());
            var publicSql = _public.GetOldPublicSql(publicTable, row, orderId, org, user, config);
            //回访
            var appraiseTable = GetVisit(row["FSH_FlowID"].ToString());
            var visitSql = visit.GetVisitSql(appraiseTable, row, VisitId, orderId, org, user, config);
            //延期
            var delayTable = GetDelay(row["FSH_FlowID"].ToString());
            var delayModule = module.AsEnumerable().FirstOrDefault(x => x.Field<string>("Code").ToString() == "OrderDelay");
            var delaySql = delay.GetDelaySql(delayTable, row, orderId, org, user, delayModule, config);
            var pgSql = pgOrderSql;
            pgSql = pgOrderWorkflowSql.Length > 0 ? pgSql + pgOrderWorkflowSql : pgSql;
            pgSql = publicSql.Length > 0 ? pgSql + publicSql : pgSql;
            pgSql = visitSql.Length > 0 ? pgSql + visitSql : pgSql;
            pgSql = delaySql.Length > 0 ? pgSql + delaySql : pgSql;
            await _orderSnapshotRepo.ExecuteSqlAsync(pgSql);
        }
    }

    public async Task InsertOrderSnapshotAsync(DataRow row,string orderId, CancellationToken token)
    {
        var userId = _snapshotUserInfoRepo.Queryable().Where(m => m.PhoneNumber == row["FSH_LinkTel"].ToString()).Select(m => m.Id).First();
        var industryId = industries.FirstOrDefault(m => m.Name == row["FSH_IndustryName"].ToString())?.Id;
        var community = communityInfos.FirstOrDefault(m => m.Id == row["FSH_SSPAreaID"].ToString());
        var snapshot = new OrderSnapshot();
        snapshot.Id = orderId;
        snapshot.IndustryId = industryId;
        snapshot.IndustryName = row["FSH_IndustryName"].ToString();
        snapshot.CommunityId = row["FSH_SSPAreaID"].ToString();
        snapshot.CommunityName = community?.Name;
        snapshot.CommunityFullName = community?.FullName;
        snapshot.JobType = row["WEA_OperationType"].ToString() == "电焊" ? 0 : 1;
        snapshot.JobTypeName = row["WEA_OperationType"].ToString();
        snapshot.BusinessUnitType = row["WEA_CompanyType"].ToString(); // 经营单位类别
        snapshot.Workplace = row["WEA_OperationPlace"].ToString(); // 作业场所
        snapshot.WorkplaceName = row["WEA_PlaceTypeName"].ToString(); // 场所名称(多个场所使用 - 连接)
        snapshot.WorkArea = row["WEA_AreaFullName"].ToString(); // 作业区域
        snapshot.WorkAreaId = row["WEA_AreaID"].ToString(); // 作业区域Id
        snapshot.StartWorkTime = row["WEA_OperationStartTime"].ObjToDate(); // 作业时间
        snapshot.EndWorkTime = row["WEA_OperationEndTime"].ObjToDate(); // 作业时间
        snapshot.IsEmphasis = false; // 是否重点
        snapshot.IsSafetyDepartment = row["STL_TagType"] == null ? null : row["STL_TagType"].ToString() == "1" ? true : false; // 部门标记是否安全生产
        snapshot.SignUserId = GetUserId(row["STL_AddUserID"]); // 标记人
        snapshot.SignUserName = row["STL_UserName"].ToString(); // 标记人
        snapshot.SignTime = row["STL_AddDate"].ObjToDate(); // 标记时间
        snapshot.SignRemark = row["STL_TagRemark"].ToString(); // 标记备注
        snapshot.IsDangerDepartment = row["FSH_ReplyIsBMTrue"].ToString() == "1" ? true : false; // 部门是否存在安全隐患
        snapshot.IsTruthDepartment = null; // 部门是否属实
        snapshot.IsRectifyDepartment = row["FSH_IsRectification"].ToString() == "1" ? true : false; // 部门是否整改
        snapshot.IndustryCase = row["FSH_IndustryIDType"].ToString(); // 线索分类
        snapshot.IsAppOpened = false; // 小程序公开
        snapshot.LabelName = row["SWLS_TypeName"].ToString(); // 工单标签(多个使用 | 分隔)
        snapshot.LabelUserName = GetUserName(row["SWLS_AddUserID"]); // 标签用户
        snapshot.LabelUserId = GetUserId(row["SWLS_AddUserID"]); // 标签用户Id
        snapshot.LabelTime = row["SWLS_AddDate"].ObjToDate(); // 标签时间
        snapshot.IsAward = null; // 是否申请追加奖励
        snapshot.AwardOpenBank = null; // 追加奖励人开户银行
        snapshot.AwardName = null; // 追加奖励人姓名
        snapshot.AwardBankCardNo = null; // 追加奖励人银行卡号
        snapshot.AwardAmount = null; // 追加奖励金额(元)
        snapshot.ReplenishTypeId = null; // 补充奖励类型
        snapshot.ReplenishTypeName = null; // 补充奖励类型
        snapshot.VerifyType = null; // 核实方式
        snapshot.IsCheckList = row["FSH_Checklist"].ObjToBool(); // 是否按清单检查
        snapshot.GuiderAccLog = null; // 网格员系统交互日志
        snapshot.DeadLine = null; // 网格员回复截止日期
        snapshot.NetworkENumber = row["FSH_SSPCode"].ToString(); // 网格E通编号
        snapshot.IsTruth = row["FPC_ISTrue"].ToString() == "1"; // 网格员是否属实
        snapshot.IsRepetition = row["isRepeat"].ToString() == "1"; // 是否重复
        snapshot.IsDanger = row["isHiddenDanger"].ToString() == "1"; // 是否隐患
        snapshot.NetworkRemark = row["FPC_Content"].ToString(); // 网格员回复内容
        snapshot.ReplyDate = row["FPC_AcceptDate"].ObjToDate(); // 网格员办理时间
        snapshot.MemberName = row["memberName"].ToString(); // 网格员姓名
        snapshot.MemberMobile = row["memberMobile"].ToString(); // 网格员电话
        snapshot.ReplyUserName = null; // 办理人账号
        snapshot.ReplyBMName = null; // 办理部门
        snapshot.ReplyResultType = row["FPC_ResultType"].ObjToInt(); // 网格员办理状态
        snapshot.CompanyName = null; // 购气单位/个人名称
        snapshot.SendGuidSystemTime = row["FPC_AcceptDate"].ObjToDate(); // 发送网格员系统时间
        snapshot.GuidSystemCallBackTime = row["FPC_InsertDate"].ObjToDate(); // 网格员系统回调时间
        snapshot.IsGuidSystemCallBack = row["FPC_ID"] != null; // 网格员系统是否回调
        snapshot.SpecialReasonId = null; // 重办原因Id
        snapshot.SpecialReasonName = null; // 重办原因
        var typeName = row["SWLS_TypeName"].ToString();
        if (typeName.NotNullOrEmpty())
        {
            var labelId = snapshotOrderLabels.Where(m => m.DicDataName == typeName).FirstOrDefault();
            var labels = new Kv { Key = labelId.DicDataValue, Value = labelId.DicDataName};
            snapshot.Labels = new List<Kv> { labels };
        }
        if (snapshot.NetworkENumber.NotNullOrEmpty())
            snapshot.IsDeal = true; // 网格员是否办理
        switch (row["FSH_SSPAreaID"].ToString())
        {
            case "1":
                snapshot.CompliantType = ECompliantType.First;
                break;
            case "2":
                snapshot.CompliantType = ECompliantType.Second;
                break;
            case "3":
                snapshot.CompliantType = ECompliantType.Third;
                break;
            case "0":
                snapshot.CompliantType = ECompliantType.Not;
                break;
            default:
                snapshot.CompliantType = null;
                break;
        }
        await _orderSnapshotRepo.InsertAsync(snapshot, token);
    }

    public string GetUserId(object oldId)
    {
        return _users.FirstOrDefault(m => m.OldUserId == oldId.ObjToInt())?.Id;
    }

    public string GetUserName(object oldId)
    {
        return _users.FirstOrDefault(m => m.OldUserId == oldId.ObjToInt())?.Name;
    }

    public DataTable GetDelay(string flowId)
    {
        var sql = $@" SELECT * FROM  dbo.Flow11_Postpone WHERE FPE_FlowID = {flowId} AND FPE_AuditFlag IN (1) ";
        return _oldOrderRepo.GetDataTable(sql);
    }

    public DataTable GetVisit(string flowId)
    {
        var appraiseSql = $@"SELECT * FROM  dbo.Flow18_AppraiseList  FA 
									LEFT JOIN  dbo.Flow15_PubList FP ON FA.FALT_FlowID =FP.FPLT_FlowID 
									LEFT JOIN  dbo.Flow18_AppraiseExpand FAE ON  FAE.FAED_FALTID = FA.FALT_ListID 
									LEFT JOIN SYS15_DICT SD ON  SD.SDICT_Key='Appraise_DissType' AND  SD.SDICT_PID!=0  AND FAE.FAED_DissTypeID = SD.SDICT_ID
									LEFT JOIN  dbo.SYS09_BMDIR SBM ON FA.FALT_AppBMID = SBM.SBM_ID
									WHERE FALT_FlowID  = {flowId} AND FA.FALT_AppFlag = 1 ";
        return _oldOrderRepo.GetDataTable(appraiseSql);
    }

    public DataTable GetOldPublic(string oldOrderId)
    {

        var sql = $@" SELECT * FROM Flow16_PubIdea FP
						LEFT JOIN Flow03_Search  FS ON FP.FPIA_FlowID = FS.FSH_FlowID
						LEFT JOIN Flow15_PubList FPL ON FP.FPIA_FlowID  = FPL.FPLT_FlowID
						WHERE  FP.FPIA_FlowID ='{oldOrderId}';";
        return _oldOrderRepo.GetDataTable(sql);
    }

    public DataTable GetWorkflow(string OldOrderId, string StoreCode)
    {
        var orderSql = $@"SELECT  * FROM Flow_Transfer01_RunBackups  FTR
							LEFT JOIN Flow_Set01_Store  FSS ON FTR.FTRB_StoreID = FSS.FSL_StoreID  
							LEFT JOIN Flow03_Search FS ON FTR.FTRB_FlowID = FS.FSH_FlowID
							WHERE FTRB_FlowID ='{OldOrderId}' AND FSS.FSL_Code ='{StoreCode}' ORDER BY  FTRB_MoveDate  ";
        return _oldOrderRepo.GetDataTable(orderSql);
    }


    public DataTable GetPgOrganize()
    {
        var sql = $@"  SELECT * FROM  system_organize  ";
        return _orderSnapshotRepo.GetDataTable(sql);
    }

    public DataTable GetPgUser()
    {
        var sql = $@"  SELECT * FROM  ""user""   ";
        return _orderSnapshotRepo.GetDataTable(sql);
    }


    private (string sql, List<SugarParameter> parameters) GetOrderSql()
    {
        var parameters = new List<SugarParameter>
        {
            new SugarParameter("@st_time", DateTime.Now.AddDays(-5)),
            new SugarParameter("@end_time", DateTime.Now)
        };
        return ("SELECT ROW_NUMBER() OVER (ORDER BY FSH_FlowID) AS Serial, CHPRCI.RCI_FlowID,*,REPLACE(CAST(FCT.FCT_Content  AS NVARCHAR(MAX)),CHAR(13)+CHAR(10), '') 受理内容, CASE WHEN ISNULL(FSH_DisposeDate, GETDATE())  > FSH_TTime THEN '超期' WHEN ISNULL(FSH_DisposeDate, GETDATE()) > FSH_EightyTTime THEN '即将超期' ELSE '正常' END AS FSH_OverTimeState, REPLACE(REPLACE(FSH_Title, '</font>', ''), '<font color=mediumorchid>', '') AS FSH_Title_ys, REPLACE(CAST(ISNULL(FTC2.FTC_RRemark,FTRB.FTRB_RRemark) AS NVARCHAR(MAX)),CHAR(13)+CHAR(10), '')  承办意见 FROM  dbo.Flow03_Search FSH LEFT JOIN  Flow02_From FRM ON FSH.FSH_FromID = FRM.FFM_FromID LEFT JOIN  dbo.Flow05_LinkMan  FLM ON FSH.FSH_FlowID = FLM.FLM_FlowID LEFT JOIN  dbo.Flow01_Type  FT ON FT.FTE_TypeFlag =2 AND FSH.FSH_ConTypeID = FT.FTE_TypeID LEFT JOIN  dbo.SYS19_Area  SA ON FSH.FSH_AreaID = SA.SA_AreaID LEFT JOIN  dbo.Flow04_Expand  FED ON FSH.FSH_FlowID = FED.FED_FlowID LEFT JOIN Flow06_Content FCT WITH(NOLOCK) ON FCT.FCT_FlowID = FSH.FSH_FlowID LEFT JOIN  dbo.Flow18_AppraiseList  FAL ON FSH.FSH_FlowID = FAL.FALT_FlowID LEFT JOIN  Flow_Transfer04_Specialty FTS WITH(NOLOCK) ON FTS.FTS_FlowID = FSH.FSH_FlowID LEFT JOIN Flow_Transfer02_Content FTC2 WITH(NOLOCK) ON FTC2.FTC_RunID = FTS.FTS_RunID LEFT JOIN Flow_Transfer01_RunBackups FTRB WITH(NOLOCK) ON  FTS.FTS_RunID =FTRB.FTRB_RunID LEFT JOIN CityHotline_Province.dbo.receive_case_info CHPRCI ON CHPRCI.RCI_FlowID = FSH.FSH_FlowID LEFT JOIN ZG_CityHotline_Web.dbo.Web09_FlowAccept Web09_FlowAccept ON Web09_FlowAccept.WFAT_SyncFlowID = FSH.FSH_FlowID LEFT JOIN ZG_CityHotline_Web.dbo.Web60_EGWAccept Web60_EGWAccept ON Web60_EGWAccept.WEA_AcceptID = Web09_FlowAccept.WFAT_AcceptID LEFT JOIN ZG_CityHotline_Ver3.dbo.SSP_TagLog SSP_TagLog ON SSP_TagLog.STL_FlowID = FTS.FTS_FlowID LEFT JOIN ZG_CityHotline_Ver3.dbo.SSP_WorkLabelSign LabelSign ON LabelSign.SWLS_FlowID  = FSH.FSH_FlowID LEFT JOIN ZG_CityHotline_Ver3.dbo.Flow03_PushContent PushContent ON PushContent.FPC_Code = FSH_Code WHERE FSH_AddDate >= @st_time AND FSH_AddDate <= @end_time AND  ISNULL(FAL.FALT_ListID,0) > 0 AND FAL.FALT_AppFlag ='1' AND (FSH_FromID = 25 OR FSH_FromID = 26);", parameters);
    }

}