aya子-m​​ssql所以在这里’我们面临的挑战:

我们的支持系统使用Rackspace Cloud和mySQL数据库在Kayako eSupport上运行

我们的主系统(我们的应用程序或管理区域–一切!)在我们自己的MSSQL服务器上运行。

随着业务的增长,支持票的数量也在增长,支持团队的规模也在增长。支持经理需要更好地了解什么’在团队的支持和个人成员的帮助下继续前进。

我们内部没有mySQL / PHP技能,因此我们确实需要MSSQL数据库中的这些数据才能针对它编写报告。

这里’是我们如何实现的。

添加链接服务器

首先,我们安装了 MySQL ODBC驱动程序 在我们的MSSQL数据库服务器上
添加然后添加mySQL数据库 作为MSSQL中的链接服务器 (我们称为链接服务器‘kfsupport’)

建立表格

我们想要的数据在mySQL数据库中‘swstaaff’, ‘swtickets’ and ‘swticketposts’
因此,我们需要在MSSQL中创建表来保存此数据


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[st_staff](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[staffid] [int] NULL,
	[fullname] [nvarchar](250) NULL,
	[username] [nvarchar](150) NULL,
	[lastvisit] [bigint] NULL,
	[lastactivity] [bigint] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[st_ticketposts](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[ticketpostid] [int] NULL,
	[ticketid] [int] NULL,
	[dateline] [bigint] NULL,
	[fullname] [nvarchar](250) NULL,
	[creator] [int] NULL,
	[staffid] [int] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[st_tickets](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[ticketid] [nchar](10) NULL,
	[ticketmaskid] [nvarchar](50) NULL,
	[ticketstatusid] [int] NULL,
	[ownerstaffid] [int] NULL,
	[assignstatus] [int] NULL,
	[fullname] [nvarchar](250) NULL,
	[lastreplier] [nvarchar](250) NULL,
	[subject] [nvarchar](350) NULL,
	[dateline] [bigint] NULL,
	[lastactivity] [bigint] NULL,
	[laststaffreplytime] [bigint] NULL
) ON [PRIMARY]

GO

日期格式

和大多数一样 东西,mySQL中数据的日期格式是UNIXTIME(自1970年1月1日以来的秒数)。而我们更喜欢使用ISO格式(例如20110129或20110229161700)

我们将需要几个函数来处理从UNIXTIME到ISO格式的转换


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetISODateLong]
(
	@date datetime
)
RETURNS bigint
AS
BEGIN
DECLARE @ISOdate varchar(8)
DECLARE @ISOTime varchar(8)
DECLARE @ISOTimeStamp bigint

SET @ISOdate = convert(varchar(8), @date, 112)
SET @ISOTime = replace(convert(varchar(8), @date, 108),':','')

SET @ISOTimeStamp = @ISOdate + '' + @ISOTime

RETURN @ISOTimeStamp

END

GO

CREATE FUNCTION [dbo].[f_UnixTimeToISO_Long]
(
	@unixtime bigint
)
RETURNS bigint
AS
BEGIN
	RETURN dbo.GetISODateLong(dateadd(ss,@unixtime,'1970-01-01'))

END

GO

跨数据复制

以便’s everything set up –我们有一个与mySQL数据库的连接,并且我们有用于存储数据的本地表。现在,一个存储过程可以跨数据复制


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[p_Support_RefreshData]
	AS
BEGIN
	set nocount on

	--staff
	TRUNCATE table st_staff

	INSERT INTO st_staff
	(staffid, fullname, username, lastvisit, lastactivity)
	SELECT
		staffid, fullname, username,
		dbo.f_UnixTimeToISO_Long(lastvisit),
		dbo.f_UnixTimeToISO_Long(lastactivity)
	FROM
	openquery (kfsupport, 'select staffid, fullname, username,
 lastvisit, lastactivity FROM swstaff')

	--ticket headers
	TRUNCATE table st_tickets

	INSERT INTO st_tickets
	( [ticketid]
      ,[ticketmaskid]
      ,[ticketstatusid]
      ,[ownerstaffid]
      ,[assignstatus]
      ,[fullname]
      ,[lastreplier]
      ,[subject]
      ,[dateline]
      ,[lastactivity]
      ,[laststaffreplytime])
	SELECT [ticketid]
      ,[ticketmaskid]
      ,[ticketstatusid]
      ,[ownerstaffid]
      ,[assignstatus]
      ,[fullname]
      ,[lastreplier]
      ,[subject]
      ,dbo.f_UnixTimeToISO_Long(dateline)
      ,dbo.f_UnixTimeToISO_Long(lastactivity)
      ,dbo.f_UnixTimeToISO_Long(laststaffreplytime) FROM
	openquery (kfsupport, 'select  ticketid,ticketmaskid,ticketstatusid,ownerstaffid,assignstatus,
fullname,lastreplier,subject,dateline,lastactivity,
laststaffreplytime FROM ti order by ticketid desc LIMIT 5000')

	TRUNCATE TABLE st_ticketposts

	INSERT INTO st_ticketposts
	(ticketpostid,ticketid,dateline,fullname,creator,staffid)
	SELECT [ticketpostid]
      ,[ticketid]
      ,dbo.f_UnixTimeToISO_Long(dateline)
      ,[fullname]
      ,[creator]
      ,[staffid] FROM
	openquery (kfsupport, 'select ticketpostid,ticketid,dateline,fullname,creator
,staffid FROM 哨所 ORDER BY ticketid DESC limit 25000')

	--update t_sys with current timestamo
	UPDATE t_sys SET supportdata = dbo.GetISOTimeStamp()
END

GO

就在最后,我们用当前日期和时间更新t_sys,以便支持管理器知道上次导入数据的时间,并可以在需要时进行更新。

还请注意我们’重新限制了我们获取的最近5,000张门票和最近25,000个帖子的数据量。那里’没有必要复制所有数据,因为我们’不太可能想报告那张旧票。

刷新数据仅需不到6秒的时间。

因此,我们现在拥有MSSQL中的所有最新支持数据,并且可以开始编写有关它的报告。

I’不知道这是否对其他任何人有用–我希望是。如果您确实使用过它,那么请在下面的评论中告诉我,然后我还可以添加我们用于生成报告的代码。

See how 卡什流works with your business and your books