private void Btn_guardar_Click(object sender, EventArgs e)
{
string fecha = DateTime.Now.ToString("yyyy-MM-dd");
// MessageBox.Show(fecha);
string scodigoProveedor = "";
bool validacionProveedor = false;
//validacion de combobox vacio
if (Cbo_proveedor.SelectedItem != null)
{
string snombreProveedor = Cbo_proveedor.SelectedItem.ToString();
//consulta el codigo del proveedor obteniendo el texto del combobox con el nombre del proveedor
try
{
OdbcCommand sql = new OdbcCommand("Select PK_IdProveedores from tbl_proveedores where nombre_proveedor = '" + snombreProveedor + "' and Estado_proveedor = 1", conexion.conectar());
OdbcDataReader almacena = sql.ExecuteReader();
while (almacena.Read() == true)
{
scodigoProveedor = almacena.GetString(0);
}
almacena.Close();
conexion.cerrarConexion();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
validacionProveedor = true;
MessageBox.Show("Debe seleccionar un proveedor para el registro de la orden de compra");
Cbo_proveedor.Focus();
}
if (validacionProveedor == false)
{
//validar en el grid que no hayan cantidades vacias
if (validacionColumnaCantidad())
{
MessageBox.Show("Debe ingresar una cantidad de productos para la cotizacion");
}
else
{
//valida que hayan datos en el grid para poder guardarlos en la bd
if (dgv_ordenCompra.Rows.Count > 0)
{
try
{
string scodigoOrdenCompra = Txt_noOrdenCompra.Text;
string stotalOrdenCompra = Txt_total.Text;
OdbcCommand sql = new OdbcCommand("INSERT INTO tbl_orden_compra_encabezado (PK_IdComprasEncabezado, PK_IdProveedores, fecha_pedido, total)" +
" VALUES( " + scodigoOrdenCompra + ", " + scodigoProveedor + ", '" + fecha + "' , " + stotalOrdenCompra + " )", conexion.conectar());
sql.ExecuteNonQuery();
conexion.cerrarConexion();
try
{
sql = new OdbcCommand("INSERT INTO tbl_compras (PK_IdComprasEncabezado, fecha_compra, fecha_modificacion, Tracking_compra, estado_compra)" +
" VALUES( " + scodigoOrdenCompra + ", '" + fecha + "', '" + fecha + "' , 'En espera de respuesta por el proveedor', '1')", conexion.conectar());
sql.ExecuteNonQuery();
conexion.cerrarConexion();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
try
{
foreach (DataGridViewRow row in dgv_ordenCompra.Rows)
{
try
{
string scodigoOrdenCompra = Txt_noOrdenCompra.Text;
if (row.Cells[0].Value != null)
{
OdbcCommand sql = new OdbcCommand("INSERT INTO tbl_orden_compra_detalle (PK_IdComprasEncabezado, PK_IdProducto, Precio, Cantidad)" +
" VALUES ( " + scodigoOrdenCompra + ", " + Convert.ToString(row.Cells[0].Value) + ", " + Convert.ToString(row.Cells[3].Value) + ", " + Convert.ToString(row.Cells[4].Value) + " )", conexion.conectar());
sql.ExecuteNonQuery();
conexion.cerrarConexion();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
MessageBox.Show("Orden de compra con el codigo: " + Txt_noOrdenCompra.Text + " almacenada exitosamente!");
limpiarForm();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
MessageBox.Show("No se puede generar una orden de compra sin productos en listados");
}
}
}
}
void Button1Click(object sender, EventArgs e)
{
// add
OdbcCommand cmd0 = new OdbcCommand();
OdbcDataReader reader0 = null;
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма - прервано..");
cnn.Close();
return;
}
cmd0.Connection = cnn;
decimal id_cnt = 0;
int i = 0; // можем создать 100 систем сбора, берем ближайшее! свободное! целое число
for (i = 1; i <= 100; i++)
{
cmd0.CommandText = "select count(*) from sys_tbllst where upper(table_name) like 'DA_V_LST_" + i.ToString() + "'";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7) { continue; }
if (reader0.HasRows)
{
while (reader0.Read())
{
id_cnt = reader0.GetDecimal(0);
}
}
reader0.Close();
if (id_cnt == 0)
{
DialogResult result = MessageBox.Show("Создать Сегмент Сбора с номером = " + i.ToString(), "?", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Cancel)
{
break;
}
cmd0.CommandType = System.Data.CommandType.StoredProcedure;
cmd0.Parameters.Clear();
cmd0.CommandText = "{call RSDUADMIN.DA_CREATE_NEW_SEGMENT_P( ? )}";
//PROCEDURE RSDUADMIN.DA_create_new_segment_p (pTOP_ID NUMBER)
OdbcParameter param = new OdbcParameter();
param.Direction = System.Data.ParameterDirection.Input;
param.OdbcType = OdbcType.Numeric;
param.ParameterName = "pTOP_ID";
param.Value = i;
//param.Size = 1024 ;
cmd0.Parameters.Add(param);
try { cmd0.ExecuteNonQuery(); }
catch (Exception ex11) { AddLogString("Ошибка вызова процедуры =" + ex11.Message); }
AddLogString("Проверьте список Систем Сбора !");
break;
}
}
// перечитываем из базы
Button3Click(sender, e);
}
protected override OpResult _Store(Sale _obj)
{
if (_obj == null)
{
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.ObjectIsNull, _obj, "Sale object cannot be deleted as it is null"));
}
bool is_creating = !Exists(_obj);
if (is_creating)
{
RepositoryMgr.MiscNumberMgr.SaveInvoiceNumber(_obj.InvoiceNumber);
}
string query = "";
if (_obj.InvoiceType != null)
{
if (_obj.InvoiceType.IsProfessional)
{
query = GetQuery_StoreProfessionalSaleLines(_obj);
}
else if (_obj.InvoiceType.IsTimeBilling)
{
query = GetQuery_StoreTimeBillingSaleLines(_obj);
}
else if (_obj.InvoiceType.IsService)
{
query = GetQuery_StoreServiceSaleLines(_obj);
}
else if (_obj.InvoiceType.IsItem)
{
query = GetQuery_StoreItemSaleLines(_obj);
}
else if (_obj.InvoiceType.IsMisc)
{
query = GetQuery_StoreMiscellaneousSaleLines(_obj);
}
}
if (string.IsNullOrEmpty(query))
{
if (is_creating)
{
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.CreateFailedOnCriteria, _obj, "Sale object does not have any SaleLines"));
}
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.UpdateFailedOnCriteria, _obj, "Sale object does not have any SaleLines"));
}
DbCommand cmdSQLInsert = new OdbcCommand(query, (OdbcConnection)DbMgr.DbConnection);
DbTransaction myTrans = DbMgr.DbConnection.BeginTransaction();
try
{
cmdSQLInsert.CommandText = query;
cmdSQLInsert.Transaction = myTrans;
cmdSQLInsert.ExecuteNonQuery();
myTrans.Commit();
}
catch (OdbcException ex)
{
myTrans.Rollback();
Log(ex.Message);
Console.WriteLine(ex.ToString());
if (is_creating)
{
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.CreatedWithException, _obj, ex.ToString()));
}
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.UpdatedWithException, _obj, ex.ToString()));
}
catch (Exception ex)
{
Log(ex.Message);
Console.WriteLine(ex.ToString());
if (is_creating)
{
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.CreatedWithException, _obj, ex.ToString()));
}
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.UpdatedWithException, _obj, ex.ToString()));
}
_obj.FromDb = true;
if (is_creating)
{
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.Created, _obj));
}
return(OpResult.NotifyStoreAction(OpResult.ResultStatus.Updated, _obj));
}
private int?edit()
{
try
{
List <string> images = new List <string>();
string moicImg = "";
try
{
for (int i = 0; i < int.Parse(hcfCount.Value); i++)
{
HttpPostedFile file = Request.Files["image" + (i + 1).ToString()];
//check file was submitted
if (file != null && file.ContentLength > 0)
{
string fname = Guid.NewGuid().ToString() + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath(Path.Combine(ConfigurationManager.AppSettings["Files"], fname)));
images.Add(fname);
}
}
HttpPostedFile file2 = Request.Files["moicImg"];
moicImg = Guid.NewGuid().ToString() + System.IO.Path.GetExtension(file2.FileName);
file2.SaveAs(Server.MapPath(Path.Combine(ConfigurationManager.AppSettings["Files"], moicImg)));
}
catch (Exception ex)
{
StackTrace st = new StackTrace(ex, true);
Helper.logError(ex);
return(-3);
}
Guid id = Guid.Parse(ViewState["id"].ToString());
OdbcConnection con1 = new OdbcConnection(ConfigurationManager.AppSettings["connectionStringReal"]);
con1.Open();
OdbcCommand command = new OdbcCommand(@"UPDATE ArtWorkRequest set deptName= ?,
deptNID=?,
title=?,
email=?,
artist=?,
artist1=?,
artist2=?,
Mobile=?,
fax=?,
postOffice=?,
PostBox=?,
typeID=?,
episodeNo=?,
dimentions=?,
material=?,
prodHDate=?,
prodADate=?,
deptCity=?,
note=''
WHERE id =?" , con1);
command.Parameters.Add(new OdbcParameter("@deptName", txtDepName.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@deptNID", txtDepNID.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@title", txtTitle.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@email", txtDepEmail.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@authors", txtArtist.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@authors", txtArtist1.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@authors", txtArtist2.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@mobile", txtDepPhone.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@fax", txtDepFax.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@postOffice", txtDepZip.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@PostBox", txtDepPost.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@typeID", int.Parse(typeID.Items[typeID.SelectedIndex].Value)));
command.Parameters.Add(new OdbcParameter("@episodeNo", txtepisodeNo.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@totalNoEpisode", txtDimentions.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@txtMaterial", txtMaterial.Value.Trim()));
command.Parameters.Add(new OdbcParameter("@Hdate", hijry.Items[hijry.SelectedIndex].Value));
command.Parameters.Add(new OdbcParameter("@GDate", gerg.Items[gerg.SelectedIndex].Value));
command.Parameters.Add(new OdbcParameter("@deptCity", deptCity.Items[deptCity.SelectedIndex].Value));
command.Parameters.Add(new OdbcParameter("@id", id.ToString()));
command.ExecuteNonQuery();
foreach (string image in images)
{
OdbcCommand command2 = new OdbcCommand(@"insert into dbo.ImagesRequest (
ImageID,
RefID,
TYPE )
values (?,?,?)" , con1);
command2.Parameters.Add(new OdbcParameter("@ImageID", image));
command2.Parameters.Add(new OdbcParameter("@RefID", id.ToString()));
command2.Parameters.Add(new OdbcParameter("@TYPE", 4));// for artWork Type
command2.ExecuteNonQuery();
}
OdbcCommand command3 = new OdbcCommand(@"insert into dbo.ImagesRequest (
ImageID,
RefID,
TYPE )
values (?,?,?)" , con1);
command3.Parameters.Add(new OdbcParameter("@ImageID", moicImg));
command3.Parameters.Add(new OdbcParameter("@RefID", id.ToString()));
command3.Parameters.Add(new OdbcParameter("@TYPE", 41));// for Moic image in artWork Type
command3.ExecuteNonQuery();
return(1);
}
catch (Exception ex)
{
StackTrace st = new StackTrace(ex, true);
Helper.logError(ex);
return(-2);
}
}
private void btn_aceptar_Click(object sender, EventArgs e)
{
if (txt_contraseña.Text == "" || txt_usuario.Text == "" || cmb_perfil.SelectedIndex == -1)
{
MessageBox.Show("Llenar todos los campos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (cmb_perfil.Text.ToString() == "Administrador" || cmb_perfil.Text.ToString() == "Trabajador")
{
try
{
OdbcConnection con = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd1 = new OdbcCommand("insert into Usuario values ('" + txt_usuario.Text + "',null,null,'" + txt_contraseña.Text + "','" + cmb_perfil.Text.ToString() + "',1)", con);
con.Open(); //abre la conexion
cmd1.ExecuteNonQuery(); //ejecuta el query
con.Close(); //cierra la conexion
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("insert into Trabajador values ('" + txt_usuario.Text + "','" + txt_usuario.Text + "')", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
MessageBox.Show("Usuario creado exitosamente", "Usuario creado", MessageBoxButtons.OK);
txt_contraseña.Text = "";
txt_usuario.Text = "";
}
catch (Exception ex)
{
//MessageBox.Show("El nombre de usuario ya esta en uso, utilice otro nombre", "Error al crear", MessageBoxButtons.OK, MessageBoxIcon.Error);
MessageBox.Show(ex.ToString());
}
}
else if (cmb_perfil.Text.ToString() == "Personalizado")
{
chb_produtos.Enabled = true;
chb_traslados.Enabled = true;
chb_reportes.Enabled = true;
chb_graficas.Enabled = true;
chb_confAdmin.Enabled = true;
chb_confTrabajador.Enabled = true;
OdbcConnection con1 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd1 = new OdbcCommand("insert into Perfil values ('" + cmb_perfil.Text.ToString() + txt_usuario.Text + "',0,0,0,0,0,0,2)", con1);
con1.Open(); //abre la conexion
cmd1.ExecuteNonQuery(); //ejecuta el query
con1.Close(); //cierra la conexion*/
OdbcConnection con = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd = new OdbcCommand("insert into Usuario values ('" + txt_usuario.Text + "',null,null,'" + txt_contraseña.Text + "','" + cmb_perfil.Text.ToString() + "" + txt_usuario.Text + "',1)", con);
con.Open(); //abre la conexion
cmd.ExecuteNonQuery(); //ejecuta el query
con.Close(); //cierra la conexion
OdbcConnection con3 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd3 = new OdbcCommand("insert into Trabajador values ('" + txt_usuario.Text + "','" + txt_usuario.Text + "')", con3);
con3.Open(); //abre la conexion
cmd3.ExecuteNonQuery(); //ejecuta el query
con3.Close(); //cierra la conexion*/
MessageBox.Show("Usuario creado exitosamente", "Usuario creado", MessageBoxButtons.OK);
if (chb_produtos.Checked == true)
{
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("update Perfil set Productos = '1' where Tipo='" + cmb_perfil.Text.ToString() + txt_usuario.Text + "' ", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
}
if (chb_traslados.Checked == true)
{
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("update Perfil set Traslados = '1' where Tipo='" + cmb_perfil.Text.ToString() + txt_usuario.Text + "' ", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
}
if (chb_reportes.Checked == true)
{
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("update Perfil set Reportes = '1' where Tipo='" + cmb_perfil.Text.ToString() + txt_usuario.Text + "' ", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
}
if (chb_graficas.Checked == true)
{
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("update Perfil set Graficas = '1' where Tipo='" + cmb_perfil.Text.ToString() + txt_usuario.Text + "' ", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
}
if (chb_confAdmin.Checked == true)
{
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("update Perfil set Configuracion1 = '1' where Tipo='" + cmb_perfil.Text.ToString() + txt_usuario.Text + "' ", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
}
if (chb_confTrabajador.Checked == true)
{
OdbcConnection con2 = new OdbcConnection(ConnStr); //varibale para llamar la conexion ODBC
OdbcCommand cmd2 = new OdbcCommand("update Perfil set Configuracion2 = '1' where Tipo='" + cmb_perfil.Text.ToString() + txt_usuario.Text + "' ", con2);
con2.Open(); //abre la conexion
cmd2.ExecuteNonQuery(); //ejecuta el query
con2.Close(); //cierra la conexion*/
}
}
}
}
private void Btn_guardar_Click(object sender, EventArgs e)
{
string sNombre = txtNombre.Text;
string sDescripcion = txtDescripcion.Text;
string sMarca = cboMarca.Text;
if (boton_ingreso == true)
{
if (txtNombre.Text.Trim() == "" || txtDescripcion.Text.Trim() == "" || cboMarca.Text.Trim() == "")
{
MessageBox.Show("Faltan campos por llenar");
}
else
{
try
{
string sInsertar = "INSERT INTO tbl_modelo(nombre, descripcion, cod_marca, estado) " +
"VALUES ('" + sNombre
+ "', '" + sDescripcion
+ "', '" + lIdMarca[cboMarca.SelectedIndex] + "', '1')";
OdbcCommand sqlInsertar = new OdbcCommand(sInsertar, con);
sqlInsertar.ExecuteNonQuery();
MessageBox.Show("Marca ingresada Exitosamente");
dgv_clientes.Rows.Clear();
limpiarForm();
ActualizarGrid();
sInsertar = "INSERT INTO tbl_bitacora_seguridad (PK_idUsuario, " +
"accion, " +
"fecha, " +
"hora, " +
"IP) " +
"VALUES(" + sIdUsuario + "" +
",'Inserto un modelo: " + sNombre +
"','" + DateTime.Now.ToString("yyy/MM/dd") + "'" +
",'" + DateTime.Now.ToString("hh:mm:ss") + "'" +
",'" + myIP + "')";
sqlInsertar = new OdbcCommand(sInsertar, con);
sqlInsertar.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error al insertar una marca" + ex.Message);
MessageBox.Show("Ocurrio un error, intentelo de nuevo");
}
tc_Clientes.SelectedTab = tp_datos;
}
}
else if (boton_modificar == true)
{
if (txtNombre.Text.Trim() == "" || txtDescripcion.Text.Trim() == "" || cboMarca.Text.Trim() == "")
{
MessageBox.Show("Faltan campos por llenar");
}
else
{
string sEstado;
if (rdb_actio.Checked)
{
sEstado = "1";
}
else
{
sEstado = "0";
}
try
{
string sModificarCliente = "UPDATE tbl_modelo SET nombre = '" + sNombre
+ "', descripcion = '" + sDescripcion
+ "', cod_marca = '" + lIdMarca[cboMarca.SelectedIndex]
+ "', estado = '" + sEstado
+ "' WHERE PK_idModelo = '" + txtCodigo.Text + "'; ";
OdbcCommand sqlModificar = new OdbcCommand(sModificarCliente, con);
sqlModificar.ExecuteNonQuery();
MessageBox.Show("Modelo modificada exitosamente");
dgv_clientes.Rows.Clear();
limpiarForm();
ActualizarGrid();
sModificarCliente = "INSERT INTO tbl_bitacora_seguridad (PK_idUsuario, " +
"accion, " +
"fecha, " +
"hora, " +
"IP) " +
"VALUES(" + sIdUsuario + "" +
",'modifico un modelo: " + txtCodigo.Text +
"','" + DateTime.Now.ToString("yyy/MM/dd") + "'" +
",'" + DateTime.Now.ToString("hh:mm:ss") + "'" +
",'" + myIP + "')";
sqlModificar = new OdbcCommand(sModificarCliente, con);
sqlModificar.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error en la modificacion de modelo: " + ex.Message);
MessageBox.Show("Ocurrio un error, intentelo de nuevo");
}
tc_Clientes.SelectedTab = tp_datos;
}
}
else if (boton_eliminar == true)
{
try
{
int iCodigo = Convert.ToInt32(txtCodigo.Text);
string sEliminar = "UPDATE tbl_modelo SET estado = '0' WHERE PK_idModelo = '" + iCodigo + "'; ";
OdbcCommand sqlEliminar = new OdbcCommand(sEliminar, con);
sqlEliminar.ExecuteNonQuery();
MessageBox.Show("Modelo eliminado Exitosamente");
dgv_clientes.Rows.Clear();
limpiarForm();
ActualizarGrid();
sEliminar = "INSERT INTO tbl_bitacora_seguridad (PK_idUsuario, " +
"accion, " +
"fecha, " +
"hora, " +
"IP) " +
"VALUES(" + sIdUsuario + "" +
",'Elimino un modelo: " + iCodigo +
"','" + DateTime.Now.ToString("yyy/MM/dd") + "'" +
",'" + DateTime.Now.ToString("hh:mm:ss") + "'" +
",'" + myIP + "')";
sqlEliminar = new OdbcCommand(sEliminar, con);
sqlEliminar.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error en la eliminacion del punto de modelo: " + ex.Message);
MessageBox.Show("Ocurrio un error, intentelo de nuevo");
}
tc_Clientes.SelectedTab = tp_datos;
}
}
private void button1_Click(object sender, EventArgs e)
{
ConexionCapaDatos cone = new ConexionCapaDatos();
double sueldoM;
double sueldoD;
string saldo;
string dinero = "";
try {
try{
string auxi;
int diastxt = Convert.ToInt32(textBox6.Text);
if (btn1 == 1 && btn == 1 && diastxt <= 31 && diastxt > 0)
{
////
string cadenaB = "SELECT tE.ID_empleado, SUM(tCR.importe) AS total" +
" FROM tbl_empleados tE" +
" INNER JOIN tbl_empleadoconcepto tEC ON" +
" tE.ID_Empleado = tEC.ID_Empleado" +
" INNER JOIN tbl_conceptosretributivos tCR ON" +
" tCR.ID_ConceptosR = tEC.ID_ConceptosR" +
" WHERE tCR.tipo = 'ABONO' AND tE.ID_empleado=" + ID_EMP +
" group by 1;";
OdbcCommand cmd3 = new OdbcCommand(cadenaB, cone.cnxOpen());
OdbcDataReader leer3 = cmd3.ExecuteReader();
while (leer3.Read())
{
bono = leer3.GetDouble(1);
}
cone.cnxClose();
string cadenaA = "SELECT tbl_empleados.ID_empleado ,tbl_empleados.nombre,tbl_contratos.salario " +
"FROM tbl_empleados " +
"INNER JOIN tbl_contratos ON tbl_empleados.ID_contrato = tbl_contratos.ID_contrato WHERE ID_Empleado=" + ID_EMP + ";";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer = cmd1.ExecuteReader();
while (leer.Read())
{
saldo = leer.GetString(2);
sueldo = Convert.ToDouble(saldo);
switch (mescmx)
{
case (1):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (2):
sueldoM = (sueldo / 28);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (3):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (4):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (5):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (6):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (7):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (8):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (9):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (10):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (11):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (12):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
}
}
cone.cnxClose();
auxi = String.Format("{0:0.00}", sueldo);
string result = auxi.Replace(",", ".");
DIA = textBox6.Text;
string cadena = "UPDATE `Percepciones` SET `Dias` = " + "'" + DIA + "'" + ", `Total` = " + "'" + result + "'" + " WHERE (Percepciones.ID_Percepcion=" +
"\"" + ID_PER + "\"" + " and Percepciones.ID_Empleado=" + ID_EMP + ");";
OdbcCommand cmd = new OdbcCommand(cadena, cone.cnxOpen());
cmd.ExecuteNonQuery();
cone.cnxClose();
DataTable dt = new DataTable();
cadena = "SELECT ID_Empleado, ID_Percepcion, Total, Dias FROM Percepciones" +
" WHERE ID_Percepcion=" + "\"" + ID_PER + "\"" + ";";
OdbcDataAdapter dta = new OdbcDataAdapter(cadena, cone.cnxOpen());
DataSet dst = new DataSet();
dta.Fill(dst);
dt = dst.Tables[0];
dataGridView1.DataSource = dt;
MessageBox.Show("Actualizado");
cone.cnxClose();
}
else
{
MessageBox.Show("Debe usar primero el boton de Actualizar\n o Los dias no deben superar los 31 ni menor a 1");
}
}
catch (Exception ex)
{
MessageBox.Show("HUBO UN ERROR \n" + ex);
}
}
catch (OdbcException ex)
{
MessageBox.Show("ERROE EN LA BASE DE DATOS \n" + ex);
}
}
protected void updateMember(Membre member)
{
DataSet ds = null;
Connexion c = new Connexion(_ConnectionString);
c.Open();
ds = c.exeRequette("select * from Clients WHERE id_client ='" + member.ID_CLIENTA + "'");
c.Close();
if (((int)ds.Tables[0].Rows.Count) != 1)
{
member = new Membre();
}
else
{
OdbcConnection c3 = new OdbcConnection(_ConnectionString);
OdbcCommand commande = new OdbcCommand("UPDATE Clients SET civilite= ?,nom_client=?, prenom_client= ?, adresse_client=?, postal_client=?, ville_client=?, tel_client=?, id_client=?, pass_client=?, société_client= ?, pays_client=? WHERE id_client= ?", c3);
#region parametres odbc
OdbcParameter param0 = new OdbcParameter("", DbType.String);
param0.Value = member.CIVILITE;
commande.Parameters.Add(param0);
OdbcParameter param1 = new OdbcParameter("", DbType.String);
param1.Value = member.NOM;
commande.Parameters.Add(param1);
OdbcParameter paramIdClient = new OdbcParameter("", DbType.String);
paramIdClient.Value = member.PRENOM;
commande.Parameters.Add(paramIdClient);
OdbcParameter param2 = new OdbcParameter("", DbType.String);
param2.Value = member.ADRESSE;
commande.Parameters.Add(param2);
OdbcParameter param3 = new OdbcParameter("", DbType.String);
param3.Value = member.CODE_POSTAL;
commande.Parameters.Add(param3);
OdbcParameter param4 = new OdbcParameter("", DbType.String);
param4.Value = member.VILLE;
commande.Parameters.Add(param4);
OdbcParameter param5 = new OdbcParameter("", DbType.String);
param5.Value = member.TEL;
commande.Parameters.Add(param5);
OdbcParameter param50 = new OdbcParameter("", DbType.String);
param50.Value = member.ID_CLIENT;
commande.Parameters.Add(param50);
OdbcParameter param6 = new OdbcParameter("", DbType.String);
param6.Value = member.PASSWORD;
commande.Parameters.Add(param6);
OdbcParameter param7 = new OdbcParameter("", DbType.String);
param7.Value = member.SOCIETE;
commande.Parameters.Add(param7);
OdbcParameter param8 = new OdbcParameter("", DbType.String);
param8.Value = member.PAYS;
commande.Parameters.Add(param8);
OdbcParameter param9 = new OdbcParameter("", DbType.String);
param9.Value = member.ID_CLIENTA;
commande.Parameters.Add(param9);
#endregion
c3.Open();
commande.ExecuteNonQuery();
c3.Close();
save_Quote();
}
}
public int MySQLquery(string[] sa, string MySQLcmd, int btn)
{
int r = 0;
OdbcCommand MyCommand = new OdbcCommand(MySQLcmd, this.MySQLconnection);
try
{
if (this.MySQLconnection.State == ConnectionState.Closed)
{
MessageBox.Show(StringsDictionary.KeyValue("FormDB_MySQLquery_closed", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_closed_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
#region if btn=1
if (btn == 1)
{
try
{
r = MyCommand.ExecuteNonQuery();
this.toolStripStatusLabel1.Text =
StringsDictionary.KeyValue("FormDB_insertrecord", this.culture);
return(r);
}
catch (System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
#endregion if btn=1
#region if btn=2
else if (btn == 2)
{
try
{
MyDataReader = MyCommand.ExecuteReader();
r = MyDataReader.RecordsAffected;
liste.Clear();
while (MyDataReader.Read())
{
liste.Add("prefix:\t\t " + MyDataReader.GetString(0) + "/" + MyDataReader.GetByte(1).ToString());
liste.Add("netname:\t " + MyDataReader.GetString(2));
liste.Add("person:\t\t " + MyDataReader.GetString(3));
liste.Add("organization:\t " + MyDataReader.GetString(4));
liste.Add("as-num:\t\t " + MyDataReader.GetString(5));
liste.Add("phone:\t\t " + MyDataReader.GetString(6));
liste.Add("email:\t\t " + MyDataReader.GetString(7));
liste.Add("status:\t\t " + MyDataReader.GetString(8));
liste.Add("created:\t\t " + MyDataReader.GetString(9));
liste.Add("last-updated:\t " + MyDataReader.GetString(10));
liste.Add("");
if (sa[0] != "")
{
this.textBox7.Text = MyDataReader.GetString(0) + "/" + MyDataReader.GetByte(1).ToString();
this.textBox1.Text = MyDataReader.GetString(2);
this.textBox2.Text = MyDataReader.GetString(3);
this.textBox3.Text = MyDataReader.GetString(4);
this.textBox6.Text = MyDataReader.GetString(5);
this.textBox4.Text = MyDataReader.GetString(6);
this.textBox5.Text = MyDataReader.GetString(7);
this.comboBox1.SelectedItem = MyDataReader.GetString(8);
}
}
this.listBox1.Items.AddRange(liste.ToArray());
MyDataReader.Close();
if (MyDataReader is IDisposable)
{
MyDataReader.Dispose();
}
return(r);
}
catch (System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
#endregion btn2
#region if btn=3
else if (btn == 3)
{
try
{
r = MyCommand.ExecuteNonQuery();
}
catch (System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
#endregion btn=3
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(r);
}
//To keep track of delete buttons
protected void Page_Load(object sender, EventArgs e)
{
con = new OdbcConnection(connectionstring);
con.Open();
db = new TestEntities();
//Gets next identity number by seeing how many orders are in the system
using (OdbcCommand command = new OdbcCommand("SELECT COUNT(*) FROM dbo.InfraOrders", con))
{
using (OdbcDataReader reader = command.ExecuteReader())
{
reader.Read();
string temp = reader.GetString(0);
//If there are 0 rows in the table, the first number is 1
lblOrderNum.Text = (Int32.Parse(temp) + 1).ToString();
}
}
//Creates temp table to store order lines before order is placed
if (!IsPostBack)
{
try
{
using (OdbcCommand command = new OdbcCommand("CREATE TABLE ##temp([Line #] int IDENTITY(1,1)," +
"[F Code] varchar(10), Description varchar(MAX), [Quantity Per] int, [# of Cont.] int, [Cont. Type] varchar(50)," +
"UOM varchar(10), Priority int, [Total RQ] int)", con))
{
command.ExecuteNonQuery();
}
}
catch
{
using (OdbcCommand command = new OdbcCommand("DELETE dbo.##temp; DBCC CHECKIDENT('##temp', RESEED, 0)", con))
{
command.ExecuteNonQuery();
}
}
}
//Must recreate buttons if it is a post back
else
{
PlaceHolder1.Controls.Clear();
double btnHeight = gv1.RowStyle.Height.Value + 27;
Unit height = new Unit(btnHeight);
for (int i = 0; i < gv1.Rows.Count; ++i)
{
Button btn = new Button();
btn.Text = "Delete";
btn.Height = gv1.RowStyle.Height;
btn.ID = i.ToString();
btn.Click += new EventHandler(btnDelete);
PlaceHolder1.Controls.Add(btn);
btn.Visible = true;
Literal lit = new Literal();
lit.Text = "<br/>";
PlaceHolder1.Controls.Add(lit);
}
}
}
public void LoadKelaOrderDB(SUTI from, SUTIMsg msgFrom, string msgID, int tpakCallNbr)
{
inSUTI = from;
inSUTImsg = msgFrom;
sID = msgID;
OdbcConnection connIfx = new OdbcConnection(ConfigurationSettings.AppSettings.Get("MadsODBC"));
try
{
connIfx.Open();
SUTI_svc.order theOrder = ((SUTI_svc.order)msgFrom.Item);
List <routeNode> rteList = theOrder.route;
String passenger = "";
String datetime = String.Empty;
String phonenbr = String.Empty;
String booking_id = String.Empty;
String short_booking_id = String.Empty;
String last_booking_id = String.Empty;
String manual_text = String.Empty;
int sequence_nbr = 1;
foreach (var rte in rteList)
{
List <content> nodeContent = rte.contents;
if (nodeContent.Count > 0)
{
passenger = nodeContent[0].name; //taking the first listed passenger
contentIdContent bdayContent = nodeContent[0].idContent;
if (bdayContent.id.Length >= 6)
{
if (Int16.Parse(bdayContent.id.Substring(4, 2)) <= 15)
{
passenger = passenger + "/20" + bdayContent.id.Substring(4, 2);
}
else
{
passenger = passenger + "/19" + bdayContent.id.Substring(4, 2);
}
}
List <contactInfo> contactContent = nodeContent[0].contactInfosContent;
if (contactContent.Count > 0)
{
phonenbr = contactContent[0].contactInfo1;
}
List <idType> subOrderContent = nodeContent[0].subOrderContent;
foreach (var idOrder in subOrderContent)
{
if (idOrder.src.Equals("KELA_TRIP_ID")) //this is booking_ID
{
//shorten to 6 chars according to Kela shortening algo
booking_id = idOrder.id;
short_booking_id = Kela_IsKelaIdOk(booking_id);
}
}
List <manualDescriptionType> mda1 = nodeContent[0].manualDescriptionContent;
if (mda1.Count > 0)
{
manual_text = mda1[0].manualText;
}
}
List <timesTypeTime> timeContent = rte.timesNode;
//double UnixTime = 0;
int nodeSeqno = Int16.Parse(rte.nodeSeqno);
if (timeContent.Count > 0)
{
datetime = timeContent[0].time1.ToString("yyyy-MM-ddTHH:mm:ss");
if (nodeSeqno == 1)
{
double nowtime = (DateTime.Now - new DateTime(1970, 1, 1).ToLocalTime()).TotalSeconds;
_UnixTime = (timeContent[0].time1.ToLocalTime() - new DateTime(1970, 1, 1).ToLocalTime()).TotalSeconds - 7200;
}
}
if (rte.addressNode.streetNo == null)
{
rte.addressNode.streetNo = "0";
}
List <manualDescriptionType> mda = rte.addressNode.manualDescriptionAddress;
if (mda.Count > 0)
{
manual_text += " " + mda[0].manualText;
}
//if (last_booking_id != booking_id || last_booking_id.Equals(String.Empty))
//{
// last_booking_id = booking_id;
try
{
using (OdbcCommand ct = connIfx.CreateCommand())
{
ct.CommandType = CommandType.Text;
ct.CommandText = String.Format(new System.Globalization.CultureInfo("en-US"), "insert into vpu_node values ('{0}',{1},{2},'{3}','{4}', {5}, '{6}', '{7}', '{8}', '{9}', '{10}', {11}, {12},'{13}','{14}','{15}','{16}','{17}','{18}')",
theOrder.idOrder.id,
nodeSeqno,
tpakCallNbr,
(rte.nodeType.ToString() == "pickup" ? "P" : "D"),
rte.addressNode.street.Replace("'", "''"),
Int32.Parse(rte.addressNode.streetNo),
rte.addressNode.streetNoLetter,
rte.addressNode.community,
datetime,
(passenger.Length > 0 ? passenger : ""),
(phonenbr.Length > 0 ? phonenbr : ""),
rte.addressNode.geographicLocation.lat,
rte.addressNode.geographicLocation.@long,
"",
"",
"",
short_booking_id, booking_id, manual_text
);
log.InfoFormat(ct.CommandText);
ct.ExecuteNonQuery();
//connIfx.Close();
}
}
catch (Exception exc)
{
using (OdbcCommand ct = connIfx.CreateCommand())
{
ct.CommandType = CommandType.Text;
ct.CommandText = String.Format(new System.Globalization.CultureInfo("en-US"),
"update vpu_node set tpak_id='" + tpakCallNbr.ToString() + "' where rte_id='" +
theOrder.idOrder.id + "'");
ct.ExecuteNonQuery();
//connIfx.Close();
}
}
//}
}
connIfx.Close();
}
catch (Exception exc)
{
log.Error(String.Format("Error ]inserting Informix database (reattempt as UPDATE): {0}", exc.Message));
// Try UPDATE instead
}
return;
}
protected void btnSubmit_Click1(object sender, EventArgs e)
{
if (String.IsNullOrWhiteSpace(tbOp.Text))
{
string script = "alert(\"Please enter a customer name\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
return;
}
//Gets customer ID from Operator name
var customers = db.Customers.ToList();
var idList = from customer in customers
where customer.Name == tbOp.Text
select customer.ID;
List <int> temp = idList.ToList();
//If customer doesnt exist
int id;
try
{
id = temp[0];
}
catch
{
string script = "alert(\"Please enter an existing customer name\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
return;
}
//Adds new order to dbo.InfraOrders
using (OdbcCommand command = new OdbcCommand("INSERT INTO dbo.InfraOrders([Customer ID], [Update Date]) VALUES (?, ?)", con))
{
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@time", DateTime.Now);
command.ExecuteNonQuery();
}
//Adds new order lines to dbo.InfraOrderLines
using (OdbcCommand command = new OdbcCommand("INSERT INTO dbo.InfraOrderLines([F Code], Description, [Quantity Per]," +
"[# of Cont.], [Cont. Type], UOM, Priority, [Total RQ]) SELECT [F Code], Description, [Quantity Per], [# of Cont.]," +
"[Cont. Type], UOM, Priority, [Total RQ] FROM dbo.##temp", con))
{
command.ExecuteNonQuery();
}
//Sets Order # in recently added order lines
using (OdbcCommand command = new OdbcCommand("UPDATE dbo.InfraOrderLines SET [Order #] = ? where [Order #] is NULL", con))
{
command.Parameters.AddWithValue("@num", lblOrderNum.Text);
command.ExecuteNonQuery();
}
con.Close();
Response.Redirect("~/Home.aspx");
}
void Button2Click(object sender, EventArgs e)
{
// start
if (listView1.CheckedItems.Count <= 0)
{
return;
}
AddLogString("................................Start...");
Button5Click(sender, e); // предварительно запускаем метод Find ??
//получаем даты
// начало monthCalendar1 dateTimePicker1
string date1; //= this.monthCalendar1.SelectionRange.Start.ToShortDateString();
date1 = monthCalendar1.SelectionRange.Start.ToString("yyyy-MM-dd");
DateTime dtp1 = dateTimePicker1.Value;
string dt1 = dtp1.ToLongTimeString();
dt1 = dtp1.ToString("HH:mm:ss");
string t1 = date1 + " " + dt1;
// конец monthCalendar2 dateTimePicker2
string date2; //= this.monthCalendar2.SelectionRange.Start.ToShortDateString();
date2 = monthCalendar2.SelectionRange.Start.ToString("yyyy-MM-dd");
DateTime dtp2 = dateTimePicker2.Value;
string dt2 = dtp2.ToLongTimeString();
dt2 = dtp2.ToString("HH:mm:ss");
string t2 = date2 + " " + dt2;
// listView1.BeginUpdate();
int indexSet = 0;
for (int i = listView1.CheckedItems.Count - 1; i >= 0; i--)
{
ListViewItem itm = listView1.CheckedItems[i];
indexSet = itm.Index;
string file = System.IO.Path.Combine(itm.SubItems[5].Text, itm.SubItems[0].Text);
string cnt = itm.SubItems[4].Text;
if (cnt == "" || cnt == null || cnt == System.String.Empty)
{
cnt = "0";
}
int numCnt = 0;
if (!Int32.TryParse(cnt, out numCnt))
{
AddLogString(" start>> Int32.TryParse could not parse " + cnt + " to an int.");
}
if (numCnt <= 0)
{
continue;
}
//подсоединяемся к БД
//получаем имя таблицы
//преобразовываем метки время в Unix-формат
//получаем имена столбцов
//получаем records из таблицы
//форматируем записи в Insert скидываем в файл\память.
// отдаем в Oracle
string[] arr = new string[6];
String tblName = "";
SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};", file));
connection.Open();
SQLiteCommand command;
SQLiteDataReader reader = null;
try {
command = new SQLiteCommand("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;", connection);
reader = command.ExecuteReader();
}
catch (Exception ex1)
{
AddLogString(" File=" + file);
AddLogString(" Original error: " + ex1.Message);
connection.Close();
continue;
}
if (reader == null)
{
connection.Close(); continue;
}
// Принимаем - 1 файл = 1 таблица (это в корне не верно, но пока принимаем так)
foreach (DbDataRecord record in reader)
{
tblName = record["name"].ToString();
}
string minD = "";
string maxD = "";
string cntD = "";
string selD = "";
try {
command = new SQLiteCommand(string.Format("select strftime('%s','{0}'), strftime('%s','{1}')", t1, t2), connection);
reader = command.ExecuteReader();
}
catch (Exception ex2)
{
AddLogString(" File=" + file);
AddLogString(" Original error: " + ex2.Message);
connection.Close();
continue; // файл sqlite - но не имеет таблиц
}
foreach (DbDataRecord record in reader)
{
minD = record[0].ToString();
maxD = record[1].ToString();
}
if (minD == "" || minD == null || minD == System.String.Empty)
{
minD = "0";
}
if (maxD == "" || maxD == null || maxD == System.String.Empty)
{
maxD = "0";
}
int numValue1 = 0;
if (!Int32.TryParse(minD, out numValue1))
{
AddLogString(" start>> Int32.TryParse could not parse " + minD + " to an int.");
}
int numValue2 = 0;
if (!Int32.TryParse(maxD, out numValue2))
{
AddLogString(" start>> Int32.TryParse could not parse " + maxD + " to an int.");
}
if (numValue1 > numValue2)
{
AddLogString(" Wrong Data = " + minD + " > " + maxD + " - abort process file. ");
connection.Close();
continue;
}
try {
command = new SQLiteCommand(string.Format("PRAGMA table_info({0})", tblName), connection);
reader = command.ExecuteReader();
}
catch (Exception ex3)
{
AddLogString(" Table=" + tblName);
AddLogString(" Original error: " + ex3.Message);
connection.Close();
continue;
}
List <string> strFields = new List <string>();
foreach (DbDataRecord record in reader)
{
strFields.Add(record["name"].ToString());
}
// select ID , TIME1970 , VAL , STATE , MIN_VAL , MAX_VAL from tblName where TIME1970>=t1 and TIME1970<=t2
// INSERT INTO table_name ([column_name, ... ]) VALUES (expressions, ...)
// insert into tblName (ID , TIME1970 , VAL , STATE , MIN_VAL , MAX_VAL) values (ID , TIME1970 , VAL , STATE , MIN_VAL , MAX_VAL)
// INSERT INTO table_name VALUES (expressions, ...)
// insert into tblName values (ID , TIME1970 , VAL , STATE , MIN_VAL , MAX_VAL)
// insert into %s(%s) values(%s)
// формируем шаблоны строки для Select и insert. Порядок Важен.
string strInsert = "";
string strValuesInsert = "";
string strValues = "";
int strNumber;
int strIndex = -1;
string strDelim = "";
int nVal = 0;
string strnVal = "";
string findThisString = "ID";
for (strNumber = 0; strNumber < strFields.Count; strNumber++)
{
strIndex = strFields[strNumber].IndexOf(findThisString);
if (strIndex >= 0)
{
nVal = nVal + 1;
strnVal = string.Format("{0}", nVal);;
strValuesInsert = strValuesInsert + strDelim + "{" + strnVal + "}";
strValues = strValues + strDelim + findThisString;
strDelim = ",";
break;
}
}
findThisString = "TIME1970";
for (strNumber = 0; strNumber < strFields.Count; strNumber++)
{
strIndex = strFields[strNumber].IndexOf(findThisString);
if (strIndex >= 0)
{
nVal = nVal + 1;
strnVal = string.Format("{0}", nVal);;
strValuesInsert = strValuesInsert + strDelim + "{" + strnVal + "}";
strValues = strValues + strDelim + findThisString;
strDelim = ",";
break;
}
}
findThisString = "VAL";
for (strNumber = 0; strNumber < strFields.Count; strNumber++)
{
strIndex = strFields[strNumber].IndexOf(findThisString);
if (strIndex >= 0)
{
nVal = nVal + 1;
strnVal = string.Format("{0}", nVal);;
strValuesInsert = strValuesInsert + strDelim + "{" + strnVal + "}";
strValues = strValues + strDelim + findThisString;
strDelim = ",";
break;
}
}
findThisString = "STATE";
for (strNumber = 0; strNumber < strFields.Count; strNumber++)
{
strIndex = strFields[strNumber].IndexOf(findThisString);
if (strIndex >= 0)
{
nVal = nVal + 1;
strnVal = string.Format("{0}", nVal);;
strValuesInsert = strValuesInsert + strDelim + "{" + strnVal + "}";
strValues = strValues + strDelim + findThisString;
strDelim = ",";
break;
}
}
findThisString = "MIN_VAL";
for (strNumber = 0; strNumber < strFields.Count; strNumber++)
{
strIndex = strFields[strNumber].IndexOf(findThisString);
if (strIndex >= 0)
{
nVal = nVal + 1;
strnVal = string.Format("{0}", nVal);;
strValuesInsert = strValuesInsert + strDelim + "{" + strnVal + "}";
strValues = strValues + strDelim + findThisString;
strDelim = ",";
break;
}
}
findThisString = "MAX_VAL";
for (strNumber = 0; strNumber < strFields.Count; strNumber++)
{
strIndex = strFields[strNumber].IndexOf(findThisString);
if (strIndex >= 0)
{
nVal = nVal + 1;
strnVal = string.Format("{0}", nVal);;
strValuesInsert = strValuesInsert + strDelim + "{" + strnVal + "}";
strValues = strValues + strDelim + findThisString;
strDelim = ",";
break;
}
}
//AddLogString("\nvalues= " +strValues+"");
//AddLogString("\nvalues= " +strValuesInsert+"");
//получаем записи
try {
command = new SQLiteCommand(string.Format("select " + strValues + " from {0} where TIME1970>={1} and TIME1970<={2} ", tblName, numValue1, numValue2), connection);
reader = command.ExecuteReader();
}
catch (Exception ex5)
{
AddLogString(" File " + file);
AddLogString(" Original error: " + ex5.Message);
//continue ; // файл sqlite - но не имеет таблиц
}
if (reader == null)
{
connection.Close();
AddLogString(" Select from SQLite БД - имеет пустое число записей");
continue;
}
//strInsert = "insert into " + tblName + " (" + strValues + " ) values ( " + strValuesInsert + " ) ;" ;
strInsert = "insert into {0} (" + strValues + " ) values ( " + strValuesInsert + " ) ;";
// Если пишем в Oracle
// 1/ Открываем соединение
// 2/ Устанавливаем роли
// 3/ Вызываем arc_writer_pkg.prepare_table
// 5/ вставляем записи insert.
// 6/ Вызываем перенос (arc_writer_pkg.MOVE_DATA) из таблицы стэка
// 7/ завершаем
string connetionString = "DSN=rsdu2;UID=rsduadmin; PWD=passme";
OdbcConnection cnn = null;
OdbcCommand cmd = new OdbcCommand();
String vRetVal = "";
if (checkBox1.Checked == false)
{
cnn = new OdbcConnection(connetionString);
try
{
cnn.Open();
AddLogString("Connection Open = Ok ");
}
catch (Exception ex6)
{
AddLogString("Connection Open = NONE " + ex6.Message);
}
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма вставки - прервано..");
cnn.Close();
connection.Close();
return;
}
cmd.Connection = cnn; // уже созданное и открытое соединение
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.CommandText = "SET ROLE BASE_EXT_CONNECT_OIK , ARC_STAND_ADMIN";
try
{
cmd.ExecuteNonQuery();
AddLogString("Установка роли = Ok ");
}
catch (Exception ex7)
{
AddLogString("Ошибка установки Ролей =" + ex7.Message);
AddLogString(" выполнение алгоритма вставки - прервано..");
cnn.Close();
connection.Close();
return;
}
AddLogString("Вызов процедуры очистки таблицы стэка (arc_writer_pkg.prepare_table)...");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.CommandText = "{ ? = call arc_writer_pkg.prepare_table( ? )}";
OdbcParameter parOut = new OdbcParameter();
parOut.Direction = System.Data.ParameterDirection.ReturnValue;
parOut.OdbcType = OdbcType.NChar;
parOut.ParameterName = "vRetVal";
parOut.Size = 1024;
cmd.Parameters.Add(parOut);
OdbcParameter param = new OdbcParameter();
param.Direction = System.Data.ParameterDirection.Input;
param.OdbcType = OdbcType.NChar;
param.ParameterName = "pTname";
param.Value = tblName;
param.Size = 1024;
cmd.Parameters.Add(param);
try
{
cmd.ExecuteNonQuery();
vRetVal = cmd.Parameters["vRetVal"].Value.ToString();
}
catch (Exception ex8)
{
AddLogString("Не удалось вызвать процедуру очистки таблицы стэка.");
AddLogString("Ошибка вызова процедуры =" + ex8.Message);
cnn.Close(); // закрываем oRacle
connection.Close(); // закрываем sqlite
continue; // продолжаем со следующего файла
}
if (vRetVal != "")
{
AddLogString("Ошибка при работе процедуры(arc_writer_pkg.prepare_table) = " + vRetVal);
cnn.Close(); // закрываем oRacle
connection.Close(); // закрываем sqlite
continue; // продолжаем со следующего файла
}
AddLogString("Вызов процедуры очистки таблицы стэка прошел успешно.");
AddLogString("Идет перенос данных в таблицу = " + tblName + ". Подождите...");
}
string folderName = @"c:\retro";
//
folderName = System.IO.Path.GetDirectoryName(file);
// Create a file name for the file you want to create.
//string fileNametblName = System.IO.Path.GetRandomFileName();
string fileNametblName = tblName + ".sql";
// Use Combine again to add the file name to the path.
string pathString = System.IO.Path.Combine(folderName, fileNametblName);
System.IO.StreamWriter fileOutput = null;
OdbcTransaction dbTr = null;
if (checkBox1.Checked == true)
{
fileOutput = new System.IO.StreamWriter(pathString);
}
else
{
dbTr = cnn.BeginTransaction();
}
string s0 = "", s1 = "", s2 = "", s3 = "", s4 = "", s5 = "";
string strOutput = "";
const int nCapacity = 1000;
int nCnt = 0;
List <string> strOutputs = new List <string>();
AddLogString("Начало цикла обработки " + tblName + ".");
foreach (DbDataRecord record in reader)
{
// select извлек данные из sqlite и произвел их модификацию !????? в соответствии .... непонятно чем.
// Итого, вместо . (десятичной точки) оказалась ,(запятая)
//d3=Convert.ToDouble(record[2].ToString(),System.Globalization.CultureInfo.InvariantCulture);
//d3=Double.Parse(record[2].ToString().Replace(".",","),System.Globalization.NumberStyles.Number);
s0 = record[0].ToString();
s1 = record[1].ToString();
s2 = record[2].ToString().Replace(",", ".");
s3 = record[3].ToString();
if (nVal > 4)
{
s4 = record[4].ToString().Replace(",", ".");
s5 = record[5].ToString().Replace(",", ".");
strOutput = string.Format(strInsert, tblName, s0, s1, s2, s3, s4, s5);
}
else
{
strOutput = string.Format(strInsert, tblName, s0, s1, s2, s3);
}
strOutputs.Add(strOutput);
nCnt = nCnt + 1;
// Пишем группой по 1000 строк
if (nCnt >= nCapacity)
{
if (checkBox1.Checked == true)
{
foreach (string strTemp1 in strOutputs)
{
fileOutput.WriteLine(strTemp1);
}
}
else
{
//strOutputs.Add("commit;");
// вариант засовывания файла в БД с помощью пакета arc_writer_pkg
cmd.CommandType = System.Data.CommandType.Text;
cmd.Transaction = dbTr;
cmd.Parameters.Clear();
foreach (string strTemp2 in strOutputs)
{
cmd.CommandText = strTemp2;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex9)
{
AddLogString("Ошибка при выполнении INSERT =" + ex9.Message);
break;
}
}
////try
////{
//// // Commit the transaction.
//// dbTr.Commit();
////}
////catch (Exception ex10)
////{
//// AddLogString("Ошибка при выполнении Commit ="+ex10.Message);
//// try
//// {
//// // Attempt to roll back the transaction.
//// dbTr.Rollback();
//// }
//// catch
//// {
//// // Do nothing here; transaction is not active.
//// }
//// break ;
////}
strOutputs.Clear();
nCnt = 0;
}
} // (nCnt>=nCapacity)
} // foreach
// Этот кусок необходим для записи оставшихся элементов блока
if (nCnt > 0)
{
if (checkBox1.Checked == true)
{
foreach (string strTemp1 in strOutputs)
{
fileOutput.WriteLine(strTemp1);
}
}
else
{
//strOutputs.Add("commit;");
// вариант засовывания файла в БД с помощью пакета arc_writer_pkg
cmd.CommandType = System.Data.CommandType.Text;
cmd.Transaction = dbTr;
cmd.Parameters.Clear();
foreach (string strTemp2 in strOutputs)
{
cmd.CommandText = strTemp2;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex9)
{
AddLogString("Ошибка при выполнении INSERT =" + ex9.Message);
break;
}
}
////try
////{
//// // Commit the transaction.
//// dbTr.Commit();
////}
////catch (Exception ex10)
////{
//// AddLogString("Ошибка при выполнении Commit ="+ex10.Message);
//// try
//// {
//// // Attempt to roll back the transaction.
//// dbTr.Rollback();
//// }
//// catch
//// {
//// // Do nothing here; transaction is not active.
//// }
//// //break ;
////}
strOutputs.Clear();
nCnt = 0;
}
} // (nCnt>=0)
AddLogString("Конец цикла обработки " + tblName + ".");
if (checkBox1.Checked == true)
{
fileOutput.Flush();
fileOutput.Close();
AddLogString("\t File = " + pathString + " - создан.");
}
else
{
AddLogString("Вызов процедуры переноса данных (arc_writer_pkg.MOVE_DATA) из таблицы стэка. Подождите......");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.CommandText = "{? = call arc_writer_pkg.move_data( ? )}";
OdbcParameter parOut = new OdbcParameter();
parOut.Direction = System.Data.ParameterDirection.ReturnValue;
parOut.OdbcType = OdbcType.NChar;
parOut.ParameterName = "vRetVal";
parOut.Size = 1024;
cmd.Parameters.Add(parOut);
OdbcParameter param = new OdbcParameter();
param.Direction = System.Data.ParameterDirection.Input;
param.OdbcType = OdbcType.NChar;
param.ParameterName = "pTname";
param.Value = tblName;
param.Size = 1024;
cmd.Parameters.Add(param);
vRetVal = "";
try
{
cmd.ExecuteNonQuery();
vRetVal = cmd.Parameters["vRetVal"].Value.ToString();
}
catch (Exception ex11)
{
AddLogString("Не удалось вызвать процедуру переноса данных из таблицы стэка.");
AddLogString("Ошибка вызова процедуры =" + ex11.Message);
}
if (vRetVal != "")
{
AddLogString("Ошибка при работе процедуры (arc_writer_pkg.MOVE_DATA) = " + vRetVal);
}
else
{
AddLogString("Вызов процедуры переноса данных из таблицы стэка прошел успешно.");
}
cnn.Close();
}
connection.Close();
AddLogString("Обработка БД Sqlite = " + file + " - завершена.");
} // for file
AddLogString("...............................Finished.");
// listView1.EndUpdate();
}
private void button3_Click(object sender, EventArgs e)
{
bool validaG = true;
if (cmbGrado.SelectedItem != null)
{
string nGrad = cmbGrado.SelectedItem.ToString();
try
{
cmd = new OdbcCommand("SELECT idGrado FROM Grado WHERE Nombre='" + nGrad + "'", nueva.nuevaConexion());
OdbcDataReader al = cmd.ExecuteReader();
while (al.Read() == true)
{
grado = al.GetString(0);
}
}
catch (OdbcException er)
{
MessageBox.Show(er.ToString());
}
}
else
{
validaG = false;
}
//Validar Combo
bool validaA = true;
if (cmbAlumno.SelectedItem != null)
{
string nAlum = cmbAlumno.SelectedItem.ToString();
try
{
cmd = new OdbcCommand("SELECT idAlumno FROM Alumno WHERE Nombre='" + nAlum + "'", nueva.nuevaConexion());
OdbcDataReader al = cmd.ExecuteReader();
while (al.Read() == true)
{
alumno = al.GetString(0);
}
}
catch (OdbcException er)
{
MessageBox.Show(er.ToString());
}
}
else
{
validaA = false;
}
if (txtCodigo.Text != "" || txtSeccion.Text != "" || validaA == true || validaG == true)
{
try
{
cmd = new OdbcCommand("UPDATE Alumno_Grado SET Seccion='"
+ txtSeccion.Text + "',idGrado='"
+ grado + "', idAlumno='" + alumno +
"' WHERE idAlumno_Grado='" + txtCodigo.Text + "'", nueva.nuevaConexion());
cmd.ExecuteNonQuery();
}
catch (OdbcException er)
{
MessageBox.Show(er.ToString());
IngresoC = false;
}
}
else
{
MessageBox.Show("Hacen Campos Por Llenar", "INFORMACION", MessageBoxButtons.OK, MessageBoxIcon.Information);
IngresoC = false;
}
if (IngresoC)
{
txtCodigo.Enabled = true;
MessageBox.Show("Modificado Correctamente");
txtCodigo.Clear();
txtSeccion.Clear();
cmbAlumno.Items.Clear();
cmbGrado.Items.Clear();
llenarCombo();
}
llenarTabla();
}
public int ExecuteCommand(string cmdStr)
{
try
{
OdbcCommand command = new OdbcCommand(cmdStr, dbConn);
command.CommandType = System.Data.CommandType.Text;
return command.ExecuteNonQuery();
}
catch (Exception ex)
{
exception = ex;
return -1;
}
}
private static void TryToExecuteSql(OdbcCommand command, string sql)
{
command.CommandText = sql;
try { command.ExecuteNonQuery(); }
catch { }
}
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
{
throw new ProviderException("User is not in role.");
}
}
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM `" + usersInRolesTable + "`" +
" WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn);
OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255);
OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (OdbcException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveUsersFromRoles");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
protected void ListView_ItemCommand(object sender, ListViewCommandEventArgs e)
{
if (e.CommandName == "DeleteButton")
{
try
{
OdbcConnection myConn = new OdbcConnection(connectionString);
myConn.Open();
string deleteQuery = "DELETE FROM Employee WHERE ID = ?";
OdbcCommand command = new OdbcCommand(deleteQuery, myConn);
string id = (string)e.CommandArgument;
command.Parameters.Add("@ID", OdbcType.Text).Value = id.Replace("-", string.Empty);
int result = command.ExecuteNonQuery();
myConn.Close();
Response.Redirect("Employees.aspx");
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
// displays the details by setting div visibility to true
if (e.CommandName == "DetailsButton")
{
HtmlGenericControl div = (HtmlGenericControl)e.Item.FindControl("toggleDetailsDiv");
div.Visible = true;
}
if (e.CommandName == "UpdateButton")
{
HtmlGenericControl div = (HtmlGenericControl)e.Item.FindControl("toggleDetailsDiv");
div.Visible = true;
Panel updatePanel = (Panel)e.Item.FindControl("updateInfoPanel");
updatePanel.Visible = true;
Button submitChanges = (Button)e.Item.FindControl("SubmitButton");
submitChanges.Visible = true;
}
if (e.CommandName == "SubmitButton")
{
TextBox nameValue = (TextBox)e.Item.FindControl("NameValue");
TextBox jobTitleValue = (TextBox)e.Item.FindControl("TitleValue");
TextBox addressValue = (TextBox)e.Item.FindControl("AdressValue");
TextBox certifiedForValue = (TextBox)e.Item.FindControl("CertifiedForValue");
TextBox salaryValue = (TextBox)e.Item.FindControl("SalaryValue");
Label oldName = (Label)e.Item.FindControl("Name");
Label oldJobtitleValue = (Label)e.Item.FindControl("Title");
Label oldAdressValue = (Label)e.Item.FindControl("Address");
Label oldCertifiedForValue = (Label)e.Item.FindControl("CertifiedFor");
Label oldSalaryValue = (Label)e.Item.FindControl("Salary");
if (nameValue.Text == "")
{
nameValue.Text = oldName.Text;
}
if (jobTitleValue.Text == "")
{
jobTitleValue.Text = oldJobtitleValue.Text;
}
if (addressValue.Text == "")
{
addressValue.Text = oldAdressValue.Text;
}
if (certifiedForValue.Text == "")
{
certifiedForValue.Text = oldCertifiedForValue.Text;
}
if (salaryValue.Text == "")
{
salaryValue.Text = oldSalaryValue.Text;
}
try
{
OdbcConnection myConn = new OdbcConnection(connectionString);
myConn.Open();
string query = "UPDATE Employee SET NAME = ?, ADDRESS = ?, JOBTITLE = ?, CERTIFIEDFOR = ?, SALARY = ? WHERE ID = HEXTORAW(?)";
OdbcCommand command = new OdbcCommand(query, myConn);
string id = (string)e.CommandArgument;
command.Parameters.Add("@NAME", OdbcType.Text).Value = nameValue.Text;
command.Parameters.Add("@ADDRESS", OdbcType.Text).Value = addressValue.Text;
command.Parameters.Add("@JOBTITLE", OdbcType.Text).Value = jobTitleValue.Text;
command.Parameters.Add("@CERTIFIEDFOR", OdbcType.Text).Value = certifiedForValue.Text;
command.Parameters.Add("@SALARY", OdbcType.Int).Value = int.Parse(salaryValue.Text);
command.Parameters.Add("@ID", OdbcType.Text).Value = id.Replace("-", string.Empty);
int result = command.ExecuteNonQuery();
myConn.Close();
Response.Redirect("Employees.aspx");
}
catch (Exception err)
{
Console.WriteLine(err.Message);
}
}
}
public void saveMedia(object sender, EventArgs e)
{
if (!Page.IsValid)
{
msg.InnerHtml = "";
return;
}
General g = Session["app"] as General;
string tmsg = "", SQL = "";//, Enabled = "0";
ArrayList f = new ArrayList();
OdbcTransaction myTransaction = null;
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
if (myTransaction != null)
{
myTransaction = g.DB.connDB.BeginTransaction();
}
cmd.Transaction = myTransaction;
//bool typeAllowed = bool.Parse(checkFileType(File.FileName)[0]);
if (bool.Parse(checkFileType(File.FileName)[0]) && (File.PostedFile.ContentLength <= 11048576) /* || lblidMedia.Text.Length > 0*/)//1024k
{
int i = 0, Width = 0, Height = 0;
string[] keys;
string filename, file_ext, fullfilename, filepath;//, pat = @"\\(?:.+)\\(.+)\.(.+)";
HttpFileCollection Files;
Files = Request.Files;
keys = Files.AllKeys;
for (i = 0; i <= keys.GetUpperBound(0); i++)
{
filepath = Files[i].FileName;
if (checkFileType(File.FileName)[1].ToString() == "Image")
{
//Create an image object from the uploaded file
System.Drawing.Image UploadedImage = System.Drawing.Image.FromStream(File.PostedFile.InputStream);
//Determine width and height of uploaded image
Width = int.Parse(UploadedImage.PhysicalDimension.Width.ToString());
Height = int.Parse(UploadedImage.PhysicalDimension.Height.ToString());
//Check that image does not exceed maximum dimension settings
//if (int.Parse(Width) > 600 || int.Parse(Height) > 400)
//Response.Write("This image is too big - please resize it!");
}
//if (filepath.Length > 0)
//{
// }
filename = Path.GetFileNameWithoutExtension(filepath);
file_ext = Path.GetExtension(filepath);
fullfilename = Path.GetFileName(filepath);
if (lblidMedia.Text.Length == 0) //new file
{
if (!g.Auth.CheckPermission(g.Auth.idUser, 3, "InsertData"))
{
msg.InnerHtml = "Insert new data is not allowed.";
return;
}
SQL = "INSERT INTO mediatab "
+ "VALUES (NULL, '" + txtTitle.Text + "', '" + fullfilename + "', '0', '"
+ checkFileType(File.FileName)[1].ToString() + "', " + Width + ", " + Height + ", 1)";
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
SQL = "SELECT LAST_INSERT_ID() AS ID"; // SELECT SCOPE_IDENTITY()
cmd.CommandText = SQL;
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
fullfilename = result["ID"].ToString() + file_ext;
SQL = "UPDATE mediatab SET "
+ "filename='" + fullfilename
+ "' WHERE idmedia=" + result["ID"].ToString();
cmd.CommandText = SQL;
result.Close();
result.Dispose();
cmd.ExecuteNonQuery();
f.Add(fullfilename);
}
else
{
tmsg += "No files uploaded, or wrong content type!";
if (myTransaction != null)
{
myTransaction.Rollback();
}
return;
}
try
{
Files[i].SaveAs(Server.MapPath("..\\images\\") + fullfilename);
tmsg += "- File: " + fullfilename;
tmsg += "<br/>- Saved successfully!";
}
catch (Exception err)
{
if (myTransaction != null)
{
myTransaction.Rollback();
}
tmsg += "No files uploaded, or wrong content type! <br/>" + err.Message;
throw;
}
}
else //update record
{
string idMedia = lblidMedia.Text;
if (!g.Auth.CheckPermission(g.Auth.idUser, 3, "UpdateData"))
{
msg.InnerHtml = "Update data is not allowed.";
return;
}
SQL = "UPDATE mediatab SET "
+ "title='" + txtTitle.Text + "'";
if (filename.Length > 0)
{
deleteMedia(sender, e, idMedia);
SQL += ", originalname='" + filename + file_ext + "', filename='"
+ idMedia + file_ext + "', type ='"
+ checkFileType(filename + file_ext)[1].ToString() + "' ";
if (checkFileType(filename + file_ext)[1].ToString() == "Image")
{
SQL += ", width=" + Width + ", height=" + Height;
}
}
SQL += " WHERE idmedia=" + idMedia;
cmd.CommandText = SQL;
try
{
cmd.ExecuteNonQuery();
if (fullfilename.Length > 0)
{
try
{
Files[i].SaveAs(Server.MapPath("..\\images\\") + idMedia + file_ext);
tmsg += "- File: " + fullfilename;
tmsg += "<br/>- Saved successfully!";
}
catch (Exception err)
{
if (myTransaction != null)
{
myTransaction.Rollback();
}
tmsg += "No files uploaded, or wrong content type! <br/>" + err.Message;
throw;
}
}
}
catch (OdbcException odb)
{
tmsg = g.DB.catchODBCException(odb, g.ErrorLevel);
}
}
}
}
else
{
if (myTransaction != null)
{
myTransaction.Rollback();
}
tmsg += "No files uploaded, or wrong content type!";
return;
}
if (myTransaction != null)
{
myTransaction.Commit();
}
}
catch (OdbcException o)
{
if (myTransaction != null)
{
myTransaction.Rollback();
}
tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
cmd.Dispose();
if (tmsg.Length == 0)
{
msg.InnerHtml = "Saved successfully!";
}
else
{
msg.InnerHtml = tmsg;
}
listMedia(sender, e);
}
}
private void UpdateFormStatus()
{
rootCfg = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/petroneeds");
connection = rootCfg.ConnectionStrings.ConnectionStrings["petroneedsConnectionString"].ConnectionString.ToString();
string sql, date;
date = DateTime.Now.Date.ToString("dd-MMMM-yyyy");
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
ICMANAGER = Request.QueryString["ICM"];
MANAGER = Request.QueryString["MNG"];
if (TextBox1.Text != "")
{
if (ICMANAGER == EMPLOYEE_NO)
{
sql = "UPDATE ICT_NEW_ACCOUNT SET FORM_STATUS = '" + RadioButtonList1.SelectedValue + "',REQUEST_EXECUTOR='" + DropDownList1.SelectedValue + "', IMNG_COMMENT = '" + TextBox5.Text + "' " +
", IMNG_ID = '" + ICMANAGER + "', IMNG_DATE = '" + date + "' WHERE FORM_NO = '" + TextBox1.Text + "'";
con = new OdbcConnection(connection);
con.ConnectionString = connection;
con.Open();
comm = new OdbcCommand(sql, con);
comm.ExecuteNonQuery();
con.Close();
Label_Message.Text = "Successfully...";
Status = RadioButtonList1.SelectedItem.Text.ToString();
SendEMail(Status, 1);
//dropsend();
}
else
if (MANAGER == EMPLOYEE_NO)
{
sql = "UPDATE ICT_NEW_ACCOUNT SET FORM_STATUS = '" + RadioButtonList1.SelectedValue + "', MNG_COMMENT = '" + TextBox5.Text + "' " +
", MNG_ID = '" + MANAGER + "', MNG_DATE = '" + date + "' WHERE FORM_NO = '" + TextBox1.Text + "'";
con = new OdbcConnection(connection);
con.ConnectionString = connection;
con.Open();
comm = new OdbcCommand(sql, con);
comm.ExecuteNonQuery();
con.Close();
//Page.Response.Redirect(Page.Request.Url.ToString(), true);
//Reset();
//Response.Write(HRMANAGER);
Label_Message.Text = "Successfully...";
Status = RadioButtonList1.SelectedItem.Text.ToString();
SendEMail(Status, 2);
}
/*else
* if (MANAGER == EMPLOYEE_NO & SINMANAGER == EMPLOYEE_NO)
* {
*
* sql = "UPDATE ICT_MSTR SET FORM_STATUS = '" + RadioButtonList1.SelectedValue + "', SMNG_COMMENT = '" + TextBox5.Text + "' " +
* ", SMNG_ID = '" + SINMANAGER + "', SMNG_DATE = '" + date + "' WHERE FORM_NO = '" + TextBox1.Text + "'";
*
* con = new OdbcConnection(connection);
* con.ConnectionString = connection;
* con.Open();
*
* comm = new OdbcCommand(sql, con);
* comm.ExecuteNonQuery();
* con.Close();
*
* Label_Message.Text = "Successfully...";
* Status = RadioButtonList1.SelectedItem.Text.ToString();
* SendEMail(Status, 3);
*
*
* }
*
* else
* if (SINMANAGER == EMPLOYEE_NO)
* {
*
* sql = "UPDATE ICT_MSTR SET FORM_STATUS = '" + RadioButtonList1.SelectedValue + "', SMNG_COMMENT = '" + TextBox5.Text + "' " +
* ", SMNG_ID = '" + SINMANAGER + "', SMNG_DATE = '" + date + "' WHERE FORM_NO = '" + TextBox1.Text + "'";
*
*
* con = new OdbcConnection(connection);
* con.ConnectionString = connection;
* con.Open();
*
* comm = new OdbcCommand(sql, con);
* comm.ExecuteNonQuery();
* con.Close();
*
* Label_Message.Text = "Successfully...";
* Status = RadioButtonList1.SelectedItem.Text.ToString();
* SendEMail(Status, 4);
* }*/
}
else
{
Reset();
Panel1.Visible = false;
}
}
private void button7_Click(object sender, EventArgs e)
{
string auxi;
ConexionCapaDatos cone = new ConexionCapaDatos();
int contador = 1;
double sueldoM;
double sueldoD;
string saldo;
try {
try {
int x = 1;
string[] vector = new string[1024];
string nuevo_emp = Microsoft.VisualBasic.Interaction.InputBox("Ingrese el nuevo id del empleado", "Ingreso");
string cadena = "select ID_Empleado from Percepciones";/* where ID_Empleado="+
* // nuevo_emp +" and ID_Percepcion=" +"'"+ID_PER+"'"+"; ";*/
OdbcCommand cmd = new OdbcCommand(cadena, cone.cnxOpen());
OdbcDataReader leer = cmd.ExecuteReader();
while (leer.Read())
{
vector[x] = leer.GetString(0);
x++;
}
cone.cnxClose();
for (int y = 1; y < x; y++)
{
string cadenaA = "select ID_Empleado from Percepciones where ID_Empleado=" +
nuevo_emp + " and ID_Percepcion=" + "'" + ID_PER + "'" + "; ";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer1 = cmd1.ExecuteReader();
while (leer1.Read())
{
if (vector[y] == nuevo_emp)
{
contador = 1;
}
else
{
contador = 0;
}
}
cone.cnxClose();
}
if (contador == 1)
{
MessageBox.Show("EMPLEADO YA INGRESADO");
}
else
{
string nuevo_dias = Microsoft.VisualBasic.Interaction.InputBox("Ingrese los dias", "Ingreso");
///////////////////
int diastxt = Convert.ToInt32(nuevo_dias);
string cadenaB = "SELECT tbl_empleados.ID_empleado ,tbl_empleados.nombre,tbl_contratos.salario " +
"FROM tbl_empleados " +
"INNER JOIN tbl_contratos ON tbl_empleados.ID_contrato = tbl_contratos.ID_contrato WHERE ID_Empleado=" + nuevo_emp + ";";
MessageBox.Show(cadenaB);
OdbcCommand cmd2 = new OdbcCommand(cadenaB, cone.cnxOpen());
OdbcDataReader leer1 = cmd2.ExecuteReader();
while (leer1.Read())
{
saldo = leer.GetString(2);
sueldo = Convert.ToDouble(saldo);
switch (mescmx)
{
case (1):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (2):
sueldoM = (sueldo / 28);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (3):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (4):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (5):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (6):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (7):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (8):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (9):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (10):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (11):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (12):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
}
}
auxi = String.Format("{0:0.00}", sueldo);
string result = auxi.Replace(",", ".");
DIA = textBox6.Text;
string cadenaC = "insert into Percepciones value(" +
"\"" + ID_PER + "\"," + ID_EMP + "," + result + "," + diastxt + ");";
OdbcCommand cmd3 = new OdbcCommand(cadena, cone.cnxOpen());
cmd.ExecuteNonQuery();
cone.cnxClose();
DataTable dt = new DataTable();
cadena = "SELECT ID_Empleado, ID_Percepcion, Total, Dias FROM Percepciones" +
" WHERE ID_Percepcion=" + "\"" + ID_PER + "\"" + ";";
OdbcDataAdapter dta = new OdbcDataAdapter(cadena, cone.cnxOpen());
DataSet dst = new DataSet();
dta.Fill(dst);
dt = dst.Tables[0];
dataGridView1.DataSource = dt;
MessageBox.Show("EMPLEADO INSERTADO");
cone.cnxClose();
////////////////
}
}
catch (OdbcException ex)
{
MessageBox.Show("Error en la base de datos \n" + ex);
cone.cnxClose();
}
}
catch (Exception ex)
{
MessageBox.Show("Error en la base de datos\n" + ex);
cone.cnxClose();
}
}
//
// GetSessionStoreItem is called by both the GetItem and
// GetItemExclusive methods. GetSessionStoreItem retrieves the
// session data from the data source. If the lockRecord parameter
// is true (in the case of GetItemExclusive), then GetSessionStoreItem
// locks the record and sets a new LockId and LockDate.
//
private SessionStateStoreData GetSessionStoreItem(bool lockRecord,
HttpContext context,
string id,
out bool locked,
out TimeSpan lockAge,
out object lockId,
out SessionStateActions actionFlags)
{
// Initial values for return value and out parameters.
SessionStateStoreData item = null;
lockAge = TimeSpan.Zero;
lockId = null;
locked = false;
actionFlags = 0;
// ODBC database connection.
OdbcConnection conn = new OdbcConnection(connectionString);
// OdbcCommand for database commands.
OdbcCommand cmd = null;
// DataReader to read database record.
OdbcDataReader reader = null;
// DateTime to check if current session item is expired.
DateTime expires;
// String to hold serialized SessionStateItemCollection.
string serializedItems = "";
// True if a record is found in the database.
bool foundRecord = false;
// True if the returned session item is expired and needs to be deleted.
bool deleteData = false;
// Timeout value from the data store.
int timeout = 0;
try
{
conn.Open();
// lockRecord is true when called from GetItemExclusive and
// false when called from GetItem.
// Obtain a lock if possible. Ignore the record if it is expired.
if (lockRecord)
{
cmd = new OdbcCommand(
"UPDATE Sessions SET" +
" Locked = ?, LockDate = ? " +
" WHERE SessionId = ? AND ApplicationName = ? AND Locked = ? AND Expires > ?", conn);
cmd.Parameters.Add("@Locked", OdbcType.Bit).Value = true;
cmd.Parameters.Add("@LockDate", OdbcType.DateTime).Value
= DateTime.Now;
cmd.Parameters.Add("@SessionId", OdbcType.VarChar, 80).Value = id;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar,
255).Value = ApplicationName;
cmd.Parameters.Add("@Locked", OdbcType.Int).Value = false;
cmd.Parameters.Add
("@Expires", OdbcType.DateTime).Value = DateTime.Now;
if (cmd.ExecuteNonQuery() == 0)
{
// No record was updated because the record was locked or not found.
locked = true;
}
else
{
// The record was updated.
locked = false;
}
}
// Retrieve the current session item information.
cmd = new OdbcCommand(
"SELECT Expires, SessionItems, LockId, LockDate, Flags, Timeout " +
" FROM Sessions " +
" WHERE SessionId = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@SessionId", OdbcType.VarChar, 80).Value = id;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar,
255).Value = ApplicationName;
// Retrieve session item data from the data source.
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
while (reader.Read())
{
expires = reader.GetDateTime(0);
if (expires < DateTime.Now)
{
// The record was expired. Mark it as not locked.
locked = false;
// The session was expired. Mark the data for deletion.
deleteData = true;
}
else
{
foundRecord = true;
}
serializedItems = reader.GetString(1);
lockId = reader.GetInt32(2);
lockAge = DateTime.Now.Subtract(reader.GetDateTime(3));
actionFlags = (SessionStateActions)reader.GetInt32(4);
timeout = reader.GetInt32(5);
}
reader.Close();
// If the returned session item is expired,
// delete the record from the data source.
if (deleteData)
{
cmd = new OdbcCommand("DELETE FROM Sessions " +
"WHERE SessionId = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@SessionId", OdbcType.VarChar, 80).Value = id;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar,
255).Value = ApplicationName;
cmd.ExecuteNonQuery();
}
// The record was not found. Ensure that locked is false.
if (!foundRecord)
{
locked = false;
}
// If the record was found and you obtained a lock, then set
// the lockId, clear the actionFlags,
// and create the SessionStateStoreItem to return.
if (foundRecord && !locked)
{
lockId = (int)lockId + 1;
cmd = new OdbcCommand("UPDATE Sessions SET" +
" LockId = ?, Flags = 0 " +
" WHERE SessionId = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@LockId", OdbcType.Int).Value = lockId;
cmd.Parameters.Add("@SessionId", OdbcType.VarChar, 80).Value = id;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.ExecuteNonQuery();
// If the actionFlags parameter is not InitializeItem,
// deserialize the stored SessionStateItemCollection.
if (actionFlags == SessionStateActions.InitializeItem)
{
item = CreateNewStoreData(context, pConfig.Timeout.TotalMinutes);
}
else
{
item = Deserialize(context, serializedItems, timeout);
}
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetSessionStoreItem");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
conn.Close();
}
return(item);
}
protected void deleteLocation(object sender, EventArgs e)
{
LabelError.Visible = false;
List <string> list = new List <string>();
//On récupère tous les ID vente sélectionné
foreach (GridViewRow row in GridView1.Rows)
{
System.Web.UI.WebControls.CheckBox check = (System.Web.UI.WebControls.CheckBox)row.FindControl("CheckBoxArchiver");
var selectedKey = GridView1.DataKeys[row.RowIndex].Value.ToString();
if (check.Checked)
{
if (!list.Contains(selectedKey))
{
list.Add(selectedKey);
}
}
}
if (list.Count < 1)
{
LabelError.Visible = true;
LabelError.Text = "veuillez sélectionnez au moins une vente à supprimer<br/><br/>";
}
//si il y a des ventes sélectionnées
if (list.Count > 0)
{
bool valid = true;
//on regarde si les proposition de vente ont été validés
foreach (string item in list)
{
string reqProposition = "SELECT Locations.valider_proposition FROM Locations WHERE Locations.ID=" + item;
OdbcConnection c = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
OdbcCommand requetteProposition = new OdbcCommand(reqProposition, c);
c.Open();
OdbcDataReader read = requetteProposition.ExecuteReader();
while (read.Read())
{
string validprop = read["valider_proposition"].ToString();
if (validprop == "True")
{
valid = false;
}
}
c.Close();
}
//si les propositions de location ne sont pas validés on supprime les locations
if (valid == true)
{
foreach (string item in list)
{
string reqLocation = "DELETE FROM Locations WHERE ID=" + item;
string reqHonoraire = "DELETE FROM Ventes_honoraires WHERE id_location=" + item;
OdbcConnection c = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
OdbcCommand requeteLocation = new OdbcCommand(reqLocation, c);
OdbcCommand requeteHonoraire = new OdbcCommand(reqHonoraire, c);
c.Open();
requeteLocation.ExecuteNonQuery();
requeteHonoraire.ExecuteNonQuery();
c.Close();
}
Response.Redirect(Request.RawUrl);
}
else
{
LabelError.Visible = true;
LabelError.Text = "Au moins une location sélectionnée à été validée par un administrateur et ne peut pas être supprimée<br/>Si vous souhaitez la supprimer, veuillez contacter un administrateur<br/><br/>";
}
}
}
public static void Test_Various_DataTypes2()
{
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, "; //1
sql += "c_smallint smallint, "; //2
sql += "c_integer integer, "; //3
sql += "c_bigint bigint, "; //4
sql += "c_numeric numeric(15,1), "; //5
sql += "c_float float, "; //6
sql += "c_decimal decimal(15,3), "; //7
sql += "c_double double, "; //8
sql += "c_char char, "; //9
sql += "c_varchar varchar(4096), "; //10
sql += "c_time time, "; //11
sql += "c_date date, "; //12
sql += "c_timestamp timestamp, "; //13
sql += "c_datetime datetime, "; //14
sql += "c_bit bit(1), "; //15
sql += "c_varbit bit varying(4096), "; //16
sql += "c_monetary monetary, "; //17
sql += "c_string string"; //18
sql += ")";
TestCases.ExecuteSQL(sql, conn);
using (OdbcCommand cmd = new OdbcCommand(null, conn))
{
sql = "insert into t values(?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?";
sql += ")";
OdbcParameter p1 = new OdbcParameter("?p1", OdbcType.Int);
p1.Value = 1;
cmd.Parameters.Add(p1);
OdbcParameter p2 = new OdbcParameter("?p2", OdbcType.SmallInt);
p2.Value = 11;
cmd.Parameters.Add(p2);
OdbcParameter p3 = new OdbcParameter("?p3", OdbcType.Int);
p3.Value = 111;
cmd.Parameters.Add(p3);
OdbcParameter p4 = new OdbcParameter("?p4", OdbcType.BigInt);
p4.Value = 1111;
cmd.Parameters.Add(p4);
OdbcParameter p5 = new OdbcParameter("?p5", OdbcType.Numeric);
p5.Value = 1.1;
cmd.Parameters.Add(p5);
OdbcParameter p6 = new OdbcParameter("?p6", OdbcType.Double);
p6.Value = 1.11;
cmd.Parameters.Add(p6);
OdbcParameter p7 = new OdbcParameter("?p7", OdbcType.Decimal);
p7.Value = 1.111;
cmd.Parameters.Add(p7);
OdbcParameter p8 = new OdbcParameter("?p8", OdbcType.Double);
p8.Value = 1.1111;
cmd.Parameters.Add(p8);
OdbcParameter p9 = new OdbcParameter("?p9", OdbcType.Char);
p9.Value = 'a';
cmd.Parameters.Add(p9);
OdbcParameter p10 = new OdbcParameter("?p10", OdbcType.VarChar);
p10.Value = "abcdfghijk";
cmd.Parameters.Add(p10);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
sql = "select * from t";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Assert.IsTrue(reader.GetInt32(0) == 1);
Assert.IsTrue(reader.GetInt16(1) == 11);
Assert.IsTrue(reader.GetInt32(2) == 111);
Assert.IsTrue(reader.GetInt64(3) == 1111);
Assert.IsTrue(reader.GetDecimal(4) == (decimal)1.1);
Assert.IsTrue(reader.GetFloat(5) == (float)1.11); //"Single"
Assert.IsTrue(reader.GetDecimal(6) == (decimal)1.111);
Assert.IsTrue(reader.GetDouble(7) == (double)1.1111);
Assert.IsTrue(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Assert.IsTrue(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
public void DeleteFromDB(int index)
{
OdbcCommand cmd0 = new OdbcCommand();
OdbcDataReader reader0 = null;
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма - прервано..");
cnn.Close();
return;
}
cmd0.Connection = cnn;
AddLogString(" Start>>");
cmd0.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where upper(table_name) like 'DA_V_LST_" + index.ToString() + "');";
try
{
reader0 = cmd0.ExecuteReader();
}
catch (Exception ex8)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
decimal id_cnt = 0;
if (reader0.HasRows)
{
while (reader0.Read())
{
id_cnt = reader0.GetDecimal(0);
}
}
reader0.Close();
if (id_cnt > 0)
{
AddLogString(" Сегмент описан в структуре БД ( sys_tree21 ). Удаление прервано. ");
return;
}
// Part 1
cmd0.CommandText = "drop public synonym DA_V_CAT_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop public synonym DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop public synonym ARC_DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop view DA_V_CAT_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop view DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop view ARC_DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
// Part 2
cmd0.CommandText = "delete from sys_tbllnk where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id));";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id));";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst t where table_name like 'DA_V_CAT_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl IN (SELECT id_lsttbl FROM sys_tabl_v WHERE ID = t.id)));";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
// Part 3
cmd0.CommandText = "delete from ARC_SERVICES_TUNE where id_sprofile in (select id from arc_subsyst_profile " +
" where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" AND not exists (select 1 from sys_tree21 where id_lsttbl = t.id))); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_SERVICES_ACCESS where id_sprofile in (select id from arc_subsyst_profile " +
" where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" AND not exists (select 1 from sys_tree21 where id_lsttbl = t.id))); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_SERVICES_INFO where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" AND not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from arc_subsyst_profile where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_app_serv_lst where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_READ_DEFAULTS where id_tbllst in (select id from sys_tbllst t where table_name like '%DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_READ_DEFAULTS where id_tbllst in (select id from sys_tbllst t where table_name like 'ARC_DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_READ_VIEW where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ad_sinfo where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_CAT_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
// Part 5
decimal nID = 0;
string tname = "'DA_V_LST_" + index.ToString() + "'";
cmd0.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where table_name like " + tname + ");";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
if (nID == 0)
{
cmd0.CommandText = "select id_node from sys_tbllst where table_name like " + tname + " ; ";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_lsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_dsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where id_node = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where table_name like " + tname;
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_db_part where id = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
AddLogString(" Удалено для " + tname);
}
else
{
AddLogString(" НЕ удалено для " + tname + ": используется в Навигаторе!");
}
// Part 6
nID = 0;
tname = "'ARC_DA_V_LST_" + index.ToString() + "'";
cmd0.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where table_name like " + tname + ");";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
if (nID == 0)
{
cmd0.CommandText = "select id_node from sys_tbllst where table_name like " + tname + " ; ";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_lsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_dsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where id_node = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where table_name like " + tname;
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_db_part where id = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
AddLogString(" Удалено для " + tname);
}
else
{
AddLogString(" НЕ удалено для " + tname + ": используется в Навигаторе!");
}
AddLogString(" Ready>>");
return;
}
//Create a New user and based in ID generated, add modules that the user can access.
public bool InsertUser(string firstname, string surname, string username, string email, string password,
string type, string typeAutentication, string datetime, string enabled)
{
General g = Session["app"] as General;
bool result = false;
string SQL = "INSERT INTO usertab "
+ "VALUES (NULL, '" + firstname + "', '" + surname + "', '" + username
+ "', '" + password + "', '" + email + "', '" + type + "', '" + datetime + "', '" + typeAutentication
+ "', " + enabled + ")";
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
cmd.ExecuteNonQuery();
SQL = "SELECT LAST_INSERT_ID() AS ID";
cmd.CommandText = SQL;
OdbcDataReader resultUser = cmd.ExecuteReader();
if (resultUser.HasRows)
{
ID = resultUser["ID"].ToString();
SQL = "INSERT INTO moduleusertab VALUES (1, " + ID + ", 1, 0, 0, 0)";
OdbcCommand cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (2, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (3, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (4, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (5, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (6, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (7, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (8, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
SQL = "INSERT INTO moduleusertab VALUES (9, " + ID + ", 1, 0, 0, 0)";
cmd2 = g.DB.PrepareSQL(SQL);
cmd2.ExecuteNonQuery();
}
result = true;
}
catch (OdbcException o)
{
//tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
result = false;
}
finally
{
cmd.Dispose();
/*if (tmsg.Length == 0) msg.InnerHtml = "Saved successfully!";
* else msg.InnerHtml = tmsg;*/
}
return(result);
}
void Button4Click(object sender, EventArgs e)
{
string connetionString = "DSN=rsdu2;UID=rsduadmin; PWD=passme";
//string connetionString = "DSN=rsdu2;UID=sys; PWD=passme as sysdba";
connetionString = "DSN=" + textBox1.Text +
";UID=" + textBox2.Text + ";PWD=" + textBox3.Text;
OdbcCommand cmd = new OdbcCommand();
cnn = null;
AddLogString(" РЕКОМЕНДАЦИЯ: за 1 раз создавать и использовать 1 Сегмент сбора!");
cnn = new OdbcConnection(connetionString);
try
{
cnn.Open();
AddLogString("Connection Open = Ok ");
textBox1.Enabled = false;
textBox2.Enabled = false;
textBox3.Enabled = false;
button1.Enabled = true;
button2.Enabled = true;
button3.Enabled = true;
button4.Enabled = false;
}
catch (Exception ex6)
{
AddLogString("Connection Open = NONE " + ex6.Message);
}
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма - прервано..");
cnn.Close();
return;
}
cmd.Connection = cnn; // уже созданное и открытое соединение
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Clear();
cmd.CommandText = "SET ROLE BASE_EXT_CONNECT_OIK , SBOR_STAND_ADJ, SBOR_STAND_READ";
try
{
cmd.ExecuteNonQuery();
AddLogString("Установка роли = Ok ");
}
catch (Exception ex7)
{
AddLogString("Ошибка установки Ролей =" + ex7.Message);
AddLogString(" выполнение программы - прервано..");
cnn.Close();
return;
}
Button3Click(sender, e);
return;
}
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("INSERT INTO `" + usersInRolesTable + "`" +
" (Username, Rolename, ApplicationName) " +
" Values(?, ?, ?)", conn);
OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255);
OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (OdbcException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
public void Startup()
{
if (Global.mysql_logging)
{
log.Info("Connecting to database");
mysql_conn = new OdbcConnection(Global.mysql_connection);
// Must make an initial connection
if (!DBOpen())
{
Environment.Exit(1);
}
}
while (true)
{
// End gracefully when not logging or database queue empty
if (!Global.running && (!Global.mysql_logging || DBQueueCount() == 0))
{
break;
}
// Make sure database connection is active
if (Global.mysql_logging && mysql_conn.State != ConnectionState.Open)
{
// Nothing we can do if shutting down
if (!Global.running)
{
break;
}
if (mysql_retry < DateTime.Now)
{
DBOpen();
}
if (mysql_conn.State != ConnectionState.Open)
{
// Loop to prevent database queries from executing
trigger.WaitOne(new TimeSpan(0, 0, 1));
continue;
}
}
// Sleep when not logging or database queue empty
if (!Global.mysql_logging || DBQueueCount() == 0)
{
trigger.WaitOne(new TimeSpan(0, 0, 1));
continue;
}
// Grab a copy in case the database query fails
string query;
lock (mysql_lock)
query = mysql_queue.Peek();
try
{
// Execute the database query
mysql_command = new OdbcCommand(query, mysql_conn);
mysql_command.ExecuteNonQuery();
// Successful remove query from queue
lock (mysql_lock)
mysql_queue.Dequeue();
}
catch (Exception ex)
{
if (mysql_conn.State != ConnectionState.Open)
{
log.Warn("Lost connection to database");
}
else
{
log.Error("Error executing query\r\n" + query, ex);
// Prevent an endless loop from failed query
lock (mysql_lock)
mysql_queue.Dequeue();
}
}
}
if (Global.mysql_logging)
{
DBClose();
}
}
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM `" + rolesTable + "`" +
" WHERE Rolename = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcCommand cmd2 = new OdbcCommand("DELETE FROM `" + usersInRolesTable + "`" +
" WHERE Rolename = ? AND ApplicationName = ?", conn);
cmd2.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (OdbcException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return(false);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return(true);
}
protected void btnYes_Click(object sender, EventArgs e)
{
if (ViewState["action"].ToString() == "Save")
{
#region save
con = obje.NewConnection();
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
if (btnSave.Text == "Save")
{
OdbcTransaction odbTrans = null;
try
{
odbTrans = con.BeginTransaction();
OdbcCommand f11 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f11.CommandType = CommandType.StoredProcedure;
f11.Parameters.AddWithValue("tblname", "m_userprevsetting");
f11.Parameters.AddWithValue("attribute", "prev_level");
f11.Parameters.AddWithValue("conditionv", "prev_level =" + int.Parse(txtUserlevel.Text) + " and rowstatus<>2");
OdbcDataAdapter dacnt3 = new OdbcDataAdapter(f11);
DataTable dtt3 = new DataTable();
f11.Transaction = odbTrans;
dacnt3.Fill(dtt3);
if (dtt3.Rows.Count > 0)
{
lblOk.Text = "Level already exists"; lblHead.Text = "Tsunami ARMS - Warning";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
return;
}
//user prev setting
if (cmbExecute.SelectedItem.ToString() == "Yes")
{
b = 1;
}
else if (cmbExecute.SelectedItem.ToString() == "No")
{
b = 0;
}
OdbcCommand f12 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f12.CommandType = CommandType.StoredProcedure;
f12.Parameters.AddWithValue("tblname", "m_sub_form");
f12.Parameters.AddWithValue("attribute", "form_id");
f12.Parameters.AddWithValue("conditionv", "displayname='" + cmbDefault.SelectedItem.Text.ToString() + "' and status<>'2'");
f12.Transaction = odbTrans;
OdbcDataAdapter dacnt31 = new OdbcDataAdapter(f12);
DataTable dtt31 = new DataTable();
dacnt31.Fill(dtt31);
fid = Convert.ToInt32(dtt31.Rows[0][0].ToString());
id = Convert.ToInt32(Session["userid"].ToString());
OdbcCommand cmd5 = new OdbcCommand("CALL savedata(?,?)", con);
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.AddWithValue("tblname", "m_userprevsetting");
string aaa = "" + int.Parse(txtUserlevel.Text) + "," + fid + ",'" + b + "'," + id + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "";
cmd5.Parameters.AddWithValue("val", "" + int.Parse(txtUserlevel.Text) + "," + fid + ",'" + b + "'," + id + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "");
cmd5.Transaction = odbTrans;
cmd5.ExecuteNonQuery();
OdbcCommand Del = new OdbcCommand("DELETE from m_userprev_formset where prev_level=" + int.Parse(txtUserlevel.Text) + "", con);
Del.Transaction = odbTrans;
Del.ExecuteNonQuery();
OdbcCommand cmd3 = new OdbcCommand("select max(prev_forms_id) from m_userprev_formset", con);
cmd3.Transaction = odbTrans;
if (Convert.IsDBNull(cmd3.ExecuteScalar()) == true)
{
o = 1;
}
else
{
o = Convert.ToInt32(cmd3.ExecuteScalar());
o = o + 1;
}
for (k = 0; k < lstSelectedform.Items.Count; k++)
{
OdbcCommand cmd2 = new OdbcCommand("select max(prev_forms_id) from m_userprev_formset", con);
cmd2.Transaction = odbTrans;
if (Convert.IsDBNull(cmd2.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd2.ExecuteScalar());
n = n + 1;
}
OdbcCommand f13 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f13.CommandType = CommandType.StoredProcedure;
f13.Parameters.AddWithValue("tblname", "m_sub_form");
f13.Parameters.AddWithValue("attribute", "form_id");
f13.Parameters.AddWithValue("conditionv", "displayname='" + lstSelectedform.Items[k].Text.ToString() + "' and status<>'2'");
OdbcDataAdapter dacnt32 = new OdbcDataAdapter(f13);
f13.Transaction = odbTrans;
DataTable dtt32 = new DataTable();
dacnt32.Fill(dtt32);
fid1 = Convert.ToInt32(dtt32.Rows[0][0].ToString());
OdbcCommand cmd6 = new OdbcCommand("CALL savedata(?,?)", con);
cmd6.CommandType = CommandType.StoredProcedure;
cmd6.Parameters.AddWithValue("tblname", "m_userprev_formset");
cmd6.Parameters.AddWithValue("val", "" + n + "," + int.Parse(txtUserlevel.Text) + "," + fid1 + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "");
cmd6.Transaction = odbTrans;
cmd6.ExecuteNonQuery();
}
odbTrans.Commit();
cmbDefault.SelectedIndex = -1;
cmbExecute.SelectedIndex = -1;
con.Close();
clear();
dguserlevel();
lblOk.Text = " Data saved successfully "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in saving ");
}
}
#endregion
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
else if (ViewState["action"].ToString() == "Edit")
{
#region edit
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
listboxselection.Enabled = false;
vlistbox.Enabled = false;
btnSave.CausesValidation = false;
con = obje.NewConnection();
OdbcTransaction odbTrans = null;
try
{
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
odbTrans = con.BeginTransaction();
#region log table
OdbcCommand cmd1 = new OdbcCommand("select max(rowno) from m_userprevsetting_log", con);
cmd1.Transaction = odbTrans;
if (Convert.IsDBNull(cmd1.ExecuteScalar()) == true)
{
rn = 1;
}
else
{
rn = Convert.ToInt32(cmd1.ExecuteScalar());
rn = rn + 1;
}
OdbcCommand cmd46p = new OdbcCommand("CALL selectcond(?,?,?)", con);
cmd46p.CommandType = CommandType.StoredProcedure;
cmd46p.Parameters.AddWithValue("tblname", "m_userprevsetting");
cmd46p.Parameters.AddWithValue("attribute", "*");
cmd46p.Parameters.AddWithValue("conditionv", "prev_level=" + q + "");
cmd46p.Transaction = odbTrans;
OdbcDataAdapter dacnt46p = new OdbcDataAdapter(cmd46p);
DataTable dtt46p = new DataTable();
dacnt46p.Fill(dtt46p);
OdbcCommand cmd55 = new OdbcCommand("CALL savedata(?,?)", con);
cmd55.CommandType = CommandType.StoredProcedure;
cmd55.Parameters.AddWithValue("tblname", "m_userprevsetting_log");
DateTime Date1 = DateTime.Parse(dtt46p.Rows[0]["createdon"].ToString());
string Date2 = Date1.ToString("yyyy-MM-dd HH:mm:ss");
string aaa = "" + Convert.ToInt32(dtt46p.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["defaultform_id"]) + ",'" + dtt46p.Rows[0]["execoverride"].ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["userid"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["createdby"]) + ",'" + Date2.ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["rowstatus"]) + "," + rn + "";
cmd55.Parameters.AddWithValue("val", "" + Convert.ToInt32(dtt46p.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["defaultform_id"]) + ",'" + dtt46p.Rows[0]["execoverride"].ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["userid"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["createdby"]) + ",'" + Date2.ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["rowstatus"]) + "," + rn + "");
cmd55.Transaction = odbTrans;
cmd55.ExecuteNonQuery();
OdbcCommand crr1 = new OdbcCommand("delete from m_userprev_formset_log where prev_level=" + q + "", con);
crr1.Transaction = odbTrans;
crr1.ExecuteNonQuery();
OdbcCommand cmd6 = new OdbcCommand("select max(rowno)from m_userprev_formset_log", con);
cmd6.Transaction = odbTrans;
if (Convert.IsDBNull(cmd6.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd6.ExecuteScalar());
n = n + 1;
}
OdbcCommand f16 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f16.CommandType = CommandType.StoredProcedure;
f16.Parameters.AddWithValue("tblname", "m_userprev_formset");
f16.Parameters.AddWithValue("attribute", "*");
f16.Parameters.AddWithValue("conditionv", "prev_level=" + q + "");
f16.Transaction = odbTrans;
OdbcDataAdapter dacnt3o = new OdbcDataAdapter(f16);
DataTable dtt3o = new DataTable();
dacnt3o.Fill(dtt3o);
OdbcCommand cmd24 = new OdbcCommand("CALL savedata(?,?)", con);
cmd24.CommandType = CommandType.StoredProcedure;
cmd24.Parameters.AddWithValue("tblname", "m_userprev_formset_log");
DateTime Date4 = DateTime.Parse(dtt3o.Rows[0]["createdon"].ToString());
string Date5 = Date4.ToString("yyyy-MM-dd HH:mm:ss");
cmd24.Parameters.AddWithValue("val", "" + Convert.ToInt32(dtt3o.Rows[0]["prev_forms_id"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["form_id"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["createdby"]) + ",'" + Date5.ToString() + "'," + "1" + "," + n + "");
cmd24.Transaction = odbTrans;
cmd24.ExecuteNonQuery();
#endregion
OdbcCommand defa1 = new OdbcCommand("select form_id from m_sub_form where displayname='" + cmbDefault.SelectedItem.Text.ToString() + "' and status<>'2'", con);
defa1.Transaction = odbTrans;
OdbcDataReader defr1 = defa1.ExecuteReader();
if (defr1.Read())
{
fid = Convert.ToInt32(defr1["form_id"].ToString());
}
id = Convert.ToInt32(Session["userid"].ToString());
m1 = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
OdbcCommand cmd25 = new OdbcCommand("call updatedata(?,?,?)", con);
if (cmbExecute.SelectedItem.ToString() == "Yes")
{
b = 1;
}
else if (cmbExecute.SelectedItem.ToString() == "No")
{
b = 0;
}
cmd25.CommandType = CommandType.StoredProcedure;
cmd25.Parameters.AddWithValue("tablename", "m_userprevsetting");
cmd25.Parameters.AddWithValue("valu", "prev_level=" + int.Parse(txtUserlevel.Text) + ",defaultform_id=" + fid + ",execoverride=" + b + ",userid=" + id + ",updateddate='" + dat + "',rowstatus=" + "1" + "");
cmd25.Parameters.AddWithValue("convariable", "prev_level=" + m1 + "");
cmd25.Transaction = odbTrans;
cmd25.ExecuteNonQuery();
OdbcCommand crr = new OdbcCommand("delete from m_userprev_formset where prev_level=" + m1 + "", con);
crr.Transaction = odbTrans;
crr.ExecuteNonQuery();
for (k = 0; k < lstSelectedform.Items.Count; k++)
{
OdbcCommand cmd6a = new OdbcCommand("select max(prev_forms_id)from m_userprev_formset", con);
cmd6a.Transaction = odbTrans;
if (Convert.IsDBNull(cmd6a.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd6a.ExecuteScalar());
n = n + 1;
}
OdbcCommand f15 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f15.CommandType = CommandType.StoredProcedure;
f15.Parameters.AddWithValue("tblname", "m_sub_form");
f15.Parameters.AddWithValue("attribute", "form_id");
f15.Parameters.AddWithValue("conditionv", "displayname='" + lstSelectedform.Items[k].Text.ToString() + "' and status<>'2'");
OdbcDataAdapter dacnt3a = new OdbcDataAdapter(f15);
f15.Transaction = odbTrans;
DataTable dtt3a = new DataTable();
dacnt3a.Fill(dtt3a);
fid1 = Convert.ToInt32(dtt3a.Rows[0]["form_id"].ToString());
OdbcCommand cmd61 = new OdbcCommand("CALL savedata(?,?)", con);
cmd61.CommandType = CommandType.StoredProcedure;
cmd61.Parameters.AddWithValue("tblname", "m_userprev_formset");
cmd61.Parameters.AddWithValue("val", "" + n + "," + int.Parse(txtUserlevel.Text) + "," + fid1 + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "1" + "");
cmd61.Transaction = odbTrans;
cmd61.ExecuteNonQuery();
}
odbTrans.Commit();
btnSave.Text = "Save";
cmbExecute.SelectedIndex = -1;
cmbDefault.SelectedIndex = -1;
con.Close();
clear();
dguserlevel();
lblOk.Text = " Data updated successfully "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in saving ");
}
#endregion
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
#region CHECK PRIVILEGE LEVEL IS ALREADY EXISTS OR NOT
else if (ViewState["action"].ToString() == "Level")
{
this.ScriptManager1.SetFocus(txtUserlevel);
txtUserlevel.Text = Session["prevlevel"].ToString();
lstSelectform.SelectedIndex = -1;
con = obje.NewConnection();
OdbcCommand PLevel = new OdbcCommand("DELETE from m_userprevsetting WHERE prev_level=" + int.Parse(txtUserlevel.Text) + "", con);
PLevel.ExecuteNonQuery();
con.Close();
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
#endregion
else if (ViewState["action"].ToString() == "Delete")
{
#region delete
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
con = obje.NewConnection();
OdbcTransaction odbTrans = null;
try
{
odbTrans = con.BeginTransaction();
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
id = Convert.ToInt32(Session["userid"].ToString());
OdbcCommand cmd28 = new OdbcCommand("call updatedata(?,?,?)", con);
cmd28.CommandType = CommandType.StoredProcedure;
cmd28.Parameters.AddWithValue("tablename", "m_userprevsetting");
cmd28.Parameters.AddWithValue("valu", "rowstatus=" + "2" + ",userid=" + id + "");
cmd28.Parameters.AddWithValue("convariable", "prev_level=" + q + "");
cmd28.Transaction = odbTrans;
cmd28.ExecuteNonQuery();
OdbcCommand cmd29 = new OdbcCommand("CALL updatedata(?,?,?)", con);
cmd29.CommandType = CommandType.StoredProcedure;
cmd29.Parameters.AddWithValue("tablename", "m_userprev_formset");
cmd29.Parameters.AddWithValue("valu", "rowstatus=" + "2" + "");
cmd29.Parameters.AddWithValue("convariable", "prev_level=" + q + "");
cmd29.Transaction = odbTrans;
cmd29.ExecuteNonQuery();
odbTrans.Commit();
lblOk.Text = " Data successfully deleted "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
con.Close();
clear();
dguserlevel();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in Deleting ");
}
#endregion
}
}
static void Main(string[] args)
{
string constr = "server=localhost;user=root;database=lnm;port=3306;password='';";
MySqlConnection conn = new MySqlConnection(constr);
Console.WriteLine("Connecting to Mysql");
conn.Open();
//A select statement to get all rows that do not have code filled in as per said. Also These should contain the unused/ unchecked codes.
string strSQL = "SELECT * FROM `lnm`.`sms_in` WHERE `sms_text` LIKE '%Confirmed.%' AND `sender_number` IS NOT NULL AND (`code` = '' OR `code` IS NULL)";
MySqlCommand cmd = new MySqlCommand(strSQL, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strText = dr[1].ToString();
//Console.WriteLine(strText);
//Console.ReadLine();
string[] arrText = strText.Split(' ');
//AA64SF914 Confirmed.on 17/2/14 at 12:02 PMKsh33,950.00 received from 254727137226 Mark ADAMS.New Account balance is Ksh2,550.00
//These are the values that should be inserted to the NULL columns
/*
Response.Write(arrText[0]); //TranCode
Response.Write(brk);
Response.Write(arrText[5]); //amount
Response.Write(brk);
Response.Write(arrText[9]); //fname
Response.Write(brk);
Response.Write(arrText[10]); //lname
Response.Write(brk);
Response.Write(arrText[8]); //msisdn
Response.Write(brk);
*/
//Ignore. These are the other text fields to be used as tests
//Here I'll be getting the row ID and the update will occur per Id That fulfills the said condition
string ID1 = dr["id"].ToString();
//Response.Write(ID1);
//****************************************
var charsToRemove = new string[] { "\n", "PMKsh", "AMKsh", "Ksh", ",", ".New", "'" };
string str = arrText[5];
string strfn = arrText[9];
string strln = arrText[10];
foreach (var c in charsToRemove)
{
str = str.Replace(c, string.Empty);
strfn = strfn.Replace(c, string.Empty);
strln = strln.Replace(c, string.Empty);
}
arrText[5] = str;
arrText[9] = strfn;
arrText[10] = strln;
/*//****************************************
Response.Write(brk);
Response.Write(str);
Response.Write(brk);
Response.Write(strln);
//****************************************
*/
strSQL = "Update `lnm`.`sms_in` SET `code` = '" + arrText[0] + "', `amount` = '" + arrText[5] + "', `firstname` = '" + arrText[9] + "', `lastname` = '" + arrText[10] + "', `msisdn` = '" + arrText[8] + "' WHERE `id` = " + ID1;
OdbcCommand update = new OdbcCommand(strSQL, con);
//Response.Write(brk);
//Response.Write(strSQL);
update.ExecuteNonQuery();
} //end of the while loop
}
