Migrate appointment categories from ZyDesk profile to Administration Portal

This information is primarily for administrators and/or people who manage Zylinc solutions

This information is useful if you upgrade your Zylinc solution from a version older than 5.5u3, and you've used customized appointment categories in your old version. When that's the case, you must restore the customized appointment categories manually on your new version, and the information here will help you do that.

You can extract appointment categories (color definitions for calendar entries) from an existing ZyDesk profile this way:

  1. In SQL Server Management Studio, click New Query

  2. Copy the following SQL code:

    Copy 
    Use ZyDB
    DECLARE @curr_prof nvarchar(256)
    DECLARE @curr_val nvarchar(max), @curr_line nvarchar(max)
    DECLARE @start_profrow INT, @stop_profrow INT
    DECLARE @start_entry INT, @stop_entry INT
    DECLARE @tprof nvarchar(256), @ttype nvarchar(256), @tid nvarchar(256), @tdescr nvarchar(256)
    DECLARE @tfg varchar(16),@tbg varchar(16)
    DECLARE @tofg int, @tobg int
    DECLARE @toutput TABLE(profile nvarchar(256),type nvarchar(256),identifier nvarchar(256),
    description nvarchar(256),org_bg INT,orig_fg INT,background varchar(16),foreground varchar(16))
    DECLARE tprofile cursor for
    SELECT zp.profile_name,zce.config_value FROM zydesk_profile zp
    LEFT JOIN zydesk_config_entries zce ON zp.profile_id=zce.owner_id
    WHERE config_key=1043 AND  ISNUMERIC(zce.owner_id) = 1 and LEN(CONVERT(nvarchar,zce.config_value)) > 0
    OPEN tprofile
    FETCH NEXT FROM tprofile INTO @curr_prof,@curr_val
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @start_profrow = 1
    set @stop_profrow = 1
    WHILE @stop_profrow > 0
    begin
    set @stop_profrow = charindex(';',@curr_val,@start_profrow+1)
    IF @stop_profrow = 0
    SET @curr_line = substring(@curr_val,@start_profrow,LEN(@curr_val))
    ELSE
    SET @curr_line = substring(@curr_val,@start_profrow,@stop_profrow - @start_profrow)
    set @start_entry = 1
    set @stop_entry = charindex(',',@curr_line,@start_entry+1)
    set @ttype = substring(@curr_line,@start_entry,@stop_entry - @start_entry)
    set @start_entry = @stop_entry + 1
    set @stop_entry = charindex(',',@curr_line,@start_entry+1)
    set @tid = substring(@curr_line,@start_entry,@stop_entry - @start_entry)
    set @start_entry = @stop_entry + 1
    set @stop_entry = charindex(',',@curr_line,@start_entry+1)
    set @tdescr = substring(@curr_line,@start_entry,@stop_entry - @start_entry)
    set @start_entry = @stop_entry + 1
    set @stop_entry = charindex(',',@curr_line,@start_entry+1)
    set @tobg = substring(@curr_line,@start_entry,@stop_entry - @start_entry)
    set @start_entry = @stop_entry + 1
    set @stop_entry = charindex(',',@curr_line,@start_entry+1)
    set @tofg = substring(@curr_line,@start_entry,len(@curr_line))
    set @tbg = CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), @tobg),2)
    set @tfg = CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), @tofg),2)
    INSERT INTO @toutput VALUES (@curr_prof,@ttype,@tid,@tdescr,@tobg,@tofg,right(@tbg,6),right(@tfg,6))
    SET @start_profrow = @stop_profrow + 1
    END
    FETCH NEXT FROM tprofile INTO @curr_prof,@curr_val
    END
    CLOSE tprofile
    DEALLOCATE tprofile
    SELECT * FROM @toutput
    ORDER by profile, type, identifier, description
    --

  3. Replace the second word ZyDB with the name of your ZyDB database

  4. Paste the code into SQL Server Management Studio, and click Execute (or press F5)

  5. You should now see a list of appointment categories, with foreground colors and background colors for each category

  6. In the Administration Portal menu, select CLIENTS > Appointment Categories

  7. Enter the appointment categories from the list that you extracted

    If your origination hasn’t changed the appointment categories, it may be enough to click Add Defaults to use the default settings

  8. Click Save