// Изменение данных
public static Task UpdateSoldSub(SoldSub soldSub, string id)
{
string sql = string.Format("UPDATE [Sold Subscriptions] SET " +
"[Subscription number] = N'{0}', " +
"[Subscription type] = N'{1}', " +
"[Start date] = N'{2}', " +
"[End date] = N'{3}', " +
"Visit = N'{4}', " +
"[Payment date] = N'{5}', " +
"[With Trainer] = N'{6}', " +
"[With Discount] = '{7}', " +
"[Type Payment] = '{8}', " +
"Amount = '{9}', " +
"Additional = N'{10}' " +
"WHERE id = {11} ",
soldSub.SubscriptionNumber,
soldSub.SubscriptionType,
soldSub.StartDate,
soldSub.EndDate,
soldSub.Visit,
soldSub.PaymentDate,
soldSub.WithTrainer,
soldSub.Discount,
soldSub.TypePayment,
soldSub.Amount,
soldSub.Additional,
id);
return(ExecuteCommand(sql));
}
public string getEventMore(string surname, string name, SoldSub soldsub)
{
string eventMore = "Изменен абонемент пользователя " + surname + " " + name + " [";
if (Data.SubscriptionNumber != soldsub.SubscriptionNumber.ToString())
{
eventMore += "номер абонемента с `" + Data.SubscriptionNumber + "` на `" + soldsub.SubscriptionNumber.ToString() + "`, ";
}
if (Data.SubscriptionType != soldsub.SubscriptionType)
{
eventMore += "тип абонемента с `" + Data.SubscriptionType + "` на `" + soldsub.SubscriptionType + "`, ";
}
if (Data.StartDate != soldsub.StartDate)
{
eventMore += "дата началы с `" + Data.StartDate + "` на `" + soldsub.StartDate + "`, ";
}
if (Data.EndDate != soldsub.EndDate)
{
eventMore += "дата окончания с `" + Data.EndDate + "` на `" + soldsub.EndDate + "`, ";
}
if (Data.Limit != soldsub.Visit)
{
eventMore += "кол-во визитов с `" + Data.Limit + "` на `" + soldsub.Visit + "`, ";
}
if (Data.PaymentDate != soldsub.PaymentDate)
{
eventMore += "дата оплаты с `" + Data.PaymentDate + "` на `" + soldsub.PaymentDate + "`, ";
}
string withTrainerString = soldsub.WithTrainer ? "Да" : "Нет";
if (Data.WithTrainer != withTrainerString)
{
eventMore += withTrainerString != "Да" ? "с `с тренером` на `без тренера`, " : "с `без тренером` на `с тренера`, ";
}
if (Data.Discount != soldsub.Discount)
{
eventMore += "скидка с `" + Data.Discount + "` на `" + soldsub.Discount + "`,";
}
string withCashString = soldsub.TypePayment ? "Наличные" : "Не наличные";
if (Data.TypePayment != withCashString)
{
eventMore += withCashString == "Наличные" ? "с `Не наличные` на `Наличные`, " : "с `Наличные` на `Не наличные`, ";
}
if (Data.Amount != soldsub.Amount)
{
eventMore += "сумма с `" + Data.Amount + "` на `" + soldsub.Amount + "`, ";
}
if (Data.Additional != soldsub.Additional)
{
eventMore += "дополнительное с `" + Data.Additional + "` на `" + soldsub.Additional + "`, ";
}
eventMore += "]";
return(eventMore);
}
public static async Task InsertSoldSub(SoldSub soldSub, string idOfClient, string surname, string name)
{
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
string sql = "";
sql = string.Format("INSERT INTO [Sold Subscriptions] (" +
"Surname, " + // N'{0}'
"Name, " + // N'{1}'
"[Id of Client], " + // N'{2}'
"[Subscription number], " + // N'{3}'
"[Subscription type], " + // N'{4}'
"[Start date], " + // N'{5}'
"[End date], " + // N'{6}'
"Visit, " + // N'{7}'
"[Payment date], " + // N'{8}'
"[With Trainer], " + // '{9}'
"[With Discount], " + // N'{10}'
"[Type Payment], " + // '{11}'
"Amount, " + // '{12}'
"Additional) " + // N'{13}'
"VALUES (" +
"N'{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}', N'{7}', N'{8}', '{9}', N'{10}', '{11}', '{12}', N'{13}')",
surname,
name,
idOfClient,
soldSub.SubscriptionNumber,
soldSub.SubscriptionType,
soldSub.StartDate,
soldSub.EndDate,
soldSub.Visit,
soldSub.PaymentDate,
soldSub.WithTrainer,
soldSub.Discount,
soldSub.TypePayment,
soldSub.Amount,
soldSub.Additional);
SqlCommand command = new SqlCommand(sql, connection);
await command.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
// РАЗАРХИВ
public static async Task UnZipSoldSub(string id)
{
SoldSub soldSub = new SoldSub();
string surname = "";
string name = "";
string idOfClient = "";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
string sql = "SELECT * FROM [AR Sold Subs] WHERE Id = " + id;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
idOfClient = reader[2].ToString();
surname = reader[3].ToString();
name = reader[4].ToString();
string subNumberString = reader[5].ToString().Trim();
soldSub.SubscriptionNumber = int.Parse(subNumberString);
soldSub.SubscriptionType = reader[6].ToString();
soldSub.StartDate = ((DateTime)reader[7]).ToString("yyyy-MM-dd");
soldSub.EndDate = ((DateTime)reader[8]).ToString("yyyy-MM-dd");
soldSub.Visit = reader[9].ToString();
soldSub.PaymentDate = ((DateTime)reader[10]).ToString("yyyy-MM-dd");
soldSub.WithTrainer = (bool)reader[11];
soldSub.Discount = reader[12].ToString();
soldSub.TypePayment = (bool)reader[13];
soldSub.Amount = reader[14].ToString();
soldSub.Additional = reader[15].ToString();
}
reader.Close();
var task = InsertSoldSub(soldSub, idOfClient, surname, name);
sql = "DELETE FROM [AR Sold Subs] WHERE Id = " + id;
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error); }
}
// Самый главный метод
private async void btnAdd_Click(object sender, EventArgs e)
{
if (isFormat(tbSurname.Text.Trim()) &&
isFormat(tbName.Text.Trim()) &&
isFormat(tbSubNumber.Text.Trim()) &&
isFormat(tbCount.Text.Trim()) &&
isFormat(tbAdditional.Text.Trim()) &&
isFormat(tbPercent.Text.Trim()) &&
isFormat(tbSum.Text.Trim()))
{
string errorMessage = "";
string surname = tbSurname.Text.Trim();
string name = tbName.Text.Trim();
string number = tbSubNumber.Text.Trim();
string sum = tbSum.Text.Trim();
string additional = tbAdditional.Text.Trim();
string limit = tbCount.Text.Trim();
string discount = cbDisc.Checked ? tbPercent.Text.Trim() : "";
bool withTrainer = cbTrainer.Checked ? true : false;
string withTrainerString = cbTrainer.Checked ? "Да" : "Нет";
bool withCash = btnCash.BackColor == Color.FromArgb(82, 110, 250) ? true : false; // нал : карта
string withCashString = btnCash.BackColor == Color.FromArgb(82, 110, 250) ? "Наличные" : "Не наличные";
// -------------------------- Start Check -------------------------------
bool flag1 = true;
bool flag2 = true;
bool flag3 = true;
bool flag4 = true;
bool flag5 = true;
bool flag6 = true;
bool flag7 = true;
bool flag8 = true;
bool flag9 = true;
if (surname.Length < 2 || name.Length < 2)
{
errorMessage += "Фамилия и имя должны быть больше 1 символа\n"; flag1 = false;
}
if (!isString(surname) || !isString(name))
{
errorMessage += "Фамилия и имя должны состоять только из букв\n"; flag2 = false;
}
if (number.Length > 6)
{
errorMessage += "Номер абонемента не должен привышать 6 символов\n"; flag3 = false;
}
if (!int.TryParse(number, out int numberInt))
{
errorMessage += "Номер абонемента должен состоять только из цифр\n"; flag4 = false;
}
if (!int.TryParse(sum, out int sumInt))
{
errorMessage += "Сумма должна состоять только из цифр\n"; flag5 = false;
}
if (cbLimit.Checked)
{
if (byte.TryParse(limit, out byte limitByte))
{
limit = limitByte.ToString();
}
else
{
errorMessage += "Визит должен состоять только из цифр\n"; flag6 = false;
}
}
else
{
limit = "";
}
if (cbDisc.Checked)
{
if (!short.TryParse(discount, out short discountShort))
{
errorMessage += "Скидка должна состоять только из цифр\n"; flag7 = false;
}
else
{
discount += "%";
}
}
if (btnAdd.Text == "Изменить")
{
if (Data.SubscriptionNumber == number)
{
flag8 = true; // не понял зачем это надо
}
}
else if (Data.SubNumbers.Contains(number))
{
errorMessage += "Такой номер абонемента уже есть\n"; flag8 = false;
}
if (dtStart.Value > dtEnd.Value)
{
errorMessage += "Дата окончания не может быть меньше дата начала\n"; flag9 = false;
}
// -------------------------- End Check -------------------------------
// ---------------- Проверка на одного и того же клиента --------------
fullFilter(surname, name);
if (getVisibleRows() > 0)
{
DialogResult result = MessageBox.Show(
"В базе данных уже есть клиент с таким именем и фамилией. Вы уверены что хотите добавить нового?",
"В базе уже есть такой клиент",
MessageBoxButtons.YesNo,
MessageBoxIcon.Stop,
MessageBoxDefaultButton.Button2,
MessageBoxOptions.DefaultDesktopOnly);
if (result != DialogResult.Yes)
{
return;
}
}
// --------------------- Добавление и изменение ----------------------
if (flag1 && flag2 && flag3 && flag4 && flag5 && flag6 && flag7 && flag8 && flag9)
{
var task = Task.Factory.StartNew(() => { }); // то, что добавляет в Events - не нужен await
var needTask = Task.Factory.StartNew(() => { }); // то, что добавляет в SS или CL - нужен await
Data.SqlEventDate = DateTime.Now.ToString("s");
Data.DoIt = true;
SoldSub soldSub = new SoldSub
{
SubscriptionNumber = numberInt,
SubscriptionType = Data.SubscriptionType,
StartDate = dtStart.Value.ToString("yyyy-MM-dd"),
EndDate = dtEnd.Value.ToString("yyyy-MM-dd"),
Visit = limit,
PaymentDate = dtBuy.Value.ToString("yyyy-MM-dd"),
WithTrainer = withTrainer,
Discount = discount,
TypePayment = withCash,
Amount = sum,
Additional = additional
};
if (btnAdd.Text == "Добавить")
{
Client client = new Client
{
Surname = surname,
Name = name,
DateAdd = DateTime.Today.ToString("yyyy-MM-dd"),
DateUpdate = DateTime.Today.ToString("yyyy-MM-dd")
};
if (cbNewClient.Checked)
{
needTask = DBHelper.InsertClientAndSoldSub(client, soldSub);
Data.SqlEvent = "Добавлен клиент " + surname + " " + name;
Data.SqlEventType = "insert";
task = DBHelper.InsertEvent(Data.SqlEvent, Data.SqlEventType, Data.SqlEventDate, Data.Login);
}
else
{
needTask = DBHelper.InsertSoldSub(soldSub, Data.IdOfClient, surname, name);
}
Data.SqlEvent = "Продан абонемент " + surname + " " + name;
Data.SqlEventType = "insert";
task = DBHelper.InsertEvent(Data.SqlEvent, Data.SqlEventType, Data.SqlEventDate, Data.Login);
}
if (btnAdd.Text == "Изменить")
{
needTask = DBHelper.UpdateSoldSub(soldSub, Data.Id);
Data.SqlEvent = getEventMore(surname, name, soldSub);
Data.SqlEventType = "update";
task = DBHelper.InsertEvent(Data.SqlEvent, Data.SqlEventType, Data.SqlEventDate, Data.Login);
}
await needTask;
form2.Show();
Close();
}
else
{
MessageBox.Show(errorMessage, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(DBHelper.getIsFormatError(), DBHelper.getCaption(), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
// Добавление данных
public static async Task InsertClientAndSoldSub(Client client, SoldSub soldSub)
{
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
string sql = "";
sql = string.Format("INSERT INTO Clients (" +
"Surname, " + // N'{0}'
"Name, " + // N'{1}'
"Patronymic, " + // N'{2}'
"[Date Add], " + // N'{3}'
"[Date Update]) " + // N'{4}'
"VALUES (" +
"N'{0}', N'{1}', N'{2}', '{3}', '{4}')",
client.Surname,
client.Name,
client.Patronymic,
client.DateAdd,
client.DateUpdate);
SqlCommand command = new SqlCommand(sql, connection);
await command.ExecuteNonQueryAsync();
command.CommandText = "SELECT CONVERT(int, SCOPE_IDENTITY()) FROM Clients";
int lastId = 0;
SqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
lastId = (int)reader[0];
}
reader.Close();
sql = string.Format("INSERT INTO [Sold Subscriptions] (" +
"Surname, " + // N'{0}'
"Name, " + // N'{1}'
"[Id of Client], " + // N'{2}'
"[Subscription number], " + // N'{3}'
"[Subscription type], " + // N'{4}'
"[Start date], " + // N'{5}'
"[End date], " + // N'{6}'
"Visit, " + // N'{7}'
"[Payment date], " + // N'{8}'
"[With Trainer], " + // '{9}'
"[With Discount], " + // N'{10}'
"[Type Payment], " + // '{11}'
"Amount, " + // '{12}'
"Additional) " + // N'{13}'
"VALUES (" +
"N'{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}', N'{7}', N'{8}', '{9}', N'{10}', '{11}', '{12}', N'{13}')",
client.Surname,
client.Name,
lastId,
soldSub.SubscriptionNumber,
soldSub.SubscriptionType,
soldSub.StartDate,
soldSub.EndDate,
soldSub.Visit,
soldSub.PaymentDate,
soldSub.WithTrainer,
soldSub.Discount,
soldSub.TypePayment,
soldSub.Amount,
soldSub.Additional);
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error); }
}
// АРХИВЫ
public static async Task DeleteSoldSub(string id)
{
SoldSub soldSub = new SoldSub();
string surname = "";
string name = "";
string idOfClient = "";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
string sql = "SELECT * FROM [Sold Subscriptions] WHERE Id = " + id;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
idOfClient = reader[1].ToString();
surname = reader[2].ToString();
name = reader[3].ToString();
string subNumberString = reader[4].ToString().Trim();
soldSub.SubscriptionNumber = int.Parse(subNumberString);
soldSub.SubscriptionType = reader[5].ToString();
soldSub.StartDate = ((DateTime)reader[6]).ToString("yyyy-MM-dd");
soldSub.EndDate = ((DateTime)reader[7]).ToString("yyyy-MM-dd");
soldSub.Visit = reader[8].ToString();
soldSub.PaymentDate = ((DateTime)reader[9]).ToString("yyyy-MM-dd");
soldSub.WithTrainer = (bool)reader[10];
soldSub.Discount = reader[11].ToString();
soldSub.TypePayment = (bool)reader[12];
soldSub.Amount = reader[13].ToString();
soldSub.Additional = reader[14].ToString();
}
reader.Close();
sql = string.Format("INSERT INTO [AR Sold Subs] (" +
"[Old_id]," + // '{0}'
"Surname, " + // N'{1}'
"Name, " + // N'{2}'
"[Id of Client], " + // N'{3}'
"[Subscription number], " + // N'{4}'
"[Subscription type], " + // N'{5}'
"[Start date], " + // N'{6}'
"[End date], " + // N'{7}'
"Visit, " + // N'{8}'
"[Payment date], " + // N'{9}'
"[With Trainer], " + // '{10}'
"[With Discount], " + // N'{11}'
"[Type Payment], " + // '{12}'
"Amount, " + // '{13}'
"Additional) " + // N'{14}'
"VALUES (" +
"'{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}', N'{7}', N'{8}', N'{9}', '{10}', N'{11}', '{12}', '{13}', N'{14}')",
id,
surname,
name,
idOfClient,
soldSub.SubscriptionNumber,
soldSub.SubscriptionType,
soldSub.StartDate,
soldSub.EndDate,
soldSub.Visit,
soldSub.PaymentDate,
soldSub.WithTrainer,
soldSub.Discount,
soldSub.TypePayment,
soldSub.Amount,
soldSub.Additional);
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
sql = "DELETE FROM [Sold Subscriptions] WHERE Id = " + id;
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), ex.Source.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error); }
}