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