public ListResponeMessage <SearchAuditInfo> GetListWithCondition(
string auditCode = "",
string quoteCode = "",
int CustomerID = 0, DateTime?fromDate = null,
DateTime?toDate = null, int pageSize = 10, int pageIndex = 0, string _userID = "")
{
ListResponeMessage <SearchAuditInfo> ret = new ListResponeMessage <SearchAuditInfo>();
try
{
AuditSeachCriteria _criteria = new AuditSeachCriteria();
_criteria.CustomerID = CustomerID;
_criteria.AuditCode = auditCode;
_criteria.QuoteCode = quoteCode;
_criteria.FromDate = fromDate;
_criteria.ToDate = toDate;
_criteria.pageSize = pageSize;
_criteria.pageIndex = pageIndex;
ret.isSuccess = true;
ret.data = AuditService.GetInstance().getAllAudit(pageSize, pageIndex, _criteria, _userID);
ret.totalRecords = AuditService.GetInstance().getTotalRecords(_criteria, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public int getTotalRecords(AuditSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(AuditDataLayer.GetInstance().getTotalRecords(connection, _criteria, _userID));
}
}
public List <SearchAuditInfo> getAllAudit(int pageSize, int pageIndex, AuditSeachCriteria _criteria, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
List <SearchAuditInfo> ListAudit = AuditDataLayer.GetInstance().getAuditNew(connection, _criteria, _userID);
return(ListAudit);
}
}
public int getTotalRecords(SqlConnection connection, AuditSeachCriteria _criteria, string _userID)
{
string Condition = "";
if (_criteria.pageSize == 0)
{
_criteria.pageSize = 10;
}
var offSet = _criteria.pageIndex * _criteria.pageSize;
if (_criteria.AuditCode != "" && _criteria.AuditCode != null)
{
Condition += " and tblA.AuditCode like '%" + _criteria.AuditCode + "%' ";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
Condition += " and tblQ.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
Condition += " and (tblA.UserAssign = " + _userID + " or tblA.UserI = " + _userID + ")";
}
string query = $"select count(AuditID) as TotalRecords from(" +
" select DISTINCT T.AuditID, T.UpdateTime from(" +
" select tblA.AuditID, tblA.UpdateTime, tblA.AuditCode, tblQ.QuoteCode, tblP.ProposalCode, tblA.Intime from tbl_Audit tblA" +
" inner join tbl_Audit_Quote tblAQ on tblA.AuditID = tblAQ.AuditID " +
" inner join tbl_Quote tblQ on tblAQ.QuoteID = tblQ.QuoteID " +
" inner Join tbl_Quote_Proposal tblQP " +
" on tblQP.QuoteID = tblQ.QuoteID inner join tbl_Proposal tblP on tblP.ProposalID = tblQP.ProposalID " +
" where tblA.InTime between @FromDate and @ToDate " + Condition + " ) as T " +
" ) tbltemp ";
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);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="_criteria"></param>
/// <param name="_userID"></param>
/// <returns></returns>
public List <SearchAuditInfo> getAuditNew(SqlConnection connection, AuditSeachCriteria _criteria, string _userID)
{
var result = new List <SearchAuditInfo>();
string Condition = "";
if (_criteria.pageSize == 0)
{
_criteria.pageSize = 10;
}
var offSet = _criteria.pageIndex * _criteria.pageSize;
if (_criteria.AuditCode != "" && _criteria.AuditCode != null)
{
Condition += " and tblA.AuditCode like '%" + _criteria.AuditCode + "%' ";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
Condition += " and tblQ.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
Condition += " and (tblA.UserAssign = " + _userID + " or tblA.UserI = " + _userID + ")";
}
string query = $"select tblA.AuditID , tblA.UpdateTime, tblA.AuditCode, tblQ.QuoteCode, tblP.ProposalCode , tblA.Intime " +
" from(select * from tbl_Audit where tbl_Audit.AuditID in " +
" (select AuditID from(" +
" select DISTINCT T.AuditID, T.UpdateTime from(" +
" select tblA.AuditID, tblA.UpdateTime, tblA.AuditCode, tblQ.QuoteCode, tblP.ProposalCode, tblA.Intime from tbl_Audit tblA" +
" inner join tbl_Audit_Quote tblAQ on tblA.AuditID = tblAQ.AuditID " +
" inner join tbl_Quote tblQ on tblAQ.QuoteID = tblQ.QuoteID " +
" inner Join tbl_Quote_Proposal tblQP " +
" on tblQP.QuoteID = tblQ.QuoteID inner join tbl_Proposal tblP on tblP.ProposalID = tblQP.ProposalID " +
" where tblA.InTime between @FromDate and @ToDate " + Condition + " ) as T " +
" order by T.UpdateTime " +
" OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY " +
" ) tbltemp))tblA " +
" inner join tbl_Audit_Quote tblAQ on tblA.AuditID = tblAQ.AuditID inner join tbl_Quote tblQ on tblAQ.QuoteID = tblQ.QuoteID " +
" inner Join tbl_Quote_Proposal tblQP " +
" on tblQP.QuoteID = tblQ.QuoteID inner join tbl_Proposal tblP on tblP.ProposalID = tblQP.ProposalID ";
using (var command = new SqlCommand(query, connection))
{
AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@PAGESIZE", _criteria.pageSize, System.Data.SqlDbType.Int);
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);
int tempAuditID = 0;
bool isNeedAdd = false;
var info = new SearchAuditInfo();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (tempAuditID != GetDbReaderValue <int>(reader["AuditID"]))
{
if (info.AuditID != 0)
{
isNeedAdd = false;
result.Add(info);
}
info = new SearchAuditInfo();
info.AuditID = GetDbReaderValue <int>(reader["AuditID"]);
info.AuditCode = GetDbReaderValue <string>(reader["AuditCode"]);
info.QuoteCodes = GetDbReaderValue <string>(reader["QuoteCode"]);
info.ProposalCodes = GetDbReaderValue <string>(reader["ProposalCode"]);
info.InTime = GetDbReaderValue <DateTime>(reader["InTime"]);
isNeedAdd = true;
tempAuditID = GetDbReaderValue <int>(reader["AuditID"]);
}
else
{
string proCode = GetDbReaderValue <string>(reader["ProposalCode"]);
string QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
if (info.ProposalCodes.Contains(proCode) == false)
{
info.ProposalCodes += " , " + proCode;
}
if (info.QuoteCodes.Contains(QuoteCode) == false)
{
info.QuoteCodes += " , " + QuoteCode;
}
tempAuditID = GetDbReaderValue <int>(reader["AuditID"]);
}
}
if (isNeedAdd)
{
result.Add(info);
}
}
return(result);
}
}
