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")
	{
		string fileID = ConvertObj2String(Starfish.GetStageValue(0,"#ID"));
		Starfish.LogMessage(fileID);
		Starfish.GotoNextRow();
	}

	//Reset JSON housed in Starfish memory to blank.
	Starfish.ParseJson("{}");
	//Use the ConvertObj2String Global Function to safely convert the Origin PlanId value to a string.
	Starfish.ParseJson(ConvertObj2String(Starfish.OriginData["PLANID"]));
	//Get the "amount" value from the previously parsed JSON.
	string amt = ConvertObj2String(Starfish.GetJSON("amount"));
	//Notice that "ParseJson" is lowercase "son" and "GetJSON" is uppercase "SON".  This is how Starfish requires it to be.
	Starfish.LogMessage(amt);

	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());
	}
}

Create an Array from Origin and loop Stages over items in Array

Create Global Variable To Store the Items to Loop Over

First, we need to create a Global Variable to house the items to loop over and a Global Variable to house the index of which item we are on. I prefer to use a DataTable becaues it works well with Starfish's SmartQueryDT function. In the Global C# page, add your variables:

using System.Data;
System.Data.DataTable prop;
int sugarIdIndex;

Also, be sure to include "System.dll,System.Xml.dll,System.Data.dll" in the External Assemblies box on the C# Global Code window.

Populate Global Variable of Items

Second, we need to populate the array. This is most often done in a Repeat Each Row Pre-Process:

object ScriptedField()
{
	//Clear the global variables when we start a new row.
	prop = new DataTable();
	sugarIdIndex = 0;

	//Get the data we want.
	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;
}

Loop over the data

Use an After "Repeat Each stage" Operation to loop over the data. In this case, I am only using one stage. If you need to re-process multiple stages, your code will need to manually move you from one stage to the next.

//Loop back if we have more IDs to update.
	if( sugarIdIndex < prop.Rows.Count - 1)
	{
		sugarIdIndex++;
		Starfish.GotoStage("Update CR");
	}

Using Regular Expressions

This code will take any string that contains email addresses, find all email addresses, and convert them into the JSON format used for the SugarCRM REST Connector.

In order for this script to work, you must add System.dll to your C# Global External Assemblies and 3 using statements to the Global Include Script:
using System.IO;
using System.Text.RegularExpressions;
using System.Text;

object ScriptedField()
{
	string res = "";
	bool emailsExist = false; 
	string emailJSON = "";
	
	string emails = ConvertObj2String(Starfish.OriginData["TO_ADDRS"]);
	
	//Use Regular Expressions to parse the emails from the origin information.
	//instantiate with this pattern 
	Regex emailRegex = new Regex(@"\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*",RegexOptions.IgnoreCase);
	//find items that matches with our pattern
	MatchCollection emailMatches = emailRegex.Matches(emails);
	
	//Loop through found emails and create JSON for each.
	foreach (Match emailMatch in emailMatches)
	{
		//Log that we have at least one email.
		emailsExist = true;
		
		string email = emailMatch.Value;
		string json = "";
		
		//Create the JSON For this email address.
		string userid;
		userid = ConvertObj2String(Starfish.SmartLookup("Users", "id", "[{\"email_addresses.email_address\":\"" + email + "\"}]"));
		if (!(string.IsNullOrEmpty(userid)))
		{
			json = "{\"parent_type\": \"Users\", \"parent_id\": \""+userid+"\"}";
		}
		else
		{
			string contactid = "";
			contactid = ConvertObj2String(Starfish.SmartLookup("Contacts", "id", "[{\"email_addresses.email_address\":\"" + email + "\"}]"));
			if (!(string.IsNullOrEmpty(contactid)))
			{
				json = "{\"parent_type\": \"Contacts\", \"parent_id\": \""+contactid+"\"}";
			}
			else
			{
				string emailid = "";
				emailid = ConvertObj2String(Starfish.SmartLookup("EmailAddresses","id",email));
				if (!(string.IsNullOrEmpty(emailid)))
				{			
					json = "{\"email_address_id\": \""+emailid+"\"}";
				}
			}
		}
		
		//Append the new email JSON to the larger JSON.
		if (!(string.IsNullOrEmpty(emailJSON)))
		{
			emailJSON = emailJSON + "," + json;
		}
		else
		{
			emailJSON = json;
		}
		//Starfish.LogMessage(emailJSON);
	}
	
	//If there are any emails, return the correct JSON.  If not, return blank.
	if (emailsExist)
	{
		res = "{\"create\":[" + emailJSON + "]}";
	} else 
	{
		res = "";
	}
	
	return res;
}

Useful Functions

ConvertObj2String

string ConvertObj2String(object obj)
{
    if ( obj == null || obj == System.DBNull.Value || String.IsNullOrWhiteSpace(obj.ToString()))
        return "";
    return obj.ToString();
}