public void updateAddress(int ssn, String address)
{
MakeDataConnection dataConnection = new MakeDataConnection();
SqlConnection conn = dataConnection.makeConnection();
try
{
conn.Open();
SqlCommand getName = new SqlCommand("SELECT name FROM Customer where ssn = " + ssn + " AND isDelete = 0", conn);
SqlDataReader reader = null;
reader = getName.ExecuteReader();
reader.Read();
String name = Convert.ToString(reader["name"]);
reader.Close();
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = "UPDATE Customer SET isDelete = 1 WHERE ssn = " + ssn + " AND isDelete = 0;"+
"INSERT INTO Customer VALUES (" + ssn + ",'" + name + "','" + address + "', CURRENT_TIMESTAMP,0)";
sqlCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void creditMoney(int ssn, String accNo, String branchCode, String money)
{
MakeDataConnection dataConnection = new MakeDataConnection();
SqlConnection conn = dataConnection.makeConnection();
try
{
conn.Open();
SqlCommand getBalance = new SqlCommand("SELECT balance FROM account where accNo = '"+accNo+"' AND isDelete = 0",conn);
SqlDataReader reader = null;
reader = getBalance.ExecuteReader();
reader.Read();
int currentBalance = Convert.ToInt32(reader["balance"]);
int newBalance = currentBalance - Convert.ToInt32(money);
reader.Close();
//add data for accManagment
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = "UPDATE account SET isDelete = 1 WHERE accNo = " + accNo + " AND isDelete = 0;"+
"UPDATE AccManagement SET isDelete = 1 WHERE ssn = " + ssn + " AND isDelete = 0;"+
"INSERT INTO AccManagement (ssn,accNo,credit,transactionTime,isDelete) values(" + ssn + ",'" + accNo + "'," + money + ", CURRENT_TIMESTAMP,0);"+
"INSERT INTO Account values('" + accNo + "','" + branchCode + "'," + newBalance + ", CURRENT_TIMESTAMP,0)";
sqlCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void startAcc(int ssn, String name, String address, String contactNo, String debit, String accNo,String branchCode)
{
MakeDataConnection dataConnection = new MakeDataConnection();
SqlConnection conn = dataConnection.makeConnection();
try
{
conn.Open();
//check ssn is available or not in customer
SqlCommand getRecord = new SqlCommand("select * from customer where ssn = "+ssn+" and isDelete = 0",conn);
SqlDataReader reader = null;
reader = getRecord.ExecuteReader();
if (!reader.Read())
{
//add new customer in to db
reader.Close();
SqlCommand cmd = new SqlCommand("INSERT INTO customer values(" + ssn + ",'" + name + "','" + address + "', CURRENT_TIMESTAMP,0)", conn);
cmd.ExecuteNonQuery();
//add contact for new customer
//SqlCommand addContacts = new SqlCommand("INSERT INTO CustomerContacts values(" + ssn + ",'" + contactNo + "', CURRENT_TIMESTAMP,0)", conn);
//addContacts.ExecuteNonQuery();
//add data for accManagment
SqlCommand addAccManagement = new SqlCommand("INSERT INTO AccManagement (ssn,accNo,debit,transactionTime,isDelete) values(" + ssn + ",'" + accNo + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
addAccManagement.ExecuteNonQuery();
SqlCommand addAccount = new SqlCommand("INSERT INTO Account values('" + accNo + "','" + branchCode + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
addAccount.ExecuteNonQuery();
}
else
{
reader.Close();
SqlCommand addAccManagement = new SqlCommand("INSERT INTO AccManagement (ssn,accNo,debit,transactionTime,isDelete) values(" + ssn + ",'" + accNo + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
addAccManagement.ExecuteNonQuery();
SqlCommand addAccount = new SqlCommand("INSERT INTO Account values('" + accNo + "','" + branchCode + "'," + debit + ", CURRENT_TIMESTAMP,0)", conn);
addAccount.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//public CreditDebitHistory(int ssn, String accNo, String starTime, String endTime)
//{
// getHistoyr(ssn, accNo,starTime,endTime);
//}
public DataTable getHistory(int ssn, String accNo, String starTime, String endTime)
{
DataTable table = new DataTable();
MakeDataConnection dataConnection = new MakeDataConnection();
SqlConnection conn = dataConnection.makeConnection();
try
{
conn.Open();
//SqlCommand getHistory = new SqlCommand("SELECT credit,debit, transactionTime as time FROM AccManagement WHERE ssn= "+ssn+" AND accNo='"+accNo+"' AND transactionTime<'"+endTime+"' AND transactionTime>'"+starTime+"'", conn);
//SqlDataReader reader = null;
//reader = getHistory.ExecuteReader();
//Console.WriteLine("{0}\t\t{1}\t\t{2}", "Credit", "Debit", "Time");
//while (reader.Read())
//{
// //Console.WriteLine(Convert.ToString(reader["credit"]), Convert.ToString(reader["debit"]));
// Console.WriteLine("|{0}|{1}|{2}", reader["credit"], reader["debit"], reader["time"]);
//}
//reader.Close();
String command = "SELECT credit,debit, transactionTime as time FROM AccManagement WHERE ssn= " + ssn + " AND accNo='" + accNo + "' AND transactionTime<'" + endTime + "' AND transactionTime>'" + starTime + "'";
using (SqlDataAdapter a = new SqlDataAdapter(command, conn))
{
// 3
// Use DataAdapter to fill DataTable
//DataTable t = new DataTable();
a.Fill(table);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return table;
}