using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using fatd_faturamento.ENTIDADE;
using fatd_faturamento.DAL.Conexao;

namespace fatd_faturamento.DAL.Persistencia
{
    public class dalConsumiPalm
    {

        string vFILTER = null;
        string vINNERJOIN = null;
        string vAND = null;
        string vORDERBY = null;

        #region Métodos

        public void Insert(entidadeConsumiPalm objConsumiPalm)
        {
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;
            int idCod = 0;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "UNIQUEID = ?UNIQUEID , ";
                    vFILTER = vFILTER + "CATEGORY = ?CATEGORY , ";
                    vFILTER = vFILTER + "DIRTY   = ?DIRTY   , ";
                    vFILTER = vFILTER + "SECRET = ?SECRET , ";
                    vFILTER = vFILTER + "STATUS  = ?STATUS  , ";
                    vFILTER = vFILTER + "ROTA    = ?ROTA    , ";
                    vFILTER = vFILTER + "SEQ    = ?SEQ    , ";
                    vFILTER = vFILTER + "ZONA   = ?ZONA   , ";
                    vFILTER = vFILTER + "LIVRO   = ?LIVRO   , ";
                    vFILTER = vFILTER + "CONTA   = ?CONTA   , ";
                    vFILTER = vFILTER + "SETOR = ?SETOR , ";
                    vFILTER = vFILTER + "LEIT_ANT = ?LEIT_ANT , ";
                    vFILTER = vFILTER + "LEIT_ATUAL = ?LEIT_ATUAL , ";
                    vFILTER = vFILTER + "MEDIDOR = ?MEDIDOR , ";
                    vFILTER = vFILTER + "ENDERECO = ?ENDERECO , ";
                    vFILTER = vFILTER + "MUNICIP = ?MUNICIP , ";
                    vFILTER = vFILTER + "DATA    = ?DATA    , ";
                    vFILTER = vFILTER + "NOME    = ?NOME    , ";
                    vFILTER = vFILTER + "MEDIA   = ?MEDIA   , ";
                    vFILTER = vFILTER + "OCORRENC = ?OCORRENC , ";
                    vFILTER = vFILTER + "MEDKVARH = ?MEDKVARH , ";
                    vFILTER = vFILTER + "KVARH    = ?KVARH    , ";
                    vFILTER = vFILTER + "KVARHATUAL = ?KVARHATUAL , ";
                    vFILTER = vFILTER + "SITUACAO = ?SITUACAO  ";


                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?UNIQUEID", objConsumiPalm.UNIQUEID);
                    cmd.Parameters.AddWithValue("?CATEGORY", objConsumiPalm.CATEGORY);
                    cmd.Parameters.AddWithValue("?DIRTY", objConsumiPalm.DIRTY);
                    cmd.Parameters.AddWithValue("?SECRET", objConsumiPalm.SECRET);
                    cmd.Parameters.AddWithValue("?STATUS", objConsumiPalm.STATUS);
                    cmd.Parameters.AddWithValue("?ROTA", objConsumiPalm.ROTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?SEQ", objConsumiPalm.SEQ.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ZONA", objConsumiPalm.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objConsumiPalm.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objConsumiPalm.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?SETOR", objConsumiPalm.SETOR.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LEIT_ANT", objConsumiPalm.LEIT_ANT.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LEIT_ATUAL", objConsumiPalm.LEIT_ATUAL.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MEDIDOR", objConsumiPalm.MEDIDOR.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ENDERECO", objConsumiPalm.ENDERECO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MUNICIP", objConsumiPalm.MUNICIP.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATA", objConsumiPalm.DATA);
                    cmd.Parameters.AddWithValue("?NOME", objConsumiPalm.NOME.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MEDIA", objConsumiPalm.MEDIA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?OCORRENC", objConsumiPalm.OCORRENC.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MEDKVARH", objConsumiPalm.MEDKVARH.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?KVARH", objConsumiPalm.KVARH);
                    cmd.Parameters.AddWithValue("?KVARHATUAL", objConsumiPalm.KVARHATUAL);
                    cmd.Parameters.AddWithValue("?SITUACAO", objConsumiPalm.SITUACAO.ToString().ToUpper());


                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select(entidadeConsumiPalm objConsumiPalm)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;


            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "DISTINCT LEFT(ROTA, 4) ROTA ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_1(entidadeConsumiPalm objConsumiPalm)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;


            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "UNIQUEID , ";
                    vFILTER = vFILTER + "CATEGORY , ";
                    vFILTER = vFILTER + "DIRTY , ";
                    vFILTER = vFILTER + "SECRET , ";
                    vFILTER = vFILTER + "STATUS , ";
                    vFILTER = vFILTER + "ROTA , ";
                    vFILTER = vFILTER + "SEQ , ";
                    vFILTER = vFILTER + "ZONA , ";
                    vFILTER = vFILTER + "LIVRO , ";
                    vFILTER = vFILTER + "CONTA , ";
                    vFILTER = vFILTER + "SETOR , ";
                    vFILTER = vFILTER + "LEIT_ANT , ";
                    vFILTER = vFILTER + "LEIT_ATUAL , ";
                    vFILTER = vFILTER + "MEDIDOR , ";
                    vFILTER = vFILTER + "NOME , ";
                    vFILTER = vFILTER + "ENDERECO , ";
                    vFILTER = vFILTER + "MUNICIP , ";
                    vFILTER = vFILTER + "MEDIA , ";
                    vFILTER = vFILTER + "OCORRENC , ";
                    vFILTER = vFILTER + "DATA , ";
                    vFILTER = vFILTER + "MEDKVARH , ";
                    vFILTER = vFILTER + "KVARH , ";
                    vFILTER = vFILTER + "KVARHATUAL , ";
                    vFILTER = vFILTER + "SITUACAO , ";
                    vFILTER = vFILTER + "Z_OUTROS , ";
                    vFILTER = vFILTER + "Z_USUARIO ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public void Delete(entidadeConsumiPalm objConsumiPalm)
        {
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vORDERBY = " ";

                    vAND = " ";

                    cmd = new MySqlCommand(this.QueryDelete);

                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        #endregion

        #region " Comandos SQL "

        StringBuilder sb = new StringBuilder();

        protected string QueryInsert
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("INSERT INTO  ");
                this.sb.AppendLine("CONSUMIPALM SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

        protected string QueryUpdate
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("UPDATE  ");
                this.sb.AppendLine("CONSUMIPALM SET  ");
                this.sb.AppendLine(vFILTER);
                this.sb.AppendLine("WHERE 1 = 1 ");
                this.sb.AppendLine(vAND);

                return sb.ToString();
            }
        }

        protected string QueryDelete
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("DELETE FROM CONSUMIPALM ");
                this.sb.AppendLine("WHERE 1 = 1 ");
                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        protected string QuerySelect
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("SELECT ");
                this.sb.AppendLine(vFILTER);
                this.sb.AppendLine("FROM ");
                this.sb.AppendLine("CONSUMIPALM  ");
                this.sb.AppendLine(vINNERJOIN);
                this.sb.AppendLine("WHERE 1 = 1  ");
                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        #endregion


    }
}
