package mmm;


import java.net.URL;
import java.util.ResourceBundle;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.ListView;
import javafx.scene.control.TextField;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javafx.collections.ObservableList;
import javafx.scene.control.DatePicker;

/**
 *
 * @author Sebastian
 *
 */
public class FXMLDocumentController implements Initializable {

    @FXML
    private TextField nazwa1,nazwa2,nazwa3,nazwa4,nazwa5,nazwa6,nazwa7,nazwa8,nazwa9,nazwa10;
    @FXML
    private TextField im1,im2,im3,im4,im5,im6,im7,im8,im9,im10;
    @FXML
    private TextField ilość1,ilość2,ilość3,ilość4,ilość5,ilość6,ilość7,ilość8,ilość9,ilość10;
    @FXML
    private TextField cena1,cena2,cena3,cena4,cena5,cena6,cena7,cena8,cena9,cena10;
    @FXML
    private TextField VAT1,VAT2,VAT3,VAT4,VAT5,VAT6,VAT7,VAT8,VAT9,VAT10;
    @FXML
    private ListView<String> produkty;
    @FXML
    private TextField rabat1,rabat2,rabat3,rabat4,rabat5,rabat6,rabat7rabat8,rabat9,rabat10;
    @FXML
    private TextField numer,nabywca1,sprzedawca1,adres1,adres2,nip1,nip2;
    @FXML
    private DatePicker terminplatnosci,datawystawienia;



    @FXML
    public void plus1(ActionEvent event) {
        produkty.setVisible(true);
    }
    @FXML
     private ObservableList<ObservableList> data;

     private Connection c;
    private Statement stmt;
    private ResultSet wyniki;
    private ResultSetMetaData meta;
    private int update;
    private String konsola;
public String select(String zapytanie) throws SQLException, ClassNotFoundException {
        String url = "jdbc:sqlite:bazadanychfaktura.db3";
        Class.forName("org.sqlite.JDBC");
        Connection c = DriverManager.getConnection(url);
        Statement stmt = c.createStatement();
        ResultSet wyniki = stmt.executeQuery(zapytanie);
        ResultSetMetaData meta = wyniki.getMetaData();
        String produkty = "";

        for (int i = 0; i < meta.getColumnCount(); i++) {  // Wyswietlanie wynikow
            produkty = produkty + meta.getColumnLabel(i + 1) + "t|t";
        }
        produkty = produkty + "n";

        while (wyniki.next()) {
            for (int i = 0; i < meta.getColumnCount(); i++) {
                produkty = produkty + wyniki.getString(i + 1) + "t|t";
            }
            produkty = produkty + "n";
        }
        return produkty;
    }

    public String update(String zapytanie) throws SQLException, ClassNotFoundException {
        String url = "jdbc:sqlite:bazadanychfaktura.db3";
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection(url);
        stmt = c.createStatement();
        update = stmt.executeUpdate(zapytanie);

        konsola = "Zaktualizowano " + update + " wierszy.";

        return konsola;
    }



    @FXML
    private void anulujAction(ActionEvent event) {
        System.exit(0);

    }

    @FXML
    private void ZatwierdźAction(ActionEvent event) throws ClassNotFoundException, SQLException {


        String  b = ilość1.getText();
        String t = cena1.getText();
        String e = rabat1.getText();
        int d;

           d = Integer.valueOf(b) * Integer.valueOf(t);



        String[][] values
                = {{"1", "" + nazwa1.getText() + "", "" + im1.getText() + "", "" + ilość1.getText() + "", "" + cena1.getText() + "", ""+ rabat1.getText() +"", ""+ d +"", "7", "8"},
                {"2", "" + nazwa2.getText() + "", "" + im2.getText() + "", "" + ilość2.getText() + "", "" + cena2.getText() + "", "5", "6", "7", "8"},
                {"3", "" + nazwa3.getText() + "", "" + im3.getText() + "", "" + ilość3.getText() + "", "" + cena3.getText() + "", "5", "6", "7", "8"},
                {"4", "" + nazwa4.getText() + "", "" + im4.getText() + "", "" + ilość4.getText() + "", "" + cena4.getText() + "", "5", "6", "7", "8"},
                {"5", "" + nazwa5.getText() + "", "" + im5.getText() + "", "" + ilość5.getText() + "", "" + cena5.getText() + "", "5", "6", "7", "8"},
                {"6", "" + nazwa6.getText() + "", "" + im6.getText() + "", "" + ilość6.getText() + "", "" + cena6.getText() + "", "5", "6", "7", "8"},
                {"7", "" + nazwa7.getText() + "", "" + im7.getText() + "", "" + ilość7.getText() + "", "" + cena7.getText() + "", "5", "6", "7", "8"},
                {"8", "" + nazwa8.getText() + "", "" + im8.getText() + "", "" + ilość8.getText() + "", "" + cena8.getText() + "", "5", "6", "7", "8"},
                {"9", "" + nazwa9.getText() + "", "" + im9.getText() + "", "" + ilość9.getText() + "", "" + cena9.getText() + "", "5", "6", "7", "8"},
                {"10", "" + nazwa10.getText() + "", "" + im10.getText() + "", "" + ilość10.getText() + "", "" + cena10.getText() + "", "5", "6", "7", "8"},};

        Creator pdf = new Creator(values, "" + sprzedawca1.getText() + "", nip2.getText(), "" + nip1.getText() + "", "" + numer.getText() + "",
                "" + adres2.getText() + "", "" + nabywca1.getText() + "", "" + terminplatnosci.getValue() + "", "" + adres1.getText() + "", "" + datawystawienia.getValue() + "");
        String update, update2, update3, update4, update5, update6, update7, update8, update9, update10;

        update = "update Magazyn set ilość = (ilość - '" + ilość1.getText() + "')  where Nazwa = '" + nazwa1.getText() + "'";
        update2 = "update Magazyn set ilość = (ilość - '" + ilość2.getText() + "')  where Nazwa = '" + nazwa2.getText() + "'";
        update3 = "update Magazyn set ilość = (ilość - '" + ilość3.getText() + "')  where Nazwa = '" + nazwa3.getText() + "'";
        update4 = "update Magazyn set ilość = (ilość - '" + ilość4.getText() + "')  where Nazwa = '" + nazwa4.getText() + "'";
        update5 = "update Magazyn set ilość = (ilość - '" + ilość5.getText() + "')  where Nazwa = '" + nazwa5.getText() + "'";
        update6 = "update Magazyn set ilość = (ilość - '" + ilość6.getText() + "')  where Nazwa = '" + nazwa6.getText() + "'";
        update7 = "update Magazyn set ilość = (ilość - '" + ilość7.getText() + "')  where Nazwa = '" + nazwa7.getText() + "'";
        update8 = "update Magazyn set ilość = (ilość - '" + ilość8.getText() + "')  where Nazwa = '" + nazwa8.getText() + "'";
        update9 = "update Magazyn set ilość = (ilość - '" + ilość9.getText() + "')  where Nazwa = '" + nazwa9.getText() + "'";
        update10 = "update Magazyn set ilość = (ilość - '" + ilość10.getText() + "')  where Nazwa = '" + nazwa10.getText() + "'";

        Zapytanie z6 = new Zapytanie();
        z6.update(update);
        z6.update(update2);
        z6.update(update3);
        z6.update(update4);
        z6.update(update5);
        z6.update(update6);
        z6.update(update7);
        z6.update(update8);
        z6.update(update9);
        z6.update(update10);

     String z;

      ResultSet  rs = stmt.executeQuery("select ilość from Magazyn where Nazwa = '" + nazwa1.getText() + "'");
        if (rs.first()) {z=rs.getString("typ");
        if
                (z.equals("wartosc"))
           System.out.println("bład");

                    }}
             {



    }

    @Override
    public void initialize(URL url, ResourceBundle rb) {
        ilość1.setText("0");
        cena1.setText("0");
        rabat1.setText("0");
}
}

package mmm;

import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Creator {

    public Creator(String[][] rekordy, String firma, String data,String miasto,String data2,String nip2,String adres,String nabywca ,String nip, String id) {
        try {
            CreateDocument Faktura = new CreateDocument();
            Faktura.CreateDocument(rekordy, firma,miasto,nip,nabywca,adres,nip2 ,id,data,data2);
            File plik;

            plik = new File("C:/faktura.pdf");

            Desktop.getDesktop().open(plik);
        } catch (IOException ex) {
            Logger.getLogger(Creator.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    Creator(String[] string, String string0, String string1, String string2, String string3, String string4, String string5, String string6, String string7) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}

class CreateDocument {

    public Document CreateDocument(String[][] rekordy, String firma, String data, String data2,String nip2,String nabywca,String adres,String miasto, String nip, String id) {
        Document document = new Document(PageSize.A4, 36, 20, 36, 20);


        try {

            PdfWriter.getInstance(document, new FileOutputStream("C:/faktura.pdf"));
            document.open();

            CreateTable obj = new CreateTable();
            Data objSeller = new Data();
            Font font = new Font(Font.FontFamily.COURIER, 12);

            document.add(objSeller.createSeller(firma, nabywca,adres,nip2,data,data2 ,miasto, nip, id));
            document.add(obj.createHeader(id));
            document.add(obj.createData(rekordy));
            //document.add(obj.createResum());
            document.add(obj.createSum());
        } catch (Exception e) {
            // handle exception
        }

        document.close();
        return document;
    }
}

class CreateTable {

    public PdfPTable createHeader(String id) {
        BaseFont bf = null;
        try {
            bf = BaseFont.createFont(BaseFont.TIMES_ROMAN, BaseFont.CP1250, BaseFont.CACHED);
        } catch (DocumentException ex) {
            Logger.getLogger(CreateTable.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(CreateTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        Font f = new Font(bf, 9, Font.NORMAL);

        float[] widths1 = {5, 30, 5, 5, 10, 10, 10, 10, 10};
        PdfPTable table1 = new PdfPTable(widths1);
        table1.setWidthPercentage(100f);
        PdfPCell header = new PdfPCell(new Phrase("Faktura końcowa "+id+""));
        header.setPadding(5);
        header.setColspan(9);
        table1.addCell(header);
        table1.getDefaultCell().setBackgroundColor(BaseColor.LIGHT_GRAY);
        table1.getDefaultCell().setPaddingBottom(3);
        table1.getDefaultCell().setPaddingTop(2);
        table1.getDefaultCell().setPaddingLeft(3);
        table1.getDefaultCell().setPaddingRight(3);
        table1.getDefaultCell().setBorderWidth(1);

        Phrase lp = new Phrase("Lp.", f);
        Phrase nazwa_tow = new Phrase("Nazwa towaru/usługin(SWW/KU)", f);
        Phrase jm = new Phrase("J.m.", f);
        Phrase ilosc = new Phrase("Ilość", f);
        Phrase cena_netto = new Phrase("Cena j. netto [zł]", f);
        Phrase rabat = new Phrase("Rabat [zł]", f);
        Phrase wartosc_netto = new Phrase("Wartość netto [zł]", f);
        Phrase kwota_podatku = new Phrase("Kwota podatku [zł]", f);
        Phrase wartosc_brutto = new Phrase("Wartość brutto [zł]", f);

        table1.addCell(lp);
        table1.addCell(nazwa_tow);
        table1.addCell(jm);
        table1.addCell(ilosc);
        table1.addCell(cena_netto);
        table1.addCell(rabat);
        table1.addCell(wartosc_netto);
        table1.addCell(kwota_podatku);
        table1.addCell(wartosc_brutto);

        return table1;
    }

    private float wartosc = 0;
    private float rabat = 0;
    private float netto = 0;
    private float podatek = 0;

    public PdfPTable createData(String[][] rekordy) {
        float[] widths1 = {5, 30, 5, 5, 10, 10, 10, 10, 10};
        PdfPTable table = new PdfPTable(widths1);
        table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_RIGHT);
        table.getDefaultCell().setPaddingRight(4);
        table.setWidthPercentage(100f);
        for (int i = 0; i < rekordy.length; i++) {
            for (int k = 0; k < 9; k++) {
                table.addCell(String.valueOf(rekordy[i][k]));
            }
            this.wartosc += Float.parseFloat(rekordy[i][8]);
            this.podatek += Float.parseFloat(rekordy[i][7]);
            this.netto += Float.parseFloat(rekordy[i][6]);
            this.rabat += Float.parseFloat(rekordy[i][5]);
        }
        return table;
    }


    public PdfPTable createResum() {
        float[] widths1 = {15, 10, 10, 10, 10};
        PdfPTable table = new PdfPTable(widths1);
        table.getDefaultCell().setBorderWidth(1);
        table.getDefaultCell().setPadding(3);
        table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_RIGHT);
        table.setWidthPercentage(58);
        table.setHorizontalAlignment(Element.ALIGN_RIGHT);
        table.addCell(new Phrase("W tym:"));
        table.addCell(new Phrase("600"));
        table.addCell(new Phrase("0"));
        table.addCell(new Phrase("123,00"));

        PdfPCell Suma = new PdfPCell(new Phrase("732,00"));
        Suma.setHorizontalAlignment(Element.ALIGN_RIGHT);
        Suma.setBackgroundColor(BaseColor.LIGHT_GRAY);
        table.addCell(Suma);
        return table;
    }

    public PdfPTable createSum() {
        float[] widths1 = {15, 10, 10, 10, 10};
        PdfPTable table = new PdfPTable(widths1);
        table.getDefaultCell().setBorderWidth(1);
        table.getDefaultCell().setPadding(3);
        table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_RIGHT);
        table.setWidthPercentage(58);
        table.setHorizontalAlignment(Element.ALIGN_RIGHT);
        table.addCell(new Phrase("Razem:"));
        table.addCell(new Phrase(String.valueOf(this.rabat)));
        table.addCell(new Phrase(String.valueOf(this.netto)));
        table.addCell(new Phrase(String.valueOf(this.podatek)));

        PdfPCell Suma = new PdfPCell(new Phrase(String.valueOf(this.wartosc)));
        Suma.setHorizontalAlignment(Element.ALIGN_RIGHT);
        Suma.setBackgroundColor(BaseColor.LIGHT_GRAY);
        table.addCell(Suma);

        return table;
    }
}

class Data {

    public PdfPTable createSeller(String firma,String nabywca ,String data, String data2,String nip2,String adres,String miasto, String nip, String id) {
        BaseFont bf = null;
        try {
            bf = BaseFont.createFont(BaseFont.TIMES_ROMAN, BaseFont.CP1250, BaseFont.EMBEDDED);
        } catch (DocumentException ex) {
            Logger.getLogger(CreateTable.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(CreateTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        Font f1 = new Font(bf, 9, Font.NORMAL);
        Font f2 = new Font(bf, 10, Font.BOLD);

        Phrase desc = new Phrase("Sprzedawca:n", f1);
        Phrase seller = new Phrase(firma+"n"+data+"nNIP: "+nip+"", f2);
        desc.add(seller);
        desc.add(new Phrase("nnKupujacyn", f1));
        desc.add(new Phrase(nabywca+"n"+adres+"nNIP: "+ nip2 +"",f2));

        Phrase info = new Phrase("Faktura "+id+"nnData wystawienia: "+miasto+", "+data+"nnTermin płatności: "+data2+"");

        //float[] widths1 = {200};
        PdfPTable table = new PdfPTable(2);
        table.getDefaultCell().setBorder(0);
        table.getDefaultCell().setPadding(3);

        table.setWidthPercentage(100f);

        PdfPCell cell_seller = new PdfPCell(desc);
        cell_seller.setHorizontalAlignment(Element.ALIGN_LEFT);
        cell_seller.setBorder(Rectangle.NO_BORDER);
        cell_seller.setPaddingBottom(10);

        PdfPCell cell_info = new PdfPCell(info);
        cell_info.setHorizontalAlignment(Element.ALIGN_RIGHT);
        cell_info.setBorder(Rectangle.NO_BORDER);
        cell_info.setPaddingBottom(10);

        PdfPCell cell_emptor = new PdfPCell();
        cell_emptor.setHorizontalAlignment(Element.ALIGN_LEFT);
        cell_emptor.setPaddingBottom(40);
        cell_emptor.setBorder(Rectangle.NO_BORDER);
        cell_emptor.setPhrase(desc);

        table.addCell(cell_seller);
        table.addCell(cell_info);
        table.addCell(cell_emptor);
        return table;

    }
}

package mmm;

import java.sql.*;

public class Zapytanie{

    private Connection c;
    private Statement stmt;
    private ResultSet wyniki;
    private ResultSetMetaData meta;
    private int update;
    private String konsola;


    public String select(String zapytanie) throws SQLException, ClassNotFoundException {
        String url = "jdbc:sqlite:bazadanychfaktura.db3";
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection(url);
        stmt = c.createStatement();
        wyniki = stmt.executeQuery(zapytanie);
        meta = wyniki.getMetaData();
        konsola = "";

        for (int i = 0; i < meta.getColumnCount(); i++) {  // Wyswietlanie wynikow
            konsola = konsola + meta.getColumnLabel(i + 1) + "t|t";
        }
        konsola = konsola + "n";

        while (wyniki.next()) {
            for (int i = 0; i < meta.getColumnCount(); i++) {
                konsola = konsola + wyniki.getString(i + 1) + "t|t";
            }
            konsola = konsola + "n";
        }
        return konsola;
    }

    public String update(String zapytanie) throws SQLException, ClassNotFoundException {
        String url = "jdbc:sqlite:bazadanychfaktura.db3";
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection(url);
        stmt = c.createStatement();
        update = stmt.executeUpdate(zapytanie);

        konsola = "Zaktualizowano " + update + " wierszy.";

        return konsola;
    }

    public String insert(String zapytanie) throws SQLException, ClassNotFoundException {
        String url = "jdbc:sqlite:bazadanychfaktura.db3";
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection(url);
        stmt = c.createStatement();
        update = stmt.executeUpdate(zapytanie);

        konsola = "Wstawiono " + update + " nowych wierszy.";

        return konsola;
    }

    public String delete(String zapytanie) throws SQLException, ClassNotFoundException {
        String url = "jdbc:sqlite:bazadanychfaktura.db3";
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection(url);
        stmt = c.createStatement();
        update = stmt.executeUpdate(zapytanie);

        konsola = "Usunięto " + update + " wierszy.";

        return konsola;
    }

}