public static void LoadInvoicesList()
{
MainProgram.Connection = DatabaseMySQL.Connect(MainProgram.DatabaseName);
try
{
DatabaseMySQL.OpenConnection(MainProgram.Connection);
string queryText = string.Format("SELECT f.id,numer,id_pracownika, k.nazwa,concat(p.imie,' ',p.nazwisko),ifnull(nip,'-'),ROUND(sum(pf.cena*ilosc),2), ROUND(sum(pf.cena*ilosc)-zaplacona_kwota,2),data_wystawienia,termin_platnosci,data_sprzedazy,forma_platnosci,id_kontrahenta,zaplacona_kwota FROM faktura f, kontrahent k, pracownik p, pozycja_faktury pf WHERE (id_kontrahenta = k.id and id_pracownika = p.id and numer=pf.nr_faktury) GROUP BY numer HAVING (numer LIKE '%%')");
MySqlCommand query = new MySqlCommand(queryText, MainProgram.Connection);
MySqlDataReader dataReader = query.ExecuteReader();
while (dataReader.Read())
{
string[] cols = new string[dataReader.FieldCount];
for (int i = 0; i < dataReader.FieldCount; i++)
{
cols[i] = dataReader[i].ToString();
}
MainProgram.InvoiceObjectsList.Add(new Invoice(cols));
}
dataReader.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
DatabaseMySQL.CloseConnection(MainProgram.Connection);
}
}
public static void CreateNewInvoice(int ConsumerID, string dateInvoice, string dateSell, string datePayment, string paymentForm, string alreadyPaid, DataGridView articlesList)
{
MainProgram.Connection = DatabaseMySQL.Connect(MainProgram.DatabaseName);
try
{
DatabaseMySQL.OpenConnection(MainProgram.Connection);
CreateNumberForNewInvoice(); // tworzy numer faktury
string queryText = string.Format("INSERT INTO faktura SET ");
queryText += string.Format("numer='{0}', id_kontrahenta={1}, id_pracownika={2}, ", MainProgram.EditedInvoiceNumber, ConsumerID, MainProgram.LoggeUserID);
queryText += string.Format("data_wystawienia='{0}', termin_platnosci='{1}', forma_platnosci='{2}', zaplacona_kwota={3}", dateInvoice, datePayment, paymentForm, alreadyPaid);
MySqlCommand query = new MySqlCommand(queryText, MainProgram.Connection);
query.ExecuteNonQuery();
for (int i = 0; i < articlesList.RowCount; i++)
{
DataGridViewRow row = articlesList.Rows[i];
queryText = string.Format("INSERT INTO pozycja_faktury SET ");
queryText += string.Format("kod_artykulu='{0}', nr_faktury='{1}', ilosc={2}, rabat={3}, cena={4}", row.Cells[0].Value, MainProgram.EditedInvoiceNumber, row.Cells[6].Value, row.Cells[5].Value, row.Cells[2].Value);
query = new MySqlCommand(queryText, MainProgram.Connection);
query.ExecuteNonQuery();
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
DatabaseMySQL.CloseConnection(MainProgram.Connection);
}
}
public static string[] GetCustomerData(int id)
{
// Pobiera dane na temat danego kontrahenta
// Stworzone jedynie testowo.
string[] data = new string[3];
MainProgram.Connection = DatabaseMySQL.Connect(MainProgram.DatabaseName);
try
{
DatabaseMySQL.OpenConnection(MainProgram.Connection);
string queryText = string.Format("SELECT nazwa, concat(ulica, '\n', left(kod_pocztowy,2), '-', right(kod_pocztowy,3), ' ', miasto), nip from kontrahent WHERE id={0}", id);
MySqlCommand query = new MySqlCommand(queryText, MainProgram.Connection);
MySqlDataReader dataReader = query.ExecuteReader();
dataReader.Read();
for (int i = 0; i < dataReader.FieldCount; i++)
{
data[i] = dataReader[i].ToString();
}
dataReader.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return(data);
}
private void WriteAllInvoices()
{
dataGridView1.Rows.Clear(); // wyczyść poprzednie dane nim załadujesz
DatabaseMySQL.LoadInvoicesList();
if (MainProgram.InvoiceObjectsList.Count > 0) // jeśli tablica nie-pusta
{
int i = 0;
foreach (Invoice invoice in MainProgram.InvoiceObjectsList)
{
dataGridView1.Rows.Add();
DataGridViewRow row = (DataGridViewRow)dataGridView1.Rows[i];
row.Cells["index"].Value = i;
row.Cells["NrFaktury"].Value = invoice.Number;
row.Cells["sprzedawca"].Value = invoice.EmployeeName;
row.Cells["KlientNIP"].Value = invoice.CustomerNIP;
row.Cells["KlientName"].Value = invoice.CusotmerName;
row.Cells["WartoscFaktury"].Value = invoice.InvoiceValue;
row.Cells["DoZaplaty"].Value = invoice.AmountPaid;
row.Cells["DataWystawienia"].Value = invoice.InvoiceDate;
row.Cells["TerminPlatnosci"].Value = invoice.PaymentDate;
i++;
}
}
}
private void GetAndLoadCustomerDetails()
{
if (editInvoice.CustomerID > 0)
{
labelCustomerName.Text = editInvoice.CustomerID.ToString();
string[] consumerData = DatabaseMySQL.GetCustomerData(editInvoice.CustomerID);
labelCustomerName.Text = consumerData[0];
labelCustomerAdress.Text = consumerData[1];
labelCustomerNIP.Text = consumerData[2];
}
}
private void buttonAddArticleToInvoice_Click(object sender, EventArgs e)
{
/// ======== TO DO -> potrzebny obiekt artykułu! ===============
/// ============================================================
/// ============================================================
FormArticles addingArticleToInvoice_Window = new FormArticles();
string[] addedArticleData = addingArticleToInvoice_Window.ShowWindowToAddNewArticle();
if (addedArticleData == null) // no data receive
{
return;
}
string productCode = addedArticleData[0];
string productAmount = addedArticleData[1];
string productDiscount = addedArticleData[2];
if (!productCodesOnInvoice.Contains(productCode))
{
MainProgram.Connection = DatabaseMySQL.Connect(MainProgram.DatabaseName);
try
{
DatabaseMySQL.OpenConnection(MainProgram.Connection);
string[] productData = DatabaseMySQL.GetArticleData(productCode);
string productName = productData[0];
string productPriceNetto = productData[1];
string productVAT = productData[2];
dataGridView1.Rows.Add(productCode, productName, productPriceNetto, productVAT, "*", productDiscount, productAmount, "szt.", "=", "=");
productCodesOnInvoice.Add(productCode);
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
finally
{
DatabaseMySQL.CloseConnection(MainProgram.Connection);
}
}
else
{
MessageBox.Show("Ten produkt występuje już na fakturze i nie można go dodać ponownie.\nWybierz element na liście, a następnie dokonaj stosownych poprawek.");
}
}
public static string CreateNumberForNewInvoice()
{
// Generowanie kolejnego numeru dla nowej faktury.
string lastInvoiceNumber = "-1";
string[] lastInvoiceNumberParts = { "" };
MainProgram.Connection = DatabaseMySQL.Connect(MainProgram.DatabaseName);
try
{
DatabaseMySQL.OpenConnection(MainProgram.Connection);
string queryText = string.Format("SELECT numer from faktura WHERE id=(SELECT MAX(id) FROM faktura)");
MySqlCommand query = new MySqlCommand(queryText, MainProgram.Connection);
MySqlDataReader dataReader = query.ExecuteReader();
dataReader.Read();
lastInvoiceNumber = dataReader[0].ToString();
lastInvoiceNumberParts = lastInvoiceNumber.Split('/');
if (lastInvoiceNumberParts[1] == DateTime.Now.Month.ToString("00") && lastInvoiceNumberParts[2] == DateTime.Now.Year.ToString())
{
lastInvoiceNumberParts[0] = (Convert.ToInt16(lastInvoiceNumberParts[0]) + 1).ToString();
}
else
{
lastInvoiceNumberParts[0] = "1";
}
dataReader.Close();
}
catch (Exception exc)
{
MessageBox.Show("Unable to create new number for invoice: " + exc.Message);
}
finally
{
DatabaseMySQL.CloseConnection(MainProgram.Connection);
}
return(String.Format("{0}/{1}/{2}", lastInvoiceNumberParts[0], DateTime.Now.Month.ToString("00"), DateTime.Now.Year.ToString()));
}
private void buttonSaveInvoice_Click(object sender, EventArgs e)
{
// Zapis lub uaktualnienie akturalnie otwartej faktury
if (ValidateForm() == false)
{
return;
}
string query;
if (editInvoice.Number == null)
{
// zapis nowej faktury
editInvoice.Number = DatabaseMySQL.CreateNumberForNewInvoice();
textBoxInvoiceNumber.Text = editInvoice.Number; // wypisz wygenerowany dla faktury numer
query = editInvoice.GenerateInsertQuery();
}
else
{
query = editInvoice.GenerateUpdateQuery(); // przygotowuje zapytanie UPDATE do bazy danych
}
int?returnValue = null;
returnValue = DatabaseMySQL.ExecuteQuery(query);
if (returnValue > 0)
{
MessageBox.Show("Operacja wykonana pomyślnie (" + returnValue + ")");
}
else
{
MessageBox.Show("Wystąpił błąd.");
}
}
//public static string[] GetInvoiceData(string invoiceNumber)
//{
// // NIE WIEM CZY POTRZEBNE [?]
// string[] data = null;
// try
// {
// string queryText = string.Format("SELECT * from faktura WHERE numer='{0}'", invoiceNumber);
// MySqlCommand query = new MySqlCommand(queryText, MainProgram.Connection);
// MySqlDataReader dataReader = query.ExecuteReader();
// dataReader.Read();
// data = new string[dataReader.FieldCount];
// for (int i = 0; i < dataReader.FieldCount; i++)
// {
// data[i] = dataReader[i].ToString();
// }
// dataReader.Close();
// }
// catch (Exception e)
// {
// MessageBox.Show(e.Message);
// }
// return data;
//}
public static int ExecuteQuery(string query)
{
// POZWALA ZREALIZOWAĆ DOWOLNE ZAPYTANIE WYSŁANE JAKO TEKST.
// Uwaga: zwracana wartość to tylko ilość zmodyfikowanych krotek
int n = -1;
MainProgram.Connection = DatabaseMySQL.Connect(MainProgram.DatabaseName);
try
{
DatabaseMySQL.OpenConnection(MainProgram.Connection);
MySqlCommand command = new MySqlCommand(query, MainProgram.Connection);
n = command.ExecuteNonQuery();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
finally
{
DatabaseMySQL.CloseConnection(MainProgram.Connection);
}
return(n); // zwraca liczbę zmodyfikowanych rekordów.
}