public List<Complain> GetComplainByRoomNo(int roomNo)
{
List<Complain> complainList = new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select * From Complain where RoomNo='" + roomNo + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Complain aComplain = new Complain();
aComplain.ComplainId = reader["ComplainID"].ToString();
aComplain.PersonName = reader["PersonName"].ToString();
aComplain.HostelNo = int.Parse(reader["HostelNo"].ToString());
aComplain.RoomNo = int.Parse(reader["RoomNo"].ToString());
aComplain.Category = reader["Category"].ToString();
aComplain.Summary = reader["Summary"].ToString();
aComplain.Priority = reader["Priority"].ToString();
aComplain.DateOfComplain = DateTime.Parse(reader["DateOfComplain"].ToString());
aComplain.Status = reader["Status"].ToString();
aComplain.ContactNo = reader["ContactNo"].ToString();
aComplain.AssistantName = reader["AssistantName"].ToString();
aComplain.Remarks = reader["Remarks"].ToString();
complainList.Add(aComplain);
}
reader.Close();
connection.Close();
return complainList;
}
public void AllocateWithClick()
{
int r = complainGridView1.Rows.Count;
string[] cid = new string[r];
int j = 0;
bool flag = false;
for (int i = 0; i < complainGridView1.Rows.Count; i++)
{
CheckBox ch = (CheckBox)complainGridView1.Rows[i].FindControl("CheckBox1");
Label l = (Label)complainGridView1.Rows[i].FindControl("Label2");
if (ch.Checked == true) {
cid[j++] = l.Text;
flag = true;
}
}
if (flag == true && searchAssisDropDownList0.SelectedIndex > 0 && summaryTextBox.Text != null)
{
List<Complain> aAllocate = new List<Complain>();
Complain allocate = new Complain();
string aName = searchAssisDropDownList0.SelectedItem.ToString();
int bIn = aName.IndexOf("(");
allocate.AssistantName = aName.Substring(0, bIn - 1);
allocate.Remarks = summaryTextBox.Text;
allocate.Status = "Pending";
aAllocate.Add(allocate);
string msg = aManagerBal.AllocateFinal(aAllocate, cid);
Please_Select_Label7.Text = j + " Row IS " + msg;
}
else
Please_Select_Label7.Text = "Please Check The Box";
}
public string Save(Complain aComplain)
{
if (aGateway.Save(aComplain) > 0)
{
return "Complain registered Successfully !!";
}
else
{
return "Registered Failed!";
}
}
public List<Complain> GetComplainByall(string allThings,string textB)
{
List<Complain>allShowBy=new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
string query="";
if (allThings.ToString() == "All")
query = "Select * From Complain Order By DateOfComplain Desc";
else if (allThings.ToString()=="ComplainID")
query = "Select * From Complain where ComplainID like'%"+textB+ "%'Order By DateOfComplain Desc";
else if (allThings.ToString() == "PersonName")
query = "Select * From Complain where PersonName like '%" + textB + "%'Order By DateOfComplain Desc";
else if (allThings.ToString() == "RoomNo")
query = "Select * From Complain where RoomNo like '%" + textB + "%'Order By DateOfComplain Desc";
else if (allThings.ToString() == "Category")
query = "Select * From Complain where Category like'%" + textB + "%'Order By DateOfComplain Desc";
else if(allThings.ToString() == "Status")
query = "Select * From Complain where Status like'%" + textB + "%'Order By DateOfComplain Desc";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader= command.ExecuteReader();
while (reader.Read())
{
Complain aComplain = new Complain();
aComplain.ComplainId = reader["ComplainID"].ToString();
aComplain.PersonName = reader["PersonName"].ToString();
aComplain.HostelNo = int.Parse(reader["HostelNo"].ToString());
aComplain.RoomNo = int.Parse(reader["RoomNo"].ToString());
aComplain.Category = reader["Category"].ToString();
aComplain.Subject = reader["Subject"].ToString();
aComplain.Summary = reader["Summary"].ToString();
aComplain.Priority = reader["Priority"].ToString();
aComplain.DateOfComplain = DateTime.Parse(reader["DateOfComplain"].ToString());
aComplain.Status = reader["Status"].ToString();
aComplain.ContactNo = reader["ContactNo"].ToString();
aComplain.AssistantName = reader["AssistantName"].ToString();
string remAll = reader["Remarks"].ToString();
if (remAll.Length > 10)
{
aComplain.Remarks = remAll.Substring(0, 10);
}
else
{
aComplain.Remarks = remAll;
}
aComplain.AssistantContact = reader["AssistantContact"].ToString();
allShowBy.Add(aComplain);
}
reader.Close();
connection.Close();
return allShowBy;
}
public Complain DropInGrid(string cid)
{
Complain dAll = new Complain();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("select hostelNo,Category,Priority from complain where complainId='" + cid + "'", connection);
connection.Open();
SqlDataReader dr2 = cmd.ExecuteReader();
if (dr2.Read())
{
dAll.HostelNo = int.Parse(Convert.ToString(dr2.GetValue(0)));
dAll.Category = dr2["Category"].ToString();
dAll.Priority = dr2["Priority"].ToString();
}
connection.Close();
return dAll;
}
public string Save(Complain aComplain)
{
if (aGateway.IsComplainIdExists(aComplain))
{
return " Complain ID is already Exists!";
}
else
{
if (aGateway.Save(aComplain) > 0)
{
return "Successfully Inserted !!";
}
else
{
return "Save Failed!";
}
}
}
public int Save(Complain aComplain)
{
string query = string.Format(@"INSERT INTO Complain(ComplainID,PersonName,HostelNo,RoomNo,Category,Subject,Summary,Priority,DateOfComplain,Status,ContactNo,Remarks) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')", aComplain.ComplainId, aComplain.PersonName, aComplain.HostelNo, aComplain.RoomNo, aComplain.Category,aComplain.Subject, aComplain.Summary, aComplain.Priority, aComplain.DateOfComplain, aComplain.Status, aComplain.ContactNo,aComplain.Remarks);
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public List<Complain> ShowAllComplain()
{
List<Complain> complainList = new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select * From Complain Order By DateOfComplain Desc";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Complain aComplain = new Complain();
aComplain.ComplainId = reader["ComplainID"].ToString();
aComplain.PersonName = reader["PersonName"].ToString();
aComplain.HostelNo = int.Parse(reader["HostelNo"].ToString());
aComplain.RoomNo = int.Parse(reader["RoomNo"].ToString());
aComplain.Category = reader["Category"].ToString();
aComplain.Subject = reader["Subject"].ToString();
aComplain.Summary = reader["Summary"].ToString();
aComplain.Priority = reader["Priority"].ToString();
aComplain.DateOfComplain =DateTime.Parse(reader["DateOfComplain"].ToString());
aComplain.Status = reader["Status"].ToString();
aComplain.ContactNo = reader["ContactNo"].ToString();
aComplain.AssistantName = reader["AssistantName"].ToString();
string remAll = reader["Remarks"].ToString();
if (remAll.Length > 10)
{
aComplain.Remarks = remAll.Substring(0,10);
}
else
{
aComplain.Remarks = remAll;
}
aComplain.AssistantContact = reader["AssistantContact"].ToString();
complainList.Add(aComplain);
}
reader.Close();
connection.Close();
return complainList;
}
protected void complainGridView_RowUpdating1(object sender, GridViewUpdateEventArgs e)
{
int r = e.RowIndex;
Complain bd = new Complain();
Label t1 = (Label)complainGridView.Rows[r].FindControl("Label1");
bd = aManager.DropGri(t1.Text);
int t3 = bd.HostelNo;
string t5 = bd.Category, t8 = bd.Priority;
TextBox t2 = (TextBox)complainGridView.Rows[r].FindControl("pNameTextBox");
DropDownList ch1 = (DropDownList)complainGridView.Rows[r].FindControl("hostelDropDownList");
if (ch1.SelectedIndex > 0)
{
t3 = Convert.ToInt32(ch1.SelectedItem.ToString());
}
TextBox t4 = (TextBox)complainGridView.Rows[r].FindControl("roomTextBox");
DropDownList cha1 = (DropDownList)complainGridView.Rows[r].FindControl("cateDropDownList");
if (cha1.SelectedIndex > 0)
{
t5 = cha1.SelectedItem.ToString();
}
TextBox t6 = (TextBox)complainGridView.Rows[r].FindControl("subjectTextBox");
TextBox t7 = (TextBox)complainGridView.Rows[r].FindControl("summaryTextBox");
DropDownList c1 = (DropDownList)complainGridView.Rows[r].FindControl("PrioDrop");
if (c1.SelectedIndex > 0)
{
t8 = c1.SelectedItem.ToString();
}
TextBox t9 = (TextBox)complainGridView.Rows[r].FindControl("contactTextBox");
bool flag = aManager.Update(t1.Text, t2.Text,t3,int.Parse(t4.Text),t5,t6.Text, t7.Text, t8, t9.Text);
if (flag == true)
{
msgLabel.Text = "Update Successfully";
complainGridView.DataSource = aManager.ShowAllComplain();
complainGridView.EditIndex = -1;
complainGridView.DataBind();
}
else
{
msgLabel.Text = "Not Updated, Sorry!!!!!";
}
}
public bool IsComplainIdExists(Complain aComplain)
{
bool isComplainIdExists = false;
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select ComplainID From Complain Where ComplainID='" + aComplain.ComplainId + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
isComplainIdExists = true;
break;
}
reader.Close();
connection.Close();
return isComplainIdExists;
}
public List<Complain> GridByReq(string reqStatus)
{
List<Complain> aChange = new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("select * from Complain where Status='" + reqStatus + "'", connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Complain abComplain = new Complain();
abComplain.ComplainId = dr.GetValue(0).ToString();
abComplain.PersonName = dr.GetValue(1).ToString();
abComplain.HostelNo = int.Parse(dr.GetValue(2).ToString());
abComplain.RoomNo = int.Parse(dr.GetValue(3).ToString());
abComplain.Category = dr.GetValue(4).ToString();
abComplain.Summary = dr.GetValue(5).ToString();
abComplain.Priority = dr.GetValue(6).ToString();
abComplain.DateOfComplain = DateTime.Parse(dr.GetValue(7).ToString());
abComplain.Status = dr.GetValue(8).ToString();
abComplain.ContactNo = dr.GetValue(9).ToString();
abComplain.AssistantName = dr.GetValue(10).ToString();
abComplain.Remarks = dr.GetValue(11).ToString();
aChange.Add(abComplain);
}
dr.Close();
connection.Close();
return aChange;
}
public List<Complain> ViewInLabelReq()
{
List<Complain> aViewInLabel = new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Select status from Complain", connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
Complain aView = new Complain();
while (dr.Read())
{
if (dr.GetValue(0).ToString() == "New")
{
aView.NewReq++;
}
else if (dr.GetValue(0).ToString() == "Pending")
{
aView.PenReq++;
}
else if (dr.GetValue(0).ToString() == "Solved")
{
aView.SolReq++;
}
else if (dr.GetValue(0).ToString() == "Closed")
{
aView.CanReq++;
}
}
aViewInLabel.Add(aView);
dr.Close();
connection.Close();
return aViewInLabel;
}
public List<Complain> ViewInGridView()
{
List<Complain> aGridView = new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Select * From Complain", connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Complain aComplain = new Complain();
aComplain.ComplainId = dr.GetValue(0).ToString();
aComplain.PersonName = dr.GetValue(1).ToString();
aComplain.HostelNo = int.Parse(dr.GetValue(2).ToString());
aComplain.RoomNo = int.Parse(dr.GetValue(3).ToString());
aComplain.Category = dr.GetValue(4).ToString();
aComplain.Summary = dr.GetValue(5).ToString();
aComplain.Priority = dr.GetValue(6).ToString();
aComplain.DateOfComplain = DateTime.Parse(dr.GetValue(7).ToString());
aComplain.Status = dr.GetValue(8).ToString();
aComplain.ContactNo = dr.GetValue(9).ToString();
aComplain.AssistantName = dr.GetValue(10).ToString();
aComplain.Remarks = dr.GetValue(11).ToString();
aGridView.Add(aComplain);
}
dr.Close();
connection.Close();
return aGridView;
}
public List<Complain> GridViewChangeByLink(string reqType)
{
List<Complain> aChange = new List<Complain>();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("select * from Complain where Category='" + reqType + "'", connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Complain abComplain = new Complain();
abComplain.ComplainId = dr.GetValue(0).ToString();
abComplain.PersonName = dr.GetValue(1).ToString();
abComplain.HostelNo = int.Parse(dr.GetValue(2).ToString());
abComplain.RoomNo = int.Parse(dr.GetValue(3).ToString());
abComplain.Category = dr.GetValue(4).ToString();
abComplain.Subject = dr.GetValue(5).ToString();
abComplain.Summary = dr.GetValue(6).ToString();
abComplain.Priority = dr.GetValue(7).ToString();
abComplain.DateOfComplain = DateTime.Parse(dr.GetValue(8).ToString());
abComplain.Status = dr.GetValue(9).ToString();
abComplain.ContactNo = dr.GetValue(10).ToString();
abComplain.AssistantName = dr.GetValue(11).ToString();
string viewRem = dr.GetValue(12).ToString();
if (viewRem.Length > 10)
abComplain.Remarks = viewRem.Substring(0, 10);
else
{
abComplain.Remarks = viewRem;
}
abComplain.AssistantContact = dr.GetValue(13).ToString();
aChange.Add(abComplain);
}
dr.Close();
connection.Close();
return aChange;
}
