private int AddInvoice(InvoiceEntity entity, string projectName, out string msg)
{
msg = string.Empty;
if (string.IsNullOrEmpty(entity.InvoiceNo))
{
entity.Status = InvoiceStatus.Missing_Invoice;
}
else
{
entity.Status = InvoiceStatus.Invoice_Created;
}
entity.CreatedBy = UserInfo.UserID;
entity.CreatedOn = DateTime.Now;
int id = _invoiceApp.AddInvoice(entity);
ProposalInvoiceModel model = _invoiceApp.GetInvoiceModelById(id);
if (entity.ReceiveOn != null)
{
string body = UtilFactory.Helpers.FileHelper.GetTemplateFileContent("SendEmailToPayment.txt");
body = body.Replace("[ProposalName]", model.ProposalTitle);
body = body.Replace("[Milestone]", entity.Milestone);
body = body.Replace("[InvoiceNo]", entity.InvoiceNo);
emailSender.SendMail("*****@*****.**", Config.DefaultSendEmail, "Project Payment", body);
}
if (_invoiceApp.BrokenRuleMessages.Count > 0 || id <= 0)
{
msg = _invoiceApp.BrokenRuleMessages[0].Message;
}
return(id);
}
protected void rptTicketsList_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
ProposalInvoiceModel record = (ProposalInvoiceModel)e.Item.DataItem;
HtmlAnchor lnkEdit = e.Item.FindControl("lnkEdit") as HtmlAnchor;
if (lnkEdit != null)
{
if (record.ProposalTitle == "")
{
lnkEdit.HRef = "TM/New.aspx?id=" + record.ID;
lnkEdit.Visible = true;
}
else
{
lnkEdit.HRef = "Proposal/DetailInvoice.aspx?proposalId=" + record.ProposalId + "&returnUrl=/Invoice/AwaingPayment.aspx";
lnkEdit.Attributes.Remove("data-target");
lnkEdit.Visible = true;
}
if (record.Status.ToString() == "Waive")
{
lnkEdit.Visible = false;
}
}
}
}
private void FillProposalInvoice()
{
ProposalInvoiceModel model = new ProposalInvoiceModel();
SearchInvoiceRequest request = new SearchInvoiceRequest();
request.OrderExpression = OrderBy;
request.OrderDirection = OrderDirection;
request.Keywords = txtKeyword.Text;
request.ProjectId = ddlProject.SelectedValue == "" ? 0 : int.Parse(ddlProject.SelectedValue);
request.Searchtype = InvoiceSearchType.ProposalOnly;
SearchInvoiceResponse response = invoiceApp.SearchProposalInvoice(request);
//这儿状态过滤 去掉了
//rptInvoiceList.DataSource = response.ProposalList.Where(e => e.Status == InvoiceStatus.Missing_Milestone
// || e.Status == InvoiceStatus.Missing_Invoice ||
// e.Status == InvoiceStatus.Awaiting_Send).ToList();
rptInvoiceList.DataSource = response.ProposalList;
rptInvoiceList.DataBind();
//proposalInvoiceCount.Text = response.ProposalList.Count(e => e.Status == InvoiceStatus.Missing_Milestone
// || e.Status == InvoiceStatus.Missing_Invoice ||
// e.Status == InvoiceStatus.Awaiting_Send).ToString();
proposalInvoiceCount.Text = response.ProposalList.Count.ToString();
if (response.ResultCount == 0)
{
trNoProposalInvoice.Visible = true;
}
else
{
trNoProposalInvoice.Visible = false;
}
}
/// <summary>
/// add timesheet invoice page use this method
/// </summary>
/// <param name="entityId"></param>
/// <returns></returns>
public ProposalInvoiceModel GetInvoiceModelById(int entityId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT Invoices.* ,");
strSql.Append("ProposalTracker.Title AS ProposalTitle,");
strSql.Append("ProposalTracker.PONo AS PONo,");
strSql.Append("Projects.ProjectID AS ProjectId,");
strSql.Append("Projects.Title AS ProjectTitle,");
strSql.Append("Companys.ComID AS CompanyId,");
strSql.Append("Companys.CompanyName AS CompanyName, ");
strSql.Append("TS.Hours AS [HOURS]");
strSql.Append("FROM Invoices AS Invoices ");
strSql.Append("LEFT JOIN dbo.ProposalTracker AS ProposalTracker ON ProposalTracker.ProposalTrackerID = Invoices.ProposalId ");
strSql.Append("LEFT JOIN dbo.Projects AS Projects ON ProposalTracker.ProjectID = Projects.ProjectID ");
strSql.Append("LEFT JOIN dbo.Companys AS Companys ON Projects.CompanyID = Companys.ComID ");
strSql.Append("LEFT JOIN dbo.TSInvoiceRelation AS TR ON Invoices.ID = TR.InvoiceId ");
strSql.Append("LEFT JOIN dbo.TimeSheets AS TS ON TR.TSId = TS.ID ");
strSql.Append(" where Invoices.ID=@entityId ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
db.AddInParameter(dbCommand, "entityId", DbType.Int32, entityId);
ProposalInvoiceModel model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
try
{
if (dataReader.Read())
{
model = ProposalInvoiceModel.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(model);
}
}
public void ViewBind(int id)
{
this.lbltitle.Text = "Edit";
this.btnsave.Text = "Submit";
ProposalInvoiceModel model = iapp.GetInvoiceModelById(id);
this.lblCompany.Text = model.CompanyName;
this.lblHours.Text = model.HOURS.ToString();
this.lblStatus.Text = model.Status.ToText();
this.txtInvoice.Text = model.InvoiceNo;
this.txtSendDate.Text = model.SendOn.Value.ToString("MM/dd/yyyy");
this.txtDueDate.Text = model.DueOn.Value.ToString("MM/dd/yyyy");
if (model.ReceiveOn != null)
{
this.txtReceiveDate.Text = ((DateTime)model.ReceiveOn).ToString("MM/dd/yyyy");
}
ddlStatus.SelectedValue = ((int)model.Status).ToString();
this.txtNote.Text = model.Notes;
}
private bool UpdateInvoice(InvoiceEntity entity, string projectName, out string msg)
{
msg = string.Empty;
entity.ModifiedBy = UserInfo.UserID;
entity.ModifiedOn = DateTime.Now;
bool result = _invoiceApp.UpdateInvoice(entity);
ProposalInvoiceModel model = _invoiceApp.GetInvoiceModelById(entity.ID);
if (entity.ReceiveOn != null)
{
string body = UtilFactory.Helpers.FileHelper.GetTemplateFileContent("SendEmailToPayment.txt");
body = body.Replace("[ProposalName]", model.ProposalTitle);
body = body.Replace("[Milestone]", entity.Milestone);
body = body.Replace("[InvoiceNo]", entity.InvoiceNo);
result = emailSender.SendMail("*****@*****.**", Config.DefaultSendEmail, "Project Payment", body);
}
if (_invoiceApp.BrokenRuleMessages.Count > 0)
{
msg = _invoiceApp.BrokenRuleMessages[0].Message;
}
return(result);
}
public void RepDataBind()
{
ProposalInvoiceModel model = new ProposalInvoiceModel();
SearchInvoiceRequest request = new SearchInvoiceRequest();
request.OrderExpression = OrderBy;
request.OrderDirection = OrderDirection;
request.Keywords = txtKeyword.Text;
request.Searchtype = InvoiceSearchType.AwitingPayment;
request.CompanyId = ddlCompany.SelectedValue == "" ? 0 : int.Parse(ddlCompany.SelectedValue);
SearchInvoiceResponse response = iapp.SearchInvoices(request);
rptTicketsList.DataSource = response.ResultList;
rptTicketsList.DataBind();
if (response.ResultCount == 0)
{
trNoTickets.Visible = true;
}
else
{
trNoTickets.Visible = false;
}
}
public ProposalInvoiceModel GetInvoiceModelById(int id)
{
ProposalInvoiceModel result = mgr.GetInvoiceModelById(id);
return(result);
}
/// <summary>
/// All Invoices page,Pass Due page,Awaiting Payment page use this method
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SearchInvoiceResponse SearchInvoices(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(@"SELECT i.ID,i.ProposalId,i.InvoiceNo,i.Milestone Milestone,c.CompanyName,i.SendOn,i.DueOn,i.ReceiveOn,i.Status,
(case when i.ProposalId>0 then (select SUM(Hours) from TimeSheets where ticketid in
(select TID from ProposalTrackerRelation where wid=i.ProposalId))
else sum(ts.Hours) end) [Hours],
i.Notes,pt.Title AS ProposalTitle,pt.PONo,p.Title as ProjectTitle
FROM Invoices i
LEFT JOIN dbo.TSInvoiceRelation tsr ON i.ID=tsr.InvoiceId
LEFT JOIN dbo.TimeSheets ts ON tsr.TSId=ts.ID
LEFT JOIN dbo.ProposalTracker pt ON pt.ProposalTrackerID=i.ProposalId
LEFT JOIN dbo.Projects p ON (p.ProjectID=ts.ProjectID or p.ProjectID=pt.ProjectID)
LEFT JOIN dbo.Companys c ON p.CompanyID=c.ComID ");
sqlStr.Append("WHERE 1=1 ");
switch (request.Searchtype)
{
case InvoiceSearchType.ProposalOnly:
sqlStr.Append("AND i.ProposalId !=0 ");
break;
case InvoiceSearchType.AwitingPayment:
sqlStr.Append("AND i.Status =" + (int)InvoiceStatus.Awaiting_Payment + " ");
break;
case InvoiceSearchType.Payment_Received:
sqlStr.Append("AND i.Status =" + (int)InvoiceStatus.Payment_Received + " ");
break;
case InvoiceSearchType.PassDue:
sqlStr.Append("AND i.DueOn < (select getdate()-1 ) AND i.Status <=5 ");
break;
case InvoiceSearchType.All:
break;
}
if (request.Keywords != "")
{
sqlStr.Append("AND (");
sqlStr.Append("i.InvoiceNo LIKE @Keywords ");
sqlStr.Append("OR p.Title LIKE @Keywords ");
//sqlStr.Append("OR ProposalTracker.Title LIKE %@Keywords% ");
sqlStr.Append(") ");
}
if (request.CompanyId != 0)
{
sqlStr.Append("AND c.ComID=@CompanyId ");
}
if ((int)request.InvoiceStatus != 0)
{
sqlStr.Append("AND i.Status=@Status ");
}
if (request.ProjectId != 0)
{
sqlStr.Append("AND p.ProjectId=@ProjectId ");
}
sqlStr.Append(" GROUP BY i.ID,i.ProposalId,i.InvoiceNo,i.SendOn,i.DueOn,i.ReceiveOn,i.Status,c.CompanyName,i.Notes,pt.Title,i.Milestone,pt.PONo,p.Title ");
if (request.OrderExpression != "")
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY p.Title ");
}
if (request.OrderDirection != "")
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <ProposalInvoiceModel> list;
SearchInvoiceResponse response = new SearchInvoiceResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString()));
db.AddInParameter(dbCommand, "ProjectId", DbType.Int32, request.ProjectId);
db.AddInParameter(dbCommand, "CompanyId", DbType.Int32, request.CompanyId);
db.AddInParameter(dbCommand, "OrderExpression", DbType.String, request.OrderExpression);
db.AddInParameter(dbCommand, "OrderDirection", DbType.String, request.OrderDirection);
db.AddInParameter(dbCommand, "Status", DbType.Int32, request.InvoiceStatus);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <ProposalInvoiceModel>();
while (dataReader.Read())
{
list.Add(ProposalInvoiceModel.ReaderBind(dataReader));
}
response.ResultList = list;
response.ResultCount = list.Count;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
sqlStr.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}
public ProposalInvoiceModel GetInvoiceModelById(int id)
{
ProposalInvoiceModel result = invoiceRepository.GetInvoiceModelById(id);
return(result);
}
