/*******************************************************************************
 * Copyright (c) 2011, 2013 Oracle and/or its affiliates. All rights reserved.
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
 * which accompanies this distribution.
 * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
 * and the Eclipse Distribution License is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * Contributors:
 *     Oracle - initial API and implementation
 *
 ******************************************************************************/
package org.eclipse.persistence.jpa.tests.jpql.tools.model;

/**
 * The abstract definition
 *
 * @version 2.4
 * @since 2.4
 * @author Pascal Filion
 */
@SuppressWarnings("nls")
public abstract class AbstractStateObjectTest2_0 extends AbstractStateObjectTest {

	public static StateObjectTester stateObject_014() {

		// SELECT ENTRY(addr) FROM Alias a JOIN a.addresses addr

		return selectStatement(
			select(entry("addr")),
			from("Alias", "a", join("a.addresses", "addr"))
		);
	}

	public static StateObjectTester stateObject_139() {

		// SELECT p
		// FROM Employee e JOIN e.projects p
		// WHERE e.id = :id AND INDEX(p) = 1

		return selectStatement(
			select(variable("p")),
			from("Employee", "e", join("e.projects", "p")),
			where(path("e.id").equal(inputParameter(":id")).and(index("p").equal(numeric(1))))
		);
	}

	public static StateObjectTester stateObject_205() throws Exception {

		// UPDATE Employee e
		// SET e.salary =
		//    CASE WHEN e.rating = 1 THEN e.salary * 1.1
		//         WHEN e.rating = 2 THEN e.salary * 1.05
		//         ELSE e.salary * 1.01
		//    END

		return updateStatement(
			update("Employee", "e", set(
				path("e.salary"),
				case_(
					when(path("e.rating").equal(numeric(1)),
					     path("e.salary").multiplication(numeric(1.1))
					),
					when(path("e.rating").equal(numeric(2)),
					     path("e.salary").multiplication(numeric(1.05))
					),
					path("e.salary").multiplication(numeric(1.01))
				)
			))
		);
	}

	public static StateObjectTester stateObject_206() throws Exception {

		// SELECT e.name,
		//        CASE TYPE(e) WHEN Exempt THEN 'Exempt'
		//                     WHEN Contractor THEN 'Contractor'
		//                     WHEN Intern THEN 'Intern'
		//                     ELSE 'NonExempt'
		//        END
		// FROM Employee e
		// WHERE e.dept.name = 'Engineering'

		return selectStatement(
			select(
				path("e.name"),
				case_(
					type("e"),
					new StateObjectTester[] {
						when(entity("Exempt"),     string("'Exempt'")),
						when(entity("Contractor"), string("'Contractor'")),
						when(entity("Intern"),     string("'Intern'"))
					},
					string("'NonExempt'")
				)
			),
			from("Employee", "e", "Contractor", "c"),
			where(path("e.dept.name").equal(string("'Engineering'")))
		);
	}

	public static StateObjectTester stateObject_207() throws Exception {

		// SELECT e.name,
		//        f.name,
		//        CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum '
		//                    WHEN f.annualMiles > 25000 THEN 'Gold '
		//                    ELSE ''
		//               END,
		//               'Frequent Flyer')
		// FROM Employee e JOIN e.frequentFlierPlan f

		return selectStatement(
			select(
				path("e.name"),
				path("f.name"),
				concat(
					case_(
						when(path("f.annualMiles").greaterThan(numeric(50000)),
						     string("'Platinum '")),
						when(path("f.annualMiles").greaterThan(numeric(25000)),
						     string("'Gold '")),
						string("''")
					),
					string("'Frequent Flyer'")
				)
			),
			from("Employee", "e", join("e.frequentFlierPlan", "f"))
		);
	}

	public static StateObjectTester stateObject_208() throws Exception {

		// SELECT e
		// FROM Employee e
		// WHERE TYPE(e) IN (Exempt, Contractor)

		return selectStatement(
			select(variable("e")),
			from("Employee", "e"),
			where(type("e").in(entity("Exempt"), entity("Contractor")))
		);
	}

	public static StateObjectTester stateObject_209() {

		// SELECT e
		// FROM Employee e
		// WHERE TYPE(e) IN (:empType1, :empType2)

		return selectStatement(
			select(variable("e")),
			from("Employee", "e"),
			where(type("e").in(inputParameter(":empType1"), inputParameter(":empType2")))
		);
	}

	public static StateObjectTester stateObject_210() {

		// SELECT e
		// FROM Employee e
		// WHERE TYPE(e) IN :empTypes

		InExpressionStateObjectTester inExpression = in(
			type("e"),
			inputParameter(":empTypes")
		);

		return selectStatement(
			select(variable("e")),
			from("Employee", "e"),
			where(inExpression)
		);
	}

	public static StateObjectTester stateObject_211() {

		// SELECT TYPE(employee)
		// FROM Employee employee
		// WHERE TYPE(employee) <> Exempt

		return selectStatement(
			select(type("employee")),
			from("Employee", "employee"),
			where(type("employee").different(variable("Exempt")))
		);
	}

	public static StateObjectTester stateObject_212() {

		// SELECT t
		// FROM CreditCard c JOIN c.transactionHistory t
		// WHERE c.holder.name = 'John Doe' AND INDEX(t) BETWEEN 0 AND 9

		return selectStatement(
			select(variable("t")),
			from("CreditCard", "c", join("c.transactionHistory", "t")),
			where(
					path("c.holder.name").equal(string("'John Doe'"))
				.and(
					index("t").between(numeric(0), numeric(9))))
		);
	}

	public static StateObjectTester stateObject_213() {

		// SELECT w.name
		// FROM Course c JOIN c.studentWaitlist w
		// WHERE c.name = 'Calculus'
		//       AND
		//       INDEX(w) = 0

		return selectStatement(
			select(path("w.name")),
			from("Course", "c", join("c.studentWaitlist", "w")),
			where(
					path("c.name").equal(string("'Calculus'"))
				.and(
					index("w").equal(numeric(0))))
		);
	}

	public static StateObjectTester stateObject_214() throws Exception {

		// UPDATE Employee e
		// SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1
		//                              WHEN 2 THEN e.salary * 1.05
		//                              ELSE e.salary * 1.01
		//                END

		return updateStatement(
			update(
				"Employee", "e",
				set("e.salary", case_(
					path("e.rating"),
					new StateObjectTester[] {
						when(numeric(1), path("e.salary").multiplication(numeric(1.1))),
						when(numeric(2), path("e.salary").multiplication(numeric(1.05))),
					},
					path("e.salary").multiplication(numeric(1.01))
				))
			)
		);
	}

	public static StateObjectTester stateObject_217() throws Exception {

		// SELECT o.quantity, o.cost*1.08 AS taxedCost, a.zipcode
		// FROM Customer c JOIN c.orders o JOIN c.address a
		// WHERE a.state = 'CA' AND a.county = 'Santa Clara'
		// ORDER BY o.quantity, taxedCost, a.zipcode

		return selectStatement(
			select(
				path("o.quantity"),
				selectItemAs(path("o.cost").multiplication(numeric(1.08)), "taxedCost"),
				path("a.zipcode")
			),
			from("Customer", "c", join("c.orders", "o"), join("c.address", "a")),
			where(
					path("a.state").equal(string("'CA'"))
				.and(
					path("a.county").equal(string("'Santa Clara'"))
				)
			),
			nullExpression(),
			nullExpression(),
			orderBy(
				orderByItem("o.quantity"),
				orderByItem(variable("taxedCost")),
				orderByItem("a.zipcode")
			)
		);
	}

	public static StateObjectTester stateObject_218() throws Exception {

		// SELECT AVG(o.quantity) as q, a.zipcode
		// FROM Customer c JOIN c.orders o JOIN c.address a
		// WHERE a.state = 'CA'
		// GROUP BY a.zipcode
		// ORDER BY q DESC";

		return selectStatement(
			select(
				selectItemAs(avg("o.quantity"), "q"),
				path("a.zipcode")
			),
			from("Customer", "c", join("c.orders", "o"), join("c.address", "a")),
			where(path("a.state").equal(string("'CA'"))),
			groupBy(path("a.zipcode")),
			nullExpression(),
			orderBy(orderByItemDesc(variable("q")))
		);
	}

	public static StateObjectTester stateObject_222() throws Exception {

		// SELECT e.salary / 1000D n
		// From Employee e

		return selectStatement(
			select(selectItem(path("e.salary").division(numeric("1000D")), "n")),
			from("Employee", "e")
		);
	}

	public static StateObjectTester stateObject_223() throws Exception {

		// SELECT MOD(a.id, 2) AS m
		// FROM Address a JOIN FETCH a.customerList
		// ORDER BY m, a.zipcode

		return selectStatement(
			select(selectItemAs(mod(path("a.id"), numeric(2)), "m")),
			from("Address", "a", joinFetch("a.customerList")),
			nullExpression(),
			nullExpression(),
			nullExpression(),
			orderBy(
				orderByItem(variable("m")),
				orderByItem(path("a.zipcode"))
			)
		);
	}
}