public static bool AddIncident(Incident i, int missieUID)
{
using (OracleConnection o = new OracleConnection(connstring))
{
using (OracleCommand c = new OracleCommand())
{
try
{
c.Connection = o;
o.Open();
c.CommandText = "INSERT INTO TIncident (Beschrijving) VALUES ('" + i.Beschrijving + "')";
c.ExecuteNonQuery();
c.CommandText = "SELECT MAX(ID) FROM TIncident";
OracleDataReader dr = c.ExecuteReader();
if (dr.Read())
{
c.CommandText = "INSERT INTO TMissie_Incident (MissieID, IncidentID) VALUES ('" + missieUID + "', '" + GetInt(dr[0]) + "')";
c.ExecuteNonQuery();
}
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
o.Close();
}
}
}
}
public void editarAtencionCliente(string idAC, string passwordAC, string nombreAC, string apellidosAC, string cedulaAC, string correoAC)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "atencion_cliente_paquete.editarAtencionCliente";
OracleParameter idACP = new OracleParameter("idAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
idACP.Value = idAC;
OracleParameter passwordACP = new OracleParameter("passwordAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
passwordACP.Value = passwordAC;
OracleParameter nombreACP = new OracleParameter("nombreAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
nombreACP.Value = nombreAC;
OracleParameter apellidosACP = new OracleParameter("apellidosAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
apellidosACP.Value = apellidosAC;
OracleParameter cedulaACP = new OracleParameter("cedulaAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
cedulaACP.Value = cedulaAC;
OracleParameter correoACP = new OracleParameter("correoAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
correoACP.Value = correoAC;
cmd.Parameters.AddRange(new OracleParameter[] { idACP,passwordACP, nombreACP, apellidosACP, cedulaACP, correoACP });
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
private void button1_Click(object sender, EventArgs e)
{
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "Delete from pharm.DRUG where DRUG_ID=" + textBox1.Text + " ";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not deleted");
else
MessageBox.Show("Successfully deleted!");
conn.Dispose();
}
public OracleSessionDetails(OracleDBConnection connection)
{
InitializeComponent();
this.dbConnection = connection;
OracleConnection databaseConnection = dbConnection.getDatabaseConnection();
String query;
OracleCommand command = new OracleCommand("DBMS_SESSION.set_identifier",databaseConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(dbConnection.getUsername(), OracleDbType.Char);
command.ExecuteNonQuery();
query = "SELECT SYS_CONTEXT('userenv', 'SID') AS SID, "
+ "SYS_CONTEXT('userenv', 'HOST') AS HOST, "
+ "USER AS username "
+ "FROM dual";
command = new OracleCommand(query, databaseConnection);
OracleDataReader reader;
command.CommandText = query;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
while (reader.Read())
{
this.sessionListing.Rows.Add("SID", reader["SID"]);
this.sessionListing.Rows.Add("HOST", reader["HOST"]);
this.sessionListing.Rows.Add("DATABASE", dbConnection.getDatabaseConnection().DataSource);
this.sessionListing.Rows.Add("USER", reader["USERNAME"]);
}
reader.Close();
}
//voeg nieuw account aan DB(registeren)
public void AddAccount(string Email, string Voornaam, string voorletters, string achternaam, string geslacht,
DateTime Geboortedatum, string Postcode, string woonplaats, string adres,
int telefoonnumer, int Mobielnummer, string wachtwoord)
{
int id = Database.Instance.KrijgHoogsteID3("Account");
string formateddatum = Geboortedatum.ToString("dd-MM-yyyy");
string query = string.Format("Insert into ACCOUNT (Klantnummer,Voorletters,Achternaam,Voornaam,Geslacht," +
"Geboortedatum,Adres,Postcode,Woonplaats,TelefoonThuis,TelefoonMobiel,TelefoonWerk," +
"Email,Wachtwoord,Bezorgvoorkeur,Emailvoorkeur,Cookievoorkeur,Productvoorkeur,Betaalvoorkeur" +
") values ({0},'{1}','{2}','{3}','{4}',to_date('{5}','dd/mm/yyyy'), " +
"'{6}', '{7}', '{8}', {9}, {10}, {11}," +
" '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}') ",
id, voorletters, achternaam, Voornaam, geslacht, formateddatum, adres, Postcode, woonplaats,
telefoonnumer, Mobielnummer, 0
, Email, wachtwoord, "Toegestaan", "Niet toegestaan", "Nee", "Nee", "Belansrekening sluiten")
;
using (OracleConnection conn = Database.Instance.GetConnection())
{
Console.WriteLine(query);
conn.Open();
using (OracleCommand cmd = new OracleCommand(query, conn))
{
cmd.ExecuteNonQuery();
}
}
}
protected void btnadd_Click(object sender, EventArgs e)
{
string ctc, ctd, dcc, poc;
string ad = "";
int nowh;
ctc = txtctc.Text;
ctd = txtctd.Text;
dcc = ddldcc.SelectedValue;
poc = txtpoc.Text;
if (txtnowh.Text == "")
{
nowh = 0;
}
else
{
nowh = int.Parse(txtnowh.Text);
}
int m;
string str = "select count(*) from ct where ctc='" + txtctc.Text + "' and dcc='"+ddldcc.Text+"'";
con.Open();
OracleCommand cmdcount = new OracleCommand(str, con);
m = int.Parse(cmdcount.ExecuteScalar().ToString());
if (m == 1)
{
lblctmsg.Text = "code already exists";
}
else
{
string s = "insert into ct values('" + ctc + "','" + ctd + "','" + poc + "','" + nowh + "','" + dcc + "')";
OracleCommand cmd = new OracleCommand(s, con);
cmd.ExecuteNonQuery();
lblctmsg.Text = " values inserted successfully ";
}
con.Close();
}
/// <summary>
/// Executes a query to the database, expecting no returning result
/// </summary>
/// <param name="query">Query to execute</param>
/// <param name="args">List of OracleParameters containing the named arguments</param>
/// <returns>True if the query succeeded (can still be 0 rows affected)</returns>
public static bool ExecuteNonQuery(string query, List<OracleParameter> args = null)
{
var result = -1;
try
{
using (var command = new OracleCommand(query, Connection) {BindByName = true})
{
if (args != null)
{
foreach (var arg in args)
{
command.Parameters.Add(arg);
}
}
result = command.ExecuteNonQuery();
}
}
catch (OracleException e)
{
Logger.Write(e.Message);
return false;
}
finally
{
Close();
}
return result >= 0;
}
public void Ins(Assunto u)
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("ASSUNTO_tapi.ins", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
//Define parametros
cmd.Parameters.Add("p_ID_ASSUNTO", u.Id_Assunto);
cmd.Parameters.Add("p_NOME", u.Nome);
//Executa
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
throw new Exception("Ocorreu o erro(BD): " + ex.Message);
}
catch (Exception ex)
{
throw new Exception("Erro: " + ex.Message);
}
finally
{
if (conn.State == ConnectionState.Open) { conn.Close(); }
}
}
}
}
public int sesionActiva(string toquenId)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "pk_usuarios.usuarioEnSistema";
OracleParameter toquenIdP = new OracleParameter("userTokenID", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
toquenIdP.Value = toquenId;
OracleParameter resultado = new OracleParameter("resultado", OracleDbType.Varchar2, System.Data.ParameterDirection.Output);
resultado.Size = 1024;
cmd.Parameters.AddRange(new OracleParameter[] { toquenIdP, resultado });
try
{
cmd.ExecuteNonQuery();
int respuesta = int.Parse(cmd.Parameters["resultado"].Value.ToString());
cmd.Dispose();
base.cerrarConexion();
return respuesta;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.Message);
throw e;
}
}
//发表评论
public ActionResult AddComment(string content)
{
//假设登录用户为123，需换为session
string loginuser = "******";
//string loginuser = Session["username"] as string
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStr = "select textcommentid from textcomment";
OracleCommand cmd = new OracleCommand(selectStr, conn);
OracleDataReader dr = cmd.ExecuteReader();
int temp = 0;
while (dr.Read())
{
if (Convert.ToInt32(dr[0]) > temp)
{
temp = Convert.ToInt32(dr[0]);
}
}
temp++;
string addStr = "insert into textcomment(textcommentid, textid, content, textcomment_date, username)values(" + temp + "," + textId + ",'" + content + "','" + DateTime.Now.ToString() + "','" + loginuser + "')";
OracleCommand cmd2 = new OracleCommand(addStr, conn);
int insertedLines = cmd2.ExecuteNonQuery();
conn.Close();
return RedirectToAction("Comment", "Account");
}
protected void Button1_Click(object sender, EventArgs e)
{
string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
OracleConnection conn = new OracleConnection(CS);
using (OracleCommand cmd = new OracleCommand("insert into USER_TABLE (USER_ID,U_NAME,U_PASS,FULLNAME,CONTACT,EMAIL,DOB,U_LOCATION) values (U_ID_SEQ.NEXTVAL,'" + TextBox1.Text + "','" + TextBox3.Text + "','" + TextBox7.Text + "','" + TextBox5.Text + "','" + TextBox4.Text + "','" + TextBox6.Text + "','" + TextBox8.Text + "')", conn))
{
//cmd.Parameters.("@U_NAME",TextBox1.Text);
//cmd.Parameters.AddWithValue("@U_PASS",TextBox3.Text);
//cmd.Parameters.AddWithValue("@FULLNAME", TextBox7.Text);
//cmd.Parameters.AddWithValue("@CONTACT",TextBox5.Text);
//cmd.Parameters.AddWithValue("@EMAIL",TextBox4.Text);
//cmd.Parameters.AddWithValue("@DOB",TextBox6.Text);
//cmd.Parameters.AddWithValue("@U_LOCATION",TextBox8.Text);
conn.Open();
cmd.ExecuteNonQuery();
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Text = "Data Saved In The Database";
}
//using(OracleCommand comd = new OracleCommand("select USER_ID from USER_TABLE where U_NAME = '"+TextBox1.Text+"'"))
//{
// string id= comd.ExecuteScalar().ToString();
// Session["Id"] = id;
//}
}
public int Insert(EmployeeTSI info)
{
OracleConnection conn = new OracleConnection(ConStr);
try
{
int EmpId=0;
OracleCommand command = new OracleCommand("EmpTSI_update", conn);
command.CommandType = CommandType.StoredProcedure;
var _params = command.Parameters;
conn.Open();
SetParameterValue(_params, "p_empl_id_no", info.Empl_Id_No, OracleDbType.Int32);
SetParameterValue(_params, "p_tsi", info.TSI, OracleDbType.Varchar2);
SetParameterValue(_params, "p_user_id", this.UserId, OracleDbType.Varchar2);
SetParameterValue(_params, "p_img", info.IMG, OracleDbType.Blob);
SetParameterValue(_params, "p_rtf", info.RTF, OracleDbType.Blob);
SetParameterValue(_params, "p_rif", info.RIF, OracleDbType.Blob);
SetParameterValue(_params, "p_rmf", info.RMF, OracleDbType.Blob);
SetParameterValue(_params, "p_rrf", info.RRF, OracleDbType.Blob);
SetParameterValue(_params, "p_rsf", info.RSF, OracleDbType.Blob);
SetParameterValue(_params, "p_ltf", info.LTF, OracleDbType.Blob);
SetParameterValue(_params, "p_lif", info.LIF, OracleDbType.Blob);
SetParameterValue(_params, "p_lmf", info.LMF, OracleDbType.Blob);
SetParameterValue(_params, "p_lrf", info.LRF, OracleDbType.Blob);
SetParameterValue(_params, "p_lsf", info.LSF, OracleDbType.Blob);
command.ExecuteNonQuery();
conn.Close();
return EmpId;
}
catch (Exception ex)
{
throw ex;
}
}
// GET: Album
// [HttpPost]
public List<AlbumModels> Album()
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStr = /* "insert into album(albumID,albumname,username) values('111','testalbum','KING')";*/
"select * "
+ "from album ";
// " +(Session["username"] as string)+"
OracleCommand cmd = new OracleCommand(selectStr, conn);
OracleDataReader dr = cmd.ExecuteReader();
var Models = new List<AlbumModels>();
while (dr.Read())
{
AlbumModels model = new AlbumModels();
model.AlbumName = dr["albumname"].ToString();
model.AlbumId = dr["albumid"].ToString();
//model.Username = Session["username"] as string ;
string selectStr1 = "select * from photo where albumid = " + model.AlbumId;
OracleCommand cmd1 = new OracleCommand(selectStr1, conn);
OracleDataReader dr1 = cmd1.ExecuteReader();
dr1.Read();
model.Firstphoto = dr1["photoaddress"].ToString();
Models.Add(model);
//Session.Add("currentAlbumName", albumName);
//Session.Add("currentAlbumId", albumId); //点击时保存到session
}
dr.Close();
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
return Models;
}
public void agregarNoticia(string fecha_expiracionN, string contenidoN,string encabezadoN,string tituloN)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "noticias_paquete.agregarNoticia";
OracleParameter fechaExpiracionP = new OracleParameter("fecha_expiracionN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
fechaExpiracionP.Value = fecha_expiracionN;
OracleParameter contenidoP = new OracleParameter("contenidoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
contenidoP.Value = contenidoN;
OracleParameter encabezadoP = new OracleParameter("encabezadoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
encabezadoP.Value = encabezadoN;
OracleParameter tituloP = new OracleParameter("tituloN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
tituloP.Value = tituloN;
cmd.Parameters.AddRange(new OracleParameter[] { fechaExpiracionP, contenidoP, encabezadoP, tituloP});
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
/// <summary>
/// Changes the Post
/// </summary>
/// <param name="id">id used too identify the</param>
/// <param name="changes">all changes in the same order as the parameters</param>
/// <param name="paramaterToChanged">all parameters in the same order as the changes</param>
public static void AlterPost(int id, List<string> changes, List<string> paramaterToChanged)
{
OracleConnection conn = MakeConnection();
conn.Open();
string mainQuery = "UPDATE POST SET ";
OracleCommand command = new OracleCommand();
for (int i = 0; i < changes.Count; i++)
{
mainQuery += paramaterToChanged[i];
mainQuery += " = :" + paramaterToChanged[i];
command.Parameters.Add(new OracleParameter(paramaterToChanged[i], changes[i]));
}
mainQuery += " WHERE POSTID = :POSTID";
command.CommandText = mainQuery;
command.Connection = conn;
command.Parameters.Add(new OracleParameter("POSTID", id));
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Handles the specified request.
/// </summary>
/// <param name="request">The request.</param>
public void Handle(DeployRequestDto request)
{
//Get the connection string
var sqlInputs = GetConnectionString(request);
//get the scripts folder
var directory = request.IntermediateDirectory;
//get the files in the folder
string[] fileEntries = Directory.GetFiles(directory);
foreach (string fileName in fileEntries)
{
try
{
string content = string.Format(File.ReadAllText(fileName));
using (var oracleConnection = new OracleConnection(sqlInputs.ConnectionString))
{
oracleConnection.Open();
var command = new OracleCommand();
var script = content.Replace("\t", " ");
script = script.Replace("\n", Environment.NewLine);
command.Connection = oracleConnection;
command.CommandText = script;
var result = command.ExecuteNonQuery();
Debug.Write(result);
}
}
catch (Exception ex)
{
Debug.Write("Exception message: " + ex.Message);
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
{
string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
OracleConnection conn = new OracleConnection(CS);
//string U_ID = Session["U_ID"].ToString();
//string ADD_ID = Session["ADDId"].ToString();
using (OracleCommand cmd = new OracleCommand("insert into APARTMENT (APP_ID,APP_TYPE,APP_SIZE,APP_DATE,PRICE,NO_OF_ROOMS,FEATURE,USER_ID,APP_ADD_ID) values (U_ID_SEQ.NEXTVAL,'" + TextBoxAType.Text + "','" + TextBoxASize.Text + "','" + TextBoxAdate.Text + "','" + TextBoxAprice.Text + "','" + TextBoxAroom.Text + "','" + TextBoxAfeature.Text + "','" + Session["U_ID"].ToString().Trim() + "','" + Session["ADDId"].ToString().Trim() + "')", conn))
{
conn.Open();
//cmd.ExecuteNonQuery();
if (Convert.ToBoolean(cmd.ExecuteNonQuery()))
{
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Text = "Data Saved In The Database";
Response.Redirect("~/Allapartments.aspx");
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Data Cannot Saved In The Database";
}
}
}
}
private void button1_Click_1(object sender, EventArgs e)
{
int a;
a = Convert.ToInt32(textBox4.Text);
a = int.Parse(textBox4.Text);
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update pharm.DRUG set QUANTITY =QUANTITY - " + a + "";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not inserted");
else
MessageBox.Show("Success!");
conn.Dispose();
}
//Voegt Beoordeling toe aan de databas
public bool AddBeoordeling(Beoordeling beoordeling)
{
int id = Database.Instance.KrijgHoogsteID2("Beoordeling");
string formateddatum = beoordeling.Datum.ToString("dd-MM-yyyy");
string query =
string.Format(
"Insert into Beoordeling (ID,Product_ID,Klantnummer,Beoordeling,Datum,Title,Bericht) values ({0},{1},{5},{2},to_date('{6}','dd/mm/yyyy'),'{3}','{4}')",
id, beoordeling.Product_ID, beoordeling.Beoordelingcijfer, beoordeling.Title, beoordeling.Bericht,
beoordeling.Klantnummer, formateddatum
);
using (OracleConnection conn = Database.Instance.GetConnection())
{
Console.WriteLine(query);
conn.Open();
using (OracleCommand cmd = new OracleCommand(query, conn))
{
cmd.ExecuteNonQuery();
}
}
return true;
}
private void buttonupdate_Click(object sender, EventArgs e)
{
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update pharm.DRUG set D_NAME = '" + updatename.Text + "', MFD = '"+ textBox3.Text +"', EXP= '"+ updateexp.Text + "' , COST ="+ updatecost.Text +", QUANTITY = "+ textBox2.Text + " WHERE DRUG_ID = " + textBox1.Text;
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not inserted");
else
MessageBox.Show("Success!");
conn.Dispose();
}
public bool Limpeza()
{
bool ret = false;
try
{
using (OracleConnection connection = new OracleConnection(RetornoCIP.CNX_C3))
{
OracleCommand command = new OracleCommand("TRUNCATE TABLE CCCTBLBRE", connection);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
if (Utils._logger != null)
Utils._logger.Info("Limpeza ok");
ret = true;
}
}
catch (Exception ex)
{
if (Utils._logger != null)
Utils._logger.Error(ex.Message);
}
return ret;
}
private void button3_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Are You Sure", "Some Title", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
conn.Open();
string sqlquery = "DELETE FROM MED_INFO WHERE MED_ID='" + ni + "'";
OracleCommand cmd = new OracleCommand(sqlquery, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
sqlquery = "DELETE FROM MED_STORE WHERE MED_ID='" + ni + "'";
cmd = new OracleCommand(sqlquery, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
this.Hide();
}
else if (dialogResult == DialogResult.No)
{
///
}
}
/// <summary>
/// voegt review toe
/// </summary>
/// <param name="gebruikerid">id van de gebruiker die de review plaatst</param>
/// <param name="productnaam">naam van product dat bij de review hoort</param>
/// <param name="titel">titel van de review</param>
/// <param name="datum">datum van plaatsing van review</param>
/// <param name="reactie">de review zelf</param>
/// <param name="score">score van product</param>
/// <returns>true als het gelukt is anders false</returns>
public bool AddReview(int gebruikerid, string productnaam, string titel, DateTime datum, string reactie, int score )
{
OracleCommand cmd = new OracleCommand();
try
{
this.Connect();
cmd.Connection = this.conn;
cmd.CommandText = "CreateReview";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_gebruikerid", OracleDbType.Int32, gebruikerid, ParameterDirection.Input);
cmd.Parameters.Add("p_productnaam", OracleDbType.Varchar2, productnaam, ParameterDirection.Input);
cmd.Parameters.Add("p_titel", OracleDbType.Varchar2, titel, ParameterDirection.Input);
cmd.Parameters.Add("P_datum", OracleDbType.Date, datum, ParameterDirection.Input);
cmd.Parameters.Add("p_reactie", OracleDbType.Varchar2, reactie, ParameterDirection.Input);
cmd.Parameters.Add("p_score", OracleDbType.Varchar2, score, ParameterDirection.Input);
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
this.conn.Close();
}
return true;
}
public bool Alterar(Entrada entrada)
{
try
{
int cod_entrada = entrada.Cod_Entrada1;
string tipo_entrada = entrada.Tipo_Entrada1;
float valor = entrada.Valor1;
DateTime data_entrada = entrada.Data_Entrada1;
int cod_igreja = entrada.Cod_igreja1;
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = _conn.ObterConexao();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "ALTERA_ENTRADA";
cmd.Parameters.Add("PK", OracleDbType.Int32).Value = entrada.Cod_Entrada1;
cmd.Parameters.Add("PIGREJA", OracleDbType.Int32).Value = entrada.Cod_igreja1;
cmd.Parameters.Add("PTIPOENTRADA", OracleDbType.Varchar2).Value = entrada.Tipo_Entrada1;
cmd.Parameters.Add("PVALOR", OracleDbType.BinaryFloat).Value = entrada.Valor1;
cmd.ExecuteNonQuery();
return true;
}
}
catch (OracleException ex)
{
throw ex;
}
}
private void bt_Enregistrer_Click(object sender, EventArgs e)
{
string insert = "INSERT INTO EMPLOYES1 VALUES (SEQNUMEMP.NEXTVAL, :nom , :prenom , :salaire , :dateembauche , (SELECT CODEDEP FROM DEPARTEMENT WHERE NOM=:departement ), :responsable )";
try
{
if (tb_Nom.Text.Equals(String.Empty)) throw new Exception("Le nom est vide.");
if (tb_Prenom.Text.Equals(String.Empty)) throw new Exception("Le prénom est vide.");
if (tb_Resp.Text.Equals(String.Empty)) throw new Exception("Le responsable est vide.");
if (tb_Salaire.Text.Equals(string.Empty)) throw new Exception("Le salaire est vide.");
if (double.Parse(tb_Salaire.Text) < 0) throw new Exception("Le salaire ne peut pas être négatif.");
if (double.Parse(tb_Salaire.Text) >= 500000) throw new Exception("Le salaire doit être plus petit que 500 000.");
OracleCommand cmd = new OracleCommand(insert, _conn);
cmd.Parameters.Add(new OracleParameter("nom", tb_Nom.Text));
cmd.Parameters.Add(new OracleParameter("prenom", tb_Prenom.Text));
cmd.Parameters.Add(new OracleParameter("salaire", double.Parse(tb_Salaire.Text)));
cmd.Parameters.Add(new OracleParameter("dateembauche", dtp_date.Value.Date));
cmd.Parameters.Add(new OracleParameter("departement", cb_Dept.Text));
cmd.Parameters.Add(new OracleParameter("responsable", tb_Resp.Text));
cmd.ExecuteNonQuery();
_conn.Close();
MessageBox.Show("L'employé a été ajouté.");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
static void ExecuteDropCommand(OracleConnection connection, string statement)
{
using (var dropCmd = new OracleCommand(statement, connection))
{
dropCmd.ExecuteNonQuery();
}
}
/// <summary>
/// Voegt een Bestemming toe aan de DB
/// </summary>
/// <param name="bestemming"></param>
/// <returns></returns>
public bool AddBestemming(Bestemming bestemming)
{
bool success = false;
try
{
string sql = @"INSERT INTO Bestemming VALUES( :naam, :land)";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add("naam", OracleDbType.Varchar2, bestemming.Naam, ParameterDirection.Input);
cmd.Parameters.Add("land", OracleDbType.Varchar2, bestemming.Land, ParameterDirection.Input);
cmd.CommandType = CommandType.Text;
Open();
if (cmd.ExecuteNonQuery() == 0)
success = false;
else
success = true;
}
catch
{
success = false;
}
finally
{
conn.Close();
}
return success;
}
public void adicionarProfesional(string idProfesional,string passwordProfesional,string nombreProfesional,string apellidosProfesional,string cedulaProfesional ,string correoProfesional ,string areaProfesional)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "profesionales_paquete.adicionarProfesional";
OracleParameter idProfesionalP = new OracleParameter("idProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
idProfesionalP.Value = idProfesional;
OracleParameter passwordProfesionalP = new OracleParameter("passwordProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
passwordProfesionalP.Value = passwordProfesional;
OracleParameter nombreProfesionalP = new OracleParameter("nombreProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
nombreProfesionalP.Value = nombreProfesional;
OracleParameter apellidosProfesionalP = new OracleParameter("apellidosProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
apellidosProfesionalP.Value = apellidosProfesional;
OracleParameter cedulaProfesionalP = new OracleParameter("cedulaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
cedulaProfesionalP.Value = cedulaProfesional;
OracleParameter correoProfesionalP = new OracleParameter("correoProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
correoProfesionalP.Value = correoProfesional;
OracleParameter areaProfesionalP = new OracleParameter("areaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
areaProfesionalP.Value = areaProfesional;
cmd.Parameters.AddRange(new OracleParameter[] { idProfesionalP, passwordProfesionalP, nombreProfesionalP, apellidosProfesionalP, cedulaProfesionalP, correoProfesionalP, areaProfesionalP });
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
/// <summary>
/// 执行数据库事务查询操作,返回受影响的行数
/// </summary>
/// <param name="transaction">数据库事务对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前事务查询操作影响的数据行数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand command = new OracleCommand();
OracleConnection connection = transaction.Connection;
int result = 0;
try
{
PrepareCommand(command, connection, transaction, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
transaction.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
}
return result;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (string.IsNullOrWhiteSpace(this.textBox1.Text))
{
MessageBox.Show("Fill the Group name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleCommand cmd = new OracleCommand("INSERT INTO MED_GROUP(GNAME,GDESCRIPTION,NOTES) VALUES('" + textBox1.Text + "','"+richTextBox1.Text+"','"+richTextBox2.Text+"')", conn);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
MessageBox.Show("Information Uploaded...");
this.Close();
}
}
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
conn.Close();
}
// CREATE NEW ROW IN DATABASE
protected void send_card_btn_Click(object sender, EventArgs e)
{
// Setting bind variables from textboxes
sender_fname = s_fname_input.Text;
sender_lname = s_lname_input.Text;
sender_email = s_email_input.Text;
patient_fname = p_fname_input.Text;
patient_lname = p_lname_input.Text;
card_choice = card_choice_input.SelectedValue;
card_message = message_input.Text;
if (sender_fname == "" || sender_fname == "" || sender_email == "" || patient_fname == "" || patient_lname == "" || card_message == "")
{
server_output.InnerHtml = "Please fill in all areas.";
}
else
{
// Validate Inputs
sender_email = s_email_input.Text;
Regex regex = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
Match match = regex.Match(sender_email);
if (match.Success)
{
// Resetting Rows
rows = 0;
try
{
conn.Open(); // Open Connection
// Grabbing Patient ID from persons table
select_statment = "SELECT * FROM persons WHERE upper(person_first_name) = upper(:p_fname) AND upper(person_last_name) = upper(:p_lname)";
// Executing Oracle Command
OracleCommand cmd1 = new OracleCommand(select_statment, conn);
cmd1.Parameters.Add(new OracleParameter("p_fname", patient_fname));
cmd1.Parameters.Add(new OracleParameter("p_lname", patient_lname));
OracleDataReader reader = cmd1.ExecuteReader();
// Grabbing n ID for Insert bind variable
while (reader.Read())
{
person_id = Convert.ToInt32(reader["person_id"]);
rows += 1;
}
if (rows > 0)
{
rows = 0;
// Execute Update Statement
command = "INSERT INTO ecards "
+ "VALUES (ecard_id_seq.nextval, :s_fname, :s_lname, :s_email, :p_id, :c_choice, :c_message)";
OracleCommand cmd = new OracleCommand(command, conn);
// Setting Bind Variables
cmd.Parameters.Add(new OracleParameter("s_fname", sender_fname));
cmd.Parameters.Add(new OracleParameter("s_lname", sender_lname));
cmd.Parameters.Add(new OracleParameter("s_email", sender_email));
cmd.Parameters.Add(new OracleParameter("p_id", person_id));
cmd.Parameters.Add(new OracleParameter("c_choice", card_choice));
cmd.Parameters.Add(new OracleParameter("c_message", card_message));
rows = cmd.ExecuteNonQuery();
}
else
{
patient_err.Text = "There is no one at the hospital with this name.";
}
// Close Connection
conn.Close();
}
catch (OracleException ex)
{
server_output.InnerHtml = "<div>" + ex.Message + "</div>";
}
finally
{
// Outputting Results
server_output.InnerHtml += "<div>" + Convert.ToString(rows) + " ecard created. </div>";
}
}
else
{
email_err.Text = "Email is incorrect";
}
}
}
public string GetSeqNum(string VerifyInfo, string DescInfo)
{
string connstr;
connstr = ConfigurationManager.AppSettings["oraclestr"];
OracleConnection conn = new OracleConnection(connstr);
long retSeq = 0;
long MaxSeq = 0;
long MinSeq = 0;
long SeqLength = 0;
try
{
string strSql = "Select SEQNO,MAXSEQ,MINSEQ,SEQLENS from COMM_SEQUENCE where SEQDESC='" + DescInfo + "'";
OracleCommand clsComd = new OracleCommand(strSql, conn);
OracleDataReader clsReader;
clsReader = clsComd.ExecuteReader();
if (clsReader.Read())
{
retSeq = clsReader.GetInt64(0);
MaxSeq = clsReader.GetInt64(1);
MinSeq = clsReader.GetInt64(2);
SeqLength = clsReader.GetInt64(3);
}
conn.Close();
if (retSeq < MaxSeq)
{
strSql = "Update COMM_SEQUENCE Set SEQNO=SEQNO+1 where SEQDESC='" + DescInfo + "'";
}
else
{
strSql = "Update COMM_SEQUENCE Set SEQNO=" + Convert.ToString(MinSeq) + " where SEQDESC='" + DescInfo + "'";
}
clsComd.CommandText = strSql;
conn.Open();
OracleTransaction mytrans = conn.BeginTransaction();
try
{
clsComd.Transaction = mytrans;
clsComd.ExecuteNonQuery();
mytrans.Commit();
conn.Close();
}
catch (Exception mye)
{
mytrans.Rollback();
conn.Close();
return(mye.Message);
}
}
catch (Exception e)
{
conn.Close();
return(e.Message);
}
string tempSeq = Convert.ToString(retSeq);
for (int i = tempSeq.Length; i < SeqLength; i++)
{
tempSeq = "0" + tempSeq;
}
return(tempSeq);
}
//========================================================================================
// Explain()
//========================================================================================
private void Explain(Query query)
{
string tableName = UserOptions.GetString("connections/planTable");
using (var transaction = dbase.OraConnection.BeginTransaction())
{
if (EnsurePlanTable(query, tableName))
{
string planID = "ORQA_" + DateTime.Now.Ticks.ToString("X");
string text
= "EXPLAIN PLAN SET statement_id='" + planID
+ "' INTO " + dbase.DefaultSchema + "." + tableName
+ " FOR " + query.SQL;
try
{
using (var cmd = new OracleCommand(text, dbase.OraConnection))
{
// explain the query
cmd.ExecuteNonQuery();
// load the explanation
// we use these colulmns: level, operation, optoins, object_name, cost, cardinality, bytes
cmd.CommandText
= "SELECT level-1 AS \"level\","
+ " level, operation, options, object_name, cost, cardinality, bytes"
//+ " statement_id, timestamp, remarks, operation, options, object_node, object_name,"
//+ " object_instance, object_type, optimizer, search_columns, id, parent_id, position,"
//+ " cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id,"
//+ " other, distribution"
+ " FROM " + dbase.DefaultSchema + "." + tableName
+ " CONNECT BY PRIOR id=parent_id"
+ " START WITH id=0"
+ " ORDER BY id";
var adapter = new OracleDataAdapter();
adapter.SelectCommand = cmd;
query.Plan = new DataSet();
adapter.Fill(query.Plan);
}
}
catch (OracleException exc)
{
query.AddMessage(exc);
}
catch (Exception exc)
{
if (exc.Message == String.Empty)
{
query.AddMessage(new Message(Message.MessageType.Error, exc.StackTrace));
}
else
{
query.AddMessage(new Message(Message.MessageType.Error, exc.Message));
}
}
}
transaction.Commit();
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return(true);
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
private void updatebtn_Click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection();
con.ConnectionString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-E896G02)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)));;User ID=QuanLyLopHoc;Password=123;Connection Timeout=120;";
if (capquyenrolerb.Checked)
{
if (CheckRole(tenuserroletb.Text.ToUpper()) == 0)
{
MessageBox.Show("Role không tồn tại.");
con.Close();
return;
}
}
else
{
if (CheckUser(tenuserroletb.Text.ToUpper()) == 0)
{
MessageBox.Show("User không tồn tại.");
con.Close();
return;
}
}
con.Open();
if (hocsinhrb.Checked) //update trên bảng hocsinh
{
OracleCommand cmd_update = new OracleCommand();
cmd_update.Connection = con;
if (HOCSINH.CheckedItems.Count == 0 || HOCSINH.CheckedItems.Count == 5) //cấp quyền upate trên cả bảng
{
cmd_update.CommandText = "grant update on HocSinh to " + tenuserroletb.Text.ToUpper();
}
else
{
//kiểm tra cấp quyền update trên những cột nào
string column = "";
for (int i = 0; i < HOCSINH.Items.Count; i++)
{
if (HOCSINH.GetItemCheckState(i) == CheckState.Checked)
{
column += HOCSINH.Items[i].ToString() + ",";
}
}
//xóa dấu , ở cuối
column = column.TrimEnd(',');
cmd_update.CommandText = "grant update(" + column + ") on HocSinh to " + tenuserroletb.Text.ToUpper();
}
if (wgohscb.Checked)//có with grant option
{
cmd_update.CommandText += " WITH GRANT OPTION";
}
cmd_update.CommandType = CommandType.Text;
cmd_update.ExecuteNonQuery();
MessageBox.Show("Cấp quyền update thành công");
//load lại thong tin quyền
ThongTinQuyen();
}
else //update trên bảng lop
{
OracleCommand cmd_update = new OracleCommand();
cmd_update.Connection = con;
if (LOP.CheckedItems.Count == 0 || LOP.CheckedItems.Count == 5) //cấp quyền upate trên cả bảng
{
cmd_update.CommandText = "grant update on Lop to " + tenuserroletb.Text.ToUpper();
}
else
{
//kiểm tra cấp quyền update trên những cột nào
string column = "";
for (int i = 0; i < LOP.Items.Count; i++)
{
if (LOP.GetItemCheckState(i) == CheckState.Checked)
{
column += LOP.Items[i].ToString() + ",";
}
}
//xóa dấu , ở cuối
column = column.TrimEnd(',');
cmd_update.CommandText = "grant update(" + column + ") on LOP to " + tenuserroletb.Text.ToUpper();
}
if (wgolcb.Checked)//có with grant option
{
cmd_update.CommandText += " WITH GRANT OPTION";
}
cmd_update.CommandType = CommandType.Text;
cmd_update.ExecuteNonQuery();
MessageBox.Show("Cấp quyền update thành công");
//load lại thong tin quyền
ThongTinQuyen();
}
con.Close();
}
private void selectbtn_Click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection();
con.ConnectionString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-E896G02)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)));;User ID=QuanLyLopHoc;Password=123;Connection Timeout=120;";
if (capquyenrolerb.Checked)
{
if (CheckRole(tenuserroletb.Text.ToUpper()) == 0)
{
MessageBox.Show("Role không tồn tại.");
con.Close();
return;
}
}
else
{
if (CheckUser(tenuserroletb.Text.ToUpper()) == 0)
{
MessageBox.Show("User không tồn tại.");
con.Close();
return;
}
}
con.Open();
if (hocsinhrb.Checked) //select trên bảng hocsinh
{
OracleCommand cmd_select = new OracleCommand();
cmd_select.Connection = con;
if (HOCSINH.CheckedItems.Count == 0 || HOCSINH.CheckedItems.Count == 5) //cấp quyền select trên cả bảng
{
cmd_select.CommandText = "grant select on HocSinh to " + tenuserroletb.Text.ToUpper();
}
else
{
//những cột viết tắt để tạo view không bị lỗi view quá dài
string[] short_name = { "MHS", "THS", "GT", "DC", "ML" };
//kiểm tra cấp quyền select trên những cột nào
string column = "";
//chọn ra những cột để select
string select_column = "";
for (int i = 0; i < HOCSINH.Items.Count; i++)
{
if (HOCSINH.GetItemCheckState(i) == CheckState.Checked)
{
column += short_name[i] + "_";
select_column += HOCSINH.Items[i].ToString() + ",";
}
}
//xóa dấu , ở cuối (để bỏ váo câu lệnh select)
select_column = select_column.TrimEnd(',');
//them HS vào cuối để biết là view từ bàng HS
column += "HS";
//kiểm tra view có tồn tại hay chưa
if (CheckView(column) == 0)//chưa tồn tại
{
//tạo view mới
OracleCommand cmd_taoview = new OracleCommand();
cmd_taoview.Connection = con;
cmd_taoview.CommandText = "create view " + column + " as select " + select_column + " from HOCSINH";
cmd_taoview.CommandType = CommandType.Text;
cmd_taoview.ExecuteNonQuery();
}
//cấp quyền đọc trên view này cho user
cmd_select.CommandText = "grant select on " + column + " to " + tenuserroletb.Text.ToUpper();
}
if (wgohscb.Checked)//có with grant option
{
cmd_select.CommandText += " WITH GRANT OPTION";
}
cmd_select.CommandType = CommandType.Text;
cmd_select.ExecuteNonQuery();
MessageBox.Show("Cấp quyền select thành công");
//load lại thong tin quyền
ThongTinQuyen();
}
else //select trên bảng lop
{
OracleCommand cmd_select = new OracleCommand();
cmd_select.Connection = con;
if (LOP.CheckedItems.Count == 0 || LOP.CheckedItems.Count == 5) //cấp quyền select trên cả bảng
{
cmd_select.CommandText = "grant select on Lop to " + tenuserroletb.Text.ToUpper();
}
else
{
//kiểm tra cấp quyền select trên những cột nào
//những cột viết tắt để tạo view không bị lỗi view quá dài
string[] short_name = { "ML", "TL", "SHSTD" };
//kiểm tra cấp quyền select trên những cột nào
string column = "";
//chọn ra những cột để select
string select_column = "";
for (int i = 0; i < LOP.Items.Count; i++)
{
if (LOP.GetItemCheckState(i) == CheckState.Checked)
{
column += short_name[i] + "_";
select_column += LOP.Items[i].ToString() + ",";
}
}
//xóa dấu , ở cuối (để bỏ váo câu lệnh select)
select_column = select_column.TrimEnd(',');
//them vào cuối để biết là view từ bàng Lop
column += "L";
//kiểm tra view có tồn tại?
if (CheckView(column) == 0)//chưa tồn tại
{
//tạo view mới
OracleCommand cmd_taoview = new OracleCommand();
cmd_taoview.Connection = con;
cmd_taoview.CommandText = "create view " + column + " as select " + select_column + " from LOP";
cmd_taoview.CommandType = CommandType.Text;
cmd_taoview.ExecuteNonQuery();
}
cmd_select.CommandText = "grant select on " + column + " to " + tenuserroletb.Text.ToUpper();
}
if (wgolcb.Checked)//có with grant option
{
cmd_select.CommandText += " WITH GRANT OPTION";
}
cmd_select.CommandType = CommandType.Text;
cmd_select.ExecuteNonQuery();
MessageBox.Show("Cấp quyền select thành công");
//load lại thong tin quyền
ThongTinQuyen();
}
con.Close();
}
public override void InsertData(object obj, out int errCode, out string errText)
{
string strProcedureName = string.Format("{0}.{1}", className, MethodBase.GetCurrentMethod().Name);
WriteLog.Instance.WriteBeginSplitter(strProcedureName);
errCode = 0;
errText = "";
if (obj == null)
{
return;
}
var item = obj as LoanEntity;
if (item == null)
{
return;
}
try {
WriteLog.Instance.Write(
string.Format("执行存储过程 " +
"usp_wt_dksl，参数：" +
"i_ywno={0}|i_gracntno={1}|i_grname={2}|i_citizenidno={3}|i_hyid={4}|i_hk={5}|i_dwname={6}"
+ "|i_dept={7}|i_address={8}|i_postcode={9}|i_jobid={10}|i_ZCID={11}|i_HomeAddress={12}"
+ "|i_homepostcode={13}|i_Salary={14}|i_PeopleOfFamily={15}|i_Debit={16}|i_MobileNo={17}|i_GRAcntNo2={18}"
+ "|i_GRName2={19}|i_CitizenIDNo2={20}|i_HK2={21}|i_DWName2={22}|i_Dept2={23}|i_Address2={24}"
+ "|i_PostCode2={25}|i_JobID2={26}|i_ZCID2={27}|i_MobileNo2={28}|i_GFLXID={29}|i_ContractNo={30}"
+ "|i_Seller={31}|i_HouseAddress={32}|i_BuildArea={33}|i_PayMoney={34}|i_HouseAmount={35}|i_HousePrice={36}"
+ "|i_CertNo={37}|i_ContractDate={38}|i_DKType={39}|i_LoanAmount={40}|i_LoanMonths={41}|i_PayMethod={42}"
+ "|i_IsBizLoan={43}|i_BizLoanAmount={44}|i_BizLoanMonth={45}|i_IsMerge={46}|i_MergeAmount={47}|i_BuyHouseType={48}"
+ "|i_GJJLoanInfo={49}|i_DYType={50}|i_BDCQZ={51}|i_DYHouseAddress={52}|i_DYBuildArea={53}|i_DYBelong={54}"
+ "|i_PayBankCode={55}|i_PayAcntName={56}|i_PayAcntNo={57}|i_remark={58}",
item.TRANSACTNO, item.GRACNTNO, item.GRNAME, item.CITIZENIDNO, item.HYID, item.HK, item.DWNAME,
item.DEPT, item.ADDRESS, item.POSTCODE, item.JOBID, item.ZCID, item.HOMEADDRESS,
item.HOMEPOSTCODE, item.SALARY, item.PEOPLEOFFAMILY, item.DEBIT, item.MOBILENO, item.GRACNTNO2,
item.GRNAME2, item.CITIZENIDNO2, item.HK2, item.DWNAME2, item.DEPT2, item.ADDRESS2,
item.POSTCODE2, item.JOBID2, item.ZCID2, item.MOBILENO2, item.GFLXID, item.CONTRACTNO,
item.SELLER, item.HOUSEADDRESS, item.BUILDAREA, item.PAYMONEY, item.HOUSEAMOUNT, item.HOUSEPRICE,
item.CERTNO, item.CONTRACTDATE, item.DKTYPE, item.LOANAMOUNT, item.LOANMONTHS, item.PAYMETHOD,
item.ISBIZLOAN, item.BIZLOANAMOUNT, item.BIZLOANMONTH, item.ISMERGE, item.MERGEAMOUNT, item.BUYHOUSETYPE,
item.GJJLOANINFO, item.DYTYPE, item.BDCQZ, item.DYHOUSEADDRESS, item.DYBUILDAREA, item.DYBELONG,
item.PAYBANKCODE, item.PAYACNTNAME, item.PAYACNTNO, item.REMARK),
strProcedureName);
#region 执行数据库函数或存储过程
using (OracleConnection conn = new OracleConnection(CON_STR_TARGET)) {
conn.Open();
OracleCommand ocm = conn.CreateCommand();
ocm.CommandType = CommandType.StoredProcedure;
ocm.CommandText = "usp_wt_dksl";
#region 添加参数
ocm.Parameters.Add("i_ywno", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_ywno"].Value = item.TRANSACTNO;
ocm.Parameters.Add("i_gracntno", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_gracntno"].Value = item.GRACNTNO;
ocm.Parameters.Add("i_citizenidno", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_citizenidno"].Value = item.CITIZENIDNO;
ocm.Parameters.Add("i_hyid", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_hyid"].Value = item.HYID;
ocm.Parameters.Add("i_hk", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_hk"].Value = item.HK;
ocm.Parameters.Add("i_dwname", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_dwname"].Value = item.DWNAME;
ocm.Parameters.Add("i_dept", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_dept"].Value = item.DEPT;
ocm.Parameters.Add("i_address", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_address"].Value = item.ADDRESS;
ocm.Parameters.Add("i_postcode", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_postcode"].Value = item.POSTCODE;
ocm.Parameters.Add("i_jobid", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_jobid"].Value = item.JOBID;
ocm.Parameters.Add("i_ZCID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_ZCID"].Value = item.ZCID;
ocm.Parameters.Add("i_HomeAddress", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_HomeAddress"].Value = item.HOMEADDRESS;
ocm.Parameters.Add("i_homepostcode", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_homepostcode"].Value = item.HOMEPOSTCODE;
ocm.Parameters.Add("i_Salary", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_Salary"].Value = item.SALARY;
ocm.Parameters.Add("i_PeopleOfFamily", OracleDbType.Int64).Direction = ParameterDirection.Input;
ocm.Parameters["i_PeopleOfFamily"].Value = item.PEOPLEOFFAMILY;
ocm.Parameters.Add("i_Debit", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_Debit"].Value = item.DEBIT;
ocm.Parameters.Add("i_MobileNo", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_MobileNo"].Value = item.MOBILENO;
ocm.Parameters.Add("i_GRAcntNo2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_GRAcntNo2"].Value = item.GRACNTNO2;
ocm.Parameters.Add("i_GRName2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_GRName2"].Value = item.GRNAME2;
ocm.Parameters.Add("i_CitizenIDNo2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_CitizenIDNo2"].Value = item.CITIZENIDNO2;
ocm.Parameters.Add("i_HK2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_HK2"].Value = item.HK2;
ocm.Parameters.Add("i_DWName2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_DWName2"].Value = item.DWNAME2;
ocm.Parameters.Add("i_Dept2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_Dept2"].Value = item.DEPT2;
ocm.Parameters.Add("i_Address2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_Address2"].Value = item.ADDRESS2;
ocm.Parameters.Add("i_PostCode2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_PostCode2"].Value = item.POSTCODE2;
ocm.Parameters.Add("i_JobID2", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_JobID2"].Value = item.JOBID2;
ocm.Parameters.Add("i_ZCID2", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_ZCID2"].Value = item.ZCID2;
ocm.Parameters.Add("i_MobileNo2", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_MobileNo2"].Value = item.MOBILENO2;
ocm.Parameters.Add("i_GFLXID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_GFLXID"].Value = item.GFLXID;
ocm.Parameters.Add("i_ContractNo", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_ContractNo"].Value = item.CONTRACTNO;
ocm.Parameters.Add("i_Seller", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_Seller"].Value = item.SELLER;
ocm.Parameters.Add("i_HouseAddress", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_HouseAddress"].Value = item.HOUSEADDRESS;
ocm.Parameters.Add("i_BuildArea", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_BuildArea"].Value = item.BUILDAREA;
ocm.Parameters.Add("i_PayMoney", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_PayMoney"].Value = item.PAYMONEY;
ocm.Parameters.Add("i_HouseAmount", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_HouseAmount"].Value = item.HOUSEAMOUNT;
ocm.Parameters.Add("i_HousePrice", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_HousePrice"].Value = item.HOUSEPRICE;
ocm.Parameters.Add("i_CertNo", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_CertNo"].Value = item.CERTNO;
ocm.Parameters.Add("i_ContractDate", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_ContractDate"].Value = item.CONTRACTNO;
ocm.Parameters.Add("i_DKType", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_DKType"].Value = item.DKTYPE;
ocm.Parameters.Add("i_LoanAmount", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_LoanAmount"].Value = item.LOANAMOUNT;
ocm.Parameters.Add("i_LoanMonths", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_LoanMonths"].Value = item.LOANMONTHS;
ocm.Parameters.Add("i_PayMethod", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_PayMethod"].Value = item.PAYMETHOD;
ocm.Parameters.Add("i_IsBizLoan", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_IsBizLoan"].Value = item.ISBIZLOAN;
ocm.Parameters.Add("i_BizLoanAmount", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_BizLoanAmount"].Value = item.BIZLOANAMOUNT;
ocm.Parameters.Add("i_BizLoanMonth", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_BizLoanMonth"].Value = item.BIZLOANMONTH;
ocm.Parameters.Add("i_IsMerge", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_IsMerge"].Value = item.ISMERGE;
ocm.Parameters.Add("i_MergeAmount", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_MergeAmount"].Value = item.MERGEAMOUNT;
ocm.Parameters.Add("i_BuyHouseType", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_BuyHouseType"].Value = item.BUYHOUSETYPE;
ocm.Parameters.Add("i_GJJLoanInfo", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_GJJLoanInfo"].Value = item.GJJLOANINFO;
ocm.Parameters.Add("i_DYType", OracleDbType.Decimal).Direction = ParameterDirection.Input;
ocm.Parameters["i_DYType"].Value = item.DYTYPE;
ocm.Parameters.Add("i_BDCQZ", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_BDCQZ"].Value = item.BDCQZ;
ocm.Parameters.Add("i_DYHouseAddress", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_DYHouseAddress"].Value = item.DYHOUSEADDRESS;
ocm.Parameters.Add("i_DYBuildArea", OracleDbType.Double).Direction = ParameterDirection.Input;
ocm.Parameters["i_DYBuildArea"].Value = item.DYBUILDAREA;
ocm.Parameters.Add("i_DYBelong", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_DYBelong"].Value = item.DYBELONG;
ocm.Parameters.Add("i_PayBankCode", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_PayBankCode"].Value = item.PAYBANKCODE;
ocm.Parameters.Add("i_PayAcntName", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_PayAcntName"].Value = item.PAYACNTNAME;
ocm.Parameters.Add("i_PayAcntNo", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_PayAcntNo"].Value = item.PAYACNTNO;
ocm.Parameters.Add("i_DYBelong", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_DYBelong"].Value = item.DYBELONG;
ocm.Parameters.Add("i_remark", OracleDbType.Varchar2).Direction = ParameterDirection.Input;
ocm.Parameters["i_remark"].Value = item.REMARK;
ocm.Parameters.Add("o_errCode", OracleDbType.Int32).Direction = ParameterDirection.Output;
ocm.Parameters.Add("o_errtext", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
#endregion
ocm.ExecuteNonQuery();
errCode = ocm.Parameters["o_errCode"] == null ? 0 : Convert.ToInt32(ocm.Parameters["o_errCode"].Value.ToString());
errText = ocm.Parameters["o_errtext"] == null || ocm.Parameters["o_errtext"].Value == null ? null : ocm.Parameters["o_errtext"].Value.ToString();
}
var errInfo = errText;
this.SaveResult(item.TRANSACTNO, errCode, errText);
errInfo += errText;
ShowResult(item.REMARK, errInfo);
#endregion
} catch (Exception error) {
errCode = 99000;
errText = string.Format("获取视图usp_wt_dksl内容时发生异常：{0}", error.Message);
ShowResult(item.REMARK, errText);
WriteLog.Instance.Write(errText, strProcedureName);
} finally {
WriteLog.Instance.WriteEndSplitter(strProcedureName);
}
}
private void button4_Click(object sender, EventArgs e)
{
ora.Open();
comando = new OracleCommand();
comando.Connection = ora;
String RESPUESTA = "";
foreach (DataGridViewRow row in dataGridView4.Rows)
{
if ((row.Index + 1) == dataGridView3.Rows.Count)
{
break;
}
OracleTransaction trans = ora.BeginTransaction();
comando.Transaction = trans;
String[] linea = new String[6];
linea[0] = row.Cells["banco"].Value.ToString();
linea[1] = row.Cells["referencia"].Value.ToString();
linea[2] = row.Cells["cuenta"].Value.ToString();
linea[3] = row.Cells["nocheque"].Value.ToString();
linea[4] = row.Cells["monto"].Value.ToString();
//BANCO|REFERENCIA|CUENTA|NO_CHEQUE|MONTO
String estatus = "OK";
//String log = "";
//HACER COBRO DEL CHEQUE
DateTime fecha = DateTime.Now;
var chequera = 0;
try
{
//VERIFICACION INICIAL DEL CHEQUE
comando.CommandText = "SELECT descripcion, nombre FROM estado_cheque WHERE codigo = (SELECT estado_cheque FROM cheque_local WHERE codigo_cheque = :cheque)";
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
OracleDataReader dr = comando.ExecuteReader();
//dr.Read();
if (dr.Read())
{
throw new Exception("Error el cheque aparece: " + dr["descripcion"] + " " + dr["nombre"]);
}
//EXISTE EL CHEQUE
comando.Parameters.Clear();
bool verif_cheq = false;
Int32 cod_cheque = Convert.ToInt32(linea[3]);
comando.CommandText = "SELECT * FROM chequera WHERE cuenta = :cuenta";
comando.Parameters.Add("cuenta", OracleType.Number).Value = linea[2];
dr = comando.ExecuteReader();
while (!verif_cheq && dr.Read())
{
//CHEQUE PERTENEZCA A CHEQUERA
if (cod_cheque >= Convert.ToInt32(dr["numero_inicio"]) && cod_cheque <= Convert.ToInt32(dr["numero_final"]))
{
verif_cheq = true;
chequera = Convert.ToInt32(dr["codigo_chequera"]);
}
//dr.NextResult();
}
if (!verif_cheq)
{
throw new Exception("El cheque no pertenece a ninguna chequera o a esa cuenta");
}
//LA CUENTA TIENE FONDOS
comando.Parameters.Clear();
comando.CommandText = "SELECT saldo_disponible FROM cuenta WHERE numero_cuenta = :cuenta";
comando.Parameters.Add("cuenta", OracleType.Number).Value = linea[2];
dr = comando.ExecuteReader();
dr.Read();
Int32 saldo = Convert.ToInt32(dr["saldo_disponible"]);
double monto = Convert.ToDouble(linea[4]);
if (saldo < monto)
{
throw new Exception("NO TIENE FONDOS");
}
//SE DEBITA DE LA CUENTA
comando.Parameters.Clear();
comando.CommandText = "UPDATE cuenta SET saldo_disponible = saldo_disponible - :monto WHERE numero_cuenta = :cuenta";
comando.Parameters.Add("cuenta", OracleType.Number).Value = linea[2];
comando.Parameters.Add("monto", OracleType.Number).Value = Convert.ToDouble(linea[4]);
comando.ExecuteNonQuery();
//CHEQUE COBRADO
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO CHEQUE_LOCAL (codigo_cheque,fecha,monto,chequera,estado_cheque)" +
"VALUES(:codigo_cheque,:fecha,:monto,:chequera,:estado_cheque)";
comando.Parameters.Add("codigo_cheque", OracleType.Number).Value = linea[3];
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("monto", OracleType.Number).Value = linea[4];
comando.Parameters.Add("chequera", OracleType.Number).Value = chequera;
comando.Parameters.Add("estado_cheque", OracleType.Number).Value = 4;
comando.ExecuteNonQuery();
//REGISTRAR LA TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION (FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_LOCAL) " +
"VALUES(:fecha,'0','0',:valor,:empleado,:agencia,'0',:equipo,:cheque)";
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = Convert.ToDouble(linea[4]);
comando.Parameters.Add("cheque", OracleType.Number).Value = Convert.ToInt32(linea[3]);
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
if (ex.Message == "NO TIENE FONDOS")
{
//RECHAZAR CHEQUE
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO CHEQUE_LOCAL (codigo_cheque,fecha,monto,chequera,estado_cheque)" +
"VALUES(:codigo_cheque,:fecha,:monto,:chequera,:estado_cheque)";
comando.Parameters.Add("codigo_cheque", OracleType.Number).Value = linea[3];
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("monto", OracleType.Number).Value = linea[4];
comando.Parameters.Add("chequera", OracleType.Number).Value = chequera;
comando.Parameters.Add("estado_cheque", OracleType.Number).Value = 5;
comando.ExecuteNonQuery();
}
//REGISTRAR LA TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION (NO_RECHAZO, RAZON_RECHAZO,FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_LOCAL) " +
"VALUES(:norechazo,:razonrechazo,:fecha,'0','0',:valor,:empleado,:agencia,'0',:equipo,:cheque)";
comando.Parameters.Add("norechazo", OracleType.Number).Value = 1;
comando.Parameters.Add("razonrechazo", OracleType.Number).Value = ex.Message;
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = Convert.ToDouble(linea[4]);
comando.Parameters.Add("cheque", OracleType.Number).Value = Convert.ToInt32(linea[3]);
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
trans.Commit();
estatus = "1";
}
finally
{
//GRABAR EN LA VARIABLE DE RETORNO
RESPUESTA += ConvertStringArrayToString(linea) + "|" + estatus;
RESPUESTA += "\n";
}
}
//GUARDAR EL ARCHIVO
//IN_banco_correlativo.txt
string path = Directory.GetCurrentDirectory() + "\\IN_2_00.txt";
File.WriteAllText(path, RESPUESTA);
System.Windows.Forms.MessageBox.Show("El archivo \n" + path + "\n se ha creado correctamente");
}
private void button2_Click(object sender, EventArgs e)
{
ora.Open();
comando = new OracleCommand();
comando.Connection = ora;
//por cada elemento de la tabla aceptado
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if ((row.Index + 1) == dataGridView1.Rows.Count)
{
break;
}
OracleTransaction trans = ora.BeginTransaction();
comando.Transaction = trans;
var cuenta = row.Cells["cuenta1"].Value.ToString();
var monto = row.Cells["monto"].Value;
var cheque = row.Cells["cheque_externo"].Value;
try
{
if (cuenta == "")
{
//si no tiene cuenta solo se cambia el estado del cheque
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 6 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = cheque;
comando.ExecuteNonQuery();
}
else
{
/*
* if (!IsCuentaBloqueada(cuenta))
* {
* break;
* }*/
//restar monto a reserva
comando.Parameters.Clear();
comando.CommandText = "UPDATE cuenta SET en_reserva = en_reserva - :valor WHERE numero_cuenta = :cuenta";
comando.Parameters.Add("valor", OracleType.Number).Value = monto;
comando.Parameters.Add("cuenta", OracleType.Number).Value = cuenta;
comando.ExecuteNonQuery();
//agregra monto a saldo disponible
comando.Parameters.Clear();
comando.CommandText = "UPDATE cuenta SET saldo_disponible = saldo_disponible + :valor WHERE numero_cuenta = :cuenta";
comando.Parameters.Add("valor", OracleType.Number).Value = monto;
comando.Parameters.Add("cuenta", OracleType.Number).Value = cuenta;
comando.ExecuteNonQuery();
//se cambia el estado del cheque
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 4 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = cheque;
comando.ExecuteNonQuery();
}
trans.Commit();
}catch (Exception ex)
{
trans.Rollback();
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
//por cada elemento de la tabla rechazado
foreach (DataGridViewRow row in dataGridView3.Rows)
{
if ((row.Index + 1) == dataGridView3.Rows.Count)
{
break;
}
OracleTransaction trans = ora.BeginTransaction();
comando.Transaction = trans;
var cuenta = row.Cells["cuenta1"].Value;
var monto = row.Cells["monto"].Value;
var cheque = row.Cells["cheque_externo"].Value;
try
{
if (cuenta == "")
{
//si no tiene cuenta solo se cambia el estado del cheque
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 5 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = cheque;
comando.ExecuteNonQuery();
}
else
{
//restar monto a reserva
comando.Parameters.Clear();
comando.CommandText = "UPDATE cuenta SET en_reserva = en_reserva - :valor WHERE numero_cuenta = :cuenta";
comando.Parameters.Add("valor", OracleType.Number).Value = monto;
comando.Parameters.Add("cuenta", OracleType.Number).Value = cuenta;
comando.ExecuteNonQuery();
//se cambia el estado del cheque
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 5 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = cheque;
comando.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
System.Windows.Forms.MessageBox.Show("Operacion completada!");
ora.Close();
}
public string Insert(string VerifyInfo, string tablename, DataSet newdata)
{
////连接数据库
string connstr;
connstr = ConfigurationManager.AppSettings["oraclestr"];
OracleConnection conn = new OracleConnection(connstr);
conn.Open();
OracleTransaction mytrans = conn.BeginTransaction();
string sqlinsert;
string sqlhead, sqlvalue;
int i, j, k;
i = newdata.Tables[0].Rows.Count;
j = newdata.Tables[0].Columns.Count;
sqlinsert = "Insert into " + tablename + " ( ";
sqlhead = "";
sqlvalue = "";
for (k = 0; k < j; k++)
{
if (k > 0)
{
sqlhead += ",";
sqlvalue += ",";
}
sqlhead += newdata.Tables[0].Columns[k].ColumnName;
if (newdata.Tables[0].Rows[0][k].GetType() == Type.GetType("System.Int32") || newdata.Tables[0].Rows[0][k].GetType() == Type.GetType("System.Double") || newdata.Tables[0].Rows[0][k].GetType() == Type.GetType("System.Int16"))
{
sqlvalue += Convert.ToString(newdata.Tables[0].Rows[0][k]);
}
else if (newdata.Tables[0].Rows[0][k].GetType() == Type.GetType("System.DateTime"))
{
sqlvalue += "'" + Convert.ToString(Convert.ToDateTime(newdata.Tables[0].Rows[0][k])) + "'";
}
else
{
sqlvalue += "'" + newdata.Tables[0].Rows[0][k] + "'";
}
}
sqlinsert += sqlhead + ") values(" + sqlvalue + ")";
try
{
OracleCommand myCommand = new OracleCommand(sqlinsert);
myCommand.Connection = conn;
myCommand.Transaction = mytrans;
myCommand.ExecuteNonQuery();
mytrans.Commit();
conn.Close();
}
catch (Exception e)
{
mytrans.Rollback();
conn.Close();
return(e.Message);
}
return("0");
}
private void button1_Click(object sender, EventArgs e)
{
if (txtnocuenta.Text == "")
{
System.Windows.Forms.MessageBox.Show("debe ingresar un numero de cuenta"); return;
}
if (txtmonto.Text == "")
{
System.Windows.Forms.MessageBox.Show("debe ingresar un monto"); return;
}
if (txtnocheque.Text == "")
{
System.Windows.Forms.MessageBox.Show("debe ingresar un numero de cheque"); return;
}
if (!double.TryParse(txtmonto.Text, out double test))
{
System.Windows.Forms.MessageBox.Show("el monto no corresponde a un numero valido"); return;
}
ora.Open();
comando = new OracleCommand();
comando.Connection = ora;
OracleTransaction trans = ora.BeginTransaction();
comando.Transaction = trans;
try
{
DateTime fecha = DateTime.Now;
//CARGAR LOS DATOS A CHEQUES EXTERNOS
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO cheque_externo (codigo_cheque,fecha,cuenta,monto,banco,estado_cheque)" +
"VALUES (:codigo_cheque, :fecha,:cuenta,:monto,:banco,'7')";
comando.Parameters.Add("cuenta", OracleType.Number).Value = txtnocuenta.Text;
comando.Parameters.Add("codigo_cheque", OracleType.Number).Value = txtnocheque.Text;
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("monto", OracleType.Number).Value = txtmonto.Text;
comando.Parameters.Add("banco", OracleType.Number).Value = ((KeyValuePair <string, string>)combo_bank.SelectedItem).Key;
comando.ExecuteNonQuery();
//CARGAR LOS DATOS A LA TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION " +
" (FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_EXTERNO) " +
"VALUES(:fecha,'0','0',:valor,:empleado,:agencia,'2',:equipo,:cheque)";
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = Convert.ToDouble(txtmonto.Text);
comando.Parameters.Add("cheque", OracleType.Number).Value = Convert.ToInt32(txtnocheque.Text);
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
trans.Commit();
System.Windows.Forms.MessageBox.Show("Operacion Realizada con Exito");
}
catch (Exception ex)
{
trans.Rollback();
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
ora.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
ora.Open();
comando = new OracleCommand();
comando.Connection = ora;
foreach (DataGridViewRow row in dataGridView4.Rows)
{
if ((row.Index + 1) == dataGridView3.Rows.Count)
{
break;
}
OracleTransaction trans = ora.BeginTransaction();
comando.Transaction = trans;
String[] linea = new String[6];
linea[0] = row.Cells["banco"].Value.ToString();
linea[1] = row.Cells["referencia"].Value.ToString();
linea[2] = row.Cells["cuenta"].Value.ToString();
linea[3] = row.Cells["nocheque"].Value.ToString();
linea[4] = row.Cells["monto"].Value.ToString();
linea[5] = row.Cells["resultado"].Value.ToString();
try
{
DateTime fecha = DateTime.Now;
//BANCO|REFERENCIA|CUENTA|NO_CHEQUE|MONTO|RESPONSE
if (linea[5].ToLower() == "ok")
{
//GRABAR TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION (FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_EXTERNO) " +
"VALUES(:fecha,'0','0',:valor,:empleado,:agencia,'3',:equipo,:cheque)";
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = linea[4];
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
// REGISTRAR CAMBIO EN CHEQUE_EXTRANGERO
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 8 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.ExecuteNonQuery();
trans.Commit();
}
else
{
//GRABAR TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION (NO_RECHAZO, RAZON_RECHAZO, FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_EXTERNO) " +
"VALUES(:rechazo, :razon,:fecha,'0','0',:valor,:empleado,:agencia,'3',:equipo,:cheque)";
comando.Parameters.Add("rechazo", OracleType.Number).Value = 1;
comando.Parameters.Add("razon", OracleType.VarChar).Value = "NO TIENE FONDOS";
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = linea[4];
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
// REGISTRAR CAMBIO EN CHEQUE_EXTRANGERO
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 9 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.ExecuteNonQuery();
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
ora.Close();
System.Windows.Forms.MessageBox.Show("Se ha terminado de cargar todos los cheques");
}
public void processTypeObject()
{
IEnumerable <IIfcTypeProduct> types = _model.Instances.OfType <IIfcTypeProduct>();
foreach (IIfcTypeProduct typ in types)
{
OracleCommand command = new OracleCommand(" ", DBOperation.DBConn);
string SqlStmt = "Insert into " + DBOperation.formatTabName("BIMRL_TYPE") + "(ElementId, IfcType, Name, Description, ApplicableOccurrence"
+ ", Tag, ElementType, PredefinedType, AssemblyPlace, OperationType, ConstructionType, OwnerHistoryID, ModelID)"
+ " Values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)";
command.CommandText = SqlStmt;
string currStep = SqlStmt;
OracleParameter[] Param = new OracleParameter[13];
for (int i = 0; i < 11; i++)
{
Param[i] = command.Parameters.Add(i.ToString(), OracleDbType.Varchar2);
Param[i].Direction = ParameterDirection.Input;
Param[i].Size = 1;
}
Param[11] = command.Parameters.Add("11", OracleDbType.Int32);
Param[11].Direction = ParameterDirection.Input;
Param[11].Size = 1;
Param[12] = command.Parameters.Add("12", OracleDbType.Int32);
Param[12].Direction = ParameterDirection.Input;
Param[12].Size = 1;
List <OracleParameterStatus> arrBStatN = new List <OracleParameterStatus>();
List <OracleParameterStatus> arrBStatS = new List <OracleParameterStatus>();
arrBStatN.Add(OracleParameterStatus.NullInsert);
arrBStatS.Add(OracleParameterStatus.Success);
List <string> arrGuid = new List <string>();
List <string> IfcType = new List <string>();
List <string> arrName = new List <string>();
List <string> arrDesc = new List <string>();
List <string> arrAppO = new List <string>();
List <string> arrTag = new List <string>();
List <string> arrETyp = new List <string>();
List <string> arrPDTyp = new List <string>();
List <string> arrAPl = new List <string>();
List <string> arrOpTyp = new List <string>();
List <string> arrConsTyp = new List <string>();
List <int> arrOwnH = new List <int>();
List <OracleParameterStatus> arrOwnHPS = new List <OracleParameterStatus>();
List <int> arrModelID = new List <int>();
arrGuid.Add(typ.GlobalId.ToString());
Param[0].Value = arrGuid.ToArray();
IfcType.Add(typ.GetType().Name.ToUpper());
Param[1].Value = IfcType.ToArray();
if (typ.Name == null)
{
arrName.Add(string.Empty);
Param[2].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
arrName.Add(typ.Name);
Param[2].ArrayBindStatus = arrBStatS.ToArray();
}
Param[2].Value = arrName.ToArray();
if (typ.Description == null)
{
arrDesc.Add(string.Empty);
Param[3].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
arrDesc.Add(typ.Description);
Param[3].ArrayBindStatus = arrBStatS.ToArray();
}
Param[3].Value = arrDesc.ToArray();
if (typ.ApplicableOccurrence == null)
{
arrAppO.Add(string.Empty);
Param[4].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
arrAppO.Add(typ.ApplicableOccurrence);
Param[4].ArrayBindStatus = arrBStatS.ToArray();
}
Param[4].Value = arrAppO.ToArray();
if (typ.Tag == null)
{
arrTag.Add(string.Empty);
Param[5].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
arrTag.Add(typ.Tag);
Param[5].ArrayBindStatus = arrBStatS.ToArray();
}
Param[5].Value = arrTag.ToArray();
dynamic dynTyp = typ;
if (!(typ is IIfcDoorStyle || typ is IIfcWindowStyle))
{
if (dynTyp.ElementType == null)
{
arrETyp.Add(string.Empty);
Param[6].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
arrETyp.Add(dynTyp.ElementType);
Param[6].ArrayBindStatus = arrBStatS.ToArray();
}
Param[6].Value = arrETyp.ToArray();
}
if (typ is IIfcFurnitureType)
{
// these entities do not have PredefinedType
arrPDTyp.Add(string.Empty);
Param[7].Value = arrPDTyp.ToArray();
Param[7].ArrayBindStatus = arrBStatN.ToArray();
// This entity has a different attribute: AssemblyPlace. This must be placed ahead of its supertype IfcFurnishingElementType
IIfcFurnitureType ftyp = typ as IIfcFurnitureType;
arrAPl.Add(ftyp.AssemblyPlace.ToString());
Param[8].Value = arrAPl.ToArray();
if (String.IsNullOrEmpty(ftyp.AssemblyPlace.ToString()))
{
Param[8].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
Param[8].ArrayBindStatus = arrBStatS.ToArray();
}
arrOpTyp.Add(string.Empty);
Param[9].Value = arrOpTyp.ToArray();
Param[9].ArrayBindStatus = arrBStatN.ToArray();
arrConsTyp.Add(string.Empty);
Param[10].Value = arrConsTyp.ToArray();
Param[10].ArrayBindStatus = arrBStatN.ToArray();
}
else if (typ is IIfcFastenerType || typ is IIfcMechanicalFastenerType || typ is IIfcFurnishingElementType || typ is IIfcSystemFurnitureElementType ||
typ is IIfcDiscreteAccessoryType || typ is IIfcCurtainWallType)
{
// these entities do not have PredefinedType. Xbim also has not implemented IfcCurtainWallType and therefore no PredefinedType yet!!
arrPDTyp.Add(string.Empty);
Param[7].Value = arrPDTyp.ToArray();
Param[7].ArrayBindStatus = arrBStatN.ToArray();
arrAPl.Add(string.Empty);
Param[8].Value = arrAPl.ToArray();
Param[8].ArrayBindStatus = arrBStatN.ToArray();
arrOpTyp.Add(string.Empty);
Param[9].Value = arrOpTyp.ToArray();
Param[9].ArrayBindStatus = arrBStatN.ToArray();
arrConsTyp.Add(string.Empty);
Param[10].Value = arrConsTyp.ToArray();
Param[10].ArrayBindStatus = arrBStatN.ToArray();
}
// These entities do not have predefinedtype, but OperationType and ConstructionType
// We ignore ParameterTakesPrecedence and Sizeable are only useful for object construction
else if (typ is IIfcDoorStyle)
{
// these entities do not have PredefinedType
arrETyp.Add(string.Empty);
Param[6].Value = arrETyp.ToArray();
Param[6].ArrayBindStatus = arrBStatN.ToArray();
arrPDTyp.Add(string.Empty);
Param[7].Value = arrPDTyp.ToArray();
Param[7].ArrayBindStatus = arrBStatN.ToArray();
arrAPl.Add(string.Empty);
Param[8].Value = arrAPl.ToArray();
Param[8].ArrayBindStatus = arrBStatN.ToArray();
IIfcDoorStyle dst = typ as IIfcDoorStyle;
arrOpTyp.Add(dst.OperationType.ToString());
Param[9].Value = arrOpTyp.ToArray();
Param[9].ArrayBindStatus = arrBStatS.ToArray();
arrConsTyp.Add(dst.OperationType.ToString());
Param[10].Value = arrConsTyp.ToArray();
Param[10].ArrayBindStatus = arrBStatS.ToArray();
}
else if (typ is IIfcWindowStyle)
{
// these entities do not have PredefinedType
arrETyp.Add(string.Empty);
Param[6].Value = arrETyp.ToArray();
Param[6].ArrayBindStatus = arrBStatN.ToArray();
arrPDTyp.Add(string.Empty);
Param[7].Value = arrPDTyp.ToArray();
Param[7].ArrayBindStatus = arrBStatN.ToArray();
arrAPl.Add(string.Empty);
Param[8].Value = arrAPl.ToArray();
Param[8].ArrayBindStatus = arrBStatN.ToArray();
IIfcWindowStyle wst = typ as IIfcWindowStyle;
arrOpTyp.Add(wst.OperationType.ToString());
Param[9].Value = arrOpTyp.ToArray();
Param[9].ArrayBindStatus = arrBStatS.ToArray();
arrConsTyp.Add(wst.OperationType.ToString());
Param[10].Value = arrConsTyp.ToArray();
Param[10].ArrayBindStatus = arrBStatS.ToArray();
}
else
{
arrPDTyp.Add(dynTyp.PredefinedType.ToString());
Param[7].Value = arrPDTyp.ToArray();
if (String.IsNullOrEmpty(dynTyp.PredefinedType.ToString()))
{
Param[7].ArrayBindStatus = arrBStatN.ToArray();
}
else
{
Param[7].ArrayBindStatus = arrBStatS.ToArray();
}
arrAPl.Add(string.Empty);
arrOpTyp.Add(string.Empty);
arrConsTyp.Add(string.Empty);
// These are specific attributes only for specific types handled below
Param[8].Value = arrAPl.ToArray();
Param[8].ArrayBindStatus = arrBStatN.ToArray();
Param[9].Value = arrOpTyp.ToArray();
Param[9].ArrayBindStatus = arrBStatN.ToArray();
Param[10].Value = arrConsTyp.ToArray();
Param[10].ArrayBindStatus = arrBStatN.ToArray();
}
Tuple <int, int> ownHEntry = new Tuple <int, int>(Math.Abs(typ.OwnerHistory.EntityLabel), BIMRLProcessModel.currModelID);
if (_refBIMRLCommon.OwnerHistoryExist(ownHEntry))
{
arrOwnH.Add(Math.Abs(typ.OwnerHistory.EntityLabel));
arrOwnHPS.Add(OracleParameterStatus.Success);
}
else
{
arrOwnH.Add(0);
arrOwnHPS.Add(OracleParameterStatus.NullInsert);
}
Param[11].Value = arrOwnH.ToArray();
Param[11].ArrayBindStatus = arrOwnHPS.ToArray();
arrModelID.Add(BIMRLProcessModel.currModelID);
Param[12].Value = arrModelID.ToArray();
command.ArrayBindCount = 1;
try
{
int commandStatus = command.ExecuteNonQuery();
}
catch (OracleException e)
{
string excStr = "%%Insert Error - " + e.Message + "\n\t" + currStep;
_refBIMRLCommon.StackPushIgnorableError(excStr);
}
catch (SystemException e)
{
string excStr = "%%Insert Error - " + e.Message + "\n\t" + currStep;
_refBIMRLCommon.StackPushError(excStr);
throw;
}
BIMRLProperties tProps = new BIMRLProperties(_refBIMRLCommon);
tProps.processTypeProperties(typ);
}
}
private void btn_conciliar_Click(object sender, EventArgs e)
{
//CARGAR ARCHIVO
OpenFileDialog openFileDialog1 = new OpenFileDialog();
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
ora.Open();
comando = new OracleCommand();
comando.Connection = ora;
string selectedFileName = openFileDialog1.FileName;
try
{
//POR CADA REGISTRO HACER LO SIGUIENTE
var lines = File.ReadLines(selectedFileName);
foreach (var line in lines)
{
OracleTransaction trans = ora.BeginTransaction();
comando.Transaction = trans;
try
{
DateTime fecha = DateTime.Now;
//BANCO|REFERENCIA|CUENTA|NO_CHEQUE|MONTO|RESPONSE
String[] linea = line.Split('|');
if (linea[5].ToLower() == "ok")
{
//GRABAR TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION (FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_EXTERNO) " +
"VALUES(:fecha,'0','0',:valor,:empleado,:agencia,'3',:equipo,:cheque)";
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = linea[4];
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
// REGISTRAR CAMBIO EN CHEQUE_EXTRANGERO
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 8 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.ExecuteNonQuery();
trans.Commit();
}
else
{
//GRABAR TRANSACCION
comando.Parameters.Clear();
comando.CommandText = "INSERT INTO TRANSACCION (NO_RECHAZO, RAZON_RECHAZO, FECHA,SALDO_INICIAL, SALDO_FINAL, VALOR,EMPLEADO, AGENCIA, TIPO_TRANSACCION, EQUIPO,CHEQUE_EXTERNO) " +
"VALUES(:rechazo, :razon,:fecha,'0','0',:valor,:empleado,:agencia,'3',:equipo,:cheque)";
comando.Parameters.Add("rechazo", OracleType.Number).Value = 1;
comando.Parameters.Add("razon", OracleType.VarChar).Value = "NO TIENE FONDOS";
comando.Parameters.Add("fecha", OracleType.DateTime).Value = fecha;
comando.Parameters.Add("valor", OracleType.Number).Value = linea[4];
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.Parameters.Add("empleado", OracleType.Number).Value = Properties.Settings.Default.empleado;
comando.Parameters.Add("agencia", OracleType.Number).Value = Properties.Settings.Default.agencia;
comando.Parameters.Add("equipo", OracleType.Number).Value = 0;
comando.ExecuteNonQuery();
// REGISTRAR CAMBIO EN CHEQUE_EXTRANGERO
comando.Parameters.Clear();
comando.CommandText = "UPDATE cheque_externo SET estado_cheque = 9 WHERE codigo_cheque = :cheque";
comando.Parameters.Add("cheque", OracleType.Number).Value = linea[3];
comando.ExecuteNonQuery();
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
ora.Close();
System.Windows.Forms.MessageBox.Show("Se ha terminado de cargar el archivo " + selectedFileName);
}
}
}
protected void BtnUpdate_Click(object sender, EventArgs e)
{
try
{
if (IS_EDIT_ACTIVE == "Enable")
{
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
int userID = Convert.ToInt32(Session["USER_ID"]);
int SlipNo = Convert.ToInt32(TextSlipNo.Text);
int SupplierID = Convert.ToInt32(DropDownSupplierID.Text);
int SupervisorID = Convert.ToInt32(DropDownSupervisorID.Text);
int ItemID = Convert.ToInt32(DropDownItemID.Text);
int SubItemID = Convert.ToInt32(DropDownSubItemID.Text);
string ItemName = DropDownItemID.SelectedItem.Text;
string SubItemName = "";
if (SubItemID == 0)
{
SubItemID = 0; SubItemName = "";
}
else
{
SubItemID = Convert.ToInt32(DropDownSubItemID.Text); SubItemName = DropDownSubItemID.SelectedItem.Text;
}
string ISActive = CheckIsActive.Checked ? "Enable" : "Disable";
string MakeEntryDate = EntryDate.Text;
string[] MakeEntryDateSplit = MakeEntryDate.Split('-');
String EntryDateTemp = MakeEntryDateSplit[0].Replace("/", "-");
DateTime EntryDateNewD = DateTime.ParseExact(EntryDateTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture);
string EntryDateNew = EntryDateNewD.ToString("dd-MM-yyyy");
double ItemWeight = Convert.ToDouble(TextItemWeight.Text.Trim());
string u_date = System.DateTime.Now.ToString("dd-MM-yyyy h:mm:ss tt");
// purchase master update
string update_user = "******";
cmdi = new OracleCommand(update_user, conn);
OracleParameter[] objPrm = new OracleParameter[11];
objPrm[1] = cmdi.Parameters.Add("NoSupplierID", SupplierID);
objPrm[2] = cmdi.Parameters.Add("NoItemID", ItemID);
objPrm[3] = cmdi.Parameters.Add("NoSubItemID", SubItemID);
objPrm[4] = cmdi.Parameters.Add("NoSupervisorID", SupervisorID);
objPrm[5] = cmdi.Parameters.Add("NoItemWeight", ItemWeight);
objPrm[6] = cmdi.Parameters.Add("EntryDate", EntryDateNew);
objPrm[7] = cmdi.Parameters.Add("u_date", u_date);
objPrm[8] = cmdi.Parameters.Add("NoCuserID", userID);
objPrm[9] = cmdi.Parameters.Add("TextIsActive", ISActive);
objPrm[10] = cmdi.Parameters.Add("NoSlipNo", SlipNo);
cmdi.ExecuteNonQuery();
cmdi.Parameters.Clear();
cmdi.Dispose();
conn.Close();
alert_box.Visible = true;
alert_box.Controls.Add(new LiteralControl("Job Work (Purchase) Data Update successfully"));
alert_box.Attributes.Add("class", "alert alert-success alert-dismissible");
clearText();
Display();
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
}
catch
{
Response.Redirect("~/ParameterError.aspx");
}
}
/// <summary>
/// Setup the necessary Tables & Test Data
/// </summary>
/// <param name="connectStr"></param>
public static void Setup(OracleConnection con)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", con);
// Create multimedia table
blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Warning: {0}", e.Message);
}
blr = new StringBuilder();
blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
blr.Append("story CLOB, sound BLOB)");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
blr = new StringBuilder();
blr.Append("INSERT INTO multimedia_tab values(");
blr.Append("1,");
blr.Append("'This is a long story. Once upon a time ...',");
blr.Append("'656667686970717273747576777879808182838485')");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
// Create Package Header
blr = new StringBuilder();
blr.Append("CREATE OR REPLACE PACKAGE TEST is ");
blr.Append("TYPE refcursor is ref cursor;");
blr.Append("FUNCTION Ret1Cur return refCursor;");
blr.Append("PROCEDURE Get1CurOut(p_cursor1 out refCursor);");
blr.Append("FUNCTION Get3Cur (p_cursor1 out refCursor,");
blr.Append("p_cursor2 out refCursor)");
blr.Append("return refCursor;");
blr.Append("FUNCTION Get1Cur return refCursor;");
blr.Append("PROCEDURE UpdateRefCur(new_story in VARCHAR,");
blr.Append("clipid in NUMBER);");
blr.Append("PROCEDURE GetStoryForClip1(p_cursor out refCursor);");
blr.Append("PROCEDURE GetRefCurData (p_cursor out refCursor,myStory out VARCHAR2);");
blr.Append("end TEST;");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
// Create Package Body
blr = new StringBuilder();
blr.Append("create or replace package body TEST is ");
blr.Append("FUNCTION Ret1Cur return refCursor is ");
blr.Append("p_cursor refCursor; ");
blr.Append("BEGIN ");
blr.Append("open p_cursor for select * from multimedia_tab; ");
blr.Append("return (p_cursor); ");
blr.Append("END Ret1Cur; ");
blr.Append("PROCEDURE Get1CurOut(p_cursor1 out refCursor) is ");
blr.Append("BEGIN ");
blr.Append("OPEN p_cursor1 for select * from emp; ");
blr.Append("END Get1CurOut; ");
blr.Append("FUNCTION Get3Cur (p_cursor1 out refCursor, ");
blr.Append("p_cursor2 out refCursor)");
blr.Append("return refCursor is ");
blr.Append("p_cursor refCursor; ");
blr.Append("BEGIN ");
blr.Append("open p_cursor for select * from multimedia_tab; ");
blr.Append("open p_cursor1 for select * from emp; ");
blr.Append("open p_cursor2 for select * from dept; ");
blr.Append("return (p_cursor); ");
blr.Append("END Get3Cur; ");
blr.Append("FUNCTION Get1Cur return refCursor is ");
blr.Append("p_cursor refCursor; ");
blr.Append("BEGIN ");
blr.Append("open p_cursor for select * from multimedia_tab; ");
blr.Append("return (p_cursor); ");
blr.Append("END Get1Cur; ");
blr.Append("PROCEDURE UpdateRefCur(new_story in VARCHAR, ");
blr.Append("clipid in NUMBER) is ");
blr.Append("BEGIN ");
blr.Append("Update multimedia_tab set story = new_story where thekey = clipid; ");
blr.Append("END UpdateRefCur; ");
blr.Append("PROCEDURE GetStoryForClip1(p_cursor out refCursor) is ");
blr.Append("BEGIN ");
blr.Append("open p_cursor for ");
blr.Append("Select story from multimedia_tab where thekey = 1; ");
blr.Append("END GetStoryForClip1; ");
blr.Append("PROCEDURE GetRefCurData (p_cursor out refCursor,");
blr.Append("myStory out VARCHAR2) is ");
blr.Append("BEGIN ");
blr.Append("FETCH p_cursor into myStory; ");
blr.Append("END GetRefCurData; ");
blr.Append("end TEST;");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
}
void overwrite()
{
if (dataGridView.Rows.Count <= 1)
{
MessageBox.Show("Mohon tambahkan setidaknya 1 item");
return;
}
int creditTerm = 1;
if (cbCreditTerm.Text != "Cash" && cbCreditTerm.Text != "COD")
{
creditTerm = Convert.ToInt32(cbCreditTerm.Text.Substring(0, 2));
}
String id_customer = cbIdCustomer.SelectedValue + "";
String id_gudang = cbGudang.SelectedValue + "";
String id_invoice = txtIdInvoice.Text;
String id_staff = cbStaff.SelectedValue + "";
DateTime tanggalInvoice = dateInvoice.Value;
String shipVia = cbShipVia.SelectedValue + "";
String negara = cbNegara.SelectedValue + "";
String currency = cbCurrency.SelectedValue + "";
int rate = Convert.ToInt32(txtRate.Text.Substring(4));
OracleCommand cmd = new OracleCommand("update h_invoice " +
"set " +
"total = :total, " +
"total_ppn = :totalPPN, " +
"total_harga = :netTotal, " +
"total_harga_convert = :convert " +
"where id_invoice = '" + id_invoice + "'", conn);
cmd.Parameters.Add(":total", total);
cmd.Parameters.Add(":totalPPN", totalPPN);
cmd.Parameters.Add(":netTotal", netTotal);
cmd.Parameters.Add(":convert", totalConvert);
cmd.ExecuteNonQuery();
//Delete last dokumen
cmd = new OracleCommand("delete from d_invoice where id_invoice = '" + id_invoice + "'", conn);
cmd.ExecuteNonQuery();
for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
{
String id_item = dataGridView.Rows[i].Cells[0].Value.ToString();
String id_SO = dataGridView.Rows[i].Cells[1].Value.ToString();
String nama_item = dataGridView.Rows[i].Cells[2].Value.ToString();
String satuan_item = dataGridView.Rows[i].Cells[4].Value.ToString();
String jenis_ppn = dataGridView.Rows[i].Cells[7].Value.ToString();
String discount = dataGridView.Rows[i].Cells[8].Value.ToString();
String qty_item = qtyList[i].ToString();
String hJual_item = hJualList[i].ToString();
String berat_item = beratList[i].ToString();
String subtotal = subtotalList[i].ToString();
new OracleCommand("update h_sales_order set id_invoice = '" + id_invoice + "' where id_sales_order = '" + id_SO + "'", conn).ExecuteNonQuery();
OracleCommand cmdDetail = new OracleCommand("insert into d_invoice values (:id, :so, :do, :nama, :qty, :jenis, :harga, :berat, :ppn, :discount, :subtotal)", conn);
cmdDetail.Parameters.Add(":id", id_item);
cmdDetail.Parameters.Add(":so", id_SO);
cmdDetail.Parameters.Add(":do", id_invoice);
cmdDetail.Parameters.Add(":nama", nama_item);
cmdDetail.Parameters.Add(":qty", qty_item);
cmdDetail.Parameters.Add(":jenis", satuan_item);
cmdDetail.Parameters.Add(":harga", hJual_item);
cmdDetail.Parameters.Add(":berat", berat_item);
cmdDetail.Parameters.Add(":ppn", jenis_ppn);
cmdDetail.Parameters.Add(":discount", discount);
cmdDetail.Parameters.Add(":subtotal", subtotal);
cmdDetail.ExecuteNonQuery();
}
saved = true;
MessageBox.Show("Save berhasil");
}
private void bunifuButton1_Click(object sender, EventArgs e)
{
OracleCommand cmd;
OracleDataReader dr;
if (Validation.validateUserName(bunifuTextBox2.Text))
{
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select email from Username where email=:e";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("e", bunifuTextBox2.Text);
dr = cmd.ExecuteReader();
if (dr.Read())
{
label4.Text = "This Mail is taken before.";
return;
}
dr.Close();
}
if (!Validation.validateFirstName(bunifuTextBox1.Text))
{
label4.Text = "Enter at least 5 chars for First name.";
return;
}
if (!Validation.validateLastName(bunifuTextBox3.Text))
{
label4.Text = "Enter at least 5 chars for Last name.";
return;
}
if (!Validation.validatePassword(bunifuTextBox5.Text))
{
label4.Text = "Enter at least 5 chars for Password.";
return;
}
if (!Validation.validatePasswordconfirm(bunifuTextBox5.Text, bunifuTextBox4.Text))
{
label4.Text = "Confirm password should be the same as Password.";
return;
}
if (!Validation.validatePhone(bunifuTextBox6.Text))
{
label4.Text = "This Phone is Wrong.";
}
string gender = "m";
if (bunifuRadioButton2.Checked)
{
gender = "f";
}
int age = DateTime.Now.Year - bunifuDatePicker1.Value.Year;
DateTime d = bunifuDatePicker1.Value;
string urole = "u";
Random random = new Random();
int cash = random.Next(1000, 10000);
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "CREATEMUSER";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("e", bunifuTextBox2.Text);
cmd.Parameters.Add("f", bunifuTextBox1.Text);
cmd.Parameters.Add("l", bunifuTextBox3.Text);
cmd.Parameters.Add("p1", bunifuTextBox5.Text);
cmd.Parameters.Add("birth", d);
cmd.Parameters.Add("g", gender);
cmd.Parameters.Add("ph", bunifuTextBox6.Text);
cmd.Parameters.Add("r", urole);
cmd.Parameters.Add("c", cash);
cmd.ExecuteNonQuery();
MyUser.email = bunifuTextBox2.Text;
MyUser.firstname = bunifuTextBox1.Text;
MyUser.lastname = bunifuTextBox3.Text;
MyUser.password = bunifuTextBox5.Text;
MyUser.birthdate = d.ToString();
MyUser.gender = gender;
MyUser.age = age.ToString();
MyUser.role = urole;
MyUser.cash = cash;
Form1.incta.Close();
}
public static List <cADM_USUARIOS> ADM_UsuarioLogin(string usuario, string pasword, string pcreg)
{
List <cADM_USUARIOS> obj = new List <cADM_USUARIOS>();
string Token = EncryptDecryptPhrase.Encrypt(usuario + ',' + pasword + ',' + DateTime.Now.ToShortDateString(), key, iv);
OracleCommand comando = new OracleCommand()
{
CommandText = "SP_ADM_USUARIOSLOGIN", CommandType = CommandType.StoredProcedure, Connection = new OracleConnection(ConexionBD.GetConnectionBD())
};
comando.Parameters.Add(new OracleParameter("PUSUARIO", OracleDbType.NVarchar2)).Value = usuario;
comando.Parameters.Add(new OracleParameter("PCONTRASENA", OracleDbType.NVarchar2)).Value = pasword;
comando.Parameters.Add(new OracleParameter("PTOKEN", OracleDbType.NVarchar2)).Value = Token;
comando.Parameters.Add(new OracleParameter("PPCREG", OracleDbType.NVarchar2)).Value = pcreg;
comando.Parameters.Add(new OracleParameter("OIDUSUARIO", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("ONOMBRE", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("ONIDENTIFICACION", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OCODAREA", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OCODOFICINA", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OCORREO", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OPASSCORREO", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OUSUARIO", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OESTADO", OracleDbType.Int32)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OUSERREG", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OFECHAREG", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OPCREG", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OUSERMODIFY", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OFECHAMODIFY", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OPCMODIFY", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OOBSERVACION", OracleDbType.NVarchar2, 200)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OAREA", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Parameters.Add(new OracleParameter("OOFICINA", OracleDbType.NVarchar2, 100)).Direction = ParameterDirection.Output;
comando.Connection.Open();
comando.ExecuteNonQuery();
comando.Connection.Close();
if (comando.Parameters["OIDUSUARIO"].Value.ToString() != "null")
{
DateTime?FECHAREG = null;
if (comando.Parameters["OFECHAREG"].Value.ToString() != "null")
{
FECHAREG = Convert.ToDateTime(comando.Parameters["OFECHAREG"].Value.ToString());
}
DateTime?FECHAMODIFY = null;
if (comando.Parameters["OFECHAMODIFY"].Value.ToString() != "null")
{
FECHAMODIFY = Convert.ToDateTime(comando.Parameters["OFECHAMODIFY"].Value.ToString());
}
obj.Add(new cADM_USUARIOS(
Convert.ToString(comando.Parameters["OIDUSUARIO"].Value),
Convert.ToString(comando.Parameters["ONOMBRE"].Value),
Convert.ToString(comando.Parameters["ONIDENTIFICACION"].Value),
Convert.ToString(comando.Parameters["OCODAREA"].Value),
Convert.ToString(comando.Parameters["OCODOFICINA"].Value),
Convert.ToString(comando.Parameters["OCORREO"].Value),
Convert.ToString(comando.Parameters["OPASSCORREO"].Value),
Convert.ToString(comando.Parameters["OUSUARIO"].Value),
Convert.ToInt32(comando.Parameters["OESTADO"].Value.ToString()),
Convert.ToString(comando.Parameters["OUSERREG"].Value),
FECHAREG,
Convert.ToString(comando.Parameters["OPCREG"].Value),
Convert.ToString(comando.Parameters["OUSERMODIFY"].Value),
FECHAMODIFY,
Convert.ToString(comando.Parameters["OPCMODIFY"].Value),
Convert.ToString(comando.Parameters["OOBSERVACION"].Value),
Convert.ToString(comando.Parameters["OAREA"].Value),
Convert.ToString(comando.Parameters["OOFICINA"].Value)
));
}
return(obj);
}
public void BtnAdd_Click(object sender, EventArgs e)
{
try
{
if (IS_ADD_ACTIVE == "Enable")
{
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
int userID = Convert.ToInt32(Session["USER_ID"]);
int SupplierID = Convert.ToInt32(DropDownSupplierID.Text);
int SlipNo = Convert.ToInt32(TextSlipNo.Text);
int SupervisorID = Convert.ToInt32(DropDownSupervisorID.Text);
int ItemID = Convert.ToInt32(DropDownItemID.Text);
int SubItemID = Convert.ToInt32(DropDownSubItemID.Text);
string ItemName = DropDownItemID.SelectedItem.Text;
string SubItemName = "";
if (SubItemID == 0)
{
SubItemID = 0;
SubItemName = "";
}
else
{
SubItemID = Convert.ToInt32(DropDownSubItemID.Text);
SubItemName = DropDownSubItemID.SelectedItem.Text;
}
string ISActive = CheckIsActive.Checked ? "Enable" : "Disable";
string MakeEntryDate = EntryDate.Text;
string[] MakeEntryDateSplit = MakeEntryDate.Split('-');
String EntryDateTemp = MakeEntryDateSplit[0].Replace("/", "-");
DateTime EntryDateNewD = DateTime.ParseExact(EntryDateTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture);
string EntryDateNew = EntryDateNewD.ToString("dd-MM-yyyy");
string c_date = System.DateTime.Now.ToString("dd-MM-yyyy h:mm:ss tt");
string get_user_purchase_id = "select PF_PURCHASE_JWID_SEQ.nextval from dual";
cmdsp = new OracleCommand(get_user_purchase_id, conn);
int newPurchaseID = Int16.Parse(cmdsp.ExecuteScalar().ToString());
double ItemWeight = Convert.ToDouble(TextItemWeight.Text.Trim());
string insert_purchase = "insert into PF_PURCHASE_JW (PURCHASE_JW_ID, SLIP_NO, PARTY_ID, ITEM_ID, SUB_ITEM_ID, SUPERVISOR_ID, ITEM_WEIGHT, ENTRY_DATE, CREATE_DATE, C_USER_ID, IS_ACTIVE, DIVISION_ID) values ( :NoPurchaseID, :NoSlipID, :NoSupplierID, :NoItemID, :NoSubItemID, :NoSupervisorID, :TextItemWeight, TO_DATE(:EntryDate, 'DD/MM/YYYY'), TO_DATE(:c_date, 'DD-MM-YYYY HH:MI:SS AM'), :NoCuserID, :TextIsActive, 3)";
cmdi = new OracleCommand(insert_purchase, conn);
OracleParameter[] objPrm = new OracleParameter[11];
objPrm[0] = cmdi.Parameters.Add("NoPurchaseID", newPurchaseID);
objPrm[1] = cmdi.Parameters.Add("NoSlipID", SlipNo);
objPrm[2] = cmdi.Parameters.Add("NoSupplierID", SupplierID);
objPrm[3] = cmdi.Parameters.Add("NoItemID", ItemID);
objPrm[4] = cmdi.Parameters.Add("NoSubItemID", SubItemID);
objPrm[5] = cmdi.Parameters.Add("NoSupervisorID", SupervisorID);
objPrm[6] = cmdi.Parameters.Add("TextItemWeight", ItemWeight);
objPrm[7] = cmdi.Parameters.Add("EntryDate", EntryDateNew);
objPrm[8] = cmdi.Parameters.Add("c_date", c_date);
objPrm[9] = cmdi.Parameters.Add("NoCuserID", userID);
objPrm[10] = cmdi.Parameters.Add("TextIsActive", ISActive);
cmdi.ExecuteNonQuery();
cmdi.Parameters.Clear();
cmdi.Dispose();
conn.Close();
alert_box.Visible = true;
alert_box.Controls.Add(new LiteralControl("Insert new Job Work (Purchase) Successfully"));
alert_box.Attributes.Add("class", "alert alert-success alert-dismissible");
clearText();
Display();
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
}
catch
{
Response.Redirect("~/ParameterError.aspx");
}
}
/// <summary>
/// Used in workflow
/// </summary>
//public enum Calendar
//{
// Minute = 12,
// Second = 13,
// Hour = 10,
// DayOfMonth = 5,
// DayOfYear = 6,
// Month = 2,
// Year = 1
//};
//static Context ctx = Utility.Env.GetContext();
/// <summary>
/// Get primary key ID of table
/// </summary>
/// <param name="tableName">Table Name</param>
/// <returns>int primary key</returns>
/// <author>Veena</author>
///
#pragma warning disable 612, 618
public static int GetPrimaryKeyId(string tableName)
{
try
{
int nextSeqID = 0;
int clientID = Utility.Env.GetContext().GetAD_Client_ID();
//int iOrgid = 1;
//string strQuery = "SELECT CurrentNext FROM AD_Sequence WHERE Name='" + tableName + "' AND IsActive = 'Y' AND IsTableID = 'Y'";
//int iOrgid = Utility.Util.GetValueOfInt(ExecuteQuery.ExecuteScalar(strQuery).ToString());
//string strUpdate = "UPDATE AD_Sequence SET CurrentNext=CurrentNext+IncrementNo, updated=getdate() WHERE Name='" + tableName + "'";
//ExecuteQuery.ExecuteNonQuery(strUpdate);
//string strUpdate = "UPDATE AD_Sequence SET CurrentNext=(Select " + strColumn + " from " + tableName+"), updated=getdate() WHERE Name=" + tableName + "";
//ExecuteQuery.ExecuteNonQuery(strUpdate);
// get table's sequence id
string sqlSeqQry = "SELECT AD_Sequence_ID FROM ad_sequence WHERE UPPER(NAME)=UPPER('" + tableName + "')";
string tableSeqID = ExecuteQuery.ExecuteScalar(sqlSeqQry);
char isSystem = 'N';
if (clientID < 12)
{
//isSystem = 'Y';
}
string dbConn = DB.GetConnectionString();
if (DatabaseType.IsOracle)
{
OracleConnection conn = new OracleConnection(dbConn);
//Initialize OracleCommand object for insert.
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "NEXTID";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("p_AD_Sequence_ID", OracleType.Number).Value = Utility.Util.GetValueOfInt(tableSeqID);
cmd.Parameters.Add("p_System", OracleType.Char).Value = isSystem;
cmd.Parameters.Add("o_NextID", OracleType.Number).Direction = System.Data.ParameterDirection.Output;
//Open connection and execute insert query.
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
nextSeqID = Utility.Util.GetValueOfInt(cmd.Parameters["o_NextID"].Value.ToString());
}
else if (DatabaseType.IsPostgre)
{
//dbConn = "Server = localhost; Port = 5432; User Id = postgres; Password = system; Database = postgres";
string procedureName = "nextid";
if (GlobalVariable.GetDBName != "")
{
procedureName = GlobalVariable.GetDBName + "." + procedureName;
}
string sqlUpdate = "select " + procedureName + "(" + Utility.Util.GetValueOfInt(tableSeqID) + ",'" + isSystem + "')";
nextSeqID = Utility.Util.GetValueOfInt(ExecuteQuery.ExecuteScalar(sqlUpdate).ToString());
//NpgsqlConnection conn = new NpgsqlConnection(dbConn);
////Initialize SqlCommand object for insert.
//NpgsqlCommand cmd = new NpgsqlCommand();
//cmd.Connection = conn;
//cmd.CommandText = "NEXTID";
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.Parameters.Add("p_AD_Sequence_ID", NpgsqlTypes.NpgsqlDbType.Integer).Value = Utility.Util.GetValueOfInt(tableSeqID);// 721
//cmd.Parameters.Add("p_System", NpgsqlTypes.NpgsqlDbType.Char).Value = isSystem;
//cmd.Parameters.Add("o_NextID", NpgsqlTypes.NpgsqlDbType.Integer).Direction = System.Data.ParameterDirection.Output;
////Open connection and execute insert query.
//conn.Open();
//cmd.ExecuteNonQuery();
//conn.Close();
//nextSeqID = Utility.Util.GetValueOfInt(cmd.Parameters["o_NextID"].Value.ToString());
}
else if (DatabaseType.IsMSSql)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@p_TableName", tableName);
//param[1] = new SqlParameter("@p_ID", Utility.Util.GetValueOfInt(Utility.Env.GetContext().GetAD_Client_ID()));
param[1] = new SqlParameter("@p_ID", 1000002);
string seqID = ExecuteQuery.ExecuteScalar("AD_Sequence_Next1", param, true);
if (seqID != null)
{
nextSeqID = Utility.Util.GetValueOfInt(seqID);
}
}
else if (DatabaseType.IsMySql)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@p_TableName", tableName);
//param[1] = new SqlParameter("@p_ID", Utility.Util.GetValueOfInt(Utility.Env.GetContext().GetAD_Client_ID()));
param[1] = new SqlParameter("@p_ID", 1000002);
string seqID = ExecuteQuery.ExecuteScalar("AD_Sequence_Next1", param, true);
if (seqID != null)
{
nextSeqID = Utility.Util.GetValueOfInt(seqID);
}
}
return(nextSeqID);
}
catch
{
return(0);
}
}
void save()
{
if (dataGridView.Rows.Count <= 1)
{
MessageBox.Show("Mohon tambahkan setidaknya 1 item");
return;
}
int creditTerm = 1;
if (cbCreditTerm.Text != "Cash" && cbCreditTerm.Text != "COD")
{
creditTerm = Convert.ToInt32(cbCreditTerm.Text.Substring(0, 2));
}
String id_customer = cbIdCustomer.SelectedValue + "";
String id_gudang = cbGudang.SelectedValue + "";
String id_invoice = txtIdInvoice.Text;
String id_staff = cbStaff.SelectedValue + "";
DateTime tanggalInvoice = dateInvoice.Value;
String shipVia = cbShipVia.SelectedValue + "";
String negara = cbNegara.SelectedValue + "";
String currency = cbCurrency.SelectedValue + "";
int rate = Convert.ToInt32(txtRate.Text.Substring(4));
OracleCommand cmd = new OracleCommand("insert into h_invoice values (:id, :gudang, :customer, :staff, :nama, :alamat, :tgl, :credit, :ship,:negara ,:currency, :rate, :total, :totalPPN, :netTotal, :convert)", conn);
cmd.Parameters.Add(":id", id_invoice);
cmd.Parameters.Add(":gudang", id_gudang);
cmd.Parameters.Add(":customer", id_customer);
cmd.Parameters.Add(":staff", id_staff);
cmd.Parameters.Add(":nama", txtNamaCustomer.Text);
cmd.Parameters.Add(":alamat", txtAlamatCustomer.Text);
cmd.Parameters.Add(":tgl", tanggalInvoice);
cmd.Parameters.Add(":credit", creditTerm);
cmd.Parameters.Add(":ship", shipVia);
cmd.Parameters.Add(":negara", negara);
cmd.Parameters.Add(":currency", currency);
cmd.Parameters.Add(":rate", rate);
cmd.Parameters.Add(":total", total);
cmd.Parameters.Add(":totalPPN", totalPPN);
cmd.Parameters.Add(":netTotal", netTotal);
cmd.Parameters.Add(":convert", totalConvert);
cmd.ExecuteNonQuery();
new OracleCommand("update h_delivery_order set status_do = 1 where id_delivery_order = '" + this.id_do + "'", conn).ExecuteNonQuery();
for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
{
String id_item = dataGridView.Rows[i].Cells[0].Value.ToString();
String id_SO = dataGridView.Rows[i].Cells[1].Value.ToString();
String nama_item = dataGridView.Rows[i].Cells[2].Value.ToString();
String satuan_item = dataGridView.Rows[i].Cells[4].Value.ToString();
String jenis_ppn = dataGridView.Rows[i].Cells[7].Value.ToString();
String discount = dataGridView.Rows[i].Cells[8].Value.ToString();
String qty_item = qtyList[i].ToString();
String hJual_item = hJualList[i].ToString();
String berat_item = beratList[i].ToString();
String subtotal = subtotalList[i].ToString();
OracleCommand cmdDetail = new OracleCommand("insert into d_invoice values (:id, :so, :do, :nama, :qty, :jenis, :harga, :berat, :ppn, :discount, :subtotal)", conn);
cmdDetail.Parameters.Add(":id", id_item);
cmdDetail.Parameters.Add(":so", id_SO);
cmdDetail.Parameters.Add(":do", id_invoice);
cmdDetail.Parameters.Add(":nama", nama_item);
cmdDetail.Parameters.Add(":qty", qty_item);
cmdDetail.Parameters.Add(":jenis", satuan_item);
cmdDetail.Parameters.Add(":harga", hJual_item);
cmdDetail.Parameters.Add(":berat", berat_item);
cmdDetail.Parameters.Add(":ppn", jenis_ppn);
cmdDetail.Parameters.Add(":discount", discount);
cmdDetail.Parameters.Add(":subtotal", subtotal);
cmdDetail.ExecuteNonQuery();
}
String cmdString = "select distinct id_sales_order from d_invoice where id_invoice = '" + id_invoice + "'";
OracleDataReader reader = new OracleCommand(cmdString, conn).ExecuteReader();
while (reader.Read())
{
new OracleCommand("update h_sales_order set id_invoice = '" + id_invoice + "' where id_sales_order = '" + reader.GetValue(0) + "'", conn).ExecuteNonQuery();
}
saved = true;
MessageBox.Show("Save berhasil");
}
/// <summary>
/// 执行查询--带参数查询
/// </summary>
/// <param name="strSql">带有参数的sql语句</param>
/// <param name="paramList">key为上strSql语句中的参数名称，value为该参数值</param>
/// <returns></returns>
public bool ExecuteNonQuery(string strSql, System.Collections.Hashtable paramList)
{
try
{
if (string.IsNullOrEmpty(m_strConn))
{
GetDBConnection();
}
int iResult = -1;
if (m_CurrentDataBaseType == DataBaseType.SqlServer)
{
using (SqlConnection conn = new SqlConnection(m_strConn))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(strSql, conn);
cmd.CommandTimeout = 180;
if (paramList != null && paramList.Keys.Count > 0)
{
foreach (string pn in paramList.Keys)
{
cmd.Parameters.Add(new SqlParameter(pn, paramList[pn]));
}
}
iResult = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
}
else if (m_CurrentDataBaseType == DataBaseType.Oracle)
{
using (OracleConnection conn = new OracleConnection(m_strConn))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.CommandTimeout = 180;
if (paramList != null && paramList.Keys.Count > 0)
{
foreach (string pn in paramList.Keys)
{
cmd.Parameters.Add(new OracleParameter(pn, paramList[pn]));
}
}
iResult = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
}
else if (m_CurrentDataBaseType == DataBaseType.Mysql)
{
using (MySqlConnection conn = new MySqlConnection(m_strConn))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(strSql, conn);
cmd.CommandTimeout = 180;
if (paramList != null && paramList.Keys.Count > 0)
{
foreach (string pn in paramList.Keys)
{
cmd.Parameters.Add(new MySqlParameter(pn, paramList[pn]));
}
}
iResult = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
}
return(iResult > 0);
}
catch (Exception err)
{
throw err;
}
}
/// <summary>
/// 거래종목 삽입
/// </summary>
private void button2_Click(object sender, EventArgs e)
{
OracleCommand cmd;
OracleConnection conn;
string sql;
string l_jongmok_cd;
string l_jongmok_nm;
int l_priority;
int l_buy_amt;
int l_buy_price;
int l_target_price;
int l_cut_loss_price;
string l_buy_trd_yn;
string l_sell_trd_yn;
foreach (DataGridViewRow Row in dataGridView1.Rows)
{
if (Convert.ToBoolean(Row.Cells[check.Name].Value) != true)
{
continue;
}
if (Convert.ToBoolean(Row.Cells[check.Name].Value) == true)
{
l_jongmok_cd = Row.Cells[1].Value.ToString();
l_jongmok_nm = Row.Cells[2].Value.ToString();
l_priority = int.Parse(Row.Cells[3].Value.ToString());
l_buy_amt = int.Parse(Row.Cells[4].Value.ToString());
l_buy_price = int.Parse(Row.Cells[5].Value.ToString());
l_target_price = int.Parse(Row.Cells[6].Value.ToString());
l_cut_loss_price = int.Parse(Row.Cells[7].Value.ToString());
l_buy_trd_yn = Row.Cells[8].Value.ToString();
l_sell_trd_yn = Row.Cells[9].Value.ToString();
conn = null;
conn = connect_db();
cmd = null;
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
sql = null;
sql = $"INSERT INTO TB_TRD_JONGMOK VALUES('{g_user_id}', '{l_jongmok_cd}', '{l_jongmok_nm}',";
sql += $"{l_priority}, {l_buy_amt}, {l_buy_price}, {l_target_price}, {l_cut_loss_price},";
sql += $"'{l_buy_trd_yn}', '{l_sell_trd_yn}', '{g_user_id}', sysdate, null, null)";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
write_err_log($"INSERT TB_TRD_JONGMOK ex.Message : [{ex.Message}]\n", 0);
}
write_msg_log($"종목코드 : [{l_jongmok_cd}]가 삽입되었습니다\n", 0);
conn.Close();
}
}
}
public static void Main(string[] args)
{
string connectionString =
"Data Source=testdb;" +
"User ID=scott;" +
"Password=tiger;";
OracleConnection connection = null;
connection = new OracleConnection(connectionString);
connection.Open();
Console.WriteLine("Setup test package and data...");
OracleCommand cmddrop = connection.CreateCommand();
cmddrop.CommandText = "DROP TABLE TESTTABLE";
try
{
cmddrop.ExecuteNonQuery();
}
catch (OracleException e)
{
Console.WriteLine("Ignore this error: " + e.Message);
}
cmddrop.Dispose();
cmddrop = null;
Console.WriteLine("Create table TESTTABLE...");
OracleCommand cmd = connection.CreateCommand();
// create table TESTTABLE
cmd.CommandText =
"create table TESTTABLE (\n" +
" col1 numeric(18,0),\n" +
" col2 varchar(32),\n" +
" col3 date, col4 blob)";
cmd.ExecuteNonQuery();
Console.WriteLine("Insert 3 rows...");
// insert some rows into TESTTABLE
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(45, 'Mono', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(136, 'Fun', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
Console.WriteLine("commit...");
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Update blob...");
// update BLOB and CLOB columns
OracleCommand select = connection.CreateCommand();
select.Transaction = connection.BeginTransaction();
select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE";
OracleDataReader readerz = select.ExecuteReader();
if (!readerz.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
// update blob_value
Console.WriteLine(" Update BLOB column on table testtable...");
OracleLob blob = readerz.GetOracleLob(1);
byte[] bytes = new byte[6] {
0x31, 0x32, 0x33, 0x34, 0x35, 0x036
};
blob.Write(bytes, 0, bytes.Length);
blob.Close();
readerz.Close();
select.Transaction.Commit();
select.Dispose();
select = null;
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Create package...");
// create Oracle package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
"AS\n" +
" TYPE T_CURSOR IS REF CURSOR;\n"+
"\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR);\n"+
"END TestTablePkg;";
cmd.ExecuteNonQuery();
// create Oracle package body for package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
" IS\n" +
" BEGIN\n" +
" OPEN tableCursor FOR\n" +
" SELECT *\n" +
" FROM TestTable;\n" +
" END GetData;\n" +
"END TestTablePkg;";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
Console.WriteLine("Set up command and parameters to call stored proc...");
OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
Console.WriteLine("Execute...");
command.ExecuteNonQuery();
Console.WriteLine("Get OracleDataReader for cursor output parameter...");
OracleDataReader reader = (OracleDataReader)parameter.Value;
Console.WriteLine("Read data***...");
int r = 0;
while (reader.Read())
{
Console.WriteLine("Row {0}", r);
for (int f = 0; f < reader.FieldCount; f++)
{
Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString());
object val = "";
if (f == 3)
{
Console.WriteLine("blob");
//OracleLob lob = reader.GetOracleLob (f);
//val = lob.Value;
val = reader.GetValue(f);
if (((byte[])val).Length == 0)
{
val = "Empty Blob (Not Null)";
}
else
{
val = BitConverter.ToString((byte[])val);
}
}
else
{
val = reader.GetOracleValue(f);
}
Console.WriteLine(" Field {0} Value: {1}", f, val);
}
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Clean up...");
reader.Close();
reader = null;
command.Dispose();
command = null;
connection.Close();
connection = null;
}
private void btnhesapla_Click(object sender, EventArgs e)
{
int vize, final;
int a, b, ort;
vize = Convert.ToInt32(txtvize.Text);
final = Convert.ToInt32(txtfinal.Text);
a = (vize * 40) / 100;
b = (final * 60) / 100;
ort = a + b;
txtortalama.Text = ort.ToString();
if (ort > 84)
{
txtharfnotu.Text = "AA";
}
else if (ort > 74)
{
txtharfnotu.Text = "BA";
}
else if (ort > 69)
{
txtharfnotu.Text = "BB";
}
else if (ort > 65)
{
txtharfnotu.Text = "CB";
}
else if (ort > 59)
{
txtharfnotu.Text = "CC";
}
else if (ort > 54)
{
txtharfnotu.Text = "DC";
}
else if (ort > 49)
{
txtharfnotu.Text = "DD";
}
else
{
txtharfnotu.Text = "FF";
}
OracleConnection conn = new OracleConnection("DATA SOURCE=DESKTOP-EHLEVPQ:1521/XE;PERSIST SECURITY INFO=True;USER ID=SEDA; PASSWORD=123456");
OracleCommand cmd = new OracleCommand("select IDOGRENCI from TBLOGRENCI WHERE IDOGRENCI='" + cmbogrencino.Text + "'", conn);
DataTable TBLBLM = new DataTable();
try
{
conn.Open();
// dataGridView1.AutoGenerateColumns = false;
TBLBLM.Load(cmd.ExecuteReader());
if (TBLBLM.Rows.Count > 0)
{
cmd.CommandText = "SELECT DERSID FROM TBLDERS WHERE DERSADI='" + cmbdersadi.Text + "' ";
DataTable TBLDRS = new DataTable();
TBLDRS.Load(cmd.ExecuteReader());
cmd.CommandText = "insert into TBLOGRENCINOT(ID,DERSID,VIZE,FINAL,ORTALAMA,HARF,OGRENCI_ID) values( seq_id.NEXTVAL," + Convert.ToInt32(TBLDRS.Rows[0][0]) + "," + Convert.ToInt32(txtvize.Text) + "," + Convert.ToInt32(txtfinal.Text) + "," + Convert.ToInt32(txtortalama.Text) + ",'" + txtharfnotu.Text + "'," + Convert.ToInt32(TBLBLM.Rows[0][0]) + ")";
cmd.ExecuteNonQuery();
veriGetir();
}
conn.Close();
// txtharfnotu.Clear();
txtvize.Clear();
txtfinal.Clear();
// txtortalama.Clear();
//txtsoyad.Clear();
//txtresimyolu.Clear();
//txttc.Focus();
//maskedTextBox1.Clear();
MessageBox.Show("Kayit Başarıyla eklendi!!!!", "Mesaj", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// UPDATE BY ECARD ID
protected void update_by_id_Click(object sender, EventArgs e)
{
// Setting bind variables from textboxes
sender_fname = s_fname_input.Text;
sender_lname = s_lname_input.Text;
sender_email = s_email_input.Text;
patient_fname = p_fname_input.Text;
patient_lname = p_lname_input.Text;
card_choice = card_choice_input.SelectedValue;
card_message = message_input.Text;
ecard_id = ecardId_input.Text;
Regex idregex = new Regex(@"^[0-9]+$");
Match idmatch = idregex.Match(ecard_id);
// Validate Inputs
if (sender_fname == "" || sender_fname == "" || sender_email == "" || patient_fname == "" || patient_lname == "" || card_message == "" || !idmatch.Success)
{
server_output.InnerHtml = "Please fill in all areas with valid inputs.";
}
else
{
server_output.InnerHtml = "";
id_err.Text = "";
// Validate Inputs
sender_email = s_email_input.Text;
Regex regex = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$");
Match match = regex.Match(sender_email);
if (match.Success)
{
// Resetting Rows
rows = 0;
try
{
conn.Open(); // Open Connection
// Grabbing Patient ID from persons table
select_statment = "SELECT * FROM persons WHERE upper(person_first_name) = upper(:p_fname) AND upper(person_last_name) = upper(:p_lname)";
// Execute Oracle Command
OracleCommand cmd1 = new OracleCommand(select_statment, conn);
cmd1.Parameters.Add(new OracleParameter("p_fname", patient_fname));
cmd1.Parameters.Add(new OracleParameter("p_lname", patient_lname));
OracleDataReader reader = cmd1.ExecuteReader();
// Grabbing Patient ID for Update Statement
while (reader.Read())
{
person_id = Convert.ToInt32(reader["person_id"]);
rows += 1;
}
if (rows > 0)
{
rows = 0;
// Execute Update Statement
command = "UPDATE ecards " +
"SET sender_first_name = :s_fname, " +
"sender_last_name = :s_lname, " +
"sender_email = :s_email, " +
"person_id = :p_id, " +
"card_choice = :c_choice, " +
"card_message = :c_message " +
"WHERE ecard_id = :ecardID";
OracleCommand cmd = new OracleCommand(command, conn);
// Setting Bind Variables
cmd.Parameters.Add(new OracleParameter("s_fname", sender_fname));
cmd.Parameters.Add(new OracleParameter("s_lname", sender_lname));
cmd.Parameters.Add(new OracleParameter("s_email", sender_email));
cmd.Parameters.Add(new OracleParameter("p_id", person_id));
cmd.Parameters.Add(new OracleParameter("c_choice", card_choice));
cmd.Parameters.Add(new OracleParameter("c_message", card_message));
cmd.Parameters.Add(new OracleParameter("ecardID", Convert.ToInt32(ecard_id)));
// Checking number of rows affected
rows = cmd.ExecuteNonQuery();
}
else
{
server_output.InnerHtml = "There is no one at the hospital with this name.";
}
// Close Connection
conn.Close();
}
catch (OracleException ex)
{
server_output.InnerHtml = "</br>" + ex.Message;
}
finally
{
// Outputing results
server_output.InnerHtml = "</br>" + Convert.ToString(rows) + " rows updated.";
} // End of try catch
} // End of if else
} // End of if else
} // End of update click
/// <summary>
/// 同一事物中执行多条sql语句
/// </summary>
/// <param name="cmdText"></param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string[] cmdText)
{
try
{
if (string.IsNullOrEmpty(m_strConn))
{
GetDBConnection();
}
if (m_CurrentDataBaseType == DataBaseType.Oracle)
{
using (OracleConnection myConnection = new OracleConnection(m_strConn))
{
int val = 0;
if (myConnection.State != System.Data.ConnectionState.Open)
{
myConnection.Open();
}
OracleCommand myCommand = myConnection.CreateCommand();
myCommand.CommandTimeout = 180;
OracleTransaction myTrans;
myTrans = ((OracleConnection)myConnection).BeginTransaction();
myCommand.Connection = (OracleConnection)myConnection;
myCommand.Transaction = myTrans;
try
{
foreach (string strSql in cmdText)
{
if (string.IsNullOrEmpty(strSql))
{
continue;
}
myCommand.CommandText = strSql;
int i = myCommand.ExecuteNonQuery();
if (i > 0)
{
val = val + i;
}
}
myTrans.Commit();
}
catch (Exception err)
{
myTrans.Rollback();
myConnection.Close();
throw err;
}
myConnection.Close();
return(val);
}
}
else if (m_CurrentDataBaseType == DataBaseType.SqlServer)
{
using (SqlConnection myConnection = new SqlConnection(m_strConn))
{
int val = 0;
if (myConnection.State != System.Data.ConnectionState.Open)
{
myConnection.Open();
}
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandTimeout = 180;
SqlTransaction myTrans;
myTrans = ((SqlConnection)myConnection).BeginTransaction();
myCommand.Connection = (SqlConnection)myConnection;
myCommand.Transaction = myTrans;
try
{
foreach (string strSql in cmdText)
{
if (string.IsNullOrEmpty(strSql))
{
continue;
}
myCommand.CommandText = strSql;
int i = myCommand.ExecuteNonQuery();
if (i > 0)
{
val = val + i;
}
}
myTrans.Commit();
}
catch (Exception err)
{
myTrans.Rollback();
myConnection.Close();
throw err;
}
myConnection.Close();
return(val);
}
}
else if (m_CurrentDataBaseType == DataBaseType.Mysql)
{
using (MySqlConnection myConnection = new MySqlConnection(m_strConn))
{
int val = 0;
if (myConnection.State != System.Data.ConnectionState.Open)
{
myConnection.Open();
}
MySqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandTimeout = 180;
MySqlTransaction myTrans;
myTrans = ((MySqlConnection)myConnection).BeginTransaction();
myCommand.Connection = (MySqlConnection)myConnection;
myCommand.Transaction = myTrans;
try
{
foreach (string strSql in cmdText)
{
if (string.IsNullOrEmpty(strSql))
{
continue;
}
myCommand.CommandText = strSql;
int i = myCommand.ExecuteNonQuery();
if (i > 0)
{
val = val + i;
}
}
myTrans.Commit();
}
catch (Exception err)
{
myTrans.Rollback();
myConnection.Close();
throw err;
}
myConnection.Close();
return(val);
}
}
return(-1);
}
catch (Exception err)
{
throw err;
}
}
public object CalculateWeight(Weight quantity)
{
try
{
var commandText = "insert into QUANTITYMESURE.Weight(Kilogram ,Gram ,Tonne ,convertUnits ) values(:Kilogram,:Gram,:Tonne,:convertUnits)";
using (var _db = new OracleConnection("User Id=system;Password=system;Data Source=localhost:1521/xe"))
using (OracleCommand cmd = new OracleCommand(commandText, _db))
{
if (quantity.convertUnits == convertUnit.KgToGram.ToString())
{
quantity.Gram = quantity.Kilogram / 1000;
cmd.Connection = _db;
cmd.Parameters.Add("Kilogram", quantity.Kilogram);
cmd.Parameters.Add("Gram", quantity.Gram);
cmd.Parameters.Add("Tonne", quantity.Tonne);
cmd.Parameters.Add("convertUnits", quantity.convertUnits);
_db.Open();
cmd.ExecuteNonQuery();
_db.Close();
return(quantity.Gram);
}
else if (quantity.convertUnits == convertUnit.GramToKg.ToString())
{
quantity.Kilogram = quantity.Gram * 1000;
cmd.Connection = _db;
cmd.Parameters.Add("Kilogram", quantity.Kilogram);
cmd.Parameters.Add("Gram", quantity.Gram);
cmd.Parameters.Add("Tonne", quantity.Tonne);
cmd.Parameters.Add("convertUnits", quantity.convertUnits);
_db.Open();
cmd.ExecuteNonQuery();
_db.Close();
return(quantity.Kilogram);
}
else if (quantity.convertUnits == convertUnit.TonneToKg.ToString())
{
quantity.Kilogram = quantity.Tonne * 1000;
cmd.Connection = _db;
cmd.Parameters.Add("Kilogram", quantity.Kilogram);
cmd.Parameters.Add("Gram", quantity.Gram);
cmd.Parameters.Add("Tonne", quantity.Tonne);
cmd.Parameters.Add("convertUnits", quantity.convertUnits);
_db.Open();
cmd.ExecuteNonQuery();
_db.Close();
return(quantity.Kilogram);
}
else if (quantity.convertUnits == convertUnit.KgToTanne.ToString())
{
quantity.Tonne = quantity.Kilogram / 1000;
cmd.Connection = _db;
cmd.Parameters.Add("Kilogram", quantity.Kilogram);
cmd.Parameters.Add("Gram", quantity.Gram);
cmd.Parameters.Add("Tonne", quantity.Tonne);
cmd.Parameters.Add("convertUnits", quantity.convertUnits);
_db.Open();
cmd.ExecuteNonQuery();
_db.Close();
return(quantity.Tonne);
}
else
{
throw new CustomException("Options not matched", CustomException.ExceptionType.OPTIONS_NOT_MATCH);
}
}
}
catch (CustomException e)
{
throw new CustomException(e.Message, CustomException.ExceptionType.NULL_EXCEPTION);
}
}
public string SaveCodeRoot()
{
var result = "";
var type = TypeMasterData.KH.ToString();
string rootUnitcode = ConfigurationManager.AppSettings["rootUnitCode"];
string maDonViCha = GetParentUnitCode();
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["LTT.Connection"].ConnectionString))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
MdIdBuilder config = new MdIdBuilder();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.InitialLONGFetchSize = 1000;
cmd.CommandText = "SELECT ID,TYPE,CODE,\"CURRENT\",UNITCODE FROM MD_ID_BUILDER WHERE TYPE = '" + type + "' AND CODE = '" + type + "' AND UNITCODE = '" + maDonViCha + "' AND ROWNUM = 1";
cmd.CommandType = CommandType.Text;
OracleDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
config = new MdIdBuilder
{
Id = dataReader["ID"].ToString(),
Type = type,
Code = type,
Current = dataReader["CURRENT"].ToString(),
UnitCode = maDonViCha
};
result = config.GenerateNumber();
config.Current = result;
cmd.CommandText = "UPDATE MD_ID_BUILDER SET \"CURRENT\" = '" + result + "' WHERE TYPE = '" + type + "' AND CODE = '" + type + "' AND UNITCODE = '" + maDonViCha + "' ";
cmd.CommandType = CommandType.Text;
int countUpdate = cmd.ExecuteNonQuery();
}
}
else
{
config = new MdIdBuilder
{
Id = Guid.NewGuid().ToString(),
Type = type,
Code = type,
Current = "0",
UnitCode = maDonViCha
};
result = config.GenerateNumber();
config.Current = result;
cmd.CommandText = "INSERT INTO MD_ID_BUILDER(ID,TYPE,CODE,CURRENT,UNITCODE) VALUES ('" + config.Id + "','" + config.Type + "','" + config.Code + "','" + config.Current + "','" + config.UnitCode + "')";
cmd.CommandType = CommandType.Text;
int count = cmd.ExecuteNonQuery();
}
result = string.Format("{0}{1}", config.Code, result);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
connection.Dispose();
}
}
return(result);
}