public static void ExtractToMDB(SqlResault resault, string path)
{
System.IO.File.WriteAllBytes(path, TImeShareApp.Properties.Resources.dbtemplate);
Database database = new Database(path);
string tablequery = "CREATE TABLE ThisTable (";
string dataQ = "(";
foreach (KeyValuePair <string, string> kv in resault[0])
{
tablequery += "[" + kv.Key + "] Text, ";
dataQ += kv.Key + ", ";
}
tablequery = tablequery.Remove(tablequery.Length - 2, 2);
tablequery += ");";
dataQ = dataQ.Remove(dataQ.Length - 2, 2);
dataQ += ")";
database.Query(tablequery);
string fillq = "";
for (int i = 0; i < resault.Count; i++)
{
string itemq = "INSERT INTO ThisTable " + dataQ + " VALUES (";
foreach (KeyValuePair <string, string> kv in resault[i])
{
itemq += "'" + kv.Value + "', ";
}
itemq = itemq.Remove(itemq.Length - 2, 2);
itemq += ");";
fillq += itemq;
}
database.Query(fillq);
}
private void chargeAllToolStripMenuItem_Click(object sender, EventArgs e)
{
InputForms.InputForm iform = new InputForms.InputForm(new string[2] {
"Year:", "Value:"
}, "Charge", "Charge clients", 40, 150);
string[] iformres = iform.Open(this);
if (iformres != null)
{
if (MessageBox.Show(this, "Charge all clients " + iformres[1] + "€ for " + iformres[0] + "?", "", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Utility.database.OpenConnection();
SqlResault res = Utility.database.Query("select id from Clients");
for (int i = 0; i < res.Count; i++)
{
string cid = res[i]["id"];
SqlResault appartments = Utility.database.Query("select * from Appartments where client_id = " + cid + ";");
for (int j = 0; j < appartments.Count; j++)
{
Utility.database.Query(@"insert into Owes (client_id, appartment_id, aYear,mvalue,notes)
values (" + cid + ", '" + appartments[j]["id"] + "' , '" + iformres[0] + "', " + iformres[1] + ", '');");
}
}
Utility.database.CloseConnection();
MessageBox.Show("All clients were charged for their appartments for the year " + iformres[0] + ".");
}
}
}
public static void ExtractToCSV(SqlResault resault, string path)
{
string txt = "";
foreach (KeyValuePair <string, string> kv in resault[0])
{
txt += kv.Key + ";";
}
txt += "\n";
for (int i = 0; i < resault.Count; i++)
{
foreach (KeyValuePair <string, string> kv in resault[i])
{
txt += "\"" + kv.Value + "\";";
}
txt += "\n";
}
byte[] csvBytes = Encoding.UTF8.GetBytes(txt);
byte[] Utf8Csv = new byte[csvBytes.Length + 3];
csvBytes.CopyTo(Utf8Csv, 3);
Utf8Csv[0] = 0xEF;
Utf8Csv[1] = 0xBB;
Utf8Csv[2] = 0xBF;
System.IO.File.WriteAllBytes(path, Utf8Csv);
}
public SqlResault Query(string qr)
{
if (!OnlineMode)
{
string[] qrcommands = qr.Split(';');
SqlResault res = new SqlResault();
foreach (string command in qrcommands)
{
if (command.Trim() != "")
{
using (OdbcCommand DbCommand = Con.CreateCommand())
{
DbCommand.CommandText = command + ";";
using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
{
int cn = 0;
while (DbReader.Read())
{
int fCount = DbReader.FieldCount;
Dictionary <string, string> dict = new Dictionary <string, string>();
for (int i = 0; i < fCount; i++)
{
dict[DbReader.GetName(i)] = DbReader.GetString(i);
}
res[cn] = dict;
cn += 1;
}
DbReader.Close();
DbCommand.Cancel();
DbCommand.Dispose();
}
}
}
}
return(res);
}
else
{
return(Utility.Connection.Send <SqlResault>("query", qr));
}
}
private void LoadFields()
{
list_names.Items.Clear();
list_addresses.Items.Clear();
list_phones.Items.Clear();
list_email.Items.Clear();
list_appartments.Clear();
list_contracts.Items.Clear();
txt_number.Text = "";
txt_rci_number.Text = "";
txt_bank_number.Text = "";
txt_notes.Text = "";
Utility.database.OpenConnection();
SqlResault res = Utility.database.Query("select * from Clients where id = " + id + ";");
number = res[0]["UserNumber"];
bank_num = res[0]["BankNumber"];
rci_num = res[0]["RCINumber"];
company = res[0]["cCompany"];
notes = res[0]["cNotes"];
res = Utility.database.Query("select * from Names where client_id = " + id + ";");
names = new List <string>();
for (int i = 0; i < res.Count; i++)
{
names.Add(res[i]["name"]);
}
res = Utility.database.Query("select * from Addresses where client_id = " + id + ";");
addresses = new List <string>();
for (int i = 0; i < res.Count; i++)
{
addresses.Add(res[i]["uAddress"]);
}
res = Utility.database.Query("select * from Phones where client_id = " + id + ";");
phones = new List <string>();
for (int i = 0; i < res.Count; i++)
{
phones.Add(res[i]["pNumber"]);
}
res = Utility.database.Query("select * from Emails where client_id = " + id + ";");
emails = new List <string>();
for (int i = 0; i < res.Count; i++)
{
emails.Add(res[i]["email"]);
}
res = Utility.database.Query("select * from Contracts where client_id = " + id + ";");
contracts = new List <string>();
for (int i = 0; i < res.Count; i++)
{
contracts.Add(res[i]["ContractName"]);
}
res = Utility.database.Query("select * from Appartments where client_id = " + id + ";");
appartments = new List <appartment_week>();
for (int i = 0; i < res.Count; i++)
{
appartments.Add(new appartment_week {
appartment = res[i]["AppartmentNumber"], week = res[i]["aWeek"]
});
}
Utility.database.CloseConnection();
list_appartments.View = View.Details;
list_appartments.Columns.Add(Utility.CurrentLanguage["appartment_number"]);
list_appartments.Columns.Add(Utility.CurrentLanguage["week"]);
list_appartments.Columns[0].Width = 50;
txt_number.Text = number;
txt_bank_number.Text = bank_num;
txt_rci_number.Text = rci_num;
txt_notes.Text = notes;
list_names.Items.AddRange(names.ToArray());
list_addresses.Items.AddRange(addresses.ToArray());
list_phones.Items.AddRange(phones.ToArray());
list_email.Items.AddRange(emails.ToArray());
list_contracts.Items.AddRange(contracts.ToArray());
foreach (appartment_week aw in appartments)
{
ListViewItem it = new ListViewItem();
it.Text = aw.appartment;
it.SubItems.Add(aw.week);
list_appartments.Items.Add(it);
}
}
private void LoadFields()
{
payed_list.Clear();
owes_list.Clear();
payed_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["date"], Width = 70
});
payed_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["appartment"], Width = 30
});
payed_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["week"], Width = 30
});
payed_list.Columns.Add(Utility.CurrentLanguage["m_value"]);
payed_list.Columns.Add(Utility.CurrentLanguage["pos"]);
payed_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["notes"], Width = 300
});
owes_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["year"], Width = 50
});
owes_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["appartment"], Width = 30
});
owes_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["week"], Width = 30
});
owes_list.Columns.Add(Utility.CurrentLanguage["m_value"]);
owes_list.Columns.Add(new ColumnHeader()
{
Text = Utility.CurrentLanguage["notes"], Width = 300
});
Utility.database.OpenConnection();
SqlResault res = Utility.database.Query("select *, Payed.id as payed_id from Payed, Appartments where Payed.client_id = " + client_id + " and Appartments.id = Payed.appartment_id;");
for (int i = 0; i < res.Count; i++)
{
ListViewItem it = new ListViewItem();
it.Text = DateTime.ParseExact(res[i]["aDate"], "yyyy-MM-dd H:mm:ss", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy").Replace("-", "/");
it.SubItems.Add(res[i]["AppartmentNumber"]);
it.SubItems.Add(res[i]["aWeek"]);
it.SubItems.Add(res[i]["mvalue"] + "€");
it.SubItems.Add(res[i]["pof"]);
it.SubItems.Add(res[i]["notes"]);
it.SubItems.Add(res[i]["appartment_id"]);
it.SubItems.Add(res[i]["payed_id"]);
payed_list.Items.Add(it);
}
res = Utility.database.Query("select *, Owes.id as owe_id from Owes, Appartments where Owes.client_id = " + client_id + " and Appartments.id = Owes.appartment_id;");
for (int i = 0; i < res.Count; i++)
{
SqlResault monies = Utility.database.Query("select mvalue from Payed where client_id = " + client_id + " and year(aDate) = '" + DateTime.Now.Year + "' and appartment_id = " + res[i]["appartment_id"] + ";");
ListViewItem it = new ListViewItem();
it.Text = res[i]["aYear"];
it.SubItems.Add(res[i]["AppartmentNumber"]);
it.SubItems.Add(res[i]["aWeek"]);
it.SubItems.Add(res[i]["mvalue"] + "€");
it.SubItems.Add(res[i]["notes"]);
it.SubItems.Add(res[i]["appartment_id"]);
it.SubItems.Add(res[i]["owe_id"]);
owes_list.Items.Add(it);
}
Utility.database.CloseConnection();
}
private void Search()
{
try
{
listViewEx1.Clear();
if (maskedTextBox1.Text.Trim() == "")
{
return;
}
SqlResault res = null;
string static_query_part = "select distinct Clients.UserNumber as " + Utility.CurrentLanguage["number"] + ", Names.name as " + Utility.CurrentLanguage["name"] + ", Phones.pNumber as " + Utility.CurrentLanguage["phone"] + ", Addresses.uAddress as " + Utility.CurrentLanguage["address"] + " , Appartments.AppartmentNumber as " + Utility.CurrentLanguage["appartment"] + ", Appartments.aWeek as " + Utility.CurrentLanguage["week"] + " from Names, Clients, Phones, Appartments, Addresses, Emails ";
if (combo_search.SelectedIndex == 0)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Names.name LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 1)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Clients.UserNumber LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 2)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Addresses.uAddress LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 3)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Clients.id = Phones.client_id and Phones.pNumber LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 4)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Clients.id = Emails.client_id and Emails.email LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 5)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Clients.RCINumber LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 6)
{
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Clients.BankNumber LIKE '" + maskedTextBox1.Text + "%';");
}
else if (combo_search.SelectedIndex == 7)
{
string appartment = maskedTextBox1.Text.Substring(0, 3);
string week = maskedTextBox1.Text.Substring(4, 2);
res = Utility.database.Query(static_query_part + @"where Clients.id = Appartments.client_id and Clients.id = Addresses.client_id and Clients.id = Phones.client_id and Clients.id = Names.client_id and
Clients.id = Appartments.client_id and Appartments.AppartmentNumber = '" + appartment + "' and Appartments.aWeek = '" + week + "';");
}
else
{
return;
}
List <string> codes = new List <string>();
foreach (KeyValuePair <string, string> kv in res[0])
{
listViewEx1.Columns.Add(kv.Key);
}
for (int i = 0; i < res.Count; i++)
{
ListViewItem it = new ListViewItem();
bool floop = true;
foreach (KeyValuePair <string, string> kv in res[i])
{
if (floop)
{
it.Text = kv.Value;
floop = false;
}
else
{
it.SubItems.Add(kv.Value);
}
}
if (!codes.Contains(it.SubItems[0].Text))
{
listViewEx1.Items.Add(it);
codes.Add(it.SubItems[0].Text);
}
}
}
catch {
// MessageBox.Show(ex.Message);
}
}
