Exemple #1
0
 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);
 }
Exemple #2
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());
        }
    }
Exemple #3
0
 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);
 }
Exemple #4
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());
        }
Exemple #5
0
        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);
            }
        }
Exemple #6
0
        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);
        }
Exemple #7
0
        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);
            }
        }
Exemple #8
0
        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());
        }
Exemple #9
0
        public static Dictionary <string, object> AdminInfo()
        {
            var query = "SELECT * FROM OADM ORDER BY UpdateDate DESC";

            return(SboDiUtils.QueryList(query).FirstOrDefault());
        }