common/Service/MeterProcessState/Controllers/SoftwareAccessController.cs

using System;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Web.Http;
using Xylem.Common.Logic.ServiceCore;

namespace Service.MeterProcessState.Controllers
{
    [RoutePrefix("api/SoftwareAccess")]
    public class SoftwareAccessController : ApiController
    {

        public static class GlobalConfig
        {
            public static Lazy<String> connectionString = new Lazy<String>(()
            =>
            System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString
            );
        }

        [Route("GetVersionIsValid"), HttpGet]
        public async Task<HttpResponseMessage> GetVersionIsValid(String Program, Int32 Major, Int32 Minor, Int32 Build)
        {
            try
            {
                Boolean grant = false;
                using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value))
                {
                    var query = new StringBuilder();

                    query.AppendLine(" select SoftwareAccess_ID from versionSoftwareAccess  ");

                    query.AppendLine($" where  SoftwareAccess_Program = '{Program}'");

                    query.AppendLine($" and SoftwareAccess_MajorVersion = {Major}           ");
                    query.AppendLine($" and SoftwareAccess_MinorVersion = {Minor}           ");

                    query.AppendLine($" and (SoftwareAccess_BuildVersion = {Build}   or   SoftwareAccess_BuildVersion = -1  )     ");


                    query.AppendLine(" and SoftwareAccess_VaildTo >= CURRENT_TIMESTAMP      ");

                    var dt = dataacces.ExecuteQuery(query.ToString());
                    if (dt.Rows.Count >= 1)
                    {
                        grant = true;
                    }
                }
                return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return grant; }));
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex);
            }
        }

        [Route("GetCurrentVersion"), HttpGet]
        public async Task<HttpResponseMessage> GetCurrentVersion(String Program)
        {
            try
            {
                Boolean grant = false;
                using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value))
                {
                    var query = new StringBuilder();

                    query.AppendLine(" select top (1) * from versionSoftwareAccess  ");

                    query.AppendLine($" where  SoftwareAccess_Program = '{Program}'");

                    query.AppendLine(" and SoftwareAccess_VaildTo >= CURRENT_TIMESTAMP  order by  SoftwareAccess_ID desc    ");

                    var dt = dataacces.ExecuteQuery(query.ToString());
                    if (dt.Rows.Count == 1)
                    {
                        //SoftwareAccess_ID SoftwareAccess_Program	SoftwareAccess_MajorVersion	SoftwareAccess_MinorVersion	SoftwareAccess_BuildVersion	SoftwareAccess_VaildTo
                        //Xylem.Common.Ui.GenesisToolBox  1   5   7124    2019 - 09 - 05
                        var currentVersion = new Xylem.ServiceFwUpdate.Common.FwUpdateSafe.SoftwareLicense();
                        currentVersion.Program = dt.Rows[0]["SoftwareAccess_Program"].ToString();
                        currentVersion.Major  = (Int32)dt.Rows[0]["SoftwareAccess_MajorVersion"];
                        currentVersion.Minor = (Int32)dt.Rows[0]["SoftwareAccess_MinorVersion"];
                        currentVersion.Build = (Int32)dt.Rows[0]["SoftwareAccess_BuildVersion"];
                        currentVersion.Description = dt.Rows[0]["SoftwareAccess_Desc"].ToString();
                        currentVersion.ValidTo = (DateTime)dt.Rows[0]["SoftwareAccess_VaildTo"];

                        return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return currentVersion; }));
                    }
                }
                return Request.CreateResponse(HttpStatusCode.ExpectationFailed, "");

            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex);
            }
        }


        //
        [Route("SetVersionIsValid"), HttpPost]
        public async Task<HttpResponseMessage> SetVersionIsValid(String Program, Int32 Major, Int32 Minor, Int32 Build, DateTime ValidTo, String Description, Boolean ProgramNameIsUnique = true)
        {
            try
            {
                Boolean grant = false;
                using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value))
                {
                    var query = new StringBuilder();
                    query.AppendLine(" declare @ID as int                             ");
                    query.AppendLine(" set @ID = null                                 ");
                    query.AppendLine(" select top 1 @ID = SoftwareAccess_ID           ");
                    query.AppendLine(" from versionSoftwareAccess                     ");
                    query.AppendLine($" where  SoftwareAccess_Program = '{Program}'   ");
                    if (!ProgramNameIsUnique)
                    {
                        query.AppendLine($" and SoftwareAccess_MajorVersion = {Major}     ");
                        query.AppendLine($" and SoftwareAccess_MinorVersion = {Minor}     ");
                        query.AppendLine($" and SoftwareAccess_BuildVersion = {Build}     ");
                    }
                    query.AppendLine($" if @ID is null                                 ");
                    query.AppendLine($" begin                                         ");
                    query.AppendLine($"  insert into   versionSoftwareAccess          ");
                    query.AppendLine($"  select '{Program}', {Major},{Minor} ,{Build} ");
                    query.AppendLine($"          ,'{ValidTo.ToString("yyyy-MM-dd HH:mm:ss")}' ");
                    query.AppendLine($"          ,'{Description}'                     ");
                    query.AppendLine($"  set @ID = @@IDENTITY                         ");
                    query.AppendLine($" end                                           ");
                    query.AppendLine($" else                                          ");
                    query.AppendLine($" begin                                         ");
                    query.AppendLine($"  UPDATE [dbo].[VersionSoftwareAccess]         ");
                    query.AppendLine($"   SET [SoftwareAccess_Program] = '{Program}'  ");
                    query.AppendLine($"      ,[SoftwareAccess_MajorVersion] = {Major} ");
                    query.AppendLine($"      ,[SoftwareAccess_MinorVersion] = {Minor} ");
                    query.AppendLine($"      ,[SoftwareAccess_BuildVersion] = {Build} ");
                    query.AppendLine($"      ,[SoftwareAccess_VaildTo] = '{ValidTo.ToString("yyyy-MM-dd HH:mm:ss")}' ");
                    query.AppendLine($"      ,[SoftwareAccess_Desc] = '{Description}' ");
                    query.AppendLine($" WHERE SoftwareAccess_ID = @ID                 ");
                    query.AppendLine($" end                                           ");
                    query.AppendLine($"                                               ");
                    query.AppendLine($" select @ID                                    ");

                    var dt = dataacces.ExecuteQuery(query.ToString());
                    if (dt.Rows.Count >= 1)
                    {
                        grant = true;
                    }
                }
                return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return grant; }));
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex);
            }
        }
    }
}