/// <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);
}
}
