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("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.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 = (Enum.EGender)sex; order.IdentityType = (Enum.EIdentityType)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 = DateTime.Parse(row["FSH_AddDate"].ToString()); order.ExpiredTime = DateTime.Parse(row["FSH_TTime"].ToString()); order.FiledTime = DateTime.Parse(row["FSH_DisposeDate"].ToString()); order.ExpiredStatus = (Enum.EExpiredStatus)ExpiredStatus; order.AreaCode = row["SA_AreaCode"].ToString(); order.Street = row["FLM_LKAddress"].ToString(); order.IsDeleted = false; order.CreationTime = DateTime.Parse(row["FSH_AddDate"].ToString()); order.SmsSended = false; order.ProcessType = row["FSH_AcceptBMID"].ToString() == CommonTool.CenterId ? Enum.EProcessType.Zhiban : Enum.EProcessType.Jiaoban; order.IsPublicity = row["FSH_IsPublic"].ToString() == "1"; //0：不公开 ；1：公开 12345在线传递过来的状态 #endregion //""WorkflowId"", ""ExpiredTimeConfigId"", ""Longitude"", ""Latitude"", 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"", ""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 ( '{order.Id}', '{order.CallId}', '{order.FromPhone}', '{order.TransferPhone}', '{order.FromName}', {(int)order.FromGender}, {(int)order.IdentityType}, '{order.Contact}', '{order.ContactMask}', '{order.AcceptSms.ToString()}', '{order.No}', '{order.AcceptType}', '{order.Title}', '{order.HotspotId}', '{order.HotspotName}', '{order.HotspotSpliceName}', '{order.PushTypeCode}', '{order.Contact}', {(int)order.Status}, '{order.StartTime}', '{order.ExpiredTime}', '{order.FiledTime}', {(int)order.ExpiredStatus}, '{order.AreaCode}', '{order.Street}', '{order.IsDeleted.ToString()}', '{order.CreationTime}', '{order.SmsSended.ToString()}', {(int)order.ProcessType}, '{order.IsPublicity.ToString()}', '{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; } } }