/// <summary>
/// Räknar fullständigt antal träffar för söket och påverkas inte av pagingens LIMIT och OFFSET.
/// Ingår i ett transactionsScope med VisitorSearch()
/// </summary>
/// <param name="vs"></param>
/// <param name="offset"></param>
/// <param name="hitsPerPage"></param>
/// <returns></returns>
static private int CountDBHits(VisitorSearch vs, int offset, int hitsPerPage)
{
VisitorSearchReturn vsr = new VisitorSearchReturn();
// Start på countQueryn
string sqlStart = "SELECT COUNT(*) ";
// Komplett söksträng retuneras
string stmt = ReturnStringForVisitorSearch(vs, sqlStart, offset, hitsPerPage);
using (var conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
NpgsqlCommand cmd = ReturnCMDForVisitorSearch(vs, offset, hitsPerPage, stmt);
conn.Open();
cmd.Connection = conn;
vsr.CountTotalHits = Convert.ToInt32(cmd.ExecuteScalar());
}
return(vsr.CountTotalHits);
}
}
/// <summary>
/// Publik metod att kalla för att få ett komplett sökresultat då de båda metoderna enbart retunerar vad som är logiskt för dem själva
/// </summary>
/// <param name="vs"></param>
/// <param name="offset"></param>
/// <param name="hitsPerPage"></param>
/// <returns></returns>
public static VisitorSearchReturn CompleteStatSearch(VisitorSearch vs, int offset, int hitsPerPage)
{
VisitorSearchReturn vsr = new VisitorSearchReturn
{
CountTotalHits = CountDBHits(vs, offset, hitsPerPage),
VisitorSearches = GetVisitorSearchInfo(vs, offset, hitsPerPage)
};
return(vsr);
}
/// <summary>
/// Bygger ihop de olika strängarna för att skapa en fullständig query. Returnerar en lista med Employee, Visitor och BookedMeeting
/// </summary>
/// <param name="vs"></param>
/// <param name="offset"></param>
/// <param name="hitsPerPage"></param>
/// <returns></returns>
static private List <VisitorSearch> GetVisitorSearchInfo(VisitorSearch vs, int offset, int hitsPerPage)
{
VisitorSearchReturn vsr = new VisitorSearchReturn();
// start på söksträngen
string sqlStart = " SELECT visitor.visitor_id, visitor.firstname, visitor.lastname, visitor.company, visitor.check_in_old, visitor.check_out_old, " +
" employee.employee_id, employee.firstname, employee.lastname,booked_meeting.meeting_department ";
// Slutet på söksträngen. LIMIT OFFSET ska alltid ligga sist i queryn
string sqlEnd = " GROUP BY visitor.visitor_id, employee.employee_id, booked_meeting.booked_meeting_id" +
" ORDER BY visitor.check_in_old ASC" +
" LIMIT @hitsPerPage OFFSET @offset";
using (var conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
// start på strängen skickas med och addas i metoden
string stmt = ReturnStringForVisitorSearch(vs, sqlStart, offset, hitsPerPage);
// joinar de olika delarna av söksträngen
stmt = stmt + sqlEnd;
//skapar new Command och skickar tillbaka sträng och aktuella parametrar
NpgsqlCommand cmd = ReturnCMDForVisitorSearch(vs, offset, hitsPerPage, stmt);
conn.Open();
cmd.Connection = conn;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
vs = new VisitorSearch
{
Visitor = new Visitor
{
VisitorID = (reader["visitor_id"] != DBNull.Value) ? reader.GetInt32(0) : (int?)null,
Firstname = (reader["firstname"] != DBNull.Value) ? reader.GetString(1) : null,
Lastname = (reader["lastname"] != DBNull.Value) ? reader.GetString(2) : null,
Company = (reader["company"] != DBNull.Value) ? reader.GetString(3) : null,
CheckIn = (reader["check_in_old"] != DBNull.Value) ? reader.GetDateTime(4) : (DateTime?)null,
CheckOut = (reader["check_out_old"] != DBNull.Value) ? reader.GetDateTime(5) : (DateTime?)null,
},
Employee = new Employee
{
EmployeeID = (reader["employee_id"] != DBNull.Value) ? reader.GetInt32(6) : (int?)null,
Firstname = (reader["firstname"] != DBNull.Value) ? reader.GetString(7) : null,
Lastname = (reader["lastname"] != DBNull.Value) ? reader.GetString(8) : null,
},
BookedMeeting = new BookedMeeting
{
MeetingDepartment = (reader["meeting_department"] != DBNull.Value) ? reader.GetString(9) : null,
}
};
vsr.VisitorSearches.Add(vs);
}
}
scope.Complete();
}
}
return(vsr.VisitorSearches);
}
/// <summary>
/// Hämtar tid och avdelning för alla möten med den angivna anställda
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
static public VisitorSearchReturn GetTimeAndDepartment(Employee emp)
{
BookedMeeting bm = new BookedMeeting();
//stringBuilder för att man ska kunna söka med bara för eller efternamn
var sql = new StringBuilder();
sql.AppendLine("SELECT booked_meeting.meeting_department, booked_meeting.time_start, booked_meeting.booked_meeting_id, visit_responsible" +
" FROM(booked_meeting INNER JOIN employee ON booked_meeting.visit_responsible = employee.employee_id)" +
" WHERE 1 = 1");
if (emp.Firstname != "")
{
sql.AppendFormat(" AND employee.firstname = @eFirstname");
sql.AppendLine();
}
if (emp.Lastname != "")
{
sql.AppendFormat(" AND employee.lastname = @eLastname");
sql.AppendLine();
}
VisitorSearchReturn vsr = new VisitorSearchReturn();
VisitorSearch vs = new VisitorSearch();
var stmt = sql.ToString();
using (var conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString))
{
using (var cmd = new NpgsqlCommand(stmt, conn))
{
if (emp.Firstname != "")
{
cmd.Parameters.AddWithValue("@eFirstname", emp.Firstname);
}
if (emp.Lastname != "")
{
cmd.Parameters.AddWithValue("@eLastname", emp.Lastname);
}
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
vs = new VisitorSearch
{
BookedMeeting = new BookedMeeting
{
MeetingDepartment = (reader["meeting_department"] != DBNull.Value) ? reader.GetString(0) : null,
TimeStart = (reader["time_start"] != DBNull.Value) ? reader.GetTimeSpan(1) : (TimeSpan?)null,
BookedMeetingID = (reader["booked_meeting_id"] != DBNull.Value) ? reader.GetInt32(2) : (int?)null,
},
Employee = new Employee
{
EmployeeID = (reader["visit_responsible"] != DBNull.Value) ? reader.GetInt32(3) : (int?)null,
Firstname = emp.Firstname,
Lastname = emp.Lastname,
}
};
vsr.VisitorSearches.Add(vs);
}
}
}
}
return(vsr);
}