// *****************************************************
// Copyright (c) 2006-2010 iAnywhere Solutions, Inc.
// Portions copyright (c) 2006-2010 Sybase, Inc.
// All rights reserved. All unpublished rights reserved.
// *****************************************************
// This sample code is provided AS IS, without warranty or liability
// of any kind.
//
// You may use, reproduce, modify and distribute this sample code
// without limitation, on the condition that you retain the foregoing
// copyright notice and disclaimer as to the original iAnywhere code.
//
// *********************************************************************
package com.ianywhere.ultralitej.demo;
import com.ianywhere.ultralitej12.*;

/**
 * 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 );

	executeSql( conn
		  , "CREATE TABLE Product\n"
		   +"( prod_no INT NOT NULL PRIMARY KEY\n"
		   +", prod_name VARCHAR(32)\n"
		   +", price NUMERIC(9,2)"
		   +", SYNCHRONIZE OFF"
		   +")"
		  );

	executeSql( conn
		  , "CREATE TABLE Invoice\n"
		   +"( inv_no INT NOT NULL PRIMARY KEY\n"
		   +", name VARCHAR(50)\n"
		   +", \"date\" DATE\n"
		   +", SYNCHRONIZE OFF"
		   +")"
		  );

	executeSql( conn
		  , "CREATE TABLE InvoiceItem\n"
		   +"( inv_no INT NOT NULL\n"
		   +", item_no INT NOT NULL\n"
		   +", prod_no INT NOT NULL\n"
		   +", quantity INT NOT NULL\n"
		   +", price NUMERIC(9,2)"
		   +", PRIMARY KEY( inv_no, item_no )\n"
		   +", SYNCHRONIZE OFF"
		   +")"
		  );

	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.displayException( exc );
	}
    }

    /** Add an invoice row.
     * @param conn connection to Database
     * @param inv_no invoice number
     * @param name name to whome 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();
    }

    /** Execute a SQL statement.
     * @param conn connection to current database
     * @param stmt SQL statement to be executed
     */
    private static void executeSql( Connection conn, String stmt )
    {
	try {
	    PreparedStatement ps = conn.prepareStatement( stmt );
	    ps.execute();
	    ps.close();
	} catch( ULjException exc ) {
	    Demo.displayException( exc );
	}
    }
}