Using Starfish Scripting Class Properties & Methods in C

From Starfish ETL
Jump to navigation Jump to search

See available Starfish Scripting Class Variables, Properties and Methods. To use these variables, properties and methods, you must append "Starfish." to the beginning of variable, property or method AND you must use the exact capitalization as found in this wiki.

Generic Example

Example using the PreviewMode Property and GetSetting and SaveSetting Methods:

	if (Starfish.GetStageValue(0, "#Action") == "Update")
	{
		Starfish.GotoNextRow();
	}

	if ((!Starfish.PreviewMode) && (Starfish.CurrentStageName == "NS Accounts to S"))
	{
		//Set the last run datetime for this user.
		Starfish.SaveSetting("Meetings-LRD-"+gUserID, DateTime.Now.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss+00:00"));
		
		//Check to see if there are more users to process and if so, restart job.
		string uIDs = Starfish.GetSetting("UserIDs");
		//This Setting is set in the first Job and will restrict runs to only the Admin user.
		bool b = Starfish.GetSetting("AdminUserOnlyTesting_True_OR_False") == "True";
		if (b) {
			uIDs = "ADMIN";
		}
		if (runCnt < uIDs.Split(';').Length-1)
		{
			runCnt++;
			Starfish.GotoJob("e8566f56-69cd-4d65-8835-351de24795e3");
			Starfish.LogMessage("Count: "+ runCnt.ToString());
		} else {
			runCnt = 0;
			Starfish.GotoNextRow();
		}
		
		string quoteID = "";
		//Sometimes a Starfish Origin Connector will return NULL for an field's Object.  If you try to run .ToString() on the NULL object, it will error.  If that happens, you'll have to confirm the object exists before attempting to convert it to a string.
		if (Starfish.OriginData["OPPORTUNITYID"] != null)
		{
			quoteID = Starfish.XRefRead("SLXQuoteIDs",Starfish.OriginData["OPPORTUNITYID"].ToString());
			if (string.IsNullOrEmpty(quoteID))
			{
			    //Sometimes a Starfish Smart Lookup will return NULL for an field's Object.  If you try to run .ToString() on the NULL object, it will error.  Instead, confirm the object exists before attempting to convert it to a string.
			    object id;
			    id = Starfish.SmartLookup("Quotes","id","[{\"foreignid_c\":\""+Starfish.OriginData["OPPORTUNITYID"].ToString()+"\"}]");
			    if (id == null) {
			        quoteID= "";
			        Starfish.GotoNextRow();
			    } else {
			        quoteID= id.ToString();
			    }	
			}
		}
		return quoteID;
	}

Hashing Example

Here are a couple of scripts where I used C# Hashing code to save the hash of a records data so I could skip over the record later if the data was the same. I could not use only the date_modified field because I was reading and writing the same record.

Global Code

I had to include some using statements and a function in my .NET Global section:

using System.Text;
using System.Security.Cryptography;

public static byte[] GetHash(string inputString)
{
    HashAlgorithm algorithm = SHA256.Create();
    return algorithm.ComputeHash(Encoding.UTF8.GetBytes(inputString));
}

Repeat Each Row Before Operation

void CSharpProcedure()
{
	string source = Starfish.OriginData["CONTACTID"].ToString()+Starfish.OriginData["ACCOUNTID"].ToString()+Starfish.OriginData["CONTACT_TEAM_SET"].ToString()+Starfish.OriginData["ACCOUNT_TEAM_SET"].ToString()+Starfish.OriginData["ACCOUNT_TEAM_ID"];
    StringBuilder sb = new StringBuilder();
    foreach (byte b in GetHash(source))
	{
        sb.Append(b.ToString("X2"));
	}
	Starfish.LogMessage("newval: "+sb.ToString());
	
	string oldVal = "";
	oldVal = Starfish.XRefRead("hashTeamsToContacts",Starfish.OriginData["CONTACTID"].ToString());
	Starfish.LogMessage("oldval: "+oldVal);
	if(sb.ToString() == oldVal){
		if (!Starfish.PreviewMode)
		{
			Starfish.SaveSetting("AccountTeamsToContactsLDM",Starfish.OriginData["CONTACT_DATE_MODIFIED"].ToString());
		}
		Starfish.GotoNextRow();
	}
    
}


Repeat Each Row After Operation

void CSharpProcedure()
{
	if (!Starfish.PreviewMode)
	{
		Starfish.SaveSetting("AccountTeamsToContactsLDM",Starfish.OriginData["CONTACT_DATE_MODIFIED"].ToString());
		
		//Save the hash of the origin so we can not run the record again if it does not need to be run.
		string source = "";
		source = Starfish.OriginData["CONTACTID"].ToString()+Starfish.OriginData["ACCOUNTID"].ToString()+Starfish.OriginData["CONTACT_TEAM_SET"].ToString()+Starfish.OriginData["ACCOUNT_TEAM_SET"].ToString()+Starfish.OriginData["ACCOUNT_TEAM_ID"].ToString();
		StringBuilder sb = new StringBuilder();
		foreach (byte b in GetHash(source))
		{
			sb.Append(b.ToString("X2"));
		}
		Starfish.LogMessage("newval: "+sb.ToString());
		Starfish.XRefWrite("hashTeamsToContacts",Starfish.OriginData["CONTACTID"].ToString(),sb.ToString());
	}
}

Processing only records modified after the last record processed based on date_modified

Origin Filter

Use a variable like this in your origin query: @@VAR:LastModifiedDateTime@@
Example:

[{"$and":[{"modified_user_id":{"$not_equals":"53c55c2f-6304-496c-8512-1dfea42fbd87"}},{"send_to_azure_c":"1"},{"date_modified":{"$gte":"@@VAR:LastModifiedDateTime@@"}}]}]


Before Operation to retrieve last date modified or supply one if none found

object ScriptedVariable()
{
    string strDte = "";
    //Retrieve the stored datetime value.
	strDte = Starfish.GetSetting("SugarBugsToDevOps").ToString();
	//If no datetime value, create a value equal to one month ago.  This way we don't end up erroring if a value is required and we don't end up processing all records in a table, if that is not desired.
	if (strDte == "")
	{
	    strDte = DateTime.Today.AddMonths(-1).ToString();
	}
	//Convert the datetime format of the string to a format that is expected.
	DateTime dte = Convert.ToDateTime(strDte);
	strDte = dte.ToString("yyyy-MM-ddTHH:mm:ss");
	//strDte = dte.ToString("M/d/yyyy h:m:s tt");
	return strDte;
}

After Operation to save the date modified of the last processed record

void CSharpProcedure()
{
    if (!Starfish.PreviewMode)
	{
		//Set the last run datetime for this origin.
		Starfish.SaveSetting("SugarBugsToDevOps", Starfish.OriginData["DATE_MODIFIED"].ToString());
	}
}

DataTable

Be sure to include "System.dll,System.Xml.dll,System.Data.dll" in the External Assemblies and "using System.Data" in the Code of the C# Global Code window.

object ScriptedField()
{
	System.Data.DataTable prop;
	var filter = "street_address='" + Starfish.OriginData["PRIMARY_ADDRESS_STREET"].ToString() + "' AND city='" + Starfish.OriginData["PRIMARY_ADDRESS_CITY"].ToString() + "' AND state='" + Starfish.OriginData["PRIMARY_ADDRESS_STATE"].ToString() + "' AND zip_code='" + Starfish.OriginData["PRIMARY_ADDRESS_POSTALCODE"].ToString() + "'";
	prop = Starfish.SmartQueryDT("property", filter);
	if (prop.Rows.Count>0)
	{
	    for (int index = 0; index < prop.Rows.Count; index++)
	    {
            	foreach (DataColumn column in prop.Columns)
            	{
                	Starfish.LogMessage(column.ColumnName + ": " + prop.Rows[index][column.ColumnName].ToString());
                }
            }
	}

	DataColumnCollection columns = prop.Columns;
	if ((prop.Rows.Count>0) && (columns.Contains("valuation.value")))
	    return prop.Rows[0]["valuation.value"];
	else
	    return -999999;
}