Update Inserted Record with Trigger

There are some cases that you can not change your code if you have an ancient project. If it causes to insert wrong values to db, you may want to validate the value is ok and if not, correct it. We can use db trigger for such cases.


CREATE TRIGGER [dbo].[triggerName]
ON [dbo].[TableName]
FOR INSERT
AS
BEGIN
DECLARE @Name AS varchar(5000)
DECLARE @Category AS varchar(50)
DECLARE @ID AS int
SET @FirstName=(select TestID from inserted)
SET @ID = (select ResultID from inserted)

IF @Category = 'CARS'
BEGIN
SET @Name=(select name from inserted)
IF LEN(@Name) != 50 -- Our condition is the length of the name must be 50 if not, that means it has 0s and we need to replace 0s.
BEGIN
-- 'Lentgth is incorrect:' + Cast(LEN(@Name) as varchar(50))
IF CHARINDEX('0',@Name)>0 -- If name contains any 0s, update those 0s with 'X'  - here we update the table record
BEGIN
PRINT 'Includes Zero'
UPDATE TableName
SET Items = Replace(@Name,'0','X')
WHERE ID = @ID
END
END

END
-- 'IT IS NOT CATEGORY = CARS
END

Important thing here is the type of trigger FOR INSERT

This type runs after each insert operation.

Learn more here:  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

Multiple arguments in SQL IF:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME

SET @StartDate = NULL
SET @EndDate = NULL

IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) 
    BEGIN
        Select 'This works just fine' as Msg
    END
Else
    BEGIN
    Select 'No Lol' as Msg
    END

#sql # mssql #db-trigger, #insert-trigger, #sql-trigger

Advertisements

Azure WebJobs

What is an Azure WebJob?  With WebJobs, you can simply run a program or script just like a running a web app. Azure Functions is also a similar service. You can create a console app and run it continuously or triggered. Let’s try a simple one.

  • Create the console app: dotnet new console
  • Write your code. Console.WriteLine outputs to WebJobs logs page. So you can use that to log simply.
  • Publish the project: dotnet publish -c Release
  • Add a new file with extension .cms, such as Run.cmd in the publish folder – we do this because webjobs do not run dll files directly. It will run this script file.
  • Add the command to run your dll: dotnet YourProject.dll
  • Zip the publish file: YourPorject.zip
  • Follow these steps to upload it to Azure and run it: https://docs.microsoft.com/en-us/azure/app-service/web-sites-create-web-jobs
  • You can use CRON expression for triggered jobs:

Every 15 minutes: 0 */15 * * * *
Every hour (that is, whenever the count of minutes is 0): 0 0 * * * *
Every hour from 9 AM to 5 PM: 0 0 9-17 * * *
At 9:30 AM every day: 0 30 9 * * *
At 9:30 AM every weekday: 0 30 9 * * 1-5

For more advanced features, you can use WebJobs SDK:
https://github.com/Azure/azure-webjobs-sdk

Resources:

WebJobs:
https://docs.microsoft.com/en-us/azure/app-service/web-sites-create-web-jobs

WebJobs vs. Functions:
https://docs.microsoft.com/en-us/azure/azure-functions/functions-compare-logic-apps-ms-flow-webjobs

http://www.hanselman.com/blog/IntroducingWindowsAzureWebJobs.aspx

CRON Expressions Cheat Sheet:
https://codehollow.com/2017/02/azure-functions-time-trigger-cron-cheat-sheet/

#azure, #azure-functions, #azure-webjobs, #webjobs

IoT Platforms

It is estimated that the IoT will consist of about 30 billion objects by 2020. It is also estimated that the market value will reach $7.1 trillion by 2020. The following list is the most common platforms used to create and support IoT applications. Most of them are from the biggest companies.

  1. Amazon Web Services IoT Platform 
  2. Microsoft Azure IoT Hub 
  3. IBM Watson Internet of Things (IoT) 
  4. Google Cloud Platform  
  5. Huawei OceanConnect 
  6. Oracle Cloud for IoT 
  7. Salesforce Cloud 
  8. Bosch IoT Suite
  9. Cisco IoT Cloud Connect  
  10. General Electric Predix –  Industrial Cloud-Based Platform (PaaS) 
  11. SAP Cloud Platform 
  12. ThingWorx IoT Platform
  13. Samsung ARTIK Internet of Things (IoT) Platform 
  14. HP Universal Internet of Things (IoT) Platform  
  15. MindSphere by Siemens
  16. Carriots by Altair 
  17. Open-source Kaa IoT Platform

#azure-iot-hub, #iot-platforms, #iot-platforms-list, #mindsphere, #samsung-artik

TypeScript Notes – Type assertions

Type assertions are a way to tell the compiler “trust me, I know what I’m doing.” A type assertion is like a type cast in other languages, but performs no special checking or restructuring of data. It has no runtime impact, and is used purely by the compiler. TypeScript assumes that you, the programmer, have performed any special checks that you need.

1. Way


let someValue: any = "this is a string";

let strLength: number = (<string>someValue).length;

2. Way

let someValue: any = "this is a string";

let strLength: number = (someValue as string).length;

Resource: TypeScript Handbook

#type-assertions, #typescript

TypeScript Notes – Primitive Types

Types

Boolean

The most basic datatype is the simple true/false value, which JavaScript and TypeScript call a boolean value.


let isDone: boolean = false;

Number

As in JavaScript, all numbers in TypeScript are floating point values. These floating point numbers get the type number. In addition to hexadecimal and decimal literals, TypeScript also supports binary and octal literals introduced in ECMAScript 2015.


let decimal: number = 6;
let hex: number = 0xf00d;
let binary: number = 0b1010;
let octal: number = 0o744;

String


let color: string = "blue";
color = 'red';

let fullName: string = `John Doe`;
let age: number = 27;
let sentence: string = `Hello, my name is ${ fullName }.
I'll be ${ age + 1 } years old next month.`;

Array

let list: number[] = [1, 2, 3];
let list: Array<number> = [1, 2, 3];

Tuple

// Declare a tuple type
let x: [string, number];
// Initialize it
x = ["hello", 10]; // OK
// Initialize it incorrectly
x = [10, "hello"]; // Error

console.log(x[0].substr(1)); // OK
console.log(x[1].substr(1)); // Error, 'number' does not have 'substr'<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

Enum

enum Color {Red, Green, Blue}
let c: Color = Color.Green;

enum Color {Red = 1, Green, Blue}
let c: Color = Color.Green;

enum Color {Red = 1, Green = 2, Blue = 4}
let c: Color = Color.Green;

enum Color {Red = 1, Green, Blue}
let colorName: string = Color[2];

alert(colorName);

Any

let notSure: any = 4;
notSure = "maybe a string instead";
notSure = false; // okay, definitely a boolean

let list: any[] = [1, true, "free"];

list[1] = 100;

Void

void is a little like the opposite of any. void means the absence of having any type at all. void type is mainly used as the return type of functions that do not return a value:

function warnUser(): void {
    alert("This is my warning message");
}

Null and Undefined

void is a little like the opposite of any. void means the absence of having any type at all. void type is mainly used as the return type of functions that do not return a value:

// Not much else we can assign to these variables!
let u: undefined = undefined;
let n: null = null;

#primitive-types, #typescript

MySql with ASP.NET Core

  1. Create the project: dotnet new console
  2. Add mysql package: dotnet add package MySql.Data
  3.  Optional: Add an ORM: dotnet add package Dapper
  4. Start vscode: code .
  5. Connect and and query
  6. Run the code: dotnet run

     static void Main(string[] args)
        {
            MySqlConnectionStringBuilder conn_string = new MySqlConnectionStringBuilder();
            conn_string.Server = "SERVERAdress";
            conn_string.UserID = "USERid";
            conn_string.Password = "Pws";
            conn_string.Database = "Db";

            MySqlConnection connection = new MySqlConnection
            {
                ConnectionString = conn_string.ToString()
            };
            connection.Open();

            MySqlCommand command = new MySqlCommand("SELECT * FROM Notes;", connection);

            using (MySqlDataReader reader = command.ExecuteReader())
            {

                while (reader.Read())
                {
                    string row = $"{reader["NoteId"]}\t\t{reader["NoteTitle"]}\t\t{reader["NoteText"]}";
                    System.Console.WriteLine(row);
                }
            }

            connection.Close();

            System.Console.ReadKey();
        }

#asp-net-core, #mysql, #mysql-with-net-core

Reports for Redmine

After some requests I have received, I will share some of the SQL commands that I used for creating reports for Redmine. You need to change projectIDs, journelIDs etc.  Here they are:

1 – Approximate Solution Time


a.istipi IsTipi,
COUNT(a.isnumarasi) ToplamIsSayisi,
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id IN (45,
64,
65) OR
projects.parent_id IN (64,
45,
65)) AND
journal_details.value = 3 AND
issues.status_id =12

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
GROUP BY
a.tracker_id

2. Current Project Analysis


(select name from projects where projects.id = issues.project_id) ProjeAdi,
count(*) ToplamIsSayisi,
(SELECT count(*) from issues where issues.status_id=2 and issues.project_id= $P{PROJECT_ID} ) IslemdeIsSayisi,
(SELECT count(*) from issues where issues.status_id=12 and issues.project_id=$P{PROJECT_ID}) CozumOnaylandiIsSayisi,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id = 1

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuHata,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id =2

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuIstek,

(select
SUM(DATEDIFF(issues.due_date,issues.start_date)+1) IsGunuSayisi
FROM issues
where issues.tracker_id =16
and issues.project_id = $P{PROJECT_ID}) ToplamZiyaretSayisi,
(select max(due_date) FROM issues where issues.tracker_id =16 and issues.project_id = $P{PROJECT_ID}) SonZiyaretTarihi
FROM issues where issues.project_id = $P{PROJECT_ID}
and issues.tracker_id != 16

3. Status of an Assignee

Change assignee id for your project.


SELECT id,
(SELECT name
FROM issue_statuses
WHERE issue_statuses.id = issues.status_id)
issueStatus,
(SELECT name
FROM projects
WHERE projects.id =issues.project_id)
ProjeAdi,
(select concat(firstname,' ',lastname) from users where users.id=issues.assigned_to_id) as AssigneeName
FROM issues
WHERE issues.assigned_to_id = $P{ASSIGNEE__ID}

Thanks a lot to Taner Yurdunkulu

#business-intelligence, #redmine, #redmine-reports