data-transfer/Joint/Workflow.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 Workflow
	{
		private readonly CommonTool tool = new CommonTool();

		public DataTable GetWorkflow(string OldOrderId,string StoreCode) {
			var conn = tool.GetConSqlServer();
			var orderSql = $@"SELECT  * FROM Flow_Transfer01_RunBackups  FTR
							LEFT JOIN Flow_Set01_Store  FSS ON FTR.FTRB_StoreID = FSS.FSL_StoreID  
							LEFT JOIN Flow03_Search FS ON FTR.FTRB_FlowID = FS.FSH_FlowID
							WHERE FTRB_FlowID ='{OldOrderId}' AND FSS.FSL_Code ='{StoreCode}' ORDER BY  FTRB_MoveDate  ";
			return tool.GetDataTable(orderSql, conn);
		}

		public DataTable GetWorkflow_Run(string OldOrderId, string StoreCode)
		{
			var conn = tool.GetConSqlServer();
			var orderSql = $@"SELECT  FTR_RunID as FTRB_RunID,FTR_ItemName as FTRB_ItemName, FTR_APBMID as FTRB_APBMID,FTR_APDate as FTRB_APDate,FTR_MoveUserID as FTRB_MoveUserID,
							FTR_DisposeDate as FTRB_DisposeDate, FTC.FTC_RRemark as FTRB_RRemark , FTR_APUserID as FTRB_APUserID ,FTR_UserID as FTRB_UserID,FTR_PRunID as FTRB_PRunID,
							FTR_TTime as FTRB_TTime,FTR_MoveUserID as FTRB_MoveUserID,FTR_MoveDate as FTRB_MoveDate,* FROM Flow_Transfer01_Run  FTR
							LEFT JOIN Flow_Set01_Store  FSS ON FTR.FTR_StoreID = FSS.FSL_StoreID  
							LEFT JOIN Flow03_Search FS ON FTR.FTR_FlowID = FS.FSH_FlowID
							LEFT JOIN Flow_Transfer02_Content FTC ON FTR.FTR_ListID = FTC.FTC_ListID
							WHERE FTR_FlowID ='{OldOrderId}' AND FSS.FSL_Code ='{StoreCode}' ORDER BY  FTR_MoveDate  ";
			return tool.GetDataTable(orderSql, conn);
		}

		public string GetWorkflowSql(DataTable data, DataRow row, string orderId, DataTable org, DataTable user, DataRow module,Entity.OrderDelay delay, Entity.Config config)
		{
			var pgSql = string.Empty;

			var workflow = new Entity.Workflow();
			workflow.InitId();
			workflow.DefinitionId = module["DefinitionId"].ToString();
			workflow.ModuleId = module["Id"].ToString();
			workflow.ModuleCode = module["Code"].ToString();
			workflow.ModuleName = module["Name"].ToString();
			workflow.Title = row["FSH_Title_ys"].ToString();
			var stStep = data.AsEnumerable().OrderBy(x => x.Field<int?>("FTRB_RunID")).FirstOrDefault();
			var endStep = data.AsEnumerable().OrderByDescending(x=>x.Field<int?>("FTRB_RunID")).FirstOrDefault(x=> x.Field<string>("FTRB_ItemName").ToString() == "结束");
			var actualStep = data.AsEnumerable().FirstOrDefault(x => x.Field<int?>("FSH_AcceptBMID").ToString() == x.Field<int?>("FTRB_APBMID").ToString());
			if (row["FSH_AcceptBMID"].ToString() == config.CenterId)
			{
				actualStep = data.AsEnumerable().FirstOrDefault(x => x.Field<DateTime?>("FSH_AcceptDate") != null && x.Field<DateTime?>("FTRB_APDate")!= null &&  x.Field<DateTime?>("FSH_AcceptDate").ToString() == x.Field<DateTime?>("FTRB_APDate").ToString());
			}

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

			if (workflow.ModuleCode == "OrderDelay")
			{
				 endStep = data.AsEnumerable().OrderByDescending(x => x.Field<DateTime?>("FTRB_MoveDate")).FirstOrDefault();
				actualStep = endStep;
			}
			workflow.EndTime = DateTime.Parse(endStep["FTRB_DisposeDate"].ToString());
			workflow.ActualHandleStepCode = "";
			workflow.ActualHandleStepName = "";
			workflow.ActualHandleStepId = actualStep["FSH_RunID"].ToString();
			workflow.ActualOpinion = actualStep["FTRB_RRemark"].ToString();
			workflow.CreationTime = DateTime.Parse(stStep["FSH_AddDate"].ToString());
			if (creator != null)
			{
				workflow.CreatorId = creator["Id"].ToString();
				workflow.CreatorName = creator["Name"].ToString();
			}
			if (creatorOrg != null)
			{
				workflow.CreatorOrgId = creatorOrg["Id"].ToString();
				workflow.CreatorOrgName = creatorOrg["Name"].ToString();
				workflow.CreatorOrgLevel = int.Parse(creatorOrg["Level"].ToString());
				workflow.AreaId = creatorOrg["AreaId"].ToString();
				//workflow.AssignerOrgIsCenter = bool.Parse(creatorOrg["IsCenter"].ToString());
			}
			if (workflow.ModuleCode == "OrderHandle")
			{
				workflow.FlowType =  EFlowType.Handle;
				workflow.ReviewResult =  EReviewResult.Unknown;
				workflow.ExternalId = row["FSH_FlowID"].ToString();
			}
			else if (workflow.ModuleCode == "OrderDelay")
			{
				workflow.FlowType =  EFlowType.Review;
				workflow.ReviewResult =  delay.DelayState == EDelayState.Pass ? EReviewResult.Approval : delay.DelayState == EDelayState.NoPass ? EReviewResult.Failed : EReviewResult.Unknown ;
				workflow.ExternalId = delay.Id;
			}

			var order = 1;
			foreach (DataRow item in data.Rows) {
				
				var start = order == 1;
				var end = order == data.Rows.Count;
 				var stepSql = GetWorkflowStepSql(item, org, user, start, end, ref workflow, config);
				pgSql += stepSql ;
				order++;
			}

			var sql = $@"INSERT INTO ""public"".""workflow"" (""Id"", ""DefinitionId"", ""ModuleId"", ""ModuleName"", ""ModuleCode"", ""Title"", ""Status"", 
				""ActualOpinion"", ""IsInCountersign"", ""AllDuration"", ""ExternalId"", ""HandlerUsers"", ""HandlerOrgs"", ""FlowedOrgIds"", ""FlowedUserIds"", ""CreationTime"",
				""CreatorId"", ""CreatorName"", ""CreatorOrgId"", ""CreatorOrgName"", ""CreatorOrgLevel"", ""AreaId"", ""FlowType"", ""ReviewResult"")
				VALUES ('{workflow.Id}', '{workflow.DefinitionId}', '{workflow.ModuleId}', '{workflow.ModuleName}', '{workflow.ModuleCode}', '{workflow.Title}', 20,
			    '{workflow.ActualOpinion}', 'f', '0', '{workflow.ExternalId}', '[]', '[]', '[]', '[]',   '{workflow.CreationTime.ToString()}',
				'{workflow.CreatorId}', '{workflow.CreatorName}', '{workflow.CreatorOrgId}', '{workflow.CreatorOrgName}', {workflow.CreatorOrgLevel}, '{workflow.AreaId}', {(int)workflow.FlowType}, {(int)workflow.ReviewResult});";

			
			return pgSql + sql;
		}

		/// <summary>
		/// 生成流程
		/// </summary>
		/// <param name="item"></param>
		/// <param name="org"></param>
		/// <param name="user"></param>
		/// <param name="start"></param>
		/// <param name="end"></param>
		/// <param name="workflow"></param>
		/// <returns></returns>
		private string GetWorkflowStepSql(DataRow item, DataTable org, DataTable user,bool start,bool end, ref Entity.Workflow workflow, Entity.Config config) {

			//Acceptor
			var acceptor = user.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldUserId").ToString() == item["FTRB_APUserID"].ToString());
			var acceptorOrg = org.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldBmid").ToString() == item["FTRB_APBMID"].ToString());

			//Handler
			var handler = user.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldUserId").ToString() == item["FTRB_UserID"].ToString());
			DataRow handlerOrg = null;
			if (handler != null)
			{
				handlerOrg = org.AsEnumerable().FirstOrDefault(x => x.Field<string?>("Id").ToString() == handler["OrgId"].ToString());
			}

			//Creator
			var creator = user.AsEnumerable().FirstOrDefault(x => x.Field<int?>("oldUserId").ToString() == item["FTRB_MoveUserID"].ToString());
			DataRow creatorOrg = null;
			if (creatorOrg != null)
			{
				creatorOrg = org.AsEnumerable().FirstOrDefault(x => x.Field<string?>("Id").ToString() == creator["OrgId"].ToString());
			}


			var step = new Entity.WorkflowStep();
			step.InitId();
			step.PrevStepId = item["FTRB_PRunID"].ToString();
			step.PrevStepCode = string.Empty;
			step.AcceptorId = item["FTRB_APUserID"].ToString();
			if (acceptor != null) {
				step.AcceptorId = acceptor["Id"].ToString();
				step.AcceptorName = acceptor["Name"].ToString();
			}
			step.AcceptorOrgId = item["FTRB_APBMID"].ToString();
			if (acceptorOrg != null) {
				step.AcceptorOrgId = acceptorOrg["Id"].ToString();
				step.AcceptorOrgName = acceptorOrg["Name"].ToString();
				step.AcceptorOrgAreaCode = acceptorOrg["AreaCode"].ToString();
				step.AcceptorOrgAreaName = acceptorOrg["AreaName"].ToString();
			}
			step.AcceptTime = string.IsNullOrEmpty(item["FTRB_APDate"].ToString()) ? "NULL" : "'" + item["FTRB_APDate"].ToString() + "'";
			//var AcceptTime = step.AcceptTime.HasValue ? "'" + step.AcceptTime.ToString() + "'" : step.AcceptTime;
			
			if (handler is null)
			{
				step.HandlerId = item["FTRB_UserID"].ToString();
				step.HandlerName = string.Empty;
			}
			else {
				step.HandlerId = handler["Id"].ToString();
				step.HandlerName = handler["Name"].ToString();
			}
			if (handlerOrg != null)
			{
				step.HandlerOrgId = handlerOrg["Id"].ToString();
				step.HandlerOrgName = handlerOrg["Name"].ToString();
				step.HandlerOrgAreaCode = handlerOrg["AreaCode"].ToString();
				step.HandlerOrgAreaName = handlerOrg["AreaName"].ToString();
				step.HandlerOrgIsCenter = bool.Parse(handlerOrg["IsCenter"].ToString());
			}
			else {
				step.HandlerOrgName ="热线中心";
				step.HandlerOrgId = config.CenterId.ToString();
				step.HandlerOrgAreaCode = string.Empty;
				step.HandlerOrgAreaName = string.Empty;
				step.HandlerOrgIsCenter = false;
			}
			step.HandleTime = string.IsNullOrEmpty(item["FTRB_DisposeDate"].ToString())  ? "NULL" : "'" + item["FTRB_DisposeDate"].ToString() + "'";
			step.Name = item["FTRB_ItemName"].ToString();
			step.StepType = start ? Enum.EStepType.Start : end ? Enum.EStepType.End : Enum.EStepType.Normal;
			step.BusinessType = item["FTRB_ItemName"].ToString().Contains("话务") ? EBusinessType.Seat : item["FTRB_ItemName"].ToString().Contains("派单") ? EBusinessType.Send :
				item["FTRB_ItemName"].ToString().Contains("结束") || item["FTRB_ItemName"].ToString().Contains("发布") || item["FTRB_ItemName"].ToString().Contains("回访") ? EBusinessType.File : EBusinessType.Department;

			step.Opinion = item["FTRB_RRemark"].ToString();
			step.StepExpiredTime = string.IsNullOrEmpty(item["FTRB_TTime"].ToString())  ? "NULL" : "'" +item["FTRB_TTime"].ToString() + "'";
			step.CreationTime = Convert.ToDateTime(item["FTRB_MoveDate"]) ;
			
			if (creator != null)
			{
				step.CreatorId = creator["Id"].ToString();
				step.CreatorName = creator["Name"].ToString();
			}
			else {
				step.CreatorId = item["FTRB_MoveUserID"].ToString();
				step.CreatorName = string.Empty;
			}
			if (creatorOrg != null)
			{
				step.CreatorOrgId = creatorOrg["Id"].ToString();
				step.CreatorOrgName = creatorOrg["Name"].ToString();
				step.CreatorOrgLevel = int.Parse(creatorOrg["Level"].ToString());
				step.AreaId = creatorOrg["AreaId"].ToString();
				step.AssignerOrgIsCenter = bool.Parse(creatorOrg["IsCenter"].ToString());
			}
			else {
				step.CreatorOrgName = "热线中心";
				step.CreatorOrgId = config.CenterId.ToString();
				step.CreatorOrgLevel = 1;
				step.AreaId = string.Empty;
				step.AssignerOrgIsCenter = true;
			}

			//tool.SaveLog("step :" + JsonConvert.SerializeObject(step)  + "  ;" + "workflow: "+ JsonConvert.SerializeObject(workflow) , 1);

			var stepSql = $@"INSERT INTO ""public"".""workflow_step"" (
			""Id"", ""NextSteps"", ""PrevStepId"", ""PrevStepCode"", ""IsMain"", ""Status"", ""IsOrigin"", ""CountersignId"",""CountersignPosition"", 
			""IsStartedCountersignEnd"", ""StartCountersignId"", ""IsCountersignEndStep"", ""CountersignStartStepId"", ""WorkflowId"", ""FlowAssignType"", ""Handlers"", ""PrevChosenStepCode"",
			""AcceptorId"", ""AcceptorName"", ""AcceptorOrgId"", ""AcceptorOrgName"", ""AcceptorOrgAreaCode"", ""AcceptorOrgAreaName"",
			""AcceptTime"", ""HandlerId"", ""HandlerName"", ""HandlerOrgId"", 
			""HandlerOrgIsCenter"", ""HandlerOrgName"", ""HandlerOrgAreaCode"", ""HandlerOrgAreaName"", ""HandleTime"", ""Name"", ""Code"", ""StepType"", 
			""BusinessType"", ""HandlerType"", ""CanReject"",""ExecuteMode"", ""CanStartCountersign"",  ""NextHandlers"", ""BackToCountersignEnd"", ""IsSms"", ""Opinion"", ""StepExpiredTime"",
			""IsStartCountersign"", ""CreationTime"", ""CreatorId"", ""CreatorName"", ""CreatorOrgId"", ""CreatorOrgName"", ""CreatorOrgLevel"", ""AreaId"", ""FlowDirection"", ""FileJson"", 
			""IsActualHandled"", ""ModuleId"", ""ModuleName"", ""ModuleCode"", ""ExpiredStatus"", ""CountersignSteps"", ""ExternalId"", ""PrevStepName"", ""AssignerId"", ""AssignerName"", ""AssignerOrgId"", ""AssignerOrgName"", ""AssignerOrgIsCenter"", ""HandleMode"", ""ReviewResult"",""InstanceMode"") 
			VALUES ('{step.Id}', '[]', '{step.PrevStepId}', '{step.PrevStepCode}', 'f', 2, 'f', NULL, 0,
			'f', NULL, 'f', NULL, '{workflow.Id}', 1, '[]', NULL,
			'{step.AcceptorId}', '{step.AcceptorName}', '{step.AcceptorOrgId}', '{step.AcceptorOrgName}', '{step.AcceptorOrgAreaCode}', '{step.AcceptorOrgAreaName}',
			{step.AcceptTime.ToString()}, '{step.HandlerId}', '{step.HandlerName}', '{step.HandlerOrgId}',
			'{step.HandlerOrgIsCenter.ToString()}', '{step.HandlerOrgName}', '{step.HandlerOrgAreaCode}', '{step.HandlerOrgAreaName}', {step.HandleTime.ToString()}, '{step.Name}', '', {(int)step.StepType},
			{(int)step.BusinessType}, 3, 'f', 0, 'f', '[]', 'f', 'f', '{step.Opinion}', {step.StepExpiredTime.ToString()},
			'f', '{step.CreationTime.ToString()}', '{step.CreatorId}', '{step.CreatorName}', '{step.CreatorOrgId}', '{step.CreatorOrgName}', {step.CreatorOrgLevel}, '{step.AreaId}', NULL, NULL, 
			'f', '{workflow.ModuleId}', '{workflow.ModuleName}', '{workflow.ModuleCode}', 0, '[]', '{workflow.ExternalId}',  '', '{step.CreatorId}', '{step.CreatorName}', '{step.CreatorOrgId}', '{step.CreatorOrgName}', '{step.AssignerOrgIsCenter.ToString()}', 0, 0,0);";

			stepSql = stepSql.Replace("'NULL'", "NULL");
			var traceSql = GetWorkflowTraceSql(item, step, org, user, start, end, ref workflow);
			return stepSql + traceSql;
		}

		/// <summary>
		/// 生成快照
		/// </summary>
		/// <param name="item"></param>
		/// <param name="step"></param>
		/// <param name="org"></param>
		/// <param name="user"></param>
		/// <param name="start"></param>
		/// <param name="end"></param>
		/// <param name="workflow"></param>
		/// <returns></returns>
		private string GetWorkflowTraceSql(DataRow item, Entity.WorkflowStep step , DataTable org, DataTable user, bool start, bool end, ref Entity.Workflow workflow)
		{
			var trace = new Entity.WorkflowTrace();
			trace.InitId();
			var traceSql = $@"INSERT INTO ""public"".""workflow_trace"" (""Id"", ""StepId"", ""Status"", ""ParentId"", ""WorkflowId"", ""FlowAssignType"", ""Handlers"", ""PrevChosenStepCode"", ""AcceptorId"", ""AcceptorName"", 
			""AcceptorOrgId"", ""AcceptorOrgName"", ""AcceptorOrgAreaCode"", ""AcceptorOrgAreaName"", ""AcceptTime"", ""HandlerId"", ""HandlerName"", ""HandlerOrgId"", 
			""HandlerOrgIsCenter"", ""HandlerOrgName"",""HandlerOrgAreaCode"", ""HandlerOrgAreaName"", ""HandleTime"", ""Name"", ""Code"", ""StepType"", ""BusinessType"",
			""HandlerType"", ""CanReject"", ""ExecuteMode"", ""CanStartCountersign"",  ""NextHandlers"",  ""BackToCountersignEnd"", ""IsSms"", ""Opinion"", ""StepExpiredTime"", 
			""IsStartCountersign"", ""CreationTime"", ""CreatorId"", ""CreatorName"", ""CreatorOrgId"", ""CreatorOrgName"", ""CreatorOrgLevel"", ""AreaId"", ""FlowDirection"", ""FileJson"",
			""IsActualHandled"", ""ModuleId"", ""ModuleName"", ""ModuleCode"", ""ExpiredStatus"", ""ExternalId"", ""TraceType"", ""NextSteps"", ""PrevStepId"", ""PrevStepCode"", ""IsMain"", ""IsOrigin"", ""CountersignId"",
			""CountersignPosition"", ""CountersignSteps"", ""StartCountersignId"", ""IsStartedCountersignEnd"", ""IsCountersignEndStep"", ""CountersignStartStepId"", ""SendHandleTimes"", ""RoleId"", ""RoleName"", ""TraceState"", 
			""Remark"", ""TraceStyle"", ""OrderPublishId"", ""OrderVisitId"", ""PrevStepName"", ""AssignerId"", ""AssignerName"", ""AssignerOrgId"", ""AssignerOrgName"", ""AssignerOrgIsCenter"", ""HandleMode"", ""ReviewResult"",""InstanceMode"") 
			VALUES ('{trace.Id}', '{step.Id}', 2, NULL, '{workflow.Id}', 1, '[]', NULL, '{step.AcceptorId}', '{step.AcceptorName}',
			'{step.AcceptorOrgId}', '{step.AcceptorOrgName}', '{step.AcceptorOrgAreaCode}', '{step.AcceptorOrgAreaName}', {step.AcceptTime.ToString()}, '{step.HandlerId}', '{step.HandlerName}', '{step.HandlerOrgId}',
			'{step.HandlerOrgIsCenter.ToString()}', '{step.HandlerOrgName}', '{step.HandlerOrgAreaCode}', '{step.HandlerOrgAreaName}', {step.HandleTime.ToString()}, '{step.Name}', '', {(int)step.StepType}, {(int)step.BusinessType},
			3, 'f', 0, 'f',  '[]',  'f', 'f', '{step.Opinion}', {step.StepExpiredTime.ToString()},
			'f', '{step.CreationTime.ToString()}', '{step.CreatorId}', '{step.CreatorName}', '{step.CreatorOrgId}', '{step.CreatorOrgName}', {step.CreatorOrgLevel}, '{step.AreaId}', NULL, '[]',
			'f', '{workflow.ModuleId}', '{workflow.ModuleName}', '{workflow.ModuleCode}', 0, '{workflow.ExternalId}', 1, '[]', '{step.PrevStepId}', NULL, 'f', 'f', '',
			0, '[]', NULL, 'f', 'f', '', 0, NULL, NULL, 0,
			NULL, 0, NULL, NULL, NULL, '{step.CreatorId}', '{step.CreatorName}', '{step.CreatorOrgId}', '{step.CreatorOrgName}', '{step.AssignerOrgIsCenter.ToString()}', NULL, 0,0);";

			traceSql = traceSql.Replace("'NULL'", "NULL");
			return traceSql;
		}
	}
}