在MSSQL中的Kayako Esupport报告

所以在这里’我们面临的挑战：

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

我们的主要系统（我们的申请或管理领域–一切！）在我们自己的MSSQL服务器上运行。

由于业务正在增长，支持票的数量也在增长，因此支持团队的规模也是如此。支持管理器需要更好地概述什么’s继续支持和团队的个人成员。

我们没有在内部没有MySQL / PHP技能，所以我们真的需要在我们的MSSQL数据库中进行此数据，以便能够对其进行编写报告。

这里’我们如何实现它。

添加链接服务器

首先我们安装了 MySQL ODBC驾驶员 在我们的MSSQL Databse服务器上
添加然后添加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（自1/1/1970以来的秒数）。虽然我们宁愿使用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 swtickets 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 swticketposts 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’不知道这是否会对别人使用–我希望它是。如果您确实使用它，请在下面的评论中告诉我，我还可以添加我们使用的代码来生成报表。