public int getTotalRecords(SqlConnection connection, SurveySeachCriteria criteria, string _userID)
{
if (criteria != null)
{
using (var command = new SqlCommand("Select count(temp.SurveyID) as TotalRecords from (Select S.*, P.ProposalCode, D.DepartmentName " +
" from tbl_Survey S " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = S.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" where S.SurveyID <> 0 ", connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and ( S.UserAssign = " + _userID + " ) or ( S.UserI = " + _userID + " )";
}
if (criteria.proposalCode != "" && criteria.proposalCode != null)
{
command.CommandText += " and P.ProposalCode like '%" + criteria.proposalCode + "%' ";
}
if (criteria.departmentID != 0)
{
command.CommandText += " and ( P.departmentID = @departmentID ";
command.CommandText += " or P.CurDepartmentID = @departmentID ) ";
AddSqlParameter(command, "@departmentID", criteria.departmentID, System.Data.SqlDbType.Int);
}
if (criteria.fromDate != null && criteria.toDate != null)
{
command.CommandText += " and P.DateIn between @FromDate and @ToDate ";
AddSqlParameter(command, "@FromDate", criteria.fromDate.Value.ToString("yyyy-MM-dd 00:00:00"), System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", criteria.toDate.Value.ToString("yyyy-MM-dd 23:59:59"), System.Data.SqlDbType.DateTime);
}
command.CommandText += " ) as temp";
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
}
else
{
using (var command = new SqlCommand("Select count(*) as TotalRecords from tbl_Explanation where 1 = 1 ", connection))
{
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
}
return(0);
}
public List <SurveyInfo> GetAllSurveyWithCondition(SurveySeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(SurveyDataLayer.GetInstance().getSurvey(connection, _criteria, _userID));
}
}
public int GetTotalRecords(SurveySeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(SurveyDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public List <SurveyInfo> getSurvey(SurveySeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
List <SurveyInfo> ListSurvey = SurveyDataLayer.GetInstance().getSurvey(connection, _criteria, _userID);
return(ListSurvey);
}
}
public ListResponeMessage <SurveyInfo> GetListWithCondition([FromQuery] SurveySeachCriteria _criteria, string _userID)
{
ListResponeMessage <SurveyInfo> ret = new ListResponeMessage <SurveyInfo>();
try
{
ret.isSuccess = true;
ret.data = SurveyService.GetInstance().GetAllSurveyWithCondition(_criteria, _userID);
ret.totalRecords = SurveyService.GetInstance().GetTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public List <SurveyInfo> getSurvey(SqlConnection connection, SurveySeachCriteria criteria, string _userID)
{
var result = new List <SurveyInfo>();
using (var command = new SqlCommand("Select S.*, P.ProposalCode, P.DepartmentID, D.DepartmentName, P.DepartmentID as ProDepartmentID , P.ProposalType, PT.TypeName, D1.DepartmentName as CurDepartmentName, D.DepartmentName as ProDepartmentName " +
" from tbl_Survey S" +
" LEFT JOIN tbl_Proposal P on P.ProposalID = S.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" left join tbl_Department D1 on P.CurDepartmentID = D1.DepartmentID " +
" left join tbl_ProposalType PT on PT.TypeID = P.ProposalType " +
" where S.ProposalID <> 0 ", connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and ( S.UserAssign = " + _userID + " ) or ( S.UserI = " + _userID + " )";
}
if (criteria.proposalCode != "" && criteria.proposalCode != null)
{
command.CommandText += " and P.ProposalCode like '%" + criteria.proposalCode + "%' ";
}
if (criteria.departmentID != 0)
{
command.CommandText += " and ( P.departmentID = @departmentID ";
command.CommandText += " or P.CurDepartmentID = @departmentID ) ";
AddSqlParameter(command, "@departmentID", criteria.departmentID, System.Data.SqlDbType.Int);
}
if (criteria.fromDate != null && criteria.toDate != null)
{
command.CommandText += " and S.Intime between @FromDate and @ToDate ";
AddSqlParameter(command, "@FromDate", criteria.fromDate.Value.ToString("yyyy-MM-dd 00:00:00"), System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", criteria.toDate.Value.ToString("yyyy-MM-dd 23:59:59"), System.Data.SqlDbType.DateTime);
}
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by S.UpdateTime Desc ";
command.CommandText += " OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY ";
AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@PAGESIZE", criteria.pageSize, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var info = new SurveyInfo();
info.SurveyID = GetDbReaderValue <int>(reader["SurveyID"]);
info.SurveyCode = GetDbReaderValue <string>(reader["SurveyCode"]);
info.SurveyName = GetDbReaderValue <string>(reader["SurveyName"]);
info.ProposalID = GetDbReaderValue <int>(reader["ProposalID"]);
info.ProposalCode = GetDbReaderValue <string>(reader["ProposalCode"]);
info.ProposalType = GetDbReaderValue <int>(reader["ProposalType"]);
info.TypeName = GetDbReaderValue <string>(reader["TypeName"]);
info.DepartmentID = GetDbReaderValue <int>(reader["ProDepartmentID"]);
info.DepartmentName = GetDbReaderValue <string>(reader["ProDepartmentName"]);
info.SurveyDepartmentID = GetDbReaderValue <int>(reader["SurveyDepartmentID"]);
info.SurveyDepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.Comment = GetDbReaderValue <string>(reader["Comment"]);
info.Solution = GetDbReaderValue <int>(reader["Solution"]);
info.SolutionText = GetDbReaderValue <string>(reader["SolutionText"]);
info.IsSample = GetDbReaderValue <bool>(reader["IsSample"]);
info.Valid = GetDbReaderValue <bool>(reader["Valid"]);
info.ValidText = GetDbReaderValue <string>(reader["ValidText"]);
info.UserI = GetDbReaderValue <string>(reader["UserI"]);
info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
info.DateIn = GetDbReaderValue <DateTime?>(reader["DateIn"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
result.Add(info);
}
}
return(result);
}
}
