[SqlText("default", "SELECT ansatt_nr AS EmployeeNo,fravaerskode.fravaerskode_nr AS AbsenceCodeNo,fravaerskode_navn AS AbsenceCodeName, CASE WHEN planlagt_fravaer_fra_dato > ? THEN planlagt_fravaer_fra_dato ELSE ? END AS FromDate,planlagt_fravaer_til_dato AS ToDate " +
                            "FROM planlagt_fravaer,fravaerskode WHERE planlagt_fravaer.fravaerskode_nr=fravaerskode.fravaerskode_nr AND ansatt_nr IN({0}) AND {1} AND planlagt_fravaer_til_dato>=? AND planlagt_fravaer_fra_dato<=? " +
                            "UNION SELECT ansatt_nr AS EmployeeNo,avviks_nr AS AbsenceCodeNo,fravaerskode_navn AS AbsenceCodeName, CASE WHEN avvik_fradato > ? THEN avvik_fradato ELSE ? END AS FromDate, avvik_tildato AS ToDate " +
                            "FROM ansatt_avvik,fravaerskode WHERE avviks_nr=fravaerskode.fravaerskode_nr AND ansatt_nr IN({0}) AND {2} AND avvik_fradato<=? AND (avvik_tildato>=? OR avvik_tildato IS NULL)")]
        public List<AbsenceCodeCargoSimpel> ListFuturePlannedAbsenceAndSickLeaveAbsenceForEmployee(List<long> positionsIdList, List<int> absenceCodes, DatoIntervall dates, ITransactionHelper transaction) {
            Log.TraceEntry(positionsIdList, absenceCodes, dates, transaction);
            var absenceCodeCargoSimpels = new List<AbsenceCodeCargoSimpel>();
            if (positionsIdList.Count == 0 || absenceCodes.Count == 0)
                return absenceCodeCargoSimpels;

            IDataReader dr = null;
            IDataHelper dh = OleDbDataHelper.GetInstance();
            const string inClausePlanlagtFravaer = "fravaerskode.fravaerskode_nr IN({0})";
            const string inClauseAnsattAvvik = "avviks_nr IN({0})";
            string positions = string.Join(",", Array.ConvertAll(positionsIdList.ToArray(), Convert.ToString));
            string expandedInClausePlanlagtFravaer = SQLHelper.ExpandInClause(inClausePlanlagtFravaer, absenceCodes);
            string expandedInClauseAnsattAvvik = SQLHelper.ExpandInClause(inClauseAnsattAvvik, absenceCodes);
            string parameterizedSql = string.Format(GetSql, positions, expandedInClausePlanlagtFravaer, expandedInClauseAnsattAvvik);
            try {
                IDataParameter[] parameters = {
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(DateTime.Today)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(DateTime.Today)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(dates.Start)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(dates.Slutt)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(DateTime.Today)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(DateTime.Today)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(dates.Slutt)),
                    new OleDbParameter("", DataHelper.dateTime2dbDateTimeFormat(dates.Start))
                };
                dr = transaction == null
                    ? dh.ExecuteReader(CommandType.Text, parameterizedSql, parameters)
                    : dh.ExecuteReader(transaction, CommandType.Text, parameterizedSql, parameters);
                var reader = new AbsenceCargoSimpleWithDescriptionReader(dr);
                while(dr.Read()) {
                    var cargoSimpel = new AbsenceCodeCargoSimpel(reader.EmployeeNo, reader.AbsenceCodeNo,
                        reader.AbsenceCodeName, reader.FromDate, reader.ToDate);
                    absenceCodeCargoSimpels.Add(cargoSimpel);
                }
            } catch(OleDbException odbException) {
                var kie = new KlarteIkkeHenteDataException("Could not fetch absence plan for employee. Error message: " + odbException.Message, odbException);
                throw kie;
            } finally {
                // Clean up. Must be in the finally block to clean up if something else would fail.
                dr?.Close();
            }
            Log.TraceExit(absenceCodeCargoSimpels);
            return absenceCodeCargoSimpels;
        }