// Изменение данных 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); } }