public static bool SqlNonQuery(string query)
{
if (Program.GlobalConn == null)
{
Program.GlobalConn = Functions.OpenSqlConnection(Program.Server, Program.currentDB, Program.SqlUser, Program.Password, Program.Port);
}
if (Program.GlobalConn.State != ConnectionState.Open)
{
Program.GlobalConn = Functions.OpenSqlConnection(Program.Server, Program.currentDB, Program.SqlUser, Program.Password, Program.Port);
}
MySqlConnection conn = Program.GlobalConn;
MySqlCommand cmd;
string s0 = query;
try
{
cmd = new MySqlCommand(s0, conn);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
return(false);
}
}
void FrmConnectionConfigLoad(object sender, EventArgs e)
{
try
{
string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
FileStream fs = new FileStream(path + @"\Config.dat", FileMode.Open);
BinaryFormatter bf = new BinaryFormatter();
dt = (DataTable)bf.Deserialize(fs);
fs.Close();
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
if (dt.Rows.Count > 0)
{
txb_Server.Text = dt.Rows[0]["Server"].ToString();
txb_Port.Text = dt.Rows[0]["Port"].ToString();
txb_UserName.Text = dt.Rows[0]["Username"].ToString();
txb_Password.Text = dt.Rows[0]["Password"].ToString();
txb_Database.Text = dt.Rows[0]["Database"].ToString();;
}
else
{
dt.Columns.Clear();
dt.Columns.Add("Server");
dt.Columns.Add("Port");
dt.Columns.Add("Username");
dt.Columns.Add("Password");
dt.Columns.Add("Database");
}
}
void Btn_SaveClick(object sender, EventArgs e)
{
try
{
btn_Save.Enabled = false;
dt = new DataTable();
dt.Columns.Add("Server");
dt.Columns.Add("Port");
dt.Columns.Add("Username");
dt.Columns.Add("Password");
dt.Columns.Add("Database");
DataRow r = dt.NewRow();
r["Server"] = txb_Server.Text;
r["Port"] = txb_Port.Text;
r["Username"] = txb_UserName.Text;
r["Password"] = txb_Password.Text;
r["Database"] = txb_Database.Text;
dt.Rows.Add(r);
string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
FileStream fs = new FileStream(path + @"\Config.dat", FileMode.Create);
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(fs, dt);
fs.Close();
MessageBox.Show("Changes saved successfully");
Program.Server = txb_Server.Text;
Program.Port = txb_Port.Text;
Program.SqlUser = txb_UserName.Text;
Program.Password = txb_Password.Text;
if (txb_Database.Text != "")
{
Program.currentDB = txb_Database.Text;
Program.IsDefaultDBset = true;
}
else
{
Program.currentDB = "";
Program.IsDefaultDBset = false;
}
this.Close();
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
finally
{
btn_Save.Enabled = true;
}
}
public static DataTable GetTable(string query, MySqlConnection conn, bool CloseConnAfter = true, bool dontshowerrors = false)
{
if (conn == null)
{
if (!dontshowerrors)
{
MessageBox.Show("Connection is Null", "Connectivity Error: GetTable()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(new DataTable()); //empty datatable
}
if (conn.State != ConnectionState.Open)
{
if (!dontshowerrors)
{
MessageBox.Show("Connection is not open", "Connectivity Error: GetTable()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(new DataTable()); //empty datatable
}
DataTable dt = new DataTable();
try
{
MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
//da.SelectCommand.CommandTimeout = 0;
dt.Clear();
da.Fill(dt);
if (CloseConnAfter)
{
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
da.Dispose();
return(dt);
}
catch (Exception ex)
{
if (!dontshowerrors)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
return(null);
}
finally
{
if (CloseConnAfter)
{
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
}
}
public static bool SqlNonQuery(string query, MySqlConnection conn)
{
MySqlCommand cmd;
string s0 = query;
try
{
cmd = new MySqlCommand(s0, conn);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
return(false);
}
}
public static MySqlConnection OpenSqlConnection(string server, string db, string u, string p, string port, bool dontShowErrors = false)
{
string connStr = string.Format("server={0};database={1};user={2};port={3};password={4};Allow User Variables=true;", server, db, u, port, p);
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
return(conn);
}
catch (Exception ex)
{
if (!dontShowErrors)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
return(null);
}
}
public static DataTable GetTable(string query, bool setCommandTimeoutToZero = true, bool DontShowErrors = false)
{
if (Program.GlobalConn == null)
{
Program.GlobalConn = Functions.OpenSqlConnection(Program.Server, Program.currentDB, Program.SqlUser, Program.Password, Program.Port, DontShowErrors);
}
if (Program.GlobalConn == null)
{
if (!DontShowErrors)
{
MessageBox.Show("Unable to connect to MySQL server.", "Connectivity Error: GetTable()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(new DataTable()); // empty datatable
}
if (Program.GlobalConn.State != ConnectionState.Open)
{
Program.GlobalConn = Functions.OpenSqlConnection(Program.Server, Program.currentDB, Program.SqlUser, Program.Password, Program.Port, DontShowErrors);
}
MySqlConnection conn = Program.GlobalConn;
// MySqlCommand cmd;
// string s0;
DataTable dt = new DataTable();
try
{
MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
if (setCommandTimeoutToZero)
{
da.SelectCommand.CommandTimeout = 300;
}
//da.SelectCommand.CommandTimeout = 0;
dt.Clear();
da.Fill(dt);
da.Dispose();
return(dt);
}
catch (Exception ex)
{
if (!DontShowErrors)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
//return new DataTable(); // empty datatable
return(null);
}
finally
{
}
}
public void openCompany()
{
if (lb_databases.SelectedIndex >= 0 || Program.IsDefaultDBset == true)
{
MySqlConnection conn = new MySqlConnection();
try
{
string database = Program.IsDefaultDBset == true ? Program.currentDB : "GBC_" + lb_databases.SelectedItem.ToString().Replace(' ', '_');
string connStr = string.Format("server={0};database={1};user={2};port={3};password={4};", Program.Server, database, Program.SqlUser, Program.Port, Program.Password);
Program.GlobalConn = new MySqlConnection(connStr);
Program.GlobalConn.Open();
DataTable dt = Functions.GetTable("show tables like '" +
Program.ModName_lcns + "_users'");
if (dt.Rows.Count == 0)
{
var dialogResult = MessageBox.Show("[" + Program.ThisModuleName + "] Module has not been configured with selected database. "
+ "Do you want to configure now?\n\nIt is recommended to open Accounting Module and save backup before configuring modules.", "Module Configuration", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
// module configuration code
ConfigureModule();
}
else
{
return;
}
}
else
{
// code to open the company i.e. check userid and password
dt = Functions.GetTable("select name, password from "
+ Program.ModName_lcns + "_users where name = '" + txb_userid.Text + "'");
if (dt.Rows.Count == 0)
{
MessageBox.Show("User ID or Password not correct");
}
else
{
string decryptedPassword = StringCipher.Decrypt(dt.Rows[0]["password"].ToString(), Program.EncryptionKey);
if (string.Compare(decryptedPassword, txb_password.Text) == 0)
{
//userid and password are correct
Program.currentUSER = txb_userid.Text;
Program.MainWindow.EnableMenues(true);
Program.MainWindow.Text += " - [User: "******"] ";
this.Close();
}
else
{
MessageBox.Show("User ID or Password not correct");
}
}
}
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB();
emb.ShowMB(ex);
}
}
}
private void FrmUserAuth_Load(object sender, EventArgs e)
{
DataTable ConnConfigDT = new DataTable();
try
{
string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
FileStream fs = new FileStream(path + @"\Config.dat", FileMode.Open);
BinaryFormatter bf = new BinaryFormatter();
ConnConfigDT = (DataTable)bf.Deserialize(fs);
fs.Close();
if (ConnConfigDT.Rows[0]["Database"].ToString() != "")
{
Program.currentDB = ConnConfigDT.Rows[0]["Database"].ToString();
Program.IsDefaultDBset = true;
}
else
{
Program.currentDB = "";
Program.IsDefaultDBset = false;
}
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
if (Program.IsDefaultDBset)
{
label3.Text = "Current Database: " + Program.currentDB;
lb_databases.Hide();
this.Height = 164;
}
if (Program.IsDefaultDBset)
{
return; //dont get list of databases
}
lb_databases.Items.Clear();
string connStr = string.Format("server={0};user={1};port={2};password={3};", Program.Server, Program.SqlUser, Program.Port, Program.Password);
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand cmd;
string s0;
try
{
// show list of databases
conn.Open();
s0 = "SHOW DATABASES;";
cmd = new MySqlCommand(s0, conn);
MySqlDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
string row = "";
for (int i = 0; i < Reader.FieldCount; i++)
{
row = Reader.GetValue(i).ToString();
if (row.Length > 4)
{
if (row.Substring(0, 4) == "gbc_")
{
lb_databases.Items.Add(row.Substring(4).Replace('_', ' ').ToUpper());
}
}
}
}
if (lb_databases.Items.Count == 0)
{
MessageBox.Show("No database exists. Please create new database through accounting system.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
MessageBox.Show("Not able to connect to MySQL Server. Please Enter MySQL Server Information.", "Connection Failure", MessageBoxButtons.OK, MessageBoxIcon.Information);
FrmConnectionConfig FCC = new FrmConnectionConfig();
FCC.ShowDialog();
this.Close();
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
if (lb_databases.Items.Count > 0)
{
lb_databases.SetSelected(0, true);
txb_userid.Focus();
}
}
private void ConfigureModule()
{
try
{
string userID = "";
string password = "";
FrmNewAdminUser fna = new FrmNewAdminUser();
if (fna.ShowDialog() == DialogResult.OK)
{
userID = fna.userid;
password = StringCipher.Encrypt(fna.password, Program.EncryptionKey);
}
else
{
return;
}
Functions.SqlNonQueryWithoutExHandled("CREATE TABLE if not exists `" + Program.ModName_lcns + "_users` ( " +
"`id` INT NOT NULL AUTO_INCREMENT COMMENT '', " +
"`name` VARCHAR(255) NULL COMMENT '', " +
"`password` VARCHAR(255) NULL COMMENT '', " +
"`is_admin` TINYINT NULL COMMENT '', " +
"PRIMARY KEY(`id`) COMMENT '', " +
"UNIQUE INDEX `name_UNIQUE` (`name` ASC) COMMENT '') engine = innodb; ",
Program.GlobalConn);
Functions.SqlNonQueryWithoutExHandled("CREATE TABLE if not exists `" + Program.ModName_lcns + "_user_permissions` ( " +
"`id` INT NOT NULL AUTO_INCREMENT COMMENT '', " +
"`user_id` INT NOT NULL COMMENT '', " +
"`permission` VARCHAR(255) NOT NULL COMMENT '', " +
"PRIMARY KEY(`id`) COMMENT '', " +
"INDEX `fk_" + Program.ModName_lcns + "_permissions_userid_idx` (`user_id` ASC) COMMENT '', " +
"CONSTRAINT `fk_" + Program.ModName_lcns + "_permissions_userid` " +
"FOREIGN KEY (`user_id`) " +
"REFERENCES `" + Program.ModName_lcns + "_users` (`id`) " +
"ON DELETE CASCADE " +
"ON UPDATE CASCADE) engine = innodb; ",
Program.GlobalConn);
Functions.SqlNonQueryWithoutExHandled("ALTER TABLE `" + Program.ModName_lcns + "_user_permissions` " +
"ADD COLUMN `permission_group` VARCHAR(255) NULL AFTER `user_id`;", Program.GlobalConn);
Functions.SqlNonQueryWithoutExHandled("INSERT INTO `" + Program.ModName_lcns + "_users` (`name`, `password`, `is_admin`) VALUES ('" + userID + "', '" + password + "', '1')", Program.GlobalConn);
// MODULE RELATED TABLES - START
//Functions.SqlNonQueryWithoutExHandled("", Program.GlobalConn);
Functions.SqlNonQueryWithoutExHandled("CREATE TABLE `" + Program.ModName_lcns + "_factor` ( " +
"`id` INT NOT NULL AUTO_INCREMENT COMMENT '', " +
"`coa_name` VARCHAR(500) NOT NULL COMMENT '', " +
"`factor` DECIMAL(40,5) NOT NULL COMMENT '', " +
"PRIMARY KEY (`id`) COMMENT '', " +
"INDEX `fk_coa_name_idx` (`coa_name` ASC) COMMENT '', " +
"CONSTRAINT `fk_coa_name` " +
"FOREIGN KEY (`coa_name`) " +
"REFERENCES `coa` (`Name`) " +
"ON DELETE CASCADE " +
"ON UPDATE CASCADE) engine = innodb;", Program.GlobalConn);
Functions.SqlNonQueryWithoutExHandled("ALTER TABLE `poultry_pm_factor` " +
"ADD UNIQUE INDEX `coa_name_UNIQUE` (`coa_name` ASC);", Program.GlobalConn);
// ENTRIES AND RECIPE TABLES SHOULD BE SAME
Functions.SqlNonQueryWithoutExHandled("ALTER TABLE `entries` " +
"ADD COLUMN `supply_rate` DECIMAL(40,5) NULL DEFAULT NULL AFTER `Qty`, " +
"ADD COLUMN `factor` DECIMAL(20,5) NULL DEFAULT NULL AFTER `supply_rate`;", Program.GlobalConn);
Functions.SqlNonQueryWithoutExHandled("ALTER TABLE `recipes` " +
"ADD COLUMN `supply_rate` DECIMAL(40,5) NULL DEFAULT NULL AFTER `Qty`, " +
"ADD COLUMN `factor` DECIMAL(20,5) NULL DEFAULT NULL AFTER `supply_rate`;", Program.GlobalConn);
// ENTRIES AND RECIPE TABLES SHOULD BE SAME
MessageBox.Show("Module configured successfully.", "Module Configuration", MessageBoxButtons.OK, MessageBoxIcon.Information);
// MODULE RELATED TABLES - END
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB();
emb.ShowMB(ex);
}
finally
{
}
}
