Feconnect — Groups — Own Software Writers Group

here is my first stab at creating a windows form to pick the collection year / collection number / sequence number etc to generate xml file

we only use this for olass provision so some entities aren't included

tested this through the latest LIS and works a treat… probably of no use to anyone, and needs tidying up, but thought i would share nonetheless!

——————————-

Imports System.Windows.Forms
Imports System.Xml
Imports System.Xml.Linq
Imports System.Text
Imports System.Linq
Imports System.ComponentModel
Imports System.Xml.Schema

Partial Public Class GenerateXMLExport

Private export As ExportDataContext
Private learning As LearningDataContext
Private errors As Boolean = False

#Region " Page Events "

Public Sub New()

InitializeComponent()

export = New ExportDataContext
learning = New LearningDataContext

LoadCollectionYears()

End Sub

Private Sub cboCollectionYear_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCollectionYear.SelectedValueChanged

With cboCollectionYear
If .SelectedValue > 0 Then LoadCollections(.SelectedValue)
End With

End Sub

Private Sub cboCollection_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCollection.SelectedValueChanged

With cboCollection
If .SelectedValue > 0 Then LoadCollectionTypes(.SelectedValue)
End With

End Sub

Private Sub cboCollectionType_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCollectionType.SelectedValueChanged

If cboCollectionType.SelectedValue > 0 Then btnSave.Enabled = True

End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

Me.Close()

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

ProcessXML()

End Sub

#End Region

#Region " Populate Combo Boxes "

Private Sub LoadCollectionYears()

Dim ds As BindingList(Of CollectionYear) = QueryCollectionYears()

cboCollectionYear.DataSource = ds

lblTransmissionNumber.DataBindings.Add("Text", ds, "CurrentTransmissionNumber")

End Sub

Private Sub LoadCollections(ByVal selectedYear As Integer)

Dim ds As BindingList(Of Collection) = QueryCollections(selectedYear)

cboCollection.DataSource = ds

End Sub

Private Sub LoadCollectionTypes(ByVal selectedCollection As Integer)

Dim ds As BindingList(Of CollectionType) = QueryCollectionTypes(selectedCollection)

cboCollectionType.DataSource = ds

End Sub

#End Region

#Region " Queries "

Private Function QueryCollectionYears() As BindingList(Of CollectionYear)

Dim query = From cy In export.CollectionYears
Where (cy.Enabled = True)
Order By cy.DateFrom Descending
Select cy

Return New BindingList(Of CollectionYear)(query.ToList)

End Function

Private Function QueryCollections(ByVal selectedYear As Integer) As BindingList(Of Collection)

Dim query = From c In
(From c In export.Collections
Where c.Enabled = True And c.COLLECTIONYEARFK = selectedYear
Order By c.OpenDate Ascending
Select c).ToList
Select New Collection With {.COLLECTIONPK = c.COLLECTIONPK,
.CollectionName = c.CollectionName + " – " + c.OpenDate.ToString("dd/MM/yyyy") + " to " + c.CloseDate.ToString("dd/MM/yyyy"),
.OpenDate = c.OpenDate,
.CloseDate = c.CloseDate}

Return New BindingList(Of Collection)(query.ToList)

End Function

Private Function QueryCollectionTypes(ByVal selectedCollection As Integer) As BindingList(Of CollectionType)

Dim query = From ct In export.CollectionTypes
Join c_ct In export.Collection_CollectionTypes On c_ct.COLLECTIONTYPEFK Equals ct.COLLECTIONTYPEPK
Where ct.Enabled = True And c_ct.Enabled = True And c_ct.COLLECTIONFK = selectedCollection
Order By ct.CollectionType Ascending
Select ct

Return New BindingList(Of CollectionType)(query.ToList)

End Function

Private Function QueryLearners(ByRef data As List(Of GenerateXMLResult)) As List(Of XmlLearner)

Dim query = From d In
(From d In data
Select d.PrisonerNumber, d.LearnerReferenceNumber, d.Forename, d.Surname, d.NINO, d.ULN, d.Ethnicity, d.LearningDifficultiesDisabilities,
d.LearningDifficulty, d.DisabilityOrHealthProblem, d.PriorAttainment, d.DOB, d.Sex, d.PrisonAddress, d.PrisonPostcode).Distinct
Select New XmlLearner() With {.PrisonerNumber = d.PrisonerNumber,
.Forename = d.Forename,
.Surname = d.Surname,
.NINO = d.NINO,
.ULN = d.ULN,
.LearnerReferenceNumber = d.LearnerReferenceNumber,
.Ethnicity = d.Ethnicity,
.LearningDifficultiesDisabilities = d.LearningDifficultiesDisabilities,
.LearningDifficulty = d.LearningDifficulty,
.DisabilityOrHealthProblem = d.DisabilityOrHealthProblem,
.PriorAttainment = d.PriorAttainment,
.DOB = d.DOB,
.Sex = d.Sex,
.PrisonAddress = d.PrisonAddress,
.PrisonPostcode = d.PrisonPostcode}

Return New List(Of XmlLearner)(query.ToList)

End Function

Private Function QueryAims(ByRef data As List(Of GenerateXMLResult)) As List(Of XmlAim)

Dim query = From d In data
Select New XmlAim With {.LearnerRefNumber = d.LearnerReferenceNumber,
.LearnAimRef = d.AimCode,
.AimType = d.AimType,
.AimSeqNumber = d.AimSeqNumber,
.LearnStartDate = d.StartDate,
.LearnPlanEndDate = d.PlannedEndDate,
.FundModel = d.FundingModel,
.ProgType = d.ProgType,
.MainDelMeth = d.MainDeliveryMethod,
.DelMode = d.DeliveryMode,
.DelLocPostCode = d.DeliveryPostcode,
.ContOrgCode = d.ContractingOrganisationCode,
.CompStatus = d.CompletionStatus,
.LearnActEndDate = d.ActualEndDate,
.WithdrawReason = d.ReasonLearningEnded,
.OutcomeInd = d.LearningOutcome,
.OutGrade = d.LearningOutcomeGrade,
.SourceOfFunding = d.SourceOfFunding,
.LearningDeliveryMonitoring = d.LearningDeliveryMonitoring,
.ProviderSpecifiedDeliveryMonitoring = d.ProviderSpecifiedDeliveryMonitoring}

Return New List(Of XmlAim)(query.ToList)

End Function

Private Function GenerateXMLData() As List(Of GenerateXMLResult)

'Get the 'From' date as the beginning of the academic year
Dim fromDate, toDate As Date
fromDate = (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue)
Select cy.DateFrom).First

'Get the 'To' date as the end of the specific collection run
toDate = (From c As Collection In CType(cboCollection.DataSource, BindingList(Of Collection))
Where (c.COLLECTIONPK = cboCollection.SelectedValue)
Select c.CloseDate).First

Dim query = From d As GenerateXMLResult In learning.GenerateXML(Db.PassPhrase, fromDate, toDate)
Select d

Return New List(Of GenerateXMLResult)(query.ToList)

End Function

#End Region

#Region " XML Processing "

Private Sub ProcessXML()

'get the raw data
Dim data As List(Of GenerateXMLResult) = GenerateXMLData()

Dim xsi As XNamespace = XNamespace.Get("http://www.w3.org/2001/XMLSchema-instance")
Dim ns As XNamespace = XNamespace.Get("http://www.theia.org.uk/ILR/2011-12/1")

'declaration
Dim ILR As New XDocument
ILR.Declaration = New XDeclaration("1.0", "UTF-8", Nothing)

'comment
ILR.Add(New XComment("File created by " & Db.CurrentUser.FullName & " on " & Date.Now.ToString("dd/MM/yyyy hh:mm:ss")))

'
Dim message As New XElement(ns + "Message")
message.Add(New XAttribute(XNamespace.Xmlns + "xsi", xsi.NamespaceName))
message.Add(New XAttribute(xsi + "schemaLocation", "http://www.theia.org.uk/ILR/2011-12/1 ILR-2011-12-Structure.xsd"))

ProcessHeader(message, ns)
ProcessLearningProvider(message, ns)
'ProcessLearnerDeletion(message, ns)

Dim learners As List(Of XmlLearner) = QueryLearners(data)
Dim aims As List(Of XmlAim) = QueryAims(data)
ProcessLearners(message, ns, learners, aims)

'add root node to document
ILR.Add(message)

'save document
Dim filename As String

ILR.Save("C:ILR-A-10000115-1112-0003-01.xml")

ValidateAgainstSchema(ILR, ns)

End Sub

Private Sub XSDErrors(ByVal o As Object, ByVal e As ValidationEventArgs)
txtOutput.Text += e.Message.ToString & vbCrLf
errors = True
End Sub

Sub ValidateAgainstSchema(ByVal ilr As XDocument, ByVal ns As XNamespace)

Dim schemas As XmlSchemaSet = New XmlSchemaSet()
schemas.Add(ns.NamespaceName, System.IO.Path.GetFullPath(Application.StartupPath & "….ExportXSD201112ILR-2011-12-Structure.xsd"))
txtOutput.Text += "Attempting to validate" & vbCrLf
errors = False
ilr.Validate(schemas, AddressOf XSDErrors)
txtOutput.Text += "ILR " & IIf(errors, "did not validate", "validated") & vbCrLf

End Sub

Private Sub ProcessHeader(ByVal message As XElement, ByVal ns As XNamespace)

'.
Dim header As New XElement(ns + "Header")
message.Add(header)

'..
Dim collectionDetails As New XElement(ns + "CollectionDetails")
header.Add(collectionDetails)

'…
collectionDetails.Add(New XElement(ns + "Collection", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.CollectionName).First))

'…
collectionDetails.Add(New XElement(ns + "Year", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.YearOfCollection).First))

'…
collectionDetails.Add(New XElement(ns + "FilePreparationDate", Now.Date.ToString("yyyy-MM-dd")))

'..
Dim source As New XElement(ns + "Source")
header.Add(source)

'…
source.Add(New XElement(ns + "ProtectiveMarking", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.ProtectiveMarking).First))

'…
source.Add(New XElement(ns + "UKPRN", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.UKPRN).First))

'…
source.Add(New XElement(ns + "TransmissionNumber", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.CurrentTransmissionNumber).First.ToString.PadLeft(4, "0")))

'…
source.Add(New XElement(ns + "TransmissionType", (From ct As CollectionType In CType(cboCollectionType.DataSource, BindingList(Of CollectionType))
Where (ct.COLLECTIONTYPEPK = cboCollectionType.SelectedValue) Select ct.CollectionType).First))

'…
source.Add(New XElement(ns + "SoftwareSupplier", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.SoftwareSupplier).First))

'…
source.Add(New XElement(ns + "SoftwarePackage", My.Application.Info.ProductName))

'…
source.Add(New XElement(ns + "Release", My.Application.Info.Version))

'…
source.Add(New XElement(ns + "SerialNo", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.SerialNumber).First))

'…
source.Add(New XElement(ns + "DateTime", Now.ToString("yyyy-MM-ddTHH:mm:ss")))

End Sub

Private Sub ProcessLearningProvider(ByVal message As XElement, ByVal ns As XNamespace)

'.
Dim learningProvider As New XElement(ns + "LearningProvider")
message.Add(learningProvider)

'..
learningProvider.Add(New XElement(ns + "UKPRN", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.UKPRN).First))

'..
learningProvider.Add(New XElement(ns + "UPIN", (From cy As CollectionYear In CType(cboCollectionYear.DataSource, BindingList(Of CollectionYear))
Where (cy.COLLECTIONYEARPK = cboCollectionYear.SelectedValue) Select cy.UPIN).First))

End Sub

Private Sub ProcessLearnerDeletion(ByVal message As XElement, ByVal ns As XNamespace)

'.
Dim learnerDeletion As New XElement(ns + "LearnerDeletion")
message.Add(learnerDeletion)

'..
Dim learnRefNumber As New XElement(ns + "LearnRefNumber", "123456789100")
learnerDeletion.Add(learnRefNumber)

End Sub

Private Sub ProcessLearners(ByVal message As XElement, ByVal ns As XNamespace, ByRef learners As List(Of XmlLearner), ByRef aims As List(Of XmlAim))

For Each learner As XmlLearner In learners
'.
Dim learnerElement As New XElement(ns + "Learner")
message.Add(learnerElement)

'..
Dim learnerRefNumberElement As New XElement(ns + "LearnRefNumber", learner.LearnerReferenceNumber)
learnerElement.Add(learnerRefNumberElement)

Dim learnerReferenceNumber As String = learner.LearnerReferenceNumber

'..
If Not String.IsNullOrEmpty(learner.ULN) Then learnerElement.Add(New XElement(ns + "ULN", learner.ULN))

'..
learnerElement.Add(New XElement(ns + "FamilyName", learner.Surname))

'..
learnerElement.Add(New XElement(ns + "GivenNames", learner.Forename))

'..
learnerElement.Add(New XElement(ns + "DateOfBirth", learner.DOB.ToString("yyyy-MM-dd")))

'..
learnerElement.Add(New XElement(ns + "Ethnicity", learner.Ethnicity))

'..
learnerElement.Add(New XElement(ns + "Sex", learner.Sex))

'..
learnerElement.Add(New XElement(ns + "LLDDInd", learner.LearningDifficultiesDisabilities))

'..
If Not String.IsNullOrEmpty(learner.NINO) Then learnerElement.Add(New XElement(ns + "NINumber", learner.NINO))

'..
learnerElement.Add(New XElement(ns + "PriorAttain", learner.PriorAttainment))

ProcessLearnerContact(learnerElement, ns, learner)

ProcessLearnerContactPreferences(learnerElement, ns)

If learner.LearningDifficultiesDisabilities = 1 Then ProcessLLDDandHealthProblem(learnerElement, ns, learner)

ProcessLearnerProviderSpecifiedMonitoring(learnerElement, ns, learner)

Dim learnerAims As List(Of XmlAim) = (From la As XmlAim In aims
Where la.LearnerRefNumber = learnerReferenceNumber
Select la).ToList

ProcessLearningDelivery(learnerElement, ns, learnerAims)

End Sub

Private Sub ProcessLearnerContact(ByRef learnerElement As XElement, ByVal ns As XNamespace, ByRef learner As XmlLearner)

'***CURRENT ADDRESS***

'..
Dim learnerContactElement As New XElement(ns + "LearnerContact")
learnerElement.Add(learnerContactElement)

'…
learnerContactElement.Add(New XElement(ns + "LocType", 1))

'…
learnerContactElement.Add(New XElement(ns + "ContType", 2))

'…
Dim postAddElement As New XElement(ns + "PostAdd")
learnerContactElement.Add(postAddElement)

'….
postAddElement.Add(New XElement(ns + "AddLine1", learner.PrisonAddress))

'*********************

'***CURRENT POSTCODE***

'..
learnerContactElement = New XElement(ns + "LearnerContact")
learnerElement.Add(learnerContactElement)

'…
learnerContactElement.Add(New XElement(ns + "LocType", 2))

'…
learnerContactElement.Add(New XElement(ns + "ContType", 2))

'…
learnerContactElement.Add(New XElement(ns + "PostCode", learner.PrisonPostcode))

End Sub

Private Sub ProcessLLDDandHealthProblem(ByRef learnerElement As XElement, ByVal ns As XNamespace, ByRef learner As XmlLearner)

Dim llddAndHealthProblemElement As XElement

'***Learning Difficulty

If learner.LearningDifficulty.HasValue Then
'..
llddAndHealthProblemElement = New XElement(ns + "LLDDandHealthProblem")
learnerElement.Add(llddAndHealthProblemElement)

'…
llddAndHealthProblemElement.Add(New XElement(ns + "LLDDType", "LD"))

'…
llddAndHealthProblemElement.Add(New XElement(ns + "LLDDCode", learner.LearningDifficulty))
End If

'***Disability Or Health Problem

If learner.DisabilityOrHealthProblem.HasValue Then
'..
llddAndHealthProblemElement = New XElement(ns + "LLDDandHealthProblem")
learnerElement.Add(llddAndHealthProblemElement)

'…
llddAndHealthProblemElement.Add(New XElement(ns + "LLDDType", "DS"))

'…
llddAndHealthProblemElement.Add(New XElement(ns + "LLDDCode", learner.DisabilityOrHealthProblem))
End If

End Sub

Private Sub ProcessLearnerContactPreferences(ByRef learnerElement As XElement, ByVal ns As XNamespace)

Dim contactPreferenceElement As XElement