Access API with basic Authorization

 C# Code To access API using basic authorization.

using (HttpClient client = new HttpClient())

            {

                string TransactionID = "6334360607";

                string url = $"http://103.11.137.17:8081/BillPayGW/BillInquiryService?shortcode=555&userid=dbbl&password=dbbl&txnid={ TransactionID }";

                Uri baseUri = new Uri(url);

                //This is the key section  UserName Password    

                var plainTextBytes = System.Text.Encoding.UTF8.GetBytes("dbill:dBILL!23");

                 

                string base64EncodedAuthenticationString = System.Convert.ToBase64String(plainTextBytes);

                

                var requestMessage = new HttpRequestMessage(HttpMethod.Get, baseUri);

                requestMessage.Headers.Authorization = new AuthenticationHeaderValue("Basic", base64EncodedAuthenticationString);

                requestMessage.Headers.Add("responseType", "json");

                 

                //make the request

                var task = client.SendAsync(requestMessage);

                var response = task.Result;

                response.EnsureSuccessStatusCode();

                string responseBody = response.Content.ReadAsStringAsync().Result;

            }

Postman setting: 

If Column not exist then create

 --SELECT ETinNo FROM Employee

IF NOT EXISTS (

  SELECT * 

  FROM   sys.columns 

  WHERE  object_id = OBJECT_ID(N'[dbo].[Employee]') 

         AND name = 'ETinNo'

)

BEGIN

ALTER TABLE Employee

ADD ETinNo varchar(20);

SELECT 1

END

Kill Job

 SET NOCOUNT ON

-- Table variable to hold InputBuffer data

DECLARE @Inputbuffer TABLE

    (

      EventType NVARCHAR(30) NULL,

      Parameters INT NULL,

      EventInfo NVARCHAR(4000) NULL

    )

-- Table variable to hold running processes information

DECLARE @BusyProcess TABLE

    (

      SPID INT,

      Status VARCHAR(100),

      Login VARCHAR(100),

      HostName VARCHAR(100),

      DBName VARCHAR(100),

      Command VARCHAR(200),

      CPUTime INT,

      DiskIO INT,

      LastBatch DATETIME,

      ProgramName VARCHAR(200),

      EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text

      EventTime INT   -- time in minutes, a process is running

    )

-- Insert all running processes information to table variable

INSERT  @BusyProcess

        ( SPID, Status, Login, HostName,  DBName, Command, CPUTime,

          DiskIO, LastBatch, ProgramName )

    

        SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name 

        FROM SYS.SYSPROCESSES

        WHERE 

1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1

--Transactions that are open not yet committed or rolledback

WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 

ELSE 0 END

AND cmd NOT LIKE 'BACKUP%' and DB_NAME(dbid)='gHRM'

            

-- Cursor to add actuall Procedure or Batch statement for each process

DECLARE cur_BusyProcess Cursor

    FOR SELECT  SPID

        FROM    @BusyProcess

OPEN cur_BusyProcess 

DECLARE @SPID INT     

Fetch NEXT FROM cur_BusyProcess INTO @SPID

While ( @@FETCH_STATUS <> -1 )

    BEGIN

        INSERT  @Inputbuffer

                EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'

                    ) 

        UPDATE  @BusyProcess

        SET     EventInfo = I.EventInfo,

                EventTime = DATEDIFF(MI,LastBatch,GETDATE())

        FROM    @BusyProcess b

                CROSS JOIN @Inputbuffer i

        WHERE   B.SPID = @SPID

        DELETE  FROM @Inputbuffer

        FETCH NEXT FROM cur_BusyProcess INTO @SPID

    END

CLOSE cur_BusyProcess

DEALLOCATE cur_BusyProcess

-- Create html mail 

IF EXISTS(SELECT 1

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3

)

BEGIN

Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)

Set NoCount On;

DECLARE @QKILLsp VARCHAR(1000)

SET @QKILLsp= (SELECT DISTINCT '  KILL '+ CONVERT(VARCHAR,SPID)

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3 -- Transactions Running for 3 minutes or more

for XML path('')

)

EXEC(@QKILLsp) 

END

SQL Function Lead To Get the Next Row Value

SELECT OfficeId, JoiningDate, ReleaseDate FROM trns.EmployeeTransfer WHERE EmployeeId = 521

Order By JoiningDate

------------------------------------------------------------------------------------------------------------------

SELECT OfficeId, JoiningDate, ReleaseDate  ,

isNUll(    

 LEAD(ISNULL(OfficeId,0)) OVER (ORDER BY JoiningDate)     

 , isNull(OfficeId, 0)     

 ) AS NextOfficeId     

FROM trns.EmployeeTransfer

WHERE EmployeeId = 521

Access web config.

 Get value from web config  <appSettings>.

  <add key="adminuser" value="Administrator" />

    <add key="adminpassword" value="GC@gcomm123456" />

In Controller write:

   string adminuser = WebConfigurationManager.AppSettings["adminuser"];

  string adminpassword = WebConfigurationManager.AppSettings["adminpassword"];

Use this reference: using System.Web.Configuration;

Optional Parameter in Stored Procedure

 ALTER Procedure [dbo].[SetEmploymentDetails]

(

   @LoanSummaryID    AS bigint

      ,@OfficeId    AS int

      ,@MemberId    AS bigint

      ,@CenterId    AS int

      ,@txtMaleFullTimeP1    AS int

      ,@txtFeMaleFullTimeP1    AS int

      ,@txtMalePartTimeP1    AS int

      ,@txtFeMalePartTimeP1    AS int

      ,@txtMaleFullTimeP2    AS int = 0  -- Optional pararameter

      ,@txtFeMaleFullTimeP2    AS int = 0 -- Optional Parameter

      ,@txtMalePartTimeP2    AS int = 0 -- Optional Parameter

      ,@txtFeMalePartTimeP2    AS int = 0 -- Optional Parameter

      ,@txtMaleFullTimeP3    AS int

      ,@txtFeMaleFullTimeP3    AS int

      ,@txtMalePartTimeP3    AS int

      ,@txtFeMalePartTimeP3    AS int

      ,@isActive    AS bit  = 1

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--if Not Exist then Insert.

IF EXISTS

    (

    SELECT 1

    FROM EmploymentDetails

    WHERE [LoanSummaryID] = @LoanSummaryID

    )

    begin

        --RAISERROR('Timesheets have already been posted!', 16, 1)

      

  Select 1

  

  Update EmploymentDetails 

  SET

   [SEmpMaleFullTimeP1] =  @txtMaleFullTimeP1 ,

   [SEmpFeMaleFullTimeP1] =  @txtFeMaleFullTimeP1 ,

   [SEmpMalePartTimeP1] =  @txtMalePartTimeP1 ,

   [SEmpFeMalePartTimeP1] =  @txtFeMalePartTimeP1 ,

   --[FEmpMaleFullTimeP2] =  @txtMaleFullTimeP2 ,

   --[FEmpFeMaleFullTimeP2] =  @txtFeMaleFullTimeP2 ,

   --[FEmpMalePartTimeP2] =  @txtMalePartTimeP2 ,

   --[FEmpFeMalePartTimeP2] =  @txtFeMalePartTimeP2 ,

   [WEmpMaleFullTimeP3] =  @txtMaleFullTimeP3 ,

   [WEmpFeMaleFullTimeP3] =  @txtFeMaleFullTimeP3 ,

   [WEmpMalePartTimeP3] =  @txtMalePartTimeP3 ,

   [WEmpFeMalePartTimeP3] =  @txtFeMalePartTimeP3

WHERE [LoanSummaryID] = @LoanSummaryID

   

    end

ELSE

begin

INSERT INTO EmploymentDetails 

(

   [LoanSummaryID]

      ,[OfficeId]

      ,[MemberId]

      ,[CenterId]

      ,[SEmpMaleFullTimeP1]

      ,[SEmpFeMaleFullTimeP1]

      ,[SEmpMalePartTimeP1]

      ,[SEmpFeMalePartTimeP1]

      ,[FEmpMaleFullTimeP2]

      ,[FEmpFeMaleFullTimeP2]

      ,[FEmpMalePartTimeP2]

      ,[FEmpFeMalePartTimeP2]

      ,[WEmpMaleFullTimeP3]

      ,[WEmpFeMaleFullTimeP3]

      ,[WEmpMalePartTimeP3]

      ,[WEmpFeMalePartTimeP3]

      ,[isActive]

   

)

values

(

   @LoanSummaryID

      ,@OfficeId

      ,@MemberId

      ,@CenterId

      ,@txtMaleFullTimeP1

      ,@txtFeMaleFullTimeP1

      ,@txtMalePartTimeP1

      ,@txtFeMalePartTimeP1

      ,@txtMaleFullTimeP2

      ,@txtFeMaleFullTimeP2

      ,@txtMalePartTimeP2

      ,@txtFeMalePartTimeP2

      ,@txtMaleFullTimeP3

      ,@txtFeMaleFullTimeP3

      ,@txtMalePartTimeP3

      ,@txtFeMalePartTimeP3

      ,@isActive

 

)

end -- end of else

END

Server-side model to javascript object

 Server-side model to javascript object

var model = @Html.Raw(Json.Encode(Model));

var model = @Html.Raw(Json.Encode(Model.IsApproved));

Killjob:: Stored Procedure: Process SQL server.

 SET NOCOUNT ON

-- Table variable to hold InputBuffer data

DECLARE @Inputbuffer TABLE

    (

      EventType NVARCHAR(30) NULL,

      Parameters INT NULL,

      EventInfo NVARCHAR(4000) NULL

    )

-- Table variable to hold running processes information

DECLARE @BusyProcess TABLE

    (

      SPID INT,

      Status VARCHAR(100),

      Login VARCHAR(100),

      HostName VARCHAR(100),

      DBName VARCHAR(100),

      Command VARCHAR(200),

      CPUTime INT,

      DiskIO INT,

      LastBatch DATETIME,

      ProgramName VARCHAR(200),

      EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text

      EventTime INT   -- time in minutes, a process is running

    )

-- Insert all running processes information to table variable

INSERT  @BusyProcess

        ( SPID, Status, Login, HostName,  DBName, Command, CPUTime,

          DiskIO, LastBatch, ProgramName )

    

        SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name 

        FROM SYS.SYSPROCESSES

        WHERE 

1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1

--Transactions that are open not yet committed or rolledback

WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 

ELSE 0 END

AND cmd NOT LIKE 'BACKUP%' and DB_NAME(dbid)='gHRM'

            

-- Cursor to add actuall Procedure or Batch statement for each process

DECLARE cur_BusyProcess Cursor

    FOR SELECT  SPID

        FROM    @BusyProcess

OPEN cur_BusyProcess 

DECLARE @SPID INT     

Fetch NEXT FROM cur_BusyProcess INTO @SPID

While ( @@FETCH_STATUS <> -1 )

    BEGIN

        INSERT  @Inputbuffer

                EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'

                    ) 

        UPDATE  @BusyProcess

        SET     EventInfo = I.EventInfo,

                EventTime = DATEDIFF(MI,LastBatch,GETDATE())

        FROM    @BusyProcess b

                CROSS JOIN @Inputbuffer i

        WHERE   B.SPID = @SPID

        DELETE  FROM @Inputbuffer

        FETCH NEXT FROM cur_BusyProcess INTO @SPID

    END

CLOSE cur_BusyProcess

DEALLOCATE cur_BusyProcess

-- Create html mail 

IF EXISTS(SELECT 1

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3

)

BEGIN

Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)

Set NoCount On;

DECLARE @QKILLsp VARCHAR(1000)

SET @QKILLsp= (SELECT DISTINCT '  KILL '+ CONVERT(VARCHAR,SPID)

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3 -- Transactions Running for 3 minutes or more

for XML path('')

)

EXEC(@QKILLsp) 

END

react

 React is a library to create user interfaces in a web application. React's primary purpose is to enable the developer to create user interfaces using pure JavaScript.   reactjs_tutorial.pdf

Array.Find() Return first bigger NUMBER

<!DOCTYPE html>

<html>

<body>

<h2>JavaScript Array.find()</h2>

<p id="demo"></p>

<script>

const numbers = [4, 9, 16, 25, 29];

let first = numbers.find(myFunction);

document.getElementById("demo").innerHTML = "First number over 18 is " + first;

function myFunction(value, index, array) {

  return value > 18;

}

</script>

</body>

</html>

Git Commands

 First Time ------

git add .

git commit -m "first commit"

git remote add origin https://github.com/khalidah/ReactTest.git

Always -----

git add .

git commit -m "first commit"

Percentage Calculation

 Mathmatics

stackblitz Dev Environment in Miliseconds

 https://stackblitz.com/

Offset and fetch in SQL

 

Precision and Scale

 

Use Case in SQL

 select @OfficeCode=OfficeCode   from Office where OfficeID= @OfficeID

SELECT OfficeId FROM Office

WHERE (CASE WHEN @OfficeLevel=1 THEN FirstLevel

WHEN @OfficeLevel=2 THEN SecondLevel

WHEN @OfficeLevel=3 THEN ThirdLevel

WHEN @OfficeLevel=4 THEN FourthLevel

END) = @OfficeCode

Get a Table from Comma separated value SQL

 Create a function :

CREATE FUNCTION split_string

(

    @in_string VARCHAR(MAX),

    @delimeter VARCHAR(1)

)

RETURNS @list TABLE(tuple VARCHAR(100))

AS

BEGIN

        WHILE LEN(@in_string) > 0

        BEGIN

            INSERT INTO @list(tuple)

            SELECT left(@in_string, charindex(@delimiter, @in_string+',') -1) as tuple

    

            SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), '')

        end

    RETURN 

END

Calling a function: SELECT * FROM split_string('1001,1002,1003,1004', ',')

Table Descriptions

 select * 

  from information_schema.columns 

 where table_name = 'SMSParking'

 order by ordinal_position

EXEC sp_help SMSParking

File Size Validations

 jQuery File size validations. Arrow functions for file size validations.

    Filevalidation = () => {

        document.getElementById('size').innerHTML = '';

        let fi = document.getElementById('files');

        // Check if any file is selected.

        if (fi.files.length > 0) {

            for (const i = 0; i <= fi.files.length - 1; i++) {

                const fsize = fi.files.item(i).size;

                const file = Math.round((fsize / 1024));

                // The size of the file.

                if (file >= 4096) {

                    alert(

                        "File too Big, please select a file less than 4mb");

                    document.getElementById('files').value = '';

                } else if (file <= 1 ) {

                    alert(

                        "File too small, please select a file greater than 1KB");

                } else {

                    document.getElementById('size').innerHTML = '<b>'

                        + file + '</b> KB';

                }

            }

        }

    }

HTML:    <input type="file" name="files" id="files" multiple="multiple" onchange="Filevalidation()" />

ReactJS

 ReactJS Tutorial - 1 - Introduction - YouTube

Different Ogranaization

 Phone No used to register in Multiple organizations.

SELECT distinct l.PhoneNo FROM MemberPortal_Bulk l

INNER JOIN(

SELECT OrgID, PhoneNo,count(*) Noof 

FROM MemberPortal_Bulk group by OrgID, PhoneNo

having count(*)>1) noof on l.PhoneNo=noof.PhoneNo

where l.OrgID!=noof.OrgID

express js

 express js 

Passport.js

Git Commands

 

Add a new file in git local repo as stagged

#########################################

$ git add.

Check stagged status

####################

$ git status --short

Committing changes

#################

$ git commit -a -m "YOUR_COMMENT"

Pull/Update/Latest to git repo

##############################

$ git pull

Push to git repo

#################

$ git push

Scheduler using worker Service

 Dot Net CORE Worker Services. 

.NET Core 3.0 - The New Way to Create Services

Download Source Code

Video

Bulk insert in a table From Different Server C# code

 C# Code for data collection from different servers.

it will get a list of server credentials from a table. This table contains the source server login credentials.

in c# asp.net there will be a foreach loop and it will execute the operations.

 foreach (var v in List_Members)

                {

                     

                    string connectionString = @"Data Source=" + v.ServerIP + ";Initial Catalog=" + v.DatabaseName + ";User ID=" + v.User + ";Password=" + v.Password  ;

 

                    var ServerIP = @"192.192.192.188\MSSQLSERVER2016";

                    var DatabaseName = "gBankerSMSDb";

                    var User = "gBanker6";

                    var Password = "gBanker6";

                    string csDest = @"Data Source=" + ServerIP + ";Initial Catalog=" + DatabaseName + ";User ID=" + User + ";Password=" + Password;

                    // Create source connection

                    SqlConnection source = new SqlConnection(connectionString);

                    // Create destination connection

                    SqlConnection destination = new SqlConnection(csDest);

                    // Clean up destination table. Your destination database must have the

                    // table with schema which you are copying data to.

                    // Before executing this code, you must create a table BulkDataTable

                    // in your database where you are trying to copy data to.

                    //SqlCommand cmd = new SqlCommand("DELETE FROM MemberPortal_Bulk", destination);

                    SqlCommand cmd = new SqlCommand("SELECT 1 ", destination);

                    // Open source and destination connections.

                    source.Open();

                    destination.Open();

                    cmd.ExecuteNonQuery();

                    // Select data from Products table

                    cmd = new SqlCommand(@"SELECT TOP 5000 * FROM MemberPortal", source);

                    // Execute reader

                    SqlDataReader reader = cmd.ExecuteReader();

                    // Create SqlBulkCopy

                    SqlBulkCopy bulkData = new SqlBulkCopy(destination);

                    // Set destination table name

                    bulkData.DestinationTableName = "MemberPortal_Bulk";

                    // Write data

                    bulkData.WriteToServer(reader);

                    // Close objects

                    bulkData.Close();

                    destination.Close();

                    source.Close();

                }

This code will keep adding data in 192.192.192.188\MSSQLSERVER2016 server. Keep adding data in MemberPortal_Bulk Table database gBankerSMSDb

Access in Different Database

 Collect Data from different databases. 

-- use gBankerSMSDb

DECLARE @Counter INT , @MaxId INT

Declare @db table(DataBaseID int,DBNAME varchar(50))

Insert into @db

SELECT dbid  DataBaseID,DB_NAME(dbid) DBNAME

FROM

    sys.sysprocesses

WHERE 

    dbid > 0

and DB_NAME(dbid) not in('master','msdb','tempdb','model','distribution')

and left(DB_NAME(dbid),1)<>'R'

group by dbid;

SELECT  @Counter = min(DataBaseID) , @MaxId = max(DataBaseID) 

FROM @db

WHILE(@Counter IS NOT NULL

      AND @Counter <= @MaxId)

BEGIn

Declare @vDataBaseName varchar(60), @MaxMemberId bigint;

select @vDataBaseName=DBNAME from @db where DataBaseID=@Counter

 exec ('insert into MemberPortal([MemberID], [MemberCode], [MemberName], [RefereeName], [PhoneNo], [SmartCard], [OtherID], [NationalID], [OrgID], [UnionCode], [BirthDate], [CreateUser], [CreateDate], [DBID]

)

select [MemberID], [MemberCode],FirstName [MemberName], [RefereeName], [PhoneNo], [SmartCard],OtherIdNo [OtherID], [NationalID], [OrgID], [UnionCode], [BirthDate], [CreateUser], [CreateDate],'+@Counter+' [DBID]

 from '+ @vDataBaseName +'.dbo.member'

 )

 exec ('SELECT'+ @MaxMemberId + '= MAX(MemberId) FROM'+  @vDataBaseName +'.dbo.member')

 IF Exists( SELECT 1 FROM MemberPortalMaxID WHERE OrgName = @vDataBaseName )

 BEGIN

UPDATE MemberPortalMaxID SET MaximumMemberId = @MaxMemberId WHERE OrgName = @vDataBaseName

 END 

 ELSE

 BEGIN

INSERT INTO MemberPortalMaxID 

( [MaximumMemberId]

      ,[OrgName]

      ,[isActive]

      ,[UpdateDate]

  ) VALUES (@MaxMemberId, @vDataBaseName, 1, GETDATE());

 END

SET @Counter  = @Counter  + 1        

END

--select * from DemographicRegion

None of the constructors found with 'Autofac.Core.Activators.Reflection.DefaultConstructorFinder'

 

Need to register forcefully. In App_Start folder file Bootstrapper register the new created service forcefully.

Add this line.

  builder.RegisterType<UltimateReportServiceMemberPortal>().As<IUltimateReportServiceMemberPortal>().InstancePerRequest();

 

Site Not Reach

 

Localhost Site not reached if web application runs.

Solution: Go to C:\Windows\System32\drivers\etc 

Open 'host' File

Add  '127.0.0.1 http://localhost:54499/'

Age Duration Calculator SQL

 Function IN SQL:

        

-        

--SELECT [dbo].[GetYearDuration] (REPLACE(CONVERT(Date,e.DateOfBirth,103),' ','-')  , REPLACE(CONVERT(Date,e.FirstJoiningDate,103),' ','-') ) as JoiningAge          

          

--FROM Employee e          

--WHERE e.EmployeeId = 3734       

  

--SELECT [dbo].[GetDurationYear_Month_Day]('06 June, 1985', '04 July, 2020')   

--SELECT [dbo].[GetDurationYear_Month_Day]('01 April, 1960', '26 March, 1986')   

     

          

ALTER FUNCTION [dbo].[GetDurationYear_Month_Day]           

(          

   @FromDate1 nvarchar(20), @ToDate1 nvarchar(20)          

)          

RETURNS NVARCHAR(300)          

AS          

BEGIN      

   Declare @Message NVARCHAR(300) 

 

if isDate(@FromDate1) = 1 and isDate(@ToDate1) = 1

BEGIN

Declare @FromDate DATETIME, @ToDate DATETIME 

SET @FromDate = @FromDate1

SET @ToDate = @ToDate1

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int

Declare @currentdatetime datetime  

--SELECT @date = '15-Jun-1986'

set @date = @FromDate

  

set @currentdatetime = @ToDate 

 --SELECT

 -- @date = REPLACE(CONVERT(Date,e.DateOfBirth,103),' ','-'),

  

 -- @currentdatetime =  REPLACE(CONVERT(Date,e.FirstJoiningDate,103),' ','-')                                  

 --  FROM Employee as e WHERE e.EmployeeId = 26380 --12390 

   

    

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, @currentdatetime) - CASE WHEN (MONTH(@date) > MONTH(@currentdatetime)) OR (MONTH(@date) = MONTH(@currentdatetime) AND DAY(@date) > DAY(@currentdatetime)) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, @currentdatetime) - CASE WHEN DAY(@date) > DAY(@currentdatetime) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, @currentdatetime)

 

SET @Message =  CAST(@years AS VARCHAR(4)) + '-' + CAST(@months AS VARCHAR(4)) + '-' + CAST(@days AS VARCHAR(4)) 

END -- END if

ELSE

BEGIN

SET @Message = 'Problem With Date:GetDurationYear_Month_Day'

END

RETURN @Message;

END 

Age Calculator Javascript

Javascript Code:     

                        let EPOCH = new Date(0);

                        let EPOCH_YEAR = EPOCH.getUTCFullYear();

                        let EPOCH_MONTH = EPOCH.getUTCMonth();

                        let EPOCH_DAY = EPOCH.getUTCDate();

                        var dob = document.getElementById("BirthDate").value;

                        var birthDate = new Date(dob);

                        const diff = new Date(Date.now() - birthDate.getTime());

                        var years = Math.abs(diff.getUTCFullYear() - EPOCH_YEAR);

                        var months = Math.abs(diff.getUTCMonth() - EPOCH_MONTH);

                        var days = Math.abs(diff.getUTCDate() - EPOCH_DAY);

                        $("#Result").html(years + " year(s) " + months + " month(s) " + days + " and day(s)").css("color", "red");;

HTML::: 

 <div class="col-md-3">

                <div class="form-group">

                    @Html.LabelFor(model => model.BirthDate, htmlAttributes: new { @class = "control-label", @autocomplete = "off" })<span style="color:red; font-size:15px;"> * </span>

                    <div style="margin:.1%;" id="Result"> </div>

                    @Html.EditorFor(model => model.BirthDate, new { htmlAttributes = new { @class = "form-control" } })

                    @Html.ValidationMessageFor(model => model.BirthDate, "", new { @class = "text-danger" })

                </div>

            </div>

Reference: https://dev.to/code_mystery/javascript-age-calculator-calculate-age-from-date-of-birth-o9b

Update Employee Table

  

---   

declare 

  @InitialData Table(    

    RowNum   int  IDENTITY(1,1) NOT NULL , 

  EmployeeCode  nvarchar(20)

     )    

INSERT INTO @InitialData

SELECT [Employee Code] FROM NXTRPTEmployeeProfile WHERE  [daysIN Zone Office] is NULL

  

  DECLARE @RowSl INT    

     ,@Count INT    

 

SET @RowSl = 1    

SET @Count = (SELECT COUNT(*) FROM @InitialData)    

 

WHILE @RowSl <= @Count    

BEGIN 

declare @currentEmployee varchar(20)

SELECT @currentEmployee = [EmployeeCode] FROM @InitialData WHERE RowNum = @RowSl

Update NXTRPTEmployeeProfile SET [daysIN Zone Office] = dbo.GetZoneOfficeDurationKF1Days(@currentEmployee)

WHERE [Employee code] = @currentEmployee

SET @RowSl += 1    

END -- End While    

Canvas to JPEG for Crystal Report C# MVC

 Canvas always returns images in png format. But for crystal report, it does not accept png. So it is necessary to convert png to jpeg image format.

Steps:

1. From base64 get byte array.  (png)

2. then convert to a logical file. (png)

3. get the logical file and convert it to a jpeg file (bitmap png to jpeg)

4. get a jpeg logical file and convert it to the binary array.

5. save in the database. Field Type varbinary(max)

View::

@model gBanker.Web.ViewModels.MemberInfoViewModel

@{

    ViewBag.Title = "DigitalSignature";

    Layout = "~/Views/Shared/_LayoutMaster.cshtml";

}

<h2>Add Digital Signature</h2>

@using (Html.BeginForm("DigitalSignature", "newMember", FormMethod.Post, new { id = "drawingForm" }))

{

    @Html.HiddenFor(model => model.MemberID)

<div class="row">

    <div class="col-md-4">

        <div class="form-group">

            @Html.LabelFor(model => model.CenterID, htmlAttributes: new { @class = "control-label" })

            @Html.TextBox(" ", ViewData["CenterID"].ToString(), new { @ID = "CenterID", @class = "form-control", @readonly = true })

            @Html.ValidationMessageFor(model => model.CenterID, "", new { @class = "text-danger" })

        </div>

    </div>

    <div class="col-md-4">

        <div class="form-group">

            @Html.LabelFor(model => model.MemberCode, htmlAttributes: new { @class = "control-label" })

            @Html.TextBox(" ", ViewData["MemberCode"].ToString(), new { @ID = "MemberCode", @class = "form-control", @readonly = true })

            @Html.ValidationMessageFor(model => model.MemberCode, "", new { @class = "text-danger" })

        </div>

    </div>

     

</div>

<div class="row">

    <div class="col-md-5">

        <input type="hidden" name="imageData" id="imageData" />

        <input type="hidden" MemberId_Sig="@ViewData["MemberID"]" />

        <canvas id="kfCanvas" name="kfCanvas" typeof="jpg" height="300px;" width="400px;" style="border: 3px dotted #000;  cursor: crosshair;">

            Sorry, your browser doesn't support canvas technology.

        </canvas>

    </div>

    <div class="col-md-3">

        <input type="submit" id="btnSave" class="btn btn-success" onfocus="SetBase64();" value="Save Signature" />

        <input type="button" id="btnClear" class="btn btn-warning" value="Clear" onclick="erase();" />

    </div>

    </div>

<div class="row">

    

    <div class="col-md-3"><h4>4 of 4 pages</h4><label>@ViewBag.Message</label></div>

    <div class="col-md-3">

        <div class="form-group">

        </div>

    </div>

</div>

    <div class="row">

        <div style="height:350px; margin:10px;">

            <img id="rsDiagram" src="" class="partDiagram">

        </div>

        <!-- Button for calling ajax request -->

        <button type="button" class="btn btn-primary" onclick="ShowImage();" data-toggle="modal" data-target="">

            Load Signature

        </button>

    </div>

    <img id="rsDiagram2" src="" class="partDiagram">

    }

    <script>

        var canvas, ctx, flag = false,

            prevX = 0,

            currX = 0,

            prevY = 0,

            currY = 0,

            dot_flag = false;

        var x = "black",

            y = 2.5;

        function initSignatureInput() {

            canvas = document.getElementById('kfCanvas');

            ctx = canvas.getContext("2d");

            w = canvas.width;

            h = canvas.height;

            canvas.addEventListener("mousemove", function (e) {

                findxy('move', e)

            }, false);

            canvas.addEventListener("mousedown", function (e) {

                findxy('down', e)

            }, false);

            canvas.addEventListener("mouseup", function (e) {

                findxy('up', e)

            }, false);

            canvas.addEventListener("mouseout", function (e) {

                findxy('out', e)

            }, false);

        }

        function color(obj) {

            switch (obj.id) {

                case "green":

                    x = "green";

                    break;

                case "blue":

                    x = "blue";

                    break;

                case "red":

                    x = "red";

                    break;

                case "yellow":

                    x = "yellow";

                    break;

                case "orange":

                    x = "orange";

                    break;

                case "black":

                    x = "black";

                    break;

                case "white":

                    x = "white";

                    break;

            }

            if (x == "white") y = 14;

            else y = 2.5;

        }

        function draw() {

            ctx.beginPath();

            ctx.lineCap = 'round';

            ctx.moveTo(prevX, prevY);

            ctx.lineTo(currX, currY);

            ctx.strokeStyle = x;

            ctx.lineWidth = y;

            ctx.stroke();

            ctx.closePath();

        }

        function erase() {

            var m = confirm("Want to clear");

            if (m) {

                ctx.clearRect(0, 0, w, h);

            }

        }

        //function draw2() {

        //    ctx.beginPath();

        //    ctx.moveTo(prevX, prevY);

        //    ctx.lineTo(currX, currY);

        //    ctx.strokeStyle = x;

        //    ctx.lineWidth = y;

        //    ctx.stroke();

        //    ctx.closePath();

        //}

        function findxy(res, e) {

            // var staticX = 33, staticY = 13;

            var staticX = 30, staticY = 120;

            if (res == 'down') {

                prevX = currX;

                prevY = currY;

                //currX = e.clientX - (canvas.offsetLeft+235);

                //currY = e.clientY - (canvas.offsetTop +135);

                currX = (e.pageX - canvas.offsetLeft) - canvas.width / 2;

                currY = (e.pageY - canvas.offsetTop) - canvas.height / 2;

                currX -= staticX;

                currY -= staticY;

                flag = true;

            }

            if (res == 'up' || res == "out") {

                flag = false;

            }

            if (res == 'move') {

                if (flag) {

                    prevX = currX;

                    prevY = currY;

                    // currX = e.clientX - (canvas.offsetLeft + 235);

                    // currY = e.clientY - (canvas.offsetTop + 135);

                    currX = (e.pageX - canvas.offsetLeft) - canvas.width / 2;

                    currY = (e.pageY - canvas.offsetTop) - canvas.height / 2;

                    currX -= staticX;

                    currY -= staticY;

                    draw();

                }

            }

        }

        initSignatureInput();

        function SetBase64() {

            var image = document.getElementById("kfCanvas").toDataURL("image/png");

            //var image = document.getElementById("kfCanvas").toDataURL();

            // image = image.replace('data:image/png;base64,', '');

            //image = image.replace('data:image/png;base64,', '');

            document.getElementById('rsDiagram2').src = image;

            //var rsDiagram = $("#rsDiagram").attr('src');

            $('#imageData').val(image);

        }

        function ShowImage() {

            //alert($('#MemberID').val());

            $.ajax({

                type: "GET",

                url: "/NewMember/RSView/",

                data: { MemberID: $('#MemberID').val() },

                success: function (response) {

                    // console.log(response);

                    // alert(response);

                    $("#rsDiagram").attr('src', 'data:image/png;base64,' + response);

                },

                error: function (response) {

                    alert(response.responseText);

                }

            });

        }// END Show Image

        $(document).ready(function () {

        });// END document Ready

    </script>

IN Controller: 

        [HttpPost]

        public ActionResult DigitalSignature()

        {

            string Message_ = "Signature Saved.";

            MemberInfoViewModel obj = new MemberInfoViewModel();

            try

            {

                NameValueCollection nvc = Request.Form;

                string memberIDs = nvc["MemberID"];

                var imageDatas = nvc["imageData"];

                var member = memberService.GetByIdLong(Convert.ToInt32(memberIDs));

                ViewData["CenterID"] = centerService.GetById(member.CenterID).CenterName;

                ViewData["MemberID"] = member.MemberID;

                ViewData["MemberCode"] = member.MemberCode;

                imageDatas = imageDatas.Substring(22);

                byte[] data = Convert.FromBase64String(imageDatas);

                // test: Save image 

                string fileName = Guid.NewGuid().ToString() + ".png";

                byte[] imageBytes = data;

                MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);

                ms.Write(imageBytes, 0, imageBytes.Length);

                System.Drawing.Image image = System.Drawing.Image.FromStream(ms, true);

                image.Save(Server.MapPath("~/CapturedImages/" + fileName),

                    System.Drawing.Imaging.ImageFormat.Png);

                //prphoto.ImageName = "/Uploads/" + fileName;

                // END Save Image

                // Get Image and Convert to jpg

                // Assumes myImage is the PNG you are converting

                string imageName = Server.MapPath("~/CapturedImages/" + fileName);//@"~/CapturedImages/" + fileName;

                //var ifExist = System.IO.File.Exists(imageName);

                var imageFile = System.IO.File.Open(imageName, FileMode.Open);

                Image myImage = Image.FromStream(imageFile);

                //myImage.Save(Server.MapPath("~/CapturedImages/" + fileName),

                //    System.Drawing.Imaging.ImageFormat.Jpeg);

                string fileNameJPEG = "";

                using (var b = new Bitmap(myImage.Width, myImage.Height))

                {

                    b.SetResolution(myImage.HorizontalResolution, myImage.VerticalResolution);

                    using (var g = Graphics.FromImage(b))

                    {

                        g.Clear(Color.White);

                        g.DrawImageUnscaled(myImage, 0, 0);

                    }

                    myImage.Dispose();

                    imageFile.Close();

                    fileNameJPEG = Guid.NewGuid().ToString() + ".jpeg";

                    b.Save(Server.MapPath("~/CapturedImages/" + fileNameJPEG),

                    System.Drawing.Imaging.ImageFormat.Jpeg);

                }

                // END jpg

                // Convert jpeg to byte Array and save

                string imageNameJPEG = Server.MapPath("~/CapturedImages/" + fileNameJPEG);//@"~/CapturedImages/" + fileName;

                //var ifExistJPEG = System.IO.File.Exists(imageNameJPEG);

                var imageFileJPEG = System.IO.File.Open(imageNameJPEG, FileMode.Open);

                var memoryStream = new MemoryStream();

                imageFileJPEG.CopyTo(memoryStream);

                var dataJPEG = memoryStream.ToArray();

                imageFileJPEG.Close();

                var objs = memberService.GetById(Convert.ToInt32(memberIDs));

                objs.DigitalSignature = dataJPEG;

                memberService.Update(objs);

                // Delete png and JPEG file

                System.IO.File.Delete(imageNameJPEG);

                System.IO.File.Delete(imageName);

                //END Delete

                //var objs = memberService.GetById(Convert.ToInt32(memberIDs));

                //objs.DigitalSignature = data;

                //memberService.Update(objs);

            }

            catch (Exception ex)

            {

                return GetErrorMessageResult("Sorry, Please try again.");

            }

            ViewBag.Message = Message_;

            return GetSuccessMessageResult("Signature Added.");

        }

        public ActionResult RSView(long MemberID)

        {

            var member = memberService.GetByIdLong(Convert.ToInt32(MemberID));

            string fileToSend = Convert.ToBase64String(member.DigitalSignature);

            return Json(fileToSend,JsonRequestBehavior.AllowGet);

        }

Use Session For List 2

  var Center_List = string.Format("CenterList_{0}", (int)LoggedInOrganizationID);

                var centerList = new List<DBCenterDetailModel>();

                if (Session[Center_List] != null)

                    centerList = Session[Center_List] as List<DBCenterDetailModel>;

                else

                {

                    var param = new { OrgId = (int)LoggedInOrganizationID, OfficeId =                                                                                         SessionHelper.LoginUserOfficeID};

                    var alldata = ultimateReportService.GetDataWithParameter(param, "GetCenterData");

                     centerList = alldata.Tables[0].AsEnumerable()

                    .Select(row => new DBCenterDetailModel

                    {

                        CenterID = row.Field<int>("CenterID"),

                        CenterCode = row.Field<string>("CenterCode"),

                        OfficeID = row.Field<int>("OfficeID"), 

                        OfficeCode = row.Field<string>("OfficeCode"),

                        OfficeName = row.Field<string>("OfficeName")

                    }).ToList();

                     Session[Center_List] = centerList;

                }

Get Maximum value from a ROW Data

 

-- one time table create

--CREATE TABLE InitialData (    

--    RowNum   int  IDENTITY(1,1) NOT NULL , 

-- loan  decimal (17, 2)

       

--  )    

declare @sql varchar(max);

DECLARE @paramDefinition nvarchar(500);

DECLARE @sid int = 1;

  DECLARE @RowSl INT    

     ,@Count INT    

   

 

SET @RowSl = 0    

SET @Count = 6 -- (SELECT COUNT(*) FROM Tables)    

 

WHILE @RowSl <= @Count    

BEGIN 

Set @sql = 'INSERT INTO  InitialData SELECT ';

declare @ColName varchar(28);

Set @ColName=  'loan_' + Cast( format (@RowSl, '0#')  as varchar(5)) +' ' ; 

SET @sql =  @sql + @ColName ;

SET @sql = @sql + ' FROM PRA_MN_RPT_TAB_XL_PD_Test WHERE RowNum =' + CAST( @sid AS nvarchar(2));

EXECUTE(@sql)

 

SET @RowSl += 1    

END -- End While    

SELECT MAX(loan) FROM  InitialData;

Use Session FOR List

 Keep list of data in session::

 var Center_List = string.Format("CenterList_{0}", (int)LoggedInOrganizationID);

                var centerList = new List<DBCenterDetailModel>();

                if (Session[Center_List] != null)

                    centerList = Session[Center_List] as List<DBCenterDetailModel>;

                else

                {

                    centerList = centerService.GetCenterDetail(LoggedInOrganizationID).ToList();

                    Session[Center_List] = centerList;

                }

Books

https://drive.google.com/drive/folders/1zkfruc32NTZedTcKsxlRoKsBi8sqD_dO?usp=sharing

INFORMATION_SCHEMA.ROUTINES Find Word FROM

 SELECT ROUTINE_NAME, ROUTINE_DEFINITION , ROUTINE_TYPE

FROM INFORMATION_SCHEMA.ROUTINES 

WHERE ROUTINE_DEFINITION LIKE '%INSERT INTO gcpf.Collection%' 

AND ROUTINE_TYPE='PROCEDURE'

ORDER BY ROUTINE_NAME

Find word in all stored procedure.

 SELECT OBJECT_NAME(OBJECT_ID) AS ObjectName,

       definition             AS ObjectDefinition

 FROM   sys.sql_modules 

WHERE  definition LIKE '%Collection%'

Internet Speed

 https://fast.com/ 

Resume

 resume

Delete if Duplicate Rows exist in Table

 

The following statement uses a common table expression (CTE) to delete duplicate rows: 

WITH cte AS (

    SELECT 

   SalaryYear, SalaryMonth, SalaryDate, OfficeID, EmployeeID,

   PRComponentID, PRTranTypeID,

   IsPosted, IsActive,

        ROW_NUMBER() OVER (

            PARTITION BY 

                SalaryYear, SalaryMonth, SalaryDate, OfficeID, EmployeeID,

   PRComponentID, PRTranTypeID,

   IsPosted, IsActive

            ORDER BY 

                SalaryYear, SalaryMonth, SalaryDate, PRSalaryConfigurationID, OfficeID, EmployeeID,

   PRComponentID, ComponentAmount, PRTranTypeID,

   IsPosted, IsActive

        ) row_num

     FROM 

        PRSalaryRegister

WHERE 

   SalaryMonth = 5 AND

    SalaryYear = 2020 AND 

EmployeeId = 46061

)

DELETE FROM cte

WHERE row_num > 1;

--SELECT * INTO PRSalaryRegister_8_February_2022 FROM PRSalaryRegister

Online Entity To Model Converter

 http://tmt.rejawebs.com/