public ActionResult Create(CreateChart Chart)
{
if (ModelState.IsValid)
{
string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;";
string CHART_ID ;
using (OracleConnection connection = new OracleConnection(connectionString))
{
string number = null;
number = "";
OracleCommand cmd1 = new OracleCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "select nvl(max(substr(CHART_ID,7,3)),0) from KMIINTRANET_MASTER_CHART where substr(CHART_ID,1,6)='" + Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + "'";
cmd1.Connection = connection;
connection.Open();
objDataReader = cmd1.ExecuteReader();
if (objDataReader.HasRows)
{
objDataReader.Read();
number = Convert.ToString(Convert.ToInt32(objDataReader[0]) + 1);
if (number.Length == 1)
{
number = "00" + number;
}
else if (number.Length == 2)
{
number = "0" + number;
}
else if (number.Length == 3)
{
number = number;
}
}
cmd1 = null;
connection.Close();
OracleCommand cmd2 = new OracleCommand();
CHART_ID = Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + number;
byte[] tempFile = new byte[Chart.ChartFile.InputStream.Length];
Chart.ChartFile.InputStream.Read(tempFile, 0, tempFile.Length);
cmd2.CommandText = "INSERT INTO KMIINTRANET_MASTER_CHART(CHART_ID,CHART_TITLE,CHART_FILE,CREATE_BY,CREATE_DATE) VALUES (:chartid,:title,:chartfile,'" + Session["USER"] + "',sysdate)";
cmd2.CommandType = CommandType.Text;
cmd2.Connection = connection;
connection.Open();
cmd2.Parameters.Add(":chartfile", OracleType.Blob).Value = tempFile;
cmd2.Parameters.Add(":title", OracleType.VarChar).Value = Chart.title.ToString();
cmd2.Parameters.Add(":chartid", OracleType.VarChar).Value = CHART_ID;
cmd2.ExecuteNonQuery();
cmd2 = null;
connection.Close();
return RedirectToAction("Index");
}
}
return View();
}
/*
* Finding the difference between the start booking date and end booking date
* to calculate the cost of a booking
*/
public void calculateBookingCost(DateTimePicker startDate, DateTimePicker endDate, ComboBox cbo_Description, TextBox txt_Cost)
{
connection.Close();
DateTime dt1 = Convert.ToDateTime(startDate.Text);
DateTime dt2 = Convert.ToDateTime(endDate.Text);
TimeSpan timeSpan = dt2 - dt1;
int numberOfDays = timeSpan.Days;
string query_String = "SELECT Car_Rate FROM Car_Class WHERE Description ='" + cbo_Description.Text + "'";
connection.Open();
try
{
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (data_reader.HasRows)
{
data_reader.Read();
txt_Cost.Text = ("" + data_reader.GetInt32(0) * numberOfDays);
}
connection.Close();
}
catch (Exception) { }
}
int MSOracleClient()
{
System.Data.OracleClient.OracleConnectionStringBuilder connBuilder = new System.Data.OracleClient.OracleConnectionStringBuilder();
connBuilder.DataSource = txtDataSource.Text.Trim();
connBuilder.UserID = txtUserId.Text.Trim();
connBuilder.Password = txtPwd.Text.Trim();
connBuilder.LoadBalanceTimeout = 60;
connBuilder.MinPoolSize = 0;
connBuilder.MaxPoolSize = 50;
int rows = 0;
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString))
{
//System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString);
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text.Trim();
cmd.CommandTimeout = 300;
//cmd.ResetCommandTimeout();
conn.Open();
using (System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
rows++;
}
}
return(rows);
}
}
/*
* This method selects all details of a selected combo box class description and displays the details in the selected text boxes
*/
public void selectDetails(ComboBox cmo_Description, TextBox txt_Class_Id, TextBox txt_Display_Desc, TextBox txt_Rate)
{
try
{
//Select query that brings back the details of car class using description
string query_String2 = String.Format("SELECT * FROM Car_Class WHERE Description = '{0}'", cmo_Description.Text);
connection.Open();
cmd = connection.CreateCommand();
/*
* Creating the query string to retrieve the required record when a customer id is supplied
*/
cmd.CommandText = query_String2;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
/*
* Assigning the values of the Car_Class table to the appropriate text boxes after retreving
*/
if (!data_reader.HasRows)
return;
txt_Class_Id.Text = data_reader.GetValue(0).ToString();
txt_Display_Desc.Text = data_reader.GetValue(1).ToString();
txt_Rate.Text = data_reader.GetValue(2).ToString();
}
connection.Close();
}
catch (Exception) // Catching exception if the id dosent match any records
{
//MessageBox.Show("No Record Found");
MessageBox.Show("No Records Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
//Filling the booking details into the return cars datagridview
public void bookingsDetails(DataGridView grd_Bookings)
{
var tomorrow = DateTime.Today.AddDays(1).ToString("dd-MMM-yyyy");
var dueDate = DateTime.Today.AddDays(-5).ToString("dd-MMM-yyyy");
string query_String = string.Format("SELECT DISTINCT B.Customer_Id ,Forename, Surname ,B.Car_Reg ,Cost ,Customer_Type,Return_Date FROM Cars CA, " +
"Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " +
"WHERE Car_Status = 'U' AND CA.Car_Reg = B.Car_Reg AND Return_Date BETWEEN '{0}' AND '{1}'", dueDate, tomorrow );
// MessageBox.Show(query_String);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Bookings");
grd_Bookings.DataSource = data_Set.Tables["Bookings"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception ex) {MessageBox.Show("" + ex);}
}
public static string ToJSON(OracleDataReader rdr)
{
StringBuilder sb = new StringBuilder();
if (rdr.HasRows)
{
sb.Append("[");
while (rdr.Read())
{
sb.Append("{");
for (var i = 0; i < rdr.FieldCount; i++)
{
sb.Append("\""+rdr.GetName(i)+"\":\"");
sb.Append(rdr[i].ToString().Trim() + "\",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("},");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
else
{
sb.Append("[]");
}
return sb.ToString();
}
public int getmaxattendenceid(string eid)
{
int x = 0;
//int x1 = chkentry(eid);
//if (x1 == -1)
{
string qry = "select max(aid) from attendance";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
string str = reader.GetValue(0).ToString();
if (str != "")
{
x = Convert.ToInt32(str);
}
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(x);
}
/*else
* return -1;*/
}
public void fillAccountDetails(DataGridView dataGridAccounts)
{
string query_String = "SELECT DISTINCT C.Customer_Id, COUNT(C.Customer_Id) AS NO_OF_Bookings, SUM(Balance)AS Balance ,Forename,Surname,Address,Town,County,Phone_No, Account_Status " +
"FROM Customers C RIGHT JOIN Accounts A ON C.Customer_Id = A.Customer_Id WHERE Account_Status = 'U' AND C.Customer_Id IN(SELECT Customer_Id FROM Accounts )" +
"GROUP BY C.Customer_Id,Address,Forename,Surname ,Town,County,Phone_No ,Account_Status ORDER BY C.Customer_Id";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Accounts");
dataGridAccounts.DataSource = data_Set.Tables["Accounts"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception)
{
MessageBox.Show("No record Found");
}
}
//2/09/2005 - Madan - web_users table in VITAP.world
//Input parameter UserName = First Name Initial, LastName
public static string IsValidWebUser(string UserName, string Password, System.Data.OracleClient.OracleConnection App_Connection)
{
string Message = "";
try {
System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand();
CmdLotusMail.CommandText = "SELECT password from web_users WHERE (externalservice ='T' or app_dfas_fl ='T') and upper(login_id) = '" + Utilities.SqlEncode(UserName.ToUpper().Trim()) + "'";
CmdLotusMail.Connection = App_Connection; //ConnLotusMail;
if (App_Connection.State != System.Data.ConnectionState.Open)
{
CmdLotusMail.Connection.Open();
}
System.Data.OracleClient.OracleDataReader myReader = CmdLotusMail.ExecuteReader();
if (myReader.Read())
{
Message = myReader["password"].ToString().Trim();
}
myReader.Close();
CmdLotusMail.Connection.Close();
}
catch (System.Exception ex) { // if exception occurs, make an entry in the server event log
EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message);
}
return(Message.Trim());
} //end of this method
public int getTotalAbsent(string date)
{
string qry = "select distinct count(eid)\n" +
"from employee\n" +
"where eid not in(select emp_id\n" +
"from attendance\n" +
"where to_date('" + date + "','dd/mm/yyyy')=to_date(to_char(attendance_date,'dd/mm/yyyy'),'dd/mm/yyyy'))\n" +
"and eid in(select eid\n" +
"from ba_leave\n" +
"where to_date('" + date + "','dd/mm/yyyy') between start_date and end_date)\n";
string totalEmp = "";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
totalEmp = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(Convert.ToInt32(totalEmp));
}
}//end of method
public static string AuthenticateLotusNotesUser(string firstName, string lastName, System.Data.OracleClient.OracleConnection App_Connection)
{
string Message = "";
string UserName1 = firstName.Trim().ToUpper() + " " + lastName.Trim().ToUpper();
string UserName2 = lastName.Trim().ToUpper() + ", " + firstName.Trim().ToUpper();
try {
System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand();
CmdLotusMail.CommandText = "SELECT Notesname from maildir WHERE Notesname = '" + Utilities.SqlEncode(UserName1.ToUpper().Trim()) + "' or upper(Name) ='" + Utilities.SqlEncode(UserName2) + "'";
CmdLotusMail.Connection = App_Connection;// ConnLotusMail;
if (App_Connection.State != System.Data.ConnectionState.Open)
{
CmdLotusMail.Connection.Open();
}
System.Data.OracleClient.OracleDataReader NotesReader = CmdLotusMail.ExecuteReader();
if (NotesReader.Read())
{
Message = NotesReader["Notesname"].ToString().Trim();
}
NotesReader.Close();
CmdLotusMail.Connection.Close();
}
catch (System.Exception ex) { // if exception occurs, make an entry in the server event log
EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message);
}
return(Message);
}//end of method
} //end of this method
// Madan Saini 03/29/2004 -- Clear Text Passwords for PMT
// This method validate an Internet user's membership of a web application
public static bool IsDBUserValid(string loginID, string password, string appName, System.Data.OracleClient.OracleConnection conVITAP)
{
bool valid = false;
//string encodedPassword = SharedPassword.Encode(password);
loginID = loginID.ToUpper();
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = Queries.getValidUser(loginID, password, appName);
System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader();
if (rdrReader.Read())
{
valid = true;
}
else
{
valid = false;
}
cmdVITAP.Dispose();
return(valid);
} //end of this method
/*
* The Description combo box is populated with each car class description from the database
*/
public void display_Description(ComboBox cmo_Description)
{
cmo_Description.Items.Clear();
string query_String = "Select Description FROM Car_Class ";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
if (!data_reader.HasRows)
return;
cmo_Description.Items.Add(data_reader[0].ToString());
}
connection.Close();
}
catch (Exception)
{
MessageBox.Show("Cannot Retrive Description", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
/// <summary>
/// 输出blob
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public byte[] OutputBlob(string strSql)
{
//string block="SELECT image from test_image WHERE id = 100";//INTO :b
// Set command to create Anonymous PL/SQL Block
this.command.Connection = this.con as OracleConnection;
if (this.command.Transaction == null) //没有事务，用全局事务
{
this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction;
}
command.CommandText = strSql + "";
command.CommandType = System.Data.CommandType.Text;
System.Data.OracleClient.OracleDataReader reader;
command.Parameters.Clear();
// The anonymous PL/SQL block retrieves the images as an output parameter
try
{
//cmd.ExecuteNonQuery();
reader = command.ExecuteReader();
}
catch (OracleException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.Code;
this.WriteErr();
return(null);
}
catch (Exception ex)
{
this.Err = ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.WriteErr();
return(null);
}
reader.Read();
// Create a byte array
byte[] byteData = new byte[0];
// fetch the value of Oracle parameter into the byte array
//byteData = (byte[])(cmd.Parameters[0].Value);
try
{
byteData = (byte[])(reader[0]);
}
catch (Exception ex) {
this.Err = ex.Message;
reader.Close();
return(null);
}
reader.Close();
return(byteData);
}
public void SetUp()
{
Exception exp = null;
BeginCase("Setup");
try
{
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
cmd = new OracleCommand("Select OrderID, CustomerID, OrderDate From Orders", con);
rdr = cmd.ExecuteReader();
rdr.Read();
}
catch(Exception ex){exp = ex;}
finally {EndCase(exp);}
}
public List <UsuarioSistema> ConsultarUsuario(UsuarioSistema user)
{
ConexaoOracle objConexao = new ConexaoOracle();
List <UsuarioSistema> list = new List <UsuarioSistema>();
UsuarioSistema usu;
try
{
objConexao.AdicionarParametro("P_LOGIN", user.login);
objConexao.AdicionarParametro("P_NOME", user.nome);
objConexao.AdicionarParametro("P_DEPARTAMENTO", user.departamento);
objConexao.AdicionarParametro("P_EMAIL", user.email);
objConexao.AdicionarParametroCursor("dados");
System.Data.OracleClient.OracleDataReader leitor = objConexao.ObterLeitor("FUN_PKG_USUARIO.LISTAR_USUARIOS_IMPORTADOS");
while (leitor.Read())
{
usu = new UsuarioSistema();
usu.departamento = leitor["DEPARTAMENTO"].ToString();
usu.email = leitor["EMAIL"].ToString();
usu.login = leitor["LOGIN"].ToString();
usu.nome = leitor["NOME"].ToString();
usu.dt_inclusao = DateTime.Parse(leitor["DT_INCLUSAO"].ToString());
usu.id_usuario = int.Parse(leitor["ID_USUARIO"].ToString());
usu.id_status = int.Parse(leitor["status"].ToString());
usu.descricao_status = usu.id_status == 0 ? "INATIVO" : "ATIVO";
list.Add(usu);
}
leitor.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: \\n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(list);
}
public string getEmpNamefromDb(string empId)
{
string name = "";
string qry = "select name from employee where eid = '" + empId + "'";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
name = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(name);
}
/*
* Creating the query string to retrieve the required record when a customer id is supplied
*/
public void customerDetails(TextBox txt_Retrieve_Id, TextBox txt_Forename,TextBox txt_Cust_Id,
TextBox txt_Surname, TextBox txt_Address, TextBox txt_Town,
TextBox txt_County, TextBox txt_Phone_No, TextBox txt_Cust_Type)
{
/*
*Check For empty String when retrieve button is pressed
*/
string query_String = "SELECT * FROM Customers Where Customer_Id = " + txt_Cust_Id.Text;
try
{
connection.Open();//opening a connection to the database
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
if (!data_reader.HasRows)
return;
txt_Retrieve_Id.Text = data_reader.GetValue(0).ToString();
txt_Forename.Text = data_reader.GetValue(1).ToString();
txt_Surname.Text = data_reader.GetValue(2).ToString();
txt_Address.Text = data_reader.GetValue(3).ToString();
txt_Town.Text = data_reader.GetValue(4).ToString();
txt_County.Text = data_reader.GetValue(5).ToString();
txt_Phone_No.Text = data_reader.GetValue(6).ToString();
txt_Cust_Type.Text = data_reader.GetValue(7).ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
finally
{
connection.Close();
}
}
public string getStartTimefromDb(string empId)
{
string startTime = "";
string qry = "select Start_time from attendance where emp_id = '" + empId + "' and to_date(attendance_date,'dd-mm-yyyy')= to_date(sysdate,'dd-mm-yyyy')";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
startTime = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(startTime);
}
public string getEndTimefromDbwithdate(string empId, string date)
{
string endTime = "";
string qry = "select end_time from attendance where emp_id = '" + empId + "' and to_date(to_char(attendance_date,'dd/mm/yyyy'),'dd-mm-yyyy')= to_date('" + date + "','dd-mm-yyyy')";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
endTime = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(endTime);
}
public string getCurrentDate()
{
string qry = "select to_char(sysdate,'dd/mm/yyyy') from dual";
string date = "";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
date = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(date);
}
public void SetUp()
{
Exception exp = null;
BeginCase("Setup");
try
{
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
cmd = new OracleCommand("Select EmployeeID From Employees Where FirstName = 'Oved'", con);
cmd.CommandType = CommandType.Text;
con.Open();
rdr = cmd.ExecuteReader();
rdr.Read();
typeName = rdr.GetDataTypeName(0);
con.Close();
}
catch(Exception ex){exp = ex;}
finally {EndCase(exp);}
}
public int getTotalLeave(string date)
{
string qry = "select distinct count(eid) from ba_leave where to_date('" + date + "','dd/mm/yyyy') between start_date and end_date";
string totalEmp = "";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
totalEmp = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(Convert.ToInt32(totalEmp));
}
public int checklogin(string eid)
{
int x = 0;
string qry = "select count(*) from attendance where emp_id = '" + eid + "' and to_date(attendance_date,'dd-mm-yyyy') = to_date(SYSDATE,'dd-mm-yyyy')";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
string aid;
aid = reader.GetValue(0).ToString();
x = Convert.ToInt32(aid);
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(x);
}
public int getTotalEmp()
{
string qry = "select count(eid) from employee";
string totalEmp = "";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
totalEmp = reader.GetValue(0).ToString();
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
int x = Convert.ToInt32(totalEmp);
return(x);
}
public List <UsuarioHistorico> ConsultarMovimentacao(UsuarioSistema user)
{
ConexaoOracle objConexao = new ConexaoOracle();
List <UsuarioHistorico> list = new List <UsuarioHistorico>();
UsuarioHistorico usu;
try
{
objConexao.AdicionarParametro("P_LOGIN", user.login);
objConexao.AdicionarParametroCursor("dados");
System.Data.OracleClient.OracleDataReader leitor = objConexao.ObterLeitor("FUN_PKG_USUARIO.LISTAR_MOVIMENTACAO_USUARIO");
while (leitor.Read())
{
usu = new UsuarioHistorico();
usu.nome = leitor["nome"].ToString();
usu.login = leitor["LOGIN"].ToString();
usu.dt_inclusao = DateTime.Parse(leitor["DT_INCLUSAO"].ToString());
usu.ds_justitificativa = leitor["ds_justificativa"].ToString();
usu.id_status = int.Parse(leitor["status"].ToString());
usu.descricao_status = usu.id_status == 0 ? "INATIVO" : "ATIVO";
list.Add(usu);
}
leitor.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: \\n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(list);
}
protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
{
int idx = DataList1.SelectedIndex;
Label lbl = (Label)DataList1.Items[idx].FindControl("Label2");
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_cm_images WHERE ID = '" + lbl.Text + "'", conn);
conn.Open();
System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
try
{
while (myReader.Read())
{
System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("IMAGE"));
if (!myLob.IsNull)
{
string FN = myReader.GetString(myReader.GetOrdinal("IMAGE_NAME"));
//Use buffer to transfer data
byte[] b = new byte[myLob.Length];
//Read data from database
myLob.Read(b, 0, (int)myLob.Length);
Response.AddHeader("content-disposition", "attachment;filename=" + FN);
Response.ContentType = "application/octectstream";
Response.BinaryWrite(b);
Response.End();
}
}
}
finally
{
myReader.Close();
conn.Close();
}
}
/// <summary>
/// take table name and attribute name and return a string of array;
/// </summary>
/// <param name="tableName"></param>
/// <param name="attribute"></param>
/// <returns></returns>
public string[] getDataItem(string tableName, string attribute)
{
string [] strArray = new string [N];
string qry = "select distinct " + attribute + " from " + tableName + " where " + attribute + " is not null order by " + attribute + " asc";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
int i = 0;
while (reader.Read())
{
strArray[i] = reader.GetValue(0).ToString();
i++;
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(strArray);
}
public string[] getEidlist()
{
string [] empidlist = new string[NOEMP];
string qry = "select distinct emp_id from attendance order by emp_id asc";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
int i = 0;
while (reader.Read())
{
empidlist[i] = reader.GetValue(0).ToString();
i++;
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(empidlist);
}
public String Get_From_Control_pass_verify(String str_control_id)
{
String str = "";
try
{
String qry = "select password from employee where eid = '" + str_control_id + "'";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
if (reader.Read())
{
str = reader.GetValue(0).ToString();
}
else
{
str = "";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(str);
}
public int getAid(string eid, string atdDate)
{
int x = 0;
string str;
string qry = "select aid from attendance where emp_id='" + eid + "' and to_date(to_char(attendance_date,'dd/mm/yyyy'),'dd/mm/yyyy')= to_date('" + atdDate + "','dd/mm/yyyy')";
System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry);
try
{
if (reader.Read())
{
str = reader.GetValue(0).ToString();
if (str != "")
{
x = Convert.ToInt32(str);
}
}
}
catch (Exception e)
{
MessageBox.Show("problem with database");
}
return(x);
}
//public ActionResult Search()
//{
// return View("Index");
//}
//[HttpPost]
public ActionResult Search(string BondLicenseNo)
{
if (!(System.Web.HttpContext.Current.User.IsInRole("Bonder")))
{
var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo select b.BONDERSLNO).SingleOrDefault();
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
try
{
return(View(getRenewalInfo(bonderslno)));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
return(RedirectToAction("Index"));
}
else
{
USERPERMISSION permission = session.getStoredUserPermission();
var bonderName = permission.BONDER.BONDERNAME;
var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo && b.BONDERNAME == bonderName select b.BONDERSLNO).SingleOrDefault();
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
try
{
return(View(getRenewalInfo(bonderslno)));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
return(RedirectToAction("Index"));
}
}
/*
* Displaying Booking details by a supplied Surname
*/
public void select_Surname(TextBox txt_Search_Surname, DataGridView grd_Booking_Details)
{
string query_String = string.Format("SELECT Booking_Id,B.Customer_Id,Collection_Date,Collection_Time,Return_Date,Return_Time,Description" +
",Location,Transmission,Car_Reg,Cost FROM Customers C ,Bookings B WHERE C.Customer_Id = B.Customer_Id AND Surname LIKE '{0}'", txt_Search_Surname.Text);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Bookings");
grd_Booking_Details.DataSource = data_Set.Tables["Bookings"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception)
{
MessageBox.Show("No record Found For supplied name");
}
}
/*
* Selecting avalible cars from the selected car class
*/
public void fillAvaliableCars(DataGridView dataGrid_Avaliable_Cars, TextBox txt_Car_Reg, TextBox txt_Make, TextBox txt_Model)
{
string query_String = "SELECT * FROM Cars WHERE Car_Status = 'A'";
{
try
{
connection.Open();
cmd = connection.CreateCommand();
/*
* Creating the query string to retrieve the required record
*/
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
/*
* Assigning the values of the Car_Class table to the appropriate text boxes after retreving
*/
if (!data_reader.HasRows)
{
return;
}
else
{
while (data_reader.Read())
{
txt_Car_Reg.Text = data_reader.GetValue(0).ToString();
txt_Make.Text = data_reader.GetValue(1).ToString();
txt_Model.Text = data_reader.GetValue(2).ToString();
}
}
connection.Close();
}
catch (Exception) // Catching exception if the id dosent match any records
{
//MessageBox.Show("No Record Found");
MessageBox.Show("No Cars Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblidddl.Visible = false;
txtCorreo.Text = Session["Value"].ToString();
Array enumList1 = Enum.GetValues(typeof(tiposTarea));
foreach (tiposTarea getTipoTarea in enumList1)
{
ddlTipoTarea.Items.Add(new ListItem(getTipoTarea.ToString(), ((int)getTipoTarea).ToString()));
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablasTareasDevueltas.DataSource = dt;
tablasTareasDevueltas.DataBind();
if (tablasTareasDevueltas.Rows.Count == 0)
{
// Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
System.Data.OracleClient.OracleDataReader rdr = comando.ExecuteReader();
while (rdr.Read())
{
ddlFlujo.Items.Add(rdr.GetString(0).ToString());
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
}
/// <summary>
/// Selects the locations from the location Table
/// </summary>
/// <param name="cmb_location"></param>
public void selectDetails(ComboBox cmb_location)
{
string query_String = "Select location FROM Locations ";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
cmb_location.Items.Add(data_reader[0].ToString());
}
connection.Close();
}
catch (Exception)
{
MessageBox.Show("Cannot open Locations", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
/*
* Select all bookings that are collected
*/
public void selectUptodate(DataGridView dataGridDailyBookings)
{
string query_String = "SELECT Booking_Id ,Forename, Surname ,Address, Phone_No ,B.Car_Reg ,Make ,Model ,Cost,Customer_Type FROM Cars R, " +
"Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " +
"WHERE Booking_Status = 'C' AND Car_Status = 'U' AND R.Car_Reg = B.Car_Reg AND C.Customer_Id IN (SELECT Customer_Id FROM Bookings )";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "DailyBookings");
dataGridDailyBookings.DataSource = data_Set.Tables["DailyBookings"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception) { }
}
///<summary>Use this for count(*) queries. They are always guaranteed to return one and only one value. Uses datareader instead of datatable, so faster. Can also be used when retrieving prefs manually, since they will also return exactly one value</summary>
public string GetCount(string command)
{
#if DEBUG
if(logDebugQueries){
Debug.WriteLine(command);
}
Thread.Sleep(delayForTesting);
#endif
string retVal="";
if(DBtype==DatabaseType.Oracle){
conOr.Open();
//PrepOracleConnection();
cmdOr.CommandText=command;
try{
drOr=(OracleDataReader)cmdOr.ExecuteReader();
}
catch(System.Exception e) {
Logger.openlog.LogMB("Oracle SQL Error: "+cmdOr.CommandText+"\r\n"+"Exception: "+e.ToString(),Logger.Severity.ERROR);
throw;//continue to pass the exception one level up.
}
drOr.Read();
retVal=drOr[0].ToString();
conOr.Close();
}
else if(DBtype==DatabaseType.MySql) {
cmd.CommandText=command;
con.Open();
dr=(MySqlDataReader)cmd.ExecuteReader();
dr.Read();
retVal=dr[0].ToString();
con.Close();
}
return retVal;
}
public ActionResult Renewinfo(RenewalViewModel rvm, string[] doc, string[] rgno, string[] issuedate, string[] expdate, HttpPostedFileBase[] files)
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "update BONDSTATUS set STATUS=:STATUS,BSDATE=:BSDATE,SUBMITTEDBYNM=:SUBMITTEDBYNM,REMARKS=:REMARKS"
+ " where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("STATUS", Request["STATUS"]);
if (!String.IsNullOrEmpty(Request["ApplicationSubmissionDate"]))
{
cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = Request["ApplicationSubmissionDate"];
}
else
{
cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = DBNull.Value;
}
cmd.Parameters.AddWithValue("SUBMITTEDBYNM", Request["SubmittedBy"]);
cmd.Parameters.AddWithValue("REMARKS", Request["Remarks"]);
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
try
{
try
{
bmsTransaction = conn.BeginTransaction();
}
catch { }
cmd.Transaction = bmsTransaction;
cmd.ExecuteNonQuery();
bmsTransaction.Commit();
}
catch
{
bmsTransaction.Rollback();
}
if (Request["STATUS"] == "Cm")
{
cmd.CommandText = "update BONDAPPLICATIONPROGRESS set READYFORAPP=:READYFORAPP where BONDERSLNO=:BONDERSLNO and BSNO=:BSNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
cmd.Parameters.AddWithValue("BSNO", BondInfo.BSNoToRenew);
cmd.Parameters.AddWithValue("READYFORAPP", "Y");
try
{
try
{
bmsTransaction = conn.BeginTransaction();
}
catch { }
cmd.Transaction = bmsTransaction;
cmd.ExecuteNonQuery();
bmsTransaction.Commit();
}
catch
{
bmsTransaction.Rollback();
}
}
int p = 1;
if (doc != null)
{
try
{
for (int i = 0; i < doc.Length; i++)
{
if (doc[i] != null && doc[i] != "")
{
DOCUMENTATTACHMENT D = new DOCUMENTATTACHMENT();
var path = "";
D.ATTCHSLNO = (Int16)p;
//foreach (var outitem in rvm)
//{
// foreach (var item in outitem.Bonder)
// {
D.BONDERSLNO = BondInfo.bondSlNoToRenew;
//}
//foreach (var item in outitem.Bondstatus)
//{
D.BSNO = BondInfo.BSNoToRenew;
// }
//}
if (files[i] != null)
{
var filename = Path.GetFileName(files[i].FileName);
path = Path.Combine(Server.MapPath(Url.Content("~/Uploads/")), filename);
files[i].SaveAs(path);
D.ATTACHFILENM = path;
}
D.DOCHEADINGNAME = doc[i];
D.RGATTCHNAME = rgno[i];
//D.ISSUEDATE = Convert.ToDateTime(issuedate[i], CultureInfo.CurrentCulture);
//D.EXPDATE = Convert.ToDateTime(expdate[i], CultureInfo.CurrentCulture);
if (!String.IsNullOrEmpty(issuedate[i]))
{
D.ISSUEDATE = DateTime.ParseExact(issuedate[i], "dd/MM/yyyy", null);
}
if (!String.IsNullOrEmpty(expdate[i]))
{
D.EXPDATE = DateTime.ParseExact(expdate[i], "dd/MM/yyyy", null);
}
db.DOCUMENTATTACHMENTs.Add(D);
p++;
db.SaveChanges();
}
}
ViewBag.Message = "Successfully Inserted";
//return View(renewalviewmodel);
//return Search(BIMS.CommonAppSet.BondInfo.bondLicenseNoToRenew);
//return View("Search",rvm);
//return View("RenewalForm");
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
}
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
return(View("Search", getRenewalInfo(BondInfo.bondSlNoToRenew)));
//return View("RenewalForm");
}
public void recalculate(DateTimePicker start, DateTimePicker end, TextBox txt_Cost, ComboBox cbo_Car_Reg)
{
DateTime dt1 = Convert.ToDateTime(start.Text);
DateTime dt2 = Convert.ToDateTime(end.Text);
TimeSpan timeSpan = dt2 - dt1;
int numberOfDays = timeSpan.Days;
string query_String2 = string.Format("SELECT Car_Rate FROM Car_Class C JOIN Cars S ON C.Class_Id = S.Class_Id WHERE Car_Reg = '{0}'", cbo_Car_Reg.Text);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String2;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (data_reader.HasRows)
{
data_reader.Read();
txt_Cost.Text = ("" + data_reader.GetInt32(0) * numberOfDays);
}
connection.Close();
}
catch (Exception) // Catching exception if the id dosent match any records
{
//MessageBox.Show("No Record Found");
MessageBox.Show("No Cars Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
//Populating the car make combobox
public void select_car_Make(ComboBox cmb_Car_Make)
{
cmb_Car_Make.Items.Clear();
string query_String = "SELECT Make FROM Cars";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
if (!data_reader.HasRows)
return;
cmb_Car_Make.Items.Add(data_reader[0].ToString());
}
connection.Close();
}
catch (Exception)
{
error_Message();
}
}
/*
* Inserting the avaliable cars into the car_Reg combo_Box that fit the booking criteria
*/
/// <summary>
///
/// </summary>
/// <param name="cbo_Car_Reg"></param>
/// <param name="grd_Booking_Details"></param>
public void fillReg(ComboBox cbo_Car_Reg, DataGridView grd_Booking_Details)
{
cbo_Car_Reg.Items.Clear();
cbo_Car_Reg.Text = "";
string query_String = "Select Car_Reg FROM Cars C JOIN Car_Class C2 ON C.Class_Id = C2.Class_Id WHERE Car_Status = 'A' AND Description = '" +
description + "'AND Transmission = '" + transmission + "'AND C.location = '" + location + "'";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (!data_reader.HasRows)
{
//Select query that brings back the details of car class using description
string query_String2 = "SELECT Car_Reg FROM Cars WHERE Car_Status = 'A' AND location = '" + location + "'";
cmd = connection.CreateCommand();
cmd.CommandText = query_String2;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
cbo_Car_Reg.Items.Add(data_reader[0].ToString());
}
}
else
{
while (data_reader.Read())
{
cbo_Car_Reg.Items.Add(data_reader[0].ToString());
}
}
connection.Close();
}
catch (Exception)
{
MessageBox.Show("No Cars Avaliable", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
/*
* Filling the Class Description Combobox
*/
public void select_Description(ComboBox cmb_Description)
{
string query_String = "SELECT Description FROM Car_Class";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
cmb_Description.Items.Add(data_reader[0].ToString());
}
connection.Close();
}
catch (Exception)
{
MessageBox.Show("No Description Avaliable");
}
}
/// <summary>
/// The datagrid is Populated with Forename,Surname,Customer_Id,Phone_No
/// from the customers table
/// </summary>
/// <param name="grd_Customer_Details"></param>
public void selectFillCustomers(DataGridView grd_Customer_Details)
{
string query_String3 = "SELECT * From Customers ";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String3;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Customers");
grd_Customer_Details.DataSource = data_Set.Tables["Customers"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception)
{
MessageBox.Show("Cannot open Customers", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
/*
* Filling the rental datagridview the details from the bookings table using surname to retrieve data
*/
public void select_SurnameRentals(TextBox txt_Search_Surname, DataGridView grd_Rental_Details)
{
string query_String = string.Format("SELECT * From Customers WHERE Surname LIKE '{0}'", txt_Search_Surname.Text);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Rentals");
grd_Rental_Details.DataSource = data_Set.Tables["Rentals"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception)
{
MessageBox.Show("No record Found For supplied name");
}
}
//Selecting the make and model of car that match the car reg
public void carDetails(TextBox txt_Car_Reg, TextBox txt_Make, TextBox txt_Model)
{
try
{
//Select query that brings back the details of car class using description
string query_String2 = string.Format("SELECT Make ,Model FROM Cars WHERE Car_Reg = '{0}'", txt_Car_Reg.Text);
connection.Open();
cmd = connection.CreateCommand();
/*
* Creating the query string to retrieve the required record when a customer id is supplied
*/
cmd.CommandText = query_String2;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
/*
* Assigning the values of the Car_Class table to the appropriate text boxes after retreving
*/
if (!data_reader.HasRows)
{
return;
}
else
{
data_reader.Read();
txt_Make.Text = data_reader.GetValue(0).ToString();
txt_Model.Text = data_reader.GetValue(1).ToString();
connection.Close();
}
}
catch (Exception ex) // Catching exception if the id dosent match any records
{
MessageBox.Show("" + ex);
}
}
public override bool Read()
{
return(rdr.Read());
}
public void fillReg(ComboBox cmb_Car_Reg, ComboBox cbo_Location, ComboBox cbo_Description,
DateTimePicker startDatePicker, DateTimePicker returnDatePicker, ComboBox txt_Transmittion)
{
cmb_Car_Reg.Items.Clear();
cmb_Car_Reg.Text = "";
var startDate = startDatePicker.Value.ToString("dd-MMM-yyyy");
var returnDate = returnDatePicker.Value.ToString("dd-MMM-yyyy");
string query_String = "SELECT Car_Reg FROM Cars C JOIN Car_Class C2 ON C.Class_Id = C2.Class_Id WHERE Car_Status = 'A' AND Description = '"
+ cbo_Description.Text + "'AND location = '" + cbo_Location.Text + "'AND Transmission ='" + txt_Transmittion.Text +
"'AND Car_Reg NOT IN (SELECT Car_Reg FROM Bookings WHERE Collection_Date <= '"
+ startDate + "' AND Return_Date >='" + returnDate + "')";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (!data_reader.HasRows)
{
//Select query that brings back the details of car class using description
string query_String2 = "SELECT Car_Reg FROM Cars WHERE Car_Status = 'A' AND location = '" + cbo_Location.Text +
"'AND Transmission ='" + txt_Transmittion.Text + "'AND Car_Reg NOT IN (SELECT Car_Reg FROM Bookings WHERE Collection_Date <= '"
+ startDate + "' AND Return_Date >='" + returnDate + "')";
cmd = connection.CreateCommand();
cmd.CommandText = query_String2;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
cmb_Car_Reg.Items.Add(data_reader[0].ToString());
}
}
else
{
while (data_reader.Read())
{
cmb_Car_Reg.Items.Add(data_reader[0].ToString());
}
}
connection.Close();
}
catch (Exception)
{
cmb_Car_Reg.Text = "No Avaliable Cars";
}
}
public ActionResult Create(CreateMemo Memo)
{
if (ModelState.IsValid)
{
string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;";
string MEMO_ID,AUTORIZE_DETAIL;
using (OracleConnection connection = new OracleConnection(connectionString))
{
if (Memo.Autorize.ToString() == "Department")
{
AUTORIZE_DETAIL = Memo.Autorize_detail;
}
else
{
AUTORIZE_DETAIL = "";
}
string number = null;
number = "";
OracleCommand cmd1 = new OracleCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "select nvl(max(substr(MEMO_ID,7,3)),0) from KMIINTRANET_MASTER_MEMO where substr(MEMO_ID,1,6)='" + Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + "'";
cmd1.Connection = connection;
connection.Open();
objDataReader = cmd1.ExecuteReader();
if (objDataReader.HasRows)
{
objDataReader.Read();
number = Convert.ToString(Convert.ToInt32(objDataReader[0]) + 1);
if (number.Length == 1)
{
number = "00" + number;
}
else if (number.Length == 2)
{
number = "0" + number;
}
else if (number.Length == 3)
{
number = number;
}
}
cmd1 = null;
connection.Close();
OracleCommand cmd2 = new OracleCommand();
MEMO_ID = Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + number;
byte[] tempFile = new byte[Memo.MemoFile.InputStream.Length];
Memo.MemoFile.InputStream.Read(tempFile, 0, tempFile.Length);
cmd2.CommandText = "INSERT INTO KMIINTRANET_MASTER_MEMO(MEMO_ID,MEMO_THEME,AUTORIZE,AUTORIZE_DETAIL,MEMO_FILE,VALID_FROM,VALID_UNTIL,CREATE_BY,CREATE_DATE) VALUES (:memoid,:theme,:autorize,:detail,:memofile,:validfrom,:validuntil,'" + Session["USER"] + "',sysdate)";
cmd2.CommandType = CommandType.Text;
cmd2.Connection = connection;
connection.Open();
cmd2.Parameters.Add(":memofile", OracleType.Blob).Value = tempFile;
cmd2.Parameters.Add(":validuntil", OracleType.DateTime).Value = Memo.ValidUntil.ToString("MM/dd/yyyy");
cmd2.Parameters.Add(":validfrom", OracleType.DateTime).Value = Memo.ValidFrom.ToString("MM/dd/yyyy");
cmd2.Parameters.Add(":detail", OracleType.VarChar).Value = AUTORIZE_DETAIL;
cmd2.Parameters.Add(":autorize", OracleType.VarChar).Value = Memo.Autorize.ToString();
cmd2.Parameters.Add(":theme", OracleType.VarChar).Value = Memo.Theme.ToString();
cmd2.Parameters.Add(":memoid", OracleType.VarChar).Value = MEMO_ID;
cmd2.ExecuteNonQuery();
cmd2 = null;
connection.Close();
return RedirectToAction("Index");
}
}
return View();
}
public void selectBookingId( TextBox txt_Booking_Id)
{
string query_String = "SELECT MAX(Booking_Id) FROM Bookings";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (data_reader.HasRows)
{
data_reader.Read();
var count = data_reader.GetInt32(0);
var nextId = count + 1;
txt_Booking_Id.Text =("" + nextId) ;
}
connection.Close();
}
catch (Exception ex) // Catching exception if the id dosent match any records
{
ex.GetBaseException();
}
}
public void extraCost(TextBox txt_Days_Late, TextBox txt_Search_Reg, TextBox txt_Additional_Costs,EventArgs e)
{
string query_String = string.Format("SELECT Car_Rate FROM Car_Class C JOIN Cars S ON C.Class_Id = S.Class_Id WHERE Car_Reg = '{0}'", txt_Search_Reg.Text);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (data_reader.HasRows)
{
data_reader.Read();
if (numberOfDays < 0)
{
txt_Additional_Costs.Text =("" + 0);
}
else
{
txt_Additional_Costs.Text = (" " + data_reader.GetInt32(0) * numberOfDays);
}
}
connection.Close();
}
catch (Exception ex) // Catching exception if the id dosent match any records
{
ex.GetBaseException();
}
}
public void selectBReg(TextBox txt_Search_Reg, TextBox txt_Car_Reg, TextBox txt_Make,
TextBox txt_Model, TextBox txt_Return_Date, EventArgs e)
{
string query_String = string.Format("SELECT DISTINCT R.Car_Reg,Make,Model,Return_Date,Cost From Rentals R" +
" JOIN Cars C ON R.Car_Reg = C.Car_Reg WHERE R.Car_Reg = '{0}'", txt_Search_Reg.Text);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();
while (data_reader.Read())
{
if (!data_reader.HasRows)
return;
txt_Car_Reg.Text = data_reader.GetValue(0).ToString();
txt_Make.Text = data_reader.GetValue(1).ToString();
txt_Model.Text = data_reader.GetValue(2).ToString();
txt_Return_Date.Text = data_reader.GetValue(3).ToString();
}
connection.Close();
}
catch (Exception) { }
}
/// <summary>
/// Converts a NpgsqlDataReader to a DataSet
/// <param name='reader'>
/// NpgsqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet ConvertDataReaderToDataSet(OracleDataReader reader, string tabela)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable(tabela);
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
public void select_Car_Reg( TextBox txt_Search_Reg, DataGridView grd_Booking_Details)
{
string query_String = string.Format("SELECT DISTINCT B.Customer_Id, Forename,Surname, B.Car_Reg,Make,Model,B.Cost,Customer_Type,Return_Date "
+ "From Cars Ca,Bookings B ,Customers C "
+ "WHERE Ca.Car_Reg = B.Car_Reg AND C.Customer_Id = B.Customer_Id AND B.Car_Reg LIKE '{0}'" , txt_Search_Reg.Text);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Rentals");
grd_Booking_Details.DataSource = data_Set.Tables["Rentals"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception)
{
MessageBox.Show("");
}
}
/*
* Selecting customer details from the customer table
*/
public void customerDetails(TextBox txt_Forename, TextBox txt_Customer_Id,
TextBox txt_Surname, TextBox txt_Phone_No)
{
/*
* Creating the query string to retrieve the required record when a customer id is supplied
*/
string query_String = "SELECT Forename ,Surname,Phone_No From Customers WHERE Customer_Id = " + txt_Customer_Id.Text;
try
{
connection.Open();//opening a connection to the database
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
/*
* Assigning the values ot the customer table to the appropriate text boxes
*/
txt_Forename.Text = data_reader.GetValue(0).ToString();
txt_Surname.Text = data_reader.GetValue(1).ToString();
txt_Phone_No.Text = data_reader.GetValue(2).ToString();
connection.Close();
}
catch (Exception) // Catching exception if the id dosent match any records
{
MessageBox.Show("No Record Found");
}
}