data-transfer/DataTransmission/Joint/Order.cs

using DataTransmission.Entity;
using DataTransmission.Enum;
using Newtonsoft.Json;
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), '') 受理内容,
			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
				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 = '202409240002'
			//AND FSH.FSH_Code = '202409240002'
			return tool.GetDataTable(orderSql, conn);
		}

		public string GetOrderISql(DataRow row,string orderId,DataTable org,DataTable user, Entity.Config config) {
			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" || string.IsNullOrEmpty(row["RCI_FlowID"].ToString()) ? "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);
			}
			#region CrEnt
			var order = new Entity.Order();
			//order.InitId();
			order.Id = orderId;
			order.CallId = row["FSH_SessionID"].ToString();
			order.FromPhone = row["FSH_FromTel"].ToString();
			order.TransferPhone = row["FSH_HotlineNum"].ToString();
			order.FromName = row["FSH_LinkName"].ToString();
			order.FromGender = sex;
			order.IdentityType = identityType;
			order.Contact = row["FSH_LinkTel"].ToString();
			order.ContactMask = ContactMask;
			order.AcceptSms = false;
			order.No = row["FSH_Code"].ToString().Trim();
			order.AcceptType = row["FSH_PurTypeName"].ToString();
			order.Title = row["FSH_Title_ys"].ToString();
			order.HotspotId = row["FTE_ProvinceCode"].ToString();
			order.HotspotName = row["FTE_TypeName"].ToString();
			order.HotspotSpliceName = row["FTE_TypeNameFull"].ToString();
			order.PushTypeCode = row["FSH_PushType"].ToString();
			order.Content = row["受理内容"].ToString();
			order.Status =  Enum.EOrderStatus.Visited;
			order.StartTime = string.IsNullOrEmpty(row["FSH_AddDate"].ToString()) ? "NULL" : Convert.ToDateTime(row["FSH_AddDate"]).ToString() ;
			order.ExpiredTime = string.IsNullOrEmpty(row["FSH_TTime"].ToString()) ? "NULL" : Convert.ToDateTime(row["FSH_TTime"]).ToString();
			order.FiledTime = string.IsNullOrEmpty(row["FSH_DisposeDate"].ToString()) ? "NULL" : Convert.ToDateTime(row["FSH_DisposeDate"]).ToString();
			order.ExpiredStatus = ExpiredStatus;
			order.AreaCode = row["SA_AreaCode"].ToString();
			order.Street = row["FLM_LKAddress"].ToString();
			order.IsDeleted = false;
			order.CreationTime = Convert.ToDateTime(row["FSH_AddDate"])   ;
			order.SmsSended = false;
			order.ProcessType = row["FSH_AcceptBMID"].ToString() == config.CenterId ? 10 : 20;
			if (config.Name == "自贡市")
			{
				order.IsPublicity = false;
			}
			else {
				order.IsPublicity = row["FSH_IsPublic"].ToString() == "1"; //0：不公开 ；1：公开 12345在线传递过来的状态
			}
			order.IsSecret = row["FLM_SFlag"].ToString() == "1";
			order.ActualHandleOrgName = row["FSH_AcceptName"].ToString();
			order.ActualHandleOrgCode = ActualHandleOrgCode;
			order.ActualOpinion = row["承办意见"].ToString();
			order.SourceChannel = row["FSH_FromName"].ToString();
			order.IsProvince = !(row["RCI_FlowID"].ToString() == "NULL");
			//Acceptor
			var acceptor = user.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldUserId").ToString() == row["FTRB_APUserID"].ToString());
			DataRow acceptorOrg = null;
			if (acceptor != null) {
				 acceptorOrg = org.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldBmid").ToString() == acceptor["OrgId"].ToString());
			}
			order.AcceptorId = row["FSH_CreateUserID"].ToString();
			if (acceptor != null)
			{
				order.AcceptorId = acceptor["Id"].ToString();
				order.AcceptorName = acceptor["Name"].ToString();
			}
			order.AcceptorOrgCode = row["FSH_AcceptBMID"].ToString();
			if (acceptorOrg != null)
			{
				order.AcceptorOrgCode = acceptorOrg["Id"].ToString();
				order.AcceptorOrgName = acceptorOrg["Name"].ToString();
			}
			order.AcceptorName = row["FSH_UserName"].ToString();
			order.SourceChannelCode = row["FFM_ProFromCode"].ToString();
			order.ActualHandleOrgAreaCode = row["SA_AreaCode"].ToString();
			order.ActualHandleOrgAreaName = row["SA_AreaName"].ToString();
			order.CallAddress = row["FLM_LKAddress"].ToString();
			//order.TimeLimitCount = int.Parse(row["FTRB_TLimits"].ToString());
			order.TimeLimitCount = row["FTRB_TLimits"].ToString();
			order.TimeLimit = order.TimeLimitCount + "个工作日";
			order.AcceptTypeCode = GetAcceptTypeCode(row["FSH_PurTypeName"].ToString());
			order.NearlyExpiredTime = string.IsNullOrEmpty(row["FSH_EightyTTime"].ToString()) ? "NULL" : row["FSH_EightyTTime"].ToString() ;
			order.Password = row["FED_ReultPWD"].ToString();
			order.ProvinceNo = row["FSH_ProCode"].ToString();
			order.IsUrgent = row["FSH_Urgent"].ToString() == "1";
			order.OldOrderId = row["FSH_FlowID"].ToString();
			#endregion
			//""WorkflowId"", ""ExpiredTimeConfigId"", ""Longitude"", ""Latitude"", ""ActualHandleStepCode"",""ActualHandleStepName"", ""ActualHandleStepCreateTime"", ""ActualHandleTime"", ""ActualHandlerId"", ""ActualHandlerName"",
			//""ExpiredTimeProvince"", ""AcceptorStaffNo"", ""FirstVisitResult"", ""FirstVisitResultCode"", ""CenterToOrgTime"",""ActualHandleStepAcceptTime"",  ""HandleDuration"", ""HandleDurationWorkday"", ""AllDuration"",
			//""AllDurationWorkday"", ""FileDuration"", ""FileDurationWorkday""
			//tool.SaveLog("order :" + JsonConvert.SerializeObject(order) + "  ;" , 1);
			var pgOrderSql = $@"INSERT INTO ""public"".""order"" (""Id"", ""CallId"", ""FromPhone"", ""TransferPhone"", ""FromName"", ""FromGender"", ""IdentityType"", 
						 ""Contact"", ""ContactMask"", ""AcceptSms"",
						""No"", ""AcceptType"", ""Title"", ""HotspotId"", ""HotspotName"", ""HotspotSpliceName"", 
						 ""PushTypeCode"",  ""Content"", ""Status"", ""StartTime"", ""ExpiredTime"", ""FiledTime"",
						""ExpiredStatus"", ""AreaCode"", ""Street"", 
						""IsDeleted"", ""CreationTime"",  ""SmsSended"",
						""ProcessType"", ""IsPublicity"", ""IsSecret"", ""ActualHandleOrgName"", ""ActualHandleOrgCode"", ""ActualOpinion"", ""SourceChannel"", ""IsProvince"", 
						""AcceptorId"",""AcceptorName"", ""SourceChannelCode"", ""ActualHandleOrgAreaCode"", 
						""ActualHandleOrgAreaName"",""CallAddress"",  ""IsCancel"", ""TimeLimit"", ""TimeLimitCount"", ""TimeLimitUnit"", 
						""AcceptorOrgCode"", ""AcceptorOrgName"", 
						""AcceptTypeCode"", ""Additions"", ""CreatorOrgName"", 
						""CreatorName"",""FlowedOrgIds"", ""FlowedUserIds"", ""CreatorOrgLevel"", ""ProvinceNo"", ""ExternalId"", ""Password"",
						""SignerId"", ""SignerName"", 
						""City"", ""Source"",""IsResolved"",""IsFormalistWorkOrder"",""IsSensitiveWorkOrders"",""IsUrgent"",""IsProvinceZmhd"",
						""EventCategoryExternal"",""NearlyExpiredTime"", ""CsActualHandleOrgIds"", ""CsActualHandleUserIds"", ""HandlerUsers"", ""HandlerOrgs"",
						""Is24HoursComplete"",""IsThreePartyConference"",""IsEvasive"",""IsInactively"",""IsStepUrgent"",""OldOrderId""
						) VALUES (
						'{order.Id}', '{order.CallId}', '{order.FromPhone}', '{order.TransferPhone}',  '{order.FromName}', {order.FromGender}, {order.IdentityType}, 
						 '{order.Contact}', '{order.ContactMask}', '{order.AcceptSms.ToString()}',
						'{order.No}', '{order.AcceptType}', '{order.Title}', '{order.HotspotId}', '{order.HotspotName}', '{order.HotspotSpliceName}',
						'{order.PushTypeCode}', '{order.Content}', 500, '{order.StartTime}', '{order.ExpiredTime}', '{order.FiledTime}',
						{order.ExpiredStatus}, '{order.AreaCode}', '{order.Street}',
						'{order.IsDeleted.ToString()}', '{order.CreationTime}', '{order.SmsSended.ToString()}',
						{order.ProcessType}, '{order.IsPublicity.ToString()}', '{order.IsSecret.ToString()}', '{order.ActualHandleOrgName}', {order.ActualHandleOrgCode}, '{order.ActualOpinion}', '{order.SourceChannel}', '{order.IsProvince.ToString()}',
						'{order.AcceptorId}', '{order.AcceptorName}', '{order.SourceChannelCode}', '{order.ActualHandleOrgAreaCode}',
						'{order.ActualHandleOrgAreaName}', '{order.CallAddress}','f', '{order.TimeLimit}', {order.TimeLimitCount}, 2,
						 '{order.AcceptorOrgCode}', '{order.AcceptorOrgName}',
						 '{order.AcceptTypeCode}', '[]', '{order.AcceptorOrgName}',
						'{order.AcceptorName}', '[""001""]', '[]', 1, '{order.ProvinceNo}', NULL, '{order.Password.Trim()}',
						'{order.AcceptorId}', '{order.AcceptorName}',
						'{config.Name}','0','t','f','f','{order.IsUrgent.ToString()}','f',
						'19', '{order.NearlyExpiredTime.ToString()}', '[]', '[]', '[]', '[]',
						'f','f','f','f','f','{order.OldOrderId}');";
			pgOrderSql = pgOrderSql.Replace("'NULL'", "NULL");
			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;
		}
	}
}