2



using sql_pagining.models; using System.ComponentModel; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; using Dapper; using MySqlConnector; using sql_pagining.Windows;

namespace sql_pagining {

public partial class MainWindow : Window, INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    private void Invalidate()
    {
        if (PropertyChanged != null)
            PropertyChanged(this, new PropertyChangedEventArgs("productList"));
    }
    public int productsSelectedCount = 0;
    public decimal Result;
    private void CostChangeButton_Click(object sender, RoutedEventArgs e)
    {
        decimal sum = 0;
        List<int> idList = new List<int>();

        if (ProductListBox.SelectedItems.Count == 0)
        {
            MessageBox.Show("Выберите продукты для изменения цены.");
            return;
        }

        foreach (Product item in ProductListBox.SelectedItems)
        {
            sum += item.MinCostForAgent;
            idList.Add(item.ID);
        }
        var newWindow = new EnterMinCostForAgentWindow(sum / ProductListBox.SelectedItems.Count);

        if ((bool)newWindow.ShowDialog())
        {
            try
            {
                Globals.dataProvider.setMinCostForAgent(newWindow.Result, idList.ToArray());
                MessageBox.Show("Стоимость успешно изменена.");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
        }
    }
    private void ProductListBox_OnSelectionChanged(
object? sender,
SelectionChangedEventArgs e)
    {
        if (ProductListBox != null)
        {
            productsSelectedCount = ProductListBox.SelectedItems.Count;
        }
        Invalidate();
    }
    private int productCount;
    public List<String> pageList { get; set; } = new List<String>();
    public IEnumerable<Product> productList
    {
        get
        {

            Globals.dataProvider.clearFilter();
            if (productTypeFilterId > 0)
                Globals.dataProvider.addFilter(
                    "ProductTypeID = @ProductTypeID",
                    new { ProductTypeID = productTypeFilterId }
                );
            switch (sortType)
            {
                case 0:
                    Globals.dataProvider.setOrder("");
                    break;
                case 1:
                    Globals.dataProvider.setOrder("Title");
                    break;
                case 2:
                    Globals.dataProvider.setOrder("Title DESC");
                    break;
                case 3:
                    Globals.dataProvider.setOrder("ArticleNumber");
                    break;
                case 4:
                    Globals.dataProvider.setOrder("ArticleNumber DESC");
                    break;
                case 5:
                    Globals.dataProvider.setOrder("MaterialCost");
                    break;
                case 6:
                    Globals.dataProvider.setOrder("MaterialCost DESC");
                    break;
            }
            if (searchFilter.Length > 0)
            {
                Globals.dataProvider.addFilter(
                    "(Title LIKE @search OR Description LIKE @search)",
                    new { search = $"%{searchFilter}%" }
                );
            }
            var result = Globals.dataProvider.getProduct(currentPage);


            productCount = Globals.dataProvider.getProductCount();


            pageList.Clear();
            pageList.Add("<");
            for (int i = 1; i < (productCount / Globals.PAGE_LEN) + 1; i++)
            {
                pageList.Add(i.ToString());
            }
            pageList.Add(">");


            PageListListBox.ItemsSource = pageList;

            return result;
        }
        set
        {
            _productList = value;
            Invalidate();
        }
    }
    public List<ProductType> productTypeList { get; set; }
    public MainWindow()
    {
        InitializeComponent();
        DataContext = this;
        Globals.dataProvider = new DBDataProvider();
        productList = Globals.dataProvider.getProduct(currentPage);
        productTypeList = Globals.dataProvider.getProductTypes().ToList();
        productTypeList.Insert(0, new ProductType { Title = "Все типы продукции" });
    }
    public string[] sortList { get; set; } = {
"Без сортировки",
"название по убыванию",
"название по возрастанию",
"артикул по убыванию",
"артикул по возрастанию",
"цена по убыванию",
"цена по возрастанию" };
    private IEnumerable<Product> _productList;
    private const int PAGE_LEN = 20;

    private int _currentPage = 1;
    private string searchFilter = "";
    private int currentPage
    {
        get
        {
            return _currentPage;
        }
        set
        {
            _currentPage = value;
            Invalidate();
        }
    }
    private void InputElement_OnPointerPressed(
object? sender, MouseButtonEventArgs e)
    {
        switch ((sender as TextBlock).Text)
        {
            case "<":

                if (currentPage > 1) currentPage--;
                return;
            case ">":

                if (currentPage < productCount / Globals.PAGE_LEN) currentPage++;
                return;
            default:

                currentPage = Convert.ToInt32(
                    (sender as TextBlock).Text
                );
                return;
        }
    }
    private int productTypeFilterId = 0;

    private void ProductTypeFilter_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        productTypeFilterId = (ProductTypeFilter.SelectedItem as ProductType).ID;
        Invalidate();
    }
    private void ProductListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        if (ProductListBox != null)
        {
            productsSelectedCount = ProductListBox.SelectedItems.Count;
            CostChangeButton.Visibility = productsSelectedCount > 0 ? Visibility.Visible : Visibility.Collapsed;
        }
    }
    private void SearchFilter_KeyUp(object sender, KeyEventArgs e)
    {
        searchFilter = searchFilterTextBox.Text;
        Invalidate();
    }
    private int sortType = 0;
    private bool sortAsc = true;
    private void TextBlock_PreviewMouseDown(object sender, MouseButtonEventArgs e)
    {
        switch ((sender as TextBlock).Text)
        {
            case "<":

                if (currentPage > 1) currentPage--;
                return;
            case ">":

                if (currentPage < productCount / Globals.PAGE_LEN) currentPage++;
                return;
            default:

                currentPage = Convert.ToInt32(
                    (sender as TextBlock).Text
                );
                return;
        }
    }
    private void SortTypeComboBox_SelectionChanged(
object? sender,
SelectionChangedEventArgs e)
    {
        if (SortTypeComboBox != null)
        {
            sortType = SortTypeComboBox.SelectedIndex;
            Invalidate();
        }
    }
    private void SearchFilterTextBox_OnKeyUp(object? sender, KeyEventArgs e)
    {
        if (searchFilterTextBox.Text != null && searchFilterTextBox.Text != "")
        {
            searchFilter = searchFilterTextBox.Text;
            Invalidate();
        }
    }
    private void ExitButton_Click(object sender, RoutedEventArgs e)
    {
        Application.Current.Shutdown();
    }
    public string costChangeButtonVisible
    {
        get
        {
            return productsSelectedCount > 1 ? "Visible" : "Hidden";
        }
    }
}

}

MainWindow.xaml```

<Window x:Class="sql_pagining.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:sql_pagining"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
    <Window.Resources>
        <Style x:Key="VerticalListBoxStyle" TargetType="ListBox">
            <Setter Property="ItemsPanel">
                <Setter.Value>
                    <ItemsPanelTemplate>
                        <StackPanel Orientation="Vertical"/>
                    </ItemsPanelTemplate>
                </Setter.Value>
            </Setter>
            <Setter Property="ItemTemplate">
                <Setter.Value>
                    <DataTemplate>
                        <Border 
                            BorderThickness="2" 
                            BorderBrush="Black" 
                            CornerRadius="4" 
                            Margin="4"
                            Background="{Binding BackgroundColor}">
                            <Grid>
                                <Grid.RowDefinitions>
                                    <RowDefinition Height="Auto"/>
                                    <RowDefinition Height="Auto"/>
                                    <RowDefinition Height="*"/>
                                </Grid.RowDefinitions>
                                <Grid.ColumnDefinitions>
                                    <ColumnDefinition Width="64"/>
                                    <ColumnDefinition Width="*"/>
                                    <ColumnDefinition Width="auto"/>
                                </Grid.ColumnDefinitions>

                                <StackPanel Orientation="Horizontal" Grid.Row="0" Grid.Column="0" Grid.ColumnSpan="2">
                                    <Image Width="88" Height="88" Source="{Binding ImageBitmap}" />

                                    <StackPanel Orientation="Vertical" Margin="10,0,0,0">
                                        <StackPanel Orientation="Horizontal">
                                            <TextBlock Margin="0" Text="{Binding ProductTypeTitle}" FontSize="14"/>
                                            <TextBlock Margin="5,0" Text=" | " />
                                            <TextBlock Margin="0" Text="{Binding Title}" FontSize="14"/>

                                        </StackPanel>
                                        <TextBlock Margin="0" Text="{Binding ArticleNumber}" HorizontalAlignment="Left" FontSize="12" />

                                        <TextBlock Margin="0" Text="{Binding MaterialString}" FontSize="11"/>

                                    </StackPanel>


                                </StackPanel>
                                <TextBlock Margin="0,2,10,0" Grid.Column="2" Text="{Binding MaterialCost}" FontSize="13" HorizontalAlignment="Right"/>
                            </Grid>
                        </Border>
                    </DataTemplate>
                </Setter.Value>
            </Setter>
        </Style>
        <Style x:Key="TileListBoxStyle" TargetType="ListBox">
            <Setter Property="ItemsPanel">
                <Setter.Value>
                    <ItemsPanelTemplate>
                        <WrapPanel HorizontalAlignment="Center" ItemWidth="200"/>
                    </ItemsPanelTemplate>
                </Setter.Value>
            </Setter>
            <Setter Property="ItemTemplate">
                <Setter.Value>
                    <DataTemplate>
                        <Border BorderThickness="1" BorderBrush="Black" CornerRadius="5" Margin="5" Height="270">
                            <Grid>
                                <Grid.RowDefinitions>
                                    <RowDefinition Height="Auto"/>
                                    <RowDefinition Height="Auto"/>
                                    <RowDefinition Height="*"/>

                                </Grid.RowDefinitions>


                                <Image Margin="0,20,0,0" Width="120" Height="120" Source="{Binding ImageBitmap}" Grid.Row="0" HorizontalAlignment="Center" />


                                <StackPanel Grid.Row="1" Orientation="Vertical" HorizontalAlignment="Center">
                                    <TextBlock Text="{Binding ProductTypeTitle}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                    <TextBlock Text="{Binding Title}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                    <TextBlock Text="{Binding ArticleNumber}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                    <TextBlock Text="{Binding MaterialCost}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                    <TextBlock Text="{Binding MaterialString}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                </StackPanel>
                            </Grid>
                        </Border>

                    </DataTemplate>
                </Setter.Value>
            </Setter>
        </Style>
    </Window.Resources>

    <Grid ShowGridLines="True">
        <Grid.RowDefinitions>
            <RowDefinition Height="auto"/>
            <RowDefinition />
            <RowDefinition Height="auto"/>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="200"/>
            <ColumnDefinition/>
        </Grid.ColumnDefinitions>
        <Image 
        Source="/bin/Debug/net8.0-windows/Image/1.jpg" 
        Grid.RowSpan="2" HorizontalAlignment="Right"/>

        <ListBox
            Name="ProductListBox"
SelectionMode="Multiple"
SelectionChanged="ProductListBox_SelectionChanged"
    Style="{StaticResource VerticalListBoxStyle}"
    Grid.Row="1"
            Grid.Column="1"
    Background="White"
    ItemsSource="{Binding productList}"
            ScrollViewer.HorizontalScrollBarVisibility="Disabled" >
            <ListBox.ItemContainerStyle>
                <Style 
            TargetType="ListBoxItem">
                    <Setter 
                Property="HorizontalContentAlignment"
                Value="Stretch" />
                </Style>
            </ListBox.ItemContainerStyle>
        </ListBox>
        <StackPanel 
        Orientation="Vertical"
        Grid.RowSpan="3"
        VerticalAlignment="Bottom">
            <Button 
            x:Name="ExitButton"
            Content="Выход" 
            Click="ExitButton_Click"
            Height="50"/>


        </StackPanel>

        <WrapPanel
        Orientation="Horizontal"
        Grid.Column="1"
        MinHeight="50">
            <Button
    x:Name="CostChangeButton"
    Visibility="{Binding costChangeButtonVisible}"
    Click="CostChangeButton_Click"
    Content="Изменить стоимость на..."/>
            <ListBox Margin="5"
x:Name="PageListListBox"
ItemsSource="{Binding pageList}"
Grid.Column="1"
Grid.Row="2">


                <ListBox.ItemsPanel>
                    <ItemsPanelTemplate>
                        <WrapPanel 
            HorizontalAlignment="Right"></WrapPanel>
                    </ItemsPanelTemplate>
                </ListBox.ItemsPanel>

                <ListBox.ItemTemplate>
                    <DataTemplate>
                        <TextBlock
            Margin="5"
            Text="{Binding}" 
            PreviewMouseDown="TextBlock_PreviewMouseDown"/>
                    </DataTemplate>
                </ListBox.ItemTemplate>
            </ListBox>
            <ComboBox
Name="SortTypeComboBox"
SelectedIndex="0"
VerticalContentAlignment="Center"
Width="210" Height="30" Margin="10,0,10,0"
SelectionChanged="SortTypeComboBox_SelectionChanged"
ItemsSource="{Binding sortList}"/>
            <ComboBox
Width="120" Height="30"
x:Name="ProductTypeFilter"
SelectedIndex="0"
SelectionChanged="ProductTypeFilter_SelectionChanged"
ItemsSource="{Binding productTypeList}"/>
            <Label 
Content="Поиск" 
VerticalAlignment="Center"/>
            <TextBox
    Width="200"
    VerticalAlignment="Center"
    x:Name="searchFilterTextBox" 
    KeyUp="SearchFilterTextBox_OnKeyUp"/>
        </WrapPanel>

    </Grid>
</Window>

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using sql_pagining.models; using Dapper; using MySqlConnector;

namespace sql_pagining.models {

internal class DBDataProvider : IDataProvider
{
    private string searchFilter = "";
    private Dictionary<string, object> filters = new Dictionary<string, object>();
    private string orderCondition = "";
    static string connectionString = "Server=kolei.ru; User ID=sbahtina; Password=010906; Database=sbahtina";
    public IEnumerable<Product> getProduct(int pageNum)
    {
        using (MySqlConnection db = new MySqlConnection(connectionString))
        {
            var builder = new SqlBuilder();

            if (orderCondition.Length > 0)
                builder.OrderBy(orderCondition);

            if (filters.Count > 0)
            {
                foreach (var item in filters)
                    builder.Where(item.Key, item.Value);
            }


            var template = builder.AddTemplate(
                "SELECT * FROM ProductView /**where**/ /**orderby**/ LIMIT @pageLen OFFSET @offset",
                new { pageLen = Globals.PAGE_LEN, offset = (pageNum - 1) * Globals.PAGE_LEN }
            );

            return db.Query<Product>(
                template.RawSql,
                template.Parameters).ToList();
        }

    }
    public int getProductCount()
    {
        using (MySqlConnection db = new MySqlConnection(connectionString))
        {
            var builder = new SqlBuilder();
            if (filters.Count > 0)
            {
                foreach (var item in filters)
                {
                    builder.Where(item.Key, item.Value);
                }
            }
            var template = builder.AddTemplate(
                "SELECT count(*) FROM ProductView /**where**/");
            return db.QuerySingle<int>(
                template.RawSql,
                template.Parameters);
        }
    }
    public void setOrder(string condition)
    {
        orderCondition = condition;
    }
    public void addFilter(string name, object value)
    {
        filters.Add(name, value);
    }

    public void clearFilter()
    {
        filters.Clear();
    }

    public IEnumerable<ProductType> getProductTypes()
    {
        using (MySqlConnection db = new MySqlConnection(connectionString))
        {
            return db.Query<ProductType>("SELECT * FROM ProductType");
        }
    }
    public void setMinCostForAgent(decimal minCostForAgent, int[] ids)
    {
        using (MySqlConnection db = new MySqlConnection(connectionString))
        {
            db.Execute("UPDATE Product SET MinCostForAgent=@newCost WHERE ID in @idList",
            new
            {
                newCost = minCostForAgent,
                idList = ids
            });
        }
    }
}

}

DataProvider```

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using sql_pagining.models;
using Dapper;
using MySqlConnector;


namespace sql_pagining.models
{
    public interface IDataProvider
    {
        IEnumerable<Product> getProduct(int pageNum);
        int getProductCount();
        void setOrder(string condition);
        IEnumerable<ProductType> getProductTypes();
        void clearFilter();
        void addFilter(string v, object value);
        void setMinCostForAgent(decimal result, int[] ints);
    }
}

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using sql_pagining.models; using Dapper; using MySqlConnector;

namespace sql_pagining.models {

public class Product
{
    public decimal MinCostForAgent { get; set; }
    public int ID { get; set; }
    public required string Title { get; set; }
    public string? Image { get; set; }
    public int ProductTypeID { get; set; }
    public required string ProductTypeTitle { get; set; }
    public required string ArticleNumber { get; set; }
    public double? MaterialCost { get; set; }
    public string? MaterialString { get; set; }
    public int? LastMonthSaleQuantity { get; set; }
    public string BackgroundColor
    {
        get
        {
            if (LastMonthSaleQuantity == null || LastMonthSaleQuantity == 0) return "#ff97bb"; // белый
            return "#ffafcc"; // розовый
        }
    }
    public Uri? ImageBitmap
    {
        get
        {
            var imageName = Environment.CurrentDirectory + (Image ?? "");
            return System.IO.File.Exists(imageName) ? new Uri(imageName) : null;
        }
    }
}

}

EnterMinCostForAgentWindow.haml```

<Window x:Class="sql_pagining.Windows.EnterMinCostForAgentWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:sql_pagining.Windows"
        mc:Ignorable="d"
        Title="EnterMinCostForAgentWindow" Height="450" Width="800">
    <Grid>
        <StackPanel
    Orientation="Vertical" Margin="0,50,0,0">
            <TextBox
        Name="CostTextBox"/>
            <Button 
        Content="Изменить" Click="Button_Click"/>
        </StackPanel>
    </Grid>
</Window>

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Shapes;

namespace sql_pagining.Windows {

/// <summary>
/// Логика взаимодействия для EnterMinCostForAgentWindow.xaml
/// </summary>
public partial class EnterMinCostForAgentWindow : Window
{
    public decimal Result;
    public EnterMinCostForAgentWindow(decimal AvgCost)
    {
        InitializeComponent();
        CostTextBox.Text = AvgCost.ToString();
    }
    private void Button_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            // пробуем сконвертировать в число
            Result = Convert.ToDecimal(CostTextBox.Text);
            // при присвоении результата свойству DialogResult модальное окно закрывается
            DialogResult = true;
        }
        catch (Exception)
        {
            MessageBox.Show("Стоимость должна быть числом");
        }
    }
}

} ```