data-transfer/DataTransmission/Joint/OrderPublic.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 OrderPublic
    {
        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 CONVERT(VARCHAR(100),WFSH_FlowID) FlowID,WFSH_LKName LKName,FSH_LinkTel  LinkTel,FSH_PersonnelType PersonnelType ,RTRIM(WFSH_Code) Code,WFSH_Pwd pwd,
	WFSH_Title Title,WFSH_FromID FromID,WFSH_FromName FromName,WFSH_PurTypeID PurTypeID,WFSH_PurTypeName PurTypeName,WFSH_ConTypeID ConTypeID,WFSH_ConTypeName ConTypeName,
	WFSCT_Content Content,   WFSH_RSFlag ResultState ,WFSH_BMName BMName,WFSCT_Result Result ,
	WFSH_AddDate AddDate,FSH_AcceptDate  AcceptDate,WFSH_PubDate PubDate ,FSH_DisposeDate DisposeDate
	                                    from Web10_FlowSearch wfsh with(nolock)
										left join Web10_FlowSearchContent wfshc with(nolock) on wfshc.WFSCT_FlowID=WFSH_FlowID
	                                    left join CityHotline_Ver3.dbo.View_Flow_Process fsh with(nolock) on WFSH_FlowID=FSH_FlowID
	                                    where WFSH_PubFlag=2 and  WFSH_AddDate>='{st_time}' and WFSH_AddDate<='{end_time}'";
            return tool.GetDataTable(sql, conn);
        }

        public string GetNoticeSql(DataRow item)
        {
            var noticeSql = $@"INSERT INTO ""old_public_data"" (""Id"", ""OrderId"", ""FromName"", ""Contact"", ""IdentityType"", ""OrderNo"", 
            ""OrderPwd"", ""Title"", ""SourceChannel"", ""SourceChannelCode"", ""AcceptType"", ""AcceptTypeCode"", ""HotspotId"", ""HotspotName"", 
            ""Content"", ""State"", ""ActualHandleOrgName"", ""ActualOpinion"", ""AcceptTime"", ""PubDate"", ""FiledTime"", 
            ""CreationTime"",  ""CreatorOrgLevel"") VALUES";
            var noticeId = SequentialGuidGenerator.Instance.Create().ToString("D");
            int identityType = 1;
            if (item["PersonnelType"].ToString() == "企业")
            {
                identityType = 2;
            }

            noticeSql += $@"('{noticeId}', '{item["FlowID"]}', '{item["LKName"]}', '{item["LinkTel"]}', {identityType}, '{item["Code"]}', 
                '{item["pwd"]}', '{item["Title"]}',  '{GetSourceChannelCode(item["FromID"].ToString())}', '{item["FromName"]}', '{GetAcceptTypeCode(item["PurTypeName"].ToString())}', '{item["PurTypeName"]}', '{item["ConTypeID"]}', '{item["ConTypeName"]}',
'{item["Content"]}','{item["ResultState"]}','{item["BMName"]}','{item["Result"]}','{item["AcceptDate"]}','{item["PubDate"]}','{item["DisposeDate"]}',
 '2024-10-19 09:49:55.383397',1)";


            return noticeSql;

        }

        private string GetSourceChannelCode(string name)
        {
            var acceptTypeCode = string.Empty;
            switch (name)
            {
                case "1":
                    acceptTypeCode = "RGDH";
                    break;
                case "5":
                    acceptTypeCode = "YTW";
                    break;
                case "9":
                    acceptTypeCode = "QT";
                    break;
                case "10":
                    acceptTypeCode = "ZJ";
                    break;
                case "11":
                    acceptTypeCode = "WX";
                    break;
                case "12":
                    acceptTypeCode = "WB";
                    break;
                case "13":
                    acceptTypeCode = "AP";
                    break;
                case "15":
                    acceptTypeCode = "ZHYB";
                    break;
                case "16":
                    acceptTypeCode = "ZZPT";
                    break;
                case "17":
                    acceptTypeCode = "S12328";
                    break;
                case "21":
                    acceptTypeCode = "SZYSM";
                    break;
                case "22":
                    acceptTypeCode = "YBRMT";
                    break;
                case "24":
                    acceptTypeCode = "S12345";
                    break;
                case "25":
                    acceptTypeCode = "SZMHD";
                    break;
                case "26":
                    acceptTypeCode = "YBS";
                    break;
                case "27":
                    acceptTypeCode = "SZHZ";
                    break;
                case "28":
                    acceptTypeCode = "YB110";
                    break;
                case "29":
                    acceptTypeCode = "SMZXBNCS";
                    break;
                case "30":
                    acceptTypeCode = "IYB";
                    break;

                default:
                    acceptTypeCode = "QT";
                    break;
            }
            return acceptTypeCode;
        }

        private string GetAcceptTypeCode(string name)
        {
            var acceptTypeCode = string.Empty;
            switch (name)
            {
                case "其他":
                    acceptTypeCode = "40";
                    break;
                case "表扬":
                    acceptTypeCode = "25";
                    break;
                case "举报":
                    acceptTypeCode = "30";
                    break;
                case "投诉":
                    acceptTypeCode = "35";
                    break;
                case "咨询":
                    acceptTypeCode = "10";
                    break;
                case "建议":
                    acceptTypeCode = "15";
                    break;
                case "求助":
                    acceptTypeCode = "20";
                    break;
                case "意见":
                    acceptTypeCode = "1";
                    break;
                case "惠民帮助":
                    acceptTypeCode = "2";
                    break;
                case "大气污染举报":
                    acceptTypeCode = "3";
                    break;
                default:
                    acceptTypeCode = "0";
                    break;
            }
            return acceptTypeCode;
        }

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


    }
}