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>
/// 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);
}
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
}
// <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 ");
}
}