public JsonResult VendorByLocationId(int LOCATION)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("VendorByLocationID"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, LOCATION);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "VendorByLocationID");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult AdvannceReport(int?category)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("AdvanceReport"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, category);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "AdvanceReport");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult EmployeeDetailsByID(int CATEGORY)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("EmployeeDetailsByID"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, CATEGORY);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "EmployeeDetailsByID");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult AttendanceResult(string caetory)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("AttendanceByCategory"));
objHelper.AddInParameter(objCommand, "Category", SqlDbType.VarChar, "TPI");
DataTable dtResult = objHelper.LoadDataTable(objCommand, "AttendanceByCategory");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public ActionResult MoneyTransferReport(int employeeID, string DATEFROM)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("MoneyTransferReport"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, employeeID);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, DATEFROM);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "MoneyTransferReport");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult DeductionReport(string FROM, string TO)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("DeductionReport"));
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, FROM);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, TO);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "DeductionReport");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
private DataTable BindTable(string date, int CategoryID)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("AttendanceDetails"));
objHelper.AddInParameter(objCommand, "DATE", SqlDbType.NVarChar, date);
objHelper.AddInParameter(objCommand, "CATEGORYID", SqlDbType.Int, CategoryID);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "AttendanceDetails");
return(dtResult);
}
public JsonResult SalaryProcess(string category, string DateFrom, string DateTo)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("SalaryProcess"));
objHelper.AddInParameter(objCommand, "CATEGORY", SqlDbType.Int, category);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, DateFrom);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, DateTo);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "SalaryProcess");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public ActionResult esipfreport(int category, string from, string to)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("esipfreport"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, category);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, from);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, to);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "esipfreport");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult Consolidate(int?category, string fromDate, string toDate)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("AttendanceConsolidate"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, category);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, fromDate);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, toDate);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "AttendanceConsolidate");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult HomeIndex(string Date, string firstDate, string lastDate)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("DashBoardAttendance"));
objHelper.AddInParameter(objCommand, "DATE", SqlDbType.NVarChar, Date);
objHelper.AddInParameter(objCommand, "FIRSTDATE", SqlDbType.NVarChar, firstDate);
objHelper.AddInParameter(objCommand, "LASTDATE", SqlDbType.NVarChar, lastDate);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "DashBoardAttendance");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult EmployeeIDCard(int?id)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("EmployeeIDCard"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, id);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "EmployeeIDCard");
if (dtResult.Rows.Count > 0)
{
ViewBag.photoPath = Server.MapPath(dtResult.Rows[0]["Photos"].ToString());
}
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult DailyAttendanceReport(int?category, string fromDate, string toDate, int empID)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = empID.Equals(0) ? objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("DailyAttendanceReport")) :
objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("DailyAttendanceReportByEmp"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, category);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, fromDate);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, toDate);
objHelper.AddInParameter(objCommand, "EMPLOYEEID", SqlDbType.NVarChar, empID);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "DailyAttendanceReport");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}
public JsonResult EmployeeDetailsByCatID(int CATEGORY)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("EmployeeDetailsByCategoryID"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, CATEGORY);
DataTable dtResult = objHelper.LoadDataTable(objCommand, "EmployeeDetailsByCategoryID");
for (int i = 0; i < dtResult.Rows.Count; i++)
{
empList.Add(new SelectListItem {
Text = dtResult.Rows[i]["name"].ToString(), Value = dtResult.Rows[i]["id"].ToString()
});
}
return(Json(empList, JsonRequestBehavior.AllowGet));
}
public ActionResult Index()
{
ViewBag.employeeTotal = db.Employees.Count();
ViewBag.companyTotal = db.Companies.Count();
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("TodayAbsent"));
var totalAbsent = objHelper.ExecuteScalar(objCommand);
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("TotalTPI"));
var totalTPI = objHelper.ExecuteScalar(objCommand);
ViewBag.totalAbsent = totalAbsent;
ViewBag.totalTPI = totalTPI;
return(View());
}
public JsonResult AnnexureReport(int?category, string fromDate, string toDate, int?cmpID)
{
SQLHelper.SQLHelper objHelper = new SQLHelper.SQLHelper();
SqlCommand objCommand = new SqlCommand();
QueryBuilder objBuilder = new QueryBuilder();
if (category != null)
{
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("AnnexureReportByCategory"));
objHelper.AddInParameter(objCommand, "ID", SqlDbType.Int, category);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, fromDate);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, toDate);
}
else
{
objCommand = objHelper.GetSqlQueryCommand(objBuilder.BuildQuery("AnnexureReportByCompany"));
objHelper.AddInParameter(objCommand, "COMPANYID", SqlDbType.NVarChar, cmpID);
objHelper.AddInParameter(objCommand, "DATEFROM", SqlDbType.NVarChar, fromDate);
objHelper.AddInParameter(objCommand, "DATETO", SqlDbType.NVarChar, toDate);
}
DataTable dtResult = objHelper.LoadDataTable(objCommand, "AnnexureReport");
return(Json(DataTableToJSON(dtResult), JsonRequestBehavior.AllowGet));
}