package ianywhere.ultralitej.demo;
import ianywhere.ultralitej.*;
/**
 * CreateDb: sample program to demonstrate creation of simple sales Database and
 * load it with some data.
 * <p>The program also illustrates the use of ordinals when inserting rows into tables.
 */
public class CreateSales
{
    static int ORDINAL_INVOICE_INV_NO;
    static int ORDINAL_INVOICE_NAME;
    static int ORDINAL_INVOICE_DATE;

    static int ORDINAL_INV_ITEM_INV_NO;
    static int ORDINAL_INV_ITEM_ITEM_NO;
    static int ORDINAL_INV_ITEM_PROD_NO;
    static int ORDINAL_INV_ITEM_QUANTITY;
    static int ORDINAL_INV_ITEM_PRICE;

    static int ORDINAL_PROD_NO;
    static int ORDINAL_PROD_NAME;
    static int ORDINAL_PROD_PRICE;

    /** Create the Database.
     * @return connection for a new Database
     */
    private static Connection createDatabase()
        throws ULjException
    {
        Configuration config = DatabaseManager.createConfigurationFile( "Sales.ulj" );
        Connection conn = DatabaseManager.createDatabase( config );

        conn.schemaCreateBegin();

        TableSchema table_schema = conn.createTable( "Product" );
        table_schema.createColumn( "prod_no", Domain.INTEGER );
        table_schema.createColumn( "prod_name", Domain.VARCHAR, 32 );
        table_schema.createColumn( "price", Domain.NUMERIC, 9, (short)2 );
        IndexSchema index_schema = table_schema.createPrimaryIndex( "prime_keys" );
        index_schema.addColumn( "prod_no", IndexSchema.ASCENDING );

        table_schema = conn.createTable( "Invoice" );
        table_schema.createColumn( "inv_no", Domain.INTEGER );
        table_schema.createColumn( "name", Domain.VARCHAR, 50 );
        table_schema.createColumn( "date", Domain.DATE );
        index_schema = table_schema.createPrimaryIndex( "prime_keys" );
        index_schema.addColumn( "inv_no", IndexSchema.ASCENDING );

        table_schema = conn.createTable( "InvoiceItem" );
        table_schema.createColumn( "inv_no", Domain.INTEGER );
        table_schema.createColumn( "item_no", Domain.INTEGER );
        table_schema.createColumn( "prod_no", Domain.INTEGER );
        table_schema.createColumn( "quantity", Domain.INTEGER );
        table_schema.createColumn( "price", Domain.NUMERIC, 9, (short)2 );
        index_schema = table_schema.createPrimaryIndex( "prime_keys" );
        index_schema.addColumn( "inv_no", IndexSchema.ASCENDING );
        index_schema.addColumn( "item_no", IndexSchema.ASCENDING );

        conn.schemaCreateComplete();

        return conn;
    }

    /** Populate the Database.
     * @param conn connection to Database
     */
    private static void populateDatabase( Connection conn )
        throws ULjException
    {
        PreparedStatement ri_product = conn.prepareStatement(
            "INSERT INTO Product( prod_no, prod_name, price ) VALUES( ?, ?, ? )"
            );
        ORDINAL_PROD_NO = 1;
        ORDINAL_PROD_NAME = 2;
        ORDINAL_PROD_PRICE = 3;
        addProduct( ri_product, 2001, "blue screw", ".03" );
        addProduct( ri_product, 2002, "red screw", ".09" );
        addProduct( ri_product, 2004, "hammer", "23.99" );
        addProduct( ri_product, 2005, "vice", "39.99" );
        ri_product.close();

        PreparedStatement ri_invoice = conn.prepareStatement(
            "INSERT INTO Invoice( inv_no, name, \"date\" )"
            + " VALUES( :inv_no, :name, :inv_date )"
        );
        ORDINAL_INVOICE_INV_NO = ri_invoice.getOrdinal( "inv_no" );
        ORDINAL_INVOICE_NAME = ri_invoice.getOrdinal( "name" );
        ORDINAL_INVOICE_DATE = ri_invoice.getOrdinal( "inv_date" );

        PreparedStatement ri_item = conn.prepareStatement(
            "INSERT INTO InvoiceItem( inv_no, item_no, prod_no, quantity, price )"
            + " VALUES( ?, ?, ?, ?, ? )"
        );
        ORDINAL_INV_ITEM_INV_NO = 1;
        ORDINAL_INV_ITEM_ITEM_NO = 2;
        ORDINAL_INV_ITEM_PROD_NO = 3;
        ORDINAL_INV_ITEM_QUANTITY = 4;
        ORDINAL_INV_ITEM_PRICE = 5;

        addInvoice( ri_invoice, 2006001, "Jones Mfg.", "2006/12/23" );
        addInvoiceItem( ri_item, 2006001, 1, 2001, 3000, ".02" );
        addInvoiceItem( ri_item, 2006001, 2, 2002, 5000, ".08" );

        addInvoice( ri_invoice, 2006002, "Smith Inc.", "2006/12/24" );
        addInvoiceItem( ri_item, 2006002, 1, 2004, 2, "23.99" );
        addInvoiceItem( ri_item, 2006002, 2, 2005, 3, "39.99" );

        addInvoice( ri_invoice, 2006003, "Lee Ltd.", "2006/12/24" );
        addInvoiceItem( ri_item, 2006003, 1, 2004, 5, "23.99" );
        addInvoiceItem( ri_item, 2006003, 2, 2005, 4, "39.99" );
        addInvoiceItem( ri_item, 2006003, 3, 2001, 800, ".03" );
        addInvoiceItem( ri_item, 2006003, 4, 2002, 700, ".09" );

        ri_item.close();
        ri_invoice.close();

        conn.commit();
    }

    /**
     * mainline for program.
     *
     * @param args command-line arguments
     *
     */
    public static void main
        ( String[] args )
    {
        try {
            Connection conn = createDatabase();
            populateDatabase( conn );

            conn.release();

            Demo.display( "CreateSales completed successfully" );

        } catch( ULjException exc ) {
            Demo.displayExceptionFull( exc );
        }
    }

    /** Add an invoice row.
     * @param conn connection to Database
     * @param inv_no invoice number
     * @param name name to whom invoice was sent
     */
    private static void addInvoice( PreparedStatement ri, int inv_no, String name
                                  , String date )
        throws ULjException
    {
        ri.set( ORDINAL_INVOICE_INV_NO, inv_no );
        ri.set( ORDINAL_INVOICE_NAME, name );
        ri.set( ORDINAL_INVOICE_DATE, date );
        ri.execute();
    }

    /** Add an invoice-item row.
     * @param conn connection to Database
     * @param inv_no invoice number
     * @param item_no line number for item
     * @param prod_no product number sold
     * @param quantity quantity sold
     * @param price price of one item
     */
    private static void addInvoiceItem( PreparedStatement ri, int inv_no, int item_no
                                      , int prod_no, int quantity, String price )
        throws ULjException
    {
        ri.set( ORDINAL_INV_ITEM_INV_NO, inv_no );
        ri.set( ORDINAL_INV_ITEM_ITEM_NO, item_no );
        ri.set( ORDINAL_INV_ITEM_PROD_NO, prod_no );
        ri.set( ORDINAL_INV_ITEM_QUANTITY, quantity );
        ri.set( ORDINAL_INV_ITEM_PRICE, price );
        ri.execute();
    }

    /** Add a product row.
     * @param conn connection to Database
     * @param prod_no product number
     * @param prod_name product name
     * @param price selling price
     */
    private static void addProduct( PreparedStatement ri, int prod_no, String prod_name, String price )
        throws ULjException
    {
        ri.set( ORDINAL_PROD_NO, prod_no );
        ri.set( ORDINAL_PROD_NAME, prod_name );
        ri.set( ORDINAL_PROD_PRICE, price );
        ri.execute();
    }
}