using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Analyse2
{
    class AnalyseMSSql
    {
        protected SqlConnection _connection = null;
        protected DateTime _interval_start;
        protected DateTime _interval_end;

        public AnalyseMSSql(string ConnectionString, DateTime startDate, DateTime endDate)
        {
            _connection = new SqlConnection(ConnectionString);
            _interval_start = startDate;
            _interval_end = endDate;
        }

        public bool OpenConnection()
        {
            _connection.Open();
            return true;
        }

        public bool CloseConnection()
        {
            _connection.Close();
            return true;
        }

        public ConnectionState StateConnection
        {
            get { return _connection.State; }
        }

//        public SensorInformation GetSensorInformation(int id)
//        {
//            if (this.StateConnection != ConnectionState.Open)
//                this.OpenConnection();

//            OracleCommand command = _connection.CreateCommand();
//            command.CommandText = String.Format(@"select * from sensor where sensor_id={0}", id);

//            SensorInformation res = new SensorInformation();

//            try
//            {
//                OracleDataReader reader = command.ExecuteReader();
//                if (reader != null)
//                {
//                    while (reader.Read())
//                    {
//                        res.ID = reader["sensor_id"].ToString();
//                        res.Oldcode = reader["sensor_oldcode"].ToString();
//                        res.Description = reader["sensor_descr"].ToString();
//                    }
//                }

//            }
//            catch
//            {
//                return null;
//            }

//            command.CommandText = String.Format(@"select /*+ INDEX_DESC (t idx$measure$measure_date) */ 
//                        max(t.measure_id) from measure t where sensor_id={2} and t.measure_date between '{0}' and '{1}'",
//                _interval_start.ToShortDateString(),
//                _interval_end.ToShortDateString(),
//                id);

//            try
//            {
//                double measure_id = (double)(OracleNumber)command.ExecuteOracleScalar();

//                command.CommandText = String.Format("select measure_date from measure where measure_id={0}", measure_id);

//                res.LastMeasure = command.ExecuteOracleScalar().ToString();

//                command.CommandText = String.Format(@"select count(*) as count_lane from (
//                        select t.sensor_lane_num from measure_values t where measure_id = {0} group by t.sensor_lane_num) tmp", measure_id);

//                res.LineCount = command.ExecuteOracleScalar().ToString();
//            }
//            catch { }

//            return res;

//        }


//        public int GetOldcode(int id)
//        {
//            //select sensor_oldcode from measure where sensor_id={0}

//            if (this.StateConnection != ConnectionState.Open)
//                this.OpenConnection();


//            OracleCommand command = _connection.CreateCommand();
//            command.CommandText = String.Format(@"select sensor_oldcode from measure where sensor_id={0}", id);

//            try
//            {
//                return (int)(OracleNumber)command.ExecuteOracleScalar();
//            }
//            catch
//            {
//                return -1;
//            }
//        }

        public string GetMeasureCount(int id)
        {
            //select /*+ INDEX_DESC (t idx$measure$measure_date) */ count(distinct(measure_date)) from measure t where t.sensor_id={0} and t.measure_date between '{1}' and '{2}'
            if (this.StateConnection != ConnectionState.Open)
                this.OpenConnection();


            SqlCommand command = _connection.CreateCommand();
            command.CommandText = String.Format("select /*+ INDEX_DESC (t idx$sensordata$datetime_) */ count(*) from sensordata t where sensor_id={0} and t.datetime_ between '{1}' and '{2}'",
                id,
                _interval_start.ToString("yyyy-MM-dd"),
                _interval_end.ToString("yyyy-MM-dd"));

            try
            {
                return command.ExecuteScalar().ToString();
            }
            catch(Exception e)
            {
                return e.Message;
            }

        }
    }
}