public async Task <AssignedTitles> GetUserById(int UserId)
{
var ListStudents = new AssignedTitlesDbo();
string query = "select * from AspNetUsers where RefId='" + UserId + "'";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
using (SqlCommand MyCommand = new SqlCommand(query))
{
MyCommand.Connection = MyConnection;
MyConnection.Open();
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
sdr.Read();
ListStudents.UserId = sdr["Id"].ToString();
ListStudents.User = sdr["UserName"].ToString();
MyConnection.Close();
}
}
var res = Mapper.Map <AssignedTitles>(ListStudents);
return(res);
}
public List <AssignedTitles> GetAllUsers1()
{
var list = new List <AssignedTitlesDbo>();
string query = "select Id,UserName from dbo.AspNetUsers where isAssigned!=1 And isDeleted != 1 ";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query, MyConnection))
{
SqlDataReader sdr = MyCommand.ExecuteReader();
while (sdr.Read())
{
var user = new AssignedTitlesDbo
{
UserId = sdr["Id"].ToString(),
User = sdr["UserName"].ToString()
};
list.Add(user);
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <AssignedTitles> >(list);
return(res);
}
public List <AssignedTitles> GetAllCompany()
{
var list = new List <AssignedTitlesDbo>();
string query = "select * from Tbl_CompanyDetails";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query, MyConnection))
{
SqlDataReader sdr = MyCommand.ExecuteReader();
while (sdr.Read())
{
AssignedTitlesDbo company = new AssignedTitlesDbo
{
CompanyId = Convert.ToInt32(sdr["companyId"]),
Company = sdr["companyName"].ToString()
};
list.Add(company);
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <AssignedTitles> >(list);
return(res);
}
public async Task <List <AssignedTitles> > GetForm(int TitleId)
{
List <AssignedTitlesDbo> list = new List <AssignedTitlesDbo>();
string query = "select * from Tbl_FeedBack_QuestionsAndComments where titleId =" + TitleId + " AND isDeleted != 1";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
while (sdr.Read())
{
AssignedTitlesDbo user = new AssignedTitlesDbo
{
QuestionId = Convert.ToInt32(sdr["questionId"]),
Question = sdr["question"].ToString()
};
list.Add(user);
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <AssignedTitles> >(list);
return(res);
}
public async Task <List <AssignedTitles> > GetAllUsers2()
{
string query1 = "select UserId from AspNetUserRoles where RoleId='2'";
var list1 = new List <AssignedTitlesDbo>();
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query1, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
while (sdr.Read())
{
AssignedTitlesDbo user = new AssignedTitlesDbo
{
UserId = sdr["UserId"].ToString(),
};
list1.Add(user);
}
}
MyConnection.Close();
}
var list = new List <AssignedTitlesDbo>();
foreach (var n in list1)
{
string query = "select Id,UserName,RefId from dbo.AspNetUsers where Id='" + n.UserId + "' And isDeleted != 1 And isCompanyAssigned!=1";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
while (sdr.Read())
{
AssignedTitlesDbo user = new AssignedTitlesDbo
{
UserId = sdr["Id"].ToString(),
RefId = Convert.ToInt32(sdr["RefId"]),
User = sdr["UserName"].ToString()
};
list.Add(user);
}
}
MyConnection.Close();
}
}
var res = Mapper.Map <List <AssignedTitles> >(list);
return(res);
}
public async Task <List <AssignedTitles> > AllTitles(string id)
{
int cId;
string query2 = "select [companyId] from [AspNetUsers] where Id ='" + id + "' ";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query2, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
sdr.Read();
cId = int.Parse(sdr["companyId"].ToString());
}
MyConnection.Close();
}
var list = new List <AssignedTitlesDbo>();
string query = "select * from Tbl_FeedBack_Titles where companyId='" + cId + "'";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
while (sdr.Read())
{
AssignedTitlesDbo user = new AssignedTitlesDbo
{
TitleId = Convert.ToInt32(sdr["titleId"]),
Title = sdr["title"].ToString()
};
list.Add(user);
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <AssignedTitles> >(list);
return(res);
}
public async Task <List <AssignedTitles> > GetTitles(string userID)
{
int cId;
string query2 = "select [companyId] from [AspNetUsers] where Id ='" + userID + "' ";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query2, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
sdr.Read();
cId = int.Parse(sdr["companyId"].ToString());
}
MyConnection.Close();
}
List <AssignedTitlesDbo> list = new List <AssignedTitlesDbo>();
string query = "select titleId from Tbl_Assigned_Titles_To_The_User where userId='" + userID + "' and isDeleted!=1";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand(query, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
while (sdr.Read())
{
string query1 = "select * from Tbl_FeedBack_Titles where titleId=" + Convert.ToInt32(sdr["titleId"]) + " and companyId='" + cId + "'";
using (SqlConnection MyConnection1 = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection1.Open();
using (SqlCommand MyCommand1 = new SqlCommand(query1, MyConnection1))
{
SqlDataReader sdr1 = await MyCommand1.ExecuteReaderAsync();
while (sdr1.Read())
{
AssignedTitlesDbo t = new AssignedTitlesDbo
{
TitleId = Convert.ToInt32(sdr1["titleId"]),
Title = sdr1["title"].ToString()
};
list.Add(t);
}
}
MyConnection1.Close();
}
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <AssignedTitles> >(list);
return(res);
}
