Admin XML and JSON assignments

--admin commands
Use MetroAlt
go
--schema management of objects
Create schema Management
Go
Create view Management.vw_AnnualRidership
As
Select year(BusscheduleAssignmentDate) [Year],
Count(riders) Total
From BusScheduleAssignment bs
inner join Ridership r
on r.BusScheduleAssigmentKey=bs.BusScheduleAssignmentKey
Group by year(BusscheduleAssignmentDate)

go
Create view Management.vw_EmployeeInfo
As
Select EmployeeFirstName, EmployeeLastName, EmployeeEmail,
PositionName, EmployeeHourlyPayRate
From Employee e
inner join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join Position p
on p.PositionKey=ep.PositionKey
Go
Create role managementRole

Grant Select on Schema::Management to ManagementRole

Grant Select, Insert, update on Employee to ManagementRole
Grant Select, Insert, Update on EmployeePosition to Managementrole

Create login managementLogin with password='P@ssw0rd1', default_database=MetroAlt
Use MetroAlt
Create user managementUser for login managementLogin 
Alter role managementrole add member managementUser

Backup Database MetroAlt to disk='C:\backup\MetroAlt.bak'
Restore Database metroAlt from disk='C:\backups\metroAlt'

--end admin assignment

--Begin Xml

Create xml Schema Collection MaintenanceNoteSchemaCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
           elementFormDefault="qualified" 
           targetNamespace="http://www.metroalt.com/maintenancenote" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="maintenancenote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="title" />
        <xs:element name="note">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="p" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="followup" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table MaintenanceDetail
(
     maintenanceDetailKey int identity(1,1) primary key,
  maintenanceNotes nvarchar(255)

)

Alter table MaintenanceDetail
Drop column MaintenanceNotes

Alter table MaintenanceDetail
add MaintenanceNotes xml (MaintenanceNoteSchemaCollection)

Insert into maintenanceDetail(maintenanceNotes)
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <title>Wear and Tear on Hydralic units</title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

--will fail because title is not Title
Insert into maintenanceDetail(maintenanceNotes)
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <Title>Wear and Tear on Hydralic units</Title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

Select * from MaintenanceDetail

--extras
Select * from Employee for xml raw('Employee'), elements, root('Employees')

--end xml
--begin JSON
Select * from Busbarn for JSON auto
Select BusBarnKey, Busbarnphone, busBarnAddress as 'address.street', 
Busbarncity as 'address.city', BusBarnZipcode as 'address.zipcode'
from Busbarn for JSON Path

Alter Database MetroAlt set compatibility_Level=130

Declare @JSON nvarchar(max)
Set @JSON='{"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13}'
Select * from OpenJSon(@JSON)

Declare @products nvarchar(max)
Set @products='[
    {"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13},
    {"_id" : 2, "product": "Chrome Book", "price" : 245.99, "quantityAvailable": 23},
    {"_id" : 3, "product": "Bose Lap Top Speakers", "price" : 89.50, "quantityAvailable" : 10},
    {"_id" : 4, "product": "Blue Tooth Game Controller", "price" : 149.99, "quantityAvailable" : 3},
    {"_id" : 5, "product": "Star Wars Mouse Pad", "price" : 1.50, "quantityAvailable" : 100},
    {"_id" : 6, "product": "Dell XPS Desk Top Computer", "price" : 945.00, "quantityAvailable" : 7},
    {"_id" : 7, "product": "Microsoft Surface Pro", "price" : 1250.75, "quantityAvailable" : 9},
    {"_id" : 8, "product": "Norton Anti Virus", "price" : 75.50, "quantityAvailable": 2},
    {"_id" : 9, "product": "Mechanical Keyboard", "price" : 125.50, "quantityAvailable" : 3},
    {"_id" : 10, "product": "Android Tablet", "price" : 345.23, "quantityAvailable" : 5}
  ]'
  Select * from OpenJson(@products)
  with (
    id int '$._id',
 product nvarchar(255) '$.product',
 price decimal(8,2) '$.price',
 quantity int '$.quantityAvailable'
 )
--end JSON

Triggers

--Triggers like stored proc
--except they are triggered by an event
--insert, update, delete
--enforcing business
--every grant request should be reviewed within 2 days
--after, instead of

use Community_Assist
go
Create Trigger tr_ReveiwList on GrantRequest
after insert 
As
--check to see if table exists
if not exists
   (Select name from sys.tables
      where name='ToReview')
Begin
  Create Table ToReview
  (
      GrantRequestKey int,
   GrantRequestDate datetime,
   DateToReviewBy Date
  )
end
Declare @GrantKey int
Declare @GrantRequestDate Datetime
Declare @DateToReview Date
Select @GrantKey=GrantRequestKey, 
@GrantRequestDate=GrantRequestDate
From Inserted
Set @DateToReview=dateAdd(Day,2,@GrantRequestDate)
Insert into ToReview(GrantRequestKey, GrantRequestDate, DateToReviewBy)
Values(@GrantKey, @GrantRequestDate, @DateToReview)
go

Insert into GrantRequest(GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Values(GetDate(),4,1,'Hungry this morning',60)

Select * from toReview


Select * from BusinessRule

Go
Create trigger tr_CheckMaximum on GrantRequest
instead of insert
As
Declare @requestAmount money
Declare @MaxRequestAmount money
Declare @GrantType int

Select @RequestAmount=GrantRequestAmount, 
       @GrantType=GrantTypeKey
    From inserted

Select @MaxRequestAmount=GrantTypeMaximum 
       From GrantType
    Where GrantTypekey = @GrantType

If @RequestAmount > @MaxRequestAmount
Begin
       print 'Request exceeds maximum allowed'
    return
End
Else
Begin
  Insert into GrantRequest(GrantRequestDate, PersonKey, 
  GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
  Select  GrantRequestDate, PersonKey, GrantTypeKey, 
  GrantRequestExplanation, GrantRequestAmount from inserted
End

Insert into GrantRequest(GrantRequestDate, PersonKey, 
  GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
  Values(GetDate(), 6, 1, 'Need Food', 100.75)


  Select * from GrantType
Select * from GrantRequest
Select * from ToReview