using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CKUERA
{
    public partial class Compras : Form
    {
        public Compras()
        {
            InitializeComponent();
        }

        private void Compras_Load(object sender, EventArgs e)
        {

        }

        private void Sumar()
        {
            try
            {
                //using (MySqlConnection Conex = new MySqlConnection(string.Format("Server={0};Database={1};Uid={2};Pwd={3};Port={4};", Global._host, Global._base, Global._usuario, Global._pass, Global._port)))
                {
                    //Conex.Open();

                    SQLiteDataReader RS = null;
                    string SQL = "SELECT SUM(cant*compra) TOTAL FROM compras_tem WHERE USUARIO='" + Global.USUARIO_SIST + "';";
                    SQLiteCommand CMD3 = new SQLiteCommand(SQL, Global.conexion);
                    RS = CMD3.ExecuteReader();
                    RS.Read();

                    //CON DECIMALES
                    //Text = string.Format("{0:#,##0.##}", double.Parse(RS.GetString(0)));

                    //SIN DECIMALES
                    Text = string.Format("{0:#,##0}", double.Parse(RS.GetString(0)));

                    //Conex.Close();
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Hay un error en la Suma");
            }
        }

        public void MostrarGridCompra()
        {
            DataG1.Rows.Clear();
            //using (MySqlConnection Conex = new MySqlConnection(string.Format("Server={0};Database={1};Uid={2};Pwd={3};Port={4};", Global._host, Global._base, Global._usuario, Global._pass, Global._port)))
            {
                //Conex.Open();

                //MOSTRAR TEMPORAL EN DATAGRID DE VENTA
                SQLiteDataReader RS = null;
                string SQLt = "SELECT id, codigo, cant, descripcion, compra, compra*cant monto FROM compras_tem WHERE USUARIO='" + Global.USUARIO_SIST + "';";
                SQLiteCommand CMD = new SQLiteCommand(SQLt, Global.conexion);
                RS = CMD.ExecuteReader();

                int items = 0;
                while (RS.Read())
                {
                    //CARGA EL DATAGRID
                    //**SI ES CON DECIMALES
                    //string montox = string.Format("{0:0.##}", double.Parse(RS["monto"].ToString()));
                    //string preciox= string.Format("{0:0.##}", double.Parse(RS["venta"].ToString()));

                    //SIN DECIMALE
                    string montox = string.Format("{0:0}", double.Parse(RS["monto"].ToString()));
                    string preciox = string.Format("{0:0}", double.Parse(RS["compra"].ToString()));
                    DataG1.Rows.Add(RS.GetString(0), RS.GetString(1), RS.GetString(2), RS.GetString(3), preciox, montox);//RS.GetString(5));

                    //NÚMERO CANTIDAD DE ITEM'S
                    items += +1;
                }

                //MUESTRA LA CANTIDAD DE ITEMS
                items_Label.Text = items.ToString() + " ítems.";

                //Conex.Close();
            }

            DataG1.Columns[0].Width = 0;
            DataG1.Columns[1].Width = 90;
            DataG1.Columns[2].Width = 50;
            DataG1.Columns[3].Width = 500;
            DataG1.Columns[4].Width = 60;
            DataG1.Columns[5].Width = 60;

            DataG1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            DataG1.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            DataG1.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            DataG1.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

            DataG1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            DataG1.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            DataG1.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            DataG1.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            Sumar();
        }

        private void Listar_Click(object sender, EventArgs e)
        {
           // using MySqlConnection Conex = new MySqlConnection(string.Format("Server={0};Database={1};Uid={2};Pwd={3};Port={4};", Global._host, Global._base, Global._usuario, Global._pass, Global._port));
            //Conex.Open();

            SQLiteDataReader RS = null;
            string SQL = "SELECT id, descripcion, codigo, compra, prov, " +
                "iva FROM productos WHERE CODIGO='" + Codigo.Text + "' LIMIT 1;";
            SQLiteCommand CMD1 = new SQLiteCommand(SQL, Global.conexion);
            RS = CMD1.ExecuteReader();

            //LLENA LOS DATOS A CARGAR EN TEMPORAL
            if (RS.HasRows == true)
            {
                while (RS.Read())
                ID_LABEL.Text = RS["ID"].ToString();
                Descrip.Text = RS["DESCRIPCION"].ToString().ToUpper();
                Codigo.Text = RS["CODIGO"].ToString();

                //SI ES CON DECIMAL
                //Precio.Text = string.Format("{0:0.##}", double.Parse(RS["COMPRA"].ToString()));

                //SIN DECIMAL
                Precio.Text = string.Format("{0:0}", double.Parse(RS["compra"].ToString()));

                Proveedor.Text = RS["PROV"].ToString();
                int IVAX = int.Parse(RS["IVA"].ToString());
                RS.Close();

                //SI CANTIDAD ES VACÍO SE DETIENE
                if (Cant.Text == "" | Cant.Text == "0")
                {
                    Cant.Select();
                    //Conex.Close();//CIERRA LA CONEXIÓN
                    return;
                }

                //CONSULTA SI YA EXISTE EN TEMPORAL Y EDITA, O DEJA CARGAR NUEVO
                SQLiteDataReader RS2 = null;
                string SQLt = "SELECT id, codigo FROM compras_tem WHERE CODIGO='" + Codigo.Text + "' AND USUARIO='" + Global.USUARIO_SIST + "' LIMIT 1;";
                SQLiteCommand CMD2 = new SQLiteCommand(SQLt, Global.conexion);
                RS2 = CMD2.ExecuteReader();

                if (RS2.HasRows == true)
                {
                    RS2.Close();
                    string SQLup = "UPDATE compras_tem SET CANT= CANT + '" + double.Parse(Cant.Text) + "' WHERE CODIGO='" + Codigo.Text + "' AND USUARIO='" + Global.USUARIO_SIST + "';";
                    SQLiteCommand CMD3 = new SQLiteCommand(SQLup, Global.conexion);
                    CMD3.ExecuteNonQuery();
                    //Limpiar();
                    MostrarGridCompra();
                    Sumar();
                    Codigo.Select();
                    //Conex.Close();//CIERRA LA CONEXIÓN
                    return;
                }

                RS2.Close();

                //INSERTA EN TEMPORAL
                string SQLin = "INSERT INTO compras_tem (" +
                    "codigo, descripcion, compra, prov, ruc, timbrado, cant, iva, usuario) " +
                    "VALUES ('" + Codigo.Text + "', " +
                    "'" + Descrip.Text + "', " +
                    "'" + Precio.Text + "', " +
                    "'" + Proveedor.Text + "', " +
                    "'" + RUC.Text + "', " +
                    "'" + Timbrado.Text + "', " +
                    "'" + Cant.Text + "', " +
                    "'" + IVAX + "'," +
                    "'" + Global.USUARIO_SIST + "');";

                SQLiteCommand CMD4 = new SQLiteCommand(SQLin, Global.conexion);
                CMD4.ExecuteNonQuery();

                //Limpiar();
                MostrarGridCompra();
                Sumar();
                Codigo.Select();
            }
            else
            {
                MessageBox.Show("Este Producto no está registrado.", "NO EXISTE", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                Codigo.Text = "";
                Codigo.Select();
            }

            //Conex.Close();//CERRAR LA CONEXIÓN
        }

        private void Limpiar_Click(object sender, EventArgs e)
        {

        }
    }
}