public ResponseModel Login(string userCode, string password)
{
ResponseModel result = new ResponseModel();
try
{
SqlCommand scom = new SqlCommand(" SELECT * FROM tblUser WITH (NOLOCK) WHERE UserCode='" + userCode + "' AND Password='******'; ", SQLDbConnecter.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
if (ds.Tables[0].Rows.Count == 1)
{
result.ResponseCode = 0;
result.ResponseMessage = Convert.ToString(ds.Tables[0].Rows[0]["Position"]);
}
else
{
result.ResponseCode = 1;
result.ResponseMessage = "Invalid User Code or Password";
}
}
catch (Exception e)
{
result.ResponseCode = 2;
result.ResponseMessage = e.Message;
}
return(result);
}
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);
}
public ResponseModel SaveAppraisal(RequestModel request)
{
SqlConnection con = SQLDbConnecter.Connect();
con.Close();
ResponseModel result = new ResponseModel();
try
{
con.Open();
var oTransaction = con.BeginTransaction();
//get all categories
SqlCommand scomCategories = new SqlCommand("SELECT CategoryID FROM tblCategory WITH (NOLOCK) ORDER BY CategoryID; ", con);
scomCategories.CommandType = CommandType.Text;
scomCategories.Transaction = oTransaction;
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>();
for (int i = 0; i < dsCategories.Tables[0].Rows.Count; i++)
{
var categoryId = Convert.ToInt32(dsCategories.Tables[0].Rows[i]["CategoryID"]);
lstCategories.Add(categoryId);
}
//insert mark histoy
DateTime giveOnDateTime = request.GivenOn;
string giveOnSqlDateTime = giveOnDateTime.ToString("yyyy-MM-dd HH:mm");
string sqlInsertString = "";
for (int i = 0; i < request.UserMarkData.Count; i++)
{
List <string> userMark = request.UserMarkData[i];
var userID = userMark[0];
for (int j = 0; j < lstCategories.Count; j++)
{
var categoryID = lstCategories[j];
var mark = userMark[j + 1];
sqlInsertString += "INSERT INTO tblMarkHistory VALUES (" + userID + "," + categoryID + "," + mark + ", [dbo].[GetUserIDByCode]('" + request.GivenBy + "'),'" + giveOnSqlDateTime + "');";
}
}
var totalCount = request.UserMarkData.Count * lstCategories.Count;
SqlCommand scomMarkHistory = new SqlCommand(sqlInsertString, con);
scomMarkHistory.CommandType = CommandType.Text;
scomMarkHistory.Transaction = oTransaction;
var insertedHistoryCount = scomMarkHistory.ExecuteNonQuery();
if (insertedHistoryCount == totalCount)
{
//update user mark
string sqlUpdateString = "";
for (int i = 0; i < request.UserMarkData.Count; i++)
{
List <string> userMark = request.UserMarkData[i];
var userID = userMark[0];
for (int j = 0; j < lstCategories.Count; j++)
{
var categoryID = lstCategories[j];
var mark = userMark[j + 1];
sqlUpdateString += "UPDATE tblUserMark SET Mark+=" + mark + " WHERE UserID=" + userID + " AND CategoryID=" + categoryID + ";";
}
}
SqlCommand scomUserData = new SqlCommand(sqlUpdateString, con);
scomUserData.CommandType = CommandType.Text;
scomUserData.Transaction = oTransaction;
var updatedHistoryCount = scomUserData.ExecuteNonQuery();
if (updatedHistoryCount == totalCount)
{
result.ResponseCode = 0;
}
else
{
result.ResponseCode = 1;
}
}
else
{
result.ResponseCode = 1;
}
}
else
{
result.ResponseCode = 1;
}
if (result.ResponseCode != 0)
{
oTransaction.Rollback();
result.ResponseMessage = "Something wrong in retrieving data";
}
else
{
oTransaction.Commit();
result.ResponseMessage = "Successfully saved";
}
}
catch (Exception e)
{
result.ResponseCode = 2;
result.ResponseMessage = e.Message;
}
return(result);
}
public ResponseModel GetOwnedMark(string userCode)
{
ResponseModel result = new ResponseModel();
try
{
SqlCommand scom = new SqlCommand(" SELECT *, " +
"(SELECT COUNT(DISTINCT GivenOn) FROM tblMarkHistory WHERE GivenBy=[dbo].[GetUserIDByCode]('" + userCode + "')) AS PreviousAppraisalCount, " +
"dbo.GetUserNameById(UserID) AS UserName, dbo.GetCategoryNameById(CategoryID) AS CategoryName " +
"FROM tblUserMark WHERE UserID = [dbo].[GetUserIDByCode]('" + userCode + "'); ", SQLDbConnecter.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
if (ds.Tables[0].Rows.Count >= 1)
{
result.ResponseCode = 0;
result.ResponseAppraisalCount = Convert.ToInt32(ds.Tables[0].Rows[0]["PreviousAppraisalCount"]);
List <UserMarkModel> lstUserMark = new List <UserMarkModel>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UserMarkModel usermark = new UserMarkModel();
usermark.UserMarkID = Convert.ToInt32(ds.Tables[0].Rows[i]["UserMarkID"]);
usermark.UserID = Convert.ToInt32(ds.Tables[0].Rows[i]["UserID"]);
usermark.CategoryID = Convert.ToInt32(ds.Tables[0].Rows[i]["CategoryID"]);
usermark.Mark = Convert.ToDecimal(ds.Tables[0].Rows[i]["Mark"]);
usermark.UserName = Convert.ToString(ds.Tables[0].Rows[i]["UserName"]);
usermark.CategoryName = Convert.ToString(ds.Tables[0].Rows[i]["CategoryName"]);
lstUserMark.Add(usermark);
}
result.ResponseUserMarkModel = lstUserMark;
}
else
{
result.ResponseCode = 1;
result.ResponseMessage = "No data for this user";
}
}
catch (Exception e)
{
result.ResponseCode = 2;
result.ResponseMessage = e.Message;
}
return(result);
}
