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

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

        #region Métodos

        public void Update(entidadeConcorte objConcorte)
        {
            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 + "  SIT     = ?SIT ";
                    vAND = vAND + "   AND NUMNF  = ?NUMNF";
                    //vAND = vAND + "   AND MESANO = ?MESANO";

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?SIT", objConcorte.SIT.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMNF", objConcorte.NUMNF);
                    //cmd.Parameters.AddWithValue("?MESANO", objConcorte.MESANO.ToString().ToUpper());



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

        public void Update_1(entidadeConcorte objConcorte)
        {
            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 + "    PRAZOREAV = ?PRAZOREAV , ";
                    vFILTER = vFILTER + "    IMPRESSAO = 1 ";

                    vAND = vAND + "   AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + "   AND UNID_ID = ?UNID_ID ";
                    vAND = vAND + "   AND ZONA    = ?ZONA ";
                    vAND = vAND + "   AND LIVRO   = ?LIVRO ";
                    vAND = vAND + "   AND CONTA   = ?CONTA ";
                    vAND = vAND + "   AND SIT     = 'R' ";
                    vAND = vAND + "   AND MESANO  = ?MESANO ";
                    vAND = vAND + "   AND NUMNF   = ? NUMNF ";

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?PRAZOREAV", objConcorte.PRAZOREAV);
                    cmd.Parameters.AddWithValue("?CONS_ID", objConcorte.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objConcorte.UNID_ID);
                    cmd.Parameters.AddWithValue("?ZONA", objConcorte.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objConcorte.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objConcorte.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO", objConcorte.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMNF", objConcorte.NUMNF);


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

        public void Update_2(entidadeConcorte objConcorte)
        {
            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 + "";
                    vFILTER = vFILTER + " IMPRESSAO = ?IMPRESSAO , ";
                    vFILTER = vFILTER + " FAT_NUMNF = ?FAT_NUMNF , ";
                    vFILTER = vFILTER + " FAT_ANOMES_FAT = ?FAT_ANOMES_FAT ";


                    vAND = vAND + "   AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + "   AND UNID_ID = ?UNID_ID ";
                    vAND = vAND + "   AND ZONA    = ?ZONA ";
                    vAND = vAND + "   AND LIVRO   = ?LIVRO ";
                    vAND = vAND + "   AND CONTA   = ?CONTA ";
                    vAND = vAND + "   AND SIT     = ?SIT ";
                    //vAND = vAND + "   AND MESANO  = ?MESANO ";

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?IMPRESSAO", objConcorte.IMPRESSAO);
                    cmd.Parameters.AddWithValue("?FAT_NUMNF", objConcorte.FAT_NUMNF);
                    cmd.Parameters.AddWithValue("?FAT_ANOMES_FAT", objConcorte.FAT_ANOMES_FAT);
                    cmd.Parameters.AddWithValue("?CONS_ID", objConcorte.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objConcorte.UNID_ID);
                    cmd.Parameters.AddWithValue("?ZONA", objConcorte.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objConcorte.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objConcorte.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?SIT", objConcorte.SIT.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?MESANO", objConcorte.MESANO.ToString().ToUpper());


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

        public DataTable Select(entidadeConcorte objConcorte)
        {
            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 + "  ZONA, LIVRO, CONTA, UNID_ID, CONS_ID, MESANO, NOME, NUMNF, VALOR, PRAZOREAV, REGNUM, SIT, DATAVENC ";

                    vINNERJOIN = " ";

                    vORDERBY = vORDERBY + "ORDER BY DATAREAV DESC LIMIT 0, 1 ";

                    vAND = vAND + "     AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + "     AND UNID_ID = ?UNID_ID ";
                    vAND = vAND + "     AND ZONA  = ?ZONA ";
                    vAND = vAND + "     AND LIVRO = ?LIVRO ";
                    vAND = vAND + "     AND CONTA = ?CONTA ";
                    vAND = vAND + "     AND FAT_NUMNF = ?FAT_NUMNF ";
                    vAND = vAND + "     AND FAT_ANOMES_FAT = ?FAT_ANOMES_FAT ";
                    vAND = vAND + "     AND SIT   IN( 'R','E') ";


                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?CONS_ID", objConcorte.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objConcorte.UNID_ID);
                    cmd.Parameters.AddWithValue("?ZONA", objConcorte.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objConcorte.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objConcorte.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?FAT_NUMNF", objConcorte.FAT_NUMNF);
                    cmd.Parameters.AddWithValue("?FAT_ANOMES_FAT", objConcorte.FAT_ANOMES_FAT);


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectCount(entidadeConcorte objConcorte)
        {
            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 + "COUNT(*) ";

                    vINNERJOIN = " ";

                    vORDERBY = vORDERBY + " ";

                    vAND = vAND + "     AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + "     AND UNID_ID = ?UNID_ID ";
                    vAND = vAND + "     AND ZONA  = ?ZONA ";
                    vAND = vAND + "     AND LIVRO = ?LIVRO ";
                    vAND = vAND + "     AND CONTA = ?CONTA ";
                    vAND = vAND + "     AND SIT   = ?SIT ";
                    //vAND = vAND + "     AND MESANO  = ?MESANO ";


                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?CONS_ID", objConcorte.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objConcorte.UNID_ID);
                    cmd.Parameters.AddWithValue("?ZONA", objConcorte.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objConcorte.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objConcorte.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?SIT", objConcorte.SIT.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?MESANO", objConcorte.MESANO.ToString().ToUpper());

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

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion


    }
}
