/// <summary>
/// Trae los datos del prestamo que sera eliminado
/// </summary>
/// <param name="codigo">Codigo del prestamo a eliminar</param>
private void getDatosPrestamos(string codigo)
{
System.Data.Odbc.OdbcDataReader dr = null;
Prestamo objPrestamo = new Prestamo();
string sqlselect = "SELECT codigo, valor, porcentaje, total, fecha_inicio, fecha_final, cuotas, valor_cuota, cedula_cliente, saldo";
sqlselect += " FROM tprestamo WHERE codigo = '" + codigo + "'";
dr = objPrestamo.getObjDatareader(sqlselect);
if (dr.HasRows)
{
TXTvalor.Text = dr.GetString(1);
TXTporcent.Text = dr.GetString(2);
TXTtotal.Text = dr.GetString(3);
TXTnumcuotas.Text = dr.GetString(6);
TXTvalorcuota.Text = dr.GetString(7);
TXTsaldo.Text = dr.GetString(9);
LBLcodigo.Text = dr.GetString(0);
TXTfechaini.Text = dr.GetDateTime(4).ToShortDateString();
TXTfechafin.Text = dr.GetDateTime(5).ToShortDateString();
}
else
{
this.ReestablecerPrestamo();
}
}
private void Form4_Load(object sender, EventArgs e)
{
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(gbCn))
{
try
{
conn.Open();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand("SELECT name, database_id, create_date FROM sys.databases", conn);
System.Data.Odbc.OdbcDataReader reader = command.ExecuteReader();
//agrega nombre de base datos
comboBox3.Items.Clear();
while (reader.Read())
{
comboBox3.Items.Add(reader[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Ocurrio un error al obtener las bases de datos");
Console.WriteLine("Ocurrio un error al obtener las bases de datos");
Console.WriteLine(ex.Message);
conn.Close();
this.Hide();
}
//MessageBox.Show("ODBC Conexión Satisfatoria");
//onsole.WriteLine("ODBC Connection test PASSED!");
conn.Close();
}
}
/// <summary>
/// 返回指定Sql语句的DbDataReader
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DBDataReader对象</returns>
public virtual DbDataReader GetDataReader(string sqlstr)
{
//程序不能返回抽象类，故用分数据访问分工来生成DataReader对象
if (databaseType == "Oracle")
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleDataReader oraDr = cmd.ExecuteReader();
return(oraDr);
}
else if (databaseType == "SqlServer")
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader sqlDr = cmd.ExecuteReader();
return(sqlDr);
}
else if (databaseType == "Sybase")
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbDataReader oleDr = cmd.ExecuteReader();
return(oleDr);
}
else
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader odbcDr = cmd.ExecuteReader();
return(odbcDr);
}
}
private Message MessageFromResults(System.Data.Odbc.OdbcDataReader results)
{
Message message = new Message();
message.FromID = results["FromID"].ToString();;
message.ToID = results["ToID"].ToString();;
message.FromName = results["FromName"].ToString();;
message.ToName = results["ToName"].ToString();;
message.OwnerID = results["ownerid"].ToString();;
message.Subject = results["Subject"].ToString();;
message.MessageText = results["Message"].ToString();;
message.DateSent = DateTime.Parse(results["DateSent"].ToString());
message.DateRead = DateTime.Parse(results["DateRead"].ToString());;
if (results["viewed"].ToString() == "1")
{
message.Read = true;
}
else
{
message.Read = false;
}
message.Mailbox = results["mailbox"].ToString();;
message.ID = Int16.Parse(results["ID"].ToString());;
message.ThreadID = Int16.Parse(results["ThreadID"].ToString());;
return(message);
}
private UserGroup PopulateUserGroupFromReader(System.Data.Odbc.OdbcDataReader resultSet)
{
UserGroup group = new UserGroup();
group.GroupName = resultSet["groupname"].ToString();
group.Owner = resultSet["Owner"].ToString();
group.Description = resultSet["Description"].ToString();
group.PolicyLevel = (GroupPolicyLevel)System.Convert.ToInt16(resultSet["PermissionLevel"].ToString());
group.Users = GetGroupsUsers(group.GroupName);
return(group);
}
/// <summary>
/// Devuelve el nombre del usuario actualmente conectado dependiendo de la identificacion
/// </summary>
/// <param name="identificacion">Identificacion del usuario (normalmente es la cedula)</param>
/// <returns></returns>
public string getNombreUsuario(string identificacion)
{
string sql = "SELECT nombre FROM tlogin WHERE usuario = '" + identificacion + "'";
string res = null;
System.Data.Odbc.OdbcDataReader dr = conex.getDataSelect_SQL(sql, conex.getConexion());
if (dr.HasRows)
{
dr.Read();
res = dr.GetString(0).ToUpper();
}
return(res);
}
private void button4_Click(object sender, EventArgs e)
{
String connDsnName = "DSN=" + gbInstance + "; DataBase=" + gbDB + ";Uid=" + gbUser + "; Pwd=" + gbPass + ";";
String SqlQuery = richTextBox1.Text;
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connDsnName))
{
try
{
conn.Open();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(SqlQuery, conn);
System.Data.Odbc.OdbcDataReader reader = command.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(reader);
dataGridView1.DataSource = dataTable;
//DataTable schemaTable = reader.GetSchemaTable();
/*foreach (DataRow row in schemaTable.Rows)
* {
* foreach (DataColumn column in schemaTable.Columns)
* {
* Console.WriteLine(String.Format("{0} = {1}",
* column.ColumnName, row[column]));
* }
* }*/
//dataGridView1.Update();
//while (reader.Read())
//{
//dataGridView1.Rows.Add("five", "six", "seven", "eight");
//MessageBox.Show(reader[0].ToString());
// MessageBox.Show(reader[1].ToString());
//}
dataGridView1.Update();
}
catch (Exception ex)
{
MessageBox.Show("Ocurrio un error al realalizar la consulta");
Console.WriteLine("Ocurrio un error al realalizar la consulta");
Console.WriteLine(ex.Message);
conn.Close();
this.Hide();
}
conn.Close();
button3.Enabled = true;
}
}
public authenticate()
{
InitializeComponent();
ConStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; PORT=3306; DATABASE=authenticate; UID=auth; PWD=;OPTION=3";
AuthCon = new System.Data.Odbc.OdbcConnection(ConStr);
try
{
if (AuthCon.State == ConnectionState.Closed)
{
AuthCon.Open();
}
//MessageBox.Show("Connection Opened");
}
catch (System.Data.Odbc.OdbcException Ex)
{
MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
#region if password not set
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass", AuthCon);
AuthRead = AuthCom.ExecuteReader();
string chkpass;
string[] name = { "admin", "guest" };
int term = 0;
while (AuthRead.Read())
{
chkpass = (string)AuthRead[0];
if (chkpass == "")
{
MessageBox.Show(" Please set the password for " + name[term]);
txtbxusername.Text = name[term];
txtbxusername.ReadOnly = true;
initialset = true;
break;
}
if (++term > 1)
{
break;
}
}
#endregion
}
private string GetColumnImage(string imageID, string column, string fileName)
{
string retImageFullName = "";
if (_odbcConnection == null)
{
return(retImageFullName);
}
//ODBC Access
System.Data.Odbc.OdbcCommand odbcCommand = null;
System.Data.Odbc.OdbcDataReader odbcRead = null;
try
{
odbcCommand = new System.Data.Odbc.OdbcCommand();
odbcCommand.CommandText = "SELECT " + column + " FROM DBSDESX.CHVT34A0_IMAGENS WHERE CREFARQ = '" + imageID + "'";
odbcCommand.Connection = _odbcConnection;
odbcRead = odbcCommand.ExecuteReader(); //System.Data.CommandBehavior.SequentialAccess
//byte[] _buf = (byte[]) odbcCommand.ExecuteScalar();
//if (!odbcRead.Read())
// return retImageFullName; //not found
}
catch (System.Exception ex)
{
throw ex;
}
//Read BLOB
System.Text.StringBuilder sb = new System.Text.StringBuilder();
int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
long retval;
long startIndex = 0;
try
{
// Read bytes into outByte[] and retain the number of bytes returned.
retval = odbcRead.GetBytes(0, startIndex, outByte, 0, bufferSize);
// Continue while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
//Write the buffer. (convert byte to char)
for (int i = 0; i < retval; i++)
{
sb.Append(System.Convert.ToChar(outByte[i]));
}
// Reposition start index to end of last buffer and fill buffer.
startIndex += bufferSize;
retval = odbcRead.GetBytes(0, startIndex, outByte, 0, bufferSize);
}
// Write the remaining buffer. (convert byte to char)
for (int i = 0; i < retval; i++)
{
sb.Append(System.Convert.ToChar(outByte[i]));
}
}
catch
{
}
//Close DB2 reader
try
{
odbcRead.Close();
odbcCommand.Dispose();
}
catch (System.Exception ex)
{
throw ex;
}
//Create file
try
{
if (sb.Length > 0)
{
string myDocFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonPictures) + "\\";
string text = sb.ToString();
byte[] base64EncodedBytes = System.Convert.FromBase64String(text);
System.IO.File.WriteAllBytes(myDocFolder + fileName, base64EncodedBytes);
retImageFullName = myDocFolder + fileName;
}
}
catch (System.Exception ex)
{
throw ex;
}
return(retImageFullName);
}
// <summary>
/// recompute indexes for selected table
/// to speed up queries
/// </summary>
/// <returns></returns>
public static void UpdateIndexes(string tableType, DbConnect dbConnect)
{
ProtCidSettings.progressInfo.ResetCurrentProgressInfo();
DbQuery dbQuery = new DbQuery();
try
{
System.Data.Odbc.OdbcCommand updateIndexCommand = dbConnect.CreateCommand();
// retrieve user-defined indexes
string showIndexStr = @"select RDB$INDEX_NAME, RDB$RELATION_NAME from RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0;";
//string showIndexStr = @"select RDB$INDEX_NAME from RDB$INDICES;";
updateIndexCommand.CommandText = showIndexStr;
System.Data.Odbc.OdbcDataReader indexReader = updateIndexCommand.ExecuteReader();
// ArrayList indexList = new ArrayList ();
Dictionary <string, List <string> > relationIndexHash = new Dictionary <string, List <string> > ();
string indexName = "";
string relationName = "";
if (indexReader.HasRows)
{
while (indexReader.Read())
{
relationName = indexReader.GetString(1).Trim().ToUpper();
if (relationName.IndexOf(tableType.ToUpper()) > -1)
{
indexName = indexReader.GetString(0).Trim().ToUpper();
if (relationIndexHash.ContainsKey(relationName))
{
relationIndexHash[relationName].Add(indexName);
}
else
{
List <string> relationIndexList = new List <string> ();
relationIndexList.Add(indexName);
relationIndexHash.Add(relationName, relationIndexList);
}
}
}
indexReader.Close();
}
foreach (string relationTable in relationIndexHash.Keys)
{
foreach (string index in relationIndexHash[relationTable])
{
// rebuild this index for cryst and interface
if (indexName.ToString().ToUpper().IndexOf("RDB$PRIMARY") == -1)
{
string inactiveIndexStr = string.Format("ALTER INDEX {0} INACTIVE;", index);
updateIndexCommand.CommandText = inactiveIndexStr;
updateIndexCommand.ExecuteNonQuery();
string activeIndexStr = string.Format("ALTER INDEX {0} ACTIVE;", index);
updateIndexCommand.CommandText = activeIndexStr;
updateIndexCommand.ExecuteNonQuery();
}
// recompute selectivity of this index
string updateSelectivityStr = string.Format("SET STATISTICS INDEX {0};", index);
updateIndexCommand.CommandText = updateSelectivityStr;
updateIndexCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
// Displays the Error Message in the progress label.
ProtCidSettings.progressInfo.progStrQueue.Enqueue("Update Indexes Errors: " + ex.Message);
}
}
private void SummReport_Load(object sender, EventArgs e)
{
ConStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" + constring.hostname + "; PORT=" + constring.portno + "; DATABASE=" + constring.database + "; UID=" + constring.username + "; PWD=" + constring.password + ";OPTION=3";
OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);
OdbcCon.Open();
radioButton2.Checked = true;
#region region 01
string CellSelectionStr;
string QueryStr_PartI = "SELECT count(*), sum(CallCost), sum(Duration)";
string QueryStr = "";
string QueryStr_PartII = "";
string QueryStr_PartIII = "";
int No_of_Months;
int MonthStart = forform1.stat_datetimestart.Month;
int MonthEnd = forform1.stat_datetimeend.Month;
string MonthString;
string WhereConditionStr = "";
if ((MonthEnd - MonthStart) < 0)
{
No_of_Months = MonthEnd - MonthStart + 12 + 1;
}
else
{
No_of_Months = MonthEnd - MonthStart + 1;
}
#endregion
#region region 02
for (int ChargeClassNo = 1; ChargeClassNo <= 11; ChargeClassNo++)
{
forChargeClass.TotalRec [ChargeClassNo - 1] = 0;
forChargeClass.sumCallCost[ChargeClassNo - 1] = 0;
forChargeClass.SumTotalDuration[ChargeClassNo - 1] = 0;
WhereConditionStr = string.Concat(" ChargeClass = " + ChargeClassNo.ToString() + " and ");
for (int n = 0; n <= 4; n++) //No of Services 0 for pcc , ..... , 4 for uan
{
if (forform1.stat_flagservices[n] == false)
{
continue;
}
for (int MonthNo = MonthStart; MonthNo <= MonthEnd; MonthNo++)
{
#region for No of Months
if (MonthNo > 9)
{
MonthString = MonthNo.ToString();
}
else
{
MonthString = string.Concat("0", MonthNo.ToString());
}
QueryStr_PartII = " from " + ServiceStr[n] + MonthString + " where " + WhereConditionStr;
if (MonthStart == MonthEnd)
{
QueryStr_PartIII = string.Concat("day(StopDateAndTime)>=" + forform1.stat_datetimestart.Day + " and day(StopDateAndTime)<=" + forform1.stat_datetimeend.Day);
}
else if (MonthNo == MonthStart)
{
QueryStr_PartIII = string.Concat("day(StopDateAndTime)>=" + forform1.stat_datetimestart.Day);
}
else if (MonthNo != MonthEnd && MonthNo != MonthStart)
{
QueryStr_PartIII = string.Concat("month(StopDateAndTime)=" + MonthNo.ToString());
}
else if (MonthNo == MonthEnd)
{
QueryStr_PartIII = string.Concat("day(StopDateAndTime)<=" + forform1.stat_datetimeend.Day);
}
QueryStr = string.Concat(QueryStr_PartI, QueryStr_PartII, QueryStr_PartIII);
OdbcCom = new System.Data.Odbc.OdbcCommand(QueryStr, OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
while (OdbcDR.Read()) //rows
{
forChargeClass.TotalRec [ChargeClassNo - 1] += Int64.Parse(OdbcDR[0].ToString());
if (forChargeClass.TotalRec [ChargeClassNo - 1] == 0)
{
break;
}
//MessageBox.Show(QueryStr+ " "+ forChargeClass.TotalRec [ChargeClassNo - 1].ToString() +" forChargeClass.sumCallCost["+ChargeClassNo.ToString() +"- 1]"+" "+OdbcDR[1].ToString());
if (Int64.Parse(OdbcDR[0].ToString()) != 0)
{
forChargeClass.sumCallCost[ChargeClassNo - 1] += Int64.Parse(OdbcDR[1].ToString()) / 10000;
}
if (Int64.Parse(OdbcDR[0].ToString()) != 0)
{
forChargeClass.SumTotalDuration[ChargeClassNo - 1] += Int64.Parse(OdbcDR[2].ToString());
}
}
#endregion
}
}
}
forChargeClass.TotalRec [11] = 0; forChargeClass.sumCallCost[11] = 0; forChargeClass.SumTotalDuration[11] = 0;
for (int ChargeClassNo = 1; ChargeClassNo <= 11; ChargeClassNo++)
{
forChargeClass.TotalRec [11] += forChargeClass.TotalRec [ChargeClassNo - 1];
forChargeClass.sumCallCost[11] += forChargeClass.sumCallCost[ChargeClassNo - 1];
forChargeClass.SumTotalDuration[11] += forChargeClass.SumTotalDuration[ChargeClassNo - 1];
}
#endregion
txtBxCC.Text = "1";
}
private void btnok_Click(object sender, EventArgs e)
{
if (initialset == true)
{
string newpass = txtbxpassword.Text;
newpass = EncodePass(newpass, newpass.Length);
//MessageBox.Show(newpass);
AuthCom = new System.Data.Odbc.OdbcCommand("UPDATE mis_pass SET passwd='" + newpass + "' WHERE username='******'", AuthCon);
AuthCom.ExecuteNonQuery();
MessageBox.Show(" New password updated. please restart application");
validity.cancelled = true;
AuthCon.Close();
this.Close();
}
else if (initialset == false)
{
if (txtbxusername.Text == "")
{
MessageBox.Show(" Please Specify Username ");
}
else if (AuthCon.State == ConnectionState.Open)
{
string passwd = "";
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass WHERE username='******'", AuthCon);
AuthRead = AuthCom.ExecuteReader();
while (AuthRead.Read())
{
passwd = (String)AuthRead[0];
}
//MessageBox.Show(passwd);
if (passwd.CompareTo("") == 0)
{
MessageBox.Show(" Username invalid ");
}
else if (passwd.CompareTo("") != 0)
{
passwd = DecodePass(passwd, passwd.Length);
//MessageBox.Show(passwd);
if (passwd.CompareTo(txtbxpassword.Text) == 0)
{
validity.authenticated = true;
if (txtbxusername.Text == "admin")
{
validity.uservalue = true;
}
else
{
validity.uservalue = false;
}
}
else
{
validity.authenticated = false;
}
AuthCon.Close();
this.Close();
}
}
}
}
private void btnOKnp_Click(object sender, EventArgs e)
{
if (txtBxnewuser.Text == "")
{
MessageBox.Show(" Username not specified ");
}
else if (txtBxnewuser.Text != "")
{
if (AuthCon.State == ConnectionState.Open)
{
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT username FROM mis_pass", AuthCon);
AuthRead = AuthCom.ExecuteReader();
string usrnm = "";
bool usrchek = false;//indicate that user does not exist
while (AuthRead.Read())
{
usrnm = (string)AuthRead[0];
if (usrnm.CompareTo(txtBxnewuser.Text) == 0)
{
usrchek = true; // user exists
break;
}
}
if (usrchek == false)
{
MessageBox.Show(" Username name does not exist. Please re-check ");
}
else if (usrchek == true)
{
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass WHERE username='******'", AuthCon);
AuthRead = AuthCom.ExecuteReader();
string passwd = "";
bool passchek = false;//indicate that password is invalid
while (AuthRead.Read())
{
passwd = (string)AuthRead[0];
passwd = DecodePass(passwd, passwd.Length);
if (passwd.CompareTo(txtBxoldpass.Text) == 0)
{
passchek = true; // password valid
break;
}
else if (passwd.CompareTo(txtBxoldpass.Text) != 0)
{
MessageBox.Show(" Invalid Password ");
}
}
if (passchek == true)
{
if (txtBxnewpass.Text.CompareTo(txtBxconfirm.Text) != 0)
{
MessageBox.Show("The Passwords do not match. Please Retype");
txtBxconfirm.Text = txtBxnewpass.Text = "";
}
else if (txtBxnewpass.Text.CompareTo(txtBxconfirm.Text) == 0)
{
string newpass = txtBxconfirm.Text;
newpass = EncodePass(newpass, newpass.Length);
AuthCom = new System.Data.Odbc.OdbcCommand("UPDATE mis_pass SET passwd='" + newpass + "' WHERE username='******'", AuthCon);
AuthCom.ExecuteNonQuery();
MessageBox.Show(" Password changed successfully ");
}
}
else if (passchek == false)
{
MessageBox.Show(" Invalid Password ");
}
}
}
}
else if (AuthCon.State == ConnectionState.Closed)
{
MessageBox.Show(" Connection closed ");
}
}
