Дипломная работа: Построение системы формирования управленческой отчетности в коммерческом банке

string txt2 = String.Format("select JobTitle from [diploma].[dbo].[Staff] where Name = '{0}'", NameTBk.Text);

SqlCommand cmd2 = new SqlCommand(txt2, conn);

using (SqlDataReader dr = cmd2.ExecuteReader(CommandBehavior.CloseConnection))

{

while (dr.Read())

{

JobTitleTBk.Text = dr.GetValue(0).ToString().Trim();

}

}

}

private void StaffBtn_Click(object sender, RoutedEventArgs e)

{

if (JobTitleTBk.Text != "Консультант" && JobTitleTBk.Text != "Менеджер")

{

Staff st = new Staff();

st.Show();

}

else

{

MessageBox.Show("У вас нет прав на вход в этот раздел", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Stop);

}

}

private void AccountsBtn_Click(object sender, RoutedEventArgs e)

{

if (DepartmentTBk.Text != "Кредитных и дебетовых карт" && DepartmentTBk.Text != "Дирекция")

{

MessageBox.Show("У вас нет прав на вход в этот раздел", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Stop);

}

else

{

Accounts ac = new Accounts();

ac.Show();

}

}

private void OperationsBtn_Click(object sender, RoutedEventArgs e)

{

Operations op = new Operations();

op.Show();

}

private void ClientAnalysisBtn_Click(object sender, RoutedEventArgs e)

{

ClientAnalysis ca = new ClientAnalysis();

ca.Show();

}

private void StaffAnaBtn_Click_1(object sender, RoutedEventArgs e)

{

StaffAnalysis sa = new StaffAnalysis();

sa.Show();

}

}

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

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 otchet.Windows

{

/// <summary>

/// Логика взаимодействия для Accounts.xaml

/// </summary>

public partial class Accounts : Window

{

public Accounts()

{

InitializeComponent();

FillDataGrid();

}

private void FillDataGrid()

{

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("SELECT [Account_id] as 'ID счета',[AccountNum] as 'Номер счета',[Sum] as 'Сумма на счете',[DateBegin] as 'Дата открытия',[DateEnd] as 'Дата закрытия',[Status] as 'Статус',Client.Name as 'Имя клиента' FROM [diploma].[dbo].[Account], Client where Client.Client_id=Account.Client_id");

SqlCommand cmd = new SqlCommand(txt, conn);

SqlDataAdapter ada = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("Account");

ada.Fill(dt);

AccountDG.ItemsSource = dt.DefaultView;

conn.Close();

}

}

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

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 otchet.Windows

{

/// <summary>

/// Логика взаимодействия для ClientAnalysis.xaml

/// </summary>

public partial class ClientAnalysis : Window

{

public ClientAnalysis()

{

string result = "";

InitializeComponent();

//SqlConnection con = new SqlConnection(MainParams.ConnectionString);

//SqlCommand com = new NpgsqlCommand("select * from Users order by NAME", con);

//con.Open();

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("select Name from Client");

SqlCommand cmd = new SqlCommand(txt, conn);

using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))

{

while (dr.Read())

{

result = dr.GetValue(0).ToString().Trim();

ClientCBx.Items.Add(result);

}

}

conn.Close();

}

private void Analyze_Click(object sender, RoutedEventArgs e)

{

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("select SUM(Operations.SumGot) as 'Сумма сданных денег', SUM(Operations.SumGiven) as 'Сумма выданных денег' from Operations, Client where Client.Client_id=Operations.Client_id and Client.Name='{0}'", ClientCBx.SelectedItem.ToString());

SqlCommand cmd = new SqlCommand(txt, conn);

SqlDataAdapter ada = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("Sum");

ada.Fill(dt);

SumGetGivenDG.ItemsSource = dt.DefaultView;

string txt1 = String.Format("select Operation_id as 'Номер операции', dbo.Services.Name as 'Операция', SumGiven as 'Выданная сумма', SumGot as 'Полученная сумма', Procents as 'Проценты', Term as 'Срок', Operations.DateBegin as 'Дата начала', Operations.DateEnd as 'Дата конца', ContractNumber as 'Номер контракта', Info as 'Информация', Staff.Name as 'Имя сотрудника', Departments.Name as 'Название департамента', Quantity as 'Количество' from Departments, Operations, Staff, Services, Client where Departments.Department_id=Staff.Department_id and Staff.Staff_id=Operations.Staff_id and Services.Service_id=Operations.Service_id and Client.Client_id=Operations.Client_id and Client.Name = '{0}'", ClientCBx.SelectedItem.ToString());

SqlCommand cmd1 = new SqlCommand(txt1, conn);

SqlDataAdapter ada1 = new SqlDataAdapter(cmd1);

DataTable dt1 = new DataTable("Opers");

ada1.Fill(dt1);

OperationsAnalysisDG.ItemsSource = dt1.DefaultView;

conn.Close();

}

}

}

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

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;

using System.Configuration;

using System.Data;

namespace otchet.Windows

{

/// <summary>

/// Логика взаимодействия для Clients.xaml

/// </summary>

public partial class Clients : Window

{

public Clients()

{

InitializeComponent();

FillDataGrid();

}

private void FillDataGrid()

{

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("Select * from dbo.Client");

SqlCommand cmd = new SqlCommand(txt, conn);

SqlDataAdapter ada = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("Clients");

ada.Fill(dt);

ClientsDG.ItemsSource = dt.DefaultView;

conn.Close();

}

}

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

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 otchet.Windows

{

/// <summary>

/// Логика взаимодействия для Operations.xaml

/// </summary>

public partial class Operations : Window

{

public Operations()

{

InitializeComponent();

FillDataGrid();

}

private void FillDataGrid()

{

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("select Operation_id as 'Номер операции', dbo.Services.Name as 'Операция', SumGiven as 'Выданная сумма', SumGot as 'Полученная сумма', Procents as 'Проценты', Term as 'Срок', Operations.DateBegin as 'Дата начала', Operations.DateEnd as 'Дата конца', ContractNumber as 'Номер контракта', Info as 'Информация', Staff.Name as 'Имя сотрудника', Departments.Name as 'Название департамента', Client.Name as 'Имя клиента', Quantity as 'Количество' from Departments, Operations, Staff, Services, Client where Departments.Department_id=Staff.Department_id and Staff.Staff_id=Operations.Staff_id and Services.Service_id=Operations.Service_id and Client.Client_id=Operations.Client_id");

SqlCommand cmd = new SqlCommand(txt, conn);

SqlDataAdapter ada = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("Operations");

ada.Fill(dt);

OperationsDG.ItemsSource = dt.DefaultView;

conn.Close();

}

}

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

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 otchet.Windows

{

/// <summary>

/// Логика взаимодействия для Staff.xaml

/// </summary>

public partial class Staff : Window

{

public Staff()

{

InitializeComponent();

FillDataGrid();

}

private void FillDataGrid()

{

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("SELECT [Staff_id] as 'ID Сотрудника',Staff.[Name] as 'ФИО Сотрудника',[EmploymentDate] as 'Дата взятия на работу',[BirthDate] as 'День рождения',[Telephone] as 'Телефон',[Email] as 'Электронная почта',[City] as 'Город',[Address] as 'Адрес', [JobTitle] as 'Должность', dbo.Departments.Name 'Отдел' FROM [diploma].[dbo].[Staff], Departments where Departments.Department_id=Staff.Department_id");

SqlCommand cmd = new SqlCommand(txt, conn);

SqlDataAdapter ada = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("Staff");

ada.Fill(dt);

StaffDG.ItemsSource = dt.DefaultView;

conn.Close();

}

}

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

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 otchet.Windows

{

/// <summary>

/// Логика взаимодействия для StaffAnalysis.xaml

/// </summary>

public partial class StaffAnalysis : Window

{

public StaffAnalysis()

{

InitializeComponent();

string result = "";

InitializeComponent();

//SqlConnection con = new SqlConnection(MainParams.ConnectionString);

//SqlCommand com = new NpgsqlCommand("select * from Users order by NAME", con);

//con.Open();

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("select Name from Staff");

SqlCommand cmd = new SqlCommand(txt, conn);

using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))

{

while (dr.Read())

{

result = dr.GetValue(0).ToString().Trim();

StaffCBx.Items.Add(result);

}

}

conn.Close();

}

private void Analyze_Click(object sender, RoutedEventArgs e)

{

string connStr = @"Data Source=DESKTOP-R33Q3A8\SAVLSERVER;

Initial Catalog=diploma;

Integrated Security=True";

SqlConnection conn = new SqlConnection(connStr);

try

{

conn.Open();

}

catch (SqlException se)

{

MessageBox.Show("Ошибка подключения:{0}", se.Message);

return;

}

string txt = String.Format("select SUM(Operations.SumGot) as 'Сумма сданных денег', SUM(Operations.SumGiven) as 'Сумма выданных денег' from Operations, Staff where Staff.Staff_id=Operations.Staff_id and Staff.Name='{0}'", StaffCBx.SelectedItem.ToString());

SqlCommand cmd = new SqlCommand(txt, conn);

SqlDataAdapter ada = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("sum");

ada.Fill(dt);

SumGetGivenDG1.ItemsSource = dt.DefaultView;

string txt1 = String.Format("select Operation_id as 'Номер операции', dbo.Services.Name as 'Операция', SumGiven as 'Выданная сумма', SumGot as 'Полученная сумма', Procents as 'Проценты', Term as 'Срок', Operations.DateBegin as 'Дата начала', Operations.DateEnd as 'Дата конца', ContractNumber as 'Номер контракта', Info as 'Информация', Client.Name as 'Имя клиента', Departments.Name as 'Название департамента', Quantity as 'Количество' from Departments, Operations, Staff, Services, Client where Departments.Department_id=Staff.Department_id and Staff.Staff_id=Operations.Staff_id and Services.Service_id=Operations.Service_id and Client.Client_id=Operations.Client_id and Staff.Name = '{0}'", StaffCBx.SelectedItem.ToString());

SqlCommand cmd1 = new SqlCommand(txt1, conn);

SqlDataAdapter ada1 = new SqlDataAdapter(cmd1);

DataTable dt1 = new DataTable("Opers");

ada1.Fill(dt1);

OperationsAnalysisDG1.ItemsSource = dt1.DefaultView;

conn.Close();

}

}

}