public SearchInvoiceResponse SearchPOlist(SearchInvoiceRequest request)
{
this.ClearBrokenRuleMessages();
SearchInvoiceResponse response = mgr.SearchPOlist(request);
this.AddBrokenRuleMessages(mgr.BrokenRuleMessages);
return(response);
}
public Task <SearchInvoiceResponse> GetAll(FilterInvoiceRequest request)
{
var result = new SearchInvoiceResponse();
try
{
var invoiceAll = _invoiceService.GetAll(request.page, request.page_size, request.from_time ?? null);
var invoicesData = from a in invoiceAll.Invoices
let customer = _invoiceService.GetCustomer(a.CustomerId)
let address = _invoiceService.GetAddress(customer.AddressId)
let item = _invoiceService.GetItems(a.Id)
select new
{
ID = a.Id,
//a.DeliveryTime,
a.Note,
a.Served,
//a.ServerTime,
a.Status,
a.TotalPrice,
a.WeightTotal,
DeliveryTime = a.DeliveryTime,
TimeWindows = new
{
FromTime = 0,
ToTime = 86340
},
CustomerCode = customer.Code,
CustomerName = customer.Name,
CustomerID = customer.Id.ToString(),
Address = new
{
address.City,
address.Country,
address.District,
address.Id,
address.StreetNumber,
address.Street,
Lat = double.Parse(address.Lat),
Lng = double.Parse(address.Lng)
},
Items = item
};
result.Data = invoicesData.ToList();
result.Total = invoiceAll.PageInfo.Total;
}
catch (Exception ex)
{
result.Total = 0;
result.Message = ex.Message;
}
return(Task.FromResult(result));
}
public SearchInvoiceResponse SearchTimesheetInvoice(SearchInvoiceRequest request)
{
this.ClearBrokenRuleMessages();
SearchInvoiceResponse response = invoiceRepository.SearchTimesheetInvoice(request);
if (response == null)
{
this.AddBrokenRuleMessage();
}
return(response);
}
public void TimeTsheetBind(string timeTsheetIDs)
{
string projects = QS("projectid");
SearchInvoiceRequest request = new SearchInvoiceRequest();
request.ProjectIds = projects;
request.timeTsheetIDs = timeTsheetIDs;
SearchInvoiceResponse response = iapp.SearchTimesheetInvoice(request);
lblCompany.Text = response.TimesheetList[0].CompanyName;
decimal numHours = 0;
foreach (var item in response.TimesheetList)
{
numHours += item.Hours;
}
lblHours.Text = numHours.ToString();
}
public void RepDataBind()
{
hidProject.Value = QS("projectIds");
SearchInvoiceRequest request = new SearchInvoiceRequest();
request.OrderExpression = OrderBy;
request.OrderDirection = OrderDirection;
request.ProjectIds = QS("projectIds");
request.CompanyId = QS("companyId", 0);
SearchInvoiceResponse response = iapp.SearchTimesheetInvoice(request);
rptTicketsList.DataSource = response.TimesheetList;
rptTicketsList.DataBind();
decimal numHours = 0;
foreach (var item in response.TimesheetList)
{
numHours += item.Hours;
}
lblTotalHours.Text = numHours.ToString();
}
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;
}
}
/// <summary>
/// PO List page use this method
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SearchInvoiceResponse SearchPOlist(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(@"SELECT C.CompanyName,PT.PONo,PT.Title,PT.ApprovedOn,i.Milestone,i.InvoiceNo,i.Status
FROM dbo.Companys C,dbo.ProposalTracker PT,dbo.Projects P,dbo.Invoices i
WHERE C.ComID=P.CompanyID AND P.ProjectID=PT.ProjectID AND pt.ProposalTrackerID=i.ProposalId AND pt.ApprovedOn IS NOT NULL ");
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.PassDue:
sqlStr.Append("AND i.DueOn < (select getdate() )");
break;
case InvoiceSearchType.All:
break;
}
if (request.Keywords != "")
{
sqlStr.Append("AND (");
sqlStr.Append(" PT.PONo 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 (!string.IsNullOrEmpty(request.ApproveOn))
{
sqlStr.Append(" AND PT.ApprovedOn=@ApprovedOn ");
}
if (request.OrderExpression != "")
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY PT.ApprovedOn ");
}
if (request.OrderDirection != "")
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <POListModel> 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, "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);
db.AddInParameter(dbCommand, "ApprovedOn", DbType.String, request.ApproveOn);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <POListModel>();
while (dataReader.Read())
{
list.Add(POListModel.ReaderBind(dataReader));
}
response.POList = list;
response.POListCount = 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);
}
/// <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 SearchInvoiceResponse SearchTimesheetInvoice(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("SELECT ts.ID,[SheetDate] ");
sqlStr.Append(" ,c.CompanyName,p.Title AS ProjectTitle,ts.[TicketID]");
sqlStr.Append(" ,t.Title AS TicketTitle,u.FirstName,[Hours]");
sqlStr.Append(" FROM [PM].[dbo].[TimeSheets] ts ");
sqlStr.Append(" LEFT JOIN dbo.Tickets t ON ts.TicketID =t.TicketID ");
sqlStr.Append(" LEFT JOIN dbo.Users u ON u.UserID=ts.CreatedBy ");
sqlStr.Append(" LEFT JOIN dbo.Companys c ON c.ComID=t.CompanyID ");
sqlStr.Append(" LEFT JOIN dbo.Projects p ON ts.ProjectID=p.ProjectID ");
sqlStr.Append("WHERE 1=1 and p.ProjectID IN (" + request.ProjectIds + ") " +
" and not exists (select * from TSInvoiceRelation where TSId=ts.id)");
if (request.timeTsheetIDs != "" && request.timeTsheetIDs != null)
{
sqlStr.Append("and ts.ID IN (" + request.timeTsheetIDs + ")");
}
if (request.CompanyId > 0)
{
sqlStr.Append("and c.ComID = @CompanyID ");
}
if (request.OrderExpression != "" && request.OrderExpression != null)
{
sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression);
}
else
{
sqlStr.Append("ORDER BY CompanyName ");
}
if (request.OrderDirection != "" && request.OrderDirection != null)
{
sqlStr.Append(request.OrderDirection);
}
else
{
sqlStr.Append("DESC;");
}
List <TimesheetInvoiceModel> list;
SearchInvoiceResponse response = new SearchInvoiceResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
db.AddInParameter(dbCommand, "OrderExpression", DbType.String, request.OrderExpression);
db.AddInParameter(dbCommand, "OrderDirection", DbType.String, request.OrderDirection);
// db.AddInParameter(dbCommand, "ProjectIDs", DbType.String, "("+request.ProjectIds+")");
db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <TimesheetInvoiceModel>();
while (dataReader.Read())
{
list.Add(TimesheetInvoiceModel.ReaderBind(dataReader));
}
response.TimesheetList = list;
response.TimesheetCount = 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);
}
/// <summary>
/// TO DO page use this method
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SearchInvoiceResponse SearchProposalInvoice(SearchInvoiceRequest request)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(@"select P.ProjectId,P.Title as ProjectTitle,PT.Title as ProposalTrackerTitle ,PT.PONo, Invoices.Milestone,Invoices.InvoiceNo,
Invoices.Status, PT.ProposalTrackerID,Invoices.ID as invoiceId from ProposalTracker PT
LEFT JOIN Projects P on PT.ProjectID = p.ProjectId
LEFT JOIN Invoices ON Invoices.ProposalId = PT.ProposalTrackerID ");
sqlStr.Append("WHERE 1=1 ");
sqlStr.Append("AND (");
sqlStr.Append("P.CompanyID != (select ComID from Companys where CompanyName='Sunnet')");
sqlStr.Append(")");
if (request.Keywords != "")
{
sqlStr.Append("AND (");
sqlStr.Append("Invoices.InvoiceNo LIKE @Keywords ");
sqlStr.Append("OR P.Title LIKE @Keywords ");
sqlStr.Append("OR PT.Title LIKE @Keywords ");
sqlStr.Append(") ");
}
if (request.ProjectId != 0)
{
sqlStr.Append("AND P.ProjectID=@ProjectId ");
}
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 <ProposalToDoModel> 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);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <ProposalToDoModel>();
while (dataReader.Read())
{
list.Add(ProposalToDoModel.ReaderBind(dataReader));
}
response.ProposalList = 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);
}