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

namespace LcRest
{
    /// <summary>
    /// Access to client info through the REST API
    /// </summary>
    public class Client
    {
        #region Fields
        public int clientUserID;
        public string email;
        public string firstName;
        public string lastName;
        public string secondLastName;
        public string phone;
        public bool canReceiveSms;
        public int? birthMonthDay;
        public int? birthMonth;
        public string notesAboutClient;
        public DateTime? createdDate;
        public DateTime? updatedDate;
        /// <summary>
        /// Editable is a special computed value, is true only
        /// when the client account status is 6:ServiceProfessional's Client (AKA 'client created and managed by a service professional')
        /// and the client's user.ReferredByUserID is the service professional querying
        /// the data.
        /// </summary>
        public bool editable;
        /// <summary>
        /// This fields is an alias for the internal field DeletedByServiceProfessional from the relationship since
        /// we don't want to expose the pair of fields for deletedBy* to users, just the one affecting
        /// them, on this case a Client record is exposed to professionals, so what matters are records deleted
        /// by the professional
        /// </summary>
        public bool deleted;
        #endregion

        #region Tools
        /// <summary>
        /// Only for internal checks
        /// </summary>
        /// <returns></returns>
        private string GetFullName()
        {
            var f = this.firstName;
            var l = this.lastName;
            var s = this.secondLastName;
            var r = "";

            if (!String.IsNullOrWhiteSpace(f))
                r += f;

            if (!String.IsNullOrWhiteSpace(l))
                r += (String.IsNullOrEmpty(r) ? "" : " ") + l;

            if (!String.IsNullOrWhiteSpace(s))
                r += (String.IsNullOrEmpty(r) ? "" : " ") + s;

            return r;
        }

        private const string EmptyEmailPrefix = "EMPTY:";
        /// <summary>
        /// It returns the given email when non empty, or an autogenerated
        /// string with prefix that is unique and can be stored at [UserProfile] (but is
        /// not a valid email, just a placeholder)
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public static string GetEmailForDb(string email)
        {
            if (String.IsNullOrWhiteSpace(email))
            {
                // Generate fake email, because database requires ever
                // an email, and must be non a duplicated
                return EmptyEmailPrefix + Guid.NewGuid().ToString();
            }
            else
            {
                return email;
            }
        }
        public static string GetEmailFromDb(string email)
        {
            if (email == null)
                return "";
            else if (email.StartsWith(EmptyEmailPrefix))
                return "";
            else
                return email;
        }
        #endregion

        #region Static Constructors
        public static Client FromDB(dynamic record)
        {
            if (record == null) return null;
            return new Client
            {
                clientUserID = record.clientUserID,
                email = GetEmailFromDb(record.email),
                firstName = record.firstName,
                lastName = record.lastName,
                secondLastName = record.secondLastName,
                phone = record.phone,
                canReceiveSms = record.canReceiveSms,
                birthMonthDay = record.birthMonthDay,
                birthMonth = record.birthMonth,
                notesAboutClient = record.notesAboutClient,
                createdDate = record.createdDate,
                updatedDate = record.updatedDate,
                editable = record.editable,
                deleted = record.deletedByServiceProfessional
            };
        }
        #endregion

        #region SQL
        private const string sqlSelect = @"SELECT ";
        private const string sqlSelectOne = @"SELECT TOP 1 ";
        private const string sqlFields = @"
                pc.ClientUserID as clientUserID
                ,up.Email as email
                ,uc.FirstName as firstName
                ,uc.LastName as lastName
                ,uc.SecondLastName as secondLastName
                ,uc.MobilePhone as phone
                ,uc.CanReceiveSms as canReceiveSms
                ,uc.BirthMonthDay as birthMonthDay
                ,uc.BirthMonth as birthMonth
                ,pc.NotesAboutClient as notesAboutClient
                ,pc.CreatedDate as createdDate
                ,pc.UpdatedDate as updatedDate
                ,(CASE WHEN uc.AccountStatusID = 6 AND uc.ReferredByUserID = @0 THEN Cast(1 as bit) ELSE Cast(0 as bit) END) as editable
                ,pc.DeletedByServiceProfessional as deletedByServiceProfessional
        FROM    ServiceProfessionalClient As pc
                 INNER JOIN
                Users As uc
                  ON uc.UserID = pc.ClientUserID
                 INNER JOIN
                UserProfile As up
                  ON up.UserID = uc.UserID
        WHERE   pc.Active = 1
                 AND uc.Active = 1
                 AND pc.ServiceProfessionalUserID = @0
    ";
        private const string sqlAndClientUserID = @"
        AND pc.ClientUserID = @1
    ";
        private const string sqlOrder = @"
            ORDER BY uc.FirstName, uc.LastName, uc.SecondLastName
        ";
        #endregion

        #region Fetch
        public static List<Client> GetServiceProfessionalClients(int serviceProfessionalUserID)
        {
            using (var db = Database.Open("sqlloco"))
            {
                return db.Query(sqlSelect + sqlFields + sqlOrder,
                    serviceProfessionalUserID)
                    .Select(FromDB)
                    .ToList();
            }
        }

        public static Client GetServiceProfessionalClient(int serviceProfessionalUserID, int clientUserID)
        {
            using (var db = Database.Open("sqlloco"))
            {
                // Parameter jobTitleID needs to be specified as 0 to avoid to join
                // the service-address table
                // Null value as 3th parameter since that placeholder is reserved for addressID
                // NOTE: Home address must exists ever, created on sign-up (GetSingleFrom
                // takes care to return null if not exists, but on this case is not possible
                // --or must not if not corrupted user profile)
                return GetSingleFrom(db.Query(
                    sqlSelectOne + sqlFields + sqlAndClientUserID,
                    serviceProfessionalUserID,
                    clientUserID
                ));
            }
        }

        private static Client GetSingleFrom(IEnumerable<dynamic> dbRecords)
        {
            var add = dbRecords
                .Select(FromDB)
                .ToList();

            if (add.Count == 0)
                return null;
            else
                return add[0];
        }
        #endregion

        #region Search and Checks
        /// <summary>
        /// A public search performs a search in all the database/marketplace users
        /// for an exact match of full name OR email OR phone. Only if one of them
        /// matches completely, the record is included in the results.
        /// The given serviceProfessionalUserID is used to exclude results: if the match is
        /// already a service professional's client, is excluded. The proposal of the search
        /// if to look for people out of the service professional agenda that are know by the
        /// service professional by a good identifier.
        /// The LcRest.Client class is used for the returned data,
        /// and since the client is not in the service professional agend that means that
        /// there is no data from [ServiceProfessionalClient] table so some fields comes
        /// with values as null/default, with the special Editable:false because
        /// serviceProfessional will clearly not be able to edit the record. The control
        /// fields (createdDate, updatedDate) as null clearly state that the record
        /// does not exists in the ServiceProfessional clients agenda.
        /// </summary>
        /// <param name="fullName"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public static List<Client> PublicSearch(int excludedServiceProfessionalUserID, string fullName, string email, string phone)
        {
            using (var db = Database.Open("sqlloco"))
            {
                return db.Query(@"
                SELECT<commit_msg>Fixed unoptimal 'single record' call at server Client<commit_after>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebMatrix.Data;

namespace LcRest
{
    /// <summary>
    /// Access to client info through the REST API
    /// </summary>
    public class Client
    {
        #region Fields
        public int clientUserID;
        public string email;
        public string firstName;
        public string lastName;
        public string secondLastName;
        public string phone;
        public bool canReceiveSms;
        public int? birthMonthDay;
        public int? birthMonth;
        public string notesAboutClient;
        public DateTime? createdDate;
        public DateTime? updatedDate;
        /// <summary>
        /// Editable is a special computed value, is true only
        /// when the client account status is 6:ServiceProfessional's Client (AKA 'client created and managed by a service professional')
        /// and the client's user.ReferredByUserID is the service professional querying
        /// the data.
        /// </summary>
        public bool editable;
        /// <summary>
        /// This fields is an alias for the internal field DeletedByServiceProfessional from the relationship since
        /// we don't want to expose the pair of fields for deletedBy* to users, just the one affecting
        /// them, on this case a Client record is exposed to professionals, so what matters are records deleted
        /// by the professional
        /// </summary>
        public bool deleted;
        #endregion

        #region Tools
        /// <summary>
        /// Only for internal checks
        /// </summary>
        /// <returns></returns>
        private string GetFullName()
        {
            var f = this.firstName;
            var l = this.lastName;
            var s = this.secondLastName;
            var r = "";

            if (!String.IsNullOrWhiteSpace(f))
                r += f;

            if (!String.IsNullOrWhiteSpace(l))
                r += (String.IsNullOrEmpty(r) ? "" : " ") + l;

            if (!String.IsNullOrWhiteSpace(s))
                r += (String.IsNullOrEmpty(r) ? "" : " ") + s;

            return r;
        }

        private const string EmptyEmailPrefix = "EMPTY:";
        /// <summary>
        /// It returns the given email when non empty, or an autogenerated
        /// string with prefix that is unique and can be stored at [UserProfile] (but is
        /// not a valid email, just a placeholder)
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public static string GetEmailForDb(string email)
        {
            if (String.IsNullOrWhiteSpace(email))
            {
                // Generate fake email, because database requires ever
                // an email, and must be non a duplicated
                return EmptyEmailPrefix + Guid.NewGuid().ToString();
            }
            else
            {
                return email;
            }
        }
        public static string GetEmailFromDb(string email)
        {
            if (email == null)
                return "";
            else if (email.StartsWith(EmptyEmailPrefix))
                return "";
            else
                return email;
        }
        #endregion

        #region Static Constructors
        public static Client FromDB(dynamic record)
        {
            if (record == null) return null;
            return new Client
            {
                clientUserID = record.clientUserID,
                email = GetEmailFromDb(record.email),
                firstName = record.firstName,
                lastName = record.lastName,
                secondLastName = record.secondLastName,
                phone = record.phone,
                canReceiveSms = record.canReceiveSms,
                birthMonthDay = record.birthMonthDay,
                birthMonth = record.birthMonth,
                notesAboutClient = record.notesAboutClient,
                createdDate = record.createdDate,
                updatedDate = record.updatedDate,
                editable = record.editable,
                deleted = record.deletedByServiceProfessional
            };
        }
        #endregion

        #region SQL
        private const string sqlSelect = @"SELECT ";
        private const string sqlSelectOne = @"SELECT TOP 1 ";
        private const string sqlFields = @"
                pc.ClientUserID as clientUserID
                ,up.Email as email
                ,uc.FirstName as firstName
                ,uc.LastName as lastName
                ,uc.SecondLastName as secondLastName
                ,uc.MobilePhone as phone
                ,uc.CanReceiveSms as canReceiveSms
                ,uc.BirthMonthDay as birthMonthDay
                ,uc.BirthMonth as birthMonth
                ,pc.NotesAboutClient as notesAboutClient
                ,pc.CreatedDate as createdDate
                ,pc.UpdatedDate as updatedDate
                ,(CASE WHEN uc.AccountStatusID = 6 AND uc.ReferredByUserID = @0 THEN Cast(1 as bit) ELSE Cast(0 as bit) END) as editable
                ,pc.DeletedByServiceProfessional as deletedByServiceProfessional
        FROM    ServiceProfessionalClient As pc
                 INNER JOIN
                Users As uc
                  ON uc.UserID = pc.ClientUserID
                 INNER JOIN
                UserProfile As up
                  ON up.UserID = uc.UserID
        WHERE   pc.Active = 1
                 AND uc.Active = 1
                 AND pc.ServiceProfessionalUserID = @0
    ";
        private const string sqlAndClientUserID = @"
        AND pc.ClientUserID = @1
    ";
        private const string sqlOrder = @"
            ORDER BY uc.FirstName, uc.LastName, uc.SecondLastName
        ";
        #endregion

        #region Fetch
        public static List<Client> GetServiceProfessionalClients(int serviceProfessionalUserID)
        {
            using (var db = Database.Open("sqlloco"))
            {
                return db.Query(sqlSelect + sqlFields + sqlOrder,
                    serviceProfessionalUserID)
                    .Select(FromDB)
                    .ToList();
            }
        }

        public static Client GetServiceProfessionalClient(int serviceProfessionalUserID, int clientUserID)
        {
            using (var db = Database.Open("sqlloco"))
            {
                // Parameter jobTitleID needs to be specified as 0 to avoid to join
                // the service-address table
                // Null value as 3th parameter since that placeholder is reserved for addressID
                // NOTE: Home address must exists ever, created on sign-up (GetSingleFrom
                // takes care to return null if not exists, but on this case is not possible
                // --or must not if not corrupted user profile)
                return FromDB(db.QuerySingle(
                    sqlSelectOne + sqlFields + sqlAndClientUserID,
                    serviceProfessionalUserID,
                    clientUserID
                ));
            }
        }
        #endregion

        #region Search and Checks
        /// <summary>
        /// A public search performs a search in all the database/marketplace users
        /// for an exact match of full name OR email OR phone. Only if one of them
        /// matches completely, the record is included in the results.
        /// The given serviceProfessionalUserID is used to exclude results: if the match is
        /// already a service professional's client, is excluded. The proposal of the search
        /// if to look for people out of the service professional agenda that are know by the
        /// service professional by a good identifier.
        /// The LcRest.Client class is used for the returned data,
        /// and since the client is not in the service professional agend that means that
        /// there is no data from [ServiceProfessionalClient] table so some fields comes
        /// with values as null/default, with the special Editable:false because
        /// serviceProfessional will clearly not be able to edit the record. The control
        /// fields (createdDate, updatedDate) as null clearly state that the record
        /// does not exists in the ServiceProfessional clients agenda.
        /// </summary>
        /// <param name="fullName"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public static List<Client> PublicSearch(int excludedServiceProfessionalUserID, string fullName, string email, string phone)
        {
            using (var db = Database.Open("sqlloco"))
            {
                return db.Query(@"
                SELECT
{'fraction_non_alphanumeric': 0.07488691573127827, 'fraction_numerical': 0.0014519461663036802, 'mean_word_length': 1.602528702223514, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 60, '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': '4528338', 'n_tokens_mistral': 4410, 'n_tokens_neox': 4085, 'n_words': 1498}