using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using CameraDb.model;

namespace CameraDb.oracle
{
    public class AlarmOra
    {
        // Static constants 
        private const string TABLE_NAME = "PG_ALARM";

        private const string COLUMN_ALARMDATE = "ALARM_DATE";
        private const string COLUMN_ALARMVALUE = "ALARM_VALUE";
        private const string COLUMN_DESCRIPTION = "DESCRIPTION";
        private const string COLUMN_ALARMDEVID = "ALARMDEVID";
        private const string COLUMN_ALARMTYPEID = "ALARMTYPEID";
        private const string COLUMN_USERID = "USERID";
        private const string COLUMN_ACTIVE = "ACTIVE";
        private const string COLUMN_ALARM_GRADE = "ALARM_GRADE";
        private const string COLUMN_LOG_TIME = "LOG_TIME";
        private const string COLUMN_LOG_DESC = "LOG_DESC";

        private const string PARM_ALARMDATE = ":ALARM_DATE";
        private const string PARM_ALARMVALUE = ":ALARM_VALUE";
        private const string PARM_DESCRIPTION = ":DESCRIPTION";
        private const string PARM_ALARMDEVID = ":ALARMDEVID";
        private const string PARM_ALARMTYPEID = ":ALARMTYPEID";
        private const string PARM_USERID = ":USERID";
        private const string PARM_ACTIVE = ":ACTIVE";
        private const string PARM_ALARM_GRADE = ":ALARM_GRADE";
        private const string PARM_LOG_TIME = ":LOG_TIME";
        private const string PARM_LOG_DESC = ":LOG_DESC";

        private const string SQL_INSERT_ALARM_RECORD = "INSERT INTO " + TABLE_NAME
                                                       + " (ID,"
                                                       + " " + COLUMN_ALARMDATE + ","
                                                       + " " + COLUMN_ALARMVALUE + ","
                                                       + " " + COLUMN_DESCRIPTION + ","
                                                       + " " + COLUMN_ALARMDEVID + ","
                                                       + " " + COLUMN_ALARMTYPEID + ","
                                                       + " " + COLUMN_ACTIVE + ","
                                                       + " " + COLUMN_ALARM_GRADE + ","
                                                       + " " + COLUMN_LOG_TIME + ","
                                                       + " " + COLUMN_LOG_DESC + ")"
                                                       + " VALUES "
                                                       + " (SEQ_PG_ALARM_ID.NEXTVAL,"
                                                       + " " + PARM_ALARMDATE + ","
                                                       + " " + PARM_ALARMVALUE + ","
                                                       + " " + PARM_DESCRIPTION + ","
                                                       + " " + PARM_ALARMDEVID + ","
                                                       + " " + PARM_ALARMTYPEID + ","
                                                       + " " + PARM_ACTIVE + ","
                                                       + " " + PARM_ALARM_GRADE + ","
                                                       + " " + PARM_LOG_TIME + ","
                                                       + " " + PARM_LOG_DESC + ")";

        public void insert(Alarm alarm)
        {
            OracleTransaction tran = null;
            using (OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringOrderDistributedTransaction))
            {
                try
                {
                    conn.Open();

                    updateAlarm(alarm,conn);
                    tran = conn.BeginTransaction();

                    OracleParameter[] parms = GetAdapterParameters();
                    SetAdapterParameters(parms, alarm);
                    OracleHelper.ExecuteNonQuery(tran, CommandType.Text, SQL_INSERT_ALARM_RECORD, parms);
                    tran.Commit();
                }
                catch (Exception e)
                {
                    if (null != tran)
                    {
                        tran.Rollback();
                    }
                    throw (e);
                }
            }
        }

        /// <summary>
        /// An internal function to bind values parameters for insert
        /// </summary>
        /// <param name="parms">Database parameters</param>
        /// <param name="noise">Values to bind to parameters</param>
        private void SetAdapterParameters(OracleParameter[] parms, model.Alarm alarm)
        {

            if (null != alarm.ALARM_DATE)
            {
                parms[0].Value = alarm.ALARM_DATE;
            }
            else
            {
                parms[0].Value = DBNull.Value;
            }

            if (null != alarm.ALARM_VALUE)
            {
                parms[1].Value = alarm.ALARM_VALUE;
            }
            else
            {
                parms[1].Value = DBNull.Value;
            }

            if (null != alarm.DESCRIPTION)
            {
                parms[2].Value = alarm.DESCRIPTION;
            }
            else
            {
                parms[2].Value = DBNull.Value;
            }
            if (null != alarm.ALARMDEVID)
            {
                parms[3].Value = alarm.ALARMDEVID;
            }
            else
            {
                parms[3].Value = DBNull.Value;
            }

            if (null != alarm.ALARMTYPEID)
            {
                parms[4].Value = alarm.ALARMTYPEID;
            }
            else
            {
                parms[4].Value = DBNull.Value;
            }

            if (null != alarm.ACTIVE)
            {
                parms[5].Value = alarm.ACTIVE;
            }
            else
            {
                parms[5].Value = DBNull.Value;
            }
            if (null != alarm.ALARM_GRADE)
            {
                parms[6].Value = alarm.ALARM_GRADE;
            }
            else
            {
                parms[6].Value = DBNull.Value;
            }
            if (null != alarm.LOG_TIME)
            {
                parms[7].Value = alarm.LOG_TIME;
            }
            else
            {
                parms[7].Value = DBNull.Value;
            }
            if (null != alarm.LOG_DESC)
            {
                parms[8].Value = alarm.LOG_DESC;
            }
            else
            {
                parms[8].Value = DBNull.Value;
            }
        }

        /// <summary>
        /// An internal function to get the database parameters
        /// </summary>
        /// <returns>Parameter array</returns>
        private static OracleParameter[] GetAdapterParameters()
        {
            OracleParameter[] parms = new OracleParameter[]{	
                                       new OracleParameter(PARM_ALARMDATE, OracleType.DateTime),
				                        new OracleParameter(PARM_ALARMVALUE, OracleType.VarChar, 255),
                                        new OracleParameter(PARM_DESCRIPTION, OracleType.VarChar, 255),
                                        new OracleParameter(PARM_ALARMDEVID, OracleType.Number, 19),
                                        new OracleParameter(PARM_ALARMTYPEID, OracleType.Number, 19),
                                        new OracleParameter(PARM_ACTIVE, OracleType.Int32),
                                        new OracleParameter(PARM_ALARM_GRADE,OracleType.VarChar,255),
                                        new OracleParameter(PARM_LOG_TIME, OracleType.DateTime),
                                        new OracleParameter(PARM_LOG_DESC, OracleType.VarChar,255)};

            return parms;
        }

        private static void updateAlarm(Alarm alarm, OracleConnection conn) {
            
            String sql = "update " + TABLE_NAME+ " set active = 0 where ALARMDEVID = "+alarm.ALARMDEVID +" and active = 1";
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }

    }
}