/*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*
* DESCRIPTION:
    This script creates a stored procedure in Transact SQL
    which attempts to cleanse the data in a flat database
    table and to insert the clean data into another table.
    The 'dirty' data is received from Citel Telemarketers
    (and possibly other sources). The cleansing process is 
    aimed at allowing the data to be transformed and inserted
    into the EzyStore Database Schema. This is achieved by checking
    all datatypes and lenths, as well as Null conditions and the 
    duplication of data. The EzyStore schema is being
    used as part of the Isis Directories Project to provide
    'web-commerce' functionality for SME businesses.

* LOCATION: 
    NTWSYD018 h:\DataTransform\procTransformDirtyFlatIntoCleanFlat.sql,

* WRITTEN: July 18, 2000 -->

* LAST MODIFIED: August 1, 2000   

* STATUS: in development

* PROGRAMMER: Matthew Bishop (matth3wbishop@yahoo.com)

* PROJECT SUPERVISOR: James Ward (james@forwardmedia.com.au)

* NOTES: 
  a) The final EzyStore Schema has not yet been fixed
  b) The error handling behavior has not been fixed
--*--*--*--*--*--*--*--*--*--*--*--*--**--*--*--*--*--*--*--**/

Use EzyStoreDB
/*
*/  
   
go
IF ((SELECT    COUNT(*)
     FROM      sysobjects
     WHERE     name = 'procTransformDirtyFlatIntoCleanFlat'
     AND       type = 'P') > 0)
BEGIN
  DROP PROCEDURE procTransformDirtyFlatIntoCleanFlat
END 

go
IF ((SELECT    COUNT(*)
     FROM      sysobjects
     WHERE     name = 'procHandleErrors'
     AND       type = 'P') > 0)
BEGIN
  DROP PROCEDURE procHandleErrors
END

go 
CREATE PROCEDURE procHandleErrors
(
  @sProblemDetails        varchar (255) = ''
)
AS
BEGIN
  Declare
    @sLogFile             varchar (255),
    @CommandShellString   varchar (255)
  Set
    @sLogFile = 'c:\CitelDataErrorInformation.txt'
  Print 
    'PROBLEM: ' + @sProblemDetails 
  Print
    'This message has been stored in ' + @sLogFile
  Set 
     @CommandShellString = 'echo ' + @sProblemDetails +
                           '>> ' + @sLogFile
  Execute master..xp_cmdshell @CommandShellString
END -- procHandleErrors

go
CREATE PROCEDURE procTransformDirtyFlatIntoCleanFlat
(
   @sErrorLogFileName         varchar(255)  =  'Default'
)
AS
BEGIN
-->-->-->-->-->-->-->-->-->-->-->-->-->>-->-->-->-->-->-->-->-->-->
SET NOCOUNT ON

Declare 
        @CommandShellString    varchar(255),     -- Written to the log file
        @sProblemDetails       varchar(255),     -- Information about a problem with the data
        @ErrorMessageString    varchar(255),     -- Used to concatenate error messages for logging
        @DebugToggle           varchar(10),      -- Used to turn debugging mode on or off
        @CurrentCompanyName    int,              -- From 'tblCompany.Company_id'
        @CurrentAddressName    int,              -- From 'tblAddress.Address_id' IDENTITY insert
        @CurrentRegionName     int,              -- For Converting from State name to Region Id Number
        @CurrentRegionId       int,
        @CurrentPhone          int,              -- From 'tblPhone.Phone_id' IDENTITY insert
        @CurrentPhoneZone      int,              -- a Zone Identification based on the Phone No. Area Code

	@id int,
	@SmeIdNumber varchar (20),
	@BusinessName varchar (100),
	@DateEntered datetime,
	@CurrentEmailAddress varchar (255),
	@CurrentUrl varchar (255),
	@EstablishedSince varchar (4),
	@AppointmentsRequired bit,
	@Retail char (2),
	@WholeSale char (2),
	@HomeDelivery char (2),
	@FreeQuotes char (2),
	@ProprietorSalutation varchar (20),
	@BusinessBaseCategory int,
	@ProprietorName varchar (50),
	@ProprietorSurname varchar (50),
	@Mobile varchar (20),
	@BusinessAddressStreet1 varchar (255),
	@BusinessAddressStreet2 varchar (255),
	@BusinessAddressSuburb varchar (50),
	@BusinessAddressState varchar (10),
	@BusinessAddressCity varchar (50),
	@BusinessAddressPostCode varchar (10),
	@BusinessAddressPhoneAreaCode varchar (10),
	@BusinessAddressPhone varchar (20),
	@BusinessAddressFaxAreaCode varchar (10),
	@BusinessAddressFax varchar (20),
	@BusinessAddressCountry varchar (50),
	@PostalAddressStreet1 varchar (255),
	@PostalAddressStreet2 varchar (255),
	@PostalAddressSuburb varchar (50),
	@PostalAddressState varchar (10),
	@PostalAddressCity varchar (50),
	@PostalAddressPostCode varchar (10),
	@PostalAddressCountry varchar (50),
	@Association1 int,
	@Association2 int,
	@Association3 int,
	@Association4 int,
	@PaymentType char (10),
	@AmericanExpress char (2),
	@Visa char (2),
	@Mastercard char (2),
	@DinersClub char (2),
	@Bankcard char (2),
	@CardName varchar (50),
	@CardType int,
	@CardNumber varchar (50),
	@CardExpiry char (5),
	@Eftpos char (10),
	@Cheque char (10),
	@ABN varchar (50),
	@LicenseNumber varchar (50),
	@ProfessionalQualification varchar (50),
	@NumberOfEmployees varchar (20),
	@CurrentIsp varchar (20),
	@CurrentEmailProgram varchar (20),
	@CurrentAccountingPackage varchar (20),
	@ACNorBRN varchar (20),
        @DialUpRequired bit,
	@BusinessOffering int,
	@HomePageText varchar (255),
	@MonStart varchar (5),
	@MonEnd varchar (5),
	@TueStart varchar (5),
	@TueEnd varchar (5),
	@WedStart varchar (5),
	@WedEnd varchar (5),
	@ThuStart varchar (5),
	@ThuEnd varchar (5),
	@FriStart varchar (5),
	@FriEnd varchar (5),
	@SatStart varchar (5),
	@SatEnd varchar (5),
	@SunStart varchar (5),
	@SunEnd varchar (5),
	@Amount money  

SET     @DebugToggle = 'true'
SET     @sLogFile    = 'c:\CitelDataErrorInformation.txt'
Declare FlatTableCursor       Cursor For
  SELECT
	[id],
	SmeIdNumber,
	BusinessName,
	DateEntered,
	CurrentEmailAddress,
	CurrentUrl,
	EstablishedSince,
	AppointmentsRequired,
	Retail,
	WholeSale,
	HomeDelivery,
	FreeQuotes,
	ProprietorSalutation,
	BusinessBaseCategory,
	ProprietorName,
	ProprietorSurname,
	Mobile,
	BusinessAddressStreet1,
	BusinessAddressStreet2,
	BusinessAddressSuburb,
	BusinessAddressState,
	BusinessAddressCity,
	BusinessAddressPostCode,
	BusinessAddressPhoneAreaCode,
	BusinessAddressPhone,
	BusinessAddressFaxAreaCode,
	BusinessAddressFax,
	BusinessAddressCountry,
	PostalAddressStreet1,
	PostalAddressStreet2,
	PostalAddressSuburb,
	PostalAddressState,
	PostalAddressCity,
	PostalAddressPostCode,
	PostalAddressCountry,
	Association1,
	Association2,
	Association3,
	Association4,
	PaymentType,
	AmericanExpress,
	Visa,
	Mastercard,
	DinersClub,
	Bankcard,
	CardName,
	CardType,
	CardNumber,
	CardExpiry,
	Eftpos,
	Cheque,
	ABN,
	LicenseNumber,
	ProfessionalQualification,
	NumberOfEmployees,
	CurrentIsp,
	CurrentEmailProgram,
	CurrentAccountingPackage,
	ACNorBRN,
        DialUpRequired,
	BusinessOffering,
	HomePageText,
	MonStart,
	MonEnd,
	TueStart,
	TueEnd,
	WedStart,
	WedEnd,
	ThuStart,
	ThuEnd,
	FriStart,
	FriEnd,
	SatStart,
	SatEnd,
	SunStart,
	SunEnd,
	Amount  
  FROM
        CitelDB..CleanSitelData
    
Open    FlatTableCursor    

  FETCH NEXT FROM
     FlatTableCursor
  FROM
        CitelDB..CitelData
    
Open    FlatTableCursor    

-- BUG: The first row is missed out on
  FETCH NEXT FROM
     FlatTableCursor
  INTO
	@id,
	@SmeIdNumber,
	@BusinessName,
	@DateEntered,
	@CurrentEmailAddress,
	@CurrentUrl,
	@EstablishedSince,
	@AppointmentsRequired,
	@Retail,
	@WholeSale,
	@HomeDelivery,
	@FreeQuotes,
	@ProprietorSalutation,
	@BusinessBaseCategory,
	@ProprietorName,
	@ProprietorSurname,
	@Mobile,
	@BusinessAddressStreet1,
	@BusinessAddressStreet2,
	@BusinessAddressSuburb,
	@BusinessAddressState,
	@BusinessAddressCity,
	@BusinessAddressPostCode,
	@BusinessAddressPhoneAreaCode,
	@BusinessAddressPhone,
	@BusinessAddressFaxAreaCode,
	@BusinessAddressFax,
	@BusinessAddressCountry,
	@PostalAddressStreet1,
	@PostalAddressStreet2,
	@PostalAddressSuburb,
	@PostalAddressState,
	@PostalAddressCity,
	@PostalAddressPostCode,
	@PostalAddressCountry,
	@Association1,
	@Association2,
	@Association3,
	@Association4,
	@PaymentType,
	@AmericanExpress,
	@Visa,
	@Mastercard,
	@DinersClub,
	@Bankcard,
	@CardName,
	@CardType,
	@CardNumber,
	@CardExpiry,
	@Eftpos,
	@Cheque,
	@ABN,
	@LicenseNumber,
	@ProfessionalQualification,
	@NumberOfEmployees,
	@CurrentIsp,
	@CurrentEmailProgram,
	@CurrentAccountingPackage,
	@ACNorBRN,
        @DialUpRequired,              	
        @BusinessOffering,
	@HomePageText,
	@MonStart,
	@MonEnd,
	@TueStart,
	@TueEnd,
	@WedStart,
	@WedEnd,
	@ThuStart,
	@ThuEnd,
	@FriStart,
	@FriEnd,
	@SatStart,
	@SatEnd,
	@SunStart,
	@SunEnd,
	@Amount  
--print convert(varchar(100), @@Fetch_SStatus)

While (@@Fetch_Status <> -1)
Begin
  -- Pseudo Code:
  --   Insert these variable values into the appropriate
  --   table in the EzyShop database schema       
  --Print 'The Business Name is: ' + @BusinessName  
  --
  Begin Transaction InsertCompanyDetailsTransaction

    SET @CurrentRegionId = NULL
    SET @CurrentPhoneZone = NULL

    if (@DebugToggle = 'true')
    begin
      Print ''
      Print '@@FETCH_STATUS = ' + Convert(varchar, @@FETCH_STATUS)
      Print '-->-->-->-->-->-->-->-->-->-->-->-->-->-->-->'     
    end

    --******** Business Name
    if (@BusinessName IS NULL)
    begin
      Exec procHandleErrors
        'The business name for the current company is Null'
      GoTo NextRecordLabel
    end
    
    if (LEN(@BusinessName) > 100)
    begin
      Exec procHandleErrors
        'The business name for the current company is too long'
      GoTo NextRecordLabel
    end
    
    Print 'CURRENT COMPANY: ' + @BusinessName

    --************ Date Entered (0c)
    if (ISDATE(@DateEntered) = 0)
    begin
      Exec procHandleErrors
        'The "Date Entered" field is not a valid date. This field value will not be transfered'
    end
     
    --************ Current Email Address (0d)
    if (LEN(@CurrentEmailAddress) > 255)
    begin
      Exec procHandleErrors
        'The "Current Email Address" field is too long. This field value will not be transfered'
    end

    --************ Current URL (0e)
    if (LEN(@CurrentURL) > 255)
    begin
      Exec procHandleErrors
        'The "Current URL" field is too long. This field value will not be transfered'
    end

    --<><><><> Established Since (0f)
    if ((ISDATE(@EstablishedSince) = 0) OR (LEN(@EstablishedSince) > 4))
    begin
      Exec procHandleErrors
        'The "Established Since" field is not a valid date. This field value will not be transfered'
    end

    --<*><*><*> Appointments Required (0g)
    if ((@AppointmentsRequired <> 'y') and (@AppointmentsRequired <> 'n'))
    begin
      Exec procHandleErrors
        'The "Appointments Required" field is not valid. This field value will not be transfered'
    end

    --<#><#><#> Retail (0h)
    if ((@Retail <> 'y') and (@Retail <> 'n'))
    begin
      Exec procHandleErrors
        'The "Retail" field is not valid. This field value will not be transfered'
    end

    --<><><><> Proprietor Name (0n)
    if (LEN(@ProprietorName) > 50)
    begin
      Exec procHandleErrors
        'The "Contact Given Name" field is too long. This field value will not be transfered'
    end

    --<><><><> Proprietor SurName (0o)
    if (LEN(@ProprietorSurName) > 50)
    begin
      Exec procHandleErrors
        'The "Contact Family Name" field is too long. This field value will not be transfered'
    end

    if (LEN(@ContactPosition) > 50)
    begin
      Exec procHandleErrors
        'The "Contact Family Name" field is too long. This field value will not be transfered'
    end


/*    INSERT INTO CleanCitelData
      ([name])
    VALUES
      (@BusinessName)      
*/ 
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--
    * CHECKING BUSINESS ADDRESS DETAILS
    *--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/
    SET @CurrentRegionId = NULL
    SET @CurrentPhoneZone = NULL

    if (@DebugToggle = 'true')
    begin
      Print ''
      Print 'BUSINESS ADDRESS DETAILS:'
    end

    SELECT
      @CurrentRegionId = B.Zone_Id
    FROM
       v_region_list A
    INNER JOIN
       v_region_list B
    ON 
       A.Parent_id = B.Zone_id
    WHERE
           ((B.Parent_id = 13)
    AND    (B.[Name] LIKE @BusinessAddressState))    

    if (@DebugToggle = 'true')
    begin
      Print 'CurrentRegionId = ' + Convert(varchar, @CurrentRegionId)
    end

    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--
    * POSTAL ADDRESS DETAILS
    *--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/
    --*** Convert the State Name into a Region Id 
    --*** using a self join on the v_region_list view.
    --*** ('13' == The Zone_Id of Australia in 'tblZone')
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--
    * BILLING ADDRESS DETAILS
    *--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--
    * COMPANY EMAIL DETAILS (tblEmail, tblEmailRelative, tblEmailType)
    *--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--
    * COMPANY URL DETAILS (tblURL, tblURLRelative, tblURLType)
    *--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--
    * COMPANY CONTACT (PEOPLE) DETAILS (tblPerson, tblCompanyPerson)
    *--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/
NextRecordLabel:
  Commit Transaction InsertCompanyDetailsTransaction

  FETCH NEXT FROM
     FlatTableCursor
  INTO
	@id,
	@SmeIdNumber,
	@BusinessName,
	@DateEntered,
	@CurrentEmailAddress,
	@CurrentUrl,
	@EstablishedSince,
	@AppointmentsRequired,
	@Retail,
	@WholeSale,
	@HomeDelivery,
	@FreeQuotes,
	@ProprietorSalutation,
	@BusinessBaseCategory,
	@ProprietorName,
	@ProprietorSurname,
	@Mobile,
	@BusinessAddressStreet1,
	@BusinessAddressStreet2,
	@BusinessAddressSuburb,
	@BusinessAddressState,
	@BusinessAddressCity,
	@BusinessAddressPostCode,
	@BusinessAddressPhoneAreaCode,
	@BusinessAddressPhone,
	@BusinessAddressFaxAreaCode,
	@BusinessAddressFax,
	@BusinessAddressCountry,
	@PostalAddressStreet1,
	@PostalAddressStreet2,
	@PostalAddressSuburb,
	@PostalAddressState,
	@PostalAddressCity,
	@PostalAddressPostCode,
	@PostalAddressCountry,
	@Association1,
	@Association2,
	@Association3,
	@Association4,
	@PaymentType,
	@AmericanExpress,
	@Visa,
	@Mastercard,
	@DinersClub,
	@Bankcard,
	@CardName,
	@CardType,
	@CardNumber,
	@CardExpiry,
	@Eftpos,
	@Cheque,
	@ABN,
	@LicenseNumber,
	@ProfessionalQualification,
	@NumberOfEmployees,
	@CurrentIsp,
	@CurrentEmailProgram,
	@CurrentAccountingPackage,
	@ACNorBRN,
        @DialUpRequired,              	
        @BusinessOffering,
	@HomePageText,
	@MonStart,
	@MonEnd,
	@TueStart,
	@TueEnd,
	@WedStart,
	@WedEnd,
	@ThuStart,
	@ThuEnd,
	@FriStart,
	@FriEnd,
	@SatStart,
	@SatEnd,
	@SunStart,
	@SunEnd,
	@Amount  
End --- CursorLoop

Close        FlatTableCursor
Deallocate   FlatTableCursor

SET NOCOUNT OFF
--<--<--<--<--<--<--<--<--<--<--<--<--<<--<--<--<--<--<--<--<--<--<--<--<--<--<--<
end -- procTransformDirtyFlatIntoCleanFlat
go

procTransformDirtyFlatIntoCleanFlat