Beispiel #1
0
        // Изменение данных
        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));
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
            }
        }
Beispiel #4
0
        // РАЗАРХИВ
        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); }
        }
Beispiel #5
0
        // Самый главный метод
        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);
            }
        }
Beispiel #6
0
        // Добавление данных
        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); }
        }
Beispiel #7
0
        // АРХИВЫ
        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); }
        }