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);
}
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 static void CargarBusquedasFormateadas()
{
try
{
String Query = String.Empty;
String CategoryName = "Consultas Addon Recepcion DTE Proveedores";
Int32 CategoryID = 0;
SAPbobsCOM.QueryCategories oCategory = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oQueryCategories);
oCategory.Name = CategoryName;
Int32 ret = oCategory.Add();
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"CategoryId\" FROM OQCN WHERE \"CatName\" = '" + CategoryName + "'";
break;
default:
Query = "SELECT CategoryId FROM OQCN WHERE CatName = '" + CategoryName + "'";
break;
}
SAPbobsCOM.Recordset oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
CategoryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
SAPbobsCOM.UserQueries oQuery = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
oQuery.QueryCategory = CategoryID;
oQuery.QueryDescription = "Trae Dimension 1";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
oQuery.Query = "Select \"PrcCode\", \"PrcName\" from OPRC where \"DimCode\"= 1 and \"Active\" = 'Y' and \"PrcCode\" NOT IN ('Stelle_Z')";
break;
default:
oQuery.Query = "Select PrcCode, PrcName from OPRC where DimCode= 1 and Active = 'Y' and PrcCode NOT IN ('Stelle_Z')";
break;
}
oQuery.QueryType = UserQueryTypeEnum.uqtWizard;
oQuery.Add();
oQuery = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
oQuery.QueryCategory = CategoryID;
oQuery.QueryDescription = "Trae Dimension 2";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
oQuery.Query = "Select \"PrcCode\", \"PrcName\" from OPRC where \"DimCode\"= 2 and \"Active\" = 'Y' and \"PrcCode\" NOT IN ('Stelle_2')";
break;
default:
oQuery.Query = "Select PrcCode, PrcName from OPRC where DimCode= 2 and Active = 'Y' and PrcCode NOT IN ('Stelle_2')";
break;
}
oQuery.QueryType = UserQueryTypeEnum.uqtWizard;
oQuery.Add();
oQuery = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
oQuery.QueryCategory = CategoryID;
oQuery.QueryDescription = "Trae Dimension 3";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
oQuery.Query = "Select \"PrcCode\", \"PrcName\" from OPRC where \"DimCode\"= 3 and \"Active\" = 'Y' and \"PrcCode\" NOT IN ('Stelle_3')";
break;
default:
oQuery.Query = "Select PrcCode, PrcName FROM OPRC where DimCode = 3 and Active = 'Y' and PrcCode NOT IN ('Stelle_3')";
break;
}
oQuery.QueryType = UserQueryTypeEnum.uqtWizard;
oQuery.Add();
oQuery = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
oQuery.QueryCategory = CategoryID;
oQuery.QueryDescription = "Trae Dimension 4";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
oQuery.Query = "Select \"PrcCode\", \"PrcName\" from OPRC where \"DimCode\"= 4 and \"Active\" = 'Y' and \"PrcCode\" NOT IN ('Stelle_4')";
break;
default:
oQuery.Query = "Select PrcCode, PrcName FROM OPRC where DimCode = 4 and Active = 'Y' and PrcCode NOT IN ('Stelle_4')";
break;
}
oQuery.QueryType = UserQueryTypeEnum.uqtWizard;
oQuery.Add();
oQuery = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
oQuery.QueryCategory = CategoryID;
oQuery.QueryDescription = "Trae Dimension 5";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
oQuery.Query = "Select \"PrcCode\", \"PrcName\" from OPRC where \"DimCode\"= 5 and \"Active\" = 'Y' and \"PrcCode\" NOT IN ('Stelle_5')";
break;
default:
oQuery.Query = "Select PrcCode, PrcName FROM OPRC where DimCode = 5 and Active = 'Y' and PrcCode NOT IN ('Stelle_5')";
break;
}
oQuery.QueryType = UserQueryTypeEnum.uqtWizard;
oQuery.Add();
oQuery = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oUserQueries);
oQuery.QueryCategory = CategoryID;
oQuery.QueryDescription = "Trae Cuenta Contable";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
oQuery.Query = "Select \"AcctCode\", \"AcctName\" from OACT order by \"AcctCode\"";
break;
default:
oQuery.Query = "Select AcctCode, AcctName FROM OACT order by AcctCode";
break;
}
oQuery.QueryType = UserQueryTypeEnum.uqtWizard;
oQuery.Add();
Int32 QueryID = 0;
String QueryName = "Trae Dimension 1";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"IntrnalKey\" FROM OUQR WHERE \"QName\" = '" + QueryName + "' and \"QCategory\" = " + CategoryID + "";
break;
default:
Query = "SELECT IntrnalKey FROM OUQR WHERE QName = '" + QueryName + "' and QCategory = " + CategoryID + "";
break;
}
oRecordSet = null;
oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
QueryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
SAPbobsCOM.FormattedSearches oFtts = null;
oFtts = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches);
oFtts.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFtts.FormID = "SEI_DAT";
oFtts.ItemID = "Item_0";
oFtts.ColumnID = "col_Esp";
oFtts.QueryID = QueryID;
oFtts.ByField = BoYesNoEnum.tNO;
oFtts.ForceRefresh = BoYesNoEnum.tNO;
oFtts.Refresh = BoYesNoEnum.tNO;
oFtts.Add();
QueryID = 0;
QueryName = "Trae Dimension 2";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"IntrnalKey\" FROM OUQR WHERE \"QName\" = '" + QueryName + "' and \"QCategory\" = " + CategoryID + "";
break;
default:
Query = "SELECT IntrnalKey FROM OUQR WHERE QName = '" + QueryName + "' and QCategory = " + CategoryID + "";
break;
}
oRecordSet = null;
oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
QueryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
oFtts = null;
oFtts = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches);
oFtts.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFtts.FormID = "SEI_DAT";
oFtts.ItemID = "Item_0";
oFtts.ColumnID = "col_Var";
oFtts.QueryID = QueryID;
oFtts.ByField = BoYesNoEnum.tNO;
oFtts.ForceRefresh = BoYesNoEnum.tNO;
oFtts.Refresh = BoYesNoEnum.tNO;
oFtts.Add();
QueryID = 0;
QueryName = "Trae Dimension 3";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"IntrnalKey\" FROM OUQR WHERE \"QName\" = '" + QueryName + "' and \"QCategory\" = " + CategoryID + "";
break;
default:
Query = "SELECT IntrnalKey FROM OUQR WHERE QName = '" + QueryName + "' and QCategory = " + CategoryID + "";
break;
}
oRecordSet = null;
oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
QueryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
oFtts = null;
oFtts = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches);
oFtts.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFtts.FormID = "SEI_DAT";
oFtts.ItemID = "Item_0";
oFtts.ColumnID = "col_Cat";
oFtts.QueryID = QueryID;
oFtts.ByField = BoYesNoEnum.tNO;
oFtts.ForceRefresh = BoYesNoEnum.tNO;
oFtts.Refresh = BoYesNoEnum.tNO;
oFtts.Add();
QueryID = 0;
QueryName = "Trae Dimension 4";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"IntrnalKey\" FROM OUQR WHERE \"QName\" = '" + QueryName + "' and \"QCategory\" = " + CategoryID + "";
break;
default:
Query = "SELECT IntrnalKey FROM OUQR WHERE QName = '" + QueryName + "' and QCategory = " + CategoryID + "";
break;
}
oRecordSet = null;
oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
QueryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
oFtts = null;
oFtts = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches);
oFtts.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFtts.FormID = "SEI_DAT";
oFtts.ItemID = "Item_0";
oFtts.ColumnID = "col_Catpa";
oFtts.QueryID = QueryID;
oFtts.ByField = BoYesNoEnum.tNO;
oFtts.ForceRefresh = BoYesNoEnum.tNO;
oFtts.Refresh = BoYesNoEnum.tNO;
oFtts.Add();
QueryID = 0;
QueryName = "Trae Dimension 5";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"IntrnalKey\" FROM OUQR WHERE \"QName\" = '" + QueryName + "' and \"QCategory\" = " + CategoryID + "";
break;
default:
Query = "SELECT IntrnalKey FROM OUQR WHERE QName = '" + QueryName + "' and QCategory = " + CategoryID + "";
break;
}
oRecordSet = null;
oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
QueryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
oFtts = null;
oFtts = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches);
oFtts.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFtts.FormID = "SEI_DAT";
oFtts.ItemID = "Item_0";
oFtts.ColumnID = "col_Rolp";
oFtts.QueryID = QueryID;
oFtts.ByField = BoYesNoEnum.tNO;
oFtts.ForceRefresh = BoYesNoEnum.tNO;
oFtts.Refresh = BoYesNoEnum.tNO;
oFtts.Add();
QueryID = 0;
QueryName = "Trae Cuenta Contable";
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"IntrnalKey\" FROM OUQR WHERE \"QName\" = '" + QueryName + "' and \"QCategory\" = " + CategoryID + "";
break;
default:
Query = "SELECT IntrnalKey FROM OUQR WHERE QName = '" + QueryName + "' and QCategory = " + CategoryID + "";
break;
}
oRecordSet = null;
oRecordSet = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
oRecordSet.DoQuery(Query);
if (!oRecordSet.EoF)
{
QueryID = Convert.ToInt32(oRecordSet.Fields.Item(0).Value);
}
oFtts = null;
oFtts = Conexion_SBO.m_oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches);
oFtts.Action = BoFormattedSearchActionEnum.bofsaQuery;
oFtts.FormID = "SEI_DAT";
oFtts.ItemID = "Item_0";
oFtts.ColumnID = "col_Cta";
oFtts.QueryID = QueryID;
oFtts.ByField = BoYesNoEnum.tNO;
oFtts.ForceRefresh = BoYesNoEnum.tNO;
oFtts.Refresh = BoYesNoEnum.tNO;
oFtts.Add();
}
catch (Exception ex)
{
String err = ex.Message;
}
}