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 dalTempEstatis
    {

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

        #region Métodos

        public void Execute(entidadeTempEstatis objTempEstatis)
        {
            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 das faturas
                    // vFILTER = vFILTER + "CREATE TEMPORARY TABLE " + objTempEstatis.TABLE + "(";
                    vFILTER = vFILTER + "CREATE  TABLE " + objTempEstatis.TABLE + "(";
                    vFILTER = vFILTER + "CLASSE       VARCHAR(6), "; // CLASSE
                                                                     // CONVENCIONAL
                                                                     // QTDE
                    vFILTER = vFILTER + "QKWH_CONS    DOUBLE, ";// KWH CONSUMIDO (ENERGIA CONSUMIDA)
                    vFILTER = vFILTER + "QKWH_FAT     DOUBLE, ";// KWH FATURADO
                    vFILTER = vFILTER + "QKWH_FAT_BAN DOUBLE, ";// KWH FATURADO BANDEIRA
                    vFILTER = vFILTER + "QKW_CONTR    DOUBLE, "; // KW CONTRATADO
                    vFILTER = vFILTER + "QKW_FAT      DOUBLE, ";// KW FATURADO
                                                                // VALOR
                    vFILTER = vFILTER + "VKWH_CONS    DOUBLE, ";// KWH CONSUMIDO (ENERGIA CONSUMIDA)
                    vFILTER = vFILTER + "VKWH_FAT     DOUBLE, ";// KWH FATURADO
                    vFILTER = vFILTER + "VKWH_FAT_BAN DOUBLE, ";// KWH FATURADO BANDEIRA
                    vFILTER = vFILTER + "VKW_FAT      DOUBLE, ";// KW FATURADO
                    vFILTER = vFILTER + "VCONREAT     DOUBLE, ";// REATIVO
                    vFILTER = vFILTER + "VDEMREAT     DOUBLE, "; // REATIVO
                                                                 // HOROSAZONAL
                                                                 // QTDE
                                                                 // CONSUMO
                    vFILTER = vFILTER + "QKWH_FAT_P      DOUBLE, ";// KWH FATURADO (ENERGIA FAT PONTA)
                    vFILTER = vFILTER + "QKWH_FAT_I      DOUBLE, ";// KWH FATURADO (ENERGIA FAT INTERMEDIARIO)
                    vFILTER = vFILTER + "QKWH_FAT_FP     DOUBLE, ";// KWH FATURADO (ENERGIA FAT FORA PONTA)
                    vFILTER = vFILTER + "QKWH_FAT_P_BAN  DOUBLE, "; // KWH FATURADO (ENERGIA FAT PONTA) BANDEIRA
                    vFILTER = vFILTER + "QKWH_FAT_I_BAN  DOUBLE, ";// KWH FATURADO (ENERGIA FAT INTERMEDIARIO) BANDEIRA
                    vFILTER = vFILTER + "QKWH_FAT_FP_BAN DOUBLE, ";// KWH FATURADO (ENERGIA FAT FORA PONTA) BANDEIRA
                    vFILTER = vFILTER + "QKWH_FAT_P_LIV  DOUBLE, "; // KWH FATURADO (ENERGIA FAT PONTA) LIVRE
                    vFILTER = vFILTER + "QKWH_FAT_FP_LIV DOUBLE, "; // KWH FATURADO (ENERGIA FAT FORA PONTA) LIVRE
                    vFILTER = vFILTER + "QKWH_DEV_P   DOUBLE, "; // KWH DEVOLVIDO PONTA
                    vFILTER = vFILTER + "QKWH_DEV_FP  DOUBLE, "; // KWH DEVOLVIDO FORA PONTA
                    vFILTER = vFILTER + "QKWH_UTR_P   DOUBLE, "; // KWH ULTRAPASSADO PONTA
                    vFILTER = vFILTER + "QKWH_UTR_FP  DOUBLE, ";// KWH ULTRAPASSADO FORA PONTA
                                                                // DEMANDA
                    vFILTER = vFILTER + "QKW_CONTR_P  DOUBLE, "; // KW CONTRATADO (DEMANDA CONTRATADO PONTA)
                    vFILTER = vFILTER + "QKW_CONTR_FP DOUBLE, "; // KW CONTRATADO (DEMANDA CONTRATADO FORA PONTA)
                    vFILTER = vFILTER + "QKW_FAT_P    DOUBLE, ";// KW FATURADO (DEMANDA FAT PONTA)
                    vFILTER = vFILTER + "QKW_FAT_FP   DOUBLE, ";// KW FATURADO (DEMANDA FAT FORA PONTA)
                                                                //  ARLINDO DIF. DEMANDA
                    vFILTER = vFILTER + "QKW_DIF_FAT_P    DOUBLE, "; // KW FATURADO (DIF. DEMANDA FAT PONTA)
                    vFILTER = vFILTER + "QKW_DIF_FAT_FP   DOUBLE, "; // KW FATURADO (DIF. DEMANDA FAT FORA PONTA)
                                                                     //
                    vFILTER = vFILTER + "QKW_FATU_P   DOUBLE, ";// KW FATURADO ULTRAPASSAGEM P
                    vFILTER = vFILTER + "QKW_FATU_FP  DOUBLE, ";// KW FATURADO ULTRAPASSAGEM FP
                                                                // VALOR
                                                                // CONSUMO
                    vFILTER = vFILTER + "VKWH_FAT_P      DOUBLE, "; // KWH FATURADO (ENERGIA FAT PONTA)
                    vFILTER = vFILTER + "VKWH_FAT_I      DOUBLE, "; // KWH FATURADO (ENERGIA FAT INTERMEDIARIO)
                    vFILTER = vFILTER + "VKWH_FAT_FP     DOUBLE, ";// KWH FATURADO (ENERGIA FAT FORA PONTA)
                    vFILTER = vFILTER + "VKWH_FAT_P_BAN  DOUBLE, ";// KWH FATURADO (ENERGIA FAT PONTA) BANDEIRA
                    vFILTER = vFILTER + "VKWH_FAT_I_BAN  DOUBLE, ";// KWH FATURADO (ENERGIA FAT INTERMEDIARIO) BANDEIRA
                    vFILTER = vFILTER + "VKWH_FAT_FP_BAN DOUBLE, "; // KWH FATURADO (ENERGIA FAT FORA PONTA) BANDEIRA
                    vFILTER = vFILTER + "VKWH_FAT_P_LIV  DOUBLE, "; // KWH FATURADO (ENERGIA FAT PONTA) LIVRE
                    vFILTER = vFILTER + "VKWH_FAT_FP_LIV DOUBLE, ";// KWH FATURADO (ENERGIA FAT FORA PONTA) LIVRE
                    vFILTER = vFILTER + "VKWH_DEV_P   DOUBLE, ";// KWH DEVOLVIDO (ENERGIA FAT PONTA)
                    vFILTER = vFILTER + "VKWH_DEV_FP  DOUBLE, ";// KWH DEVOLVIDO (ENERGIA FAT FORA PONTA)
                    vFILTER = vFILTER + "VKWH_UTR_P   DOUBLE, ";// KWH ULTRAPASSADO (ENERGIA FAT PONTA)
                    vFILTER = vFILTER + "VKWH_UTR_FP  DOUBLE, ";// KWH ULTRAPASSADO (ENERGIA FAT FORA PONTA)
                                                                // DEMANDA
                    vFILTER = vFILTER + "VKW_FAT_P    DOUBLE, "; // KW FATURADO (DEMANDA FAT PONTA)
                    vFILTER = vFILTER + "VKW_FAT_FP   DOUBLE, ";// KW FATURADO (DEMANDA FAT FORA PONTA)
                                                                //  ARLINDO DIF. DEMANDA
                    vFILTER = vFILTER + "VKW_DIF_FAT_P    DOUBLE, ";// KW FATURADO (DIF. DEMANDA FAT PONTA)
                    vFILTER = vFILTER + "VKW_DIF_FAT_FP   DOUBLE, "; // KW FATURADO (DIF. DEMANDA FAT FORA PONTA)
                                                                     // 
                    vFILTER = vFILTER + "VKW_FATU_P   DOUBLE, ";// KW FATURADO ULTRAPASSAGEM P
                    vFILTER = vFILTER + "VKW_FATU_FP  DOUBLE, "; // KW FATURADO ULTRAPASSAGEM FP
                                                                 // CCEE
                    vFILTER = vFILTER + "VKW_DSC_PROV DOUBLE, "; // KW - DESCONTO PROVISORIO CCEE
                    vFILTER = vFILTER + "VKW_DSC_REAL DOUBLE, ";// KW - DESCONTO REAL CCEE
                    vFILTER = vFILTER + "VKW_DEV_CCEE DOUBLE, ";// KW - DEVOLUÇÃO DA DIFEREÇA ENTRE O REAL E O PROVISÓRIO
                    vFILTER = vFILTER + "VKW_COB_CCEE DOUBLE, ";// KW - COBRANÇA DA DIFERENÇA ENTRE O REAL E O PROVISÓRIO
                    vFILTER = vFILTER + "VKWH_DSC_PROV DOUBLE, "; // KWH - DESCONTO PROVISORIO CCEE
                    vFILTER = vFILTER + "VKWH_DSC_REAL DOUBLE, ";// KWH - DESCONTO REAL CCEE
                    vFILTER = vFILTER + "VKWH_DEV_CCEE DOUBLE, ";// KWH - DEVOLUÇÃO DA DIFEREÇA ENTRE O REAL E O PROVISÓRIO
                    vFILTER = vFILTER + "VKWH_COB_CCEE DOUBLE, "; // KWH - COBRANÇA DA DIFERENÇA ENTRE O REAL E O PROVISÓRIO
                                                                  // ------ REVERSO - MICRO GERAÇÃO
                                                                  // vFILTER = vFILTER + "QKWH_REVERSO  DOUBLE, " ;// QTDE KWH REVERSO
                                                                  // vFILTER = vFILTER + "VKWH_REVERSO  DOUBLE, "; // VALLOR KWH REVERSO
                    vFILTER = vFILTER + "QKWH_REVERSOP  DOUBLE, ";// QTDE KWH REVERSO PONTA
                    vFILTER = vFILTER + "QKWH_REVERSOI  DOUBLE, ";// QTDE KWH REVERSO INTERMEDIARIO
                    vFILTER = vFILTER + "QKWH_REVERSOFP  DOUBLE, ";// QTDE KWH REVERSO FORA PONTA
                    vFILTER = vFILTER + "VKWH_REVERSOP  DOUBLE, ";// VALOR KWH REVERSO PONTA
                    vFILTER = vFILTER + "VKWH_REVERSOI  DOUBLE, ";// VALOR KWH REVERSO INTERMEDIARIO
                    vFILTER = vFILTER + "VKWH_REVERSOFP  DOUBLE, ";// VALOR KWH REVERSO FORA PONTA
                                                                   // ------ REVERSO - MICRO GERAÇÃO
                    vFILTER = vFILTER + "QKWH_REVERSOP_SEFAZ  DOUBLE, "; // QTDE KWH REVERSO PONTA
                    vFILTER = vFILTER + "QKWH_REVERSOI_SEFAZ  DOUBLE, ";// QTDE KWH REVERSO INTERMEDIARIO
                    vFILTER = vFILTER + "QKWH_REVERSOFP_SEFAZ  DOUBLE, "; // QTDE KWH REVERSO FORA PONTA
                    vFILTER = vFILTER + "VKWH_REVERSOP_SEFAZ  DOUBLE, ";// VALOR KWH REVERSO PONTA
                    vFILTER = vFILTER + "VKWH_REVERSOI_SEFAZ  DOUBLE, "; // VALOR KWH REVERSO INTERMEDIARIO
                    vFILTER = vFILTER + "VKWH_REVERSOFP_SEFAZ  DOUBLE, ";// VALOR KWH REVERSO FORA PONTA
                                                                         // REATIVOS
                    vFILTER = vFILTER + "VCONREAT_P   DOUBLE, ";// REATIVO
                    vFILTER = vFILTER + "VCONREAT_FP  DOUBLE, ";// REATIVO
                    vFILTER = vFILTER + "VDEMREAT_P   DOUBLE, ";// REATIVO
                    vFILTER = vFILTER + "VDEMREAT_FP  DOUBLE, ";// REATIVO
                                                                // ------
                    vFILTER = vFILTER + "TIP          DOUBLE, ";// CIP R$
                    vFILTER = vFILTER + "SLD_ARRED    DOUBLE, ";// SALDO ARRED. R$
                    vFILTER = vFILTER + "ENC_FINAN    DOUBLE, "; // ENCARGOS FINANCEIROS(MULTA,JUROS,CM). R$
                    vFILTER = vFILTER + "ICMS         DOUBLE, ";// ICMS R$
                    vFILTER = vFILTER + "PIS          DOUBLE, "; // PIS R$
                    vFILTER = vFILTER + "COFINS       DOUBLE, "; // COFINS R$
                                                                 // TRIBUTACAO COMERCIALIZADOR USADO PARA O LIVRE
                    vFILTER = vFILTER + "ICMS_ST      DOUBLE, ";// ICMS - ST
                    vFILTER = vFILTER + "PIS_ST       DOUBLE, ";// PIS - ST
                    vFILTER = vFILTER + "COFINS_ST    DOUBLE, ";// COFINS - ST
                                                                // *********************************************
                    vFILTER = vFILTER + "BASEICMS     DOUBLE, ";// BASE ICMS R$
                    vFILTER = vFILTER + "BASEPIS      DOUBLE, ";// BASE PIS R$
                    vFILTER = vFILTER + "BASECOFINS   DOUBLE, "; // BASE COFINS R$
                    vFILTER = vFILTER + "ICMSPISCOF   DOUBLE, "; // ICMS/PIS/COFINS R$
                    vFILTER = vFILTER + "ISENTOICMS   DOUBLE, "; // ISENTO R$
                    vFILTER = vFILTER + "DIFERIDO   DOUBLE, "; // ISENTO R$
                                                               // 
                    vFILTER = vFILTER + "DIF_ELTRBR   DOUBLE, "; // DIF.ELETR. R$
                    vFILTER = vFILTER + "JUROS        DOUBLE, ";// JUROS R$
                    vFILTER = vFILTER + "MULTA        DOUBLE, ";// MULTA R$
                    vFILTER = vFILTER + "ENCARGO      DOUBLE, ";// ENCARGO CAP EMERG R$
                    vFILTER = vFILTER + "MUST         DOUBLE, ";// MUST R$
                    vFILTER = vFILTER + "TOTAL        DOUBLE, "; // TOTAL R$
                    vFILTER = vFILTER + "TAXADOS      DOUBLE, "; // TAXAS
                    vFILTER = vFILTER + "DIVERSOS     DOUBLE, "; // DIVERSOS R$
                    vFILTER = vFILTER + "CONVENIOS    DOUBLE, "; // CONVENIOS R$\
                    vFILTER = vFILTER + "DEVOLUCOES   DOUBLE, "; // DEVOLUCOES R$
                    vFILTER = vFILTER + "DESCONTOS    DOUBLE, "; // DESCONTOS R$
                    vFILTER = vFILTER + "DESCONTOS_1  DOUBLE, "; // DESCONTOS R$
                    vFILTER = vFILTER + "RETENCAO     DOUBLE, ";// RETENCAO R$
                    vFILTER = vFILTER + "FLGMEDFAT    CHAR(1), ";
                    vFILTER = vFILTER + "FATURA_FINAL TINYINT(1), ";
                    vFILTER = vFILTER + "IRRIGACAO_AQUICULTURA TINYINT(1), ";
                    vFILTER = vFILTER + "MICRO_GERACAO TINYINT(1), ";
                    vFILTER = vFILTER + "MICRO_GERACAO_DISTRIBUIDA TINYINT(1), ";
                    vFILTER = vFILTER + "MICRO_GERACAO_CONVENIO_SEFAZ TINYINT(1), ";
                    vFILTER = vFILTER + "TARIFA_BRANCA TINYINT(1), ";
                    vFILTER = vFILTER + "VKWH_REVERSO_IMPORTE  DOUBLE, ";// QUANDO NÃO FOR SEFAZ DEVERÁ SOMAR NO IMPORTE
                    vFILTER = vFILTER + "FLGST        CHAR(1), ";
                    vFILTER = vFILTER + "FASE         CHAR(1), ";
                    vFILTER = vFILTER + "GRUPO        CHAR(4), ";
                    vFILTER = vFILTER + "ZONA         CHAR(3), ";
                    vFILTER = vFILTER + "LIVRO        CHAR(2), ";
                    vFILTER = vFILTER + "CONTA        CHAR(6), ";
                    vFILTER = vFILTER + "NOME         VARCHAR(100), ";
                    vFILTER = vFILTER + "GRUPOT       CHAR(4), ";
                    vFILTER = vFILTER + "SEQUENCIA    CHAR(1), ";
                    vFILTER = vFILTER + "MESANO       VARCHAR(7), ";
                    vFILTER = vFILTER + "MATRICULA       INT(11), ";
                    vFILTER = vFILTER + "NUMNF       DOUBLE, ";
                    vFILTER = vFILTER + "grupo_tarifario         VARCHAR(100), "; 
                    vFILTER = vFILTER + "classe_faturada         VARCHAR(100), ";
                    vFILTER = vFILTER + "descricao_classe        VARCHAR(100), ";
                    vFILTER = vFILTER + "mesano_referencia       VARCHAR(100), ";
                    vFILTER = vFILTER + "anomes_fat              VARCHAR(100), ";
                    vFILTER = vFILTER + "data_emissao            DATE          DEFAULT '0000-00-00', ";
                    vFILTER = vFILTER + "data_apresentacao       DATE          DEFAULT '0000-00-00', ";
                    vFILTER = vFILTER + "data_vencimento         DATE          DEFAULT '0000-00-00', ";
                    vFILTER = vFILTER + "data_leitura_atual      DATE          DEFAULT '0000-00-00', ";
                    vFILTER = vFILTER + "data_leitura_anterior   DATE          DEFAULT '0000-00-00', ";

                    vFILTER = vFILTER + "valor_juros_ece   DOUBLE, ";
                    vFILTER = vFILTER + "valor_diferenca_ece   DOUBLE, ";
                    vFILTER = vFILTER + "valor_ece   DOUBLE, ";
                    vFILTER = vFILTER + "valor_liquido   DOUBLE, ";

                    vFILTER = vFILTER + "qtde_fx1   DOUBLE, ";
                    vFILTER = vFILTER + "qtde_fx2   DOUBLE, ";
                    vFILTER = vFILTER + "qtde_fx3   DOUBLE, ";
                    vFILTER = vFILTER + "qtde_fx4   DOUBLE, ";
                    vFILTER = vFILTER + "vlr_fx1   DOUBLE, ";
                    vFILTER = vFILTER + "vlr_fx2   DOUBLE, ";
                    vFILTER = vFILTER + "vlr_fx3   DOUBLE, ";
                    vFILTER = vFILTER + "vlr_fx4   DOUBLE, ";

                    vFILTER = vFILTER + "kwh_lido   DOUBLE, ";

                    vFILTER = vFILTER + "base_icms_1   DOUBLE, ";
                    vFILTER = vFILTER + "aliquota_icms_1   DOUBLE, ";
                    vFILTER = vFILTER + "valor_icms_1   DOUBLE, ";
                    vFILTER = vFILTER + "base_icms_2   DOUBLE, ";
                    vFILTER = vFILTER + "aliquota_icms_2   DOUBLE, ";
                    vFILTER = vFILTER + "valor_icms_2   DOUBLE, ";
                    vFILTER = vFILTER + "Aliquota_PIS   DOUBLE, ";
                    vFILTER = vFILTER + "Aliquota_COFINS   DOUBLE, ";
                    vFILTER = vFILTER + "receitas_financeiras   DOUBLE, ";
                    vFILTER = vFILTER + "ressarcimento_idqual   DOUBLE, ";

                    vFILTER = vFILTER + "valor_encargo_uso_sis_trans   DOUBLE, ";
                    vFILTER = vFILTER + "desconto_must   DOUBLE, ";
                    vFILTER = vFILTER + "encargo_kwh_faturado_ponta   DOUBLE, ";
                    vFILTER = vFILTER + "encargo_valor_kwh_faturado_ponta   DOUBLE, ";
                    vFILTER = vFILTER + "encargo_kwh_faturado_fora_ponta   DOUBLE, ";
                    vFILTER = vFILTER + "encargo_valor_kwh_faturado_fora_ponta   DOUBLE, ";
                    vFILTER = vFILTER + "mwh_medio   DOUBLE, ";
                    vFILTER = vFILTER + "mwh_medio_horas   DOUBLE, ";

                    vFILTER = vFILTER + "VALOR_REVERSO   DOUBLE, ";
                    vFILTER = vFILTER + "VALOR_REVERSO_BAN   DOUBLE, ";
                    vFILTER = vFILTER + "REVERSO_PONTA   DOUBLE, ";
                    vFILTER = vFILTER + "REVERSO_PONTA_BANDEIRA   DOUBLE, ";
                    vFILTER = vFILTER + "REVERSO_FORA_DE_PONTA   DOUBLE, ";
                    vFILTER = vFILTER + "REVERSO_FORA_DE_PONTA_BANDEIRA   DOUBLE, ";
                    vFILTER = vFILTER + "bandeira_vlr_kwh_fat   DOUBLE, ";
                    vFILTER = vFILTER + "bandeira_valor_kwh_fat_ponta   DOUBLE, ";
                    vFILTER = vFILTER + "bandeira_valor_kwh_fat_fora_ponta   DOUBLE, ";
                    vFILTER = vFILTER + "valor_energia_desconto_provisorio   DOUBLE, ";

                    vFILTER = vFILTER + "valor_energia_desconto_realizado   DOUBLE, ";
                    vFILTER = vFILTER + "valor_energia_devolucao_diferenca_ccee   DOUBLE, ";
                    vFILTER = vFILTER + "valor_energia_cobranca_diferenca_ccee   DOUBLE, ";
                    vFILTER = vFILTER + "CONSUMO_PONTA   DOUBLE, ";
                    vFILTER = vFILTER + "CONSUMO_FORA_PONTA   DOUBLE, ";

                    vFILTER = vFILTER + "REVERSO_FORA_PONTA   DOUBLE, ";
                    vFILTER = vFILTER + "REVERSO_RESIDUO_FORA_PONTA   DOUBLE, ";
                    vFILTER = vFILTER + "TUSD   DOUBLE, ";
                    vFILTER = vFILTER + "CONSUMO_MEDIDO   DOUBLE, ";
                    vFILTER = vFILTER + "REVERSO_LIDO   DOUBLE, ";

                    vFILTER = vFILTER + "datast   DATE          DEFAULT '0000-00-00', ";
                    vFILTER = vFILTER + "data_pagamento   DATE          DEFAULT '0000-00-00', ";

                    vFILTER = vFILTER + "kwh_faturado_Ponta   DOUBLE, ";
                    vFILTER = vFILTER + "kwh_faturado_Intermediaria   DOUBLE, ";
                    vFILTER = vFILTER + "kwh_faturado_Fora_Ponta   DOUBLE, ";
                    vFILTER = vFILTER + "valor_dif_dem_contr   DOUBLE, ";

                    vFILTER = vFILTER + "KEY `KEYFLGMEDFAT`  (`FLGMEDFAT`), ";
                    vFILTER = vFILTER + "KEY `KEYFATURAFINAL`  (`FATURA_FINAL`), ";
                    vFILTER = vFILTER + "KEY `KEYFLGST`  (`FLGST`), ";
                    vFILTER = vFILTER + "KEY `KEYCLASSE`  (`CLASSE`), ";
                    vFILTER = vFILTER + "KEY `KEYKHWCONS` (`QKWH_CONS`))";


                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

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

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

                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select(entidadeTempEstatis objTempEstatis)
        {
            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 + "  	      IF(QKWH_CONS<0,0,1)  AS QKWH_CONS, ";
                    vFILTER = vFILTER + "         QKWH_CONS   AS QKWH_CONS, ";
                    vFILTER = vFILTER + "         QKWH_FAT    AS QKWH_FAT, ";
                    vFILTER = vFILTER + "         QKW_FAT     AS QKW_FAT, ";
                    vFILTER = vFILTER + "         QKW_CONTR   AS QKW_CONTR, ";
                    vFILTER = vFILTER + "         VKWH_CONS   AS VKWH_CONS, ";
                    vFILTER = vFILTER + "         VKWH_FAT    AS VKWH_FAT, ";
                    vFILTER = vFILTER + "         VKW_FAT     AS VKW_FAT, ";
                    vFILTER = vFILTER + "         VCONREAT    AS VCONREAT, ";
                    vFILTER = vFILTER + "         VDEMREAT    AS VDEMREAT, ";
                    vFILTER = vFILTER + "         QKWH_FAT_P  AS QKWH_FAT_P, ";
                    vFILTER = vFILTER + "         QKWH_FAT_FP AS QKWH_FAT_FP, ";
                    vFILTER = vFILTER + "         QKWH_DEV_P  AS QKWH_DEV_P, ";
                    vFILTER = vFILTER + "         QKWH_DEV_FP   AS QKWH_DEV_FP, ";
                    vFILTER = vFILTER + "         QKWH_UTR_P    AS QKWH_UTR_P, ";
                    vFILTER = vFILTER + "         QKWH_UTR_FP   AS QKWH_UTR_FP, ";
                    vFILTER = vFILTER + "         QKW_FAT_P     AS QKW_FAT_P, ";
                    vFILTER = vFILTER + "         QKW_FAT_FP    AS QKW_FAT_FP, ";
                    vFILTER = vFILTER + "         QKW_FATU_P    AS QKW_FATU_P, ";
                    vFILTER = vFILTER + "         QKW_FATU_FP   AS QKW_FATU_FP, ";
                    vFILTER = vFILTER + "         QKW_CONTR_P   AS QKW_CONTR_P, ";
                    vFILTER = vFILTER + "         QKW_CONTR_FP  AS QKW_CONTR_FP, ";
                    vFILTER = vFILTER + "         VKWH_FAT_P    AS VKWH_FAT_P, ";
                    vFILTER = vFILTER + "         VKWH_FAT_FP   AS VKWH_FAT_FP, ";
                    vFILTER = vFILTER + "         VKWH_DEV_P    AS VKWH_DEV_P, ";
                    vFILTER = vFILTER + "         VKWH_DEV_FP   AS VKWH_DEV_FP, ";
                    vFILTER = vFILTER + "         VKWH_UTR_P    AS VKWH_UTR_P, ";
                    vFILTER = vFILTER + "         VKWH_UTR_FP   AS VKWH_UTR_FP, ";
                    vFILTER = vFILTER + "         VKW_FAT_P     AS VKW_FAT_P, ";
                    vFILTER = vFILTER + "         VKW_FAT_FP    AS VKW_FAT_FP, ";
                    vFILTER = vFILTER + "         VKW_FATU_P    AS VKW_FATU_P, ";
                    vFILTER = vFILTER + "         VKW_FATU_FP   AS VKW_FATU_FP, ";
                    vFILTER = vFILTER + "         VKW_DSC_PROV   AS VKW_DSC_PROV, ";
                    vFILTER = vFILTER + "         VKW_DSC_REAL   AS VKW_DSC_REAL, ";
                    vFILTER = vFILTER + "         VKW_DEV_CCEE   AS VKW_DEV_CCEE, ";
                    vFILTER = vFILTER + "         VKW_COB_CCEE   AS VKW_COB_CCEE, ";
                    vFILTER = vFILTER + "         VKWH_DSC_PROV   AS VKWH_DSC_PROV, ";
                    vFILTER = vFILTER + "         VKWH_DSC_REAL   AS VKWH_DSC_REAL, ";
                    vFILTER = vFILTER + "         VKWH_DEV_CCEE   AS VKWH_DEV_CCEE, ";
                    vFILTER = vFILTER + "         VKWH_COB_CCEE   AS VKWH_COB_CCEE, ";
                    vFILTER = vFILTER + "         VCONREAT_P     AS VCONREAT_P, ";
                    vFILTER = vFILTER + "         VCONREAT_FP    AS VCONREAT_FP, ";
                    vFILTER = vFILTER + "         VDEMREAT_P     AS VDEMREAT_P, ";
                    vFILTER = vFILTER + "         VDEMREAT_FP    AS VDEMREAT_FP, ";
                    vFILTER = vFILTER + "         TIP         AS TIP, ";
                    vFILTER = vFILTER + "         SLD_ARRED   AS SLD_ARRED, ";
                    vFILTER = vFILTER + "         ENC_FINAN   AS ENC_FINAN, ";
                    vFILTER = vFILTER + "         ICMS         AS ICMS, ";
                    vFILTER = vFILTER + "         PIS          AS PIS, ";
                    vFILTER = vFILTER + "         COFINS       AS COFINS, ";
                    vFILTER = vFILTER + "         BASEICMS     AS BASEICMS, ";
                    vFILTER = vFILTER + "         BASEPIS      AS BASEPIS, ";
                    vFILTER = vFILTER + "         BASECOFINS   AS BASECOFINS, ";
                    vFILTER = vFILTER + "         ICMSPISCOF   AS ICMSPISCOF, ";
                    vFILTER = vFILTER + "         DIF_ELTRBR   AS DIF_ELTRBR, ";
                    vFILTER = vFILTER + "         JUROS        AS JUROS, ";
                    vFILTER = vFILTER + "         ENCARGO      AS ENCARGO, ";
                    vFILTER = vFILTER + "         MUST         AS MUST, ";
                    vFILTER = vFILTER + "         CONVENIOS    AS CONVENIOS, ";
                    vFILTER = vFILTER + "         DESCONTOS    AS DESCONTOS, ";
                    vFILTER = vFILTER + "         RETENCAO     AS RETENCAO, ";
                    vFILTER = vFILTER + "         DIVERSOS     AS DIVERSOS, ";
                    vFILTER = vFILTER + "         TAXADOS      AS TAXADOS, ";
                    vFILTER = vFILTER + "         TOTAL        AS TOTAL, ";
                    vFILTER = vFILTER + "         ZONA         AS ZONA, ";
                    vFILTER = vFILTER + "         LIVRO        AS LIVRO, ";
                    vFILTER = vFILTER + "         CONTA        AS CONTA, ";
                    vFILTER = vFILTER + "         NOME         AS NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vTABLE = objTempEstatis.TABLE;

                    vAND = vAND + "AND GRUPO = ?GRUPO ";
                    vAND = vAND + "AND  " + objTempEstatis.CLASSE + " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                    cmd.Parameters.AddWithValue("?CLASSE", objTempEstatis.CLASSE);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_1(entidadeTempEstatis objTempEstatis)
        {
            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 + "  	      IF(TIP <> 0, 'NORMAL', 'ISENTO')  AS GRUPO, ";
                    vFILTER = vFILTER + "         COUNT(*)   AS TARIFA_BRANCA ";


                    vINNERJOIN = " ";

                    vORDERBY = " GROUP BY 1 DESC ";

                    vTABLE = objTempEstatis.TABLE;

                    if (objTempEstatis.GRUPO != "AB")
                    {
                        if (objTempEstatis.GRUPO == "AL")
                        {

                            vAND = vAND + "   AND LEFT(GRUPO, 2) =  ?GRUPO ";
                        }
                        else if (objTempEstatis.GRUPO == "A")
                        {
                            vAND = vAND + "   AND LEFT(GRUPO, 1) = ?GRUPO AND LEFT(GRUPO, 2) <> 'AL' ";
                        }
                        else
                        {
                            vAND = vAND + "   AND LEFT(GRUPO, 1) = ?GRUPO ";
                        }


                    }



                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTempEstatis.GRUPO != "AB")
                    {
                        if (objTempEstatis.GRUPO == "AL")
                        {

                            cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                        }
                        else if (objTempEstatis.GRUPO == "A")
                        {
                            cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                        }


                    }


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_2(entidadeTempEstatis objTempEstatis)
        {
            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 + "  	      SUM(IF(FLGMEDFAT='S',1,0)) FLGMEDFAT, SUM(IF(FLGST='C',1,0)) FLGST, SUM(FATURA_FINAL) FATURA_FINAL ";


                    vINNERJOIN = " ";

                    vORDERBY = "  ";

                    vTABLE = objTempEstatis.TABLE;

                    vAND = vAND + "   AND(FLGMEDFAT = 'S' OR FLGST = 'C' OR FATURA_FINAL = 1) ";

                    if (objTempEstatis.GRUPO != "AB")
                    {
                        if (objTempEstatis.GRUPO == "AL")
                        {

                            vAND = vAND + "   AND LEFT(GRUPO, 2) =  ?GRUPO ";
                        }
                        else if (objTempEstatis.GRUPO == "A")
                        {
                            vAND = vAND + "   AND LEFT(GRUPO, 1) = ?GRUPO AND LEFT(GRUPO, 2) <> 'AL' ";
                        }
                        else
                        {
                            vAND = vAND + "   AND LEFT(GRUPO, 1) = ?GRUPO ";
                        }


                    }



                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTempEstatis.GRUPO != "AB")
                    {
                        if (objTempEstatis.GRUPO == "AL")
                        {

                            cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                        }
                        else if (objTempEstatis.GRUPO == "A")
                        {
                            cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("?GRUPO", objTempEstatis.GRUPO);
                        }


                    }


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_3(entidadeTempEstatis objTempEstatis)
        {
            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 + "  	      IF(TIP <> 0, 'NORMAL', 'ISENTO')  AS GRUPO, ";
                    vFILTER = vFILTER + "         COUNT(*)   AS TARIFA_BRANCA ";


                    vINNERJOIN = " ";

                    vORDERBY = " GROUP BY 1 DESC ";

                    vTABLE = objTempEstatis.TABLE;

                    cmd = new MySqlCommand(this.QuerySelect);
                 
                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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


        public DataTable Select_4(entidadeTempEstatis objTempEstatis)
        {
            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 + "  	      codigo AS Codigo, ";
                    vFILTER = vFILTER + "  	      nome AS Nome ";
                    //vFILTER = vFILTER + "         COUNT(*)   AS TARIFA_BRANCA ";

                    vAND = vAND + "   AND MID(codigo,1,4) =  9080 ";
                    vAND = vAND + "   AND codigo <>  9080000098 ";

                    vINNERJOIN = " ";

                    vORDERBY = " GROUP BY codigo ORDER BY codigo ASC ";

                    vTABLE = objTempEstatis.TABLE;

                   
                    cmd = new MySqlCommand(this.QuerySelect);

                    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

    }
}
