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();
}
//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);}
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet"></param>
/// <returns></returns>
public int ExecQuery(string strSql, ref string strDataSet, string strXSLFileName)
{
this.command.Connection = this.con as OracleConnection;
if (this.command.Transaction == null) //没有事务，用全局事务
{
this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction;
}
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
this.TempReader1 = this.command.ExecuteReader();
XmlDocument doc = new XmlDocument();
XmlNode root;
XmlElement node, row;
doc.AppendChild(doc.CreateXmlDeclaration("1.0", "GB2312", ""));
if (strXSLFileName != null && strXSLFileName != "")
{
string PI = "type='text/xsl' href='" + strXSLFileName + "'";
System.Xml.XmlProcessingInstruction xmlProcessingInstruction = doc.CreateProcessingInstruction("xml-stylesheet", PI);
doc.AppendChild(xmlProcessingInstruction);
}
root = doc.CreateElement("Table");
doc.AppendChild(root);
while (this.TempReader1.Read())
{
row = doc.CreateElement("Row");
for (int i = 0; i < this.TempReader1.FieldCount; i++)
{
node = doc.CreateElement("Column");
node.SetAttribute("Name", this.TempReader1.GetName(i).ToString());
node.InnerText = this.TempReader1[i].ToString() + "";
row.AppendChild(node);
}
root.AppendChild(row);
}
strDataSet = doc.OuterXml;
TempReader1.Close();
}
catch (OracleException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.Code;
this.WriteErr();
return(-1);
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.ErrCode = strSql;
this.WriteErr();
return(-1);
}
WriteDebug("执行查询sql语句！" + strSql);
return(0);
}
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();
}
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);
}
}
/*
* 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) { }
}
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");
}
}
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 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));
}
/*
* 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);
}
}
//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
}//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);
}
/// <summary>
/// 执行sql语句 重载
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet">返回DataSet xml</param>
/// <returns></returns>
public int ExecQuery(string strSql, ref string strDataSet)
{
this.command.Connection = this.con as OracleConnection;
if (this.command.Transaction == null) //没有事务，用全局事务
{
this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction;
}
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
this.TempReader1 = this.command.ExecuteReader();
XmlDocument doc = new XmlDocument();
XmlNode root;
XmlNode node, table;
root = doc.CreateElement("DataSet");
doc.AppendChild(root);
while (this.TempReader1.Read())
{
table = doc.CreateElement("Table");
for (int i = 0; i < this.TempReader1.FieldCount; i++)
{
node = doc.CreateElement(this.TempReader1.GetName(i).ToString());
node.InnerText = this.TempReader1[i].ToString() + "";
table.AppendChild(node);
}
root.AppendChild(table);
}
strDataSet = doc.OuterXml;
TempReader1.Close();
}
catch (OracleException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.Code;
this.WriteErr();
return(-1);
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.ErrCode = strSql;
this.WriteErr();
return(-1);
}
WriteDebug("执行查询sql语句！" + strSql);
return(0);
}
} //end of this method
public static bool IsPassowrdExpired(string loginID, System.Data.OracleClient.OracleConnection conVITAP)
{
bool valid = false;
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = "";
System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader();
return(valid);
}
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 System.Data.OracleClient.OracleDataReader Get_From_DB(String qry)
{
System.Data.OracleClient.OracleDataReader myReader = null;
try
{
OracleCommand comm = new OracleCommand(qry, this.GetConn()); //creates a command with given query and connection string
myReader = comm.ExecuteReader(); //executes that command that returns rows.
//System.Console.WriteLine(myReader.GetValue(0).ToString()+"");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "in qry and conn");
}
return(myReader);
}
public DataTable Consulta(string query)
{
DataTable dt = new DataTable();
if(conexao.State == ConnectionState.Open)
{
command = conexao.CreateCommand();
command.CommandText = query;
reader = command.ExecuteReader();
dt.Load(reader);
reader.Close();
return dt;
}
else
{
MessageBox.Show("Erro ao conectar-se");
return null;
}
}
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);
}
/*
* 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 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);
}
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 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 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 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 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 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 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);
}
internal OracleDataReaderWrapper(OracleDataReader innerReader)
: base(innerReader)
{
}
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>
/// Executes a select command that selects all columns specified by this collection,
/// from the table specified by this collections TableName property,
/// filtered by the given unique id.
/// The select command is executed using ExecuteReader.
/// </summary>
/// <param name="a_sUniqueId">The unique id to use as rows filter.</param>
/// <param name="a_oReader">Reader that holds the results of the select command.</param>
/// <param name="a_oConnection">A connection object that serves the reader a_oReader for retrivieng data.</param>
/// <remarks>
/// While the OracleDataReader is in use, the associated OracleConnection is open and busy serving the OracleDataReader.
/// While in this state, no other operations can be performed on the OracleConnection other than closing it.
/// This is the case until the Close method of the OracleDataReader is called.
/// It is the users responsibility to close the OracleConnection explicitly when it is no longer needed.
/// </remarks>
public virtual void ExecuteSelectReader(string a_sUniqueId, out OracleDataReader a_oReader, out OracleConnection a_oConnection)
{
OracleCommand l_cmdSelect = BuildSelectCommand(a_sUniqueId);
l_cmdSelect.Connection.Open();
a_oConnection = l_cmdSelect.Connection;
a_oReader = l_cmdSelect.ExecuteReader();
}
OracleDataReader ExecuteReader (CommandBehavior behavior)
{
AssertConnectionIsOpen ();
AssertTransactionMatch ();
AssertCommandTextIsSet ();
moreResults = -1;
bool hasRows = false;
this.behavior = behavior;
if (Transaction != null)
Transaction.AttachToServiceContext ();
OciStatementHandle statement = GetStatementHandle ();
OracleDataReader rd = null;
try {
if (preparedStatement == null)
PrepareStatement (statement);
else
preparedStatement = null; // OracleDataReader releases the statement handle
bool isNonQuery = IsNonQuery (statement);
BindParameters (statement);
if (isNonQuery)
ExecuteNonQueryInternal (statement, false);
else {
if ((behavior & CommandBehavior.SchemaOnly) != 0)
statement.ExecuteQuery (true);
else
hasRows = statement.ExecuteQuery (false);
UpdateParameterValues ();
}
if (Parameters.Count > 0) {
for (int p = 0; p < Parameters.Count; p++) {
OracleParameter parm = Parameters [p];
if (parm.OracleType.Equals (OracleType.Cursor)) {
if (parm.Direction != ParameterDirection.Input) {
rd = (OracleDataReader) parm.Value;
break;
}
}
}
}
if (rd == null)
rd = new OracleDataReader (this, statement, hasRows, behavior);
} finally {
if (statement != null && rd == null)
statement.Dispose();
}
return rd;
}
/*
* 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");
}
}
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) { }
}
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");
}
OracleDataReader ExecuteReader(CommandBehavior behavior)
{
AssertConnectionIsOpen();
AssertTransactionMatch();
AssertCommandTextIsSet();
moreResults = -1;
bool hasRows = false;
this.behavior = behavior;
if (Transaction != null)
{
Transaction.AttachToServiceContext();
}
OciStatementHandle statement = GetStatementHandle();
OracleDataReader rd = null;
try {
if (preparedStatement == null)
{
PrepareStatement(statement);
}
else
{
preparedStatement = null; // OracleDataReader releases the statement handle
}
bool isNonQuery = IsNonQuery(statement);
BindParameters(statement);
if (isNonQuery)
{
ExecuteNonQueryInternal(statement, false);
}
else
{
if ((behavior & CommandBehavior.SchemaOnly) != 0)
{
statement.ExecuteQuery(true);
}
else
{
hasRows = statement.ExecuteQuery(false);
}
UpdateParameterValues();
}
if (Parameters.Count > 0)
{
for (int p = 0; p < Parameters.Count; p++)
{
OracleParameter parm = Parameters [p];
if (parm.OracleType.Equals(OracleType.Cursor))
{
if (parm.Direction != ParameterDirection.Input)
{
rd = (OracleDataReader)parm.Value;
break;
}
}
}
}
if (rd == null)
{
rd = new OracleDataReader(this, statement, hasRows, behavior);
}
} finally {
if (statement != null && rd == null)
{
statement.Dispose();
}
}
return(rd);
}
internal RefCountingOracleDataReaderWrapper(DatabaseConnectionWrapper connection, OracleDataReader innerReader)
: base(innerReader)
{
this.connection = connection;
this.connection.AddRef();
}
public OracleDataReaderWrapper(OracleDataReader reader)
{
this.innerReader = reader;
}
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);
}
}
//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);
}
}
/*
* 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");
}
}
/*
* 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) { }
}
public OracleDataReader(System.Data.OracleClient.OracleDataReader reader)
{
this.rdr = reader;
}
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 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"));
}
}
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("");
}
}
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 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();
}
}
//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();
}
}
/*
* 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 override void Dispose (bool disposing)
{
if (!disposed) {
if (State == ConnectionState.Open)
Close ();
dataReader = null;
transaction = null;
oci = null;
pool = null;
conInfo.Username = string.Empty;
conInfo.Database = string.Empty;
conInfo.Password = string.Empty;
connectionString = null;
parsedConnectionString = null;
base.Dispose (disposing);
disposed = true;
}
}
/*
* 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);
}
}