VYUtilAPI Package

VYUtilAPI Package

The VYUtilAPI package includes a number of useful functions for custom solution developers.

It doesn't contain functions that can change anything in the database or get any object data.

VINEYARDUSER is granted EXECUTE on this package.

DBToClientGroupID

Syntax:

[VINEYARDDB].VYUtilAPI.DBToClientGroupID(
GroupIDINNUMBER);

OR

[VINEYARDDB].VYUtilAPI.DBToClientGroupID(
GroupIDINNUMBER,
ChangeUserINVARCHAR2,
ObjectTypeINVARCHAR2,
ObjectIDINNUMBER,
);

Return type: NUMBER

Description: The function translates the showgroup/editgroup database values into values which can see the current user of a Vine client:

• If GroupID is 0 (or the ID of the organization of the current user) then 0 is returned which means that the object is visbale to everyone. (Shown as ‘ALL’ in client).

• If the GroupID value is equal to the current user ID then -1 will be returned. (This is shown as ‘Self’ in client.)

• Otherwise, the function checks if the current user is in the group specified by GroupID and, if yes, the GroupID value will be returned.

• A special case is when GroupID is equal to -3. In that case, the function will check whether the user is connected to the object already or is the object’s current CHANGEUSER. In this case the function will return -3.

• If all the checks fail then -2 will be returned, which indicates that the user has no right to edit the object or its connections.

ClientToDBGroupID

Syntax:

[VINEYARDDB].VYUtilAPI.ClientToDBGroupID(
GroupIDINNUMBER);

Return type: NUMBER

Description: The function is the opposite to DBToClientGroupID and translates the showgroup/editgroup value shown in client into value that can be saved to the database:

GetPackageVersion

Syntax:

[VINEYARDDB].VYUtilAPI.GetPackageVersion;

Return type: NUMBER

Description: The function returns the version number of the VYUtilAPI package.

Parameters: None

GetUserSetting

Syntax:

[VINEYARDDB].VYUtilAPI.GetUserSetting (
UserIDINNUMBER,
SettingNameINVARCHAR2
);

Return type: VARCHAR2

Description: The function returns the string value of a user's named setting from the VY_USERSETTING table.

To get a system setting the function should be called with UserID=0.

Parameters:

UserID: A user's ID.

SettingName: The name of a setting.

Example:

declare

Recepientsvarchar2(50);

begin

...

Recepients:=vineyarddb.vyutilapi.getusersetting(0,'VY_Report Recepients');

...

end;

GetParameterName

Syntax:

[VINEYARDDB].VYUtilAPI.GetParameterName (
ParameterTableINVARCHAR2,
ParameterIDINNUMBER
);

Return type: VARCHAR2

Description: The function returns the name of a parameter which is specified by its ID and parameter table.

Parameters:

ParameterTable: The name of a parameter table, such as VY_PROJECTPHASE, VY_EVENTTYPE, etc.

ParameterID: The ID of a parameter.

Example: The following SELECT will list the names of all projects and their phases for the connected user.

SELECT name, VineyardDB.VYUtilAPI.GetParameterName('VY_PROJECTPHASE', ProjectPhase) Phase
FROM vineyarddb.vy_project_view
WHERE id in (select targetobjectid from vineyarddb.vy_connection_view WHERE connectiontype=20 and fromobjectid=VineyardDB.VYUtilAPI.UserID and status='B') and status = 'B';

NAMEPHASE

---------------------------------

Kirkland SQ 44Active

Summer '99Ready

Sales DevelopmentActive

GetConnectionTypeName

Syntax:

[VINEYARDDB].VYUtilAPI.GetConnectionTypeName (
ConnectionTypeIDINNUMBER,
FromObjectIDINNUMBER,
TargetObjectIDINNUMBER,
DirectionObjectIDINNUMBER
);

Return type: VARCHAR2

Description: The function returns the name of the chosen end of a connection type. The connection type is specified by its ID. The returned end is specified by the From, Target, Direction parameters as follows:

If Direction = FromID then the connection's direction is considered as "From" à "Target" and the function returns the name of the "Target" end.

If Direction = TargetID then the connection's direction is considered as "From" ß "Target" and the function returns the name of the "From" end.

Parameters:

ConnectionTypeID: The ID of a connection type.

FromObjectID: The ID of the object which is the "From" object in the connection.

TargetObjectID: The ID of the object which is the "Target" object in the connection. Reserved for future use.

DirectionObjectID: The ID of the object from which we are looking at the connection.

Example: If the database has the following connection types defined in the VY_CONNECTIONTYPE table:

ID   FROMNAME   TARGETNAME   FROMOBJECTTYPE   TARGETOBJECTTYPE
-----------------------------------------------------------

1    Employee    Employer      VY_COMPANY        VY_PERSON

18   Project     Client        VY_COMPANY        VY_PROJECT

And assuming that the database has a company with ID=300000022 and a few persons and a project connected to it, then the following SELECT on the VY_CONNECTION table will give the output below:

SELECT fromobjecttype, targetobjecttype, targetobjectid, connectiontype, VineyardDB.VYUtilAPI.GetConnectionTypeName(connectiontype, fromObjectID, targetObjectID, fromObjectID) ConnectionEndName
FROM vineyarddb.vy_connection_view
WHERE fromobjectid = 300000022 and status = 'B';

FROMOBJECTTYPE TARGETOBJECTTYPE TARGETOBJECTID CONNECTIONTYPE CONNECTIONENDNAME
--------------------------------------------------------------------------------

VY_COMPANY      VY_PERSON        300000434          1         Employer

VY_COMPANY      VY_PERSON        300000435          1         Employer

VY_COMPANY      VY_PERSON        300000438          1         Employer

VY_COMPANY      VY_PERSON        300000020          1         Employer

VY_COMPANY      VY_PROJECT       300000162         18         Client

IsUserInGroup

Syntax:

[VINEYARDDB].VYUtilAPI.IsUserInGroup (
GroupSettingINNUMBER,
CreateUserINVARCHAR2
);

Return type: NUMBER

Description: The function is usually used to check an object's SHOWGROUP/EDITGROUP settings and to decide whether the current user is permitted to view/edit the object created by the user specified by the CreateUser parameter.

• If GroupSetting is equal to 0 or NULL, i.e. the object is not protected, then the function returns 1 without any checks.

• If the GroupSetting value is equal to the current user ID then the user is the owner of the object and the function returns 1.

• If GroupSetting is equal to -1 (-1 can be returned as the SHOWGROUP/EDITGROUP value in views) then the name of CreateUser is compared with the name of the current user, if they are the same then the function returns 1.

• If GroupSetting is the ID of a Vine user group then the function returns 1 if the current user is included in that user group..

Parameters:

GroupSetting: An object's SHOWGROUP/EDITGROUP setting.

CreateUser: The name of the user who created the object. Needed only if GroupSetting = -1.

IsUserInGroup

Syntax:

[VINEYARDDB].VYUtilAPI.IsUserInGroup (
UserIDINNUMBER,
GroupSettingINNUMBER,
CreateUserINVARCHAR2
);

Return type: BOOLEAN

Description: The function is an overloaded version of the function described previously. Its only difference is that instead of using the current user ID, it can work with any UserID which it gets as a parameter.

The other difference that it returns boolean TRUE and FALSE instead of numerical values 1 and 0.

MakeQueryWord

Syntax:

[VINEYARDDB].VYUtilAPI.MakeQueryWord (
StringINVARCHAR2
);

Return type: VARCHAR2

Description: The function prepares a "queryname" string to be stored in the VY_CONTENTS_MV and VY_INDEX tables.

Parameters:

String: A text string.

PCToDBtime

Syntax:

[VINEYARDDB].VYUtilAPI.PCToDBtime ( TimeString   IN   VARCHAR2 );

Return type: VARCHAR2

Description: The function converts a time string from the PC format into the database format.

Parameters:

TimeString: A time string that will be converted as follows:

"H:MM:SS"->"0HMM"
"HH:MM:SS"->"HHMM"
"HHMMSS"->"HHMM"

PhoneNumberToSearchIndex

Syntax:

[VINEYARDDB].VYUtilAPI.PhoneNumberToSearchIndex (
PhoneStringINVARCHAR2
);

Return type: VARCHAR2

Description: The function takes a phone string, reverses it skipping all non-numeric characters and then returns it.

'[phone]' -> '98765432199944'

Parameters:

PhoneString: A phone string that can include not only numbers.

OnlyNumbers

Syntax:

[VINEYARDDB].VYUtilAPI.OnlyNumbers (
StringINVARCHAR2
);

Return type: BOOLEAN

Description: The function returns FALSE if an input string has at least one non-numeric character.

Parameters:

String: A text string.

Reverse

Syntax:

[VINEYARDDB].VYUtilAPI.Reverse (
StringINVARCHAR2
);

Return type: VARCHAR2

Description: The function reverses a string.

Parameters:

String: A text string.

UserIDToName

Syntax:

[VINEYARDDB].VYUtilAPI.UserIDToName ( ID   IN   NUMBER );

Return type: VARCHAR2

Description: The function takes a user's ID and returns the corresponding user's name from the VY_USER table.

Parameters:

ID: A user's ID.

UserNameToID

Syntax:

[VINEYARDDB].VYUtilAPI.UserNameToID ( Name   IN   VARCHAR2 );

Return type: NUMBER

Description: The function takes a user's name and returns the user's ID from the VY_USER table.

Parameters:

Name: A user's name.

UserID

Syntax:

[VINEYARDDB].VYUtilAPI.UserID ;

Return type: NUMBER

Description: The function returns the ID of the current user.

Parameters: None

Example: See the example for the GetParameterName fuction.