public ListResponeMessage <NegotiationInfo> GetListWithCondition(
string negotiationCode,
string quoteCode,
int customerID,
DateTime fromDate, DateTime toDate, int pageSize = 10, int pageIndex = 0, string _userID = "")
{
ListResponeMessage <NegotiationInfo> ret = new ListResponeMessage <NegotiationInfo>();
try
{
NegotiationSeachCriteria _criteria = new NegotiationSeachCriteria();
_criteria.QuoteCode = quoteCode;
_criteria.NegotiationCode = negotiationCode;
_criteria.CustomerID = customerID;
_criteria.FromDate = Helpers.Utils.StartOfDate(fromDate);
_criteria.ToDate = Helpers.Utils.EndOfDate(toDate);
_criteria.pageSize = pageSize;
_criteria.pageIndex = pageIndex;
ret.isSuccess = true;
ret.data = NegotiationService.GetInstance().getAllNegotiation(pageSize, pageIndex, _criteria, _userID);
ret.totalRecords = NegotiationService.GetInstance().getTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public List <NegotiationInfo> getAllNegotiation(int pageSize, int pageIndex, NegotiationSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(NegotiationDataLayer.GetInstance().getNegotiation(connection, _criteria, _userID));
}
}
public int getTotalRecords(NegotiationSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(NegotiationDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public int getTotalRecords(SqlConnection connection, NegotiationSeachCriteria _criteria, string _userID)
{
int result = 0;
var query = "SELECT count (tblN.NegotiationID) as totalRecords from tbl_Negotiation tblN" +
" inner join tbl_Quote tblQ " +
" on tblN.QuoteID = tblQ.QuoteID" +
" inner join tbl_Customer" +
" on tblN.CustomerID = tbl_Customer.CustomerID " +
" where tblN.DateIn between @FromDate and @ToDate ";
if (_criteria.NegotiationCode != null && _criteria.NegotiationCode != "")
{
query += " and tblN.NegotiationCode like '%" + _criteria.NegotiationCode + "%'";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
query += " and tblQ.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (_criteria.CustomerID != 0)
{
query += " and tbl_Customer.CustomerID =" + _criteria.CustomerID + " ";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
query += " and ( tblN.UserAssign = " + _userID + " ) or ( tblN.UserI = " + _userID + " )";
}
using (var command = new SqlCommand(query, connection))
{
AddSqlParameter(command, "@FromDate", _criteria.FromDate, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", _criteria.ToDate, System.Data.SqlDbType.DateTime);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result = GetDbReaderValue <int>(reader["totalRecords"]);
}
}
return(result);
}
}
/// <summary>
/// Hàm lấy tất cả khoa phòng
/// </summary>
/// <returns>Return List<NegotiationInfo></returns>
///
public List <NegotiationInfo> getNegotiation(SqlConnection connection, NegotiationSeachCriteria _criteria, string _userID)
{
var result = new List <NegotiationInfo>();
var query = "SELECT tblN.* , tblQ.QuoteCode , tbl_Customer.CustomerName from tbl_Negotiation tblN" +
" inner join tbl_Quote tblQ " +
" on tblN.QuoteID = tblQ.QuoteID" +
" inner join tbl_Customer" +
" on tblN.CustomerID = tbl_Customer.CustomerID " +
" where tblN.DateIn between @FromDate and @ToDate ";
if (_criteria.NegotiationCode != null && _criteria.NegotiationCode != "")
{
query += " and tblN.NegotiationCode like '%" + _criteria.NegotiationCode + "%'";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
query += " and tblQ.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (_criteria.CustomerID != 0)
{
query += " and tbl_Customer.CustomerID =" + _criteria.CustomerID + " ";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
query += " and ( tblN.UserAssign = " + _userID + " ) or ( tblN.UserI = " + _userID + " )";
}
query += " order by tblN.UpdateTime Desc ";
using (var command = new SqlCommand(query
, connection))
{
AddSqlParameter(command, "@FromDate", _criteria.FromDate, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", _criteria.ToDate, System.Data.SqlDbType.DateTime);
if (_criteria.pageSize == 0)
{
_criteria.pageSize = 10;
}
var offSet = _criteria.pageIndex * _criteria.pageSize;
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 NegotiationInfo();
info.NegotiationID = GetDbReaderValue <int>(reader["NegotiationID"]);
info.NegotiationCode = GetDbReaderValue <string>(reader["NegotiationCode"]);
info.BidType = GetDbReaderValue <string>(reader["BidType"]);
info.BidExpirated = GetDbReaderValue <int>(reader["BidExpirated"]);
info.BidExpiratedUnit = GetDbReaderValue <string>(reader["BidExpiratedUnit"]);
info.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
info.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
info.CustomerName = GetDbReaderValue <string>(reader["CustomerName"]);
info.CustomerID = GetDbReaderValue <int>(reader["CustomerID"]);
info.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]);
info.UserI = GetDbReaderValue <string>(reader["UserI"]);
info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
info.Term = GetDbReaderValue <int>(reader["Term"]);
result.Add(info);
}
}
return(result);
}
}
