Restart HR.Net services

HR.Net is full of WTF moments. This whole blog is based on these moments: from ancient version of jQuery to obscure bugs in places where “things should just work”.

This time I run into Workflow service that sometimes gets stuck: the actual windows service gets hanged and stop responding and processing any new workflows. But the whole system heavily depends on workflows being processed in a timely manner, the system just crashes and I have a lot of unhappy customers calling me in panic. The fix is simple – restart the workflow service. Though the fix does not show signs of life – it works. HR.Net queues workflows in a table and the service picks up one workflow at a time and runs it. Usually by the time I get a phone call there are hundreds of unprocessed workflows already and the service needs some time to crunch through them. This bit of SQL will show you how many workflow instances are there

select count(*) from OC_WORKFLOWINSTANCES
where STARTEDDATETIME > '2017-07-10 00:00:00.000'

replace the date with your current date to show how many have been queued during your day.

Solution to this problem is to restart HR.Net Workflow Service regularly. Do this via commands:

net stop "HR.net Workflow Service"
net Start "HR.net Workflow Service"

Put this as a scheduled task and be done with this. I’ve scheduled this command to be executed every hour, but I suggest you start from every 24 hours. I already had HR.Net Reports service restarted every hour because it caused problems too. And I’ve included workflow service there as well to save myself some typing -)

So my whole bat file looks like this:

@@ECHO OFF

ECHO ***STOPPING HR.NET SERVICES***
net stop "HR.net Reports Service"
net stop "HR.net Workflow Service"

ECHO ***STARTING HR.NET SERVICES***
net Start "HR.net Reports Service"
net Start "HR.net Workflow Service"

EXIT
Posted in Uncategorized | Tagged , , | 4 Comments

Find workflows/mailers with particular subject

Sometimes I get support requests quoting an email received from HR.Net and asking to modify that email text or add more recipients. And then I need need to find that workflow containing the mailer that sent out that particular message. And given thousands of workflows, it can take days to find the exact workflow (unless you know exactly where to look for)

So I wrote this little SQL query that matches email subject to a workflow name/folder:


select 
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
	flow.workflowname,
	flow.description,
	flow.allowstart
 from OC_WORKFLOWMAILER mailer
  inner join OC_WORKFLOWS flow on mailer.workflowid = flow.workflowid
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where mailer.htmlsubject like '%my subject%'
Posted in Uncategorized | Tagged , , | Leave a comment

Custom Script Validation on Screens… Again!

HR.Net is a mysterious beast! Something may work in one place, but same copy-pasted might not work in the other place. Last couple hours I have spent fighting Screen Validation again. I have already written about this feature a few times and this time none of these methods worked for me, even though I have not done anything different. No idea what happened with HR.Net and why old versions work in old screens, but not in new screens. Because HR.Net is full of shitty bugs, sometimes javascript function Page_ClientValidate is not available on a screen – I noticed that it disappears as soon as you add a grid to a screen. And in all my previous validations I was relying on this function to be present and called by the framework (“Page_ClientValidate” function which is provided by underlying .Net framework).

So after a lot of detective work I have found that save button triggers event HRnet.Form.UI.Buttons.Save.action and I could override this function. But “Save And Close” button was triggering different event HRnet.Form.UI.Buttons.SaveAndClose.action, so both of these should be overriden if both buttons are available.

Here is the basic script that ensures that some fields are filled in on the screen:

// override click on Save button
(function() {
  var proxied = HRnet.Form.UI.Buttons.Save.action;
  HRnet.Form.UI.Buttons.Save.action = function() {
    
    if(MyValidate() === false){
        return false;
    }
    
    return proxied.apply( this, arguments );
  };
})();

// override click on Save And Close button
(function() {
  var proxied = HRnet.Form.UI.Buttons.SaveAndClose.action;
  HRnet.Form.UI.Buttons.SaveAndClose.action = function() {
    
    if(MyValidate() === false){
        return false;
    }
    
    return proxied.apply( this, arguments );
  };
})();



function MyValidate(){
    var field1 = $HRnet("Field1").getDisplayValue();
    var field2 = $HRnet("Field2").getDisplayValue();
    var field2 = $HRnet("Field3").getDisplayValue();

    var ErrMsg ="";
    var validate = true;

    if (isBlank(field1)){
        ErrMsg += "You need to fill in 'Field 1'\n";
        validate = false;
    }

    if (isBlank(field2)){
        ErrMsg += "You need to fill in 'Field 2'\n";
        validate = false;
    }
   
    if (isBlank(field3)){
        ErrMsg += "You need to fill in 'Field 3'\n";
        validate = false;
    }
            
    if (!validate){
        alert(ErrMsg);
        return validate;
    }
}


function isBlank(val){
    if(val==null){
        return true;
    }
    for(var i=0;i
							
Posted in Uncategorized | Tagged , , | 2 Comments

Java Screen Field Validation

It has been a while since I had to do JavaScript in screens. And even longer since I done a validation on the screen. Since then there were changes in HR.Net which I have missed. And my old way to validate screens no longer works. But the fix is simple enough – all you need to do is add a parameter to the old script:

var orig_ValidatorOnSubmit = window.ValidatorOnSubmit;

window.ValidatorOnSubmit = function(validationGroup){
  var ErrMsg ='';
  var validate = true;
 
  // your custom validation goes here
	if (!validate){
          alert(ErrMsg);
		return false
	}
	return orig_ValidatorOnSubmit(validationGroup);
}
Posted in Uncategorized | Tagged , | 1 Comment

Find all users in a role

You know, HR.Net users have a primary role that defines their level of access to the data. Also there are many additional roles that allow to do other stuff, like using of navigator. But additional roles don’t give any data-permissions. That’s the way HR.Net security is done.

And sometimes you need to see all the users with a specific additional role. Here is the script:

select 
    OC_USERS.USERID, 
    primaryRole.ROLENAME as PrimaryRole
from [dbo].[OC_ROLELANGUAGES] 
    inner join OC_USERROLES on OC_ROLELANGUAGES.ROLEID = OC_USERROLES.ROLEID
    inner join OC_USERS on OC_USERS.USERID=OC_USERROLES.USERID
    inner join [OC_ROLELANGUAGES] primaryRole on primaryRole.ROLEID = Oc_users.ROLEID
where OC_ROLELANGUAGES.rolename='My Role'
order by primaryRole.ROLENAME
Posted in Uncategorized | Tagged , | Leave a comment

Updating of tables ends up with a messy error message about incompatible type

After modifying one of tables in C.Net I ended up with an error message saying “Incompatible type blah-blah-blah” on table save (sorry, don’t have exact words or screenshot). Turned out that I have changed type of one of the fields (from computed Text to Pick-List). This drove mad the security permission model and HR.Net could not re-generate the security views it uses in the background.

To figure out what exact permission was causing issues I came up with this script:

select 
    vs.name,
    definition
from sys.objects o
    join sys.sql_modules m on m.object_id = o.object_id
    cross join sys.views vs 
where o.object_id = object_id(vs.name)
  and o.type = 'V'
  and vs.name like '%[_]tablename[_]%'

This script outputs a list of all views generated by the system and outputs actual SQL that is behind the view. That way I could easily identify what role and what permission was causing the issue and could modify.

I was lucky I remembered the table name I modified, otherwise I would have to look through a gazillion of SQL Views and figure out which one is causing issues to fragile internals of HR.Net

Posted in Uncategorized | Tagged , | Leave a comment

Convert Report to be Landscape

Today I had to make a new report in HR.Net Document Explorer. Unfortunately version of HR.Net I’m using is buggy.

Every time you try to view a report preview, it crashes:

2014-11-03 00_54_03

Vizual claims that this error is because I’m running 64-bit OS, but their software is only supposed to work on 32-bit. But if I create a sub-report, I can run a preview on that. I think lousy coding standards are at fault here and nothing to do with 32/64-bit compatibility. And Vizual support just tries to make themselves look less bad.

Anyway, after some poking about, I have found a database table where these report definitions are stored as XML. And we can manipulate XML as we wish! One of the properties report XML-definition is <Orientation>. It is set to 1 for Portrait page layout and 2 for Landscape (I did compare different reports). So now all we have to do is replace this value in the database:

update [dbo].[OC_REPORTS_REPORTDEFINITIONS]
set DEFINITIONDATA = cast(REPLACE(cast(DEFINITIONDATA as nvarchar(max)), '1', '2') as ntext)
from [dbo].[OC_REPORTS_REPORTDEFINITIONS] repdef
   inner join OC_DOCUMENTS docs on repdef.DOCUMENTID = docs.DOCUMENTID
where docs.DOCUMENTNAME = 'My Report Name'

Replace “My Report Name” with name of your report. Before running this query, please make a copy of your report – there is no guarantee that this will work, also there is a chance that your report might be messed up beyond recognition.

Also if you have sub-reports, they also will become Landscape. If this is your goal, then no problem. If not, you’ll have to go through all sub-reports and modify them back – you are in luck here. Document Console allows to modify the sub-report orientation without throwing errors.

Posted in Uncategorized | Tagged , , | 5 Comments

Find a Report By Name

It’s been a while since the last post. But the blog is not dead, I keep working with HR.Net and when I find good enough reason to post here, I do.

Here is the new gem I had to discover. In attempt to delete an unused column from an unused table, I was present with dialog saying “Can’t delete field, it is used in a report”. Good on Vizual that they are showing reports that depend on fields. But how they do it:

2014-10-20 00_08_50-Ts - ts.cornerstone.org.uk - Remote Desktop Connection

Yep, thanks. This field is used in report “Test Blah”. But where the hell is this report? In the system I support just now there are over a thousand of reports with about a hundred of folders.

So this script will help you show the location of a report in your system:

select 
    pppf.foldername as [Folder],
    ppf.foldername as [Folder],
    pf.foldername as [Folder],
    f.foldername as [Folder],
    d.*
from OC_DOCUMENTS d
	left join dbo.OC_DOCUMENTFOLDERS f on d.folderid = f.folderid
	left join dbo.OC_DOCUMENTFOLDERS pf on f.PARENTFOLDERID = pf.folderid
	left join dbo.OC_DOCUMENTFOLDERS ppf on pf.PARENTFOLDERID = ppf.folderid
	left join dbo.OC_DOCUMENTFOLDERS pppf on ppf.PARENTFOLDERID = pppf.folderid
where d.DOCUMENTNAME = 'Test Blah'
Posted in Uncategorized | Tagged , | Leave a comment

Find all workflows that use a picklist, by picklist name

One of my readers did ask if there is a way to find all the workflows that use a particular picklist.
A little bit of digging and here you go:

select 
    pppf.folder_name as [Folder],
    ppf.folder_name as [Folder],
    pf.folder_name as [Folder],
    f.folder_name as [Folder],
    w.WORKFLOWNAME, 
    pl.SHORTDESCRIPTION as PicklistName 
from OC_WORKFLOWS w
	inner join OC_SCREENS s on w.SCREENID = s.SCREENID
	inner join OC_SCREENCONTROLS sc on s.SCREENID = sc.SCREENID
	inner join OC_PICKLISTS p on p.PICKLISTID = sc.WORKFLOWPICKLISTID
	inner join OC_PICKLISTLANGUAGES pl on pl.PICKLISTID = p.PICKLISTID
	left join OC_ADMINCONSOLE_FOLDERS f on w.folderid = f.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where pl.SHORTDESCRIPTION = 'Picklist Name'
Posted in Uncategorized | Tagged , , , , | Leave a comment

Lost a picklist? Find it with a query

Many times over I did spend a lot of time looking for a particular picklist. I know the name, but have no idea what folder it is in. And if you have more than just a few of them, it is sometimes impossible to find what you are looking for without help. Here is how you find a picklist by a name:

select 
  pppf.folder_name as [Folder],
  ppf.folder_name as [Folder],
  pf.folder_name as [Folder],
  f.folder_name as [Folder],
  pickLang.SHORTDESCRIPTION
from OC_PICKLISTS pick
	inner join OC_PICKLISTLANGUAGES pickLang on pick.PICKLISTID = pickLang.PICKLISTID
  left join OC_ADMINCONSOLE_FOLDERS f on pick.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where pickLang.SHORTDESCRIPTION = 'Picklist Name'
Posted in Uncategorized | Tagged , | 3 Comments