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