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); } }