<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 decimal amount;
        public DateTimeOffset paidDate;
        public int durationMinutes;
        public int userExternalListingID;
        public int jobTitleID;
        public int clientUserID;
        public DateTimeOffset createdDate;
        public DateTimeOffset updatedDate;
        public string notes;
        public string jobTitleName;
        public string listingTitle;
        #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,
                amount = record.amount,
                paidDate = record.paidDate,
                durationMinutes = record.durationMinutes,
                userExternalListingID = record.userExternalListingID,
                jobTitleID = record.jobTitleID,
                clientUserID = record.clientUserID,
                createdDate = record.createdDate,
                updatedDate = record.updatedDate,
                notes = record.notes,
                jobTitleName = record.jobTitleName,
                listingTitle = record.listingTitle
            };
        }
        #endregion

        #region Fetch
        const string sqlSelect = @"
            SELECT TOP @LIMIT
                e.userID,
                e.earningsEntryID,
                e.amount,
                e.paidDate,
                e.durationMinutes,
                e.userExternalListingID,
                e.jobTitleID,
                e.clientUserID,
                e.notes,
                j.positionSingular as jobTitleName,
                l.title as listingTitle,
                e.createdDate,
                e.updatedDate
            FROM
                UserEarningsEntry as e
                INNER JOIN positions as j
                ON e.jobTitleID = j.positionID
                INNER JOIN UserExternalListing as l
                ON l.userExternalListingID = e.userExternalListingID
            WHERE
                e.Active = 1
                AND e.UserID = @0
                AND j.languageID = @1
                AND j.countryID = @2
                AND
                (@4 is null OR e.earningsEntryID < @4)
                AND
                (@5 is null OR e.earningsEntryID > @5)
        ";
        const string sqlAndID = @"
                AND e.earningsEntryID = @3
        ";
        private const string sqlOrderDesc = @"
            ORDER BY e.paidDate DESC
        ";
        private const string sqlOrderAsc = @"
            ORDER BY e.paidDate ASC
        ";
        public static UserEarningsEntry Get(int userID, int earningsEntryID, int languageID, int countryID)
        {
            using (var db = new LcDatabase())
            {
                return FromDB(db.QuerySingle(sqlSelect + sqlAndID, userID, languageID, countryID, earningsEntryID));
            }
        }

        public static IEnumerable<UserEarningsEntry> GetList(int userID, int languageID, int countryID, 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;<commit_msg>Fixed SQL error get earnings entry #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 decimal amount;
        public DateTimeOffset paidDate;
        public int durationMinutes;
        public int userExternalListingID;
        public int jobTitleID;
        public int clientUserID;
        public DateTimeOffset createdDate;
        public DateTimeOffset updatedDate;
        public string notes;
        public string jobTitleName;
        public string listingTitle;
        #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,
                amount = record.amount,
                paidDate = record.paidDate,
                durationMinutes = record.durationMinutes,
                userExternalListingID = record.userExternalListingID,
                jobTitleID = record.jobTitleID,
                clientUserID = record.clientUserID,
                createdDate = record.createdDate,
                updatedDate = record.updatedDate,
                notes = record.notes,
                jobTitleName = record.jobTitleName,
                listingTitle = record.listingTitle
            };
        }
        #endregion

        #region Fetch
        const string sqlSelect = @"
            SELECT TOP @LIMIT
                e.userID,
                e.earningsEntryID,
                e.amount,
                e.paidDate,
                e.durationMinutes,
                e.userExternalListingID,
                e.jobTitleID,
                e.clientUserID,
                e.notes,
                j.positionSingular as jobTitleName,
                l.title as listingTitle,
                e.createdDate,
                e.updatedDate
            FROM
                UserEarningsEntry as e
                INNER JOIN positions as j
                ON e.jobTitleID = j.positionID
                INNER JOIN UserExternalListing as l
                ON l.userExternalListingID = e.userExternalListingID
            WHERE
                e.Active = 1
                AND e.UserID = @0
                AND j.languageID = @1
                AND j.countryID = @2
                AND
                (@4 is null OR e.earningsEntryID < @4)
                AND
                (@5 is null OR e.earningsEntryID > @5)
        ";
        const string sqlAndID = @"
                AND e.earningsEntryID = @3
        ";
        private const string sqlOrderDesc = @"
            ORDER BY e.paidDate DESC
        ";
        private const string sqlOrderAsc = @"
            ORDER BY e.paidDate ASC
        ";
        public static UserEarningsEntry Get(int userID, int earningsEntryID, int languageID, int countryID)
        {
            using (var db = new LcDatabase())
            {
                var sql = (sqlSelect + sqlAndID).Replace("@LIMIT", "1");
                return FromDB(db.QuerySingle(sql, userID, languageID, countryID, earningsEntryID));
            }
        }

        public static IEnumerable<UserEarningsEntry> GetList(int userID, int languageID, int countryID, 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;
{'fraction_non_alphanumeric': 0.06242956952896101, 'fraction_numerical': 0.00428217263917061, 'mean_word_length': 1.4523348991434097, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, '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': '5438044', 'n_tokens_mistral': 2291, 'n_tokens_neox': 2127, 'n_words': 633}