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

namespace СУБД_Техносервис
{
    public partial class FormWorkPlace : Form
    {
        public FormWorkPlace()
        {
            InitializeComponent();
        }

        string TxtCon = "Data Source=213.155.192.79,3002;Initial Catalog=TechServiceBAU;Persist Security Info=True;User ID=u21baranov;Password=au5p";

        /// <summary>
        /// вывод данных в виде плиточного интерфейса
        /// </summary>
        void OutputRequest()
        {
            SqlConnection con = new SqlConnection(TxtCon);
            con.Open();
            string TxtQuery = @"SELECT        Request.IdRequest, Manager.Photo, (Manager.Surname + ' ' + Manager.Name + ' ' + Manager.Patronymic) as FIOManager, (Client.Surname + ' ' + Client.Name + ' ' + Client.Patronymic) as FIOClient, Request.EquipmentDefect, Request.DateAccepting, TypeDefect.Title as TypeDefect, 
                         Status.Title AS Statys, (SELECT        String_AGG(Master.Surname + ' ' + Master.Name + ' ' + Master.Patronymic, ', ')
FROM            Employee AS Master INNER JOIN
                         ExecutionRequest ON Master.IdEmployee = ExecutionRequest.IdMaster
where ExecutionRequest.IdRequest = Request.IdRequest) as FIOMasters
FROM            Client INNER JOIN
                         Request ON Client.IdClient = Request.IdClient INNER JOIN
                         Employee AS Manager ON Request.IdManager = Manager.IdEmployee INNER JOIN
                         TypeDefect ON Request.IdTypeDefect = TypeDefect.IdTypeDefect INNER JOIN
                         Status ON Request.IdStatus = Status.IdStatus";
            SqlCommand cmd = new SqlCommand(TxtQuery, con);
            SqlDataReader Rez = cmd.ExecuteReader();
            DgvRequest.Rows.Clear();
            while (Rez.Read())
            {
                string TxtInfo = $@"Менеджер: {Rez["FIOManager"]}
Клиент: {Rez["FIOClient"]}
Оборудование: {Rez["EquipmentDefect"]}
Заявка добавлена: {Rez["DateAccepting"]}
Тип неисправности: {Rez["TypeDefect"]}
Статус заявки {Rez["Statys"]}";
                try
                {
                    DgvRequest.Rows.Add(Rez["IdRequest"], Image.FromFile(Application.StartupPath + "\\Photo\\" + Rez["Photo"]), TxtInfo, $"Исполнители: {Rez["FIOMasters"]}");
                }
                catch
                {
                    DgvRequest.Rows.Add(Rez["IdRequest"], Image.FromFile(Application.StartupPath + "\\Photo\\Picture.png"), TxtInfo, $"Исполнители: {Rez["FIOMasters"]}");
                }
            }
            con.Close();
        }

        private void FormWorkPlace_Load(object sender, EventArgs e)
        {
            OutputRequest();
        }
    }
}