Get Data variation of sonar to create custom excel report

If you are using sonar for your projects and want to generate a excel report.
then you can complete this task by two way,
first one by using web API provided by sonar team
or do R & D with sonar DB(Not a standard way as mention on sonar group dashboard)

First way-
To go with web Api you can use sonar web client,
after that you can use below code to call web service for desired data.


 String url = "http://localhost:9000/";(url of Sonar)
 String login = "root";(Database user name)
 String password = "root";(Database password)
 Sonar sonar = new Sonar(new HttpClient4Connector(new Host(url, login,password)));
 Resource soanrExample= sonar.find(ResourceQuery
            .createForMetrics("projectKey", "statements"));

projectKey=You can get this from projects table or from your pom its combination of
                     <groupId>:<artifactId>

Measure statements = soanrExample.getMeasure("statements");
    System.out.println("statements : " + statements.getMetricKey()
            + " === " + statements.getFormattedValue());
like "statments" you can also pass "test" "coverage",minor_violations etc

Second Way-

As I am facing issue with variations I am not able to get variation data through web API(which shows in bracket 73 statements(+4))

So for this I did some work on DB part.
These are some steps which I followed to get variation data

Step1-
Get Project key with the help of WEB API(or through DB from projects table)

Step2-
Get project_id from projects table with the help of project key

Step3-
Get period1_param,period2_param etc from snapshots table  with the help of project Id,
from here you can get variation period like 15 days or 30 days,Like this

       if(rs.getString("period1_param")!=null){
  if(rs.getString("period1_param").equalsIgnoreCase("7")){
VARIATIONCOUNT7DAYS="1";
 }
 if (rs.getString("period1_param").equalsIgnoreCase("30")) {
VARIATIONCOUNT30DAYS="1";
 }
  }

Step4-
Use this query to generate data per project

select distinct proj.name NAME_OF_PROJ,metric.name metric_name, metric.description Description, projdesc.value value,projdesc.variation_value_1,projdesc.variation_value_2,projdesc.variation_value_3,projdesc.variation_value_4,projdesc.variation_value_5 ,snap.created_at CREATED_DATE from projects proj inner join snapshots snap on snap.project_id=proj.id inner join " + "(select max(snap2.id) as id from snapshots snap2 where snap2.project_id  in ("+projectId+") GROUP BY snap2.project_id ) as Lookup on Lookup.id=snap.id inner join project_measures projdesc on projdesc.snapshot_id=snap.id inner join metrics metric on  projdesc.metric_id =metric.id where  metric.id between '1' and "+metricid_Count+'

This query gives you all required data with the variation value,
You can put additional logic  to get compare variation for different period,

Hope this help any one.
Let me know if I miss anything

Windows batch stuff

Today I am going to explain  some important  stuffs of windows batch,which may help you to do some batch programming

1)-First one is related to admin permission,Generally this is the first step of any batch file program.
To accomplish this task I am showing you to simplest way,
you can check for user net session like

    net session >nul 2>&1
    if %errorLevel% == 0 (
        echo Success: Congrats, You have administrative permissions.
        echo. 
       ) else (
        echo Failure: Current permissions inadequate. Please contact your NetOps Team
        timeout 10 >nul
        exit
    )
  
This will check user right

2)-To put blank line between two statement
use   echo.

3)-To check user OS version
If you want to get user OS version means user using 64 bit or 32 bit then you can use this statement

IF EXIST "%PROGRAMFILES(X86)%" (set bit=64) ELSE (set bit=32)
 if %bit%==64 ( echo user have 64 bit operating system) else (  echo user have 32 bit operating system)


 4)-To get first sub-directory name
Assume you have any directory name A and in this directory you have 3 more sub directory name as B,C,D and you want to get name of first listing directory then you can use this  stuff,In my program I used this stuff to update java path,
it will search sub directory  under "C:\Program Files\Java" and set first directory name in javaPath variable,then you can use this variable where you want

for /f "delims=" %%F in ('dir "C:\Program Files\Java" /b /o-n') do set javaPath=%%F
          setx PATH "%PATH%;%javaPath%\bin" /M

5)-To check java is already installed or not
I think for this task registry is best option.

for /f tokens^=2^ delims^=^" %%i in ('reg query HKEY_CLASSES_ROOT\jarfile\shell\open\command /ve 2^>nul') do set javaInstallpath=%%i
 
 Set javaString=Java
 
Echo.%javaInstallpath% | findstr %javaString%>nul && (
    echo It seems java is already installed on your system,
    ) || (
   echo.
   echo It seems Java is not installed on your system.
   )



2^>nul-Its a optional one ,you can remove this from code,Its used to hide the error message ,if it fails to get java instance on given location


6)-Extraction of zip file using 7-Zip
If you don't want to use vb script you can third party tool for your purpose,
I hope you have 7-Zip in c drive,Then in batch file you can use these three line to extract required zip at required destination,
You can see I have used %~dp0 it have the path of your batch file directory,means if your batch file is in d:\test\batch\testBatch.bat then %~dp0 have path upto d:\test\batch
 c:
cd C:\Program Files\7-Zip
7z x "%~dp0\devTool.zip" -o%~dp0\ *.* -r

7)-Downloading software from http location-In my view the best and simple way to accomplish this one is to use power shell
You just need to put 2  line of code in you batch file to download any software or file,it use one ps file you need to provide this file in addition

psFileToDownload.ps1--copy and paste these three line in txt  file and save it as
psFileToDownload.ps1

param($url, $filename)
$client = new-object System.Net.WebClient 
$client.DownloadFile( $url, $filename)

In Batch
Put these two line in batch file

powershell Set-ExecutionPolicy Unrestricted
powershell -ExecutionPolicy RemoteSigned -File %psPath% %downloadLocation% "%~dp0\TestDownload.zip"

psPath--Path  of  psFileToDownload.ps1
downloadLocation=The http location from where you want to download any file
TestDownload.zip=The required file will save as TestDownload.zip

8)-To put sleep between to statement you can use timeout
timeout 10>nul
sleep for 10 sec