data-transfer/Joint/Notice.cs

using Abp;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataTransmission.Joint
{
    public class Notice
    {
        private readonly CommonTool tool = new CommonTool();

        public DataTable GetOldNotice(string st_time, string end_time)
        {
            st_time = "2024-08-26 15:42:22";
            var conn = tool.GetConSqlServer();
            var sql = $@"select WNLT_Title AS  Title,WNCT_Content AS Content,WNLT_TypeID AS BulletinTypeId,WNT_TypeName AS BulletinTypeName,
                    WNED_ReadCount AS ReadedNum,WNLT_CreateDate AS BulletinTime ,2 AS BulletinState,WNED_WebPubFlag AS WebPub,WNED_WeChat AS WeChat,
                    '001' AS SourceOrgId,'市民热线服务系统' AS SourceOrgName,WNLT_CreateDate AS CommitTime,WNLT_AuditUserID AS AuditUserID,WNLT_AuditDate AS AuditDate
,WNLT_UserID as UserID,
WNLT_UserName as UserName  ,WNED_WinFlag as WinFlag  ,   WNED_UpFlag as UpFlag  
from Web04_NoticeList nl
                    left join Web05_NoticeExpand ne on nl.WNLT_NoticeID=ne.WNED_NoticeID
                    left join Web06_NoticeContent nc on nc.WNCT_NoticeID=nl.WNLT_NoticeID
                    left join Web03_NoticeType nt on nl.WNLT_TypeID=nt.WNT_TypeID
                    where WNLT_CreateDate>='{st_time}' and WNLT_CreateDate<='{end_time}'";
            //HR.SHR_Date >= '{st_time}' AND HR.SHR_Date <= '{end_time}' AND
            return tool.GetDataTable(sql, conn);
        }

        public string GetNoticeSql(DataRow item)
        {
            var noticeSql = $@"INSERT INTO ""public"".""bulletin"" (""Id"", ""Title"", ""Content"", ""BulletinTypeId"", ""BulletinTypeName"", ""ReadedNum"", ""BulletinTime"", 
""LoseEfficacyTime"", ""BulletinState"", ""PushRanges"", ""SourceOrgId"", ""SourceOrgName"", ""CreationTime"", ""CreatorId"", ""CreatorName"",
""CreatorOrgId"", ""CreatorOrgName"", ""CreatorOrgLevel"", ""AreaId"", ""CommitTime"", ""ExaminOpinion"", ""ExaminManId"", ""ExaminTime"", 
""IsArrive"",""DisplayLocation"")  VALUES";

            var PushRanges = "[";
            if (item["WebPub"].ToString() == "1")
            {
                PushRanges += $@"{{""Key"":""2"",""Value"":""门户网站""}}";
            }
            if (item["WeChat"].ToString() == "1")
            {
                PushRanges += $@",{{""Key"":""1"",""Value"":""微信小程序""}}";
            }
            PushRanges += "]";
            var noticeId = SequentialGuidGenerator.Instance.Create().ToString("D");
            var cretuser = GetPgUser(item["UserID"].ToString());
            string cretuserid = "";
            if (cretuser != null && cretuser.Rows.Count > 0)
            {
                cretuserid = cretuser.Rows[0]["Id"].ToString();
            }

            var aduuser = GetPgUser(item["AuditUserID"].ToString());
            string caduuserid = "";
            if (aduuser != null && aduuser.Rows.Count > 0)
            {
                caduuserid = aduuser.Rows[0]["Id"].ToString();
            }
            var displayLocation = "[";
            if (item["WinFlag"].ToString() == "1")//飘窗 [{"Key":"1","Value":"是否置顶"},{"Key":"2","Value":"是否飘窗 "}]
            {
                displayLocation += $@"{{""Key"":""2"",""Value"":""是否飘窗""}}";
            }
            if (item["UpFlag"].ToString() == "1")//置顶
            {
                displayLocation += $@",{{""Key"":""1"",""Value"":""是否置顶""}}";
            }
            displayLocation += "]";
            noticeSql += $@"('{noticeId}', '{item["Title"]}', '{item["Content"]}', '{item["BulletinTypeId"]}', '{item["BulletinTypeName"]}', 
                {item["ReadedNum"]}, '{item["BulletinTime"]}', '2050-06-07 00:00:00', 2, '{PushRanges}', '001', '市民热线服务中心',
                '{item["BulletinTime"]}', '{cretuserid}', '{item["UserName"]}', '001', '市民热线服务中心', 1, '001', '{item["CommitTime"]}', 
                '同意', '{caduuserid}', '{item["AuditDate"]}', 't','{displayLocation}')";

            return noticeSql;

        }

        public DataTable GetPgUser(string userid)
        {
            var conn = tool.GetConPgSql();
            var sql = $@"select ""Id"",""Name"" from ""user"" where ""OldUserId""='{userid}' ";
            return tool.GetDataTable(sql, conn);
        }
    }
}