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 List(ClientFilter filter)
{
var data = ClientInfo.Find(UserID, Employee.ID, Employee.BussinessID, "", filter, true, null);
if (Request.IsAjaxRequest())
{
return(Json(new
{
result = true,
html = RenderPartialViewToString(Views.ListPartial, data)
}, JsonRequestBehavior.AllowGet));
}
return(View(Views.List, data));
}
public ActionResult Download(ClientFilter filter)
{
var result = false;
try
{
var data = ClientInfo.Find(UserID, Employee.ID, Employee.BussinessID, "", filter, true, null);
var fileName = String.Format("Client_{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss"));
var file = String.Format("{0}/Content/Download/{1}", SiteConfiguration.ApplicationPath, fileName);
Functions.CheckDirectory(String.Format("{0}/Content/Download/", SiteConfiguration.ApplicationPath));
SaveDownload(file, data);
Session[SessionKey.Download] = fileName;
result = true;
}
catch { }
return(Json(new
{
result = result
}, JsonRequestBehavior.DenyGet));
}
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);
}
public ClientReport(ClientFilter filter = null)
{
Filter = filter != null ? filter : new ClientFilter();
}
public ClientList(string message = "", ClientFilter filter = null)
{
Data = new List <ClientInfo>();
Filter = filter != null ? filter : new ClientFilter();
Message = message;
}
public static ClientList Find(int userID, int employeeID, int bussinessID, string message = "", ClientFilter filter = null, bool log = false, int?max = 100)
{
var result = new ClientList(message, filter);
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));
}
if (!String.IsNullOrEmpty(filter.Phone))
{
conditions.Add(String.Format("and c.Phone like N'%{0}%'", filter.Phone));
}
if (!String.IsNullOrEmpty(filter.Address))
{
conditions.Add(String.Format("and c.Address like N'%{0}%'", filter.Address));
}
if (!String.IsNullOrEmpty(filter.Email))
{
conditions.Add(String.Format("and c.Email like N'%{0}%'", filter.Email));
}
if (!String.IsNullOrEmpty(filter.City))
{
conditions.Add(String.Format("and c.City like N'%{0}%'", filter.City));
}
if (!String.IsNullOrEmpty(filter.District))
{
conditions.Add(String.Format("and c.District like N'%{0}%'", filter.District));
}
if (filter.TypeID.HasValue)
{
conditions.Add(String.Format("and t.ID = {0}", filter.TypeID.DbValue()));
}
}
QueryOutput queryResult; // coding here - change the way of list paging to improve performance
var strSql = String.Format(@"select {2} 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
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'
where c.BussinessID = {0} and c.Status = 'active' {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 c.Name",
bussinessID, String.Join("", conditions), max.HasValue ? String.Format("top {0}", max.Value) : String.Format("top {0}", 100));
result.Data = Query <ClientInfo>(
new DbQuery(userID, employeeID, DbAction.Client.View,
strSql, log), out queryResult);
return(result);
}