Replicating user profile

[Scott.Pearce]

There are two tables involved:

1) SY_TabControlSettings - These are settings that relate to the tab control as a whole.
2) SY_TabSettings - These are settings that relate to an invidual tab.

Here are my observations using a tab with no entries in either table:

If you choose "Use Custom Tabs" for a tab strip, you get an entry placed SY_TabControlSettings, but a blank TabOrderXML value. You also get an entry for each tab in the tab control inserted into the SY_TabSettings table.

As soon as you change the tab order, a value for TabOrderXML is placed into SY_TabControlSettings for that tab control. TabOrderXML contains ordering information for ALL tabs that make up that tab control.

Entries in these tables are not deleted at any point, i.e. if you untick "Use Custom Tabs", the entries remain in the tables.

So, I believe the answer to your question is "everything that could potentially be customised is stored."

[pricerc]

There are two tables involved:

1) SY_TabControlSettings - These are settings that relate to the tab control as a whole.
2) SY_TabSettings - These are settings that relate to an invidual tab.

Here are my observations using a tab with no entries in either table:

If you choose "Use Custom Tabs" for a tab strip, you get an entry placed SY_TabControlSettings, but a blank TabOrderXML value. You also get an entry for each tab in the tab control inserted into the SY_TabSettings table.

As soon as you change the tab order, a value for TabOrderXML is placed into SY_TabControlSettings for that tab control. TabOrderXML contains ordering information for ALL tabs that make up that tab control.

Entries in these tables are not deleted at any point, i.e. if you untick "Use Custom Tabs", the entries remain in the tables.

So, I believe the answer to your question is "everything that could potentially be customised is stored."

Maybe...

If SY_TabControlSettings is 'form-specific', and an entry is only created when you customise a screen, then we shouldn't have any 'uncustomised' screens with an entry in SY_TabControlSettings. (assuming a new 'template' user is being used that has no pre-existing customisations).

I'm wanting to tweak the 'shared profile', so that we can customise the columns on a few key screens (mostly sales orders and purchase orders), but leave most of the others to individual users. The current plugin code wipes out the (current) user's settings before loading the 'shared' profile.



... but hang on. Are column orders saved in those two tables? I'm not seeing anything that looks like a column order ?

[Scott.Pearce]

column orders

These two tables are for tab customisations only. Not grids.

Grid information is stored in SY_UserProfile, and information pertaining to the layout of the *entire* grid, and all it's columns, is stored. So if the user only modifies the caption of 1 column, XML defining the layout of the entire grid is stored.

[pricerc]

Ok,

so I got my wires crossed a bit. While the tabs are interesting, the columns are more interesting.

The plugin currently wipes out the 'loaded' profile, and then reads the 'template' profile into the 'loaded' profile:

Code: Select all

string sql = @"SELECT Section, IDKey, Contents FROM SY_UserProfile WHERE UserID = @ModelUserID ORDER BY Section, IDKey";
        using (var cmd = new SqlCommand(sql, db.SQLConnection, db.SQLTransaction))
...
                Plugin.Manager.Database.UserSettings.Clear();
...
                while (reader.Read())
                {
                    db.UserSettings.Add(new JiwaFinancials.Jiwa.JiwaODBC.clsUserSetting()
                    {
                        Section = db.Sanitise(reader, "Section").ToString(),
                        IDKey = db.Sanitise(reader, "IDKey").ToString(),
                        Contents = db.Sanitise(reader, "Contents").ToString()
                    });
                }



Can you see a problem with removing the "UserSettings.Clear()" and replacing the "UserSetttings.Add" with logic that adds or replaces as necessary, leaving alone anything that's missing from the 'template' profile? (e.g. if the template only has customisations for sales and purchases, but a user has customisations for debtors, I don't want their debtor customisation being wiped out, but I do want the sales and purchases 'standardised').

[Mike.Sheen]

Can you see a problem with removing the "UserSettings.Clear()" and replacing the "UserSetttings.Add" with logic that adds or replaces as necessary, leaving alone anything that's missing from the 'template' profile?

That should be fine. UserSettings in this case is an ugly duckling that inherits from BindingList. We have implemented an Exists method - which returns a boolean for given Key - note that the matching is case sensitive - but I wouldn't use that - using the Item method with a given Key will either return null or the item with a matching key (case sensitive) - that should be good enough for your SetOrAdd method you want to implement.

[pricerc]

Can you see a problem with removing the "UserSettings.Clear()" and replacing the "UserSetttings.Add" with logic that adds or replaces as necessary, leaving alone anything that's missing from the 'template' profile?

That should be fine. UserSettings in this case is an ugly duckling that inherits from BindingList. We have implemented an Exists method - which returns a boolean for given Key - note that the matching is case sensitive - but I wouldn't use that - using the Item method with a given Key will either return null or the item with a matching key (case sensitive) - that should be good enough for your SetOrAdd method you want to implement.

Thanks Mike.

By the power of lambda, I am going to go with:

Code: Select all

string sql = @"SELECT Section, IDKey, Contents FROM SY_UserProfile WHERE UserID = @ModelUserID ORDER BY Section, IDKey";

...

while (reader.Read())
{
    string section = (string)reader["Section"];
    string idKey = (string)reader["IDKey"];
    string contents = (string)reader["Contents"];

    var existingSetting = db.UserSettings.FirstOrDefault(u => string.Equals(u.Section, section, StringComparison.OrdinalIgnoreCase) && string.Equals(u.IDKey, idKey, StringComparison.OrdinalIgnoreCase));
    if (existingSetting != null)
    {
        existingSetting.Contents = contents;
    }
    else
    {
        db.UserSettings.Add(new JiwaFinancials.Jiwa.JiwaODBC.clsUserSetting()
        {
            Section = section,
            IDKey = idKey,
            Contents = contents
        });
    }
}


I'm looking over some SQL for the tab control bit over a cup of tea before I test the above. I'm figuring I'll be able to use a pair of MERGE statements (one for each table).

[pricerc]

Code: Select all

        // Merge SY_TabControlSettings and SY_TabSettings
        sql = @"-- Merge SY_TabControlSettings
                    with source as (
                       select RecID, HR_Staff_RecID, Context, FullName, UseCustomTabs, TabOrderXML
                       from dbo.SY_TabControlSettings tcs
                       where tcs.UseCustomTabs = 1 and tcs.HR_Staff_RecID = @modelStaffID
                    )
                    merge dbo.SY_TabControlSettings target
                    using source
                    on source.FullName = target.FullName
                       and source.Context = target.Context
                       and target.HR_Staff_RecID = @targetStaffID
                    when matched and target.TabOrderXml <> source.TabOrderXML
                       then update set target.TabOrderXml = source.TabOrderXML
                    when not matched then insert(RecID, HR_Staff_RecID, Context, FullName, UseCustomTabs, TabOrderXML)
                       values (NEWID(), @targetStaffID, Context, FullName, UseCustomTabs, TabOrderXML);

                    -- Merge SY_TabSettings
                    with source as (
                       select ts.RecID, SY_TabControlSettings_RecID, TabKey, Text, Visible, tcst.RecID as Target_TabControlSettings_RecID
                       from dbo.SY_TabSettings ts
                          inner join dbo.SY_TabControlSettings tcs on tcs.RecID = ts.SY_TabControlSettings_RecID and tcs.HR_Staff_RecID = @modelStaffID
                          inner join dbo.SY_TabControlSettings tcst on tcst.HR_Staff_RecID = @targetStaffID and tcst.Context = tcs.Context and tcst.FullName = tcs.FullName
                       where tcs.UseCustomTabs = 1
                    )
                    merge dbo.SY_TabSettings target using source on target.TabKey = source.TabKey
                    and target.SY_TabControlSettings_RecID = source.Target_TabControlSettings_RecID
                    when not matched then insert(RecID, SY_TabControlSettings_RecID, TabKey, Text, Visible)
                       values(newid(), source.Target_TabControlSettings_RecID, TabKey, Text, Visible)
                    when matched and source.Text <> target.Text or source.Visible != target.Visible
                       then update set target.Text = source.Text, target.Visible = source.Visible;";

        using (var cmd = new SqlCommand(sql, db.SQLConnection, db.SQLTransaction))
        {
            cmd.Parameters.Add(new SqlParameter("@targetStaffID", SqlDbType.VarChar)).Value = staffId;
            cmd.Parameters.Add(new SqlParameter("@modelStaffID", SqlDbType.VarChar)).Value = modelStaffId;

            db.ExecuteNonQuery(cmd);
        }


I'll post an updated plugin when I've tested it.

[pricerc]

So here's my updated plugin.

It provides two layers of 'shared profile' that can be used:
1) a 'global' profile, as in Mike's original sample, specified in System Configuration, and/or
2) a 'user' profile, specified in Staff Maintenance using a custom field.

The 'global' profile can also be excluded at a user level by making the member of a user group, that you specify in System Configuration.

Instead of replacing the user's profile, this one will merge it. So if the shared profile only has customisations in, say, sales orders, it won't wipe out a user's personal customisations in debtors.

I've only done some basic testing so far, but it appears to be doing what I was expecting.