data-transfer/Joint/Order.cs

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

namespace DataTransmission.Joint
{
	
	public class Order
	{
		private readonly CommonTool tool = new CommonTool();
		public DataTable GetOrder( string st_time, string end_time) {
			var conn = tool.GetConSqlServer();
			//获取工单数据
			var orderSql = $@"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), '') 受理内容,REPLACE(CAST(ISNULL(FTC2.FTC_RRemark,FTRB.FTRB_RRemark) AS NVARCHAR(MAX)),CHAR(13)+CHAR(10), '')  承办意见 FROM  dbo.View_Flow_Process   FSH
				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
				WHERE FSH_AddDate >= '{st_time}' AND FSH_AddDate <= '{end_time}' AND  ISNULL(FAL.FALT_ListID,0) > 0 AND FAL.FALT_AppFlag ='1' ";
			//AND FSH.FSH_Code = '202309190017'
			return tool.GetDataTable(orderSql, conn);
		}

		public string GetOrderISql(DataRow row,string orderId,DataTable org,DataTable user) {
			var sex = row["FLM_LKSex"].ToString() == "1" ? 1 : row["FLM_LKSex"].ToString() == "2" ? 0 : 2;
			var identityType = row["FSH_PersonnelType"].ToString() == "企业" ? 2 : 1;
			var ContactMask = tool.MaskPhoneNumber(row["FSH_LinkTel"].ToString());
			var EmergencyLevel = row["FSH_Urgent"].ToString() == "1" ? 2 : 0;
			var ExpiredStatus = row["FSH_OverTimeState"].ToString() == "正常" ? 0 : row["FSH_OverTimeState"].ToString() == "即将超期" ? 1 : 2;
			var IsSecret = row["FLM_SFlag"].ToString() == "1" ? "t" : "f";
			var IsProvince = row["RCI_FlowID"].ToString() == "NULL" ? "f" : "t";
			var ActualHandleOrgCode = row["FSH_AcceptBMID"].ToString();
			try
			{
				var orgRow = org.AsEnumerable().First(x => x.Field<int?>("oldBmid").ToString() == row["FSH_AcceptBMID"].ToString());
				ActualHandleOrgCode = string.IsNullOrEmpty(orgRow?["Id"].ToString()) ? "NULL" : orgRow?["Id"].ToString();
			}
			catch (Exception e)
			{
				tool.SaveLog(" OrderMessage :" + e.Message + ";" + row["FSH_AcceptBMID"].ToString() + "  ;", 1);
			}
			
			var pgOrderSql = $@"INSERT INTO ""public"".""order"" (""Id"", ""CallId"", ""FromPhone"", ""TransferPhone"", ""FromName"", ""FromGender"", ""IdentityType"", ""LicenceTypeCode"",
						""LicenceType"", ""LicenceNo"", ""AgeRangeCode"", ""AgeRange"", ""Contact"", ""ContactMask"", ""AcceptSms"", ""Company"",
						""No"", ""AcceptType"", ""EmergencyLevel"", ""Title"", ""HotspotId"", ""HotspotName"", ""HotspotSpliceName"", ""HotspotExternal"",
						""IncidentTime"", ""PushTypeCode"", ""PushType"", ""Content"", ""Status"", ""StartTime"", ""ExpiredTime"", ""FiledTime"",
						""ExpiredStatus"", ""WorkflowId"", ""ExpiredTimeConfigId"", ""Longitude"", ""Latitude"", ""AreaCode"", ""Street"", ""LastModificationTime"",
						""IsDeleted"", ""DeletionTime"", ""CreationTime"", ""CreatorOrgId"", ""CreatorId"", ""AreaId"", ""Source"", ""SmsSended"",
						""ProcessType"", ""IsPublicity"", ""IsSecret"", ""ActualHandleOrgName"", ""ActualHandleOrgCode"", ""ActualOpinion"", ""SourceChannel"", ""IsProvince"", 
						""ActualHandleStepCode"",""ActualHandleStepName"", ""ActualHandleStepCreateTime"", ""ActualHandleTime"", ""ActualHandlerId"", ""ActualHandlerName"", ""OrgLevelOneCode"", ""OrgLevelOneName"", 
						""AcceptorId"",""AcceptorName"", ""SourceChannelCode"", ""ExpiredTimeProvince"", ""AcceptorStaffNo"", ""FirstVisitResult"", ""FirstVisitResultCode"", ""ActualHandleOrgAreaCode"", 
						""ActualHandleOrgAreaName"",""CallAddress"", ""DuplicateIds"", ""IsCancel"", ""TimeLimit"", ""TimeLimitCount"", ""TimeLimitUnit"", ""RealHandlerName"", 
						""RealHandlerPhone"",""RealCommunicationMode"", ""RealCommunicationTime"", ""RealCommunicationAddress"", ""AcceptorOrgCode"", ""AcceptorOrgName"", ""Address"", ""FullAddress"", 
						""CenterToOrgTime"",""ActualHandleStepAcceptTime"", ""HandleDuration"", ""HandleDurationWorkday"", ""AllDuration"", ""AcceptTypeCode"", ""Additions"", ""CreatorOrgName"", 
						""CreatorName"",""FlowedOrgIds"", ""FlowedUserIds"", ""CreatorOrgLevel"", ""ProvinceNo"", ""ExternalId"", ""Password"", ""EnterpriseName"", 
						""ZhuanBanMingCheng"", ""CounterSignType"", ""IncidentPurpose"", ""RealIsContacted"", ""RealContactLocale"", ""TagNames"", ""SignerId"", ""SignerName"", 
						""FileJson"",""Province"", ""City"", ""County"", ""Town"", ""EventCategoryId"", ""EventCategoryName"", ""EventCategorySpliceName"",
						""EventCategoryExternal"",""NearlyExpiredTime"", ""CsActualHandleOrgIds"", ""CsActualHandleUserIds"", ""HandlerUsers"", ""HandlerOrgs"", ""FileDuration"", ""FileDurationWorkday"", 
						""AllDurationWorkday"") VALUES (
						'{orderId}', NULL, '{row["FSH_FromTel"].ToString()}', '{row["FSH_HotlineNum"]}',  '{row["FSH_LinkName"]}', {sex}, {identityType}, NULL,
						NULL, '', NULL, NULL, '{row["FSH_LinkTel"].ToString()}', '{ContactMask}', 'f', NULL,
						'{row["FSH_Code"].ToString().Trim()}', '{row["FSH_PurTypeName"]}', {EmergencyLevel}, '{row["FSH_Title_ys"]}', '{row["FTE_ProvinceCode"]}', '{row["FTE_TypeName"]}', '{row["FTE_TypeNameFull"]}', NULL,
						NULL,  '{row["FSH_PushType"]}', NULL, '{row["受理内容"]}', 500, '{row["FSH_AddDate"]}', '{row["FSH_TTime"]}', '{row["FSH_DisposeDate"]}',
						{ExpiredStatus}, NULL, NULL, NULL, NULL, '{row["SA_AreaCode"]}', '{row["FLM_LKAddress"]}', NULL,
						'f', NULL, '{row["FSH_AddDate"]}', NULL, NULL, NULL, 0, 'f',
						20, 'f', '{IsSecret}', '{row["FSH_AcceptName"]}', {ActualHandleOrgCode}, '{row["承办意见"]}', '{row["FSH_FromName"]}', '{IsProvince}',
						NULL, NULL, '{row["FSH_AcceptDate"]}', '{row["FSH_AcceptDate"]}',NULL, '{row["FSH_AcceptUserName"]}', NULL, NULL,
						NULL, '{row["FSH_UserName"]}', NULL,NULL, '', NULL, NULL, '{row["SA_AreaCode"]}',
						'{row["SA_AreaName"]}', '{row["FLM_LKAddress"]}', NULL, 'f', '5个工作日', 5, 2, NULL,
						NULL, NULL, NULL, NULL, NULL, '市民热线服务系统', '{row["FLM_LKAddress"]}', '{row["FLM_LKAddress"]}',
						'2024-03-15 09:49:55.383397', '2024-03-15 09:53:50.264902', '4', '4', '5', '{GetAcceptTypeCode(row["FSH_PurTypeName"].ToString())}', '[]', '市民热线服务系统',
						'坐席', '[""001""]', '[]', 1, '{row["FSH_ProCode"]}', NULL, '{row["FED_ReultPWD"]}', NULL,
						NULL, NULL, NULL, NULL, NULL, NULL, '08dc0121-5960-4267-817c-b6dc6c068fa4', '坐席',
						NULL, NULL, '宜宾市', '', '', '', '', '', 
						'19', '{row["FSH_EightyTTime"]}', '[]', '[]', '[]', '[]', '4', '4',
						'4');";
			return pgOrderSql;
		}

		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;
		}
	}
}