public static User GetUser(string username)
{
User user = null;
if (username != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = "SELECT password,customerId,securityQuestion,securityAnswer,email FROM Users WHERE username LIKE :username";
command.Parameters.Add(":username", OracleDbType.NVarchar2).Value = username;
command.Connection = connection;
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string password = reader["password"].ToString();
string customerId = reader["customerId"].ToString();
string securityQuestion = reader["securityQuestion"].ToString();
string securityAnswer = reader["securityAnswer"].ToString();
string email = reader["email"].ToString();
user = new User(username, password, customerId, securityQuestion, securityAnswer, email);
}
}
}
catch (Exception e)
{
user = null;
Logger.LogException(e);
}
}
return user;
}
public string[] GetSearchItems(string prefix)
{
List<string> items = new List<string>();
using (OracleConnection conn = new OracleConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["Bazi"].ConnectionString;
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = "select ime,igraid from Igra where upper(ime) like upper(:SearchText)||'%'";
cmd.Parameters.Add(new OracleParameter("SearchText", prefix));
cmd.Connection = conn;
conn.Open();
using (OracleDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
items.Add(string.Format("{0}`{1} Game", sdr.GetString(0), sdr.GetInt64(1)));
}
}
cmd.CommandText = "select IMEMODEL,HARDID from HARDVERPROIZVOD where upper(IMEMODEL) like upper(:SearchText)||'%'";
using (OracleDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
items.Add(string.Format("{0}`{1} Hardware", rdr.GetString(0), rdr.GetInt64(1)));
}
}
conn.Close();
}
return items.ToArray();
}
}
//krijg alle producten uit DB
public List<Product> GetAll()
{
List<Product> producten = new List<Product>();
using (OracleConnection conn = Database.Instance.GetConnection())
{
conn.Open();
string query = string.Format("Select * from product");
using (OracleCommand cmd = new OracleCommand(query, conn))
{
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product product = new Product(Convert.ToInt32(reader["ID"]),
reader["productnaam"].ToString(), Convert.ToDecimal(reader["prijs"]),
reader["sale"].ToString()
, Convert.ToInt32(reader["categorie_Id"]), Convert.ToInt32(reader["voorraad"]),
Convert.ToInt32(reader["Specificaties_ID"]));
producten.Add(product);
}
}
}
}
return producten;
}
public IDbCommand CreateQuery()
{
var command = new OracleCommand(Statement) { BindByName = true };
foreach (var p in Query.Parameters.Parameters)
command.Parameters.Add(p);
return command;
}
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();
}
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();
}
public List<Assunto> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_ASSUNTO, NOME FROM ASSUNTO", conn))
{
List<Assunto> lista = new List<Assunto>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Assunto a = new Assunto();
a.Id_Assunto = Convert.ToInt32(reader["ID_ASSUNTO"]);
a.Nome = Convert.ToString(reader["NOME"]);
lista.Add(a);
}
return lista;
}
}
}
public void Upd(Assunto u)
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("ASSUNTO_tapi.upd", 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 (SqlException 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 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");
}
/// <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 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;
}
}
/**
Permite eliminar un cliente
*/
public string deleteCustomer(string id)
{
string deleteCustomer = "DELETE FROM CUSTOMERS.CUSTOMER WHERE Id = :Id";
OracleTransaction transaction;
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
try
{
connection.Open();
transaction = connection.BeginTransaction();
deleteCustomerAddress(id, connection, transaction);
using (OracleCommand command = new OracleCommand(deleteCustomer, connection))
{
command.Parameters.Add(new OracleParameter("Id", id));
command.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception e)
{
throw new PlatformException(e.Message);
}
}
return "OK";
}
public List<Troca> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_TROCA, ID_USUARIO_SOLICITANTE, ID_USUARIO_DETENTOR, DATA_INCLUSAO, DATA_SITUACAO,"
+"SITUACAO, POSICAO_SOLICITANTE, POSICAO_DETENTOR FROM TROCA", conn))
{
List<Troca> lista = new List<Troca>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Troca t = new Troca();
t.Id_Troca = Convert.ToInt32(reader["ID_TROCA"]);
t.Id_Usuario_Solicitante = Convert.ToInt32(reader["ID_USUARIO_SOLICITANTE"]);
t.Id_Usuario_Detentor = Convert.ToInt32(reader["ID_USUARIO_DETENTOR"]);
t.Data_Inclusao = Convert.ToDateTime(reader["DATA_INCLUSAO"]);
t.Data_Situacao = Convert.ToDateTime(reader["DATA_SITUACAO"]);
t.Situacao = Convert.ToInt32(reader["SITUACAO"]);
t.Posicao_Solicitante = Convert.ToInt32(reader["POSICAO_SOLICITANTE"]);
t.Posicao_Detentor = Convert.ToInt32(reader["POSICAO_DETENTOR"]);
lista.Add(t);
}
return lista;
}
}
}
public SesionUsuario inicioSesion(string userCorreo, string userPassword, string tipoUsuario)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "pk_usuarios.session_login";
OracleParameter userCorreoP = new OracleParameter("correoLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
userCorreoP.Value = userCorreo;
OracleParameter userPasswordP = new OracleParameter("passwordLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
userPasswordP.Value = userPassword;
//cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleParameter tipoUsuarioP = new OracleParameter("rol", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
tipoUsuarioP.Value = tipoUsuario;
OracleParameter resultado = new OracleParameter("resultado", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
cmd.Parameters.AddRange(new OracleParameter[] { userCorreoP, userPasswordP, tipoUsuarioP, resultado });
OracleDataReader lectorDatos = cmd.ExecuteReader();
if (lectorDatos.HasRows)
{
lectorDatos.Read();
return new SesionUsuario() { id = lectorDatos.GetString(0), operacionExitosa = true, rol = tipoUsuario };
}
else
return new SesionUsuario() { id = string.Empty, operacionExitosa = false, rol = tipoUsuario };
}
public List<Fabricante> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_FABRICANTE, NOME FROM FABRICANTE", conn))
{
List<Fabricante> lista = new List<Fabricante>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Fabricante f = new Fabricante();
f.Id_Fabricante = Convert.ToInt32(reader["ID_FABRICANTE"]);
f.Nome = Convert.ToString(reader["NOME"]);
lista.Add(f);
}
return lista;
}
}
}
public void Ins(Fabricante u)
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("FABRICANTE_tapi.ins", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
//Define parametros
cmd.Parameters.Add("p_ID_FABRICANTE", u.Id_Fabricante);
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 DbCommand CreateCommand(string sql, IDbConnection connection)
{
OracleCommand cmd = new OracleCommand(sql, connection as OracleConnection);
cmd.BindByName = true;
return cmd;
}
public DbCommand CreateBulkCommand(string sql, int rowsToInsert, IDbConnection cn)
{
OracleCommand cmd = new OracleCommand(sql, cn as OracleConnection);
cmd.ArrayBindCount = rowsToInsert;
cmd.BindByName = true;
return cmd;
}
/// <summary>
/// Selects and retrieves values from the database
/// </summary>
/// <param name="query">The selection statement</param>
/// <returns>A DataTable with the retrieved values></returns>
public static DataTable RetrieveQuery(string query)
{
if (Regex.IsMatch(query, @"-{2,}"))
{
throw new SQLInjectionException();
}
using (OracleConnection c = new OracleConnection(@connectionstring))
{
try
{
c.Open();
OracleCommand cmd = new OracleCommand(@query);
cmd.Connection = c;
try
{
OracleDataReader r = cmd.ExecuteReader();
DataTable result = new DataTable();
result.Load(r);
c.Close();
return result;
}
catch (OracleException e)
{
Console.Write(e.Message);
throw;
}
}
catch (OracleException e)
{
Console.Write(e.Message);
return new DataTable();
}
}
}
public static Category FindById(int id)
{
string query = "SELECT * FROM TBL_CATEGORY WHERE ID=:id";
using (OracleConnection connection = CreateConnection())
using (OracleCommand command = new OracleCommand(query, connection))
{
command.BindByName = true;
command.Parameters.Add(new OracleParameter("id", id));
using (OracleDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
var dicId = GetCategoryIdFromRecord(reader);
if (!Dictionaries.Categories.ContainsKey(dicId))
{
Dictionaries.Categories.Add(dicId, GetCategoryFromDataRecord(reader));
}
return Dictionaries.Categories[dicId];
}
}
}
return null;
}
public static void GetEmployeeMatches(List<Employee> list, DPFP.Sample Sample,int FingerNo)
{
OracleConnection conn = new OracleConnection(ConStr);
Employee info = null;
string FingerDesc = Util.GetFingerDesc(FingerNo);
try
{
string _result = string.Empty;
string sql = "select Empl_Id_No,RTF,RIF,RMF,RRF,RSF,LTF,LIF,LMF,LRF,LSF from EMPTSI";
OracleCommand command = new OracleCommand(sql, conn);
command.CommandType = CommandType.Text;
conn.Open();
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
info = dcEmployee.GetInfo(reader, Sample, FingerDesc);
AddInfo(list, info);
}
}
if (conn.State == ConnectionState.Open) conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
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;
}
}
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();
}
// 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;
}
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();
}
/// <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);
}
}
}
void AutocompleteText()
{
textBox5.AutoCompleteMode = AutoCompleteMode.Suggest;
textBox5.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection coll = new AutoCompleteStringCollection();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sqluery = "SELECT * FROM MED_INFO WHERE MED_MGF='" + comboBox1.Text + "' ORDER BY MED_NAME";
OracleCommand cd = new OracleCommand(sqluery, conn);
OracleDataReader r;
r = cd.ExecuteReader();
while(r.Read())
{
string sn = r.GetString(1);
coll.Add(sn);
}
r.Dispose();
cd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
textBox5.AutoCompleteCustomSource = coll;
}
public static bool IsAvailable(string columnName, string value)
{
bool available = false;
if (!String.IsNullOrWhiteSpace(columnName) && value != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = String.Format("SELECT COUNT(*) FROM Users WHERE {0} LIKE :value",columnName);
command.Parameters.Add(":value", OracleDbType.NVarchar2).Value = value;
command.Connection = connection;
connection.Open();
int count = Convert.ToInt32(command.ExecuteScalar());
if (count==0)
available = true;
else
available = false;
}
}
catch (Exception e)
{
available = false;
Logger.LogException(e);
}
}
return available;
}
/// <summary>
/// Changes the thread
/// </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 AlterThread(int id, List<string> changes, List<string> paramaterToChanged)
{
OracleConnection conn = MakeConnection();
conn.Open();
string mainQuery = "UPDATE THREAD 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 THREADID = :THREADID";
command.CommandText = mainQuery;
command.Connection = conn;
command.Parameters.Add(new OracleParameter("THREADID", id));
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
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 async Task <IActionResult> EditDataPost(string connectionName, string tableName, IEnumerable <string> dataFields, IEnumerable <string> oldDataFields, string tableColumnInfosJson)
{
var tableDataVM = new TableDataVM
{
TableList = await _util.GetTableList(connectionName),
TableName = tableName,
ConnectionName = connectionName
};
var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName);
var connectionString = Util.GetConnectionString(customConnection);
tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList);
var columnList = new List <TableColumnInfo>();
var tableDataDict = new PagedData {
Data = new Dictionary <int, Row>()
};
columnList = await _util.GetColumnInfo(connectionName, tableName);
var tablemetadata = await _util.GetTableMetadata(connectionName, tableName);
tableDataVM.ColumnList = columnList;
tableDataVM.TableDataList = tableDataDict;
tableDataVM.SequenceName = tablemetadata.SequenceName;
var dataFieldArray = dataFields.ToArray();
var oldDataFieldArray = oldDataFields.ToArray();
var primaryKey = GetPrimaryKey(columnList, dataFieldArray);
var whereColumnListStmt = "";
if (string.IsNullOrEmpty(primaryKey))
{
var oldColumnList = JsonConvert.DeserializeObject <List <TableColumnInfo> >(tableColumnInfosJson).ToArray();
var builderWhere = new System.Text.StringBuilder();
builderWhere.Append(whereColumnListStmt);
for (int j = 0; j < columnList.Count(); j++)
{
builderWhere.Append(columnList[j].Name + "='" + oldColumnList[j].Value + "' and ");
}
whereColumnListStmt = builderWhere.ToString();
whereColumnListStmt = whereColumnListStmt.TrimEnd(' ', 'd', 'n', 'a');
}
var columnListStmt = "";
var builder = new System.Text.StringBuilder();
builder.Append(columnListStmt);
for (int j = 0; j < columnList.Count(); j++)
{
if (columnList[j].IsPrimaryKey)
{
continue;
}
if (columnList[j].DataType.Equals("DATE"))
{
builder.Append(columnList[j].Name + "=TO_DATE('" + dataFieldArray[j] + "','dd.mm.yyyy HH24:MI:SS'), ");;
}
else
{
builder.Append(columnList[j].Name + "='" + dataFieldArray[j] + "', ");;
}
}
columnListStmt = builder.ToString();
columnListStmt = columnListStmt.TrimEnd(' ').TrimEnd(',');
var updateSqlStmt = "";
var whereStmt = Util.FindUniqueRowWhereStmt(primaryKey, columnList);
updateSqlStmt = string.IsNullOrEmpty(primaryKey) ? "update " + tableName + " set " + columnListStmt + " where " + whereColumnListStmt : "update " + tableName + " set " + columnListStmt + " where " + whereStmt;
var sessionHistorySql = new SessionSqlHistory
{
EventDate = DateTime.Now,
SqlText = updateSqlStmt,
BasicSqlText = updateSqlStmt
};
using (var oconn = new OracleConnection(connectionString))
{
oconn.Open();
using (var cmd = new OracleCommand
{
Connection = oconn,
CommandText = updateSqlStmt,
CommandType = CommandType.Text
})
{
var result = cmd.ExecuteNonQuery();
}
}
await _context.SessionSqlHistory.AddAsync(sessionHistorySql);
await _context.SaveChangesAsync();
var tableColumnInfos = new List <TableColumnInfo>();
for (int j = 0; j < columnList.Count(); j++)
{
var tableColumnInfo = new TableColumnInfo
{
DataType = columnList[j].DataType,
IsPrimaryKey = columnList[j].IsPrimaryKey,
Name = columnList[j].Name,
Value = dataFieldArray[j],
OldValue = columnList[j].OldValue
};
tableColumnInfos.Add(tableColumnInfo);
}
var newTableColumnInfosJson = JsonConvert.SerializeObject(tableColumnInfos);
tableDataVM.RowData = await _util.GetRowData(connectionName, tableName, primaryKey, newTableColumnInfosJson);
tableDataVM.TableColumnInfosJson = newTableColumnInfosJson;
ViewBag.Message = "Successfully saved";
return(View(nameof(EditData), tableDataVM));
}
private void Select_ID_for_viewprisonerdata_using_proc_SelectedIndexChanged(object sender, EventArgs e)
{
OracleCommand cp = new OracleCommand();
cp.Connection = conn;
cp.CommandText = "select_one_row_first_name";
cp.CommandType = CommandType.StoredProcedure;
cp.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cp.Parameters.Add("f_namee", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cp.ExecuteNonQuery();
First_Name_box_view_using_proc.Text = cp.Parameters["f_namee"].Value.ToString();
/***************************************************************************/
OracleCommand cm = new OracleCommand();
cm.Connection = conn;
cm.CommandText = "select_one_row_middle_name";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cm.Parameters.Add("middle_name", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cm.ExecuteNonQuery();
Middle_Initial_box_updateview_using_proc.Text = cm.Parameters["middle_name"].Value.ToString();
/**********************************************************************************/
OracleCommand cw = new OracleCommand();
cw.Connection = conn;
cw.CommandText = "select_one_row_last_name";
cw.CommandType = CommandType.StoredProcedure;
cw.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cw.Parameters.Add("last_namee", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cw.ExecuteNonQuery();
Last_Name_box_updateview_using_proc.Text = cw.Parameters["last_namee"].Value.ToString();
/**********************************************************************************/
OracleCommand cq = new OracleCommand();
cq.Connection = conn;
cq.CommandText = "select_one_row_address";
cq.CommandType = CommandType.StoredProcedure;
cq.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cq.Parameters.Add("addresss", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cq.ExecuteNonQuery();
Address_box_updateview_using_proc.Text = cq.Parameters["addresss"].Value.ToString();
/**********************************************************************************/
OracleCommand cv = new OracleCommand();
cv.Connection = conn;
cv.CommandText = "select_one_row_gender";
cv.CommandType = CommandType.StoredProcedure;
cv.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cv.Parameters.Add("genderr", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cv.ExecuteNonQuery();
Gender_box_updateview_using_proc.Text = cv.Parameters["genderr"].Value.ToString();
/**********************************************************************************/
OracleCommand cl = new OracleCommand();
cl.Connection = conn;
cl.CommandText = "select_one_row_age";
cl.CommandType = CommandType.StoredProcedure;
cl.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cl.Parameters.Add("agee", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cl.ExecuteNonQuery();
Age_box_updateview_using_proc.Text = cl.Parameters["agee"].Value.ToString();
/**********************************************************************************/
OracleCommand co = new OracleCommand();
co.Connection = conn;
co.CommandText = "select_one_row_cell_no";
co.CommandType = CommandType.StoredProcedure;
co.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
co.Parameters.Add("cell_noo", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
co.ExecuteNonQuery();
cell_no_box_view_row_using_proc.Text = co.Parameters["cell_noo"].Value.ToString();
/**********************************************************************************/
OracleCommand cy = new OracleCommand();
cy.Connection = conn;
cy.CommandText = "select_one_row_prison_name";
cy.CommandType = CommandType.StoredProcedure;
cy.Parameters.Add("iid", Convert.ToInt64(Select_ID_for_viewprisonerdata_using_proc.SelectedItem.ToString()));
cy.Parameters.Add("prison_namee", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
cy.ExecuteNonQuery();
prison_name_box_using_proc.Text = cy.Parameters["prison_namee"].Value.ToString();
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
tainted_2 = "hardcoded";
tainted_3 = tainted_2;
if ((Math.Sqrt(42) >= 42))
{
{}
}
else
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "admin" && textBox2.Text == "admin")
{
admin_profil f = new admin_profil();
f.Show();
this.Hide();
}
else
{
conn = new OracleConnection(ordb);
conn.Open();
OracleCommand cmd = new OracleCommand();
string user_cmd = "select * from USER_INFO where E_MAIL=:mail and PASSWORD=:password";
string eng_cmd = "select * from ENGINEER where E_MAIL=:mail and PASSWORD=:password";
string comm = " ";
if (user_radioButton1.Checked == true)
{
comm = user_cmd;
}
else if (eng_radioButton2.Checked == true)
{
comm = eng_cmd;
}
cmd.Connection = conn;
cmd.CommandText = comm;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("mail", textBox1.Text.ToString());
cmd.Parameters.Add("password", textBox2.Text.ToString());
OracleDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
id = Convert.ToInt32(dr[0]);
if (user_radioButton1.Checked == true)
{
USER_PROFILE f = new USER_PROFILE();
f.Show();
this.Hide();
}
if (eng_radioButton2.Checked == true)
{
ENGINEER_PROFILE f = new ENGINEER_PROFILE();
f.Show();
this.Hide();
}
else if (eng_radioButton2.Checked == false && user_radioButton1.Checked == false)
{
MessageBox.Show("ERROR>>YOU SHOULD SELECT(USER OR ENGINEER)");
}
}
else
{
MessageBox.Show("invaild data");
}
conn.Close();
}
}
protected void EfetuarCargaDefault(string nomeCarga, Func <int, int> final = null, int index = 1)
{
Log.Info("------------------");
Log.Info($"Migração {nomeCarga}");
if (PreRequisits != null && PreRequisits.Any())
{
foreach (var req in PreRequisits)
{
EfetuarPreRequisits(req);
}
}
var i = index;
try
{
var dt1 = DateTime.Now;
var con = new OracleConnection {
ConnectionString = ConexaoOrigem
};
con.Open();
var cmdQuery = ComandoSqlOrigem;
var cmd = new OracleCommand(cmdQuery)
{
InitialLONGFetchSize = 300,
Connection = con,
CommandType = CommandType.Text
};
var lista = new List <T>();
Log.Info("Inicio Execução do banco de dados");
var reader = cmd.ExecuteReader();
Log.Info("Fim Execução do banco de dados");
while (reader.Read())
{
try
{
var readerLine = new List <object>();
#region Campos
int camposIdx;
for (camposIdx = 0; camposIdx < QuantidadeCampos; camposIdx++)
{
readerLine.Add(reader.GetValue(camposIdx));
}
var model = GetModel(readerLine, i);
var valid = model.Validar();
if (valid.IsValid)
{
lista.Add(model);
}
else
{
valid.Erros.ForEach(x =>
{
Log.Error(
$"Campo {x.Campo} possui {x.TamanhoReal} caracteres e deveria ter no maximo {x.TamanhoMaximo} ({x.Valor})");
});
}
#endregion
}
catch (Exception ex)
{
Log.Error("Erro na leitura do registro : " + i + " - detalhes : " + ex.Message);
}
if ((i % QuantidadeRegistrosPorTransacao) == 0)
{
try
{
Log.Info("Gravando : " + i.ToString("N0"));
var ls = lista;
lista = new List <T>();
GravarBulk(ls);
//log.Info("Gravado com sucesso!");
}
catch (Exception ex)
{
Log.Error("Erro na Gravacao dos registros : " + (i - QuantidadeRegistrosPorTransacao) + " até " + i + " - detalhes : " + ex.Message);
}
}
i++;
}
GravarBulk(lista);
Log.Info("Gravando : " + (i - 1).ToString("N0"));
reader.Close();
reader.Dispose();
con.Close();
con.Dispose();
var dt2 = DateTime.Now;
var elapsed = (dt2 - dt1).TotalSeconds;
Log.Info("Tempo Total em Segundos - " + elapsed);
Log.Info("------------------");
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
final?.Invoke(i);
}
}
private void InsertFileInfo(string File_Name, string File_rec, string CreatedBy, ref string V_Pout)
{
BaseResponse response = new BaseResponse();
bool isSuccess = true;
string code = "00";
string message = string.Empty;
OracleConnection con = null;
OracleCommand cmd = null;
try
{
// using (cmd = new NpgsqlCommand("CALL PRC_RETAILER_LOGIN_INFO_SAVE(@PRETAILERCNIC,@PRETAILERID,@PRETAILERREGION,@PFSRETAILERID,@PFUNDAMOPOS,@PEAgreementState,@PBVMT_Agent_Sent,@PBVMT_Agent_Receive,@PWIFI_FALLBACK,@PSMS_FALLBACK,@PUPSELL,@pDAP,@PIRCASHWITHDRAWL,@PIRBVSVERIFICATION,@PIRPULLFUND,@P4GUPSELLCHECK,@PDAPACCUPSELL,@PRETAILER_CREATE,@PRSO_TYPE,@PRETAILER_CREATE_TYPE,@PREPEAT_RECHARGE,@PEASY_BAZAR,@PRETAILER_REGISTRATION,@POTP,@PCOMPLIANCE_PASSWORD,@P789_SIM_REPLACEMENT,@PACCESSRIGHTS,@PCREATEDBY)", connection = new NpgsqlConnection(_Configuration.GetConnectionString("DefaultConnection")))
// using (connection = new OracleConnection("data source=10.0.1.192:1521/demoaksa; User ID=MBPU_STG; Password=mbpu321;"))
using (con = new OracleConnection(_connectionString))
{
using (cmd = new OracleCommand("PKG_MAPPING_BULK.mpng_tmpp_file", con))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter {
ParameterName = "pFILE_NME", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = File_Name
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "pFILE_REC", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = File_rec
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "pCreatedBy", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = CreatedBy
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "pfle", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
con.Open();
cmd.ExecuteNonQuery();
con.Close();
//if (Convert.ToString(cmd.Parameters["PCODE"].Value) == "00" || Convert.ToString(cmd.Parameters["PCODE"].Value) == "0")
//{
V_Pout = cmd.Parameters["pfle"].Value.ToString();
// obj.msg = cmd.Parameters["PDESC"].Value.ToString();
//}
// else
// {
// obj.msg = cmd.Parameters["PDESC"].Value.ToString();
// }
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
try
{
if (con != null && con.State != ConnectionState.Closed)
{
con.Close();
con.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
catch { }
}
// return new BaseResponse { Code = code, IsSuccess = isSuccess, Message = message };
}
public BaseResponse GetLBCOffers(string File_ID)
{
BaseResponse response = new BaseResponse();
var resultt = "";
dynamic dtt = null;
bool isSuccess = true;
string code = "00";
string message = string.Empty;
OracleConnection con = null;
OracleCommand cmd = null;
try
{
// using (cmd = new NpgsqlCommand("CALL PRC_RETAILER_LOGIN_INFO_SAVE(@PRETAILERCNIC,@PRETAILERID,@PRETAILERREGION,@PFSRETAILERID,@PFUNDAMOPOS,@PEAgreementState,@PBVMT_Agent_Sent,@PBVMT_Agent_Receive,@PWIFI_FALLBACK,@PSMS_FALLBACK,@PUPSELL,@pDAP,@PIRCASHWITHDRAWL,@PIRBVSVERIFICATION,@PIRPULLFUND,@P4GUPSELLCHECK,@PDAPACCUPSELL,@PRETAILER_CREATE,@PRSO_TYPE,@PRETAILER_CREATE_TYPE,@PREPEAT_RECHARGE,@PEASY_BAZAR,@PRETAILER_REGISTRATION,@POTP,@PCOMPLIANCE_PASSWORD,@P789_SIM_REPLACEMENT,@PACCESSRIGHTS,@PCREATEDBY)", connection = new NpgsqlConnection(_Configuration.GetConnectionString("DefaultConnection")))
// using (connection = new OracleConnection("data source=10.0.1.192:1521/demoaksa; User ID=MBPU_STG; Password=mbpu321;"))
using (con = new OracleConnection(_connectionString))
{
using (cmd = new OracleCommand("PKG_LBC_OFFERS.PRC_GET_LBC_OFFERS", con))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter {
ParameterName = "pFILEID", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = File_ID
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "pCUR_LBC_OFFERS", OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PCODE", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PDESC", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PMSG", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
OracleDataAdapter oAdapdter = new OracleDataAdapter(cmd);
dtt = oAdapdter.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
}
}
}
con.Dispose();
con = null;
return(dtt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
try
{
if (con != null && con.State != ConnectionState.Closed)
{
con.Close();
con.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
catch { }
}
return(new BaseResponse {
Code = code, IsSuccess = isSuccess, Message = message, Data = dtt
});
}
private string Call_Update_LBC_Offer(string pFranchise_ID, string pOfferPrice, string pOfferType, string pOfferVolume, string pOfferDescription, DateTime startDate, DateTime endDate, string pLBC_OfferPrice, string pFileId, string pCreatedBy)
{
BaseResponse response = new BaseResponse();
bool isSuccess = true;
string code = "00";
string message = string.Empty;
OracleConnection con = null;
OracleCommand cmd = null;
try
{
// using (cmd = new NpgsqlCommand("CALL PRC_RETAILER_LOGIN_INFO_SAVE(@PRETAILERCNIC,@PRETAILERID,@PRETAILERREGION,@PFSRETAILERID,@PFUNDAMOPOS,@PEAgreementState,@PBVMT_Agent_Sent,@PBVMT_Agent_Receive,@PWIFI_FALLBACK,@PSMS_FALLBACK,@PUPSELL,@pDAP,@PIRCASHWITHDRAWL,@PIRBVSVERIFICATION,@PIRPULLFUND,@P4GUPSELLCHECK,@PDAPACCUPSELL,@PRETAILER_CREATE,@PRSO_TYPE,@PRETAILER_CREATE_TYPE,@PREPEAT_RECHARGE,@PEASY_BAZAR,@PRETAILER_REGISTRATION,@POTP,@PCOMPLIANCE_PASSWORD,@P789_SIM_REPLACEMENT,@PACCESSRIGHTS,@PCREATEDBY)", connection = new NpgsqlConnection(_Configuration.GetConnectionString("DefaultConnection")))
// using (connection = new OracleConnection("data source=10.0.1.192:1521/demoaksa; User ID=MBPU_STG; Password=mbpu321;"))
using (con = new OracleConnection(_connectionString))
{
using (cmd = new OracleCommand("PKG_LBC_OFFERS.PRC_LBC_OFFER_UPDATE", con))
{
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PFRANCHISE_ID", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = pFranchise_ID
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "POFFER_PRICE", OracleDbType = OracleDbType.Int32, Direction = ParameterDirection.Input, Value = pOfferPrice
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "POFFER_TYPE", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = pOfferType
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "POFFER_VOLUME", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = pOfferVolume
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "POFFER_DESC", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = pOfferDescription
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PSTART_DATE", OracleDbType = OracleDbType.TimeStamp, Direction = ParameterDirection.Input, Value = startDate
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PEND_DATE", OracleDbType = OracleDbType.TimeStamp, Direction = ParameterDirection.Input, Value = endDate
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PLBC_PRICE", OracleDbType = OracleDbType.Int32, Direction = ParameterDirection.Input, Value = pLBC_OfferPrice
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PFILE_ID", OracleDbType = OracleDbType.Int32, Direction = ParameterDirection.Input, Value = pFileId
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PCREATED_BY", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input, Value = pCreatedBy.ToLower()
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PCODE", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PDESC", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
cmd.Parameters.Add(new OracleParameter {
ParameterName = "PMSG", OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Output, Size = 1000
});
con.Open();
cmd.ExecuteNonQuery();
con.Close();
if (Convert.ToString(cmd.Parameters["PCODE"].Value) == "00" || Convert.ToString(cmd.Parameters["PMSG"].Value) == "Y")
{
response.Message = "Operation Performed Successfully";
}
else
{
response.Message = cmd.Parameters["PDESC"].Value.ToString();
}
con.Dispose();
con = null;
}
}
return(response.Message);
}
catch (Exception ex)
{
throw ex;
}
finally
{
try
{
if (con != null && con.State != ConnectionState.Closed)
{
con.Close();
con.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
catch { }
}
// return new BaseResponse { Code = code, IsSuccess = isSuccess, Message = message };
}
private void InsertToDatabase(StatusDTO data, string conString, string tableName)
{
try
{
using (var con = new OracleConnection(conString))
{
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText =
string.Format(
"INSERT INTO {0} (MU_NAME, LONGITUDE, LATITUDE, AZIMUTH, ACTION_STATE, POSITION_UPDATE, STATE_UPDATE) VALUES (:i, :a, :o, :b, :st, TO_DATE(:t, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(:tt, 'YYYY-MM-DD HH24:MI:SS'))",
tableName)
};
cmd.Parameters.Add(new OracleParameter
{
Value = data.i,
ParameterName = "i"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.o,
ParameterName = "o"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.a,
ParameterName = "a"
});
// is not in table in database
//commmand.Parameters.Add(new OracleParameter
//{
// Value = value.l,
// ParameterName = "l"
//});
//commmand.Parameters.Add(new OracleParameter
//{
// Value = value.s,
// ParameterName = "s"
//});
cmd.Parameters.Add(new OracleParameter
{
Value = data.b,
ParameterName = "b"
});
cmd.Parameters.Add(new OracleParameter
{
Value = "SHUTDOWN",
ParameterName = "st"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.t?.ToString("yyyy-MM-dd HH:mm:ss"),
ParameterName = "t"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.t?.ToString("yyyy-MM-dd HH:mm:ss"),
ParameterName = "tt"
});
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.Error(e);
}
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Sqrt(42) >= 42))
{
{}
}
else if (!(Math.Sqrt(42) >= 42))
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
else
{
{}
}
//flaw
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
private void english_Load(object sender, EventArgs e)
{
pgOraConn = new OracleConnection($"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={dbIp})(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={dbName})));User ID={dbId};Password={dbPw};Connection Timeout=30;");
pgOraConn.Open();
pgOraCmd = pgOraConn.CreateCommand();
}
public void UpdateDatabase(StatusDTO data, string conString, string tableName)
{
{
try
{
using (var con = new OracleConnection(conString))
{
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = string.Format(
"UPDATE {0} SET LONGITUDE = :a, LATITUDE = :o , AZIMUTH = :b, ACTION_STATE = :st, POSITION_UPDATE = TO_DATE(:t, 'YYYY-MM-DD HH24:MI:SS'), STATE_UPDATE = TO_DATE(:t, 'YYYY-MM-DD HH24:MI:SS') WHERE MU_NAME = '{1}'",
tableName, data.i)
};
// does not work
//cmd.Parameters.Add(new OracleParameter
//{
// Value = data.i,
// ParameterName = "i"
//});
cmd.Parameters.Add(new OracleParameter
{
Value = data.o,
ParameterName = "o"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.a,
ParameterName = "a"
});
// is not in table in database
//commmand.Parameters.Add(new OracleParameter
//{
// Value = value.l,
// ParameterName = "l"
//});
//commmand.Parameters.Add(new OracleParameter
//{
// Value = value.s,
// ParameterName = "s"
//});
cmd.Parameters.Add(new OracleParameter
{
Value = data.b,
ParameterName = "b"
});
cmd.Parameters.Add(new OracleParameter
{
Value = "SHUTDOWN",
ParameterName = "st"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.t?.ToString("yyyy-MM-dd HH:mm:ss"),
ParameterName = "t"
});
cmd.Parameters.Add(new OracleParameter
{
Value = data.t?.ToString("yyyy-MM-dd HH:mm:ss"),
ParameterName = "tt"
});
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.Error(e);
}
}
}
void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var connection = new SqlConnection(ConnectionString))
using (var command = new SqlCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
case Type.Access:
using (var connection = new OleDbConnection(ConnectionString))
using (var command = new OleDbCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
case Type.Oracle:
using (var connection = new OracleConnection(ConnectionString))
using (var command = new OracleCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
case Type.MySql:
using (var connection = new MySqlConnection(ConnectionString))
using (var command = new MySqlCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
case Type.Sqlite:
using (var connection = new SQLiteConnection(ConnectionString))
using (var command = new SQLiteCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
case Type.PostGreSql:
using (var connection = new NpgsqlConnection(ConnectionString))
using (var command = new NpgsqlCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
case Type.Teradata:
using (var connection = new TdConnection(ConnectionString))
using (var command = new TdCommand(sql, connection))
{
ConvertToCsv(connection, command);
}
break;
}
}
public void CheckPin()
{
try
{
using (OracleConnection con = new OracleConnection(ConString))
{
con.Open();
OracleCommand cmd = new OracleCommand("SELECT count(*) FROM customer1 where card_no=" + CardNo + " and card_pin=" + Pin + " ", con);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataTable dt = new DataTable(); //this is creating a virtual table
oda.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
/* I have made a new page called home page. If the user is successfully authenticated then the form will be moved to the next form */
OracleCommand cmd1 = new OracleCommand("SELECT c_name FROM Customer1 where card_no='" + CardNo + "' and card_pin='" + Pin + "' ", con);
// OracleDataAdapter oda = new OracleDataAdapter(cmd);
OracleDataReader odr = cmd1.ExecuteReader();
if (odr.Read())
{
MessageBox.Show("Welcome " + Convert.ToString(odr.GetValue(0)));
cust_card_no = CardNo;
cust_pin = Pin;
ministatementViewModel mns = new ministatementViewModel();
mns.cust_card_no = cust_card_no;
cust_name = Convert.ToString(odr.GetValue(0));
}
// MessageBox.Show("SUCCESS");
Menuscreen menuscrn = new Menuscreen();
WithdrawMoney withdrmnyscrn = new WithdrawMoney();
/* MainWindow mainw =new MainWindow();
* mainw.Close();
* mainw.Hide(); */
menuscrn.Show();
//withdrmnyscrn.Show();
menuscrn.DataContext = childvm;
/// withdrmnyscrn.DataContext = childvmm;
// childvmm.CardNo1 = CardNo;
childvm.CardNo = CardNo;
childvm.Pin = Pin;
/// childvmm.cust_card_no = CardNo;
///childvmm.Pin1 = Pin;
childvm.Custlbl = "Hi, " + Convert.ToString(odr.GetValue(0));
///childvmm.cust_name = Convert.ToString(odr.GetValue(0));
}
else
{
MessageBox.Show("Invalid username or password");
}
// datagridview1.ItemsSource=dt.DefaultView;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void EmailAlertCommon(Int16 EmailID, ArrayList List)
{
try
{
//return;
String To = "";
String Cc = "";
String DisplayName = "";
String Subject = "";
String Body = "";
OracleConnection conn_GetUserRole = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString());
conn_GetUserRole.Open();
OracleCommand cmd = conn_GetUserRole.CreateCommand();
cmd.CommandText = " select t.email_to_list,t.email_cc_list,t.email_display_name,t.email_subject,t.email_body from " +
" fas_ibt_email_alerts t where t.Email_id = '" + EmailID + "' and t.effective_end_date is null";
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
oda.Fill(dt);
//Database having values
To = dt.Rows[0]["email_to_list"].ToString().Replace(";", ",") + ",";
Cc = dt.Rows[0]["email_cc_list"].ToString().Replace(";", ",") + ",";
//DisplayName = dt.Rows[0]["email_display_name"].ToString() + "Testing Only";
//Subject = dt.Rows[0]["email_subject"].ToString() + "Testing Only";
DisplayName = dt.Rows[0]["email_display_name"].ToString();
Subject = dt.Rows[0]["email_subject"].ToString();
//Body = dt.Rows[0]["email_body"].ToString();
//-------------MRP ERROR LIST--------------
if (List != null)
{
Body = "<html><body><table>";
Body += "<tr bgcolor=" + "#7C9CB6" + ">" +
"<td><font face=" + "Tahoma" + " size=" + "-1>Serial Number</font></td></tr>";
foreach (var item in List)
{
Body += "<tr bgcolor=" + "#CCCCCC" + ">" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + item + "</font></td></tr>";
}
Body += "</table></html></body>";
}
else
{
Body = dt.Rows[0]["email_body"].ToString();
}
//-----------------------------------------
MailMessage message = new MailMessage();
MailAddress from = new MailAddress("*****@*****.**", DisplayName);
String OriginTO = To;
for (int i = 0; i < OriginTO.Length; i++)
{
String Temp = OriginTO.Substring(0, OriginTO.IndexOf(",") + 1);
if (Temp == "")
{
break;
}
OriginTO = OriginTO.Replace(Temp, "");
MailAddress to = new MailAddress(Temp.Replace(",", ""));
message.To.Add(to);
}
String OriginCC = Cc;
for (int i = 0; i < OriginCC.Length; i++)
{
String Temp = OriginCC.Substring(0, OriginCC.IndexOf(",") + 1);
if (Temp == "")
{
break;
}
OriginCC = OriginCC.Replace(Temp, "");
MailAddress cc = new MailAddress(Temp.Replace(",", ""));
message.CC.Add(cc);
}
message.From = from;
message.Subject = Subject;
if (List != null)
{
message.IsBodyHtml = true;
}
message.Body = @Body;
SmtpClient client = new SmtpClient("smtp.hnbassurance.com");
client.Credentials = new System.Net.NetworkCredential("misreports", "Water@1234");
client.Send(message);
}
catch (Exception ex)
{
throw ex;
}
}
public void TransferMoney()
{
try
{
using (OracleConnection con = new OracleConnection(ConString))
{
//MessageBox.Show(cust_name);
con.Open();
OracleCommand cmd12 = new OracleCommand("SELECT * FROM Customer1 where card_no='" + CardNO_T + "' ", con);
OracleDataReader odr12 = cmd12.ExecuteReader();
OracleCommand cmd = new OracleCommand("SELECT acc_bal FROM Customer1 where card_no='" + cust_card_no + "' and card_pin='" + cust_pin + "' ", con);
// OracleDataAdapter oda = new OracleDataAdapter(cmd);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.Read() && odr12.HasRows == true)
{
cust_balance = Convert.ToInt64(odr.GetValue(0));
if (cust_balance < Convert.ToInt64(Amount_T))
{
MessageBox.Show(cust_name + "\n Withdraw Amount is more than Your Account Balance");
}
else
{
// con.Open();
OracleCommand cmd2 = new OracleCommand("UPDATE customer1 set acc_bal='" + (cust_balance - Convert.ToInt64(Amount_T)) + "' where card_no='" + cust_card_no + "' ", con);
OracleCommand cmd11 = new OracleCommand("UPDATE customer1 set acc_bal= acc_bal+'" + (Convert.ToInt64(Amount_T)) + "' where card_no='" + CardNO_T + "' ", con);
// OracleDataAdapter oda = new OracleDataAdapter(cmd2);
int nn11 = cmd11.ExecuteNonQuery();
int nn = cmd2.ExecuteNonQuery();
if (nn > 0 && nn11 > 0)
{
MessageBox.Show(" Transaction Successfull \n " + Amount_T + " Has been Transfered to " + CardNO_T);
// WithdrAmt=string.Empty;
}
}
//Page1 pg=new Page1();
//pg.InitializeComponent();
// MessageBox.Show(cust_name + " Transaction Successfull ");
Amount_T = string.Empty;
CardNO_T = string.Empty;
}
else
{
MessageBox.Show("Invalid Card No");
CardNO_T = string.Empty;
}
// datagridview1.ItemsSource=dt.DefaultView;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Global.CurrentPerson == null || string.IsNullOrEmpty(Request.QueryString["Series"]))
{
Response.Redirect("Index.aspx");
}
SeriesId = long.Parse(Request.QueryString["Series"]);
DataTable eventTable = new DataTable();
List <Models.Event> eventModel = new List <Models.Event>();
DataTable requestsTable = new DataTable();
List <Models.GroupRequest> requestsModel = new List <Models.GroupRequest>();
using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
{
// Set up the getEvent command
var eventCommand = new OracleCommand("TICKETS_QUERIES.getEvent", objConn)
{
BindByName = true, CommandType = CommandType.StoredProcedure
};
eventCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
eventCommand.Parameters.Add("p_SeriesId", OracleDbType.Int64, SeriesId, ParameterDirection.Input);
// Set up the getGroupRequestsForEvent command
var requestsCommand = new OracleCommand("TICKETS_QUERIES.getAcceptedGroupForEvent", objConn)
{
BindByName = true, CommandType = CommandType.StoredProcedure
};
requestsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
requestsCommand.Parameters.Add("p_SeriesId", OracleDbType.Int64, SeriesId, ParameterDirection.Input);
requestsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);
try
{
// Execute the queries and auto map the results to models
objConn.Open();
var eventAdapter = new OracleDataAdapter(eventCommand);
eventAdapter.Fill(eventTable);
eventModel = Mapper.DynamicMap <IDataReader, List <Models.Event> >(eventTable.CreateDataReader());
var requestsAdapter = new OracleDataAdapter(requestsCommand);
requestsAdapter.Fill(requestsTable);
requestsModel = Mapper.DynamicMap <IDataReader, List <Models.GroupRequest> >(requestsTable.CreateDataReader());
}
catch (Exception)
{
Response.Redirect("EventSignup.aspx?Series=" + SeriesId);
}
objConn.Close();
}
if (eventModel.Any())
{
var currentEvent = eventModel.FirstOrDefault();
EventName.InnerHtml = currentEvent.name;
EventDescription.Text = currentEvent.description;
EventLocation.Text = currentEvent.building;
EventDateList.DataSource = eventModel;
EventDateList.DataBind();
}
if (requestsModel.Any())
{
var leaderInformation = requestsModel.First();
GroupLeaderName.Text = string.Format("{0} {1}", leaderInformation.group_leader_firstname, leaderInformation.group_leader_lastname);
GuestTickets = leaderInformation.guest_tickets;
GroupList.DataSource = requestsModel;
GroupList.DataBind();
}
else
{
Response.Redirect("EventSignup.aspx?Series=" + SeriesId);
}
}
}
public void SendEmails(String Type)
{
try
{
OracleConnection connORAGetData = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF_ACC"].ToString());
SqlConnection connEmail = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconn"].ToString());
SqlCommand cmdEmail = new SqlCommand();
SqlDataReader drEmail;
OracleCommand cmdORADetails = new OracleCommand();
OracleDataReader drORADetails;
DataTable Details = new DataTable();
String MailHeader = "";
String MailSubject = "";
String RefNo = "";
String AgentCode = "";
cmdEmail.CommandType = CommandType.Text;
//cmdEmail.CommandText = "SELECT * FROM BRANCH_E_MAIL WHERE BRANCH_CODE='" + Convert.ToString(Dts.Variables["Branch_code_name"].Value) + "'";
cmdEmail.CommandText = "SELECT * FROM BRANCH_E_MAIL WHERE BRANCH_CODE = 'HDO'";
cmdEmail.Connection = connEmail;
connEmail.Open();
drEmail = cmdEmail.ExecuteReader();
cmdORADetails.CommandType = CommandType.Text;
if (Type == "General")
{
MailHeader = "GI";
MailSubject = "GI";
RefNo = "Debit No.";
AgentCode = "Agent Code";
//cmdORADetails.CommandText = "SELECT NVL(crc_get_agent_code(to_char(T1.POL_POLICY_ID)),'D') as PPA_PTY_PARTY_CODE,T.DEBIT_NO,SUBSTR(T.STATUS,-59,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE,T4.CREATEDBY " +
// "FROM iims_acc.TEMP_GI_RECEIPT_BATCH T,T_POLICY T1, T_DEBIT_CREDIT_NOTE T3,iims_acc.TEMP_GI_RECEIPT_BATCH_ORG T4 " +
// "WHERE T.SERIAL = T4.SERIAL " +
// "AND T.DEBIT_NO=T3.DCN_DEBIT_CREDIT_NOTE_NO " +
// "AND T1.POL_POLICY_ID=T3.DCN_POL_POLICY_ID " +
// "AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-59,20),PPA_PTY_PARTY_CODE";
//15-08-2017
cmdORADetails.CommandText = " SELECT T2.PPA_PTY_PARTY_CODE,T.DEBIT_NO,SUBSTR(T.STATUS,-59,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE,T4.CREATEDBY " +
" FROM TEMP_GI_RECEIPT_BATCH T,T_POLICY T1, T_POLICY_PARTY T2, T_DEBIT_CREDIT_NOTE T3,TEMP_GI_RECEIPT_BATCH_ORG T4 " +
" WHERE T.SERIAL = T4.SERIAL " +
" AND T.DEBIT_NO=T3.DCN_DEBIT_CREDIT_NOTE_NO " +
" AND T1.POL_POLICY_ID=T3.DCN_POL_POLICY_ID " +
" AND T1.POL_POLICY_ID=T2.PPA_POL_POLICY_ID " +
" AND T2.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','HNB_BANK','DIRECT','DIR_SP','DIR_SPIND','BROKER','ASSU_STAFF','HNB_STAFF') " +
" AND T2.PPA_EFFECTIVE_END_DATE IS NULL " +
" AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-59,20),T2.PPA_PTY_PARTY_CODE";
//" AND T.POLICY_BRANCH = '" + Convert.ToString(Dts.Variables["Branch_code_name"].Value) + "' ORDER BY SUBSTR(T.STATUS,-59,20),T2.PPA_PTY_PARTY_CODE";
}
if (Type == "Life")
{
MailHeader = "LIFE";
MailSubject = "LIFE";
RefNo = "Policy No.";
AgentCode = "Agent Code";
//cmdORADetails.CommandText = "SELECT NVL(crc_get_agent_code(to_char(T1.POL_POLICY_ID)),'D') as PPA_PTY_PARTY_CODE,T.POLICY_NUMBER,SUBSTR(T.STATUS,-20,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE,T3.CREATEDBY " +
// "FROM iims_acc.TEMP_LIFE_RECEIPT_BATCH T,T_POLICY T1,iims_acc.TEMP_LIFE_RECEIPT_BATCH_ORG T3 WHERE T.SERIAL = T3.SERIAL " +
// "AND (T.POLICY_NUMBER=T1.POL_POLICY_NUMBER OR T.POLICY_NUMBER=T1.POL_PROPOSAL_NUMBER) " +
// "AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-20,20),PPA_PTY_PARTY_CODE";
//15-08-2017
cmdORADetails.CommandText = "SELECT T2.PPA_PTY_PARTY_CODE,T.POLICY_NUMBER,SUBSTR(T.STATUS,-20,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE,T3.CREATEDBY " +
"FROM TEMP_LIFE_RECEIPT_BATCH T,T_POLICY T1, T_POLICY_PARTY T2,TEMP_LIFE_RECEIPT_BATCH_ORG T3 " +
"WHERE T.SERIAL = T3.SERIAL AND (T.POLICY_NUMBER=T1.POL_POLICY_NUMBER OR T.POLICY_NUMBER=T1.POL_PROPOSAL_NUMBER) AND T1.POL_POLICY_ID=T2.PPA_POL_POLICY_ID " +
"AND T2.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','HNB_BANK','DIRECT','DIR_SP','DIR_SPIND','BROKER','ASSU_STAFF','HNB_STAFF') " +
"AND T2.PPA_EFFECTIVE_END_DATE IS NULL AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-20,20),T2.PPA_PTY_PARTY_CODE";
//12-07-2016
//cmdORADetails.CommandText = "SELECT T2.PPA_PTY_PARTY_CODE,T.POLICY_NUMBER,SUBSTR(T.STATUS,-20,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE " +
// "FROM TEMP_LIFE_RECEIPT_BATCH T,T_POLICY T1, T_POLICY_PARTY T2,TEMP_LIFE_RECEIPT_BATCH_ORG T3 " +
// "WHERE T.SERIAL = T3.SERIAL " +
// "AND T.POLICY_NUMBER=T1.POL_POLICY_NUMBER " +
// "AND T1.POL_POLICY_ID=T2.PPA_POL_POLICY_ID " +
// "AND T2.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','HNB_BANK','DIRECT','DIR_SP','DIR_SPIND','BROKER','ASSU_STAFF','HNB_STAFF') " +
// "AND T2.PPA_EFFECTIVE_END_DATE IS NULL " +
// "AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-20,20),T2.PPA_PTY_PARTY_CODE";
/////////////"AND T.POLICY_BRANCH = '" + Convert.ToString(Dts.Variables["Branch_code_name"].Value) + "' ORDER BY SUBSTR(T.STATUS,-20,20),T2.PPA_PTY_PARTY_CODE";
}
if (Type == "NonTCS")
{
MailHeader = "NonTCS";
MailSubject = "NonTCS";
RefNo = "Policy No.";
AgentCode = "Instrument No.";
//cmdORADetails.CommandText = " SELECT substr(T.NARRATION,0,instr(T.NARRATION,'-')-1 ) Narration,T.POLICY_NUMBER,SUBSTR(T.STATUS,-20,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE,T.TRANSACTION_CODE " +
// " FROM TEMP_LIFE_RECEIPT_BATCH_NONTCS T,temp_receipt_batch_nontcs_org T4 " + //TEMP_LIFE_RECEIPT_BATCH_NT_ORG T4 " +
// " WHERE T.SERIAL = T4.SERIAL " +
// " AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-20,20)";
// //" AND T.POLICY_BRANCH = '" + Convert.ToString(Dts.Variables["Branch_code_name"].Value) + "' ORDER BY SUBSTR(T.STATUS,-20,20)";
cmdORADetails.CommandText = " SELECT T.Transaction_Code, T.POLICY_NUMBER,SUBSTR(T.STATUS,-20,20) AS RV,T.AMOUNT,T.STATUS,T.CASH_ACCOUNT,T.INSTRUMENT_DATE,T4.CREATEDBY " +
" FROM iims_acc.TEMP_LIFE_RECEIPT_BATCH_NONTCS T,iims_acc.temp_receipt_batch_nontcs_org T4 " +
" WHERE T.SERIAL = T4.SERIAL " +
" AND T.POLICY_BRANCH = 'HDO' ORDER BY SUBSTR(T.STATUS,-20,20) ";
}
//connEmail.Close();
cmdORADetails.Connection = connORAGetData;
connORAGetData.Open();
drORADetails = cmdORADetails.ExecuteReader();
Details.Load(drORADetails);
//
if (Details.Rows.Count == 0)
{
ClearTable(Type);
return;
}
//if (drORADetails.HasRows)
//{
string test;
String UploadedUser = "";
// TODO: Add your code here
test = "<html><body><table>";
test += "<tr bgcolor=" + "#7C9CB6" + ">" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + AgentCode + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + RefNo + "</font></td>" + //"<td><font face=" + "Tahoma" + " size=" + "-1>Debit No.</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>Receipt No.</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>Amount</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>Status</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>Cash Acc.</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>IBT Date</font></td></tr>";
for (int i = 0; i < Details.Rows.Count; i++)
{
if (i % 2 == 0)
{
test += "<tr bgcolor=" + "#FFFFFF" + ">" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][0] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][1] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][2] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Convert.ToDouble(Details.Rows[i][3]).ToString("#,##0.00") + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][4] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][5] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][6] + "</font></td></tr>";
}
else
{
test += "<tr bgcolor=" + "#CCCCCC" + ">" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][0] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][1] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][2] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Convert.ToDouble(Details.Rows[i][3]).ToString("#,##0.00") + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][4] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][5] + "</font></td>" +
"<td><font face=" + "Tahoma" + " size=" + "-1>" + Details.Rows[i][6] + "</font></td></tr>";
}
}
UploadedUser = Details.Rows[0]["CREATEDBY"].ToString();
test += "</table></html></body>";
//Dts.Variables["Email"].Value = test.ToString();
MailMessage message = new MailMessage();
//MailAddress from = new MailAddress("*****@*****.**", "TCS Receipt Upload - " + MailHeader + "Testing Only");
MailAddress from = new MailAddress("*****@*****.**", "TCS Receipt Upload - " + MailHeader);
while (drEmail.Read())
{
MailAddress to = new MailAddress(drEmail["EMAIL"].ToString());
message.To.Add(to);
}
message.From = from;
//message.Subject = "TCS Receipt Upload - " + MailSubject + " Uploaded User - " + UploadedUser + "Testing Only";
message.Subject = "TCS Receipt Upload - " + MailSubject + " Uploaded User - " + UploadedUser;
message.IsBodyHtml = true;
//string BodyText;
message.Body = @test;
SmtpClient client = new SmtpClient("smtp2.hnbassurance.com");
client.Credentials = new System.Net.NetworkCredential("smtp2.hnbassurance.com", "Water@1234");
client.Send(message);
client.Dispose();
ClearTable(Type);
}
catch (Exception ex)
{
throw ex;
}
}
protected void DisposeCommand(OracleCommand cmd)
{
_conexion.DisposeCommand(cmd);
}
protected bool Actualizar(OracleCommand cmd)
{
return(_conexion.Actualizar(cmd));
}
//oracle连接
public void UpdateData()
{
System.Data.OracleClient.OracleTransaction sqltran;
//将事务绑定到连接对像
//init.DBConnect();
try
{
//writeLog.Write("" + dtSave.Count, "log");
using (OracleConnection connection = new OracleConnection(OracleHelper.GetConnectionstring()))
{
sqltran = connection.BeginTransaction();// ConnectDB.con.BeginTransaction();
for (int i = 0; i < dtSave.Count; i++)
{
OracleDataAdapter adapter = new OracleDataAdapter(selectStr[i], connection);
OracleCommandBuilder cmdBuilder = new OracleCommandBuilder(adapter);
try
{
adapter.SelectCommand = new OracleCommand(selectStr[i], ConnectDB.con, sqltran);
adapter.InsertCommand = cmdBuilder.GetInsertCommand();
adapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
if (dtSave[i].Select(null, null, DataViewRowState.Deleted).Length > 0)
{
adapter.Update(dtSave[i].Select(null, null, DataViewRowState.Deleted));
}
if (dtSave[i].Select(null, null, DataViewRowState.ModifiedCurrent).Length > 0)
{
adapter.Update(dtSave[i].Select(null, null, DataViewRowState.ModifiedCurrent));
}
if (dtSave[i].Select(null, null, DataViewRowState.Added).Length > 0)
{
adapter.Update(dtSave[i].Select(null, null, DataViewRowState.Added));
}
}
catch (Exception ex)
{
writeLog.Write("UpdateData处理：" + ex.Message, "log");
}
}
try
{
sqltran.Commit();
}
catch (Exception ex1)
{
sqltran.Rollback();
writeLog.Write("事务提交失败：" + ex1.Message, "log");
}
finally
{
connection.Close();
connection.Dispose();
}
}
for (int i = 0; i < strGraph.Count; i++)
{
//writeLog.Write(strGraph[i].ToString(), "log");
System.IO.FileStream fs = new System.IO.FileStream(strGraph[i].ToString(), System.IO.FileMode.OpenOrCreate);
byte[] blob = new byte[fs.Length];
fs.Read(blob, 0, blob.Length);
fs.Close();
strUpdateSQL = "update test_graph_result set GRAPH_RESULT = :Photo where GRAPH_RESULT_ID = '" + strGraphID[i] + "'";
OracleCommand oraCmd = new OracleCommand(strUpdateSQL, ConnectDB.con);
OracleParameter op = new OracleParameter("Photo", OracleType.Blob);
op.Value = blob;
oraCmd.Parameters.Add(op);
try
{
oraCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
writeLog.Write("UpdateDataIO处理：" + ex.Message, "log");
}
}
}
catch (System.Data.OracleClient.OracleException sqlex)
{
// sqltran.Rollback();
writeLog.Write("UpdateData_sqlex:" + sqlex.Message, "log");
//return sqlex.Message;
}
catch (Exception ex)
{
// sqltran.Rollback();
writeLog.Write("UpdateData_catch:" + ex.Message, "log");
//return ex.Message;
}
finally
{
sqltran = null;
}
}
protected DataTable EjecutarCmd(OracleCommand cmd)
{
DataTable tabla = _conexion.EjecutarCmd(cmd);
return(tabla);
}
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 testPackage 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 testPackage;");
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 testPackage 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 testPackage;");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
}
/// <summary>
/// Retorna uma lista de itens de notas de saída
/// </summary>
/// <param name="filial">Código da filial</param>
/// <param name="numeroNota">Número da Nota</param>
/// <param name="serieNota">Série da Nota</param>
/// <returns>listaItens</returns>
public List <E140IPVModel> PesquisarItensNotasFiscaisSaida(int filial, long numeroNota, string serieNota)
{
try
{
string sql = "Select A.SeqIpv as Sequencia, " +
" A.TnsPro as Transacao, " +
" A.CodPro || ' ' || A.CODDER as CodigoProduto, " +
" B.DesPro || ' ' || E.DESDER as DescricaoProduto, " +
" A.QtdFat as QtdeFaturada, " +
" 0 as QtdeDevolucao, " +
" TO_CHAR(A.PreUni, '999999990D99999') as PrecoUnitario, " +
" 'Selecione...' As MotivoDevolucao, " +
" 'Selecione...' As OrigemOcorrencia, " +
" TO_CHAR(A.PerDsc, '999999990D99') as PercDescUnit, " +
" TO_CHAR(A.PerIpi, '999999990D99') as PercIpi, " +
" TO_CHAR(A.VlrIpi, '999999990D99') as ValorIpi, " +
" TO_CHAR(A.VlrLiq, '999999990D99') as ValorLiquido, " +
" TO_CHAR(A.vlrics, '999999990D99') as ValorSt, " +
" TO_CHAR(A.VLRDZF + A.VLRPIT + A.VLRCRT, '999999990D99') as DescontoSuframa, " +
" B.CodFam as CodigoFamilia, " +
" A.VLRFRE AS ValorFrete, " +
" COALESCE((SELECT SUM(QTDDEV) " +
" FROM NWMS_PRODUCAO.N0203IPV SUBIPV " +
" INNER JOIN NWMS_PRODUCAO.N0203REG SUBREG " +
" ON SUBIPV.NUMREG = SUBREG.NUMREG " +
" WHERE SUBIPV.NUMNFV = " + numeroNota + " AND SUBREG.SITREG NOT IN (5, 7)" +
" AND SUBIPV.CODPRO = A.CodPro " +
" AND SUBIPV.CODDER = A.CodDer),0) AS QTDDEVSALDO " +
" From SAPIENS.E140IPV A, SAPIENS.E075PRO B, SAPIENS.E075DER E " +
" Where A.CodEmp = B.CodEmp " +
" And A.CodPro = B.CodPro " +
" AND A.CodEmp = E.CodEmp " +
" And A.CodPro = E.CodPro " +
" AND A.CODDER = E.CODDER " +
" And A.CodEmp = 1 " +
" And A.CodFil = " + filial +
" And A.CodSnf = " + "'" + serieNota + "'" +
" And A.NumNfv = " + numeroNota;
OracleConnection conn = new OracleConnection(OracleStringConnection);
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
List <E140IPVModel> listaItens = new List <E140IPVModel>();
E140IPVModel itemNota = new E140IPVModel();
while (dr.Read())
{
itemNota = new E140IPVModel();
itemNota.Sequencia = dr.GetInt32(0);
itemNota.Transacao = dr.GetString(1);
itemNota.CodigoProduto = dr.GetString(2);
itemNota.DescricaoProduto = dr.GetString(3);
itemNota.QtdeFaturada = dr.GetInt32(4);
itemNota.QtdeDevolucao = dr.GetInt32(5);
itemNota.PrecoUnitario = dr.GetString(6);
itemNota.MotivoDevolucao = dr.GetString(7);
itemNota.OrigemOcorrencia = dr.GetString(8);
itemNota.PercDescUnit = dr.GetString(9);
itemNota.PercIpi = dr.GetString(10);
itemNota.ValorIpi = dr.GetString(11);
itemNota.ValorLiquido = dr.GetString(12);
itemNota.ValorSt = dr.GetString(13);
itemNota.DescontoSuframa = dr.GetString(14);
itemNota.CodigoFamilia = dr.GetString(15);
itemNota.ValorFrete = dr.GetDecimal(16);
itemNota.SaldoDevolucao = Convert.ToInt32(dr["QTDDEVSALDO"]);
string codigoDep = string.Empty;
string DescricaoDep = string.Empty;
PesquisarCentroCustoItemNoRateio(1, filial, numeroNota, serieNota, itemNota.Sequencia, out codigoDep, out DescricaoDep);
if (!string.IsNullOrEmpty(codigoDep))
{
itemNota.Departamento = codigoDep;
itemNota.DescricaoDepartamento = DescricaoDep;
}
else
{
PesquisarCentroCustoItemNoProduto(1, itemNota.CodigoProduto, out codigoDep, out DescricaoDep);
if (!string.IsNullOrEmpty(codigoDep))
{
itemNota.Departamento = codigoDep;
itemNota.DescricaoDepartamento = DescricaoDep;
}
else
{
PesquisarCentroCustoItemNaFamilia(1, itemNota.CodigoFamilia, out codigoDep, out DescricaoDep);
itemNota.Departamento = codigoDep;
itemNota.DescricaoDepartamento = DescricaoDep;
}
}
listaItens.Add(itemNota);
}
dr.Close();
conn.Close();
return(listaItens);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Gets the last bill summary.
/// </summary>
/// <param name="accountNumber">The account number.</param>
/// <returns></returns>
public static CH_CR_TERMDTO GetSummary(string accountNumber)
{
CH_CR_TERMDTO objTermDTO = new CH_CR_TERMDTO();
try
{
using (OracleConnection conn = new OracleConnection(common.GetConnectionstring()))
{
conn.Open();
//string sql = "SELECT * from BOBVW_BILLSUMMARY where STAT_DATE =(select max(C.STAT_DATE) from cr_term C where C.cr_account_nbr=:cr_account_nbr) and cr_account_nbr=:cr_account_nbr";
string sql = "SELECT * from BOBVW_BILLSUMMARY where cr_account_nbr=:cr_account_nbr";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleParameter p1 = new OracleParameter("cr_account_nbr", OracleDbType.Varchar2, 30);
p1.Value = accountNumber;
cmd.Parameters.Add(p1);
OracleDataReader dr = cmd.ExecuteReader();
if (dr == null)
{
return(null);
}
while (dr.Read())
{
if (dr[BILLED_OPENING_BAL] != null & Convert.ToString(dr[BILLED_OPENING_BAL]) != "")
{
objTermDTO.BILLED_OPENING_BAL = Convert.ToDouble(dr[BILLED_OPENING_BAL]);
}
else
{
objTermDTO.BILLED_OPENING_BAL = 0;
}
if (dr[col_Total_Amount_Due] != null & Convert.ToString(dr[col_Total_Amount_Due]) != "")
{
objTermDTO.Total_Amount_Due = Convert.ToDouble(dr[col_Total_Amount_Due]);
}
else
{
objTermDTO.Total_Amount_Due = 0;
}
if (dr[TOTAL_CREDITS] != null & Convert.ToString(dr[TOTAL_CREDITS]) != "")
{
objTermDTO.TOTAL_CREDITS = Convert.ToDouble(dr[TOTAL_CREDITS]);
}
else
{
objTermDTO.BILLED_OPENING_BAL = 0;
}
if (dr[TOTAL_DEBITS] != null & Convert.ToString(dr[TOTAL_DEBITS]) != "")
{
objTermDTO.TOTAL_DEBITS = Convert.ToDouble(dr[TOTAL_DEBITS]);
}
else
{
objTermDTO.BILLED_OPENING_BAL = 0;
}
// objTermDTO.Total_Outstanding = dr[col_Total_Outstanding] == null ? 0 : Convert.ToDouble(dr[col_Total_Outstanding]);
objTermDTO.Minimum_Amount_Due = dr[col_Billed_Min_Due] == null ? 0 : Convert.ToDouble(dr[col_Billed_Min_Due]);
objTermDTO.Payment_Due_Date = Convert.ToDateTime(dr[col_Payment_Due_Date]);
objTermDTO.Stat_Date = Convert.ToDateTime(dr[STAT_DATE]);
if (dr[col_Amount_Received] != null & Convert.ToString(dr[col_Amount_Received]) != "")
{
objTermDTO.Amount_Received = Convert.ToDouble(dr[col_Amount_Received]);
}
else
{
objTermDTO.Amount_Received = 0;
}
if (objTermDTO.Paymnet_Received_Date != null && Convert.ToString(objTermDTO.Paymnet_Received_Date) != "" && Convert.ToString(objTermDTO.Paymnet_Received_Date) != "01-Jan-01 12:00:00 AM")
{
objTermDTO.Paymnet_Received_Date = Convert.ToDateTime(dr[col_Payment_Received_Date]);
}
else
{
objTermDTO.Paymnet_Received_Date = DateTime.MinValue;
}
//objTermDTO.Closing_Balance = dr[col_Closing_Balance] == null ? 0 : Convert.ToDouble(dr[col_Closing_Balance]);
//objTermDTO.Opening_Balance = dr[col_Opening_Balance] == null ? 0 : Convert.ToDouble(dr[col_Opening_Balance]);
//objTermDTO.Earned_For_The_Month = dr[col_Earned_For_The_Month] == null ? 0 : Convert.ToDouble(dr[col_Earned_For_The_Month]);
//objTermDTO.Redeemed_For_The_Month = dr[col_Redeemed_For_The_Month] == null ? 0 : Convert.ToDouble(dr[col_Redeemed_For_The_Month]);
//objTermDTO.Points_Expiring = dr[col_Redeemed_For_The_Month] == null ? 0 : Convert.ToDouble(dr[col_Redeemed_For_The_Month]); //Query
}
}
}
catch (Exception ex)
{
string str = ex.Message;
common.logger.Debug(errorGenerated + " Summary : " + ex.Message.ToString());
return(null);
}
return(objTermDTO);
}
/// <summary>
/// 根据连接实体得到数据表
/// </summary>
/// <param name="linkID"></param>
/// <returns></returns>
public DataTable GetDataTable(RoadFlow.Data.Model.DBConnection dbconn, string sql, IDataParameter[] parameterArray = null)
{
if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty())
{
return(null);
}
DataTable dt = new DataTable();
switch (dbconn.Type)
{
#region SqlServer
case "SqlServer":
using (SqlConnection conn = new SqlConnection(dbconn.ConnectionString))
{
try
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
if (parameterArray != null && parameterArray.Length > 0)
{
cmd.Parameters.AddRange((SqlParameter[])parameterArray);
}
using (SqlDataAdapter dap = new SqlDataAdapter(cmd))
{
dap.Fill(dt);
}
}
}
catch (SqlException ex)
{
Platform.Log.Add(ex);
}
}
break;
#endregion
#region Oracle
case "Oracle":
using (OracleConnection conn = new OracleConnection(dbconn.ConnectionString))
{
try
{
conn.Open();
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
if (parameterArray != null && parameterArray.Length > 0)
{
cmd.Parameters.AddRange((OracleParameter[])parameterArray);
}
using (OracleDataAdapter dap = new OracleDataAdapter(cmd))
{
dap.Fill(dt);
}
}
}
catch (OracleException ex)
{
Platform.Log.Add(ex);
}
}
break;
#endregion
}
return(dt);
}
public async Task <JsonResult> DeleteRowAsync(string connectionName, string tableName, string id, int page, int tableRowIndx, string searchFields)
{
var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName);
var connectionString = Util.GetConnectionString(customConnection);
var tableDataDict = new PagedData {
Data = new Dictionary <int, Row>()
};
var searchFieldsArray = JsonConvert.DeserializeObject <List <SearchFieldInfo> >(searchFields);
var columnList = await _util.GetColumnInfo(connectionName, tableName);
tableDataDict = searchFieldsArray.Any() ? await _util.GetTableDataListSearch(connectionName, tableName, 0, searchFieldsArray[0].Name, searchFieldsArray[0].Value) : await _util.GetTableDataList(columnList, connectionName, tableName, page);
var row = tableDataDict.Data[tableRowIndx];
var tableColumnInfosJson = row.TableColumnInfosJson;
var whereStmt = Util.FindUniqueRowWhereStmt(id, columnList);
var deleteSqlStmt = "delete " + tableName + " where " + whereStmt;
if (string.IsNullOrEmpty(id))
{
var whereColumnListStmt = "";
var oldColumnList = JsonConvert.DeserializeObject <List <TableColumnInfo> >(tableColumnInfosJson).ToArray();
var builder = new System.Text.StringBuilder();
builder.Append(whereColumnListStmt);
for (int j = 0; j < oldColumnList.Count(); j++)
{
builder.Append(oldColumnList[j].Name + "='" + oldColumnList[j].Value + "' and ");
}
whereColumnListStmt = builder.ToString();
whereColumnListStmt = whereColumnListStmt.TrimEnd(' ', 'd', 'n', 'a');
deleteSqlStmt = "delete " + tableName + " where " + whereColumnListStmt;
}
var sessionHistorySql = new SessionSqlHistory
{
EventDate = DateTime.Now,
SqlText = deleteSqlStmt,
BasicSqlText = deleteSqlStmt
};
using (var oconn = new OracleConnection(connectionString))
{
oconn.Open();
using (var cmd = new OracleCommand
{
Connection = oconn,
CommandText = deleteSqlStmt,
CommandType = CommandType.Text
})
{
var result = cmd.ExecuteNonQuery();
}
}
await _context.SessionSqlHistory.AddAsync(sessionHistorySql);
await _context.SaveChangesAsync();
return(Json(true));
}
