public ChecklistDTO GetChecklist(int id)
{
ChecklistDTO checklist;
this.ConnectToDatabase();
MySqlCommand command = this.mySQLConnection.CreateCommand();
command.CommandText = "SELECT * FROM CHECKLIST WHERE CHECKLIST_ID = " + id;
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int checklistId = reader.GetInt32(0);
int cardId = reader.GetInt32(1);
int checklistIndex = reader.GetInt32(2);
string title = reader.GetString(3);
byte status = reader.GetByte(4);
checklist = new ChecklistDTO(checklistId, cardId, checklistIndex, title, status);
return(checklist);
}
reader.Close();
this.Close();
return(null);
}
public List <ChecklistDTO> GetAllCheckedlist(int _cardId)
{
List <ChecklistDTO> listChecklist = new List <ChecklistDTO>();
this.ConnectToDatabase();
MySqlCommand command = this.mySQLConnection.CreateCommand();
command.CommandText = "SELECT * FROM CHECKLIST WHERE CARD_ID = '" + _cardId + "' AND STATUS = '" + 1 + "'";
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int checklistId = reader.GetInt32(0);
int cardId = reader.GetInt32(1);
int checklistIndex = reader.GetInt32(2);
string title = reader.GetString(3);
byte status = reader.GetByte(4);
ChecklistDTO checklist = new ChecklistDTO(checklistId, cardId, checklistIndex, title, status);
listChecklist.Add(checklist);
}
reader.Close();
this.Close();
return(listChecklist);
}
public bool UpdateChecklist(ChecklistDTO checklist)
{
this.ConnectToDatabase();
string Query = "update CHECKLIST set INDEX_CHECKLIST = '" + checklist.ChecklistIndex + "',TITLE = '" + checklist.Title + "',STATUS = '" + checklist.Status + "' WHERE TITLE = '" + checklist.Title + "'";
//This is command class which will handle the query and connection object.
MySqlCommand command = new MySqlCommand(Query, mySQLConnection);
command.ExecuteNonQuery();
this.Close();
return(true);
}
private void SaveButton_Click(object sender, EventArgs e)
{
cardDTO.Title = this.CardName.Text;
cardDTO.Description = descriptionText.Text;
progressBar1.Value = 0;
progressBar1.Maximum = checklistDTOs.Count();
foreach (CheckBox task in tasks)
{
if (task.Checked == true)
{
progressBar1.Increment(1);
checklistDTO = checklistBLL.GetChecklist(task.Text);
checklistDTO.Status = 1;
checklistBLL.UpdateChecklist(checklistDTO);
}
else
{
checklistDTO = checklistBLL.GetChecklist(task.Text);
checklistDTO.Status = 0;
checklistBLL.UpdateChecklist(checklistDTO);
}
}
if (followCheck.Checked == true)
{
followPic.Visible = true;
}
else
{
followPic.Visible = false;
}
if (checklistDTOs.Count() != 0)
{
checklistPn.Visible = true;
progressBar1.Visible = true;
}
else
{
checklistPn.Visible = false;
}
cardBLL.UpdateCard(cardDTO);
}
public void CreateChecklist(ChecklistDTO checklistDto)
{
Checklist checklist = new Checklist
{
DateTime = DateTime.Now,
Conformity = checklistDto.Conformity,
Top = checklistDto.Top,
Bottom = checklistDto.Bottom,
Left = checklistDto.Left,
Right = checklistDto.Right,
Length = checklistDto.Length,
Barcode = checklistDto.Barcode,
UserName = checklistDto.UserName,
Workplace = checklistDto.Workplace,
ShiftNumber = checklistDto.ShiftNumber
};
Database.Checklists.Create(checklist);
Database.Save();
}
public CardInfoDTO CardInfo(int id)
{
this.ConnectToDatabase();
MySqlCommand command = this.mySQLConnection.CreateCommand();
command.CommandText = "SELECT * FROM CARD WHERE CARD_ID = " + id;
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int cardId = reader.GetInt32(0);
int listId = reader.GetInt32(1);
int indexCard = reader.GetInt32(2);
string title = reader.GetString(3);
string description = reader.GetString(4);
int label = reader.GetInt32(5);
DateTime dueDate = (reader.IsDBNull(6)) ? DateTime.MinValue : (reader.GetDateTime(6));
float status = reader.GetInt64(7);
card = new CardDTO(cardId, listId, indexCard, title, description, label, dueDate, status);
}
reader.Close();
command.CommandText = "SELECT * FROM CHECKLIST WHERE CARD_ID = '" + id + "' AND STATUS = '" + 1 + "'";
MySqlDataReader reader1 = command.ExecuteReader();
while (reader1.Read())
{
int checklistId = reader1.GetInt32(0);
int cardId = reader1.GetInt32(1);
int checklistIndex = reader1.GetInt32(2);
string title = reader1.GetString(3);
byte status = reader1.GetByte(4);
ChecklistDTO checklist = new ChecklistDTO(checklistId, cardId, checklistIndex, title, status);
listCheckedlist.Add(checklist);
}
countCheckedlist = listCheckedlist.Count();
reader1.Close();
command.CommandText = "SELECT * FROM CHECKLIST WHERE CARD_ID = '" + id + "'";
MySqlDataReader reader2 = command.ExecuteReader();
while (reader2.Read())
{
int checklistId = reader2.GetInt32(0);
int cardId = reader2.GetInt32(1);
int checklistIndex = reader2.GetInt32(2);
string title = reader2.GetString(3);
byte status = reader2.GetByte(4);
ChecklistDTO checklist = new ChecklistDTO(checklistId, cardId, checklistIndex, title, status);
listChecklist.Add(checklist);
}
countChecklist = listChecklist.Count();
reader2.Close();
command.CommandText = "SELECT * FROM USERS WHERE USER_ID = " + id;
MySqlDataReader reader3 = command.ExecuteReader();
while (reader3.Read())
{
int userId = reader3.GetInt32(0);
string username = reader3.GetString(1);
string password = reader3.GetString(2);
string name = reader3.GetString(3);
user = new CardUserDTO(userId, username, password, name);
}
reader3.Close();
command.CommandText = "SELECT u.NAME "
+ " FROM USERS u, LAMVIEC l "
+ " WHERE u.USER_ID = l.USER_ID and CARD_ID = " + id;
MySqlDataReader reader4 = command.ExecuteReader();
while (reader4.Read())
{
string name = reader4.GetString(0).Substring(0, 1);
listNameUser.Add(name);
}
reader4.Close();
command.CommandText = "SELECT * FROM COMMENT WHERE CARD_ID = '" + id + "'";
MySqlDataReader reader5 = command.ExecuteReader();
while (reader5.Read())
{
int userId = reader5.GetInt32(1);
string content = reader5.GetString(2);
DateTime time = reader5.GetDateTime(3);
int cmtIndex = reader5.GetInt32(4);
CommentDTO comment = new CommentDTO(id, userId, content, time, cmtIndex);
listComment.Add(comment);
}
countCmt = listComment.Count();
reader5.Close();
this.Close();
CardInfoDTO cardInfoDTO = new CardInfoDTO(this.card, this.listNameUser, countChecklist, countCheckedlist, this.user, countCmt);
return(cardInfoDTO);
GC.Collect();
}
private void CardDetail_Activated(object sender, EventArgs e)
{
cardDTO = cardBLL.GetCard(_cardId);
AddMember();
checkDueDate.Text = cardDTO.DueDate.ToString();
switch (cardDTO.Label)
{
case 1:
this.cardLabel.BackColor = Color.Red;
break;
case 2:
this.cardLabel.BackColor = Color.Yellow;
break;
case 3:
this.cardLabel.BackColor = Color.Green;
break;
case 4:
this.cardLabel.BackColor = Color.Orange;
break;
case 5:
this.cardLabel.BackColor = Color.Blue;
break;
case 6:
this.cardLabel.BackColor = Color.Fuchsia;
break;
default:
this.cardLabel.BackColor = Color.Transparent;
break;
}
listDTO = listBLL.GetList(cardDTO.ListId);
List.Text = listDTO.Title;
taskFlpanel.Controls.Clear();
tasks.Clear();
checklistDTOs = checklistBLL.GetAllChecklist(_cardId);
if (checklistDTOs.Count() != 0)
{
this.checklistPn.Visible = true;
this.progressBar1.Visible = true;
this.progressBar1.Maximum = checklistDTOs.Count();
taskFlpanel.Controls.Clear();
checklistDTOs = checklistBLL.GetAllChecklist(_cardId);
foreach (ChecklistDTO checklist in checklistDTOs)
{
CheckBox task = new CheckBox();
task.Font = new Font(task.Font.FontFamily, 10.0f);
task.Text = checklist.Title;
if (checklist.Status == 1)
{
task.Checked = true;
this.progressBar1.Increment(1);
}
else
{
task.Checked = false;
}
taskFlpanel.Controls.Add(task);
tasks.Add(task);
}
}
else
{
checklistPn.Visible = false;
}
progressBar1.Value = 0;
progressBar1.Maximum = checklistDTOs.Count();
foreach (CheckBox task in tasks)
{
if (task.Checked == true)
{
progressBar1.Increment(1);
checklistDTO = checklistBLL.GetChecklist(task.Text);
checklistDTO.Status = 1;
checklistBLL.UpdateChecklist(checklistDTO);
}
else
{
checklistDTO = checklistBLL.GetChecklist(task.Text);
checklistDTO.Status = 0;
checklistBLL.UpdateChecklist(checklistDTO);
}
}
}
public bool UpdateChecklist(ChecklistDTO checklist)
{
ChecklistDAL cldal = new ChecklistDAL();
return(cldal.UpdateChecklist(checklist));
}
