public int getTotalRecords(DeliveryReceiptCriteria conditions, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(DeliveryReceiptDataLayer.GetInstance().GetTotalRecords(connection, conditions, _userID));
}
}
public List <DeliveryReceiptInfo> GetList(DeliveryReceiptCriteria conditions, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
List <DeliveryReceiptInfo> ListAcceptanceInfo = DeliveryReceiptDataLayer.GetInstance().Getlist(connection, conditions, _userID);
return(ListAcceptanceInfo);
}
}
public ListResponeMessage <DeliveryReceiptInfo> GetList([FromQuery] DeliveryReceiptCriteria criteria, string _userID)
{
ListResponeMessage <DeliveryReceiptInfo> ret = new ListResponeMessage <DeliveryReceiptInfo>();
try
{
ret.isSuccess = true;
ret.data = DeliveryReceiptServices.GetInstance().GetList(criteria, _userID);
ret.totalRecords = DeliveryReceiptServices.GetInstance().getTotalRecords(criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public int GetTotalRecords(SqlConnection connection, DeliveryReceiptCriteria criteria, string _userID)
{
using (var command = new SqlCommand("Select count(temp.DeliveryReceiptID) as TotalRecords from " +
" (Select DR.*, P.ProposalCode, D.DepartmentName " +
" from tbl_DeliveryReceipt DR " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = DR.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" where DR.DeliveryReceiptID <> 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 (DR.UserAssign = @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"]));
}
}
}
return(0);
}
public List <DeliveryReceiptInfo> Getlist(SqlConnection connection, DeliveryReceiptCriteria criteria, string _userID)
{
var result = new List <DeliveryReceiptInfo>();
using (var command = new SqlCommand("Select DR.*, P.ProposalCode, D.DepartmentName , D1.DepartmentName as CurDepartmentName" +
" from tbl_DeliveryReceipt DR " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = DR.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" left join tbl_Department D1 on P.CurDepartmentID = D1.DepartmentID " +
" where DR.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 DR.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 (DR.UserAssign = @UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by DR.UpdateTime ";
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 DeliveryReceiptInfo();
info.DeliveryReceiptID = GetDbReaderValue <int>(reader["DeliveryReceiptID"]);
info.DeliveryReceiptType = GetDbReaderValue <int>(reader["DeliveryReceiptType"]);
info.DeliveryReceiptCode = GetDbReaderValue <string>(reader["DeliveryReceiptCode"]);
info.DeliveryReceiptDate = GetDbReaderValue <DateTime>(reader["DeliveryReceiptDate"]);
info.DeliveryReceiptNumber = GetDbReaderValue <int>(reader["DeliveryReceiptNumber"]);
info.ProposalID = GetDbReaderValue <int>(reader["ProposalID"]);
info.ProposalCode = GetDbReaderValue <string>(reader["ProposalCode"]);
info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.CurDepartmentName = GetDbReaderValue <string>(reader["CurDepartmentName"]);
info.DeliveryReceiptPlace = GetDbReaderValue <string>(reader["DeliveryReceiptPlace"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
info.CreateTime = GetDbReaderValue <DateTime>(reader["CreateTime"]);
result.Add(info);
}
}
return(result);
}
}
