public List <ProposalTrackerEntity> GetProposalTrackerByPid(int projectId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT pt.*,p.Title as ProjectName,p.CompanyID");
strSql.Append(@" FROM ProposalTracker pt,Projects p");
strSql.Append(@" where 1=1 and pt.ProjectID=p.ProjectID");
strSql.Append(@" AND pt.ProjectID=@projectId ");
List <ProposalTrackerEntity> list = new List <ProposalTrackerEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "projectId", DbType.Int32, projectId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
while (dataReader.Read())
{
list.Add(ProposalTrackerEntity.ReaderBind(dataReader));
}
}
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(list);
}
public List <ProposalTrackerEntity> GetEntitiesForPaymentEmail(string condition, string connStr)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ProposalTracker.*,Projects.Title AS ProjectName FROM dbo.ProposalTracker LEFT JOIN dbo.Projects ON ProposalTracker.ProjectID=Projects.ProjectID ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" WHERE ");
strSql.Append(condition);
}
List <ProposalTrackerEntity> list = new List <ProposalTrackerEntity>();
Database db = DatabaseFactory.CreateDatabase(connStr);
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
while (dataReader.Read())
{
list.Add(ProposalTrackerEntity.ReaderBind(dataReader));
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},Messages:\r\n{1}]"
, strSql.ToString(), ex.Message));
return(null);
}
}
return(list);
}
public bool Update(ProposalTrackerEntity entity)
{
BaseValidator <ProposalTrackerEntity> validator = new AddProposalTrackerValidator();
if (entity.ProjectID == 0)
{
this.AddBrokenRuleMessage("Project", "Please select Project");
return(false);
}
if (string.IsNullOrEmpty(entity.Title))
{
this.AddBrokenRuleMessage("Title", "Please input Title");
return(false);
}
if (entity.Description.Length > 500)
{
this.AddBrokenRuleMessage("Description", "Please input Description in 500 words");
return(false);
}
bool result = proposalTrackerRepository.Update(entity);
if (!result)
{
this.AddBrokenRuleMessage();
return(false);
}
return(result);
}
public ProposalTrackerEntity Get(int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *,'' as ProjectName, '' as CompanyName, 1 as CompanyID from ProposalTracker ");
strSql.Append(" where ProposalTrackerID=@ID ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ID", DbType.Int32, ID);
ProposalTrackerEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
model = ProposalTrackerEntity.ReaderBind(dataReader);
}
}
return(model);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
}
public bool UpdateProposalTrackerForPayment(ProposalTrackerEntity model, string connStr)
{
this.ClearBrokenRuleMessages();
bool result = mgr.UpdateProposalTrackerForPayment(model, connStr);
this.AddBrokenRuleMessages(mgr.BrokenRuleMessages);
return(result);
}
public bool UpdateProposalTracker(ProposalTrackerEntity model)
{
this.ClearBrokenRuleMessages();
bool result = mgr.Update(model);
this.AddBrokenRuleMessages(mgr.BrokenRuleMessages);
return(result);
}
public int AddProposalTracker(ProposalTrackerEntity model)
{
this.ClearBrokenRuleMessages();
int id = mgr.Add(model);
this.AddBrokenRuleMessages(mgr.BrokenRuleMessages);
return(id);
}
public int Insert(ProposalTrackerEntity model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ProposalTracker(");
strSql.Append("RequestNo,ProjectID,Title,Description,Status,InvoiceNo,Payment,DueDate,CreatedOn,CreatedBy,WorkScope,WorkScopeDisplayName");
strSql.Append(",ProposalSentTo,ProposalSentOn,PONo,ApprovedBy,ApprovedOn,InvoiceSentOn,Reminded,RemindTime, PoTotalLessThenProposalTotal)");
strSql.Append(" values (");
strSql.Append("@RequestNo,@ProjectID,@Title,@Description,@Status,@InvoiceNo,@Payment,@DueDate,@CreatedOn,@CreatedBy,@WorkScope,@WorkScopeDisplayName");
strSql.Append(",@ProposalSentTo,@ProposalSentOn,@PONo,@ApprovedBy,@ApprovedOn,@InvoiceSentOn,@Reminded,@RemindTime, @PoTotalLessThenProposalTotal)");
strSql.Append(";select ISNULL( SCOPE_IDENTITY(),0);");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "RequestNo", DbType.String, model.RequestNo);
db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, model.ProjectID);
db.AddInParameter(dbCommand, "Title", DbType.String, model.Title);
db.AddInParameter(dbCommand, "Description", DbType.String, model.Description);
db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status);
db.AddInParameter(dbCommand, "InvoiceNo", DbType.String, model.InvoiceNo);
db.AddInParameter(dbCommand, "Payment", DbType.String, model.Payment);
db.AddInParameter(dbCommand, "DueDate", DbType.DateTime, model.DueDate);
db.AddInParameter(dbCommand, "CreatedOn", DbType.String, model.CreatedOn);
db.AddInParameter(dbCommand, "CreatedBy", DbType.Int32, model.CreatedBy);
db.AddInParameter(dbCommand, "WorkScope", DbType.String, model.WorkScope);
db.AddInParameter(dbCommand, "WorkScopeDisplayName", DbType.String, model.WorkScopeDisplayName);
db.AddInParameter(dbCommand, "ProposalSentTo", DbType.String, model.ProposalSentTo);
db.AddInParameter(dbCommand, "ProposalSentOn", DbType.DateTime, model.ProposalSentOn);
db.AddInParameter(dbCommand, "PONo", DbType.String, model.PONo);
db.AddInParameter(dbCommand, "ApprovedBy", DbType.String, model.ApprovedBy);
db.AddInParameter(dbCommand, "ApprovedOn", DbType.DateTime, model.ApprovedOn);
db.AddInParameter(dbCommand, "InvoiceSentOn", DbType.DateTime, model.InvoiceSentOn);
db.AddInParameter(dbCommand, "Reminded", DbType.Int32, model.Reminded);
db.AddInParameter(dbCommand, "RemindTime", DbType.DateTime, model.RemindTime);
db.AddInParameter(dbCommand, "PoTotalLessThenProposalTotal", DbType.Boolean, model.PoTotalLessThenProposalTotal);
int result;
object obj = db.ExecuteScalar(dbCommand);
if (!int.TryParse(obj.ToString(), out result))
{
return(0);
}
return(result);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(0);
}
}
}
private void InitProject(ProposalTrackerEntity entity)
{
ProjectApplication projApp = new ProjectApplication();
List <ProjectDetailDTO> list = projApp.GetUserProjectsForCreateObject(UserInfo, entity.ProjectID);
if (list.Count > 1 || list.Count == 0)
{
list.BindDropdown <ProjectDetailDTO>(ddlProject, "Title", "ID", "Please select...", "0");
}
else if (list.Count == 1)
{
list.BindDropdown <ProjectDetailDTO>(ddlProject, "Title", "ID");
}
}
public ProposalTrackerEntity Get(int tid)
{
this.ClearBrokenRuleMessages();
if (tid <= 0)
{
return(null);
}
ProposalTrackerEntity ticketInfo = mgr.Get(tid);
if (ticketInfo == null)
{
this.AddBrokenRuleMessages(mgr.BrokenRuleMessages);
return(null);
}
return(ticketInfo);
}
public int Add(ProposalTrackerEntity entity)
{
BaseValidator <ProposalTrackerEntity> validator = new AddProposalTrackerValidator();
if (entity.ProjectID == 0)
{
this.AddBrokenRuleMessage("Project", "Please select Project");
return(0);
}
if (string.IsNullOrEmpty(entity.Title))
{
this.AddBrokenRuleMessage("Title", "Please input Title");
return(0);
}
//if (string.IsNullOrEmpty(entity.WorkScope))
//{
// this.AddBrokenRuleMessage("WorkScope", "Please select WorkScope");
// return 0;
//}
if (entity.Description.Length > 500)
{
this.AddBrokenRuleMessage("Description", "Please input Description in 500 words");
return(0);
}
//if (!validator.Validate(entity))
//{
// this.AddBrokenRuleMessages(validator.BrokenRuleMessages);
// return 0;
//}
int id = proposalTrackerRepository.Insert(entity);
if (id <= 0)
{
this.AddBrokenRuleMessage();
return(0);
}
return(id);
}
public bool UpdateProposalTrackerForPayment(ProposalTrackerEntity entity, string connStr)
{
BaseValidator <ProposalTrackerEntity> validator = new AddProposalTrackerValidator();
return(proposalTrackerRepository.UpdateProposalTrackerForPayment(entity, connStr));
}
protected void btnSave_Click(object sender, EventArgs e)
{
bool result = false;
ProposalTrackerEntity entity = GetEntity();
//Check Work Scope Format First David02/25/2016
if (fileUpload.HasFile)
{
string fileContentType = fileUpload.PostedFile.ContentType;
if ((fileContentType != "application/msword" && fileContentType != "application/pdf" &&
fileContentType != "application/vnd.openxmlformats-officedocument.wordprocessingml.document"))
{
ShowFailMessageToClient("Please select a file to upload ( *.doc, *.docx, *.pdf)");
return;
}
}
int id = wrApp.AddProposalTracker(entity);
if (id > 0)
{
if (fileUpload.HasFile) //Save Work Scope to 'Fiels' Table
{
FilesEntity model = FileFactory.CreateFileEntity(UserInfo.ID, ObjectFactory.GetInstance <ISystemDateTime>());
model.FilePath = entity.WorkScope;
model.ProposalTrackerId = id;
model.ContentType = fileUpload.PostedFile.ContentType;
model.FileID = 0;
model.FileSize = fileUpload.PostedFile.ContentLength;
model.FileTitle = Path.GetFileName(fileUpload.FileName);
model.IsDelete = false;
model.IsPublic = UserInfo.Role == RolesEnum.CLIENT;
model.SourceType = (int)FileSourceType.WorkRequestScope;
model.ThumbPath = Path.GetFileName(fileUpload.PostedFile.FileName);
result = fileApp.AddFile(model) > 0 ? true : false;
}
Redirect(entity.Status < 4 ? "index.aspx" : "EditProposalTracker.aspx?ID=" + id + "&returnurl=/OA/ProposalTracker/Index.aspx");
}
else
{
ShowFailMessageToClient(wrApp.BrokenRuleMessages);
}
/* David 0225 Changed the work scope is not required
* if (fileUpload.HasFile)
* {
* string fileContentType = fileUpload.PostedFile.ContentType;
* if ((fileContentType == "application/msword" || fileContentType == "application/pdf"
|| fileContentType == "application/vnd.openxmlformats-officedocument.wordprocessingml.document"))
|| {
|| int id = wrApp.AddProposalTracker(entity);
|| if (id > 0)
|| {
|| FilesEntity model = FileFactory.CreateFileEntity(UserInfo.ID, ObjectFactory.GetInstance<ISystemDateTime>());
|| model.FilePath = entity.WorkScope;
||
|| model.ProposalTrackerId = id;
|| model.ContentType = fileUpload.PostedFile.ContentType;
|| model.FileID = 0;
|| model.FileSize = fileUpload.PostedFile.ContentLength;
|| model.FileTitle = Path.GetFileName(fileUpload.FileName);
|| model.IsDelete = false;
|| model.IsPublic = UserInfo.Role == RolesEnum.CLIENT;
|| model.SourceType = (int)FileSourceType.WorkRequestScope;
|| model.ThumbPath = Path.GetFileName(fileUpload.PostedFile.FileName);
|| result = fileApp.AddFile(model) > 0 ? true : false;
|| Redirect("Index.aspx");
|| }
|| else
|| {
|| ShowFailMessageToClient(wrApp.BrokenRuleMessages);
|| }
|| }
|| else
|| {
|| ShowFailMessageToClient("Please select a file to upload ( *.doc, *.docx, *.pdf)");
|| }
||}
||else
||{
|| ShowFailMessageToClient("Please upload Work Scope");
||}
||
*/
}
private ProposalTrackerEntity GetEntity()
{
ProposalTrackerEntity model = new ProposalTrackerEntity();
// basic infomation
model.ProjectID = Convert.ToInt32(ddlProject.SelectedValue);
model.Status = Convert.ToInt32(ddlStatus.SelectedValue);
model.Title = txtTitle.Text.Trim();
model.Description = txtDescription.Text.Trim();
model.ProposalSentTo = txtProposalSentTo.Text.Trim();
DateTime proposalSentOn;
if (DateTime.TryParse(txtProposalSentOn.Text.Trim(), out proposalSentOn))
{
model.ProposalSentOn = proposalSentOn;
}
else
{
model.ProposalSentOn = null;
}
model.PONo = txtPO.Text.Trim();
model.ApprovedBy = txtApprovedBy.Text.Trim();
DateTime approvedOn;
if (DateTime.TryParse(txtApprovedOn.Text.Trim(), out approvedOn))
{
model.ApprovedOn = approvedOn;
}
else
{
model.ApprovedOn = null;
}
model.PoTotalLessThenProposalTotal = chkLessThen.Checked;
model.InvoiceNo = "";
model.InvoiceSentOn = null;
//DateTime dueDate;
//if (DateTime.TryParse(txtDueDate.Text.Trim(), out dueDate))
//{
// model.DueDate = dueDate;
//}
//else
//{
// model.DueDate = null;
//}
if (fileUpload.HasFile)
{
string fileContentType = fileUpload.PostedFile.ContentType;
model.WorkScope = UtilFactory.Helpers.FileHelper.SaveUploadFiles("WorkScope", model.ProjectID, fileUpload.PostedFile);
model.WorkScopeDisplayName = fileUpload.FileName;
}
model.RequestNo = "";
//model.RequestNo = wrApp.GetProposalTrackerNo();
model.CreatedOn = DateTime.Now;
model.CreatedBy = UserInfo.UserID;
return(model);
}
public List <ProposalTrackerEntity> GetProposalTrackers(string keyword, int projectId, int status,
int payment, int userId, string order, string dir)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT w.*,p.Title as ProjectName,ROW_NUMBER() OVER (order by workrequestid desc) as rowno");
strSql.Append(@" FROM ProposalTracker w ");
strSql.Append(@"left join Projects p on w.ProjectID= p.ProjectID");
strSql.Append(@" where 1=1 ");
if (projectId > 0)
{
strSql.Append(@" AND w.projectId=@projectId ");
}
if (status > 0)
{
strSql.Append(@" AND w.status=@status ");
}
if (payment > 0)
{
strSql.Append(@" AND w.payment=@payment ");
}
if (!string.IsNullOrEmpty(keyword))
{
strSql.Append(@" AND (w.requestNo LIKE @keyword
OR w.Title LIKE @keyword OR InvoiceNo LIKE @keyword )");
}
if (userId > 0)
{
strSql.Append(@" AND w.projectid in (select projectid from projectusers where userid = @userId) ");
}
strSql.Append(@" order by @order+ ' '+ @dir ");
List <ProposalTrackerEntity> list = new List <ProposalTrackerEntity>();
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, "projectId", DbType.Int32, projectId);
db.AddInParameter(dbCommand, "payment", DbType.Int32, payment);
db.AddInParameter(dbCommand, "userId", DbType.Int32, userId);
db.AddInParameter(dbCommand, "order", DbType.String, order);
db.AddInParameter(dbCommand, "dir", DbType.String, dir);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
while (dataReader.Read())
{
list.Add(ProposalTrackerEntity.ReaderBind(dataReader));
}
}
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(list);
}
private void BindData(ProposalTrackerEntity entity)
{
ListItem li = ddlProject.Items.FindByValue(entity.ProjectID.ToString());
if (li == null)
{
ProjectsEntity projectEntity = new App.ProjectApplication().Get(entity.ProjectID);
if (projectEntity != null)
{
ddlProject.Items.Insert(0, new ListItem()
{
Text = projectEntity.Title, Value = projectEntity.ID.ToString()
});
}
}
else
{
li.Selected = true;
}
//ddlPayment.SelectedValue = entity.Payment.ToString();
//txtRequestNo.Text = entity.RequestNo;
//txtInvoiceNo.Text = entity.InvoiceNo;
ddlStatus.SelectedValue = entity.Status.ToString();
//txtDueDate.Text = entity.DueDate == null ? "" : ((DateTime)entity.DueDate).ToString("MM/dd/yyyy");
txtTitle.Text = entity.Title;
txtDescription.Text = entity.Description;
txtProposalSentTo.Text = entity.ProposalSentTo;
txtProposalSentOn.Text = entity.ProposalSentOn == null ? "" : entity.ProposalSentOn.Value.ToShortDateString();
txtPO.Text = entity.PONo;
chkLessThen.Checked = entity.PoTotalLessThenProposalTotal.Value;
txtApprovedBy.Text = entity.ApprovedBy;
txtApprovedOn.Text = entity.ApprovedOn == null ? "" : entity.ApprovedOn.Value.ToShortDateString();
//txtInvoiceSentOn.Text = entity.InvoiceSentOn == null ? "" : entity.InvoiceSentOn.Value.ToShortDateString();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<ul class=\"fileList\"><li><a href=\"/do/DoDownWrokRequestFile.ashx?ID={0}\" title='{1}' target=\"_blank\">{1}</a>"
, entity.ProposalTrackerID, entity.WorkScopeDisplayName);
sb.Append("</li></ul>");
lblFile.InnerHtml = sb.ToString();
rptNotes.DataSource = wrApp.GetProposalTrackerNotes(entity.ProposalTrackerID);;
rptNotes.DataBind();
rptDocuments.DataSource = fileApp.GetFileListBySourceId(entity.ProposalTrackerID, FileSourceType.WorkRequest);
rptDocuments.DataBind();
decimal hours = wrApp.GetProposalTrackerHours(QS("id", 0));
string link = string.Format("<a href='/Report/Timesheet.aspx?WID={0}'>{1}</a>",
entity.ProposalTrackerID, hours.ToString("N"));
litHours.Text = link;
litProjectName.Text = ddlProject.SelectedItem.Text;
this.rptTicketsList.DataSource = wrApp.GetAllRelation(entity.ProposalTrackerID);
this.rptTicketsList.DataBind();
}
public bool Update(ProposalTrackerEntity model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update ProposalTracker set ");
strSql.Append("ProjectID=@ProjectID,");
strSql.Append("InvoiceNo=@InvoiceNo,");
strSql.Append("Payment=@Payment,");
strSql.Append("Status=@Status,");
strSql.Append("DueDate=@DueDate,");
strSql.Append("Title=@Title,");
strSql.Append("Description=@Description,");
strSql.Append("WorkScope=@WorkScope,");
strSql.Append("WorkScopeDisplayName=@WorkScopeDisplayName,");
strSql.Append("ModifyOn=@ModifyOn,");
strSql.Append("ModifyBy=@ModifyBy,");
strSql.Append("ProposalSentTo=@ProposalSentTo,");
strSql.Append("ProposalSentOn=@ProposalSentOn,");
strSql.Append("PONo=@PONo,");
strSql.Append("ApprovedBy=@ApprovedBy,");
strSql.Append("ApprovedOn=@ApprovedOn,");
strSql.Append("InvoiceSentOn=@InvoiceSentOn,");
strSql.Append("Reminded=@Reminded,");
strSql.Append("PoTotalLessThenProposalTotal=@PoTotalLessThenProposalTotal,");
strSql.Append("RemindTime=@RemindTime ");
strSql.Append(" where ProposalTrackerID=@ProposalTrackerID ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ProposalTrackerID", DbType.Int32, model.ProposalTrackerID);
db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, model.ProjectID);
db.AddInParameter(dbCommand, "InvoiceNo", DbType.String, model.InvoiceNo);
db.AddInParameter(dbCommand, "Payment", DbType.Int32, model.Payment);
db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status);
db.AddInParameter(dbCommand, "DueDate", DbType.DateTime, model.DueDate);
db.AddInParameter(dbCommand, "Title", DbType.String, model.Title);
db.AddInParameter(dbCommand, "Description", DbType.String, model.Description);
db.AddInParameter(dbCommand, "WorkScope", DbType.String, model.WorkScope);
db.AddInParameter(dbCommand, "WorkScopeDisplayName", DbType.String, model.WorkScopeDisplayName);
db.AddInParameter(dbCommand, "ModifyOn", DbType.DateTime, model.ModifyOn);
db.AddInParameter(dbCommand, "ModifyBy", DbType.Int32, model.ModifyBy);
db.AddInParameter(dbCommand, "ProposalSentTo", DbType.String, model.ProposalSentTo);
db.AddInParameter(dbCommand, "ProposalSentOn", DbType.DateTime, model.ProposalSentOn);
db.AddInParameter(dbCommand, "PONo", DbType.String, model.PONo);
db.AddInParameter(dbCommand, "ApprovedBy", DbType.String, model.ApprovedBy);
db.AddInParameter(dbCommand, "ApprovedOn", DbType.DateTime, model.ApprovedOn);
db.AddInParameter(dbCommand, "InvoiceSentOn", DbType.DateTime, model.InvoiceSentOn);
db.AddInParameter(dbCommand, "Reminded", DbType.Int32, model.Reminded);
db.AddInParameter(dbCommand, "RemindTime", DbType.DateTime, model.RemindTime);
db.AddInParameter(dbCommand, "PoTotalLessThenProposalTotal", DbType.Boolean, model.PoTotalLessThenProposalTotal);
int rows = db.ExecuteNonQuery(dbCommand);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
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);
}
