public ActionResult Client(ClientFilter filter)
{
var model = ClientReport.Get(Employee.BussinessID, filter);
if (Request.IsAjaxRequest())
{
return(Json(new
{
html = RenderPartialViewToString(Views.ClientPartial, model)
}, JsonRequestBehavior.AllowGet));
}
return(View(Views.Client, model));
}
public ActionResult Client()
{
var model = new ClientReport();
if (Request.IsAjaxRequest())
{
return(Json(new
{
html = RenderPartialViewToString(Views.ClientPartial, model)
}, JsonRequestBehavior.AllowGet));
}
return(View(Views.Client, model));
}
public static ClientReport Get(int bussinessID, ClientFilter filter = null)
{
var result = new ClientReport(filter);
try
{
var conditions = new List <string>();
if (filter != null)
{
if (!String.IsNullOrEmpty(filter.Code))
{
conditions.Add(String.Format("and c.Code like N'%{0}%'", filter.Code));
}
if (!String.IsNullOrEmpty(filter.Name))
{
conditions.Add(String.Format("and c.Name like N'%{0}%'", filter.Name));
}
}
var query = String.Format(
@"select c.ID, c.BussinessID, c.Code, c.Name, c.Phone, c.Address, c.Email, c.City, c.District, MAX(t.ID) as [TypeID], t.Name as [TypeName], c.Point,
isnull(sum(ts.Amount), 0) as [SaleTotal]
from Client c
left join ClientType ct on c.ID = ct.ClientID and ct.Status = 'active'
left join Type t on ct.TypeID = t.ID and t.Status = 'active'
left join [Order] o on o.ClientID = c.ID
left join Transactions ts on o.ID = ts.OrderID {2} {3}
where o.Removed = 0 and o.BussinessID = {0} {1}
group by c.ID, c.BussinessID, c.Code, c.Name, c.Phone, c.Address, c.Email, c.City, c.District, t.Name, c.Point
order by isnull(sum(ts.Amount), 0) desc",
bussinessID, String.Join(" ", conditions),
filter != null && filter.From.HasValue ? String.Format("and o.SubmitDate >= {0}", filter.From.DbValue()) : "",
filter != null && filter.To.HasValue ? String.Format("and o.SubmitDate <= {0}", filter.To.DbValue()) : "");
using (var con = Repo.DB.SKtimeManagement)
{
result.Data = con.Query <ClientInfo>(query).ToList();
}
}
catch { }
return(result);
}