ITTerminal/CardManager.cs at master · ilshatus/ITTerminal · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
using System.Data.SqlClient;
using System.Configuration;
using System;

namespace ITTerminal
{
    class CardManager
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        private static string sqlExpression1 = "SELECT TOP 1 [surname_ru],[name_ru],[sname_ru],[position],[status],[Card ID] FROM ["+ConfigurationManager.ConnectionStrings["tableName"].ConnectionString+"].[dbo].[data] WHERE [Card ID] = @card_id";
        private static string sqlExpression2 = "SELECT TOP 1 [surname_ru],[name_ru],[sname_ru],[position],[status],[Card ID] FROM ["+ConfigurationManager.ConnectionStrings["tableName"].ConnectionString+"].[dbo].[data] WHERE [surname_ru] = @surname AND [name_ru] = @name";
        private static string sqlExpression3 = "SELECT TOP 1 [surname_ru],[name_ru],[sname_ru],[position],[status],[Card ID] FROM ["+ConfigurationManager.ConnectionStrings["tableName"].ConnectionString+"].[dbo].[data] WHERE [surname_ru] = @surname AND [name_ru] = @name AND [sname_ru] = @sname";


        //Returns User or null.
        //Returns null, if card owner isn't found or if card owner isn't worker or student now.
        public static User getUser(string card_id)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sqlExpression1, connection);
                SqlParameter nameParam = new SqlParameter("@card_id", card_id);
                command.Parameters.Add(nameParam);
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();

                    string surname_ru = reader.GetString(0);
                    string name_ru = reader.GetString(1);
                    object sname_ru = reader.GetValue(2);
                    string position = reader.GetString(3);
                    string status = reader.GetString(4);

                    if (status.Equals("Действует") && (position.Contains("Сотрудник") || position.Contains("Студент")))
                    {
                        if (sname_ru != null && !sname_ru.Equals(""))
                            return new User(surname_ru + " " + name_ru + " " + sname_ru, card_id, position);
                        else return new User(surname_ru + " " + name_ru, card_id, position);
                    }
                }
                reader.Close();
                connection.Close();
            }
            return null;
        }

        //Returns User or null.
        //Returns null, if card owner isn't found or if card owner isn't worker or student now.
        public static User getUser(string surname, string name, string sname)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command;
                if (sname.Equals(""))
                    command = new SqlCommand(sqlExpression2, connection);
                else
                    command = new SqlCommand(sqlExpression3, connection);

                SqlParameter param = new SqlParameter("@surname", surname);
                command.Parameters.Add(param);
                param = new SqlParameter("@name", name);
                command.Parameters.Add(param);
                param = new SqlParameter("@sname", sname);
                command.Parameters.Add(param);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();

                    string surname_ru = reader.GetString(0);
                    string name_ru = reader.GetString(1);
                    object sname_ru = reader.GetValue(2);
                    string position = reader.GetString(3);
                    string status = reader.GetString(4);
                    string card_id = reader.GetString(5);

                    if (status.Equals("Действует") && (position.Contains("Сотрудник") || position.Contains("Студент")))
                    {
                        if (position.Contains("Сотрудник")) position = "Сотрудник";
                        if (position.Contains("Студент")) position = "Студент";
                        if (sname_ru != null && !sname_ru.Equals(""))
                            return new User(surname_ru + " " + name_ru + " " + sname_ru, card_id, position);
                        else return new User(surname_ru + " " + name_ru, card_id, position);
                    }
                }
                reader.Close();
                connection.Close();
            }
            return null;
        }
    }
}