public async Task <TitleAndQuestions> GetFormById(int QuestionId)
{
var ListStudents = new TitleAndQuestionsDbo();
string query = "select * from Tbl_FeedBack_QuestionsAndComments where questionId=" + QuestionId + "";
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.QuestionId = Convert.ToInt32(sdr["questionId"]);
ListStudents.Question = sdr["question"].ToString();
MyConnection.Close();
}
}
var res = Mapper.Map <TitleAndQuestions>(ListStudents);
return(res);
}
public async Task <List <TitleAndQuestions> > AvgRating(int titleId)
{
var list = new List <TitleAndQuestionsDbo>();
var list1 = new List <TitleAndQuestionsDbo>();
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand("getAllFormDetails", MyConnection))
{
MyCommand.CommandType = System.Data.CommandType.StoredProcedure;
MyCommand.Parameters.AddWithValue("@type", 3);
MyCommand.Parameters.AddWithValue("@titleId", titleId);
SqlDataReader reader = await MyCommand.ExecuteReaderAsync();
TitleAndQuestionsDbo test = null;
while (reader.Read())
{
test = new TitleAndQuestionsDbo();
test.QuestionId = int.Parse(reader["questionId"].ToString());
test.Question = reader["question"].ToString();
test.Rating = int.Parse(reader["rating"].ToString());
list.Add(test);
}
}
MyConnection.Close();
}
int count = 0, sum = 0, repeat = 0;
var q = "";
foreach (var i in list)
{
foreach (var j in list)
{
if (i.QuestionId == j.QuestionId && repeat != i.QuestionId)
{
count += 1;
sum += j.Rating;
q = j.Question;
}
}
if (count != 0)
{
var temp = new TitleAndQuestionsDbo();
temp.Rating = sum;
temp.QuestionId = count;
temp.Question = q;
sum = count = 0;
repeat = i.QuestionId;
list1.Add(temp);
temp = null;
q = null;
}
}
foreach (var i in list1)
{
decimal avg = (Convert.ToDecimal(i.Rating) / Convert.ToDecimal(i.QuestionId * 5)) * 100;
i.AvgRating = avg;
}
var res = Mapper.Map <List <TitleAndQuestions> >(list1);
return(res);
}
public async Task <List <TitleAndQuestions> > GenerateExcel(int titleId, int type)
{
if (type == 1)
{
var list = new List <TitleAndQuestionsDbo>();
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand("getAllFormDetails", MyConnection))
{
MyCommand.CommandType = System.Data.CommandType.StoredProcedure;
MyCommand.Parameters.AddWithValue("@type", 1);
MyCommand.Parameters.AddWithValue("@titleId", titleId);
SqlDataReader reader = await MyCommand.ExecuteReaderAsync();
TitleAndQuestionsDbo test = null;
while (reader.Read())
{
test = new TitleAndQuestionsDbo();
test.UserName = reader["UserName"].ToString();
test.Question = reader["question"].ToString();
test.Comment = reader["comment"].ToString();
test.Rating = int.Parse(reader["rating"].ToString());
list.Add(test);
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <TitleAndQuestions> >(list);
return(res);
}
else if (type == 2)
{
var list = new List <TitleAndQuestionsDbo>();
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
MyConnection.Open();
using (SqlCommand MyCommand = new SqlCommand("getAllFormDetails", MyConnection))
{
MyCommand.CommandType = System.Data.CommandType.StoredProcedure;
MyCommand.Parameters.AddWithValue("@type", 2);
MyCommand.Parameters.AddWithValue("@titleId", titleId);
SqlDataReader reader = await MyCommand.ExecuteReaderAsync();
TitleAndQuestionsDbo test = null;
while (reader.Read())
{
test = new TitleAndQuestionsDbo();
test.UserName = reader["UserName"].ToString();
list.Add(test);
}
}
MyConnection.Close();
}
var res = Mapper.Map <List <TitleAndQuestions> >(list);
return(res);
}
return(null);
}
public async Task <List <TitleAndQuestions> > GetFormsList(string userID)
{
int cId;
string query1 = "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(query1, MyConnection))
{
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
sdr.Read();
cId = int.Parse(sdr["companyId"].ToString());
}
MyConnection.Close();
}
var list = new List <TitleAndQuestionsDbo>();
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())
{
TitleAndQuestionsDbo user = new TitleAndQuestionsDbo
{
TitleId = Convert.ToInt32(sdr["titleId"]),
Title = sdr["title"].ToString()
};
list.Add(user);
}
}
MyConnection.Close();
}
var ListForms = new List <TitleAndQuestionsDbo>();
foreach (var itr in list)
{
string query2 = "select * from Tbl_FeedBack_QuestionsAndComments where isDeleted != 1 and titleId=" + itr.TitleId + " ";
using (SqlConnection MyConnection = new SqlConnection("data source=AMX-MANIKANDAN;database=FEEDBACK_SYSTEM;User Id=sa;Password=sa5"))
{
using (SqlCommand MyCommand = new SqlCommand(query2))
{
MyCommand.Connection = MyConnection;
MyConnection.Open();
SqlDataReader sdr = await MyCommand.ExecuteReaderAsync();
while (sdr.Read())
{
TitleAndQuestionsDbo form = new TitleAndQuestionsDbo
{
QuestionId = Convert.ToInt32(sdr["questionId"]),
TitleId = Convert.ToInt32(sdr["titleId"]),
Title = itr.Title,
Question = sdr["question"].ToString()
};
ListForms.Add(form);
}
MyConnection.Close();
}
}
}
var res = Mapper.Map <List <TitleAndQuestions> >(ListForms);
return(res);
}