public ListResponeMessage <BidPlanInfo> GetListWithCondition(
string bidPlanCode,
string quoteCode,
int customerID,
DateTime fromDate, DateTime toDate, string _userID, int pageSize = 10, int pageIndex = 0)
{
ListResponeMessage <BidPlanInfo> ret = new ListResponeMessage <BidPlanInfo>();
try
{
BidPlanSeachCriteria _criteria = new BidPlanSeachCriteria();
_criteria.BidPlanCode = bidPlanCode;
_criteria.QuoteCode = quoteCode;
_criteria.CustomerID = customerID;
_criteria.FromDate = fromDate;
_criteria.ToDate = toDate;
_criteria.pageSize = pageSize;
_criteria.pageIndex = pageIndex;
ret.isSuccess = true;
ret.data = BidPlanService.GetInstance().getAllBidPlan(pageSize, pageIndex, _criteria, _userID);
ret.totalRecords = BidPlanService.GetInstance().getTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public List <BidPlanInfo> getAllBidPlan(int pageSize, int pageIndex, BidPlanSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(BidPlanDataLayer.GetInstance().getBidPlan(connection, _criteria, _userID));
}
}
public int getTotalRecords(BidPlanSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(BidPlanDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public int getTotalRecords(SqlConnection connection, BidPlanSeachCriteria _criteria, string _userID)
{
string query = " Select count (BP.BidPlanID) as TotalRecords from (Select " +
"BP.* " +
" from tbl_BidPlan BP where 1 = 1 and BP.DateIn between @FromDate and @ToDate";
query += " ) as BP " +
" LEFT JOIN tbl_Quote Q on BP.QuoteID = Q.QuoteID " +
" LEFT JOIN tbl_Quote_Customer QC on QC.QuoteID = Q.QuoteID and QC.[IsChoosed] = 1 " +
" LEFT JOIN tbl_Customer C on C.CustomerID = QC.CustomerID " +
" where 1 = 1 ";
if (_criteria.BidPlanCode != null && _criteria.BidPlanCode != "")
{
query += " and BP.BidPlanCode like '%" + _criteria.BidPlanCode + "%'";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
query += " and Q.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (_criteria.CustomerID != 0)
{
query += " and C.CustomerID =" + _criteria.CustomerID + " ";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
query += " and (BP.UserI = " + _userID + "or BP.UserAssign = " + _userID + " )";
}
using (var command = new SqlCommand(query, connection))
{
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);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
return(0);
}
public List <BidPlanInfo> getBidPlan(SqlConnection connection, BidPlanSeachCriteria _criteria, string _userID)
{
var result = new List <BidPlanInfo>();
string query = " Select BP.* ," +
" Q.QuoteID, Q.IsVAT , Q.VATNumber ," +
" Q.QuoteCode from (Select " +
"BP.* " +
" from tbl_BidPlan BP where 1 = 1 and BP.DateIn between @FromDate and @ToDate";
query += " ) as BP " +
" LEFT JOIN tbl_Quote Q on BP.QuoteID = Q.QuoteID " +
" LEFT JOIN tbl_Quote_Customer QC on QC.QuoteID = Q.QuoteID and QC.[IsChoosed] = 1 " +
" LEFT JOIN tbl_Customer C on C.CustomerID = QC.CustomerID " +
" where 1 = 1 ";
if (_criteria.BidPlanCode != null && _criteria.BidPlanCode != "")
{
query += " and BP.BidPlanCode like '%" + _criteria.BidPlanCode + "%'";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
query += " and Q.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (_criteria.CustomerID != 0)
{
query += " and C.CustomerID =" + _criteria.CustomerID + " ";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
query += " and (BP.UserI = @UserID or BP.UserAssign =@UserID )";
}
using (var command = new SqlCommand(query, connection)) //, A.AuditID , A.AuditCode, D.DepartmentName, P.ProposalCode
{
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 += " order by BP.UpdateTime Desc ";
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 BidPlanInfo();
info.BidPlanID = GetDbReaderValue <int>(reader["BidPlanID"]);
info.BidPlanCode = GetDbReaderValue <string>(reader["BidPlanCode"]);
info.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
info.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
info.IsVAT = GetDbReaderValue <bool>(reader["IsVAT"]);
info.VATNumber = GetDbReaderValue <double>(reader["VATNumber"]);
info.Bid = GetDbReaderValue <string>(reader["Bid"]);
info.BidName = GetDbReaderValue <string>(reader["BidName"]);
info.BidTime = GetDbReaderValue <string>(reader["BidTime"]);
info.BidLocation = GetDbReaderValue <string>(reader["BidLocation"]);
info.BidMethod = GetDbReaderValue <int>(reader["BidMethod"]);
info.BidType = GetDbReaderValue <string>(reader["BidType"]);
info.BidExpirated = GetDbReaderValue <int>(reader["BidExpirated"]);
info.BidExpiratedUnit = GetDbReaderValue <string>(reader["BidExpiratedUnit"]);
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"]);
result.Add(info);
}
}
return(result);
}
}
