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; }