Date Ranges Start and End ASE 12 T-SQL

January 27, 2020 ~ Warren LaFrance ~ Leave a comment

— Getting month ranges

–First you need to figure out the current date:

declare @today datetime
select @today=getdate()
–Let’s start with the current month:

–Make the Assumption for examples I am working with a date of January, 30th. So the first day of the month is January, 1st.
–Unfortunately, you cannot directly set the day to 1. But you can extract the day from the date, in our case 30 and 1 is 30 – (30 – 1), so:

select dateadd(dd,-(day(@today)-1),@today)
–This will return: Jan 1 2013 1:49PM

–So basically we have the right date but for comparison purposes, we’d rather have midnight than 1:49pm.
–In order to do it, you need to convert it to a date string and back to a datetime:

select convert(datetime, convert(varchar(10),dateadd(dd,-(day(@today)-1),@today),101))
–Now we get: Jan 1 2013 12:00AM
–if you’re only interested in a string containing the date, just drop the outer convert:
select convert(varchar(10),dateadd(dd,-(day(@today)-1),@today),101)
–Use another format than 101 if needed. The complete list of date conversion formats can be found here. For example, for the German date format, use 104 (dd.mm.yyyy).

–MULTIPE PARTS HERE — Now let’s get the last day of the current month. This is basically the day before the first day of next month.

–So first let’s get the first day of next month.
— This is actually just 1 month later than the first day of the current month:

select convert(varchar(10),dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today)),101) as ‘First day of next month.’ — Part one of last day of current month
–This returns: 02/01/2013

–Now let’s just substract one day and we’ll get the last day of the current month:

select convert(varchar(10),dateadd(dd,-1,dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today))),101) as ‘last day of the current month’
–This returns: 01/31/2013

–Since we already have the first day of next month, let’s get the last day of next month.
— This is basically the same again but instead of adding 1 month, you add 2 months:

select convert(varchar(10),dateadd(mm,2,dateadd(dd,-day(@today),@today)),101) as ‘last day of next month’
–This returns: 02/28/2013

–Now let’s tackle the previous month.
— The first day of last month is basically the first day of the current month minus 1 month:

select convert(varchar(10),dateadd(mm,-1,dateadd(dd,-(day(@today)-1),@today)),101) as ‘first day of last month’
–This returns: 12/01/2012

–And then the last day of previous month. It is one day before the first day of the current month:
select convert(varchar(10),dateadd(dd,-(day(@today)),@today),101) as ‘last day of previous month’
–This returns: 12/31/2012

Powershell convert table colm index to letters for excel

December 4, 2019 ~ Warren LaFrance ~ Leave a comment

#============================================================================== # Convert columns Interger values to Letters for excel column range selections #============================================================================== # 1 based values, if your array is not zero based subtract 1 from index first function ExcelColumnIndexToName { param( [Parameter(Mandatory = $true)] [int] $index )

[string]$range = [string]::Empty;
if ($Index -lt 0 ) { return $range; }

[int]$a = 26;
$mfr = [Math]::Floor([Math]::Log(($Index) * ($a – 1) / $a + 1, $a))
[int]$x = [int]$mfr;
$Index -= [int]([Math]::Pow($a, $x) – 1) * $a / ($a – 1);
for ([int] $i = $x + 1; $Index + $i -gt 0; $i–) {
$range = ([char](65 + $Index % $a)).ToString() + $range;
$Index /= $a;
}
return $range;
}

So in another method that loops through the columns I could do the following

foreach ($tmpDataColm in $table.Columns) {

# This is the index integer value of the column in the columns array of the datatable

$colIndex = $table.Columns.IndexOf($tmpDataColm);

Write-Host($colIndex);

$colLetter = ExcelColumnIndexToName($colIndex);

#$excelCol = ExcelColumnIndexToName($table.Columns.IndexOf($tmpDataColm));

Write-Host(“Table Column index value: $colIndex to Excel letter: ” + $colLetter );

}

So the output in the console looks like this

psoutput

You can use this to make range selections without knowing how many columns the sql is creating and etc…

Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING

December 3, 2019December 3, 2019 ~ Warren LaFrance ~ Leave a comment

I needed to pull data from a ASE Database (T-SQL), filter it and then dump it into a excel workbook. Finally wrap all that up and attach it to a Email via SMTP…

See below for inline code or jump over to my github repo
https://github.com/wlafrance/Powershell/blob/master/QueryExcelEmail.ps

Continue reading “Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING” →

Quick NODE Demo site exposed to Internet…

June 14, 2019 ~ Warren LaFrance ~ Leave a comment

WARNING – YOU WILL EXPOSE YOUR COMPUTER TO THE WORLD…

Simplest option ever: local tunnel

Even if you have a dynamic IP, or you’re under a NAT, you can deploy your app and serve the requests right from your computer using a local tunnel. This option is suited for some quick testing, demo a product or sharing of an app with a very small group of people.

A very nice tool for this, available on all platforms, is ngrok.
Using it, you can just type ngrok PORT and the PORT you want is exposed to the internet. You will get a ngrok.io domain, but with a paid subscription you can get a custom URL as well as more security options (remember that you are opening your machine to the public Internet).

Another service you can use is https://github.com/localtunnel/localtun

‘ILoggerFactory’ does not contain a definition for ‘AddLog4Net’ and no accessible extension method

June 13, 2019June 13, 2019 ~ Warren LaFrance ~ Leave a comment

To fix this issue you need to run the npm command to install the MS Extension.

If you are getting this error:

‘ILoggerFactory’ does not contain a definition for ‘AddLog4Net’ and no accessible extension method ‘AddLog4Net’ accepting a first argument of type ‘ILoggerFactory’ could be found (are you missing a using directive or an assembly reference?)

You will need to add this extension to our project: [ link ]

Run this NUGET Command from the Package Manager Console in Visual Studio.

install-package Microsoft.Extensions.Logging.Log4Net.AspNetCore

React + Redux with ASP.NET Core 2.0 – Login & Registration Tutorial & Example Authorization

May 23, 2019May 23, 2019 ~ Warren LaFrance ~ Leave a comment

https://www.pointblankdevelopment.com.au/blog/135/react-redux-with-aspnet-core-20-login-registration-tutorial-example

VUE.JS and Angular – Enable Windows Authentication In Web API And Angular

May 15, 2019May 15, 2019 ~ Warren LaFrance ~ Leave a comment

Original Article: https://www.c-sharpcorner.com/article/enable-windows-authentication-in-web-api-and-angular-app/

Re-posted in case site goes away

Other resource: https://www.npmjs.com/package/activedirectory

Introduction

In this article, we will learn about how to use inbuilt Windows authentication in Web API and Angular application for authentication and authorization purposes.

Description

We have a requirement for in-house project development in the Angular App using Web API. As the purpose of this application is to use inside office only, so it’s suggested to use Windows Authentication mode.

Prerequisite

To access any web API from Angular or any Ajax method Web API must be CORS (Cross Origin Resource Sharing) enabled otherwise the request is not executed.

You can achieve this by referring to the below links.

Step 1

Create Web API Project and in Web.config select Authentication mode as “Windows”,

Enable Windows Authentication in Web API and Angular App

Web Config Code snippet

<system.web>
<authentication mode=“Windows” ></authentication>
</system.web>

Or you can publish web API project in IIS and Enable Windows Authentication from there.

So, based on the above screenshot you can enable Windows authentication for Web API Project.

Step 2

Use Authorize attribute on the controller or on any action method for security.

Code snippet for WebAPI Controller

[EnableCors(origins: “*”, headers: “*”, methods: “*”, SupportsCredentials = true)]
public partial class WebAPIController : ApiController
{
/// <summary>
/// This method contains Authorize attribute for authentication and authroization
/// </summary>
/// <returns></returns>
[HttpGet]
[Authorize]
[Route(“api/AuthenticateUser”)]
public HttpResponseMessage AuthenticateUser()
{
if (User != null)
{
return Request.CreateResponse(HttpStatusCode.OK, new
{
status = (int)HttpStatusCode.OK,
isAuthenticated = true,
isLibraryAdmin = User.IsInRole(@“domain\AdminGroup”),
username = User.Identity.Name.Substring(User.Identity.Name.LastIndexOf(@“\”) + 1)
});
}
else
{
//This code never execute as we have used Authorize attribute on action method
return Request.CreateResponse(HttpStatusCode.OK, new
{
status = (int)HttpStatusCode.BadRequest,
isAuthenticated = false,
isLibraryAdmin = false,
username = “”
});
}
}
}

As per the above screenshot, I have added [Authorize] attribute in AuthenticateUser Action method. This attribute makes sure that action is only executed if the user entered a valid credential otherwise it will display 401 Unauthorized access.

Here, I have added [Authorize] attribute only to action method. This can be put at Controller level as well and if the application has multiple Roles then it can be extended by passing Role name along with Authorize Attribute. You can explore more from WebAPI Authorization

While testing WebAPI from localhost (With Visual Studio by hitting F5 or Ctrl + F5) I am always getting this error {“Message”:”Authorization has been denied for this request.“}. However, if I publish this same website in IIS then I am getting a valid response as below with no extra work required.

{“status”:200,”isAuthenticated”:true,”isLibraryAdmin”:false,”username”:”admin”}

Here, username (admin it can be any user) is logged in user in windows system (or Virtual Machine).

Stack Overflow Question – https://stackoverflow.com/questions/24194941/windows-authentication-not-working-in-iis-express-debugging-with-visual-studio

When we run or debug the application from Visual Studio it is not hosted in IIS, instead it hosts in IISExpress which is part of the Visual Studio and stores the minimum required configuration or default configuration to run any application.

So, based on the provided solution from above Stack overflow question I have enabled windows authentication (<windowsAuthentication enabled=”true”>) in “applicationhost.config” file which resides at Project root directory “.vs\config”, this folder is hidden you must enable the show all hidden files and folder option.

By Default, Windows authentication value is false in “applicationhost.config”

Now, we have successfully enabled Windows authentication in WebAPI Project.

Step 3

As per the prerequisite enable CORS at controller level along with SupportCredentials true,

As per screenshot, enable CORS with the provided configuration. Here Instead of “*“(Allow from any origin), you can restrict with specific IP Address or domain name.

Step 4

CORS is enabled from the server side. Now while requesting API, pass flag withCredentials: true from the frontend.

For jQuery Ajax you must pass the request as below.

$.ajax({url:apiURL ,xhrFields: { withCredentials: true }, success:successHandler });

For Angular you must pass the request as below.

private options = new RequestOptions({ withCredentials: true });
this.http.get(this.baseUrl, this.options)

Code Snippet

<!DOCTYPE html>
<html>
<head>
“https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js”>
$(document).ready(function(){
$(“button”).click(function(){
// IIS localhost but with host domain name, with support credential true in Enable CORS
//var apiURL=”http://libraryapi.com/api/authenticateuser”;
// IIS Localhost with support credential true in Enable CORS
//var apiURL=”http://localhost:3005/api/AuthenticateUser”;
//IIS Express with support credential true in Enable CORS
var apiURL=“http://localhost:51647/api/AuthenticateUser”;
$.ajax({url:apiURL ,xhrFields: {
withCredentials: true
}, success: function(result){
console.log(JSON.stringify(result));
document.write(JSON.stringify(result));
$(“#div1”).html(JSON.stringify(result));
}});
});
});
</head>
<body>
“div1”>

Let jQuery AJAX to Access Web API Response with Windows Authentication
<button> Click Me to Get Web API Response</button>
</body>
</html>

Now, we have successfully consumed Windows Enabled Authenticated WebAPI .

In case of accessing WebAPI, the dialog for login and password prompts infinitely for the correct password. Then you need to disable the loopback check by executing PowerShell command

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name “DisableLoopbackCheck” -value “1” -PropertyType dword

Reference link

Conclusion

In this article, we learned about how to enable Windows Authentication in Web API and Consume Secure web API from jQuery Ajax and Angular App.

	Kevin on SSIS DTS + MVC API
	Warren LaFrance on SSIS DTS + MVC API
	Warren LaFrance on SSIS DTS + MVC API
	search on SSIS DTS + MVC API
	kevin on SSIS DTS + MVC API

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

WARNING – YOU WILL EXPOSE YOUR COMPUTER TO THE WORLD…

Simplest option ever: local tunnel

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Original Article: https://www.c-sharpcorner.com/article/enable-windows-authentication-in-web-api-and-angular-app/

Re-posted in case site goes away

Let jQuery AJAX to Access Web API Response with Windows Authentication

Share this:

Like this: