Alphapol/СУБД Альфапол/FormManager.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 FormManager : Form
    {
        public FormManager()
        {
            InitializeComponent();
        }

       
        void FillPartner()
        {
            string additionalUslovie = $" where (surname like '{tbxFind.Text}%' or NamePartner like '{tbxFind.Text}%')";

            if(cbxFindReting.Checked && tbxMaxReting.Text != "" && tbxMinRating.Text != "")
            {
                additionalUslovie += $" and Rating >= {tbxMinRating.Text} and Rating <= {tbxMaxReting.Text}";
            }

            if(cmbTypePartner.Text != "Все типы")
            {
                additionalUslovie += $"  and Type = {cmbTypePartner.Text}";
            }

            SqlConnection con = new SqlConnection(FormAutorization.txtCon);
            con.Open();
            string txtQuery = $@"
SELECT        Partner.idPartner, TypePartner.Type, Partner.NamePartner, Partner.Surname, Partner.Name, Partner.Patronymic, 
Partner.Phone, Partner.Rating, (SELECT        sum(CountProduct)
FROM            ProductPartner where idPartner = Partner.idPartner) as CountSale
FROM            Partner INNER JOIN
                         TypePartner ON Partner.idTypePartner = TypePartner.idTypePartner" + additionalUslovie;

            SqlCommand query = new SqlCommand(txtQuery, con);
            SqlDataReader res = null;
            try
            {
               res = query.ExecuteReader();
            }
            catch 
            { 
                dgvPartner.Rows.Clear();
                return;
            }
            dgvPartner.Rows.Clear();
            while (res.Read())
            {
                string info = $@"{res["Type"]} | {res["NamePartner"]}
Директор: {res["Surname"]} {res["Name"]} {res["Patronymic"]}
{res["Phone"]}
Рейтинг: {res["Rating"]}";
                string Percent = "0%";
                try
                {
                    int countSale = int.Parse(res["CountSale"].ToString());

                    if (countSale < 10000)
                    {
                        Percent = "0%";
                    }
                    else if (countSale < 50000)
                    {
                        Percent = "5%";
                    }
                    else if (countSale < 300000)
                    {
                        Percent = "10%";
                    }
                    else
                    {
                        Percent = "15%";
                    }
                }
                catch { }
                dgvPartner.Rows.Add(res["idPartner"], info, Percent);
            }


            con.Close();
        }

        private void FormManager_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dbAlphapolAGDataSet.TypeForFilter' table. You can move, or remove it, as needed.
            this.typeForFilterTableAdapter.Fill(this.dbAlphapolAGDataSet.TypeForFilter);
            // TODO: This line of code loads data into the 'dbAlphapolAGDataSet.Manager' table. You can move, or remove it, as needed.
            this.managerTableAdapter.Fill(this.dbAlphapolAGDataSet.Manager);
            try
            {
                pbxPhoto.Image = Image.FromFile(Application.StartupPath + "\\photo\\" + lblPhoto.Text);
            }
            catch { }
            lblPhoto.Hide();
            FillPartner();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            FormAddEdit formAddEdit = new FormAddEdit();
            formAddEdit.ShowDialog();
            FillPartner();
        }

        private void dgvPartner_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            FormAddEdit formAddEdit = new FormAddEdit();
            formAddEdit.lblTitle.Text = "Редактирование партнера";
            formAddEdit.bsPartner.Filter = $"idPartner = {dgvPartner.CurrentRow.Cells[0].Value}";
            formAddEdit.ShowDialog();
            FillPartner();
        }

        private void tbxFind_TextChanged(object sender, EventArgs e)
        {
            FillPartner();

        }

        private void cbxFindReting_CheckedChanged(object sender, EventArgs e)
        {
            FillPartner();
        }

        private void tbxMaxReting_TextChanged(object sender, EventArgs e)
        {
            FillPartner();
        }

        private void tbxMinRating_TextChanged(object sender, EventArgs e)
        {
            FillPartner();
        }

        private void cmbTypePartner_SelectedIndexChanged(object sender, EventArgs e)
        {
            FillPartner();
        }
    }
}