public void QueryTop3CustomersSample()
{
String query = @"Select Top 3 CardCode as 'Code', CardName as 'Name', Balance as 'Balance' From OCRD Where CardType = 'C' Order by Balance DESC";
try
{
Recordset oRS = ExecuteQuery(query);
// loop recordset
if (oRS != null)
{
String bpCode = String.Empty;
String bpName = String.Empty;
Double balance = Double.MinValue;
String msg = "Customer Code : {0}. BP Name: {1}. Balance: {2}";
while (!oRS.EoF)
{
bpCode = oRS.Fields.Item("Code").Value;
bpName = oRS.Fields.Item("Name").Value;
balance = oRS.Fields.Item("Balance").Value;
AddOnUtilities.MsgBoxWrapper(String.Format(msg, bpCode, bpName, balance));
oRS.MoveNext();
}
}
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
}
public void BrowserTop3CustomerSample()
{
String query = @"Select Top 3 CardCode as 'Code', CardName as 'Name', Balance as 'Balance' From OCRD Where CardType = 'C' Order by Balance DESC";
try
{
Recordset oRS = ExecuteQuery(query);
SAPbobsCOM.BusinessPartners oBP = AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners);
// set the RecordSet to Browser of BO
oBP.Browser.Recordset = oRS;
String bpCode = String.Empty;
String bpName = String.Empty;
Double balance = Double.MinValue;
String msg = "Customer Code : {0}. BP Name: {1}. Balance: {2}";
oBP.Browser.MoveFirst();
while (!oBP.Browser.EoF)
{
bpCode = oBP.CardCode;
bpName = oBP.CardName;
balance = oBP.CurrentAccountBalance; // read from DI API properties
AddOnUtilities.MsgBoxWrapper(String.Format(msg, bpCode, bpName, balance));
oBP.SaveXML(oBP.CardCode + ".xml");
oBP.Browser.MoveNext();
}
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
}
/// <summary>
/// Get Data Source From Configration Table [TableName = @OCFL]
/// If eventType is not provided, this function will assume as "Tab" event.
/// </summary>
/// <param name="formId"></param>
/// <param name="itemId"></param>
/// <param name="colId"></param>
/// <param name="eventType"></param>
/// <returns></returns>
public static Recordset GetConfiguration(string formId, string itemId, string colId, int eventType)
{
SAPbobsCOM.Recordset oRecordSet = null;
string documentType = String.Empty;
try
{
colId = (colId == "" ? "0" : colId); //If colUID is "0", the event return as "".
if (itemId == "38") // Item
{
documentType = "1";
}
else if (itemId == "39") // Service
{
documentType = "2";
}
else
{
documentType = "3";
}
if (formId.Contains("UDO"))
{
formId = System.Text.RegularExpressions.Regex.Replace(formId.Substring(formId.LastIndexOf('_') + 1), @"[\d-]", string.Empty);
}
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT * FROM \"@OCFL\" WHERE \"U_FormId\" = '" + formId + "' AND \"U_ItemId\" = '" + itemId + "' AND \"U_ColumnId\" = '" + colId + "' AND \"U_EventType\" = " + eventType + " AND \"U_DocType\" = " + documentType;
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
public DIServerResult AddObject(String sessionID, String commandID, string sBOM)
{
DIServerResult result = new DIServerResult();
try
{
String soapResponse = Interact(String.Format(Resources.AddObjectSoapRequest, sessionID, commandID, sBOM));
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(soapResponse);
XmlNode node = xmlDoc.SelectSingleNode("//*[local-name()='RetKey']");
if (node == null)
{
//error
AddOnUtilities.MsgBoxWrapper("DI Server - Add Obj Failed.");
result.Success = false;
}
else
{
AddOnUtilities.MsgBoxWrapper(String.Format("DI Server - Add Obj Success. Return Key: {0}.", node.InnerText));
result.Success = true;
}
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(result);
}
public static void SetValue(ref SAPbouiCOM.Form oForm, String tableName, String newValue, String fieldIndex, int rowIndex = 0)
{
try
{
SAPbouiCOM.DBDataSource dbDataSource = oForm.DataSources.DBDataSources.Item(tableName);
dbDataSource.SetValue(fieldIndex, rowIndex, newValue);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
}
public static String GetValue(ref SAPbouiCOM.Form oForm, String tableName, String fieldIndex, int rowIndex = 0)
{
try
{
SAPbouiCOM.DBDataSource dbDataSource = oForm.DataSources.DBDataSources.Item(tableName);
return(dbDataSource.GetValue(fieldIndex, rowIndex));
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(String.Empty);
}
public String Interact(String soapRequest)
{
String soapResponse = String.Empty;
try
{
soapResponse = GetDIServerNode().Interact(soapRequest);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(soapResponse);
}
public static string GetMatrixData(SAPbouiCOM.Matrix oMatrix, string itemId, string colId, int rowIndex)
{
string input = String.Empty;
try
{
input = ((SAPbouiCOM.EditText)oMatrix.Columns.Item(colId).Cells.Item(rowIndex).Specific).Value;
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(input);
}
public SAPbobsCOM.Recordset ExecuteQuery(String query)
{
SAPbobsCOM.Recordset oRS = null;
try
{
oRS = AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRS.DoQuery(query);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRS);
}
/// <summary>
/// Get All Data From Configuration Table [TableName = @OCFL]
/// </summary>
/// <returns></returns>
public static Recordset GetAllConfiguration()
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT * FROM \"@OCFL\" ORDER BY \"U_AccessMatrixKey\"";
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
/// <summary>
/// Retrieve all dimension 5 from SAP.
/// </summary>
/// <returns>RecordSet filled with Dimension 5.</returns>
public static Recordset GetDimension5()
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT T0.\"OcrCode\", T0.\"OcrName\" FROM OOCR T0 WHERE T0.\"DimCode\" = 5";
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
public static Recordset GetAccountsByUser()
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT T1.\"U_AccountCode\" FROM \"@OUAC\" T0 JOIN \"@UAC1\" T1 ON T0.\"Code\" = T1.\"Code\" JOIN \"OACT\" T2 ON T1.\"U_AccountCode\" = T2.\"AcctCode\" WHERE T0.\"U_User\" = (SELECT \"USER_CODE\" FROM OUSR WHERE \"INTERNAL_K\" = " + AddOnUtilities.oCompany.UserSignature + ")";
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
/// <summary>
/// Retrieve dimension 5 Name by dimension 5 Code.
/// </summary>
/// <returns>Dimension 5 Name.</returns>
public static string GetDimension5NameByCode(string strDimension5Code)
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT T0.\"OcrName\" FROM OOCR T0 WHERE T0.\"DimCode\" = 5 AND T0.\"OcrCode\" = '" + strDimension5Code + "'";
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet.Fields.Item(0).Value.ToString());
}
public static Recordset GetConfigurationForLineLoop(string formId, string itemId, string colId, int eventType)
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
colId = (colId == "" ? "0" : colId); //If colUID is "0", the event return as "".
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT * FROM \"@OCFL\" WHERE \"U_FormId\" = '" + formId + "' AND \"U_ItemId\" = '" + itemId + "' AND \"U_ColumnId\" = '" + colId + "' AND \"U_EventType\" = " + eventType;
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
/// <summary>
/// Retrieve all branches from SAP.
/// </summary>
/// <returns>RecordSet filled with Branches.</returns>
public static Recordset GetBranches()
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "SELECT T0.\"BPLName\" FROM OBPL T0";
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
private void btnCreateQueries_Click(object sender, EventArgs e)
{
UserQueries oUserQueries = (SAPbobsCOM.UserQueries)AddOnUtilities.oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
QueryCategories oQueryCategories = (SAPbobsCOM.QueryCategories)AddOnUtilities.oCompany.GetBusinessObject(BoObjectTypes.oQueryCategories);
int lRetCode = 0, queryCategoryId = 0;
string strQuery = "select \"CategoryId\" from \"OQCN\" where \"CatName\" = 'Customization'";
SAPbobsCOM.Recordset oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
oRecordSet.DoQuery(strQuery);
bool isQueryCategoryExist = Convert.ToBoolean(oRecordSet.Fields.Item(0).Value);
if (!isQueryCategoryExist)
{
oQueryCategories.Name = "Customization";
oQueryCategories.Permissions = "YYYYYYYYYYYYYYY";
lRetCode = oQueryCategories.Add();
if (lRetCode != 0)
{
string errMsg = AddOnUtilities.oCompany.GetLastErrorDescription();
MessageBox.Show("Failed to create Query Category.");
MessageBox.Show(errMsg);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(oQueryCategories);
}
List <string[]> lstQueryList = AddOnUtilities.ReadQueries("C:\\Users\\LynnHtet\\Desktop\\UserQueryList.txt");
queryCategoryId = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
foreach (string[] query in lstQueryList)
{
oUserQueries.QueryDescription = query[0];
oUserQueries.Query = query[1];
oUserQueries.QueryType = UserQueryTypeEnum.uqtWizard;
oUserQueries.QueryCategory = queryCategoryId;
lRetCode = oUserQueries.Add();
if (lRetCode != 0)
{
string errMsg = AddOnUtilities.oCompany.GetLastErrorDescription();
MessageBox.Show("Failed to create Query Category.");
MessageBox.Show(errMsg);
}
}
}
public static string GetInputValue(string itemId, string colId, string controlType, string relatedCol = "")
{
string input = String.Empty;
try
{
int rowIndex;
SAPbouiCOM.Matrix oMatrix = null;
switch (controlType)
{
case "1": //Textbox
input = ((SAPbouiCOM.EditText)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(itemId).Specific).Value;
break;
case "2": //Checkbox (Only for BP Selection Window)
string colIdForDescription = (relatedCol == "" ? itemId : relatedCol);
oMatrix = (SAPbouiCOM.Matrix)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(itemId).Specific;
rowIndex = oMatrix.GetCellFocus().rowIndex;
input = ((SAPbouiCOM.EditText)oMatrix.Columns.Item(colIdForDescription).Cells.Item(rowIndex).Specific).Value;
break;
case "3": //Dropdown List
break;
case "4": //Matrix
oMatrix = (SAPbouiCOM.Matrix)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(itemId).Specific;
rowIndex = oMatrix.GetCellFocus().rowIndex;
input = ((SAPbouiCOM.EditText)oMatrix.Columns.Item(colId).Cells.Item(rowIndex).Specific).Value;
break;
default:
break;
}
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(input);
}
public String Login(String DbServer, String DbName, String DbType, String User, String Password, String Language, String License)
{
String sessionID = String.Empty;
try
{
// 1. Get Soap Request + Pass Parameters
String soapLoginRequest = String.Format(Resources.DIServerLoginSOAP, DbServer, DbName, DbType, User, Password, Language, License);
// 2. Send to DI Server Node
String soapLoginResponse = Interact(soapLoginRequest);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(soapLoginResponse);
XmlNode xmlNode = xmlDoc.SelectSingleNode("//*[local-name()='SessionID']");
if (xmlNode == null)
{
// Error
AddOnUtilities.MsgBoxWrapper("DI Server Login failed.");
}
else
{
// Success
sessionID = xmlNode.InnerText;
sSessionID = sessionID;
AddOnUtilities.MsgBoxWrapper(string.Format("DI Server Login Success. {0}", sessionID));
}
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(sessionID);
}
public static Recordset GetBusinessPartnerNamesByUser()
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = String.Empty;
if (ConfigurationManager.AppSettings["DbType"].ToString().ToUpper() == "SQL")
{
strQuery = "SELECT T0.\"CardName\", T0.\"CardCode\" FROM OCRD T0 INNER JOIN \"@UBP1\" T1 ON T1.\"U_GroupCode\" = T0.\"GroupCode\" INNER JOIN \"@OUBP\" T2 ON T2.\"Code\" = T1.\"Code\" WHERE T2.\"U_User\" = (SELECT \"USER_CODE\" FROM OUSR WHERE \"INTERNAL_K\" = " + AddOnUtilities.oCompany.UserSignature + ")";
}
else
{
strQuery = "SELECT T0.\"CardName\", T0.\"CardCode\" FROM OCRD T0 INNER JOIN \"@UBP1\" T1 ON T1.\"U_GroupCode\" = T0.\"GroupCode\" INNER JOIN \"@OUBP\" T2 ON T2.\"Code\" = T1.\"Code\" WHERE T2.\"U_User\" = (SELECT \"USER_CODE\" FROM OUSR WHERE \"INTERNAL_K\" = " + AddOnUtilities.oCompany.UserSignature + ")";
}
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
public static Recordset GetAllBusinessPartnerGroups()
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = String.Empty;
if (ConfigurationManager.AppSettings["DbType"].ToString().ToUpper() == "SQL")
{
strQuery = "SELECT T0.\"GroupCode\", T0.\"GroupName\" FROM OCRG T0";
}
else
{
strQuery = "SELECT T0.\"GroupCode\", T0.\"GroupName\" FROM OCRG T0";
}
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
private void CreateUDO(string udoName, string udoCode, BoUDOObjType udoType, string headerTableName, List <String> childTables, BoYesNoEnum isMenuItem, string menuCaption, int fatherMenuId, int menuPosition, BoYesNoEnum canCancel = BoYesNoEnum.tYES, BoYesNoEnum canClose = BoYesNoEnum.tNO, BoYesNoEnum canCreateDefaultForm = BoYesNoEnum.tYES, BoYesNoEnum canDelete = BoYesNoEnum.tYES, BoYesNoEnum canFind = BoYesNoEnum.tYES, BoYesNoEnum canLog = BoYesNoEnum.tYES, BoYesNoEnum canYearTransfer = BoYesNoEnum.tNO)
{
try
{
if (!(AddOnUtilities.UDOExist(udoName)))
{
UserObjectsMD UDO = (UserObjectsMD)AddOnUtilities.oCompany.GetBusinessObject(BoObjectTypes.oUserObjectsMD);
//Set Services
// TODO: use optional params for properties below
UDO.CanCancel = canCancel;
UDO.CanClose = canClose;
UDO.CanCreateDefaultForm = canCreateDefaultForm;
UDO.CanDelete = canDelete;
UDO.CanFind = canFind;
UDO.CanLog = canLog;
UDO.CanYearTransfer = canYearTransfer;
UDO.TableName = headerTableName;
UDO.FormColumns.SonNumber = childTables.Count;
UDO.EnableEnhancedForm = BoYesNoEnum.tYES; // To show Header Line Style
UDO.Code = udoCode;
UDO.Name = udoName;
UDO.ObjectType = udoType;
//Display columns
UDO.FormColumns.FormColumnAlias = "Code";
UDO.FormColumns.FormColumnDescription = "Code";
UDO.FormColumns.Editable = BoYesNoEnum.tYES;
UDO.FormColumns.Add();
UDO.FormColumns.FormColumnAlias = "U_User";
UDO.FormColumns.FormColumnDescription = "User";
UDO.FormColumns.Editable = BoYesNoEnum.tYES;
UDO.FormColumns.Add();
//UDO.FormColumns.FormColumnAlias = "U_Dimension1";
//UDO.FormColumns.FormColumnDescription = "Dimension 1";
//UDO.FormColumns.Add();
//UDO.FormColumns.FormColumnAlias = "U_Dimension2";
//UDO.FormColumns.FormColumnDescription = "Dimension 2";
//UDO.FormColumns.Add();
//Columns to be used in find mode
UDO.FindColumns.ColumnAlias = "Code";
UDO.FindColumns.ColumnDescription = "Code";
UDO.FindColumns.Add();
UDO.FindColumns.ColumnAlias = "U_User";
UDO.FindColumns.ColumnDescription = "User";
UDO.FindColumns.Add();
// Set UDO to have a menu
UDO.MenuItem = isMenuItem;
UDO.MenuCaption = menuCaption;
// Set father and gnment of menu item.
UDO.FatherMenuID = fatherMenuId;
UDO.Position = 1;
// Set UDO menu UID
UDO.MenuUID = "UserDimension";
//Link with child tables.
foreach (var childTable in childTables)
{
UDO.ChildTables.TableName = childTable;
UDO.ChildTables.Add();
}
int RetCode = UDO.Add();
if (RetCode != 0)
{
string errMsg = AddOnUtilities.oCompany.GetLastErrorDescription();
MessageBox.Show("Failed to add UDO");
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(UDO);
}
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
SAPbouiCOM.EventForm e = null;
}
}
private void btnConnectUI_Click(object sender, EventArgs e)
{
AddOnUtilities.ConnectViaUI();
}
public static string CheckIfValidOrNotAllLine(SAPbobsCOM.Recordset rsConfigData, string documentType)
{
try
{
string controlName = rsConfigData.Fields.Item("U_ControlName").Value.ToString();
string controlType = rsConfigData.Fields.Item("U_ControlType").Value.ToString();
string accessMatrixKey = rsConfigData.Fields.Item("U_AccessMatrixKey").Value.ToString();
string itemId = rsConfigData.Fields.Item("U_ItemId").Value.ToString();
string colId = rsConfigData.Fields.Item("U_ColumnId").Value.ToString();
string relatedItemId = rsConfigData.Fields.Item("U_RelatedItem").Value.ToString();
string relatedColId = rsConfigData.Fields.Item("U_RelatedColumn").Value.ToString();
string relatedItemId2 = rsConfigData.Fields.Item("U_RelatedItem2").Value.ToString();
string relatedColId2 = rsConfigData.Fields.Item("U_RelatedColumn2").Value.ToString();
string result = "Invalid " + controlName + " at line : ";
bool isValid = true, isValidAll = true;
SAPbobsCOM.Recordset rs = null;
if (controlName == "Business Partners")
{
rs = GetBusinessPartnersByUser();
}
else if (controlName.Contains("Dimension"))
{
string branch = "";
bool isBranchId = false;
dynamic BranchControl = AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId2).Specific;
if (BranchControl is SAPbouiCOM.ComboBox)
{
branch = ((SAPbouiCOM.ComboBox)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId2).Specific).Value;
isBranchId = true;
}
else //if(BranchControl is SAPbouiCOM.EditText)
{
branch = ((SAPbouiCOM.EditText)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId2).Specific).Value;
isBranchId = false;
}
rs = GetDimensionsByBranch(controlName, branch, isBranchId);
}
else if (controlName == "Project")
{
rs = GetProjectsByUser();
}
else if (controlName == "G/L Account")
{
rs = GetAccountsByUser();
}
SAPbouiCOM.Matrix oMatrix = (SAPbouiCOM.Matrix)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId).Specific;
int RowCount = oMatrix.RowCount;
for (int CurrentRow = 1; CurrentRow < RowCount; CurrentRow++)
{
string input = ((SAPbouiCOM.EditText)(oMatrix.Columns.Item(relatedColId).Cells.Item(CurrentRow).Specific)).Value;
rs.MoveFirst();
if (input != "")
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
isValid = true;
break;
}
else
{
isValid = false;
}
}
if (!isValid && rs.EoF)
{
isValidAll = false;
result += CurrentRow.ToString() + ", ";
}
}
}
if (!isValidAll)
{
result = result.Substring(0, result.Length - 2) + ".";
return(result);
}
return("");
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
return(ex.Message + " " + ex.StackTrace);
}
}
/// <summary>
/// Check if User's input value is valid or not.
/// </summary>
/// <param name="rsConfigData"></param>
/// <returns>If input is invalid, return value starts with "Invalid". If input is valid, return "".</returns>
public static string CheckIfValidOrNot(SAPbobsCOM.Recordset rsConfigData)
{
try
{
string controlName = rsConfigData.Fields.Item("U_ControlName").Value.ToString();
string controlType = rsConfigData.Fields.Item("U_ControlType").Value.ToString();
string accessMatrixKey = rsConfigData.Fields.Item("U_AccessMatrixKey").Value.ToString();
string itemId = rsConfigData.Fields.Item("U_ItemId").Value.ToString();
string colId = rsConfigData.Fields.Item("U_ColumnId").Value.ToString();
string input = GetInputValue(itemId, colId, controlType);
string result = String.Empty;
SAPbobsCOM.Recordset rs = null;
//Retrieve valid data according to the control name.
if (controlName == "Business Partner")
{
rs = GetBusinessPartnersByUser();
}
else if (controlName == "Business Partner Name")
{
rs = GetBusinessPartnerNamesByUser();
}
else if (controlName == "All Business Partner Group")
{
rs = GetAllBusinessPartnerGroups();
}
else if (controlName.StartsWith("Dimension")) //Dimension by Branch
{
string branch = "";
string relatedItemId = rsConfigData.Fields.Item("U_RelatedItem").Value.ToString();
string relatedColId = rsConfigData.Fields.Item("U_RelatedColumn").Value.ToString();
bool isBranchId = false;
//Use Dynamic because Control Type can be identified only during runtime. It depends on the document types.
//If Control type is ComboBox, branch value from UI is ID.
//If Control type is EditText, branch value from UI is Value.
dynamic BranchControl = AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId).Specific;
if (BranchControl is SAPbouiCOM.ComboBox)
{
branch = ((SAPbouiCOM.ComboBox)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId).Specific).Value;
isBranchId = true;
}
else //if(BranchControl is SAPbouiCOM.EditText)
{
branch = ((SAPbouiCOM.EditText)AddOnUtilities.oApplication.Forms.ActiveForm.Items.Item(relatedItemId).Specific).Value;
isBranchId = false;
}
rs = GetDimensionsByBranch(controlName, branch, isBranchId);
}
else if (controlName == "Project")
{
rs = GetProjectsByUser();
}
else if (controlName == "All Users")
{
rs = GetUsers();
}
else if (controlName == "All Projects")
{
rs = GetProjects();
}
else if (controlName.Contains("Dimension"))
{
string dimensionType = controlName.ElementAt(controlName.Length - 1).ToString();
switch (dimensionType)
{
case "1":
rs = GetDimension1();
break;
case "2":
rs = GetDimension2();
break;
case "3":
rs = GetDimension3();
break;
case "4":
rs = GetDimension4();
break;
case "5":
rs = GetDimension5();
break;
}
}
else if (controlName == "All Branches")
{
rs = GetBranches();
}
else if (controlName == "G/L Account")
{
rs = GetAccountsByUser();
}
else if (controlName == "All Accounts")
{
rs = GetAllAccounts();
}
//Retrieve user's input value.
//input = GetInputValue(itemId, colId, controlType);
// Below switch case statement is used to identify which kind of data is valid/invalid. So that error can be showed as "Invalid Input Type (Project, User, Dimension, etc.)
// Check if user's input is in valid values.
switch (accessMatrixKey)
{
//------------------------------------------------------------------------ Checking for Projects ------------------------------------------------------------------------//
case "1": // Projects by User
case "5": // All Projects
if (input != "")
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
return("");
}
}
return("Invalid Project.");
}
break;
//------------------------------------------------------------------------ End of Checking for Projects ------------------------------------------------------------------------//
//------------------------------------------------------------------------ Checking for Dimensions ------------------------------------------------------------------------//
case "2": // Dimensions by Branch
case "6": // All Dimension 1
case "7": // All Dimension 2
case "8": // All Dimension 3
case "9": // All Dimension 4
case "10": // All Dimension 5
if (input != "") // To check if project column is blank or not first.
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
return("");
}
}
return("Invalid Dimension.");
}
break;
//------------------------------------------------------------------------ End of Checking for Dimensions ------------------------------------------------------------------------//
//------------------------------------------------------------------------ Checking for Business Partners ------------------------------------------------------------------------//
case "3": // Business Partners by User
case "12": // All Business Partners
if (input != "")
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
return("");
}
}
return("Invalid Business Partner.");
}
break;
//------------------------------------------------------------------------ End of Checking for Business Partners ------------------------------------------------------------------------//
//------------------------------------------------------------------------ Start of Checking for G/L Account ------------------------------------------------------------------------//
case "13": // Chart of Accounts By User
if (input != "")
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
return("");
}
}
return("Invalid Account Code.");
}
break;
//------------------------------------------------------------------------ End of Checking for G/L Account ------------------------------------------------------------------------//
//------------------------------------------------------------------------ Checking for Users ------------------------------------------------------------------------//
case "4": // All Users
if (input != "") // To check if project column is blank or not first.
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
return("");
}
}
return("Invalid User.");
}
break;
//------------------------------------------------------------------------ End of Checking for Users ------------------------------------------------------------------------//
case "11":
if (input != "") // To check if project column is blank or not first.
{
while (!rs.EoF)
{
string retrievedData = rs.Fields.Item(0).Value.ToString();
rs.MoveNext();
if (input == retrievedData)
{
return("");
}
}
return("Invalid Branch.");
}
break;
default:
break;
}
return("");
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
return(ex.Message + " " + ex.StackTrace);
}
}
/// <summary>
/// Get Available A Type of Dimensions List Defined by Project.
/// </summary>
/// <param name="dimensionType"></param>
/// <returns></returns>
public static Recordset GetDimensionsByBranch(string dimensionType, string branch = "", bool isBranchId = false)
{
SAPbobsCOM.Recordset oRecordSet = null;
try
{
oRecordSet = ((SAPbobsCOM.Recordset)(AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)));
string strQuery = "";
if (isBranchId)
{
switch (dimensionType)
{
case "Dimension1":
strQuery = "SELECT T1.\"U_Dimension1\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM1\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = (SELECT \"BPLName\" FROM OBPL WHERE \"BPLId\" = " + branch + ")";
break;
case "Dimension2":
strQuery = "SELECT T1.\"U_Dimension2\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM2\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = (SELECT \"BPLName\" FROM OBPL WHERE \"BPLId\" = " + branch + ")";
break;
case "Dimension3":
strQuery = "SELECT T1.\"U_Dimension3\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM3\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = (SELECT \"BPLName\" FROM OBPL WHERE \"BPLId\" = " + branch + ")";
break;
case "Dimension4":
strQuery = "SELECT T1.\"U_Dimension4\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM4\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = (SELECT \"BPLName\" FROM OBPL WHERE \"BPLId\" = " + branch + ")";
break;
case "Dimension5":
strQuery = "SELECT T1.\"U_Dimension5\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM5\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = (SELECT \"BPLName\" FROM OBPL WHERE \"BPLId\" = " + branch + ")";
break;
default:
break;
}
}
else
{
switch (dimensionType)
{
case "Dimension1":
strQuery = "SELECT T1.\"U_Dimension1\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM1\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = '" + branch + "'";
break;
case "Dimension2":
strQuery = "SELECT T1.\"U_Dimension2\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM2\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = '" + branch + "'";
break;
case "Dimension3":
strQuery = "SELECT T1.\"U_Dimension3\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM3\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = '" + branch + "'";
break;
case "Dimension4":
strQuery = "SELECT T1.\"U_Dimension4\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM4\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = '" + branch + "'";
break;
case "Dimension5":
strQuery = "SELECT T1.\"U_Dimension5\" FROM \"@OBDM\" T0 INNER JOIN \"@BDM5\" T1 ON T1.\"Code\" = T0.\"Code\" WHERE T0.\"U_Branch\" = '" + branch + "'";
break;
default:
break;
}
}
oRecordSet.DoQuery(strQuery);
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
return(oRecordSet);
}
private void button1_Click(object sender, EventArgs e)
{
AddOnUtilities.ConnectViaDI();
}
private void btnAddUDVs_Click(object sender, EventArgs e)
{
AddOnUtilities.CreateUDV();
}
