Go/2022-23/Larin/SUBD/fMain.cs

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

namespace SUBD
{
    public partial class fMain : Form
    {
        public static string txtCon = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BDPraktik.mdf;Integrated Security=True;Connect Timeout=30";
        int CountPerPage = 5;
        int NBegin = 5;
        public string idSelectedUser="";

        class ItemPanel : Panel
        {
            //public System.Windows.Forms.Panel panel1;
            public System.Windows.Forms.Label lRole;
            public System.Windows.Forms.Label lAdr;
            public System.Windows.Forms.Label lOtch;
            public System.Windows.Forms.Label lName;
            public System.Windows.Forms.Label lFam;
            public System.Windows.Forms.PictureBox pbImage;
            public System.Windows.Forms.Label lPol;
            public System.Windows.Forms.Label lid;
            public System.Windows.Forms.Label label1;
            public System.Windows.Forms.Label label2;
            public System.Windows.Forms.Label label3;
            public System.Windows.Forms.Label label4;
            public System.Windows.Forms.Label label5;
            public System.Windows.Forms.Label label6;

            public ItemPanel()
            {
                this.lid = new System.Windows.Forms.Label();
                this.lPol = new System.Windows.Forms.Label();
                this.lRole = new System.Windows.Forms.Label();
                this.lAdr = new System.Windows.Forms.Label();
                this.lOtch = new System.Windows.Forms.Label();
                this.lName = new System.Windows.Forms.Label();
                this.lFam = new System.Windows.Forms.Label();
                this.pbImage = new System.Windows.Forms.PictureBox();
                this.label1 = new System.Windows.Forms.Label();
                this.label2 = new System.Windows.Forms.Label();
                this.label3 = new System.Windows.Forms.Label();
                this.label4 = new System.Windows.Forms.Label();
                this.label5 = new System.Windows.Forms.Label();
                this.label6 = new System.Windows.Forms.Label();
                // 
                // panel1
                // 
                this.BackColor = System.Drawing.Color.White;
                this.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
                this.Controls.Add(this.label1);
                this.Controls.Add(this.label2);
                this.Controls.Add(this.label3);
                this.Controls.Add(this.label4);
                this.Controls.Add(this.label5);
                this.Controls.Add(this.label6);
                this.Controls.Add(this.lid);
                this.Controls.Add(this.lPol);
                this.Controls.Add(this.lRole);
                this.Controls.Add(this.lAdr);
                this.Controls.Add(this.lOtch);
                this.Controls.Add(this.lName);
                this.Controls.Add(this.lFam);
                this.Controls.Add(this.pbImage);
                this.Font = new System.Drawing.Font("Courier New", 9F);
                this.Location = new System.Drawing.Point(15, 178);
                this.Name = "panel1";
                this.Size = new System.Drawing.Size(790, 225);
                this.TabIndex = 0;
                // 
                // lid
                // 
                this.lid.AutoSize = true;
                this.lid.Location = new System.Drawing.Point(22, 188);
                this.lid.Name = "lid";
                this.lid.Size = new System.Drawing.Size(17, 17);
                this.lid.TabIndex = 7;
                this.lid.Text = "a";
                this.lid.Visible = false;
                // 
                // lPol
                // 
                this.lPol.AutoSize = true;
                this.lPol.Location = new System.Drawing.Point(360, 168);
                this.lPol.Name = "lPol";
                this.lPol.Size = new System.Drawing.Size(62, 17);
                this.lPol.TabIndex = 6;
                this.lPol.Text = "label1";
                // 
                // lRole
                // 
                this.lRole.AutoSize = true;
                this.lRole.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.lRole.Location = new System.Drawing.Point(360, 140);
                this.lRole.Name = "lRole";
                this.lRole.Size = new System.Drawing.Size(62, 17);
                this.lRole.TabIndex = 5;
                this.lRole.Text = "label1";
                // 
                // lAdr
                // 
                this.lAdr.AutoSize = true;
                this.lAdr.Location = new System.Drawing.Point(360, 112);
                this.lAdr.Name = "lAdr";
                this.lAdr.Size = new System.Drawing.Size(62, 17);
                this.lAdr.TabIndex = 4;
                this.lAdr.Text = "label1";
                // 
                // lOtch
                // 
                this.lOtch.AutoSize = true;
                this.lOtch.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.lOtch.Location = new System.Drawing.Point(360, 85);
                this.lOtch.Name = "lOtch";
                this.lOtch.Size = new System.Drawing.Size(62, 17);
                this.lOtch.TabIndex = 3;
                this.lOtch.Text = "label3";
                // 
                // lName
                // 
                this.lName.AutoSize = true;
                this.lName.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.lName.Location = new System.Drawing.Point(360, 57);
                this.lName.Name = "lName";
                this.lName.Size = new System.Drawing.Size(62, 17);
                this.lName.TabIndex = 2;
                this.lName.Text = "label2";
                // 
                // lFam
                // 
                this.lFam.AutoSize = true;
                this.lFam.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.lFam.Location = new System.Drawing.Point(360, 29);
                this.lFam.Name = "lFam";
                this.lFam.Size = new System.Drawing.Size(62, 17);
                this.lFam.TabIndex = 1;
                this.lFam.Text = "label1";
                // 
                // pbImage
                // 
                this.pbImage.Location = new System.Drawing.Point(25, 28);
                this.pbImage.Name = "pbImage";
                this.pbImage.Size = new System.Drawing.Size(169, 157);
                this.pbImage.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage;
                this.pbImage.TabIndex = 0;
                this.pbImage.TabStop = false;
                // 
                // label1
                // 
                this.label1.AutoSize = true;
                this.label1.Location = new System.Drawing.Point(200, 168);
                this.label1.Name = "label1";
                this.label1.Size = new System.Drawing.Size(44, 17);
                this.label1.TabIndex = 13;
                this.label1.Text = "Пол:";
                // 
                // label2
                // 
                this.label2.AutoSize = true;
                this.label2.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.label2.Location = new System.Drawing.Point(200, 140);
                this.label2.Name = "label2";
                this.label2.Size = new System.Drawing.Size(98, 17);
                this.label2.TabIndex = 12;
                this.label2.Text = "Должность:";
                // 
                // label3
                // 
                this.label3.AutoSize = true;
                this.label3.Location = new System.Drawing.Point(200, 112);
                this.label3.Name = "label3";
                this.label3.Size = new System.Drawing.Size(161, 17);
                this.label3.TabIndex = 11;
                this.label3.Text = "Место жительства:";
                // 
                // label4
                // 
                this.label4.AutoSize = true;
                this.label4.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.label4.Location = new System.Drawing.Point(200, 84);
                this.label4.Name = "label4";
                this.label4.Size = new System.Drawing.Size(89, 17);
                this.label4.TabIndex = 10;
                this.label4.Text = "Отчество:";
                // 
                // label5
                // 
                this.label5.AutoSize = true;
                this.label5.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.label5.Location = new System.Drawing.Point(200, 56);
                this.label5.Name = "label5";
                this.label5.Size = new System.Drawing.Size(44, 17);
                this.label5.TabIndex = 9;
                this.label5.Text = "Имя:";
                // 
                // label6
                // 
                this.label6.AutoSize = true;
                this.label6.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                this.label6.Location = new System.Drawing.Point(200, 28);
                this.label6.Name = "label6";
                this.label6.Size = new System.Drawing.Size(80, 17);
                this.label6.TabIndex = 8;
                this.label6.Text = "Фамилия:";
            }
        }

        public fMain()
        {
            InitializeComponent();
        }

        private void bAddUser_Click(object sender, EventArgs e)
        {
            fAddUser f = new fAddUser();
            f.ShowDialog();

            GetData();
            FillPanel();
        }

        private void bDelUser_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("вы действительно хотите удалить пользователя?", "Запрос на удаление", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                if (idSelectedUser != "")
                {
                    SqlConnection con = new SqlConnection(txtCon);
                    con.Open();
                    string txt = $"delete from Teacher where IdTeacher={idSelectedUser}";
                    SqlCommand com = new SqlCommand(txt, con);
                    com.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("Вы удалили пользователя", "Внимание");
                    MessageBox.Show("Запрос: " + txt);
                    GetData();
                    FillPanel();
                    idSelectedUser = "";
                }
                else
                {
                    MessageBox.Show("Выберите пользователя");
                }
            }
        }

        struct Agent
        {
            public string idTeacher, famTeacher, NameTeacher, OtchTeacher, adr, pol, role;
            public Image img;
        }
        List<Agent> lstAgent = new List<Agent>();
        ItemPanel CurrentItem;
        string usl = "";

        void GetData()
        {
            usl = "";
            NBegin = 0;
            lstAgent.Clear();
            if (CmbGender.SelectedIndex == 0) usl += "";
            if (CmbGender.SelectedIndex == 1) usl += " and Pol=N'муж'";
            if (CmbGender.SelectedIndex == 2) usl += " and Pol=N'жен'";
            if (CmbFind.Text == "фамилии" && TbxFind.Text != "")
                usl += $" and FamTeacher Like N'{TbxFind.Text}%'";
            if (CmbFind.Text == "имени" && TbxFind.Text != "")
                usl += $" and NameTeacher Like N'{TbxFind.Text}%'";
            if (CmbFind.Text == "отчеству" && TbxFind.Text != "")
                usl += $" and OtchTeacher Like N'{TbxFind.Text}%'";
            SqlConnection con = new SqlConnection(txtCon);
            con.Open();
            string txt = $@"select IdTeacher, FamTeacher, NameTeacher, OtchTeacher, Adr, Pol, Photo, NameRole
from Teacher, RoleTeacher
where Teacher.Role=RoleTeacher.IdRole {usl}";
            SqlCommand com = new SqlCommand(txt, con);
            SqlDataReader rez = com.ExecuteReader();
            while (rez.Read())
            {
                Agent agent1 = new Agent();
                agent1.idTeacher = rez["IdTeacher"].ToString();
                agent1.famTeacher = rez["FamTeacher"].ToString();
                agent1.NameTeacher = rez["NameTeacher"].ToString();
                agent1.OtchTeacher = rez["OtchTeacher"].ToString();
                agent1.adr = rez["Adr"].ToString();
                agent1.pol = rez["Pol"].ToString();
                agent1.role = rez["NameRole"].ToString();
                try
                {
                    byte[] masphoto = (byte[])rez["Photo"];
                    MemoryStream msPhoto = new MemoryStream(masphoto);
                    agent1.img = Image.FromStream(msPhoto);
                }
                catch
                {
                    agent1.img = Properties.Resources.imgPng;
                }
                lstAgent.Add(agent1);
            }
            con.Close();
        }

        void FillPanel()
        {
            MainPanel.Controls.Clear();
            //foreach (Agent agent1 in lstAgent)
            //{
            //    ItemPanel item = new ItemPanel();
            //    item.lFam.Text = agent1.famTeacher;
            //    item.lid.Text = agent1.idTeacher;
            //    item.lName.Text = agent1.NameTeacher;
            //    item.lOtch.Text = agent1.OtchTeacher;
            //    item.lPol.Text = agent1.pol;
            //    item.lRole.Text = agent1.role;
            //    item.pbImage.Image = agent1.img;
            //    item.lAdr.Text = agent1.adr;
            //    MainPanel.Controls.Add(item);
            //}
            for (int i = NBegin; i <= NBegin + CountPerPage - 1; i++)
            {
                if (i > lstAgent.Count - 1)
                    break;
                ItemPanel item = new ItemPanel();
                item.lFam.Text = lstAgent[i].famTeacher;
                item.lid.Text = lstAgent[i].idTeacher;
                item.lName.Text = lstAgent[i].NameTeacher;
                item.lOtch.Text = lstAgent[i].OtchTeacher;
                item.lPol.Text = lstAgent[i].pol;
                item.lRole.Text = lstAgent[i].role;
                try
                {
                    item.pbImage.Image = lstAgent[i].img;
                }
                catch
                {
                    item.pbImage.Image = Properties.Resources.imgPng;
                }
                item.lAdr.Text = lstAgent[i].adr;
                MainPanel.Controls.Add(item);

                item.Click += Item_Click;
                item.lAdr.Click += Elem_Click;
                item.lFam.Click += Elem_Click;
                item.lid.Click += Elem_Click;
                item.lName.Click += Elem_Click;
                item.lOtch.Click += Elem_Click;
                item.lPol.Click += Elem_Click;
                item.lRole.Click += Elem_Click;
                item.pbImage.Click += Elem_Click;

                if (MainPanel.Controls.Count == 1)
                {
                    CurrentItem = item;
                    CurrentItem.BackColor = Color.LightGreen;
                }
            }
            if (CountPerPage == 0)
                CountPerPage = 1;
            int Cnt = lstAgent.Count / CountPerPage;
            if (lstAgent.Count % CountPerPage != 0)
                Cnt++;
            PanelPages.Controls.Clear();

            PanelPages.Controls.Add(lNext);
            lback.Dock = DockStyle.Left;
            lNext.Dock = DockStyle.Left;
            for (int i = Cnt; i >= 1; i--)
            {
                Label lbl = new Label();
                lbl.Text = i.ToString();
                lbl.Dock = DockStyle.Left;
                lbl.AutoSize = true;
                if ((NBegin + CountPerPage) / CountPerPage == i)
                {
                    lbl.BackColor = Color.LightGray;
                    lbl.Font = new Font(lbl.Font, FontStyle.Underline);
                }
                PanelPages.Controls.Add(lbl);

                lbl.Click += Lbl_Click;
            }
            PanelPages.Controls.Add(lback);
        }

        private void Elem_Click(object sender, EventArgs e)
        {
            CurrentItem.BackColor = Color.White;
            CurrentItem = (sender as Control).Parent as ItemPanel;
            CurrentItem.BackColor = Color.LightGreen;
            idSelectedUser = CurrentItem.lid.Text;
        }

        private void Item_Click(object sender, EventArgs e)
        {
            CurrentItem.BackColor = Color.White;
            CurrentItem = sender as ItemPanel;
            CurrentItem.BackColor = Color.LightGreen;
            idSelectedUser = CurrentItem.lid.Text;
        }

        private void Lbl_Click(object sender, EventArgs e)
        {
            int n = int.Parse((sender as Label).Text);

            NBegin = (n - 1) * CountPerPage;
            FillPanel();
        }

        private void fMain_Load(object sender, EventArgs e)
        {
            this.Icon = Properties.Resources.imgIco;
            pbPhoto.Image = Properties.Resources.imgPng;
            GetData();
            FillPanel();
            CmbGender.SelectedIndex = 0;
            CmbFind.SelectedIndex = 0;
            CmbCountLines.SelectedIndex = 0;
        }

        private void lback_Click(object sender, EventArgs e)
        {
            if (NBegin > 0)
            {
                NBegin -= CountPerPage;
                FillPanel();
            }
        }

        private void lNext_Click(object sender, EventArgs e)
        {
            if (NBegin + CountPerPage < lstAgent.Count)
            {
                NBegin += CountPerPage;
                FillPanel();
            }
        }

        private void CmbCountLines_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                int s = int.Parse(CmbCountLines.Text);
                CountPerPage = s;
            }
            catch
            {
                CountPerPage = lstAgent.Count;
            }
            NBegin = 0;
            FillPanel();
        }

        private void CmbGender_SelectedIndexChanged(object sender, EventArgs e)
        {
            GetData();
            FillPanel();
        }

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

        private void CmbFind_SelectedIndexChanged(object sender, EventArgs e)
        {
            GetData();
            FillPanel();
        }
    }
}