using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace BazaZamowien.Classes
{
    class ZamowieniaUtils
    {

        public static object ExecuteQuery(string query)
        {
            SqlConnection conn = new SqlConnection(ConnString.getConnString().ZamowieniaConnStr);

            SqlCommand command = new SqlCommand(query);
            command.Connection = conn;

            object result = null;

            conn.Open();
            result = command.ExecuteScalar();
            conn.Close();

            return result;
            
        }

        public static int IDENowegoZamowienia()
        {
            string query = "select top 1 IDE from dbo.Zamowienia order by IDE desc";
            return (int)(ExecuteQuery(query) ?? 0) + 1;
        }

        public static int IDENowejRealizacji()
        {
            string query = "select top 1 IDE from dbo.Realizacje order by IDE desc";
            return (int)(ExecuteQuery(query) ?? 0) + 1;
        }
        
        public static int nrNowegoZamowienia(int IDEDzialu, int year)
        {
            /*   
            string query = "select max(Numer) from dbo.Zamowienia where ZamowienieDzial=" +
             IDEDzialu.ToString() + " and Numer_Rok=year(getdate())"*/

            string query = "select top 1 Numer from dbo.Zamowienia where ZamowienieDzial=" +
            + IDEDzialu + " and Numer_Rok=" + year + " order by numer desc";

            return (int)(ExecuteQuery(query) ?? 0) + 1;
        }


        public static string Numer_Roz(int IDEDzialu)
        {
            string query = " select Roz from dbo.Dzialy where IDE =" + IDEDzialu.ToString();

            return (string)(ExecuteQuery(query) ?? "?");
        }

        public static bool czyJestDyrGeneralnym(int IDE)
        {
            string query = " select * from dbo.DyrNaczelni where IDEUser=" + IDE.ToString();

            return ExecuteQuery(query) == null ? false : true;
        }

        public static bool czyJestGlownymKsiegowym(int IDE)
        {
            string query = " select * from dbo.GlowniKsiegowi where IDEUser=" + IDE.ToString();

            return ExecuteQuery(query) == null ? false : true;
        }

        public static bool czyJestKierownikiem(int IDE)
        {
            string query = " select * from dbo.Kierownicy where IDEUser=" + IDE.ToString();

            return ExecuteQuery(query) == null ? false : true;
        }

        public static bool czyJestDyrFinansowym(int IDE)
        {
            string query = " select * from dbo.DyrFinansowi where IDEUser=" + IDE.ToString();

            return ExecuteQuery(query) == null ? false : true;            
        }

        public static bool czyMoznaUsunacZam(int IDE)
        {
            short dec1;
            short dec2;
            short dec3;
            object obj;

            string query = "select Decyzja1 from dbo.Zamowienia where IDE =" + IDE.ToString();
            obj = ExecuteQuery(query);
            dec1 = (obj == DBNull.Value) ? Convert.ToInt16(0) : (short)obj;

            query = "select Decyzja2 from dbo.Zamowienia where IDE =" + IDE.ToString();
            obj = ExecuteQuery(query);
            dec2 = (obj == DBNull.Value) ? Convert.ToInt16(0) : (short)obj;

            query = "select Decyzja3 from dbo.Zamowienia where IDE =" + IDE.ToString();
            obj = ExecuteQuery(query);
            dec3 = (obj == DBNull.Value) ? Convert.ToInt16(0) : (short)obj;

         //   query = "select Decyzja3 from dbo.Zamowienia where IDE =" + IDE.ToString();

            int d = dec1 + dec2 + dec3;            

            return d == 0 ? true : false; 
        }


        public static bool czyJestToZamDzialuUzytkownika(int IDEUser,int IDEDZial)
        {
            string query = " select * from dbo.UsersDzialy where IDEUser= " + IDEUser + " and IDEDzial = " + IDEDZial;

            return ExecuteQuery(query) == null ? false : true;
        }

        public static string podajDecyzje(int IDE)
        {
            string query = " select Decyzja from dbo.Decyzje where IDE=" + IDE;

            return (string)ExecuteQuery(query);
        }

        public static string podajLoginUzytkownika(int IDE)
        {
            string query = " select symbol from dbo.Users where IDE= " + IDE;

            return (string)ExecuteQuery(query);
        }

        public static string podajDaneUzytkownika(int IDE)
        {
            string query = " select imie + ' ' + nazwisko from dbo.Users where IDE= " + IDE;

            return (string)ExecuteQuery(query);
        }

        public static string podajEmailaUzytkownika(int IDE)
        {
            string query = " select email from dbo.Users where IDE= " + IDE;

            return (string)ExecuteQuery(query);
        }
   
    }
}