void OdemeBilgileriGoster()
{
try
{
DatabaseAccess db = new DatabaseAccess();
System.Data.SqlServerCe.SqlCeDataReader data = db.DataOku("Select * from log where [no]=@0", odemeNo);
if (data.Read())
{
txtNo.Text = data["no"].ToString();
txtTutar.Text = data["tutar"].ToString();
txtOdemeTarihi.Text = Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString();
this.Text = "Ödeme Bilgileri - Tarih:" + txtOdemeTarihi.Text;
string durum = data["durumu"].ToString();
if (durum == "0")
{
radioOdenmedi.Checked = true;
}
else
{
radioOdendi.Checked = true;
}
txtAciklama.Text = data["aciklama"].ToString();
}
}
catch { MessageBox.Show("Ödeme bilgileri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); }
}
void MusteriyiGoster()
{
try
{
DatabaseAccess db = new DatabaseAccess();
System.Data.SqlServerCe.SqlCeDataReader data = db.DataOku("Select * from musteri where [no]=@0", musteriNo);
if (data.Read())
{
txtNo.Text = data["no"].ToString();
txtAdSoyad.Text = data["adsoyad"].ToString();
this.Text = "Müşteri - " + txtAdSoyad.Text;
txtEposta.Text = data["eposta"].ToString();
txtTel.Text = data["telefon"].ToString();
txtAnaPara.Text = data["anapara"].ToString();
txtKar.Text = data["kar"].ToString();
//txtKar2.Text =
txtAKar.Text = data["akar"].ToString();
txtHesapBilgileri.Text = data["hesap"].ToString();
txtMusteriNotu.Text = data["not"].ToString();
//txtAKar2.Text =
datePickKayit.Value = Convert.ToDateTime(data["ktarih"].ToString());
datePickOdeme.Value = Convert.ToDateTime(data["otarih"].ToString());
}
}
catch { MessageBox.Show("Müşteri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); }
}
private void btnAra_Click(object sender, EventArgs e)
{
int musterino;
if (int.TryParse(textBox1.Text, out musterino))
{
try
{
DatabaseAccess db = new DatabaseAccess();
data = db.DataOku("select isim from musteri where [no]=@0", listMusteri.Items[musterino].Tag.ToString());
if (data.Read())
{
MusteriDuzenle(listMusteri.Items[musterino].Tag.ToString(), listMusteri.Items[musterino].Text);
}
else
{
MusterileriListele(textBox1.Text);
}
//DatabaseAccess db = new DatabaseAccess();
//data = db.DataOku("select * from musteri where [no]=@0", musterino.ToString());
//if (data.Read())
// MusteriDuzenle(musterino.ToString());
//else
// MusterileriListele(textBox1.Text);
}
catch { MusterileriListele(textBox1.Text); }
}
else
{
MusterileriListele(textBox1.Text);
}
}
// ----------- Функции -------------
public static int DB_GetMaxLength(string table, string column)
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + table + "' and COLUMN_NAME='" + column + "'";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = ExecuteReader(sqlQuery);
int len = 0;
if (sqlReader.Read())
{
len = (int)sqlReader.GetSqlInt32(sqlReader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"));
}
sqlReader.Close();
return(len);
}
void MusterileriListele()
{
OdemeZamaniKontrol();
try
{
double anapara1 = 0, kar1 = 0, akar1 = 0;
int demo = 0;
listMusteri.Items.Clear();
DatabaseAccess db = new DatabaseAccess();
data = db.DataOku("select * from musteri order by no");
while (data.Read())
{
string anapara = data["anapara"].ToString();
anapara1 += Convert.ToDouble(anapara);
string kar = PRG.YuzdeToPara(anapara, data["kar"].ToString());
kar1 += Convert.ToDouble(kar);
string akar = PRG.YuzdeToPara(anapara, data["akar"].ToString());
akar1 += Convert.ToDouble(akar);
ListViewItem item = new ListViewItem();
item.Tag = item.Text = data["no"].ToString();
item.SubItems.Add(data["adsoyad"].ToString());
item.SubItems.Add(anapara);
item.SubItems.Add("%" + data["kar"].ToString() + "(" + kar + ")");
item.SubItems.Add("%" + data["akar"].ToString() + "(" + akar + ")");
item.SubItems.Add(PRG.OdenecekTutar(item.Text, "1"));
string bekleyen = PRG.OdenecekTutar(item.Text, "0");
if (bekleyen == "0")
{
item.ForeColor = Color.Green;
}
else
{
item.ForeColor = Color.Red;
}
item.SubItems.Add(bekleyen);
item.SubItems.Add(Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString());
if (demo != 5)
{
demo++;
listMusteri.Items.Add(item);
}
}
lblAnaPara.Text = anapara1.ToString();
lblKar.Text = kar1.ToString();
lblAKar.Text = akar1.ToString();
lblOdenmis.Text = PRG.OdenecekTutar("1");
lblBekleyen.Text = PRG.OdenecekTutar("0");
}
catch { MessageBox.Show("Müşteriler okunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
}
void MusterileriListele()
{
listMusteri.Items.Clear();
DatabaseAccess db = new DatabaseAccess();
data = db.DataOku("select * from musteri order by no");
while (data.Read())
{
ListViewItem item = new ListViewItem();
item.Tag = data["no"].ToString();
item.Text = (listMusteri.Items.Count + 1).ToString();
item.SubItems.Add(data["isim"].ToString());
item.SubItems.Add(data["site"].ToString());
item.SubItems.Add(Convert.ToDateTime(data["baslangic"].ToString()).ToShortDateString());
item.SubItems.Add(Convert.ToDateTime(data["bitis"].ToString()).ToShortDateString());
listMusteri.Items.Add(item);
}
}
//Загрузка списка игр
void LoadGamesToList()
{
// Сначала очистить:
m_listview_Games.Items.Clear();
// Если папка не выбрана, не показывать ничего!
if (!SELECTOR_FOLDER__is_enabled)
{
return;
}
// Теперь загрузить:
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id";
if (SELECTOR_FOLDER__is_enabled)
{
if (SELECTOR_FOLDER == -1)
{
sqlQuery.CommandText += " WHERE g.fk_Folder_id IS NULL";
}
else
{
sqlQuery.CommandText += (" WHERE g.fk_Folder_id = " + SELECTOR_FOLDER);
}
}
sqlQuery.CommandText += " ORDER BY g.StartDate DESC, g.id ASC";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
while (sqlReader.Read())
{
Load1Game(null, sqlReader);
}
sqlReader.Close();
// Подогнать ширину столбцов
this.m_listview_Games.Columns[0].Width = -1; //Комментарий
this.m_listview_Games.Columns[1].Width = -1; //Дата
this.m_listview_Games.Columns[2].Width = -1; //Тип
this.m_listview_Games.Columns[3].Width = -1; //Опции
this.m_listview_Games.Columns[4].Width = -1; //Место
this.m_listview_Games.Columns[5].Width = -1; //Игроки
}
void MusterileriListele(string ara)
{
OdemeZamaniKontrol();
try
{
ara = ClearSqlInjection(ara);
int demo = 0;
listMusteri.Items.Clear();
DatabaseAccess db = new DatabaseAccess();
data = db.DataOku("select * from musteri where adsoyad like '%" + ara + "%' order by no");
while (data.Read())
{
string anapara = data["anapara"].ToString();
string kar = PRG.YuzdeToPara(anapara, data["kar"].ToString());
string akar = PRG.YuzdeToPara(anapara, data["akar"].ToString());
ListViewItem item = new ListViewItem();
item.Tag = item.Text = data["no"].ToString();
item.SubItems.Add(data["adsoyad"].ToString());
item.SubItems.Add(anapara);
item.SubItems.Add("%" + data["kar"].ToString() + "(" + kar + ")");
item.SubItems.Add("%" + data["akar"].ToString() + "(" + akar + ")");
item.SubItems.Add(PRG.OdenecekTutar(item.Text, "1"));
string bekleyen = PRG.OdenecekTutar(item.Text, "0");
if (bekleyen == "0")
{
item.ForeColor = Color.Green;
}
else
{
item.ForeColor = Color.Red;
}
item.SubItems.Add(bekleyen);
item.SubItems.Add(Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString());
if (demo != 5)
{
demo++;
listMusteri.Items.Add(item);
}
}
}
catch { MessageBox.Show("Müşteriler okunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
}
void MusterileriListele(string ara)
{
ara = ClearSqlInjection(ara);
string command = "select * from musteri where site like '%" + ara + "%' or isim like '%" + ara + "%' or eposta like '%" + ara + "%' order by no";
listMusteri.Items.Clear();
DatabaseAccess db = new DatabaseAccess();
data = db.DataOku(command);
while (data.Read())
{
ListViewItem item = new ListViewItem();
item.Tag = data["no"].ToString();
item.Text = (listMusteri.Items.Count + 1).ToString();
item.SubItems.Add(data["isim"].ToString());
item.SubItems.Add(data["site"].ToString());
item.SubItems.Add(Convert.ToDateTime(data["baslangic"].ToString()).ToShortDateString());
item.SubItems.Add(Convert.ToDateTime(data["bitis"].ToString()).ToShortDateString());
listMusteri.Items.Add(item);
}
}
void OdemeZamaniKontrol()
{
try
{
DatabaseAccess db = new DatabaseAccess();
//System.Data.SqlServerCe.SqlCeParameter tarih1 = new System.Data.SqlServerCe.SqlCeParameter("@0", SqlDbType.DateTime);
data = db.DataOku("select no,otarih,kar,anapara from musteri where otarih<@0", DateTime.Now.Date.AddDays(1).ToString());
while (data.Read())
{
string tarih = data["otarih"].ToString();
DateTime otarih = Convert.ToDateTime(tarih);
string no = data["no"].ToString();
string tutar = PRG.YuzdeToPara(data["anapara"].ToString(), data["kar"].ToString());
DatabaseAccess db2 = new DatabaseAccess();
db2.Sorgu("insert into log (musterino,tutar,otarih,durumu,aciklama) values (@0,@1,@2,@3,@4)", no, tutar, otarih.ToString(), "0", "");
DatabaseAccess db3 = new DatabaseAccess();
db3.Sorgu("update musteri set otarih=@0 where no=@1", otarih.AddMonths(1).ToString(), no);
}
}
catch { MessageBox.Show("Ödeme zamanı kontrol edilirken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
}
void OdemeleriGoster()
{
try
{
lblOdenmis.Text = PRG.OdenecekTutar(musteriNo, "1");
lblBekleyen.Text = PRG.OdenecekTutar(musteriNo, "0");
lblAKari.Text = txtAKar2.Text;
lblKari.Text = txtKar2.Text;
listOdemeler.Items.Clear();
DatabaseAccess db = new DatabaseAccess();
data = db.DataOku("select * from log where musterino=@0", musteriNo);
while (data.Read())
{
ListViewItem item = new ListViewItem();
item.Tag = item.Text = data["no"].ToString();
item.SubItems.Add(data["tutar"].ToString());
item.SubItems.Add(Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString());
switch (data["durumu"].ToString())
{
case "1":
item.SubItems.Add("Ödendi");
item.ForeColor = Color.Green;
break;
case "0":
item.SubItems.Add("Ödenmedi");
item.ForeColor = Color.Red;
break;
default:
break;
}
//1 - tamamlandı
//0 - ödenmedi
item.SubItems.Add(data["aciklama"].ToString());
listOdemeler.Items.Add(item);
}
}
catch { MessageBox.Show("Ödeme bilgileri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); }
}
void MusteriyiListele()
{
try
{
DatabaseAccess db = new DatabaseAccess();
System.Data.SqlServerCe.SqlCeDataReader data = db.DataOku("Select * from musteri where [no]=@0", musterino);
if (data.Read())
{
txtVeritabaniNo.Text = data["no"].ToString();
txtAdSoyad.Text = data["isim"].ToString();
txtEposta.Text = data["eposta"].ToString();
txtKAdi.Text = data["kadi"].ToString();
txtKSifre.Text = data["ksifre"].ToString();
txtSite.Text = data["site"].ToString();
txtUcret.Text = data["ucret"].ToString();
txtTel.Text = data["tel"].ToString();
datePickBaslangic.Value = Convert.ToDateTime(data["baslangic"].ToString());
datePickBitis.Value = Convert.ToDateTime(data["bitis"].ToString());
}
}
catch { MessageBox.Show("Müşteri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); }
}
bool CloneGame(int gameId)
{
System.Data.SqlServerCe.SqlCeTransaction trans = null;
System.Data.SqlServerCe.SqlCeCommand sqlQuery = null;
System.Data.SqlServerCe.SqlCeDataReader sqlReader = null;
System.Data.SqlServerCe.SqlCeDataReader sqlReader2 = null;
try
{
int comm_maxlen = DB.DB_GetMaxLength("Games", "Comment");
// Склонировать (используя транзакцию)
sqlQuery = DB.CreateQuery();
trans = DB.sqlConnection.BeginTransaction();
sqlQuery.Transaction = trans;
sqlQuery.CommandText = "INSERT INTO Games(Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, Comment, StartDate) (SELECT Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, (CASE WHEN Comment is NULL THEN '{клон}' ELSE (SUBSTRING(Comment, 1, (" + comm_maxlen + " - LEN(' {клон}'))) + ' {клон}') END) as Comment_clon, StartDate FROM Games WHERE id=" + gameId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
int new_gameId = DB.GetLastInsertId(trans);
sqlQuery.CommandText = "SELECT id FROM Matches WHERE fk_Game_id=" + gameId + " ORDER BY id";
sqlReader = sqlQuery.ExecuteReader();
while (sqlReader.Read())
{
int cur_matchId = sqlReader.GetInt32(0);
sqlQuery.CommandText = "INSERT INTO Matches(fk_Game_id, SCORE_NS, SCORE_EW) (SELECT " + new_gameId + " as fk_Game_id__new, SCORE_NS, SCORE_EW FROM Matches WHERE id=" + cur_matchId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
int new_cur_matchId = DB.GetLastInsertId(trans);
/****int added = 0;
* sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + ")";
* added = sqlQuery.ExecuteNonQuery();
*
* sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + ")";
* added = sqlQuery.ExecuteNonQuery();
*
* sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + ")";
* added = sqlQuery.ExecuteNonQuery();*****/
int cur_dealId = -1;
sqlQuery.CommandText = "SELECT id FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
sqlReader2 = sqlQuery.ExecuteReader();
while (sqlReader2.Read())
{
cur_dealId = (int)sqlReader2.GetSqlInt32(0);
sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE id=" + cur_dealId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader2.Close();
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
sqlReader2.Close();
sqlQuery.CommandText = "SELECT id FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
sqlReader2 = sqlQuery.ExecuteReader();
while (sqlReader2.Read())
{
cur_dealId = (int)sqlReader2.GetSqlInt32(0);
sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE id=" + cur_dealId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader2.Close();
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
sqlReader2.Close();
sqlQuery.CommandText = "SELECT id FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
sqlReader2 = sqlQuery.ExecuteReader();
while (sqlReader2.Read())
{
cur_dealId = (int)sqlReader2.GetSqlInt32(0);
sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE id=" + cur_dealId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader2.Close();
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
sqlReader2.Close();
}
sqlReader.Close();
// Добавить в список
sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id WHERE g.id=" + new_gameId;
sqlReader = sqlQuery.ExecuteReader();
if (sqlReader.Read())
{
Load1Game(null, sqlReader);
sqlReader.Close();
trans.Commit();
LoadFoldersToCombo(true); // перезагрузить список папок
MessageBox.Show("Игра #" + gameId + " успешно склонирована в #" + new_gameId, "Клонирование успешно", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
return(true);
}
else
{
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована в #" + new_gameId, "Клонирование неудачно", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
catch (System.Data.SqlServerCe.SqlCeException ex)
{
if (trans != null)
{
DB.SafeTransRollback(trans);
}
if (sqlReader != null && sqlReader.IsClosed == false)
{
sqlReader.Close();
}
if (sqlReader2 != null && sqlReader2.IsClosed == false)
{
sqlReader2.Close();
}
if (ex.NativeError == 0)
{
return(false);
}
else
{
throw;
}
}
}
// Редактирование игры
void ContextMenu_OnClickEdit(object sender, EventArgs e)
{
ListViewItem lvi = m_listview_Games.Items[m_listview_Games.SelectedIndices[0]];
int gameId = (int)lvi.Tag;
// Открыть окно редактирования
GameOptionsForm_Common form = new GameOptionsForm_Common(true, gameId);
if (form.ShowDialog() == DialogResult.OK && form.done2steps == true)
{
// Конечные id папки и игроков (-1 для NULL, если нет то создать)
int folder_id = DB.DB_GetAttributeId(form.Folder_Id, form.Folder_Name, "Folders", "id", "Name");
int n_id = DB.DB_GetAttributeId(form.N_Id, form.N_Name, "Players", "id", "Name");
int s_id = DB.DB_GetAttributeId(form.S_Id, form.S_Name, "Players", "id", "Name");
int e_id = DB.DB_GetAttributeId(form.E_Id, form.E_Name, "Players", "id", "Name");
int w_id = DB.DB_GetAttributeId(form.W_Id, form.W_Name, "Players", "id", "Name");
// Обрезанные строки места и комментария
string place__cut = DB.DB_GetCuttedString(form.Place, "Games", "Place");
string comment__cut = DB.DB_GetCuttedString(form.Comment, "Games", "Comment");
// Редактировать в БД
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "UPDATE Games SET fk_Folder_id = @folder, fk_N = @n, fk_S = @s, fk_E = @e, fk_W = @w, Place = @place, Comment = @comment WHERE id=" + gameId;
sqlQuery.Parameters.Add("folder", (folder_id != -1 ? (object)folder_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("n", (n_id != -1 ? (object)n_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("s", (s_id != -1 ? (object)s_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("e", (e_id != -1 ? (object)e_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("w", (w_id != -1 ? (object)w_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("place", (place__cut.Length > 0 ? (object)place__cut : (object)DBNull.Value));
sqlQuery.Parameters.Add("comment", (comment__cut.Length > 0 ? (object)comment__cut : (object)DBNull.Value));
sqlQuery.Prepare();
while (DB.ExecuteNonQuery(sqlQuery, true) == 0)
{
if (MessageBox.Show("Игра " + gameId + " не была изменена!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry)
{
DB.sqlConnection.Close();
DB.sqlConnection.Open();
}
else
{
return;
}
}
// Если изменилась папка, убрать из списка; иначе обновить запись об игре в списке
if (SELECTOR_FOLDER__is_enabled && folder_id != SELECTOR_FOLDER)
{
// Убрать из списка
m_listview_Games.Items.RemoveAt(m_listview_Games.SelectedIndices[0]);
// Перезагрузить список папок
LoadFoldersToCombo(true);
}
else
{
// Обновить запись об игре в списке
sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id WHERE g.id=" + gameId;
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
if (sqlReader.Read())
{
Load1Game(lvi, sqlReader);
}
sqlReader.Close();
}
}
}
//Загрузка комбы папок
void LoadFoldersToCombo(bool reload)
{
// Сначала очистить:
m_combo_Folders.Items.Clear();
// Теперь загрузить:
//1. NULL
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT COUNT(id) FROM Games WHERE fk_Folder_id is NULL";
object o = DB.ExecuteScalar(sqlQuery);
int fid = -1;
string fname = "- нет папки -";
if (o != null && o != DBNull.Value)
{
fname += " (" + (int)o + ")";
}
int index = m_combo_Folders.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (SELECTOR_FOLDER__is_enabled && SELECTOR_FOLDER == fid)
{
if (reload)
{
dont_react_on_folder_selection = true;
}
m_combo_Folders.SelectedIndex = index;
if (reload)
{
dont_react_on_folder_selection = false;
}
}
// 2. остальные
sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT f.id as [fid], f.Name as [fname], COUNT(g.id) as [fcount] FROM Folders f LEFT JOIN Games g ON f.id=g.fk_Folder_id GROUP BY f.id, f.Name ORDER BY [fname]";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
while (sqlReader.Read())
{
fid = sqlReader.IsDBNull(sqlReader.GetOrdinal("fid")) ? -1 : sqlReader.GetInt32(sqlReader.GetOrdinal("fid"));
fname = "";
if (fid == -1)
{
fname = "- нет папки -";
}
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("fname")))
{
fname = sqlReader.GetString(sqlReader.GetOrdinal("fname"));
}
fname += " (" + (sqlReader.IsDBNull(sqlReader.GetOrdinal("fcount")) ? 0 : sqlReader.GetInt32(sqlReader.GetOrdinal("fcount"))) + ")";
index = m_combo_Folders.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (SELECTOR_FOLDER__is_enabled && SELECTOR_FOLDER == fid)
{
if (reload)
{
dont_react_on_folder_selection = true;
}
m_combo_Folders.SelectedIndex = index;
if (reload)
{
dont_react_on_folder_selection = false;
}
}
}
sqlReader.Close();
}
// **** контролы ****
//m_textbox1 добавить
//m_textbox2 переименовать
//m_combo1 source
//m_combo2 destination
//----------------------------------------------------------------------------------------------------
//Загрузка комбы папок
void LoadFoldersToCombo(ComboBox combo)
{
bool is_from = (combo == m_combo1);
bool is_to = (combo == m_combo2);
// Сохранить SELECTOR_FOLDER...
int saved__SELECTOR_FOLDER = SELECTOR_FOLDER;
int saved__SELECTOR_FOLDER2 = SELECTOR_FOLDER2;
bool saved__SELECTOR_FOLDER__is_enabled = SELECTOR_FOLDER__is_enabled;
bool saved__SELECTOR_FOLDER2__is_enabled = SELECTOR_FOLDER2__is_enabled;
// Сначала очистить:
combo.Items.Clear();
combo.SelectedIndex = -1;
// Теперь загрузить:
//1. NULL
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT COUNT(id) FROM Games WHERE fk_Folder_id is NULL";
object o = DB.ExecuteScalar(sqlQuery);
int fid = -1;
string fname = "- нет папки -";
if (o != null && o != DBNull.Value)
{
fname += " (" + (int)o + ")";
}
int index = combo.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (is_from && saved__SELECTOR_FOLDER__is_enabled && saved__SELECTOR_FOLDER == fid || is_to && saved__SELECTOR_FOLDER2__is_enabled && saved__SELECTOR_FOLDER2 == fid)
{
combo.SelectedIndex = index;
}
// 2. остальные
sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT f.id as [fid], f.Name as [fname], COUNT(g.id) as [fcount] FROM Folders f LEFT JOIN Games g ON f.id=g.fk_Folder_id GROUP BY f.id, f.Name ORDER BY [fname]";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
while (sqlReader.Read())
{
fid = sqlReader.IsDBNull(sqlReader.GetOrdinal("fid")) ? -1 : sqlReader.GetInt32(sqlReader.GetOrdinal("fid"));
fname = "";
if (fid == -1)
{
fname = "- нет папки -";
}
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("fname")))
{
fname = sqlReader.GetString(sqlReader.GetOrdinal("fname"));
}
fname += " (" + (sqlReader.IsDBNull(sqlReader.GetOrdinal("fcount")) ? 0 : sqlReader.GetInt32(sqlReader.GetOrdinal("fcount"))) + ")";
index = combo.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (is_from && saved__SELECTOR_FOLDER__is_enabled && saved__SELECTOR_FOLDER == fid || is_to && saved__SELECTOR_FOLDER2__is_enabled && saved__SELECTOR_FOLDER2 == fid)
{
combo.SelectedIndex = index;
}
}
sqlReader.Close();
}
// Загрузка 1 игры в список
void Load1Game(ListViewItem lvi, System.Data.SqlServerCe.SqlCeDataReader sqlReader)
{
bool update = (lvi != null); //обновить существующий lvi или добавить новый?
// Комментарий
String str = "";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("Comment")))
{
str = sqlReader.GetString(sqlReader.GetOrdinal("Comment")).Trim();
}
if (update)
{
lvi.Text = str;
}
else
{
lvi = m_listview_Games.Items.Add(new ListViewItem(str));
}
// Дата
str = "";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("StartDate")))
{
DateTime dt = sqlReader.GetDateTime(sqlReader.GetOrdinal("StartDate"));
str = dt.ToString("dd.MM.yyyy HH") + "ч";
}
if (update)
{
lvi.SubItems[1].Text = str;
}
else
{
lvi.SubItems.Add(str);
}
// id (тэг)
if (!update)
{
lvi.Tag = sqlReader.GetInt32(sqlReader.GetOrdinal("id"));
}
// Тип
GameType t = (GameType)sqlReader.GetByte(sqlReader.GetOrdinal("Type"));
if (!update)
{
switch (t)
{
case GameType.Robber:
lvi.SubItems.Add("Робб");
break;
case GameType.Sport:
lvi.SubItems.Add("Спорт");
break;
case GameType.Compensat:
lvi.SubItems.Add("Комп");
break;
case GameType.SimpleIMP:
lvi.SubItems.Add("IMP");
break;
}
}
// Строка опций:
if (!update)
{
ArrayOfString arrOptions = new ArrayOfString();
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("DealsInMatch")))
{
arrOptions.Add(sqlReader.GetByte(sqlReader.GetOrdinal("DealsInMatch")).ToString() + "x");
}
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("GameOptions")))
{
byte GOptions = sqlReader.GetByte(sqlReader.GetOrdinal("GameOptions"));
if (t == GameType.Robber)
{
bool BonusForOners = ((GOptions & 1) > 0);
bool BonusForWholeRobber = ((GOptions & 2) > 0);
arrOptions.Add(BonusForWholeRobber ? "за робб" : "за гейм");
if (BonusForOners)
{
arrOptions.Add("за онеры");
}
}
if (t == GameType.Compensat)
{
BridgeGameScoring.TypeOfCompensation CompType = (BridgeGameScoring.TypeOfCompensation)(GOptions & 3); //2 bits
bool TenCardsIsTwoFits = ((GOptions & 4) > 0); //3rd bit
bool LessCompFor2Fits23PC = ((GOptions & 8) > 0); //4th bit
switch (CompType)
{
case BridgeGameScoring.TypeOfCompensation.Chicago:
arrOptions.Add("чикаг");
break;
case BridgeGameScoring.TypeOfCompensation.Europe:
arrOptions.Add("европ");
if (TenCardsIsTwoFits)
{
arrOptions.Add("10 за 2");
}
if (LessCompFor2Fits23PC)
{
arrOptions.Add("< 23-2");
}
break;
case BridgeGameScoring.TypeOfCompensation.Milton_York:
arrOptions.Add("М-У");
break;
case BridgeGameScoring.TypeOfCompensation.Moscow:
arrOptions.Add("моск");
break;
}
}
}
str = "";
if (arrOptions.Count != 0)
{
for (int j = 0; j < arrOptions.Count; j++)
{
if (j > 0)
{
str += ", ";
}
str += arrOptions[j];
}
}
lvi.SubItems.Add(str);
}
// Место
str = "";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("Place")))
{
str = sqlReader.GetString(sqlReader.GetOrdinal("Place")).Trim();
}
if (update)
{
lvi.SubItems[4].Text = str;
}
else
{
lvi.SubItems.Add(str);
}
// Игроки
str = "";
str += "N: ";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("N")))
{
str += sqlReader.GetString(sqlReader.GetOrdinal("N")).Trim();
}
str += ", S: ";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("S")))
{
str += sqlReader.GetString(sqlReader.GetOrdinal("S")).Trim();
}
str += ", E: ";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("E")))
{
str += sqlReader.GetString(sqlReader.GetOrdinal("E")).Trim();
}
str += ", W: ";
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("W")))
{
str += sqlReader.GetString(sqlReader.GetOrdinal("W")).Trim();
}
if (update)
{
lvi.SubItems[5].Text = str;
}
else
{
lvi.SubItems.Add(str);
}
}
