Profile picture
I want the bottom two endpoints to still have the same exact functionality but read from the sql instead

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using Newtonsoft.Json;
using PortalBackend;
using System.Data.SqlClient;

public class ContractEligibilitiesController : ApiController
{

    private string connectionString = "Server=RHALFADBUAT01;Database=SPLUAT3_MSCRM;User Id=azaidan99;Password='SPL123456789123456789!';";

    [HttpGet]
    [Route("api/contractEligibilites/sql")]
    public async Task<IHttpActionResult> GetContractEligibilities(string contractId)
    {
        string query = $"SELECT * FROM alfa_contracteligibilitiesBase WHERE alfa_Contractid = '{contractId}'";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                SqlDataReader reader = await command.ExecuteReaderAsync();

                List<AlfaContractEligibility> contractEligibilities = new List<AlfaContractEligibility>();

                while (reader.Read())
                {
                    AlfaContractEligibility eligibility = new AlfaContractEligibility();

                    if (!reader.IsDBNull(reader.GetOrdinal("alfa_name")))
                    {
                        eligibility.AlfaName = reader.GetString(reader.GetOrdinal("alfa_name"));
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("alfa_MowahadCityId")))
                    {
                        eligibility.AlfaMowahadCityId = reader.GetString(reader.GetOrdinal("alfa_MowahadCityId"));
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("alfa_Type")))
                    {
                        eligibility.AlfaType = reader.GetInt32(reader.GetOrdinal("alfa_Type"));
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("alfa_MowahadCountryId")))
                    {
                        eligibility.AlfaMowahadCountryId = reader.GetString(reader.GetOrdinal("alfa_MowahadCountryId"));
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("alfa_AddressTypeId")))
                    {
                        eligibility.AlfaAddressTypeId = reader.GetString(reader.GetOrdinal("alfa_AddressTypeId"));
                    }

                    contractEligibilities.Add(eligibility);
                }

                return Json(contractEligibilities);
            }
        }
    }


    [HttpGet]
    [Route("api/contractEligibilites/city")]
    public async Task<IHttpActionResult> GetCities(string contractId)
    {
        var url = $"http://rhalffeuat01/SPLUAT3/api/data/v9.1/alfa_contracteligibilitieses?$filter=_alfa_contractid_value%20eq%20%27{contractId}%27%20and%20alfa_type%20eq%203";

        using (HttpClient client = new HttpClient(new HttpClientHandler() { Credentials = new NetworkCredential("tswisy", "Tt@123456789", "SP") }))
        {
            var response = await client.GetAsync(url);
            var responseData = await response.Content.ReadAsStringAsync();
            var odataResponse = JsonConvert.DeserializeObject<ODataResponse<AlfaContractEligibility>>(responseData);

            var contractEligibilities = odataResponse.Value;

            var groupedContractEligibilities = contractEligibilities
                .GroupBy(e => GetNameForAddressType(e.AlfaAddressTypeValue))
                .Select(g => new
                {
                    AlfaAddressTypeId = g.Key,
                    Eligibilities = g.Select(e => new
                    {
                        e.AlfaType,
                        e.AlfaName,
                        e.AlfaMowahadCityId,
                        e.AlfaMowahadCountryId
                    })
                })
                .ToList();

            return Json(groupedContractEligibilities);
        }
    }

    [HttpGet]
    [Route("api/contractEligibilites/all")]
    public async Task<IHttpActionResult> GetGroupedContractEligibilities(string contractId)
    {
        var url = $"http://rhalffeuat01/SPLUAT3/api/data/v9.1/alfa_contracteligibilitieses?$filter=_alfa_contractid_value%20eq%20%27{contractId}%27";

        using (HttpClient client = new HttpClient(new HttpClientHandler() { Credentials = new NetworkCredential("tswisy", "Tt@123456789", "SP") }))
        {
            var response = await client.GetAsync(url);
            var responseData = await response.Content.ReadAsStringAsync();
            var odataResponse = JsonConvert.DeserializeObject<ODataResponse<AlfaContractEligibility>>(responseData);

            var contractEligibilities = odataResponse.Value;

            var groupedContractEligibilities = contractEligibilities
                .Where(e => e.AlfaType != 0 && e.AlfaType != 3) // Exclude entries where alfa_type is 0
                .GroupBy(e => e.AlfaType)
                .Select(g => new
                {
                    AlfaType = g.Key,
                    Eligibilities = g.Select(e => new
                    {
                        e.AlfaName,
                        e.AlfaMowahadCountryId,
                        e.AlfaAddressTypeId
                    })
                })
                .ToList();

            return Json(groupedContractEligibilities);
        }
    }

    private string GetNameForAddressType(string addressTypeValue)
    {
        Dictionary<string, string> addressTypeNames = new Dictionary<string, string>()
    {
        { "3dbf1e46-0107-ee11-8985-005056b358ac", "National Address ID" },
        { "5c872261-0107-ee11-8985-005056b358ac", "Descriptive Address" },
        {"a3c80f96-0107-ee11-8985-005056b358ac","International Address" },
        { "3a79b77e-0107-ee11-8985-005056b358ac", "Office" },
        {"1c1c3a8b-0107-ee11-8985-005056b358ac","Parcel Station" },
        {"1bf9e16f-0107-ee11-8985-005056b358ac","P.O Box" },
    };

        if (addressTypeNames.TryGetValue(addressTypeValue, out string name))
        {
            return name;
        }
        else
        {
            return "Unknown";
        }
    }

    public class ODataResponse<T>
    {
        [JsonProperty("@odata.context")]
        public string Context { get; set; }

        public List<T> Value { get; set; }
    }

    public class AlfaContractEligibility
    {
        [JsonProperty("alfa_type")]
        public int AlfaType { get; set; }

        [JsonProperty("alfa_name")]
        public string AlfaName { get; set; }

        [JsonProperty("alfa_mowahadcityid")]
        public string AlfaMowahadCityId { get; set; }

        [JsonProperty("alfa_mowahadcountryid")]
        public string AlfaMowahadCountryId { get; set; }

        [JsonProperty("alfa_addresstypeid")]
        public string AlfaAddressTypeId { get; set; }

        [JsonProperty("_alfa_addresstype_value")]
        public string AlfaAddressTypeValue { get; set; }
    }

}