data-transfer/Joint/Visit.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 Visit
	{
		private readonly CommonTool tool = new CommonTool();
		public DataTable GetVisit(string flowId) {
			var conn = tool.GetConSqlServer();
			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 tool.GetDataTable(appraiseSql, conn);
		}

		public string GetVisitSql(DataTable data,DataRow row,string VisitId, string orderId,DataTable org,DataTable user) {
			var num = 0;
			var pgVisitSql = string.Empty;
			var pgVisitDetail = string.Empty;
			foreach (DataRow item in data.Rows)
			{
				DataRow ogrRow = org.AsEnumerable().First(x => x.Field<int?>("oldBmid").ToString() == item["FALT_AppBMID"].ToString());
				var VisitOrgCode = string.IsNullOrEmpty(ogrRow?["Id"].ToString()) ? item["FALT_AppBMID"].ToString() : ogrRow?["Id"].ToString();
				if (num == 0)
				{
					var EmployeeId = item["FALT_UserID"].ToString();
					try
					{
						DataRow userRow = user.AsEnumerable().First(x => x.Field<int?>("OldUserId").ToString() == item["FALT_UserID"].ToString());
						EmployeeId = string.IsNullOrEmpty(userRow?["Id"].ToString()) ? "NULL" : userRow?["Id"].ToString();
					}
					catch (Exception e)
					{
						tool.SaveLog(" VisitMessage :" + e.Message + ";" + row["FALT_UserID"].ToString() + "  ;", 1);
					}
					
					//主表
					pgVisitSql = $@"INSERT INTO ""public"".""order_visit"" (""Id"", ""No"", ""OrderId"", ""VisitState"", ""VisitType"", ""PublishTime"", ""EmployeeId"", ""IsPutThrough"", ""VisitTime"", ""CreationTime"", ""CreatorOrgId"", 
							""CreatorId"", ""AreaId"", ""NowEvaluate"", ""AgainState"", ""CreatorOrgName"", ""CreatorName"", ""CreatorOrgLevel"", ""IsCanHandle"", ""CallId"", ""IsCanAiVisit"", ""FirstVisitTime"", ""LastVisitTime"",
							""AiVisitCount"") VALUES ('{VisitId}', '{row["FSH_Code"].ToString().Trim()}', '{orderId}', 30, 10, '{item["FPLT_PubDate"]}', '{EmployeeId}', 't', '{item["FALT_CallDate"]}', '{item["FALT_InsertDate"]}', '', '', '', '{{""Key"":""{GetSatisfaction(item["FALT_AppTypeName"].ToString())}"",""Value"":""{item["FALT_AppTypeName"]}""}}',
						0, '市民热线服务系统', '', 0, 'f', NULL, 'f', NULL, NULL, 0);
							";
					var seatsDetailId = SequentialGuidGenerator.Instance.Create().ToString("D");
					
					//明细表
					pgVisitDetail = $@"INSERT INTO ""public"".""order_visit_detail"" (""Id"", ""VisitId"", ""VoiceEvaluate"", ""SeatEvaluate"", ""OrgProcessingResults"", ""OrgNoSatisfiedReason"", ""OrgHandledAttitude"", ""VisitContent"", ""VisitOrgCode"",
					""VisitOrgName"", ""CreationTime"", ""CreatorOrgId"", ""CreatorId"", ""AreaId"", ""VisitTarget"", ""CreatorOrgName"", ""CreatorName"", ""CreatorOrgLevel"") VALUES 
					('{seatsDetailId}', '{VisitId}', {GetSatisfaction(item["FALT_SeatsTypeName"].ToString())}, {GetSatisfaction(item["FALT_SeatsTypeName"].ToString())}, NULL, NULL,NULL,
						'{item["FALT_ZxRemark"]}', NULL,NULL, '{item["FALT_InsertDate"]}', '', '', '', 10, '市民热线服务系统', '', 1)";
				}
				//部门
				var bmDetailId = SequentialGuidGenerator.Instance.Create().ToString("D");
				var OrgNoSatisfiedReason = "NULL";
				if ((item["FALT_AppTypeName"].ToString() == "非常不满意" || item["FALT_AppTypeName"].ToString() == "非常不满意") && !string.IsNullOrEmpty(item["SDICT_Name"].ToString()))
				{
					OrgNoSatisfiedReason = $@" '[{{""Key"":""{GetDissatisfiedReason(item["SDICT_Name"].ToString())}"",""Value"":""{item["SDICT_Name"].ToString()}""}}]'";
				}
				pgVisitDetail += $@",('{bmDetailId}', '{VisitId}', NULL, NULL, '{{""Key"":""{GetSatisfaction(item["FALT_AppTypeName"].ToString())}"",""Value"":""{item["FALT_AppTypeName"]}""}}',{OrgNoSatisfiedReason}, 
				'{{""Key"":""{GetSatisfaction(item["FALT_AppTypeBName"].ToString())}"",""Value"":""{item["FALT_AppTypeBName"]}""}}', '{item["FALT_ZxRemark"]}', '{VisitOrgCode}', '{item["SBM_Name"]}', '{item["FALT_InsertDate"]}', '', '', '', 20, '市民热线服务系统', '', 1)";
				num++;
			}
			pgVisitDetail += ";";
			return pgVisitSql + pgVisitDetail;
		}

		private string GetSatisfaction(string name)
		{
			var satisfaction = string.Empty;
			switch (name)
			{
				case "非常不满意":
					satisfaction = "1";
					break;
				case "不满意":
					satisfaction = "2";
					break;
				case "一般":
					satisfaction = "3";
					break;
				case "满意":
					satisfaction = "4";
					break;
				case "非常满意":
					satisfaction = "5";
					break;
				case "未接通":
					satisfaction = "6";
					break;
				case "未做评价":
					satisfaction = "7";
					break;
				case "视为满意":
					satisfaction = "-1";
					break;
				case "默认满意":
					satisfaction = "0";
					break;
				default:
					satisfaction = "-2";
					break;
			}
			return satisfaction;
		}
		private string GetDissatisfiedReason(string name)
		{
			var dissatisfiedReason = string.Empty;
			switch (name)
			{
				case "回复敷衍":
					dissatisfiedReason = "1";
					break;
				case "办件推诿":
					dissatisfiedReason = "2";
					break;
				case "未有效处理工单问题":
					dissatisfiedReason = "3";
					break;
				case "处理案件力度不够":
					dissatisfiedReason = "4";
					break;
				case "未与市民联系":
					dissatisfiedReason = "5";
					break;
				case "阶段性回复":
					dissatisfiedReason = "6";
					break;
				case "未回复":
					dissatisfiedReason = "7";
					break;
				default:
					dissatisfiedReason = "8";
					break;
			}
			return dissatisfiedReason;
		}
	}
}