Exemple #1
0
        public IEnumerable <CardLogEntry> ReadAllCardsInTimespan(DateTime start, DateTime end)
        {
            Debug.Assert(start != null, "ReadAllCardsInTimespan: start time was null");
            Debug.Assert(end != null, "ReadAllCardsInTimespan: end time was null");
            var cursor = databaseConnection.CreateCommand();

            if (start == null || start.Equals(DateTime.MinValue) ||
                end == null || end.Equals(DateTime.MaxValue))
            {
                cursor.CommandText = TABLE_QUERY;
            }
            else
            {
                cursor.CommandText = TABLE_QUERY_WITH_DATES;

                cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
                cursor.Parameters[0].Value = start;

                cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
                cursor.Parameters[1].Value = end;
            }

            CardLogEntry    entry  = null;
            OleDbDataReader reader = null;

            try
            {
                cursor.Prepare();
                reader = cursor.ExecuteReader();
            }
            catch (InvalidOperationException ex)
            {
                Debug.Print(ex.ToString());
            }
            catch (OleDbException ex)
            {
                if ((uint)ex.HResult == 0x80040E37)
                {
                    MessageBox.Show("Logide tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
                }
                Debug.Print(ex.ToString());
            }

            if (reader == null)
            {
                Debug.Print("Something went wrong while reading.");
                yield break;
            }

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string   firstName = reader.GetString(reader.GetOrdinal(FIRST_NAME_FIELD));
                    string   lastName  = reader.GetString(reader.GetOrdinal(LAST_NAME_FIELD));
                    string   idCode    = reader.GetString(reader.GetOrdinal(ID_CODE_FIELD));
                    DateTime dateTime  = reader.GetDateTime(reader.GetOrdinal(DATETIME_FIELD));

                    long idCodeL = 0;

                    if (!long.TryParse(idCode, out idCodeL))
                    {
                        Debug.Print("Failed to parse idCode to long: {0}", idCode);
                    }

                    entry = new CardLogEntry()
                    {
                        firstName = firstName,
                        lastName  = lastName,
                        idCode    = idCodeL,
                        datetime  = dateTime
                    };
                    yield return(entry);
                }
            }
            else
            {
                yield break;
            }
        }
Exemple #2
0
        public List <AttendanceItem> ReadAttendanceData(DateTime start, DateTime end, JFilter j1, JFilter j2, string companyFilter = null, string platoonFilter = null)
        {
            Debug.Assert(start != null, "ReadAttendanceData: start time was null");
            Debug.Assert(end != null, "ReadAttendanceData: end time was null");
            var cursor = databaseConnection.CreateCommand();

            cursor.CommandText = ComposeAttendanceQuery(companyFilter != null, platoonFilter != null, j1, j2);

            Debug.Print("ReadAttendanceData: {0}", cursor.CommandText);
            Debug.Print("ReadAttendanceData: Start: {0}, End: {1} ", start, end);
            Debug.Print("ReadAttendanceData: company filter: {0}", companyFilter);
            Debug.Print("ReadAttendanceData: platoon filter: {0}", platoonFilter);
            Debug.Print("ReadAttendanceData: j1 filter: {0}", j1.enabled);
            Debug.Print("ReadAttendanceData: j2 filter: {0}", j2.enabled);

            cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
            cursor.Parameters[0].Value = start;

            cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
            cursor.Parameters[1].Value = end;

            var data = new List <AttendanceItem>();

            if (companyFilter != null)
            {
                Debug.Print("ReadAttendanceData: Using company filter");
                var param = new OleDbParameter("@company", OleDbType.VarWChar, companyFilter.Length);
                param.Value = companyFilter;
                cursor.Parameters.Add(param);
            }

            if (platoonFilter != null)
            {
                Debug.Assert(companyFilter != null, "Company filter cannot be null when platoon filter is active");
                Debug.Print("ReadAttendanceData: Using attendance for platoon: '{0}'", platoonFilter);
                var param = new OleDbParameter("@platoon", OleDbType.VarWChar, platoonFilter.Length);
                param.Value = platoonFilter;
                cursor.Parameters.Add(param);
            }

            if (j1.enabled && j1.desiredValue != null)
            {
                Debug.Print("Using J1 filter in attendance");
                var param = new OleDbParameter("@j1", OleDbType.Integer);
                if (j1.desiredValue == null)
                {
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Value = j1.desiredValue;
                }

                cursor.Parameters.Add(param);
            }

            if (j2.enabled && j2.desiredValue != null)
            {
                Debug.Print("Using J2 filter in attendance");
                var param = new OleDbParameter("@j2", OleDbType.Integer);
                if (j2.desiredValue == null)
                {
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Value = j2.desiredValue;
                }
                cursor.Parameters.Add(param);
            }

            CardLogEntry    entry  = null;
            OleDbDataReader reader = null;

            try
            {
                cursor.Prepare();
                reader = cursor.ExecuteReader();
            }
            catch (InvalidOperationException ex)
            {
                Debug.Print(ex.ToString());
                return(data);
            }
            catch (OleDbException ex)
            {
                if ((uint)ex.HResult == 0x80040E37)
                {
                    MessageBox.Show("Logide tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
                }
                Debug.Print(ex.ToString());
                return(data);
            }

            if (reader == null)
            {
                Debug.Print("Something went wrong while reading.");
                return(data);
            }

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string name    = reader.GetString(reader.GetOrdinal("Eesnimi")) + " " + reader.GetString(reader.GetOrdinal("Perekonnanimi"));
                    string platoon = reader.GetString(reader.GetOrdinal("Ryhm"));

                    if (data.Exists((item) => item.name == name))
                    {
                        Debug.Print("{0} on juba nimekirjas", name);
                        continue;
                    }

                    data.Add(new AttendanceItem()
                    {
                        name    = name,
                        platoon = platoon
                    });
                }
            }
            return(data);
        }
Exemple #3
0
        public List <Person> ReadUnknownPeople(DateTime start, DateTime end)
        {
            Debug.Assert(start != null, "ReadUnknownPeople: start time was null");
            Debug.Assert(end != null, "ReadUnknownPeople: end time was null");
            var cursor = databaseConnection.CreateCommand();

            Debug.Print("ReadUnknownPeople: start");

            var data = new List <Person>();

            cursor.CommandText = UNKNOWN_PEOPLE_QUERY;

            cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
            cursor.Parameters[0].Value = start;

            cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
            cursor.Parameters[1].Value = end;

            CardLogEntry    entry  = null;
            OleDbDataReader reader = null;

            try
            {
                cursor.Prepare();
                reader = cursor.ExecuteReader();
            }
            catch (InvalidOperationException ex)
            {
                Debug.Print(ex.ToString());
                return(data);
            }
            catch (OleDbException ex)
            {
                if ((uint)ex.HResult == 0x80040E37)
                {
                    MessageBox.Show("Logide või personali tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
                }
                Debug.Print(ex.ToString());
                return(data);
            }

            if (reader == null)
            {
                Debug.Print("Something went wrong while reading.");
                return(data);
            }

            if (reader.HasRows)
            {
                Debug.Print("ReadTreeViewData: has rows");
                while (reader.Read())
                {
                    string firstName = reader.GetString(reader.GetOrdinal("Eesnimi"));
                    string lastName  = reader.GetString(reader.GetOrdinal("Perekonnanimi"));
                    string idCode    = reader.GetString(reader.GetOrdinal("Isikukood"));

                    var p = new Person();
                    p.data.Add("Eesnimi", firstName);
                    p.data.Add("Perekonnanimi", lastName);
                    p.data.Add("Isikukood", idCode);
                    p.idCode = idCode;
                    data.Add(p);
                }
            }
            else
            {
                Debug.Print("ReadTreeViewData: no rows");
                return(data);
            }
            return(data);
        }
Exemple #4
0
        /// <summary>
        /// Read data for the PERSREP.
        /// Uses the PERSREP_QUERY.
        /// The PERSREP report can be limited by the start & end dates, and by string matching a company.
        /// </summary>
        /// <param name="start">The minimum signin date, inclusive</param>
        /// <param name="end">The maximum signin date, exclusive</param>
        /// <param name="companyFilter">If present, limit people to only one company.</param>
        /// <returns>List of PERSREP columns.</returns>
        public List <PersrepItem> ReadPersrepData(DateTime start, DateTime end, JFilter j1, JFilter j2, string companyFilter = null)
        {
            Debug.Assert(start != null, "ReadPersrepData: start time was null");
            Debug.Assert(end != null, "ReadPersrepData: end time was null");
            var cursor = databaseConnection.CreateCommand();

            Debug.Print("ReadPersrepData: start");

            var data = new List <PersrepItem>();

            cursor.CommandText = ComposePersrepQuery(companyFilter != null, j1, j2);


            Debug.Print(cursor.CommandText);
            Debug.Print("company filter: {0}", companyFilter);
            Debug.Print("j1 filter: {0}", j1.enabled);
            Debug.Print("j2 filter: {0}", j2.enabled);

            // NOTE: Parameters are order-specific only in access SQL. the @names don't matter, but are good for clarification.

            cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
            cursor.Parameters[0].Value = start;

            cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
            cursor.Parameters[1].Value = end;

            if (companyFilter != null)
            {
                Debug.Print("Company filter active");
                var param = new OleDbParameter("@company", OleDbType.VarWChar, companyFilter.Length);
                param.Value = companyFilter;
                cursor.Parameters.Add(param);
            }

            if (j1.enabled && j1.desiredValue != null)
            {
                Debug.Print("J1 filter active");
                var param = new OleDbParameter("@j1", OleDbType.Integer);
                param.Value = j1.desiredValue;
                cursor.Parameters.Add(param);
            }

            if (j2.enabled && j2.desiredValue != null)
            {
                Debug.Print("J2 filter active");
                var param = new OleDbParameter("@j1", OleDbType.Integer);
                if (j2.desiredValue == null)
                {
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Value = j2.desiredValue;
                }
                cursor.Parameters.Add(param);
            }

            CardLogEntry    entry  = null;
            OleDbDataReader reader = null;

            try
            {
                cursor.Prepare();
                reader = cursor.ExecuteReader();
            }
            catch (InvalidOperationException ex)
            {
                Debug.Print(ex.ToString());
                return(data);
            }
            catch (OleDbException ex)
            {
                if ((uint)ex.HResult == 0x80040E37)
                {
                    MessageBox.Show("Logide tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
                }
                Debug.Print(ex.ToString());
                return(data);
            }

            if (reader == null)
            {
                Debug.Print("Something went wrong while reading.");
                return(data);
            }

            if (reader.HasRows)
            {
                Debug.Print("ReadPersrepData: has rows");
                while (reader.Read())
                {
                    string company;
                    if (companyFilter == null)
                    {
                        company = reader.GetString(reader.GetOrdinal("Kompanii"));
                    }
                    else
                    {
                        company = reader.GetString(reader.GetOrdinal("Ryhm"));
                    }
                    int ohvitsere    = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Ohvitsere")));
                    int allohvitsere = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Allohvitsere")));
                    int sodureid     = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Sodureid")));
                    int tsiviliste   = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Tsiviliste")));

                    Debug.Print("PERSREP Kompanii: {0} O {1} AO {2} S {3} TSIV {4}", company, ohvitsere, allohvitsere, sodureid, tsiviliste);
                    data.Add(new PersrepItem()
                    {
                        company      = company,
                        ohvitsere    = ohvitsere,
                        allohvitsere = allohvitsere,
                        sodureid     = sodureid,
                        tsiviliste   = tsiviliste
                    });
                }
            }
            else
            {
                Debug.Print("ReadPersrepData: no rows");
                return(data);
            }
            return(data);
        }