Общая характеристика ГБУЗ ПК «Поликлиника №5»

ExecuteReader();while (read.Read()) {TheList.Add(newBoolStringClass{ IsSelected = false, TheText = read["name"].ToString() }); }lb.ItemsSource = TheList;read.Close();lb.DataContext = this;this.DataContext = this; }publicstaticvoidIncreaseLength(refstring[] arr, int delta) {string[] tmp = newstring[arr.Length + 1];Array.Copy(arr, 0, tmp, 0, arr.Length);arr = tmp; }publicclassBoolStringClass {publicstringTheText{ get; set; }publicboolIsSelected{ get; set; } }privatevoidClientAdd(object sender, RoutedEventArgs e) {stringvrach = "SELECT id FROM Worker WHERE fio ='" + cmVrach.SelectedValue.ToString() + "'";SqlCommandcmd_vrach = newSqlCommand(vrach, con1);stringvrach_idf = cmd_vrach.ExecuteScalar().ToString();intvrach_id = int.Parse(vrach_idf);stringpac = "SELECT id FROM Client WHERE fio ='" + cmPac.SelectedValue.ToString() + "'";SqlCommandcmd_pac = newSqlCommand(pac, con1);stringpac_idf = cmd_pac.ExecuteScalar().ToString();intpac_id = int.Parse(pac_idf);stringbolezn = "SELECT id FROM Bolezn WHERE name ='" + cmBolezn.SelectedValue.ToString() + "'";SqlCommandcmd_bolezn = newSqlCommand(bolezn, con1);stringbolezn_idf = cmd_bolezn.ExecuteScalar().ToString();intbolezn_id = int.Parse(bolezn_idf);controller.Priem_Add(pac_id, vrach_id, dat.SelectedDate.Value.Date, bolezn_id, opis.Text);foreach (BoolStringClass item inlb.Items) {if (item.IsSelected == true) {stringname_query = "SELECT id FROM Preparat WHERE name ='" + item.TheText + "'";SqlCommandcmd_name = newSqlCommand(name_query, con1);stringname_idf = cmd_name.ExecuteScalar().ToString();intname_id = int.Parse(name_idf);controller.NaznPrepar_Add(name_id); } }MessageBox.Show("Добавлено");grid.ItemsSource = controller.Priem_Update().DefaultView;opis.Clear(); } }}privatevoidWinLoad(object sender, RoutedEventArgs e) { controller = newQuery(ConnectionString.ConnStr);grid.ItemsSource = controller.Preparat_Update().DefaultView;grid.Columns[0].Header = "Код";grid.Columns[1].Header = "Название"; }privatevoidSpecialAdd(object sender, RoutedEventArgs e) {if (tbName.Text.Trim().Length == 0) {MessageBox.Show("Некорректныйввод"); }else {controller.Preparat_Add(tbName.Text);MessageBox.Show("Добавлено");grid.ItemsSource = controller.Preparat_Update().DefaultView;tbName.Clear(); } }publicpartialclassOthet1Win : Window { Query controller;SqlConnection con1 = newSqlConnection(ConfigurationManager.ConnectionStrings["MED.Properties.Settings.ConnStr"].ConnectionString);publicOthet1Win() {InitializeComponent(); }privatevoidWinLoad(object sender, RoutedEventArgs e) { con1.Open(); controller = newQuery(ConnectionString.ConnStr);grid.ItemsSource = controller.Priem_Update().DefaultView;SqlCommand command = newSqlCommand("SELECT fio FROM Client", con1);SqlDataAdapter adapter = newSqlDataAdapter(command);SqlDataReader reader = command.ExecuteReader();DataTable data = newDataTable();data.Columns.Add("fio", typeof(string));data.Load(reader);cmPac.DisplayMemberPath = "fio";cmPac.SelectedValuePath = "fio";cmPac.ItemsSource = data.DefaultView; }privatevoidClientAdd(object sender, RoutedEventArgs e) {stringysl = cmPac.SelectedValue.ToString();string query = "SELECT * FROM View_priem WHERE Пациент ='" + ysl + "'";SqlDataAdapter command = newSqlDataAdapter(query, con1);DataTable dt = newDataTable();command.Fill(dt);grid.ItemsSource = dt.DefaultView; }privatevoidExc(object sender, RoutedEventArgs e) {Excel.Application excel = newExcel.Application();excel.Visible = true;Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);Excel.Worksheetwsh = (Excel.Worksheet)workbook.Sheets[1];for (inti = 1; i < grid.Columns.Count + 1; i++) {wsh.Cells[2, i] = grid.Columns[i - 1].Header; }for (inti = 0; i <= grid.Columns.Count - 1; i++) {for (int j = 0; j <= grid.Items.Count - 1; j++) {TextBlock b = grid.Columns[i].GetCellContent(grid.Items[j]) asTextBlock;Microsoft.Office.Interop.Excel.RangemyRange = (Microsoft.Office.Interop.Excel.Range)wsh.Cells[j + 3, i + 1]; myRange.Value2 = b.Text.Trim(); } }wsh.Cells[1, 1] = "Историяболезней";wsh.Cells[2, 2].CurrentRegion.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //границыwsh.Rows[2].Font.Bold = true; //вся 2-я строка становится Жирным шрифтомwsh.Rows[1].Font.Bold = true; //вся 1-я строка становится Жирным шрифтомwsh.Rows[1].Style.HorizontalAlignment = HorizontalAlignment.Center; //горизонтальное выравнивание по центруwsh.Range["A:H"].EntireColumn.AutoFit(); //автоподбор по ширине столбцовexcel.Visible = true; } }publicpartialclassOtchet3Win : Window { Query controller;SqlConnection con1 = newSqlConnection(ConfigurationManager.ConnectionStrings["MED.Properties.Settings.ConnStr"].ConnectionString);publicOtchet3Win() {InitializeComponent(); }privatevoidWinLoad(object sender, RoutedEventArgs e) { con1.Open(); }privatevoidClientAdd(object sender, RoutedEventArgs e) {DateTime date = dat1.SelectedDate.Value.Date;string str = date.ToString();stringsubString = str.Substring(6, 4);string subString1 = str.Substring(3, 2);string subString2 = str.Substring(0, 2);string res = subString + '-' + subString1 + '-' + subString2; date = dat2.SelectedDate.Value.Date; str = date.ToString();subString = str.Substring(6, 4); subString1 = str.Substring(3, 2); subString2 = str.Substring(0, 2);string res1 = subString + '-' + subString1 + '-' + subString2;string query = "SELECT dbo.Worker.fio, COUNT(dbo.Priem.id_worker) AS Количество FROM dbo.Worker INNER JOIN dbo.Priem ON dbo.Worker.id = dbo.Priem.id_worker WHERE((dbo.Priem.dat >= '" + res + "') AND (dbo.Priem.dat <= '" + res1 + "')) GROUP BY dbo.Worker.fio ";SqlDataAdapter command = newSqlDataAdapter(query, con1);System.Data.DataTable dt = newSystem.Data.DataTable();command.Fill(dt);grid.ItemsSource = dt.DefaultView; }privatevoidExc(object sender, RoutedEventArgs e) {Excel.Application excel = newExcel.Application();excel.Visible = true;Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);Excel.Worksheetwsh = (Excel.Worksheet)workbook.Sheets[1];for (inti = 1; i < grid.Columns.Count + 1; i++) {wsh.Cells[2, i] = grid.Columns[i - 1].Header; }for (inti = 0; i <= grid.Columns.Count - 1; i++) {for (int j = 0; j <= grid.Items.Count - 1; j++) {TextBlock b = grid.Columns[i].GetCellContent(grid.Items[j]) asTextBlock;Microsoft.Office.Interop.Excel.RangemyRange = (Microsoft.Office.Interop.Excel.Range)wsh.Cells[j + 3, i + 1]; myRange.Value2 = b.Text.Trim(); } }wsh.Cells[1, 1] = "Историяболезней";wsh.Cells[2, 2].CurrentRegion.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //границыwsh.Rows[2].Font.Bold = true; //вся 2-я строка становится Жирным шрифтомwsh.Rows[1].Font.Bold = true; //вся 1-я строка становится Жирным шрифтомwsh.Rows[1].Style.HorizontalAlignment = HorizontalAlignment.Center; //горизонтальное выравнивание по центруwsh.Range["A:H"].EntireColumn.AutoFit(); //автоподбор по ширине столбцовexcel.Visible = true; } }public partial class Otchet2Win : Window { Query controller;SqlConnection con1 = newSqlConnection(ConfigurationManager.ConnectionStrings["MED.Properties.Settings.ConnStr"].ConnectionString);public Otchet2Win() {InitializeComponent(); }privatevoidWinLoad(object sender, RoutedEventArgs e) { con1.Open(); }privatevoidClientAdd(object sender, RoutedEventArgs e) {DateTime date = dat1.SelectedDate.Value.Date;string str = date.ToString();stringsubString = str.Substring(6, 4);string subString1 = str.Substring(3, 2);string subString2 = str.Substring(0, 2);string res = subString + '-' + subString1 + '-' + subString2 ; date = dat2.SelectedDate.Value.Date; str = date.ToString();subString = str.Substring(6, 4); subString1 = str.Substring(3, 2); subString2 = str.Substring(0, 2);string res1 = subString + '-' + subString1 + '-' + subString2 ;string query = "SELECT dbo.Bolezn.name AS Болезнь, COUNT(dbo.Priem.id_bolezn) AS Количество FROM dbo.Bolezn INNER JOIN dbo.Priem ON dbo.Bolezn.id = dbo.Priem.id_bolezn WHERE ((dbo.Priem.dat >= '"+res+ "') AND (dbo.Priem.dat <= '" + res1 + "')) GROUP BY dbo.Bolezn.name ";SqlDataAdapter command = newSqlDataAdapter(query, con1);System.Data.DataTable dt = newSystem.Data.DataTable();command.Fill(dt);grid.ItemsSource = dt.DefaultView; }privatevoidExc(object sender, RoutedEventArgs e) {Excel.Application excel = newExcel.Application();excel.Visible = true;Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);Excel.Worksheetwsh = (Excel.Worksheet)workbook.Sheets[1];for (inti = 1; i < grid.Columns.Count + 1; i++) {wsh.Cells[2, i] = grid.Columns[i - 1].Header; }for (inti = 0; i <= grid.Columns.Count - 1; i++) {for (int j = 0; j <= grid.Items.Count - 1; j++) {TextBlock b = grid.Columns[i].GetCellContent(grid.Items[j]) asTextBlock;Microsoft.Office.Interop.Excel.RangemyRange = (Microsoft.Office.Interop.Excel.Range)wsh.Cells[j + 3, i + 1]; myRange.Value2 = b.Text.Trim(); } }wsh.Cells[1, 1] = "Историяболезней";wsh.Cells[2, 2].CurrentRegion.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //границыwsh.Rows[2].Font.Bold = true; //вся 2-я строка становится Жирным шрифтомwsh.Rows[1].Font.Bold = true; //вся 1-я строка становится Жирным шрифтомwsh.Rows[1].Style.HorizontalAlignment = HorizontalAlignment.Center; //горизонтальное выравнивание по центруwsh.Range["A:H"].EntireColumn.AutoFit(); //автоподбор по ширине столбцовexcel.Visible = true; } }privatevoidWinLoad(object sender, RoutedEventArgs e) { controller = newQuery(ConnectionString.ConnStr);grid.ItemsSource = controller.Client_Update().DefaultView;grid.Columns[0].Header = "Код";grid.Columns[1].Header = "ФИО";grid.Columns[2].Header = "Адрес";grid.Columns[3].Header = "Датарождения"; }privatevoidClientAdd(object sender, RoutedEventArgs e) {if ((tbName.Text.Trim().Length == 0)|| (tbAddress.Text.Trim().Length == 0)|| (dat.DisplayDate == null)) {MessageBox.Show("Некорректныйввод"); }else {controller.Client_Add(tbName.Text, tbAddress.Text, dat.DisplayDate.Date);MessageBox.Show("Добавлено");grid.ItemsSource = controller.Client_Update().DefaultView;tbName.Clear();tbAddress.Clear(); } }privatevoidWinLoad(object sender, RoutedEventArgs e) { controller = newQuery(ConnectionString.ConnStr);grid.ItemsSource = controller.Bolezn_Update().DefaultView;grid.Columns[0].Header = "Код";grid.Columns[1].Header = "Название"; }privatevoidSpecialAdd(object sender, RoutedEventArgs e) {if (tbName.Text.Trim().Length == 0) {MessageBox.Show("Некорректныйввод"); }else {controller.Bolezn_Add(tbName.Text);MessageBox.Show("Добавлено");grid.ItemsSource = controller.Bolezn_Update().DefaultView;tbName.Clear(); } }Приложение 2Скрипты генерации БДCREATETABLE[dbo].[Bolezn]([id][int]NOTNULL,[name][nchar](100)NULL,CONSTRAINT[PK_Bolezn]PRIMARYKEYCLUSTERED([id]ASC)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOCREATETABLE[dbo].[Client]([id][int]NOTNULL,[fio][nchar](100)NOTNULL,[address][nchar](100)NOTNULL,[birthday][date]NOTNULL,CONSTRAINT[PK_Client]PRIMARYKEYCLUSTERED([id]ASC)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOCREATETABLE[dbo].[NaznPrepar]([id][int]NOTNULL,[id_priem][int]NOTNULL,[id_prepar][int]NOTNULL,CONSTRAINT[PK_NaznPrepar]PRIMARYKEYCLUSTERED([id]ASC)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOCREATETABLE[dbo].[Preparat]([id][int]NOTNULL,[name][nchar](100)NOTNULL,CONSTRAINT[PK_Preparat]PRIMARYKEYCLUSTERED([id]ASC)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]CREATETABLE[dbo].[Priem]([id][int]NOTNULL,[id_client][int]NOTNULL,[id_worker][int]NOTNULL,[dat][date]NOTNULL,[id_bolezn][int]NULL,[opis][nchar](300)NULL,CONSTRAINT[PK_Priem]PRIMARYKEYCLUSTERED([id]ASC)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOCREATETABLE[dbo].[Special]([id][int]NOTNULL,[name][nchar](100)NOTNULL,CONSTRAINT[PK_Special]PRIMARYKEYCLUSTERED([id]ASC)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GO