data-transfer/DataTransmission/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, Entity.Config config) {
			var num = 0;
			var pgVisitSql = string.Empty;
			var pgVisitDetail = string.Empty;

			Entity.OrderVisit  visit = new Entity.OrderVisit();
			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);
					}

					var creator = user.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldUserId").ToString() == item["FALT_InsertUserID"].ToString());
					var creatorOrg = org.AsEnumerable().FirstOrDefault(x => x.Field<string?>("Id").ToString() == creator["OrgId"].ToString());

					visit.Id = VisitId;
					visit.No = row["FSH_Code"].ToString().Trim();
					visit.OrderId = orderId;
					visit.PublishTime = Convert.ToDateTime(item["FPLT_PubDate"]);
					visit.EmployeeId = EmployeeId;
					visit.VisitTime = Convert.ToDateTime(item["FALT_CallDate"]);
					visit.CreationTime = Convert.ToDateTime(item["FALT_InsertDate"]);
					visit.CreatorId = row["FALT_InsertUserID"].ToString();
					if (creator != null)
					{
						visit.CreatorId = creator["Id"].ToString();
						visit.CreatorName = creator["Name"].ToString();
					}
					if (creatorOrg != null)
					{
						visit.CreatorOrgId = creatorOrg["Id"].ToString();
						visit.CreatorOrgName = creatorOrg["Name"].ToString();
						visit.CreatorOrgLevel = int.Parse(creatorOrg["Level"].ToString());
						visit.AreaId = creatorOrg["AreaId"].ToString();
						//model.AssignerOrgIsCenter = bool.Parse(creatorOrg["IsCenter"].ToString());
					}
					//主表
					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 (
							'{visit.Id}', '{visit.No}', '{visit.OrderId}', 30, 10, '{visit.PublishTime.ToString()}', '{visit.EmployeeId}', 't', '{visit.VisitTime}', '{visit.CreationTime}', '{visit.CreatorOrgId}',
							'{visit.CreatorId}', '{visit.AreaId}', '{{""Key"":""{GetSatisfaction(item["FALT_AppTypeName"].ToString())}"",""Value"":""{item["FALT_AppTypeName"]}""}}',0, '{visit.CreatorOrgName}', '{visit.CreatorName}',
							{visit.CreatorOrgLevel}, 'f', NULL, 'f', '{visit.VisitTime}', '{visit.VisitTime}', 0);";
					//var seatsDetailId = SequentialGuidGenerator.Instance.Create().ToString("D");
					
					Entity.OrderVisitDetail seatDetail = new Entity.OrderVisitDetail();

					//var creatorDetail = user.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldUserId").ToString() == item["FALT_InsertUserID"].ToString());
					//var creatorDetailOrg = org.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldBmid").ToString() == creator["OrgId"].ToString());

					seatDetail.InitId();
					seatDetail.VisitId = visit.Id;
					seatDetail.CreationTime = Convert.ToDateTime(item["FALT_InsertDate"]);


					//明细表
					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 
					('{seatDetail.VisitId}', '{VisitId}', {GetSatisfaction(item["FALT_SeatsTypeName"].ToString())}, {GetSatisfaction(item["FALT_SeatsTypeName"].ToString())}, NULL, NULL,NULL,'{item["FALT_ZxRemark"]}',
					NULL,NULL, '{seatDetail.CreationTime.ToString()}', '{visit.CreatorOrgId}', '{visit.CreatorId}', '{visit.AreaId}', 10, '{visit.CreatorOrgName}', '{visit.CreatorName}', {visit.CreatorOrgLevel})";
				}
				//部门
				//var bmDetailId = SequentialGuidGenerator.Instance.Create().ToString("D");
				Entity.OrderVisitDetail bmDetail = new Entity.OrderVisitDetail();
				bmDetail.InitId();
				bmDetail.VisitId = visit.Id;
				bmDetail.CreationTime = Convert.ToDateTime(item["FALT_InsertDate"]);

				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(),config)}"",""Value"":""{item["SDICT_Name"].ToString()}""}}]'";
				}
				pgVisitDetail += $@",('{bmDetail.Id}', '{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"]}',
				'{bmDetail.CreationTime.ToString()}', '{visit.CreatorOrgId}', '{visit.CreatorId}', '{visit.AreaId}', 20, '{visit.CreatorOrgName}', '{visit.CreatorName}', {visit.CreatorOrgLevel})";
				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, Entity.Config config) {
			switch (config.Name)
			{
				case "宜宾市":
					return GetDissatisfiedReason_YB(name);
				case "自贡市":
					return GetDissatisfiedReason_ZG(name);
				case "泸州市":
					return GetDissatisfiedReason_LZ(name);
				default:
					return "";
			}
		}
		private string GetDissatisfiedReason_YB(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;
				case "对部门工作人员态度不满意":
					dissatisfiedReason = "8";
					break;
				default:
					dissatisfiedReason = "";
					break;
			}
			return dissatisfiedReason;
		}

		private string GetDissatisfiedReason_ZG(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;
				case "对部门工作人员态度不满意":
					dissatisfiedReason = "8";
					break;
				case "办件时间长":
					dissatisfiedReason = "9";
					break;
				case "阶段性回复":
					dissatisfiedReason = "10";
					break;
				case "其他":
					dissatisfiedReason = "11";
					break;
				default:
					dissatisfiedReason = "";
					break;
			}
			return dissatisfiedReason;
		}

		private string GetDissatisfiedReason_LZ(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;
				case "对部门工作人员态度不满意":
					dissatisfiedReason = "8";
					break;
				default:
					dissatisfiedReason = "";
					break;
			}
			return dissatisfiedReason;
		}
	}
}