public void LoadDataFromDB(int id) { if (usedTable.Fields.Count < 1) { logger.Info("Нет полей для загрузки."); return; } logger.Info("Загружаем данные настраиваемых полей для id={0}", id); this.id = id; DBWorks.SQLHelper sql = new DBWorks.SQLHelper("SELECT "); foreach (CFFieldInfo field in usedTable.Fields) { sql.AddAsList(String.Format("{0}.{1}", usedTable.DBName, field.ColumnName)); } ; sql.Add(" FROM {0} WHERE {0}.id = @id ", usedTable.DBName); logger.Debug(sql.Text); try { MySqlCommand cmd = new MySqlCommand(sql.Text, (MySqlConnection)QSMain.ConnectionDB); cmd.Parameters.AddWithValue("@id", id); using (MySqlDataReader rdr = cmd.ExecuteReader()) { rdr.Read(); foreach (CFFieldInfo field in usedTable.Fields) { switch (field.FieldType) { case FieldTypes.TString: Entry stringWid = (Entry)FieldWidgets[field.ID]; stringWid.Text = DBWorks.GetString(rdr, field.ColumnName, ""); break; case FieldTypes.TCurrency: SpinButton moneyWid = (SpinButton)FieldWidgets[field.ID]; moneyWid.Value = DBWorks.GetDouble(rdr, field.ColumnName, 0); break; } } } logger.Info("Ок"); }catch (Exception ex) { string mes = "Ошибка чтения данных для настраиваемых полей!"; logger.Error(ex, mes); throw new ApplicationException(mes, ex); } }
public static void LoadReadFeed() { logger.Info("Получаем таблицу прочитанных пользователем новостей..."); if (NewsFeeds == null) { NewsFeeds = new List <NewsFeed> (); } string sql = "SELECT * FROM `read_news` WHERE user_id = @user_id"; DbCommand cmd = QSMain.ConnectionDB.CreateCommand(); cmd.CommandText = sql; DbParameter param = cmd.CreateParameter(); param.ParameterName = "@user_id"; param.Value = QSMain.User.Id; cmd.Parameters.Add(param); using (DbDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { NewsFeed feed = NewsFeeds.Find(f => f.Id == DBWorks.GetString(rdr, "feed_id", "")); if (feed != null) { feed.FirstRead = false; feed.DataBaseId = DBWorks.GetInt(rdr, "id", -1); string[] items = DBWorks.GetString(rdr, "items", "").Split(','); feed.ReadItems = new List <string> (items); } else { logger.Warn("В базе найден feed_id={0}, но в программе он не настроен.", DBWorks.GetString(rdr, "feed_id", "")); } } } logger.Info("Ok"); }
private void RefreshCalendar(OrdersCalendar calendar) { if (0 != Interlocked.Exchange(ref calendar.InUpdatingState, 1)) { return; } try { var items = new ItemsList(); using (var connection = new MySqlConnection(QSMain.ConnectionString)) { connection.Open(); logger.Info("Запрос заказов на {0:d}", calendar._StartDate); string sql = "SELECT orders.*, models.name as model, marks.name as mark, status.color, stocks.name as stock, stocks.color as stockcolor, " + "status.name as status, manufacturers.name as manufacturer, tablesum.sum, order_type.name as order_type_name, " + "(SELECT COUNT(*) FROM sms_history WHERE sms_history.order_id = orders.id) as sent_messages " + "FROM orders " + "LEFT JOIN order_type ON order_type.id = orders.id_order_type " + "LEFT JOIN models ON models.id = orders.car_model_id " + "LEFT JOIN marks ON marks.id = models.mark_id " + "LEFT JOIN status ON status.id = orders.status_id " + "LEFT JOIN stocks ON stocks.id = orders.stock_id " + "LEFT JOIN manufacturers ON manufacturers.id = orders.manufacturer_id " + "LEFT JOIN (" + "SELECT order_id, SUM(cost) as sum FROM order_pays GROUP BY order_id) as tablesum " + "ON tablesum.order_id = orders.id " + "WHERE date BETWEEN @start AND @end " + "AND point_number = @point " + "AND calendar_number = @calendar "; MySqlCommand cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@start", calendar._StartDate); cmd.Parameters.AddWithValue("@end", calendar._StartDate.AddDays(6)); cmd.Parameters.AddWithValue("@point", calendar.PointNumber); cmd.Parameters.AddWithValue("@calendar", calendar.CalendarNumber); using (MySqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { CalendarItem order = new CalendarItem( rdr.GetDateTime("date"), rdr.GetInt32("hour") ); order.id = rdr.GetInt32("id"); order.MessageCount = rdr.GetUInt32("sent_messages"); if (!QSMain.User.Permissions["worker"]) { order.FullText = String.Format("{9}\nСостояние: {0}\nАвтомобиль: {1} {2}\nЕврокод: {3}\nПроизводитель: {4}\nСклад:{5}\nТелефон: {6}\nСтоимость: {7:C}\n{8}", rdr["status"], rdr["mark"], rdr["model"], rdr["eurocode"], rdr["manufacturer"], rdr["stock"], rdr["phone"], DBWorks.GetDecimal(rdr, "sum", 0), rdr["comment"], rdr["order_type_name"] ); order.Text = String.Format("{0} {1}\n{2}\n{3}", rdr["mark"], rdr["model"], rdr["phone"], rdr["comment"]); order.DeleteOrder += OnDeleteOrder; order.OpenOrder += OnOpenOrder; order.TimeChanged += OnChangeTimeOrderEvent; } else { order.FullText = String.Format("{7}\nСостояние: {0}\nАвтомобиль: {1} {2}\nЕврокод: {3}\nПроизводитель: {4}\nСклад: {5}\n{6}", rdr["status"], rdr["mark"], rdr["model"], rdr["eurocode"], rdr["manufacturer"], rdr["stock"], rdr["comment"], rdr["order_type_name"] ); var firstStr = rdr["mark"] + " " + rdr["model"] + "\n"; var secondStr = rdr["eurocode"] + " " + rdr["manufacturer"] + " " + rdr["comment"]; String[] words = secondStr.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); order.Text = firstStr; foreach (var word in words) { order.Text += word + "\n"; } } order.Color = DBWorks.GetString(rdr, "color", ""); order.TagColor = DBWorks.GetString(rdr, "stockcolor", ""); if (rdr["stock"].ToString().Length > 0 && rdr["stockcolor"] != DBNull.Value) { order.Tag = rdr["stock"].ToString().Substring(0, 1); } order.Calendar = calendar; int day = (order.Date - calendar._StartDate).Days; order.TypeItemButton = TypeItemOrButton.Order; items.AddItem(day, order.Hour, order); } } logger.Info($"Получено {items.Count} заказов"); logger.Info("Запрос расписания работы сотрудников на {0:d}...", calendar.StartDate); sql = "select shw.id, shw.date_work " + "from shedule_works shw " + "WHERE shw.date_work BETWEEN @start AND @end " + "AND shw.point_number = @point AND shw.calendar_number = @calendar"; List <CalendarItem> calendarItemsShedule = new List <CalendarItem>(); cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@start", calendar.StartDate); cmd.Parameters.AddWithValue("@end", calendar.StartDate.AddDays(6)); cmd.Parameters.AddWithValue("@point", calendar.PointNumber); cmd.Parameters.AddWithValue("@calendar", calendar.CalendarNumber); using (MySqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { CalendarItem shedule = new CalendarItem(rdr.GetDateTime("date_work"), 23); shedule.id = rdr.GetInt32("id"); shedule.Tag = ""; shedule.Color = shedule.TagColor = GetBgColor(calendar); shedule.Calendar = calendar; shedule.DeleteOrder += OnDeleteShedule; shedule.OpenOrder += OnOpenSheduleWork; shedule.TypeItemButton = TypeItemOrButton.Shedule; calendarItemsShedule.Add(shedule); } } foreach (var sh in calendarItemsShedule) { sh.Text = getEmployeeInShedule(sh.id, connection); items.AddItem((sh.Date - calendar._StartDate).Days, 23, sh); } logger.Info("Запрос заметок на {0:d}...", calendar.StartDate); sql = "select id, date, message " + "FROM note " + "WHERE date BETWEEN @start AND @end " + "AND point_number = @point AND calendar_number = @calendar"; cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@start", calendar.StartDate); cmd.Parameters.AddWithValue("@end", calendar.StartDate.AddDays(6)); cmd.Parameters.AddWithValue("@point", calendar.PointNumber); cmd.Parameters.AddWithValue("@calendar", calendar.CalendarNumber); using (MySqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { CalendarItem calendarItemNote = new CalendarItem(DateTime.Parse(rdr["date"].ToString()), 22); calendarItemNote.id = rdr.GetInt32("id"); calendarItemNote.Tag = ""; calendarItemNote.Color = calendarItemNote.TagColor = GetBgColor(calendar); calendarItemNote.Calendar = calendar; calendarItemNote.DeleteOrder += OnDeleteNote; calendarItemNote.OpenOrder += OnOpenNote; if (rdr["message"].ToString().Length > 400) { calendarItemNote.Text = rdr["message"].ToString().Substring(0, 400); } else { calendarItemNote.Text = rdr["message"].ToString(); } int day = (DateTime.Parse(rdr["date"].ToString()) - calendar._StartDate).Days; calendarItemNote.TypeItemButton = TypeItemOrButton.Note; items.AddItem(day, 22, calendarItemNote); } } logger.Info("Ok"); } Application.Invoke(delegate { calendar.Items = items; calendar.QueueDraw(); frmClientCalendar.QueueDraw(); }); } finally { Interlocked.Exchange(ref calendar.InUpdatingState, 0); } }