Wednesday, November 28, 2018

JAVA - How to work with LocalDateTime

/* now() */
    
LocalDateTime dateNow = LocalDateTime.now();
System.out.println( dateNow );        
System.out.println( dateNow.format( DateTimeFormatter.ofPattern( "dd.MM.yyyy H:m:s" ) ) );
    
/* defined datetime 1. Jan 2018 17:00 h */
    
LocalDateTime date = LocalDateTime.of( 2018, 1, 1, 17, 0, 0 );
System.out.println( date.format( DateTimeFormatter.ofPattern( "dd.MM.yyyy H:m:s" ) ) );
    
/* ..add 25 minutes to previous */
    
LocalDateTime dateend = date.plusMinutes( 25 );
System.out.println( dateend.format( DateTimeFormatter.ofPattern( "dd.MM.yyyy H:m:s" ) ) );
    
/* ..and check difference in minutes */
    
System.out.println( date.until( dateend, MINUTES ) );
Output:
2018-11-28T09:50:58.724
28.11.2018 9:50:58
01.01.2018 17:0:0
01.01.2018 17:25:0
25

DELPHI - How work with memory table (TFDMemTable)

Variant 1) Copy data from TDataSet to TFDMemTable:
var
  pMemTable : TFDMemTable;
  pQuery : TFDQuery;
begin
  ...
  pMemTable := TFDMemTable.Create( self );
  pMemTable.CloneCursor( pQuery );
  ...

Variant 2) Define own structure of TFDMemTable:
var
  pMemTable : TFDMemTable;
  pQuery : TFDQuery;
begin
  ...
  pMemTable := TFDMemTable.Create( self );
  pMemTable.FieldDefs.Add( 'key', ftString, 25 );
  pMemTable.FieldDefs.Add( 'value', ftString, 25 );
  pMemTable.CreateDataSet();
  ...
  pMemTable.AppendRecord( [ 'firstkey', 'firstvalue' ] );
  ...

Friday, November 23, 2018

DELPHI - How to solve error "...E2009 Incompatible types: 'method pointer and regular procedure'"

..try to surround called procedure as class procedure in class.
type
  TEachRowProcedures = class
  public
    class procedure ExportAsHTML_Proc( ARowIndex: Integer; ARowInfo: TcxRowInfo );
  end;
Call as:
...
_pView.DataController.ForEachRow( true, TEachRowProcedures.ExportAsHTML_Proc );

Wednesday, November 14, 2018

SSRS - How to make different color for bar of charts

When you need different color from bar of chart - here different for actual week:

1) Click with right mouse button on chart row value.

2) And for bar color use this expression:
=IIf(Fields!ID_week.Value=DATEPART(DateInterval.WeekOfYear, Today()), "LimeGreen","Tomato")

Thursday, October 11, 2018

AX, SQL SERVER - How to get difference between date and time columns in hours

Suppose this code:
select prodid, fromdate, fromtime, todate, totime from prodroutejob
where
prodid = '18-044906' and
jobtype = 4;
..and this result:
prodid               fromdate                fromtime    todate                  totime
-------------------- ----------------------- ----------- ----------------------- -----------
18-044906            2018-10-08 00:00:00.000 42933       2018-10-08 00:00:00.000 53735
18-044906            2018-10-09 00:00:00.000 31905       2018-10-09 00:00:00.000 39106
18-044906            2018-10-10 00:00:00.000 46033       2018-10-10 00:00:00.000 53234

(3 row(s) affected)
How to get difference between FROM and TO in hours ?

Use this code:
select datediff( hour, [start], [end] ) from
(
select dateadd( second, a.fromtime, a.fromdate ) as [start],
dateadd( second, a.totime, a.todate ) as [end]
from prodroutejob a
where
a.prodid = '18-044906' and
a.jobtype = 4
) a
Output:
diff
-----------
3
2
2

(3 row(s) affected)

Tuesday, October 9, 2018

AX, SQL SERVER - How find released items without assigned document of specified type

Question shows all released items without assigned documents of type PictureDB (=where PictureDB document is missing).
select a.* from
(
select itemid, namealias, 
( select count(*) from docuref 
  where
  typeid = 'PictureDB' and
  reftableid = 175 and
  refrecid = a.recid
) as count_
from inventtable a
where
itemid like 'S-653-%' or  
itemid like 'S-697-' or 
itemid like 'S-698-'
) a
where
count_ = 0
order by a.itemid
Output could be:
itemid                                   namealias            count_
---------------------------------------- -------------------- -----------
S-653-000-0001                           AIDA ALBERT 792      0
S-653-000-0008                           AIDA AJA 5006024259  0
S-653-000-0009                           AIDA AVISA5006024257 0

Thursday, October 4, 2018

SQL SERVER - How to get week number

select getdate(), datepart( wk, getdate() )
Output:
----------------------- -----------
2018-10-04 06:32:01.717 40

(1 row(s) affected)

Wednesday, October 3, 2018

DELPHI - How read data from text file divided by semicolon

This example reads two columns from text file saved in this format:
0-0_0_0200;R-IVECO FRANCIE, 57M
0-0_0_2500;KOSTRY/8019-57 MIST
0-00260-X-0_0_0000;PODPĚR. TRUBKA PRAVÁ
For storing is used memory table (TdxMemTable, QData):
procedure TFDrawingImportItems.doReading;
var
  f : TextFile;
  sRow : string;
  aSplitted: TArray< string >;
begin
  { -- delete all rows }

  if QData.Active then
    begin
      QData.Close;
      QData.Open;
    end;

  { -- open file and read data (two columns divided by ";") }

  AssignFile( f, EFileName.Text );
  try
    Screen.Cursor := crHourGlass;
    QData.DisableControls;

    reset( f );
  while not eof( f ) do
    begin
      readln( f, sRow );

      { only two columns }
      aSplitted := sRow.Split( [';'], 2 );

      QData.Insert;
      QDataItemid.AsString := aSplitted[0];
      QDataname.AsString := aSplitted[1];
      QData.Post;
    end;
  finally
    closeFile( f );

    QData.EnableControls;
    Screen.Cursor := crDefault;
  end;

end;
Output should be:

Tuesday, September 18, 2018

AX - How to import data from Excel to AX table

This example shows job for import of two columns from Excel to AX table AssetSorting.
static void c_Import_AssetSorting( Args _args )
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    int row = 1;    
    str s1, s2;
    AssetSorting assetSorting;

    /* create excel instance */
    
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();

    /* try to open excel data */
    
    startLengthyOperation();    
    try
    {
        workbooks.open( "c:\\ax\\cz_cpa.xlsx" );
    }
    catch ( Exception::Error )
    {
        endLengthyOperation();
        error( strFmt( "@CIE269", "c:\\ax\\cz_cpa.xlsx" ) );
        return;
    }
        
    workbook = workbooks.item( 1 );
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum( 1 );
    cells = worksheet.cells();

    /* go throught excel rows, insert data to table */
    
    ttsBegin;

    do
    {
      s1 = cells.item(row, 1).value().bStr();
      s2 = cells.item(row, 2).value().bStr();

      //info( s1 + " - " + s2 );

      /* TYPE is for testing, if we are at end of data */
      row++;
      type = cells.item( row, 1 ).value().variantType();

      //if ( row > 500 ) break;

      if ( !AssetSorting::Exist( s1, AssetSortValue::Sorting2 ) ) 
      {  
        assetSorting.SortCode = AssetSortValue::Sorting2;
        assetSorting.SortingId = s1;
        assetSorting.Description = s2;
        assetSorting.insert();
      }
        else info( "Exists - >" + s1 );
        
    }
    while ( type != COMVariantType::VT_EMPTY );

    ttsCommit;

    /* end excel */
    application.quit();

    info( "OK = " + int2str( row ) );
}

Friday, September 14, 2018

AX - How to insert new row based on existing row data in table

This example shows how to duplicate one row data to new row in one table (with update of some values).
static void job_duplicate_row(Args _args)
{
    EmployeeTable_RU tab1, tab2;
         
    tab1 = EmployeeTable_RU::findByEmployeeId( '32' );
    if ( !tab1 ) {
        info( "Source row not found." );
        return;
    }
            
    ttsBegin;
    
    /* copy row data from tab1 to tab2 + change two columns values */

    tab2.data( tab1 );
    tab2.EmployeeId = '463';
    tab2.HcmEmployment = 5637145327;

    if ( !tab2.validateWrite() ) throw Exception::Error;

    tab2.insert();

    ttsCommit;
    
    info( "OK" );
}

Wednesday, September 12, 2018

POWERSHELL - How to close all windows of specified process

This example shows script for closing all processes of "iexplore".

List of these running processes:
Get-Process iexplore
Output could be:
Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName                                                                                                    
-------  ------    -----      ----- -----   ------     -- -----------                                                                                                    
    499      46    29208      58308   303     0,45  25512 iexplore                                                                                                       
    641      54    13280      31924   212     0,31  26804 iexplore   
Here is the script for killing of this running processes:
# get process (check if exists)

$process = Get-Process iexplore -ErrorAction SilentlyContinue

# kill it

if ( $process ) { 
   
    echo " -> process found..it will be killed.." 
    
    $process.Kill()
}
else { 
    echo " -> process not found.." 
}
Output:
-> process found..it will be killed..

ORACLE - How to find table name by table name substring

You can use CAT table (catalog). It shows to you all object in user profile.
select * from cat
where
table_name like 'MEN%'
Output:
TABLE_NAME                     TABLE_TYPE 
------------------------------ -----------
MENU                           TABLE      
MENU_SEQ                       SEQUENCE   
MENU_ZAL                       TABLE 

C# - How to add items to TreeView

treeView1.Nodes.Clear();
TreeNode parentNode, node;

parentNode = treeView1.Nodes.Add( "Parent item" );

node = parentNode.Nodes.Add( "Child item 1" );
node = parentNode.Nodes.Add( "Child item 2" );
node = parentNode.Nodes.Add( "Child item 3" );

treeView1.ExpandAll();
Output:

WIN OS - How check if port is blocked

This commnad will check, if port 1760 is blocked.
netstat -aon | find "1760"
Output:
TCP    127.0.0.1:17600        0.0.0.0:0              LISTENING       7944

Tuesday, September 11, 2018

C# - How add values to DataGridView (with source in DataTable)

DataTable table = new DataTable();

table.Columns.Add(new DataColumn( "Text", typeof( string ) ) );
table.Columns.Add(new DataColumn( "Number", typeof( double ) ) );

table.Rows.Add( new object[] { "Value 1", 1 } );
table.Rows.Add( new object[] { "Value 2", 2 } );
table.Rows.Add( new object[] { "Value 3", 3 } );

dataGridView1.DataSource = table;
Output:

Monday, September 10, 2018

AX - How check if specified configuration key is enabled

if ( isConfigurationkeyEnabled( configurationKeyNum( Retail) ) ) ...

C# - How add items to ListView with two columns

/* add two new columns */
            
listView1.Columns.Clear();
listView1.Columns.Add( "Name", 150, HorizontalAlignment.Left );
listView1.Columns.Add( "Detail", 100, HorizontalAlignment.Left );

listView1.View = View.Details;

/* enable row selection */
listView1.HideSelection = false;
listView1.FullRowSelect = true;

/* add three items */

ListViewItem lvi = new ListViewItem( "Item 1" );
listView1.Items.Add( lvi );
lvi.SubItems.Add( "Detail 1" );
lvi.Selected = true; 

lvi = new ListViewItem( "Item 2" );
listView1.Items.Add( lvi );
lvi.SubItems.Add( "Detail 2" );

lvi = new ListViewItem( "Item 3" );
listView1.Items.Add( lvi );
lvi.SubItems.Add( "Detail 3" );

listView1.Focus();
Output:

C# - How add items to ListView

listView1.View = View.List;
            
ListViewItem lvi = new ListViewItem( "Item 1" );
listView1.Items.Add( lvi );

lvi = new ListViewItem( "Item 2" );
listView1.Items.Add( lvi );

lvi = new ListViewItem( "Item 3" );
listView1.Items.Add( lvi );    
Output:

Friday, September 7, 2018

C# - How change cursor and how to load own cursor

/* set cursor for current form */        
Cursor = Cursors.WaitCursor;
      
/* ..with exception for this button */
button1.Cursor = Cursors.Default;

/* load own cursor from file; and set it for one button */ 
Cursor myCursor = new Cursor( @"c:\cursorfilename.cur" );
if ( myCursor != null ) button2.Cursor = myCursor;

DELPHI - How to load own (animated) cursor

There is a several steps:

1) Prepare cursor - here it is animated cursor in file "cursor_busy.ani".

2) Create file with .rc extension, write path to cursor to it.
CUR_ANIM 21 "c:\delphi\projects\project_name\cursors\cursor_busy.ani"

3) Add link to .rc file to project:
{$R 'myResources.res' 'myResources.rc'}

4) Define internal number for own cursor:
const
  crCursor_Busy = 115;

5) Load defined cursor to app:
const
  Screen.Cursors[ crCursor_Busy ] := LoadCursor( hInstance, 'CUR_ANIM' );

6) Now, when you use this code:
Screen.Cursor := crCursor_Busy;
..you will see this loaded (animated) cursor:

Thursday, September 6, 2018

AX - How read specified note from production order (DocuRefSearch)

static void cieb_GetProdNote(Args _args)
{
    Notes notes = "";
    DocuRef docuRefTmp;
    ProdTable prodTable;
    DocuRefSearch docuRefSearchTmp;
    
    /* - find production order */
    
    prodTable = prodTable::find( "18-037399" );
    if ( ! prodTable ) return;
    
    /* - read its notes */
        
    docuRefSearchTmp = DocuRefSearch::newCommon( prodTable );
    docuRefSearchTmp.parmDocuTypeId( "Poznámka" );
    docuRefSearchTmp.init();
    
    if ( docuRefSearchTmp.next() )
    {
        docuRefTmp = docuRefSearchTmp.docuRef();
        
        notes += docuRefTmp.Notes;
    }

    /* - show result */
    
    info( notes );
}
Output:

C# - How add more items to multiline TextBox

textBox1.Text = null;

textBox1.Text += "Row 1\r\n";
textBox1.Text += "Row 2\r\n";
textBox1.Text += "Row 3"; 
Output:

C# - How to show the most easy message box

MessageBox.Show( "This is a message.." );
Output:

Tuesday, September 4, 2018

AX, SSRS - How to solve error "Type mismatch. Report parameter is of type String, but value is of type Int64"

This error could be shown when you deployed report from VS.

Effective solution was: redeploy SSRS report again from AX AOT tree (..or maybe deploy it from VS with CLEAN step).

C# - How to add formatted text to RichTextBox

richTextBox1.Clear();

richTextBox1.SelectionFont = new Font( "Courier New", 10, FontStyle.Bold );
richTextBox1.SelectionColor = Color.Red;
richTextBox1.SelectedText = "This ";

richTextBox1.SelectionFont = new Font( "Courier New", 15, FontStyle.Bold );
richTextBox1.SelectionColor = Color.Blue;
richTextBox1.SelectedText = "is ";

richTextBox1.SelectionFont = new Font("Courier New", 20, FontStyle.Bold | FontStyle.Italic);
richTextBox1.SelectionColor = Color.Green;
richTextBox1.SelectedText = "a text";
Output:

C# - How to add items to ListBox from DataTable

DataTable table = new DataTable();

table.Columns.Add( new DataColumn( "Text", typeof( string ) ) );
table.Columns.Add( new DataColumn( "Number", typeof( double ) ) );

table.Rows.Add( new object[] { "Value 1", 1 } );
table.Rows.Add( new object[] { "Value 2", 2 } );
table.Rows.Add( new object[] { "Value 3", 3 } );

listBox1.DisplayMember = "Text";
listBox1.DataSource = table;
Output:

C# - How to add items to ListBox

listBox1.Items.Clear();

listBox1.Items.Add( "One" );
listBox1.Items.Add( "Two" );
listBox1.Items.Add( "Three" );
, or (from array):
listBox1.Items.Clear();
   
String[] sArray = { "One", "Two", "Three" };
listBox1.Items.AddRange( sArray );
, or
listBox1.DataSource = new string[] { "One", "Two", "Three" };
Output:

Friday, August 24, 2018

AX - How get active country code

This country code is readed from legal entity. The example shows how to work with macro. #isoCZ is in #ISOCountryRegionCodes macro defined as "CZ".
#ISOCountryRegionCodes
static void Job(Args _args)
{
  LogisticsAddressCountryRegionISOCode isoCode = SysCountryRegionCode::countryInfo();
    
  if (isoCode == #isoCZ ) info( isoCode );
}
Output:
CZ
Note: To defined macro you can pass parameters. In macro you access to its by order -> %1, %2 ...and so on.

Monday, August 6, 2018

SQL SERVER - How to interpret empty string as NULL value

When is in varchar field saved NULL value, is situation easy:
select isnull( revision, '< null >' ) from drawing_drawing;
Output is:
----------
< null >
Different situation is when varchar field is only empty. In that case isnull() returns only empty string:
select isnull( revision, '< null >' ) from drawing_drawing;
Output is:
----------
The solution is to use inner function nullif() - ...question: which value is NULL ?
select isnull( nullif( revision, '' ), '< null >' ) from drawing_drawing;
Output is:
----------
< null >

Thursday, August 2, 2018

ORACLE - How declare variable accross existing table field (%type)

Both variables are declared accross existing PEOPLE.NAME table field:
function Month_Execute( pPeriod varchar2, sErrorMessage out varchar2 ) return number
is
  sMinName people.name%type;
  sMaxName people.name%type;
begin
...

SQL SERVER - How disable updating of the table/field, UPDATE trigger

This code disable updating of the table:
create trigger [dbo].[drawing_drawing_update]
on [dbo].[drawing_drawing]
for update
as
begin
  set nocount on;

  raiserror( N'Table can`t be updated.', 16, 1 ) with nowait;
  rollback tran;
  return;
end;
Alternatively, you can protect only specified updated field(s):
create trigger [dbo].[drawing_drawing_update]
on [dbo].[drawing_drawing]
for update
as
begin
  set nocount on;

  if UPDATE( validsince )
  begin
    raiserror( N'Field can`t be updated.', 16, 1 ) with nowait;
    rollback tran;
    return;
  end;
end;
When you trying to save row in application, this message will appears:

Thursday, July 26, 2018

AX - How calculate only with opened days in calendar

Use WorkCalendarSched class:
WorkCalendarSched workCalendarSched;
date dateOnlyWorking, dateAll;

workCalendarSched = new workCalendarSched();

dateOnlyWorking = 
  workCalendarSched.schedDate( SchedDirection::Backward, today(), 6, NoYes::Yes, "5x24" );
dateAll = 
  workCalendarSched.schedDate( SchedDirection::Backward, today(), 6, NoYes::No, "5x24" );

info( date2str( dateOnlyWorking, 321, DateDay::Digits2, DateSeparator::Hyphen, 
      DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4 ) );
info( date2str( dateAll, 321, DateDay::Digits2, DateSeparator::Hyphen, 
      DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4 ) );
Output:

Wednesday, July 25, 2018

SSRS - How use labels from AX in SSRS report

1) Click right mouse button on the field, select "Expression..".

2) Use "Labels!" prefix and then the number of AX label:

Tuesday, July 3, 2018

POWERSHELL - How write colored text output

write-host 'This is with red backgroud' -BackgroundColor Red
write-host '..and this is with yellow foretext' -ForegroundColor Yellow
Output:

Monday, July 2, 2018

AX - How to run AX from command line with another language

..here it run it in Russian language:
"C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\Ax32.exe" -language=ru

Sunday, July 1, 2018

AX - How to use "display" method in forms datasource

This is useful if you need find some value in form. Parameter is source type for DataSource.

public display ItemNameDisplay ItemName( vm_data_detail _vm_data_detail )
{
    return ProdTable::find( _vm_data_detail.ProdId ).ItemName;
}

Friday, June 29, 2018

SSRS - How add to TextBox HTML formatting support

When you need use HTML formatting in TextBox item:

1) Select text (not only click on item, you must select it).
2) Right mouse click, properties (placeholder).
3) On General page set formatting to HTML. Now you can use base HTML tags in source string.

SSRS - How return substring only to first occurrence of specified char

When field ItemId contains for example value "ABC-156032_0_0000" and you want only first part of this string to first occurrence of "_" char, use this code for expression:
=Mid( First( Fields!ItemId.Value, "ProdRouteCardDS" ), 
1, InStr( First(Fields!ItemId.Value, "ProdRouteCardDS" ), "_" ) - 1 )
Output will be:
ABC-156032

Wednesday, June 27, 2018

AX - How to split path to file - to path, filename and extension

static void test_job(Args _args)
{
    Filename filepath;
    Filename filename;
    Filename filetype;

    /* get info */

    [ filepath, filename, filetype ] = Global::fileNameSplit( "c:\\temp\\readme.txt" );
    
    /* show result */
    
    info( filepath );
    info( filename );
    info( fileType );  
}
Output:

Friday, June 22, 2018

POWERSHELL - How to solve "import-module : the specified module 'activedirectory' was not loaded because no valid module file was found in any module directory." error

When you get for this powershell command,
Import-Module ActiveDirectory 
this error:
import-module : the specified module 'activedirectory' was not 
loaded because no valid module file was found in any module directory.
, you have to install "Active Directory Management Gateway Service" at first, and then run command again.

The command get for you access to AD powershell functions.

SQL SERVER - How to solve problem "OLE DB error: OLE DB or ODBC error: Login failed for user" on BI cubes processing

This error occurs, when user (here NT SERVICE\MSOLAP$TSTINST) have not access to source DB.


























Solution: Add to user db_datareader rights.

Tuesday, June 19, 2018

AX - How to solve Error "This installation package could not be opened" (Visual Studio Tools)

This error can occur in installation "Visual Studio Tools" for AX 2012.

When you have VS 2013 (Professional and higher), installation is supported only from CU8.

-> check, if you have CU8 subdir in upgrade directory (in install AX 2012 R3 directory).

DELPHI - How to work with TDirectory (generic)

TDictionary is collection of key-value. In this example is it used for saving previous values - and its refreshing.
procedure TSQLParams.LoadForSQL( _iSQL_ID : integer );
var
  i : integer;
  b : boolean;
  vValue : variant;
  pParam : TSQLParam;
  pSQLParam : TSQLParam;
  pOldValues : TDictionary < string, variant >;
begin

  pOldValues := TDictionary < string, variant >.Create;

  try
    { -- save old values of params }

    for i := 0 to self.Count - 1 do
      begin
        pParam := TSQLParam( Items[ i ] );

        if not VarIsNull( pParam.vValue ) then
          pOldValues.Add( pParam.sIdent, pParam.vValue );
      end;

    { -- clear all }

    clear;

    { - get info  }

    if DB_SP.sp_params_by_sql_id.Active then DB_SP.sp__params_by_sql_id.Close;
    DB_SP.sp_params_by_sql_id.ParamByName( '@sql_id' ).AsInteger := _iSQL_ID;
    DB_SP.sp_sql_detail_params_by_sql_id.Open;

    DB_SP.sp_params_by_sql_id.First;
    while not DB_SP.sp_params_by_sql_id.Eof do
      begin
        { create new param }

        pSQLParam := TSQLParam( self.Add );
        pSQLParam.sIdent := DB_SP.sp_params_by_sql_id.FieldByName( 'ident' ).AsString.ToLower;
        pSQLParam.vValue := Variants.null;

       { - try to find old saved value }

       b := pOldValues.TryGetValue( pSQLParam.sIdent, vValue );
       if b then pSQLParam.vValue := vValue;

       { - next row }

       DB_SP.sp_params_by_sql_id.Next;

     end;

  finally
    pOldValues.Free;
  end;

end;

Monday, June 18, 2018

Monday, June 11, 2018

DELPHI - How to split string with TStringList class support

For string split you can use class TStringList.
var
  i : integer;
  sResult : string;
  pSplit : TStringList;
begin
  pSplit := TStringList.Create;
  try
    { define delimiter }
    pSplit.Delimiter := ';';

    { - split it }

    pSplit.Clear;
    pSplit.DelimitedText := 'This;is;a;list';

    { - list it }

    sResult := '';
    for i := 0 to pSplit.Count - 1  do
      begin
        sResult := sResult + pSplit[i] + #13;
      end;

    ShowMessage( sResult );

  finally
    pSplit.Free;
  end;
Output:

Friday, June 1, 2018

POWERSHELL - Send output to file (with appending)

This example send first output to bothdirs.txt; second line with ">>" appends second output to end of file.
dir c:\tmp > c:\tmp\bothdirs.txt;
dir C:\Intel >> c:\tmp\bothdirs.txt;

notepad c:\tmp\bothdirs.txt;
Output:
Directory: C:\tmp

Mode                LastWriteTime     Length Name                                                                                                                        
----                -------------     ------ ----                                                                                                                        
d----       24. 5. 2018     11:05            png                                                                                                                         
-a---        1. 6. 2018     10:50          0 bothdirs.txt                                                                                                                
-a---       31. 5. 2018     13:54       8304 export.txt                                                                                                                  

    Directory: C:\Intel

Mode                LastWriteTime     Length Name                                                                                                                        
----                -------------     ------ ----                                                                                                                        
d----       14. 7. 2017     15:44            ExtremeGraphics                                                                                                             
d----       23. 5. 2018     12:47            gp                                                                                                                          
d----       14. 7. 2017     15:41            Logs                                                                                                                        

Friday, May 4, 2018

DELPHI - How ensure (system) path delimiter to end of file path

One of the good technique to ensure, that file path is ended with system delimiter.
var
  sMask : string;
begin
  ..
  sMask := IncludeTrailingPathDelimiter( ExtractFilePath( Application.ExeName ) ) + 
  '*' + '.txt';
Output (should be):
c:\dir\*.txt

Thursday, April 19, 2018

DELPHI - How check if OLE library is installed on client

When you use some special OLE object, you can check, if is installed on client (Winapi.ActiveX):
var
  classID: TCLSID;
  sOLEObject: string;
  bIsSupport : boolean;
begin
  { is installed ? }

  sOLEObject := 'Microsoft.DirectMusic.1';
  bIsSupport := CLSIDFromProgID( PWideChar( WideString( sOLEObject ) ), classID ) = S_OK;

  if bIsSupport then ShowMessage( 'Supported.' );
  ...

AX - How to work with advanced filters (..with sql where condition)

When you need some special filters, especially in relation to another field(s) from table, you can write directly sql where code.

This example shows all production orders, where difference between Delivery date and End date is 10 days:

Wednesday, April 18, 2018

SQL SERVER - How and why use "N" prefix for unicode strings

N prefix before string takes string as unicode (without N is string converted to DB codepage). It is useful for some special chars in some alphabets.
select 'Počty vzrůstajících kusů' as description
union all
select N'Počty vzrůstajících kusů'
Output:
description
------------------------
Pocty vzrustajících kusu
Počty vzrůstajících kusů

(2 row(s) affected)

Tuesday, April 17, 2018

DELPHI - How to create new directory (..and check if directory exists)

var
  sBackupPath : string;
begin
  ...
  sBackupPath := gsExePath + gcsDictionarySubDir;

  { check if not exists }
  if not DirectoryExists( sBackupPath ) then
    begin
      { try to create new directory }

      b := ForceDirectories( sBackupPath );

      if not b then
        begin
          ShowMessage( 'Backup directory cannot be created.' );
          exit;
        end;
    end;
...

Thursday, April 12, 2018

DELPHI - How to make tray icon with WIN API support only (without TTrayIcon)

trayIconData : TNotifyIconData;
...
{ add icon }
with TrayIconData do
begin
  cbSize := sizeOf( trayIconData );
  Wnd := Handle;
  uID := 0;
  uFlags := NIF_MESSAGE + NIF_ICON + NIF_TIP;
  uCallbackMessage := WM_ICONTRAY;
  hIcon := Application.Icon.Handle;
  StrPCopy( szTip, self.Caption );
end;

Shell_NotifyIcon( NIM_ADD, @TrayIconData );
And callback procedure:
procedure TFMain.WMIconTray( var msg : TMessage );
var
  pt : TPoint;
begin
  case msg.lParam of
    { popup menu }
    WM_RBUTTONDOWN:
      begin
         GetCursorPos( pt );
         PopupTray.Popup( pt.x, pt.y );
      end;

    { doubleclick }
    WM_LBUTTONDBLCLK :
      begin
        MPopup_NextClick( nil );
      end;

    { mouse over }
    WM_MOUSEMOVE :
      begin
        self.Caption := gcsTitle + GetNextWordTime;

        with TrayIconData do
        begin
          StrPCopy( szTip, self.Caption );
          uFlags := NIF_TIP;
          Shell_NotifyIcon( NIM_MODIFY, @TrayIconData );
        end;
        
      end;
   end;
end;
And removing after destroy:
procedure TFMain.FormDestroy(Sender: TObject);
begin
  ...
  Shell_NotifyIcon( NIM_DELETE, @trayIconData );
end;
Output:

Wednesday, April 11, 2018

AX - How to round values

Second parameter from round() function define how will be rounded.
real iValue = 15.5678532;
real iResult1, iResult2;
    
// round to 0.1
iResult1 = round( iValue, 0.1 );
// round to all number */
iResult2 = round( iValue, 1 );
        
info( num2str( iResult1, 15, 2, 1, 0 ) + " - " + num2str( iResult2, 15, 2, 1, 0 ) );
Output:
15.60 - 16.00

DELPHI - How to make rotation of shape around point

Suppose this easy class for shape - triangle:
TShape = class( TObject )
  p1 : TPoint;
  p2 : TPoint;
  p3 : TPoint;

  procedure Moving( _iDeltaX, _iDeltaY : integer );
  procedure Rotation( _iAngle : integer );
  procedure RotationAroundXY( _x, _y : integer; _iAngle : integer );
end;
And initialization:
pShape := TShape.Create;
pShape.p1.X := 300;  pShape.p1.Y := 250;
pShape.p2.X := 250;  pShape.p2.Y := 280;
pShape.p3.X := 350;  pShape.p3.Y := 330;
For rotation shape around point use this code:
procedure TShape.RotationAroundXY( _x, _y : integer; _iAngle : integer );
var
  iNewX, iNewY : integer;
  dCos, dSin : double;
  dRadian : double;
  p1x, p1y, p2x, p2y, p3x, p3y : integer;
begin
  { -- angle }

  dRadian := ( 2 * pi ) / ( 360 / _iAngle );
                     
  dCos := cos( dRadian );
  dSin := sin( dRadian );

  { -- point for rotation around (in other case the rotation is around start of coords) }

  p1x := p1.x - _x;
  p1y := p1.y - _y;
  p2x := p2.x - _x;
  p2y := p2.y - _y;
  p3x := p3.x - _x;
  p3y := p3.y - _y;

  { -- calculation of the positions }

  iNewX := round( ( p1x * dCos ) - ( p1y * dSin ) );
  iNewY := round( ( p1y * dCos ) + ( p1x * dSin ) );
  p1.X := iNewX + _x;
  p1.Y := iNewY + _y;

  iNewX := round( ( p2x * dCos ) - ( p2y * dSin ) );
  iNewY := round( ( p2y * dCos ) + ( p2x * dSin ) );
  p2.X := iNewX + _x;
  p2.Y := iNewY + _y;

  iNewX := round( ( p3x * dCos ) - ( p3y * dSin ) );
  iNewY := round( ( p3y * dCos ) + ( p3x * dSin ) );
  p3.X := iNewX + _x;
  p3.Y := iNewY + _y;
end;
Output:

Tuesday, April 3, 2018

JAVA, JAVAFX - listener and lambda expression

Since JDK 1.8 you can use lambda expression. This article compares old and new variants on one example with CheckBox listener.

Old variant (response for change in CheckBox):
@FXML CheckBox checkbox1;
@FXML Label label1;

@Override
public void initialize( URL url, ResourceBundle rb ) {               

  /* variant without lambda expression */
    
  checkbox1.selectedProperty().addListener( new ChangeListener< boolean >() {
    @Override
    public void changed( ObservableValue observable, Boolean oldValue, Boolean newValue ) {
      label1.setText( newValue.toString() );
    }
  } );        
        
  /* -- init */
    
  checkbox1.selectedProperty().set( true );    
}  
And new (optional - old variant is still working) with lambda expression:
@FXML CheckBox checkbox1;
@FXML Label label1;
  
@Override
public void initialize( URL url, ResourceBundle rb ) {               
   
  /* variant with lambda expression */
    
  checkbox1.selectedProperty().addListener(
    ( ObservableValue observable, Boolean oldValue, Boolean newValue ) -> {
      label1.setText( newValue.toString() );
      }
    );    
    
  /* -- init */
    
  checkbox1.selectedProperty().set( true );    
}  

Both variant have same result:

SSRS - How disable fitting chart axis (..to more columns)

Sometimes SSRS chart left axis is fitted to more columns.

For solving that, set left axis property LabelsAutoFitDisabled to true.

SQL SERVER - How get database instance version

print @@version
Output:
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) 
 Jul  3 2017 02:25:44 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Thursday, March 29, 2018

DELPHI - How get greatest common divisor (for two digits)

The function returns greatest common divisor for two digits. When it does not found it, returns 1.
function GCD( _iNumber1, _iNumber2 : integer ) : integer;
var
  iTemp : integer;
begin
  if _iNumber1 < 0 then _iNumber1 := -_iNumber1;
  if _iNumber2 < 0 then _iNumber2 := -_iNumber2;

  repeat

    if _iNumber1 < _iNumber2 then
      begin
        iTemp := _iNumber1;
        _iNumber1 := _iNumber2;
        _iNumber2 := iTemp;
      end;

    _iNumber1 := _iNumber1 mod _iNumber2;

  until ( _iNumber1 = 0 );

  result := _iNumber2;
end;
Calling:
var
  i : integer;
begin
  i := GCD( 12, 16 );

  ShowMessage( IntToStr( i ) );
end;
Output:

Wednesday, March 28, 2018

DELPHI - How to show chi-square distribution curve

Propability density function (chi-square distribution).
var
  i, iK : integer;
  iX, iY, iGamma, iNumerator, iDenominator : double;
  pSerie : TLineSeries;
  pIntegral : TIntegral;
begin
  Memo1.Clear;

  pSerie := TLineSeries( Chart1.Series[0] );
  pSerie.Clear;

  pIntegral := TIntegral.Create;
  pIntegral.iStep := 0.05;
  pIntegral.SetInterval( 0.001, 20 );

  { -- set param + calc GAMMA function }

  iK := 8;
  iGamma := GetGamma( iK/2 );

  for i := 0 to pIntegral.pX.Count - 1 do
    begin
      iX := pIntegral.pX.GetValue( i );

      try
        iNumerator := power( iX, iK/2-1 ) * exp( ( -1*iX ) /2 );
        iDenominator := power( 2, iK / 2 ) * iGamma;

        if iDenominator <> 0 then
          iY := iNumerator / iDenominator
        else
          iY := 0;
      except
        iY := 0;
      end;
      pIntegral.pY.AddValue( iY );

      { -- add to chart }

      pSerie.AddXY( iX, iY );
    end;
end;
And here is very important Gamma() function with k parameter.
function GammaStirF( X : double ) : double;
var
  y : double;
  w : double;
  v : double;
  stir : double;
begin
  w := 1 / x;
  stir := 7.87311395793093628397E-4;
  stir := -2.29549961613378126380E-4 + w * stir;
  stir := -2.68132617805781232825E-3 + w * stir;
  stir := 3.47222221605458667310E-3 + w * stir;
  stir := 8.33333333333482257126E-2 + w * stir;
  w := 1 + w * stir;
  y := exp(x);
  if x > 143.01608 then
    begin
      v := power( x, 0.5 * x -0.25 );
      y := v *( v / y );
    end
  else
    begin
      y := power( x, x -0.5 ) / y;
    end;
  result := 2.50662827463100050242 * y * w;
end;

function GetGamma( _x : double ) : double;
var
  p : double;
  PP : double;
  q : double;
  QQ : double;
  z : double;
  i : longint;
  SgnGam : double;
begin
  SgnGam := 1;
  q := abs( _x );
  if q > 33.0 then
  begin
    if _x < 0.0 then
    begin
      p := floor(q);
      i := round(p);

      if i mod 2 = 0 then
      begin
        SgnGam := -1;
      end;

      z := q - p;

      if z > 0.5 then
      begin
        p := p+1;
        z := q-p;
      end;

      z := q * sin( pi * z );
      z := Abs(z);
      z := pi / ( z * GammaStirF( q ) );
    end
    else
    begin
      z := GammaStirF( _x );
    end;
    result := SgnGam * z;
    exit;
  end;
  z := 1;
  while _x >= 3 do
  begin
    _x := _x - 1;
    z := z *_x;
  end;
  while _x < 0 do
  begin
    if _x > -0.000000001 then
    begin
      result := z / ( ( 1 + 0.5772156649015329 *_x ) * _x );
      exit;
    end;
    z := z /_x;
    _x := _x + 1;
  end;
  while _x < 2 do
  begin
    if _x < 0.000000001 then
    begin
      result := z / ( ( 1 + 0.5772156649015329 * _x ) *_x );
      exit;
    end;
    z := z /_x;
    _x := _x + 1.0;
  end;
  if _x = 2 then
  begin
    result := z;
    exit;
  end;

  _x := _x - 2.0;
  PP := 1.60119522476751861407E-4;
  PP := 1.19135147006586384913E-3 + _X * PP;
  PP := 1.04213797561761569935E-2 + _X * PP;
  PP := 4.76367800457137231464E-2 + _X * PP;
  PP := 2.07448227648435975150E-1 + _X * PP;
  PP := 4.94214826801497100753E-1 + _X * PP;
  PP := 9.99999999999999996796E-1 + _X * PP;
  QQ := -2.31581873324120129819E-5;
  QQ := 5.39605580493303397842E-4 + _X * QQ;
  QQ := -4.45641913851797240494E-3 + _X * QQ;
  QQ := 1.18139785222060435552E-2 + _X * QQ;
  QQ := 3.58236398605498653373E-2 + _X * QQ;
  QQ := -2.34591795718243348568E-1 + _X * QQ;
  QQ := 7.14304917030273074085E-2 + _X * QQ;
  QQ := 1.00000000000000000320 + _X * QQ;

  result := z * PP / QQ;
  exit;
end;
Output:
k=2
k=8

Tuesday, March 27, 2018

Thursday, March 22, 2018

DELPHI - How to show normal distribution curve (Gaussian)

Typical propability density function (Gaussian distribution).
var
  i : integer;
  iY : double;
  pDN : TDistribution_Normal;
  pSerie : TLineSeries;
begin
  Memo1.Clear;

  /* prepare chart */

  pSerie := TLineSeries( Chart1.Series[0] );
  pSerie.Clear;

  /* prepare distribution data */

  pDN := TDistribution_Normal.Create;
  pDN.Init( 0, 1 );

  for i := 0 to pDN.pIntegral.pY.Count - 1 do
    begin
      { -- get value }

      iY := pDN.pIntegral.pY.GetValue( i );

      pSerie.AddXY( i, iY );
    end;

And here is very important TDistribution_Normal.Init() function with parameters as MEAN and SIGMA, here is generated range MEAN +-3.5 SIGMA.
The sum values under curve is equeal to 1.
function TDistribution_Normal.Init( _iMean, _iSigma : double ) : boolean;
var
  i : integer;
  iFrom, iTo, iValue : double;
begin
  result := false;

  { -- check }

  if _iSigma <= 0 then
    begin
      ShowMessage( 'Distribution_Normal() - standard deviation can`t be 0.' );
      exit;
    end;

  { -- add range -3.5 sigma - Mean - + 3.5 sigma }

  pIntegral.Clear;

  iFrom := _iMean - ( 3.5 * _iSigma );
  iTo := _iMean + ( 3.5 * _iSigma );
  { aproximation - 300 items }
  pIntegral.iStep := ( iTo - iFrom ) / 300;
  pIntegral.SetInterval( iFrom, iTo );

  { -- calc values normal distribution for every X }

  for i := 0 to pIntegral.pX.Count - 1 do
    begin
      iValue := ( 1 / ( _iSigma * sqrt( 2*pi ) ) ) * 
                exp( - ( sqr( pIntegral.pX.GetValue( i ) - _iMean ) ) / 
                ( 2 * sqr( _iSigma ) ) );

      pIntegral.pY.AddValue( iValue );
    end;

  pIntegral.Calc;

  result := true;
end;
Output:

Wednesday, March 21, 2018

JAVAFX - How fill and work with PieChart (graph)

import javafx.scene.chart.PieChart;
...
@FXML PieChart pieChart1;
...
/* set text */
    
pieChart1.setTitle( "This is a chart" );        

/* add data */
    
ObservableList series = FXCollections.observableArrayList(); 
series.add( new PieChart.Data( "Q1", 5 ) );
series.add( new PieChart.Data( "Q2", 5.5 ) );
series.add( new PieChart.Data( "Q3", 8 ) );
series.add( new PieChart.Data( "Q4", 3 ) );
         
pieChart1.setData( series ); 
Output:

JAVAFX - How fill and work with BarChart (graph)

import javafx.scene.chart.BarChart;
...
@FXML BarChart barChart1;
...
/* set texts */
    
barChart1.setTitle( "This is a chart" );
    
barChart1.getXAxis().setLabel( "x axis" );
barChart1.getYAxis().setLabel( "y axis" );
   
barChart1.setLegendVisible( false );

/* add data */
    
XYChart.Series series1 = new XYChart.Series();    
series1.getData().add( new XYChart.Data( "Q1", 5 ) );
series1.getData().add( new XYChart.Data( "Q2", 5.5 ) );
series1.getData().add( new XYChart.Data( "Q3", 8 ) );
series1.getData().add( new XYChart.Data( "Q4", 3 ) );        
         
barChart1.getData().add( series1 );       
Output:

Tuesday, March 20, 2018

DELPHI - How get hash value for file (SHA-1 fingerprint)

Hash function returns same value for same (in this case) file. This uses SHA-1 (Secure Hash Algorithm) algorithm.
uses IdHashMessageDigest;
...
var
  sFile : string;
  pSHA : TIdHashSHA1;
  pStream : TFileStream;
begin
  sFile := 'c:\_ax\error.png';

  pSHA := TIdHashSHA1.Create;
  pStream := TFileStream.Create( sFile, fmOpenRead or fmShareDenyWrite );

 try
   ShowMessage( 'File fingerprint = ' + pSHA.HashStreamAsHex( pStream ) );
 finally
   pStream.Free;
   pSHA.Free;
 end;
Output:

JAVAFX - How make "raw" object centering in the form (with properties binding)

public class FXMLMainController implements Initializable {
  
  @FXML private Label lbl1;
  
  @Override
  public void initialize( URL url, ResourceBundle rb ) {               
  }  
  
  /* Method for label centering */

  public void prepare() {
    Scene scene = lbl1.getScene();       
  
    if ( scene != null ) {
      lbl1.layoutXProperty().bind( scene.widthProperty().subtract( 
        lbl1.layoutBoundsProperty().get().getWidth()).divide( 2 ) );
      
      lbl1.layoutYProperty().bind( scene.heightProperty().subtract( 
        lbl1.layoutBoundsProperty().get().getHeight()).divide( 2 ) );
    }
    
  }
}
Method prepare() must be called after creating Scene class instance:
@Override
    public void start(Stage stage) throws Exception {

        FXMLLoader fLoader = new FXMLLoader( getClass().getResource( "FXMLMain.fxml" ) );
        Parent root = fLoader.load();
        FXMLMainController controller = fLoader.< fxmlmaincontroller >getController();      
        
        Scene scene = new Scene(root);
        
        stage.setScene(scene);
        stage.show();

        /* needs to be called here */
        controller.prepare();
    }
Output:

Monday, March 19, 2018

DELPHI - How get hash value for string (SHA-1 fingerprint)

Hash function returns same value for same string. This uses SHA-1 (Secure Hash Algorithm) algorithm.
uses IdHashSHA;
...
var
  pSHA : TIdHashSHA1;
begin
  pSHA := TIdHashSHA1.Create;

  try
    ShowMessage( 'abc = ' + pSHA.HashStringAsHex( 'abc' ) + #13 +
                 'abc = ' + pSHA.HashStringAsHex( 'abc' ) + #13 +
                 'abd = ' + pSHA.HashStringAsHex( 'abd' ) );
  finally
    pSHA.Free;
  end;
Output:

Friday, March 16, 2018

DELPHI - How get MD5 hash value for string (RSA-MD5 fingerprint)

Hash function returns same value for same string. This uses RSA-MD5 algorithm.
uses IdHashMessageDigest;
...
var
  pMD5: TIdHashMessageDigest5;
begin
  pMD5 := TIdHashMessageDigest5.Create;
  try
    ShowMessage( 'abc = ' + pMD5.HashStringAsHex( 'abc' ) + #13 +
                 'abc = ' + pMD5.HashStringAsHex( 'abc' ) + #13 +
                 'abd = ' + pMD5.HashStringAsHex( 'abd' ) );
  finally
    pMD5.Free;
  end;
Output:

Thursday, March 15, 2018

AX - How check free space in sequences (=if sequences have space for growing)

Sometimes you need to know, which sequences are full - or near to full.
This SQL script shows free space in sequences in percent.
select a.* 
from
(
select a.*, 
cast( cast( a.[free] as numeric(15,2) ) / cast( a.[space] as numeric(15,2) ) * 100 
as numeric(15,2) ) as [free_%] 
from
(
select a.numbersequence, a.lowest, a.highest, a.nextrec, 

a.highest - a.lowest as [space],
a.highest - a.nextrec as [free]

from numbersequencetable a
where
a.blocked = 0 and
a.cyclic = 0
) a
) a
order by a.[free_%]
Output:
numbersequence lowest      highest     nextrec     space       free        free_%
-------------- ----------- ----------- ----------- ----------- ----------- --------
Inve_38        1           999999      703701      999998      296298      29.63
Inve_33        1           99999       31366       99998       68633       68.63
Gene_24        1           99999       29841       99998       70158       70.16
Time_6         1           9999999     2665830     9999998     7334169     73.34
Docu_3         1           99999       19037       99998       80962       80.96
Prod_26        1           9999999     1327561     9999998     8672438     86.72
Prod_27        14000       999999      130299      985999      869700      88.20

Wednesday, March 14, 2018

WIN OS - How show user accounts dialog

..for example for possibility to change saved passwords.

From command line:
control userpasswords2
Output:

SQL SERVER - How update statistics for all DB tables

use framework;
exec sp_updatestats;
Output:
Updating [dbo].[sql_detail_graphs]
    [PK__sql_deta__3213E83F07EFD247] has been updated...
    [_WA_Sys_00000002_00507F52] has been updated...
    [_WA_Sys_0000000A_00507F52] has been updated...
    [_WA_Sys_00000009_00507F52] has been updated...
    [_WA_Sys_00000003_00507F52] has been updated...
    5 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [dbo].[sql_zal]
    0 index(es)/statistic(s) have been updated, 0 did not require update.
...
Statistics for all tables have been updated.

Tuesday, March 13, 2018

SQL SERVER - INSERT trigger

Sometimes you need to perform some actions immediately after you insert row to table. For this purpose you can use insert trigger on the table.
Pseudo-table named "inserted" contains inserted data row - you probably utilize it here.
ALTER trigger [dbo].[sql_insert]
on [dbo].[sql]
for insert
as
  begin transaction;
  
  begin try

    /* -- add new initial role */

    insert into [role_object]
    ( ident, [description], [role_object_type_ident] )
    select inserted.id, null, 'Q' from inserted;  
  
    /* -- add log row about creation to log table */
  
    insert into sql_detail_changes
    ( sql_id, [description], [type] )
    select inserted.id, 'Creation', 'I' from inserted;  
    
    if @@TRANCOUNT > 0 commit transaction;      
  end 

  /* -- problem ? */

  try begin catch
    if @@TRANCOUNT > 0 rollback transaction;      
  end catch;  

Monday, March 12, 2018

JAVA - How to split string to parts (java.util.StringTokenizer)

Another variant for string split - class java.util.StringTokenizer:
StringTokenizer pSpaceDelim = new StringTokenizer( "This is a text." );
           
System.out.println( "Count = " + pSpaceDelim.countTokens() + System.lineSeparator() );
    
while ( pSpaceDelim.hasMoreTokens() )
  System.out.println( pSpaceDelim.nextToken() );
Output:
Count = 4

This
is
a
text.
NOTE: System.lineSeparator() is system dependent line separator.

SQL SERVER - How use COLLATE over special chars

Sometimes (dependent how is your DB created) you need specify character set for some command that used special text chars. Here, for example in WHERE section.
Without COLLATE..
select count( * ) as rows
from salesline a
where
a.itemid = 'PŘEPRAVNÉ' and
a.confirmeddlv >= '2018/02/01' and
a.confirmeddlv <= '2018/02/07'
Output:
rows
-----------
0

(1 row(s) affected)
..you get empty result set.

But with COLLATE..
select count( * ) as rows
from salesline a
where
a.itemid collate latin1_general_ci_ai = 'PŘEPRAVNÉ' and
a.confirmeddlv >= '2018/02/01' and
a.confirmeddlv <= '2018/02/07'
Output:
rows
-----------
4

(1 row(s) affected)
..it is something else.

NOTE: Collate DB parameter you can get from DB properties.

Friday, March 9, 2018

DELPHI - How compute integral - (sin(x))^2 dx from 0 to PI

For integration is used class TIntegral which use for calculation trapezoidal rule.
procedure TForm1.BIntegralClick(Sender: TObject);
var
  i : integer;
  iX, iY : double;
  pIntegral : TIntegral;
  pSerie : TLineSeries;
begin
    pSerie := TLineSeries( Chart1.Series[0] );
    pSerie.Clear;

    { -- prepare integral class - interval 0..PI }

    pIntegral := TIntegral.Create;
    pIntegral.SetInterval( 0, pi );

    for i := 0 to pIntegral.pX.Count - 1 do
      begin
        { calc Y value }
        pIntegral.pY.AddValue( sqr( sin( pIntegral.pX.GetValue( i ) ) ) );

        { -> to graph }
        iX := pIntegral.pX.GetValue( i );
        iY := pIntegral.pY.GetValue( i );
        pSerie.AddXY( iX, iY );
      end;

    ShowMessage( FloatToStr( pIntegral.Calc ) );        // 1.57

    pIntegral.Free;
end;
Here is TIntegral.calc() method:
{ ---------------------------------------------------------------------------
  Method computes integral value (trapezoidal rule).  
  -------------------------------------------------------------------------- }
function TIntegral.Calc( _iCheckSumIntegralValue : double = 999 ) : double;
var
  i, iDeleteXFrom : integer;
  iValue : double;
begin
  result := 0;

  { -- checks }

  if pX.Count < 2 then exit;
  if pX.Count <> pY.Count then exit;

  { -- delete values }

  pIntegral.Clear;
  pCumulation.Clear;

  { -- trapezoidal rule }

  iDeleteXFrom := -1;

  for i := 1 to pX.Count - 1 do
    begin
      { -- compute integral value in interval }

      iValue := (
                  ( pX.GetValueCheckNull( i ) - pX.GetValueCheckNull( i - 1 ) )
                  *
                  ( ( pY.GetValueCheckNull( i ) + pY.GetValueCheckNull( i - 1 ) ) / 2 )
                );

       result := result + iValue;

       { -- check -> is greater than parameter ? }

       if _iCheckSumIntegralValue <> 999 then
       if result > _iCheckSumIntegralValue then
         begin
           { -- subtract }
           result := result - iValue;

           { -- index for removing }
           iDeleteXFrom := i;

           break;
         end;

       { -- record values }

       if bLogIntegral then pIntegral.AddValue( iValue );
       if bLogCumulation then pCumulation.AddValue( result );
     end;

  { -- delete items over test value (if exists) }

  if iDeleteXFrom <> -1 then
    for i := pX.Count - 1 downto iDeleteXFrom do
      begin
        pX.Delete( i );
        pY.Delete( i );
      end;

end;
Output:














And check (same result):

JAVA - How convert decimal to binary, hex, octal etc.

For converting from decimal to binary, hexadecimal, octal etc., use function Integer.toString().
Second parameter is base.
String sText = "";
Integer iValue;
String sValue = "";

/* -- convert from dec */

sValue = Integer.toString( 100, 10 );
sText += "dec(100) to dec =" + sValue + "\n";

sValue = Integer.toString( 100, 2 );
sText += "dec(100) to bin =" + sValue + "\n";

sValue = Integer.toString( 100, 16 );
sText += "dec(100) to hex =" + sValue + "\n";

sValue = Integer.toString( 255, 16 );
sText += "dec(255) to hex =" + sValue + "\n";

/* result */

memo.setText( sText );      
Output:

JAVA - How convert binary, hex, octal etc. to decimal

For converting from binary, hexadecimal, octal etc. to decimal, use function Integer.parseInt(). Second parameter is base.
String sText = "";
Integer iValue;
String sValue = "";
    
/* -- convert to dec */
    
iValue = Integer.parseInt( "100", 10 );    
sText += "dec(100) to dec = " + iValue.toString() + "\n";
    
iValue = Integer.parseInt( "100", 2 );    
sText += "bin(100) to dec = " + iValue.toString() + "\n";

iValue = Integer.parseInt( "100", 16 );    
sText += "hex(100) to dec = " + iValue.toString() + "\n";

iValue = Integer.parseInt( "ff", 16 );    
sText += "hex(ff) to dec = " + iValue.toString() + "\n";

/* result */
    
memo.setText( sText );      
Output:

Thursday, March 8, 2018

DELPHI - How get angle between points

This function:
{ ---------------------------------------------------------------------------
  Function calculate angle between points _iSource and _iDest in direction
  from _iSource.

  Returns value [0-360]. 
  -------------------------------------------------------------------------- }
function CalcAngle( _iSource, _iDest : TPoint ) : double;
var
  iAngle : extended;
begin
  iAngle := ArcTan2( _iSource.Y - _iDest.Y, _iSource.X - _iDest.X );

  result := ( iAngle * 180 ) / pi;

  { greater than 180 is negative -> change it }
  if result < 0 then
    result := 180 + ( 180 + result );
end;
And here is calling examle:
procedure TForm1.Button2Click(Sender: TObject);
var
  pt1, pt2, pt3, pt4 : TPoint;
  iResult1, iResult2, iResult3 : double;
begin
  pt1.x := 50; pt1.y := 50;
  pt2.x := 100; pt2.y := 100;
  pt3.x := 150; pt3.y := 50;
  pt4.x := 100; pt4.y := 150;

  iResult1 := CalcAngle( pt2, pt1 );
  iResult2 := CalcAngle( pt2, pt3 );
  iResult3 := CalcAngle( pt2, pt4 );

  ShowMessage( iResult1.ToString() + ' , ' + iResult2.ToString() + ' , ' + iResult3.ToString );
end;
Output:

DELPHI - How to call (old) BDE stored procedure

Note: orcl is TDatabase with connection.
var
  pStoredProc : TStoredProc;
begin
  try

    pStoredProc := TStoredProc.Create( Application );
    pStoredProc.DatabaseName := orcl.DataBaseName;

    { -- call stored procedure }

    pStoredProc.StoredProcName := AnsiUpperCase(  'factory.an_p1.Sql_Standard_DeleteDay' );
    pStoredProc.Prepare;
    pStoredProc.Params.ParamByName( 'pPeriod' ).AsString := _sPeriod;
    pStoredProc.ExecProc;

  finally
    pStoredProc.Free;
  end;

Wednesday, March 7, 2018

DELPHI - How add item (with more columns) to TListView

var
  pItem : TListItem;
begin
  ...
  Item := List.Items.Add;
  pItem.ImageIndex := -1;
  pItem.Caption    := IntToStr( iCounter );
  pItem.SubItems.Add( s.Name );
  pItem.SubItems.Add( IntToStr( iRowCount ) );
  pItem.SubItems.Add( IntToStr( iRowCountSum ) );
Output:

Friday, March 2, 2018

DELPHI - How get row count in text file

var
  iRowCount : integer;
  F : TextFile;
begin
  ...
  { read row count }
  try
    iRowCount := 0;

    AssignFile( F, EPath.Text + s.Name );
    Reset( F );
    while not EOF( F ) do
      begin
        readln( F, sText );
        inc( iRowCount );
      end;   
     ...       
   finally
     CloseFile( F );
   end;
...

DELPHI - How to make cubic B-Spline interpolation + Cox-De-Boora algorithmus

const giCount = 15;
      giDetail = 10;
...
procedure TForm1.BSpline_Clamped;
var
  j, i, iIndex : integer;
  iNumSegmentsCount : integer;
  iT, i1_T, iB0, iB1, iB2, iB3 : double;
  iNewX, iNewY : double;
  pSerieLine : TLineSeries;
  iValue : extended;
begin

  pSerieLine := TLineSeries( Chart1.Series[1] );
  pSerieLine.Clear;

  { draw count of cubic curves }
  for iIndex := -2 to px.Count - 2 do
    begin

       { every curve generate from I parts }
       for i := 0 to giDetail - 1 do
         begin
           iT := i / (giDetail-1);
           i1_T := 1 - iT;

           iB0 := ( i1_T * i1_T * i1_T ) / 6;
           iB1 := ( ( 3 * iT * iT * iT ) - ( 6 * iT * iT ) + 4 ) / 6;
           iB2 := ( ( -3 * iT * iT * iT ) + ( 3 * iT * iT ) + ( 3 * iT ) + 1 ) / 6;
           iB3 := ( iT * iT * iT ) / 6;

           iNewX := iB0 * GetValueX( iIndex ) +
                    iB1 * GetValueX( iIndex + 1 ) +
                    iB2 * GetValueX( iIndex + 2 ) +
                    iB3 * GetValueX( iIndex + 3 )
                    ;

           iNewY := iB0 * GetValueY( iIndex ) +
                    iB1 * GetValueY( iIndex + 1 ) +
                    iB2 * GetValueY( iIndex + 2 ) +
                    iB3 * GetValueY( iIndex + 3 );

           pSerieLine.AddXY( iNewX, iNewY );
         end;
  end;
  
end;