using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Baza_Reklam.Classes.Model;

namespace Baza_Reklam.Classes.Repositories
{
    public class ProductionRepository : Repository<Production>
    {
        private const string _query =
            "SELECT TOP 100 P.*, R.[Id Reklamy] AS AdName, R.CustomerId FROM dbo.Produkcja P INNER JOIN dbo.Reklama R ON R.ReklamaId=P.ReklamaId WHERE P.[Type]=2 {0} ORDER BY P.Id DESC";

        public override Production Find(int id)
        {
            string query = string.Format(_query, " AND P.Id=" + id);
            List<Production> productions = GetProductionList(query);
            if (productions.Count > 0)
                return productions[0];

            return null;
        }

        public override List<Production> FindAll()
        {
            return GetProductionList(_query);
        }

        public List<Production> FindAllToDo(string productionAgent)
        {
            string query = string.Format(_query, " AND (P.Typ_Projektu='NOWY' OR (P.Typ_Projektu='W PRODUKCJI' AND P.Produkcja='{0}'))");
            query = string.Format(query, productionAgent);

            return GetProductionList(query);
        }

        public List<Production> FindAllCompleted()
        {
            string query = string.Format(_query, " AND P.Typ_Projektu='GOTOWY'");

            return GetProductionList(query);
        }

        public List<Production> Find(string status, string adName)
        {
            string conditions = string.Empty;
            if (!string.IsNullOrEmpty(status))
                conditions += " AND P.Typ_Projektu='" + status + "'";

            if (!string.IsNullOrEmpty(adName))
                conditions += " AND R.[Id Reklamy] LIKE '%" + adName + "%'";


            string query = string.Format(_query, conditions);

            return GetProductionList(query);
        }

        public override void Save(Production entity)
        {
            const string query = "UPDATE dbo.Produkcja SET Typ_Projektu=@status, DATA_PRODUKCJA=@dataProdukcja, DATA_AKCEPTACJI=@dataAkceptacji, DATA_KOREKTA=@returnDate, Produkcja=@productionAgent WHERE Id=@id;";

            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(ConnString.getConnString().Value);
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@id", entity.Id);
                cmd.Parameters.AddWithValue("@status", entity.Status);
                cmd.Parameters.AddWithValue("@productionAgent", entity.ProductionAgent);

                if (entity.ProductionDate != DateTime.MinValue)
                    cmd.Parameters.AddWithValue("@dataProdukcja", entity.ProductionDate);
                else
                    cmd.Parameters.AddWithValue("@dataProdukcja", DBNull.Value);
                
                if (entity.AcceptanceDate != DateTime.MinValue)
                    cmd.Parameters.AddWithValue("@dataAkceptacji", entity.AcceptanceDate);
                else
                    cmd.Parameters.AddWithValue("@dataAkceptacji", DBNull.Value);

                if (entity.ReturnDate != DateTime.MinValue)
                    cmd.Parameters.AddWithValue("@returnDate", entity.ReturnDate);
                else
                    cmd.Parameters.AddWithValue("@returnDate", DBNull.Value);
            
                cmd.ExecuteNonQuery();
            }
            finally
            {

                if (cmd != null) cmd.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        private static List<Production> GetProductionList(string query)
        {
            List<Production> productionList = new List<Production>();

            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(ConnString.getConnString().Value);
                conn.Open();
                cmd = new SqlCommand(query, conn);
                reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Production production = new Production();
                        production.Id = Convert.ToInt32(reader["ID"]);
                        production.AdId = Convert.ToInt32(reader["ReklamaId"]);
                        production.CustomerId = Convert.ToInt32(reader["CustomerId"]);
                        production.AdName = Convert.ToString(reader["AdName"]);
                        production.SellAgent = Convert.ToString(reader["Agent"]);
                        production.ProductionAgent = Convert.ToString(reader["Produkcja"]);
                        production.Status = Convert.ToString(reader["Typ_Projektu"]);
                        production.Content = Convert.ToString(reader["Opis"]);
                        production.Module = Convert.ToString(reader["Moduł"]);
                        if (reader["DATA_PRODUKCJA"] != DBNull.Value)
                            production.ProductionDate = Convert.ToDateTime(reader["DATA_PRODUKCJA"]);
                        if (reader["DATA_AKCEPTACJI"] != DBNull.Value)
                            production.AcceptanceDate = Convert.ToDateTime(reader["DATA_AKCEPTACJI"]);
                        if (reader["DATA_KOREKTA"] != DBNull.Value)
                            production.ReturnDate = Convert.ToDateTime(reader["DATA_KOREKTA"]);
                        productionList.Add(production);
                    }
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null) cmd.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }
            return productionList;
        } 
    }
}
