TechServise/СУБД Техносервис/FormWorkPlace.cs

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();
        }

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

        public static string IdManager = "";
        /// <summary>
        /// вывод данных в виде плиточного интерфейса
        /// </summary>
        void OutputRequest()
        {
            string Uslovie = "";
            if (RbtInfo.Checked)
            {
                Uslovie = $@"where (EquipmentDefect Like '%{TbxFind.Text}%' or Status.Title Like '%{TbxFind.Text}%' or (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) Like '%{TbxFind.Text}%')";
            }
            else if (RbtNum.Checked && TbxFind.Text != "")
            {
                try
                {
                    Uslovie = $"where IdRequest = {int.Parse(TbxFind.Text)}";
                }
                catch
                {
                    Uslovie = $"where IdRequest = 0";
                }
            }
            if (CmbDefect.SelectedIndex > 0)
                Uslovie += $" and TypeDefect.Title = '{CmbDefect.Text}'";
            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 " + Uslovie;
            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"]}");
                }
                if (Rez["Statys"].ToString() == "В ожидании")
                    DgvRequest.Rows[DgvRequest.RowCount - 1].DefaultCellStyle.BackColor = Color.Yellow;
            }
            ToolSCount.Text = $"Записей {DgvRequest.RowCount} из {BsRequest.Count}";
            con.Close();
        }

        private void FormWorkPlace_Load(object sender, EventArgs e)
        {
            // TODO: данная строка кода позволяет загрузить данные в таблицу "techServiceBAUDataSet.Request". При необходимости она может быть перемещена или удалена.
            this.requestTableAdapter.Fill(this.techServiceBAUDataSet.Request);
            // TODO: данная строка кода позволяет загрузить данные в таблицу "techServiceBAUDataSet.AllTypeDefect". При необходимости она может быть перемещена или удалена.
            this.allTypeDefectTableAdapter.Fill(this.techServiceBAUDataSet.AllTypeDefect);
            // TODO: данная строка кода позволяет загрузить данные в таблицу "techServiceBAUDataSet.AllTypeDefect". При необходимости она может быть перемещена или удалена.
            this.allTypeDefectTableAdapter.Fill(this.techServiceBAUDataSet.AllTypeDefect);
            // TODO: данная строка кода позволяет загрузить данные в таблицу "techServiceBAUDataSet.Employee". При необходимости она может быть перемещена или удалена.
            this.employeeTableAdapter.Fill(this.techServiceBAUDataSet.Employee);
            OutputRequest();
            try
            {
                PbxPhoto.Image = Image.FromFile(Application.StartupPath + $"\\Photo\\{LblPhoto.Text}");
            }
            catch { }
            IdManager = ((DataRowView)BsEmployee.Current)[0].ToString();
            LblPhoto.Hide();
            LblIdRole.Hide();
            if (LblIdRole.Text == "1")
            {
                LblTitleWork.Text = "Рабочее место менеджера";
                BtnProf.Visible = false;
            }
            else
            {
                LblTitleWork.Text = "Рабочее место мастера";
                BtnAdd.Visible = false;
                BtnInfo.Visible = false;
            }
        }

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

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

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

        private void BtnAdd_Click(object sender, EventArgs e)
        {
            FormAddEditRequest Frm = new FormAddEditRequest();
            Frm.ShowDialog();
            this.requestTableAdapter.Fill(this.techServiceBAUDataSet.Request);
            OutputRequest();
        }

        private void DgvRequest_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (LblIdRole.Text == "1")
            {
                FormAddEditRequest Frm = new FormAddEditRequest();
                Frm.LblTitleRequest.Text = "Редактирование зайвки";
                Frm.BsRequest.Filter = $"IdRequest = {DgvRequest.CurrentRow.Cells[0].Value}";
                Frm.ShowDialog();
            }
            else if (LblIdRole.Text == "2")
            {
                FormEditMaster Frm = new FormEditMaster();
                Frm.BsRequest.Filter = $"IdRequest = {DgvRequest.CurrentRow.Cells[0].Value}";
                Frm.ShowDialog();
            }
            this.requestTableAdapter.Fill(this.techServiceBAUDataSet.Request);
            OutputRequest();
        }

        private void BtnInfo_Click(object sender, EventArgs e)
        {
            Forminfo Frm = new Forminfo();
            Frm.ShowDialog();
        }

        private void BtnProf_Click(object sender, EventArgs e)
        {
            FormProfil Frm = new FormProfil();
            Frm.BsMaster.Filter = BsEmployee.Filter;
            Frm.ShowDialog();
            this.employeeTableAdapter.Fill(this.techServiceBAUDataSet.Employee);
            OutputRequest();
            try
            {
                PbxPhoto.Image = Image.FromFile(Application.StartupPath + $"\\Photo\\{LblPhoto.Text}");
            }
            catch { }
        }
    }
}