public static RepairList Find(int userID, int employeeID, int bussinessID, RepairFilter 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.Code, w.Service,
case when w.ProductID is null then w.ProductName else p.Name end as [ProductName],
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], e.Name as [EmployeeName],
c.Name as [ClientName], c.Code as [ClientCode], c.Address as [ClientAddress], c.Phone as [ClientPhone], isnull(sum(t.Amount), 0) as [Paid]
from Repair 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
left join Product p on p.ID = w.ProductID
left join Client c on w.ClientID = c.ID
left join Transactions t on w.ID = t.RepairID
where w.BussinessID = {0} and w.Status = 'active' {2}
group by w.ID, w.BussinessID, w.EmployeeID, w.WarehouseID, w.ProductID, w.ProductName, w.ClientID, 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, rwh.Name, p.Name, e.Name, c.Name, c.Code, c.Address, c.Phone
order by w.ID desc", bussinessID, top.HasValue ? String.Format("top {0}", top.Value) : "", String.Join(" ", conditions), userID);
var result = new RepairList(filter);
result.Data = Query <Repair>(new DbQuery(userID, employeeID, DbAction.Repair.View, query, log), out queryResult).ToList();
return(result);
}
public RepairList(RepairFilter filter = null)
{
Filter = filter;
if (Filter == null)
{
Filter = new RepairFilter();
}
}