public static SalaryReport Get(int userID, int employeeID, int bussinessID, EmployeeFilter filter)
{
var result = new SalaryReport(filter);
var employees = SalaryReport.FindEmployees(bussinessID, filter);
foreach (var employee in employees)
{
var infos = SalaryCalculator.Find(employee.ID, filter.From, filter.To);
dynamic record = new ExpandoObject();
var dictionary = (IDictionary <string, object>)record;
dictionary.Add("Name", employee.Name);
foreach (var info in infos)
{
result.Employees.Add(info);
var month = info.Month.Value;
if (!result.Months.Contains(month))
{
result.Months.Add(month);
}
dictionary.Add(info.Month.Value.ToString("_MMyyyy"), filter.ViewBaseSalary ? info.BaseSalary > 0 ? info.BaseSalary : employee.BaseSalary : info.CalculatedTotal.Round());
}
result.Records.Add(record);
}
result.Months = result.Months.OrderByDescending(i => i).ToList();
return(result);
}
public ActionResult FindEmployee(EmployeeFilter filter)
{
var data = EmployeeInfo.Find(UserID, Employee.ID, Employee.BussinessID, "", filter);
return(Json(new
{
html = RenderPartialViewToString("EmployeeList", data)
}, JsonRequestBehavior.DenyGet));
}
public ActionResult FindSalary(EmployeeFilter filter)
{
var data = SalaryCalculator.Find(Employee.BussinessID, filter);
return(Json(new
{
html = RenderPartialViewToString("SalaryList", data)
}, JsonRequestBehavior.DenyGet));
}
public SalaryReport(EmployeeFilter filter = null)
{
Filter = filter;
if (filter == null)
{
Filter = new EmployeeFilter();
}
Employees = new List <SalaryInfo>();
Months = new List <DateTime>();
Records = new List <dynamic>();
}
public ActionResult Calculate()
{
var data = new EmployeeFilter();
if (Request.IsAjaxRequest())
{
return(Json(new
{
html = RenderPartialViewToString(Views.CalculatePartial, data)
}, JsonRequestBehavior.AllowGet));
}
return(View(Views.Calculate, data));
}
public ActionResult Salary(EmployeeFilter filter)
{
var model = SalaryReport.Get(UserID, Employee.ID, Employee.BussinessID, filter);
if (Request.IsAjaxRequest())
{
return(Json(new
{
html = RenderPartialViewToString(Views.SalaryPartial, model)
}, JsonRequestBehavior.DenyGet));
}
return(View(Views.Salary, model));
}
public ActionResult List(EmployeeFilter filter)
{
var data = EmployeeInfo.Find(UserID, Employee.ID, Employee.BussinessID, "", filter, true);
if (Request.IsAjaxRequest())
{
return(Json(new
{
result = true,
html = RenderPartialViewToString(Views.ListPartial, data)
}, JsonRequestBehavior.AllowGet));
}
return(View(Views.List, data));
}
public SalaryCalculator(EmployeeFilter filter, SalaryInfo info, EmployeeInfo employeeInfo = null)
{
EmployeeFilter = filter;
EmployeeInfo = employeeInfo;
Info = info;
Paid = info.Paid;
Other = info.Other;
var time = DateTime.Now.AddMonths(-1);
if (info.Month.HasValue)
{
time = info.Month.Value;
}
From = new DateTime(time.Year, time.Month, 1);
To = From.AddMonths(1);
Note = info.Note;
}
public static List <EmployeeInfo> FindEmployees(int bussinessID, EmployeeFilter filter = null)
{
var conditions = new List <string>();
if (filter != null)
{
if (filter.ID.HasValue)
{
conditions.Add(String.Format("and e.ID = {0}", filter.ID.DbValue()));
}
if (!String.IsNullOrEmpty(filter.Address))
{
conditions.Add(String.Format("and e.Address like N'%{0}%'", filter.Address));
}
if (filter.LoginID.HasValue)
{
conditions.Add(String.Format("and l.ID = {0}", filter.LoginID.DbValue()));
}
if (!String.IsNullOrEmpty(filter.Name))
{
conditions.Add(String.Format("and e.Name like N'%{0}%'", filter.Name));
}
if (!String.IsNullOrEmpty(filter.Phone))
{
conditions.Add(String.Format("and e.Phone like N'%{0}%'", filter.Phone));
}
if (filter.StoreID.HasValue)
{
conditions.Add(String.Format("and e.StoreID = {0}", filter.StoreID.DbValue()));
}
}
using (var con = Repo.DB.SKtimeManagement)
{
return(con.Query <EmployeeInfo>(String.Format(@"select e.*, l.ID as [LoginID], l.Username, isnull(sum(o.Total - o.Discount), 0) as [CurrentSale]
from Employee e left join Login l on l.EmployeeID = e.ID
left join [Order] o on o.EmployeeID = e.ID and o.Removed = 0 and o.Status <> N'{2}'
where e.Status = 'active' and e.BussinessID = {0} {1}
group by e.AdditionalSalary, e.Address, e.BaseSalary, e.BussinessID, e.ID, e.Image, e.MonthlySale, e.Name, e.OffDays, e.Phone, e.Position, e.DOB,
e.StartDate, e.Status, e.StoreID, e.Summary, e.WorkDays, e.WorkTime, e.EndDate, e.BankNumber, e.BankName, e.BankBranch, e.WorkStatus, l.ID, l.Username
order by e.Name",
bussinessID, String.Join(" ", conditions), OrderStatus.Refunded)).ToList());
}
}
public ActionResult Download(EmployeeFilter filter)
{
var result = false;
try
{
var data = EmployeeInfo.Find(UserID, Employee.ID, Employee.BussinessID, "", filter, true);
if (data != null)
{
var fileName = String.Format("Employees_{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.Data);
Session[SessionKey.Download] = fileName;
result = true;
}
}
catch { }
return(Json(new
{
result = result
}, JsonRequestBehavior.DenyGet));
}
public static List <SalaryInfo> Find(int bussinessID, EmployeeFilter filter = null)
{
var conditions = new List <string>();
if (filter != null)
{
if (!String.IsNullOrEmpty(filter.Name))
{
conditions.Add(String.Format(" and e.Name like N'%{0}%'", filter.Name));
}
if (!String.IsNullOrEmpty(filter.Phone))
{
conditions.Add(String.Format(" and e.Phone like N'%{0}%'", filter.Phone));
}
if (!String.IsNullOrEmpty(filter.Address))
{
conditions.Add(String.Format(" and e.Address like N'%{0}%'", filter.Address));
}
if (filter.StoreID.HasValue)
{
conditions.Add(String.Format(" and e.StoreID = {0}", filter.StoreID.Value));
}
if (filter.SalaryMonth.HasValue)
{
conditions.Add(String.Format(" and s.Month = '{0}-{1}-1'", filter.SalaryMonth.Value.Year, filter.SalaryMonth.Value.Month));
}
}
var query = String.Format(
@"select s.*, e.Name as [EmployeeName] from Salary s join Employee e on s.EmployeeID = e.ID where e.BussinessID = {0} {1} order by s.Month desc",
bussinessID, String.Join("", conditions));
using (var con = Repo.DB.SKtimeManagement)
{
return(con.Query <SalaryInfo>(query).ToList());
}
}
public EmployeeList(string message = "", EmployeeFilter filter = null)
{
Data = new List <EmployeeInfo>();
Filter = filter != null ? filter : new EmployeeFilter();
Message = message;
}
public static EmployeeList Find(int userID, int employeeID, int bussinessID, string message = "", EmployeeFilter filter = null, bool log = false)
{
QueryOutput queryResult;
var conditions = new List <string>();
if (filter != null)
{
if (!String.IsNullOrEmpty(filter.Address))
{
conditions.Add(String.Format("and e.Address like N'%{0}%'", filter.Address));
}
if (filter.LoginID.HasValue)
{
conditions.Add(String.Format("and l.ID = {0}", filter.LoginID.DbValue()));
}
if (!String.IsNullOrEmpty(filter.Name))
{
conditions.Add(String.Format("and e.Name like N'%{0}%'", filter.Name));
}
if (!String.IsNullOrEmpty(filter.Phone))
{
conditions.Add(String.Format("and e.Phone like N'%{0}%'", filter.Phone));
}
if (!String.IsNullOrEmpty(filter.WorkStatus))
{
conditions.Add(String.Format("and e.WorkStatus = N'{0}'", filter.WorkStatus));
}
if (filter.StoreID.HasValue)
{
conditions.Add(String.Format("and e.StoreID = {0}", filter.StoreID.DbValue()));
}
if (filter.DOB.HasValue)
{
conditions.Add(String.Format("and e.DOB >= '{0}' and e.DOB <= '{1}'", filter.DOB.Value.ToString("yyyy-MM-01"), filter.DOB.Value.AddMonths(1).ToString("yyyy-MM-01")));
}
if (filter.Month.HasValue)
{
conditions.Add(String.Format("and month(e.DOB) = {0}", filter.Month.Value));
}
}
var result = new EmployeeList(message, filter);
result.Data = Query <EmployeeInfo>(new DbQuery(userID, employeeID, DbAction.Employee.View,
String.Format(@"select e.*, l.ID as [LoginID], l.Username, isnull(sum(o.Total - o.Discount), 0) as [CurrentSale], s.Name as [StoreName]
from Employee e left join Login l on l.EmployeeID = e.ID left join Store s on e.StoreID = s.ID
left join [Order] o on o.EmployeeID = e.ID and o.Removed = 0 and o.Status <> N'{2}'
where e.Status = 'active' and e.BussinessID = {0} {1}
group by e.AdditionalSalary, e.Address, e.BaseSalary, e.BussinessID, e.ID, e.Image, e.MonthlySale, e.Name, e.OffDays, e.Phone, e.Position, e.DOB, s.Name,
e.StartDate, e.Status, e.StoreID, e.Summary, e.WorkDays, e.WorkTime, e.EndDate, e.BankNumber, e.BankName, e.BankBranch, e.WorkStatus, l.ID, l.Username
order by e.Name",
bussinessID, String.Join(" ", conditions), OrderStatus.Refunded), log), out queryResult);
return(result);
}