/DICK.B1/B1QueryTools.cs

/*  This file is part of DI Construction Kit.

 *

 *  DI Construction Kit is free software: you can redistribute it and/or modify

 *  it under the terms of the GNU Lesser General Public License as published by

 *  the Free Software Foundation, either version 3 of the License, or

 *  (at your option) any later version.

 *

 *  DI Construction Kit is distributed in the hope that it will be useful,

 *  but WITHOUT ANY WARRANTY; without even the implied warranty of

 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 *  GNU Lesser General Public License for more details.

 *

 *  You should have received a copy of the GNU Lesser General Public License

 *  along with DI Construction Kit.  If not, see <http://www.gnu.org/licenses/>.   

 *

 */

using System;

using System.Collections.Generic;

using System.Text;

using SAPbobsCOM;

using DICK.Core;

using System.Data;



namespace DICK.B1

{

    /// <summary>

    /// This library contains static helper methods that help using the DI API query resultsets.

    /// </summary>

    public class B1QueryTools

    {

        /// <summary>

        /// This is a library class not meant to be instantiated.

        /// </summary>

        private B1QueryTools() { }



        /// <summary>

        /// Retrieves the first record from the query resultset and converts it to an instance of Dictionary<string,object>.

        /// </summary>

        /// <param name="session">CompanyClass object</param>

        /// <param name="query">The query to be executed</param>

        /// <returns></returns>

        public static Dictionary<string,object> QueryRecordToDictionary(CompanyClass session, string query)

        {

            Dictionary<string, object> result = new Dictionary<string, object>();

            SAPbobsCOM.Recordset rec;

            rec = (Recordset)session.GetBusinessObject(BoObjectTypes.BoRecordset);

            rec.DoQuery(query);

            if (rec.RecordCount > 0)

            {

                rec.MoveFirst();

                for (int fieldIndex = 0; fieldIndex < rec.Fields.Count; fieldIndex++)

                {

                    Field field = rec.Fields.Item(fieldIndex);

                    result.Add(field.Name, field.Value);

                }

            }

            if (result.Count == 0)

            {

                Engine.GetInstance().LogWarn("query:" + query + "returned an empty recordset!");

            }

            return result;

        }



        public static List<object> QueryColumnToList(CompanyClass session, string query)

        {

            return QueryColumnToList(session, query,0);

        }



        public static void ExecuteQuery(string query)

        {            

                CompanyClass sess = B1Engine.GetInstance().session;

                SAPbobsCOM.Recordset rec;

                rec = (Recordset)sess.GetBusinessObject(BoObjectTypes.BoRecordset);

                rec.DoQuery(query);            

        }



        public static List<object> QueryColumnToList(CompanyClass session, string query,int columnno)

        {

            List<object> result = new List<object>();

            SAPbobsCOM.Recordset rec;

            rec = (Recordset)session.GetBusinessObject(BoObjectTypes.BoRecordset);

            rec.DoQuery(query);

            if (rec.RecordCount > 0)

            {

                rec.MoveFirst();

                for (int rowindex = 0; rowindex < rec.RecordCount; rowindex++)

                {

                    Field field = rec.Fields.Item(columnno);

                    result.Add(field.Value.ToString());

                    rec.MoveNext();

                }

            }

            return result;

        }



        public static System.Data.DataSet QueryToDataSet(string dataTableName, CompanyClass session, string query, out MessageToken res)

        {

            Engine app = Engine.GetInstance();

            app.LogDebug("QueryToDataSet:b " + query);

            DataSet dataSet = new DataSet();

            dataSet.Tables.Add(QueryToDataTable(dataTableName, session, query, out res));

            return dataSet;

        }



        public static System.Data.DataTable QueryToDataTable(string dataTableName, string query, out MessageToken res)

        {

            return QueryToDataTable(dataTableName, B1Engine.GetInstance().session, query, out res);

        }



        public static System.Data.DataTable QueryToDataTable(string dataTableName, CompanyClass session, string query, out MessageToken res)

        {

            res = new MessageToken();

            res.OK = true;            

            Engine app = Engine.GetInstance();

            app.LogDebug("Calling getQueryTable with " + query);

            if (session == null)

            {

                res.OK = false;                

                res.AddTextToBody("Session is null");

                app.LogError("session is null");

            }

            DataTable result = new DataTable(dataTableName);

            SAPbobsCOM.Recordset rec;

            rec = (Recordset)session.GetBusinessObject(BoObjectTypes.BoRecordset);

            if (rec == null)

            {

                res.OK = false;                

                res.AddTextToBody("Recordset is null");

                app.LogError("Recordset is null");

            }

            rec.DoQuery(query);

            if (rec != null && rec.RecordCount > 0)

            {

                rec.MoveFirst();

                foreach (Field f in rec.Fields)

                {

                    result.Columns.Add(f.Name, f.Value.GetType());

                }

                for (int rowindex = 0; rowindex < rec.RecordCount; rowindex++)

                {

                    object[] row = new object[rec.Fields.Count];

                    int col = 0;

                    foreach (Field f in rec.Fields)

                    {

                        app.LogDebug("FIELDINFO: " + f.Name + ":" + f.Type + "/" + f.Value);

                        row[col] = f.Value;

                        col++;

                    }

                    result.Rows.Add(row);

                    rec.MoveNext();

                }

            }

            return result;

        }



        public static List<Dictionary<string,object>> QueryTableToDictionaryList(CompanyClass session, string query)

        {

            Engine app = Engine.GetInstance();

            app.LogDebug("Calling getQueryTable with " + query);

            if (session == null)

            {

                app.LogError("session is null");

            }

            List<Dictionary<string,object>> result = new List<Dictionary<string,object>>();

            SAPbobsCOM.Recordset rec;

            rec = (Recordset)session.GetBusinessObject(BoObjectTypes.BoRecordset);

            if (rec == null)

            {

                app.LogError("recordset is null!");

            }

            rec.DoQuery(query);

            if (rec != null && rec.RecordCount > 0)

            {

                rec.MoveFirst();

                for (int rowindex = 0; rowindex < rec.RecordCount; rowindex++)

                {

                    Dictionary<string,object> row = new Dictionary<string, object>();

                    for (int columnindex = 0; columnindex < rec.Fields.Count; columnindex++)

                    {

                        Field field = rec.Fields.Item(columnindex);

                        app.LogDebug("FIELDINFO: " + field.Name + ":" + field.Type + "/" + field.Value);

                        row.Add(field.Name, field.Value.ToString());

                    }

                    result.Add(row);

                    rec.MoveNext();

                }

            }

            return result;

        }



        public static object QuerySingleValue(string qry, string returnkey)

        {

            Engine app = Engine.GetInstance();

            app.LogDebug("Calling dynamic retrieval");

            if (qry != null && qry.Trim() != "")

            {

                app.LogDebug("querying " + qry);

                Dictionary<string,object> qryresult = QueryRecordToDictionary(B1Engine.GetInstance().session, qry);

                object result = null;

                bool ok=qryresult.TryGetValue(returnkey, out result);

                if (ok)

                {

                    return result;

                }

                else

                {

                    app.LogError("B1.B1QueryTools.QuerySingleValue: failed to get value from Dictionary with key " + returnkey);

                    return null;

                }

            }

            else

            {

                app.LogError("ERROR WHILE CONSTRUCTING QUERY FOR DYNAMIC CONVERSION. QUERY=" + qry);

                return null;

            }

        }



        public static object QuerySingleValue(string query, string returnkey, string format)

        {

            object o = QuerySingleValue(query, returnkey);

            if (o != null)

            {

                return B1Engine.FormatValue(o, format);

            }

            else

            {

                return "";

            }

        }

    }

}