public int getTotalRecords(SqlConnection connection, ExplanationSeachCriteria criteria, string _userID)
{
if (criteria != null)
{
using (var command = new SqlCommand("Select count(temp.ExplanationID) as TotalRecords from (Select E.ExplanationID " +
" from tbl_Explanation E " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = E.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" where E.ProposalID <> 0 ", connection))
{
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, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", criteria.toDate, System.Data.SqlDbType.DateTime);
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and ( E.UserAssign = @UserID ) or ( E.UserI = @UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
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 <ExplanationInfo> getAllExplanationWwithCondition(ExplanationSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(ExplanationDataLayer.GetInstance().getExplanation(connection, _criteria, _userID));
}
}
public int getTotalRecords(ExplanationSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(ExplanationDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public ListResponeMessage <ExplanationInfo> GetListWithCondition([FromQuery] ExplanationSeachCriteria _criteria, string _userID)
{
ListResponeMessage <ExplanationInfo> ret = new ListResponeMessage <ExplanationInfo>();
try
{
ret.isSuccess = true;
ret.data = ExplanationService.GetInstance().getAllExplanationWwithCondition(_criteria, _userID);
ret.totalRecords = ExplanationService.GetInstance().getTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public List <ExplanationInfo> getExplanation(SqlConnection connection, ExplanationSeachCriteria criteria, string _userID)
{
var result = new List <ExplanationInfo>();
using (var command = new SqlCommand("Select E.*, P.ProposalCode, P.DepartmentID, D.DepartmentName, P.ProposalType, PT.TypeName, D1.DepartmentName as CurDepartmentName" +
" from tbl_Explanation E" +
" LEFT JOIN tbl_Proposal P on P.ProposalID = E.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 E.ProposalID <> 0 ", connection))
{
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, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", criteria.toDate, System.Data.SqlDbType.DateTime);
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and ( E.UserAssign = @UserID ) or ( E.UserI = @UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by E.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 ExplanationInfo();
info.ExplanationID = GetDbReaderValue <int>(reader["ExplanationID"]);
info.ExplanationCode = GetDbReaderValue <string>(reader["ExplanationCode"]);
info.ExplanationName = GetDbReaderValue <string>(reader["ExplanationName"]);
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["DepartmentID"]);
info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.Necess = GetDbReaderValue <bool>(reader["Necess"]);
info.Suitable = GetDbReaderValue <bool>(reader["Suitable"]);
info.NBNum = GetDbReaderValue <string>(reader["NBNum"]);
info.XNNum = GetDbReaderValue <string>(reader["XNNum"]);
info.Available = GetDbReaderValue <string>(reader["Available"]);
info.IsAvailable = GetDbReaderValue <bool>(reader["IsAvailable"]);
info.Comment = GetDbReaderValue <string>(reader["Comment"]);
info.TNCB = GetDbReaderValue <string>(reader["TNCB"]);
info.Status = GetDbReaderValue <string>(reader["Status"]);
info.DBLTCN = GetDbReaderValue <string>(reader["DBLTCN"]);
info.NVHTTB = GetDbReaderValue <string>(reader["NVHTTB"]);
info.DTNL = GetDbReaderValue <string>(reader["DTNL"]);
info.NQL = GetDbReaderValue <string>(reader["NQL"]);
info.HQKTXH = GetDbReaderValue <string>(reader["HQKTXH"]);
info.UserI = GetDbReaderValue <string>(reader["UserI"]);
info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
result.Add(info);
}
}
return(result);
}
}
