/// <summary> /// Get or create User Query /// </summary> /// <param name="company">Company Object</param> /// <param name="userQueryName">User Query Name</param> /// <param name="userQueryDefaultQuery">Query</param> /// <param name="parameterFormat">Define parameter format [%0]/@p0/{0}</param> /// <returns>SQL</returns> public static string GetOrCreateUserQuery(this Company company, string userQueryName, string userQueryDefaultQuery, ParameterFormat parameterFormat = ParameterFormat.Sbo) { var userQuery = userQueryDefaultQuery; using (var userQueryObject = new SboRecordsetQuery <UserQueries>( $"SELECT [IntrnalKey] FROM [OUQR] WHERE [QName] = '{userQueryName}'", BoObjectTypes.oUserQueries)) { if (userQueryObject.Count == 0) { userQueryObject.BusinessObject.QueryDescription = userQueryName; userQueryObject.BusinessObject.Query = userQueryDefaultQuery; userQueryObject.BusinessObject.QueryCategory = -1; var response = userQueryObject.BusinessObject.Add(); ErrorHelper.HandleErrorWithException(response, $"Could not create User Query '{userQueryName}'"); } else { userQuery = userQueryObject.Result.First().Query; } } userQuery = ReturnParameterStyle(userQuery, parameterFormat); return(userQuery); }
/// <summary> /// Get query category code, create if not exists /// </summary> /// <param name="queryCategoryName">Query category name</param> /// <returns>Returns query category key</returns> public static int GetOrCreateQueryCategory(string queryCategoryName) { var queryCategoryCode = -1; if (string.IsNullOrEmpty(queryCategoryName)) { return(queryCategoryCode); } if (QueryCategoryCache.ContainsKey(queryCategoryName)) { return(QueryCategoryCache[queryCategoryName]); } var sql = FrameworkQueries.Instance.GetOrCreateQueryCategoryQuery(queryCategoryName); using (var queryCategoryObject = new SboRecordsetQuery <QueryCategories>(sql, BoObjectTypes.oQueryCategories)) { if (queryCategoryObject.Count == 1) { queryCategoryCode = queryCategoryObject.BusinessObject.Code; } else { queryCategoryObject.BusinessObject.Name = queryCategoryName; var response = queryCategoryObject.BusinessObject.Add(); ErrorHelper.HandleErrorWithException(response, $"Could not create Query Category '{queryCategoryName}'"); queryCategoryCode = int.Parse(SboApp.Company.GetNewObjectKey()); } } QueryCategoryCache.Add(queryCategoryName, queryCategoryCode); return(queryCategoryCode); }
/// <summary> /// Get or create User Query /// </summary> /// <param name="company">Company Object</param> /// <param name="userQueryName">User Query Name</param> /// <param name="userQueryDefaultQueryFunc">Default query</param> /// <param name="parameterFormat">Define parameter format [%0]/(@p0/:p0)/{0}</param> /// <param name="queryCategoryName">Add query in category with this name</param> /// <returns>SQL Query</returns> public static string GetOrCreateUserQuery(this Company company, string userQueryName, Func <string> userQueryDefaultQueryFunc, ParameterFormat parameterFormat = ParameterFormat.Database, string queryCategoryName = null) { string userQuery; using (var userQueryObject = new SboRecordsetQuery <UserQueries>(FrameworkQueries.Instance.GetOrCreateUserQueryQuery(userQueryName), BoObjectTypes.oUserQueries)) { var queryCategoryCode = GetOrCreateQueryCategory(queryCategoryName); if (userQueryObject.Count == 0) { userQuery = userQueryDefaultQueryFunc.Invoke(); userQueryObject.BusinessObject.QueryDescription = userQueryName; userQueryObject.BusinessObject.Query = userQuery; userQueryObject.BusinessObject.QueryCategory = queryCategoryCode; var response = userQueryObject.BusinessObject.Add(); ErrorHelper.HandleErrorWithException(response, $"Could not create User Query '{userQueryName}'"); } else { var row = userQueryObject.Result.First(); userQuery = row.Query; if (queryCategoryCode > 0 && userQueryObject.BusinessObject.QueryCategory != queryCategoryCode) { userQueryObject.BusinessObject.QueryCategory = queryCategoryCode; var response = userQueryObject.BusinessObject.Update(); ErrorHelper.HandleErrorWithException(response, $"Could not update User Query '{userQueryName}' with Query Category {queryCategoryCode}"); } } } userQuery = ReturnParameterStyle(userQuery, parameterFormat); return(userQuery); }
/// <summary> /// Save Setting /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key"></param> /// <param name="value"></param> /// <param name="userCode"></param> /// <param name="name"></param> public void SaveSetting <T>(string key, T value = default(T), string userCode = null, string name = null) { Init(); var sqlKey = key.Trim().ToLowerInvariant(); if (userCode != null) { sqlKey = $"{key}[{userCode}]"; } if (sqlKey.Length > 30) { throw new Exception($"SQL Key '{sqlKey}' for Setting is to long (Max 30, Actual {sqlKey.Length})"); } var sql = $"SELECT [U_{UdfSettingValue}], [Name] FROM [@{UdtSettings}] WHERE [Code] = '{sqlKey}'"; bool exists; using (var query = new SboRecordsetQuery(sql)) { exists = query.Count == 1; } var sqlValue = string.Format(CultureInfo.InvariantCulture, "'{0}'", value); if (value == null) { sqlValue = "NULL"; } if (exists) { sql = $"UPDATE [@{UdtSettings}] SET [U_{UdfSettingValue}] = {sqlValue} WHERE [Code] = '{sqlKey}'"; } else { if (sqlValue.Length > 254) { throw new Exception($"SaveSetting sqlValue '{sqlValue}' value is to long (max 254) "); } if (name == null) { name = sqlKey; } if (name.Length > 30) { name = name.Substring(0, 30); // Max Length is 30 } sql = $"INSERT INTO [@{UdtSettings}] ([Code], [Name], [U_{UdfSettingValue}]) VALUES ('{sqlKey}', '{name}', {sqlValue})"; } SboRecordset.NonQuery(sql); }
/// <summary> /// Save Setting /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key"></param> /// <param name="value"></param> /// <param name="userCode"></param> /// <param name="name"></param> public void SaveSetting <T>(string key, T value = default(T), string userCode = null, string name = null) { Init(); var sqlKey = key.Trim().ToLowerInvariant(); if (userCode != null) { sqlKey = $"{key}[{userCode}]"; } if (sqlKey.Length > KeyMaxLength) { throw new Exception($"SQL Key '{sqlKey}' for Setting is to long (Max {KeyMaxLength}, Actual {sqlKey.Length})"); } var sql = FrameworkQueries.Instance.SaveSettingExistsQuery(sqlKey); bool exists; using (var query = new SboRecordsetQuery(sql)) { exists = query.Count == 1; } var sqlValue = string.Format(CultureInfo.InvariantCulture, "'{0}'", value); if (value == null) { sqlValue = "NULL"; } if (exists) { sql = FrameworkQueries.Instance.SaveSettingUpdateQuery(sqlKey, sqlValue); } else { if (sqlValue.Length > ValueMaxLength) { throw new Exception($"SaveSetting sqlValue '{sqlValue}' value is to long (max {ValueMaxLength}) "); } if (name == null) { name = sqlKey; } if (name.Length > KeyMaxLength) { name = name.Substring(0, KeyMaxLength); // Max Length is 50 } sql = FrameworkQueries.Instance.SaveSettingInsertQuery(sqlKey, name, sqlValue); } SboRecordset.NonQuery(sql); }
/// <summary> /// Add values into Combobox from SQL /// </summary> /// <param name="comboBox"></param> /// <param name="sql"></param> public static void AddComboBoxValues(this ComboBox comboBox, string sql) { using (var query = new SboRecordsetQuery(sql)) { foreach (var combo in query.Result) { comboBox.ValidValues.Add(combo.Item(0).Value.ToString(), combo.Item(1).Value.ToString()); } } }
/// <summary> /// Get DocEntry from DocNum /// </summary> /// <param name="docNum">DocNum</param> /// <param name="table">SBO Table</param> /// <returns>DocEntry</returns> public static int?GetDocEntry(this int docNum, string table) { using (var query = new SboRecordsetQuery(FrameworkQueries.Instance.GetDocEntryQuery(table, docNum))) { if (query.Count == 0) { return(null); } return(int.Parse(query.Result.First().Item("DocEntry").Value.ToString())); } }
/// <summary> /// Get DocEntry from DocNum /// </summary> /// <param name="docNum">DocNum</param> /// <param name="table">SBO Table</param> /// <returns>DocEntry</returns> public static int?GetDocEntry(this int docNum, string table) { using (var query = new SboRecordsetQuery($"SELECT [DocEntry] FROM [{table}] WHERE [DocNum]={docNum}")) { if (query.Count == 0) { return(null); } return(int.Parse(query.Result.First().Item("DocEntry").Value.ToString())); } }
private static string GetSettingTitle(string key) { var sqlKey = key.Trim().ToLowerInvariant(); var sql = $"SELECT [Name] FROM [@{UdtSettings}] WHERE [Code] = '{sqlKey}'"; using (var query = new SboRecordsetQuery(sql)) { if (query.Count == 0) { return(key); } var result = query.Result.First(); var name = result.Item(0).Value as string; return(string.IsNullOrEmpty(name) ? key : name); } }
private static string GetSettingTitle(string key) { var sqlKey = key.Trim().ToLowerInvariant(); var sql = FrameworkQueries.Instance.GetSettingTitleQuery(key); using (var query = new SboRecordsetQuery(sql)) { if (query.Count == 0) { return(key); } var result = query.Result.First(); var name = result.Item(0).Value as string; return(string.IsNullOrEmpty(name) ? key : name); } }
/// <summary> /// Get Changed Items for object since timestamp /// </summary> /// <param name="timeStamp">Timestamp (secounds from a given startpoint)</param> /// <param name="objectType">BoObject Type</param> /// <returns>Collection of Key and timestamp for updated objects</returns> public ICollection <KeyAndTimeStampModel> GetChanged(int timeStamp, BoObjectTypes objectType) { using (var query = new SboRecordsetQuery( "SELECT DISTINCT [U_ITCO_CT_Key] AS [Key], CAST([Code] AS int) AS [Timestamp] FROM [@ITCO_CHANGETRACKER] " + $"WHERE [U_ITCO_CT_Obj] = {(int) objectType} AND CAST([Code] AS int) > {timeStamp} " + "ORDER BY CAST([Code] AS int) ASC")) { if (query.Count == 0) { return(new List <KeyAndTimeStampModel>()); } return(query.Result.Select(r => new KeyAndTimeStampModel { Key = r.Item(0).Value.ToString(), Timestamp = int.Parse(r.Item(1).Value.ToString()) }).ToList()); } }
/// <summary> /// SetCurrentLine by "Contact ID" /// </summary> /// <param name="businessPartners"></param> /// <param name="contactId"></param> /// <returns></returns> public static bool SetContactEmployeesLineByContactId(this BusinessPartners businessPartners, string contactId) { var cardCode = businessPartners.CardCode; var contactEmployees = businessPartners.ContactEmployees; using (var query = new SboRecordsetQuery(FrameworkQueries.Instance.SetContactEmployeesLineByContactIdQuery(cardCode, contactId))) { if (query.Count == 0) { return(false); } var lineNum = (int)query.Result.First().Item(0).Value; SboApp.Logger.Debug($"Contact ID '{contactId}' is LineNum {lineNum} for CardCode {cardCode}"); contactEmployees.SetCurrentLine(lineNum); return(true); } }
/// <summary> /// Wait for open transaction to complete /// Useful when using BeginTransaction /// WARNING: Does not work in HANA /// </summary> /// <param name="company"></param> /// <param name="sleep"></param> /// <param name="tryCount"></param> public static void WaitForOpenTransactions(this Company company, int sleep = 500, int tryCount = 10) { for (var i = 0; i < tryCount; i++) { using (var query = new SboRecordsetQuery(FrameworkQueries.Instance.WaitForOpenTransactionsQuery(company.CompanyDB))) { if (query.Count == 0) { return; } var openTransaction = query.Result.First(); SboApp.Logger.Trace($"Open Transaction by {openTransaction.Item("hostname").Value}, waiting {sleep} ms..."); } Thread.Sleep(sleep); } throw new Exception($"Waiting for open transactions to long! ({sleep * tryCount} ms)"); }
/// <summary> /// SetCurrentLine by "Contact ID" /// </summary> /// <param name="businessPartners"></param> /// <param name="contactId"></param> /// <returns></returns> public static bool SetContactEmployeesLineByContactId(this BusinessPartners businessPartners, string contactId) { var cardCode = businessPartners.CardCode; var contactEmployees = businessPartners.ContactEmployees; using (var query = new SboRecordsetQuery( "SELECT [LineNum] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [CntctCode] ASC) - 1 AS [LineNum], [Name] FROM [OCPR] " + $"WHERE [CardCode]='{cardCode}') AS [T0] WHERE [Name]='{contactId}'")) { if (query.Count == 0) { return(false); } var lineNum = (int)query.Result.First().Item(0).Value; SboApp.Logger.Debug($"Contact ID '{contactId}' is LineNum {lineNum} for CardCode {cardCode}"); contactEmployees.SetCurrentLine(lineNum); return(true); } }
/// <summary> /// Wait for open transaction to complete /// Useful when using BeginTransaction /// </summary> /// <param name="company"></param> /// <param name="sleep"></param> /// <param name="tryCount"></param> public static void WaitForOpenTransactions(this Company company, int sleep = 500, int tryCount = 10) { for (var i = 0; i < tryCount; i++) { using (var query = new SboRecordsetQuery( $"SELECT hostname, loginame FROM sys.sysprocesses WHERE open_tran=1 AND dbid=DB_ID('{company.CompanyDB}')")) { if (query.Count == 0) { return; } var openTransaction = query.Result.First(); SboApp.Logger.Trace($"Open Transaction by {openTransaction.Item("hostname").Value}, waiting {sleep} ms..."); } Thread.Sleep(sleep); } throw new Exception($"Waiting for open transactions to long! ({sleep * tryCount} ms)"); }
private string GetSettingAsString(string key, string userCode = null) { var sqlKey = key.Trim().ToLowerInvariant(); if (userCode != null) { sqlKey = $"{sqlKey}[{userCode}]"; } var sql = FrameworkQueries.Instance.GetSettingAsStringQuery(sqlKey); using (var query = new SboRecordsetQuery(sql)) { if (query.Count == 0) { return(null); } var result = query.Result.First(); return(result.Item(0).Value as string); } }
private string GetSettingAsString(string key, string userCode = null) { var sqlKey = key.Trim().ToLowerInvariant(); if (userCode != null) { sqlKey = $"{sqlKey}[{userCode}]"; } var sql = $"SELECT [U_{UdfSettingValue}], [Name] FROM [@{UdtSettings}] WHERE [Code] = '{sqlKey}'"; using (var query = new SboRecordsetQuery(sql)) { if (query.Count == 0) { return(null); } var result = query.Result.First(); return(result.Item(0).Value as string); } }