Exemple #1
0
        /// <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);
            }
        }
Exemple #2
0
        /// <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);
        }
Exemple #3
0
        /// <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);
        }
Exemple #4
0
        /// <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);
        }