Sunday, February 28, 2021

Date sorting in Kendo UI Grid for dd.MM.yyyy format

Date sorting not working as expected in Kedno grid when format does not follow the order YEAR -> MONTH -> DAY.

It is because dates are treated as strings objects and they are compared as plain text.

One solution is to parse strings to dates and sorting will work in an expected manner.

KendoGridSortedDate.js

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
var orders = [        
    { OrderDate: '10 20 1999' }, 
    { OrderDate: '2/06/2015' }, 
    { OrderDate: '09/09/2112'}
];

$("#singleSort").kendoGrid({
    dataSource: {
        schema: {
            model: {
                fields: {
                    OrderDate: {
                        type: "date",
                        parse: function (e) {
                            return new Date(e)
                        }
                    }
                }
            }
        },
        data: orders
    },
    sortable: true,

    columns: [
        {
            field: "OrderDate",
            title: "Order Date",
            format: "{0:dd.MM.yyyy}"
        }
    ]
});

KendoGridSortedDate.html

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<!DOCTYPE html>
<html>
  <head>
   
    <style>html { font-size: 12px; font-family: Arial, Helvetica, sans-serif; }</style>
    <title></title>
   
    <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.common.min.css" />
    <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.default.min.css" />
    <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.dataviz.min.css" />
    <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.dataviz.default.min.css" />

    <script src="http://cdn.kendostatic.com/2015.1.318/js/jquery.min.js"></script>
    <script src="http://cdn.kendostatic.com/2015.1.318/js/angular.min.js"></script>
    <script src="http://cdn.kendostatic.com/2015.1.318/js/kendo.all.min.js"></script>

  </head>
  <body>
    <div class="demo-section k-header">
      <div id="singleSort"></div>
    </div>

    <script src="KendoGridSortedDate.js"></script>

  </body>
</html>

Source links:

Sorted grid


Saturday, February 27, 2021

Convert a string to a date with javascript method

Change string to date is a frequent action.

Below you can find javascript method which return Date from string.

Function name is stringToDate.

Input parameters are:

  • _date parameter with date in string format
  • _format parameter - use yyyy, dd and MM. Example : yyyy.MM.dd
  • _delimiter - delimiter, for example ".". Can be omitted

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<script>

console.log(stringToDate("01/9/2020","dd/MM/yyyy","/")); // Tue Sep 01 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("9 2020","MM yyyy"," ")); // Tue Sep 01 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("9/17/2020","mm/dd/yyyy","/")); // Thu Sep 17 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("9-17-2020","mm-dd-yyyy","-")); // Thu Sep 17 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("09/2/2020","mm/dd/yyyy","/")); // Wed Sep 02 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("01/9/2020","dd/MM/yyyy")); // Tue Sep 01 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("9 2020","MM yyyy")); // Tue Sep 01 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("9/17/2020","mm/dd/yyyy")); // Thu Sep 17 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("9-17-2020","mm-dd-yyyy")); // Thu Sep 17 2020 00:00:00 GMT+0200 (Central European Summer Time)
console.log(stringToDate("09/2/2020","mm/dd/yyyy")); // Wed Sep 02 2020 00:00:00 GMT+0200 (Central European Summer Time

function stringToDate(_date,_format,_delimiter)
{
            if (!_delimiter)
                _delimiter = _format.match(/\W/g)[0];

            var formatLowerCase=_format.toLowerCase();
            var formatItems=formatLowerCase.split(_delimiter);
            var dateItems=_date.split(_delimiter);
            var monthIndex=formatItems.indexOf("mm");
            var dayIndex=formatItems.indexOf("dd");
            var yearIndex=formatItems.indexOf("yyyy");
            var month=parseInt(dateItems[monthIndex]);
            month-=1;

            var day = 1;
            if (dayIndex >= 0)
                day = dateItems[dayIndex];

            var formatedDate = new Date(dateItems[yearIndex], month, day);
            return formatedDate;
}

</script>

Monday, February 22, 2021

SQL How to group identifiers that are related with each other in specific groups

This article is a guide for grouping mutually related identifiers in groups using SQL. In math terminology, we could say it will find all connected subgraphs of an undirected graph (Graph Theory - Connectivity).

A graph is said to be connected if there is a path between every pair of a vertex.

Input, connected ident pairs:

Ident1 | Ident2 
---------------------------------
 1     | 2         
 1     | 3         
 4     | 5         
 4     | 6        

So, in the example above 1, 2 and 3 are in a group (subgraph) 1.

4, 5 and 6 are in group 2.

Note that 1, 2 and 3 are in the same group although 2 and 3 are not directly related.

Output, idents with related groups:

Ident  | Group
---------------------------------
 1     | 1         
 2     | 1         
 3     | 1         
 4     | 2 
 5     | 2
 6     | 2

So, how to make connectivity groups of related elements in SQL?

My suggestion which is relatively easy to achieve is to use sp_GetIdentByGroup stored procedure. This stored procedure expects temporary table #PairIds to be created and filled. #PairIds have connected pairs ids. Result set returns Ident columns with Ids from #PairIds which are assigned to a specific group of connected elements.

First stored procedure sp_GetIdentByGroup should be created and after that sp_GetIdentByGroup can be executed to get a result.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE #PairIds
(
	Ident1 INT,
	Ident2 INT
)

INSERT INTO #PairIds
VALUES (1, 2),
(1, 3),
(4, 5),
(4, 6)

exec [dbo].[sp_GetIdentByGroup]

This stored procedure use cursor for grouping together related elements. The index makes it relatively fast. It is SQL 2012+ compatible.

Script for create [dbo].[sp_GetIdentByGroup] procedure:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
CREATE PROCEDURE [dbo].[sp_GetIdentByGroup]
AS
BEGIN

	DECLARE @message VARCHAR(70);
	DECLARE @IdentInput1 varchar(20)
	DECLARE @IdentInput2 varchar(20)
	DECLARE @Counter INT
	DECLARE @Group1 INT
	DECLARE @Group2 INT
	DECLARE @Ident varchar(20)
	DECLARE @IdentCheck1 varchar(20)
	DECLARE @IdentCheck2 varchar(20)

	SET @Counter = 1

	DECLARE @IdentByGroupCursor TABLE (
	Ident varchar(20) UNIQUE CLUSTERED,
	GroupID INT  
	);

	-- Use a cursor to select your data, which enables SQL Server to extract
	-- the data from your local table to the variables.
	declare ins_cursor cursor for
	select  Ident1, Ident2 from #PairIds


	open ins_cursor
	fetch next from ins_cursor into @IdentInput1, @IdentInput2 -- At this point, the data from the first row
	 -- is in your local variables.

	-- Move through the table with the @@FETCH_STATUS=0
	WHILE @@FETCH_STATUS=0
	BEGIN

		SET @Group1 = null
		SET @Group2 = null

		SELECT TOP 1  @Group1 = GroupID,  @IdentCheck1 = Ident
		FROM @IdentByGroupCursor
		WHERE Ident in (@IdentInput1)

		SELECT TOP 1  @Group2 = GroupID,  @IdentCheck2 = Ident
		FROM @IdentByGroupCursor
		WHERE Ident in (@IdentInput2)

		IF (@Group1 IS NOT NULL AND @Group2 IS NOT NULL)
		BEGIN
			IF @Group1 > @Group2
			BEGIN
				UPDATE @IdentByGroupCursor
				SET GroupID = @Group2
				WHERE
				GroupID = @Group1
			END

			IF @Group2 > @Group1
			BEGIN
				UPDATE @IdentByGroupCursor
				SET GroupID = @Group1
				WHERE
				GroupID = @Group2
			END
		END
		ELSE IF @Group1 IS NOT NULL
		BEGIN
			UPDATE @IdentByGroupCursor
			SET GroupID = @Group1
			WHERE
			Ident IN (@IdentInput1)
		END
		ELSE IF @Group2 IS NOT NULL
		BEGIN
			UPDATE @IdentByGroupCursor
			SET GroupID = @Group2
			WHERE
			Ident IN (@IdentInput2)
		END

		IF (@Group1 IS NOT NULL AND @Group2 IS NOT NULL)
		BEGIN
			IF @Group1 > @Group2
			BEGIN
				UPDATE @IdentByGroupCursor
				SET GroupID = @Group2
				WHERE
				GroupID = @Group1
			END

			IF @Group2 > @Group1
			BEGIN
				UPDATE @IdentByGroupCursor
				SET GroupID = @Group1
				WHERE
				GroupID = @Group2
			END

		END

			IF @Group1 IS NULL
			BEGIN

				INSERT INTO @IdentByGroupCursor (Ident, GroupID)
				VALUES (@IdentInput1, ISNULL(@Group2, @Counter))

			END

			IF @Group2 IS NULL
			BEGIN
				INSERT INTO @IdentByGroupCursor (Ident, GroupID)
				VALUES (@IdentInput2, ISNULL(@Group1, @COunter))
			END

			IF (@Group1 IS NULL OR @Group2 IS NULL)
			BEGIN

			SET @COunter =  @COunter  +1

		END

		-- Once the execution has taken place, you fetch the next row of data from your local table.
		fetch next from ins_cursor into @IdentInput1, @IdentInput2

	End


	-- When all the rows have inserted you must close and deallocate the cursor.
	-- Failure to do this will not let you re-use the cursor.    
	close ins_cursor
	deallocate ins_cursor


	SELECT Ident ,DENSE_RANK() OVER( ORDER BY GroupID ASC) AS GroupID
	FROM @IdentByGroupCursor

END
GO

Sunday, February 21, 2021

How to add code snippet in blogger

After a a while I wanted to write new blog post with snippet of SQL code. After googled "add code snippet to blogger" search terms at the of the results a link to the hilite web was found.

It is a very easy to use web. I recommend.

It can be used for various programming languages.

The user only needs to:

  • Type source code
  • Choose Language, Style and Line numbers
  • Click the Higlight button, Preview will appear the and the user only needs to copy and paste HTML code from HTML textbox into blogger post (in HTML view)

Thursday, May 22, 2014

.NET project on online Source control

Visual Studio 2013 express have a option to add your project to Source Control. It is easy to add project to local repository location. In this article you will find step by step tutorial how to add your project to free private Source control located on the internet.

Bitbucket supports private or public Git/Mercurial repositories up to 5 users.

Why you need online Source control Repository?

Let's say you developing your project and one day your computer crash down. It is pity to lose all your development effort till this point. So if you have local Source Control repository you will lost everything. But if you have online repository you could continue to work from your last check in on other computer.

Of course, there are other reasons why you should use online Source safe like sharing work and code with other people who are distant from you but in my case online backup is main reason to use online source control.

Step by step guide for online Source Control with Visual Studio 2013 express
  • To add your project to Source control click FILE --> Add to Source Control



  • Choose Git radio button and click OK

  • If you get error like this: "The current solution has projects that are located outside the solution folder. These projects will not be source controlled in the Git repository..." check Add ASP .NET Empty Web Site to Git Source control

  • Open Team Explorer, VIEW --> Team Explorer

  • Choose Changes in dropdown of Team Explorer window



  • Commit to local GIT, enter the comment (eg. "Initial Commit") and choose Commit from dropdown



  • Click on "Unsynced Commits"



  • Now you need to enter URL of an empty GIT repo, to get this URL you need to create account on Bitbucket

    • Creating Bitbucket repository

    • Go to Bitbucket and Sign up for free

    • Click on button Create to create new Bitbucket repository


    • enter Name, Description, Access level (in this sample I checked private repostiroy checkbox), Repository type (Git) and Language (C#)

    • after Create repository button is clicked you create new repository

    • next step is to copy URL,
      URL is in format :
      https://bitbucket.org/[ProfileName]/[RepoName]
      So if my profile on bitbucket is Me URL will be:
      https://bitbucket.org/Me/newwebsite

    • let's go back on Visual Studio 2013

  • In Visaul Studio in Team Explorer paste link of your BitBucket Repository and click Pubish


  • Enter you BitBucket username and password if you are asked. If everything is ok you will see meassage : "The origin remote has been added and the current branch has been published."

  • If you click Sync button your code will be synced on online BitBucket location

  • You can check it by going on BitBucket web page, choose repository and clicking Source under Navigation tab.