/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
List <Document> unityDocs = new List <Document>();
unityDocs.Add(_currentDocument);
PrintQueue printQueue = app.Core.PrintManagement.PrintQueues.Find(printQ);
PrintRequestProperties printReqProp = app.Core.PrintManagement.CreatePrintRequestProperties(unityDocs, printQueue);
PrintJob printJob = app.Core.PrintManagement.EnqueuePrintRequest(printReqProp);
app.Diagnostics.Write("Print Job Sent");
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Application # keywords for passing to LicEase database
KeywordType kwtAppNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamAppNum);
string strAppNum = "";
if (kwtAppNum != null)
{
KeywordRecord keyRecFileNum = _currentDocument.KeywordRecords.Find(kwtAppNum);
if (keyRecFileNum != null)
{
Keyword kwdFileNum = keyRecFileNum.Keywords.Find(kwtAppNum);
if (kwdFileNum != null)
{
strAppNum = CleanSeedKW(kwdFileNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
if ((strAppNum == "") || (strLicenseType == ""))
{
throw new Exception(string.Format("Either {0} or {1} is blank.", gParamAppNum, gParamLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT e.e_mail_addr ");
strSql.Append(@" FROM appl a, link l, clnt_link_typ clt, link_typ lt, email e ");
strSql.Append(@" WHERE a.xent_id = l.xent_id ");
strSql.Append(@" AND l.curr_ind = 'Y' ");
strSql.Append(@" AND l.clnt_link_typ_id = clt.clnt_link_typ_id ");
strSql.Append(@" AND clt.link_typ_id = lt.link_typ_id ");
strSql.Append(@" AND lt.link_typ_cde = 'MA' ");
strSql.Append(@" AND l.e_mail_id = e.e_mail_id ");
strSql.Append(@" AND a.clnt_cde = ");
strSql.Append(strLicenseType);
strSql.Append(@"' AND a.applc_nbr = '");
strSql.Append(strAppNum);
strSql.Append(@"'");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strEmail = "";
reader.Read();
strEmail = reader["EMAIL ADDRESS"].ToString();
Keyword kwdEmail = null;
if (!String.IsNullOrEmpty(strEmail))
{
KeywordType kwtEmail = app.Core.KeywordTypes.Find(gSaveToEmailAddress);
if (kwtEmail != null)
{
kwdEmail = CreateKeywordHelper(kwtEmail, strEmail);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdEmail != null)
{
keyModifier.AddKeyword(kwdEmail);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {3}added to Document {2}.",
gSaveToEmailAddress, strEmail, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
}
}
else
{
throw new Exception(string.Format("No records found in database for {0}='{1}' and {4}{2}='{3}' ", gParamLicType, strLicenseType, gParamAppNum, strAppNum, Environment.NewLine));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean InspectionID keyword for passing to LicEase database
KeywordType kwtInspectionID = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamInspectionID);
string strInspectionID = "";
if (kwtInspectionID != null)
{
KeywordRecord keyRecInspectionID = _currentDocument.KeywordRecords.Find(kwtInspectionID);
if (keyRecInspectionID != null)
{
Keyword kwdInspectionID = keyRecInspectionID.Keywords.Find(kwtInspectionID);
if (kwdInspectionID != null)
{
strInspectionID = CleanSeedKW(kwdInspectionID.ToString());
}
}
}
if (strInspectionID == "")
{
throw new Exception(string.Format("Search keyword {0} is blank. {3}, {4}, {5}", gParamInspectionID));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT a.insp_nbr, key_name, dba_name as dba_kw, a.insp_vst_id, a.lic_type AS lic_type, a.indorg_num, a.file_num, ");
strSql.Append(@" a.lic_num AS lic_num, a.visit_num, a.visit_date, a.insp_typ_desc, a.disposition_kw, city_kw, county_kw, region_kw, a.inspector_name ");
strSql.Append(@" FROM(SELECT DISTINCT TO_CHAR (insp_hist.insp_nbr) AS insp_nbr, TO_CHAR (insp_vst.insp_vst_id) AS insp_vst_id, lic.clnt_cde AS lic_type, lic.xent_id AS indorg_num, ");
strSql.Append(@" lic.lic_nbr AS lic_num, lic.file_nbr as file_num, TO_CHAR(insp_vst.insp_vst_nbr) AS visit_num, TO_CHAR (insp_vst.insp_vst_strt_dte) AS visit_date,");
strSql.Append(@" insp_typ_defn.insp_typ_desc, insp_disp_typ.insp_disp_typ_desc AS disposition_kw, stff.frst_nme || '.' || stff.surnme AS inspector_name, insp_hist.lic_id as insp_lic_id, ");
strSql.Append(@" (select max(key_nme) from name n, link k where k.link_id = insp_hist.link_id and k.nme_id = n.nme_id) as key_name, ");
strSql.Append(@" (SELECT MAX(key_nme) FROM NAME n, LINK k WHERE k.prnt_id = insp_hist.lic_id ");
strSql.Append(@" AND k.link_prnt_cde = 'L' AND k.curr_ind = 'Y' AND k.clnt_link_typ_id IN (SELECT clnt_link_typ_id FROM clnt_link_typ c, link_typ t WHERE t.link_typ_id = c.link_typ_id AND t.link_typ_cde = 'DBA') ");
strSql.Append(@" AND k.nme_id = n.nme_id) AS dba_name,(select addr_cty from addr n, link k where k.link_id = insp_hist.link_id and k.addr_id = n.addr_id) as city_kw, ");
strSql.Append(@" (select cnty_desc from cnty c, addr n, link k where k.link_id = insp_hist.link_id and k.addr_id = n.addr_id and c.cnty = n.cnty) as county_kw, ");
strSql.Append(@" (select insp_regn_cde from insp_regn r, link k where k.link_id = insp_hist.link_id and k.insp_regn_id = r.insp_regn_id) as region_kw FROM insp_vst, insp_hist, ");
strSql.Append(@" insp_typ_defn, insp_disp_typ, inspr, stff, lic WHERE insp_hist.insp_hist_id = insp_vst.insp_hist_id ");
strSql.Append(@" AND insp_vst.insp_vst_id = (SELECT NVL(max(s.alt_insp_vst_id), max(s.insp_vst_id)) FROM insp_vst_synch s WHERE s.insp_vst_id = '");
strSql.Append(strInspectionID);
strSql.Append(@"') AND insp_typ_defn.insp_typ_defn_id = insp_hist.insp_typ_defn_id AND insp_vst.inspr_id = inspr.inspr_id AND stff.stff_oper_id = inspr.stff_oper_id AND lic.lic_id = insp_hist.lic_id AND insp_disp_typ.insp_disp_typ_id = insp_hist.insp_disp_typ_id) a ");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strLicType = "";
string strLicNum = "";
string strFileNum = "";
string strKeyName = "";
string strDBAName = "";
string strInDorgNum = "";
string strVisitNum = "";
string strDisposition = "";
string strCity = "";
string strCounty = "";
string strRegion = "";
string strInspector = "";
string strInspNum = "";
string strSubject = "";
string strInspType = "";
reader.Read();
strLicType = reader["lic_Type"].ToString();
strLicNum = reader["lic_Num"].ToString();
strFileNum = reader["file_Num"].ToString();
strKeyName = reader["key_Name"].ToString();
strDBAName = reader["dba_kw"].ToString();
strInDorgNum = reader["indorg_num"].ToString();
strVisitNum = reader["visit_Num"].ToString();
strDisposition = reader["disposition_kw"].ToString();
strCity = reader["city_kw"].ToString();
strInspector = reader["inspector_name"].ToString();
strInspNum = reader["insp_nbr"].ToString();
strSubject = reader["key_Name"].ToString();
strInspType = reader["insp_typ_desc"].ToString();
if (reader["county_kw"] != DBNull.Value)
{
strCounty = reader["county_kw"].ToString();
}
else
{
strCounty = "Not Available";
}
if (reader["region_kw"] != DBNull.Value)
{
strRegion = reader["region_kw"].ToString();
}
else
{
strRegion = "Not Available";
}
Keyword kwdLicType = null;
if (!String.IsNullOrEmpty(strLicType))
{
KeywordType kwtLicType = app.Core.KeywordTypes.Find(gSaveToLicType);
if (kwtLicType != null)
{
kwdLicType = CreateKeywordHelper(kwtLicType, strLicType);
}
}
Keyword kwdLicenseNum = null;
if (!String.IsNullOrEmpty(strLicNum))
{
KeywordType kwtLicenseNum = app.Core.KeywordTypes.Find(gSaveToLicNum);
if (kwtLicenseNum != null)
{
kwdLicenseNum = CreateKeywordHelper(kwtLicenseNum, strLicNum);
}
}
Keyword kwdFileNum = null;
if (!String.IsNullOrEmpty(strFileNum))
{
KeywordType kwtFileNum = app.Core.KeywordTypes.Find(gSaveToFileNum);
if (kwtFileNum != null)
{
kwdFileNum = CreateKeywordHelper(kwtFileNum, strFileNum);
}
}
Keyword kwdKeyName = null;
if (!String.IsNullOrEmpty(strKeyName))
{
KeywordType kwtKeyName = app.Core.KeywordTypes.Find(gSaveToKeyName);
if (kwtKeyName != null)
{
kwdKeyName = CreateKeywordHelper(kwtKeyName, strKeyName);
}
}
Keyword kwdSubject = null;
if (!String.IsNullOrEmpty(strSubject))
{
KeywordType kwtSubject = app.Core.KeywordTypes.Find(gSaveToSubject);
if (kwtSubject != null)
{
kwdSubject = CreateKeywordHelper(kwtSubject, strSubject);
}
}
Keyword kwdDBAName = null;
if (!String.IsNullOrEmpty(strDBAName))
{
KeywordType kwtDBAName = app.Core.KeywordTypes.Find(gSaveToDBA);
if (kwtDBAName != null)
{
kwdDBAName = CreateKeywordHelper(kwtDBAName, strDBAName);
}
}
Keyword kwdInDorgNum = null;
if (!String.IsNullOrEmpty(strInDorgNum))
{
KeywordType kwtInDorgNum = app.Core.KeywordTypes.Find(gSaveToIndOrgNum);
if (kwtInDorgNum != null)
{
kwdInDorgNum = CreateKeywordHelper(kwtInDorgNum, strInDorgNum);
}
}
Keyword kwdVisitNum = null;
if (!String.IsNullOrEmpty(strVisitNum))
{
KeywordType kwtVisitNum = app.Core.KeywordTypes.Find(gSaveToVisitNum);
if (kwtVisitNum != null)
{
kwdVisitNum = CreateKeywordHelper(kwtVisitNum, strVisitNum);
}
}
Keyword kwdDisposition = null;
if (!String.IsNullOrEmpty(strDisposition))
{
KeywordType kwtDisposition = app.Core.KeywordTypes.Find(gSaveToLicType);
if (kwtDisposition != null)
{
kwdDisposition = CreateKeywordHelper(kwtDisposition, strDisposition);
}
}
Keyword kwdCity = null;
if (!String.IsNullOrEmpty(strCity))
{
KeywordType kwtCity = app.Core.KeywordTypes.Find(gSaveToCity);
if (kwtCity != null)
{
kwdCity = CreateKeywordHelper(kwtCity, strCity);
}
}
Keyword kwdCounty = null;
if (!String.IsNullOrEmpty(strCounty))
{
KeywordType kwtCounty = app.Core.KeywordTypes.Find(gSaveToCounty);
if (kwtCounty != null)
{
kwdCounty = CreateKeywordHelper(kwtCounty, strCounty);
}
}
Keyword kwdRegion = null;
if (!String.IsNullOrEmpty(strRegion))
{
KeywordType kwtRegion = app.Core.KeywordTypes.Find(gSaveToRegion);
if (kwtRegion != null)
{
kwdRegion = CreateKeywordHelper(kwtRegion, strRegion);
}
}
Keyword kwdInspector = null;
if (!String.IsNullOrEmpty(strInspector))
{
KeywordType kwtInspector = app.Core.KeywordTypes.Find(gSaveToInspectorName);
if (kwtInspector != null)
{
kwdInspector = CreateKeywordHelper(kwtInspector, strInspector);
}
}
Keyword kwdInspectorNum = null;
if (!String.IsNullOrEmpty(strInspNum))
{
KeywordType kwtInspNum = app.Core.KeywordTypes.Find(gSaveToInspNum);
if (kwtInspNum != null)
{
kwdInspectorNum = CreateKeywordHelper(kwtInspNum, strInspNum);
}
}
Keyword kwdInspTypeDesc = null;
if (!String.IsNullOrEmpty(strInspType))
{
KeywordType kwtInspTypeDesc = app.Core.KeywordTypes.Find(gSaveToInspTypeDesc);
if (kwtInspTypeDesc != null)
{
kwdInspTypeDesc = CreateKeywordHelper(kwtInspTypeDesc, strInspType);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdLicType != null)
{
keyModifier.UpdateKeyword(kwdLicType, kwdLicType);
}
if (kwdLicenseNum != null)
{
keyModifier.AddKeyword(kwdLicenseNum);
}
if (kwdFileNum != null)
{
keyModifier.AddKeyword(kwdFileNum);
}
if (kwdKeyName != null)
{
keyModifier.AddKeyword(kwdKeyName);
}
if (kwdDBAName != null)
{
keyModifier.AddKeyword(kwdDBAName);
}
if (kwdInDorgNum != null)
{
keyModifier.AddKeyword(kwdInDorgNum);
}
if (kwdVisitNum != null)
{
keyModifier.AddKeyword(kwdVisitNum);
}
if (kwdDisposition != null)
{
keyModifier.AddKeyword(kwdDisposition);
}
if (kwdCity != null)
{
keyModifier.AddKeyword(kwdCity);
}
if (kwdCounty != null)
{
keyModifier.AddKeyword(kwdCounty);
}
if (kwdRegion != null)
{
keyModifier.AddKeyword(kwdRegion);
}
if (kwdInspector != null)
{
keyModifier.AddKeyword(kwdInspector);
}
if (kwdInspTypeDesc != null)
{
keyModifier.AddKeyword(kwdInspTypeDesc);
}
if (kwdInspectorNum != null)
{
keyModifier.AddKeyword(kwdInspectorNum);
}
if (kwdSubject != null)
{
keyModifier.AddKeyword(kwdSubject);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
documentLock.Release();
string output = String.Format("Keyword: '{0}' Value: '{1}', {33}Keyword: '{2}' Value: '{3}', {33}Keyword: '{4}' Value: '{5}', {33}Keyword: '{6}' Value: '{7}'," +
"{33}Keyword: '{8}' Value: '{9}', {33}Keyword: '{10}' Value: '{11}', {33}Keyword: '{12}' Value: '{13}', {33}Keyword: '{14}' Value: '{15}', {33}Keyword: '{16}' Value: '{17}'," +
"{33}Keyword: '{18}' Value: '{19}', {33}Keyword: '{20}' Value: '{21}', {33}Keyword: '{22}' Value: '{23}', {33}Keyword: '{24}' Value: '{25}', {33}Keyword: '{26}' Value: '{27}'," +
"{33}Keyword: '{28}' Value: '{29}', {33}Keyword: '{30}' Value: '{31}', {33}added to Document {32}.",
gSaveToLicNum, strLicNum, gSaveToLicType, strLicType, gSaveToInspNum, strInspNum, gSaveToFileNum, strFileNum, gSaveToKeyName, strKeyName, gSaveToSubject, strSubject,
gSaveToDBA, strDBAName, gSaveToIndOrgNum, strInDorgNum, gSaveToVisitNum, strVisitNum, gSaveToDisposition, strDisposition, gSaveToCity, strCity, gSaveToCounty, strCounty,
gSaveToRegion, strRegion, gSaveToInspectorName, strInspector, gSaveToInspTypeDesc, strInspType, gSaveToInspNum, strInspectionID, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
}
}
else
{
throw new Exception(string.Format("No records found in database"));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Case # keywords for passing to LicEase database
KeywordType kwtAppNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamAppNum);
string strAppNum = "";
if (kwtAppNum != null)
{
KeywordRecord keyRecLicNum = _currentDocument.KeywordRecords.Find(kwtAppNum);
if (keyRecLicNum != null)
{
Keyword kwdLicNum = keyRecLicNum.Keywords.Find(kwtAppNum);
if (kwdLicNum != null)
{
strAppNum = CleanSeedKW(kwdLicNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
if (strAppNum == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gParamAppNum));
}
if (strLicenseType == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gParamLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
//access Config Item for LicEase PROD ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
{
}
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select lic.clnt_cde, lic.lic_nbr as License_Number from appl, lic where appl.lic_id = lic.lic_id");
strSql.Append(@" and appl.applc_nbr = '");
strSql.Append(strAppNum);
strSql.Append(@"' and appl.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"')");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strLicNum = "";
reader.Read();
strLicNum = reader["License_Number"].ToString();
Keyword kwdLicenseNum = null;
if (!String.IsNullOrEmpty(strLicNum))
{
KeywordType kwtLicenseNum = app.Core.KeywordTypes.Find(gSaveToLicNum);
if (kwtLicenseNum != null)
{
kwdLicenseNum = CreateKeywordHelper(kwtLicenseNum, strLicNum);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdLicenseNum != null)
{
keyModifier.AddKeyword(kwdLicenseNum);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', added to Document {2}.", gSaveToLicNum, strLicNum, _currentDocument.ID);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
documentLock.Release();
}
}
else
{
throw new Exception(string.Format("No records found in database"));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Case # keywords for passing to LicEase database
KeywordType kwtLicNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gSaveToLicNum);
string strLicNum = "";
if (kwtLicNum != null)
{
KeywordRecord keyRecLicNum = _currentDocument.KeywordRecords.Find(kwtLicNum);
if (keyRecLicNum != null)
{
Keyword kwdLicNum = keyRecLicNum.Keywords.Find(kwtLicNum);
if (kwdLicNum != null)
{
strLicNum = CleanSeedKW(kwdLicNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gSaveToLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
KeywordType kwtInspectionDate = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gSaveToInspectionID);
string strInspectionID = "";
if (kwtInspectionDate != null)
{
KeywordRecord keyRecInspectionDate = _currentDocument.KeywordRecords.Find(kwtInspectionDate);
if (keyRecInspectionDate != null)
{
Keyword kwdInspectionDate = keyRecInspectionDate.Keywords.Find(kwtInspectionDate);
if (kwdInspectionDate != null)
{
strInspectionID = CleanSeedKW(kwdInspectionDate.ToString());
}
}
}
if (strInspectionID == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gSaveToInspectionID));
}
if (strLicNum == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gSaveToLicNum));
}
if (strLicenseType == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gSaveToLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select a.insp_nbr, b.key_name, a.insp_vst_id, NVL(b.lic_type,a.lic_type) as lic_type, b.indorg_num, b.file_num, NVL(b.lic_num,a.lic_num) as lic_num, a.visit_num, ");
strSql.Append(@" a.visit_date, a.insp_typ_desc, a.disposition_kw, b.city_kw, b.county_kw, b.region_kw, a.inspector_name from (SELECT DISTINCT to_char(insp_hist.insp_nbr) as insp_nbr, ");
strSql.Append(@" to_char(insp_vst.insp_vst_id) as insp_vst_id, lic.clnt_cde AS lic_type, lic.xent_id AS indorg_num, lic.lic_nbr AS lic_num, to_char(insp_vst.insp_vst_nbr) AS visit_num, ");
strSql.Append(@" to_char(insp_vst.insp_vst_strt_dte) AS visit_date, insp_typ_defn.insp_typ_desc, insp_disp_typ.insp_disp_typ_desc AS disposition_kw, ");
strSql.Append(@" stff.frst_nme || '.' || stff.surnme AS inspector_name FROM insp_vst, insp_hist, insp_typ_defn, insp_disp_typ, inspr, stff, lic ");
strSql.Append(@" WHERE insp_hist.insp_hist_id = insp_vst.insp_hist_id and insp_vst.insp_vst_id = '");
strSql.Append(strInspectionID);
strSql.Append(@"' AND insp_typ_defn.insp_typ_defn_id = insp_hist.insp_typ_defn_id AND insp_vst.inspr_id = inspr.inspr_id AND stff.stff_oper_id = inspr.stff_oper_id");
strSql.Append(@" AND lic.lic_id = insp_hist.lic_id AND insp_disp_typ.insp_disp_typ_id = insp_hist.insp_disp_typ_id ) a, (SELECT DISTINCT '' as insp_nbr, pri_name.key_nme AS key_name, ");
strSql.Append(@" '' as insp_vst_id, lic.clnt_cde AS lic_type, to_char(lic.xent_id) AS indorg_num, to_char(lic.file_nbr) AS file_num, lic.lic_nbr AS lic_num, '' AS visit_num, ");
strSql.Append(@" '' AS visit_date, '' as insp_typ_desc, '' AS disposition_kw, addr.addr_cty AS city_kw, cnty.cnty_desc AS county_kw, insp_regn.insp_regn_cde AS region_kw, ");
strSql.Append(@" '' AS inspector_name FROM insp_vst, insp_hist, clnt, lic, NAME pri_name, insp_regn, inspr_insp_regn, LINK, addr, cnty WHERE lic.lic_nbr = '");
strSql.Append(strLicNum);
strSql.Append(@"' and lic.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"' AND insp_hist.lic_id = lic.lic_id AND pri_name.ent_nme_typ = 'P' AND pri_name.cur_nme_ind = 'Y' AND LINK.nme_id = pri_name.nme_id AND addr.addr_id = LINK.addr_id AND LINK.curr_ind = 'Y'");
strSql.Append(@"' AND LINK.insp_regn_id = inspr_insp_regn.insp_regn_id AND lic.xent_id = pri_name.xent_id AND insp_regn.insp_regn_id = inspr_insp_regn.insp_regn_id AND addr.cnty = cnty.cnty AND lic.clnt_cde = clnt.clnt_cde AND clnt.clnt_cde_prnt = '33' and insp_vst.insp_vst_id = (select max(iv.insp_vst_id) from insp_vst iv, insp_hist ih where iv.insp_hist_id = ih.insp_hist_id and ih.lic_id = lic.lic_id)) b where b.indorg_num = a.indorg_num (+)");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strLicType = "";
//string strInspID = "";
string strLicenseNum = "";
string strFileNum = "";
string strKeyName = "";
//string strDBAName = "";
//string strInDorgNum = "";
string strVisitNum = "";
string strDisposition = "";
string strInspDate = "";
string strCity = "";
string strCounty = "";
string strRegion = "";
string strInspector = "";
string strInspNum = "";
string strSubject = "";
//string strInspType = "";
reader.Read();
strLicType = reader["lic_Type"].ToString();
//strInspID = reader["insp_vst_id"].ToString();
strLicenseNum = reader["lic_Num"].ToString();
strFileNum = reader["file_Num"].ToString();
strKeyName = reader["key_Name"].ToString();
//strDBAName = reader["dba_kw"].ToString();
//strInDorgNum = reader["indorg_num"].ToString();
strVisitNum = reader["visit_Num"].ToString();
strInspDate = reader["visit_date"].ToString();
strDisposition = reader["disposition_kw"].ToString();
strCity = reader["city_kw"].ToString();
strInspector = reader["inspector_name"].ToString();
strInspNum = reader["insp_nbr"].ToString();
strSubject = reader["key_Name"].ToString();
//strInspType = reader["insp_typ"].ToString();
if (reader["county_kw"] != DBNull.Value)
{
strCounty = reader["county_kw"].ToString();
}
else
{
strCounty = "Not Available";
}
if (reader["region_kw"] != DBNull.Value)
{
strRegion = reader["region_kw"].ToString();
}
else
{
strRegion = "Not Available";
}
Keyword kwdLicType = null;
if (!String.IsNullOrEmpty(strLicType))
{
KeywordType kwtLicType = app.Core.KeywordTypes.Find(gSaveToLicType);
if (kwtLicType != null)
{
kwdLicType = CreateKeywordHelper(kwtLicType, strLicType);
}
}
Keyword kwdLicenseNum = null;
if (!String.IsNullOrEmpty(strLicenseNum))
{
KeywordType kwtLicenseNum = app.Core.KeywordTypes.Find(gSaveToLicNum);
if (kwtLicenseNum != null)
{
kwdLicenseNum = CreateKeywordHelper(kwtLicenseNum, strLicenseNum);
}
}
Keyword kwdFileNum = null;
if (!String.IsNullOrEmpty(strFileNum))
{
KeywordType kwtFileNum = app.Core.KeywordTypes.Find(gSaveToFileNum);
if (kwtFileNum != null)
{
kwdFileNum = CreateKeywordHelper(kwtFileNum, strFileNum);
}
}
Keyword kwdKeyName = null;
if (!String.IsNullOrEmpty(strKeyName))
{
KeywordType kwtKeyName = app.Core.KeywordTypes.Find(gSaveToKeyName);
if (kwtKeyName != null)
{
kwdKeyName = CreateKeywordHelper(kwtKeyName, strKeyName);
}
}
Keyword kwdSubject = null;
if (!String.IsNullOrEmpty(strSubject))
{
KeywordType kwtSubject = app.Core.KeywordTypes.Find(gSaveToSubject);
if (kwtSubject != null)
{
kwdSubject = CreateKeywordHelper(kwtSubject, strSubject);
}
}
/*Keyword kwdDBAName = null;
* if (!String.IsNullOrEmpty(strDBAName))
* {
* KeywordType kwtDBAName = app.Core.KeywordTypes.Find(gSaveToDBA);
* if (kwtDBAName != null)
* kwdDBAName = CreateKeywordHelper(kwtDBAName, strDBAName);
* } */
Keyword kwdVisitNum = null;
if (!String.IsNullOrEmpty(strVisitNum))
{
KeywordType kwtVisitNum = app.Core.KeywordTypes.Find(gSaveToVisitNum);
if (kwtVisitNum != null)
{
kwdVisitNum = CreateKeywordHelper(kwtVisitNum, strVisitNum);
}
}
Keyword kwdDisposition = null;
if (!String.IsNullOrEmpty(strDisposition))
{
KeywordType kwtDisposition = app.Core.KeywordTypes.Find(gSaveToLicType);
if (kwtDisposition != null)
{
kwdDisposition = CreateKeywordHelper(kwtDisposition, strDisposition);
}
}
Keyword kwdCity = null;
if (!String.IsNullOrEmpty(strCity))
{
KeywordType kwtCity = app.Core.KeywordTypes.Find(gSaveToCity);
if (kwtCity != null)
{
kwdCity = CreateKeywordHelper(kwtCity, strCity);
}
}
Keyword kwdCounty = null;
if (!String.IsNullOrEmpty(strCounty))
{
KeywordType kwtCounty = app.Core.KeywordTypes.Find(gSaveToCounty);
if (kwtCounty != null)
{
kwdCounty = CreateKeywordHelper(kwtCounty, strCounty);
}
}
Keyword kwdRegion = null;
if (!String.IsNullOrEmpty(strRegion))
{
KeywordType kwtRegion = app.Core.KeywordTypes.Find(gSaveToRegion);
if (kwtRegion != null)
{
kwdRegion = CreateKeywordHelper(kwtRegion, strRegion);
}
}
Keyword kwdInspector = null;
if (!String.IsNullOrEmpty(strInspector))
{
KeywordType kwtInspector = app.Core.KeywordTypes.Find(gSaveToInspectorName);
if (kwtInspector != null)
{
kwdInspector = CreateKeywordHelper(kwtInspector, strInspector);
}
}
Keyword kwdInspectorID = null;
if (!String.IsNullOrEmpty(strInspID))
{
KeywordType kwtInspNum = app.Core.KeywordTypes.Find(gSaveToInspectionID);
if (kwtInspNum != null)
{
kwdInspectorID = CreateKeywordHelper(kwtInspNum, strInspID);
}
}
Keyword kwdInspTypeDesc = null;
if (!String.IsNullOrEmpty(strInspDate))
{
KeywordType kwtInspTypeDesc = app.Core.KeywordTypes.Find(gSaveToInspectVisitDate);
if (kwtInspTypeDesc != null)
{
kwdInspTypeDesc = CreateKeywordHelper(kwtInspTypeDesc, strInspDate);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdLicType != null)
{
keyModifier.AddKeyword(kwdLicType);
}
if (kwdLicenseNum != null)
{
keyModifier.AddKeyword(kwdLicenseNum);
}
if (kwdInspectorID != null)
{
keyModifier.AddKeyword(kwdInspectorID);
}
if (kwdFileNum != null)
{
keyModifier.AddKeyword(kwdFileNum);
}
if (kwdKeyName != null)
{
keyModifier.AddKeyword(kwdKeyName);
}
//if (kwdDBAName != null) keyModifier.AddKeyword(kwdDBAName);
if (kwdVisitNum != null)
{
keyModifier.AddKeyword(kwdVisitNum);
}
if (kwdDisposition != null)
{
keyModifier.AddKeyword(kwdDisposition);
}
if (kwdCity != null)
{
keyModifier.AddKeyword(kwdCity);
}
if (kwdCounty != null)
{
keyModifier.AddKeyword(kwdCounty);
}
if (kwdRegion != null)
{
keyModifier.AddKeyword(kwdRegion);
}
if (kwdInspector != null)
{
keyModifier.AddKeyword(kwdInspector);
}
if (kwdInspTypeDesc != null)
{
keyModifier.AddKeyword(kwdInspTypeDesc);
}
if (kwdSubject != null)
{
keyModifier.AddKeyword(kwdSubject);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {29}Keyword: '{2}' Value: '{3}', {29}Keyword: '{4}' Value: '{5}', {29}Keyword: '{6}' Value: '{7}'," +
"{29}Keyword: '{8}' Value: '{9}', {29}Keyword: '{10}' Value: '{11}', {29}Keyword: '{12}' Value: '{13}', {29}Keyword: '{14}' Value: '{15}', {29}Keyword: '{16}' Value: '{17}'," +
"{29}Keyword: '{18}' Value: '{19}', {29}Keyword: '{20}' Value: '{21}', {29}Keyword: '{22}' Value: '{23}', {29}Keyword: '{24}' Value: '{25}', {29}Keyword: '{26}' Value: '{27}'," +
"{29}Keyword: '{28}' Value: '{29}', {29}Keyword: '{30}' Value: '{31}', {29}added to Document {28}.",
gSaveToLicNum, strLicNum, gSaveToLicType, strLicType, gSaveToInspectionID, strInspID, gSaveToFileNum, strFileNum, gSaveToKeyName, strKeyName, gSaveToSubject, strSubject,
gSaveToDBA, "not being returned by query", gSaveToVisitNum, strVisitNum, gSaveToDisposition, strDisposition, gSaveToCity, strCity, gSaveToCounty, strCounty,
gSaveToRegion, strRegion, gSaveToInspectorName, strInspector, gSaveToInspectVisitDate, strInspDate, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
documentLock.Release();
}
}
else
{
throw new Exception(string.Format("No records found in database"));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Case # keywords for passing to LicEase database
KeywordType kwtCaseNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamCaseNum);
string strCaseNum = "";
if (kwtCaseNum != null)
{
KeywordRecord keyRecFileNum = _currentDocument.KeywordRecords.Find(kwtCaseNum);
if (keyRecFileNum != null)
{
Keyword kwdFileNum = keyRecFileNum.Keywords.Find(kwtCaseNum);
if (kwdFileNum != null)
{
strCaseNum = CleanSeedKW(kwdFileNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
if ((strCaseNum == "") || (strLicenseType == ""))
{
throw new Exception(string.Format("Either {0} or {1} is blank.", gParamCaseNum, gParamLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT ");
strSql.Append(@" (SELECT clnt.clnt_lng_nme ");
strSql.Append(@" FROM clnt clnt ");
strSql.Append(@" WHERE a.clnt_cde = clnt.clnt_cde) AS Profession, ");
strSql.Append(@" (SELECT clnt3.clnt_lng_nme ");
strSql.Append(@" FROM clnt clnt2, clnt clnt3 ");
strSql.Append(@" WHERE a.clnt_cde = clnt2.clnt_cde ");
strSql.Append(@" AND clnt2.clnt_cde_prnt = clnt3.clnt_cde) AS BOARD, ");
strSql.Append(@" (SELECT x.key_nme ");
strSql.Append(@" FROM invl_party y, link u, clnt_link_typ v, link_typ w, name x ");
strSql.Append(@" WHERE a.cmpln_id = y.cmpln_id ");
strSql.Append(@" AND y.xent_id = u.xent_id ");
strSql.Append(@" AND y.invl_party_id = u.prnt_id ");
strSql.Append(@" AND u.link_prnt_cde = 'I' ");
strSql.Append(@" AND u.curr_ind = 'Y' ");
strSql.Append(@" AND u.clnt_link_typ_id = v.clnt_link_typ_id ");
strSql.Append(@" AND v.link_typ_id = w.link_typ_id ");
strSql.Append(@" AND w.link_typ_cde = 'CM' ");
strSql.Append(@" AND u.nme_id = x.nme_id ");
strSql.Append(@" AND x.ent_nme_typ = 'R' ");
strSql.Append(@" AND x.cur_nme_ind = 'Y') as COMPLAINANT, ");
strSql.Append(@" (SELECT n.key_nme ");
strSql.Append(@" FROM rspn r, link l, clnt_link_typ clt, link_typ lt, name n ");
strSql.Append(@" WHERE a.cmpln_id = r.cmpln_id ");
strSql.Append(@" AND r.rspn_id = l.prnt_id ");
strSql.Append(@" AND l.link_prnt_cde = 'R' ");
strSql.Append(@" AND l.curr_ind = 'Y' ");
strSql.Append(@" AND l.clnt_link_typ_id = clt.clnt_link_typ_id ");
strSql.Append(@" AND clt.link_typ_id = lt.link_typ_id ");
strSql.Append(@" AND lt.link_typ_cde = 'RS' ");
strSql.Append(@" AND l.nme_id = n.nme_id ");
strSql.Append(@" AND n.ent_nme_typ = 'R' ");
strSql.Append(@" AND n.cur_nme_ind = 'Y') as SUBJECT ");
strSql.Append(@" FROM cmpln a ");
strSql.Append(@" WHERE a.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"' AND a.cmpln_nbr = '");
strSql.Append(strCaseNum);
strSql.Append(@"'");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strBoard = "";
string strSubject = "";
string strComplainant = "";
reader.Read();
strBoard = reader["BOARD"].ToString();
strSubject = reader["SUBJECT"].ToString();
strComplainant = reader["COMPLAINANT"].ToString();
Keyword kwdBoard = null;
if (!String.IsNullOrEmpty(strBoard))
{
KeywordType kwtBoard = app.Core.KeywordTypes.Find(gSaveToBoard);
if (kwtBoard != null)
{
kwdBoard = CreateKeywordHelper(kwtBoard, strBoard);
}
}
Keyword kwdSubject = null;
if (!String.IsNullOrEmpty(strSubject))
{
KeywordType kwtSubject = app.Core.KeywordTypes.Find(gSaveToSubject);
if (kwtSubject != null)
{
kwdSubject = CreateKeywordHelper(kwtSubject, strSubject);
}
}
Keyword kwdComplainant = null;
if (!String.IsNullOrEmpty(strComplainant))
{
KeywordType kwtComplainant = app.Core.KeywordTypes.Find(gSaveToComplainant);
if (kwtComplainant != null)
{
kwdComplainant = CreateKeywordHelper(kwtComplainant, strComplainant);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdBoard != null)
{
keyModifier.AddKeyword(kwdBoard);
}
if (kwdSubject != null)
{
keyModifier.AddKeyword(kwdSubject);
}
if (kwdComplainant != null)
{
keyModifier.AddKeyword(kwdComplainant);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {7}Keyword: '{2}' Value: '{3}', {7}Keyword: '{4}' Value: '{5}', {7}added to Document {6}.",
gSaveToBoard, strBoard, gSaveToSubject, strSubject, gSaveToComplainant, strComplainant, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
}
}
else
{
throw new Exception(string.Format("No records found in database for {0}='{1}' and {4}{2}='{3}' ", gParamLicType, strLicenseType, gParamCaseNum, strCaseNum, Environment.NewLine));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app">Unity Application object</param>
/// <param name="args">Workflow event arguments</param>
// public void OnWorkflowScriptExecute(Application app, WorkflowEventArgs args)
//public void OnWorkflowScriptExecute(Application app, WorkflowEventArgs args = null)
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
KeywordType kwtLicenseNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamFileNumber);
string strFileNum = "";
if (kwtLicenseNum != null)
{
KeywordRecord keyRecLicenseNum = _currentDocument.KeywordRecords.Find(kwtLicenseNum);
if (keyRecLicenseNum != null)
{
Keyword kwdLicenseNum = keyRecLicenseNum.Keywords.Find(kwtLicenseNum);
if (kwdLicenseNum != null)
{
strFileNum = CleanSeedKW(kwdLicenseNum.ToString());
}
}
}
if ((strFileNum == "") || (strLicenseType == ""))
{
throw new Exception(string.Format("Either {0} or {1} is blank.", gParamFileNumber, gParamLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select * from (select distinct(c.cmpln_nbr) as CASENUMBER, becec.enf_cde as ACTYPE, c.rcv_dte as ISSUEDATE, to_char(cacat.actv_strt_dte, 'MM/DD/YYYY') as CLERKEDDATE, ccscs.cmpln_sta_cde as COMPSTATUS, c.clnt_cde as LICTYPE ");
strSql.Append(@", c1_mc.chrg_amt as FINEAMT from cmpln c inner join (select ec.enf_cde, bec.clnt_enf_cde_id from brd_enf_cde bec inner join enf_cde ec on bec.enf_cde_id = ec.enf_cde_id where (ec.enf_cde like 'CLOS' or ec.clnt_cde like 'AD%' or ec.enf_cde like 'AC%') ) becec on c.clnt_cmpln_cls_id = becec.clnt_enf_cde_id inner join ");
strSql.Append(@"(select cs.cmpln_sta_cde, ccs.clnt_cmpln_sta_id from clnt_cmpln_sta ccs inner join cmpln_sta cs on ccs.cmpln_sta_id = cs.cmpln_sta_id ) ccscs on c.clnt_cmpln_sta_id = ccscs.clnt_cmpln_sta_id inner join (select l.file_nbr, l.clnt_cde, l.lic_id, r.cmpln_id from rspn r inner join lic l on r.lic_id = l.lic_id ) rl ");
strSql.Append(@" on c.cmpln_id = rl.cmpln_id left outer join (select ca.actv_strt_dte, ca.cmpln_id from cmpln_actv ca inner join cmpln_actv_typ cat on ca.cmpln_actv_typ_id = cat.cmpln_actv_typ_id where cat.cmpln_actv_typ_cde = 'A400' ) cacat on c.cmpln_id = cacat.cmpln_id left outer join (select mc.chrg_amt, c1.cmpln_id from cmply_ordr c1 inner join misc_chrg mc on c1.misc_chrg_id = mc.misc_chrg_id ) c1_mc on c.cmpln_id = c1_mc.cmpln_id where rl.file_nbr = '");
strSql.Append(strFileNum);
strSql.Append(@"' and rl.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"' and c.rcv_dte > (SYSDATE - 1826) and c.clnt_cde like '20%' and c.cmpln_nbr > '2010%') order by 1 desc ");
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
lstProp1.Add(reader["CASENUMBER"].ToString());
lstProp2.Add(reader["ACTYPE"].ToString());
lstProp3.Add(reader["ISSUEDATE"].ToString());
lstProp4.Add(reader["CLERKEDDATE"].ToString());
lstProp5.Add(reader["COMPSTATUS"].ToString());
lstProp6.Add(reader["LICTYPE"].ToString());
lstProp7.Add(reader["FINEAMT"].ToString());
}
// Create keyword modifier object to hold keyword changes
EForm currentForm = _currentDocument.EForm;
FieldModifier fieldModifier = currentForm.CreateFieldModifier();
foreach (string props in lstProp1)
{
if (props != null)
{
fieldModifier.UpdateField("CASENUMBER", props);
}
}
foreach (string props in lstProp2)
{
if (props != null)
{
fieldModifier.UpdateField("ACTYPE", props);
}
}
foreach (string props in lstProp3)
{
if (props != null)
{
fieldModifier.UpdateField("ISSUEDATE", props);
}
}
foreach (string props in lstProp4)
{
if (props != null)
{
fieldModifier.UpdateField("CLERKEDDATE", props);
}
}
foreach (string props in lstProp5)
{
if (props != null)
{
fieldModifier.UpdateField("COMPSTATUS", props);
}
}
foreach (string props in lstProp6)
{
if (props != null)
{
fieldModifier.UpdateField("LICTYPE", props);
}
}
foreach (string props in lstProp7)
{
if (props != null)
{
fieldModifier.UpdateField("FINEAMT", props);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Apply keyword change to the document
fieldModifier.ApplyChanges();
documentLock.Release();
}
}
else
{
throw new Exception(string.Format("No records found in database for {0}='{1}' and {4}{2}='{3}' ", gParamLicType, strLicenseType, gParamFileNumber, strFileNum, Environment.NewLine));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
