public notebooks[] getShablonDate()
{
notebooks[] request_note = new notebooks[getCountShablonDate()];
SQLiteConnection connection = openConnection();
string query = "SELECT * FROM notebook WHERE date(strftime('%Y', 'now')||strftime('-%m-%d', date)) BETWEEN date(strftime('%Y-%m-%d', 'now'), '-1 day') and date(strftime('%Y-%m-%d', 'now'), '+7 day') ORDER BY lastname, firstname, secondname;";
SQLiteCommand Command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = Command.ExecuteReader();
int i = 0;
while (reader.Read())
{
request_note[i].id = Convert.ToInt32(reader["id"]);
request_note[i].firstname = reader["firstname"].ToString();
request_note[i].lastname = reader["lastname"].ToString();
request_note[i].secondname = reader["secondname"].ToString();
request_note[i].sex = Convert.ToInt32(reader["sex"]);
request_note[i].date = Convert.ToDateTime(reader["date"]);
i++;
}
reader.Close();
closeConnection(ref connection);
return(request_note);
}
public notebooks[] getMultiNote(int count)
{
//Запрос всех анкет
notebooks[] request_note = new notebooks[count];
SQLiteConnection connection = openConnection();
string query = "SELECT * FROM notebook ORDER BY lastname, firstname, secondname;";
SQLiteCommand Command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = Command.ExecuteReader();
int i = 0;
while (reader.Read())
{
request_note[i].id = Convert.ToInt32(reader["id"]);
request_note[i].firstname = reader["firstname"].ToString();
request_note[i].lastname = reader["lastname"].ToString();
request_note[i].secondname = reader["secondname"].ToString();
request_note[i].sex = Convert.ToInt32(reader["sex"]);
request_note[i].date = Convert.ToDateTime(reader["date"]);
request_note[i].job = reader["job"].ToString();
request_note[i].position = reader["position"].ToString();
request_note[i].more = reader["more"].ToString();
i++;
}
reader.Close();
closeConnection(ref connection);
return(request_note);
}
public void setSingleNote(notebooks post)
{
// Изменение записи в таблицу notebook
SQLiteConnection connection = openConnection();
string query = "UPDATE notebook SET lastname = '" + post.lastname + "', firstname = '" + post.firstname + "', secondname = '" + post.secondname + "', sex = '" + post.sex + "', date = '" + post.date.ToString("yyyy-MM-dd HH:mm:ss.sss") + "', job = '" + post.job + "', position = '" + post.position + "', more = '" + post.more + "' WHERE id = '" + post.id + "';";
SQLiteCommand Command = new SQLiteCommand(query, connection);
Command.ExecuteNonQuery();
closeConnection(ref connection);
}
public void addSingleNote(notebooks post)
{
// Добавление записи в таблицу notebook
SQLiteConnection connection = openConnection();
string query = "INSERT INTO notebook (lastname, firstname, secondname, sex, date, job, position, more) VALUES ('" + post.lastname + "','" + post.firstname + "','" + post.secondname + "','" + post.sex + "','" + post.date.ToString("yyyy-MM-dd HH:mm:ss.sss") + "','" + post.job + "','" + post.position + "','" + post.more + "');";
SQLiteCommand Command = new SQLiteCommand(query, connection);
Command.ExecuteNonQuery();
closeConnection(ref connection);
}
private void Form1_Load(object sender, EventArgs e)
{
int size = note.getCountNote();
notebooks[] answer = new notebooks[size];
phones[] phoneAnswer = new phones[note.getCountMultiPhone()];
answer = note.getMultiNote(size);
phoneAnswer = note.getMultiPhone();
createFormObject(size, answer, phoneAnswer);
}
private void formEdit_Load(object sender, EventArgs e)
{
this.Text = (id > 0) ? "Изменить" : "Добавить";
edit.Text = (id > 0) ? "Изменить" : "Добавить";
if (id > 0)
{
notebooks answer = note.getSingleNote(id);
firstname.Text = answer.firstname;
secondname.Text = answer.secondname;
lastname.Text = answer.lastname;
date.Value = answer.date;
job.Text = answer.job;
position.Text = answer.position;
more.Text = answer.more;
if (answer.sex == 0)
{
sex1.Checked = true;
}
else
{
sex2.Checked = true;
}
int size = note.getCountPhone(id);
if (size > 0)
{
phones[] phoneAnswer = note.getSinglePhone(id);
for (int i = 0; i < size; i++)
{
Control[] temp = phone.Controls.Find("phone" + (i + 1), true);
temp[0].Text = phoneAnswer[i].phone;
temp = phone.Controls.Find("note" + (i + 1), true);
temp[0].Text = phoneAnswer[i].note;
}
}
addresss addressAnswer = note.getSingleAddress(id);
address.Text = addressAnswer.address;
city.Text = addressAnswer.city;
state.Text = addressAnswer.state;
country.Text = addressAnswer.country;
postal.Text = addressAnswer.postal;
}
}
private void refreshForm()
{
if (refresh == true)
{
notes.Controls.Clear();
int size = note.getCountNote();
notebooks[] answer = new notebooks[size];
phones[] phoneAnswer = new phones[note.getCountMultiPhone()];
answer = note.getMultiNote(size);
phoneAnswer = note.getMultiPhone();
createFormObject(size, answer, phoneAnswer);
refresh = false;
}
}
private void rebuilds()
{
if (!note.existShablon(id))
{
int[] idShablon = note.getIdShablon(id);
if (idShablon.Length > 0)
{
Random rand = new Random();
note.addLink(id, idShablon[rand.Next(0, idShablon.Length - 1)]);
}
}
string temp = note.getSinleShablon(id);
notebooks answer = note.getSingleNote(id);
temp = temp.Replace("%firstname%", answer.firstname);
temp = temp.Replace("%secondname%", answer.secondname);
temp = temp.Replace("%lastname%", answer.lastname);
text.Text = temp;
}
