public static List<Invoice> GetTransakcje(int ID, string numer, int CID, int UID,
string DataOD, string DataDo, string BDataOd, string BDataDo, int status, int CARID)
{
string Query = "SELECT CAR.*, (Cars.Marka + ' ' + Cars.Model + ' ' + Cars.Poj + ' ' + Cars.Rej) AS CARNAME FROM " +
"(SELECT INVU.*, (Urs.IMIE + ' ' + Urs.NAZWISKO) AS [Urs] FROM " +
"(SELECT INV.*, (Customers.FIRMA + ',' + Customers.NAZWISKO + ' ' + Customers.IMIE + ',' + Customers.ADRES) AS [CUSTOMER] FROM " +
"(SELECT * FROM Transakcje WHERE ID IS NOT NULL";
if (ID > 0) Query += " AND ID=" + ID;
if (!String.IsNullOrEmpty(numer)) Query += " AND NUMER LIKE '" + numer + "%'";
if (CID > 0) Query += " AND CID=" + CID;
if (UID > 1) Query += " AND UID=" + UID;
if (!String.IsNullOrEmpty(DataOD)) Query += " AND DATA >= '" + DataOD + "'";
if (!String.IsNullOrEmpty(DataDo)) Query += " AND DATA <= '" + DataDo + "'";
if (!String.IsNullOrEmpty(BDataOd)) Query += " AND BDATA >= '" + BDataOd + "'";
if (!String.IsNullOrEmpty(BDataDo)) Query += " AND BDATA <= '" + BDataDo + "'";
if (status > -1) Query += " AND STATUS=" + status;
if(CARID>0) Query += " AND CARID=" + CARID;
Query += ") INV INNER JOIN Customers ON INV.CID=Customers.ID)";
Query += " INVU INNER JOIN Urs ON INVU.UID=Urs.ID";
Query += ") CAR INNER JOIN Cars ON CAR.CARID=Cars.ID";
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Invoice> invoices = new List<Invoice>();
for (int i = 0; i < table.Rows.Count; i++)
{
Invoice invoice = new Invoice();
invoice.ID = int.Parse(table.Rows[i]["ID"].ToString());
invoice.NUMER = table.Rows[i]["NUMER"].ToString();
invoice.PRICE = float.Parse(table.Rows[i]["PRICE"].ToString());
invoice.VATPRICE = float.Parse(table.Rows[i]["VATPRICE"].ToString());
invoice.CID = int.Parse(table.Rows[i]["CID"].ToString());
invoice.UID = int.Parse(table.Rows[i]["UID"].ToString());
invoice.DATA = table.Rows[i]["DATA"].ToString().Substring(0,10);
invoice.STATUS = (int)table.Rows[i]["STATUS"];
invoice.CNAME = table.Rows[i]["CUSTOMER"].ToString();
invoice.UNAME = table.Rows[i]["Urs"].ToString();
invoice.PLATNOSC = (int)table.Rows[i]["PLATNOSC"];
invoice.UWAGI = table.Rows[i]["UWAGI"].ToString();
//invoice.ILOSC_SZTUK = float.Parse(table.Rows[i]["CNT"].ToString());
invoice.TYP_FAKTURY = (FakturaTyp.TYPE)int.Parse(table.Rows[i]["STATUS"].ToString());
invoice.CARID = int.Parse(table.Rows[i]["CARID"].ToString());
invoice.CAR_NAME = table.Rows[i]["CARNAME"].ToString();
try { invoice.TERMIN = DateTime.Parse(table.Rows[i]["TERMIN"].ToString()).ToShortDateString(); }
catch { invoice.TERMIN = ""; }
try { invoice.TERMIN_UKONCZENIA = DateTime.Parse(table.Rows[i]["TERMINU"].ToString()).ToShortDateString(); }
catch { invoice.TERMIN_UKONCZENIA = ""; }
//try { invoice.DATA_ZWROTU = table.Rows[i]["BDATA"].ToString().Substring(0, 10); }
//catch { invoice.DATA_ZWROTU = ""; }
if (invoice.TYP_FAKTURY == 0) invoice.TYP_FAKTURY_NAZWA = "Faktura";
else invoice.TYP_FAKTURY_NAZWA = "Faktura korygująca";
try { invoice.Licznik = int.Parse(table.Rows[i]["Licznik"].ToString()); }
catch { invoice.Licznik = -1; }
try { invoice.Paliwo = int.Parse(table.Rows[i]["Paliwo"].ToString()); }
catch { invoice.Paliwo = -1; }
try { invoice.Dowod = int.Parse(table.Rows[i]["Dowod"].ToString()); }
catch { invoice.Dowod = -1; }
invoices.Add(invoice);
}
return invoices;
}
public static List<Invoice> GetFaktury(int ID, string numer, int TYP, int CID, int WID, int UID,
string DataOD, string DataDo, int STATUS)
{
string Query = "SELECT INVW.*, (Workers.IMIE + ' ' + Workers.NAZWISKO + ', ' + Workers.ADRESS) AS [WORKER] FROM " +
"(SELECT INVU.*, (Urs.IMIE + ' ' + Urs.NAZWISKO) AS [Urs] FROM " +
"(SELECT INV.*, (Customers.FIRMA + ',' + Customers.NAZWISKO + ' ' + Customers.IMIE + ',' + Customers.ADRES) AS [CUSTOMER] FROM " +
"(SELECT FAK.*, Transakcje.NUMER AS TNUMER, Transakcje.PRICE, Transakcje.VATPRICE, Transakcje.CNT, Transakcje.CID, Transakcje.WID, Transakcje.UID, Transakcje.SUMAPROWIZJI, Transakcje.PLATNOSC, Transakcje.BDATA, Transakcje.STATUS, Transakcje.ACTIVATED, Transakcje.ACTIVDATA, Transakcje.UWAGI FROM " +
"(SELECT NUMER, TYP, TID, DATA FROM Faktury WHERE ID IS NOT NULL";
if (ID > 0) Query += " AND ID=" + ID;
if (!String.IsNullOrEmpty(numer)) Query += " AND NUMER LIKE '" + numer + "%'";
if (UID > 1) Query += " AND UID=" + UID;
if (TYP > 0) Query += " AND TYP=" + TYP;
if (!String.IsNullOrEmpty(DataOD)) Query += " AND DATA >= '" + DataOD + "'";
if (!String.IsNullOrEmpty(DataDo)) Query += " AND DATA <= '" + DataDo + "'";
Query += ") FAK INNER JOIN Transakcje ON FAK.TID=Transakcje.ID WHERE Transakcje.ID IS NOT NULL";
if (CID > 0) Query += " AND CID=" + CID;
if (WID > 0) Query += " AND WID=" + WID;
if (STATUS > 0) Query += " AND STATUS=" + STATUS;
Query += ") INV INNER JOIN Customers ON INV.CID=Customers.ID)";
Query += " INVU INNER JOIN Urs ON INVU.UID=Urs.ID) INVW INNER JOIN Workers ON INVW.WID=Workers.ID";
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Invoice> invoices = new List<Invoice>();
for (int i = 0; i < table.Rows.Count; i++)
{
Invoice invoice = new Invoice();
invoice.ID = int.Parse(table.Rows[i]["TID"].ToString());
invoice.NUMER = table.Rows[i]["NUMER"].ToString();
invoice.PRICE = float.Parse(table.Rows[i]["PRICE"].ToString());
invoice.VATPRICE = float.Parse(table.Rows[i]["VATPRICE"].ToString());
invoice.CID = int.Parse(table.Rows[i]["CID"].ToString());
invoice.WID = (int)table.Rows[i]["WID"];
invoice.UID = int.Parse(table.Rows[i]["UID"].ToString());
invoice.DATA = table.Rows[i]["DATA"].ToString();
invoice.STATUS = (int)table.Rows[i]["STATUS"];
invoice.ACTIVATED = table.Rows[i]["ACTIVATED"].ToString();
invoice.ACTIVDATA = table.Rows[i]["ACTIVDATA"].ToString();
invoice.CNAME = table.Rows[i]["CUSTOMER"].ToString();
invoice.UNAME = table.Rows[i]["Urs"].ToString();
invoice.WNAME = table.Rows[i]["WORKER"].ToString();
invoice.SUMA_PROWIZJI = float.Parse(table.Rows[i]["SUMAPROWIZJI"].ToString());
invoice.PLATNOSC = (int)table.Rows[i]["PLATNOSC"];
invoice.UWAGI = table.Rows[i]["UWAGI"].ToString();
invoice.TYP_FAKTURY = (FakturaTyp.TYPE)int.Parse(table.Rows[i]["TYP"].ToString());
invoices.Add(invoice);
}
return invoices;
}
