-- OFFEN in mygood
in den selects für die Zugriffe auf Verteilpunkte,Produkte etc. einbauen (je nach member_type_kz)
query += " AND (cdp.fperson_contact_seq_id = "+req.query.DistUserId+" OR EXISTS (SELECT 1 FROM persongroup_members pgm WHERE pgm.member_type_kz IN ('EDITOR','SELLER','ADMIN') AND pgm.fpersongroup_seq_id = cd.fpersongroup_seq_id AND pgm.fperson_member_seq_id = "+req.query.DistUserId+"))";
-- Erledigt
drop table person_cd_referer;
ALTER TABLE person DROP FOREIGN KEY person_ibfk_20;
alter table person drop column fcd_referer_seq_id;
Procedures neu: UpdateCompany,UpdateDistProduct
log tabellen auf product, collective_distribution, company_plant
ALTER TABLE persongroup_members ADD member_type_kz VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;
-- persongroup_members können Stati haben:
NULL, darf nur in persongroup Tabellen suchen und kaufen
'EDITOR' darf Produkte, Verteilpunkt bearbeiten
'SELLER' darf zusätzlich Bestellungen bearbeiten und Verkaufen
'ADMIN' darf auch Betrieb bearbeite wie fperson_contact_seq_id
-- Anwendung
// Suche über Shop
query += " AND (cd.fpersongroup_seq_id IS NULL OR cd.fpersongroup_seq_id IS NOT NULL ";
query += " AND EXISTS (SELECT 1 FROM persongroup_members pgm WHERE pgm.fpersongroup_seq_id = cd.fpersongroup_seq_id AND pgm.fperson_member_seq_id = "+query_userid+")";
query += " )";
// Suche über/für Anlage
query += " AND (cdp.fperson_contact_seq_id = "+req.query.DistUserId+" OR EXISTS (SELECT 1 FROM persongroup_members pgm WHERE pgm.member_type_kz IN ('EDITOR','SELLER','ADMIN') AND pgm.fpersongroup_seq_id = cd.fpersongroup_seq_id AND pgm.fperson_member_seq_id = "+req.query.DistUserId+"))";
-- Alter Zustand (nun über persongroups in azeero)
mysql> select seq_id,substr(name,1,16),fperson_contact_seq_id,fcompany_plant_seq_id from collective_distribution;
+--------+--------------------+------------------------+-----------------------+
| seq_id | substr(name,1,16) | fperson_contact_seq_id | fcompany_plant_seq_id |
+--------+--------------------+------------------------+-----------------------+
| 1 | ecoenergy Lager | 9 | 33 |
| 16 | TU-Laden | 1 | NULL |
| 18 | Volxhaus | 1 | NULL |
| 19 | Bioladen Nau | 1 | NULL |
| 20 | 1 offizielle Aus | 1 | NULL |
| 21 | Paternion | 1 | NULL |
| 22 | Finkensteiner Te | 1 | 84 |
| 23 | Brunner | 1 | 85 |
| 24 | Josef Rauter "Sc | 1 | 86 |
| 25 | Exzelsior Kaffee | 1 | 87 |
| 26 | VöstlHof | 84 | 90 |
| 31 | Aetherwelt | 87 | 95 |
| 32 | Dorfgarten Boden | 88 | 96 |
| 35 | Imkerei Bienenga | 108 | 98 |
| 36 | FoodCoop St. Rup | 119 | 33 |
| 42 | DI Oliver Hönigs | 9 | 33 |
| 43 | KAFFEERÖSTEREI E | 147 | 220 |
| 44 | Exzelsior Cafe & | 147 | 220 |
| 46 | Paracelsus Droge | 165 | 317 |
| 47 | Berni0011@gmx.ne | 141 | 300 |
| 48 | *Morla - Biola | 172 | 349 |
| 49 | * *joerg.aurora | 177 | 350 |
| 51 | mathiaspuggl@gmx | 181 | 351 |
| 52 | tiroler_fleisch@ | 182 | 352 |
| 59 | Best of the Rest | 9 | 33 |
| 62 | CoSto Fach | 189 | 33 |
| 63 | Treffen | 190 | NULL |
| 64 | Petra Horni-Dere | 224 | 362 |
| 67 | LocoMotion – Fah | 85 | 363 |
| 68 | Naturnahes Bauen | 9 | 223 |
+--------+--------------------+------------------------+-----------------------+
30 rows in set (0,00 sec)
mysql>
mysql> select username from person where seq_id=119;
+----------+
| username |
+----------+
| ohonigfe |
+----------+
1 row in set (0,00 sec)
mysql> select username from person where seq_id=189;
+------------+
| username |
+------------+
| ohonigbest |
+------------+
1 row in set (0,00 sec)
mysql>
mysql> select fperson_contact_seq_id from company_plant where seq_id=33;
+------------------------+
| fperson_contact_seq_id |
+------------------------+
| 9 |
+------------------------+
1 row in set (0,00 sec)
mysql> select username from person where seq_id=9;
+-----------+
| username |
+-----------+
| ecoenergy |
+-----------+
1 row in set (0,00 sec)
mysql>
mysql> select count(*) from coll_dist_product where fcoll_dist_seq_id=36;
+----------+
| count(*) |
+----------+
| 143 |
+----------+
1 row in set (0,00 sec)
mysql> select count(*) from coll_dist_product where fcoll_dist_seq_id=1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0,00 sec)
mysql> select count(*) from coll_dist_product where fcoll_dist_seq_id=42;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0,00 sec)
mysql> select max(fproduct_seq_id) from coll_dist_product where fcoll_dist_seq_id=1;
+----------------------+
| max(fproduct_seq_id) |
+----------------------+
| NULL |
+----------------------+
1 row in set (0,00 sec)
mysql> select max(fproduct_seq_id) from coll_dist_product where fcoll_dist_seq_id=36;
+----------------------+
| max(fproduct_seq_id) |
+----------------------+
| 349 |
+----------------------+
1 row in set (0,00 sec)
mysql> select fcompany_plant_seq_id,fperson_contact_seq_id from products where seq_id=349;
+-----------------------+------------------------+
| fcompany_plant_seq_id | fperson_contact_seq_id |
+-----------------------+------------------------+
| 33 | 9 |
+-----------------------+------------------------+
1 row in set (0,00 sec)
mysql>
