DDCE - Get Inspector Name - 429.cs

// Skeleton generated by Hyland Unity Editor on 3/30/2017 12:35:32 PM
namespace CRDEMailAddressLookUp
{
    using System;
    using System.Text;
    using System.Data;
    using System.Data.Odbc;
    using Hyland.Unity;


    /// <summary>
    /// 
    /// </summary>
    public class CRDEMailAddressLookUp : IWorkflowScript
    {
        #region User-Configurable Script Settings
        // Script name for diagnostics logging
        private const string ScriptName = "429 - DDCE - Get Inspector Name";

        // Diagnostics logging level - set to Verbose for testing, Error for production
        private const Diagnostics.DiagnosticsLevel DiagLevel = Diagnostics.DiagnosticsLevel.Verbose;

        // Workflow property where error message will be stored
        private const string ErrorMessageProperty = "UnityError";

        // If true, errors will be added to document history
        private const bool WriteErrorsToHistory = true;

        // Date/Time format for diagnostics logging
        private const string DateTimeFormat = "MM-dd-yyyy HH:mm:ss.fff";

        //Search parameters and Save to Keywords
        private const string gSaveToEnfInspector = "Enforcement Inspector";
        private const string gParamInspectorName = "Inspector Name";
        private const string gParamInspVisitID = "Inspection Visit ID";

        #endregion

        /***********************************************
         * USER/SE: PLEASE DO NOT EDIT BELOW THIS LINE *
         ***********************************************/

        #region Private Globals
        // Active workflow document
        private Document _currentDocument;
        #endregion

        #region IWorkflowScript
        /// <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 kwtInspectorName = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamInspectorName);
                string strInspectorName = "";
                if (kwtInspectorName != null)
                {
                    KeywordRecord keyRecFileNum = _currentDocument.KeywordRecords.Find(kwtInspectorName);
                    if (keyRecFileNum != null)
                    {
                        Keyword kwdFileNum = keyRecFileNum.Keywords.Find(kwtInspectorName);
                        if (kwdFileNum != null)
                            strInspectorName = CleanSeedKW(kwdFileNum.ToString());
                    }
                }
                KeywordType kwtInspVisitID = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamInspVisitID);
                string strInspVisitID = "";
                if (kwtInspVisitID != null)
                {
                    KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtInspVisitID);
                    if (keyRecLicenseType != null)
                    {
                        Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtInspVisitID);
                        if (kwdLicenseType != null)
                            strInspVisitID = CleanSeedKW(kwdLicenseType.ToString());
                    }
                }

                if ((strInspectorName == "") || (strInspVisitID == ""))
                {
                    throw new Exception(string.Format("Either {0} or {1} is blank.", gParamInspectorName, gParamInspVisitID));
                }

                //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 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, a.lic_num AS lic_num, a.visit_num, a.visit_date, ");
                strSql.Append(@" a.insp_typ_desc, a.disposition_kw, city_kw, county_kw, 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, lic.file_nbr as file_num,");
                strSql.Append(@" TO_CHAR (insp_vst.insp_vst_nbr) AS visit_num, 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 ,insp_hist.lic_id as insp_lic_id ,(select max(key_nme) from name n, link k where k.link_id = insp_hist.link_id and ");
                strSql.Append(@" k.nme_id = n.nme_id) as key_name,(SELECT MAX (key_nme) FROM NAME n, LINK k WHERE k.prnt_id = insp_hist.lic_id and k.link_prnt_cde = 'L' and k.curr_ind = 'Y' ");
                strSql.Append(@" 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 AND insp_vst.insp_vst_id = (SELECT NVL(max(s.alt_insp_vst_id), ");
                strSql.Append(@" max(s.insp_vst_id)) FROM insp_vst_synch s WHERE s.insp_vst_id = '");
                strSql.Append(strInspVisitID);
                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");
                strSql.Append(@" 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 strMOD = "";

                                reader.Read();

                                strMOD = reader["inspector_name"].ToString();

                                Keyword kwdMOD = null;
                                if (!String.IsNullOrEmpty(strMOD))
                                {
                                    KeywordType kwtMOD = app.Core.KeywordTypes.Find(gSaveToEnfInspector);
                                    if (kwtMOD != null)
                                        kwdMOD = CreateKeywordHelper(kwtMOD, strMOD);
                                }

                                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 (kwdMOD != null) keyModifier.AddKeyword(kwdMOD);

                                    // Apply keyword change to the document
                                    keyModifier.ApplyChanges();

                                    string output = String.Format("Keyword: '{0}' Value: '{1}', {3}added to Document {2}.",
                                        gSaveToEnfInspector, strMOD, _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}' ", gParamInspVisitID, strInspVisitID, gParamInspectorName, strInspectorName, 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));
            }
        }
        #endregion

        #region Helper Functions
        /// <summary>
        /// Initialize global settings
        /// </summary>
        /// <param name="app">Unity Application object</param>
        /// <param name="args">Workflow event arguments</param>
        private void IntializeScript(ref Application app, ref WorkflowEventArgs args)
        {
            // Set the specified diagnostics level
            app.Diagnostics.Level = DiagLevel;

            // Log script execution start
            app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
                string.Format("{0} - Start Script - [{1}]", DateTime.Now.ToString(DateTimeFormat), ScriptName));

            // Capture active document as global
            _currentDocument = args.Document;
            //  _currentDocument = app.Core.GetDocumentByID(TestDocId);
            // If an error was stored in the property bag from a previous execution, clear it
            if (args.SessionPropertyBag.ContainsKey(ErrorMessageProperty)) args.SessionPropertyBag.Remove(ErrorMessageProperty);

            // Set ScriptResult = true for workflow rules (will become false if an error is caught)
            args.ScriptResult = true;
        }

        /// <summary>
        /// Handle exceptions and log to Diagnostics Console and document history
        /// </summary>
        /// <param name="ex">Exception</param>
        /// <param name="app">Unity Application object</param>
        /// <param name="args">Workflow event arguments</param>
        /// <param name="otherDocument">Document on which to update history if not active workflow document</param>
        private void HandleException(Exception ex, ref Application app, ref WorkflowEventArgs args, Document otherDocument = null)
        {
            var history = app.Core.LogManagement;
            bool isInner = false;

            // Cycle through all inner exceptions
            while (ex != null)
            {
                // Construct error text to store to workflow property
                string propertyError = string.Format("{0}{1}: {2}",
                    isInner ? "Inner " : "",
                    ex.GetType().Name,
                    ex.Message);

                // Construct error text to store to document history
                string historyError = propertyError.Replace(ex.GetType().Name, "Unity Script Error");

                // Construct error text to log to diagnostics console
                string diagnosticsError = string.Format("{0} - ***ERROR***{1}{2}{1}{1}Stack Trace:{1}{3}",
                    DateTime.Now.ToString(DateTimeFormat),
                    Environment.NewLine,
                    propertyError,
                    ex.StackTrace);

                // Add error message to document history (on current or specified document)
                var document = otherDocument ?? _currentDocument;
                if (document != null && WriteErrorsToHistory) history.CreateDocumentHistoryItem(document, historyError);

                // Write error message to Diagnostcs Consonle
                app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Error, diagnosticsError);

                // Store the original (inner) exception message to error workflow property
                if (ex.InnerException == null) args.SessionPropertyBag.Set(ErrorMessageProperty, ex.Message);

                // Move on to next inner exception
                ex = ex.InnerException;
                isInner = true;
            }

            // Set ScriptResult = false for workflow rules
            args.ScriptResult = false;
        }

        /// <summary>
        /// Clean Seed Keyword
        /// </summary>
        /// <param name="pValue">Keyword Value</param>
        private string CleanSeedKW(string pValue)
        {
            string temp = pValue;
            StringBuilder newString = new StringBuilder();

            int firstSpace = pValue.IndexOf(" ");
            if (firstSpace > 0)
            {
                temp = pValue.Substring(0, firstSpace);
                foreach (char c in temp)
                {
                    if (((c >= '0') && (c <= '9')) || ((c >= 'A') && (c <= 'Z')))
                    {
                        newString.Append(c);
                    }
                    else
                    {
                        newString.Append(" ");
                    }
                }
                newString = newString.Replace(" ", "");
                newString = newString.Replace("'", "''");
                return newString.ToString();
            }
            else
            {
                return pValue.ToString();
            }
        }
        private Keyword CreateKeywordHelper(KeywordType Keytype, string Value)
        {
            Keyword key = null;
            switch (Keytype.DataType)
            {
                case KeywordDataType.Currency:
                case KeywordDataType.Numeric20:
                    decimal decVal = decimal.Parse(Value);
                    key = Keytype.CreateKeyword(decVal);
                    break;
                case KeywordDataType.Date:
                case KeywordDataType.DateTime:
                    DateTime dateVal = DateTime.Parse(Value);
                    key = Keytype.CreateKeyword(dateVal);
                    break;
                case KeywordDataType.FloatingPoint:
                    double dblVal = double.Parse(Value);
                    key = Keytype.CreateKeyword(dblVal);
                    break;
                case KeywordDataType.Numeric9:
                    long lngVal = long.Parse(Value);
                    key = Keytype.CreateKeyword(lngVal);
                    break;
                default:
                    key = Keytype.CreateKeyword(Value);
                    break;
            }
            return key;
        }
        #endregion
    }
}