ВКР_Макаревич П.Е (1218757), страница 6

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 6)

Файл конфигурации соединения:

<?xml version="1.0"?>

<configuration>

<startup><supportedRuntime version="v2.0.50727"/></startup>

<connectionStrings>

<add name="polyclinic" connectionString="

server=MUSHROOM\SQLEXPRESS;

Trusted_Connection=yes;

database=webclinic;

connection timeout=5" />

<add name="medialog" connectionString="server=10.10.56.241,1433\SQLSERV\MEDIALOG;

user='sa';

password='1';

Trusted_Connection=false;

database=webclinic;

connection timeout=10" />

</connectionStrings>

</configuration>

Реализация функции MD5 хэширования, которое используется для хранения паролей пользователей системы:

using System;

using System.Collections.Generic;

using System.Text;

using System.Security.Cryptography;

namespace PolyClinic

{

static class Hash

{

static public string CalculateMD5Hash(string input)

{

MD5 md5 = MD5.Create();

byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);

byte[] hash = md5.ComputeHash(inputBytes);

StringBuilder sb = new StringBuilder();

for (int i = 0; i < hash.Length; i++)

{

sb.Append(hash[i].ToString("x2"));

}

return sb.ToString();

}

}

}

Авторизация и получение прав доступа пользователя:

SqlConnection connection = Connection.connection;

try

{

connection.Open();

string query = "SELECT COUNT(*) FROM users WHERE username=@user and password=@pass";

SqlCommand checkUsers = new SqlCommand(query, connection);

checkUsers.Parameters.AddWithValue("user", username);

checkUsers.Parameters.AddWithValue("pass", password);

int selectedCount = (int)checkUsers.ExecuteScalar();

connection.Close();

if (selectedCount == 0)

{

labelMessage.Text = "Имя пользователя или пароль введены неверно!";

tbPassword.Text = "";

return;

}

Connection.user = username;

Connection.passHash = password;

connection.Open();

query = "SELECT * FROM users WHERE username=@user";

SqlCommand takeUser = new SqlCommand(query, connection);

takeUser.Parameters.AddWithValue("user", username);

SqlDataReader dataReader = takeUser.ExecuteReader();

dataReader.Read();

Connection.controlUsers = Convert.ToInt16(dataReader["controlusers"]) == 1 ? true : false;

Connection.controlArchiveRead = Convert.ToInt16(dataReader["controlarchiveread"]) == 1 ? true : false;

Connection.controlArchiveWrite = Convert.ToInt16(dataReader["controlarchivewrite"]) == 1 ? true : false;

Connection.controlNewbornsRead = Convert.ToInt16(dataReader["controlnewbornsread"]) == 1 ? true : false;

Connection.controlNewbornsWrite = Convert.ToInt16(dataReader["controlnewbornswrite"]) == 1 ? true : false;

Connection.controlInfsRead = Convert.ToInt16(dataReader["controlinfsread"]) == 1 ? true : false;

Connection.controlInfsWrite = Convert.ToInt16(dataReader["controlinfswrite"]) == 1 ? true : false;

Connection.controlAddresser = Convert.ToInt16(dataReader["controladdresser"]) == 1 ? true : false;

dataReader.Close();

connection.Close();

this.Close();

}

Построение отчета в Excel по архиву карт:

using (SqlConnection connection = Connection.connection)

{

connection.Open();

string query = "SELECT * FROM archive ORDER BY birthdate ASC";

SqlCommand takeArchive = new SqlCommand(query, connection);

SqlDataReader dataReader = takeArchive.ExecuteReader();

int counter = 0;

dataReader.Read();

int[] years = new int[100];

years[counter] = Convert.ToDateTime(dataReader["birthdate"]).Year;

while (dataReader.Read())

{

if (Convert.ToDateTime(dataReader["birthdate"]).Year != years[counter])

{

counter++;

years[counter] = Convert.ToDateTime(dataReader["birthdate"]).Year;

}

}

dataReader.Close();

excelapp = new Microsoft.Office.Interop.Excel.Application();

excelapp.SheetsInNewWorkbook = counter + 1;

excelapp.Workbooks.Add(Type.Missing);

int i = 0;

while (years[i] != 0)

{

(excelapp.Sheets[i + 1] as Worksheet).Name = years[i].ToString();

i++;

}

excelapp.Visible = true;

query = "SELECT COUNT(*) FROM archive";

SqlCommand check = new SqlCommand(query, connection);

int selectedCount = (int)check.ExecuteScalar();

pbReports.Value = 0;

pbReports.Maximum = selectedCount;

query = "SELECT * FROM types";

SqlCommand takeTypes = new SqlCommand(query, connection);

dataReader = takeTypes.ExecuteReader();

counter = 0;

while (dataReader.Read())

{

Connection.types[Convert.ToInt32(dataReader["id"])] = dataReader["name"].ToString().Trim();

}

dataReader.Close();

query = "SELECT * FROM streets ORDER BY name ASC";

SqlCommand takeStreets = new SqlCommand(query, connection);

dataReader = takeStreets.ExecuteReader();

while (dataReader.Read())

{

Connection.streets[Convert.ToInt32(dataReader["id"])] = string.Format("{0} {1}", dataReader["name"].ToString().Trim(), Connection.types[Convert.ToInt32(dataReader["type"])]);

}

dataReader.Close();

Excel.Worksheet excelworksheet;

for (int j = 0; j < counter; j++)

{

excelworksheet = (Excel.Worksheet)excelapp.Sheets[j + 1];

Range excelcells;

excelcells = excelworksheet.get_Range("A1", Type.Missing);

excelcells.Value = "Фамилия";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Имя";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Отчество";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Дата рождения";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Домашний адрес";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Выдана ли карта";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Когда выдана";

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = "Кому выдана";

excelcells = excelcells.get_Offset(1, -7);

query = "SELECT * FROM archive ORDER BY lastname ASC, firstname ASC, secondname ASC, birthdate ASC";

takeArchive = new SqlCommand(query, connection);

dataReader = takeArchive.ExecuteReader();

counter = 0;

while (dataReader.Read())

{

if (Convert.ToDateTime(dataReader["birthdate"]).Year == years[j])

{

excelcells.Value = dataReader["lastname"].ToString().Trim();

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = dataReader["firstname"].ToString().Trim();

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = dataReader["secondname"].ToString().Trim();

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = Convert.ToDateTime(dataReader["birthdate"]).Date.ToString("dd.MM.yyyy");

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = string.Format("{0}, дом {1}, кв {2}", Connection.streets[Convert.ToInt32(dataReader["street"])], dataReader["house"].ToString().Trim(), dataReader["flat"].ToString().Trim());

excelcells = excelcells.get_Offset(0, 1);

if (Convert.ToBoolean(dataReader["isout"]))

{

excelcells.Value = "да";

excelcells = excelcells.get_Offset(0, 1);

try

{

excelcells.Value = Convert.ToDateTime(dataReader["outdate"]).Date.ToString("dd.MM.yyyy");

}

catch

{

}

excelcells = excelcells.get_Offset(0, 1);

excelcells.Value = dataReader["whoout"].ToString().Trim();

}

else

{

excelcells.Value = "нет";

excelcells = excelcells.get_Offset(0, 1);

excelcells = excelcells.get_Offset(0, 1);

}

counter++;

excelcells = excelcells.get_Offset(1, -7);

pbReports.Value++;

}

}

dataReader.Close();

}

}

Характеристики

Список файлов ВКР