CREATE DEFINER=`root`@`%` PROCEDURE `sp_push_service_provider`(IN lastextractdate bigint)
BEGIN

  -- Update service provider details
  INSERT INTO roadsmart_dw.DimSeviceProvider (ServiceProviderID,
  CreateDateTime,
  CreatedBy,
  ModifiedDateTime,
  ModifiedBy,
  LegalFirstName,
  LegalLastName,
  IsSuspended,
  IsSuspendedCode,
  Postcode,
  TravelDistanceTo3000,
  PhoneNo,
  Email,
  HasClearedToWorkStatus,
  HasClearedToWorkStatusCode,
  ClearedToWorkExpiryDate,
  HasWorkingWithChildren,
  HasWorkingWithChildrenCode,
  WorkingWithChildrenExpiryDate,
  HasValidDrivingInstructorAuthority,
  HasValidDrivingInstructorAuthorityCode,
  DrivingInstructorAuthorityExpiryDate,
  GenderCode,
  LanguagesSpoken,
  WWCStatus,
  DIAStatus,
  DIARequired)
    SELECT
      ServiceProviderID AS ServiceProviderID,
      CURRENT_TIMESTAMP() AS CreateDateTime,
      'SYSTEM' AS CreatedBy,
      CURRENT_TIMESTAMP() AS ModifiedDateTime,
      'SYSTEM' AS ModifiedBy,
      LegalFirstName AS LegalFirstName,
      LegalLastName AS LegalLastName,
      Postcode AS Postcode,
      TravelDistanceTo3000 AS TravelDistanceTo3000,
      PhoneNo AS PhoneNo,
	  IsSuspended As IsSuspended,
	  IF(IsSuspended = 1, 'Yes', 'No') AS IsSuspendedCode,
      Email AS Email,
      HasClearedToWorkStatus AS HasClearedToWorkStatus,
      HasClearedToWorkStatusCode AS HasClearedToWorkStatusCode,
      ClearedToWorkExpiryDate AS ClearedToWorkExpiryDate,
      HasWorkingWithChildren AS HasWorkingWithChildren,
      HasWorkingWithChildrenCode AS HasWorkingWithChildrenCode,
      WorkingWithChildrenExpiryDate AS WorkingWithChildrenExpiryDate,
      HasValidDrivingInstructorAuthority AS HasValidDrivingInstructorAuthority,
      HasValidDrivingInstructorAuthorityCode AS HasValidDrivingInstructorAuthorityCode,
      DrivingInstructorAuthorityExpiryDate AS DrivingInstructorAuthorityExpiryDate,
      GenderCode AS GenderCode,
      LanguagesSpoken AS LanguagesSpoken,
      WWCStatus AS WWCStatus,
      DIAStatus AS DIAStatus,
      DIARequired AS DIARequired
    FROM vw_service_providers
  --  WHERE timemodified > lastextractdate
  ON DUPLICATE KEY UPDATE
  ServiceProviderID = VALUES(ServiceProviderID),
  CreateDateTime = VALUES(CreateDateTime),
  CreatedBy = VALUES(CreatedBy),
  ModifiedDateTime = VALUES(ModifiedDateTime),
  ModifiedBy = VALUES(ModifiedBy),
  LegalFirstName = VALUES(LegalFirstName),
  LegalLastName = VALUES(LegalLastName),
  Postcode = VALUES(Postcode),
  IsSuspended = VALUES(IsSuspended),
  IsSuspendedCode = VALUES(IsSuspendedCode),
  TravelDistanceTo3000 = VALUES(TravelDistanceTo3000),
  PhoneNo = VALUES(PhoneNo),
  Email = VALUES(Email),
  HasClearedToWorkStatus = VALUES(HasClearedToWorkStatus),
  HasClearedToWorkStatusCode = VALUES(HasClearedToWorkStatusCode),
  ClearedToWorkExpiryDate = VALUES(ClearedToWorkExpiryDate),
  HasWorkingWithChildren = VALUES(HasWorkingWithChildren),
  HasWorkingWithChildrenCode = VALUES(HasWorkingWithChildrenCode),
  WorkingWithChildrenExpiryDate = VALUES(WorkingWithChildrenExpiryDate),
  HasValidDrivingInstructorAuthority = VALUES(HasValidDrivingInstructorAuthority),
  HasValidDrivingInstructorAuthorityCode = VALUES(HasValidDrivingInstructorAuthorityCode),
  DrivingInstructorAuthorityExpiryDate = VALUES(DrivingInstructorAuthorityExpiryDate),
  GenderCode = VALUES(GenderCode),
  LanguagesSpoken = VALUES(LanguagesSpoken),
  WWCStatus = VALUES(WWCStatus),
  DIAStatus = VALUES(DIAStatus),
  DIARequired = VALUES(DIARequired)
  ;

  -- Set the on road role values
  -- IsOnRoad,
  -- IsOnRoadCode,
  UPDATE roadsmart_dw.DimSeviceProvider dw
  JOIN (SELECT DISTINCT
      mdl_local_psdp_users.userid AS ServiceProviderID,
      IF(mdl_role.shortname = 'rs_drivinginstructor_onroad', 1, 0) AS IsOnRoad,
      IF(mdl_role.shortname = 'rs_drivinginstructor_onroad', 'Yes', 'No') AS IsOnRoadCode
    FROM mdl_role_assignments
      INNER JOIN mdl_role
        ON mdl_role_assignments.roleid = mdl_role.id
      RIGHT OUTER JOIN mdl_local_psdp_users
        ON mdl_role_assignments.userid = mdl_local_psdp_users.userid) mdl
    ON dw.ServiceProviderID = mdl.ServiceProviderID
  SET dw.IsOnRoad = mdl.IsOnRoad,
      dw.IsOnRoadCode = mdl.IsOnRoadCode;

  -- Set the on road role values
  -- IsOffRoad,
  -- IsOffRoadCode
  UPDATE roadsmart_dw.DimSeviceProvider dw
  JOIN (SELECT DISTINCT
      mdl_local_psdp_users.userid AS ServiceProviderID,
      IF(mdl_role.shortname = 'rs_drivinginstructor_offroad', 1, 0) AS IsOffRoad,
      IF(mdl_role.shortname = 'rs_drivinginstructor_offroad', 'Yes', 'No') AS IsOffRoadCode
    FROM mdl_role_assignments
      INNER JOIN mdl_role
        ON mdl_role_assignments.roleid = mdl_role.id
      RIGHT OUTER JOIN mdl_local_psdp_users
        ON mdl_role_assignments.userid = mdl_local_psdp_users.userid) mdl
    ON dw.ServiceProviderID = mdl.ServiceProviderID
  SET dw.IsOffRoad = mdl.IsOffRoad,
      dw.IsOffRoadCode = mdl.IsOffRoadCode;

  -- Set the on road role values
  -- IsFacilitator,
  -- IsFacilitatorCode,
  UPDATE roadsmart_dw.DimSeviceProvider dw
  JOIN (SELECT DISTINCT
      mdl_local_psdp_users.userid AS ServiceProviderID,
      IF(mdl_role.shortname = 'rs_facilitator', 1, 0) AS IsFacilitator,
      IF(mdl_role.shortname = 'rs_facilitator', 'Yes', 'No') AS IsFacilitatorCode
    FROM mdl_role_assignments
      INNER JOIN mdl_role
        ON mdl_role_assignments.roleid = mdl_role.id
      RIGHT OUTER JOIN mdl_local_psdp_users
        ON mdl_role_assignments.userid = mdl_local_psdp_users.userid) mdl
    ON dw.ServiceProviderID = mdl.ServiceProviderID
  SET dw.IsFacilitator = mdl.IsFacilitator,
      dw.IsFacilitatorCode = mdl.IsFacilitatorCode;

  -- Set the on road role values
  -- IsDTCRep,
  -- IsDTCRepCode,
  UPDATE roadsmart_dw.DimSeviceProvider dw
  JOIN (SELECT DISTINCT
      mdl_local_psdp_users.userid AS ServiceProviderID,
      IF(mdl_role.shortname = 'rs_ds_rep', 1, 0) AS IsDTCRep,
      IF(mdl_role.shortname = 'rs_ds_rep', 'Yes', 'No') AS IsDTCRepCode
    FROM mdl_role_assignments
      INNER JOIN mdl_role
        ON mdl_role_assignments.roleid = mdl_role.id
      RIGHT OUTER JOIN mdl_local_psdp_users
        ON mdl_role_assignments.userid = mdl_local_psdp_users.userid) mdl
    ON dw.ServiceProviderID = mdl.ServiceProviderID
  SET dw.IsDTCRep = mdl.IsDTCRep,
      dw.IsDTCRepCode = mdl.IsDTCRepCode;

  -- Set the on road role values
  -- IsMasterTrainerInClass,
  -- IsMasterTrainerInClassCode,
  UPDATE roadsmart_dw.DimSeviceProvider dw
  JOIN (SELECT DISTINCT
      mdl_local_psdp_users.userid AS ServiceProviderID,
      IF(mdl_role.shortname = 'rs_master_trainer_in_class', 1, 0) AS IsMasterTrainerInClass,
      IF(mdl_role.shortname = 'rs_master_trainer_in_class', 'Yes', 'No') AS IsMasterTrainerInClassCode
    FROM mdl_role_assignments
      INNER JOIN mdl_role
        ON mdl_role_assignments.roleid = mdl_role.id
      RIGHT OUTER JOIN mdl_local_psdp_users
        ON mdl_role_assignments.userid = mdl_local_psdp_users.userid) mdl
    ON dw.ServiceProviderID = mdl.ServiceProviderID
  SET dw.IsMasterTrainerInClass = mdl.IsMasterTrainerInClass,
      dw.IsMasterTrainerInClassCode = mdl.IsMasterTrainerInClassCode;

  -- Set the on road role values
  -- IsMasterTrainerInCar,
  -- IsMasterTrainerInCarCode,
  UPDATE roadsmart_dw.DimSeviceProvider dw
  JOIN (SELECT DISTINCT
      mdl_local_psdp_users.userid AS ServiceProviderID,
      IF(mdl_role.shortname = 'rs_master_trainer_in_car', 1, 0) AS IsMasterTrainerInCar,
      IF(mdl_role.shortname = 'rs_master_trainer_in_car', 'Yes', 'No') AS IsMasterTrainerInCarCode
    FROM mdl_role_assignments
      INNER JOIN mdl_role
        ON mdl_role_assignments.roleid = mdl_role.id
      RIGHT OUTER JOIN mdl_local_psdp_users
        ON mdl_role_assignments.userid = mdl_local_psdp_users.userid) mdl
    ON dw.ServiceProviderID = mdl.ServiceProviderID
  SET dw.IsMasterTrainerInCar = mdl.IsMasterTrainerInCar,
      dw.IsMasterTrainerInCarCode = mdl.IsMasterTrainerInCarCode;

END