public LoadCollectionResponseData getEventStatus(string eventName)
{
LoadCollectionRequestData Load = new LoadCollectionRequestData();
Load.IDOName = "EventStates";
Load.PropertyList.Add("EventName");
Load.PropertyList.Add("Status");
Load.PropertyList.Add("Originator");
Load.PropertyList.Add("EventParmId");
Load.Filter = "EventName = '" + eventName + "' AND Status = '2'";
Load.OrderBy = "";
Load.RecordCap = 100;
LoadCollectionResponseData response = IDOClient.LoadCollection(Load);
if (response.Items.Count > 0)
{
ThisForm.Variables("varDebug").Value += response[0, "EventName"].Value + "\n";
ThisForm.Variables("varDebug").Value += response[0, "Status"].Value + "\n";
ThisForm.Variables("varDebug").Value += response[0, "Originator"].Value + "\n";
for (int i = 0; i < response.Items.Count; i++)
{
ThisForm.Variables("varDebug").Value += response[i, "EventParmId"].Value + "\n";
}
}
return(response);
}
private static LoadCollectionRequestData getLoadCollectionRequest(IIdoQueryBuilder idoParameterBuilder, bool topLevel)
{
var props = idoParameterBuilder.GetProperties();
if (props.IsNullOrEmpty())
{
idoParameterBuilder.Add(new[] { IdoConstants.KEY });
}
var loadCollectionRequest = new LoadCollectionRequestData(idoParameterBuilder.GetIDOName(),
idoParameterBuilder.GetProperties(),
idoParameterBuilder.GetFilter(), "", idoParameterBuilder.GetMaxResults()) { LoadCap = 0 };
loadCollectionRequest.SetContext("AUTOMATION", "AUTOMATION", "AUTOMATION", "IDO");
if (idoParameterBuilder.HasLinkFields)
{
if (topLevel)
throw new SytelineInterfaceQueryException();
var pairList = idoParameterBuilder.GetLinkFields();
var linkField = pairList[0];
if (pairList.Length == 1)
loadCollectionRequest.SetLinkBy(linkField.ParentProperty, linkField.ChildProperty);
else
loadCollectionRequest.SetLinkBy(linkField.ParentProperty, linkField.ChildProperty,
get_additional_pairs(pairList));
}
foreach (IIdoQueryBuilder child in idoParameterBuilder.GetChildren())
{
loadCollectionRequest.AddNestedRequest(getLoadCollectionRequest(child, false));
}
return loadCollectionRequest;
}
public void EmailRelated()
{
var emailList = string.Empty;
var contactIdList = string.Empty;
var emailQueryFilter = "EntityID in ({0}) and IsPrimary = 1";
var accountId = ThisForm.PrimaryIDOCollection.CurrentItem.Properties["ID"].GetValueOfString(string.Empty);
var acctFilter = string.Format("AccountID='{0}'", accountId);
var req = new LoadCollectionRequestData("CRMContact", "ID", acctFilter, "", 0);
Application.DiagnosticsLog("EmailRelated: Load Collection contact");
var resp = IDOClient.LoadCollection(req);
var mailToUrl = "mailto:";
if (resp.Items != null && resp.Items.Count > 0)
{
for (int i = 0; i < resp.Items.Count; i++)
{
Application.DiagnosticsLog("EmailRelated: " + resp.Items.Count.ToString());
var tempId = resp.Items[i].PropertyValues[0].Value;
// filter ID values need wrapped in single quotes to pass through load collection
if (!string.IsNullOrEmpty(contactIdList))
{
contactIdList += @",";
}
contactIdList += @"'" + tempId + @"'";
}
}
var emailFilter = string.Format(emailQueryFilter, contactIdList);
if (!string.IsNullOrEmpty(contactIdList))
{
var emailReq = new LoadCollectionRequestData("CRMEmail", "Address", emailFilter, "Address", 0);
Application.DiagnosticsLog("EmailRelated: Load Collection email");
var emailResp = IDOClient.LoadCollection(emailReq);
for (int i = 0; i < emailResp.Items.Count; i++)
{
var tempEmail = emailResp.Items[i].PropertyValues[0].Value;
if (!string.IsNullOrEmpty(emailList))
{
emailList += ";";
}
emailList += tempEmail;
}
}
mailToUrl += emailList;
ThisForm.Variables("VarMailToUrl").SetValue(mailToUrl);
ThisForm.Components["linkEmailRelated"].SetValue(mailToUrl);
ThisForm.Components["linkEmailRelated"].Caption = Application.GetStringValue("sEmailRelated");
}
public void CalculateAccountKPIs(string username)
{
var req = new LoadCollectionRequestData("CRMAccountManagerKPI",
"WinPercent, TotalPotential, TotalSales", string.Format("Username='******'", username), "", 1);
var resp = IDOClient.LoadCollection(req);
decimal salesPotential = 0.00M, actualSales = 0.00M, wins = 0.00M;
if (resp.Items != null && resp.Items.Count > 0)
{
foreach (var t in resp.Items)
{
if (t.PropertyValues.Count > 0 && !string.IsNullOrEmpty(t.PropertyValues[0].Value))
{
wins = t.PropertyValues[0].GetValue <decimal>();
}
if (t.PropertyValues.Count > 1 && !string.IsNullOrEmpty(t.PropertyValues[1].Value))
{
salesPotential = t.PropertyValues[1].GetValue <decimal>();
}
if (t.PropertyValues.Count > 2 && !string.IsNullOrEmpty(t.PropertyValues[2].Value))
{
actualSales = t.PropertyValues[2].GetValue <decimal>();
}
}
}
ThisForm.Variables("VarKPIPipelineData").SetValue(FormatNumberForKPIDisplay(salesPotential));
ThisForm.Variables("VarKPIWinsData").SetValue(FormatNumberForKPIDisplay(actualSales));
if (wins > 0)
{
ThisForm.Variables("VarKPIWinRateData").SetValue(wins.ToString("P0",
new NumberFormatInfo {
PercentPositivePattern = 1, PercentNegativePattern = 1
}));
}
else
{
ThisForm.Variables("VarKPIWinRateData").SetValue("N/A");
}
}
public string getTableNameFromIdo(string ido)
{
LoadCollectionRequestData Load = new LoadCollectionRequestData();
Load.IDOName = "IdoTables";
Load.PropertyList.Add("TableName");
Load.Filter = "CollectionName = '" + ido + "'";
Load.OrderBy = "";
Load.RecordCap = 1;
LoadCollectionResponseData idoResponse = Context.Commands.LoadCollection(Load);
if (idoResponse.Items.Count <= 0)
{
return(null);
}
else
{
return(idoResponse[0, "TableName"].Value);
}
}
public LoadCollectionResponseData getEventOutputParameters(string eventParmId)
{
LoadCollectionRequestData Load = new LoadCollectionRequestData();
Load.IDOName = "EventOutputParameters";
Load.PropertyList.Add("Name");
Load.PropertyList.Add("Value");
Load.PropertyList.Add("Originator");
Load.PropertyList.Add("EventParmId");
Load.PropertyList.Add("EventName");
Load.Filter = "EventParmId = '" + eventParmId + "'";
Load.OrderBy = "";
Load.RecordCap = 10;
LoadCollectionResponseData response = IDOClient.LoadCollection(Load);
//ThisForm.Variables("varDebug").Value += "\n\nEvent Output Parameters\n";
//ThisForm.Variables("varDebug").Value += response[0, "EventName"].Value + "\n";
//ThisForm.Variables("varDebug").Value += response[0, "EventParmId"].Value + "\n";
//ThisForm.Variables("varDebug").Value += response[0, "Originator"].Value + "\n";
return(response);
}
public void OnLocationChange()
{
if (string.IsNullOrWhiteSpace(ThisForm.Variables("VarAccountIdComponentKey").Value) ||
string.IsNullOrWhiteSpace(ThisForm.Variables("VarLocationIdComponentKey").Value) ||
string.IsNullOrWhiteSpace(ThisForm.Variables("VarAccountingEntityComponentKey").Value))
{
return;
}
var accountIdComponentKey = ThisForm.Variables("VarAccountIdComponentKey").Value;
var locationIdComponentKey = ThisForm.Variables("VarLocationIdComponentKey").Value;
var accountingEntityComponentKey = ThisForm.Variables("VarAccountingEntityComponentKey").Value;
if (!ThisForm.Components.ContainsKey(accountIdComponentKey) ||
!ThisForm.Components.ContainsKey(locationIdComponentKey) ||
!ThisForm.Components.ContainsKey(accountingEntityComponentKey))
{
return;
}
var integrationEnabled = Application.Variables("VarCRMIsBOEEnabled").Value;
var accountID = ThisForm.Components[accountIdComponentKey].GetValueOfString(string.Empty);
if (integrationEnabled == "1" && string.IsNullOrWhiteSpace(accountID))
{
var locationId = ThisForm.Components[locationIdComponentKey].GetValueOfString(string.Empty);
if (string.IsNullOrWhiteSpace(locationId))
{
ThisForm.Components[accountingEntityComponentKey].SetValue(string.Empty);
return;
}
var request = new LoadCollectionRequestData("CRMLocation", "SORAccountingEntityID", string.Format("ID='{0}'", locationId), string.Empty, 1);
var response = IDOClient.LoadCollection(request);
if (response != null && response.Items.Count > 0)
{
ThisForm.Components[accountingEntityComponentKey].SetValue(response.Items[0].PropertyValues[0].GetValue(string.Empty));
}
}
}
/// <summary>
/// Insert trxHeader record with approval of adjustments
/// Insert all trxLine record with adjustments under trxHeader
/// </summary>
public void Submit()
{
string trxHeaderIdoName = "CRMWarehouseLocationTrxHeader";
string trxType = "StockAdjust";
string todayDate = DateTime.UtcNow.ToString("yyyy-MM-ddThh:mm:ss.fffZ");
string stockBin = ThisForm.Variables("VarStockBin").Value;
//INSERT TRX HEADER RECORD
UpdateCollectionRequestData insert = new UpdateCollectionRequestData(trxHeaderIdoName);
IDOUpdateItem itm = new IDOUpdateItem(UpdateAction.Insert);
itm.Properties.Add("TrxType", trxType);
itm.Properties.Add("TrxDate", todayDate);
itm.Properties.Add("VanStockLocation", stockBin);
itm.Properties.Add("Status", "Posted");
insert.Items.Add(itm);
try {
IDOClient.UpdateCollection(insert);
} catch (Exception e) {
Application.ShowMessage("Unable to insert record to " + trxHeaderIdoName + "\n" + e.Message);
}
Application.ShowMessage("Successfully submitted TrxHeader at " + todayDate);
//GET TRX HEADER ID FOR TRX LINES
LoadCollectionRequestData trxHeaderLoadRequest = new LoadCollectionRequestData();
trxHeaderLoadRequest.IDOName = trxHeaderIdoName;
trxHeaderLoadRequest.PropertyList.Add("ID");
trxHeaderLoadRequest.Filter = "TrxDate = '" + todayDate + "'";
trxHeaderLoadRequest.OrderBy = "";
trxHeaderLoadRequest.RecordCap = 1;
LoadCollectionResponseData trxHeaderLoadResponse = IDOClient.LoadCollection(trxHeaderLoadRequest);
if (trxHeaderLoadResponse.Items.Count != 1)
{
Application.ShowMessage("Could not load " + trxHeaderIdoName + " record with TrxDate = " + todayDate);
return;
}
//INSERT TRX LINE RECORD
insert = new UpdateCollectionRequestData("CRMWarehouseStockLotLocationTrx");
ThisForm.PrimaryIDOCollection.First();
for (int i = 0; i < ThisForm.PrimaryIDOCollection.GetNumEntries() - 1; i++)
{
string trxHeader = trxHeaderLoadResponse[0, "ID"].Value;
string productID = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("ProductID");
string locationID = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("LocationID");
string lotCode = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("StockLotCode");
string systemQuantity = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("QuantityOnHand");
string adjustment = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("Adjustment");
string reason = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("Reason");
Application.ShowMessage(productID + "\n" + locationID + "\n" + lotCode + "\n" + systemQuantity + "\n" + adjustment + "\n" + reason);
//if adjustment is empty, skip
if (adjustment.Equals(""))
{
ThisForm.PrimaryIDOCollection.Next();
continue;
}
itm = new IDOUpdateItem(UpdateAction.Insert);
itm.Properties.Add("TrxHeader", trxHeader);
itm.Properties.Add("ProductID", productID);
itm.Properties.Add("LocationID", locationID);
itm.Properties.Add("StockLotCode", lotCode);
itm.Properties.Add("QuantityOnHand", systemQuantity);
itm.Properties.Add("CountAdjustment", adjustment);
itm.Properties.Add("Reason", reason);
insert.Items.Add(itm);
ThisForm.PrimaryIDOCollection.Next();
}
try {
IDOClient.UpdateCollection(insert);
} catch (Exception e) {
Application.ShowMessage("Unable to insert into CRMWarehouseStockLotLocationTrx\n" + e.Message);
}
}
public void idoLoad()
{
LogMessage("idoLoad");
LoadCollectionRequestData Load = new LoadCollectionRequestData();
Load.IDOName = ido;
//ONE PROPERTY
if (!properties.Contains(","))
{
Load.PropertyList.Add(properties);
}
else
{
string[] fields = properties.Split(',');
for (int i = 0; i < fields.Length; i++)
{
//LogMessage(fields[i]);
Load.PropertyList.Add(fields[i]);
}
}
Load.Filter = filter;
Load.OrderBy = orderBy;
Load.RecordCap = recordCap;
string result = String.Empty;
stopwatch.Start();
LogMessage("After stopwatch");
LoadCollectionResponseData response = null;
try {
response = Context.Commands.LoadCollection(Load);
} catch (Exception e) {
LogMessage("Failed to load " + ido + "\n" + e.Message);
}
LogMessage("After load");
if (response.Items.Count <= 0)
{
LogMessage(ido + " was not found");
}
else
{
for (int i = 0; i < response.Items.Count; i++)
{
for (int j = 0; j < fields.Length; j++)
{
try {
result += response[i, fields[j]].Value + " " + response.Items[i].ItemID + "\n";
} catch (Exception e) {
LogMessage("Exception at row " + i + " column " + j + "\n" + e.Message);
}
}
result += "\n\n";
}
}
stopwatch.Stop();
idoLoadTime = timeElapsed(stopwatch);
stopwatch.Reset();
LogMessage(result);
}
public LoadCollectionResponseData LoadCollection(LoadCollectionRequestData request)
{
return _client.LoadCollection(request);
}
