<commit_before>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace LcRest
{
    public class UserEarningsEntry
    {
        #region Fields
        public int userID;
        public int earningsEntryID;
        public DateTimeOffset paidDate;
        public int durationMinutes;
        public int userExternalListingID;
        public int jobTitleID;
        public int clientUserID;
        public DateTimeOffset createdDate;
        public DateTimeOffset updatedDate;
        public string notes;
        #endregion

        #region Computed fields
        private TimeSpan duration
        {
            get
            {
                return TimeSpan.FromMinutes(durationMinutes);
            }
            set
            {
                durationMinutes = (int)Math.Ceiling(value.TotalMinutes);
            }
        }
        #endregion

        #region Instances
        public UserEarningsEntry() { }

        private static UserEarningsEntry FromDB(dynamic record)
        {
            if (record == null) return null;
            return new UserEarningsEntry
            {
                userID = record.userID,
                earningsEntryID = record.earningsEntryID,
                paidDate = record.paidDate,
                durationMinutes = record.durationMinutes,
                userExternalListingID = record.userExternalListingID,
                jobTitleID = record.jobTitleID,
                clientUserID = record.clientUserID,
                createdDate = record.createdDate,
                updatedDate = record.updatedDate,
                notes = record.notes
            };
        }
        #endregion

        #region Fetch
        const string sqlSelect = @"
            SELECT 
                userID,
                earningsEntryID,
                paidDate,
                durationMinutes,
                userExternalListingID,
                jobTitleID,
                clientUserID,
                notes,
                createdDate,
                updatedDate
            FROM
                UserEarningsEntry
            WHERE
                Active = 1
                AND UserID = @0
        ";
        const string sqlAndID = @"
                AND earningsEntryID = @1
        ";
        private const string sqlOrderDesc = @"
            ORDER BY T.paidDate DESC
        ";
        private const string sqlOrderAsc = @"
            ORDER BY T.paidDate ASC
        ";
        public static UserEarningsEntry Get(int userID, int earningsEntryID)
        {
            using (var db = new LcDatabase())
            {
                return FromDB(db.QuerySingle(sqlSelect + sqlAndID, userID, earningsEntryID));
            }
        }

        public static IEnumerable<UserEarningsEntry> GetList(int userID, int limit, int? untilID = null, int? sinceID = null)
        {
            // Limits: Minimum: 1, Maximum: 100
            limit = Math.Max(1, Math.Min(limit, 100));

            var sql = sqlSelect + sqlOrderDesc;

            // Generally, we get the more recent records (order desc), except
            // if the parameter sinceID was set without an untilID: we
            // want the closest records to that, in other words, 
            // the older records that are more recent that sinceID.
            // A final sorting is done to return rows in descending as ever.
            var usingSinceOnly = sinceID.HasValue && !untilID.HasValue;
            if (usingSinceOnly)
            {
                sql = sqlSelect + sqlOrderAsc;
            }

            using (var db = new LcDatabase())
            {
                // db.Query has a bug not processiong parameters in 'select top @1'
                // so manual replacement
                sql = sql.Replace("@1", limit.ToString());

                var data = db.Query(sqlSelect, userID, limit, untilID, sinceID)
                .Select(FromDB);

                if (usingSinceOnly)
                {
                    // Since rows were get in ascending, records need to be inverted
                    // so we ever return data in descending order (latest first).
                    data.Reverse();
                }

                return data;
            }
        }
        #endregion

        #region Delete
        const string sqlDelete = @"
            UPDATE UserEarningsEntry
            SET Active = 0
            WHERE UserID = @0 AND EarningsEntryID = @1
        ";
        /// <summary>
        /// Delete an entry.
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="earningsEntryID"></param>
        public static void Delete(int userID, int earningsEntryID)
        {
            using (var db = new LcDatabase())
            {
                db.Execute(sqlDelete, userID, earningsEntryID);<commit_msg>Bugfixes earnings list with query limits #776<commit_after>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace LcRest
{
    public class UserEarningsEntry
    {
        #region Fields
        public int userID;
        public int earningsEntryID;
        public DateTimeOffset paidDate;
        public int durationMinutes;
        public int userExternalListingID;
        public int jobTitleID;
        public int clientUserID;
        public DateTimeOffset createdDate;
        public DateTimeOffset updatedDate;
        public string notes;
        #endregion

        #region Computed fields
        private TimeSpan duration
        {
            get
            {
                return TimeSpan.FromMinutes(durationMinutes);
            }
            set
            {
                durationMinutes = (int)Math.Ceiling(value.TotalMinutes);
            }
        }
        #endregion

        #region Instances
        public UserEarningsEntry() { }

        private static UserEarningsEntry FromDB(dynamic record)
        {
            if (record == null) return null;
            return new UserEarningsEntry
            {
                userID = record.userID,
                earningsEntryID = record.earningsEntryID,
                paidDate = record.paidDate,
                durationMinutes = record.durationMinutes,
                userExternalListingID = record.userExternalListingID,
                jobTitleID = record.jobTitleID,
                clientUserID = record.clientUserID,
                createdDate = record.createdDate,
                updatedDate = record.updatedDate,
                notes = record.notes
            };
        }
        #endregion

        #region Fetch
        const string sqlSelect = @"
            SELECT TOP @1
                userID,
                earningsEntryID,
                paidDate,
                durationMinutes,
                userExternalListingID,
                jobTitleID,
                clientUserID,
                notes,
                createdDate,
                updatedDate
            FROM
                UserEarningsEntry
            WHERE
                Active = 1
                AND UserID = @0
                AND
                (@2 is null OR earningsEntryID < @2)
                AND
                (@3 is null OR earningsEntryID > @3)
        ";
        const string sqlAndID = @"
                AND earningsEntryID = @1
        ";
        private const string sqlOrderDesc = @"
            ORDER BY paidDate DESC
        ";
        private const string sqlOrderAsc = @"
            ORDER BY paidDate ASC
        ";
        public static UserEarningsEntry Get(int userID, int earningsEntryID)
        {
            using (var db = new LcDatabase())
            {
                return FromDB(db.QuerySingle(sqlSelect + sqlAndID, userID, earningsEntryID));
            }
        }

        public static IEnumerable<UserEarningsEntry> GetList(int userID, int limit, int? untilID = null, int? sinceID = null)
        {
            // Limits: Minimum: 1, Maximum: 100
            limit = Math.Max(1, Math.Min(limit, 100));

            var sql = sqlSelect + sqlOrderDesc;

            // Generally, we get the more recent records (order desc), except
            // if the parameter sinceID was set without an untilID: we
            // want the closest records to that, in other words, 
            // the older records that are more recent that sinceID.
            // A final sorting is done to return rows in descending as ever.
            var usingSinceOnly = sinceID.HasValue && !untilID.HasValue;
            if (usingSinceOnly)
            {
                sql = sqlSelect + sqlOrderAsc;
            }

            using (var db = new LcDatabase())
            {
                // db.Query has a bug not processiong parameters in 'select top @1'
                // so manual replacement
                sql = sql.Replace("@1", limit.ToString());

                var data = db.Query(sql, userID, limit, untilID, sinceID)
                .Select(FromDB);

                if (usingSinceOnly)
                {
                    // Since rows were get in ascending, records need to be inverted
                    // so we ever return data in descending order (latest first).
                    data.Reverse();
                }

                return data;
            }
        }
        #endregion

        #region Delete
        const string sqlDelete = @"
            UPDATE UserEarningsEntry
            SET Active = 0
            WHERE UserID = @0 AND EarningsEntryID = @1
        ";
        /// <summary>
        /// Delete an entry.
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="earningsEntryID"></param>
        public static void Delete(int userID, int earningsEntryID)
        {
            using (var db = new LcDatabase())
            {
                db.Execute(sqlDelete, userID, earningsEntryID);
{'fraction_non_alphanumeric': 0.06938527084601338, 'fraction_numerical': 0.004057618178129438, 'mean_word_length': 1.3620028749401054, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7545309', 'n_tokens_mistral': 2487, 'n_tokens_neox': 2297, 'n_words': 705}