MSSQL split column values by delimiter and get the last value

 

SELECT BQCODE, REVERSE(LEFT(REVERSE(BQCODE), CHARINDEX('/', REVERSE(BQCODE)) - 1))

FROM [jordan_settelement_pacels]

Connect Devexpress Dashboard to SQL datasource and Run Custom queries

 Add the following code in the page load function to allow the devexpress dashboard connect to database and to create custome queries 

protected void Page_Load(object sender, EventArgs e) {

            DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards");

            ASPxDashboard1.SetDashboardStorage(dashboardFileStorage);

 

            // Uncomment this string to allow end users to create new data sources based on predefined connection strings.

            ASPxDashboard1.SetConnectionStringsProvider(new DevExpress.DataAccess.Web.ConfigFileConnectionStringsProvider());

           

            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

           

            // Registers an SQL data source.

            //DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", "NWindConnectionString");

            //SelectQuery query = SelectQueryFluentBuilder

            //    .AddTable("SalesPerson")

            //    .SelectAllColumnsFromTable()

            //    .Build("Sales Person");

            //sqlDataSource.Queries.Add(query);

            //dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml());

           

            // Registers an Object data source.

            DashboardObjectDataSource objDataSource = new DashboardObjectDataSource("Object Data Source");

            objDataSource.DataId = "Object Data Source Data Id";

            dataSourceStorage.RegisterDataSource("objDataSource", objDataSource.SaveToXml());

           

            // Registers an Excel data source.

            DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source");

            excelDataSource.ConnectionName = "Excel Data Source Connection Name";

            excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Sheet1"));

            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());

            

            ASPxDashboard1.SetDataSourceStorage(dataSourceStorage);

            ASPxDashboard1.EnableCustomSql = true;

            ASPxDashboard1.EnableJsonDataSource = true;

            ASPxDashboard1.EnableTextBoxItemEditor = true;

            ASPxDashboard1.AllowExecutingCustomSql= true;

            ASPxDashboard1.AllowCreateNewDashboard = true;

            ASPxDashboard1.AllowCreateNewDashboard = true;

            ASPxDashboard1.AllowCreateNewJsonConnection = true;

            ASPxDashboard1.AllowExportDashboard = true;

            ASPxDashboard1.AllowExportDashboardItems = true;

            ASPxDashboard1.AllowInspectAggregatedData = true;

            ASPxDashboard1.AllowInspectRawData = true;

            ASPxDashboard1.AllowMaximizeItems = true;

            ASPxDashboard1.AllowOpenDashboard = true;

            ASPxDashboard1.AllowReadUnlistedColumnsFromClientApi = true;

 

        }

 

        protected void DataLoading(object sender, DataLoadingWebEventArgs e) {

            if(e.DataId == "Object Data Source Data Id") {

                e.Data = Invoices.CreateData();

            }

        }

 

        protected void ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e) {

            if(e.ConnectionName == "Excel Data Source Connection Name") {

                ExcelDataSourceConnectionParameters excelParameters = (ExcelDataSourceConnectionParameters)e.ConnectionParameters;

                excelParameters.FileName = HostingEnvironment.MapPath(@"~/App_Data/Sales.xlsx");

            }

        }

Prevent the parent hosted web application on IIS from forwarding the configuration to its children

surround the tag or group of tags you want to prevent it from forwarding by the following tag

<location path="." inheritInChildApplications="false">

......

</location>

Multiple parameters in a select statement and exclude it if it is null

SELECT *

  FROM AdminUnitTemplates aut

  WHERE ID = COALESCE(@ID, ID)

    AND TempName = COALESCE(@TempName, TempName)

    AND Title = COALESCE(@Title, Title)

       AND AdminUnitID = COALESCE(@AdminUnitID, AdminUnitID)

Using the previous query, you can skip using if statements to generate the query. 

How to download files from Slide share

 1. Go to the slideshare and inspect the source and get the link and use the following code in python 

import urllib.request

# URL of the image to be downloaded

# Loop over a range of numbers with a different starting and ending value

for i in range(1, 311):

   url = 'https://image.slidesharecdn.com/an14v1consar-160210214304/75/annex-14-icao-arabic-version-'+str(i)+'-2048.jpg'

   filename = str(i)+'.jpg'

   urllib.request.urlretrieve(url, filename)

Convert the images to pdf using the following code 

from reportlab.lib.pagesizes import letter

from reportlab.pdfgen import canvas

from PIL import Image

# List of image filenames to combine

image_files =[]

for i in range(1, 311):

   image_files.append(str(i)+'.jpg')

# Create a new PDF file

pdf_file = canvas.Canvas('images.pdf', pagesize=letter)

# Loop over the image files and add them to the PDF

for image_file in image_files:

    # Open the image file using PIL

    image = Image.open(image_file)

    # Calculate the aspect ratio of the image

    width, height = image.size

    aspect_ratio = height / width

    # Add the image to the PDF

    pdf_file.setPageSize((letter[0], letter[0] * aspect_ratio))

    pdf_file.drawImage(image_file, 0, 0, letter[0], letter[0] * aspect_ratio)

    # Add a new page to the PDF for the next image

    pdf_file.showPage()

# Save the PDF file

pdf_file.save()

Statistical fundamentals and terminology for model building and validation

1. Mean: This is a simple arithmetic average, which is computed by taking the aggregated sum of values divided by a count of those values. The mean is sensitive to outliers in the data. An outlier is the value of a set or column that is highly deviant from the many other values in the same data; it usually has very high or low values. 

2. Median: This is the midpoint of the data, and is calculated by either arranging it in ascending or descending order. If there are N observations. 

3. Mode: This is the most repetitive data point in the data

import numpy as np

import statistics as stats

data = np.array([4,5,1,2,7,2,6,9,3,9,9,2])

# Calculate Mean

dt_mean = np.mean(data) ; print ("Mean :",round(dt_mean,2))

# Calculate Median

dt_median = np.median(data) ; print ("Median :",dt_median)

# Calculate Mode

dt_mode = stats.multimode(data);

print(dt_mode)

Result:

Mean : 4.92 Median : 4.5 Mode: [2, 9]

After real test, WMS is faster than WMTS using openlayer 6

 To run the following code you have to import the required js and css files 

HTML