public ListResponeMessage <ContractInfo> GetListWithCondition(
string quoteCode, string contractCode,
int customerID,
DateTime fromDate, DateTime toDate, string _userID, int pageSize = 10, int pageIndex = 0)
{
ListResponeMessage <ContractInfo> ret = new ListResponeMessage <ContractInfo>();
try
{
ContractSeachCriteria _criteria = new ContractSeachCriteria();
_criteria.QuoteCode = quoteCode;
_criteria.ContractCode = contractCode;
_criteria.CustomerID = customerID;
_criteria.pageSize = pageSize;
_criteria.pageIndex = pageIndex;
_criteria.FromDate = fromDate;
_criteria.ToDate = toDate;
_criteria.ToDate = toDate;
ret.isSuccess = true;
ret.data = ContractService.GetInstance().getAllContract(pageSize, pageIndex, _criteria, _userID);
ret.totalRecords = ContractService.GetInstance().getTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public List <ContractInfo> getAllContract(int pageSize, int pageIndex, ContractSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(ContractDataLayer.GetInstance().getContract(connection, _criteria, _userID));
}
}
public int getTotalRecords(ContractSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(ContractDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public int getTotalRecords(SqlConnection connection, ContractSeachCriteria _criteria, string _userID)
{
string query = "select count(*) as TotalRecords from tbl_Contract tblC " +
" inner join tbl_Quote tblQ " +
" on tblC.QuoteID = tblQ.QuoteID " +
" inner join tbl_Quote_Customer tblQC " +
" on tblQC.QuoteID = tblQ.QuoteID and tblQC.IsChoosed = 1 " +
" inner join tbl_Customer " +
" on tblQC.CustomerID = tbl_Customer.CustomerID " +
" where tblC.DateIn between @FromDate and @ToDate ";
if (_criteria.ContractCode != null && _criteria.ContractCode != "")
{
query += " and tblC.ContractCode like '%" + _criteria.ContractCode + "%'";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
query += " and tblQ.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (_criteria.CustomerID != null && _criteria.CustomerID != 0)
{
query += " and tbl_Customer.CustomerID = " + _criteria.CustomerID + " ";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
query += " and (tblC.UserI = " + _userID + "or tblC.UserAssign = " + _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())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
return(0);
}
/// <summary>
/// </summary>
/// <returns>Return List<ContractInfo></returns>
///
public List <ContractInfo> getContract(SqlConnection connection, ContractSeachCriteria _criteria, string _userID)
{
var result = new List <ContractInfo>();
string query = "select tblC.DateIn, tblC.ContractID , tblC.ContractCode, tblQ.QuoteCode , tbl_Customer.CustomerName from tbl_Contract tblC " +
" inner join tbl_Quote tblQ " +
" on tblC.QuoteID = tblQ.QuoteID " +
" inner join tbl_Quote_Customer tblQC " +
" on tblQC.QuoteID = tblQ.QuoteID and tblQC.IsChoosed = 1 " +
" inner join tbl_Customer " +
" on tblQC.CustomerID = tbl_Customer.CustomerID " +
" where tblC.DateIn between @FromDate and @ToDate ";
if (_criteria.ContractCode != null && _criteria.ContractCode != "")
{
query += " and tblC.ContractCode like '%" + _criteria.ContractCode + "%'";
}
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 (tblC.UserI = " + _userID + "or tblC.UserAssign = " + _userID + " )";
}
query += " order by tblC.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 ContractInfo();
info.ContractID = GetDbReaderValue <int>(reader["ContractID"]);
info.ContractCode = GetDbReaderValue <string>(reader["ContractCode"]);
info.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
info.CustomerName = GetDbReaderValue <string>(reader["CustomerName"]);
info.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]);
result.Add(info);
}
}
return(result);
}
}
