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

       
        void FillPartner()
        {
            SqlConnection conection = new SqlConnection(FormAuthorizathion.txtcon);
            conection.Open();
            string additional = $"Where (Namepartner like '{tbxSearch.Text}%' or Surname like '{tbxSearch.Text}%') ";//условие поиска
            if (cbxRatingSearch.Checked && tbxMinR.Text !="" && tbxMaxR.Text != "")
            {
                try
                {
                    additional += $" and Rating >='{int.Parse(tbxMinR.Text)}' and Rating <='{int.Parse(tbxMaxR.Text)}'";
                }
                catch { return; }//на случай если рейтинг не число
            }
            if (cmbTypePartner.Text != "Все типы")
            {
                additional += $"  and TypePartner = '{cmbTypePartner.Text}'";// фильтр по типу партнера
            }

            string query = @"SELECT distinct        partner.IDPartner ,partner.Namepartner, TypePartner.TypePartner, Partner.Surname, Partner.Name, Partner.Patronomyc, Partner.Phone, Partner.Rating, 
(SELECT         sum(CountProduct) FROM            PartnerProducts  where IDPartner = partner.IDPartner group by IDPartner) as countsales
FROM            Partner INNER JOIN
                         TypePartner ON Partner.IDTypePartner = TypePartner.IDTypePartner INNER JOIN
                         PartnerProducts ON Partner.IDPartner = PartnerProducts.IDPartner  " + additional;
            SqlCommand command = new SqlCommand(query, conection);
            SqlDataReader res;
            try
            {
                res = command.ExecuteReader();
            }
            catch { dgvPartner.Rows.Clear(); tbxMinR.Text = ""; tbxMaxR.Text = ""; return; }
            dgvPartner.Rows.Clear();
            while (res.Read())
            {
                string info = $@"{res["TypePartner"]}| {res["NamePartner"]} 
{res["Surname"]} {res["Name"]} {res["Patronomyc"]}
{res["Phone"]}
Рейтинг: {res["Rating"]}";
                string proc = "0%";
                int countSales = int.Parse(res["countsales"].ToString());
                try
                {
                    if (countSales < 10000)
                    {
                        proc = "0%";
                        
                    }
                    else if (countSales <= 50000 && countSales> 10000)
                    {
                        proc = "5%";
                    }
                    else if (countSales < 300000 && countSales >= 50000)
                    {
                        proc = "10%";
                    }
                    else
                    {
                        proc = "15%";
                    }
                }
                catch { }
                dgvPartner.Rows.Add(res["IDPartner"], info,proc);
                if (proc == "0%")
                {
                    dgvPartner.Rows[dgvPartner.RowCount-1].DefaultCellStyle.BackColor = Color.Orange;
                }
                if (proc == "15%")
                {
                    dgvPartner.Rows[dgvPartner.RowCount - 1].DefaultCellStyle.BackColor = Color.DarkRed;
                }
                if (proc == "10%")
                {
                    dgvPartner.Rows[dgvPartner.RowCount - 1].DefaultCellStyle.BackColor = Color.Green;
                }
                if (proc == "5%")
                {
                    dgvPartner.Rows[dgvPartner.RowCount - 1].DefaultCellStyle.BackColor = Color.Yellow;
                }
            }

            //try
            //{

            //}
            //finally
            //{
            //    res.Close();
            //}




            conection.Close();
        }
        private void FormManager_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'alfafloorGDVDataSet1.Type4Filter' table. You can move, or remove it, as needed.
            this.type4FilterTableAdapter.Fill(this.alfafloorGDVDataSet1.Type4Filter);
            // TODO: This line of code loads data into the 'alfafloorGDVDataSet1.Manager' table. You can move, or remove it, as needed.
            this.managerTableAdapter.Fill(this.alfafloorGDVDataSet1.Manager);
            try
            {
                pbxProfile.Image = Image.FromFile(Application.StartupPath + "\\profilepic\\" + lblphoto.Text);
            }
            catch 
            {
                MessageBox.Show("Фото менеджера отсутствует в папке", "Внимание",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            lblphoto.Hide();


            FillPartner();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

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

        private void cbxRatingSearch_CheckedChanged(object sender, EventArgs e)
        {
            if (cbxRatingSearch.Checked)
            {
                FillPartner();
            }
            FillPartner();
        }

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

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

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

        private void btnAddEdit_Click(object sender, EventArgs e)
        {
            FormAddEdit frmAddEdit = new FormAddEdit();
            this.Hide();
            frmAddEdit.lblTitleForm.Text = "Добавление партнера";
            frmAddEdit.ShowDialog();
            this.Show();
        }

        private void dgvPartner_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            FormAddEdit frmAddEdit = new FormAddEdit();
            this.Hide();
            frmAddEdit.lblTitleForm.Text = "Редактирование партнера";
            frmAddEdit.lblIdPartner.Text = dgvPartner.CurrentRow.Cells[0].Value.ToString();//передать нулевую ячейку(idpartner) выделенной строки
            frmAddEdit.bsPartner.Filter = $"Idpartner ={dgvPartner.CurrentRow.Cells[0].Value.ToString()}";//фильтр записей по id на форме редакта
            frmAddEdit.ShowDialog();
            this.Show();
            FillPartner();
        }
    }
}