public static WarrantyList Find(int userID, int employeeID, int bussinessID, WarrantyFilter filter = null, bool log = false, int?top = 100)
{
QueryOutput queryResult;
var conditions = new List <string>();
if (filter != null)
{
if (filter.WarehouseID.HasValue)
{
conditions.Add(String.Format("and w.WarehouseID = {0}", filter.WarehouseID.DbValue()));
}
if (!String.IsNullOrEmpty(filter.Code))
{
conditions.Add(String.Format("and w.Code like N'%{0}%'", filter.Code));
}
if (!String.IsNullOrEmpty(filter.ClientName))
{
conditions.Add(String.Format("and c.Name like N'%{0}%'", filter.ClientName));
}
if (!String.IsNullOrEmpty(filter.ClientPhone))
{
conditions.Add(String.Format("and c.Phone like N'%{0}%'", filter.ClientPhone));
}
if (!String.IsNullOrEmpty(filter.ClientEmail))
{
conditions.Add(String.Format("and c.Email like N'%{0}%'", filter.ClientEmail));
}
if (filter.From.HasValue)
{
conditions.Add(String.Format("and w.SubmitDate >= '{0}'", filter.From.Value.ToString(Constants.DatabaseDatetimeString)));
}
if (filter.To.HasValue)
{
conditions.Add(String.Format("and w.SubmitDate <= '{0}'", filter.To.Value.ToString(Constants.DatabaseDatetimeString)));
}
}
var query = String.Format(
@"select {1} w.ID, w.BussinessID, w.EmployeeID, w.WarehouseID, w.ProductID, w.ClientID, w.OrderID, w.Code, w.Service,
w.SubmitDate, w.TransferDate, w.ReceivedDate, w.ProcessedDate, w.ReturnedDate, w.FinishDate, w.ProductState,
w.Fee, w.Discount, w.Other, w.Note, wh.Name as [WarehouseName], rwh.Name as [ReceiveWarehouseName], w.ContactName, w.ContactPhone, e.Name as [EmployeeName],
c.Name as [ClientName], c.Code as [ClientCode], c.Address as [ClientAddress], c.Phone as [ClientPhone],
case when w.OrderID is not null then o.Code else w.OrderCode end as [OrderCode], isnull(sum(t.Amount), 0) as [Paid]
from Warranty w
join Warehouse wh on w.WarehouseID = wh.ID and w.Status = 'active' and ((select Username from Login where ID = {3}) = 'admin' or wh.ID in (select WarehouseID from LoginWarehouse where LoginID = {3}))
join Warehouse rwh on w.ReceiveWarehouseID = rwh.ID
join Employee e on w.EmployeeID = e.ID
join Product p on p.ID = w.ProductID
left join Client c on w.ClientID = c.ID
left join [Order] o on w.OrderID = o.ID
left join Transactions t on w.ID = t.WarrantyID
where w.BussinessID = {0} and w.Status = 'active' {2}
group by w.ID, w.BussinessID, w.EmployeeID, w.WarehouseID, w.ProductID, w.ClientID, w.OrderID, w.Code, w.ContactName, w.ContactPhone,
w.SubmitDate, w.TransferDate, w.ReceivedDate, w.ProcessedDate, w.ReturnedDate, w.FinishDate, w.ProductState, w.Service,
w.Fee, w.Discount, w.Other, w.Note, wh.Name, rwh.Name, e.Name, c.Name, c.Code, c.Address, c.Phone, o.Code, w.OrderCode
order by w.ID desc",
bussinessID, top.HasValue ? String.Format("top {0}", top.Value) : "", String.Join(" ", conditions), userID);
var result = new WarrantyList(filter);
result.Data = Query <Warranty>(new DbQuery(userID, employeeID, DbAction.Warranty.View, query, log), out queryResult).ToList();
return(result);
}