public static Produkt CheckTowarInMagazyn(int PID, int MID)
{
DataTable table = new DataTable();
table = Connect.FillCommand("SELECT * FROM Magazyn WHERE PID=" + PID + " AND MID=" + MID);
if (table.Rows.Count == 0) return null;
Produkt towar = new Produkt();
towar.ID = (int)table.Rows[0]["ID"];
towar.COUNT = float.Parse(table.Rows[0]["CNT"].ToString());
return towar;
}
private int GetTowarIndex(Produkt towar)
{
for (int i = 0; i < PRODUKTY.Count; i++)
if (PRODUKTY[i].ID == towar.ID) return i;
return -1;
}
public static List<Produkt> GetTransItems(int IID)
{
string Query = "SELECT TCID.*, Customers.FIRMA, Customers.IMIE, Customers.NAZWISKO, Customers.ADRES AS CADRES FROM " +
"(SELECT TCAT.*, Categories.CATEGORY FROM " +
"(SELECT TM.*, Programs.CATID, Programs.CID, Programs.NUMERKAT, Programs.MODEL, Programs.NUMERSER, Programs.OPIS, Programs.NAZWA, Programs.PRICE AS PPRICE, Programs.VATPRICE AS PVATPRICE, Programs.PRICE2, Programs.VATPRICE2, Programs.JEDNOSTKA, Programs.PROWIZJA FROM " +
"(SELECT TI.*, MagType.MAGAZYN, MagType.ADRES FROM " +
"(SELECT * FROM TransItems WHERE IID=" + IID + " AND MAGID>0";
Query += ") TI INNER JOIN MagType ON TI.MAGID=MagType.ID";
Query += ") TM INNER JOIN Programs ON TM.PID=Programs.ID";
Query += ") TCAT INNER JOIN Categories ON TCAT.CATID=Categories.ID";
Query += ") TCID INNER JOIN Customers ON TCID.CID=Customers.ID";
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Produkt> towary = new List<Produkt>();
for (int i = 0; i < table.Rows.Count; i++)
{
Produkt towar = new Produkt();
towar.TIID = (int)table.Rows[i]["ID"];
towar.IID = (int)table.Rows[i]["IID"];
towar.MID = (int)table.Rows[i]["MAGID"];
towar.MAGAZYN = table.Rows[i]["MAGAZYN"].ToString();
towar.MAGAZYNADRES = table.Rows[i]["ADRES"].ToString();
towar.PID = (int)table.Rows[i]["PID"];
towar.ID = (int)table.Rows[i]["PID"];
towar.CATID = (int)table.Rows[i]["CATID"];
towar.CATNAME = table.Rows[i]["CATEGORY"].ToString();
towar.CID = (int)table.Rows[i]["CID"];
towar.CNAME = table.Rows[i]["FIRMA"].ToString() + ", " +
table.Rows[i]["IMIE"].ToString() + " " +
table.Rows[i]["NAZWISKO"].ToString() + ", " +
table.Rows[i]["CADRES"].ToString();
towar.NUMERKAT = table.Rows[i]["NUMERKAT"].ToString();
towar.MODEL = table.Rows[i]["MODEL"].ToString();
towar.NUMERSER = table.Rows[i]["NUMERSER"].ToString();
towar.OPIS = table.Rows[i]["OPIS"].ToString();
towar.NAME = table.Rows[i]["NAZWA"].ToString();
towar.PRICE = float.Parse(table.Rows[i]["PPRICE"].ToString());
towar.VATPRICE = float.Parse(table.Rows[i]["PVATPRICE"].ToString());
try { towar.PRICE2 = float.Parse(table.Rows[i]["PRICE2"].ToString()); }
catch { towar.PRICE2 = 0; }
try { towar.VATPRICE2 = float.Parse(table.Rows[i]["VATPRICE2"].ToString()); }
catch { towar.VATPRICE2 = 0; }
towar.STAN = float.Parse(table.Rows[i]["CNT"].ToString());
towar.SUMA_NETTO = float.Parse(table.Rows[i]["PRICE"].ToString());
towar.SUMA_BRUTTO = float.Parse(table.Rows[i]["VATPRICE"].ToString());
towar.SUMA = float.Parse(table.Rows[i]["VATPRICE"].ToString());
towar.JEDNOSTKA = table.Rows[i]["JEDNOSTKA"].ToString();
// towar.KWOTA_VAT = float.Parse(MathHelp.AddZero(Math.Round((towar.SUMA_NETTO * Settings.PROCENT) / 100, 2).ToString()));
towar.KWOTA_VAT = (float)Math.Round(towar.SUMA_BRUTTO - towar.SUMA_NETTO,2);
towary.Add(towar);
}
return towary;
}
public static List<Produkt> GetTowarFromMagazyn(int CID, int CATID, int PID, int MID, string nazwa_towaru,
string numer_kat, string model, string numer_seryjny)
{
string Query = "SELECT TPCAT.*, Categories.CATEGORY FROM" +
" (SELECT TPC.*, Customers.FIRMA, Customers.IMIE, Customers.NAZWISKO, Customers.ADRES AS CADRESS FROM" +
" (SELECT TPP.*, MagType.MAGAZYN, MagType.ADRES FROM" +
" (SELECT TP.*, Programs.CATID, Programs.CID, Programs.NUMERKAT, Programs.MODEL, Programs.NUMERSER, Programs.OPIS, Programs.NAZWA, Programs.PRICE, Programs.VATPRICE, Programs.PRICE2, Programs.VATPRICE2, Programs.JEDNOSTKA, Programs.PROWIZJA FROM" +
" (SELECT ID, PID, MID, CNT FROM Magazyn WHERE ID IS NOT NULL";
if (PID > 0) Query += " AND PID=" + PID;
if (MID > 0) Query += " AND MID=" + MID;
Query += ") TP INNER JOIN Programs ON TP.PID=Programs.ID WHERE Programs.ID IS NOT NULL";
if (PID > 0) Query += " AND Programs.ID=" + PID;
if (CID > 0) Query += " AND Programs.CID=" + CID;
if (!String.IsNullOrEmpty(nazwa_towaru)) Query += " AND NAZWA LIKE '" + nazwa_towaru + "%'";
if (!String.IsNullOrEmpty(numer_kat)) Query += " AND NUMERKAT LIKE '" + numer_kat + "%'";
if (!String.IsNullOrEmpty(model)) Query += " AND MODEL LIKE '" + model + "%'";
if (!String.IsNullOrEmpty(numer_seryjny)) Query += " AND NUMERSER LIKE '" + numer_seryjny + "%'";
Query += ") TPP INNER JOIN MagType ON TPP.MID=MagType.ID";
Query += ") TPC INNER JOIN Customers ON TPC.CID=Customers.ID";
Query += ") TPCAT INNER JOIN Categories ON TPCAT.CATID=Categories.ID";
if (CATID > 0) Query += " WHERE Categories.ID=" + CATID;
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Produkt> towary = new List<Produkt>();
for (int i = 0; i < table.Rows.Count; i++)
{
Produkt towar = new Produkt();
towar.ID = (int)table.Rows[i]["ID"];
towar.PID = (int)table.Rows[i]["PID"];
towar.MID = (int)table.Rows[i]["MID"];
towar.COUNT = float.Parse(table.Rows[i]["CNT"].ToString());
towar.CATID = (int)table.Rows[i]["CATID"];
towar.CID = (int)table.Rows[i]["CID"];
towar.NUMERKAT = table.Rows[i]["NUMERKAT"].ToString();
towar.MODEL = table.Rows[i]["MODEL"].ToString();
towar.NUMERSER = table.Rows[i]["NUMERSER"].ToString();
towar.OPIS = table.Rows[i]["OPIS"].ToString();
towar.NAME = table.Rows[i]["NAZWA"].ToString();
towar.PRICE = float.Parse(table.Rows[i]["PRICE"].ToString());
try { towar.PRICE2 = float.Parse(table.Rows[i]["PRICE2"].ToString()); }
catch { towar.PRICE2 = 0; }
towar.VATPRICE = float.Parse(table.Rows[i]["VATPRICE"].ToString());
try { towar.VATPRICE2 = float.Parse(table.Rows[i]["VATPRICE2"].ToString()); }
catch { towar.VATPRICE2 = 0; }
towar.JEDNOSTKA = table.Rows[i]["JEDNOSTKA"].ToString();
towar.MAGAZYN = table.Rows[i]["MAGAZYN"].ToString();
towar.MAGAZYNADRES = table.Rows[i]["ADRES"].ToString();
towar.CNAME = table.Rows[i]["FIRMA"].ToString() + ", "
+ table.Rows[i]["IMIE"].ToString() + " " + table.Rows[i]["NAZWISKO"].ToString() + ", " +
table.Rows[i]["CADRESS"].ToString();
towar.CATNAME = table.Rows[i]["CATEGORY"].ToString();
towar.PROWIZJA = int.Parse(table.Rows[i]["PROWIZJA"].ToString());
towary.Add(towar);
}
return towary;
}
public static List<Produkt> GetPzWzItems(int PZID)
{
string Query = "SELECT ITC.*, Customers.FIRMA, Customers.IMIE, Customers.NAZWISKO, Customers.ADRES FROM" +
" (SELECT IT.*, Programs.CATID, Programs.CID, Programs.NUMERKAT, Programs.MODEL, Programs.NUMERSER, Programs.OPIS, Programs.NAZWA, Programs.PRICE, Programs.VATPRICE AS PVATPRICE, Programs.PRICE2, Programs.VATPRICE2 , Programs.JEDNOSTKA, Programs.PROWIZJA FROM" +
" (SELECT * FROM ItemsPzWz WHERE PZID=" + PZID;
Query += ") IT INNER JOIN Programs ON IT.PID=Programs.ID";
Query += ") ITC INNER JOIN Customers ON ITC.CID=Customers.ID";
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Produkt> pzwzs = new List<Produkt>();
for (int i = 0; i < table.Rows.Count; i++)
{
Produkt pzwz = new Produkt();
pzwz.ID = (int)table.Rows[i]["ID"];
pzwz.PID = (int)table.Rows[i]["PID"];
pzwz.STAN = float.Parse(table.Rows[i]["CNT"].ToString());
pzwz.SUMA = float.Parse(table.Rows[i]["VATPRICE"].ToString());
//pzwz.PID = (int)table.Rows[i]["Programs.ID"];
pzwz.CATID = (int)table.Rows[i]["CATID"];
pzwz.CID = (int)table.Rows[i]["CID"];
pzwz.NAME = table.Rows[i]["NAZWA"].ToString();
pzwz.PRICE = float.Parse(table.Rows[i]["PRICE"].ToString());
pzwz.VATPRICE = float.Parse(table.Rows[i]["PVATPRICE"].ToString());
try { pzwz.PRICE2 = float.Parse(table.Rows[i]["PRICE2"].ToString()); }
catch { pzwz.PRICE2 = 0; }
try { pzwz.VATPRICE2 = float.Parse(table.Rows[i]["VATPRICE2"].ToString()); }
catch { pzwz.VATPRICE2 = 0; }
pzwz.JEDNOSTKA = table.Rows[i]["JEDNOSTKA"].ToString();
pzwz.PROWIZJA = int.Parse(table.Rows[i]["PROWIZJA"].ToString());
pzwz.CNAME = table.Rows[i]["FIRMA"].ToString() + ", " + table.Rows[i]["IMIE"].ToString() + " " +
table.Rows[i]["NAZWISKO"].ToString() + ", " + table.Rows[i]["ADRES"].ToString();
//pzwz.SUMA = float.Parse(table.Rows[i]["VATPRICE"].ToString());
pzwzs.Add(pzwz);
}
return pzwzs;
}
public static List<Produkt> GetPzWz(int UID, int CID, int MIDI, int MIDII, int TYPE, string DataOd,
string DataDo)
{
string Query = "SELECT PWZM.*, MagType.MAGAZYN AS MAGAZYN FROM " +
"(SELECT PWZC.*, (Customers.FIRMA + ', ' + Customers.IMIE + ' ' + Customers.NAZWISKO + ', ' + Customers.ADRES) AS FIRMA FROM " +
"(SELECT PWZ.*, (Urs.IMIE + ' ' + Urs.NAZWISKO) AS Urs FROM " +
"(SELECT * FROM PZWZ WHERE ID IS NOT NULL";
if (UID > 0) Query += " AND UID=" + UID;
if (TYPE > 1)
{
if (MIDI > 0) Query += " AND MID=" + MIDI;
if (MIDII > 0) Query += " AND CID=" + MIDII;
}
else
{
if (CID > 0) Query += " AND CID=" + CID;
if (MIDI > 0) Query += " AND MID=" + MIDI;
}
if (TYPE > 0) Query += " AND TYPE=" + TYPE;
if (!String.IsNullOrEmpty(DataOd)) Query += " AND DATA >= '" + DateTime.Parse(DataOd) + "'";
if (!String.IsNullOrEmpty(DataDo)) Query += " AND DATA <= '" + DateTime.Parse(DataDo) + "'";
Query += ") PWZ INNER JOIN Urs ON PWZ.UID=Urs.ID";
Query += ") PWZC INNER JOIN Customers ON PWZC.CID=Customers.ID";
Query += ") PWZM INNER JOIN MagType ON PWZM.MID=MagType.ID";
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Produkt> pzwzLista = new List<Produkt>();
for (int i = 0; i < table.Rows.Count; i++)
{
Produkt pzwz = new Produkt();
pzwz.PZWZID = (int)table.Rows[i]["ID"];
pzwz.UID = (int)table.Rows[i]["UID"];
pzwz.CID = (int)table.Rows[i]["CID"];
pzwz.MID = (int)table.Rows[i]["MID"];
pzwz.TYPE = (int)table.Rows[i]["TYPE"];
pzwz.DATA = table.Rows[i]["DATA"].ToString().Substring(0,10);
pzwz.SUMA = float.Parse(table.Rows[i]["SUMA"].ToString());
pzwz.UNAME = table.Rows[i]["Urs"].ToString();
pzwz.CNAME = table.Rows[i]["FIRMA"].ToString();
//pzwz.MID = (int)table.Rows[i]["MagType.ID"];
pzwz.MAGAZYN = table.Rows[i]["MAGAZYN"].ToString();
pzwzLista.Add(pzwz);
}
return pzwzLista;
}
public static List<Produkt> GetPrograms(int ID, int CATID, int CID, string name, string numerKat, string model,
string numerSer)
{
DataTable table = new DataTable();
string Query = "SELECT PRG.*, (Customers.FIRMA + ', ' + Customers.IMIE + ' ' + Customers.NAZWISKO + ', ' + Customers.ADRES) AS [CUSTOMER] FROM" +
" (SELECT * FROM Programs WHERE ID IS NOT NULL";
if (ID > 0) Query += " AND ID=" + ID;
if (!String.IsNullOrEmpty(name)) Query += " AND NAZWA LIKE '" + name + "%'";
if (CATID > 0) Query += " AND CATID=" + CATID;
if (CID > 0) Query += " AND CID=" + CID;
if (!String.IsNullOrEmpty(numerKat)) Query += " AND NUMERKAT LIKE '" + numerKat + "%'";
if (!String.IsNullOrEmpty(model)) Query += " AND MODEL LIKE '" + model + "%'";
if (!String.IsNullOrEmpty(numerSer)) Query += " AND NUMERSER LIKE '" + numerSer + "%'";
Query += ") PRG INNER JOIN Customers ON PRG.CID=Customers.ID";
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Produkt> programs = new List<Produkt>();
for (int i = 0; i < table.Rows.Count; i++)
{
Produkt program = new Produkt();
program.ID = int.Parse(table.Rows[i]["ID"].ToString());
program.NAME = table.Rows[i]["NAZWA"].ToString();
program.PRICE = float.Parse(table.Rows[i]["PRICE"].ToString().Replace('.',','));
program.VATPRICE = float.Parse(table.Rows[i]["VATPRICE"].ToString().Replace('.', ','));
program.CNAME = table.Rows[i]["CUSTOMER"].ToString();
program.NUMERKAT = table.Rows[i]["NUMERKAT"].ToString();
program.MODEL = table.Rows[i]["MODEL"].ToString();
program.NUMERSER = table.Rows[i]["NUMERSER"].ToString();
program.OPIS = table.Rows[i]["OPIS"].ToString();
program.CATID = (int)table.Rows[i]["CATID"];
program.CID = (int)table.Rows[i]["CID"];
//program.STAN = int.Parse(table.Rows[i]["CNT"].ToString());
program.JEDNOSTKA = table.Rows[i]["JEDNOSTKA"].ToString();
program.PROWIZJA = float.Parse(table.Rows[i]["PROWIZJA"].ToString().Replace('.',','));
try { program.PRICE2 = float.Parse(table.Rows[i]["PRICE2"].ToString().Replace('.', ',')); }
catch { program.PRICE2 = 0; }
try { program.VATPRICE2 = float.Parse(table.Rows[i]["VATPRICE2"].ToString().Replace('.', ',')); }
catch { program.VATPRICE2 = 0; }
programs.Add(program);
}
return programs;
}
public static List<Produkt> GetItems(int IID)
{
string Query = "SELECT Programs.* FROM (SELECT * FROM Items WHERE ID IS NOT NULL";
if (IID > 0) Query += " AND IID=" + IID + ")";
Query += " ITM INNER JOIN Programs ON ITM.PID=Programs.ID";
DataTable table = new DataTable();
table = Connect.FillCommand(Query);
if (table.Rows.Count == 0) return null;
List<Produkt> items = new List<Produkt>();
for (int i = 0; i < table.Rows.Count; i++)
{
Produkt item = new Produkt();
item.ID = (int)table.Rows[i]["ID"];
item.NAME = table.Rows[i]["NAZWA"].ToString();
//item. = (int)table.Rows[i]["PID"];
items.Add(item);
}
return items;
}
public static void NewPzWzItems(int PZWZID, Produkt[] towary)
{
for (int i = 0; i < towary.Length; i++)
Connect.SetCommand("INSERT INTO ItemsPzWz (PZID,PID,CNT,VATPRICE) VALUES(" + PZWZID +
"," + towary[i].ID + "," + towary[i].STAN + "," +
towary[i].SUMA.ToString().Replace(',','.') + ")");
}