Trying to pull in the time a job was closed to a Job Closing BAQ and am having difficulty doing so. I can see the job close time in the change log, so I know it is stored, but I am not sure how to pull it into the BAQ.
Ideas?
You can pull the Ice.ChgLog table into your BAQ.
This is on due date, but should get you started.
To get the log in one string, so you can parse it do a replace like this.
Criteria like this but with Closed
then once you have the cleanlog you need to use something like carindex and substring to get the time from the string.
1 Like
Thank you - giving this a shot now
Got this info pulled in now, thanks to your guidance, thank you - another quick question. If I wanted to trim out all of the extra details and only include the time in standard (non military) format. Any ideas how to do that?
I could not parse to that level with my sql skills so I made the baq updatable and the below code routine post processing on GetList to get my dates. You can do something similar to get the time out.
GetDatesFromLog.txt (2.1 KB)
If I can remember to dig it up, I made a change log parser a long time ago.
3 Likes
Here it is, might need some touch up to remove that one line of reflection.
This was a Post-Processing on a GetList in a BAQ.
No idea how this works anymore lol.
bool DEBUG = false;
string retString = "";
string nl = Environment.NewLine;
Action<Dictionary<string, string>> BuildFieldLabelDefs = (dictFields) =>
{
//Get the Query ID --> This is just so you don't have to hard code it.
string queryID = this.GetType().Module.ToString(); //this.GetType().Module.ToString() returns "[COMPANYID]_[QUERYNAME].[BPMAction].dll"
queryID = queryID.Replace(Session.CompanyID + "_", ""); //Replace "[COMPANYID]_" with nothing
queryID = queryID.Split('.')[0]; //Split value into two parts on ".", select index 0, [QUERYNAME]
Guid queryIDSubQueryGUID = (from subQ in Db.QuerySubQuery
where
subQ.QueryID == queryID &&
subQ.Type == "TopLevel"
select
subQ.SubQueryID).FirstOrDefault();
var colLabels = from col in Db.QueryField
where
col.QueryID == queryID
select
col;
//Build Dictionary
foreach(var fld in colLabels)
{
if(fld.SubQueryID == queryIDSubQueryGUID)
{
dictFields.Add(fld.TableID + "_" + fld.FieldName, fld.FieldLabel);
}
}
};
Dictionary<string, string> dictFieldLabels = new Dictionary<string, string>();
BuildFieldLabelDefs(dictFieldLabels);
foreach(var ttResult in result.Results)
{
foreach(var col in result.Results.Columns)
{
if(col.ColumnName.Contains("_") == true)
{
try
{
string outString = ttResult.Calculated_LogText;
foreach(var fld in dictFieldLabels)
{
outString = outString.Replace(fld.Key.Replace(fld.Key.Split("_")[0] + "_", ""), fld.Value);
}
ttResult.Calculated_LogText = outString;
} catch {}
}
}
}
foreach(var ttResult in result.Results)
{
string temp = ttResult.Calculated_LogText;
List<string> dateRecords = temp.Split("|||").ToList();
SortedDictionary<DateTime, string> dictDateRecords = new SortedDictionary<DateTime, string>();
foreach(var dRec in dateRecords)
{
string[] dictItem = dRec.Split(">>>");
dictDateRecords.Add( DateTime.Parse(dictItem[0]), dictItem[1]);
}
temp = "";
var dictCnt = 0;
foreach(var item in dictDateRecords.Reverse())
{
//temp += $"{item.Key.ToShortDateString()} {item.Value}";
string dictItemOutString = item.Value;
List<string> clRecords = dictItemOutString.Split("\n\n").ToList(); //Split records on '\n\n' (Double New Lines)
List<string> clRecordsFixed = new List<string>(); //The records after processing
foreach(var rec in clRecords)
{
List<string> recLines = rec.Split("\n").ToList(); //Split lines on '\n' (New Line)
List<string> newRecLines = new List<string>(); //The lines after processing
int xCnt = 0; //We use this to skip touching up the first "New Record"
foreach(var line in recLines)
{
newRecLines.Add(line.Contains("New Record") == true && xCnt > 0 ? $"\n\t{line}" : $"\t{line}"); //Add an extra \n to the front of "New Record" (Unless it's the first line)
xCnt++;
}
//Join lines with '\n' (New Line) and add back to "Fixed" Records List
clRecordsFixed.Add(string.Join("\n", newRecLines));
}
//Reverse the list
IEnumerable<string> reversedList = clRecordsFixed.ToArray().Reverse();
//Join everything back with '\n\n' (Double New Lines)
dictItemOutString = string.Join("\n\n", reversedList);
string addNL = dictCnt > 0 == true ? "\n" : "";
temp += $"{addNL}{item.Key.ToShortDateString()}\n\n{dictItemOutString}\n";
dictCnt++;
}
ttResult.Calculated_LogText = temp.Replace("\n\t\n", "\n");
}
if(DEBUG == true)
{
this.PublishInfoMessage(retString, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
}
1 Like