public AddMeasure()
{
InitializeComponent();
string dbPath = Path.Combine(
Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location),
Properties.Settings.Default.DbName);
this.engine = new SQLiteDBEngine(dbPath);
// Облачность
cbCloud.DataSource = engine.AddBlankRow(engine.ExecuteQueryReturnDataTable(new SQLiteCommand("SELECT ID, Name FROM cloud")));
cbCloud.DisplayMember = "Name";
cbCloud.ValueMember = "ID";
// Ветер
cbWind.DataSource = engine.AddBlankRow(engine.ExecuteQueryReturnDataTable(new SQLiteCommand("SELECT ID, Name FROM wind")));
cbWind.DisplayMember = "Name";
cbWind.ValueMember = "ID";
// Сила ветра
cbWindForce.DataSource = engine.AddBlankRow(engine.ExecuteQueryReturnDataTable(new SQLiteCommand("SELECT ID, Name FROM windForce")));
cbWindForce.DisplayMember = "Name";
cbWindForce.ValueMember = "ID";
// Осадки
cbFallouts.DataSource = engine.ExecuteQueryReturnDataTable(new SQLiteCommand("SELECT ID, Name, IconPath FROM fallout"));
cbFallouts.DisplayMember = "Name";
cbFallouts.ValueMember = "ID";
cbFallouts.DropDownStyle = ComboBoxStyle.DropDownList;
btnAddFallout.Click += btnAddFallout_Click;
btnDelFallout.Click += btnDelFallout_Click;
// Осадки
dgvFallouts.ReadOnly = true;
dgvFallouts.RowHeadersVisible = false;
dgvFallouts.ColumnHeadersVisible = false;
dgvFallouts.AutoGenerateColumns = false;
dgvFallouts.AllowUserToAddRows = false;
dgvFallouts.CellBorderStyle = DataGridViewCellBorderStyle.None;
dgvFallouts.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgvFallouts.RowTemplate.Height = 36;
// Precipitation table
var flColumn = new DataGridViewTextBoxColumn();
flColumn.DataPropertyName = "Name";
flColumn.Name = "Name";
flColumn.HeaderText = "Имя";
flColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvFallouts.Columns.Add(flColumn);
var flIconColumn = new DataGridViewImageColumn();
flIconColumn.DataPropertyName = "FalloutIcon";
flIconColumn.Name = "FalloutIcon";
flIconColumn.HeaderText = "Иконка";
flIconColumn.Width = 36;
dgvFallouts.Columns.Add(flIconColumn);
errorProvider = new ErrorProvider();
tbTemperature.Validating += new CancelEventHandler(tbTemperature_Validating);
dgvFallouts.DataSource = this.engine.ExecuteQueryReturnDataTable(new SQLiteCommand("SELECT fs.ID, fs.Measure_ID, fs.Fallout_ID, f.Name, f.IconPath, NULL AS FalloutIcon FROM fallouts fs INNER JOIN fallout f ON fs.Fallout_ID = f.ID WHERE fs.Measure_ID IS NULL"));
dgvFallouts.RowsAdded += new DataGridViewRowsAddedEventHandler(dgvFallouts_RowsAdded);
}
public Book()
{
InitializeComponent();
this.CancelButton = btnCancel;
btnSave.DialogResult = DialogResult.OK;
btnCancel.DialogResult = DialogResult.Cancel;
string dbPath = Path.Combine(
Path.GetDirectoryName(Assembly.GetEntryAssembly().Location),
Properties.Settings.Default.DbName);
this.engine = new DBEngine.SQLiteDBEngine(dbPath);
dgvBook.RowHeadersVisible = false;
dgvBook.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgvBook.AutoGenerateColumns = true;
}
static void importData()
{
string file = "weather.xls";
SQLiteDBEngine engine = new SQLiteDBEngine("weather.s3db");
DateTime? dateMes = null;
string timeMes = null;
object tempMes = null;
object pressureMes = null;
object falloutMes = null;
Console.WriteLine("====== --- Wether Importer --- =====");
Console.WriteLine("Don't forget to delete all unnecessary sheets!");
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];
// start row
int rowIndex = 2;
while (true)
{
Row row = sheet.Cells.GetRow(rowIndex);
if (row.GetCell(0).IsEmpty)
return;
// Date
dateMes = row.GetCell(0).DateTimeValue;
// Time
// 2 вида времени: одна цифра "10" или в формате "hh:mm:ss" например "9:30"
Cell timeCell = row.GetCell(1);
if (timeCell.Format.FormatType == CellFormatType.Time)
timeMes = timeCell.DateTimeValue.ToString("HH:mm");
else
timeMes = timeCell.StringValue;
// Temperature
tempMes = row.GetCell(2).StringValue;
// Pressure
pressureMes = row.GetCell(3).StringValue;
// Fallout
falloutMes = row.GetCell(4).StringValue;
// Parse time column
DateTime fullDateTime;
string[] timeParts = timeMes.ToString().Split(':');
if (timeParts.Length == 2)
fullDateTime = new DateTime(dateMes.Value.Year, dateMes.Value.Month, dateMes.Value.Day, Convert.ToInt16(timeParts[0]), Convert.ToInt16(timeParts[1]), 0);
else if (timeParts.Length == 1)
fullDateTime = new DateTime(dateMes.Value.Year, dateMes.Value.Month, dateMes.Value.Day, Convert.ToInt16(timeParts[0]), 0, 0);
else
fullDateTime = new DateTime(dateMes.Value.Year, dateMes.Value.Month, dateMes.Value.Day, 0, 0, 0);
//Console.WriteLine(fullDateTime.ToString());
string sql = string.Format("INSERT INTO weather (Measure_Date, Temperature, Pressure, Cloud_ID, Wind_ID) VALUES ({0}, {1}, {2}, {3}, {4})",
new object[] {
// TODO: 2013-11-08 how to save datetime to database
// 2014-12-28: Add seconds ('ss') to datetime value for correct update in WeatherDiary
string.Format("'{0}'", fullDateTime.ToString("yyyy-MM-dd HH:mm:ss")),
tempMes,
pressureMes,
"NULL",
"NULL"
});
object id = engine.ExecuteQueryReturnID(new SQLiteCommand(sql));
Console.WriteLine(id.ToString());
if (id != null && falloutMes != null)
{
// Write fallouts
string fallouts = Convert.ToString(falloutMes);
string falloutSql = string.Empty;
if (fallouts.Contains("дождь"))
{
falloutSql = string.Format("INSERT INTO fallouts (Measure_ID, Fallout_ID) VALUES ({0}, {1})", id, 8);
engine.ExecuteQuery(new SQLiteCommand(falloutSql));
}
if (fallouts.Contains("снег"))
{
falloutSql = string.Format("INSERT INTO fallouts (Measure_ID, Fallout_ID) VALUES ({0}, {1})", id, 9);
engine.ExecuteQuery(new SQLiteCommand(falloutSql));
}
if (fallouts.Contains("гроза"))
{
falloutSql = string.Format("INSERT INTO fallouts (Measure_ID, Fallout_ID) VALUES ({0}, {1})", id, 11);
engine.ExecuteQuery(new SQLiteCommand(falloutSql));
}
}
if (dateMes == null || timeMes == null || pressureMes == null)
Console.WriteLine(rowIndex.ToString());
//Console.SetCursorPosition(0, Console.CursorTop);
rowIndex++;
}
}
