public ResponseModel GetLatestSummary()
{
ResponseModel result = new ResponseModel();
try
{
LatestSummaryModel latestSummary = new LatestSummaryModel();
//collect all categories
SqlCommand scomCategories = new SqlCommand("SELECT CategoryID, dbo.GetCategoryNameById(CategoryID) AS CategoryName FROM tblCategory WITH (NOLOCK) ORDER BY CategoryID; ", SQLDbConnecter.Connect());
scomCategories.CommandType = CommandType.Text;
DataSet dsCategories = new DataSet();
SqlDataAdapter adapterCategories = new SqlDataAdapter(scomCategories);
adapterCategories.Fill(dsCategories);
if (dsCategories.Tables[0].Rows.Count >= 1)
{
List <string> lstCategoryNames = new List <string>();
for (int i = 0; i < dsCategories.Tables[0].Rows.Count; i++)
{
var categoryName = Convert.ToString(dsCategories.Tables[0].Rows[i]["CategoryName"]);
lstCategoryNames.Add(categoryName);
}
latestSummary.CategoryNames = lstCategoryNames;
//collect all users'marks related to each category
List <List <string> > lstSummaryUserMarks = new List <List <string> >();
SqlCommand scomUserMarks = new SqlCommand("SELECT dbo.GetUserNameById(um.UserID) AS UserName, dbo.GetCategoryNameById(um.CategoryID) AS CategoryName, um.Mark, " +
"dbo.GetPositionById(um.UserID) AS Position, " +
"(SELECT COUNT(DISTINCT GivenOn) FROM tblMarkHistory WHERE GivenBy = um.UserID) AS PreviousAppraisalCount " +
"FROM tblUserMark um WITH(NOLOCK) ORDER BY UserID, CategoryID; ", SQLDbConnecter.Connect());
scomUserMarks.CommandType = CommandType.Text;
DataSet dsUserMarks = new DataSet();
SqlDataAdapter adapterUserMarks = new SqlDataAdapter(scomUserMarks);
adapterUserMarks.Fill(dsUserMarks);
int sn = 1;
if (dsUserMarks.Tables[0].Rows.Count >= 1)
{
result.ResponseCode = 0;
for (int i = 0; i < dsUserMarks.Tables[0].Rows.Count; i++)
{
List <string> userMarkData = new List <string>();
userMarkData.Add(Convert.ToString(sn));
sn++;
var userName = Convert.ToString(dsUserMarks.Tables[0].Rows[i]["UserName"]);
userMarkData.Add(userName);
var position = Convert.ToString(dsUserMarks.Tables[0].Rows[i]["Position"]);
userMarkData.Add(position);
var previousAppraisalCount = Convert.ToString(dsUserMarks.Tables[0].Rows[i]["PreviousAppraisalCount"]);
userMarkData.Add(previousAppraisalCount);
for (int j = 0; j < lstCategoryNames.Count; j++)
{
var mark = Convert.ToString(dsUserMarks.Tables[0].Rows[i + j]["Mark"]);
userMarkData.Add(mark);
}
i += lstCategoryNames.Count - 1;
lstSummaryUserMarks.Add(userMarkData);
}
}
else
{
result.ResponseCode = 1;
}
latestSummary.UserMarks = lstSummaryUserMarks;
result.ResponseLatestSummaryModel = latestSummary;
}
else
{
result.ResponseCode = 1;
}
if (result.ResponseCode != 0)
{
result.ResponseMessage = "Something wrong in retrieving data";
}
}
catch (Exception e)
{
result.ResponseCode = 2;
result.ResponseMessage = e.Message;
}
return(result);
}
public ResponseModel StartForAppraisal(DateTime givenOn, string userCode)
{
ResponseModel result = new ResponseModel();
try
{
LatestSummaryModel latestSummary = new LatestSummaryModel();
//collect all categories
SqlCommand scomCategories = new SqlCommand("SELECT dbo.GetCategoryNameById(CategoryID) AS CategoryName FROM tblCategory WITH (NOLOCK) ORDER BY CategoryID; ", SQLDbConnecter.Connect());
scomCategories.CommandType = CommandType.Text;
DataSet dsCategories = new DataSet();
SqlDataAdapter adapterCategories = new SqlDataAdapter(scomCategories);
adapterCategories.Fill(dsCategories);
if (dsCategories.Tables[0].Rows.Count >= 1)
{
List <int> lstCategories = new List <int>();
List <string> lstCategoryNames = new List <string>();
for (int i = 0; i < dsCategories.Tables[0].Rows.Count; i++)
{
var categoryName = Convert.ToString(dsCategories.Tables[0].Rows[i]["CategoryName"]);
lstCategoryNames.Add(categoryName);
}
latestSummary.CategoryNames = lstCategoryNames;
//collect all users'marks related to each category
List <List <string> > lstUsers = new List <List <string> >();
SqlCommand scomUsers = new SqlCommand("SELECT UserID, dbo.GetUserNameById(UserID) AS UserName FROM tblUserMark WITH (NOLOCK) WHERE UserID<>ISNULL([dbo].[GetUserIDByCode]('" + userCode + "'), 0) GROUP BY UserID; ", SQLDbConnecter.Connect());
scomUsers.CommandType = CommandType.Text;
DataSet dsUsers = new DataSet();
SqlDataAdapter adapterUsers = new SqlDataAdapter(scomUsers);
adapterUsers.Fill(dsUsers);
if (dsUsers.Tables[0].Rows.Count >= 1)
{
result.ResponseCode = 0;
for (int i = 0; i < dsUsers.Tables[0].Rows.Count; i++)
{
List <string> userData = new List <string>();
var userId = Convert.ToString(dsUsers.Tables[0].Rows[i]["UserID"]);
userData.Add(userId);
var userName = Convert.ToString(dsUsers.Tables[0].Rows[i]["UserName"]);
userData.Add(userName);
for (int j = 0; j < lstCategoryNames.Count; j++)
{
userData.Add("0");
}
lstUsers.Add(userData);
}
}
else
{
result.ResponseCode = 1;
}
latestSummary.UserMarks = lstUsers;
result.ResponseLatestSummaryModel = latestSummary;
}
else
{
result.ResponseCode = 1;
}
if (result.ResponseCode != 0)
{
result.ResponseMessage = "Something wrong in retrieving data";
}
}
catch (Exception e)
{
result.ResponseCode = 2;
result.ResponseMessage = e.Message;
}
return(result);
}