JAVA - How check if file exists

import java.io.*;
...
if ( new File( "c:\\test.txt" ).exists() ) System.out.print( "File exists" );

SSRS - How add current timezone name to report

=Globals!ExecutionTime + “ (” + TimeZone.CurrentTimeZone.StandardName + “)”

DELPHI - How enumerate all files in directory

This example enumerate all *.png files in directory (..and get its system date info).

var
  sr : TSearchRec;
  tim : TSystemTime;
  dDate : TDate;
  sMask : string;
begin
  ..
  { mask for .png files only }
  sMask := gcsDrawingDirectory + sFocusedValue + '*' + '.png';

  { find first file, to sr }
  i := FindFirst( sMask , faAnyFile, sr );
  while i = 0 do
    begin
      try
        { here is some action with file -> here get file time }       
 
        FileTimeToSystemTime( FileTime( sr.FindData.ftLastWriteTime) , tim );
        dDate := EncodeDate( tim.wYear, tim.wMonth, tim.wDay );

        { do some action..}

      except
      end;

  { try find next file }  
  i := FindNext( sr );
end;
{ close finding }
FindClose( sr );

SQL SERVER - How create table with constraint on column

This example shows creating table with constraint on column [type]. Allowed values are "I", "E" and "W" (..and null, column is not mandatory).

create table [logs]
( 
  id int not null primary key identity,
  [date] datetime default getdate(),
  /* I=info; E=error, W=warning */
  [type] varchar(1) check ( [type] in ( 'I', 'E', 'W' ) ),
  [description] nvarchar( 1024 ),
  [data_group] varchar( 10 )
)

JAVA - How convent number to string

int to String

int i = 10;
String s = Integer.toString( i );

System.out.println( s );  // write result to Output window

Output:

10

long to String

long l = 456;
s = Long.toString( l );

System.out.println( s );  // write result to Output window

Output:

456

double to String

double d = 5.51;
s = Double.toString( d );

System.out.println( s );  // write result to Output window

Output:

5.51

float to String

float f = 15.55f;
s = Float.toString( f );

System.out.println( s );  // write result to Output window

The output is:

15.55

AX - How change Grid cells colors (conditional formatting)

When you need to change grid cells color you must override form DataSource displayOption() method.


This method has two parameters:

• First is record for every row in grid.
• Second is used for color change.

Here is applied change only for grid`s column ProductivityPercent.

public void displayOption(Common _record, FormRowDisplayOption _options)
{

    DM_ProductivityTable dm_productivityTable;
    real iPercent;

    /* record for every row (currently drawn row), get percent from it */

    dm_productivityTable = _record;

    iPercent = dm_productivityTable.ProductivityPercent;

    /* conditions */

    if ( ( iPercent > 0 ) && ( iPercent < 75 ) ) {
        _options.backColor( WinAPI::RGB2int( 153, 31, 0 ) );
        _options.textColor( WinAPI::RGB2int( 255, 255, 255 ) ) ;
        _options.affectedElementsByControl( DM_ProductivityTable_ProductivityPercent.id() );
    }
    if ( ( iPercent >= 75 ) && ( iPercent < 100 ) ) {
        _options.backColor( WinAPI::RGB2int( 255, 92, 51 ) );
        _options.textColor( WinAPI::RGB2int( 255, 255, 255 ) ) ;
        _options.affectedElementsByControl( DM_ProductivityTable_ProductivityPercent.id() );
    }
    if ( ( iPercent >= 100 ) && ( iPercent < 120 ) ) {
        _options.backColor( WinAPI::RGB2int( 0, 230, 0 ) );
        //_options.textColor( WinAPI::RGB2int( 255, 255, 255 ) ) ;
        _options.affectedElementsByControl( DM_ProductivityTable_ProductivityPercent.id() );
    }
    if ( iPercent >= 120 ) {
        _options.backColor( WinAPI::RGB2int( 0, 128, 0 ) );
        _options.textColor( WinAPI::RGB2int( 255, 255, 255 ) ) ;
        _options.affectedElementsByControl( DM_ProductivityTable_ProductivityPercent.id() );
    }

    /* call */

    super( _record, _options );
}

PS: ..and set Grid.HighlightActive => No.

SQL SERVER - How drop table (with table exists check)

This example shows script for drop table with check if this table exists in DB.

use db;

set nocount on;

if exists( select 'x' from information_schema.tables 
           where
           lower( table_name ) = 'sql_detail_methods'            
         )
begin
  drop table [sql_detail_methods];
end;

SQL SERVER - How create new type (with type exists check)

Sometimes is better make new type. Later you can use it, for example for new column in table, or in stored procedure. Here is created type for nvarchar(25).

use db;
...
if not exists
  ( select 'x' from sys.systypes
    where
    lower( name ) = 'user'
  )
begin
  create type [dbo].[USER] from nvarchar(25);
end;

And using it in create table statement:

create table [users]
( 
  ident [dbo].[USER] not null primary key,
  [name] [dbo].[USER_NAME],
  [active] bit not null default 1
);

JAVA - How to use TreeMap

java.util.TreeMap is map with this behavior:

• Keys can`t be duplicated.
• Elements are sorted by keys.

TreeMap tr = new TreeMap();

/* -- add keys and it values */
    
tr.put( "one", 1 );
tr.put( "two", 2 );
tr.put( "three", 3 );
tr.put( "three", 5 ); // exists already -> ignore
tr.put( "three", 3 ); // exists already -> ignore
tr.put( "four", null ); 

/* elements count */
System.out.println( "Elements count = " + tr.size() );   

/* check if contains key */
System.out.println( "Contains \"one\" = " + tr.containsKey( "one" ) );   

/* get value for key */
System.out.println( "\"one\" value = " + tr.get( "two" ) );   
    
/* -- write all keys and all values */
        
System.out.println( "Keys = " + new ArrayList( tr.keySet() ).toString() );  
System.out.println( "Values = " + new ArrayList( tr.values() ).toString() );

Output:

Elements count = 4
Contains "one" = true
"one" value = 2
Keys = [four, one, three, two]
Values = [null, 1, 3, 2]

See that the order of elements are by the key name. Also count of elements is 4 - two items were omitted.

JAVAFX - How convent numeric to string with locale rules

This example convent double value to String with locale rules (first US, second French, third English):

@FXML TextArea memo;

private void btnActionOnAction(ActionEvent event) {
  double iValue = 1234567.2345;
  String sText;
    
  sText = "US = " + 
    NumberFormat.getNumberInstance( Locale.US ).format( iValue ) + "\n";
  sText += "French = " + 
    NumberFormat.getNumberInstance( Locale.FRENCH ).format( iValue ) + "\n";         
  sText += "English = " + 
    NumberFormat.getNumberInstance( Locale.ENGLISH ).format( iValue ) + "\n";         
    
  memo.setText( sText );    
}

How you can see, US adds thousand separator:

US = 1,234,567.235
French = 1 234 567,235
English = 1,234,567.235

AX - How find sales (purchase) price for item

On Released products in AX 2012 you can define Sales price:

So here is code for its reading:

InventTable inventTable = InventTable::find( '5801-105-535-ND_0_0000' );
PriceDisc priceDisc;
PriceCur iPrice;

if ( inventTable ) {

  /* -- find sales price for item, dimension, unit, date and company */
                
  priceDisc = new PriceDisc( ModuleInventPurchSales::Sales, inventTable.ItemId, null, 'ks', 
                             systemdateget(), 1, 'COMPANY_AG' );

  if ( priceDisc.findPrice( CustTable::find( 'COMPANY_AG' ).PriceGroup ) )
    iPrice = priceDisc.price();
  /*else if ( priceDisc.findItemPrice() )
    iPrice = priceDisc.price();*/           

  info( "Sales price: " + num2str( iPrice, 15, 2, 1, 0 ) );                            
}

Output could be:

Sales price: 20.50

JAVAFX - How show new modal dialog

@FXML
private void btnAboutAction( ActionEvent event ) throws Exception {
 
  /* read form from .fxml */

  FXMLLoader fxmlLoader = new FXMLLoader( getClass().getResource( "FAbout.fxml" ) );

  /* get form */

  Parent form = (Parent) fxmlLoader.load();

  /* set dialog properties */

  Stage stage = new Stage();
  stage.initModality( Modality.APPLICATION_MODAL );
  stage.setResizable( false );
  stage.setTitle( "About" );  

  /* show it in screen center */

  stage.setScene( new Scene( form ) );  
  stage.centerOnScreen();
  stage.show();
}

AX - How get item cost (std) price

For getting cost price use class InventItemPrice. Second parameter is date for cost price,
then are two parameters for dimension. Last parameter tells if throw error, if cost price will not be found.

InventTable inventTable = InventTable::find( '55555-555555_555' );
InventItemPrice inventItemPrice;

if ( inventTable ) {

  /* -- get price for selected date */
        
  inventItemPrice = InventItemPrice::stdCostFindDate( 
                      inventTable, /*systemDateGet()*/
                      mkDate( 31, 1, 2017 ), 
                      '', null, 'COMPANY', false 
                    );

  info( "Price: " + num2str( inventItemPrice.Price(), 15, 2, 1, 0 ) );                            
}

Output could be:

Price: 11879.30

AX - How check if file exists + YesNo dialog

Link for external WIN API function.

FilenameSave filename;
...
if ( WinAPI::fileExists( filename ) )
  if ( ! Box::yesNo( strfmt( "@SYS60148", filename ), DialogButton::No ) )
  {
    info( "Export cancelled." );
    return false;
  }

ORACLE - How remove time part from datetime

Use trunc() function:

delete orders a
where
( select date_ from menu b
  where
  b.id = a.menu_id 
) <= trunc( to_date( '01.01.2017' ) )

AX - How find active BOM version and list its items

Base example shows how to get BOM active version to data, with fromQty=1, no configuration.

InventTable inventTable = InventTable::find( '5801-382-066_0_0200' );
BOMVersion bomVersion;
BOM bom;
    
if ( inventTable ) {
        
  /* -- get active BOM version to date, with fromQty=1, no config */
        
  bomVersion = BomVersion::findActive( inventTable.ItemId, systemdateGet(), 1, null );
        
  if ( bomVersion ) {
            
    /* -- list items and quantity */
            
    while 
    select bom 
    where bom.BOMId == bomVersion.BOMId 
    {            
      info( bom.ItemId + ", " + num2str( bom.BOMQty(), 15, 2, 1, 0 ) + " " + bom.UnitId );
    }
  }

}

Ouput could be:

5801-380-510_0_0000,       1.00 KS
5801-361-927-1_0_0000,     2.00 ks
S-442-900-2347,            1.00 KS
...

AX - How get info about AX user

When you need info about connected user, you can use class UserInfo and xUserInfo:

UserInfo userInfo = xUserInfo::find( false, curUserId() );
    
if ( userInfo ) {
  info( "Name: " + userInfo.name );        
  info( "Language: " + userInfo.language );
  info( "Enabled: " + ( userInfo.enable ? "Yes" : "No" ) );
}

Output:

Name: Aldiss, Brian
Language: en-us
Enabled: Yes

Note: For current user you can use directly:

UserInfo userInfo = xUserInfo::find();

AX - How get info about licence

X++ contains class xSysConfig for providing information about licence. Below code shows info with AX serial number and licence holder.

info( "Licence number: " + xSysConfig::serialnumber() );    
info( "Licence holder: " + xSysConfig::licenseHolder() );    

Output could be:

Licence number: M1218154
Licence holder: Company AG

DELPHI - How add items to TListView

Listview has 2 columns. First column value is added by Caption property, second by SubItems list.

var
  List: TListView;
  pItem : TListItem;
begin
  ..
  { first item }
  pItem := List.Items.Add;
  pItem.Caption := 'Total record count';
  pItem.SubItems.Add( IntToStr( FLocalData.TotalRecordCount ) );
  pItem.ImageIndex := 0;
  pItem.Selected := true;

  { second item }
  pItem := List.Items.Add;
  pItem.Caption := 'Record count';
  pItem.SubItems.Add( IntToStr( FLocalData.RecordCount ) );
  pItem.ImageIndex := 0;
  ..
end;

JAVA - How to use HashMap

java.util.HashMap is map with this behavior:

• Keys can`t be duplicated.
• Order of keys is unpredictable.

HashMap hm = new HashMap();

/* -- add keys and it values */

hm.put( "one", 1 );
hm.put( "two", 2 );
hm.put( "three", 3 );
hm.put( "three", 5 ); // exists already -> ignore
hm.put( "three", 3 ); // exists already -> ignore
hm.put( "four", null );

/* elements count */
System.out.println( "Elements count = " + hm.size() );

/* check if contains key */
System.out.println( "Contains \"one\" = " + hm.containsKey( "one" ) );

/* get value for key */
System.out.println( "\"one\" value = " + hm.get( "two" ) );

/* -- write all keys and all values */

System.out.println( "Keys = " + new ArrayList( hm.keySet() ).toString() );
System.out.println( "Values = " + new ArrayList( hm.values() ).toString() );

The output:

Elements count = 4
Contains "one" = true
"one" value = 2
Keys = [two, one, three, four]
Values = [2, 1, 3, null]

See that the order of elements are different than you have inserted. Also two items were omitted.

ORACLE - How handle pl/sql exception

Typical example for handling pl/sql exception (when no row is found in select):

is
  iCount number;
  /* take type from table field */
  iKategp factory.an_hr_table.kategp%type;
begin
...
  begin
    select kategp into iKategp
    from factory.an_hr_table 
    where
    osc = pOsc;
  exception
    when NO_DATA_FOUND then
      pErrorMessage := 'No data found for worker: ' || pOsc;
      return( -1 );
  end;
...

ORACLE - example of pl/sql function (stored function)

Stored function takes two params and returns number value (0 when row is added):

function sql_addToFavorite( pUser in varchar2, pID in number ) return number
is
  iCount number;
begin
  /* exists ? */

  select count(*) into iCount
  from factory.cieb_an_sql_favorite
  where
  upper( c_user ) = upper( pUser ) and
  c_ID = pID;

  if iCount <> 0 then
    return( 1 );
  end if;

  /* add */

  insert into factory.cieb_an_sql_favorite
  ( c_user, c_id )
  values
  ( upper( pUser ), pID );

  commit;

  return( 0 );
end;

ORACLE - How call stored pl/sql procedure/function

How to call externally pl/sql stored procedure:

declare
  i number;
  sErrorMessage varchar2(255);
begin
  /* form user.package.function */

  i := factory.cieb_inv_nv_p.CallBatchProc( '201611', 11, sErrorMessage );
end;

ORACLE - How to get substring from string

Get 2 chars from position 6:

select substr( 'This is text', 6, 2 ) from dual 

Output:

SU
--
is
1 row selected.

SQL SERVER - How get substring from string

Get 2 chars from position 6:

select substring( 'This is text', 6, 2 ) 

Output:

----
is

(1 row(s) affected)