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 dalClasses
    {
        string vFILTER = null;
        string vINNERJOIN = null;
        string vAND = null;
        string vORDERBY = null;

        #region Métodos

        public void Insert(entidadeClasses objClasses)
        {
            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 + "  DESCRICAO    = ?DESCRICAO  ";
                    vFILTER = vFILTER + ", CONTROLE  	= ?CONTROLE  ";
					vFILTER = vFILTER + ", ORD_CTA  	= ?ORD_CTA  ";
					vFILTER = vFILTER + ", IDPRODIST_MOD06_TCLA_SUBCLA  = ?IDPRODIST_MOD06_TCLA_SUBCLA  ";
					vFILTER = vFILTER + ", SITUACAO  	= ?SITUACAO  ";
					vFILTER = vFILTER + ", EXTRAS  		= ?EXTRAS  ";
					vFILTER = vFILTER + ", IDUSUARIO  	= ?IDUSUARIO  ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?DESCRICAO", objClasses.DESCRICAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTROLE", objClasses.CONTROLE.ToString().ToUpper());
					cmd.Parameters.AddWithValue("?ORD_CTA", objClasses.ORD_CTA);
					cmd.Parameters.AddWithValue("?IDPRODIST_MOD06_TCLA_SUBCLA", objClasses.IDPRODIST_MOD06_TCLA_SUBCLA);
					cmd.Parameters.AddWithValue("?SITUACAO", objClasses.SITUACAO.ToString().ToUpper());
					cmd.Parameters.AddWithValue("?EXTRAS", objClasses.EXTRAS.ToString().ToUpper());
					cmd.Parameters.AddWithValue("?IDUSUARIO", objClasses.IDUSUARIO);


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

        public void Update(entidadeClasses objClasses)
        {
            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 + " DESCRICAO     = ?DESCRICAO  ";
                    vFILTER = vFILTER + ", CONTROLE  	= ?CONTROLE  ";
					vFILTER = vFILTER + ", ORD_CTA  	= ?ORD_CTA  ";
					vFILTER = vFILTER + ", IDPRODIST_MOD06_TCLA_SUBCLA  = ?IDPRODIST_MOD06_TCLA_SUBCLA  ";
					vFILTER = vFILTER + ", SITUACAO     = ?SITUACAO  ";
					vFILTER = vFILTER + ", EXTRAS  	    = ?EXTRAS  ";
					vFILTER = vFILTER + ", IDUSUARIO  	= ?IDUSUARIO  ";
					
                    vAND = vAND + "AND ID  = ?ID ";

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?DESCRICAO", objClasses.DESCRICAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTROLE", objClasses.CONTROLE.ToString().ToUpper());
					cmd.Parameters.AddWithValue("?ORD_CTA", objClasses.ORD_CTA);
					cmd.Parameters.AddWithValue("?IDPRODIST_MOD06_TCLA_SUBCLA", objClasses.IDPRODIST_MOD06_TCLA_SUBCLA);
					cmd.Parameters.AddWithValue("?SITUACAO", objClasses.SITUACAO.ToString().ToUpper());
					cmd.Parameters.AddWithValue("?EXTRAS", objClasses.EXTRAS.ToString().ToUpper());
					cmd.Parameters.AddWithValue("?IDUSUARIO", objClasses.IDUSUARIO);
					
                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select(entidadeClasses objClasses)
        {
            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 + " DESCRICAO ";
                    vFILTER = vFILTER + ", CONTROLE ";
					vFILTER = vFILTER + ", ORD_CTA  ";
					vFILTER = vFILTER + ", IDPRODIST_MOD06_TCLA_SUBCLA ";
					vFILTER = vFILTER + ", SITUACAO ";
					vFILTER = vFILTER + ", EXTRAS ";
					vFILTER = vFILTER + ", IDUSUARIO ";
					
                    vINNERJOIN = " ";

                    vORDERBY = " ORDER BY ID ";
                    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(entidadeClasses objClasses)
        {
            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 + " DESCRICAO ";
                    vFILTER = vFILTER + ", CONTROLE ";
                    vFILTER = vFILTER + ", ORD_CTA  ";
                    vFILTER = vFILTER + ", IDPRODIST_MOD06_TCLA_SUBCLA ";
                    vFILTER = vFILTER + ", SITUACAO ";
                    vFILTER = vFILTER + ", EXTRAS ";
                    vFILTER = vFILTER + ", IDUSUARIO ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND ID = ?ID ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ID", objClasses.ID);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);


                }

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

        public void Delete(entidadeClasses objClasses)
        {
            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 = vAND + "AND ID  = ?ID ";

                    cmd = new MySqlCommand(this.QueryDelete);

                    cmd.Parameters.AddWithValue("?ID", objClasses.ID);

                    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("CLASSES SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

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

                this.sb.AppendLine("UPDATE  ");
                this.sb.AppendLine("CLASSES 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 CLASSES ");
                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("CLASSES  ");
                this.sb.AppendLine(vINNERJOIN);
                this.sb.AppendLine("WHERE 1 = 1  ");
                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        #endregion


    }
}
