public List <EmployeeAbsence> GetAbcense()
{
List <EmployeeAbsence> absenceList = new List <EmployeeAbsence>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT [Employee No_], [First Name], [Last Name], [From Date], [Cause of Absence Code] FROM [CRONUS Sverige AB$Employee] INNER JOIN [CRONUS Sverige AB$Employee Absence] ON [CRONUS Sverige AB$Employee].No_= [CRONUS Sverige AB$Employee Absence].[Employee No_] WHERE [Cause of Absence Code]= 'SJUK' AND [From Date] LIKE '%2004%'", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
EmployeeAbsence newAbsence = new EmployeeAbsence();
newAbsence.empNo_ = reader.GetValue(0).ToString();
newAbsence.empFirstName = reader.GetValue(1).ToString();
newAbsence.empLastName = reader.GetValue(2).ToString();
newAbsence.fromDate = reader.GetValue(3).ToString();
newAbsence.causeOfAbsenceCode = reader.GetValue(4).ToString();
absenceList.Add(newAbsence);
}
}
}
}
return(absenceList);
}
public List <Relative> GetRelatives()
{
List <Relative> relativeList = new List <Relative>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT[CRONUS Sverige AB$Employee].[First Name], [CRONUS Sverige AB$Employee].[Last Name],[CRONUS Sverige AB$Employee].No_, [Relative Code], [CRONUS Sverige AB$Employee Relative].[First Name], [CRONUS Sverige AB$Employee Relative].[Last Name] FROM[CRONUS Sverige AB$Employee] INNER JOIN[CRONUS Sverige AB$Employee Relative] ON[CRONUS Sverige AB$Employee].[No_] = [CRONUS Sverige AB$Employee Relative].[Employee No_]", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Relative newRelative = new Relative();
newRelative.empFirstName = reader.GetValue(0).ToString();
newRelative.empLastName = reader.GetValue(1).ToString();
newRelative.empNo_ = reader.GetValue(2).ToString();
newRelative.relativeCode = reader.GetValue(3).ToString();
newRelative.firstName = reader.GetValue(4).ToString();
newRelative.lastName = reader.GetValue(5).ToString();
relativeList.Add(newRelative);
}
}
}
}
return(relativeList);
}
public List <string> GetIndexes()
{
List <string> indexList = new List <string>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT DISTINCT name FROM sys.indexes", connection))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
indexList.Add(reader.GetValue(0).ToString());
}
}
return(indexList);
}
}
public List <string> GetKeys()
{
List <string> keysList = new List <string>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT DISTINCT CONSTRAINT_NAME AS Keys FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE", connection))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
keysList.Add(reader.GetValue(0).ToString());
}
}
return(keysList);
}
}
public List <String> GetConstraints()
{
List <String> constraintsList = new List <String>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
constraintsList.Add(reader.GetValue(0).ToString());
}
}
}
return(constraintsList);
}
}
public List <String> GetMostAbsent()
{
List <String> mostAbsent = new List <String>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT TOP 1[First Name], COUNT([First Name]) AS 'Antal dagar borta' FROM[CRONUS Sverige AB$Employee Absence] JOIN[CRONUS Sverige AB$Employee] ON[CRONUS Sverige AB$Employee Absence].[Employee No_] =[CRONUS Sverige AB$Employee].No_ GROUP BY[First Name] ORDER BY COUNT([First Name]) DESC", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
mostAbsent.Add(reader.GetValue(0).ToString());
}
}
}
return(mostAbsent);
}
}
public List <String> GetColumns()
{
List <String> columnsList = new List <String>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CRONUS Sverige AB$Employee'", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
columnsList.Add(reader.GetValue(0).ToString());
}
}
}
return(columnsList);
}
}
public List <Employee> GetEmployees()
{
List <Employee> employeeList = new List <Employee>();
using (SqlConnection connection = new SqlConnection(CronusQueryWebService.GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT No_ AS 'Employee Number' , [First Name] AS 'First Name', [Last Name] AS 'Last Name' FROM [CRONUS Sverige AB$Employee]", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Employee newEmployee = new Employee();
newEmployee.no_ = reader.GetValue(0).ToString();
newEmployee.firstName = reader.GetValue(1).ToString();
newEmployee.lastName = reader.GetValue(2).ToString();
employeeList.Add(newEmployee);
}
}
}
}
return(employeeList);
}
