public ListResponeMessage <DecisionInfo> GetListWithCondition(
string decisionCode = "",
string quoteCode = "", int customerID = 0,
DateTime?fromDate = null, DateTime?toDate = null, int pageSize = 10, int pageIndex = 0, string _userID = "")
{
ListResponeMessage <DecisionInfo> ret = new ListResponeMessage <DecisionInfo>();
try
{
DecisionSeachCriteria _criteria = new DecisionSeachCriteria();
_criteria.DecisionCode = decisionCode;
_criteria.QuoteCode = quoteCode;
_criteria.CustomerID = customerID;
_criteria.FromDate = fromDate;
_criteria.ToDate = toDate;
_criteria.pageSize = pageSize;
_criteria.pageIndex = pageIndex;
ret.isSuccess = true;
ret.data = DecisionService.GetInstance().getAllDecision(pageSize, pageIndex, _criteria, _userID);
ret.totalRecords = DecisionService.GetInstance().getTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public List <DecisionInfo> getAllDecision(int pageSize, int pageIndex, DecisionSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(DecisionDataLayer.GetInstance().getDecision(connection, _criteria, _userID));
}
}
public int getTotalRecords(DecisionSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(DecisionDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public List <DecisionInfo> getDecision(DecisionSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
List <DecisionInfo> ListDecision = DecisionDataLayer.GetInstance().getDecision(connection, _criteria, _userID);
return(ListDecision);
}
}
public int getTotalRecords(SqlConnection connection, DecisionSeachCriteria _criteria, string _userID)
{
using (var command = new SqlCommand("select count(D.DecisionCode) as TotalRecords from (Select D.* " +
" from tbl_Decision D where 1 = 1 and D.DateIn between @FromDate and @ToDate ", 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);
if (!string.IsNullOrEmpty(_criteria.DecisionCode))
{
command.CommandText += " and D.DecisionCode = @DecisionCode";
AddSqlParameter(command, "@DecisionCode", _criteria.DecisionCode, System.Data.SqlDbType.NVarChar);
}
command.CommandText += " ) as D " +
" LEFT JOIN tbl_Quote Q on D.QuoteID = Q.QuoteID " +
" LEFT JOIN tbl_BidPlan BP on BP.QuoteID = Q.QuoteID " +
" Left join tbl_Quote_Customer QC on Q.QuoteID = QC.QuoteID and QC.IsChoosed = 1 " +
" LEFT join tbl_Customer C on C.CustomerID = QC.CustomerID " +
" where 1 = 1 ";
if (!string.IsNullOrEmpty(_criteria.QuoteCode))
{
command.CommandText += " and Q.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (!string.IsNullOrEmpty(_criteria.DecisionCode))
{
command.CommandText += " and D.DecisionCode like '%" + _criteria.DecisionCode + "%'";
}
if (_criteria.CustomerID != 0)
{
command.CommandText += " and C.CustomerID = @CustomerID";
AddSqlParameter(command, "@CustomerID", _criteria.CustomerID, System.Data.SqlDbType.Int);
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (D.UserI = @UserID or D.UserU = @UserID or D.UserAssign = @UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
return(0);
}
public List <DecisionInfo> getDecision(SqlConnection connection, DecisionSeachCriteria _criteria, string _userID)
{
var result = new List <DecisionInfo>();
using (var command = new SqlCommand(" Select D.* , BP.BidPlanID, BP.BidPlanCode, Q.QuoteCode, Q.IsVAT, Q.VATNumber from (Select D.* " +
" from tbl_Decision D where 1 = 1 and D.DateIn between @FromDate and @ToDate ", 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);
if (!string.IsNullOrEmpty(_criteria.DecisionCode))
{
command.CommandText += " and D.DecisionCode = @DecisionCode";
AddSqlParameter(command, "@DecisionCode", _criteria.DecisionCode, System.Data.SqlDbType.NVarChar);
}
command.CommandText += " ) as D " +
" LEFT JOIN tbl_Quote Q on D.QuoteID = Q.QuoteID " +
" LEFT JOIN tbl_BidPlan BP on BP.QuoteID = Q.QuoteID " +
" Left join tbl_Quote_Customer QC on Q.QuoteID = QC.QuoteID and QC.IsChoosed = 1 " +
" LEFT join tbl_Customer C on C.CustomerID = QC.CustomerID " +
" where 1 = 1 ";
if (!string.IsNullOrEmpty(_criteria.QuoteCode))
{
command.CommandText += " and Q.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (!string.IsNullOrEmpty(_criteria.DecisionCode))
{
command.CommandText += " and D.DecisionCode like '%" + _criteria.DecisionCode + "%'";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (D.UserI = @UserID or D.UserU = @UserID or D.UserAssign =@UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
if (_criteria.CustomerID != 0)
{
command.CommandText += " and C.CustomerID = @CustomerID";
AddSqlParameter(command, "@CustomerID", _criteria.CustomerID, System.Data.SqlDbType.Int);
}
command.CommandText += " order by D.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 DecisionInfo();
info.DecisionID = GetDbReaderValue <int>(reader["DecisionID"]);
info.DecisionCode = GetDbReaderValue <string>(reader["DecisionCode"]);
//info.NegotiationID = GetDbReaderValue<int>(reader["NegotiationID"]);
//info.NegotiationCode = GetDbReaderValue<string>(reader["NegotiationCode"]);
info.BidPlanID = GetDbReaderValue <int>(reader["BidPlanID"]);
info.BidMethod = GetDbReaderValue <int>(reader["BidMethod"]);
info.BidPlanCode = GetDbReaderValue <string>(reader["BidPlanCode"]);
//info.ProposalID = GetDbReaderValue<int>(reader["ProposalID"]);
//info.ProposalCode = GetDbReaderValue<string>(reader["ProposalCode"]);
info.IsVAT = GetDbReaderValue <bool>(reader["IsVAT"]);
info.VATNumber = GetDbReaderValue <double>(reader["VATNumber"]);
//info.DepartmentName = GetDbReaderValue<string>(reader["DepartmentName"]);
info.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
info.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
//info.AuditID = GetDbReaderValue<int>(reader["AuditID"]);
//info.AuditCode = GetDbReaderValue<string>(reader["AuditCode"]);
info.Comment = GetDbReaderValue <string>(reader["Comment"]);
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);
}
}
