예제 #1
0
 /// <summary>
 /// Обновляет телефон в БД.
 /// </summary>
 public void Update()
 {
     try
     {
         connection.Open();
         string queryString = "UPDATE phone_number SET number = @number, subscriber_id = @subscriber_id, tariff_id = @tariff_id WHERE id = @id OR number = @number";
         MySqlCommand cmd = new MySqlCommand(queryString, connection);
         cmd.Parameters.AddWithValue("@number", _number);
         cmd.Parameters.AddWithValue("@subscriber_id", _subscriberID);
         cmd.Parameters.AddWithValue("@tariff_id", _tariffID);
         cmd.Parameters.AddWithValue("@id", _id);
         cmd.ExecuteNonQuery();
     }
     catch (MySqlException ex)
     {
         System.Windows.Forms.MessageBox.Show(ex.ToString());
     }
     finally
     {
         connection.Close();
     }
     if (_tariffID != _newTariffID)
     {
         updateTariffHistory();
         if (_tariffID > 0)
         {
             Service s = Service.SelectServiceByName(Constants.TariffChanging);
             Charge ch = new Charge(this, s, 0, DateTime.Now);
             ch.WriteOff();
         }
     }
     _tariffID = _newTariffID;
     _newTariffID = -1;
 }
        /// <summary>
        /// Возвращает расчет списаний за звонки
        /// </summary>
        /// <param name="from">Начальная дата</param>
        /// <param name="to">Конечная дата</param>
        /// <returns>Списания</returns>
        public static List<Charge> CalculateCallsCharges(DateTime from, DateTime to)
        {
            Service service = Service.SelectServiceByName(Constants.ChargeCalls);
            List<PhoneNumber> phones = PhoneNumber.SelectAllPhoneNumbers();
            double full_sum = 0;
            int phones_count = 0;
            //int calls_count = 0;
            List<Charge> result = new List<Charge>();
            foreach (PhoneNumber pn in phones)
            {
                double sum = 0;

                try
                {
                    connection.Open();
                    string query = @"
                    SELECT SUM(TIMESTAMPDIFF(SECOND, c.start_time, c.end_time) *
                    (
                        SELECT cost FROM price
                        WHERE
                        (
                            tariff_id =
                            (
                                SELECT tariff_id FROM tariff_history
                                WHERE phone_id = @phone_id AND start_date <= c.start_time AND (end_date > c.start_time OR end_date IS NULL)
                            )
                            AND c.called_number LIKE CONCAT(prefix, '%')
                        )
                        ORDER BY LENGTH(prefix) DESC LIMIT 1
                    )
                    ) as sum FROM calls c WHERE calling_number LIKE(@number) AND end_time IS NOT NULL AND start_time >= @from AND start_time <= @to";
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@phone_id", pn.ID);
                    cmd.Parameters.AddWithValue("@number", pn.Number);
                    cmd.Parameters.AddWithValue("@from", from);
                    cmd.Parameters.AddWithValue("@to", to);
                    MySqlDataReader r = cmd.ExecuteReader();
                    if (r.Read())
                    {
                        sum = r.IsDBNull(r.GetOrdinal("sum")) ? 0 : r.GetDouble("sum");
                    }
                    r.Close();
                }
                catch (MySqlException ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
                /*List<Call> calls = pn.SelectCalls(from, to);
                foreach (Call call in calls)
                {
                    if (call.CallingNumber == pn.Number)
                    {
                        Tariff tariff = pn.SelectTariffByDate(call.StartTime);
                        sum += CalculateCallCost(call, tariff);
                        calls_count++;
                    }
                }*/
                if (sum > 0)
                {
                    DateTime time = (to.Month == DateTime.Now.Month && to.Year == DateTime.Now.Year || to > DateTime.Now) ? DateTime.Now : to;
                    Charge callsCharge = new Charge(pn, service, sum, time);
                    result.Add(callsCharge);
                    full_sum += sum;
                    phones_count++;
                }
            }
            full_sum = Math.Round(full_sum, 2);
            //System.Windows.Forms.MessageBox.Show("Будет списано " + full_sum.ToString() + " " + Constants.Currency + " с " + phones_count.ToString() + " номеров.", "Расчет завершен");
            return result;
        }