internal void Sync(long rowid)
{
IsValid = false;
_command.Parameters[0].Value = rowid;
_reader = _command.ExecuteReader();
_reader.Read();
}
// TODO: Honor behavior
public new SQLiteDataReader ExecuteReader(CommandBehavior behavior)
{
if (OpenReader != null)
{
throw new InvalidOperationException(Strings.OpenReaderExists);
}
if (_connection == null
|| _connection.State != ConnectionState.Open)
{
throw new InvalidOperationException(Strings.FormatCallRequiresOpenConnection("ExecuteReader"));
}
if (string.IsNullOrWhiteSpace(_commandText))
{
throw new InvalidOperationException(Strings.FormatCallRequiresSetCommandText("ExecuteReader"));
}
ValidateTransaction();
Prepare();
Bind();
var changes = 0;
var resultHandles = new List<StatementHandle>();
foreach (var handle in _handles)
{
var hasResults = NativeMethods.sqlite3_stmt_readonly(handle) != 0;
var rc = NativeMethods.sqlite3_step(handle);
if (rc == Constants.SQLITE_ROW
|| (rc == Constants.SQLITE_DONE && hasResults))
{
resultHandles.Add(handle);
continue;
}
rc = NativeMethods.sqlite3_reset(handle);
MarshalEx.ThrowExceptionForRC(rc);
changes += NativeMethods.sqlite3_changes(_connection.Handle);
}
return OpenReader = new SQLiteDataReader(this, resultHandles, changes);
}
/// <summary>
/// This function does all the nasty work at determining what keys need to be returned for
/// a given statement.
/// </summary>
/// <param name="cnn"></param>
/// <param name="reader"></param>
/// <param name="stmt"></param>
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
Dictionary<string, int> catalogs = new Dictionary<string, int>();
Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>();
List<string> list;
List<KeyInfo> keys = new List<KeyInfo>();
// Record the statement so we can use it later for sync'ing
_stmt = stmt;
// Fetch all the attached databases on this connection
using (DataTable tbl = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// Fetch all the unique tables and catalogs used by the current statement
using (DataTable schema = reader.GetSchemaTable(false, false))
{
foreach (DataRow row in schema.Rows)
{
// Check if column is backed to a table
if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
continue;
// Record the unique table so we can look up its keys
string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string table = (string)row[SchemaTableColumn.BaseTableName];
if (tables.ContainsKey(catalog) == false)
{
list = new List<string>();
tables.Add(catalog, list);
}
else
list = tables[catalog];
if (list.Contains(table) == false)
list.Add(table);
}
// For each catalog and each table, query the indexes for the table.
// Find a primary key index if there is one. If not, find a unique index instead
foreach (KeyValuePair<string, List<string>> pair in tables)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string table = pair.Value[i];
DataRow preferredRow = null;
using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
{
// Loop twice. The first time looking for a primary key index,
// the second time looking for a unique index
for (int n = 0; n < 2 && preferredRow == null; n++)
{
foreach (DataRow row in tbl.Rows)
{
if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
{
preferredRow = row;
break;
}
else if (n == 1 && (bool)row["UNIQUE"] == true)
{
preferredRow = row;
break;
}
}
}
if (preferredRow == null) // Unable to find any suitable index for this table so remove it
{
pair.Value.RemoveAt(i);
i--;
}
else // We found a usable index, so fetch the necessary table details
{
using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
{
// Find the root page of the table in the current statement and get the cursor that's iterating it
int database = catalogs[pair.Key];
int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture);
int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
// Now enumerate the members of the index we're going to use
using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
{
KeyQuery query = null;
List<string> cols = new List<string>();
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
bool addKey = true;
// If the column in the index already appears in the query, skip it
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(SchemaTableColumn.BaseColumnName))
continue;
if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
(string)row[SchemaTableColumn.BaseTableName] == table &&
(string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
{
indexColumns.Rows.RemoveAt(x);
x--;
addKey = false;
break;
}
}
if (addKey == true)
cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
}
// If the index is not a rowid alias, record all the columns
// needed to make up the unique index and construct a SQL query for it
if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
{
// Whatever remains of the columns we need that make up the index that are not
// already in the query need to be queried separately, so construct a subquery
if (cols.Count > 0)
{
string[] querycols = new string[cols.Count];
cols.CopyTo(querycols);
query = new KeyQuery(cnn, pair.Key, table, querycols);
}
}
// Create a KeyInfo struct for each column of the index
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
KeyInfo key = new KeyInfo();
key.rootPage = rootPage;
key.cursor = cursor;
key.database = database;
key.databaseName = pair.Key;
key.tableName = table;
key.columnName = columnName;
key.query = query;
key.column = x;
keys.Add(key);
}
}
}
}
}
}
}
}
// Now we have all the additional columns we have to return in order to support
// CommandBehavior.KeyInfo
_keyInfo = new KeyInfo[keys.Count];
keys.CopyTo(_keyInfo);
}
private int?ReadNullableInt(SQLiteDataReader reader, string columnName)
{
var value = reader[columnName];
return(value == DBNull.Value ? (int?)null : Convert.ToInt32(value));
}
//读取数据库配置到From1
static public void ReadConfigsFormSQL()
{
int count = 0;
SQLiteConnection conn = null;
string strSQLiteDB = Environment.CurrentDirectory; // 获取目录
try
{
string dbPath = "Data Source=" + strSQLiteDB + "\\data.db";
conn = new SQLiteConnection(dbPath); //创建数据库实例，指定文件位置
conn.Open(); //打开数据库，若文件不存在会自动创建
string sql = "CREATE TABLE IF NOT EXISTS config(key varchar(250), value varchar(250));"; //建表语句 ,key为配置，value为值
SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn);
cmdCreateTable.ExecuteNonQuery(); //如果表不存在，创建数据表
//连接到数据库文件，使用PRAGMA命令准备
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM config";
SQLiteDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
count++;
String key = dataReader.GetString(0);
if (key == "NameOfCompany")
{
Form1.NameOfCompany = dataReader.GetString(1);
}
if (key == "LogoPath")
{
Form1.LogoPath = dataReader.GetString(1);
}
if (key == "MultipleLuck")
{
String multipleLuck = dataReader.GetString(1);
Form1.MultipleLuck = Boolean.Parse(multipleLuck);
}
if (key == "LuckType")
{
String sweepstakeType = dataReader.GetString(1);
Form1.LuckType = Boolean.Parse(sweepstakeType);
}
if (key == "HideLuckType")
{
String hideLuckType = dataReader.GetString(1);
Form1.HideLuckType = Boolean.Parse(hideLuckType);
}
if (key == "ProgramTitle")
{
String programTitle = dataReader.GetString(1);
Form1.ProgramTitle = Boolean.Parse(programTitle);
}
}
dataReader.Close();
conn.Close();
}
catch (Exception ex)
{
throw; // <-------------------
}
return;
}
private void LoadParameter()
{
string sql_command = "";
//0 , 1 , 2 , 3 , 4 , 5 , 6
sql_command += "Select S7Adress, S7Symbol, S7SymbolType, S7Comment, SymbolType, cast(RankingGroup as integer) as RankingGroup, GroupComment, ";
//7 , 8 , 9 , 10 , 11 , 12 , 13 , 14
sql_command += "cast(RankingSymbol as integer) as RankingSymbol, Comment, Unit, SymbolFormat, UpperLimit, LowerLimit, UserRightEnable, UserRightVisible, ";
//15
sql_command += "Column ";
if (this.m_formularType == FormularType.Parameter)
{
sql_command += "from plcitems where (SymbolType='P' or SymbolType='PO') order by RankingGroup, RankingSymbol";
}
if (this.m_formularType == FormularType.Info)
{
sql_command += "from plcitems where (SymbolType='I') order by RankingGroup, RankingSymbol";
}
if (this.m_formularType == FormularType.Service)
{
sql_command += "from plcitems where (SymbolType='S') order by RankingGroup, RankingSymbol";
}
this.m_sqliteCommand.CommandText = sql_command;
if (this.m_sqliteDataReader != null)
{
this.m_sqliteDataReader.Close();
this.m_sqliteDataReader = null;
}
this.m_sqliteDataReader = this.m_sqliteCommand.ExecuteReader();
int i = 0;
TabControl tab_control = this.CreateTabControl(4, 20, this.GbxOutput.Width - 8, this.GbxOutput.Height - 25, this.GbxOutput);
tab_control.TabStop = false;
this.m_tabControl = tab_control;
TabPage tab_page = null;
GroupBox group_box = null;
Label label_text = null;
Label label_unit = null;
CompToggleSwitch toggle_switch = null;
CompTxtBox txt_box = null;
CompInputBox input_box = null;
PlcItemList plc_item_list;
CompLedRectangle led_rectangle = null;
string old_group_comment = "";
int row = 0;
int old_ranging_symbol = 0;
bool first_start = true;
while (this.m_sqliteDataReader.Read())
{
plc_item_list.S7Adress = this.m_sqliteDataReader.GetValue(0).ToString();
plc_item_list.S7Symbol = this.m_sqliteDataReader.GetValue(1).ToString();
plc_item_list.S7SymbolType = this.m_sqliteDataReader.GetValue(2).ToString();
plc_item_list.SymbolType = this.m_sqliteDataReader.GetValue(4).ToString();
plc_item_list.GroupComment = this.m_sqliteDataReader.GetValue(6).ToString();
plc_item_list.RankingSymbol = Convert.ToInt32(this.m_sqliteDataReader.GetValue(7).ToString());
if (first_start)
{
first_start = false;
old_ranging_symbol = plc_item_list.RankingSymbol + 1;
}
plc_item_list.Comment = this.m_sqliteDataReader.GetValue(8).ToString();
plc_item_list.Unit = this.m_sqliteDataReader.GetValue(9).ToString();
plc_item_list.Format = this.m_sqliteDataReader.GetValue(10).ToString();
plc_item_list.Column = Convert.ToInt32(this.m_sqliteDataReader.GetValue(15).ToString());
plc_item_list.UpperLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(11).ToString());
plc_item_list.LowerLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(12).ToString());
plc_item_list.UserRightEnable = Convert.ToInt32(this.m_sqliteDataReader.GetValue(13).ToString());
string[] adress_info = plc_item_list.S7Adress.Split('.');
string db_number = adress_info[0];
string varname = db_number + "." + plc_item_list.S7Symbol;
plc_item_list.Varname = varname;
if (plc_item_list.GroupComment != old_group_comment)
{
old_ranging_symbol = plc_item_list.RankingSymbol - 1;
row = 0;
old_group_comment = plc_item_list.GroupComment;
tab_page = (TabPage)this.CreateTabPage(plc_item_list.GroupComment, tab_control);
group_box = (GroupBox)this.CreateGroupBox(4, 4, tab_control.Width - 18, tab_control.Height - 56, old_group_comment, tab_page);
}
int top = row * (GlobalVar.ConstParameterTextHeigt + GlobalVar.ConstControlSpacing) + GlobalVar.ConstGroupBoxOffset;
int text_width = group_box.Width - (5 * GlobalVar.ConstControlSpacing +
GlobalVar.ConstParameterTxtBoxWidth +
GlobalVar.ConstParameterTxtBoxWidth +
GlobalVar.ConstParameterTxtBoxWidth) -
GlobalVar.ConstOffsetCorrectWidth;
label_text = (Label)this.CreateLabel(GlobalVar.ConstControlSpacing, top, text_width,
GlobalVar.ConstParameterTextHeigt, plc_item_list.Comment,
group_box, System.Drawing.ContentAlignment.MiddleRight);
int left_unit = 0;
if (plc_item_list.S7SymbolType == "BOOL")
{
if (plc_item_list.SymbolType == "P" || plc_item_list.SymbolType == "S")
{
toggle_switch = (CompToggleSwitch)this.CreateToggleSwitch(GlobalVar.ConstControlSpacing +
label_text.Width + label_text.Left, top, group_box);
toggle_switch.Click += new System.EventHandler(this.ToggleSwitch_Click);
toggle_switch.Symbol = varname;
toggle_switch.DoSwitch = false;
this.m_dataBinding.AddList(this, toggle_switch.Name.ToString(), "State", varname);
led_rectangle = this.CreateLedRectangle(GlobalVar.ConstControlSpacing + toggle_switch.Width + toggle_switch.Left, top, group_box);
this.m_dataBinding.AddList(this, led_rectangle.Name.ToString(), "State", varname);
this.m_userManagement.AddUserRightControl(toggle_switch, plc_item_list.UserRightEnable);
}
if (plc_item_list.SymbolType == "I")
{
led_rectangle = this.CreateLedRectangle(GlobalVar.ConstControlSpacing + label_text.Width + label_text.Left, top, group_box);
this.m_dataBinding.AddList(this, led_rectangle.Name.ToString(), "State", varname);
}
}
else
{
if (plc_item_list.SymbolType == "PO" || plc_item_list.SymbolType == "I" || plc_item_list.SymbolType == "SO")
{
int left = GlobalVar.ConstControlSpacing + (plc_item_list.Column - 1) * (GlobalVar.ConstParameterTxtBoxWidth +
GlobalVar.ConstControlSpacing) +
label_text.Left +
label_text.Width;
txt_box = this.CreateTxtBox(left, top, plc_item_list.Format, group_box);
this.m_dataBinding.AddList(this, txt_box.Name.ToString(), "Text", varname);
left_unit = txt_box.Left + txt_box.Width + GlobalVar.ConstControlSpacing;
}
if (plc_item_list.SymbolType == "P" || plc_item_list.SymbolType == "S")
{
int left = GlobalVar.ConstControlSpacing + (plc_item_list.Column - 1) * (GlobalVar.ConstParameterTxtBoxWidth +
GlobalVar.ConstControlSpacing) +
label_text.Left +
label_text.Width;
input_box = this.CreateInputBox(left, top, plc_item_list.Format, group_box);
input_box.Symbol = varname;
input_box.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.InputBox_KeyPress);
input_box.Leave += new System.EventHandler(this.InputBox_Leave);
this.m_dataBinding.AddList(this, input_box.Name.ToString(), "Text", varname);
left_unit = input_box.Left + input_box.Width + GlobalVar.ConstControlSpacing;
this.m_plcItemList.Add(input_box, plc_item_list);
this.m_userManagement.AddUserRightControl(input_box, plc_item_list.UserRightEnable);
left_unit = input_box.Left + input_box.Width + GlobalVar.ConstControlSpacing;
}
}
label_unit = (Label)this.CreateLabel(left_unit, top, GlobalVar.ConstParameterTxtBoxWidth,
GlobalVar.ConstParameterTextHeigt,
plc_item_list.Unit,
group_box,
System.Drawing.ContentAlignment.MiddleLeft);
if (plc_item_list.RankingSymbol != old_ranging_symbol)
{
old_ranging_symbol = plc_item_list.RankingSymbol;
row++;
}
i++;
}
this.m_sqliteDataReader.Close();
this.m_sqliteDataReader = null;
}
private int ReadInt(SQLiteDataReader reader, string columnName)
{
return(Convert.ToInt32(reader[columnName]));
}
private void Table()
{
Application.VisualStyleState = VisualStyleState.NoneEnabled;
pnlNav.Height = sum.Height;
pnlNav.Top = sum.Top;
pnlNav.Left = sum.Left;
Info.BackColor = Color.FromArgb(24, 30, 54);
sum.BackColor = Color.FromArgb(46, 51, 73);
btnCal.BackColor = Color.FromArgb(24, 30, 54);
btnDashboard.BackColor = Color.FromArgb(24, 30, 54);
statPanel.Hide();
carbPanel.Hide();
fatPanel.Hide();
protPanel.Hide();
infoLabel.Hide();
calPanel.Hide();
add.Hide();
calendar.Hide();
all.Columns.Clear();
all.Columns.Add("ID", "ID");
all.Columns.Add("Username", "Felhasználónév");
all.Columns.Add("Food", "Étel/Ital");
all.Columns.Add("cal", "Kalória");
all.Columns.Add("prot", "Fehérje");
all.Columns.Add("fat", "Zsír");
all.Columns.Add("carb", "Szénhidrát");
all.Columns.Add("date", "Dátum");
all.Columns["ID"].Visible = false;
all.Size = new Size(625, 400);
all.Location = new Point(statusLabel.Left, 75);
all.AllowUserToAddRows = false;
all.AllowUserToDeleteRows = false;
all.ForeColor = Color.White;
all.GridColor = Color.FromArgb(158, 161, 176);
all.BorderStyle = BorderStyle.None;
all.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
all.ScrollBars = ScrollBars.Vertical;
all.EnableHeadersVisualStyles = false;
all.MultiSelect = false;
all.RowHeadersVisible = false;
all.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
all.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
all.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single;
all.ColumnHeadersDefaultCellStyle.SelectionBackColor = Color.FromArgb(46, 51, 73);
all.DefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73);
all.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73);
all.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73);
all.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
all.RowHeadersDefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73);
all.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
all.Columns["Food"].ValueType = typeof(String);
all.BackgroundColor = Color.FromArgb(46, 51, 73);
all.BackColor = Color.FromArgb(46, 51, 73);
this.Controls.Add(all);
LogBase userData = new LogBase();
userData.OpenConnection();
string query = "SELECT ROWID, * FROM DATAS WHERE USERNAME='******' AND DATE='" + DateTime.Now.ToString("yyyyMMdd") + "'";
SQLiteCommand ossz = new SQLiteCommand(query, userData.myConnection);
SQLiteDataReader result = ossz.ExecuteReader();
if (result.HasRows)
{
while (result.Read())
{
string[] temp = Convert.ToString(result["Cal"]).Split('/');
var tempDate = DateTime.ParseExact(result["Date"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture).ToString("yyyy. MM. dd.");
all.Rows.Add(result["ROWID"], result["Username"], result["Food"], temp[1] + " kcal", result["Prot"] + " g", result["Fat"] + " g", result["carb"] + " g", tempDate.ToString());
this.Controls.Add(all);
}
}
delete.Location = new Point(862, 515);
delete.Size = new Size(100, 50);
delete.Image = Image.FromFile("delete.png");
delete.SizeMode = PictureBoxSizeMode.Zoom;
delete.Cursor = Cursors.Hand;
this.Controls.Add(delete);
delete.Show();
}
private string ReadString(SQLiteDataReader reader, string columnName)
{
return(reader[columnName].ToString());
}
public Food(string name)
{
this.calendar.ValueChanged += new System.EventHandler(this.calendar_ValueChanged);
this.delete.Click += new System.EventHandler(this.delete_Click);
username = name;
InitializeComponent();
Region = System.Drawing.Region.FromHrgn(CreateRoundRectRgn(0, 0, Width, Height, 25, 25));
userName.Text = name;
pnlNav.Height = btnDashboard.Height;
pnlNav.Top = btnDashboard.Top;
pnlNav.Left = btnDashboard.Left;
calendar.Location = new Point(statusLabel.Left, 55);
this.Controls.Add(calendar);
calendar.Hide();
btnDashboard.BackColor = Color.FromArgb(46, 51, 73);
LogBase userData = new LogBase();
string query = "SELECT * FROM USER WHERE USERNAME='******'";
userData.OpenConnection();
SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection);
SQLiteDataReader result = getUser.ExecuteReader();
double calFull = 0;
double calBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (Convert.ToInt32(result["Sex"]) == 1)
{
calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"]));
}
if (Convert.ToInt32(result["Sex"]) == 0)
{
calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"]));
}
goal = Convert.ToInt32(result["Goal"]);
}
}
query = "SELECT * FROM DATAS WHERE USERNAME='******'";
SQLiteCommand getData = new SQLiteCommand(query, userData.myConnection);
result = getData.ExecuteReader();
double carbBevitt = 0;
double protBevitt = 0;
double fatBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (DateTime.Now.ToString("yyyyMMdd") == result["Date"].ToString())
{
string[] temp = Convert.ToString(result["Cal"]).Split('/');
calBevitt = Convert.ToDouble(temp[1]) + calBevitt;
carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]);
protBevitt = protBevitt + Convert.ToDouble(result["Prot"]);
fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]);
}
}
}
userData.CloseConnection();
if (goal == 0)
{
calFull -= 200;
}
if (goal == 1)
{
calFull += 500;
}
double carbFull = (calFull * 0.45) / 4;
double protFull = (calFull * 0.35) / 4;
double fatFull = (calFull * 0.20) / 8;
double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero);
if (percent < 100)
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = Convert.ToInt32(percent);
}
else
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = 100;
}
fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g";
protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g";
carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g";
cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal";
}
private void Food_Activated(object sender, EventArgs e)
{
LogBase userData = new LogBase();
string query = "SELECT * FROM USER WHERE USERNAME='******'";
userData.OpenConnection();
SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection);
SQLiteDataReader result = getUser.ExecuteReader();
double calFull = 0;
double calBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (Convert.ToInt32(result["Sex"]) == 1)
{
calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"]));
}
if (Convert.ToInt32(result["Sex"]) == 0)
{
calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"]));
}
goal = Convert.ToInt32(result["Goal"]);
}
}
query = "SELECT * FROM DATAS WHERE USERNAME='******'";
SQLiteCommand getData = new SQLiteCommand(query, userData.myConnection);
result = getData.ExecuteReader();
double carbBevitt = 0;
double protBevitt = 0;
double fatBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (DateTime.Now.ToString("yyyyMMdd") == result["Date"].ToString())
{
string[] temp = Convert.ToString(result["Cal"]).Split('/');
calBevitt = Convert.ToDouble(temp[1]) + calBevitt;
carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]);
protBevitt = protBevitt + Convert.ToDouble(result["Prot"]);
fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]);
}
}
}
userData.CloseConnection();
if (goal == 0)
{
calFull -= 200;
}
if (goal == 1)
{
calFull += 500;
}
double carbFull = (calFull * 0.45) / 4;
double protFull = (calFull * 0.35) / 4;
double fatFull = (calFull * 0.20) / 8;
double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero);
if (percent < 100)
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = Convert.ToInt32(percent);
}
else
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = 100;
}
fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g";
protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g";
carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g";
cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal";
}
private void setCalendar()
{
if (Convert.ToDateTime(calendar.Value) > DateTime.Now)
{
Application.VisualStyleState = VisualStyleState.ClientAndNonClientAreasEnabled;
MessageBox.Show("A kiválasztott dátum nem lehet későbbi mint a mai dátum!");
}
else
{
LogBase userData = new LogBase();
statPanel.Show();
carbPanel.Show();
fatPanel.Show();
protPanel.Show();
calPanel.Show();
string query = "SELECT * FROM USER WHERE USERNAME='******'";
userData.OpenConnection();
SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection);
SQLiteDataReader result = getUser.ExecuteReader();
double calFull = 0;
double calBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (Convert.ToInt32(result["Sex"]) == 1)
{
calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"]));
}
if (Convert.ToInt32(result["Sex"]) == 0)
{
calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"]));
}
goal = Convert.ToInt32(result["Goal"]);
}
}
query = "SELECT * FROM DATAS WHERE USERNAME='******' AND DATE='" + Convert.ToDateTime(calendar.Value).ToString("yyyyMMdd") + "'";
SQLiteCommand keres = new SQLiteCommand(query, userData.myConnection);
result = keres.ExecuteReader();
double carbBevitt = 0;
double protBevitt = 0;
double fatBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
string[] temp = Convert.ToString(result["Cal"]).Split('/');
calBevitt = Convert.ToDouble(temp[1]) + calBevitt;
carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]);
protBevitt = protBevitt + Convert.ToDouble(result["Prot"]);
fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]);
}
}
userData.CloseConnection();
if (goal == 0)
{
calFull -= 200;
}
if (goal == 1)
{
calFull += 500;
}
double carbFull = (calFull * 0.45) / 4;
double protFull = (calFull * 0.35) / 4;
double fatFull = (calFull * 0.20) / 8;
double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero);
if (percent < 100)
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = Convert.ToInt32(percent);
}
else
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = 100;
}
fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g";
protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g";
carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g";
cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal";
dailyCal.Text = "Összesen elfogyasztott kalória\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd.");
dailyFat.Text = "Összesen elfogyasztott zsír\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd.");
dailyProt.Text = "Összesen elfogyasztott fehérje\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd.");
dailyCarb.Text = "Összesen elfogyasztott szénhidrát\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd.");
dailyPercent.Text = "Összesen elfogyasztott kalória %\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd.");
}
}
private void btnDashboard_Click(object sender, EventArgs e)
{
Application.VisualStyleState = VisualStyleState.ClientAndNonClientAreasEnabled;
pnlNav.Height = btnDashboard.Height;
pnlNav.Top = btnDashboard.Top;
pnlNav.Left = btnDashboard.Left;
btnDashboard.BackColor = Color.FromArgb(46, 51, 73);
btnCal.BackColor = Color.FromArgb(24, 30, 54);
Info.BackColor = Color.FromArgb(24, 30, 54);
infoLabel.Hide();
statPanel.Show();
carbPanel.Show();
fatPanel.Show();
protPanel.Show();
calPanel.Show();
add.Show();
calendar.Hide();
all.Hide();
delete.Hide();
dailyCal.Text = "Összesen elfogyasztott kalória";
dailyFat.Text = "Összesen elfogyasztott zsír";
dailyProt.Text = "Összesen elfogyasztott fehérje";
dailyCarb.Text = "Összesen elfogyasztott szénhidrát";
dailyPercent.Text = "Összesen elfogyasztott kalória %";
sum.BackColor = Color.FromArgb(24, 30, 54);
LogBase userData = new LogBase();
string query = "SELECT * FROM USER WHERE USERNAME='******'";
userData.OpenConnection();
SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection);
SQLiteDataReader result = getUser.ExecuteReader();
double calFull = 0;
double calBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (Convert.ToInt32(result["Sex"]) == 1)
{
calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"]));
}
if (Convert.ToInt32(result["Sex"]) == 0)
{
calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"]));
}
goal = Convert.ToInt32(result["Goal"]);
}
}
query = "SELECT * FROM DATAS WHERE USERNAME='******'";
SQLiteCommand getData = new SQLiteCommand(query, userData.myConnection);
result = getData.ExecuteReader();
double carbBevitt = 0;
double protBevitt = 0;
double fatBevitt = 0;
if (result.HasRows)
{
while (result.Read())
{
if (DateTime.Now.ToString("yyyyMMdd") == result["Date"].ToString())
{
string[] temp = Convert.ToString(result["Cal"]).Split('/');
calBevitt = Convert.ToDouble(temp[1]) + calBevitt;
carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]);
protBevitt = protBevitt + Convert.ToDouble(result["Prot"]);
fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]);
}
}
}
userData.CloseConnection();
if (goal == 0)
{
calFull -= 200;
}
if (goal == 1)
{
calFull += 500;
}
double carbFull = (calFull * 0.45) / 4;
double protFull = (calFull * 0.35) / 4;
double fatFull = (calFull * 0.20) / 8;
double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero);
if (percent < 100)
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = Convert.ToInt32(percent);
}
else
{
statPercent.Text = percent.ToString() + "%";
statPercent.Value = 100;
}
fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g";
protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g";
carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g";
cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal";
statusLabel.Text = "Főoldal";
}
private void bwPopuate_DoWork(object sender, DoWorkEventArgs e)
{
string kit = (string)e.Argument;
SQLiteConnection cnn = GGKUtilLib.getDBConnection();
//kit master
SQLiteCommand query = new SQLiteCommand(@"SELECT name, sex from kit_master where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
SQLiteDataReader reader = query.ExecuteReader();
string sex = "U";
if (reader.Read())
{
this.Invoke(new MethodInvoker(delegate
{
txtName.Text = reader.GetString(0);
sex = reader.GetString(1);
if (sex == "U")
{
cbSex.SelectedIndex = 0;
}
else if (sex == "M")
{
cbSex.SelectedIndex = 1;
}
else if (sex == "F")
{
cbSex.SelectedIndex = 2;
}
}));
}
reader.Close();
query.Dispose();
// kit autosomal - RSID,Chromosome,Position,Genotypoe
query = new SQLiteCommand(@"SELECT rsid 'RSID',chromosome 'Chromosome',position 'Position',genotype 'Genotype' from kit_autosomal where kit_no=@kit_no order by chromosome,position", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
this.Invoke(new MethodInvoker(delegate
{
dataGridViewAutosomal.Rows.Clear();
dataGridViewAutosomal.Columns.Clear();
dataGridViewAutosomal.DataSource = dt;
dataGridViewAutosomal.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewAutosomal.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewAutosomal.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridViewAutosomal.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[0]).MaxInputLength = 20;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[1]).MaxInputLength = 2;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[2]).MaxInputLength = 15;
((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[3]).MaxInputLength = 2;
}));
reader.Close();
query.Dispose();
// kit - ysnp
query = new SQLiteCommand(@"SELECT ysnps from kit_ysnps where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
if (reader.Read())
{
ysnps = reader.GetString(0);
}
reader.Close();
query.Dispose();
this.Invoke(new MethodInvoker(delegate
{
textBoxYDNA.Text = ysnps;
}));
// kit - ystr
query = new SQLiteCommand(@"SELECT marker,value from kit_ystr where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
string marker = null;
string value = null;
Dictionary <string, string> ystr_dict = new Dictionary <string, string>();
while (reader.Read())
{
marker = reader.GetString(0);
value = reader.GetString(1);
ystr_dict.Add(marker, value);
}
reader.Close();
query.Dispose();
this.Invoke(new MethodInvoker(delegate
{
//
//YDNA12
for (int i = 0; i < ydna12.Length; i++)
{
if (ystr_dict.ContainsKey(ydna12[i]))
{
dgvy12.Rows[i].Cells[1].Value = ystr_dict[ydna12[i]];
ystr_dict.Remove(ydna12[i]);
}
}
//YDNA25
for (int i = 0; i < ydna25.Length; i++)
{
if (ystr_dict.ContainsKey(ydna25[i]))
{
dgvy25.Rows[i].Cells[1].Value = ystr_dict[ydna25[i]];
ystr_dict.Remove(ydna25[i]);
}
}
//YDNA37
for (int i = 0; i < ydna37.Length; i++)
{
if (ystr_dict.ContainsKey(ydna37[i]))
{
dgvy37.Rows[i].Cells[1].Value = ystr_dict[ydna37[i]];
ystr_dict.Remove(ydna37[i]);
}
}
//YDNA67
for (int i = 0; i < ydna67.Length; i++)
{
if (ystr_dict.ContainsKey(ydna67[i]))
{
dgvy67.Rows[i].Cells[1].Value = ystr_dict[ydna67[i]];
ystr_dict.Remove(ydna67[i]);
}
}
//YDNA111
for (int i = 0; i < ydna111.Length; i++)
{
if (ystr_dict.ContainsKey(ydna111[i]))
{
dgvy111.Rows[i].Cells[1].Value = ystr_dict[ydna111[i]];
ystr_dict.Remove(ydna111[i]);
}
}
//YDNA MISC
dgvymisc.Rows.Clear();
foreach (string str in ystr_dict.Keys)
{
dgvymisc.Rows.Add(new string[] { str, ystr_dict[str] });
}
}));
// kit - mtdna
query = new SQLiteCommand(@"SELECT mutations,fasta from kit_mtdna where kit_no=@kit_no", cnn);
query.Parameters.AddWithValue("@kit_no", kit);
reader = query.ExecuteReader();
string fasta = null;
if (reader.Read())
{
this.Invoke(new MethodInvoker(delegate
{
textBoxMtDNA.Text = reader.GetString(0);
fasta = reader.GetString(1);
if (fasta != null)
{
tbFASTA.Text = fasta;
}
else
{
tbFASTA.Text = "";
}
}));
}
reader.Close();
query.Dispose();
}
/// <summary>
/// Overrides the default behavior to return a SQLiteDataReader specialization class
/// </summary>
/// <param name="behavior">The flags to be associated with the reader.</param>
/// <returns>A SQLiteDataReader</returns>
public new SQLiteDataReader ExecuteReader(CommandBehavior behavior)
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
InitializeForReader();
SQLiteDataReader rd = new SQLiteDataReader(this, behavior);
_activeReader = new WeakReference(rd, false);
return rd;
}
private string ReadNullableString(SQLiteDataReader reader, string columnName)
{
var value = reader[columnName];
return(value == DBNull.Value ? null : value.ToString());
}
private static SQLiteDataReader CreateReader()
{
var command = new SQLiteCommand();
var reader = new SQLiteDataReader(command, new StatementHandle[0], 0);
command.OpenReader = reader;
return reader;
}
private void LoadError()
{
string sql_command = "";
//0 , 1 , 2 , 3 , 4 , 5 , 6
sql_command += "Select S7Adress, S7Symbol, S7SymbolType, S7Comment, SymbolType, cast(RankingGroup as integer) as RankingGroup, GroupComment, ";
//7 , 8 , 9 , 10 , 11 , 12 , 13 , 14
sql_command += "cast(RankingSymbol as integer) as RankingSymbol, Comment, Unit, SymbolFormat, UpperLimit, LowerLimit, UserRightEnable, UserRightVisible, ";
//15
sql_command += "Column ";
if (this.m_formularType == FormularType.Error)
{
sql_command += "from plcitems where (SymbolType='E') order by RankingGroup, RankingSymbol";
}
if (this.m_formularType == FormularType.Release)
{
sql_command += "from plcitems where (SymbolType='R') order by RankingGroup, RankingSymbol";
}
this.m_sqliteCommand.CommandText = sql_command;
if (this.m_sqliteDataReader != null)
{
this.m_sqliteDataReader.Close();
this.m_sqliteDataReader = null;
}
this.m_sqliteDataReader = this.m_sqliteCommand.ExecuteReader();
TabControl tab_control = this.CreateTabControl(4, 20, this.GbxOutput.Width - 8, this.GbxOutput.Height - 25, this.GbxOutput);
tab_control.TabStop = false;
this.m_tabControl = tab_control;
TabPage tab_page = null;
int page_no = 0;
tab_page = this.CreateTabPage("Seite " + (page_no + 1), tab_control);
int gbx_row = 0;
int gbx_col = 0;
GroupBox group_box = null;
Label label_text = null;
PlcItemList plc_item_list;
CompLedRound led_round = null;
string old_group_comment = "";
int row_comp = 0;
int gbx_width = (tab_control.DisplayRectangle.Width + 10) / GlobalVar.ConstNumberOfColumns - (GlobalVar.ConstNumberOfColumns + 1) * 4;
while (this.m_sqliteDataReader.Read())
{
plc_item_list.S7Adress = this.m_sqliteDataReader.GetValue(0).ToString();
plc_item_list.S7Symbol = this.m_sqliteDataReader.GetValue(1).ToString();
plc_item_list.S7SymbolType = this.m_sqliteDataReader.GetValue(2).ToString();
plc_item_list.SymbolType = this.m_sqliteDataReader.GetValue(4).ToString();
plc_item_list.GroupComment = this.m_sqliteDataReader.GetValue(6).ToString();
plc_item_list.RankingSymbol = Convert.ToInt32(this.m_sqliteDataReader.GetValue(7).ToString());
plc_item_list.Comment = this.m_sqliteDataReader.GetValue(8).ToString();
plc_item_list.Unit = this.m_sqliteDataReader.GetValue(9).ToString();
plc_item_list.Format = this.m_sqliteDataReader.GetValue(10).ToString();
plc_item_list.Column = Convert.ToInt32(this.m_sqliteDataReader.GetValue(15).ToString());
plc_item_list.UpperLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(11).ToString());
plc_item_list.LowerLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(12).ToString());
plc_item_list.UserRightEnable = Convert.ToInt32(this.m_sqliteDataReader.GetValue(13).ToString());
string[] adress_info = plc_item_list.S7Adress.Split('.');
string db_number = adress_info[0];
string varname = db_number + "." + plc_item_list.S7Symbol;
plc_item_list.Varname = varname;
int top = 0;
int left = 0;
if (old_group_comment != plc_item_list.GroupComment)
{
old_group_comment = plc_item_list.GroupComment;
top = 0;
left = 0;
if (gbx_row == 0)
{
top = 4;
left = 4;
gbx_row++;
}
else
{
left = group_box.Left;
top = group_box.Top + group_box.Height + 4;
}
group_box = this.CreateGroupBox(left, top, gbx_width, 50, plc_item_list.GroupComment, tab_page);
row_comp = 0;
}
int height = (row_comp) * 27 + 50;
int height_max = height + group_box.Top;
if (height_max > tab_control.DisplayRectangle.Height)
{
gbx_col++;
if (gbx_col > 2)
{
page_no++;
tab_page = this.CreateTabPage("Seite " + (page_no + 1), tab_control);
gbx_row = 0;
gbx_col = 0;
}
else
{
left = gbx_col * (gbx_width + 4) + 4;
top = 4;
group_box = this.CreateGroupBox(left, top, gbx_width, 50, plc_item_list.GroupComment, tab_page);
row_comp = 0;
}
}
height = (row_comp) * 27 + 50;
group_box.Height = height;
int top_comp = row_comp * 27 + 20;
int left_comp = 4;
if (this.m_formularType == FormularType.Release)
{
led_round = this.CreateLedRound(left_comp, top_comp, group_box, CompLedRound.LEDType.Release);
}
if (this.m_formularType == FormularType.Error)
{
led_round = this.CreateLedRound(left_comp, top_comp, group_box, CompLedRound.LEDType.Error);
}
this.m_dataBinding.AddList(this, led_round.Name.ToString(), "State", varname);
label_text = this.CreateLabel(left_comp + 40, top_comp, 200, 25, plc_item_list.Comment, group_box, ContentAlignment.MiddleLeft);
row_comp++;;
}
this.m_sqliteDataReader.Close();
this.m_sqliteDataReader = null;
}
