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();
        }
Ejemplo n.º 2
0
        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;
            }
        }
        /// <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);
        }