public List<Assistant> GetAssistantsByType(string type)
{
List<Assistant> assistantsList = new List<Assistant>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select * From AssistantDetails where AssistantType='" + type + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Assistant aAssistant = new Assistant();
aAssistant.AssistantId = reader["AssistantID"].ToString();
aAssistant.AssistantName = reader["AssistantName"].ToString();
aAssistant.ContactNo = reader["ContactNo"].ToString();
aAssistant.AssistantType = reader["AssistantType"].ToString();
assistantsList.Add(aAssistant);
}
reader.Close();
connection.Close();
return assistantsList;
}
public Assistant ChangeReqLAM(string aName)
{
Assistant ab = new Assistant();
SqlConnection connect = new SqlConnection(connectionString);
string querree = "Select SolvingReq,PendingReq from AssistantDetails where AsstName ='" + aName.Trim() + "'";
SqlCommand cmd1 = new SqlCommand(querree, connect);
connect.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
string que = Convert.ToString(dr1["SolvingReq"]);
if (que != "")
{
ab.SolvingReq = int.Parse(que);
}
else
{
ab.SolvingReq = 0;
}
string q = Convert.ToString(dr1["PendingReq"]);
if (q != "")
{
ab.PendingReq = int.Parse(q);
}
else
{
ab.PendingReq = 0;
}
}
dr1.Close();
connect.Close();
return ab;
}
public int Save(Assistant aAssistant)
{
string query = string.Format(@"INSERT INTO AssistantDetails(AssistantID,AsstName,ContactNo,AsstType) VALUES('{0}','{1}','{2}','{3}')",aAssistant.AssistantId, aAssistant.AsstName, aAssistant.ContactNo, aAssistant.AsstType);
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public string Save(Assistant aAssistant)
{
if (aGateway.Save(aAssistant) > 0)
{
return "Successfully Inserted !";
}
else
{
return "Insertion Failed!!";
}
}
public bool IsAssistantIdExists(Assistant aAssistant)
{
bool isAssistantIdExists = false;
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select AssistantID From AssistantDetails Where AssistantID='" + aAssistant.AssistantId + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
isAssistantIdExists = true;
break;
}
reader.Close();
connection.Close();
return isAssistantIdExists;
}
public string Save(Assistant aAssistant)
{
if (aGateway.IsAssistantIdExists(aAssistant))
{
return "Assistant ID is already Exixts !!";
}
else
{
if (aGateway.Save(aAssistant) > 0)
{
return "Successfully Inserted !";
}
else
{
return "Insertion Failed!!";
}
}
}
public List<Assistant> GetAssistantData()
{
List<Assistant> AssistList = new List<Assistant>();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Select AssistantName,AssistantType From AssistantDetails", connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Assistant aAssistant = new Assistant();
aAssistant.AssistantName = dr.GetValue(0).ToString();
aAssistant.AssistantType = dr.GetValue(1).ToString();
aAssistant.AssistantName = dr.GetValue(0).ToString() + " (" + dr.GetValue(1).ToString() + " )";
AssistList.Add(aAssistant);
}
dr.Close();
connection.Close();
return AssistList;
}
//public bool IsAssistantIdExists(Assistant aAssistant)
//{
// bool isAssistantIdExists = false;
// SqlConnection connection = new SqlConnection(connectionString);
// string query = "Select AssistantID From AssistantDetails Where AssistantID='" + aAssistant.AssistantId + "'";
// SqlCommand command = new SqlCommand(query, connection);
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// isAssistantIdExists = true;
// break;
// }
// reader.Close();
// connection.Close();
// return isAssistantIdExists;
//}
public List<Assistant> ShowAllAssistant()
{
List<Assistant> assistantsList = new List<Assistant>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select AssistantID,AsstName,ContactNo,AsstType From AssistantDetails ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Assistant aAssistant = new Assistant();
aAssistant.AssistantId = reader["AssistantID"].ToString();
aAssistant.AsstName = reader["AsstName"].ToString();
aAssistant.ContactNo = reader["ContactNo"].ToString();
aAssistant.AsstType = reader["AsstType"].ToString();
assistantsList.Add(aAssistant);
}
reader.Close();
connection.Close();
return assistantsList;
}
public List<Assistant> ViewInLabel(string aName)
{
List<Assistant> aViewInLabel = new List<Assistant>();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd =new SqlCommand("Select AssistantID,ContactNo from AssistantDetails where AssistantName = '" + aName + "'",connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
if (dr.Read())
{
Assistant aViewLabel = new Assistant();
aViewLabel.AssistantId = dr.GetValue(0).ToString();
aViewLabel.ContactNo = dr.GetValue(1).ToString();
aViewInLabel.Add(aViewLabel);
}
dr.Close();
connection.Close();
return aViewInLabel;
}
public List<Assistant> ViewInGridView(string aName)
{
List<Assistant> aViewInGridView = new List<Assistant>();
SqlConnection connection = new SqlConnection(connectionString);
string querry = "Select AssistantID,ContactNo,SolvingReq,PendingReq from AssistantDetails where AsstName ='" + aName +
"'";
SqlCommand cmd = new SqlCommand(querry, connection);
connection.Open();
cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Assistant aViewLabel = new Assistant();
aViewLabel.AssistantId = dr.GetValue(0).ToString();
aViewLabel.ContactNo = Convert.ToString(dr.GetValue(1));
string aname = Convert.ToString(dr.GetValue(2));
if (aname == "")
{
aViewLabel.SolvingReq = 0;
}
else
{
aViewLabel.SolvingReq = int.Parse(aname);
}
string ano = Convert.ToString(dr.GetValue(3));
if (ano == "")
{
aViewLabel.PendingReq = 0;
}
else
{
aViewLabel.PendingReq = int.Parse(ano);
}
aViewInGridView.Add(aViewLabel);
}
dr.Close();
connection.Close();
return aViewInGridView;
}
