Thursday, April 2, 2020

SQL SERVER - How to align string length to fixed size

With left() function is problem because ending spaces are lost. The solution is little trick with space() function.
select top 1 left( itemid, 25 ) + 'x' from inventitemprice
where
itemid = 'S-144-700-1006'

select top 1 left( itemid + space(100), 25 ) + 'x' from inventitemprice
where
itemid = 'S-144-700-1006'
Output:
--------------------------
S-144-700-1006x

(1 row(s) affected)


--------------------------
S-144-700-1006           x

(1 row(s) affected)

Monday, March 9, 2020

C# - How call stored proc with output/return parameter ?

public static string GetStatistics() {
  string value = "";

  SqlCommand command = DB.SPPrepare( "GetStatisticsAsString" );
            
  command.Parameters.Add( new SqlParameter( "@catalog", "HYG" ) );
  command.Parameters.Add( new SqlParameter( "@catalogDSO", "MYDSO" ) );
  SqlParameter par = new SqlParameter( "@output", System.Data.SqlDbType.NVarChar, 2048 );
  par.Direction = System.Data.ParameterDirection.Output;
  command.Parameters.Add( par );

  command.ExecuteNonQuery();

  value = (string)command.Parameters[ "@output" ].Value;

  return value;
}

Monday, March 2, 2020

C# - How to use generic Min(), Max()

shape.Saved_Width =
 Math.Abs( shape.Points.Max( item => item.Draw_X )-shape.Points.Min( item => item.Draw_X ) );

Friday, February 28, 2020

WIN OS - How to flush your machine DNS cache ?

ipconfig /flushdns

AX - How to make sorting in grid column

Override datasource Init() method:
public void init()
{
    super();

    this.query().dataSourceNo( 1 ).addSortField( fieldNum( CIEB_Torque, cieb_datetime ), 
      SortOrder::Ascending );
}
Output:

Friday, January 31, 2020

SQL SERVER - How to make cumulative sum column

If you need cumulative sum if SQL select, you can use analytical function:
select  cast( mag as int ) as mag, count(*) as count,
sum ( count(*) ) over 
( order by cast( mag as int ) rows between unbounded preceding and current row ) 
as count_cumul
from dso
where
catalog = 'MYDSO'
group by cast( mag as int )
order by cast( mag as int )
Output:
mag         count       count_cumul
----------- ----------- -----------
0           5           5
1           5           10
2           7           17
3           13          30
4           26          56
5           45          101
6           88          189
...

Tuesday, January 14, 2020

C# - How to sort generic list by item value

private List<ConstellationIndex> ConstellationsIndex = new List<ConstellationIndex>();
...
this.ConstellationsIndex.Sort( (x, y) => x.Constellation.CompareTo( y.Constellation ) );

Thursday, January 9, 2020

C# - How to go through files in directory with files copy

DirectoryInfo dir = new DirectoryInfo( this.OverviewMapCopyFromDir );

foreach( FileInfo file in dir.GetFiles( "overview_*.tif" ) ) {
  File.Copy( file.FullName, Path.Combine( m_FullOutputDirectory, file.Name ) );    
}

Friday, January 3, 2020

C# - How to convert first character of string to upper letter

The problem is little complicated, because access to string chars (through indexer) is only read-only.
One way is this:

row.Constellation = arr[2].Trim().ToLower();
char[] ch = row.Constellation.ToCharArray();
ch[0] = char.ToUpper( row.Constellation[0] );
row.Constellation = new string( ch );

Sunday, December 22, 2019

SQL SERVER - How to get min/max value from more values

declare @ra_min float = ( select min(x) from ( values ( @ra_from ), ( @ra_to ) ) as n(x) );
declare @ra_max float = ( select max(x) from ( values ( @ra_from ), ( @ra_to ) ) as n(x) );

Tuesday, December 3, 2019

SQL SERVER - How to generate more rows from one row (with count limited by source row value)

Suppose this situation, I have this query that returns two rows.
select a.prodid, a.itemid, a.qtycalc from prodtable a
where 
a.prodid in ( '19-047442', '19-047441' ) 
Output:
prodid               itemid                                   qtycalc
-------------------- ---------------------------------------- --------------------
19-047441            5801-989-012_1138-00_lak                 4.0000000000000000
19-047442            5801-989-012_1138-00_lak                 12.0000000000000000

(2 row(s) affected)
Now I want to make "exemplar query" - for every piece in PRODID to generate one row, so total 16 rows.

How to make it ? One variant is here:
with cte as (
select row_number() over ( order by itemid ) AS rownum from inventtable )

select n.rownum, a.prodid, a.qtycalc
from prodtable a
cross join 
 ( select * from cte 
   where 
   rownum < 999 ) n
where 
a.prodid in ( '19-047442', '19-047441' ) and
a.prodpoolid = 'SED_CES_ST' and
a.prodstatus = 4 and
n.rownum <= a.qtycalc
Output:
rownum               prodid               qtycalc
-------------------- -------------------- ---------------------
1                    19-047441            4.0000000000000000
2                    19-047441            4.0000000000000000
3                    19-047441            4.0000000000000000
4                    19-047441            4.0000000000000000
1                    19-047442            12.0000000000000000
2                    19-047442            12.0000000000000000
3                    19-047442            12.0000000000000000
4                    19-047442            12.0000000000000000
5                    19-047442            12.0000000000000000
6                    19-047442            12.0000000000000000
7                    19-047442            12.0000000000000000
8                    19-047442            12.0000000000000000
9                    19-047442            12.0000000000000000
10                   19-047442            12.0000000000000000
11                   19-047442            12.0000000000000000
12                   19-047442            12.0000000000000000

(16 row(s) affected)

Thursday, November 21, 2019

C# - How combine path with file name without problem

..function care about suitable separator:
string sFileName = Path.Combine( PIFiles.GetExePath(), "file.dat" );

MessageBox.Show( sFileName );
Output:

C# - List.AddRange() - how copy one List object to another in one row

I have two List:
List<ObjectCoordinateBase> AllObjects;

List<InfoStar> Rows;
When I want copy objects from InfoStar class to ObjectCoordinate class, I can use List.AddRange() method.
Node: Import is class, that contains Rows.
Map.AllObjects.AddRange( import.GetAsObjectCoordinateBase() );
And here:
public IEnumerable<ObjectCoordinateBase> GetAsObjectCoordinateBase() {
      
  List<ObjectCoordinateBase> list = new List<ObjectCoordinateBase>();

  this.Rows.ForEach( item => { 
      
    list.Add( new ObjectCoordinateBase( item ) );
      
  } );
      
  return list;
}

Tuesday, November 12, 2019

C# - How to create yes-no question

..last parameter is default button (=second button in dialog).
if ( MessageBox.Show( this, "All data will be deleted. Continue ?",
     "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, 
     MessageBoxDefaultButton.Button2 ) == DialogResult.No ) return;
Output:

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;