using Clients_Management.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace Clients_Management.Controller
{
    public class cclavesgestioncartagantt
    {
        public mclavesgestioncartagantt Retornarclavesgestioncartagantt(string id)
        {
            clsConexao banco = new clsConexao();

            //ALTERAR NOME DA TABELA, COLOCAR A LETRA m ANTES DO NOME ONDE POSSUI
            mclavesgestioncartagantt clavesgestioncartagantt = new mclavesgestioncartagantt();

            DataSet ds = null;

            if (id == "0")
                return null;

            //ALTEREAR NOME DA TABELA
            ds = banco.Selecionar("select * from clavesgestioncartagantt where id=" + id);

            //SEGUIR O MODELO NOME DA TABELA . CAMPO
            //EX: TABELAclavesgestioncartagantt.NOME
            clavesgestioncartagantt.Id = Convert.ToInt32(id);
            clavesgestioncartagantt.ano = ds.Tables[0].Rows[0]["ano"].ToString();
            clavesgestioncartagantt.mes = ds.Tables[0].Rows[0]["mes"].ToString();
            clavesgestioncartagantt.clave = ds.Tables[0].Rows[0]["clave"].ToString();
            clavesgestioncartagantt.fecha = ds.Tables[0].Rows[0]["fecha"].ToString();

            return clavesgestioncartagantt;
        }

        public List<mclavesgestioncartagantt> Retornarclavesgestioncartagantt()
        {
            clsConexao banco = new clsConexao();
            mclavesgestioncartagantt clavesgestioncartagantt = new mclavesgestioncartagantt();
            List<mclavesgestioncartagantt> lstclavesgestioncartagantt = null;
            DataSet ds = null;

            ds = banco.Selecionar("select * from clavesgestioncartagantt ");

            if (ds.Tables[0].Rows.Count > 0)
            {
                lstclavesgestioncartagantt = new List<mclavesgestioncartagantt>();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    //mesma coisa da função de cima
                    clavesgestioncartagantt = new mclavesgestioncartagantt();
                    clavesgestioncartagantt.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"].ToString());
                    clavesgestioncartagantt.clave = ds.Tables[0].Rows[i]["clave"].ToString();
                    clavesgestioncartagantt.fecha = ds.Tables[0].Rows[i]["fecha"].ToString();
                    clavesgestioncartagantt.ano = ds.Tables[0].Rows[i]["ano"].ToString();
                    clavesgestioncartagantt.mes = ds.Tables[0].Rows[i]["mes"].ToString();

                    lstclavesgestioncartagantt.Add(clavesgestioncartagantt);
                }
            }

            return lstclavesgestioncartagantt;
        }

        public string Inserir(mclavesgestioncartagantt clavesgestioncartagantt)
        {

            clsConexao banco = new clsConexao();

            banco.CreateUpdateDelete("Insert into clavesgestioncartagantt("
                                                + "ano"
                                                + ",mes"
                                                + ",clave"
                                                + ",fecha"
                                                + ") values('"
                                                + clavesgestioncartagantt.ano + "'"
                                                + ",'" + clavesgestioncartagantt.mes + "'"
                                                + ",'" + clavesgestioncartagantt.clave + "'"
                                                + ",'" + clavesgestioncartagantt.fecha + "'"
                                                + ")");

            return banco.Selecionar("select  * from clavesgestioncartagantt order by id desc limit 1").Tables[0].Rows[0]["id"].ToString();

        }

        public bool Update(mclavesgestioncartagantt clavesgestioncartagantt)
        {
            clsConexao banco = new clsConexao();
            return banco.CreateUpdateDelete("update clavesgestioncartagantt set "
                                                + " ano='" + clavesgestioncartagantt.ano + "'"
                                                + " ,mes='" + clavesgestioncartagantt.mes + "'"
                                                + " ,clave='" + clavesgestioncartagantt.clave + "'"
                                                + " ,fecha='" + clavesgestioncartagantt.fecha + "'"
                                                + " where id=" + clavesgestioncartagantt.Id.ToString());
        }
        public bool Deletar(mclavesgestioncartagantt clavesgestioncartagantt)
        {
            clsConexao banco = new clsConexao();
            return banco.CreateUpdateDelete("delete from clavesgestioncartagantt  "
                                                + " where id=" + clavesgestioncartagantt.Id.ToString());
        }
        public DataSet PopularGrid(int limite)
        {
            clsConexao banco = new clsConexao();
            DataSet dsConsulta = new DataSet();
            string _sql = string.Empty;

            if (limite > 0)
            {
                _sql = "select mv.id"
                    + " ,mv.ano"
                    + " ,mv.mes"
                        + " ,mv.clave"
                        + " ,mv.fecha"
                        + " from"
                        + " clavesgestioncartagantt mv"
                         + " order by mv.id desc limit " + limite.ToString();
            }
            else
            {
                _sql = "select mv.id"
     + " ,mv.ano"
     + " ,mv.mes"
         + " ,mv.clave"
         + " ,mv.fecha"
         + " from"
         + " clavesgestioncartagantt mv"
                         + " order by mv.id desc";
            }

            dsConsulta = banco.Selecionar(_sql);

            return dsConsulta;
        }

        public DataSet PopularGridAnoMes(string ano, string mes)
        {
            clsConexao banco = new clsConexao();
            DataSet dsConsulta = new DataSet();
            string _sql = string.Empty;



            _sql = "select mv.id"
                + " ,mv.ano"
                + " ,mv.mes"
                + " ,mv.clave"
                + " ,mv.fecha"
                + " from"
                + " clavesgestioncartagantt mv"
                + " where mv.ano='" + ano + "' and mes='" + mes + "'"
                + " order by mv.id desc";


            dsConsulta = banco.Selecionar(_sql);

            return dsConsulta;
        }
    }
}