using System;
using System.Collections.Generic;
using System.Linq;
using BHEL.PUMPSDAS.Datatypes;
using System.Data.Odbc;
using DasCoreUtilities;

namespace DasCoreLib
{
    public class DataManager
    {
        DatabaseManager _database;


        #region Queries

        string qry_insertNewTestMeasurements = "insert into testmeasurements "+
            "(fk_machinecode,fk_projectcode,fk_machinenumber, fk_testreference, paramname, paramvalue, displayindex, date, time, pointnum, noise1, noise2 ) "+
            "values({0},{1},'{2}','{3}','{4}',{5},{6},'{7}','{8}',{9},'{10}','{11}')";

        string qry_insertNewTestMeasurements_act = "insert into testmeasurements_act " +
            "(fk_machinecode,fk_projectcode,fk_machinenumber, fk_testreference, paramname, paramvalue, displayindex, date, time, pointnum, noise1, noise2 ) " +
            "values({0},{1},'{2}','{3}','{4}',{5},{6},'{7}','{8}',{9},'{10}','{11}')";

        string qry_insertNewTestResults = "insert into testresults " +
            "(fk_machinecode,fk_projectcode,fk_machinenumber, fk_testreference, paramname, paramvalue, displayindex, date, time, pointnum ) " +
            "values({0},{1},'{2}','{3}','{4}',{5},{6},'{7}','{8}',{9})";

        string qry_insertNewTestResults_act = "insert into testresults_act " +
            "(fk_machinecode,fk_projectcode,fk_machinenumber, fk_testreference, paramname, paramvalue, displayindex, date, time, pointnum ) " +
            "values({0},{1},'{2}','{3}','{4}',{5},{6},'{7}','{8}',{9})";

        string qry_maxRecPoint = "select max(pointnum) as pointnum from testmeasurements where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}'";

        string qry_getMeasurementDataPoint = "select paramname, paramvalue, displayindex, to_char(date,'yyyy-mm-dd') as date, time, noise1, noise2 from testmeasurements " +
            "where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}' and pointnum={4} order by displayindex";

        string qry_getActMeasurementDataPoint = "select paramname, paramvalue, displayindex, to_char(date,'yyyy-mm-dd') as date, time, noise1, noise2 from testmeasurements_act " +
            "where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}' and pointnum={4} order by displayindex";

        string qry_getResultDataPoint = "select paramname, paramvalue, displayindex, to_char(date,'yyyy-mm-dd') as date, time from testresults " +
            "where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}' and pointnum={4} order by displayindex";

        string qry_getActResultDataPoint = "select paramname, paramvalue, displayindex, to_char(date,'yyyy-mm-dd') as date, time from testresults_act " +
            "where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}' and pointnum={4} order by displayindex";

        string qry_deleteTestMeasurementData = "delete from testmeasurements where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}'";

        string qry_deleteActTestMeasurementData = "delete from testmeasurements_act where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}'";

        string qry_deleteTestResultData = "delete from testresults where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}'";

        string qry_deleteActTestResultData = "delete from testresults_act where fk_machinecode={0} and fk_projectcode={1} and fk_machinenumber='{2}' and fk_testreference='{3}'";

        #endregion

        public DataManager(DatabaseManager _database)
        {
            this._database = _database;
        }   

        public void RecordData(int mc, int projc, string mnum, string tref, 
                                List<ObjectData> testmeasurement, List<ObjectData> testresult, 
                                DateTime dt, int pointnum, string noise1, string noise2, bool isActual)
        {
            string qryM, qryR, queries="";
            string date, time;

            if (isActual)
            {
                qryM = qry_insertNewTestMeasurements_act;
                qryR = qry_insertNewTestResults_act;
            }
            else
            {
                qryM = qry_insertNewTestMeasurements;
                qryR = qry_insertNewTestResults;
            }

            for (int i = 0; i < testmeasurement.Count; i++)
            {
                date = dt.Month + "/" + dt.Day + "/" + dt.Year;
                time = dt.Hour + ":" + dt.Minute + ":" + dt.Second;

                queries += string.Format(qryM, mc, projc, mnum, tref,
                                testmeasurement[i].pname, testmeasurement[i].pvalue, testmeasurement[i].pindex,
                                date, time, pointnum, noise1, noise2) + ";";
            }

            for (int i = 0; i < testresult.Count; i++)
            {
                date = dt.Month + "/" + dt.Day + "/" + dt.Year;
                time = dt.Hour + ":" + dt.Minute + ":" + dt.Second;

                queries += string.Format(qryR, mc, projc, mnum, tref,
                                testresult[i].pname, testresult[i].pvalue, testresult[i].pindex,
                                date, time, pointnum) + ";";
            }

            _database.ExecuteInsertQuery(queries);

        }

        public int GetNumberOfRecordedPoints(int mc, int projc, string mnum, string tref)
        {
            OdbcDataReader reader;
            string qry = string.Format(qry_maxRecPoint, mc, projc, mnum, tref);
            string data;
            reader = _database.ExecuteSelectQuery(qry);
            if (reader.HasRows)
            {
                reader.Read();
                data = reader["pointnum"].ToString();
                if (data == "")
                    return -1;
                else
                    return int.Parse(reader["pointnum"].ToString());
            }
            return -1;
        }
        

        public bool GetMeasurementDataPoint(int mc, int projc, string mnum, string tref, int pointnum, bool isActual, 
            out List<ObjectData> measPt, out List<ObjectData> resultPt,
            out DateTime dt, out string noise1, out string noise2)
        {
            measPt = new List<ObjectData>();
            resultPt = new List<ObjectData>();
            dt = new DateTime();

            noise1 = "";
            noise2 = "";

            if (GetNumberOfRecordedPoints(mc, projc, mnum, tref) < pointnum)
                return false;                       
            
            OdbcDataReader reader;

            string qryM, qryR;
            string date, time;

            if (isActual)
            {
                qryM = qry_getActMeasurementDataPoint;
                qryR = qry_getActResultDataPoint;
            }
            else
            {
                qryM = qry_getMeasurementDataPoint;
                qryR = qry_getResultDataPoint;
            }

            qryM = string.Format(qryM, mc, projc, mnum, tref, pointnum);
            qryR = string.Format(qryR, mc, projc, mnum, tref, pointnum);

            reader = _database.ExecuteSelectQuery(qryM);

            if (reader.HasRows)
            {
                reader.Read();
                date = reader["date"].ToString();
                time = reader["time"].ToString();
                date += " " + time;
                dt = DateTime.ParseExact(date, "yyyy-MM-dd HH:mm:ss", null);
                noise1 = reader["noise1"].ToString();
                noise2 = reader["noise2"].ToString();
                ObjectData objData;

                do
                {
                    objData = new ObjectData();
                    objData.pname = reader["paramname"].ToString();
                    objData.pvalue = reader["paramvalue"].ToString();
                    objData.pindex = DefaultConversion.GetIntValue(reader["displayindex"].ToString());

                    measPt.Add(objData);
                }
                while (reader.Read());
            }

            reader = _database.ExecuteSelectQuery(qryR);

            if (reader.HasRows)
            {
                reader.Read();               
                
                ObjectData objData;

                do
                {
                    objData = new ObjectData();
                    objData.pname = reader["paramname"].ToString();
                    objData.pvalue = reader["paramvalue"].ToString();
                    objData.pindex = DefaultConversion.GetIntValue(reader["displayindex"].ToString());

                    resultPt.Add(objData);
                }
                while (reader.Read());
            }

            return true;
        }

        public void DeleteTestData(int mc, int projc, string mnum, string tref)
        {
            string qry = "";

            qry += string.Format(qry_deleteTestMeasurementData, mc, projc, mnum, tref) + ";";

            qry += string.Format(qry_deleteActTestMeasurementData, mc, projc, mnum, tref)+";";

            qry += string.Format(qry_deleteTestResultData, mc, projc, mnum, tref)+";";

            qry += string.Format(qry_deleteActTestResultData, mc, projc, mnum, tref)+";";

            _database.ExecuteSystemQuery(qry);
        }

    }
}