The basic process involves creating specific views in the Links Database for each of the reports that are uploaded to Swimdesk.
The creation of these views will require admin access to the Links database.
In the below script I have highlighted the areas that need to be configured for each school\location that will be uploading to swimdesk. Modify the View Name as appropriate, this will be used later when automating the SQL extraction.
The database should be backed up prior to executing the SQL commands and if possible should be tested against the LinksTraining database before running against live.
/****** CREATE VIEW: Search and replace [LMS_STU_SCHOOL] Script Date: 8/03/2016 3:46:13 p.m. ******/
USE [LinksDataBase] --Set this to the appropriate database name
GO
/****** Object: View [dbo].[ LMS_STU_SCHOOL] Script Date: 8/03/2016 3:46:13 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[LMS_STU_SCHOOL]
AS
SELECT TOP (100) PERCENT StuSurname, StuGivenNames, StudId, LessonDay, LessonTime, LessonLevel, Area, TeachSurname, TeachGivenNames, StuBookStartDate,
RPSurname, RPGivenNames, RPId, RPAddress, RPSuburb, RPPostCode, RPHomePhone, RPWorkPhone, RPMobilePhone AS RPPhone, RPEmail,
RPBookingBalance AS 'RPBookingBalance.$', RPBookingFee AS 'RPBookingFee.$', FamilyPaymentType, SpecialDiscount, RPState AS RP, StuGender,
StuDateOfBirth, StuAge
FROM dbo.LNK_CLS_StudBookDetails(CONVERT(VARCHAR(8), GETDATE(), 112), '', '', 0, 1, '38,39,40,41,42,43', 0, 0, '', '', 0) AS LNK_CLS_StudBookDetails_1
--These are the class ID’s from ClassTypes table -For this example all class types for the Albany School have been used – See below screenshot:
ORDER BY StuSurname + ',' + StuGivenNames
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "LNK_CLS_StudBookDetails_1"
Begin Extent =
Top = 6
Left = 38
Bottom = 114
Right = 214
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'LMS_STU_SCHOOL’
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'LMS_STU_SCHOOL’
GO
Once you have the view modified save it as a .sql file and execute against the Links Database. You should now be able to see the created view in SSMS. In the below example there are 5 schools with the corresponding views called LMSNZ_STUschoolname.
You now need to create a cmd file to extract the data from the view and upload to the swimdesk ftp site. The cmd contains 2 parts.
1:The sql extraction. This is comprised of a single line for each of the views created, in this example I have 5 views and have located the batch files in a directory called D:\swimdeskexports and am outputting the results to a subfolder called CSV.
2:The ftp command. The ftp section uses a reference to an ftp command file, in the following example this is called “d.ftp” and is located in the same directory as the cmd file.
IP address, Username, and Password will be supplied by SwimDesk.
ExtractUpload.bat
sqlcmd -S LinksDataBaseServer -d LinksDatabase -U SQLUserName -P SQLPassword -Q "SELECT * FROM ViewNameCreatedBefore" -o "D:\SwimDeskExports\CSV\FILENAME.csv" -s"|" -W
sqlcmd -S LinksDataBaseServer -d LinksDatabase -U SQLUserName -P SQLPassword -Q "SELECT * FROM SecondViewName" -o " D:\SwimDeskExports\CSV\FILENAME2.csv " -s"|" -W
sqlcmd -S LinksDataBaseServer -d LinksDatabase -U SQLUserName -P SQLPassword -Q "SELECT * FROM ThirdViewName" -o " D:\SwimDeskExports\CSV\FILENAME3.csv " -s"|" -W
sqlcmd -S LinksDataBaseServer -d LinksDatabase -U SQLUserName -P SQLPassword -Q "SELECT * FROM ForthViewName" -o " D:\SwimDeskExports\CSV\FILENAME4.csv " -s"|" -W
sqlcmd -S LinksDataBaseServer -d LinksDatabase -U SQLUserName -P SQLPassword -Q "SELECT * FROM FifthViewName" -o " D:\SwimDeskExports\CSV\FILENAME5.csv " -s"|" -W
ftp -i -s:d.ftp
d.ftp
open 54.153.133.216
ftpUserName
ftpPassword
lcd CSV
mput *.csv
disconnect
quit
Once the files have been created they can be tested by manually executing the ExtractUpload.bat file and confirming csv files are written to the D:\SwimDeskExports\CSV folder. Ftp upload can be confirmed by manually connecting to the ftp site using an appropriate FTP browser or in conjuction with SwimDesk Support.
Following confirmation that the process is working correctly you should now create a scheduled task to automate the process at a schedule that is appropriate.
