public static bool HasColumn(string table, string column)
{
return
(SboDiUtils.QueryValue <int>(SboAddon.Instance.Company.DbServerType.Equals(SAPbobsCOM.BoDataServerTypes.dst_HANADB) ?
$@"SELECT COUNT(*) FROM ""SYS"".""TABLE_COLUMNS"" WHERE ""SCHEMA_NAME"" = '{SboAddon.Instance.Company.CompanyDB}' AND ""TABLE_NAME"" = '{table}' AND ""COLUMN_NAME"" = '{column}'" :
$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table}' AND COLUMN_NAME = '{column}'") > 0);
}
public static Dictionary <string, object> DocumentContact(BoObjectTypes type, int docEntry, params string[] fields)
{
if (fields == null || fields.Length == 0)
{
fields = new[] { "*" }
}
;
var query = string.Format(@"
SELECT {2}
FROM
(SELECT ObjType, DocEntry, CntctCode FROM OQUT WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM ORDR WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM ODLN WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM ORDN WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM OINV WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM ODPI WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM ORIN WHERE ObjType = '{0}'
UNION ALL
SELECT ObjType, DocEntry, CntctCode FROM OPOR WHERE ObjType = '{0}'
) DOC
INNER JOIN OCPR CPR ON DOC.CntctCode = CPR.CntctCode
WHERE DOC.ObjType = '{0}' AND DOC.DocEntry = '{1}'
", (int)type, docEntry, String.Join(",", fields.Select(f => "CPR." + f)));
return(SboDiUtils.QueryList(query).FirstOrDefault());
}
}
public static bool IsVersion91OrMore()
{
return
(SboDiUtils.QueryValue <int>(SboAddon.Instance.Company.DbServerType.Equals(SAPbobsCOM.BoDataServerTypes.dst_HANADB) ?
@"SELECT COUNT(*) FROM ""SYS"".""TABLE_COLUMNS"" WHERE ""SCHEMA_NAME"" = '{0}' AND ""TABLE_NAME"" = 'OCPR' AND ""COLUMN_NAME"" = 'EmlGrpCode'" :
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OCPR' AND COLUMN_NAME = 'EmlGrpCode'", SboAddon.Instance.Company.CompanyDB) > 0);
}
public static Dictionary <string, object> DocumentHead(BoObjectTypes type, int docEntry, params string[] fields)
{
if (fields == null || fields.Length == 0)
{
fields = new[] { "*" }
}
;
else
{
var temp = fields.ToList();
if (!temp.Contains("DocEntry", StringComparer.OrdinalIgnoreCase))
{
temp.Add("DocEntry");
}
if (!temp.Contains("ObjType", StringComparer.OrdinalIgnoreCase))
{
temp.Add("ObjType");
}
fields = temp.ToArray();
}
var query = string.Format(@"
SELECT {2}
FROM
(SELECT {2} FROM OQUT WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM ORDR WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM ODLN WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM ORDN WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM OINV WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM ODPI WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM ORIN WHERE ObjType = '{0}'
UNION ALL
SELECT {2} FROM OPOR WHERE ObjType = '{0}'
) DOC
WHERE DOC.ObjType = '{0}' AND DOC.DocEntry = '{1}'
", (int)type, docEntry, String.Join(",", fields));
return(SboDiUtils.QueryList(query).FirstOrDefault());
}
public static int GetLock(string resource, string mode = "Exclusive", string owner = "Transaction", TimeSpan?timeout = null)
{
if (String.IsNullOrWhiteSpace(resource))
{
throw new ArgumentException();
}
if (owner.Equals("Transaction") && !SboAddon.Instance.Company.InTransaction)
{
throw new Exception("Cannot get lock with owner transaction without a running transaction");
}
if (SboAddon.Instance.Company.DbServerType.Equals(SAPbobsCOM.BoDataServerTypes.dst_HANADB))
{
// TODO: Ugly hack for HANA - fix soon
Task.Delay(TimeSpan.FromMilliseconds(new Random().NextDouble() * 2500d)).Wait();
return(0);
}
try
{
var result =
SboDiUtils.QueryValue <int>(
"DECLARE @res INT; EXEC @res = sp_getapplock @Resource = '{0}', @LockMode = '{1}', @LockOwner = '{2}', @LockTimeout = {3}; SELECT @res",
resource,
mode,
owner,
(int)(timeout ?? TimeSpan.Zero).TotalMilliseconds);
return(result);
}
catch (Exception)
{
Logger.Warn("Error requesting distributed lock for resource {0}, mode {1}, owner {2} and timeout {3}",
resource,
mode,
owner,
timeout);
return(-999);
}
}
public static void RenameUserTable(String oldName, String newName, String description = null)
{
if (oldName.StartsWith("@"))
{
oldName = oldName.Substring(1);
}
if (newName.StartsWith("@"))
{
newName = newName.Substring(1);
}
if (!UserTableExists(oldName))
{
throw new Exception(String.Format("Cannot rename table {0} to {1}, table {0} doesn't exist", oldName, newName));
}
if (UserTableExists(newName))
{
throw new Exception(String.Format("Cannot rename table {0} to {1}, table {1} already exists", oldName, newName));
}
var newFields = new List <dynamic>();
using (var factory = new SboDisposableBusinessObjectFactory())
{
var userTableOld = factory.Create <UserTablesMD>(BoObjectTypes.oUserTables);
userTableOld.GetByKey(oldName);
AddUserTable(newName, description ?? userTableOld.TableDescription, userTableOld.TableType);
var userField = factory.Create <UserFieldsMD>(BoObjectTypes.oUserFields);
var recordSet = factory.Create <Recordset>(BoObjectTypes.BoRecordset);
recordSet.DoQuery(string.Format(@"SELECT * FROM CUFD WHERE (""TableID"" = '{0}' OR ""TableID"" = '@{0}')",
oldName));
while (!recordSet.EoF)
{
userField.GetByKey((string)recordSet.Fields.Item("TableID").Value, (int)recordSet.Fields.Item("FieldID").Value);
newFields.Add(
new
{
TableName = newName,
Name = userField.Name,
Description = userField.Description,
Type = userField.Type,
SubType = userField.SubType,
EditSize = userField.EditSize,
ValidValues = userField.ValidValues.ToDictionary(),
DefaultValue = userField.DefaultValue
});
recordSet.MoveNext();
}
}
foreach (var f in newFields)
{
AddUserField(f.TableName, f.Name, f.Description, f.Type, f.SubType, f.EditSize, f.ValidValues, f.DefaultValue);
}
SboDiUtils.QueryValue <int>(string.Format(@"INSERT INTO ""@{0}"" SELECT * FROM ""@{1}""", newName, oldName));
RemoveUserTable(oldName);
Logger.Info("Renamed user table {0} to {1}", oldName, newName);
}
public static void UpdateUserField(string tableName, string name, string description = null, int?editSize = null,
Dictionary <string, string> validValues = null, string defaultValue = null, string linkedUdo = null, bool?mandatory = false)
{
if (!UserFieldExists(tableName, name))
{
throw new Exception(string.Format("Userfield {0} doesn't exists in table {1}", name, tableName));
}
using (var factory = new SboDisposableBusinessObjectFactory())
{
var fieldId = SboDiUtils.QueryValue <int>(string.Format(@"SELECT ""FieldID"" FROM CUFD WHERE (""TableID"" = '{0}' OR ""TableID"" = '@{0}') AND (""AliasID"" = '{1}' OR ""AliasID"" = 'U_{1}')", tableName, name));
var userFieldsMd = factory.Create <UserFieldsMD>(BoObjectTypes.oUserFields);
if (!userFieldsMd.GetByKey(tableName, fieldId))
{
throw new Exception(string.Format("Could not find userfield {0} in table {1}", name, tableName));
}
if (!String.IsNullOrWhiteSpace(description))
{
userFieldsMd.Description = description;
}
if (editSize.HasValue)
{
userFieldsMd.EditSize = editSize.Value;
}
/* TODO: Update validvalues
* if (validValues != null)
* {
* int insertedValues = 0;
* foreach (string key in validValues.Keys)
* {
* userFieldsMd.ValidValues.Value = key;
* userFieldsMd.ValidValues.Description = validValues[key];
*
* if (insertedValues < validValues.Count - 1)
* userFieldsMd.ValidValues.Add();
*
* insertedValues++;
* }
* }*/
if (!String.IsNullOrWhiteSpace(defaultValue))
{
userFieldsMd.DefaultValue = defaultValue;
}
if (!String.IsNullOrWhiteSpace(linkedUdo))
{
userFieldsMd.LinkedUDO = linkedUdo;
}
if (mandatory.HasValue)
{
userFieldsMd.Mandatory = mandatory.GetValueOrDefault(false) ? BoYesNoEnum.tYES : BoYesNoEnum.tNO;
}
Logger.Debug("Updating field {0} in table {1}", name, tableName);
if (userFieldsMd.Update() != 0)
{
throw new Exception(SboAddon.Instance.Company.GetLastErrorDescription());
}
Logger.Info("Updated field {0} in table {1}", name, tableName);
}
}
public static Dictionary <string, object> AdminInfo1()
{
var query = "SELECT ADM1.* FROM OADM INNER JOIN ADM1 ON OADM.Code = ADM1.Code ORDER BY OADM.UpdateDate DESC";
return(SboDiUtils.QueryList(query).FirstOrDefault());
}
public static Dictionary <string, object> AdminInfo()
{
var query = "SELECT * FROM OADM ORDER BY UpdateDate DESC";
return(SboDiUtils.QueryList(query).FirstOrDefault());
}