public int catchDB(DateTime date, string category)
{
string datedb = date.ToString("yyyy-MM-dd");
int result;
MySqlConnection con = null;
MySqlCommand command, command2;
con = new MySqlConnection(conectabanco);
command = new MySqlCommand("SELECT SUM(Tempo) FROM `concurso` WHERE Categoria = '" + category + "' AND DataRevisao = '" + datedb + "'", con);
command2 = new MySqlCommand("SELECT count(DataRevisao) FROM concurso WHERE Categoria = '" + category + "' AND DataRevisao = '" + datedb + "'", con);
con.Open();
MySqlDataReader Reader1, Reader2;
Reader2 = command2.ExecuteReader();
Reader2.Read();
string valid = Reader2.GetString(0);
Reader2.Close();
if (Convert.ToInt32(valid) > 0)
{
Reader1 = command.ExecuteReader();
Reader1.Read();
result = Convert.ToInt32(Reader1.GetString(0));
Reader1.Close();
return(result);
}
else
{
return(0);
}
}
public (string, string, string, string, string) ConnectDataBase(string Data, int nextresult)
{
string name, path, cate, time, revision;
name = "0"; path = "0"; cate = "0"; time = "0";
try
{
con = new MySqlConnection(conectabanco);
command2 = new MySqlCommand("SELECT count(Data) FROM concurso where DataRevisao='" + Data + "'", con);
command = new MySqlCommand("SELECT * FROM concurso where DataRevisao='" + Data + "'", con);
con.Open();
MySqlDataReader Reader1, Reader2;
Reader2 = command2.ExecuteReader();
Reader2.Read();
string v = Reader2.GetString(0);
int tam = Convert.ToInt32(v);
string[] namestr, pathstr, catestr, timestr, revistr;
namestr = new string[tam];
pathstr = new string[tam];
catestr = new string[tam];
timestr = new string[tam];
revistr = new string[tam];
Reader2.Close();
Reader1 = command.ExecuteReader();
int valid = Convert.ToInt32(v) - nextresult;
if (Convert.ToInt32(valid) > 0)
{
while (Reader1.HasRows)
{
int aux = 0;
while (Reader1.Read())
{
namestr[aux] = Reader1.GetString(0);
pathstr[aux] = Reader1.GetString(1);
catestr[aux] = Reader1.GetString(2);
revistr[aux] = Reader1.GetString(5);
timestr[aux] = Reader1.GetString(6);
aux++;
}
Reader1.NextResult();
}
name = namestr[nextresult];
path = pathstr[nextresult];
cate = catestr[nextresult];
time = timestr[nextresult];
revision = revistr[nextresult];
}
else
{
name = "0"; path = "0"; cate = "0"; time = "0"; revision = "0";
}
}
catch (Exception erro)
{
name = "ERRO"; path = "ERRO"; cate = "ERRO"; time = "ERRO"; revision = "ERRO";
MessageBox.Show(Convert.ToString(erro));
}
return(name, path, cate, time, revision);
}
/// <summary>
/// Invoked when this page is about to be displayed in a Frame.
/// </summary>
/// <param name="e">Event data that describes how this page was reached. The Parameter
/// property is typically used to configure the page.</param>
protected override void OnNavigatedTo(NavigationEventArgs e)
{
//============================================= ISSO AQUI PRA PREENCHER O LIST DE ORÇAMENTOS=========================>>
lsbOrcamento.Items.Clear();
string MyConString = "SERVER=localhost;DATABASE=Gessoft;UID=root;PASSWORD=vegas;";
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select Cliente.cdCliente, Cliente.nmCliente, Cliente.cpf, Cliente.rg, Cliente.email, Cliente.sexo, Cliente.cdStatus, Telefones.nResidencial, Telefones.nCelular, Endereco.cdEndereco, Endereco.rua, Endereco.bairro, Endereco.cidade, Endereco.estado, Endereco.cep, Endereco.numero, Endereco.complemento, agendaServico.numS, agendaServico.cdOrcamento, agendaServico.cdServico, agendaServico.descricao, agendaServico.cdusuario, agendaServico.dia, agendaServico.mes, agendaServico.ano, agendaServico.dtgeracao from Cliente inner join endereco on endereco.cdCliente = cliente.cdCliente inner join telefones on telefones.cdCliente = cliente.cdCliente inner join agendaservico on agendaservico.cdCliente = cliente.cdCliente where cliente.cdCliente = " + Gambis.BadCodigo + " and cdOrcamento <> 'null' and Dia <> 'null' and Mes <> 'null' and Ano <> 'null' and dtgeracao <> 'null';";
connection.Open();
Reader = command.ExecuteReader();
int cont = 0;
string cdOrcamento;
string ClienteNome;
string DiaAgendameto;
string MesAgendameto;
string AnoAgendameto;
string DataGeracao;
while (Reader.Read())
{
cont++;
cdOrcamento = (Reader.GetString("cdOrcamento"));
ClienteNome = (Reader.GetString("nmCliente"));
DiaAgendameto = (Reader.GetString("dia"));
MesAgendameto = (Reader.GetString("mes"));
AnoAgendameto = (Reader.GetString("ano"));
DataGeracao = (Reader.GetString("dtgeracao"));
TextBlock Orcamento = new TextBlock();
Orcamento.Text = " Orçamento Nº: " + cdOrcamento;
Orcamento.FontSize = 20;
Orcamento.FontWeight = FontWeights.Bold;
Orcamento.Foreground = new SolidColorBrush(Colors.Black);
Orcamento.TextAlignment = TextAlignment.Left;
Orcamento.VerticalAlignment = VerticalAlignment.Center;
TextBlock Data = new TextBlock();
Data.Text = " Data de agendamento: " + DiaAgendameto + "/" + MesAgendameto + "/" + AnoAgendameto;
Data.FontSize = 13;
Data.Foreground = new SolidColorBrush(Colors.Black);
Data.TextAlignment = TextAlignment.Left;
Data.VerticalAlignment = VerticalAlignment.Center;
TextBlock DataGera = new TextBlock();
DataGera.Text = " Data de Criação: " + DataGeracao;
DataGera.FontSize = 13;
DataGera.Foreground = new SolidColorBrush(Colors.Black);
DataGera.TextAlignment = TextAlignment.Left;
DataGera.VerticalAlignment = VerticalAlignment.Center;
StackPanel UserBlock = new StackPanel();
UserBlock.Background = new SolidColorBrush(Colors.SkyBlue);
UserBlock.Height = 110;
UserBlock.Width = 650;
UserBlock.VerticalAlignment = VerticalAlignment.Center;
UserBlock.HorizontalAlignment = HorizontalAlignment.Center;
UserBlock.Children.Add(Orcamento);
UserBlock.Children.Add(Data);
UserBlock.Children.Add(DataGera);
lsbOrcamento.Items.Add(UserBlock);
}
connection.Close();
// ===============================================================================================================>>
lsbServico.Items.Clear();
string MyConString2 = "SERVER=localhost;DATABASE=Gessoft;UID=root;PASSWORD=vegas;";
MySqlConnection connection2 = new MySqlConnection(MyConString2);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "select Cliente.cdCliente, Cliente.nmCliente, Cliente.cpf, Cliente.rg, Cliente.email, Cliente.sexo, Cliente.cdStatus, Telefones.nResidencial, Telefones.nCelular, Endereco.cdEndereco, Endereco.rua, Endereco.bairro, Endereco.cidade, Endereco.estado, Endereco.cep, Endereco.numero, Endereco.complemento, agendaServico.numS, agendaServico.cdOrcamento, agendaServico.cdServico, agendaServico.descricao, agendaServico.cdusuario, agendaServico.dia, agendaServico.mes, agendaServico.ano, agendaServico.dtgeracao from Cliente inner join endereco on endereco.cdCliente = cliente.cdCliente inner join telefones on telefones.cdCliente = cliente.cdCliente inner join agendaservico on agendaservico.cdCliente = cliente.cdCliente where cliente.cdCliente = " + Gambis.BadCodigo + " and cdServico <> 'null' and Dia <> 'null' and Mes <> 'null' and Ano <> 'null' and dtgeracao <> 'null';";
connection2.Open();
Reader2 = command2.ExecuteReader();
int contS = 0;
string cdServico;
string ClienteNomeS;
string DiaAgendametoS;
string MesAgendametoS;
string AnoAgendametoS;
string DataGeracaoS;
while (Reader2.Read())
{
cont++;
cdServico = (Reader2.GetString("cdServico"));
ClienteNomeS = (Reader2.GetString("nmCliente"));
DiaAgendametoS = (Reader2.GetString("dia"));
MesAgendametoS = (Reader2.GetString("mes"));
AnoAgendametoS = (Reader2.GetString("ano"));
DataGeracaoS = (Reader2.GetString("dtgeracao"));
TextBlock Servico = new TextBlock();
Servico.Text = " Serviço Nº: " + cdServico;
Servico.FontSize = 20;
Servico.FontWeight = FontWeights.Bold;
Servico.Foreground = new SolidColorBrush(Colors.Black);
Servico.TextAlignment = TextAlignment.Left;
Servico.VerticalAlignment = VerticalAlignment.Center;
TextBlock DataS = new TextBlock();
DataS.Text = " Data de agendamento: " + DiaAgendametoS + "/" + MesAgendametoS + "/" + AnoAgendametoS;
DataS.FontSize = 13;
DataS.Foreground = new SolidColorBrush(Colors.Black);
DataS.TextAlignment = TextAlignment.Left;
DataS.VerticalAlignment = VerticalAlignment.Center;
TextBlock DataGeraS = new TextBlock();
DataGeraS.Text = " Data de Criação: " + DataGeracaoS;
DataGeraS.FontSize = 13;
DataGeraS.Foreground = new SolidColorBrush(Colors.Black);
DataGeraS.TextAlignment = TextAlignment.Left;
DataGeraS.VerticalAlignment = VerticalAlignment.Center;
StackPanel UserBlockS = new StackPanel();
UserBlockS.Background = new SolidColorBrush(Colors.SkyBlue);
UserBlockS.Height = 110;
UserBlockS.Width = 650;
UserBlockS.VerticalAlignment = VerticalAlignment.Center;
UserBlockS.HorizontalAlignment = HorizontalAlignment.Center;
UserBlockS.Children.Add(Servico);
UserBlockS.Children.Add(DataS);
UserBlockS.Children.Add(DataGeraS);
lsbServico.Items.Add(UserBlockS);
}
connection.Close();
//.Children.Add(UserBlock); Width="452" Height="126"
//ListBox1.Items.Add(codigo+"--"+email+"--"+nome);
//new TextBlock() { Name = "teste", Text = "Olaaaaaaaaa poha ", FontSize = 50 };
//new TextBox() { Name = "asd", Width=11, Height=11 };
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT *,file.name As file,client.name As client FROM disbursements LEFT JOIN client ON client.clientID = disbursements.clientID LEFT JOIN file ON file.fileID = disbursements.fileID WHERE disbursements.fileID='" + id + "' ;";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string));
t.Columns.Add("No.", typeof(string));
t.Columns.Add("AMOUNT", typeof(string));
t.Columns.Add("BAL.", typeof(string));
t.Columns.Add("METHOD", typeof(string));
t.Columns.Add("DETAILS", typeof(string));
t.Rows.Add(new object[] { "", " ", "", "FILE SUMMARY", "", "" });
t.Rows.Add(new object[] { " ", " ", "", "", "", "" });
t.Rows.Add(new object[] { "DISBURSEMENTS", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
DisDictionary.Clear();
while (Reader.Read())
{
DisDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(9) ? "0" : Reader.GetString(9)));
t.Rows.Add(new object[] { (Reader.IsDBNull(14) ? "none" : Reader.GetString(14)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), Convert.ToDouble(Reader.IsDBNull(9) ? "0" : Reader.GetString(9)).ToString("n0"), Convert.ToDouble(Reader.IsDBNull(11) ? "none" : Reader.GetString(11)).ToString("n0"), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)), (Reader.IsDBNull(35) ? "none" : Reader.GetString(35)) });
}
totalDis = DisDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " Total", totalDis.ToString("n0"), "", "", "" });
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM expenses LEFT JOIN client ON client.clientID = expenses.clientID LEFT JOIN file ON file.fileID = expenses.fileID WHERE expenses.fileID='" + id + "';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EXPENSES", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
ExpDictionary.Clear();
while (Reader2.Read())
{
ExpDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)));
t.Rows.Add(new object[] { (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)), " ", Convert.ToDouble(Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)).ToString("n0"), (Reader2.IsDBNull(8) ? "none" : Reader2.GetString(8)), (Reader2.IsDBNull(36) ? "none" : Reader2.GetString(36)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)) });
}
totalExp = ExpDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", "Total ", totalExp.ToString("n0"), "", "", "" });
connection2.Close();
MySqlConnection connection3 = new MySqlConnection(DBConnect.conn);
MySqlCommand command3 = connection3.CreateCommand();
MySqlDataReader Reader3;
command3.CommandText = "SELECT * FROM events WHERE file ='" + nameTxt.Text + "';";
connection3.Open();
Reader3 = command3.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EVENTS", "SCHEDULES ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Event", "Start", "End", "Progress", "Cost" });
while (Reader3.Read())
{
EventDictionary.Add((Reader3.IsDBNull(0) ? "none" : Reader3.GetString(0)), (Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)));
t.Rows.Add(new object[] { (Reader3.IsDBNull(10) ? "none" : Reader3.GetString(10)), (Reader3.IsDBNull(1) ? "none" : Reader3.GetString(1)), Convert.ToDateTime(Reader3.IsDBNull(2) ? "none" : Reader3.GetString(2)).ToString("H:mm:s"), Convert.ToDateTime(Reader3.IsDBNull(3) ? "none" : Reader3.GetString(3)).ToString("H:mm:s"), (Reader3.IsDBNull(16) ? "none" : Reader3.GetString(16)) + " " + (Reader3.IsDBNull(8) ? "none" : Reader3.GetString(8)), Convert.ToDouble(Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)).ToString("n0") });
}
totalEvent = EventDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " ", "", "", "Total", totalEvent.ToString("n0") });
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "", " ", "", "", "Total", (totalDis - (totalEvent + totalExp)).ToString("n0") });
connection3.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
}
private void button2_Click(object sender, EventArgs e)
{
loginBtn.Visible = false;
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
if (contactTxt.Text == "" || passwordTxt.Text == "")
{
MessageBox.Show("Insert login credentials");
loginBtn.Visible = true;
return;
}
command.CommandText = "SELECT * FROM users WHERE contact = '" + contactTxt.Text + "' AND password = '******'";
try
{
connection.Open();
}
catch {
MessageBox.Show("Error connecting to server !");
}
Reader = command.ExecuteReader();
while (Reader.Read())
{
if ((Reader.IsDBNull(7) ? "" : Reader.GetString(7)) == "")
{
MessageBox.Show("Access denied");
loginBtn.Visible = true;
}
else
{
Helper.username = (Reader.IsDBNull(2) ? "none" : Reader.GetString(2));
Helper.contact = (Reader.IsDBNull(7) ? "none" : Reader.GetString(7));
Helper.designation = (Reader.IsDBNull(5) ? "none" : Reader.GetString(5));
Helper.email = (Reader.IsDBNull(3) ? "none" : Reader.GetString(3));
Helper.image = (Reader.IsDBNull(8) ? "none" : Reader.GetString(8));
Helper.orgID = (Reader.IsDBNull(1) ? "none" : Reader.GetString(1));
loginBtn.Visible = false;
}
}
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM org WHERE orgID = '" + Helper.orgID + "'";
connection2.Open();
Reader2 = command2.ExecuteReader();
while (Reader2.Read())
{
Helper.logo = (Reader2.IsDBNull(9) ? "none" : Reader2.GetString(9));
Helper.address = (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6));
Helper.orgName = (Reader2.IsDBNull(1) ? "none" : Reader2.GetString(1));
Helper.code = (Reader2.IsDBNull(5) ? "none" : Reader2.GetString(5));
}
connection2.Close();
if (string.IsNullOrEmpty(Helper.contact) || string.IsNullOrEmpty(Helper.orgID))
{
MessageBox.Show("Access denied");
loginBtn.Visible = true;
}
else
{
loginBtn.Visible = false;
MainForm frm = new MainForm();
frm.Show();
this.Hide();
}
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT fees.date As date,fees.invoice AS invoice,client.name As client,file.name As file,fees.amount As amount,fees.balance AS balance,fees.method as method,fees.details AS details,fees.paid AS paid FROM fees LEFT JOIN client ON client.clientID = fees.clientID LEFT JOIN file ON file.fileID = fees.fileID WHERE fees.date LIKE '%" + month + "%';";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string)); //0
t.Columns.Add("No.", typeof(string)); //1
t.Columns.Add("CLIENT", typeof(string)); //2
t.Columns.Add("FILE", typeof(string)); //3
t.Columns.Add("AMOUNT", typeof(string)); //4
t.Columns.Add("BAL.", typeof(string)); //5
t.Columns.Add("METHOD", typeof(string)); //6
t.Columns.Add("DETAILS", typeof(string)); //7
t.Columns.Add("PAID", typeof(string)); //8
t.Columns.Add(" ", typeof(string)); //8
t.Rows.Add(new object[] { " ", " ", "", "", "FINANCIAL", "REPORT ", "" + month, "", "", "" });
t.Rows.Add(new object[] { "FEES", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Client", "File", "Amount", "Balance", "Method", "Details", "Paid", "" });
while (Reader.Read())
{
t.Rows.Add(new object[] { (Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(1) ? "none" : Reader.GetString(1)), (Reader.IsDBNull(2) ? "none" : Reader.GetString(2)), (Reader.IsDBNull(3) ? "none" : Reader.GetString(3)), Convert.ToDouble((Reader.IsDBNull(4) ? "none" : Reader.GetString(4))).ToString("n0"), (Reader.IsDBNull(5) ? "none" : Reader.GetString(5)), (Reader.IsDBNull(6) ? "none" : Reader.GetString(6)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)) });
FeesDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(4) ? "none" : Reader.GetString(4)));
}
try
{
totalFees = FeesDictionary.Sum(m => Convert.ToDouble(m.Value));
// double amount = totalRent - totalExpense;
t.Rows.Add(new object[] { " ", " ", "TOTAL FEES:", "", "" + totalFees.ToString("n0"), "", "", "", "", "" });
}
catch
{
}
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM petty WHERE date LIKE '%" + month + "%';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "FIRM EXPENSES", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Item", "Unit cost", "Qty", "Total Cost", "Method", "Reason", "Paid", "Approved" });
while (Reader2.Read())
{
t.Rows.Add(new object[] { (Reader2.IsDBNull(5) ? "none" : Reader2.GetString(5)), (Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(1) ? "none" : Reader2.GetString(1)), Convert.ToDouble((Reader2.IsDBNull(2) ? "none" : Reader2.GetString(2))).ToString("n0"), (Reader2.IsDBNull(3) ? "none" : Reader2.GetString(3)), Convert.ToDouble((Reader2.IsDBNull(4) ? "none" : Reader2.GetString(4))).ToString("n0"), (Reader2.IsDBNull(10) ? "none" : Reader2.GetString(10)), (Reader2.IsDBNull(9) ? "none" : Reader2.GetString(9)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)), (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)) });
ExpenseDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(4) ? "none" : Reader2.GetString(4)));
}
connection2.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
try
{
totalExpense = ExpenseDictionary.Sum(m => Convert.ToDouble(m.Value));
amount = totalFees - totalExpense;
t.Rows.Add(new object[] { " ", " ", "", "TOTAL EXPENSES:", "", "" + totalExpense.ToString("n0"), "", "", "", "" });
}
catch
{
}
// double amount = totalRent - totalExpense;
t.Rows.Add(new object[] { " ", " ", "", "", "PROFIT DECLARATION: ", "" + amount.ToString("n0"), "", "", "", "" });
}