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

namespace LcRest
{
    /// <summary>
    /// UserSolution
    /// </summary>
    public class UserSolution
    {
        #region Fetch solutions
        public static IEnumerable<Solution> GetSolutionsByListing(int userID, int userListingID, Locale locale)
        {
            using (var db = new LcDatabase())
            {
                var sql = "SELECT " + Solution.sqlFields + Solution.sqlFrom + @"
                    INNER JOIN UserSolution As U
                     ON U.solutionID = S.solutionID
                        AND U.languageID = S.languageID
                        AND U.countryID = S.countryID
                    WHERE
                        S.Active = 1
                         AND U.userID = @0
                         AND U.userListingID = @1
                         AND S.languageID = @2
                         AND S.countryID = @3
                    ORDER BY U.DisplayRank
                ";
                return db.Query(sql, userID, userListingID, locale.languageID, locale.countryID).Select(Solution.FromDB);
            }
        }
        #endregion

        #region Put/replace list
        const string sqlSoftDeleteByUserListing = @"
            UPDATE UserSolution
            SET Active = 0
            WHERE UserID = @0
                AND UserListingID = @1
                AND LanguageID = @2
                AND CountryID = @3
        ";
        const string sqlSet = @"
            UPDATE UserSolution
            SET Active = 1,
                DisplayRank = @5,
                UpdatedDate = getdate(),
                ModifiedBy = @6
            WHERE UserID = @0
                AND UserListingID = @1
                AND LanguageID = @2
                AND CountryID = @3
                AND SolutionID = @4

            IF @@ROWCOUNT = 0 BEGIN
                INSERT INTO UserSolution (
                    UserID
                    ,UserListingID
                    ,LanguageID
                    ,CountryID
                    ,SolutionID
                    ,DisplayRank
                    ,CreatedDate
                    ,UpdatedDate
                    ,ModifiedBy
                    ,Active
                ) VALUES (
                    @0
                    ,@1
                    ,@2
                    ,@3
                    ,@4
                    ,@5
                    ,getdate()
                    ,getdate()
                    ,@6
                    ,1
                )
            END
        ";

        static void Set(int userID, int userListingID, int solutionID, int displayRank, Locale locale, LcDatabase sharedDb = null)
        {
            using (var db = new LcDatabase(sharedDb))
            {
                db.QueryValue(sqlSet,
                    userID,
                    userListingID,
                    locale.languageID,
                    locale.countryID,
                    solutionID,
                    displayRank,
                    userID.ToString()
                );
            }
        }

        public static void SetList(int userID, int userListingID, IEnumerable<int> sortedSolutions, Locale locale)
        {
            using (var db = new LcDatabase())
            {
                db.Execute("BEGIN TRANSACTION");
                db.Execute(sqlSoftDeleteByUserListing, userID, userListingID, locale.languageID, locale.countryID);
                var displayRank = 1;
                foreach (var solutionID in sortedSolutions)
                {
                    Set(userID, userListingID, solutionID, displayRank, locale, db);
                    displayRank += 1;
                }
                db.Execute("COMMIT TRANSACTION");
            }
        }
        #endregion
    }
}
<commit_msg>Fix on user solution API #796<commit_after>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace LcRest
{
    /// <summary>
    /// UserSolution
    /// </summary>
    public class UserSolution
    {
        #region Fetch solutions
        public static IEnumerable<Solution> GetSolutionsByListing(int userID, int userListingID, Locale locale)
        {
            using (var db = new LcDatabase())
            {
                var sql = "SELECT " + Solution.sqlFields + Solution.sqlFrom + @"
                    INNER JOIN UserSolution As U
                     ON U.solutionID = S.solutionID
                        AND U.languageID = S.languageID
                        AND U.countryID = S.countryID
                    WHERE
                        S.Active = 1
                         AND U.Active = 1
                         AND U.userID = @0
                         AND U.userListingID = @1
                         AND S.languageID = @2
                         AND S.countryID = @3
                    ORDER BY U.DisplayRank
                ";
                return db.Query(sql, userID, userListingID, locale.languageID, locale.countryID).Select(Solution.FromDB);
            }
        }
        #endregion

        #region Put/replace list
        const string sqlSoftDeleteByUserListing = @"
            UPDATE UserSolution
            SET Active = 0
            WHERE UserID = @0
                AND UserListingID = @1
                AND LanguageID = @2
                AND CountryID = @3
        ";
        const string sqlSet = @"
            UPDATE UserSolution
            SET Active = 1,
                DisplayRank = @5,
                UpdatedDate = getdate(),
                ModifiedBy = @6
            WHERE UserID = @0
                AND UserListingID = @1
                AND LanguageID = @2
                AND CountryID = @3
                AND SolutionID = @4

            IF @@ROWCOUNT = 0 BEGIN
                INSERT INTO UserSolution (
                    UserID
                    ,UserListingID
                    ,LanguageID
                    ,CountryID
                    ,SolutionID
                    ,DisplayRank
                    ,CreatedDate
                    ,UpdatedDate
                    ,ModifiedBy
                    ,Active
                ) VALUES (
                    @0
                    ,@1
                    ,@2
                    ,@3
                    ,@4
                    ,@5
                    ,getdate()
                    ,getdate()
                    ,@6
                    ,1
                )
            END
        ";

        static void Set(int userID, int userListingID, int solutionID, int displayRank, Locale locale, LcDatabase sharedDb = null)
        {
            using (var db = new LcDatabase(sharedDb))
            {
                db.QueryValue(sqlSet,
                    userID,
                    userListingID,
                    locale.languageID,
                    locale.countryID,
                    solutionID,
                    displayRank,
                    userID.ToString()
                );
            }
        }

        public static void SetList(int userID, int userListingID, IEnumerable<int> sortedSolutions, Locale locale)
        {
            using (var db = new LcDatabase())
            {
                db.Execute("BEGIN TRANSACTION");
                db.Execute(sqlSoftDeleteByUserListing, userID, userListingID, locale.languageID, locale.countryID);
                var displayRank = 1;
                foreach (var solutionID in sortedSolutions)
                {
                    Set(userID, userListingID, solutionID, displayRank, locale, db);
                    displayRank += 1;
                }
                db.Execute("COMMIT TRANSACTION");
            }
        }
        #endregion
    }
}

{'fraction_non_alphanumeric': 0.06855151045701006, 'fraction_numerical': 0.008004131164471986, 'mean_word_length': 0.9772843287391526, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, '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': '3205967', 'n_tokens_mistral': 1971, 'n_tokens_neox': 1764, 'n_words': 464}