/AutoSzkola/AutoSzkola/DataManager/KursManager.cs
C# | 1445 lines | 1260 code | 157 blank | 28 comment | 419 complexity | 75f859f701dd6af039069451ea59a84e MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Windows.Forms;
- using System.Data.SQLite;
-
- namespace EE4IT.AutoSzkola
- {
- public class KursManager : DbManager
- {
- #region KURSY
-
- /// <summary>
- /// Funkcja pobiera listę obiektów typu Kurs
- /// </summary>
- /// <param name="typKursuId">ID typu kursu dla którego wyciągana jest lista ustawień godzinowo kosztowych </param>
- /// <param name="_c">Jeżeli ten parametr ma wartość NULL wtedy connection zostanie stworzone</param>
- /// <returns>listę obiektów typu KursTypData zawierającą ustawienia godzinowo kosztowe danego typu kurs</returns>
- public List<Kurs> GetKursyListByUserId(long kursantId, SQLiteConnection _c)
- {
- List<Kurs> list = new List<Kurs>();
- bool ownConnection = false;
- SQLiteConnection c;
-
- if (_c == null)
- {
- c = GetConnection();
- ownConnection = false;
- }
- else
- {
- c = _c;
- ownConnection = true;
- }
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = @"select id,kursantId,typKursuId,dataDodania,uzytkownikDodajacy,cena
- , rata1data,rata2data,rata3data,rata4data
- , rata1wpowadzajacyId, rata2wpowadzajacyId,rata3wpowadzajacyId,rata4wpowadzajacyId
- , rata1kwota,rata2kwota,rata3kwota,rata4kwota
- , aktywny
- , rata1gotowka,rata2gotowka,rata3gotowka,rata4gotowka
- , instruktorId, nrZaswiadczenia
- , rata5data,rata6data,rata7data,rata8data,rata9data,rata10data
- , rata5wpowadzajacyId, rata6wpowadzajacyId,rata7wpowadzajacyId,rata8wpowadzajacyId,rata9wpowadzajacyId,rata10wpowadzajacyId
- , rata5kwota,rata6kwota,rata7kwota,rata8kwota,rata9kwota,rata10kwota
- , rata5gotowka,rata6gotowka,rata7gotowka,rata8gotowka,rata9gotowka,rata10gotowka
- , w1_data, w1_osoba, w2_data, w2_osoba, w3_data, w3_osoba
-
- from Kurs where kursantId = " + kursantId;
-
-
- KursantManager km = new KursantManager();
- UserManager u = new UserManager();
-
- SQLiteDataReader reader = command.ExecuteReader();
- while (reader.Read())
- {
- Kurs k = new Kurs();
- k.Id = reader.GetInt64(0);
- k.KursantId = reader.GetInt64(1);
- k.TypKursu = GetTypKursuById(reader.GetInt64(2),c);
- k.DataDodania = reader.IsDBNull(3) || reader.GetString(3) == "" ? DateTime.MaxValue : reader.GetDateTime(3);
- k.UzytkownikDodajacy = u.GetUserById(reader.GetInt64(4),c);
- k.Cena = reader.GetDouble(5);
- k.Rata1Data = reader.IsDBNull(6) || reader.GetString(6) == "" ? DateTime.MaxValue : reader.GetDateTime(6);
- k.Rata2Data = reader.IsDBNull(7) || reader.GetString(7) == "" ? DateTime.MaxValue : reader.GetDateTime(7);
- k.Rata3Data = reader.IsDBNull(8) || reader.GetString(8) == "" ? DateTime.MaxValue : reader.GetDateTime(8);
- k.Rata4Data = reader.IsDBNull(9) || reader.GetString(9) == "" ? DateTime.MaxValue : reader.GetDateTime(9);
- k.Rata1Wprowadzajacy = u.GetUserById(reader.IsDBNull(10) ? 0 : reader.GetInt64(10),c);
- k.Rata2Wprowadzajacy = u.GetUserById(reader.IsDBNull(11) ? 0 : reader.GetInt64(11), c);
- k.Rata3Wprowadzajacy = u.GetUserById(reader.IsDBNull(12) ? 0 : reader.GetInt64(12), c);
- k.Rata4Wprowadzajacy = u.GetUserById(reader.IsDBNull(13) ? 0 : reader.GetInt64(13), c);
- k.Rata1Kwota = reader.IsDBNull(14) ? 0 : reader.GetDouble(14);
- k.Rata2Kwota = reader.IsDBNull(15) ? 0 : reader.GetDouble(15);
- k.Rata3Kwota = reader.IsDBNull(16) ? 0 : reader.GetDouble(16);
- k.Rata4Kwota = reader.IsDBNull(17) ? 0 : reader.GetDouble(17);
- k.Aktywny = reader.GetBoolean(18);
- k.Rata1Gotowka = reader.GetBoolean(19);
- k.Rata2Gotowka = reader.GetBoolean(20);
- k.Rata3Gotowka = reader.GetBoolean(21);
- k.Rata4Gotowka = reader.GetBoolean(22);
-
- long instruktorId = reader.IsDBNull(23) ? 0 : reader.GetInt64(23);
- InstruktorManager im = new InstruktorManager();
- k.Instruktor = im.GetInstruktorById(instruktorId,c);
-
- k.NumerZaswiadczenia = reader.IsDBNull(24) ? "" : reader.GetString(24);
-
- int i = 25;
-
- k.Rata5Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata6Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata7Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata8Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata9Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata10Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata5Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata6Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata7Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata8Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata9Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata10Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata5Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata6Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata7Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata8Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata9Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata10Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata5Gotowka = reader.GetBoolean(i);
- i++; k.Rata6Gotowka = reader.GetBoolean(i);
- i++; k.Rata7Gotowka = reader.GetBoolean(i);
- i++; k.Rata8Gotowka = reader.GetBoolean(i);
- i++; k.Rata9Gotowka = reader.GetBoolean(i);
- i++; k.Rata10Gotowka = reader.GetBoolean(i);
-
- i++; k.Wydruk_1_data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Wydruk_1_Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Wydruk_2_data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Wydruk_2_Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Wydruk_3_data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Wydruk_3_Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
-
- k.GodzinyTeorii = GetGodzinyForKurs(k, GodzinaTyp.TEORIA, false,c);
- k.GodzinyJazdy = GetGodzinyForKurs(k, GodzinaTyp.JAZDA, false,c);
- k.GodzinyDodatkowe = GetGodzinyForKurs(k, GodzinaTyp.DODATKOWA, false,c);
- k.GodzinyEgzaminJazda = GetGodzinyForKurs(k, GodzinaTyp.EJAZDA, false, c);
- k.GodzinyEgzaminTeoria = GetGodzinyForKurs(k, GodzinaTyp.ETEORIA, false, c);
-
- list.Add(k);
- }
- reader.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (!ownConnection && c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return list;
- }
-
- public bool UpdatePlatnosc(Kurs kurs, Double wplata, User user, DateTime data, bool IsGotowka, int rata)
- {
- List<Kurs> list = new List<Kurs>();
- bool ownConnection = false;
- SQLiteConnection c;
- c = GetConnection();
-
- String SQLset = String.Format(" rata{0}data = '{1}', rata{0}wpowadzajacyId = {2}, rata{0}kwota = {3}, rata{0}gotowka = {4} "
- , rata, data.ToString("yyyy-MM-dd HH:mm:ss"), user.Id, wplata, IsGotowka ? 1 : 0);
-
- bool ret = true;
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = "Update Kurs set " + SQLset + " where id = " + kurs.Id;
- int i = command.ExecuteNonQuery();
- if (i == 1)
- {
- if (rata == 1)
- {
- kurs.Rata1Data = data;
- kurs.Rata1Kwota = wplata;
- kurs.Rata1Wprowadzajacy = user;
- kurs.Rata1Gotowka = IsGotowka;
- }
- else if (rata == 2)
- {
- kurs.Rata2Data = data;
- kurs.Rata2Kwota = wplata;
- kurs.Rata2Wprowadzajacy = user;
- kurs.Rata2Gotowka = IsGotowka;
- }
- else if (rata == 3)
- {
- kurs.Rata3Data = data;
- kurs.Rata3Kwota = wplata;
- kurs.Rata3Wprowadzajacy = user;
- kurs.Rata3Gotowka = IsGotowka;
- }
- else if (rata == 4)
- {
- kurs.Rata4Data = data;
- kurs.Rata4Kwota = wplata;
- kurs.Rata4Wprowadzajacy = user;
- kurs.Rata4Gotowka = IsGotowka;
- }
- else if (rata == 5)
- {
- kurs.Rata5Data = data;
- kurs.Rata5Kwota = wplata;
- kurs.Rata5Wprowadzajacy = user;
- kurs.Rata5Gotowka = IsGotowka;
- }
- else if (rata == 6)
- {
- kurs.Rata6Data = data;
- kurs.Rata6Kwota = wplata;
- kurs.Rata6Wprowadzajacy = user;
- kurs.Rata6Gotowka = IsGotowka;
- }
- else if (rata == 7)
- {
- kurs.Rata7Data = data;
- kurs.Rata7Kwota = wplata;
- kurs.Rata7Wprowadzajacy = user;
- kurs.Rata7Gotowka = IsGotowka;
- }
- else if (rata == 8)
- {
- kurs.Rata8Data = data;
- kurs.Rata8Kwota = wplata;
- kurs.Rata8Wprowadzajacy = user;
- kurs.Rata8Gotowka = IsGotowka;
- }
- else if (rata == 9)
- {
- kurs.Rata9Data = data;
- kurs.Rata9Kwota = wplata;
- kurs.Rata9Wprowadzajacy = user;
- kurs.Rata9Gotowka = IsGotowka;
- }
- else if (rata == 10)
- {
- kurs.Rata10Data = data;
- kurs.Rata10Kwota = wplata;
- kurs.Rata10Wprowadzajacy = user;
- kurs.Rata10Gotowka = IsGotowka;
- }
- else
- ret = false;
- }
- else
- ret = false;
-
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- ret = false;
- }
- finally
- {
- if (!ownConnection && c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return ret;
- }
-
- public bool SaveKurs(Kurs kurs)
- {
- SQLiteConnection c = GetConnection();
- bool ret = true;
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
-
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = String.Format("insert into Kurs(kursantId,typKursuId,dataDodania,uzytkownikDodajacy,cena,instruktorId, nrZaswiadczenia) values ({0},{1},'{2}',{3},{4},{5},'{6}')"
- , kurs.KursantId, kurs.TypKursu.ID,kurs.DataDodania.ToString("yyyy-MM-dd"),kurs.UzytkownikDodajacy.Id,kurs.Cena
- , kurs.Instruktor == null ? 0 : kurs.Instruktor.Id, kurs.NumerZaswiadczenia);
- int i = command.ExecuteNonQuery();
- if (i != 1)
- {
- ret = false;
- throw new Exception("Błąd przy wstawianiu danych kursanta!");
- }
- else
- {
- command.CommandText = "SELECT last_insert_rowid();";
- kurs.Id = Convert.ToInt32(command.ExecuteScalar());
- }
- }
- catch (Exception e)
- {
- ret = false;
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return ret;
- }
-
- public bool DeleteKurs(Kurs kurs)
- {
- SQLiteConnection c = GetConnection();
- bool ret = true;
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = "delete from KursGodzina where kursId = " + kurs.Id;
- command.ExecuteNonQuery();
-
- command.CommandText = "delete from Kurs where Id = " + kurs.Id;
- command.ExecuteNonQuery();
-
- }
- catch (Exception e)
- {
- ret = false;
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return ret;
- }
-
- public bool UpdateKurs(Kurs kurs, KursTyp kt, bool aktywny, Double cena, Double? rata1, Double? rata2, Double? rata3, Double? rata4, Double? rata5, Double? rata6, Double? rata7, Double? rata8, Double? rata9, Double? rata10
- , bool? rata1gotowka, bool? rata2gotowka, bool? rata3gotowka, bool? rata4gotowka, bool? rata5gotowka, bool? rata6gotowka, bool? rata7gotowka, bool? rata8gotowka, bool? rata9gotowka, bool? rata10gotowka
- , String nrZaswiadczenia, Instruktor instruktor)
- {
- List<Kurs> list = new List<Kurs>();
- SQLiteConnection c = GetConnection();
- bool ret = true;
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- StringBuilder sb = new StringBuilder();
- sb.Append("update Kurs set typKursuId = " + kt.ID+", cena = "+cena );
-
- if (aktywny)
- sb.Append(", aktywny = 1");
- else
- sb.Append(", aktywny = 0");
-
- if(rata1 != null)
- sb.Append(" ,rata1kwota = " + rata1);
- if (rata1gotowka != null)
- sb.Append(" ,rata1gotowka = " + (rata1gotowka.Value ? "1" : "0"));
- if (rata2 != null)
- sb.Append(" ,rata2kwota = " + rata2);
- if (rata2gotowka != null)
- sb.Append(" ,rata2gotowka = " + (rata2gotowka.Value ? "1" : "0"));
- if (rata3 != null)
- sb.Append(" ,rata3kwota = " + rata3);
- if (rata3gotowka != null)
- sb.Append(" ,rata3gotowka = " + (rata3gotowka.Value ? "1" : "0"));
- if (rata4 != null)
- sb.Append(" ,rata4kwota = " + rata4);
- if (rata4gotowka != null)
- sb.Append(" ,rata4gotowka = " + (rata4gotowka.Value ? "1" : "0"));
- if (rata5 != null)
- sb.Append(" ,rata5kwota = " + rata5);
- if (rata5gotowka != null)
- sb.Append(" ,rata5gotowka = " + (rata5gotowka.Value ? "1" : "0"));
- if (rata6 != null)
- sb.Append(" ,rata6kwota = " + rata6);
- if (rata6gotowka != null)
- sb.Append(" ,rata6gotowka = " + (rata6gotowka.Value ? "1" : "0"));
- if (rata7 != null)
- sb.Append(" ,rata7kwota = " + rata7);
- if (rata7gotowka != null)
- sb.Append(" ,rata7gotowka = " + (rata7gotowka.Value ? "1" : "0"));
- if (rata8 != null)
- sb.Append(" ,rata8kwota = " + rata8);
- if (rata8gotowka != null)
- sb.Append(" ,rata8gotowka = " + (rata8gotowka.Value ? "1" : "0"));
- if (rata9 != null)
- sb.Append(" ,rata9kwota = " + rata9);
- if (rata9gotowka != null)
- sb.Append(" ,rata9gotowka = " + (rata9gotowka.Value ? "1" : "0"));
- if (rata10 != null)
- sb.Append(" ,rata10kwota = " + rata10);
- if (rata10gotowka != null)
- sb.Append(" ,rata10gotowka = " + (rata10gotowka.Value ? "1" : "0"));
-
- if (!String.IsNullOrEmpty(nrZaswiadczenia))
- sb.Append(" ,nrZaswiadczenia = '" + nrZaswiadczenia + "'");
-
- if (instruktor != null)
- sb.Append(" ,instruktorId = " + instruktor.Id);
-
- sb.Append(" where id = " + kurs.Id);
-
- command.CommandText = sb.ToString();
- int i = command.ExecuteNonQuery();
- if (i != 1)
- {
- ret = false;
- throw new Exception("Błąd przy wstawianiu danych kursanta!");
- }
- else
- {
- kurs.TypKursu = kt;
- kurs.Cena = cena;
- kurs.Aktywny = aktywny;
- if (rata1 != null)
- kurs.Rata1Kwota = (double)rata1;
- if (rata2 != null)
- kurs.Rata2Kwota = (double)rata2;
- if (rata3 != null)
- kurs.Rata3Kwota = (double)rata3;
- if (rata4 != null)
- kurs.Rata4Kwota = (double)rata4;
- if (rata5 != null)
- kurs.Rata5Kwota = (double)rata5;
- if (rata6 != null)
- kurs.Rata6Kwota = (double)rata6;
- if (rata7 != null)
- kurs.Rata7Kwota = (double)rata7;
- if (rata8 != null)
- kurs.Rata8Kwota = (double)rata8;
-
- if (rata1gotowka != null)
- kurs.Rata1Gotowka = rata1gotowka.Value;
- if (rata2gotowka != null)
- kurs.Rata2Gotowka = rata2gotowka.Value;
- if (rata3gotowka != null)
- kurs.Rata3Gotowka = rata3gotowka.Value;
- if (rata4gotowka != null)
- kurs.Rata4Gotowka = rata4gotowka.Value;
- if (rata5gotowka != null)
- kurs.Rata5Gotowka = rata5gotowka.Value;
- if (rata6gotowka != null)
- kurs.Rata6Gotowka = rata6gotowka.Value;
- if (rata7gotowka != null)
- kurs.Rata7Gotowka = rata7gotowka.Value;
- if (rata8gotowka != null)
- kurs.Rata8Gotowka = rata8gotowka.Value;
- if (rata9gotowka != null)
- kurs.Rata9Gotowka = rata9gotowka.Value;
- if (rata10gotowka != null)
- kurs.Rata10Gotowka = rata10gotowka.Value;
-
- if (!String.IsNullOrEmpty(nrZaswiadczenia))
- kurs.NumerZaswiadczenia = nrZaswiadczenia;
-
- if (instruktor != null)
- kurs.Instruktor = instruktor;
- }
- }
- catch (Exception e)
- {
- ret = false;
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return ret;
- }
-
- public Kurs GetKursById(long kursId, SQLiteConnection _c)
- {
-
- Kurs k = new Kurs();
- bool ownConnection = false;
- SQLiteConnection c;
-
- if (_c == null)
- {
- c = GetConnection();
- ownConnection = false;
- }
- else
- {
- c = _c;
- ownConnection = true;
- }
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = @"select id,kursantId,typKursuId,dataDodania,uzytkownikDodajacy,cena
- , rata1data,rata2data,rata3data,rata4data
- , rata1wpowadzajacyId, rata2wpowadzajacyId,rata3wpowadzajacyId,rata4wpowadzajacyId
- , rata1kwota,rata2kwota,rata3kwota,rata4kwota
- , aktywny
- , rata1gotowka,rata2gotowka,rata3gotowka,rata4gotowka
- , instruktorId, nrZaswiadczenia
- , rata5data,rata6data,rata7data,rata8data,rata9data,rata10data
- , rata5wpowadzajacyId, rata6wpowadzajacyId,rata7wpowadzajacyId,rata8wpowadzajacyId,rata9wpowadzajacyId,rata10wpowadzajacyId
- , rata5kwota,rata6kwota,rata7kwota,rata8kwota,rata9kwota,rata10kwota
- , rata5gotowka,rata6gotowka,rata7gotowka,rata8gotowka,rata9gotowka,rata10gotowka
- , w1_data, w1_osoba, w2_data, w2_osoba, w3_data, w3_osoba
- from Kurs where id = " + kursId;
-
-
- KursantManager km = new KursantManager();
- UserManager u = new UserManager();
-
- SQLiteDataReader reader = command.ExecuteReader();
- while (reader.Read())
- {
- k.Id = reader.GetInt64(0);
- k.KursantId = reader.GetInt64(1);
- k.TypKursu = GetTypKursuById(reader.GetInt64(2),c);
- k.DataDodania = reader.IsDBNull(3) || reader.GetString(3) == "" ? DateTime.MaxValue : reader.GetDateTime(3);
- k.UzytkownikDodajacy = u.GetUserById(reader.GetInt64(4),c);
- k.Cena = reader.GetDouble(5);
- k.Rata1Data = reader.IsDBNull(6) || reader.GetString(6) == "" ? DateTime.MaxValue : reader.GetDateTime(6);
- k.Rata2Data = reader.IsDBNull(7) || reader.GetString(7) == "" ? DateTime.MaxValue : reader.GetDateTime(7);
- k.Rata3Data = reader.IsDBNull(8) || reader.GetString(8) == "" ? DateTime.MaxValue : reader.GetDateTime(8);
- k.Rata4Data = reader.IsDBNull(9) || reader.GetString(9) == "" ? DateTime.MaxValue : reader.GetDateTime(9);
- k.Rata1Wprowadzajacy = u.GetUserById(reader.IsDBNull(10) ? 0 : reader.GetInt64(10),c);
- k.Rata2Wprowadzajacy = u.GetUserById(reader.IsDBNull(11) ? 0 : reader.GetInt64(11), c);
- k.Rata3Wprowadzajacy = u.GetUserById(reader.IsDBNull(12) ? 0 : reader.GetInt64(12), c);
- k.Rata4Wprowadzajacy = u.GetUserById(reader.IsDBNull(13) ? 0 : reader.GetInt64(13), c);
- k.Rata1Kwota = reader.IsDBNull(14) ? 0 : reader.GetDouble(14);
- k.Rata2Kwota = reader.IsDBNull(15) ? 0 : reader.GetDouble(15);
- k.Rata3Kwota = reader.IsDBNull(16) ? 0 : reader.GetDouble(16);
- k.Rata4Kwota = reader.IsDBNull(17) ? 0 : reader.GetDouble(17);
- k.Aktywny = reader.GetBoolean(18);
- k.Rata1Gotowka = reader.GetBoolean(19);
- k.Rata2Gotowka = reader.GetBoolean(20);
- k.Rata3Gotowka = reader.GetBoolean(21);
- k.Rata4Gotowka = reader.GetBoolean(22);
-
- long instruktorId = reader.IsDBNull(23) ? 0 : reader.GetInt64(23);
- InstruktorManager im = new InstruktorManager();
- k.Instruktor = im.GetInstruktorById(instruktorId,c);
-
- k.NumerZaswiadczenia = reader.IsDBNull(24) ? "" : reader.GetString(24);
-
- int i = 25;
-
- k.Rata5Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata6Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata7Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata8Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata9Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata10Data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Rata5Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata6Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata7Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata8Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata9Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata10Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Rata5Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata6Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata7Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata8Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata9Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata10Kwota = reader.IsDBNull(i) ? 0 : reader.GetDouble(i);
- i++; k.Rata5Gotowka = reader.GetBoolean(i);
- i++; k.Rata6Gotowka = reader.GetBoolean(i);
- i++; k.Rata7Gotowka = reader.GetBoolean(i);
- i++; k.Rata8Gotowka = reader.GetBoolean(i);
- i++; k.Rata9Gotowka = reader.GetBoolean(i);
- i++; k.Rata10Gotowka = reader.GetBoolean(i);
-
- i++; k.Wydruk_1_data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Wydruk_1_Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Wydruk_2_data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Wydruk_2_Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
- i++; k.Wydruk_3_data = reader.IsDBNull(i) || reader.GetString(i) == "" ? DateTime.MaxValue : reader.GetDateTime(i);
- i++; k.Wydruk_3_Wprowadzajacy = u.GetUserById(reader.IsDBNull(i) ? 0 : reader.GetInt64(i), c);
-
- k.GodzinyTeorii = GetGodzinyForKurs(k, GodzinaTyp.TEORIA, false, c);
- k.GodzinyJazdy = GetGodzinyForKurs(k, GodzinaTyp.JAZDA, false, c);
- k.GodzinyDodatkowe = GetGodzinyForKurs(k, GodzinaTyp.DODATKOWA, false, c);
- k.GodzinyEgzaminJazda = GetGodzinyForKurs(k, GodzinaTyp.EJAZDA, false, c);
- k.GodzinyEgzaminTeoria = GetGodzinyForKurs(k, GodzinaTyp.ETEORIA, false, c);
-
- }
- reader.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (!ownConnection && c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return k;
- }
-
- public DateTime GetMaxRataDate()
- {
- DateTime dt = DateTime.MinValue;
- SQLiteConnection c = GetConnection();
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = "SELECT max(rata1data), max(rata2data), max(rata3data), max(rata4data), max(rata5data), max(rata6data), max(rata7data), max(rata8data), max(rata9data), max(rata10data) FROM Kurs";
-
- SQLiteDataReader reader = command.ExecuteReader();
- if (reader.Read())
- {
- DateTime dt1 = reader.IsDBNull(0) ? DateTime.MinValue : new DateTime(reader.GetDateTime(0).Year, reader.GetDateTime(0).Month, reader.GetDateTime(0).Day);
- DateTime dt2 = reader.IsDBNull(1) ? DateTime.MinValue : new DateTime(reader.GetDateTime(1).Year, reader.GetDateTime(1).Month, reader.GetDateTime(1).Day);
- DateTime dt3 = reader.IsDBNull(2) ? DateTime.MinValue : new DateTime(reader.GetDateTime(2).Year, reader.GetDateTime(2).Month, reader.GetDateTime(2).Day);
- DateTime dt4 = reader.IsDBNull(3) ? DateTime.MinValue : new DateTime(reader.GetDateTime(3).Year, reader.GetDateTime(3).Month, reader.GetDateTime(3).Day);
-
- DateTime dt5 = reader.IsDBNull(4) ? DateTime.MinValue : new DateTime(reader.GetDateTime(4).Year, reader.GetDateTime(4).Month, reader.GetDateTime(4).Day);
- DateTime dt6 = reader.IsDBNull(5) ? DateTime.MinValue : new DateTime(reader.GetDateTime(5).Year, reader.GetDateTime(5).Month, reader.GetDateTime(5).Day);
- DateTime dt7 = reader.IsDBNull(6) ? DateTime.MinValue : new DateTime(reader.GetDateTime(6).Year, reader.GetDateTime(6).Month, reader.GetDateTime(6).Day);
- DateTime dt8 = reader.IsDBNull(7) ? DateTime.MinValue : new DateTime(reader.GetDateTime(7).Year, reader.GetDateTime(7).Month, reader.GetDateTime(7).Day);
- DateTime dt9 = reader.IsDBNull(8) ? DateTime.MinValue : new DateTime(reader.GetDateTime(8).Year, reader.GetDateTime(8).Month, reader.GetDateTime(8).Day);
- DateTime dt10 = reader.IsDBNull(9) ? DateTime.MinValue : new DateTime(reader.GetDateTime(9).Year, reader.GetDateTime(9).Month, reader.GetDateTime(9).Day);
-
- if (dt < dt1) dt = dt1;
- if (dt < dt2) dt = dt2;
- if (dt < dt3) dt = dt3;
- if (dt < dt4) dt = dt4;
- if (dt < dt5) dt = dt5;
- if (dt < dt6) dt = dt6;
- if (dt < dt7) dt = dt7;
- if (dt < dt8) dt = dt8;
- if (dt < dt9) dt = dt9;
- if (dt < dt10) dt = dt10;
-
- }
- reader.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return dt;
- }
- #endregion
-
- #region TYPY KURSOW
- /// <summary>
- /// Funkcja pobiera listę obiektów typu KursTypData zawierającą ustawienia godzinowo kosztowe danego typu kursu
- /// </summary>
- /// <param name="typKursuId">ID typu kursu dla którego wyciągana jest lista ustawień godzinowo kosztowych </param>
- /// <param name="_c">Jeżeli ten parametr ma wartość NULL wtedy connection zostanie stworzone</param>
- /// <returns>listę obiektów typu KursTypData zawierającą ustawienia godzinowo kosztowe danego typu kurs</returns>
- public List<KursTypData> GetTypKursuDataList(long typKursuId, SQLiteConnection _c)
- {
- List<KursTypData> list = new List<KursTypData>();
- bool ownConnection = false;
- SQLiteConnection c;
-
- if (_c == null)
- {
- c = GetConnection();
- ownConnection = false;
- }
- else
- {
- c = _c;
- ownConnection = true;
- }
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = @"select id, courseTypeId,dateFrom,dateTo,noHoursDrive,priceDrive,paymentDrive,noHoursTheory,priceTheory,paymentTheory
- ,priceAdditional,paymentAdditional, priceEgzDrive, paymentEgzDrive, priceEgzTheory, paymentEgzTheory, noHoursAdditional
- , noHoursEgzTheory, noHoursEgzDrive
- from TypKursuDane where courseTypeId = " + typKursuId + " order by dateFrom desc";
-
- SQLiteDataReader reader = command.ExecuteReader();
- while (reader.Read())
- {
- KursTypData ktd = new KursTypData();
- ktd.Id = reader.GetInt64(0);
- ktd.KursTypId = reader.GetInt64(1);
- ktd.DateFrom = reader.GetDateTime(2);
- ktd.DateTo = reader.IsDBNull(3) || reader.GetString(3) == "" ? DateTime.MaxValue : reader.GetDateTime(3);
- ktd.NoDriveHours = reader.GetInt32(4);
- ktd.PriceDriveHours = reader.GetFloat(5);
- ktd.PaymentDriveHours = reader.GetFloat(6);
- ktd.NoTheoryHours = reader.GetInt32(7);
- ktd.PriceTheoryHours = reader.GetFloat(8);
- ktd.PaymentTheoryHours = reader.GetFloat(9);
- ktd.PriceAdditionalHours = reader.GetFloat(10);
- ktd.PaymentAdditionalHours = reader.GetFloat(11);
- ktd.PriceEgzDriveHours = reader.GetFloat(12);
- ktd.PaymentEgzDriveHours = reader.GetFloat(13);
- ktd.PriceEgzTheoryHours = reader.GetFloat(14);
- ktd.PaymentEgzTheoryHours = reader.GetFloat(15);
- ktd.NoAdditionalHours = reader.GetInt32(16);
- ktd.NoEgTheoryHours = reader.GetInt32(17);
- ktd.NoEgDriveHours = reader.GetInt32(18);
-
- list.Add(ktd);
- }
- reader.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (!ownConnection && c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return list;
- }
-
- /// <summary>
- /// Funkcja pobiera listę obiektów typu KursTypData zawierającą ustawienia godzinowo kosztowe danego typu kursu
- /// </summary>
- /// <param name="typKursuId">ID typu kursu dla którego wyciągana jest lista ustawień godzinowo kosztowych </param>
- /// <param name="_c">Jeżeli ten parametr ma wartość NULL wtedy connection zostanie stworzone</param>
- /// <returns>listę obiektów typu KursTypData zawierającą ustawienia godzinowo kosztowe danego typu kurs</returns>
- public KursTyp GetTypKursuById(long typKursuId, SQLiteConnection _c )
- {
- KursTyp kt = new KursTyp();
- bool ownConnection = false;
- SQLiteConnection c;
-
- if (_c == null)
- {
- c = GetConnection();
- ownConnection = false;
- }
- else
- {
- c = _c;
- ownConnection = true;
- }
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = String.Format("SELECT id, name, isActive, description, IsRemainder FROM TypyKursow where id = {0}", typKursuId);
-
- SQLiteDataReader reader = command.ExecuteReader();
- if (reader.Read())
- {
- kt.ID = reader.GetInt64(0);
- kt.Name = reader.GetString(1);
- kt.IsActive = reader.GetBoolean(2);
- kt.Description = reader.IsDBNull(3) ? "" : reader.GetString(3);
- kt.IsRemainder = reader.GetBoolean(4);
- kt.DataList = GetTypKursuDataList(kt.ID, c);
-
- }
- reader.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (!ownConnection && c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return kt;
- }
-
-
- public bool UpdateTypKursuData(KursTypData ktd, DateTime DateFrom, DateTime DateTo, int NoDriveHours, float PriceDriveHours, float PaymentDriveHours, int NoTheoryHours
- ,float PriceTheoryHours,float PaymentTheoryHours, float PriceAdditionalHours, float PaymentAdditionalHours, float PriceEgzDrive, float PaymentEgzDrive
- , float PriceEgzTheory, float PaymentEgzTheory, int NoAdditionalHours, int NoEgTheoryHours, int NoEgDriveHours)
- {
- bool ret = true;
- SQLiteConnection c = GetConnection();
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- //SELECT id, name, isActive FROM
- command.CommandText = String.Format("update TypKursuDane set dateFrom = '{0}', dateTo = '{1}', noHoursDrive = {2}, priceDrive = {3}, paymentDrive = {4} " +
- ", noHoursTheory = {5}, priceTheory = {6}, paymentTheory = {7}, priceAdditional = {8}, paymentAdditional = {9} " +
- ", priceEgzDrive = {11}, paymentEgzDrive = {12}, priceEgzTheory = {13}, paymentEgzTheory = {14} " +
- ", noHoursAdditional = {15}, noHoursEgzTheory = {16}, noHoursEgzDrive = {17} " +
- " where id = {10}", DateFrom.ToString("yyyy-MM-dd 00:00"), DateTo == DateTimePicker.MaximumDateTime ? "" : DateTo.ToString("yyyy-MM-dd 00:00"), NoDriveHours
- , PriceDriveHours, PaymentDriveHours, NoTheoryHours, PriceTheoryHours, PaymentTheoryHours, PriceAdditionalHours, PaymentAdditionalHours
- , ktd.Id, PriceEgzDrive, PaymentEgzDrive, PriceEgzTheory, PaymentEgzTheory,NoAdditionalHours, NoEgTheoryHours, NoEgDriveHours);
-
- int i = command.ExecuteNonQuery();
- if (i != 1)
- {
- throw new Exception("Błąd przy aktualizacji danych szczegółowych typu kursu!");
- }
- else
- {
- ktd.DateFrom = DateFrom;
- ktd.DateTo = DateTo == DateTimePicker.MaximumDateTime ? DateTime.MaxValue : DateTo;
- ktd.NoDriveHours = NoDriveHours;
- ktd.PriceDriveHours = PriceDriveHours;
- ktd.PaymentDriveHours = PaymentDriveHours;
- ktd.NoTheoryHours = NoTheoryHours;
- ktd.PriceTheoryHours = PriceTheoryHours;
- ktd.PaymentTheoryHours = PaymentTheoryHours;
- ktd.PriceAdditionalHours = PriceAdditionalHours;
- ktd.PaymentAdditionalHours = PaymentAdditionalHours;
- ktd.NoEgTheoryHours = NoAdditionalHours;
- ktd.NoEgTheoryHours = NoEgTheoryHours;
- ktd.NoEgDriveHours = NoEgDriveHours;
- }
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- ret = false;
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
- return ret;
- }
-
-
- public bool SaveTypKursuData(KursTypData ktd)
- {
- bool ret = true;
- SQLiteConnection c = GetConnection();
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- using (SQLiteTransaction dbTrans = c.BeginTransaction())
- {
- SQLiteCommand command = c.CreateCommand();
- //najpierw update wpisu z nieustawiona data do
- command.CommandText = String.Format("update TypKursuDane set dateTo = date('{0}','-1 day') where courseTypeId = {1} and (dateTo is null or dateTo = '')", ktd.DateFrom, ktd.KursTypId);
- command.ExecuteNonQuery();
- //teraz insert
- command.CommandText = String.Format("insert into TypKursuDane(courseTypeId, dateFrom, dateTo, noHoursDrive, priceDrive, paymentDrive, noHoursTheory, priceTheory " +
- " , paymentTheory, priceAdditional, paymentAdditional, priceEgzDrive, paymentEgzDrive, priceEgzTheory, paymentEgzTheory, noHoursAdditional " +
- " , noHoursEgzTheory, noHoursEgzDrive) " +
- " values ({0},'{1}','{2}',{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17})", ktd.KursTypId, ktd.DateFrom.ToString("yyyy-MM-dd 00:00")
- , ktd.DateTo == DateTime.MaxValue ? "" : ktd.DateTo.ToString("yyyy-MM-dd 00:00"), ktd.NoDriveHours, ktd.PriceDriveHours, ktd.PaymentDriveHours
- , ktd.NoTheoryHours, ktd.PriceTheoryHours, ktd.PaymentTheoryHours, ktd.PriceAdditionalHours, ktd.PaymentAdditionalHours
- , ktd.PriceEgzDriveHours, ktd.PaymentEgzDriveHours, ktd.PriceEgzTheoryHours, ktd.PaymentEgzTheoryHours, ktd.NoAdditionalHours
- , ktd.NoEgTheoryHours, ktd.NoEgDriveHours);
-
- int i = command.ExecuteNonQuery();
- if (i != 1)
- {
- throw new Exception("Błąd przy zapisie danych szczegółowych typu kursu!");
- }
-
- dbTrans.Commit();
- }
-
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- ret = false;
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
- return ret;
- }
-
- public List<KursTyp> GetTypyKursow()
- {
- List<KursTyp> list = new List<KursTyp>();
- SQLiteConnection c = GetConnection();
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- command.CommandText = "SELECT id, name, isActive,description,IsRemainder FROM TypyKursow order by name;";
-
- SQLiteDataReader reader = command.ExecuteReader();
- while (reader.Read())
- {
- KursTyp kt = new KursTyp();
- kt.ID = reader.GetInt64(0);
- kt.Name = reader.GetString(1);
- kt.IsActive = reader.GetBoolean(2);
- kt.Description = reader.IsDBNull(3) ? "" : reader.GetString(3);
- kt.IsRemainder = reader.GetBoolean(4);
- kt.DataList = GetTypKursuDataList(kt.ID,c);
-
- list.Add(kt);
- }
- reader.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
-
- return list;
- }
-
- public bool UpdateTypKursu(KursTyp kt, String name, bool isActive, String description, bool isRemainder)
- {
- bool ret = true;
- SQLiteConnection c = GetConnection();
-
- try
- {
- if (c.State != System.Data.ConnectionState.Open)
- c.Open();
-
- SQLiteCommand command = c.CreateCommand();
- //SELECT id, name, isActive FROM
- command.CommandText = String.Format("update TypyKursow set name = '{0}', isActive = {1}, description = '{3}', IsRemainder = {4} where id = {2}", name, isActive == false ? 0 : 1, kt.ID, description, isRemainder == false ? 0 : 1);
-
- int i = command.ExecuteNonQuery();
- if (i != 1)
- {
- throw new Exception("Błąd przy aktualizacji danych typu kursu!");
- }
- else
- {
- kt.Name = name;
- kt.IsActive = isActive;
- kt.Description = description;
- kt.IsRemainder = isRemainder;
- }
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message);
- ret = false;
- }
- finally
- {
- if (c.State == System.Data.ConnectionState.Open)
- c.Close();
- }
- return ret;
- }
-
- public bool IsSendReminderDone(int NoOfPayment, Kurs kurs)
- {
- bool ret = true;
- SQLiteConnection c = GetConnection();
-
- try
- {
- if (c.State …
Large files files are truncated, but you can click here to view the full file