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

        #region Métodos

        public void Execute(entidadeTempRecbtosDiaBanco objTempRecbtosDiaBanco)
        {
            MySqlCommand cmd = null;

            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

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


                    // Cria Tabela temporaria 

                    //vFILTER = vFILTER + " CREATE TEMPORARY TABLE " + objTempRecbtosDiaBanco.TABLE + "(";
                    vFILTER = vFILTER + " CREATE  TABLE " + objTempRecbtosDiaBanco.TABLE + "(";
                    vFILTER = vFILTER + " DATAPAG    DATE          DEFAULT '0000-00-00',";
                    vFILTER = vFILTER + " BANCO      INTEGER(4)    DEFAULT NULL,";
                    vFILTER = vFILTER + " NMBANCO    VARCHAR(40)   DEFAULT NULL,";
                    vFILTER = vFILTER + " KWH_FAT    DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " CONSUMO    DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " ARREDON    DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " SOBTAXA    DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " ILUM       DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " ENCARGOS   DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " DIVERSOS   DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " CONVENIOS  DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " TAXADOS    DECIMAL(14,2) DEFAULT '0.00',";
                    vFILTER = vFILTER + " TOTAL      DECIMAL(14,2) DEFAULT '0.00'";
                    vFILTER = vFILTER + " ) ENGINE=INNODB DEFAULT CHARSET=LATIN1;";


                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

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

        public void Execute_1(entidadeTempRecbtosDiaBanco objTempRecbtosDiaBanco)
        {
            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 + " DROP TABLE IF EXISTS " + objTempRecbtosDiaBanco.TABLE + ";";
                    vFILTER = vFILTER + " DROP TEMPORARY TABLE IF EXISTS " + objTempRecbtosDiaBanco.TABLE + ";";

                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

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

        public DataTable Execute_2(entidadeTempRecbtosDiaBanco objTempRecbtosDiaBanco)
        {
            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 + objTempRecbtosDiaBanco.TABLE;

                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            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(vTABLE + " SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

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

                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        #endregion

    }
}
