private void TilesGenerate()
{
//Looking for startDate and finishDate
Diapazon diapazon = new Diapazon(KeyDate);
DateTime startDate = diapazon.startDate;
DateTime finishDate = diapazon.finishDate;
DateTime date = startDate;
int baseLeft = 40;
int tileWidth = 49;
int betweenTilesV = 1;
int betweenTilesH = 7;
int baseTop = 60;
int tileHeight = 6;
tiles.Clear();
for (int day = 0; day < 21; day++) //Three weeks
{
for (int unit = 0; unit < 24 * 4; unit++)
{ //for possible lesson unit per hour
Tile tile = new Tile();
tile.left = baseLeft + day * (tileWidth + betweenTilesH);
tile.width = tileWidth;
tile.top = baseTop + unit * (tileHeight + betweenTilesV);
tile.height = tileHeight;
//tile.text = "Tile"
bool isEvenHour = unit / 4 % 2 == 0;
if (isEvenHour)
{
tile.color = Color.LightSeaGreen;
}
tiles.Add(tile);
}
}
LessonsListManager lessonsListManager = new LessonsListManager(dbPath);
List <LessonInfo> lessonInfos = lessonsListManager.getLessonsList(startDate, finishDate);
for (int i = 0; i < lessonInfos.Count; i++)
{
LessonInfo lessonInfo = lessonInfos[i];
int tileNum = 0;
DateTime lessonDateTime = startDate;
while (lessonDateTime < lessonInfo.DateAndTime)
{
lessonDateTime = lessonDateTime.AddMinutes(15); //***
tileNum++;
}
tiles[tileNum].height = lessonInfo.duration * tileHeight + (lessonInfo.duration - 1) * betweenTilesV;
tiles[tileNum].text = lessonInfo.studentInfo.name;
tiles[tileNum].color = Color.LightGreen;
tiles[tileNum].free = false;
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (comboBoxStudentsNames.SelectedIndex == -1)
{
return;
}
if (comboBoxSubjects.SelectedIndex == -1)
{
return;
}
LessonInfo lessonInfo = new LessonInfo();
lessonInfo.DateAndTime = dateTimePicker.Value;
lessonInfo.duration = (int)numericUpDownDuration.Value;
lessonInfo.studentInfo = studentInfos[comboBoxStudentsNames.SelectedIndex];
lessonInfo.subjectInfo = subjectInfos[comboBoxSubjects.SelectedIndex];
lessonInfo.topic = textBoxTopic.Text;
lessonInfo.done = checkBoxDone.Checked;
lessonInfo.price = (int)numericUpDownPrice.Value;
lessonInfo.paid = checkBoxPaid.Checked;
lessonInfo.remark = textBoxRemark.Text;
lessonsListManager.AddLesson(lessonInfo);
RefreshAllInfo();
}
public void DelLesson(LessonInfo lessonInfo)
{
SQLiteCommand cmd = connection.CreateCommand();
cmd.CommandText = @"DELETE FROM lessons WHERE id = @id";
cmd.Parameters.AddWithValue("@id", lessonInfo.id);
cmd.ExecuteNonQuery(); // выполнить запрос;
}
private void buttonDelLesson_Click(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
{
return;
}
LessonInfo lessonInfo = lessonInfos[listBox1.SelectedIndex];
lessonsListManager.DelLesson(lessonInfo);
RefreshAllInfo();
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listBox1.SelectedIndex == -1)
{
//Todo: Clear controls ==> it seems it is not nessesery
return;
}
LessonInfo lessonInfo = lessonInfos[listBox1.SelectedIndex];
dateTimePicker.Value = lessonInfo.DateAndTime;
numericUpDownDuration.Value = lessonInfo.duration;
//------------
int student_id = lessonInfo.studentInfo.id;
int idx_stud = -1;
for (int i = 0; i < studentInfos.Count; i++)
{
if (studentInfos[i].id == student_id)
{
idx_stud = i;
}
}
comboBoxStudentsNames.SelectedIndex = idx_stud;
//------------
int subject_id = lessonInfo.subjectInfo.id;
int idx_subj = -1;
for (int i = 0; i < subjectInfos.Count; i++)
{
if (subjectInfos[i].id == subject_id)
{
idx_subj = i;
}
}
comboBoxSubjects.SelectedIndex = idx_subj;
//------------
textBoxTopic.Text = lessonInfo.topic;
checkBoxPaid.Checked = lessonInfo.paid;
checkBoxDone.Checked = lessonInfo.done;
numericUpDownPrice.Value = lessonInfo.price;
textBoxRemark.Text = lessonInfo.remark;
}
public void AddLesson(LessonInfo lessonInfo)
{
string commandText = "INSERT INTO lessons (id, DateAndTime, duration, student_id, subject_id," +
"topic, done, price, paid, remark ) " +
"VALUES (@id, @DateAndTime, @duration, @student_id, @subject_id, " +
"@topic, @done, @price, @paid, @remark)";
SQLiteCommand Command = new SQLiteCommand(commandText, connection);
Command.Parameters.AddWithValue("@id", null);
Command.Parameters.AddWithValue("@DateAndTime", lessonInfo.DateAndTime.ToString(@"yyyy/MM/dd/ hh:mm:ss"));
Command.Parameters.AddWithValue("@duration", lessonInfo.duration);
Command.Parameters.AddWithValue("@student_id", lessonInfo.studentInfo.id);
Command.Parameters.AddWithValue("@subject_id", lessonInfo.subjectInfo.id);
Command.Parameters.AddWithValue("@topic", lessonInfo.topic);
Command.Parameters.AddWithValue("@done", lessonInfo.done);
Command.Parameters.AddWithValue("@price", lessonInfo.price);
Command.Parameters.AddWithValue("@paid", lessonInfo.paid);
Command.Parameters.AddWithValue("@remark", lessonInfo.remark);
Command.ExecuteNonQuery();
}
public void ModifyLesson(LessonInfo lessonInfo)
{
string commandText = "UPDATE lessons SET DateAndTime=@DateAndTime, " +
"duration=@duration, student_id=@student_id, subject_id=@subject_id, " +
"topic=@topic, done=@done, price=@price, paid=@paid, remark=@remark " +
"WHERE id=@id;";
SQLiteCommand Command = new SQLiteCommand(commandText, connection);
Command.Parameters.AddWithValue("@id", lessonInfo.id);
Command.Parameters.AddWithValue("@DateAndTime", lessonInfo.DateAndTime.ToString(@"yyyy/MM/dd/ hh:mm:ss"));
Command.Parameters.AddWithValue("@duration", lessonInfo.duration);
Command.Parameters.AddWithValue("@student_id", lessonInfo.studentInfo.id);
Command.Parameters.AddWithValue("@subject_id", lessonInfo.subjectInfo.id);
Command.Parameters.AddWithValue("@topic", lessonInfo.topic);
Command.Parameters.AddWithValue("@done", lessonInfo.done);
Command.Parameters.AddWithValue("@price", lessonInfo.price);
Command.Parameters.AddWithValue("@paid", lessonInfo.paid);
Command.Parameters.AddWithValue("@remark", lessonInfo.remark);
Command.ExecuteNonQuery();
}
public List <LessonInfo> getLessonsList(DateTime from = new DateTime(),
DateTime to = new DateTime(),
StudentInfo studentInfo = new StudentInfo(),
SubjectInfo subjectInfo = new SubjectInfo())
{
List <LessonInfo> list = new List <LessonInfo>();
//---------------------------------------------
DateTime def = new DateTime();
if (to == def)
{
to = new DateTime(9999, to.Month, to.Day);
}
//---------------------------------------------
SQLiteCommand cmd = connection.CreateCommand();
string query = @"SELECT * FROM lessons WHERE @from <= DateAndTime and DateAndTime <= @to";
//---------------------------------------------
StudentInfo def_stud = new StudentInfo();
if (!studentInfo.Equals(def_stud))
{
query += $" and student_id = {studentInfo.id}";
}
//---------------------------------------------
SubjectInfo def_subj = new SubjectInfo();
if (!subjectInfo.Equals(def_subj))
{
query += $" and subject_id = {subjectInfo.id}";
}
//---------------------------------------------
query += ";";
//---------------------------------------------
cmd.CommandText = query;
string fromS = from.ToString(@"yyyy/MM/dd HH:mm:ss");
string toS = to.ToString(@"yyyy/MM/dd HH:mm:ss");
cmd.Parameters.AddWithValue("@from", fromS);
cmd.Parameters.AddWithValue("@to", toS);
SQLiteDataReader sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
LessonInfo lessonInfo = new LessonInfo();
lessonInfo.id = Convert.ToInt32(sqlReader["id"].ToString());
//-------------
string formatString = "yyyyMMddHHmmss";
string sample = sqlReader["DateAndTime"].ToString();
string[] words = sample.Split(new char[] { ' ', '.', ':' });
sample = string.Join("", words);
lessonInfo.DateAndTime = DateTime.ParseExact(sample, formatString, null);
//-------------
lessonInfo.duration = Int32.Parse(sqlReader["duration"].ToString());
//-------------
int student_id = Int32.Parse(sqlReader["student_id"].ToString());
StudentsListManager studentsListManager = new StudentsListManager(dbPath);
lessonInfo.studentInfo = studentsListManager.getStudentInfoById(student_id);
//-------------
int subject_id = Int32.Parse(sqlReader["subject_id"].ToString());
SubjectsListManager subjectsListManager = new SubjectsListManager(dbPath);
lessonInfo.subjectInfo = subjectsListManager.getSubjectInfoById(subject_id);
//-------------
lessonInfo.topic = sqlReader["topic"].ToString();
lessonInfo.done = sqlReader["done"].ToString().Equals("0") ? false : true;
lessonInfo.price = Int32.Parse(sqlReader["price"].ToString());
lessonInfo.paid = sqlReader["paid"].ToString().Equals("0") ? false : true;
lessonInfo.remark = sqlReader["remark"].ToString();
list.Add(lessonInfo);
}
return(list);
}
