/*
* :tabSize=2:indentSize=2:noTabs=true:
* :folding=explicit:collapseFolds=1:
*
* Copyright (C) 2009 Chaniel AB, Thomas Dilts This program is free
* software; you can redistribute it and/or modify it under the terms of the
* GNU General Public License as published by the Free Software Foundation;
* either version 2 of the License, or (at your option) any later version. This
* program is distributed in the hope that it will be useful, but WITHOUT ANY
* WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
* details. You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software Foundation,
* Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA For more
* information, surf to www.lazy8.nu or email support@lazy8.nu
*/
package nu.lazy8.ledger.jdbc;
//{{{ imports
import java.awt.Dimension;
import java.awt.GridLayout;
import java.io.File;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import lazy8ledger.Lazy8LedgerPlugin;
import nu.lazy8.ledger.forms.CompanyComponents;
import nu.lazy8.ledger.forms.DataConnectDialog;
import nu.lazy8.ledger.main.Lazy8Ledger;
import nu.lazy8.util.gen.Fileio;
import nu.lazy8.util.gen.SetupInfo;
import nu.lazy8.util.gen.SystemLog;
import nu.lazy8.util.gen.Translator;
import org.gjt.sp.jedit.gui.DockableWindowManager;
import org.gjt.sp.util.Log;
//}}}
/**
* Description of the Class
*
* @author Lazy Eight Data HB, Thomas Dilts
* @created den 5 mars 2002
*/
public class DataConnection {
//{{{ static members
public static boolean isDatabaseChanged = false;
public final static String MCKOI_DRIVER = "com.mckoi.JDBCDriver";
public final static String[] createTables = {
"SELECT * FROM Activity2 WHERE CompId=99999",
"create table Activity2 " +
"(CompId INTEGER NOT NULL, " +
"Act_id INTEGER NOT NULL, " +
"RegDate DATE, " +
"InvDate DATE, " +
"Notes VARCHAR(200), " +
"FileInfo VARCHAR(200), " +
"PeriodId INTEGER NOT NULL, " +
"PRIMARY KEY(Act_id,CompId))",
"SELECT * FROM Amount WHERE CompId=99999",
"create table Amount " +
"(CompId INTEGER NOT NULL, " +
"Act_id INTEGER NOT NULL, " +
"Account INTEGER NOT NULL, " +
"Amount DOUBLE, " +
"IsDebit INTEGER, " +
"Customer INTEGER, " +
"Notes VARCHAR(40)) ",
"SELECT * FROM APP.Account WHERE CompId=99999",
"create table APP.Account " +
"(CompId int NOT NULL, " +
"Account int NOT NULL, " +
"AccDesc VARCHAR(255), " +
"IsAsset int, " +
"PRIMARY KEY(Account,CompId))",
"SELECT * FROM Customer2 WHERE CompId=99999",
"create table Customer2 " +
"(CompId int NOT NULL, " +
"CustId int NOT NULL, " +
"CustName VARCHAR(100), " +
"CustDesc VARCHAR(200), " +
"DefaultAcc int NOT NULL, " +
"PRIMARY KEY (CustId,CompId))",
"SELECT * FROM UniqNum WHERE CompId=99999",
"create table UniqNum " +
"(CompId int NOT NULL, " +
"UniqName varchar(50) NOT NULL, " +
"LastNumber int, " +
"PRIMARY KEY (UniqName,CompId))",
"SELECT * FROM AccountingPeriods WHERE CompId=99999",
"create table AccountingPeriods " +
"(CompId int NOT NULL, " +
"StartPeriod date NOT NULL, " +
"EndPeriod date NOT NULL, " +
"PRIMARY KEY (CompId,StartPeriod,EndPeriod))",
"SELECT * FROM Company WHERE CompId=99999",
"create table Company " +
"(CompId int NOT NULL, " +
"Name VARCHAR(200), " +
"PRIMARY KEY (CompId))",
"SELECT * FROM AccountType WHERE CompId=99999",
"create table AccountType " +
"(CompId int NOT NULL, " +
"AccTypeId int NOT NULL, " +
"TypeName VARCHAR(100), " +
"SortOrder int NOT NULL, " +
"IsInBalanceReport int NOT NULL, " +
"PRIMARY KEY (AccTypeId,CompId))"};
private final static String[] accountTypeInitData = {
"INSERT INTO AccountType SET CompId=?,AccTypeId=0,TypeName='Assets',SortOrder=100,IsInBalanceReport=1",
"INSERT INTO AccountType SET CompId=?,AccTypeId=1,TypeName='Debts',SortOrder=200,IsInBalanceReport=1",
"INSERT INTO AccountType SET CompId=?,AccTypeId=2,TypeName='Incomes',SortOrder=400,IsInBalanceReport=0",
"INSERT INTO AccountType SET CompId=?,AccTypeId=3,TypeName='Expenses',SortOrder=500,IsInBalanceReport=0",
"INSERT INTO AccountType SET CompId=?,AccTypeId=4,TypeName='Equity',SortOrder=300,IsInBalanceReport=1"};
private static DataConnection myInstance;
//}}}
//{{{ members
public boolean bIsConnectionMade;
private CompanyComponents companyComponents;
public Connection con;
private Driver drv;
private JDialog jd;
//}}}
//{{{ +DataConnection(JFrame) : <init>
/**
* Constructor for the DataConnection object
*
* @param view Description of the Parameter
*/
public DataConnection(JFrame view) {
//show always the welcome window
if (SetupInfo.getBoolProperty(SetupInfo.ALL_WINDOWS_FLOATING)) {
DockableWindowManager mgr = ((org.gjt.sp.jedit.View) view).getDockableWindowManager();
mgr.showDockableWindow("infoviewer");
}
Lazy8Ledger.ShowContextHelp(view, "welcome", "");
bIsConnectionMade = false;
String stringPass = SetupInfo.getProperty(SetupInfo.CONNECT_PASSWORD);
String stringUser = SetupInfo.getProperty(SetupInfo.CONNECT_USERNAME);
boolean isLoginRequired = SetupInfo.getBoolProperty(SetupInfo.REQUIRE_LOGIN);
if (isLoginRequired) {
PasswordDialog pDialog = new PasswordDialog(null, true, false);
if (pDialog.bIsCancel) {
return;
}
stringPass = new String(pDialog.jPass.getPassword());
stringUser = new String(pDialog.jUser.getText());
pDialog.dispose();
}
while (!(makeConnection(
createTables,
SetupInfo.getProperty(SetupInfo.CONNECT_DRIVER),
SetupInfo.getProperty(SetupInfo.CONNECT_DATABASE),
stringUser,
stringPass))) {
JOptionPane.showMessageDialog(view,
Translator.getTranslation("Unable to connect to the database. Perhaps wrong password"),
Translator.getTranslation("Lazy 8 ledger"),
JOptionPane.PLAIN_MESSAGE);
DataConnectDialog ds = ShowDataConnectDialog(view);
if (ds.isAbort) {
break;
}
stringPass = SetupInfo.getProperty(SetupInfo.CONNECT_PASSWORD);
stringUser = SetupInfo.getProperty(SetupInfo.CONNECT_USERNAME);
}
}//}}}
//{{{ +_AddDefaultAccountTypes(int)_ : void
/**
* Description of the Method
*
* @param iCompId Description of the Parameter
*/
public static void AddDefaultAccountTypes(int iCompId) {
if(iCompId==0){
//every company must have these new account types entered
try {
DataConnection dc = new DataConnection(null);
Statement st = dc.con.createStatement();
ResultSet rsCompanies = st.executeQuery(dc.filterSQL(
"SELECT CompId FROM Company"));
while (rsCompanies.next()) {
AddDefaultAccountTypesOneComp(rsCompanies.getInt(1));
}
} catch (Exception ex) {
ex.printStackTrace();
}
}else{
AddDefaultAccountTypesOneComp(iCompId);
}
}//}}}
//{{{ -_AddDefaultAccountTypesOneComp(int)_ : void
/**
* Description of the Method
*
* @param iCompId Description of the Parameter
*/
private static void AddDefaultAccountTypesOneComp(int iCompId) {
int numOfTypes = accountTypeInitData.length - 1;
if (SetupInfo.getBoolProperty(SetupInfo.USE_EQUITY_ACCOUNT_TYPE)) {
numOfTypes++;
}
try {
DataConnection dc = DataConnection.getInstance(null);
if (dc == null || !dc.bIsConnectionMade) {
return;
}
//create the AccountType information
PreparedStatement prepSt;
for (int i = 0; i < numOfTypes; i++) {
prepSt = dc.con.prepareStatement(accountTypeInitData[i]);
prepSt.setInt(1, iCompId);
prepSt.executeUpdate();
}
//add the uniq number
Statement st = dc.con.createStatement();
st.executeUpdate("INSERT INTO UniqNum (UniqName,CompId,LastNumber) VALUES ('AccTypeId', "
+ iCompId+ ", " + numOfTypes + " )");
isDatabaseChanged = true;
} catch (Exception ex) {
ex.printStackTrace();
}
}//}}}
//{{{ +IsConnectionMade() : boolean
/**
* Description of the Method
*
* @return Description of the Return Value
*/
public boolean IsConnectionMade() {
return bIsConnectionMade;
}//}}}
//{{{ +_ShowDataConnectDialog(JFrame)_ : DataConnectDialog
/**
* Description of the Method
*
* @param frame Description of the Parameter
* @return Description of the Return Value
*/
public static DataConnectDialog ShowDataConnectDialog(JFrame frame) {
return new DataConnectDialog(frame, createTables);
}//}}}
//{{{ +_filterSQL(String)_ : String
/**
* Description of the Method
*
* @param in Description of the Parameter
* @return Description of the Return Value
*/
public static String filterSQL(String in) {
if (SetupInfo.getProperty(SetupInfo.CONNECT_DRIVER).equals(MCKOI_DRIVER)) {
return in;
} else {
return in.replaceAll("APP.", "");
}
}//}}}
//{{{ +_getInstance(JFrame)_ : DataConnection
/**
* Gets the instance attribute of the DataConnection class
*
* @param view Description of the Parameter
* @return The instance value
*/
public static DataConnection getInstance(JFrame view) {
if (myInstance == null || !myInstance.bIsConnectionMade) {
if(!Lazy8LedgerPlugin.isInStart
&& !SetupInfo.getBoolProperty(SetupInfo.IS_INSTALL_ROUTINE_DONE))
new Lazy8LedgerPlugin().start();
if (SetupInfo.getBoolProperty(SetupInfo.IS_INSTALL_ROUTINE_DONE) || Lazy8LedgerPlugin.isInStart) {
myInstance = new DataConnection(view);
if (myInstance.bIsConnectionMade) {
//this is a perfect starting point for the whole program. Here you come the
//first time you successfully start the program and you come here never again
//untill the next time the program is successfully started
//need to set the default number formats.
System.setProperty("user.language", SetupInfo.getProperty(SetupInfo.NUMBER_FORMAT_LANGUAGE));
System.setProperty("user.region", SetupInfo.getProperty(SetupInfo.PRESENT_COUNTRY));
try {
Log.log(Log.DEBUG, null, "user.language=" + System.getProperty("user.language") +
" : user.region=" + System.getProperty("user.region"));
Locale lc = new Locale(System.getProperty("user.language"),
System.getProperty("user.region"));
Locale.setDefault(lc);
} catch (Exception eee) {}
//Ask the user to choose a company/period...
myInstance.jd = new JDialog(view, Translator.getTranslation("Select the company and period for this session"), true);
myInstance.companyComponents = new CompanyComponents(myInstance.jd.getContentPane(),
Translator.getTranslation("Default Company"), true, "setup", view);
myInstance.companyComponents.AddPeriod(myInstance.jd.getContentPane(),
Translator.getTranslation("Period"), true);
if (myInstance.companyComponents.comboBox.getItemCount() > 1 ||
myInstance.companyComponents.comboBoxPeriod.getItemCount() > 1) {
myInstance.jd.getContentPane().setLayout(new GridLayout(4, 2));
myInstance.jd.getContentPane().add(new JLabel());
myInstance.jd.getContentPane().add(new JLabel());
myInstance.jd.getContentPane().add(new JLabel());
JButton button3 = new JButton(Translator.getTranslation("OK"));
button3.addActionListener(
new java.awt.event.ActionListener() {
//{{{ +actionPerformed(java.awt.event.ActionEvent) : void
public void actionPerformed(java.awt.event.ActionEvent evt) {
myInstance.companyComponents.saveDefaults();
SetupInfo.store();
myInstance.jd.setVisible(false);
myInstance.jd.dispose();
}//}}}
}
);
myInstance.jd.getContentPane().add(button3);
myInstance.jd.getRootPane().setDefaultButton(button3);
myInstance.jd.pack();
if (view != null) {
myInstance.jd.setLocationRelativeTo(view);
}
myInstance.jd.setVisible(true);
}
}
}
}
return myInstance;
}//}}}
//{{{ +makeConnection(String[], String, String, String, String)
/**
* Description of the Method
*
* @param createTables Description of the Parameter
* @param stringDriver Description of the Parameter
* @param stringConnect Description of the Parameter
* @param stringName Description of the Parameter
* @param stringPass Description of the Parameter
* @return Description of the Return Value
*/
public boolean makeConnection(String createTables[], String stringDriver,
String stringConnect, String stringName, String stringPass) {
bIsConnectionMade = false;
Statement stmt;
try {
drv = (Driver) Class.forName(stringDriver).newInstance();
} catch (Exception e) {
SystemLog.ErrorPrint("ClassNotFoundException: ");
SystemLog.ErrorPrintln(e.getMessage());
return false;
}
if (stringDriver.compareTo(MCKOI_DRIVER) == 0) {
File ff;
try {
ff = Fileio.getFile("db.conf", "data", false, false);
} catch (Exception ee) {
SystemLog.ErrorPrint("Cant find db.conf : " + ee.getMessage());
return false;
}
stringConnect = "jdbc:mckoi:local://" + ff.getAbsolutePath();
SystemLog.ErrorPrintln(stringConnect);
if (stringName.length() == 0 || stringPass.length() == 0) {
//mckoi requires some sort of password
stringName = "PasswordIsPass";
stringPass = "Pass";
SetupInfo.setProperty(SetupInfo.CONNECT_USERNAME, stringName);
SetupInfo.setProperty(SetupInfo.CONNECT_PASSWORD, stringPass);
SetupInfo.store();
}
try {
con = java.sql.DriverManager.getConnection(
stringConnect, stringName, stringPass);
} catch (Exception ex) {
// This URL specifies we are creating a local database. The
// configuration file for the database is found at './ExampleDB.conf'
// The 'create=true' argument means we want to create the database. If
// the database already exists, it can not be created.
stringConnect = "jdbc:mckoi:local://" + ff.getAbsolutePath() + "?create=true";
// The username/password for the database. This will be the username/
// password for the user that has full control over the database.
// ( Don't use this demo username/password in your application! )
// Make a connection with the database. This will create the database
// and log into the newly created database.
try {
con = DriverManager.getConnection(stringConnect, stringName, stringPass);
} catch (SQLException e) {
SystemLog.ErrorPrintln("ConnectionException: " + stringConnect + " : " + e.getMessage());
return false;
}
}
} else {
try {
con = DriverManager.getConnection(stringConnect, stringName, stringPass);
} catch (SQLException e) {
SystemLog.ErrorPrintln("ConnectionException: " + stringConnect + " : " + e.getMessage());
return false;
}
}
try {
stmt = con.createStatement();
for (int i = 0; i < createTables.length; i += 2) {
//check if the table exists first
try {
ResultSet rs = stmt.executeQuery(filterSQL(createTables[i]));
} catch (Exception ex) {
SystemLog.ErrorPrintln("Error executing test " + createTables[i]);
SystemLog.ErrorPrintln(ex.getMessage());
SystemLog.ErrorPrintln("Table probably does not exist, now creating");
//lets try creating the table
try {
stmt.executeUpdate(filterSQL(createTables[i + 1]));
} catch (Exception ex2) {
SystemLog.ErrorPrintln("Error creating table: " +
ex2.getMessage());
//no way to recover from this, get out
con.close();
return false;
}
}
}
} catch (Exception ex) {
SystemLog.ErrorPrintln("ConnectionException: " + stringConnect + " : " + ex.getMessage());
return false;
}
bIsConnectionMade = true;
return true;
}//}}}
//{{{ +class _PasswordDialog_
public static class PasswordDialog extends JDialog {
public boolean bIsCancel = false;
public JPasswordField jPass;
public JPasswordField jPass2;
public JTextField jUser;
boolean twoPasswords;
//{{{ +PasswordDialog(javax.swing.JFrame, boolean, boolean) : <init>
/**
* Constructor for the PasswordDialog object
*
* @param frame Description of the Parameter
* @param showUser Description of the Parameter
* @param twoPasswords Description of the Parameter
*/
public PasswordDialog(javax.swing.JFrame frame, boolean showUser, boolean twoPasswords) {
super(frame, Translator.getTranslation("Lazy 8 ledger"), true);
this.twoPasswords = twoPasswords;
getContentPane().setLayout(new GridLayout(showUser || twoPasswords ? 4 : 3, 2));
if (showUser) {
getContentPane().add(new JLabel(
Translator.getTranslation("User name")));
jUser = new JTextField();
getContentPane().add(jUser);
}
getContentPane().add(new JLabel(
Translator.getTranslation("Password")));
jPass = new JPasswordField();
getContentPane().add(jPass);
if (twoPasswords) {
getContentPane().add(new JLabel(
Translator.getTranslation("Enter the password again")));
jPass2 = new JPasswordField();
getContentPane().add(jPass2);
}
getContentPane().add(new JPanel());
getContentPane().add(new JPanel());
JButton button3 = new JButton(Translator.getTranslation("OK"));
Dimension ddm2 = button3.getPreferredSize();
Dimension ddm = new Dimension(120, ddm2.height);
button3.setPreferredSize(ddm);
button3.setMinimumSize(ddm);
button3.addActionListener(
new java.awt.event.ActionListener() {
//{{{ +actionPerformed(java.awt.event.ActionEvent) : void
public void actionPerformed(java.awt.event.ActionEvent evt) {
if (PasswordDialog.this.twoPasswords && new String(jPass.getPassword()).compareTo(
new String(jPass2.getPassword())) != 0) {
JOptionPane.showMessageDialog(PasswordDialog.this,
Translator.getTranslation("You must enter the same password 2 times"),
Translator.getTranslation("Update not entered"),
JOptionPane.PLAIN_MESSAGE);
return;
}
buttonOK();
}//}}}
}
);
getContentPane().add(button3);
if (showUser) {
JButton button4 = new JButton(Translator.getTranslation("Cancel"));
button4.addActionListener(
new java.awt.event.ActionListener() {
//{{{ +actionPerformed(java.awt.event.ActionEvent) : void
public void actionPerformed(java.awt.event.ActionEvent evt) {
buttonCancel();
}//}}}
}
);
getContentPane().add(button4);
} else {
getContentPane().add(new JLabel());
}
getRootPane().setDefaultButton(button3);
pack();
setLocationRelativeTo(frame);
setVisible(true);
}//}}}
//{{{ +buttonCancel() : void
/**
* Description of the Method
*/
public void buttonCancel() {
bIsCancel = true;
setVisible(false);
}//}}}
//{{{ +buttonOK() : void
/**
* Description of the Method
*/
public void buttonOK() {
bIsCancel = false;
setVisible(false);
}//}}}
}//}}}
}