/// <summary>
/// Preenche model generico
/// </summary>
/// <param name="sql">Comando SELECT</param>
/// <param name="forceNoLock">Força comando NOLOCK</param>
/// <returns>Model generico preenchido</returns>
public GenericModel FillGenericModel(string sql, bool forceNoLock)
{
GenericModel genericModel = null;
// SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED tem o mesmo efeito do WITH (NOLOCK)
if (forceNoLock)
{
sql = " SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " + sql;
}
// Lê os dados em um Recordset
Recordset rs = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
sql = SBOApp.TranslateToHana(sql);
rs.DoQuery(sql);
// Lê os dados e insere no model
if (rs.RecordCount > 0)
{
genericModel = new GenericModel();
genericModel.Fields = new Dictionary <string, object>();
for (int i = 0; i < rs.Fields.Count; i++)
{
genericModel.Fields.Add(rs.Fields.Item(i).Name, rs.Fields.Item(i).Value);
}
}
Marshal.ReleaseComObject(rs);
rs = null;
GC.Collect();
return(genericModel);
}
static void Main(string[] Args)
{
// Se não foi passado nenhum argumento à aplicação, esta é finalizada
if (Args.Length.Equals(0))
{
MessageBox.Show("O aplicativo deve ser iniciado dentro do SAP Business One Client.", "Add-On Default",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
Application.Exit(); //exit teste
return;
}
//
// Nova instância do controlador
SBOApp sboApp = new SBOApp(Args[0], Application.StartupPath + "\\View.dll");
sboApp.InitializeApplication();
CreateMenu();
EventFilterController.SetDefaultEvents();
// Gera nova instância do AppListener para realizar o gerenciamento de memória do aplicativo
// O gerenciamento é feito em background através de uma nova thread
ListenerController oListener = new ListenerController();
System.Threading.Thread oThread = new System.Threading.Thread(new System.Threading.ThreadStart(oListener.startListener));
oThread.IsBackground = true;
oThread.Start();
Application.Run();
}
public static string QueryForValue(string Sql)
{
SAPbobsCOM.Recordset oRecordset = (SAPbobsCOM.Recordset)(SBOApp.Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset));
string Retorno = null;
try
{
Sql = SBOApp.TranslateToHana(Sql);
oRecordset.DoQuery(Sql);
// Executa e, caso exista ao menos um registro, devolve o mesmo.
// retorna sempre o primeiro campo da consulta (SEMPRE)
if (!oRecordset.EoF)
{
Retorno = oRecordset.Fields.Item(0).Value.ToString();
}
}
catch
{
}
finally
{
Marshal.ReleaseComObject(oRecordset);
oRecordset = null;
}
return(Retorno);
}
/// <summary>
/// Verifica se registro existe
/// </summary>
/// <param name="tableName">Nome da tabela</param>
/// <param name="returnColumn">Coluna a ser retornada</param>
/// <param name="where">Condição WHERE</param>
/// <returns>Código do registro</returns>
public string Exists(string returnColumn, string where)
{
string sql = String.Format("SELECT TOP 1 {0} FROM [{1}] WITH(NOLOCK) ", returnColumn, TableName);
if (!String.IsNullOrEmpty(where))
{
sql += String.Format(" WHERE {0} ", where);
}
// Lê os dados em um Recordset
Recordset rs = (Recordset)SBOApp.Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
sql = SBOApp.TranslateToHana(sql);
rs.DoQuery(sql);
if (rs.RecordCount > 0)
{
returnColumn = rs.Fields.Item(0).Value.ToString();
//Libera o objeto rs e chama o Garbage Collector
Marshal.ReleaseComObject(rs);
rs = null;
GC.Collect();
return(returnColumn);
}
else
{
return(null);
}
}
public int GetSysCatID()
{
int functionReturnValue = 0;
functionReturnValue = -3;
SAPbobsCOM.Recordset oRS = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
try
{
SBOApp.TranslateToHana("SELECT TOP 1 CATEGORYID FROM OQCN WHERE CATNAME = 'Geral'");
oRS.DoQuery("SELECT TOP 1 CATEGORYID FROM OQCN WHERE CATNAME = 'Geral'");
if (oRS.RecordCount > 0)
{
functionReturnValue = Convert.ToInt32(oRS.Fields.Item(0).Value);
}
}
catch
{
throw new Exception(String.Format("Erro: {0}", SBOApp.Company.GetLastErrorDescription()));
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS);
oRS = null;
GC.Collect();
}
return(functionReturnValue);
}
/// <summary>
/// Deleta registro
/// </summary>
/// <param name="tableName">Nome da tabela</param>
/// <param name="where">Condição WHERE</param>
public void DeleteModel(string tableName, string where)
{
Recordset rs = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = @"SELECT Code FROM [{0}] WHERE {1}";
sql = SBOApp.TranslateToHana(String.Format(sql, tableName, where));
rs.DoQuery(String.Format(sql, tableName, where));
if (rs.RecordCount > 0)
{
CompanyService sCompany = SBOApp.Company.GetCompanyService();
GeneralService oGeneralService = sCompany.GetGeneralService(tableName.Replace("@", ""));
GeneralDataParams oGeneralParams = (GeneralDataParams)oGeneralService.GetDataInterface(SAPbobsCOM.GeneralServiceDataInterfaces.gsGeneralDataParams);
oGeneralParams.SetProperty("Code", rs.Fields.Item(0).Value.ToString());
oGeneralService.Delete(oGeneralParams);
}
//Libera o objeto rs e chama o Garbage Collector
Marshal.ReleaseComObject(rs);
rs = null;
GC.Collect();
}
public static void RightClickEvent(ref ContextMenuInfo contextMenuInfo, out Boolean BubbleEvent)
{
BubbleEvent = true;
SBOApp oApplication = new SBOApp();
// Executa o método RightClickEvent do formulário em que ocorreu o evento
ExecuteEvent <ContextMenuInfo>(SBOApp.Application.Forms.Item(contextMenuInfo.FormUID.ToString()).TypeEx,
contextMenuInfo,
"RightClickEvent",
false);
}
public int CreateQuery(string QueryName, string TheQuery)
{
int functionReturnValue = 0;
functionReturnValue = -1;
SAPbobsCOM.Recordset oRS = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
SAPbobsCOM.UserQueries oQuery = (UserQueries)SBOApp.Company.GetBusinessObject(BoObjectTypes.oUserQueries);
try
{
SBOApp.TranslateToHana("SELECT TOP 1 INTRNALKEY FROM OUQR WHERE QCATEGORY=" + GetSysCatID() + " AND QNAME='" + QueryName + "'");
oRS.DoQuery("SELECT TOP 1 INTRNALKEY FROM OUQR WHERE QCATEGORY=" + GetSysCatID() + " AND QNAME='" + QueryName + "'");
if (oRS.RecordCount > 0)
{
functionReturnValue = (int)oRS.Fields.Item(0).Value;
}
else
{
oQuery.QueryCategory = GetSysCatID();
oQuery.QueryDescription = QueryName;
oQuery.Query = TheQuery;
if (oQuery.Add() != 0)
{
throw new Exception(String.Format("Erro ao criar query {0}: {1}", QueryName, SBOApp.Company.GetLastErrorDescription()));
}
string newKey = SBOApp.Company.GetNewObjectKey();
if (newKey.Contains('\t'))
{
newKey = newKey.Split('\t')[0];
}
functionReturnValue = Convert.ToInt32(newKey);
}
}
catch
{
throw new Exception(String.Format("Erro ao criar query {0}: {1}", QueryName, SBOApp.Company.GetLastErrorDescription()));
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS);
oRS = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oQuery);
oQuery = null;
GC.Collect();
}
return(functionReturnValue);
}
/// <summary>
/// Atualiza dados no banco
/// </summary>
/// <param name="tableName">Nome da tabela</param>
/// <param name="where">Condição WHRE</param>
/// <param name="model">Model com os dados a serem atualizados</param>
public void UpdateModel(string where)
{
Recordset rs = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = @"SELECT DocEntry FROM [{0}] WHERE {1}";
sql = SBOApp.TranslateToHana(String.Format(sql, TableName, where));
rs.DoQuery(String.Format(sql, TableName, where));
if (rs.RecordCount > 0)
{
CompanyService sCompany = SBOApp.Company.GetCompanyService();
GeneralService oGeneralService = sCompany.GetGeneralService(TableName.Replace("@", ""));
GeneralDataParams oGeneralParams = (GeneralDataParams)oGeneralService.GetDataInterface(SAPbobsCOM.GeneralServiceDataInterfaces.gsGeneralDataParams);
oGeneralParams.SetProperty("DocEntry", rs.Fields.Item(0).Value.ToString());
GeneralData oGeneralData = oGeneralService.GetByParams(oGeneralParams);
ModelControllerAttribute modelController;
foreach (PropertyInfo property in Model.GetType().GetProperties())
{
foreach (Attribute attribute in property.GetCustomAttributes(true))
{
modelController = attribute as ModelControllerAttribute;
if (modelController.DataBaseFieldYN)
{
if (String.IsNullOrEmpty(modelController.ColumnName))
{
modelController.ColumnName = property.Name;
}
oGeneralData.SetProperty(modelController.ColumnName, property.GetValue(Model, null));
}
}
}
oGeneralService.Update(oGeneralData);
}
Marshal.ReleaseComObject(rs);
rs = null;
GC.Collect();
}
public static int GetBplIdFromWarehouse(string warehouse)
{
int bplId = 1;
Recordset rsBpl = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = " SELECT BPlId FROM OBPL WHERE DflWhs = '{0}' ";
sql = String.Format(sql, warehouse);
sql = SBOApp.TranslateToHana(sql);
rsBpl.DoQuery(sql);
if (rsBpl.RecordCount > 0)
{
bplId = Convert.ToInt32(rsBpl.Fields.Item(0).Value);
}
Marshal.ReleaseComObject(rsBpl);
rsBpl = null;
GC.Collect();
return(bplId);
}
public static string GetMatrixCnpj()
{
string cnpj = String.Empty;
Recordset rsBpl = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = " SELECT TaxIdNum FROM OBPL WHERE MainBPL = 'Y' ";
sql = SBOApp.TranslateToHana(sql);
rsBpl.DoQuery(sql);
if (rsBpl.RecordCount > 0)
{
cnpj = rsBpl.Fields.Item(0).Value.ToString();
}
Marshal.ReleaseComObject(rsBpl);
rsBpl = null;
GC.Collect();
return(cnpj);
}
public static object GetBpProperty(int bplId, string fieldName)
{
Recordset rsBpl = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = " SELECT {0} FROM OBPL WHERE BPlId = '{0}' ";
sql = String.Format(sql, fieldName, bplId);
object value = null;
sql = SBOApp.TranslateToHana(sql);
rsBpl.DoQuery(sql);
if (rsBpl.RecordCount > 0)
{
value = Convert.ToInt32(rsBpl.Fields.Item(0).Value);
}
Marshal.ReleaseComObject(rsBpl);
rsBpl = null;
GC.Collect();
return(value);
}
/// <summary>
/// Busca Filial Padrão
/// </summary>
/// <returns>Id Filial Padrão</returns>
public static int GetCurrentBPlId()
{
// FH: unica solucao encontrada no forum foi buscar a string da tela para pegar a filial selecionada.
SAPbouiCOM.Forms forms = SBOApp.Application.Forms;
int formType = 169;
int bplId = 0;
for (int I = 0; I < forms.Count; I++)
{
if (forms.Item(I).Type == formType)
{
Form form = forms.Item(I);
string bplName = ((StaticText)form.Items.Item(6).Specific).Caption;
if (bplName.Contains("Filial: "))
{
bplName = bplName.Substring(bplName.IndexOf("Filial: "));
bplName = bplName.Replace("Filial: ", String.Empty);
Recordset rsBpl = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = " SELECT BPlId FROM OBPL WHERE BPLName = '{0}' ";
sql = String.Format(sql, bplName);
sql = SBOApp.TranslateToHana(sql);
rsBpl.DoQuery(sql);
if (rsBpl.RecordCount > 0)
{
bplId = Convert.ToInt32(rsBpl.Fields.Item(0).Value);
}
else
{
bplId = 1;
}
Marshal.ReleaseComObject(rsBpl);
rsBpl = null;
GC.Collect();
}
break;
}
}
return(bplId);
}
public bool ExistsQuery(string query)
{
query = query.Replace("'", "''");
bool exists = false;
string sql = "SELECT TOP 1 1 FROM OUQR WHERE CAST(QString AS NVARCHAR(MAX)) = '{0}'";
sql = String.Format(sql, query);
SAPbobsCOM.Recordset oRS = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
sql = SBOApp.TranslateToHana(sql);
oRS.DoQuery(sql);
if (oRS.RecordCount > 0)
{
exists = true;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS);
oRS = null;
GC.Collect();
return(exists);
}
public static bool FieldExists(string tableName, string fieldName)
{
string sql = @" SELECT TOP 1 1 FROM CUFD WHERE TableID = '{0}' AND AliasID = '{1}' ";
sql = String.Format(sql, tableName, fieldName.Replace("U_", ""));
Recordset rst = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
sql = SBOApp.TranslateToHana(sql);
rst.DoQuery(sql);
bool exists = false;
if (rst.RecordCount > 0)
{
exists = true;
}
Marshal.ReleaseComObject(rst);
rst = null;
return(exists);
}
/// <summary>
/// Retorna o próximo código
/// </summary>
/// <param name="fieldName">Nome do campo</param>
/// <param name="tableName">Nome da tabela</param>
/// <param name="where">Where</param>
/// <returns>Código</returns>
public static string GetNextCode(string tableName, string fieldName, string where)
{
string sSql = String.Format(" SELECT ISNULL(MAX(CAST(Code AS NUMERIC(19, 6))), 0) + 1 FROM [{1}] ", fieldName, tableName);
if (!String.IsNullOrEmpty(where))
{
sSql += String.Format(" WHERE {0} ", where);
}
Recordset rs = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
sSql = SBOApp.TranslateToHana(sSql);
rs.DoQuery(sSql);
string code = rs.Fields.Item(0).Value.ToString();
//Libera o objeto rs e chama o Garbage Collector
Marshal.ReleaseComObject(rs);
rs = null;
GC.Collect();
return(code);
}
public void RemoveFormattedSearch(string queryName, string itemId, string formId)
{
SAPbobsCOM.Recordset oRS = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
SAPbobsCOM.UserQueries oQuery = (UserQueries)SBOApp.Company.GetBusinessObject(BoObjectTypes.oUserQueries);
SAPbobsCOM.FormattedSearches oFS = (FormattedSearches)SBOApp.Company.GetBusinessObject(BoObjectTypes.oFormattedSearches);
string sSql = "SELECT IndexId FROM CSHS WHERE ItemId = '{0}' AND FormId = '{1}'";
sSql = string.Format(sSql, itemId, formId);
sSql = SBOApp.TranslateToHana(sSql);
oRS.DoQuery(sSql);
if (oRS.RecordCount > 0)
{
oFS.GetByKey(Convert.ToInt32(oRS.Fields.Item(0).Value));
oFS.Remove();
}
string sql = "SELECT IntrnalKey, QCategory FROM OUQR WHERE QName = '{0}' and QCategory = {1}";
sql = String.Format(sql, queryName, this.GetSysCatID());
sSql = SBOApp.TranslateToHana(sSql);
oRS.DoQuery(sql);
if (oRS.RecordCount > 0)
{
oQuery.GetByKey(Convert.ToInt32(oRS.Fields.Item(0).Value), Convert.ToInt32(oRS.Fields.Item(1).Value));
oQuery.Remove();
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS);
oRS = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oFS);
oFS = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oQuery);
oQuery = null;
GC.Collect();
}
public string RetrieveModelSql(Type modelType, string where, string orderBy, bool getValidValues)
{
Dictionary <string, Dictionary <string, string> > fieldValidValues = new Dictionary <string, Dictionary <string, string> >();
if (getValidValues)
{
string sqlValidValues = @"SELECT CUFD.AliasID, UFD1.FldValue, UFD1.Descr FROM CUFD
INNER JOIN UFD1
ON UFD1.TableID = CUFD.TableID
AND UFD1.FieldID = CUFD.FieldID
WHERE CUFD.TableID = '{0}'";
sqlValidValues = String.Format(sqlValidValues, TableName);
Recordset rstValidValues = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
sqlValidValues = SBOApp.TranslateToHana(sqlValidValues);
rstValidValues.DoQuery(sqlValidValues);
string aliasId = null;
while (!rstValidValues.EoF)
{
if (String.IsNullOrEmpty(aliasId))
{
aliasId = rstValidValues.Fields.Item("AliasID").Value.ToString();
}
Dictionary <string, string> validValues = new Dictionary <string, string>();
while (!rstValidValues.EoF && aliasId == rstValidValues.Fields.Item("AliasID").Value.ToString())
{
validValues.Add(rstValidValues.Fields.Item("FldValue").Value.ToString(), rstValidValues.Fields.Item("Descr").Value.ToString());
rstValidValues.MoveNext();
}
fieldValidValues.Add(aliasId, validValues);
if (!rstValidValues.EoF)
{
aliasId = rstValidValues.Fields.Item("AliasID").Value.ToString();
}
}
}
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT ");
ModelControllerAttribute modelController;
foreach (PropertyInfo property in modelType.GetProperties())
{
foreach (Attribute attribute in property.GetCustomAttributes(true))
{
modelController = attribute as ModelControllerAttribute;
// Se propriedade "ColumnName" estiver vazia, pega o nome da propriedade
if (String.IsNullOrEmpty(modelController.Description))
{
modelController.Description = property.Name;
}
if (String.IsNullOrEmpty(modelController.ColumnName))
{
modelController.ColumnName = property.Name;
}
if (!getValidValues || !fieldValidValues.ContainsKey(modelController.ColumnName.Replace("U_", "")))
{
try
{
if (SBOApp.Company.DbServerType == (BoDataServerTypes)9)
{
sql.AppendFormat(", {0} ", modelController.ColumnName, modelController.Description);
}
else
{
sql.AppendFormat(", {0} AS '{1}' ", modelController.ColumnName, modelController.Description);
}
}
catch
{
sql.AppendFormat(", {0} AS '{1}' ", modelController.ColumnName, modelController.Description);
}
sql.AppendLine();
}
else
{
sql.AppendFormat(", CASE CAST({0} AS NVARCHAR) ", modelController.ColumnName);
sql.AppendLine();
foreach (string strKey in fieldValidValues[modelController.ColumnName.Replace("U_", "")].Keys)
{
sql.AppendFormat(" WHEN '{0}' THEN '{1}' ", strKey, fieldValidValues[modelController.ColumnName.Replace("U_", "")][strKey]);
sql.AppendLine();
}
sql.AppendFormat(" END AS {0} ", modelController.Description);
sql.AppendLine();
}
//if (String.IsNullOrEmpty(modelController.ValidValuesId))
//{
// sql.AppendFormat(", {0} AS '{1}' ", modelController.ColumnName, modelController.Description);
// sql.AppendLine();
//}
//else
//{
// string[] validValuesId = modelController.ValidValuesId.Split(',');
// string[] validValuesDesc = modelController.ValidValuesDesc.Split(',');
// if (validValuesId.Length != validValuesDesc.Length)
// {
// throw new Exception("Id e Descrição dos valores válidos não coincidem");
// }
// sql.AppendFormat(", CASE CAST({0} AS NVARCHAR) ", modelController.ColumnName);
// sql.AppendLine();
// for (int i = 0; i < validValuesId.Length; i++)
// {
// sql.AppendFormat(" WHEN '{0}' THEN '{1}' ", validValuesId[i].Trim(), validValuesDesc[i].Trim());
// sql.AppendLine();
// }
// sql.AppendFormat(" END AS {0} ", modelController.Description);
// sql.AppendLine();
//}
}
}
sql.AppendFormat(" FROM [{0}]", TableName);
sql.AppendLine();
if (!String.IsNullOrEmpty(where))
{
sql.AppendFormat(" WHERE {0} ", where);
sql.AppendLine();
}
if (!String.IsNullOrEmpty(orderBy))
{
sql.AppendFormat(" ORDER BY {0} ", orderBy);
sql.AppendLine();
}
return(sql.ToString().Replace("SELECT ,", "SELECT "));
}
/// <summary>
/// Salva o model no BD de acordo com o tipo da operação
/// </summary>
/// <param name="enumCrudOperation">Operação - Create ou Update</param>
/// <param name="tableName">Nome da tabela</param>
/// <param name="model">Modelo</param>
private void SaveModel(EnumCrudOperation enumCrudOperation)
{
CompanyService sCompany = null;
GeneralService oGeneralService = null;
GeneralData oGeneralData = null;
try
{
sCompany = SBOApp.Company.GetCompanyService();
oGeneralService = sCompany.GetGeneralService(TableName.Replace("@", ""));
oGeneralData = (GeneralData)oGeneralService.GetDataInterface(GeneralServiceDataInterfaces.gsGeneralData);
if (enumCrudOperation == EnumCrudOperation.Update)
{
GeneralDataParams oGeneralParams = (GeneralDataParams)oGeneralService.GetDataInterface(GeneralServiceDataInterfaces.gsGeneralDataParams);
object code = null;
try
{
code = Model.GetType().GetProperty("Code").GetValue(Model, null);
}
catch
{
Recordset rstDocEntry = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
string sql = @"SELECT DocEntry FROM {0} WHERE Code = '{1}'";
sql = String.Format(sql, TableName, Model.GetType().GetProperty("Code").GetValue(Model, null));
sql = SBOApp.TranslateToHana(sql);
rstDocEntry.DoQuery(sql);
if (rstDocEntry.RecordCount > 0)
{
code = rstDocEntry.Fields.Item(0).Value;
}
Marshal.ReleaseComObject(oGeneralParams);
oGeneralParams = null;
Marshal.ReleaseComObject(rstDocEntry);
rstDocEntry = null;
GC.Collect();
}
oGeneralParams.SetProperty("Code", code);
oGeneralData = oGeneralService.GetByParams(oGeneralParams);
}
ModelControllerAttribute modelController;
object value;
// Percorre as propriedades do Model
foreach (PropertyInfo property in Model.GetType().GetProperties())
{
try
{
// Busca os Custom Attributes
foreach (Attribute attribute in property.GetCustomAttributes(true))
{
modelController = attribute as ModelControllerAttribute;
if (property.GetType() != typeof(DateTime))
{
value = property.GetValue(Model, null);
}
else
{
value = ((DateTime)property.GetValue(Model, null)).ToString("yyyy-MM-dd HH:mm:ss");
}
if (modelController != null)
{
// Se não for DataBaseField não seta nas properties
if (!modelController.DataBaseFieldYN)
{
break;
}
if (String.IsNullOrEmpty(modelController.ColumnName))
{
modelController.ColumnName = property.Name;
}
if (value == null)
{
if (property.PropertyType == typeof(string))
{
value = String.Empty;
}
else if (property.PropertyType != typeof(DateTime) && property.PropertyType != typeof(Nullable <DateTime>))
{
value = 0;
}
else
{
value = new DateTime();
}
}
if (property.PropertyType != typeof(decimal) && property.PropertyType != typeof(Nullable <decimal>))
{
oGeneralData.SetProperty(modelController.ColumnName, value);
}
else
{
oGeneralData.SetProperty(modelController.ColumnName, Convert.ToDouble(value));
}
break;
}
}
}
catch (Exception e)
{
throw new Exception(String.Format("Erro ao setar propriedade {0}: {1}", property.Name, e));
}
}
switch (enumCrudOperation)
{
case EnumCrudOperation.Create:
oGeneralService.Add(oGeneralData);
break;
case EnumCrudOperation.Update:
oGeneralService.Update(oGeneralData);
break;
default:
break;
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (sCompany != null)
{
Marshal.ReleaseComObject(sCompany);
sCompany = null;
}
if (oGeneralService != null)
{
Marshal.ReleaseComObject(oGeneralService);
oGeneralService = null;
}
if (oGeneralData != null)
{
Marshal.ReleaseComObject(oGeneralData);
oGeneralData = null;
}
GC.Collect();
}
}
/// <summary>
/// Seta o FormattedSearch no campo desejado
/// </summary>
/// <param name="QueryName">Nome da Query</param>
/// <param name="TheQuery">Query</param>
/// <param name="FormID">ID do form</param>
/// <param name="ItemID">ID do item</param>
/// <param name="ColID">ID da coluna (Default -1)</param>
/// <returns></returns>
public bool AssignFormattedSearch(string QueryName, string TheQuery, string FormID, string ItemID, string ColID = "-1")
{
bool functionReturnValue = false;
functionReturnValue = false;
SAPbobsCOM.Recordset oRS = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
SAPbobsCOM.FormattedSearches oFS = (FormattedSearches)SBOApp.Company.GetBusinessObject(BoObjectTypes.oFormattedSearches);
try
{
string sql = @"SELECT * FROM CSHS T0
INNER JOIN OUQR T1
ON T0.QueryId = T1.IntrnalKey
WHERE T0.FormID = '{0}'
AND T0.ItemId = '{1}'
AND T0.ColID = '{2}' " ;
sql = SBOApp.TranslateToHana(sql);
oRS.DoQuery(String.Format(sql, FormID, ItemID, ColID));
if (oRS.RecordCount == 0)
{
int QueryID;
QueryID = CreateQuery(QueryName, TheQuery);
oFS.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFS.FormID = FormID;
oFS.ItemID = ItemID;
oFS.ColumnID = ColID;
oFS.QueryID = QueryID;
oFS.FieldID = ItemID;
if (ColID == "-1")
{
oFS.ByField = BoYesNoEnum.tYES;
}
else
{
oFS.ByField = BoYesNoEnum.tNO;
}
long lRetCode = oFS.Add();
if (lRetCode == -2035)
{
sql = SBOApp.TranslateToHana(sql);
oRS.DoQuery("SELECT TOP 1 T0.IndexID FROM [dbo].[CSHS] T0 WHERE T0.FormID='" + FormID + "' AND T0.ItemId='" + ItemID + "' AND T0.ColID='" + ColID + "'");
if (oRS.RecordCount > 0)
{
oFS.GetByKey((int)oRS.Fields.Item(0).Value);
oFS.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFS.FormID = FormID;
oFS.ItemID = ItemID;
oFS.ColumnID = ColID;
oFS.QueryID = QueryID;
oFS.FieldID = ItemID;
if (ColID == "-1")
{
oFS.ByField = BoYesNoEnum.tYES;
}
else
{
oFS.ByField = BoYesNoEnum.tNO;
}
lRetCode = oFS.Update();
}
}
if (lRetCode != 0)
{
throw new Exception(String.Format("Erro ao criar query {0}: {1}", QueryName, SBOApp.Company.GetLastErrorDescription()));
}
}
functionReturnValue = true;
}
catch
{
throw new Exception(String.Format("Erro ao criar query {0}: {1}", QueryName, SBOApp.Company.GetLastErrorDescription()));
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS);
oRS = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oFS);
oFS = null;
GC.Collect();
}
return(functionReturnValue);
}
/// <summary>
/// Preenche a lista de model através do SQL
/// </summary>
/// <typeparam name="T">Model</typeparam>
/// <param name="sql">Comando SQL</param>
/// <returns>Lista de Model preenchido</returns>
public List <T> FillModel <T>(string sql)
{
List <T> modelList = new List <T>();
T model;
ModelControllerAttribute modelController;
Recordset rs = null;
try
{
// Lê os dados em um Recordset
rs = (Recordset)SBOApp.Company.GetBusinessObject(BoObjectTypes.BoRecordset);
sql = SBOApp.TranslateToHana(sql);
rs.DoQuery(sql);
// Lê os dados e insere no model
if (rs.RecordCount > 0)
{
while (!rs.EoF)
{
// Cria nova instância do model
model = Activator.CreateInstance <T>();
// Seta os valores no model
foreach (PropertyInfo property in model.GetType().GetProperties())
{
try
{
// Busca os Custom Attributes
foreach (Attribute attribute in property.GetCustomAttributes(true))
{
modelController = attribute as ModelControllerAttribute;
if (modelController != null)
{
// Se propriedade "ColumnName" estiver vazia, pega o nome da propriedade
if (String.IsNullOrEmpty(modelController.ColumnName))
{
modelController.ColumnName = property.Name;
}
// Se não for DataBaseField não seta nas properties
if (!modelController.DataBaseFieldYN)
{
break;
}
if (property.PropertyType == typeof(decimal) || property.PropertyType == typeof(Nullable <decimal>))
{
property.SetValue(model, Convert.ToDecimal(rs.Fields.Item(modelController.ColumnName).Value), null);
}
else if (property.PropertyType == typeof(short) || property.PropertyType == typeof(Nullable <short>))
{
property.SetValue(model, Convert.ToInt16(rs.Fields.Item(modelController.ColumnName).Value), null);
}
else if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(Nullable <bool>))
{
property.SetValue(model, Convert.ToBoolean(rs.Fields.Item(modelController.ColumnName).Value), null);
}
else
{
try
{
property.SetValue(model, rs.Fields.Item(modelController.ColumnName).Value, null);
}
catch (Exception e)
{
if (property.PropertyType == typeof(DateTime))
{
try
{
string hour = rs.Fields.Item(modelController.ColumnName).Value.ToString().PadLeft(4, '0');
DateTime date;
if (DateTime.TryParseExact(hour, "HHmm", CultureInfo.CurrentCulture, DateTimeStyles.None, out date))
{
property.SetValue(model, date, null);
}
}
catch
{
throw e;
}
}
else
{
throw e;
}
}
}
}
}
}
catch (Exception e)
{
throw new Exception(String.Format("Erro ao setar propriedade {0}: {1}", property.Name, e));
}
}
// Adiciona na lista
modelList.Add(model);
rs.MoveNext();
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (rs != null)
{
//Libera o objeto rs e chama o Garbage Collector
Marshal.ReleaseComObject(rs);
rs = null;
}
}
return(modelList);
}