private void InitControl() { int userId = 0; if (UserInfo.Role == RolesEnum.CLIENT) { userId = UserInfo.UserID; } DateTime?beginTime = null; DateTime?endTime = null; if (!string.IsNullOrEmpty(QS("start").Trim())) { DateTime start = DateTime.MinValue; DateTime.TryParse(QS("start").Trim(), out start); if (start > DateTime.MinValue) { start = DateTime.Parse(start.ToString("yyyy-MM-dd 00:00:00")); beginTime = start; } } if (!string.IsNullOrEmpty(QS("end").Trim())) { DateTime end = DateTime.MaxValue; DateTime.TryParse(QS("end").Trim(), out end); if (end < DateTime.MaxValue) { end = DateTime.Parse(end.ToString("yyyy-MM-dd 23:59:59")); endTime = end; } } SearchProposalTrackerRequest request = wrApp.GetSearchProposalTrackers(QS("keywork").Trim(), QS("project", 0), QS("status", 0), QS("companyId", 0), QS("payment", 0), userId, beginTime, endTime , OrderBy, OrderDirection, anpWaitting.PageSize, CurrentPageIndex); rptWR.DataSource = request.ResultList; rptWR.DataBind(); anpWaitting.RecordCount = request.ResultCount; }
public SearchProposalTrackerRequest GetProposalTrackers(string keyword, int projectId, int status, int companyId, int payment, int userId, DateTime?beginTime, DateTime?endTime, string order, string dir, int pageCount, int pageIndex) { string strOrderby = string.Format(" {0} {1} ", order, dir); int start = pageIndex * pageCount + 1 - pageCount; int end = pageIndex * pageCount; StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT count(1)"); strSql.Append(@" from (SELECT w.RequestNo,w.Title,w.ProjectID,w.status,w.payment,p.CompanyID,"); strSql.Append(@"(SELECT STUFF(( SELECT ',' + PP.InvoiceNo FROM Invoices AS PP WHERE PP.Proposalid = w.ProposalTrackerID AND PP.InvoiceNo != '' FOR XML PATH('') ), 1, 1, '')) AS InvoiceNo"); strSql.Append(@" FROM (ProposalTracker w "); strSql.Append(@"left join Projects p on w.ProjectID= p.ProjectID LEFT JOIN dbo.Companys C ON C.ComID = p.CompanyID))NT"); strSql.Append(@" where 1=1 "); if (projectId > 0) { strSql.Append(@" AND NT.projectId=@projectId "); } if (status > 0) { strSql.Append(@" AND NT.status=@status "); } if (companyId > 0) { strSql.Append(@" AND NT.CompanyID=@companyId "); } if (payment > 0) { strSql.Append(@" AND NT.payment=@payment "); } if (!string.IsNullOrEmpty(keyword)) { strSql.Append(@" AND (NT.requestNo LIKE @keyword OR NT.Title LIKE @keyword OR NT.InvoiceNo LIKE @keyword)"); } if (userId > 0) { strSql.Append(@" AND NT.projectid in (select projectid from projectusers where userid = @userId); "); } strSql.Append(@"select * from "); strSql.Append(@"(select *,"); strSql.Append(string.Format(@"ROW_NUMBER() OVER (order by {0} )rownumber ", strOrderby)); strSql.Append(@"FROM(SELECT "); strSql.Append(@"w.ProposalTrackerID ,"); strSql.Append(@"w.ProjectID ,"); strSql.Append(@"p.CompanyID ,"); strSql.Append(@"c.CompanyName ,"); strSql.Append(@"w.Status ,"); strSql.Append(@"w.Title ,"); strSql.Append(@"w.Description ,"); strSql.Append(@"w.ProposalSentTo ,"); strSql.Append(@"w.ProposalSentOn ,"); strSql.Append(@"w.PONo ,"); strSql.Append(@"w.ApprovedBy ,"); strSql.Append(@"w.ApprovedOn ,"); strSql.Append(@"w.InvoiceSentOn ,"); strSql.Append(@"w.WorkScope ,"); strSql.Append(@"w.WorkScopeDisplayName ,"); strSql.Append(@"w.RequestNo ,"); strSql.Append(@"w.Payment ,"); strSql.Append(@"w.DueDate ,"); strSql.Append(@"w.CreatedOn ,"); strSql.Append(@"w.CreatedBy ,"); strSql.Append(@"w.ModifyOn ,"); strSql.Append(@"w.ModifyBy ,"); strSql.Append(@"w.Reminded ,"); strSql.Append(@"w.RemindTime ,"); strSql.Append(@"w.PoTotalLessThenProposalTotal ,"); strSql.Append(@"p.Title as ProjectName,"); strSql.Append(@"(SELECT STUFF(( SELECT ',' + PP.InvoiceNo FROM Invoices AS PP "); strSql.Append(@" WHERE PP.Proposalid = w.ProposalTrackerID AND PP.InvoiceNo != '' FOR XML PATH('') ), 1, 1, '')) AS InvoiceNo "); strSql.Append(@" FROM (ProposalTracker w "); strSql.Append(@"left join Projects p on w.ProjectID= p.ProjectID"); strSql.Append(@" LEFT JOIN dbo.Companys C ON C.ComID = p.CompanyID))NK "); strSql.Append(@" where 1=1 "); if (!string.IsNullOrEmpty(keyword)) { strSql.Append(@" AND (NK.InvoiceNo LIKE @keyword or NK.requestNo LIKE @keyword OR NK.Title LIKE @keyword )"); } if (projectId > 0) { strSql.Append(@" AND NK.projectId=@projectId "); } if (status > 0) { strSql.Append(@" AND NK.status=@status "); } if (companyId > 0) { strSql.Append(@" AND NK.CompanyID=@companyId "); } if (payment > 0) { strSql.Append(@" AND NK.payment=@payment "); } if (userId > 0) { strSql.Append(@" AND w.projectid in (select projectid from projectusers where userid = @userId) "); } if (beginTime != null) { strSql.Append(@" AND w.ApprovedOn>=@approvedOnBegin "); } if (endTime != null) { strSql.Append(@" AND w.ApprovedOn<=@approvedOnEnd "); } strSql.Append(@" )NEWTT where rownumber between @start and @end "); List <ProposalTrackerEntity> list; SearchProposalTrackerRequest response = new SearchProposalTrackerRequest(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "keyword", DbType.String, string.Format("%{0}%", keyword.FilterSqlString())); db.AddInParameter(dbCommand, "Status", DbType.String, status); db.AddInParameter(dbCommand, "companyId", DbType.Int32, companyId); db.AddInParameter(dbCommand, "projectId", DbType.Int32, projectId); db.AddInParameter(dbCommand, "payment", DbType.Int32, payment); db.AddInParameter(dbCommand, "userId", DbType.Int32, userId); if (beginTime != null) { db.AddInParameter(dbCommand, "approvedOnBegin", DbType.DateTime, beginTime); } if (endTime != null) { db.AddInParameter(dbCommand, "approvedOnEnd", DbType.DateTime, endTime); } db.AddInParameter(dbCommand, "order", DbType.String, order); db.AddInParameter(dbCommand, "dir", DbType.String, dir); db.AddInParameter(dbCommand, "start", DbType.Int32, start); db.AddInParameter(dbCommand, "end", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <ProposalTrackerEntity>(); if (dataReader.Read()) { response.ResultCount = dataReader.GetInt32(0); dataReader.NextResult(); } while (dataReader.Read()) { list.Add(ProposalTrackerEntity.ReaderBind(dataReader)); } response.ResultList = list; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]" , strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } return(response); }