MySQL Update example Using group by and Inner Join

	 UPDATE  tmpbilledsummary AS a 
	 INNER JOIN
	 (	SELECT
			a.practiceCode AS accountCode, b.name AS accountName, COUNT(*) AS counter
		FROM 
			laborder a,  practice b
		WHERE 
			a.practiceCode = b.code 
		AND
			a.DOS >= COALESCE(pFromDOS,DOS)
		AND 
			a.DOS <= COALESCE(pToDOS,DOS)
		GROUP BY a.practiceCode
	  ) AS b
	  ON 
	     a.accountCode = b.accountCode 
	  SET a.clientOrders = b.counter;
	 

ZK Dynamic Menu Part 6 Using ZK 7 Navigation Bar

In Part 5, we have seen how to create the dynamic menu Using Tab, Tool Bar and Group Box. In this post, we will see how to create Dynamic Menu using ZK 7 New component called Navigation Bar.

MenuItem.java

package ZKDynamicMenu;

import java.util.ArrayList;
import java.util.List;

public class MenuItem {
	private String name;
	private List<MenuItem> children;
	private int level;

	public MenuItem(String name, int level) {
		this.name = name;
		this.level = level;
		children = new ArrayList<MenuItem>();
	}

	public void addChild(MenuItem node) {
		children.add(node);
	}

	public void appendChild(MenuItem child) {
		if (children == null)
			children = new ArrayList<MenuItem>();
		children.add(child);
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public List<MenuItem> getChildren() {
		return children;
	}

	public void setChildren(List<MenuItem> children) {
		this.children = children;
	}

	public int getLevel() {
		return level;
	}

	public void setLevel(int level) {
		this.level = level;
	}

}

MenuItemData.java

package ZKDynamicMenu;

import java.util.ArrayList;
import java.util.List;

public class MenuItemData {

	private static List<MenuItem> menus = new ArrayList<MenuItem>();
	static {

		MenuItem m1 = new MenuItem("Administration", 1);
		MenuItem m1_lv1 = new MenuItem("Security", 2);
		MenuItem m1_Lv2 = new MenuItem("Accounts", 3);

		MenuItem m1_Lv3 = new MenuItem("User", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Role", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("User Rights", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Role Rights", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("Regional", 3);
		m1_Lv3 = new MenuItem("Clock", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Language", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Keyboard", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("Devices", 3);
		m1_Lv3 = new MenuItem("Printer", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Projector", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Mouse", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1.addChild(m1_lv1);

		/***************************************************/
		m1_lv1 = new MenuItem("Network", 2);
		m1_Lv2 = new MenuItem("Wireless", 3);
		m1_Lv3 = new MenuItem("Connection 1", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Connection 2", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("Adapter", 3);
		m1_Lv3 = new MenuItem("Local Area Connection", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Virtual Box Network", 4);
		m1_Lv2.addChild(m1_Lv3);

		m1_lv1.addChild(m1_Lv2);

		m1.addChild(m1_lv1);

		/***************************************************/
		m1_lv1 = new MenuItem("My Computer", 2);
		m1_Lv2 = new MenuItem("Drives", 3);
		m1_Lv3 = new MenuItem("C Drive", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("D Drive", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("E Drive", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("Favorites", 3);
		m1_Lv3 = new MenuItem("Desktop", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Downloads", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Recent Places", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Google Drive", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);
		m1.addChild(m1_lv1);
		menus.add(m1);

		/************************************************************************/
		m1 = new MenuItem("ZK", 1);
		m1_lv1 = new MenuItem("Products", 2);
		m1_Lv2 = new MenuItem("ZK SpreadSheet", 3);

		m1_Lv3 = new MenuItem("3D Cell", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Freeze rows", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Ranged Cells", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("ZK Pivottable", 3);
		m1_Lv3 = new MenuItem("Drill Down", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Render", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Paging", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("ZK Calender", 3);
		m1_Lv3 = new MenuItem("Views", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Drag and Drop", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Time Zones", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("ZK Spring", 3);
		m1_Lv3 = new MenuItem("HTTP Request", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("HTTP Basic", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("MD4 Password", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1.addChild(m1_lv1);

		/***************************************************/
		m1_lv1 = new MenuItem("ZK Demo", 2);
		m1_Lv2 = new MenuItem("Grid", 3);
		m1_Lv3 = new MenuItem("Master Detail", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Data Binding", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Dynamic Data", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Data Filter", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("ListBox", 3);
		m1_Lv3 = new MenuItem("Dual ListBox", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Paging", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Auto Sort", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("List Group", 4);
		m1_Lv2.addChild(m1_Lv3);

		m1_Lv2 = new MenuItem("Effects", 3);
		m1_Lv3 = new MenuItem("jQuery Effects", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Upload Effect", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Login Effect", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Display Action", 4);
		m1_Lv2.addChild(m1_Lv3);

		m1_Lv2 = new MenuItem("Layout", 3);
		m1_Lv3 = new MenuItem("Portal Layout", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Complex Border Layout", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Group Box", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("Boxes", 4);
		m1_Lv2.addChild(m1_Lv3);

		m1_lv1.addChild(m1_Lv2);

		m1.addChild(m1_lv1);

		/***************************************************/
		m1_lv1 = new MenuItem("ZK Support", 2);
		m1_Lv2 = new MenuItem("Documentation", 3);
		m1_Lv3 = new MenuItem("Spread Sheet", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("ZK Calender", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("ZK Studio", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);

		m1_Lv2 = new MenuItem("Downloads", 3);
		m1_Lv3 = new MenuItem("ZK Spring", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("ZK JSP", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);
		m1.addChild(m1_lv1);

		menus.add(m1);
		m1 = new MenuItem("Eclipse", 1);
		menus.add(m1);
		m1_lv1 = new MenuItem("File", 2);
		m1_Lv2 = new MenuItem("Project", 3);

		m1_Lv3 = new MenuItem("New Maven", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("New JPA", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_Lv3 = new MenuItem("New JEE", 4);
		m1_Lv2.addChild(m1_Lv3);
		m1_lv1.addChild(m1_Lv2);
		m1.addChild(m1_lv1);
	}

	public static List<MenuItem> getAllMenus() {
		return new ArrayList<MenuItem>(menus);
	}

}

index.zul

<window title="Dynamic Menu Example !!" border="normal" height="98%"
	apply="org.zkoss.bind.BindComposer"
	viewModel="@id('vm') @init('ZKDynamicMenu.MainMenuVM')">
	<borderlayout>
	 
		<west size="20%" flex="true" maxsize="250" splittable="true"
			collapsible="true">
			<div>
				<navbar orient="vertical"
					children="@load(vm.menuItems)">
					<template name="children" var="item">
						<nav label="@load(item.name)"  
							iconSclass="z-icon-th-list">
							<navitem label="${each.name}"
								forEach="${item.children}" />
						</nav>
					</template>
				</navbar>
			</div>
		</west>
		<center border="none" flex="true">
			<div style="background:#E6D92C">
				<label value="" style="color:white;font-size:50px" />
			</div>
		</center>
	</borderlayout>
</window>

MainMenuVM

package ZKDynamicMenu;

import java.util.List;

import org.zkoss.bind.annotation.BindingParam;
import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.NotifyChange;

public class MainMenuVM {

	private List<MenuItem> menuItems;
	private MenuItem selectedMenuItem;

	public MenuItem getSelectedMenuItem() {
		return selectedMenuItem;
	}

	public void setSelectedMenuItem(MenuItem selectedMenuItem) {
		this.selectedMenuItem = selectedMenuItem;
	}

	public List<MenuItem> getMenuItems() {
		return menuItems;
	}

	public void setMenuItems(List<MenuItem> menuItems) {
		this.menuItems = menuItems;
	}

	public MainMenuVM() {
		menuItems = MenuItemData.getAllMenus();
		setSelectedMenuItem(menuItems.get(0).getChildren().get(0));
	}

	@Command
	@NotifyChange("selectedMenuItem")
	public void onMenuClick(@BindingParam("item") MenuItem item) {
		setSelectedMenuItem(item);
	}
}

Output

Please check ZK 7 Project Start-up Kit – Dynamic Menu

ZK and Lookup Components using MVVM–Part 1

In this example, we will see how we can retrieve the information using ZK Combo Box.

Step 1:
Create ZK 7 Maven project in the name of ZK7Lookup using the post Create and Run Your First ZK 7 Application with Eclipse and Maven.

After creating the Maven Project, the Structure should look like this



Step 2:
Next we will create Employee class to store the information about the Employee.

package ZK7Lookup;

import java.math.BigDecimal;

public class Employee {
	private String code;
	private String lastName;
	private String firstName;
	private String gender;
	private BigDecimal salary;
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public BigDecimal getSalary() {
		return salary;
	}
	public void setSalary(BigDecimal salary) {
		this.salary = salary;
	}
	
	
}

Step 3:
Next we will create data class to give some sample Employee values. In real world application, this will come from the database.
Class: EmployeeData.java

package ZK7Lookup;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class EmployeeData {

	private static List<Employee> Employees= new ArrayList<Employee>();
    static {
    	Employees.add(new Employee("EMP1001","GERALDO", "YANES", "Male",new BigDecimal((1000))));
    	Employees.add(new Employee("EMP1002","REYES", "BRIZUELA", "Male",new BigDecimal((2000))));
    	Employees.add(new Employee("EMP1003","CHARBEL", "MERCEDES", "Female",new BigDecimal((3000))));
    	Employees.add(new Employee("EMP1004","ESPINOSA", "MERCEDES", "Male",new BigDecimal((5000))));
    	Employees.add(new Employee("EMP1005","MEDINA", "MERCEDES", "Female",new BigDecimal((5000))));
    	Employees.add(new Employee("EMP1006","JONATHAN", "MERCEDES", "Male",new BigDecimal((7000))));
    	Employees.add(new Employee("EMP1007","MARICHAL", "WALSH", "Female",new BigDecimal((2000))));
    	Employees.add(new Employee("EMP1008","CORREA", "LUNA", "Male",new BigDecimal((5000))));
    	Employees.add(new Employee("EMP1009","JUNITAVE", "MCDERMOTT", "Male",new BigDecimal((2000))));
    	Employees.add(new Employee("EMP1010","JESSICA", "OWENS", "Female",new BigDecimal((12000))));
    	Employees.add(new Employee("EMP1011","GOVIN", "CODY", "Male",new BigDecimal((2020))));
    	Employees.add(new Employee("EMP1012","JOHN", "CALAMIA", "Female",new BigDecimal((8000))));
    	Employees.add(new Employee("EMP1013","BARBARA", "CAMPO", "Male",new BigDecimal((400))));
    	Employees.add(new Employee("EMP1014","JORGE", "DIAZ", "Female",new BigDecimal((12000))));
    	Employees.add(new Employee("EMP1015","RAYDA", "MARICHAL", "Male",new BigDecimal((3000))));
    	Employees.add(new Employee("EMP1016","ALAN", "MEEHAN", "Female",new BigDecimal((300))));
    	Employees.add(new Employee("EMP1017","BLACK", "KRESIEN", "Female",new BigDecimal((300))));
    	Employees.add(new Employee("EMP1018","MARGO", "KASPER", "Male",new BigDecimal((1200))));
    	Employees.add(new Employee("EMP1019","LEANDRO", "PERINI", "Female",new BigDecimal((1200))));
    	Employees.add(new Employee("EMP1020","ABELARDO", "YANES", "Male",new BigDecimal((1000))));
    	Employees.add(new Employee("EMP1021","CANDELARIA", "BRIZUELA", "Male",new BigDecimal((2000))));
    	Employees.add(new Employee("EMP1022","LEBOFSKY", "MERCEDES", "Female",new BigDecimal((3000))));
    	Employees.add(new Employee("EMP1023","RODITTI", "JENNIFER", "Male",new BigDecimal((5000))));
    	Employees.add(new Employee("EMP1024","FULSOM", "CHRIS", "Female",new BigDecimal((5000))));
    	Employees.add(new Employee("EMP1025","BARRERA", "MERCEDES", "Male",new BigDecimal((7000))));
    	Employees.add(new Employee("EMP1026","HAMMAN", "WALSH", "Female",new BigDecimal((2000))));
    	Employees.add(new Employee("EMP1027","BENNSON", "LUNA", "Male",new BigDecimal((5000))));
    	Employees.add(new Employee("EMP1028","MARTINEZ", "MCDERMOTT", "Male",new BigDecimal((2000))));
    	Employees.add(new Employee("EMP1029","JONES", "OWENS", "Female",new BigDecimal((12000))));
    	Employees.add(new Employee("EMP1030","FRIED", "NANCY", "Male",new BigDecimal((2020))));
    	Employees.add(new Employee("EMP1031","JOHN", "JUSTIN", "Female",new BigDecimal((8000))));
    	Employees.add(new Employee("EMP1032","MCCAHH", "WAYNE", "Male",new BigDecimal((400))));
    	Employees.add(new Employee("EMP1033","MOLINA", "SULMA", "Female",new BigDecimal((12000))));
    	Employees.add(new Employee("EMP1034","MATTHEWS", "FRANK", "Male",new BigDecimal((3000))));
    	Employees.add(new Employee("EMP1035","RIVERA", "MEEHAN", "Female",new BigDecimal((300))));
    	Employees.add(new Employee("EMP1036","DAVIK", "JOSEPH", "Female",new BigDecimal((300))));
    	Employees.add(new Employee("EMP1037","MILLER", "JESUSA", "Male",new BigDecimal((1200))));
    	Employees.add(new Employee("EMP1038","POWERS", "PERINI", "Female",new BigDecimal((1200))));
    	
    	
    	
    }
 
    public static List<Employee> getAllEmployee() {
        return new ArrayList<Employee>(Employees);
    }
}

Step 4:
Next we will create zul file combo box.zul.

 

<zk>
	<window title="ZK Look up " border="normal"
		apply="org.zkoss.bind.BindComposer"
		viewModel="@id('vm') @init('ZK7Lookup.ComboBoxVM')">
		<separator></separator>
		<label value="Select Employee"   />
		<separator></separator>
		<combobox model="@load(vm.employeeList)"
			selectedItem="@bind(vm.selectedEmployee)">
			<template name="model" var="p">
				<comboitem value="@bind(p.code)"
					label="@load(p.lastName)" />
			</template>
		</combobox>
		<separator></separator>
		<label value="Last Name"   />
		<separator></separator>
		<textbox value="@bind(vm.selectedEmployee.lastName)"></textbox>
		<separator></separator>
		<label value="First Name"   />
		<separator></separator>
		<textbox value="@bind(vm.selectedEmployee.firstName)"></textbox>
		<separator></separator>
		<label value="Gender"   />
		<separator></separator>
		<textbox value="@bind(vm.selectedEmployee.gender)"></textbox>
		<separator></separator>
		<label value="Salary"   />
		<separator></separator>
		<textbox value="@bind(vm.selectedEmployee.salary)"></textbox>
		<separator></separator>
		<separator></separator>
		<separator></separator>

	</window>
</zk>

Step 5:
Next we will create the view Model to support the ZUL File
Class : ComboBoxVM.java

package ZK7Lookup;

import java.util.ArrayList;
import java.util.List;

import org.zkoss.bind.annotation.AfterCompose;
import org.zkoss.bind.annotation.ContextParam;
import org.zkoss.bind.annotation.ContextType;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.select.Selectors;

public class ComboBoxVM {

	private List<Employee> employeeList = new ArrayList<Employee>();
	private Employee selectedEmployee;
	
	public Employee getSelectedEmployee() {
		return selectedEmployee;
	}

	public void setSelectedEmployee(Employee selectedEmployee) {
		this.selectedEmployee = selectedEmployee;
	}

	public List<Employee> getEmployeeList() {
		return employeeList;
	}

	public void setEmployeeList(List<Employee> employeeList) {
		this.employeeList = employeeList;
	}

	@AfterCompose
	public void initSetup(@ContextParam(ContextType.VIEW) Component view) {
		Selectors.wireComponents(view, this, false);
		this.employeeList = EmployeeData.getAllEmployee();
	}

}

Now you can run the zul file and you can check the output.

ZK and Lookup(Selection) Components using MVVM

In this post, i will explain how to achieve the lookup methods using different components in ZK. So what is a lookup Method. ?

The LOOKUP method or function is used to search one column of data and find data in the corresponding row. For example, if you are searching a column of employee IDs the LOOKUP function can find, say, employee number 12345 in the ID column. Once it has found the ID 12345 it then can return data from that same row.

The above logic can be achieved using ZK Components as follows

1. Combo Box
2. Chosen Box
3. Customizable Combo Box
4. List Box


Let us start and see one by one examples:
All these examples are based in MVVM Pattern.

Before looking each example, follow and get trained on creating ZK Maven Project using the following link.
Create and Run Your First ZK 7 Application with Eclipse and Maven

All the examples will illustrate how to select the key data and retrieve the corresponding data on the same row. For example, Search and select by employee code and show the other information such as LastName, firstName, Gender, DOB, address, etc..

Here is the links which describes different methods.

1. ZK Selection or Lookup Using Combo Box.

MYSQL Query for Aging Report

Assume the following table scheme

Table Name : billing

billNo varchar(20);

insuranceCode varchar(20);

balanceAmount decimal(12,2);

billedDate date;

Here is the query for Aging Report

SELECT insuranceCode,
    SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 1 AND 30, balanceAmount, 0)) AS age130,
    SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 31 AND 60, balanceAmount, 0)) AS age3160,
    SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 61 AND 90, balanceAmount, 0)) AS age6190,
    SUM(IF(DATEDIFF(CURDATE(), billedDate ) > 90, balanceAmount, 0)) AS agegt90,
    SUM(balanceAmount) AS totalBalance
FROM billing  bill
WHERE
	bill.balanceAmount  > 0

GROUP BY insuranceCode  
ORDER BY totalBalance DESC

MYSQL How to pass list of values to Stored Procedure.

DELIMITER $$


DROP PROCEDURE IF EXISTS `updateAllOtherInsurance`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateAllOtherInsurance`(pIDs VARCHAR(1000))
    BEGIN
	
	DELETE FROM patientinsuranceelgihistory WHERE  FIND_IN_SET(patientInsID, pIDs) > 0;

    END$$

DELIMITER ;

MYSQL Update From within the same table using some condition

In Some cases, we need to update the table of one row with the values of the other row on the same table. Here is the query to do the same.

UPDATE patientinsurance dest
		JOIN ( SELECT 
				patientCode,
				policyNo
			FROM
				patientinsurance
			WHERE
				ID = 121590
			) AS source
			
			ON dest.patientCode = source.patientCode

SET dest.policyNo = source.policyNo
WHERE dest.id IN (121911)

ZK upload PDF to server and show in the screen using MVVM–Part 2

In the Part 1, we have seen how to upload the PDF File and show in the same desktop. This example will show you how to show the PDF in the separate tab in the same browser window.


ZK Version : ZK 7.0.3
Project Name : zk7example6

Step 1:
Follow this post, to create ZK 7 Maven Project.


When you create ZK Maven Project, you can see by default index.zul and MyViewModel.Java will be created. We will modify this zul and VM for our example.

Step 2:
Let us modify the index.zul and MyViewModel.java to accept the PDF File from the user and stored in the server directory. And also You can see that there are two buttons to show the PDF ; one is to show PDF File on the same desktop, and the other is to show the same PDF in the separate tab in the same browser.

Index.zul

<zk>
	<window apply="org.zkoss.bind.BindComposer"
		viewModel="@id('vm')@init('ZKExample6.MyViewModel')">
		<label value="You are using: ${desktop.webApp.version}" />
		<separator></separator>
		<label
			value="Example for File upload to the server and display it" />
		<separator></separator>
		<hbox>
			<label value="Upload any PDF File" />
			<button label="Upload" upload="true"
				onUpload="@command('onUploadPDF',upEvent=event)">
			</button>
		</hbox>
		<button label="Show PDF Option 1" visible="@load(vm.fileuploaded)"
			onClick="@command('showPDFOption1')">
		</button>
		<button label="Show PDF Option 2" visible="@load(vm.fileuploaded)"
			onClick="@command('showPDFOption2')">
		</button>
	</window>
</zk>

ViewModel for our Index.zul file.

package ZKExample6;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Calendar;

import org.zkoss.bind.BindContext;
import org.zkoss.bind.annotation.AfterCompose;
import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.ContextParam;
import org.zkoss.bind.annotation.ContextType;
import org.zkoss.bind.annotation.NotifyChange;
import org.zkoss.io.Files;
import org.zkoss.util.media.AMedia;
import org.zkoss.util.media.Media;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Executions;
import org.zkoss.zk.ui.event.UploadEvent;
import org.zkoss.zk.ui.select.Selectors;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zul.Iframe;
import org.zkoss.zul.Messagebox;
import org.zkoss.zul.Window;

public class MyViewModel {

	private String filePath;
	private boolean fileuploaded = false;
	AMedia fileContent;

	@Wire("#test")
	private Window win;

	public AMedia getFileContent() {
		return fileContent;
	}

	public void setFileContent(AMedia fileContent) {
		this.fileContent = fileContent;
	}

	public boolean isFileuploaded() {
		return fileuploaded;
	}

	public void setFileuploaded(boolean fileuploaded) {
		this.fileuploaded = fileuploaded;
	}

	@AfterCompose
	public void initSetup(@ContextParam(ContextType.VIEW) Component view) {
		Selectors.wireComponents(view, this, false);

	}

	@Command
	@NotifyChange("fileuploaded")
	public void onUploadPDF(
			@ContextParam(ContextType.BIND_CONTEXT) BindContext ctx)
			throws IOException {

		UploadEvent upEvent = null;
		Object objUploadEvent = ctx.getTriggerEvent();
		if (objUploadEvent != null && (objUploadEvent instanceof UploadEvent)) {
			upEvent = (UploadEvent) objUploadEvent;
		}
		if (upEvent != null) {
			Media media = upEvent.getMedia();
			Calendar now = Calendar.getInstance();
			int year = now.get(Calendar.YEAR);
			int month = now.get(Calendar.MONTH); // Note: zero based!
			int day = now.get(Calendar.DAY_OF_MONTH);
			filePath = Executions.getCurrent().getDesktop().getWebApp()
					.getRealPath("/");
			String yearPath = "\\" + "PDFs" + "\\" + year + "\\" + month + "\\"
					+ day + "\\";
			filePath = filePath + yearPath;
			File baseDir = new File(filePath);
			if (!baseDir.exists()) {
				baseDir.mkdirs();
			}
			Files.copy(new File(filePath + media.getName()),
					media.getStreamData());
			Messagebox.show("File Sucessfully uploaded in the path [ ."
					+ filePath + " ]");
			fileuploaded = true;
			filePath = filePath + media.getName();
		}
	}

	@Command
	public void showPDFOption1() throws IOException {
		Window win = (Window) Executions.createComponents(
				"zkpdfviewer.zul", null, null);
		Iframe frame = (Iframe) win.getFellow("reportframe");
		File f = new File(this.filePath);
		byte[] buffer = new byte[(int) f.length()];
		FileInputStream fs = new FileInputStream(f);
		fs.read(buffer);
		fs.close();

		ByteArrayInputStream is = new ByteArrayInputStream(buffer);
		AMedia amedia = new AMedia(filePath, "pdf", "application/pdf", is);
		frame.setContent(amedia);

	}
	
	@Command
	public void showPDFOption2() throws IOException {
		String URL;
		URL = "zkpdfviewer.zul?filepath=";
		URL = URL + EncryptionUtil.encode(filePath);
		Executions.getCurrent().sendRedirect(URL, "_blank");

	}
}

Step 3:
As you can see, we have two buttons to show the uploaded PDF. One option is normal to open on the same screen, and the other one is open the pdf in the separate tab.In the both option, we have used separate ZUL File to view the PDF. Here is the zul file and its corresponding VM.

 <window id="pdfwindow" width="100%" height="100%" title="PDF-Viewer"
	border="normal" minimizable="false" mode="modal" maximizable="false"
	sclass="mymodal" closable="true" apply="org.zkoss.bind.BindComposer"
	viewModel="@id('vm') @init('ZKExample6.ZKPDFViewerVM')">
	<iframe height="100%" width="100%" id="reportframe"></iframe>
</window>

View Model

package ZKExample6;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;

import org.zkoss.bind.annotation.AfterCompose;
import org.zkoss.bind.annotation.ContextParam;
import org.zkoss.bind.annotation.ContextType;
import org.zkoss.bind.annotation.QueryParam;
import org.zkoss.util.media.AMedia;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.select.Selectors;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zul.Iframe;
 
public class ZKPDFViewerVM {

	@Wire("#reportframe")
	private Iframe iframe;

	@AfterCompose
	public void initSetup(@ContextParam(ContextType.VIEW) Component view,
			@QueryParam("filepath") String filePath) throws Exception {
		Selectors.wireComponents(view, this, false);
		if (filePath==null)
			filePath = "";
		if (filePath.isEmpty()==false) {
			filePath = EncryptionUtil.decode(filePath);
			File f = new File(filePath);
			byte[] buffer = new byte[(int) f.length()];
			FileInputStream fs = new FileInputStream(f);
			fs.read(buffer);
			fs.close();
			ByteArrayInputStream is = new ByteArrayInputStream(buffer);
			AMedia amedia = new AMedia(filePath, "pdf", "application/pdf", is);
			iframe.setContent(amedia);
		}
	}
}

For the second option, actually we are passing the PDF File path from the server as URL and we received this in the VM and then showing the IFrame. For security reasons,  We can encode the URL and then we can decode the URL. That's what the additional static class EncryptionUtil.java does. Here is the code.

package ZKExample6;

import java.security.spec.AlgorithmParameterSpec;
import java.security.spec.KeySpec;

import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.PBEKeySpec;
import javax.crypto.spec.PBEParameterSpec;

import org.apache.commons.codec.binary.Base64;

public class EncryptionUtil {

	private static final byte[] SALT = { (byte) 0x21, (byte) 0x21, (byte) 0xF0,
			(byte) 0x55, (byte) 0xC3, (byte) 0x9F, (byte) 0x5A, (byte) 0x75 };

	private final static int ITERATION_COUNT = 31;

	private EncryptionUtil() {
	}

	public static String encode(String input) {
		if (input == null) {
			throw new IllegalArgumentException();
		}
		try {

			KeySpec keySpec = new PBEKeySpec(null, SALT, ITERATION_COUNT);
			AlgorithmParameterSpec paramSpec = new PBEParameterSpec(SALT,
					ITERATION_COUNT);

			SecretKey key = SecretKeyFactory.getInstance("PBEWithMD5AndDES")
					.generateSecret(keySpec);

			Cipher ecipher = Cipher.getInstance(key.getAlgorithm());
			ecipher.init(Cipher.ENCRYPT_MODE, key, paramSpec);

			byte[] enc = ecipher.doFinal(input.getBytes());

			String res = new String(Base64.encodeBase64(enc));
			// escapes for url
			res = res.replace('+', '-').replace('/', '_').replace("%", "%25")
					.replace("\n", "%0A");

			return res;

		} catch (Exception e) {
		}

		return "";

	}

	public static String decode(String token) {
		if (token == null) {
			return null;
		}
		try {

			String input = token.replace("%0A", "\n").replace("%25", "%")
					.replace('_', '/').replace('-', '+');

			byte[] dec = Base64.decodeBase64(input.getBytes());

			KeySpec keySpec = new PBEKeySpec(null, SALT, ITERATION_COUNT);
			AlgorithmParameterSpec paramSpec = new PBEParameterSpec(SALT,
					ITERATION_COUNT);

			SecretKey key = SecretKeyFactory.getInstance("PBEWithMD5AndDES")
					.generateSecret(keySpec);

			Cipher dcipher = Cipher.getInstance(key.getAlgorithm());
			dcipher.init(Cipher.DECRYPT_MODE, key, paramSpec);

			byte[] decoded = dcipher.doFinal(dec);

			String result = new String(decoded);
			return result;

		} catch (Exception e) {
			// use logger in production code
			e.printStackTrace();
		}

		return null;
	}

}

Here is the complete Project Structure


|
Now you can run the index.zul file and you check the output by clicking the two buttons.
You can download the source code here.

ZK List Box : How to show particular row in different color in MVVM

In this example, we will see how to change the color based on some conditions.

ZK Version : ZK 7.0.3
Project Name : zk7example5

Step 1:
Follow this post, to create ZK 7 Maven Project.

Step 2:
Create the Person Bean in the ZKExample5 Package

package ZKExample5;

public class Person {
	private String firstName;
	private String lastName;
	private String email;
	private Integer active;

	public Person(String firstName, String lastName, String email,Integer active) {
		this.firstName = firstName;
		this.lastName = lastName;
		this.email = email;
		this.active = active;
	}

	public Integer getActive() {
		return active;
	}

	public void setActive(Integer active) {
		this.active = active;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

}

Step 3:
Create the Person ZUL Under the webapp folder as

<window title="Example for ZK MVVM List Box" width="500px"
	apply="org.zkoss.bind.BindComposer"
	viewModel="@id('vm') @init('ZKExample5.ViewModel.PersonVM')">
	<label value="You are using: ${desktop.webApp.version}" />
	<separator></separator>
	<listbox model="@load(vm.allPersons)" checkmark="true" mold="paging"
		pageSize="10" multiple="true"
		selectedItems="@bind(vm.selectedPersons)">
		<listhead sizable="true">
			<listheader label="First Name" sortDirection="ascending"
				sort="auto(firstName)" />
			<listheader label="Last Name" sort="auto(lastName)" />
			<listheader label="Email" sort="auto(email)" />
		</listhead>
		<template name="model" var="p1">
			<listitem>
				<listcell label="@load(p1.firstName)" />
				<listcell label="@load(p1.lastName)" />
				<listcell label="@load(p1.email)" />
			</listitem>
		</template>
	</listbox>
	 
</window>

 

Step 4:
Now let us create the View Model for the above zul file as follows

package ZKExample5.ViewModel;

import java.util.ArrayList;
import java.util.List;

import ZKExample5.Person;

public class PersonVM {
	private List<Person> allPersons = new ArrayList<Person>();
	private List<Person> selectedPersons;


	public List<Person> getSelectedPersons() {
		return selectedPersons;
	}

	public void setSelectedPersons(List<Person> selectedPersons) {
		this.selectedPersons = selectedPersons;
	}

	public List<Person> getAllPersons() {
		return allPersons;
	}

	public void setAllPersons(List<Person> allPersons) {
		this.allPersons = allPersons;
	}

	public PersonVM()
	{
		 allPersons.add(new Person("John", "James", "JohnJames@yahoo.com",1));
	     allPersons.add(new Person("Taylor", "Harris", "Harris@yahoo.com",0));
	     allPersons.add(new Person("Allen", "Scott", "Scott@yahoo.com",1));
	     allPersons.add(new Person("Minna", "Kristeen", "Kristeen@yahoo.com",0));
	     allPersons.add(new Person("Abel", "Olive", "Olive@yahoo.com",1));
	     allPersons.add(new Person("Kiley", "Renea", "Renea@yahoo.com",0));
	     allPersons.add(new Person("Graciela", "Samira", "Samira@yahoo.com",0));
	     allPersons.add(new Person("Cammy", "Jenelle", "Jenelle@yahoo.com",1));
	     allPersons.add(new Person("Mattie", "Jerry", "Jerry@yahoo.com",1));
	     allPersons.add(new Person("Meaghan", "Ozell", "Ozell@yahoo.com",0));
	}
	
	 
}

Step 5:
Now let us create our css file. Create a folder called “css” in the webapp folder and create a css file namely style.css as shown

Here is the content of the CSS File

@CHARSET "ISO-8859-1";

.inactive .z-listcell-content {
	color: #FF9900;
	text-decoration: underline;
	text-decoration: line-through;
}

Step 6:
Now refer the above css file in the person.zul and As you can see, we have a property called active which basically contains 1 or 0. Now we will show persons which is inactive (1) in different color. So change the zul file as follows 

<window title="Example for ZK MVVM List Box" width="500px"
	apply="org.zkoss.bind.BindComposer"
	viewModel="@id('vm') @init('ZKExample5.ViewModel.PersonVM')">
	<style src="/css/style.css" />
	<label value="You are using: ${desktop.webApp.version}" />
	<separator></separator>
	<listbox model="@load(vm.allPersons)" checkmark="true" mold="paging"
		pageSize="10" multiple="true"
		selectedItems="@bind(vm.selectedPersons)">
		<listhead sizable="true">
			<listheader label="First Name" sortDirection="ascending"
				sort="auto(firstName)" />
			<listheader label="Last Name" sort="auto(lastName)" />
			<listheader label="Email" sort="auto(email)" />
			<listheader label="Active" sort="auto(active)" />
		</listhead>
		<template name="model" var="p1">
			<listitem sclass="@load(p1.active eq 1 ?'inactive':'')">
				<listcell label="@load(p1.firstName)" />
				<listcell label="@load(p1.lastName)" />
				<listcell label="@load(p1.email)" />
				<listcell label="@load(p1.active)" />
			</listitem>
		</template>
	</listbox>

</window>

Now you can run the person.zul . Here is the output.

ZK MVVM List Box Select All and Unselect all Records

I have a list box with multiple selection allowed and paging. Actually it means I have 'select all' checkbox in list box header, which allows me select all entries that shown on the current page.

Problem statement: I did not find a way how to catch events from 'select all' checkbox. Actually, I need select all entries (on all pages and not on displayed page!)  when 'select all' checked. And deselect all entries on all pages when 'select all' unchecked.
 
Since 6.5.5, The Select all checkbox on listheader now support onCheckSelectAll event that can determine whether it is checked or not.
ZK Reference

Note: Select all checkbox in list header is only available if ROD is false.

ZK Version : ZK 7.0.3
Project Name : zk7example4

Step 1:
Follow this post, to create ZK 7 Maven Project.

 

Step 2:
Create the Person Bean in the ZKExample4 Package

package ZKExample4;

public class Person {
	private String firstName;
	private String lastName;
	private String email;

	public Person(String firstName, String lastName, String email) {
		this.firstName = firstName;
		this.lastName = lastName;
		this.email = email;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

}

Step 3:
Create the Person ZUL Under the webapp folder as

<window title="Example for ZK MVVM List Box" width="500px"
	apply="org.zkoss.bind.BindComposer"
	viewModel="@id('vm') @init('ZKExample4.ViewModel.PersonVM')">
	<label value="You are using: ${desktop.webApp.version}" />
	<separator></separator>
	<listbox model="@load(vm.allPersons)" checkmark="true" mold="paging"
		pageSize="4" multiple="true"
		onCheckSelectAll="@command('onSelectAll')"
		selectedItems="@bind(vm.selectedPersons)">
		<listhead sizable="true">
			<listheader label="First Name" sortDirection="ascending"
				sort="auto(firstName)" />
			<listheader label="Last Name" sort="auto(lastName)" />
			<listheader label="Email" sort="auto(email)" />
		</listhead>
		<template name="model" var="p1">
			<listitem>
				<listcell label="@load(p1.firstName)" />
				<listcell label="@load(p1.lastName)" />
				<listcell label="@load(p1.email)" />
			</listitem>
		</template>
	</listbox>
	<label value="Total Selected" />
	<separator></separator>
	<label value="@load(vm.selectedPersons.size())" />
</window>

Step 4:
Now let us create the View Model for the above zul file as follows

package ZKExample4.ViewModel;

import java.util.ArrayList;
import java.util.List;

import org.zkoss.bind.BindUtils;
import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.ContextParam;
import org.zkoss.bind.annotation.ContextType;
import org.zkoss.zk.ui.event.CheckEvent;

import ZKExample4.Person;

public class PersonVM {
	private List<Person> allPersons = new ArrayList<Person>();
	private List<Person> selectedPersons;


	public List<Person> getSelectedPersons() {
		return selectedPersons;
	}

	public void setSelectedPersons(List<Person> selectedPersons) {
		this.selectedPersons = selectedPersons;
	}

	public List<Person> getAllPersons() {
		return allPersons;
	}

	public void setAllPersons(List<Person> allPersons) {
		this.allPersons = allPersons;
	}

	public PersonVM()
	{
		 allPersons.add(new Person("John", "James", "JohnJames@yahoo.com"));
	     allPersons.add(new Person("Taylor", "Harris", "Harris@yahoo.com"));
	     allPersons.add(new Person("Allen", "Scott", "Scott@yahoo.com"));
	     allPersons.add(new Person("Minna", "Kristeen", "Kristeen@yahoo.com"));
	     allPersons.add(new Person("Abel", "Olive", "Olive@yahoo.com"));
	     allPersons.add(new Person("Kiley", "Renea", "Renea@yahoo.com"));
	     allPersons.add(new Person("Graciela", "Samira", "Samira@yahoo.com"));
	     allPersons.add(new Person("Cammy", "Jenelle", "Jenelle@yahoo.com"));
	     allPersons.add(new Person("Mattie", "Jerry", "Jerry@yahoo.com"));
	     allPersons.add(new Person("Meaghan", "Ozell", "Ozell@yahoo.com"));
	     allPersons.add(new Person("Gladys", "Whitley", "Whitley@yahoo.com"));
	     allPersons.add(new Person("Yuki", "Ligia", "Ligia@yahoo.com"));
	     allPersons.add(new Person("Chanel", "Erinn", "Erinn@yahoo.com"));
	     allPersons.add(new Person("Wilda", "Noah", "Noah@yahoo.com"));
	     allPersons.add(new Person("Rolland", "Gail", "Gail@yahoo.com"));
	     allPersons.add(new Person("Bok", "Mitzie", "Mitzie@yahoo.com"));
	}
	
	@Command
	public void onSelectAll(
			@ContextParam(ContextType.TRIGGER_EVENT) CheckEvent e) {
		this.selectedPersons = new ArrayList<Person>();
		if (e.isChecked())
			selectedPersons.addAll(allPersons);
		BindUtils.postNotifyChange(null, null, this, "selectedPersons");
	}
}

Here is the Complete Project Structure



Now you can select the person.zul file and run using tomcat. If you select the check box in the header, you can see that all the persons are selected not only in the current page



You can download the source here

Hibernate Criteria sqlRestriction example

	Criteria criteria = getCurrentSession().createCriteria(LabOrder.class);
		criteria.add(Restrictions.sqlRestriction("YEAR(createdDate)="
				+ labOrderConditions.getYear()));
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.sqlGroupProjection(
				"MONTHNAME(createdDate) as orderMonthName",
				"MONTHNAME(createdDate)", new String[] { "orderMonthName" },
				new Type[] { StandardBasicTypes.STRING }));
		projectionList.add(Projections.sqlGroupProjection(
				"Month(createdDate) as orderMonthNo", "Month(createdDate)",
				new String[] { "orderMonthNo" },
				new Type[] { StandardBasicTypes.INTEGER }));
		projectionList.add(Projections.rowCount());
		criteria.setProjection(projectionList);
		criteria.addOrder(org.hibernate.criterion.Order.asc("createdDate"));
		return criteria.list();

MySQL Query for the above.

select
        MONTHNAME(createdDate) as orderMonthName,
        Month(createdDate) as orderMonthNo,
        count(*) as y2_ 
    from
        laborder this_ 
    where
        YEAR(createdDate)=2014 
    group by
        MONTHNAME(createdDate),
        Month(createdDate) 
    order by
        this_.createdDate asc

To get Start and End date by given Month, Year and Day

import java.util.Calendar;
import java.util.Date;

public class HelloWorld {

	public static void main(String[] args) {

		Date fromDate = getFromDate(2014, 7, null);
		System.out.println(" From Date is  " + fromDate);

		fromDate = getFromDate(2014, 7, 14);
		System.out.println(" From Date is  " + fromDate);

		Date toDate = getToDate(2014, 7, null);
		System.out.println(" To Date is  " + toDate);

		toDate = getToDate(2014, 7, 14);
		System.out.println(" To Date is  " + toDate);

	}

	private static Date getFromDate(Integer year, Integer month, Integer day) {
		// Setup a Calendar instance.
		Calendar cal = Calendar.getInstance();
		cal.setLenient(false);
		// Set the year as 2009
		cal.set(Calendar.YEAR, year);
		// Set the month as February (can be set as 1 or Calendar.FEBRUARY)
		cal.set(Calendar.MONTH, month);
		// Set the date as 1st - optional
		if (day == null)
			cal.set(Calendar.DATE, 1);
		else
			cal.set(Calendar.DAY_OF_MONTH, day);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		return cal.getTime();
	}

	private static Date getToDate(Integer year, Integer month, Integer day) {
		// Setup a Calendar instance.
		Calendar cal = Calendar.getInstance();
		cal.setLenient(false);
		// Set the year as 2009
		cal.set(Calendar.YEAR, year);
		// Set the month as February (can be set as 1 or Calendar.FEBRUARY)
		cal.set(Calendar.MONTH, month - 1);
		// Set the date as 1st - optional

		if (day == null) {
			int lastDateOfMonth = cal.getActualMaximum(Calendar.DATE);
			cal.set(Calendar.DATE, lastDateOfMonth);
		} else
			cal.set(Calendar.DAY_OF_MONTH, day);
		cal.set(Calendar.HOUR_OF_DAY, 23);
		cal.set(Calendar.MINUTE, 59);
		cal.set(Calendar.SECOND, 59);
		return cal.getTime();
	}
}

Output:

From Date is  Fri Aug 01 00:00:00 IST 2014
From Date is  Thu Aug 14 00:00:00 IST 2014
To Date is  Thu Jul 31 23:59:59 IST 2014
To Date is  Mon Jul 14 23:59:59 IST 2014

How to exclude .svn folder in eclipse without subversion installed

1. Open Project Properties and Resource Filer as shown



2. Click Add Button

Select Filter type as “Exclude ALL” and Applies to “Folders” and Select “All Children” and Type .svn for File and Folder Attributes

 

Click ok and now the .svn folder will not be listed in the Eclipse Explorer.

Hibernate Criteria retrieve records when two fields are equal

Here is the code snippet from my project to retrieve the records when two field value in the  table has the same value.

if (laborderConditions.getBalanceStatus() != null) {
			if (laborderConditions.getBalanceStatus().equalsIgnoreCase("Open")) {
				criteria.add(Restrictions
						.sqlRestriction("IFNULL(amount,0)=IFNULL(balance,0)"));
			}

		}

Hibernate Sort String field after changing to Integer

	Criteria criteria = getCurrentSession().createCriteria(LabOrder.class);

		criteria.createAlias("practice", "pract", JoinType.INNER_JOIN);
		criteria.createAlias("patient", "pat", JoinType.INNER_JOIN);
		criteria.createAlias("labpanel", "panel", JoinType.LEFT_OUTER_JOIN);
		criteria.addOrder(new org.hibernate.criterion.Order("orderNo", true) {
			@Override
			public String toSqlString(Criteria criteria,
					CriteriaQuery criteriaQuery) throws HibernateException {
				return "cast(orderNo as UNSIGNED) desc";
			}
		});

How to compare dates in hibernate using criteria if DB Field contains both Date and Time values

Here is the small code snippet from my current project to retrieve the records between two date. User will just enter the date part with year, month and date, but the DB Contains date and time value.

if (laborderConditions.getFromDOE() != null) {
        criteria.add(Restrictions.ge("createdDate",
                getFormattedFromDateTime(laborderConditions.getFromDOE())));
    }

    if (laborderConditions.getToDOE() != null) {
        criteria.add(Restrictions.le("createdDate",
                getFormattedToDateTime(laborderConditions.getToDOE())));
    }




private Date getFormattedFromDateTime(Date date) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    cal.set(Calendar.HOUR_OF_DAY, 0);
    cal.set(Calendar.MINUTE, 0);
    cal.set(Calendar.SECOND, 0);
    return cal.getTime();
}

private Date getFormattedToDateTime(Date date) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    cal.set(Calendar.HOUR_OF_DAY, 23);
    cal.set(Calendar.MINUTE, 59);
    cal.set(Calendar.SECOND, 59);
    return cal.getTime();
}

CentOS Commands

To Check CentOS Version via command Line

cat /etc/redhat-release

Connect with MySQL via command Line

mysql -uroot -p123456

To Create folder
mkdir foo

MySQL dropping index is very slow

I found a good post in server fault on how we can speed up drop index in big table in MySQL. Here is the scripts.

CREATE TABLE patient_new LIKE patient;
ALTER TABLE patient_new DROP INDEX post_text;
ALTER TABLE patient_new DISABLE KEYS;
INSERT INTO patient_new SELECT * FROM patient;
ALTER TABLE patient_new ENABLE KEYS;
ALTER TABLE patient RENAME patient_old;
ALTER TABLE patient_new RENAME patient;
DROP TABLE patient_old;

MySQL Trigger Before Insert example

Here is an small example how to create trigger in MySQL after inserting record into table.
This examples how to generate random password in the MySQL and update the user name in the Before insert trigger.

Step 1 :
Let us create a small table with three fields such as ID, loginName and password. Here is the script for the same;

DROP TABLE IF EXISTS `appusers`;

CREATE TABLE `appusers` (
  `ID` bigint(20) NOT NULL auto_increment,
  `loginName` varchar(20) default NULL,
  `password` varchar(100) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2 :
Let us create a stored procedure which will return random password using MySQL RAND,ROUND and MOD function.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `getPassword`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getPassword`(OUT _password VARCHAR(7))
BEGIN
	DECLARE COUNT INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE PASSWORD VARCHAR(10) DEFAULT "";
	
	WHILE COUNT<7 DO
		SET COUNT=COUNT+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
			#Generate a random digit
			SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
		ELSE
			#Generate a random digit
			SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
		END IF;
		#CONCAT function concatenates two or more strings and returns result
		SELECT CONCAT(PASSWORD,randomCharacter) INTO PASSWORD;
	END WHILE;
	SET _password=PASSWORD;
    END$$

DELIMITER ;

Step 3 :
Here is trigger

DELIMITER $$


DROP TRIGGER /*!50032 IF EXISTS */ `updatepassword`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `updatepassword` BEFORE INSERT ON `appusers` 

    FOR EACH ROW BEGIN
	 DECLARE varPassword VARCHAR(100);
	 CALL getPassword(varPassword);
	 SET NEW.password = varPassword;
    END;
$$

DELIMITER ;

Now you can test by inserting record  as follows:
INSERT INTO appusers(loginName) VALUES('John1');

ZK MVVM List Box Export to Excel

In this post, we will see how to export records from the DB to Excel format.

ZK Version : ZK 7.0.2
Project Name : zk7example3

Step 1:
Follow this post, to create ZK 7 Maven Project.

Step 2:
Follow this post, to connect MySQL Via Hibernate Spring Integration. After this step, the project structure should be like as follows;



Step 3:
Follow this post, to connect MySQL via Hibernate Spring integration and display the records in ZK List View Component . After this step, the output
will be as follows



Step 4:

Next we will see how to take a list of JAVA beans and generate an Excel spread sheet using the Apache Commons POI library, with help from the
Apache Commons BeanUtils library. First let us create our Columns class which basically defines the properties of the column.

Let us add Apache dependencies in our POM File. Add the following dependencies;

	<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>commons-beanutils</groupId>
			<artifactId>commons-beanutils</artifactId>
			<version>1.8.3</version>
		</dependency>

In the package zk7example3, create a class called ExcelColumns.java and a enum called FormatType

package zk7example3;

public enum FormatType {
	TEXT, INTEGER, FLOAT, DATE, MONEY, PERCENTAGE
}

ExcelColumns.Java

package zk7example3;

import org.apache.poi.xssf.usermodel.XSSFFont;

public class ExcelColumns {

	private String m_method;
	private String m_header;
	private FormatType m_type;
	private XSSFFont m_font;
	private Short m_color;
	private int m_width;

	public ExcelColumns(String method, String header, FormatType type,
			XSSFFont font, Short color, int width) {
		this.m_method = method;
		this.m_header = header;
		this.m_type = type;
		this.m_font = font;
		this.m_color = color;
		this.m_width = width;
	}

	public ExcelColumns(String method, String header, FormatType type,
			XSSFFont font) {
		this(method, header, type, font, null, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type,
			Short color) {
		this(method, header, type, null, color, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type) {
		this(method, header, type, null, null, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type, int width) {
		this(method, header, type, null, null, width);
	}

	public String getMethod() {
		return m_method;
	}

	public void setMethod(String method) {
		this.m_method = method;
	}

	public String getHeader() {
		return m_header;
	}

	public void setHeader(String header) {
		this.m_header = header;
	}

	public FormatType getType() {
		return m_type;
	}

	public void setType(FormatType type) {
		this.m_type = type;
	}

	public XSSFFont getFont() {
		return m_font;
	}

	public void setFont(XSSFFont m_font) {
		this.m_font = m_font;
	}

	public Short getColor() {
		return m_color;
	}

	public void setColor(Short m_color) {
		this.m_color = m_color;
	}

	public int getWidth() {
		if (this.m_width == 0)
			return 3000;
		else
			return m_width;
	}

	public void setWidth(int m_width) {
		this.m_width = m_width;
	}

}

Step 5:
Next we will create our generic class which converts list of beans to excel. In the package zk7example3, create a class called BeanToExcel

package zk7example3;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.zkoss.zul.Filedownload;

public class BeanToExcel {

	protected List<?> dataList = null;
	protected List<ExcelColumns> excelColumns = null;
	protected String dataSheetName;
	private XSSFSheet sheet;
	private XSSFWorkbook workbook;

	public List<?> getDataList() {
		return dataList;
	}

	public void setDataList(List<?> dataList) {
		this.dataList = dataList;
	}

	public List<ExcelColumns> getExcelColumns() {
		return excelColumns;
	}

	public void setExcelColumns(List<ExcelColumns> excelColumns) {
		this.excelColumns = excelColumns;
	}

	public String getDataSheetName() {
		return dataSheetName;
	}

	public void setDataSheetName(String dataSheetName) {
		this.dataSheetName = dataSheetName;
	}

	public void exportToExcel() {

		try {

			// Blank workbook
			this.workbook = new XSSFWorkbook();
			// Create a blank sheet
			this.sheet = this.workbook.createSheet(this.dataSheetName);

			int numCols = this.excelColumns.size();
			int currentRow = 0;

			// Create the report header at row 0
			Row excelHeader = sheet.createRow(currentRow);
			Cell dataCell;
			// Loop over all the column beans and populate the report headers
			for (int i = 0; i < numCols; i++) {
				dataCell = excelHeader.createCell(i);
				dataCell.setCellStyle(getHeaderCellStyle(workbook));
				dataCell.setCellValue(excelColumns.get(i).getHeader());
				this.sheet.setColumnWidth(i, excelColumns.get(i).getWidth());
			}

			currentRow++;
			Row dataRow;
			for (int i = 0; i < this.dataList.size(); i++) {
				// create a row in the spreadsheet
				dataRow = sheet.createRow(currentRow++);
				// get the bean for the current row
				// get the bean for the current row
				Object bean = dataList.get(i);
				for (int y = 0; y < numCols; y++) {
					Object value = PropertyUtils.getProperty(bean, excelColumns
							.get(y).getMethod());
					writeCell(dataRow, y, value, excelColumns.get(y).getType(),
							excelColumns.get(y).getColor(), excelColumns.get(y)
									.getFont());
				}
			}

		} catch (IllegalAccessException e) {
			System.out.println(e);
		} catch (InvocationTargetException e) {
			System.out.println(e);
		} catch (NoSuchMethodException e) {
			System.out.println(e);
		}

		Calendar now = Calendar.getInstance();
		int day = now.get(Calendar.DAY_OF_MONTH);
		int hour = now.get(Calendar.HOUR_OF_DAY);
		int minute = now.get(Calendar.MINUTE);
		int second = now.get(Calendar.SECOND);
		String fileName = this.dataSheetName + "_" + day + "_" + hour + "_"
				+ minute + "_" + second + "_";

		try {
			// Write the workbook in file system
			File temp = File.createTempFile(fileName, ".xlsx");
			FileOutputStream out = new FileOutputStream(temp);
			workbook.write(out);
			out.close();
			Filedownload.save(temp, null);
		} catch (Exception e) {
			System.out.println(e);

		}
	}

	private void writeCell(Row dataRow, int col, Object value,
			FormatType formatType, Short bgColor, XSSFFont font) {

		Cell dataCell;
		dataCell = dataRow.createCell(col);
		if (value == null) {
			return;
		}
		switch (formatType) {
		case TEXT:
			dataCell.setCellValue(value.toString());
			break;
		case DATE:
			dataCell.setCellStyle(getDateFormatStyle(this.workbook));
			dataCell.setCellValue((Date) value);
			break;
		case FLOAT:
			break;
		case INTEGER:
			dataCell.setCellStyle(getIntegerCellStyle(this.workbook));
			dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
			dataCell.setCellValue(((Number) value).intValue());
			break;
		case MONEY:
			dataCell.setCellStyle(getNumberCellStyle(this.workbook));
			dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
			dataCell.setCellValue(((BigDecimal) value).doubleValue());
			break;
		case PERCENTAGE:
			break;
		default:
			break;
		}

	}

	public static void createExcelHeader(XSSFWorkbook workbook,
			XSSFSheet excelSheet, String titles[]) {

		Row excelHeader = excelSheet.createRow(0);
		Cell dataCell;
		int count = 0;
		for (String caption : titles) {
			dataCell = excelHeader.createCell(count++);
			dataCell.setCellStyle(getHeaderCellStyle(workbook));
			dataCell.setCellValue(caption);
		}
	}

	public static XSSFCellStyle getHeaderCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle titleStyle = workbook.createCellStyle();

		titleStyle.setFillPattern(XSSFCellStyle.FINE_DOTS);
		titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		titleStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		titleStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);

		Font headerFont = workbook.createFont();
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short) 10);
		titleStyle.setFont(headerFont);

		return titleStyle;
	}

	private XSSFCellStyle getDateFormatStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat df = workbook.createDataFormat();
		style.setDataFormat(df.getFormat("mm/dd/yyyy"));
		return style;
	}

	private XSSFCellStyle getNumberCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		style.setDataFormat(format.getFormat("0.00"));
		return style;
	}

	private XSSFCellStyle getIntegerCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		style.setDataFormat(format.getFormat("#,##0"));
		return style;
	}
}

Step 6:
Next we will see how we can use our generic class to generate  excel from List of beans. In the index.zul , at the top, add button as follows

<?taglib uri="http://www.zkoss.org/dsp/web/core" prefix="c"?>
<zk>
	<style src="/css/style.css" />
	<window apply="org.zkoss.bind.BindComposer"
		viewModel="@id('vm')@init('zk7example3.MyViewModel')">
		<button label="Export to Excel"
			onClick="@command('onExcelExport')">
		</button>
		<listbox selectedItem="@bind(vm.selectedOrder)"
			model="@bind(vm.ordersList)">
			<listhead sizable="true">
				<listheader label="order No" sort="auto(orderNumber)" />
				<listheader label="Customer Name"
					sort="auto(LOWER(customerFname))" />
				<listheader label="Gender" sort="auto(customerGender)" />
				<listheader label="Phone" sort="auto(customerPhone)" />
				<listheader label="Status" sort="auto(status)" />
				<listheader label="Order Total" align="right"
					sort="auto(orderTotal)" />
				<listheader label="Order Date" align="center"
					sort="auto(orderDate)" />
				<listheader label="Delivered" sort="auto(delivered)" />
			</listhead>
			<template name="model" var="p1">
				<listitem>
					<listcell label="@load(p1.orderNumber)" />

					<listcell
						label="@load(vm.getCustomerName(p1.customerFname, p1.customerLname))" />
					<listcell>
						<hbox spacing="20px">
							<image sclass="@bind(p1.customerGender)" />
							<label value="@load(p1.customerGender)" />
						</hbox>
					</listcell>
					<listcell
						label="@load(p1.customerPhone) @converter('zk7example3.MyPhoneConverter')" />
					<listcell label="@load(p1.status)" />
					<listcell
						label="@load(c:formatNumber(p1.orderTotal, '$#,###.00'))" />
					<listcell
						label="@load(p1.orderDate) @converter('formatedDate', format='MM/dd/yyyy')" />
					<listcell
						label="@bind(p1.delivered eq 1?'Yes':'No')" />
				</listitem>
			</template>
		</listbox>
	</window>
</zk>

Now let us add the method “onExcelExport” in our viewmodel.

package zk7example3;

import java.util.ArrayList;
import java.util.List;

import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.Init;
import org.zkoss.zk.ui.select.annotation.WireVariable;
import org.zkoss.zkplus.spring.SpringUtil;

import com.example.business.service.CRUDService;
import com.example.domain.Orders;

public class MyViewModel {

	@WireVariable
	private CRUDService crudService;
	private List<Orders> ordersList = null;
	private Orders selectedOrder;

	public Orders getSelectedOrder() {
		return selectedOrder;
	}

	public void setSelectedOrder(Orders selectedOrder) {
		this.selectedOrder = selectedOrder;
	}

	public List<Orders> getOrdersList() {
		return ordersList;
	}

	public void setOrdersList(List<Orders> ordersList) {
		this.ordersList = ordersList;
	}

	@Init
	public void init() {
		crudService = (CRUDService) SpringUtil.getBean("CRUDService");
		ordersList = crudService.GetListByNamedQuery("Orders.getAllOrders");
	}

	public String getCustomerName(String firstName, String lastName) {
		return firstName + " " + lastName;
	}

	@Command
	public void onExcelExport() {
		List<ExcelColumns> excelColumns = new ArrayList<ExcelColumns>();
		excelColumns.add(new ExcelColumns("orderNumber", "Order No",
				FormatType.INTEGER));
		excelColumns.add(new ExcelColumns("orderDate", "Order Date",
				FormatType.DATE));

		excelColumns.add(new ExcelColumns("customerFname", "First Name",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerLname", "Last Name",
				FormatType.TEXT,5000));
		excelColumns.add(new ExcelColumns("customerGender", "Gender",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerAddress1", "Address1",
				FormatType.TEXT,5000));
		excelColumns.add(new ExcelColumns("customerCity", "City",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerState", "State",
				FormatType.TEXT,2000));
		excelColumns.add(new ExcelColumns("customerZip", "Zip",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerPhone", "Phone",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("orderTotal", "Order Total",
				FormatType.MONEY));

		BeanToExcel beanToExcel = new BeanToExcel();
		beanToExcel.setExcelColumns(excelColumns);
		beanToExcel.setDataSheetName("orders");
		beanToExcel.setDataList(ordersList);
		beanToExcel.exportToExcel();
	}

}

Thats all. Now you can run and click the button to export the list box records into excel.





You can download the source here.