using System;
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 dalTempItemFaturaB
    {

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

        #region Métodos

        public DataTable SelectCount(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            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 = "LIMIT 0, 1 ";

                    vAND = vAND + "   AND NUMNF  = ?NUMNF ";
                    vAND = vAND + "   AND MESANO = ?MESANO ";
                    vAND = vAND + "   AND ANOMES_FAT = ?ANOMES_FAT ";


                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?NUMNF", objTempItemFaturaB.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objTempItemFaturaB.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ANOMES_FAT", objTempItemFaturaB.ANOMES_FAT.ToString().ToUpper());


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Execute(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            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))
                {

                    vORDERBY = " ";
                    vAND = " ";
                    vAND = vAND + "  DELETE FROM `TEMP_ITEM_FATURA_B` WHERE `NUMNF` = " + objTempItemFaturaB.NUMNF + " AND `MESANO` = '" + objTempItemFaturaB.MESANO.ToString().ToUpper() + "' AND `ANOMES_FAT` = " + objTempItemFaturaB.ANOMES_FAT.ToString().ToUpper() + "; ";
                    vAND = vAND + "  ALTER TABLE `TEMP_ITEM_FATURA_B`AUTO_INCREMENT = 1; ";
                    vAND = vAND + "  CALL CRIAITENSFATURATRANS3(" + objTempItemFaturaB.NUMNF + ",'" + objTempItemFaturaB.MESANO.ToString().ToUpper() + "'," + objTempItemFaturaB.ANOMES_FAT.ToString().ToUpper() + "); ";
              
                    cmd = new MySqlCommand(this.QueryExecute);
                    cmd.CommandTimeout = 3600;

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            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 + "COD, ";
                    vFILTER = vFILTER + "NUMNF, ";
                    vFILTER = vFILTER + "DESCR, ";
                    vFILTER = vFILTER + "VALOR_ITEM, ";
                    vFILTER = vFILTER + "CODITEM, ";
                    vFILTER = vFILTER + "TA, ";
                    vFILTER = vFILTER + "REF, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "CLASSE, ";
                    vFILTER = vFILTER + "ANOMES_FAT, ";
                    vFILTER = vFILTER + "UN ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND UN = 'R$'";
                    vAND = vAND + "AND MESANO = ?MESANO ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?NUMNF", objTempItemFaturaB.NUMNF.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO", objTempItemFaturaB.MESANO.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);

                }

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

        public DataTable Select_1(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            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 + "COD, ";
                    vFILTER = vFILTER + "NUMNF, ";
                    vFILTER = vFILTER + "DESCR, ";
                    vFILTER = vFILTER + "VALOR_ITEM, ";
                    vFILTER = vFILTER + "CODITEM, ";
                    vFILTER = vFILTER + "TA, ";
                    vFILTER = vFILTER + "REF, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "CLASSE, ";
                    vFILTER = vFILTER + "ANOMES_FAT ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";
                    vAND = vAND + "AND ANOMES_FAT = ?ANOMES_FAT ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?NUMNF", objTempItemFaturaB.NUMNF.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO", objTempItemFaturaB.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ANOMES_FAT", objTempItemFaturaB.ANOMES_FAT.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);

                }

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

        public DataTable Select_2(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fatwin_BD);

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

                    vFILTER = vFILTER + "COD, ";
                    vFILTER = vFILTER + "NUMNF, ";
                    vFILTER = vFILTER + "DESCR, ";
                    vFILTER = vFILTER + "VALOR_ITEM, ";
                    vFILTER = vFILTER + "CODITEM, ";
                    vFILTER = vFILTER + "TA, ";
                    vFILTER = vFILTER + "REF, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "CLASSE, ";
                    vFILTER = vFILTER + "ANOMES_FAT, ";
                    vFILTER = vFILTER + "UN ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND UN = 'R$'";
                    vAND = vAND + "AND MESANO = ?MESANO ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?NUMNF", objTempItemFaturaB.NUMNF.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO", objTempItemFaturaB.MESANO.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);

                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }
        public void Delete(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            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 NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";

                    cmd = new MySqlCommand(this.QueryDelete);

                    cmd.Parameters.AddWithValue("?NUMNF", objTempItemFaturaB.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objTempItemFaturaB.MESANO.ToString());

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

        public void Delete_1(entidadeTempItemFaturaB objTempItemFaturaB)
        {
            MySqlCommand cmd = null;

            IGerenciadorTransacao objGerenciadorTransacao = null;

            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fatwin_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vORDERBY = " ";

                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";

                    cmd = new MySqlCommand(this.QueryDelete);

                    cmd.Parameters.AddWithValue("?NUMNF", objTempItemFaturaB.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objTempItemFaturaB.MESANO.ToString());

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

                return sb.ToString();
            }
        }

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

                this.sb.AppendLine("UPDATE  ");
                this.sb.AppendLine("TEMP_ITEM_FATURA_B 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 TEMP_ITEM_FATURA_B ");
                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("TEMP_ITEM_FATURA_B  ");
                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


    }
}
