Exemple #1
0
  @BeforeClass
  public static void initDriver() throws Exception {
    Class.forName("org.postgresql.Driver");

    PoolingDataSource ds = new PoolingDataSource();
    ds.setServerName(System.getProperty("pgsql.server", "localhost"));
    ds.setPortNumber(Integer.parseInt(System.getProperty("pgsql.port", "5432")));
    ds.setDatabaseName(System.getProperty("pgsql.db", "test"));
    ds.setUser(System.getProperty("pgsql.user", "test"));
    ds.setPassword(System.getProperty("pgsql.password", "test"));
    ds.setMaxConnections(10);
    ds.setInitialConnections(1);
    try {
      ds.initialize();
    } catch (Exception e) {
      skipTests = true;
      System.out.println("Failed to initialize datasource. Tests will be skipped ...");
      e.printStackTrace();
      return;
    }

    datasource = ds;

    schema = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8);
    schema_two = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8);
    if (schema.compareTo(schema_two) > 0) {
      String tmp = schema;
      schema = schema_two;
      schema_two = tmp;
    }
    // first
    try {
      cleanup();
    } catch (Exception e) {
      // ignore
    }

    // create schema for the test
    try (Connection c = datasource.getConnection()) {
      try (CallableStatement s = c.prepareCall("create schema " + schema)) {
        s.execute();
      }
      try (CallableStatement s = c.prepareCall("create schema " + schema_two)) {
        s.execute();
      }
    }

    createTables();
    insertData();
  }
Exemple #2
0
  public static void createTables() throws Exception {
    try (Connection c = datasource.getConnection()) {

      try (PreparedStatement ps =
          c.prepareStatement(
              "create table "
                  + schema
                  + ".addresses ("
                  + "address_id integer PRIMARY KEY, "
                  + "name varchar (255) NOT NULL, "
                  + "street varchar (255) NOT NULL, "
                  + "postcode varchar (10), "
                  + "city varchar (60) NOT NULL, "
                  + "country_iso char(2), "
                  + "is_company boolean default false)")) {
        ps.execute();
      }

      try (PreparedStatement ps =
          c.prepareStatement(
              "create table "
                  + schema
                  + ".orders ("
                  + "order_id varchar (40) PRIMARY KEY, "
                  + "create_date timestamp NOT NULL, "
                  + "total int8 NOT NULL, "
                  + "address_id integer NOT NULL, "
                  + "FOREIGN KEY (address_id) REFERENCES "
                  + schema
                  + ".addresses (address_id))")) {
        ps.execute();
      }

      try (PreparedStatement ps =
          c.prepareStatement(
              "create table "
                  + schema_two
                  + ".orders ("
                  + "order_id varchar (40) PRIMARY KEY, "
                  + "create_date timestamp NOT NULL, "
                  + "total int8 NOT NULL, "
                  + "address_id integer NOT NULL, "
                  + "FOREIGN KEY (address_id) REFERENCES "
                  + schema
                  + ".addresses (address_id))")) {
        ps.execute();
      }
    }
  }
Exemple #3
0
  @AfterClass
  public static void cleanup() throws SQLException {
    if (skipTests()) {
      return;
    }
    // delete schemas for the test
    try (Connection c = datasource.getConnection()) {
      try (CallableStatement s = c.prepareCall("drop schema " + schema_two + " cascade")) {
        s.execute();
      }

      try (CallableStatement s = c.prepareCall("drop schema " + schema + " cascade")) {
        s.execute();
      }
    }
  }
Exemple #4
0
  public static void insertData() throws Exception {
    try (Connection c = datasource.getConnection()) {
      try (PreparedStatement ps =
          c.prepareStatement("insert into " + schema + ".addresses VALUES (?,?,?,?,?,?,?)")) {
        insertAddress(ps, 1, "John F. Doe", "Liveoak street 7", null, "London", "UK", false);
        insertAddress(
            ps, 2, "Lombaas Inc.", "Liveoak square 1", "94114", "San Francisco", "US", true);
      }

      try (PreparedStatement ps =
          c.prepareStatement("insert into " + schema_two + ".orders VALUES (?,?,?,?)")) {
        insertOrder(ps, "014-1003095", sdf.parse("2014-06-07 15:10:15").getTime(), 18990, 1);
        insertOrder(ps, "014-2004096", sdf.parse("2014-04-02 11:06:12").getTime(), 43800, 2);
        insertOrder(ps, "014-2004345", sdf.parse("2014-06-01 18:06:12").getTime(), 32500, 2);
      }
    }
  }
  //    public JFreeReport makeReport(ReportGenerator generator) throws Exception {
  //
  //        ProductDPanel p = new ProductDPanel();
  //
  //        AUniversalDialog d = new AUniversalDialog(p, null, true);
  //        d.setTitleIcon(new
  // javax.swing.ImageIcon(getClass().getResource("/tradeterminal/icons/srch_32.png")));
  //        d.setVisible(true); d.dispose();
  //
  //        if (d.getReturnStatus() == ADialog.RET_OK) {
  //
  //            Product1DataAdapter tm = new Product1DataAdapter(Setup.getSource(),
  // p.getBeginDate(), p.getEndDate());
  //
  //
  //            JFreeReport report =
  // generator.parseReport(getClass().getResource("/tradeterminal/reports/product1/product.xml"));
  //
  //            TextElement t1 = LabelElementFactory.createLabelElement(
  //                    "edqwe", new Rectangle2D.Double(10, 80, 200.0, 20.0), Color.black,
  // ElementAlignment.LEFT, null,
  //                    "За период с " + df.format(p.getBeginDate()) + " по " +
  // df.format(p.getEndDate()) + ".");
  //
  //            t1.setFontSize(10);
  //            t1.setBold(true);
  //
  //            report.getReportHeader().addElement(t1);
  //
  //            report.setData(tm);
  //
  //            return report;
  //        } else {
  //            return null;
  //        }
  //
  //    }
  @Override
  public JRViewer makeReport(PGPoolingDataSource source) throws Exception {
    TTRProductDPanel p = new TTRProductDPanel();

    AUniversalDialog d = new AUniversalDialog(p, null, true);
    d.setTitleIcon(
        new javax.swing.ImageIcon(
            getClass().getResource("/tradeterminal/icons/TT_icons/32X32/reports/12.png")));
    d.setVisible(true);
    d.dispose();

    if (d.getReturnStatus() == ADialog.RET_OK) {
      //
      //            ADBProc proc = new ADBProc("rpt_prod_select_moving");
      //            proc.addInParametr(
      //                    new ADBProcParametr(Types.DATE,
      //                    p.getBeginDate()));
      //
      //            proc.addInParametr(
      //                    new ADBProcParametr(Types.DATE,
      //                    p.getEndDate()));
      //
      //            //PADBUtils.executeVoidProcedure(Setup.getSource(), proc);
      //
      //            PADBResult result = PADBUtils.getResultSet(source, proc);

      PreparedStatement ptmt =
          source
              .getConnection()
              .prepareStatement(
                  "select "
                      + "pg.id as groups_id, "
                      + "pg.\"name\" as groups_name, "
                      + "p.id as products_id, "
                      + "p.\"name\" as products_name, "
                      + "p.scod as scod, "
                      + "p.list_price as list_price, "
                      + "ppl.quantity as ppl_quantity, "
                      + "ppl.summ as ppl_summ, "
                      + "pmn.quantity as pmn_quantity, "
                      + "pmn.summ as pmn_summ, "
                      + "psl.quantity as psl_quantity, "
                      + "psl.summ as psl_summ, "
                      + "prt.quantity as prt_quantity, "
                      + "prt.summ as prt_summ ,"
                      + "select_products_quantity_for_id(p.id) as products_quantity "
                      + "from products_groups pg, (select DISTINCT v.products_id as id , p.\"name\", p.products_groups_id, p.scod as scod, p.list_price as list_price "
                      + "from v_operations v, products p "
                      + "where products_id is not null AND v.products_id=p.id AND date(order_date) BETWEEN ? and ?) p "
                      + "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
                      + "from v_operations where operation_type_code='ppl' AND date(order_date) BETWEEN ? and ? "
                      + "group by operation_type_code,products_id ) ppl ON (p.id=ppl.products_id) "
                      + "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
                      + "from v_operations where operation_type_code='pmn' AND date(order_date) BETWEEN ? and ? "
                      + "group by operation_type_code,products_id ) pmn ON (p.id=pmn.products_id) "
                      + "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
                      + "from v_operations where operation_type_code='psl' AND date(order_date) BETWEEN ? and ? "
                      + "group by operation_type_code,products_id ) psl ON (p.id=psl.products_id) "
                      + "LEFT OUTER JOIN(select operation_type_code,products_id,sum(quantity) as quantity,sum(actual_price * quantity) as summ "
                      + "from v_operations where operation_type_code='prt' AND date(order_date) BETWEEN ? and ? "
                      + "group by operation_type_code,products_id ) prt ON (p.id=prt.products_id) "
                      + "where pg.id = p.products_groups_id;");

      ptmt.setObject(1, p.getBeginDate(), Types.DATE);
      ptmt.setObject(2, p.getEndDate(), Types.DATE);

      ptmt.setObject(3, p.getBeginDate(), Types.DATE);
      ptmt.setObject(4, p.getEndDate(), Types.DATE);

      ptmt.setObject(5, p.getBeginDate(), Types.DATE);
      ptmt.setObject(6, p.getEndDate(), Types.DATE);

      ptmt.setObject(7, p.getBeginDate(), Types.DATE);
      ptmt.setObject(8, p.getEndDate(), Types.DATE);

      ptmt.setObject(9, p.getBeginDate(), Types.DATE);
      ptmt.setObject(10, p.getEndDate(), Types.DATE);

      ResultSet rs = ptmt.executeQuery();

      JasperReport jasperReport =
          JasperCompileManager.compileReport(
              getClass()
                  .getResource("/tt/reports/product_movong_a3/prod_obor_report.jrxml")
                  .openStream());

      HashMap map = new HashMap();
      map.put(
          "TextRepParam",
          "За период с " + df.format(p.getBeginDate()) + " по " + df.format(p.getEndDate()) + ".");

      JasperPrint jasperPrint =
          JasperFillManager.fillReport(jasperReport, map, new JRResultSetDataSource(rs));

      rs.close();
      ptmt.close();

      return new JRViewer(jasperPrint);
    } else {
      return null;
    }
  }
Exemple #6
0
 public Connection getConnection() throws SQLException {
   return source.getConnection();
 }