Friday, October 4, 2019

C# - How to join two LINQ dataset

This example join COUNTRY with CONTINENT; and shows CONTINENT where exists COUNTRY with population greater than 300.

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

/* -- join */

List listContinent = new List();

listContinent.Add( new Continent( "europe", 1 ) );
listContinent.Add( new Continent( "america", 2 ) );
listContinent.Add( new Continent( "asia", 3 ) );

var list = ( from rowCountry in listCountry join rowContinent in listContinent
             on rowCountry.ContinentName equals rowContinent.ContinentName
             where
             rowCountry.Population > 300
             select new {
               Name = rowCountry.Name,                     
               RelationField = rowContinent.RelationField
             } );

foreach ( var row in list ) 
  Console.WriteLine( 
    string.Format( "ContinentName = {0} RelationField={1} ", row.Name, row.RelationField ) );
      
Console.WriteLine();
Output:
ContinentName = usa RelationField=2 
ContinentName = china RelationField=3 

Thursday, August 22, 2019

C# - How to use IEnumerable interface with LINQ

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

/* -- IEnumerable -> list of europe items */

IEnumerable<country> europeList = 
  from row in listCountry where row.ContinentName == "europe" select row;

foreach ( Country row in europeList ) Console.WriteLine( row.Name );

Console.WriteLine();
Output:
czech
germany
france

Wednesday, August 21, 2019

C# - How to get sum for field in LINQ, where condition, with both syntax variants

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

double value;

/* -- sum - query syntax variant */

value = ( from row in listCountry
          where row.ContinentName == "europe" 
          select row.Population ).Sum();

Console.WriteLine( string.Format( "Sum of population, europe (variant 1) = {0}", value ) );

/* -- sum - fluent syntax variant */

value = listCountry.Where( w => w.ContinentName == "europe" ).Sum( x => x.Population );

Console.WriteLine( string.Format( "Sum of population, europe (variant 2) = {0}", value ) );
Output:
Sum of population, europe (variant 1) = 130
Sum of population, europe (variant 2) = 130

C# - How to get sum for field in LINQ with both syntax variants

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

double value;

/* -- sum - query syntax variant */

value = ( from row in listCountry                      
          select row.Population ).Sum();

Console.WriteLine( string.Format( "Sum of population(variant 1) = {0}", value ) );

/* -- sum - fluent syntax variant */

value = listCountry.Sum( x => x.Population );

Console.WriteLine( string.Format( "Sum of population (variant 2) = {0}", value ) );
Output:
Sum of population(variant 1) = 1680
Sum of population (variant 2) = 1680

Monday, August 5, 2019

AX - How to work with time zone

This example shows actual hour:

First row is actual time; second row is shifted to user predefined time zone; third row is shifted to specific fixed time zone.
info( int2str( DateTimeUtil::hour( DateTimeUtil::getSystemDateTime() ) ) );
    
info( int2str( DateTimeUtil::hour( 
  DateTimeUtil::applyTimeZoneOffset( DateTimeUtil::getSystemDateTime(), 
  DateTimeUtil::getUserPreferredTimeZone() ) ) ) );

info( int2str( DateTimeUtil::hour( 
  DateTimeUtil::applyTimeZoneOffset( DateTimeUtil::getSystemDateTime(), 
  Timezone::GMTPLUS0100_AMSTERDAM_BERLIN_BERN_ROME ) ) ) );
Output:
7
9
9

Friday, May 3, 2019

JAVAFX - How to fill LineChart

This LineChart has X String axis and Y Number axis.
@FXML LineChart Chart;
...
/* when is TRUE, is problem with X axis labels */
Chart.setAnimated( false );
  
/* -- get axis */
  
CategoryAxis bottomAxis = (CategoryAxis) Chart.getXAxis();
Axis leftAxis = Chart.getYAxis();
  
/* -- set axis title */
  
bottomAxis.setLabel( "Bottom axis" );
leftAxis.setLabel( "Left axis" );
  
/* -- create series */
  
/* first */
Series serie = new XYChart.Series();
serie.setName( "First" );
  
serie.getData().add( new XYChart.Data( "Jan", 23 ) );
serie.getData().add( new XYChart.Data( "Feb", 32 ) );
serie.getData().add( new XYChart.Data( "Mar", 50 ) );
  
/* second */
Series serie2 = new XYChart.Series();
serie2.setName( "Second" );
  
serie2.getData().add( new XYChart.Data( "Jan", 100 ) );
serie2.getData().add( new XYChart.Data( "Feb", 72 ) );
serie2.getData().add( new XYChart.Data( "Mar", 20 ) );

/* remove point symbols */
Chart.setCreateSymbols(false);
  
/* add series to chart */
Chart.getData().clear();
Chart.getData().add( serie );
Chart.getData().add( serie2 );
Output:

Monday, March 25, 2019

SQL SERVER - How to get end of month date

select eomonth( getdate(), -1 )
select eomonth( getdate() )    
select eomonth( getdate(), 1 )
Output:
----------
2019-02-28

(1 row(s) affected)


----------
2019-03-31

(1 row(s) affected)


----------
2019-04-30

(1 row(s) affected)

Tuesday, March 19, 2019

DELPHI - How to go through all rows in dataset with bookmark

Bookmarks store your position in dataset and  after action you can return back to original record.
var
  iCount : integer;
  pBookmark : TBookmark;
  pDataSetSource : TDataSet;
begin
  ...
  Screen.Cursor := crHourGlass;
  iCount := 1;
  pBookmark := pDatasetSource.GetBookmark;
  pDatasetSource.DisableControls;
  try

    pDatasetSource.First;
    while not pDatasetSource.Eof do
      begin
        try

          { action with record }

        finally
          inc( iCount );
          pDatasetSource.Next;
        end;

      end;
  finally
    pDatasetSource.GotoBookmark( pBookmark );
    pDatasetSource.EnableControls;

    Screen.Cursor := crDefault;
  end;

DELPHI - How to show enum name as string (TFieldType)

Add System.TypInfo to your uses statement:
var
  i : integer;
  pColumn : TcxGridDBColumn;
begin
  ...
  for i := 0 to pGridView.VisibleColumnCount - 1 do
    begin
      pColumn := TcxGridDBColumn( pGridView.VisibleColumns[i] );

      ShowMessage( pColumn.DataBinding.FieldName + ' , ' + 
                   GetEnumName( TypeInfo(TFieldType), 
                                ord( pColumn.DataBinding.Field.DataType ) ) );
    end;
  ...
Output:

Thursday, March 7, 2019

Thursday, January 31, 2019

JAVA - How to solve REF/OUT method parameters

JAVA doesn`t support REF/OUT method parameters (=parameters that could be changed in method to another value).

But you can define class instance, which can be passed and used as REF:

1) Prepare this help class with one int variable:
public class ParamWrapperInt {
  public int value;
}
2) Use it:
private boolean methodName( ParamWrapperInt _maxMinorDimension ) {
  ...
  /* work with int */
  _maxMinorDimension.value = ...
  ...
  return true;
}

Wednesday, January 30, 2019

WIN OS - How to get IP address from MAC address

"arp -a" command display context of actual ARP table.

You could use this code for find IP address by MAC address:


PS: For refreshing ARP cache you can use:
netsh interface ip delete arpcache

Wednesday, January 9, 2019

AX - How to split string to parts

The field custVendExternalItem.Description contains value "PO885895/C".

For split to strings with "/" delimiter use this code:
List list = new List( Types::String );
ListEnumerator listEnumerator;
int iCounter;
...
/* split field custVendExternalItem.Description */

list = strSplit( custVendExternalItem.Description, "/" );
                                    
listEnumerator = list.getEnumerator();    
iCounter = 1;
while ( listEnumerator.moveNext() ) {                
   if ( iCounter == 1 ) tmpTable.PO_number = listEnumerator.current();
   if ( iCounter == 2 ) tmpTable.revision = listEnumerator.current();
                
   iCounter++;  
}

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 ) );
}