Brooklyn Campus Library - LIU, LLC x Ogbonnayajrakpara, LLC

1)
select count (username)
from doorlog
where doorid = '1' and event = 'E';

2)
select count (*)
from doorlog
where doorid = '2';

3)
select count (*)
from doorlog
where (doorid = '1' and  doorid = '3') and (event = 'E' and event = 'X');

4)
select count (*)
from doorlog
where (tim >= '0704:10:00' and tim < '0705:10:00') and (event = 'E')

5)
select doorid
from doorlog
where doorid = '7' and tim = '2017'
group by occupancy, day

6)
select avg, STDEV
from doorlog
where doorid = '7' and tim = '2017'
group by occupancy

7)
select sum (case when doorid= '7' then count(*) else 0 end) / sum (case when doorid >= '7' and doorid <= '8' then count(*) else 0 end) * 100.0 percent

from doorlog
where doorid = '7' and doorid= '8'
group by username

8)
select avg (cnt)
from  (select doorid, count (*) cnt from day a natural inner join dayitem b
where doorid = '2'
group by doorid a

9)
select sum (case when username then count(*) else 0 end) / sum (case when username then count(*) else 0 end) * 100.0 percent

from doorlog a
where tim >= '20170703:5:15' and tim < '20170704'

10)
select a.usename
from doorlog a
where tim <= '20170703:1:00'








public static void main(String[] args) throws IOException {
    List<Path> paths = Arrays.asList(Paths.get("c:/temp/file1.csv"), Paths.get("c:/temp/file2.csv"));
    List<String> mergedLines = getMergedLines(paths);
    Path target = Paths.get("c:/temp/merged.csv");
    Files.write(target, mergedLines, Charset.forName("UTF-8"));
}

private static List<String> getMergedLines(List<Path> paths) throws IOException {
    List<String> mergedLines = new ArrayList<> ();
    for (Path p : paths){
        List<String> lines = Files.readAllLines(p, Charset.forName("UTF-8"));
        if (!lines.isEmpty()) {
            if (mergedLines.isEmpty()) {
                mergedLines.add(lines.get(0)); //add header only once
            }
            mergedLines.addAll(lines.subList(1, lines.size()));
        }
    }
    return mergedLines;
}





import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class CSVReadLoginPass {

 public static  Map<String,String> map1 = null;
 public static  Map<String,String> map2 = null;
  public static void main(String[] args) {

      try
      {
          readFileandPopulate();
          for (Map.Entry<String, String> entry : map1.entrySet()) {
                System.out.println("Key : " + entry.getKey() + " Value : "
                    + entry.getValue()+" address :"+map2.get(entry.getKey()));
                //insert into DB
            }
            }catch (Exception e){//Catch exception if any
                e.printStackTrace();
          System.err.println("Error: " + e.getMessage());
          }

  }
  public static void readFileandPopulate() throws Exception
  {


      FileInputStream fstream = new FileInputStream("E:\\Eclipse-Leo\\StackOverflow\\src\\resources\\textfile1.csv");
      DataInputStream in = new DataInputStream(fstream);
      BufferedReader br = new BufferedReader(new InputStreamReader(in));
      String strLine;
      map1 = new HashMap<String,String>();
          while ((strLine = br.readLine()) != null)   {
              System.out.println(strLine);
          String temp[] = strLine.split(",");
          map1.put(temp[0], temp[1]);

      }
     in.close();
     System.out.println("done 1");


     FileInputStream fstream2 = new FileInputStream("E:\\Eclipse-Leo\\StackOverflow\\src\\resources\\textfile2.csv");
      DataInputStream in2= new DataInputStream(fstream2);
      BufferedReader br2 = new BufferedReader(new InputStreamReader(in2));
      String strLine2;
          map2