【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】v7.5.1
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
tidb是否支持powerbi呢 ，现在保存数据源的时候报错Connection must be valid and open.
详细信息
Feedback Type:
Frown (Error)
Error Message:
Connection must be valid and open.
Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
在 Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
在 Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
在 Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
在 Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
在 Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
在 Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
在 Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunkAndCheckIfClosed()
在 Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunk()
在 Microsoft.Mashup.Common.ChunkedInputStream.ReadByte()
在 System.IO.BinaryReader.FillBuffer(Int32 numBytes)
在 Microsoft.Mashup.Evaluator.RemotePageReader.PageReader.NextResult()
在 Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.NextResult()
在 Microsoft.Mashup.Evaluator.SoftCancellingDocumentEvaluator.DataReaderSourceEvaluation.SoftCancellingDataReaderSource.SoftCancellingPageReader.NextResult()
在 Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.GetNextReader()
在 Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.Read(IPage page)
在 Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.Read(IPage page)
在 Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.Read(IPage page)
在 Microsoft.OleDb.Serialization.PageReaderRowset.ReadNextPage()
在 Microsoft.OleDb.Serialization.PageReaderRowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hchapter, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
在 Microsoft.OleDb.Rowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hReserved, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
在 Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hReserved, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
Stack Trace Message:
Connection must be valid and open.
Invocation Stack Trace:
在 Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
在 Microsoft.Mashup.Client.UI.Shared.StackTraceInfo…ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
在 Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
在 Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
在 Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.b__0()
在 Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_0.b__0(Object null)
在 System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
在 System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
在 System.Delegate.DynamicInvokeImpl(Object args)
在 System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
在 System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
在 System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
在 System.Windows.Forms.Control.InvokeMarshaledCallbacks()
在 System.Windows.Forms.Control.WndProc(Message& m)
在 System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
在 System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
在 System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
在 System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
在 System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
在 Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
在 Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction) 在 Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryUIService queryServices, IDesktopModelingHost modelingHost, LocalizedString title, LoadToModelContext loadToModelContext) 在 Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass16_0.<TryShowDialogForQueries>b__0() 在 Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass68_01.b__0()
在 Microsoft.PowerBI.Client.Windows.Report.d__691.MoveNext() 在 System.Runtime.CompilerServices.AsyncTaskMethodBuilder1.Start[TStateMachine](TStateMachine& stateMachine)
在 Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func1 getTask) 在 Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func1 action)
在 Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.DataImportCommands.RefreshAllQueries(IPowerBIWindowService windowService, IExceptionHandler exceptionHandler, RefreshAllQueriesParameters parameters)
在 Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
在 Microsoft.Practices.Prism.Commands.DelegateCommandBase.<>c__DisplayClass4_0.<.ctor>b__0(Object arg)
在 Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(ICommand command, Object parameter) 在 Microsoft.Practices.Prism.Commands.CompositeCommand.Execute(Object parameter) 在 Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(T parameter)
在 Microsoft.PowerBI.Client.Windows.CommandStoreHostService.d__14.MoveNext()
在 System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
在 Microsoft.PowerBI.Client.Windows.CommandStoreHostService.ExecuteCore(String commandId, String parameterJson, Int64 promiseHandle)
在 Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.d__1.MoveNext()
在 System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
在 Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func1 asyncFunc) 在 Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext() 在 System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine) 在 Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func1 asyncFunc)
在 Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
在 System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
在 System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
在 System.Delegate.DynamicInvokeImpl(Object args)
在 System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
在 System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
在 System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
在 System.Windows.Forms.Control.InvokeMarshaledCallbacks()
在 System.Windows.Forms.Control.WndProc(Message& m)
在 System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
在 System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
在 System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
在 System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
在 System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
在 Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
在 Microsoft.PowerBI.Client.Program.<>c__DisplayClass6_0.b__0()
在 Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.b__0()
在 Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
在 Microsoft.PowerBI.Client.Program.RunApplication(String args)
在 Microsoft.PowerBI.Client.Program.Main(String args)
PowerBINonFatalError:
{“AppName”:“PBIDesktop”,“AppVersion”:“2.117.984.0”,“ModuleName”:“”,“Component”:“”,“Error”:“Microsoft.Mashup.Host.Document.SerializedException - Microsoft.Mashup.Evaluator.Interface.ErrorException”,“MethodDef”:“”,“ErrorOffset”:“”}
Snapshot Trace Logs:
C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop SSRS\FrownSnapShot4ad26fae-ef38-4726-98db-6e9b114be36f.zip
Model Default Mode:
Import
Model Version:
PowerBI_V3
Performance Trace Logs:
C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop SSRS\PerformanceTraces.zip
Enabled Preview Features:
PBI_scorecardVisual
PBI_NlToDax
PBI_horizontalFusion
PBI_setLabelOnExportPdf
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_sparklines
PBI_fieldParametersSuperSwitch
PBI_angularRls
PBI_onObject
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
PowerBINonFatalError_ErrorDescription:
Microsoft.Mashup.Evaluator.Interface.ErrorException
PowerBIUserFeedbackServices_IsReported:
True
Formulas:
section Section1;
shared 工单数据报表 = let
源 = MySQL.Database(“10.10.10.53:4000”, “dscmall”, [ReturnSingleDatabase=true, Query=“select a.#(lf),ifnull(aur.user_name, a.客户姓名_2) ‘客户姓名’, ifnull(aur.mobile_phone, a.客户手机号_2) ‘客户手机号’#(lf)from #(lf)(#(lf)-- explain#(lf)SELECT#(lf)dwos.id,#(lf)dwos.serial_number ‘工单流水号’,#(lf)max(dwode.order_sn) ‘工单编号’,#(lf)dwody.dict_name ‘所属项目’,#(tab)#(lf)dwody2.dict_name ‘服务类型’,#(tab)#(lf)dwody3.dict_name ‘工单类型’,#(tab)#(lf)dwody4.dict_name ‘工单子类型’,#(lf)case dwos.status#(lf)when 1 then ‘待转派’#(lf)when 2 then ‘已转派’#(lf)when 3 then ‘其他’#(lf)when 4 then ‘退单申请中’#(lf)when 5 then ‘其他’#(lf)when 6 then ‘已撤单-站端’#(lf)when 7 then ‘已分派’#(lf)when 8 then ‘已接单’#(lf)when 9 then ‘其他’#(lf)when 10 then ‘已联系-预约’#(lf)when 11 then ‘已联系-出发’#(lf)when 12 then ‘已联系-改约’#(lf)when 13 then ‘其他’#(lf)when 14 then ‘已撤单-工程师’#(lf)when 15 then ‘上门中’#(lf)when 16 then ‘其他’#(lf)when 17 then ‘服务完成’#(lf)when 18 then ‘关单’#(lf)when 19 then ‘已退单’#(lf)when 20 then ‘拒绝结算’#(lf)when 21 then ‘同意结算’#(lf)when 22 then ‘客户到场’#(lf)when 23 then ‘到店服务中’#(lf)when 24 then ‘抢单中’#(lf)when 25 then ‘远程服务中’#(lf)when 26 then ‘已锁定’#(lf)when 27 then ‘已核准’#(lf)when 28 then ‘待用户预约’#(lf)when 29 then ‘待妥投’#(lf)when 30 then ‘待分配’#(lf)when 31 then ‘SF服务中’#(lf)when 32 then ‘用户取消’#(lf)when 33 then ‘工单终止’#(lf)else ‘其他’#(lf)end ‘工单状态’,#(lf)case dwos.status#(lf)when 1 then ‘活动工单’#(lf)when 2 then ‘活动工单’#(lf)when 3 then ‘其他’#(lf)when 4 then ‘退回工单’#(lf)when 5 then ‘其他’#(lf)when 6 then ‘历史工单’#(lf)when 7 then ‘活动工单’#(lf)when 8 then ‘活动工单’#(lf)when 9 then ‘其他’#(lf)when 10 then ‘活动工单’#(lf)when 11 then ‘活动工单’#(lf)when 12 then ‘活动工单’#(lf)when 13 then ‘其他’#(lf)when 14 then ‘已撤单’#(lf)when 15 then ‘活动工单’#(lf)when 16 then ‘其他’#(lf)when 17 then ‘服务完成’#(lf)when 18 then ‘历史工单’#(lf)when 19 then ‘历史工单’#(lf)when 20 then ‘历史工单’#(lf)when 21 then ‘历史工单’#(lf)when 22 then ‘客户到场’#(lf)when 23 then ‘活动工单’#(lf)when 24 then ‘活动工单’#(lf)else ‘其他’#(lf)end ‘工单状态-大类’,#(lf)case dwos.order_to #(lf)when ‘xian-niu’ then ‘贤牛’#(lf)when ‘soc’ then ‘SOC’#(lf)when ‘byo’ then ‘BYO’#(lf)when ‘sf’ then ‘sf’#(lf)when ‘auto’ then ‘自动派单’#(lf)else ‘其他’#(lf)end ‘派单渠道’,#(lf)dwos.third_party_id,#(lf)-- dus.user_name ‘客户姓名’,#(lf)-- maur.name ‘客户姓名’,#(lf)-- dus.mobile_phone ‘客户手机号’,#(lf)-- maur.telephone ‘客户手机号’,#(lf)dler.real_name ‘工程师姓名’,#(lf)dwos.create_user_name ‘开单人’,#(lf)if(dwos.create_user_name = ‘系统’, ‘自动指派’, #(lf)case dwos.assigned_way #(lf)when 1 then ‘自动指派’#(lf)when 2 then ‘手动指派’#(lf)else if(dwos.create_user_name = ‘系统’, ‘自动指派’, ‘’)#(lf)end) ‘开单方式’,#(lf)dmsi2.rz_shopName ‘工程师所属服务商名称’,#(lf)case dmsi2.shop_type#(lf) when 1 then ‘服务商’#(lf) when 2 then ‘服务站’#(lf) when 3 then ‘供应商|国代’#(lf) when 4 then ‘SaaS商’#(lf) when 5 then ‘个人供应商-省代’#(lf) when 6 then ‘个人服务商’#(lf) when 7 then ‘个人供应商-普通’#(lf) when 8 then ‘省代’#(lf) when 9 then ‘供应商（营通社类）’#(lf) when 10 then ‘自营’#(lf) when 11 then ‘T2/市代’#(lf) when 13 then ‘智商务-商’#(lf) when 14 then ‘智商务-站’#(lf) else ‘其他’#(lf) end ‘服务商类型’,#(lf)died.station_name ‘工程师所属服务站名称’,#(lf)case dmsi.shop_type#(lf) when 1 then ‘服务商’#(lf) when 2 then ‘服务站’#(lf) when 3 then ‘供应商|国代’#(lf) when 4 then ‘SaaS商’#(lf) when 5 then ‘个人供应商-省代’#(lf) when 6 then ‘个人服务商’#(lf) when 7 then ‘个人供应商-普通’#(lf) when 8 then ‘省代’#(lf) when 9 then ‘供应商（营通社类）’#(lf) when 10 then ‘自营’#(lf) when 11 then ‘T2/市代’#(lf) when 13 then ‘智商务-商’#(lf) when 14 then ‘智商务-站’#(lf) else ‘其他’#(lf) end ‘服务站类型’,#(lf)died.new_station_code ‘工程师所属服务站编号’,#(lf)(IFNULL((select dr.region_name from dsc_region dr where dr.region_id = died.station_city_id),‘’)) ‘服务站所在城市’,#(lf)dwos.price / 100 ‘工单价格’,#(lf)dwos.jie_suan_price / 100 ‘结算金额’,#(lf)(select max(created_at) from dsc_work_orders_flow where operate_name = ‘同意结算’ and work_order_id = dwos.id) ‘结算时间’,#(lf)dwos.created_at ‘开单时间’,#(lf)dwos.closed_at ‘关单时间’,#(lf)dwos.updated_at ‘更新时间’,#(lf)case dwos.order_receive_type #(lf)when 1 then ‘指派到站’#(lf)when 2 then ‘指派到人’#(lf)when 3 then ‘自动分派’#(lf)when 4 then ‘抢单’#(lf)else ‘其他’#(lf)end ‘接单属性’,#(lf)dwos.urgency ‘紧急程度’,#(lf)dwos.response_level ‘响应级别’,#(lf)dwos.fault_type ‘故障分类’,#(lf)dwos.contact_user ‘联系人’,#(lf)dwos.contact_mobile ‘联系电话’,#(lf)dwos.expected_time ‘期望上门时间’,#(lf)dwos.spare_part_info ‘备件信息’,#(lf)dwos.qq_qun ‘qq群’,#(lf)concat(IFNULL((select dr.region_name from dsc_region dr where dr.region_id = dwos.province_id),‘’),#(lf)#(tab)IFNULL((select dr.region_name from dsc_region dr where dr.region_id = dwos.city_id),‘’),#(lf)#(tab)IFNULL((select dr.region_name from dsc_region dr where dr.region_id = dwos.district_id),‘’),#(lf)#(tab)IFNULL((select dr.region_name from dsc_region dr where dr.region_id = dwos.street_id),‘’)) ‘客户地址’,#(lf)dwos.address ‘详细地址’,#(lf)dwos.fault_desc ‘故障描述’,#(lf)dwos.solution ‘解决方案’,#(lf)dwos.comment ‘备注’,#(lf)-- 客户信息#(lf)case dwou.gender #(lf)when 1 then ‘男’#(lf)when 2 then ‘女’#(lf)else ‘其他’#(lf)end ‘性别’,#(lf)dwou.customer_no ‘客户编号’,#(lf)dwou.fixed_line ‘固定电话’,#(lf)dwou.qq ‘QQ’,#(lf)dwou.wx ‘微信’,#(lf)-- doi.order_sn ‘订单号’,#(lf)dwos.bill_order_from,#(lf)case dwos.bill_order_from#(lf)when 0 then doi.order_sn#(lf)when 1 then doi.order_sn#(lf)when 2 then dwoh.order_sn#(lf)when 3 then ‘省CMCC的巡检工单’#(lf)when 4 then ‘省CMCC的后续工单’#(lf)when 5 then doi.order_sn#(lf)when 6 then dwoh.order_sn#(lf)when 7 then dwos.bill_order_no#(lf)when 8 then dwos.bill_order_no#(lf)when 9 then dwos.bill_order_no#(lf)else dwos.bill_order_no#(lf)end ‘订单号’,#(lf)case dwos.bill_order_from#(lf)when 1 then ‘联想百应平台’#(lf)when 2 then ‘CMCC移动’#(lf)when 3 then ‘省CMCC的巡检工单’#(lf)when 4 then ‘省CMCC的后续工单’#(lf)when 5 then ‘O2O’#(lf)when 6 then dwody.dict_name#(lf)when 7 then ‘SF项目工单’#(lf)when 8 then ‘美团点评’#(lf)when 9 then ‘京东服务自营’#(lf)when 10 then ‘联想商城’#(lf)when 11 then ‘联想商城企业购’#(lf)when 12 then ‘阿里自营’#(lf)when 13 then ‘天猫供销平台’#(lf)when 14 then ‘美团外卖’#(lf)when 15 then ‘京东pop店’#(lf)when 16 then ‘高德口碑’#(lf)when 17 then ‘京东联想百应旗舰店’#(lf)else ‘其他’#(lf)end ‘订单来源’,#(lf)‘所属公司’,#(lf)dwou.comment ‘客户信息备注’,#(lf)-- 设备信息#(lf)dwod.device_sn ‘设备序列号’,#(lf)dwod.contact_mobile ‘设备信息联络电话’,#(lf)dwod.manufacturer ‘所属厂商’,#(lf)dwod.device_name ‘设备名称’,#(lf)dwod.product ‘产品’,#(lf)dwod.device_no ‘设备编号’,#(lf)dwod.address_data ‘位置名称’,#(lf)dwod.device_model ‘设备型号’,#(lf)concat(IFNULL((select dr.region_name from dsc_region dr where dr.region_id = dwod.province_id),‘’),#(lf)#(tab)IFNULL((select dr.region_name from dsc_region dr where dr.region_id = dwod.city_id),‘’)) ‘所在省市’,#(lf)dwod.device_type ‘设备类型’,#(lf)dwod.contact_user ‘联系人(设备信息)’,#(lf)dwod.address ‘详细地址(设备信息)’,#(lf)dwocs.overall_service ‘整体服务情况分数’,#(lf)dwocs.engineer_attitude ‘工程师态度分数’,#(lf)case dwocs.is_resolve_question #(lf)when 1 then ‘是’#(lf)when 2 then ‘否’#(lf)else ‘其他’#(lf)end ‘问题是否解决’,#(lf)case dwocs.is_arrive_at_time #(lf)when 1 then ‘是’#(lf)when 2 then ‘否’#(lf)else ‘其他’#(lf)end ‘工程师是否按约到达’,#(lf)dwocs.station_position ‘店面位置好找吗’,#(lf)dwocs.overall_time ‘整体时效’,#(lf)dwocs.engineer_response_speed ‘工程师响应速度’,#(lf)dwocs.comment ‘用户的意见或建议’,#(lf)sub_a.第一次派单时间,#(lf)sub_a.派单是否及时,#(lf)sub_a.派单次数,#(lf)sub_b.预约是否及时,#(lf)sub_b.预约次数,#(lf)sub_c.服务时长,#(lf)sub_b.第一次预约时间,#(lf)sub_b.最后一次预约时间,#(lf)sub_b.最后一次协商预约时间,#(lf)sub_c.最后一次离场时间,#(lf)sub_d.上门是否及时,#(lf)sub_d.最后一次到场时间,#(lf)sub_e.撤单原因,#(lf)dwos.contact_user ‘客户姓名_2’,#(lf)dwos.contact_mobile ‘客户手机号_2’#(lf)#(lf)FROM#(lf)dsc_work_orders dwos#(lf)left join dsc_work_orders_distribute dwode on dwos.id = dwode.order_id#(lf)left join dsc_work_orders_dictionary dwody on dwos.project_id = dwody.id#(lf)left join dsc_work_orders_dictionary dwody2 on dwos.service_type_id = dwody2.id#(lf)left join dsc_work_orders_dictionary dwody3 on dwos.work_order_type_id = dwody3.id#(lf)left join dsc_work_orders_dictionary dwody4 on dwos.work_order_sub_type_id = dwody4.id#(lf)left join dsc_work_order_help dwoh on dwoh.id = dwos.bill_order_id#(lf)-- left join dsc_users dus on dwos.third_party_id = dus.third_party_id#(lf)-- left join paasmongodb.ana_user maur force index(idx_lenovoid) on dwos.third_party_id = maur.lenovoid#(lf)left join dsc_lenovo_engineer dler on dwos.engineer_id = dler.id#(lf)left join dsc_seller_shopinfo dss on dwos.station_id = dss.ru_id#(lf)left join dsc_merchants_shop_information dmsi on dss.information_id = dmsi.shop_id#(lf)left join dsc_merchants_shop_information dmsi2 on dmsi2.shop_id = dmsi.parent_id#(lf)left join dsc_information_extend died on dss.information_id = died.shop_id#(lf)left join dsc_work_orders_comments dwocs on dwos.id = dwocs.work_order_id#(lf)left join dsc_order_info doi on dwos.bill_order_id = doi.order_id#(lf)left join dsc_order_goods dogs on doi.order_id = dogs.order_id #(lf)left join dsc_work_orders_user dwou on dwos.third_party_id = dwou.third_party_id#(lf)left join dsc_work_orders_relation dwor on dwos.id = dwor.work_order_id #(lf)left join dsc_work_orders_devices dwod on dwor.relation_val = dwod.device_sn#(lf)left join (#(lf)select #(lf)dwos_a.id,#(lf)dwos_a.serial_number ‘工单流水号’,#(lf)dwode_a.order_sn ‘工单编号’,#(lf)dwos_a.created_at ‘开单时间’,#(lf)if(DATE_FORMAT(dwos_a.created_at,‘%Y-%m-%d %H:%i:%s’) > CONCAT(DATE_FORMAT(dwos_a.created_at, '%Y-%m-%d '), ‘17:00:00’), CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL -1 DAY), '%Y-%m-%d '), ‘10:00:00’), dwos_a.created_at) ‘开单时间补充’,#(lf)#(lf)if(dwos_a.order_to =‘xian-niu’,if(UNIX_TIMESTAMP(if(CONCAT(DATE_FORMAT(DATE_SUB(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at),INTERVAL 0 DAY), ‘%Y-%m-%d ‘), ‘17:00:00’) < DATE_FORMAT(min(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at)),’%Y-%m-%d %H:%i:%s’), CONCAT(DATE_FORMAT(DATE_SUB(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at),INTERVAL -1 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), if(DATE_FORMAT(min(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at)),’%Y-%m-%d %H:%i:%s’) < CONCAT(DATE_FORMAT(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at), '%Y-%m-%d '), ‘10:00:00’), CONCAT(DATE_FORMAT(DATE_SUB(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at),INTERVAL -0 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), (DATE_FORMAT(min(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at)),’%Y-%m-%d %H:%i:%s’))))) - UNIX_TIMESTAMP(min(dwof_a.created_at)) > -1800, ‘Y’, ‘N’) ,if(UNIX_TIMESTAMP(if(CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL 0 DAY), ‘%Y-%m-%d ‘), ‘17:00:00’) < DATE_FORMAT(min(dwos_a.created_at),’%Y-%m-%d %H:%i:%s’), CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL -1 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), if(DATE_FORMAT(min(dwos_a.created_at),’%Y-%m-%d %H:%i:%s’) < CONCAT(DATE_FORMAT(dwos_a.created_at, '%Y-%m-%d '), ‘10:00:00’),CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL -0 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), DATE_FORMAT(min(dwos_a.created_at),’%Y-%m-%d %H:%i:%s’)))) - UNIX_TIMESTAMP(min(dwof_a.created_at)) > -1800, ‘Y’, ‘N’)) ‘派单是否及时’,#(lf)#(lf)dwof_a.create_user_name ‘操作者’,#(lf)dwof_a.operate_name ‘操作’,#(lf)min(dwof_a.created_at) ‘第一次派单时间’,#(lf)count() ‘派单次数’#(lf)from #(lf)dsc_work_orders dwos_a#(lf)left join dsc_work_orders_distribute dwode_a on dwos_a.id = dwode_a.order_id#(lf)left join dsc_work_orders_flow dwof_a on dwos_a.id = dwof_a.work_order_id#(lf)left join (select #(lf)dwos_a.id,#(lf)dwos_a.serial_number ‘工单流水号’,#(lf)dwode_a.order_sn ‘工单编号’,#(lf)dwos_a.created_at ‘开单时间’,#(lf) dwof_a.created_at ‘贤牛开单时间’#(lf)from #(lf)dsc_work_orders dwos_a#(lf)left join dsc_work_orders_distribute dwode_a on dwos_a.id = dwode_a.order_id #(lf)left join dsc_work_orders_flow dwof_a on dwos_a.id = dwof_a.work_order_id#(lf)where #(lf)dwof_a.operate_name in (‘自动派单到贤牛’, ‘自动派单至贤牛’, ‘手工派单到贤牛’)#(lf)and dwos_a.deleted_at is null#(lf)and dwode_a.deleted_at is null#(lf)and dwof_a.deleted_at is null) sub_xianniupaidan on sub_xianniupaidan.id = dwos_a.id#(lf)where #(lf)dwof_a.operate_name = ‘派单’#(lf)-- and dwode_a.order_sn = ‘20220818229000073001’ #(lf)group by dwos_a.serial_number) sub_a on dwos.id = sub_a.id#(lf)left join (select #(lf)dwos_a.id,#(lf)dwos_a.serial_number ‘工单流水号’,#(lf)dwode_a.order_sn ‘工单编号’,#(lf)dwos_a.created_at ‘开单时间’,#(lf)if(DATE_FORMAT(dwos_a.created_at,‘%Y-%m-%d %H:%i:%s’) > CONCAT(DATE_FORMAT(dwos_a.created_at, '%Y-%m-%d '), ‘17:00:00’), CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL -1 DAY), '%Y-%m-%d '), ‘10:00:00’), dwos_a.created_at) ‘开单时间补充’,#(lf)#(lf)if(dwos_a.order_to =‘xian-niu’,if(UNIX_TIMESTAMP(if(CONCAT(DATE_FORMAT(DATE_SUB(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at),INTERVAL 0 DAY), ‘%Y-%m-%d ‘), ‘17:00:00’) < DATE_FORMAT(min(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at)),’%Y-%m-%d %H:%i:%s’), CONCAT(DATE_FORMAT(DATE_SUB(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at),INTERVAL -1 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), if(DATE_FORMAT(min(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at)),’%Y-%m-%d %H:%i:%s’) < CONCAT(DATE_FORMAT(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at), '%Y-%m-%d '), ‘10:00:00’), CONCAT(DATE_FORMAT(DATE_SUB(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at),INTERVAL -0 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), (DATE_FORMAT(min(ifnull(sub_xianniupaidan.贤牛开单时间,dwos_a.created_at)),’%Y-%m-%d %H:%i:%s’))))) - UNIX_TIMESTAMP(min(dwof_a.created_at)) > -3600, ‘Y’, ‘N’) ,if(UNIX_TIMESTAMP(if(CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL 0 DAY), ‘%Y-%m-%d ‘), ‘17:00:00’) < DATE_FORMAT(min(dwos_a.created_at),’%Y-%m-%d %H:%i:%s’), CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL -1 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), if(DATE_FORMAT(min(dwos_a.created_at),’%Y-%m-%d %H:%i:%s’) < CONCAT(DATE_FORMAT(dwos_a.created_at, '%Y-%m-%d ‘), ‘10:00:00’),CONCAT(DATE_FORMAT(DATE_SUB(dwos_a.created_at,INTERVAL -0 DAY), ‘%Y-%m-%d ‘), ‘10:00:00’), DATE_FORMAT(min(dwos_a.created_at),’%Y-%m-%d %H:%i:%s’)))) - UNIX_TIMESTAMP(min(dwof_a.created_at)) > -3600, ‘Y’, ‘N’)) ‘预约是否及时’,#(lf)dwof_a.create_user_name ‘操作者’,#(lf)dwof_a.operate_name ‘操作’,#(lf)min(dwof_a.created_at) ‘第一次预约时间’,#(lf)max(dwof_a.created_at) ‘最后一次预约时间’,#(lf)date_format(replace(replace(LEFT(JSON_EXTRACT(cast(dwof_a.extra_data as json),’$.”“extraDesc”“‘),INSTR(JSON_EXTRACT(cast(dwof_a.extra_data as json),’$.”“extraDesc”“‘),’\n’)),‘\’, ‘’),'”“预约时间：‘, ‘’), ‘%Y-%m-%d %H:%i:%s’) ‘最后一次协商预约时间’,#(lf)count(*) ‘预约次数’#(lf)from #(lf)dsc_work_orders dwos_a#(lf)left join dsc_work_orders_distribute dwode_a on dwos_a.id = dwode_a.order_id#(lf)left join dsc_work_orders_flow dwof_a on dwos_a.id = dwof_a.work_order_id#(lf)left join (select #(lf)dwos_a.id,#(lf)dwos_a.serial_number ‘工单流水号’,#(lf)dwode_a.order_sn ‘工单编号’,#(lf)dwos_a.created_at ‘开单时间’,#(lf) dwof_a.created_at ‘贤牛开单时间’#(lf)from #(lf)dsc_work_orders dwos_a#(lf)left join dsc_work_orders_distribute dwode_a on dwos_a.id = dwode_a.order_id #(lf)left join dsc_work_orders_flow dwof_a on dwos_a.id = dwof_a.work_order_id#(lf)where #(lf)dwof_a.operate_name in (‘自动派单到贤牛’, ‘自动派单至贤牛’, ‘手工派单到贤牛’)#(lf)and dwos_a.deleted_at is null#(lf)and dwode_a.deleted_at is null#(lf)and dwof_a.deleted_at is null) sub_xianniupaidan on sub_xianniupaidan.id = dwos_a.id#(lf)where #(lf)dwof_a.operate_name = ‘预约’#(lf)-- and dwode_a.order_sn = ‘20240322319003173001’ #(lf)group by dwos_a.serial_number) sub_b on dwos.id = sub_b.id#(lf)left join (select #(lf)dwos.id,#(lf)dwos.serial_number ‘工单流水号’,#(lf)dwode.order_sn ‘工单编号’,#(lf)dwos.created_at ‘开单时间’,#(lf)max(dwof.created_at) ‘最后一次离场时间’,#(lf)UNIX_TIMESTAMP(max(dwof.created_at)) - UNIX_TIMESTAMP(dwos.created_at) ‘服务时长’#(lf)from #(lf)dsc_work_orders dwos#(lf)left join dsc_work_orders_distribute dwode on dwos.id = dwode.order_id#(lf)left join dsc_work_orders_flow dwof on dwos.id = dwof.work_order_id#(lf)where #(lf)dwof.operate_name = ‘离场’#(lf)-- and dwode_a.order_sn = ‘20220823629000013002’ #(lf)group by dwos.serial_number) sub_c on dwos.id = sub_c.id#(lf)left join (select #(lf)dwos.id,#(lf)dwos.serial_number ‘工单流水号’,#(lf)dwode.order_sn ‘工单编号’,#(lf)dwos.created_at ‘开单时间’,#(lf)max(dwof.created_at) ‘最后一次到场时间’,#(lf)max(dwof2.created_at) ‘最后一次预约时间’,#(lf)date_format(replace(replace(LEFT(JSON_EXTRACT(cast(dwof2.extra_data as json),’$.”“extraDesc”“‘),INSTR(JSON_EXTRACT(cast(dwof2.extra_data as json),’$.”“extraDesc”“‘),’\n’)),‘\’, ‘’),'”“预约时间：‘, ‘’), ‘%Y-%m-%d %H:%i:%s’) ‘最后一次协商预约时间’,#(lf)case #(lf)when UNIX_TIMESTAMP(date_format(replace(replace(LEFT(JSON_EXTRACT(cast(dwof2.extra_data as json),’$.”“extraDesc”“‘),INSTR(JSON_EXTRACT(cast(dwof2.extra_data as json),’$.”“extraDesc”“‘),’\n’)),‘\’, ‘’),'”“预约时间：‘, ‘’), ‘%Y-%m-%d %H:%i:%s’)) - UNIX_TIMESTAMP(max(dwof.created_at))#(tab)< -1800 then ‘N’#(lf)when UNIX_TIMESTAMP(date_format(replace(replace(LEFT(JSON_EXTRACT(cast(dwof2.extra_data as json),’$.”“extraDesc”“‘),INSTR(JSON_EXTRACT(cast(dwof2.extra_data as json),’$.”“extraDesc”“‘),’\n’)),‘\’, ‘’),'”“预约时间：', ‘’), ‘%Y-%m-%d %H:%i:%s’)) - UNIX_TIMESTAMP(max(dwof.created_at))< 1800 then ‘Y’#(lf)else ‘N’#(lf)end ‘上门是否及时’#(lf)from #(lf)dsc_work_orders dwos#(lf)left join dsc_work_orders_distribute dwode on dwos.id = dwode.order_id#(lf)left join dsc_work_orders_flow dwof on dwos.id = dwof.work_order_id#(lf)left join dsc_work_orders_flow dwof2 on dwos.id = dwof2.work_order_id#(lf)where #(lf)dwof.operate_name = ‘到场’#(lf)and dwof2.operate_name = ‘预约’#(lf)-- and dwode_a.order_sn = ‘20220823629000013002’ #(lf)group by dwos.serial_number) sub_d on dwos.id = sub_d.id#(lf)left join (select #(lf)dwos.id,#(lf)dwos.serial_number ‘工单流水号’,#(lf)dwode.order_sn ‘工单编号’,#(lf)dwos.created_at ‘开单时间’,#(lf)dwof.desc ‘撤单原因’#(lf)from #(lf)dsc_work_orders dwos#(lf)left join dsc_work_orders_distribute dwode on dwos.id = dwode.order_id#(lf)left join dsc_work_orders_flow dwof on dwos.id = dwof.work_order_id#(lf)left join dsc_work_orders_flow dwof2 on dwos.id = dwof2.work_order_id#(lf)where #(lf)dwof.operate_name = ‘撤单’#(lf)and dwof2.operate_name = ‘撤单’#(lf)-- and dwode_a.order_sn = ‘20220823629000013002’ #(lf)group by dwos.serial_number) sub_e on dwos.id = sub_e.id#(lf)#(lf)where dwos.deleted_at is null#(lf)-- and dwode_a.order_sn = ‘20220818229000073001’#(lf)group by dwos.id#(lf)ORDER BY dwos.created_at desc#(lf)) a#(lf)-- join paasmongodb.ana_user aur on a.third_party_id = aur.lenovoid#(lf)left join dsc_users aur on a.third_party_id = aur.third_party_id and aur.third_party_id != 0 #(lf)-- where #(lf)-- a.工单编号 = ‘20240407729002443001’#(lf)-- a.工单流水号 = ‘20240220S091’#(lf)-- 处理转派产生多条#(lf)#(lf)ORDER BY a.工单编号 desc#(lf)#(lf)”])
in
源;
