MS SQL Server Date StartOfDay EndOfDay

CREATE FUNCTION [dbo].[fnDate_EndOfDay]
(
@date datetime
)
RETURNS datetime
AS
BEGIN
return CONVERT(DATETIME, CONVERT(varchar(11), @date, 111 ) + ' 23:59:59', 111)
END

CREATE FUNCTION [dbo].[fnDate_StartOfDay]
(
@date datetime
)
RETURNS datetime
AS
BEGIN
return CONVERT(DATETIME, CONVERT(varchar(11), @date, 111 ) + ' 00:00:00', 111)
END


print dbo.fnDate_StartOfDay(getdate())
print dbo.fnDate_EndOfDay(getdate())


Jan 24 2019 12:00AM
Jan 24 2019 11:59PM

WebClient UploadString POST Object as JSON

            string apiUrl = "http://localhost:14101/Print/MutfakAdisyonYazdir";

            var req = new MutfakAdisyonYazdir_RequestDTO();
            req.TokenGuid = Guid.NewGuid().ToString();
            req.AdisyonSiparisIdler = new List<long>() { 123, 234, 345, 456 };

            string inputJson = JsonHelper.Serialize(req);
            WebClient client = new WebClient();
            client.Headers["Content-type"] = "application/json";
            client.Encoding = Encoding.UTF8;
            string json = client.UploadString(apiUrl, inputJson);

            var resp = JsonHelper.Deserialize<BaseResponseDTO>(json);

SQL Server Reorganize All Indexes in Database

declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)

declare FragmentedTableList cursor for
 SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
   ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
   indexstats.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
   INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
        AND ind.index_id = indexstats.index_id
  WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
   indexstats.avg_fragmentation_in_percent > 5
  AND ind.Name is not null
  ORDER BY indexstats.avg_fragmentation_in_percent DESC

    OPEN FragmentedTableList
    FETCH NEXT FROM FragmentedTableList 
    INTO @tableName, @indexName, @indexType, @percentFragment

    WHILE @@FETCH_STATUS = 0
    BEGIN
      print 'Processing ' + @indexName + ' on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
     
      if(@percentFragment<= 30)
      BEGIN
            EXEC( 'ALTER INDEX [' +  @indexName + '] ON ' + @tableName + ' REBUILD; ')
       print 'Finished reorganizing ' + @indexName + ' on table ' + @tableName
      END
      ELSE
      BEGIN
         EXEC( 'ALTER INDEX [' +  @indexName + '] ON ' + @tableName + ' REORGANIZE;')
        print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
      END 
      FETCH NEXT FROM FragmentedTableList 
        INTO @tableName, @indexName, @indexType, @percentFragment
    END
    CLOSE FragmentedTableList
    DEALLOCATE FragmentedTableList

SSH Login Without Password Using ssh-keygen & ssh-copy-id

1. create public key
[red.red-pc] ➤ ssh-keygen

WARNING:

You should not store anything in MobaXterm HOME directoy (/home/mobaxterm): with your current settings, this folder is not "persistent", so it will be cleared at each MobaXterm restart.

If you want to set a "persistent" HOME directory which will not be cleared at each MobaXterm startup, go to MobaXterm settings window and choose a folder in which to store MobaXterm home files.

Generating public/private rsa key pair.
Enter file in which to save the key (/home/mobaxterm/.ssh/id_rsa):
/home/mobaxterm/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/mobaxterm/.ssh/id_rsa.
Your public key has been saved in /home/mobaxterm/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:InGPnZ7IvfhE/ejPBFOOsdoWGGVitbmKm7T/4M2RsD8 red@red-pc

2. copy the public key to remote host

[red.red-pc] ➤ ssh-copy-id -i id_rsa.pub 10.123.105.119

/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

red@10.123.105.119's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '10.123.105.119'"

and check to make sure that only the key(s) you wanted were added.

3. login to remote host whitout password

[red.red-pc] ➤ ssh root@10.123.105.119

Last login: Thu Jul 12 16:26:06 2018 from 10.123.105.101

Python Get Script Path

print(os.path.dirname(os.path.realpath(__file__)))
or
print(os.path.dirname(os.path.realpath(sys.argv[0])))

SQL Server Refresh All View

SET NOCOUNT ON
DECLARE @SQL varchar(max) = ''
SELECT @SQL = @SQL + 'print ''Refreshing --> ' + name + '''
EXEC sp_refreshview ' + name + ';
'
  FROM sysobjects
  WHERE type = 'V' --< condition to select all views, may vary by your standards
--SELECT @SQL
EXEC(@SQL)

SQL server long running queries query stats

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_seconds DESC;

SQL Server unused indexes

select object_name(i.object_id) as ObjectName, i.name as [Unused Index],MAX(p.rows) Rows
,8 * SUM(a.used_pages) AS 'Indexsize(KB)',
case
when i.type = 0 then 'Heap'
when i.type= 1 then 'clustered'
when i.type=2 then 'Non-clustered' 
when i.type=3 then 'XML' 
when i.type=4 then 'Spatial'
when i.type=5 then 'Clustered xVelocity memory optimized columnstore index' 
when i.type=6 then 'Nonclustered columnstore index'
end index_type,
'DROP INDEX ' + i.name + ' ON ' + object_name(i.object_id) 'Drop Statement'
from sys.indexes i
left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id
     and i.index_id = s.index_id
     and s.database_id = db_id()
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
where objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
and s.index_id is null -- and dm_db_index_usage_stats has no reference to this index
or (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)-- index is being updated, but not used by seeks/scans/lookups
GROUP BY object_name(i.object_id) ,i.name,i.type
order by object_name(i.object_id) asc

WCF Service IParameterInspector kullanımı

public class ParameterValidator : IParameterInspector
{
public void AfterCall(string operationName, object[] outputs, object returnValue, object correlationState)
{
           
}

public object BeforeCall(string operationName, object[] inputs)
{
            return null;
}
}

public class CustomDataOperationBehavior : Attribute, IOperationBehavior
{
public void AddBindingParameters(OperationDescription operationDescription, BindingParameterCollection bindingParameters)
{
           
}

public void ApplyClientBehavior(OperationDescription operationDescription, ClientOperation clientOperation)
{
           
}

public void ApplyDispatchBehavior(OperationDescription operationDescription, DispatchOperation dispatchOperation)
{
            dispatchOperation.ParameterInspectors.Add(new ParameterValidator());
}

public void Validate(OperationDescription operationDescription)
{
           
}

[CustomDataOperationBehavior]
public BaseDataTable_Response GetDataTable(int rowCount)

Google Maps Api için şehir, bölge, ülke sınırları

1. http://nominatim.openstreetmap.org/ adresinden istenilen şehir arandıktan sonra listeden uygun sonuç seçilip "Details" tıklanır

2. Açılan sayfada listeden OSM Id kopyalanır. Ankara için örnek 223422

3. http://polygons.openstreetmap.fr/index.py adresindeki "Id of relation" a Id (223422) yapıştırılıp "Gönder" düğmesine tıklanır.

Açılan sayfada WKT, GeoJSON, poly yada image formatında ilgili şehir için poligon bilgileri alınabilir.

İlçe, İl, Bölge, Ülke ile ilgili sınır bilgileri bu şekilde elde edilebilir.

Daha sonra bu bilgilerle google maps api üzerinde çizim yaptırılabilir.