MpWmbrA3

1--  35.199.186.160 
2
3--
4-- PostgreSQL database dump
5--
6
7-- Dumped from database version 9.6.1
8-- Dumped by pg_dump version 9.6.1
9
10DROP SCHEMA public CASCADE;
11CREATE SCHEMA public;
12
13GRANT ALL ON SCHEMA public TO postgres;
14GRANT ALL ON SCHEMA public TO public;
15
16CREATE TYPE type_ptype AS ENUM (
17  'servicefee', 
18  'reimbursement');
19
20CREATE TYPE type_pstatus AS ENUM ('to pay', 
21	'paid by CHQ', 
22	'paid by TSF', 
23	'paid by CASH', 
24	'confirmed',
25	'to be confirmed');
26
27
28--
29-- Name: 01arrcitylist; Type: TABLE; Schema: public; Owner: -
30--
31
32CREATE TABLE "01arrcitylist" (
33    arrcity character varying(254) NOT NULL,
34    station character varying(254)
35);
36
37
38--
39-- Name: 02shopstation; Type: TABLE; Schema: public; Owner: -
40--
41
42CREATE TABLE "02shopstation" (
43    station character varying(254) NOT NULL
44);
45
46
47--
48-- Name: 03currencyexchange; Type: TABLE; Schema: public; Owner: -
49--
50
51CREATE TABLE "03currencyexchange" (
52    currencyname character varying(254) NOT NULL,
53    currencyexchangerate double precision
54);
55
56
57--
58-- Name: 11groupinfo; Type: TABLE; Schema: public; Owner: -
59--
60
61CREATE TABLE "11groupinfo" (
62    "团表日期" date,
63    groupno character varying(254) NOT NULL,
64    pax smallint,
65    ln character varying(254),
66    paxt smallint,
67    "sched行程" character varying(254),
68    "sched日期" character varying(254),
69    "团类" character varying(254),
70    auagent character varying(254),
71    chinaagent character varying(254),
72    "客源地" character varying(254),
73    "团性" character varying(254),
74    "aucost明细" character varying(254),
75    aucost money,
76    "nzcost明细" character varying(254),
77    nzcost money,
78    other character varying(254),
79    nzdr1 double precision,
80    f19 character varying(254),
81    f20 character varying(254),
82    f21 character varying(254),
83    f22 character varying(254),
84    readstatus character varying(254)
85);
86
87
88--
89-- Name: 12groupschedule; Type: TABLE; Schema: public; Owner: -
90--
91
92CREATE TABLE "12groupschedule" (
93    sno character varying(254) NOT NULL,
94    groupno character varying(254),
95    arrcity character varying(254),
96    "到达日期" date,
97    "arr航班号" character varying(254),
98    "到达时间" date,
99    "离开日期" date,
100    "dep航班号" character varying(254),
101    "离开时间" date,
102    notes character varying(254),
103    gnid integer,
104    gtype character varying(254),
105    dn character varying(254),
106    "车牌号" character varying(254),
107    coach character varying(254),
108    sdate date,
109    "进店时间" date,
110    "进店当天行程" character varying(254),
111    "op夜游" character varying(254),
112    "金额" character varying(254),
113    "领取人签字" character varying(254)
114);
115
116--
117-- Name: 13groupnamelist; Type: TABLE; Schema: public; Owner: -
118--
119
120CREATE TABLE "13groupnamelist" (
121    groupno character varying(254) NOT NULL,
122    "中文名" character varying(254),
123    "拼音" character varying(254),
124    "性别" character varying(254),
125    "出生日期" date,
126    "出生地" character varying(254),
127    "护照号码" character varying(254),
128    "发证机关" character varying(254),
129    region character varying(254)
130);
131
132
133--
134-- Name: 20guideinfo; Type: TABLE; Schema: public; Owner: -
135--
136
137CREATE TABLE "20guideinfo" (
138    gnid integer NOT NULL,
139    station character varying(254),
140    gtype character varying(254),
141    gn character varying(254) NOT NULL,
142    englishname character varying(254),
143    firstname character varying(254),
144    lastname character varying(254),
145    tel character varying(254),
146    abnno character varying(254),
147    abnname character varying(254),
148    gst character varying(254),
149    bank character varying(254),
150    bsb character varying(254),
151    acctno character varying(254),
152    acctname character varying(254),
153    remark character varying(254)
154);
155
156
157--
158-- Name: 21kbpayment; Type: TABLE; Schema: public; Owner: -
159--
160
161CREATE TABLE "21kbpayment" (
162    sno character varying(254) NOT NULL,
163    ptype type_ptype,
164    deduct character varying(254),
165    "号码本号码" character varying(254),
166    pcom character varying(254),
167    pacc character varying(254),
168    pcode character varying(254),
169    ptotal money,
170    pstatus type_pstatus,
171    pdate date
172);
173
174
175--
176-- Name: 22guide/dr-servicefee/reimbursement; Type: TABLE; Schema: public; Owner: -
177--
178
179CREATE TABLE "22guide/dr-servicefee/reimbursement" (
180    dgpayid integer DEFAULT 0 NOT NULL,
181    "g/d" character varying(254),
182    "提交日期" date,
183    sno character varying(254) NOT NULL,
184    tdate character varying(254),
185    ptype type_ptype,
186    "明细" character varying(254),
187    "夜游" character varying(254),
188    tips money,
189    total money,
190    invoice text,
191    "归档状态" character varying(254),
192    pcom character varying(254),
193    pacc character varying(254),
194    ptotal money,
195    pcode character varying(254),
196    pstatus type_pstatus,
197    pdate date,
198    refusereason character varying(254)
199);
200
201
202--
203-- Name: 32shopcash; Type: TABLE; Schema: public; Owner: -
204--
205
206CREATE TABLE "32shopcash" (
207    sdate date NOT NULL,
208    station character varying(254) NOT NULL,
209    visa money,
210    unipay money,
211    mastercard money,
212    cashsales money,
213    cashaud money DEFAULT 0,
214    cashusd money,
215    cashnzd money,
216    cashrmb money,
217    cashhkd money,
218    casheur money,
219    "店务文具支出" character varying(254),
220    "店务货运支出" character varying(254),
221    "店务清洁支出" character varying(254),
222    shopexpenses money,
223    "其他支出" character varying(254),
224    otherexpenses money,
225    "垫付" character varying(254),
226    shopreceivable money DEFAULT 0,
227    "证明附件" text,
228    alipay money,
229    "支付宝" money,
230    nzdr2 double precision DEFAULT 0,
231    usdr1 double precision,
232    usdr2 double precision DEFAULT 0,
233    nzdr1 double precision,
234    rmbr1 double precision,
235    rmbr2 double precision DEFAULT 0,
236    hkdr1 double precision,
237    hkdr2 double precision DEFAULT 0,
238    eurr1 double precision,
239    eurr2 double precision DEFAULT 0
240);
241
242
243--
244-- Name: 33shipment; Type: TABLE; Schema: public; Owner: -
245--
246
247CREATE TABLE "33shipment" (
248    sno character varying(254) NOT NULL,
249    "货运日期" date,
250    "货运地" character varying(254),
251    "发出件数" character varying(254),
252    "货运费用aud" money,
253    "货运小票明细" character varying(254),
254    "货运小票单" text,
255    "预计到达日期" date,
256    "实际到达日期" date,
257    "实际到达件数" character varying(254),
258    "签收人" character varying(254),
259    "货运国内" character varying(254),
260    "批复人" character varying(254),
261    "货运团号" character varying(254),
262    "货运公司+no" character varying(254),
263    "货运费用rmb" money,
264    "货运凭证" text,
265    "签收日期" date,
266    rmbr1 double precision DEFAULT 0
267);
268
269
270--
271-- Name: 34nstleaderkb; Type: TABLE; Schema: public; Owner: -
272--
273
274CREATE TABLE "34nstleaderkb" (
275    sdate date,
276    station character varying(254),
277    groupno character varying(254) NOT NULL,
278    "lc留存" double precision,
279    "领取人" character varying(254),
280    "领取日期" character varying(254),
281    "领取人签字" text,
282    nzdr1 double precision,
283    ln character varying(254)
284);
285
286
287--
288-- Name: 36shopdeposit; Type: TABLE; Schema: public; Owner: -
289--
290
291CREATE TABLE "36shopdeposit" (
292    sdate date NOT NULL,
293    station character varying(254) NOT NULL,
294    dpsaud money DEFAULT 0,
295    dpsusd money,
296    dpsnzd money,
297    dpsrmb money,
298    dpshkd money,
299    dpseur money,
300    nzdr2 double precision DEFAULT 0,
301    usdr1 double precision,
302    usdr2 double precision DEFAULT 0,
303    nzdr1 double precision,
304    rmbr1 double precision,
305    rmbr2 double precision DEFAULT 0,
306    hkdr1 double precision,
307    hkdr2 double precision DEFAULT 0,
308    eurr1 double precision,
309    eurr2 double precision DEFAULT 0
310);
311
312
313--
314-- Name: 37refundlist; Type: TABLE; Schema: public; Owner: -
315--
316
317CREATE TABLE "37refundlist" (
318    refundno character varying(254) NOT NULL,
319    refunddate character varying(254),
320    sno character varying(254) NOT NULL,
321    salesamount money DEFAULT 0,
322    refundamount money DEFAULT 0,
323    lcreturn money DEFAULT 0,
324    lbreturn money DEFAULT 0,
325    gcreturn money DEFAULT 0,
326    gbreturn money DEFAULT 0,
327    "退货原因" text,
328    "退货状态" character varying(254),
329    "收退货经手人" character varying(254),
330    "入库单no" integer DEFAULT 0,
331    "证明文件" text
332);
333
334
335--
336-- Name: 41shopgroupsales; Type: TABLE; Schema: public; Owner: -
337--
338
339CREATE TABLE "41shopgroupsales" (
340    sno character varying(254) NOT NULL,
341    "店小号" character varying(254),
342    dr money DEFAULT 0,
343    lc money DEFAULT 0,
344    lb money DEFAULT 0,
345    lzc money DEFAULT 0,
346    gc money DEFAULT 0,
347    gb money DEFAULT 0,
348    guggb money DEFAULT 0,
349    glab money DEFAULT 0,
350    nt money DEFAULT 0,
351    sop money DEFAULT 0,
352    uggnk money DEFAULT 0,
353    uggjb money DEFAULT 0,
354    huiteng money DEFAULT 0,
355    slanst money,
356    gcwool money DEFAULT 0,
357    bbag money DEFAULT 0,
358    oop money DEFAULT 0,
359    olanst money,
360    bh money DEFAULT 0,
361    bp money DEFAULT 0,
362    groupsales money DEFAULT 0,
363    localsales money DEFAULT 0,
364    "gp$" money DEFAULT 0,
365    nzdr double precision DEFAULT 0,
366    notes character varying(254),
367    zengersales money DEFAULT 0 NOT NULL,
368    kingopalsales money DEFAULT 0,
369    wooluxsales money DEFAULT 0,
370    deersales money DEFAULT 0,
371    "g/d" integer
372);
373
374
375--
376-- Name: 44coogroupsales; Type: TABLE; Schema: public; Owner: -
377--
378
379CREATE TABLE "44coogroupsales" (
380    "合作社名" character varying(254),
381    id integer NOT NULL,
382    coogroupno character varying(254),
383    coodate date,
384    sno character varying(254),
385    "cooln/gn" character varying(254),
386    coopax integer DEFAULT 0,
387    coosales money DEFAULT 0,
388    recom money DEFAULT 0,
389    rrate character varying(254),
390    leaderkb character varying(254),
391    guidekb character varying(254),
392    "邮费" money DEFAULT 0,
393    rstatus character varying(254),
394    rcom character varying(254),
395    racc character varying(254),
396    rdate date
397);
398
399
400--
401-- Name: 46DFSsales; Type: TABLE; Schema: public; Owner: -
402--
403
404CREATE TABLE "46DFSsales" (
405    dfsname character varying(254) NOT NULL,
406    inshopdate character varying(254),
407    sno character varying(254),
408    groupno character varying(254) NOT NULL,
409    gn character varying(254),
410    totalsales money DEFAULT 0
411);
412
413
414--
415-- Name: 47号码本; Type: TABLE; Schema: public; Owner: -
416--
417
418CREATE TABLE "47号码本" (
419    id integer NOT NULL,
420    rdate date,
421    sno character varying(254) NOT NULL,
422    person character varying(254),
423    reason text,
424    "经手人" character varying(254),
425    gamount money DEFAULT 0,
426    schedpbdate character varying(254),
427    paybackdate character varying(254),
428    payto character varying(254),
429    status character varying(254)
430);
431
432
433--
434-- Name: 48店累计销售额; Type: TABLE; Schema: public; Owner: -
435--
436
437CREATE TABLE "48店累计销售额" (
438    sdate date,
439    station character varying(254),
440    "banking-nab" money DEFAULT 0,
441    "forex-adj" money DEFAULT 0,
442    "underover(under)" money DEFAULT 0,
443    "other-adj" money DEFAULT 0,
444    undepositedfunds money DEFAULT 0,
445    "z-read" money DEFAULT 0,
446    "z-read(new)" money DEFAULT 0,
447    differents money DEFAULT 0
448);
449
450
451--
452-- Name: 60coo/coninfo; Type: TABLE; Schema: public; Owner: -
453--
454
455CREATE TABLE "60coo/coninfo" (
456    supplierid integer NOT NULL,
457    brandname character varying(254) NOT NULL,
458    companyname character varying(254),
459    station character varying(254),
460    suppliertype character varying(254),
461    billingcycle character varying(254),
462    contactperson character varying(254),
463    contactnumber character varying(254),
464    emailaddress character varying(254),
465    notes character varying(254)
466);
467
468
469--
470-- Name: akl备用金; Type: TABLE; Schema: public; Owner: -
471--
472
473CREATE TABLE "akl备用金" (
474    sdate date NOT NULL,
475    amountcost money DEFAULT 0,
476    deposit money DEFAULT 0,
477    person character varying(254),
478    depositdate date
479);
480
481--
482-- Name: 11groupinfo groupno; Type: CONSTRAINT; Schema: public; Owner: -
483--
484
485ALTER TABLE ONLY "11groupinfo"
486    ADD CONSTRAINT groupno PRIMARY KEY (groupno);
487
488
489--
490-- Name: 44coogroupsales id; Type: CONSTRAINT; Schema: public; Owner: -
491--
492
493ALTER TABLE ONLY "44coogroupsales"
494    ADD CONSTRAINT id PRIMARY KEY (id);
495
496--
497-- Name: 02shopstation primarykey; Type: CONSTRAINT; Schema: public; Owner: -
498--
499
500ALTER TABLE ONLY "02shopstation"
501    ADD CONSTRAINT primarykey PRIMARY KEY (station);
502
503
504--
505-- Name: 01arrcitylist primarykey1; Type: CONSTRAINT; Schema: public; Owner: -
506--
507
508ALTER TABLE ONLY "01arrcitylist"
509    ADD CONSTRAINT primarykey1 PRIMARY KEY (arrcity);
510
511
512--
513-- Name: 46DFSsales primarykey10; Type: CONSTRAINT; Schema: public; Owner: -
514--
515
516ALTER TABLE ONLY "46DFSsales"
517    ADD CONSTRAINT primarykey10 PRIMARY KEY (sno);
518
519
520--
521-- Name: 47号码本 primarykey11; Type: CONSTRAINT; Schema: public; Owner: -
522--
523
524ALTER TABLE ONLY "47号码本"
525    ADD CONSTRAINT primarykey11 PRIMARY KEY (id);
526
527
528--
529-- Name: 60coo/coninfo primarykey12; Type: CONSTRAINT; Schema: public; Owner: -
530--
531
532ALTER TABLE ONLY "60coo/coninfo"
533    ADD CONSTRAINT primarykey12 PRIMARY KEY (supplierid);
534
535
536--
537-- Name: akl备用金 primarykey13; Type: CONSTRAINT; Schema: public; Owner: -
538--
539
540ALTER TABLE ONLY "akl备用金"
541    ADD CONSTRAINT primarykey13 PRIMARY KEY (sdate);
542
543
544--
545-- Name: 03currencyexchange primarykey2; Type: CONSTRAINT; Schema: public; Owner: -
546--
547
548ALTER TABLE ONLY "03currencyexchange"
549    ADD CONSTRAINT primarykey2 PRIMARY KEY (currencyname);
550
551
552--
553-- Name: 12groupschedule primarykey3; Type: CONSTRAINT; Schema: public; Owner: -
554--
555
556ALTER TABLE ONLY "12groupschedule"
557    ADD CONSTRAINT "12groupschedule_sno" PRIMARY KEY (sno);
558
559--
560-- Name: 20guideinfo primarykey4; Type: CONSTRAINT; Schema: public; Owner: -
561--
562
563ALTER TABLE ONLY "20guideinfo"
564    ADD CONSTRAINT "20guideinfo_gnid" PRIMARY KEY (gnid);
565
566
567--
568-- Name: 21kbpayment primarykey5; Type: CONSTRAINT; Schema: public; Owner: -
569--
570
571ALTER TABLE ONLY "21kbpayment"
572    ADD CONSTRAINT "21kbpayment_sno" PRIMARY KEY (sno);
573
574
575
576--
577-- Name: 22guide/dr-servicefee/reimbursement primarykey6; Type: CONSTRAINT; Schema: public; Owner: -
578--
579
580ALTER TABLE ONLY "22guide/dr-servicefee/reimbursement"
581    ADD CONSTRAINT "22guide/dr-servicefee/reimbursement_dbpayid" PRIMARY KEY (dgpayid);
582--
583-- Name: 32shopcash primarykey7; Type: CONSTRAINT; Schema: public; Owner: -
584--
585
586ALTER TABLE ONLY "32shopcash"
587    ADD CONSTRAINT "32shopcash_sdate_station" PRIMARY KEY (sdate, station);
588
589
590--
591-- Name: 34nstleaderkb primarykey8; Type: CONSTRAINT; Schema: public; Owner: -
592--
593
594ALTER TABLE ONLY "34nstleaderkb"
595    ADD CONSTRAINT "34nstleaderkb_groupno" PRIMARY KEY (groupno);
596
597
598--
599-- Name: 36shopdeposit primarykey9; Type: CONSTRAINT; Schema: public; Owner: -
600--
601
602ALTER TABLE ONLY "36shopdeposit"
603    ADD CONSTRAINT "36shopdeposit_sdate_station" PRIMARY KEY (sdate, station);
604
605
606--
607-- Name: 37refundlist refundno; Type: CONSTRAINT; Schema: public; Owner: -
608--
609
610ALTER TABLE ONLY "37refundlist"
611    ADD CONSTRAINT "37refundlist_refundno" PRIMARY KEY (refundno);
612
613
614--
615-- Name: 41shopgroupsales sno; Type: CONSTRAINT; Schema: public; Owner: -
616--
617
618ALTER TABLE ONLY "41shopgroupsales"
619    ADD CONSTRAINT "41shopgroupsales_sno" PRIMARY KEY (sno);
620
621
622--
623-- Name: 33shipment sno1; Type: CONSTRAINT; Schema: public; Owner: -
624--
625
626ALTER TABLE ONLY "33shipment"
627    ADD CONSTRAINT "33shipment_sno" PRIMARY KEY (sno);
628
629
630--
631-- Name: 03shopstation32shopcash; Type: INDEX; Schema: public; Owner: -
632--
633
634CREATE INDEX "03shopstation32shopcash" ON "32shopcash" USING btree (station);
635
636
637
638--
639-- Name: 11groupinfo13附团名单; Type: INDEX; Schema: public; Owner: -
640--
641
642CREATE INDEX "11groupinfo13附团名单" ON "13groupnamelist" USING btree (groupno);
643
644
645--
646-- Name: 11groupinfo44consignmentgroupsales; Type: INDEX; Schema: public; Owner: -
647--
648
649CREATE INDEX "12groupschedule44consignmentgroupsales" ON "44coogroupsales" USING btree (sno);
650
651
652--
653-- Name: 12groupschedule22guide/dr-wage/df; Type: INDEX; Schema: public; Owner: -
654--
655
656CREATE INDEX "12groupschedule22guide/dr-wage/df" ON "22guide/dr-servicefee/reimbursement" USING btree (sno);
657
658
659--
660-- Name: 30shopstation34leaderkb留存; Type: INDEX; Schema: public; Owner: -
661--
662
663CREATE INDEX "30shopstation34leaderkb留存" ON "34nstleaderkb" USING btree (station);
664
665
666--
667-- Name: 30station20guideinfo; Type: INDEX; Schema: public; Owner: -
668--
669
670CREATE INDEX "30station20guideinfo" ON "20guideinfo" USING btree (station);
671
672
673--
674-- Name: 30stationarrcitylist; Type: INDEX; Schema: public; Owner: -
675--
676
677CREATE INDEX "30stationarrcitylist" ON "01arrcitylist" USING btree (station);
678
679
680--
681-- Name: arrcitylist12groupschedule; Type: INDEX; Schema: public; Owner: -
682--
683
684CREATE INDEX arrcitylist12groupschedule ON "12groupschedule" USING btree (arrcity);
685
686
687--
688-- Name: gnid; Type: INDEX; Schema: public; Owner: -
689--
690
691CREATE INDEX gnid ON "20guideinfo" USING btree (gnid);
692
693
694--
695-- Name: invoice_6e2f22e29a2346128cc235ef8c93f211; Type: INDEX; Schema: public; Owner: -
696--
697
698CREATE UNIQUE INDEX invoice_6e2f22e29a2346128cc235ef8c93f211 ON "22guide/dr-servicefee/reimbursement" USING btree (invoice);
699
700
701--
702-- Name: pcode; Type: INDEX; Schema: public; Owner: -
703--
704
705CREATE INDEX pcode ON "21kbpayment" USING btree (pcode);
706
707
708--
709-- Name: pcode1; Type: INDEX; Schema: public; Owner: -
710--
711
712CREATE INDEX pcode1 ON "22guide/dr-servicefee/reimbursement" USING btree (pcode);
713
714
715--
716-- Name: 证明文件_a07672c2032e4295b891ca0f7d5d8210; Type: INDEX; Schema: public; Owner: -
717--
718
719CREATE UNIQUE INDEX "证明文件_a07672c2032e4295b891ca0f7d5d8210" ON "37refundlist" USING btree ("证明文件");
720
721
722--
723-- Name: 证明附件_b084422a6b0248419aa6cc2656de44bb; Type: INDEX; Schema: public; Owner: -
724--
725
726CREATE UNIQUE INDEX "证明附件_b084422a6b0248419aa6cc2656de44bb" ON "32shopcash" USING btree ("证明附件");
727
728
729--
730-- Name: 货运凭证_af04b74b825a488e94e3841bb7652875; Type: INDEX; Schema: public; Owner: -
731--
732
733CREATE UNIQUE INDEX "货运凭证_af04b74b825a488e94e3841bb7652875" ON "33shipment" USING btree ("货运凭证");
734
735
736--
737-- Name: 货运小票单_f3622badfc0045fa9f80d37e2a6bd93a; Type: INDEX; Schema: public; Owner: -
738--
739
740CREATE UNIQUE INDEX "货运小票单_f3622badfc0045fa9f80d37e2a6bd93a" ON "33shipment" USING btree ("货运小票单");
741
742
743--
744-- Name: 领取人签字_960e4685d88742189ad520a0e792d031; Type: INDEX; Schema: public; Owner: -
745--
746
747CREATE UNIQUE INDEX "领取人签字_960e4685d88742189ad520a0e792d031" ON "34nstleaderkb" USING btree ("领取人签字");
748
749
750
751
752    
753ALTER TABLE public."12groupschedule"
754    ADD CONSTRAINT foreignkey_gnid FOREIGN KEY (gnid)
755    REFERENCES public."20guideinfo" (gnid) MATCH SIMPLE
756    ON UPDATE CASCADE
757    ON DELETE CASCADE;
758
759
760ALTER TABLE ONLY "21kbpayment"
761    ADD CONSTRAINT fkey10 FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
762    ON UPDATE CASCADE
763    ON DELETE CASCADE;
764
765ALTER TABLE ONLY "37refundlist"
766    ADD CONSTRAINT "12groupschedule22guide/dr-wage/df" FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
767    ON UPDATE CASCADE
768    ON DELETE CASCADE;
769
770
771--
772-- Name: 32shopcash 03shopstation32shopcash; Type: FK CONSTRAINT; Schema: public; Owner: -
773--
774
775ALTER TABLE ONLY "32shopcash"
776    ADD CONSTRAINT "03shopstation32shopcash" FOREIGN KEY (station) REFERENCES "02shopstation"(station)
777    ON UPDATE CASCADE;
778
779
780--
781-- Name: 13groupnamelist 11groupinfo13附团名单; Type: FK CONSTRAINT; Schema: public; Owner: -
782--
783
784ALTER TABLE ONLY "13groupnamelist"
785    ADD CONSTRAINT "11groupinfo13附团名单" FOREIGN KEY (groupno) REFERENCES "11groupinfo"(groupno)
786    ON UPDATE CASCADE;
787
788
789--
790-- Name: 34nstleaderkb 11groupinfo34leaderkb留存; Type: FK CONSTRAINT; Schema: public; Owner: -
791--
792
793ALTER TABLE ONLY "34nstleaderkb"
794    ADD CONSTRAINT "11groupinfo34leaderkb留存" FOREIGN KEY (groupno) REFERENCES "11groupinfo"(groupno)
795    ON UPDATE CASCADE;
796
797--
798-- Name: 22guide/dr-servicefee/reimbursement 12groupschedule22guide/dr-wage/df; Type: FK CONSTRAINT; Schema: public; Owner: -
799--
800
801ALTER TABLE ONLY "22guide/dr-servicefee/reimbursement"
802    ADD CONSTRAINT "12groupschedule22guide/dr-wage/df" FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
803    ON UPDATE CASCADE
804    ON DELETE CASCADE;
805
806
807--
808-- Name: 41shopgroupsales 12groupschedule41店groupsales; Type: FK CONSTRAINT; Schema: public; Owner: -
809--
810
811ALTER TABLE ONLY "41shopgroupsales"
812    ADD CONSTRAINT "12groupschedule41店groupsales" FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
813    ON UPDATE CASCADE
814    ON DELETE CASCADE;
815
816
817
818--
819-- Name: 34nstleaderkb 30shopstation34leaderkb留存; Type: FK CONSTRAINT; Schema: public; Owner: -
820--
821
822ALTER TABLE ONLY "34nstleaderkb"
823    ADD CONSTRAINT "30shopstation34leaderkb留存" FOREIGN KEY (station) REFERENCES "02shopstation"(station)
824    ON UPDATE CASCADE;
825
826
827--
828-- Name: 20guideinfo 30station20guideinfo; Type: FK CONSTRAINT; Schema: public; Owner: -
829--
830
831ALTER TABLE ONLY "20guideinfo"
832    ADD CONSTRAINT "30station20guideinfo" FOREIGN KEY (station) REFERENCES "02shopstation"(station)
833    ON UPDATE CASCADE;
834
835
836--
837-- Name: 01arrcitylist 30stationarrcitylist; Type: FK CONSTRAINT; Schema: public; Owner: -
838--
839
840ALTER TABLE ONLY "01arrcitylist"
841    ADD CONSTRAINT "30stationarrcitylist" FOREIGN KEY (station) REFERENCES "02shopstation"(station)
842    ON UPDATE CASCADE;
843
844
845--
846-- Name: 33shipment 41店groupsales33shipment; Type: FK CONSTRAINT; Schema: public; Owner: -
847--
848
849ALTER TABLE ONLY "33shipment"
850    ADD CONSTRAINT "41店groupsales33shipment" FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
851    ON UPDATE CASCADE;
852
853
854--
855-- Name: 12groupschedule arrcitylist12groupschedule; Type: FK CONSTRAINT; Schema: public; Owner: -
856--
857
858ALTER TABLE ONLY "12groupschedule"
859    ADD CONSTRAINT arrcitylist12groupschedule FOREIGN KEY (arrcity) REFERENCES "01arrcitylist"(arrcity)
860    ON UPDATE CASCADE;
861
862ALTER TABLE ONLY "44coogroupsales"
863    ADD CONSTRAINT "12groupschedule" FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
864    ON UPDATE CASCADE;
865
866ALTER TABLE ONLY "46DFSsales"
867    ADD CONSTRAINT fkey10 FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
868    ON UPDATE CASCADE;
869ALTER TABLE ONLY "47号码本"
870    ADD CONSTRAINT fkey10 FOREIGN KEY (sno) REFERENCES "12groupschedule"(sno)
871    ON UPDATE CASCADE;
872
873
874--
875-- Name: GET_ARRIVE_DATE(integer); Type: FUNCTION; Schema: public; Owner: -
876--
877
878CREATE FUNCTION GET_ARRIVE_DATE(text) RETURNS date
879    LANGUAGE sql IMMUTABLE STRICT
880    AS $_$ SELECT "到达日期" FROM "12groupschedule" WHERE "12groupschedule"."sno" = $1 $_$;
881
882
883--
884-- Name: GET_STATION(integer); Type: FUNCTION; Schema: public; Owner: -
885--
886
887CREATE FUNCTION GET_STATION(text) RETURNS text
888    LANGUAGE sql IMMUTABLE STRICT
889    AS $_$ SELECT "station" FROM ("12groupschedule" INNER JOIN "01arrcitylist"  ON "01arrcitylist"."arrcity" = "12groupschedule"."arrcity") WHERE "12groupschedule"."sno" = $1 $_$;
890
891SET default_tablespace = '';
892
893SET default_with_oids = false;
894
895
896--
897-- Name: IS_AKL_FROM_SNO(text); Type: FUNCTION; Schema: public; Owner: -
898--
899
900CREATE FUNCTION IS_AKL_FROM_SNO(text) RETURNS boolean
901    LANGUAGE sql IMMUTABLE STRICT
902    AS $_$ SELECT GET_STATION($1) = 'AKL'; $_$;
903
904
905--
906-- Name: IS_MEL_FROM_SNO(text); Type: FUNCTION; Schema: public; Owner: -
907--
908
909CREATE FUNCTION IS_MEL_FROM_SNO(text) RETURNS boolean
910    LANGUAGE sql IMMUTABLE STRICT
911    AS $_$ SELECT GET_STATION($1) = 'MEL'; $_$;
912
913
914--
915-- Name: IS_OOL_FROM_SNO(text); Type: FUNCTION; Schema: public; Owner: -
916--
917
918CREATE FUNCTION IS_OOL_FROM_SNO(text) RETURNS boolean
919    LANGUAGE sql IMMUTABLE STRICT
920    AS $_$ SELECT GET_STATION($1) = 'OOL'; $_$;
921
922--
923-- Name: IS_SYD_FROM_SNO(text); Type: FUNCTION; Schema: public; Owner: -
924--
925
926CREATE FUNCTION IS_SYD_FROM_SNO(text) RETURNS boolean
927    LANGUAGE sql IMMUTABLE STRICT
928    AS $_$ SELECT GET_STATION($1) = 'SYD'; $_$;
929
930
931CREATE FUNCTION IS_AKL_FROM_ARRCITY(text) RETURNS boolean
932    LANGUAGE sql IMMUTABLE STRICT
933    AS $_$ SELECT CASE WHEN station='AKL' THEN TRUE ELSE FALSE END FROM "01arrcitylist" WHERE "arrcity" = $1; $_$;
934
935CREATE FUNCTION IS_MEL_FROM_ARRCITY(text) RETURNS boolean
936    LANGUAGE sql IMMUTABLE STRICT
937    AS $_$ SELECT CASE WHEN station='MEL' THEN TRUE ELSE FALSE END FROM "01arrcitylist" WHERE "arrcity" = $1; $_$;
938
939CREATE FUNCTION IS_OOL_FROM_ARRCITY(text) RETURNS boolean
940    LANGUAGE sql IMMUTABLE STRICT
941    AS $_$ SELECT CASE WHEN station='OOL' THEN TRUE ELSE FALSE END FROM "01arrcitylist" WHERE "arrcity" = $1; $_$;
942
943CREATE FUNCTION IS_SYD_FROM_ARRCITY(text) RETURNS boolean
944    LANGUAGE sql IMMUTABLE STRICT
945    AS $_$ SELECT CASE WHEN station='SYD' THEN TRUE ELSE FALSE END FROM "01arrcitylist" WHERE "arrcity" = $1; $_$;
946
947--
948-- Name: IS_RECENT(date); Type: FUNCTION; Schema: public; Owner: -
949--
950
951CREATE FUNCTION IS_RECENT(date) RETURNS boolean
952    LANGUAGE sql IMMUTABLE STRICT
953    AS $_$ SELECT coalesce($1, CURRENT_DATE) >= CURRENT_DATE - INTERVAL '8 month'; $_$;
954
955--
956-- COLUMN
957-- PERMISSIONS
958--
959
960REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "syd_office1";
961REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "syd_office2";
962REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "shop_manager";
963REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "cashier";
964REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "maggie";
965REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "beijing_office1";
966REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "syd";
967REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "mel";
968REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "akl";
969REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "ool";
970REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "akl_cashier";
971REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "beijing_acc";
972REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "john";
973REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM "susan";
974
975
976
977GRANT SELECT, INSERT, UPDATE, REFERENCES ON ALL TABLES IN SCHEMA PUBLIC TO "john";
978GRANT SELECT, INSERT, UPDATE, REFERENCES ON ALL TABLES IN SCHEMA PUBLIC TO "susan";
979-- 
980--- TABLE 1
981GRANT SELECT, REFERENCES ON TABLE "01arrcitylist" TO PUBLIC;
982GRANT INSERT, UPDATE ON TABLE "01arrcitylist" TO "maggie";
983
984
985--- TABLE 2
986GRANT SELECT, REFERENCES ON TABLE "02shopstation" TO PUBLIC;
987GRANT INSERT, UPDATE ON TABLE "02shopstation" TO "maggie";
988
989
990
991
992--- TABLE 3
993GRANT REFERENCES ON TABLE "03currencyexchange" TO PUBLIC;
994GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "03currencyexchange" TO syd_office1;
995
996--- TABLE 11
997
998GRANT REFERENCES ON TABLE "11groupinfo" TO PUBLIC;
999/*GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE
1000    "团表日期",
1001    groupno,
1002    pax,
1003    ln,
1004    paxt,
1005    "sched行程",
1006    "sched日期",
1007    "团类",
1008    auagent,
1009    chinaagent,
1010    "客源地",
1011    "团性",
1012    "aucost明细",
1013    aucost,
1014    "nzcost明细",
1015    nzcost,
1016    other,
1017    f19,
1018    f20,
1019    f21,
1020    f22 ON TABLE "11groupinfo" TO maggie;
1021*/
1022
1023GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "11groupinfo" TO maggie;
1024
1025/*GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE
1026    "团表日期",
1027    groupno,
1028    pax,
1029    ln,
1030    paxt,
1031    "sched行程",
1032    "sched日期",
1033    "团类",
1034    auagent,
1035    chinaagent,
1036    "客源地",
1037    "团性",
1038    "aucost明细",
1039    aucost,
1040    "nzcost明细",
1041    nzcost,
1042    other,
1043    f19,
1044    f20,
1045    f21,
1046    f22 ON TABLE "11groupinfo" TO beijing_office1;*/
1047GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "11groupinfo" TO beijing_office1;
1048GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "11groupinfo" TO beijing_acc;
1049
1050GRANT SELECT
1051    (groupno,
1052    pax,
1053    ln,
1054    paxt,
1055    "sched行程",
1056    "团类",
1057    "客源地")
1058    ON TABLE "11groupinfo" TO shop_manager;
1059
1060GRANT SELECT
1061    ("sched日期")
1062    ON TABLE "11groupinfo" TO shop_manager;
1063GRANT INSERT
1064    ("sched日期")
1065    ON TABLE "11groupinfo" TO shop_manager;
1066GRANT REFERENCES
1067    ("sched日期")
1068    ON TABLE "11groupinfo" TO shop_manager;
1069GRANT UPDATE
1070    ("sched日期")
1071    ON TABLE "11groupinfo" TO shop_manager;
1072
1073--DROP VIEW IF EXISTS "11groupinfo_shop_manager" ;
1074--CREATE VIEW "11groupinfo_shop_manager" AS
1075--    SELECT 
1076--    groupno,
1077--    pax,
1078--    ln,
1079--    paxt,
1080--    "sched行程",
1081--    "sched日期",
1082--    "团类",
1083--    "客源地")
1084--    FROM "11groupinfo";
1085--GRANT SELECT,UPDATE, INSERT, DELETE  ON TABLE "11groupinfo_shop_manager" TO shop_manager;
1086
1087GRANT SELECT
1088    (groupno,
1089    pax,
1090    paxt,
1091    ln,
1092    "sched行程",
1093    "sched日期",
1094    "团类",
1095    "客源地")
1096    ON TABLE "11groupinfo" TO cashier;
1097GRANT REFERENCES
1098    (groupno,
1099    pax,
1100    paxt,
1101    ln,
1102    "sched行程",
1103    "sched日期",
1104    "团类",
1105    "客源地")
1106     ON TABLE "11groupinfo" TO cashier;
1107GRANT INSERT, UPDATE ("sched日期") ON TABLE "11groupinfo" TO cashier;
1108
1109
1110GRANT SELECT
1111    ("团表日期",
1112    groupno,
1113    pax,
1114    ln,
1115    paxt,
1116    "sched行程",
1117    "sched日期",
1118    "团类",
1119    auagent,
1120    chinaagent,
1121    "客源地",
1122    "团性",
1123    aucost,
1124    "nzcost明细",
1125    nzcost,
1126    nzdr1,
1127    other,
1128    f19,
1129    f20,
1130    f21,
1131    f22 ) 
1132    ON TABLE "11groupinfo" TO syd_office1;
1133
1134GRANT REFERENCES
1135    ("团表日期",
1136    groupno,
1137    pax,
1138    ln,
1139    paxt,
1140    "sched行程",
1141    "sched日期",
1142    "团类",
1143    auagent,
1144    chinaagent,
1145    "客源地",
1146    "团性",
1147    aucost,
1148    "nzcost明细",
1149    nzcost,
1150    nzdr1,
1151    other,
1152    f19,
1153    f20,
1154    f21,
1155    f22 ) 
1156    ON TABLE "11groupinfo" TO syd_office1;
1157
1158
1159GRANT SELECT
1160    ("团表日期",
1161    groupno,
1162    pax,
1163    ln,
1164    paxt,
1165    "sched行程",
1166    "sched日期",
1167    "团类",
1168    auagent,
1169    chinaagent,
1170    "客源地",
1171    "团性",
1172    aucost,
1173    "nzcost明细",
1174    nzcost,
1175    nzdr1,
1176    other,
1177    f19,
1178    f20,
1179    f21,
1180    f22 ) 
1181    ON TABLE "11groupinfo" TO syd_office2;
1182
1183GRANT REFERENCES
1184    ("团表日期",
1185    groupno,
1186    pax,
1187    ln,
1188    paxt,
1189    "sched行程",
1190    "sched日期",
1191    "团类",
1192    auagent,
1193    chinaagent,
1194    "客源地",
1195    "团性",
1196    aucost,
1197    "nzcost明细",
1198    nzcost,
1199    nzdr1,
1200    other,
1201    f19,
1202    f20,
1203    f21,
1204    f22 ) 
1205    ON TABLE "11groupinfo" TO syd_office2;
1206
1207-- TABLE 12
1208
1209
1210GRANT REFERENCES ON TABLE "12groupschedule" TO PUBLIC;
1211GRANT DELETE ON TABLE "12groupschedule" TO maggie;
1212
1213GRANT SELECT
1214    (sno,
1215    groupno,
1216    arrcity,
1217    "到达日期",
1218    "arr航班号",
1219    "到达时间",
1220    "离开日期",
1221    "dep航班号",
1222    "离开时间",
1223    notes)
1224	ON TABLE "12groupschedule" TO maggie;
1225GRANT INSERT
1226    (sno,
1227    groupno,
1228    arrcity,
1229    "到达日期",
1230    "arr航班号",
1231    "到达时间",
1232    "离开日期",
1233    "dep航班号",
1234    "离开时间",
1235    notes)
1236	ON TABLE "12groupschedule" TO maggie;
1237GRANT REFERENCES
1238    (sno,
1239    groupno,
1240    arrcity,
1241    "到达日期",
1242    "arr航班号",
1243    "到达时间",
1244    "离开日期",
1245    "dep航班号",
1246    "离开时间",
1247    notes)
1248	ON TABLE "12groupschedule" TO maggie;
1249GRANT UPDATE 
1250    (sno,
1251    groupno,
1252    arrcity,
1253    "到达日期",
1254    "arr航班号",
1255    "到达时间",
1256    "离开日期",
1257    "dep航班号",
1258    "离开时间",
1259    notes)
1260	ON TABLE "12groupschedule" TO maggie;
1261
1262GRANT SELECT
1263    (sno,
1264    groupno,
1265    arrcity,
1266    "到达日期",
1267    "arr航班号",
1268    "到达时间",
1269    "离开日期",
1270    "dep航班号",
1271    "离开时间",
1272    notes)
1273	ON TABLE "12groupschedule" TO beijing_office1;
1274GRANT INSERT
1275    (sno,
1276    groupno,
1277    arrcity,
1278    "到达日期",
1279    "arr航班号",
1280    "到达时间",
1281    "离开日期",
1282    "dep航班号",
1283    "离开时间",
1284    notes)
1285	ON TABLE "12groupschedule" TO beijing_office1;
1286GRANT REFERENCES
1287    (sno,
1288    groupno,
1289    arrcity,
1290    "到达日期",
1291    "arr航班号",
1292    "到达时间",
1293    "离开日期",
1294    "dep航班号",
1295    "离开时间",
1296    notes)
1297	ON TABLE "12groupschedule" TO beijing_office1;
1298GRANT UPDATE 
1299    (sno,
1300    groupno,
1301    arrcity,
1302    "到达日期",
1303    "arr航班号",
1304    "到达时间",
1305    "离开日期",
1306    "dep航班号",
1307    "离开时间",
1308    notes)
1309	ON TABLE "12groupschedule" TO beijing_office1;
1310
1311GRANT SELECT
1312    (gnid,
1313    gtype,
1314    dn,
1315    coach,
1316    "车牌号",
1317    sdate,
1318    "进店时间",
1319    "进店当天行程",
1320    "op夜游")
1321	ON TABLE "12groupschedule" TO beijing_office1;
1322GRANT REFERENCES
1323    (gnid,
1324    gtype,
1325    dn,
1326    coach,
1327    "车牌号",
1328    sdate,
1329    "进店时间",
1330    "进店当天行程",
1331    "op夜游")
1332	ON TABLE "12groupschedule" TO beijing_office1;
1333
1334
1335
1336GRANT SELECT ("sno",
1337"groupno",
1338"arrcity",
1339"到达日期",
1340"arr航班号",
1341"到达时间",
1342"离开日期",
1343"dep航班号",
1344"离开时间",
1345"notes",
1346"gnid",
1347"gtype",
1348"dn",
1349"coach",
1350"车牌号",
1351"sdate",
1352"进店时间",
1353"进店当天行程",
1354"op夜游",
1355"金额",
1356"领取人签字") ON TABLE "12groupschedule" TO beijing_acc;
1357
1358
1359GRANT REFERENCES ("sno",
1360"groupno",
1361"arrcity",
1362"到达日期",
1363"arr航班号",
1364"到达时间",
1365"离开日期",
1366"dep航班号",
1367"离开时间",
1368"notes",
1369"gnid",
1370"gtype",
1371"dn",
1372"coach",
1373"车牌号",
1374"sdate",
1375"进店时间",
1376"进店当天行程",
1377"op夜游",
1378"金额",
1379"领取人签字") ON TABLE "12groupschedule" TO beijing_acc;
1380
1381
1382GRANT INSERT ("sno",
1383"groupno",
1384"arrcity",
1385"到达日期",
1386"arr航班号",
1387"到达时间",
1388"离开日期",
1389"dep航班号",
1390"离开时间",
1391"notes") ON TABLE "12groupschedule" TO beijing_acc;
1392
1393
1394GRANT UPDATE ("sno",
1395"groupno",
1396"arrcity",
1397"到达日期",
1398"arr航班号",
1399"到达时间",
1400"离开日期",
1401"dep航班号",
1402"离开时间",
1403"notes") ON TABLE "12groupschedule" TO beijing_acc;
1404
1405GRANT SELECT
1406    (sno,
1407     groupno,
1408    arrcity,
1409    "到达日期",
1410    "arr航班号",
1411    "到达时间",
1412    "离开日期",
1413    "dep航班号",
1414    "离开时间",
1415    notes,
1416    "金额",
1417    "领取人签字")
1418    ON TABLE "12groupschedule" TO shop_manager;
1419GRANT REFERENCES
1420    (sno,
1421     groupno,
1422    arrcity,
1423    "到达日期",
1424    "arr航班号",
1425    "到达时间",
1426    "离开日期",
1427    "dep航班号",
1428    "离开时间",
1429    notes,
1430    "金额",
1431    "领取人签字")
1432    ON TABLE "12groupschedule" TO shop_manager;
1433
1434
1435GRANT SELECT
1436    (gnid,
1437    gtype,
1438    dn,
1439    "车牌号",
1440    coach,
1441    sdate,
1442    "进店时间",
1443    "进店当天行程",
1444    "op夜游")
1445ON TABLE "12groupschedule" TO shop_manager;
1446GRANT REFERENCES
1447    (gnid,
1448    gtype,
1449    dn,
1450    "车牌号",
1451    coach,
1452    sdate,
1453    "进店时间",
1454    "进店当天行程",
1455    "op夜游")
1456ON TABLE "12groupschedule" TO shop_manager;
1457GRANT UPDATE
1458    (gnid,
1459    gtype,
1460    dn,
1461    "车牌号",
1462    coach,
1463    sdate,
1464    "进店时间",
1465    "进店当天行程",
1466    "op夜游")
1467ON TABLE "12groupschedule" TO shop_manager;
1468GRANT INSERT
1469    (gnid,
1470    gtype,
1471    dn,
1472    "车牌号",
1473    coach,
1474    sdate,
1475    "进店时间",
1476    "进店当天行程",
1477    "op夜游")
1478ON TABLE "12groupschedule" TO shop_manager;
1479
1480GRANT SELECT
1481    (sno,
1482    groupno,
1483    arrcity,
1484    gnid,
1485    gtype,
1486    dn,
1487    coach,
1488    车牌号,
1489    sdate,
1490    进店时间,
1491    "进店时间",
1492    "到达日期",
1493    "进店当天行程",
1494    "arr航班号",
1495    "到达时间",
1496    "离开日期",
1497    "dep航班号",
1498    "离开时间",
1499    "notes"
1500    )
1501ON TABLE "12groupschedule" TO cashier;
1502GRANT REFERENCES
1503    (sno,
1504    groupno,
1505    arrcity,
1506    gnid,
1507    gtype,
1508    dn,
1509    coach,
1510    车牌号,
1511    sdate,
1512    进店时间,
1513    "进店时间",
1514    "进店当天行程",
1515    "到达日期",
1516    "arr航班号",
1517    "到达时间",
1518    "离开日期",
1519    "dep航班号",
1520    "离开时间",
1521    "notes"
1522    )
1523ON TABLE "12groupschedule" TO cashier;
1524
1525GRANT SELECT
1526("金额",
1527"领取人签字",
1528"进店当天行程")
1529ON TABLE "12groupschedule" TO cashier;
1530GRANT REFERENCES
1531("金额",
1532"领取人签字",
1533"进店当天行程")
1534ON TABLE "12groupschedule" TO cashier;
1535GRANT UPDATE
1536("金额",
1537"领取人签字",
1538"进店当天行程")
1539ON TABLE "12groupschedule" TO cashier;
1540GRANT INSERT
1541("金额",
1542"领取人签字",
1543"进店当天行程")
1544ON TABLE "12groupschedule" TO cashier;
1545
1546GRANT SELECT,REFERENCES, UPDATE, INSERT
1547ON TABLE "12groupschedule" TO syd_office1;
1548GRANT SELECT,REFERENCES, UPDATE, INSERT
1549ON TABLE "12groupschedule" TO syd_office2;
1550
1551
1552-- TABLE 13
1553
1554
1555GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "13groupnamelist" TO maggie;
1556GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "13groupnamelist" TO beijing_office1;
1557
1558--
1559-- TABLE 20
1560
1561GRANT REFERENCES ON TABLE "20guideinfo" TO PUBLIC;
1562GRANT SELECT (gnid,gn) ON TABLE "20guideinfo" TO PUBLIC;
1563GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "20guideinfo" TO syd_office2;
1564-- TABLE 21
1565GRANT REFERENCES ON TABLE "21kbpayment" TO PUBLIC;
1566GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "21kbpayment" TO syd_office2;
1567GRANT SELECT, REFERENCES ON TABLE "21kbpayment" TO shop_manager;
1568
1569-- TABLE 22
1570GRANT REFERENCES ON TABLE "22guide/dr-servicefee/reimbursement" TO PUBLIC;
1571GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "22guide/dr-servicefee/reimbursement" TO beijing_office1;
1572GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "22guide/dr-servicefee/reimbursement" TO syd_office2;
1573GRANT SELECT, REFERENCES ON TABLE "22guide/dr-servicefee/reimbursement" TO shop_manager;
1574
1575-- TABLE 32
1576GRANT REFERENCES ON TABLE "32shopcash" TO PUBLIC;
1577GRANT SELECT, REFERENCES ON TABLE "32shopcash" TO shop_manager;
1578GRANT SELECT
1579    (sdate,
1580    station,
1581    visa,
1582    unipay,
1583    mastercard,
1584    cashsales,
1585    cashaud,
1586    cashusd,
1587    cashnzd,
1588    cashrmb,
1589    cashhkd,
1590    casheur,
1591    "店务文具支出",
1592    "店务货运支出",
1593    "店务清洁支出",
1594    shopexpenses,
1595    "其他支出",
1596    otherexpenses,
1597    "垫付",
1598    shopreceivable,
1599    "证明附件")
1600ON TABLE "32shopcash" TO cashier;
1601GRANT REFERENCES
1602    (sdate,
1603    station,
1604    visa,
1605    unipay,
1606    mastercard,
1607    cashsales,
1608    cashaud,
1609    cashusd,
1610    cashnzd,
1611    cashrmb,
1612    cashhkd,
1613    casheur,
1614    "店务文具支出",
1615    "店务货运支出",
1616    "店务清洁支出",
1617    shopexpenses,
1618    "其他支出",
1619    otherexpenses,
1620    "垫付",
1621    shopreceivable,
1622    "证明附件")
1623ON TABLE "32shopcash" TO cashier;
1624GRANT UPDATE
1625    (sdate,
1626    station,
1627    visa,
1628    unipay,
1629    mastercard,
1630    cashsales,
1631    cashaud,
1632    cashusd,
1633    cashnzd,
1634    cashrmb,
1635    cashhkd,
1636    casheur,
1637    "店务文具支出",
1638    "店务货运支出",
1639    "店务清洁支出",
1640    shopexpenses,
1641    "其他支出",
1642    otherexpenses,
1643    "垫付",
1644    shopreceivable,
1645    "证明附件")
1646ON TABLE "32shopcash" TO cashier;
1647GRANT INSERT
1648    (sdate,
1649    station,
1650    visa,
1651    unipay,
1652    mastercard,
1653    cashsales,
1654    cashaud,
1655    cashusd,
1656    cashnzd,
1657    cashrmb,
1658    cashhkd,
1659    casheur,
1660    "店务文具支出",
1661    "店务货运支出",
1662    "店务清洁支出",
1663    shopexpenses,
1664    "其他支出",
1665    otherexpenses,
1666    "垫付",
1667    shopreceivable,
1668    "证明附件")
1669ON TABLE "32shopcash" TO cashier;
1670
1671GRANT SELECT, REFERENCES, UPDATE, INSERT, DELETE ON TABLE "32shopcash" TO syd_office1;
1672
1673
1674-- TABLE 33
1675
1676GRANT REFERENCES ON TABLE "33shipment" TO PUBLIC;
1677GRANT SELECT, REFERENCES, UPDATE, INSERT, DELETE ON TABLE "33shipment" TO shop_manager;
1678GRANT SELECT, REFERENCES, UPDATE, INSERT, DELETE ON TABLE "33shipment" TO syd_office1;
1679GRANT DELETE ON TABLE "33shipment" TO cashier;
1680GRANT SELECT
1681    (sno,
1682    "货运日期",
1683    "货运地",
1684    "发出件数",
1685    "货运费用aud",
1686    "货运小票明细",
1687    "货运小票单",
1688    "预计到达日期",
1689    "实际到达日期",
1690    "实际到达件数",
1691    "签收人",
1692    "货运国内",
1693    "批复人",
1694    "货运团号",
1695    "货运公司+no",
1696    "货运费用rmb",
1697    "货运凭证",
1698    "签收日期")
1699ON TABLE "33shipment" TO cashier;
1700GRANT REFERENCES
1701    (sno,
1702    "货运日期",
1703    "货运地",
1704    "发出件数",
1705    "货运费用aud",
1706    "货运小票明细",
1707    "货运小票单",
1708    "预计到达日期",
1709    "实际到达日期",
1710    "实际到达件数",
1711    "签收人",
1712    "货运国内",
1713    "批复人",
1714    "货运团号",
1715    "货运公司+no",
1716    "货运费用rmb",
1717    "货运凭证",
1718    "签收日期")
1719ON TABLE "33shipment" TO cashier;
1720GRANT UPDATE
1721    (sno,
1722    "货运日期",
1723    "货运地",
1724    "发出件数",
1725    "货运费用aud",
1726    "货运小票明细",
1727    "货运小票单",
1728    "预计到达日期",
1729    "实际到达日期",
1730    "实际到达件数",
1731    "签收人",
1732    "货运国内",
1733    "批复人",
1734    "货运团号",
1735    "货运公司+no",
1736    "货运费用rmb",
1737    "货运凭证",
1738    "签收日期")
1739ON TABLE "33shipment" TO cashier;
1740GRANT INSERT     
1741    (sno,
1742    "货运日期",
1743    "货运地",
1744    "发出件数",
1745    "货运费用aud",
1746    "货运小票明细",
1747    "货运小票单",
1748    "预计到达日期",
1749    "实际到达日期",
1750    "实际到达件数",
1751    "签收人",
1752    "货运国内",
1753    "批复人",
1754    "货运团号",
1755    "货运公司+no",
1756    "货运费用rmb",
1757    "货运凭证",
1758    "签收日期")
1759ON TABLE "33shipment" TO cashier;
1760
1761--
1762-- TABLE 34
1763
1764GRANT REFERENCES ON TABLE "34nstleaderkb" TO PUBLIC;
1765GRANT SELECT,REFERENCES ON TABLE "34nstleaderkb" TO shop_manager;
1766GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "34nstleaderkb" TO syd_office1;
1767GRANT DELETE ON TABLE "34nstleaderkb" TO cashier;
1768GRANT SELECT
1769   (sdate,
1770    station,
1771    groupno,
1772    "lc留存",
1773    "领取人",
1774    "领取日期",
1775    "领取人签字")
1776ON TABLE "34nstleaderkb" TO cashier;
1777GRANT INSERT
1778   (sdate,
1779    station,
1780    groupno,
1781    "lc留存",
1782    "领取人",
1783    "领取日期",
1784    "领取人签字")
1785ON TABLE "34nstleaderkb" TO cashier;
1786GRANT REFERENCES
1787   (sdate,
1788    station,
1789    groupno,
1790    "lc留存",
1791    "领取人",
1792    "领取日期",
1793    "领取人签字")
1794ON TABLE "34nstleaderkb" TO cashier;
1795GRANT UPDATE     
1796   (sdate,
1797    station,
1798    groupno,
1799    "lc留存",
1800    "领取人",
1801    "领取日期",
1802    "领取人签字")
1803ON TABLE "34nstleaderkb" TO cashier;
1804
1805-- TABLE 36
1806GRANT REFERENCES ON TABLE "36shopdeposit" TO PUBLIC;
1807GRANT SELECT,REFERENCES ON TABLE "36shopdeposit" TO shop_manager;
1808GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "36shopdeposit" TO cashier;
1809GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "36shopdeposit" TO syd_office1;
1810
1811-- TABLE 37
1812
1813GRANT REFERENCES ON TABLE "37refundlist" TO PUBLIC;
1814GRANT SELECT, REFERENCES ON TABLE "37refundlist" TO "shop_manager";
1815GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "37refundlist" TO cashier;
1816GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "37refundlist" TO syd_office1;
1817
1818
1819-- TABLE 41
1820GRANT REFERENCES ON TABLE "41shopgroupsales" TO PUBLIC;
1821GRANT SELECT,REFERENCES ON TABLE "41shopgroupsales" TO shop_manager;
1822GRANT SELECT,REFERENCES ON TABLE "41shopgroupsales" TO beijing_acc;
1823GRANT SELECT,REFERENCES ON TABLE "41shopgroupsales" TO syd_office2;
1824GRANT SELECT,REFERENCES (sno) ON TABLE "41shopgroupsales" TO cashier;
1825
1826GRANT SELECT
1827(
1828    "店小号",
1829    dr,
1830    lc,
1831    lb,
1832    gc,
1833    gb,
1834    nt,
1835    groupsales,
1836    localsales,
1837    "gp$",
1838    nzdr,
1839    notes
1840)
1841ON TABLE "41shopgroupsales" TO cashier;
1842GRANT INSERT
1843(
1844    "店小号",
1845    dr,
1846    lc,
1847    lb,
1848    gc,
1849    gb,
1850    nt,
1851    groupsales,
1852    localsales,
1853    "gp$",
1854    nzdr,
1855    notes
1856)
1857ON TABLE "41shopgroupsales" TO cashier;
1858GRANT REFERENCES
1859(
1860    "店小号",
1861    dr,
1862    lc,
1863    lb,
1864    gc,
1865    gb,
1866    nt,
1867    groupsales,
1868    localsales,
1869    "gp$",
1870    nzdr,
1871    notes
1872)
1873ON TABLE "41shopgroupsales" TO cashier;
1874GRANT UPDATE 
1875(
1876    "店小号",
1877    dr,
1878    lc,
1879    lb,
1880    gc,
1881    gb,
1882    nt,
1883    groupsales,
1884    localsales,
1885    "gp$",
1886    nzdr,
1887    notes
1888)
1889ON TABLE "41shopgroupsales" TO cashier;
1890
1891GRANT SELECT
1892(
1893    lzc,
1894    guggb,
1895    glab,
1896    sop,
1897    uggnk,
1898    uggjb,
1899    huiteng,
1900    slanst,
1901    zengersales
1902)
1903ON TABLE "41shopgroupsales" TO syd;
1904GRANT INSERT
1905(
1906    lzc,
1907    guggb,
1908    glab,
1909    sop,
1910    uggnk,
1911    uggjb,
1912    huiteng,
1913    slanst,
1914    zengersales
1915)
1916ON TABLE "41shopgroupsales" TO syd;
1917GRANT REFERENCES
1918(
1919    lzc,
1920    guggb,
1921    glab,
1922    sop,
1923    uggnk,
1924    uggjb,
1925    huiteng,
1926    slanst,
1927    zengersales
1928)
1929ON TABLE "41shopgroupsales" TO syd;
1930GRANT UPDATE 
1931(
1932    lzc,
1933    guggb,
1934    glab,
1935    sop,
1936    uggnk,
1937    uggjb,
1938    huiteng,
1939    slanst,
1940    zengersales
1941)
1942ON TABLE "41shopgroupsales" TO syd;
1943
1944GRANT SELECT
1945(
1946    gcwool,
1947    bbag,
1948    oop,
1949    olanst,
1950    kingopalsales
1951)
1952ON TABLE "41shopgroupsales" TO ool;
1953GRANT INSERT
1954(
1955    gcwool,
1956    bbag,
1957    oop,
1958    olanst,
1959    kingopalsales
1960)
1961ON TABLE "41shopgroupsales" TO ool;
1962GRANT REFERENCES
1963(
1964    gcwool,
1965    bbag,
1966    oop,
1967    olanst,
1968    kingopalsales
1969)
1970ON TABLE "41shopgroupsales" TO ool;
1971GRANT UPDATE 
1972(
1973    gcwool,
1974    bbag,
1975    oop,
1976    olanst,
1977    kingopalsales
1978)
1979ON TABLE "41shopgroupsales" TO ool;
1980
1981GRANT SELECT
1982(
1983    bh,
1984    bp,
1985    wooluxsales,
1986    deersales
1987)
1988ON TABLE "41shopgroupsales" TO akl;
1989GRANT INSERT
1990(
1991    bh,
1992    bp,
1993    wooluxsales,
1994    deersales
1995)
1996ON TABLE "41shopgroupsales" TO akl;
1997GRANT REFERENCES
1998(
1999    bh,
2000    bp,
2001    wooluxsales,
2002    deersales
2003)
2004ON TABLE "41shopgroupsales" TO akl;
2005GRANT UPDATE 
2006(
2007    bh,
2008    bp,
2009    wooluxsales,
2010    deersales
2011)
2012ON TABLE "41shopgroupsales" TO akl;
2013
2014
2015GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "41shopgroupsales" TO syd_office1;
2016
2017--TABLE 44
2018GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "44coogroupsales" TO syd_office1;
2019GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "44coogroupsales" TO syd_office2;
2020
2021
2022-- TABLE 46
2023
2024GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "46DFSsales" TO syd_office1;
2025GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "46DFSsales" TO syd_office2;
2026
2027-- TABLE 47
2028
2029GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "47号码本" TO syd_office1;
2030GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "47号码本" TO syd_office2;
2031
2032-- TABLE 48
2033
2034GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "48店累计销售额" TO syd_office1;
2035
2036-- TABLE 60
2037GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "60coo/coninfo" TO syd_office1;
2038
2039
2040-- TABLE AKL备用金
2041GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "akl备用金" TO akl_cashier;
2042GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE "akl备用金" TO syd_office1;
2043
2044----- RLS
2045
2046----- HERE!!!
2047--
2048-- Name: 12groupschedule; Type: ROW SECURITY; Schema: public; Owner: -
2049--
2050
2051ALTER TABLE "12groupschedule" DISABLE ROW LEVEL SECURITY;
2052ALTER TABLE "32shopcash" DISABLE ROW LEVEL SECURITY;
2053ALTER TABLE "33shipment" DISABLE ROW LEVEL SECURITY;
2054ALTER TABLE "34nstleaderkb" DISABLE ROW LEVEL SECURITY;
2055ALTER TABLE "36shopdeposit" DISABLE ROW LEVEL SECURITY;
2056ALTER TABLE "37refundlist" DISABLE ROW LEVEL SECURITY;
2057ALTER TABLE "41shopgroupsales" DISABLE ROW LEVEL SECURITY;
2058
2059
2060
2061ALTER TABLE "12groupschedule" ENABLE ROW LEVEL SECURITY;
2062ALTER TABLE "32shopcash" ENABLE ROW LEVEL SECURITY;
2063ALTER TABLE "33shipment" ENABLE ROW LEVEL SECURITY;
2064ALTER TABLE "34nstleaderkb" ENABLE ROW LEVEL SECURITY;
2065ALTER TABLE "36shopdeposit" ENABLE ROW LEVEL SECURITY;
2066ALTER TABLE "37refundlist" ENABLE ROW LEVEL SECURITY;
2067ALTER TABLE "41shopgroupsales" ENABLE ROW LEVEL SECURITY;
2068
2069
2070DROP POLICY akl ON "12groupschedule";
2071DROP POLICY syd ON "12groupschedule";
2072DROP POLICY mel ON "12groupschedule";
2073DROP POLICY ool ON "12groupschedule";
2074
2075
2076--- AKL
2077CREATE POLICY akl ON "12groupschedule" FOR ALL TO "akl" USING ((IS_RECENT("到达日期") 
2078	AND IS_AKL_FROM_ARRCITY(("arrcity")::text))) WITH CHECK ((IS_RECENT("到达日期") AND IS_AKL_FROM_ARRCITY(("arrcity")::text)));
2079
2080CREATE POLICY akl ON "32shopcash" FOR ALL TO "akl" USING ((IS_RECENT("sdate") 
2081	AND station = 'AKL')) WITH CHECK (IS_RECENT("sdate") AND station = 'AKL');
2082
2083CREATE POLICY akl ON "33shipment" FOR ALL TO "akl" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2084	AND IS_AKL_FROM_SNO(("sno")::text))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_AKL_FROM_SNO(("sno")::text)));
2085--UNSURE
2086CREATE POLICY akl ON "34nstleaderkb" FOR ALL TO "akl" USING (IS_RECENT("sdate") 
2087	AND "station" = 'AKL') WITH CHECK ((IS_RECENT("sdate") AND "station" = 'AKL'));
2088-- END UNSURE
2089CREATE POLICY akl ON "36shopdeposit" FOR ALL TO "akl" USING (IS_RECENT("sdate") 
2090	AND ("station" = 'AKL')) WITH CHECK (IS_RECENT("sdate") AND "station" = 'AKL');
2091
2092CREATE POLICY akl ON "37refundlist" FOR ALL TO "akl" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2093	AND IS_AKL_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_AKL_FROM_SNO(("sno"))));
2094
2095CREATE POLICY akl ON "41shopgroupsales" FOR ALL TO "akl" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2096	AND IS_AKL_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_AKL_FROM_SNO(("sno"))));
2097
2098
2099--- OOL
2100
2101
2102CREATE POLICY ool ON "12groupschedule" FOR ALL TO "ool" USING ((IS_RECENT("到达日期") 
2103	AND IS_OOL_FROM_ARRCITY(("arrcity")::text))) WITH CHECK ((IS_RECENT("到达日期") AND IS_OOL_FROM_ARRCITY(("arrcity")::text)));
2104
2105CREATE POLICY ool ON "32shopcash" FOR ALL TO "ool" USING ((IS_RECENT("sdate") 
2106	AND station = 'OOL')) WITH CHECK (IS_RECENT("sdate") AND station = 'OOL');
2107
2108CREATE POLICY ool ON "33shipment" FOR ALL TO "ool" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2109	AND IS_OOL_FROM_SNO(("sno")::text))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_OOL_FROM_SNO(("sno")::text)));
2110--UNSURE
2111CREATE POLICY ool ON "34nstleaderkb" FOR ALL TO "ool" USING (IS_RECENT("sdate") 
2112	AND "station" = 'OOL') WITH CHECK ((IS_RECENT("sdate") AND "station" = 'OOL'));
2113-- END UNSURE
2114CREATE POLICY ool ON "36shopdeposit" FOR ALL TO "ool" USING (IS_RECENT("sdate") 
2115	AND ("station" = 'OOL')) WITH CHECK (IS_RECENT("sdate") AND "station" = 'OOL');
2116
2117CREATE POLICY ool ON "37refundlist" FOR ALL TO "ool" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2118	AND IS_OOL_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_OOL_FROM_SNO(("sno"))));
2119
2120CREATE POLICY ool ON "41shopgroupsales" FOR ALL TO "ool" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2121	AND IS_OOL_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_OOL_FROM_SNO(("sno"))));
2122
2123
2124
2125--- SYD
2126
2127CREATE POLICY syd ON "12groupschedule" FOR ALL TO "syd" USING ((IS_RECENT("到达日期") 
2128	AND IS_SYD_FROM_ARRCITY(("arrcity")::text))) WITH CHECK ((IS_RECENT("到达日期") AND IS_SYD_FROM_ARRCITY(("arrcity")::text)));
2129
2130CREATE POLICY syd ON "32shopcash" FOR ALL TO "syd" USING ((IS_RECENT("sdate") 
2131	AND station = 'SYD')) WITH CHECK (IS_RECENT("sdate") AND station = 'SYD');
2132
2133CREATE POLICY syd ON "33shipment" FOR ALL TO "syd" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2134	AND IS_SYD_FROM_SNO(("sno")::text))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_SYD_FROM_SNO(("sno")::text)));
2135--UNSURE
2136CREATE POLICY syd ON "34nstleaderkb" FOR ALL TO "syd" USING (IS_RECENT("sdate") 
2137	AND "station" = 'SYD') WITH CHECK ((IS_RECENT("sdate") AND "station" = 'SYD'));
2138-- END UNSURE
2139CREATE POLICY syd ON "36shopdeposit" FOR ALL TO "syd" USING (IS_RECENT("sdate") 
2140	AND ("station" = 'SYD')) WITH CHECK (IS_RECENT("sdate") AND "station" = 'SYD');
2141
2142CREATE POLICY syd ON "37refundlist" FOR ALL TO "syd" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2143	AND IS_SYD_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_SYD_FROM_SNO(("sno"))));
2144
2145CREATE POLICY syd ON "41shopgroupsales" FOR ALL TO "syd" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2146	AND IS_SYD_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_SYD_FROM_SNO(("sno"))));
2147--- MEL
2148
2149CREATE POLICY mel ON "12groupschedule" FOR ALL TO "mel" USING ((IS_RECENT("到达日期") 
2150	AND IS_MEL_FROM_ARRCITY(("arrcity")::text))) WITH CHECK ((IS_RECENT("到达日期") AND IS_MEL_FROM_ARRCITY(("arrcity")::text)));
2151
2152CREATE POLICY mel ON "32shopcash" FOR ALL TO "mel" USING ((IS_RECENT("sdate") 
2153	AND station = 'MEL')) WITH CHECK (IS_RECENT("sdate") AND station = 'MEL');
2154
2155CREATE POLICY mel ON "33shipment" FOR ALL TO "mel" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2156	AND IS_MEL_FROM_SNO(("sno")::text))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_MEL_FROM_SNO(("sno")::text)));
2157--UNSURE
2158CREATE POLICY mel ON "34nstleaderkb" FOR ALL TO "mel" USING (IS_RECENT("sdate") 
2159	AND "station" = 'MEL') WITH CHECK ((IS_RECENT("sdate") AND "station" = 'MEL'));
2160-- END UNSURE
2161CREATE POLICY mel ON "36shopdeposit" FOR ALL TO "mel" USING (IS_RECENT("sdate") 
2162	AND ("station" = 'MEL')) WITH CHECK (IS_RECENT("sdate") AND "station" = 'MEL');
2163
2164CREATE POLICY mel ON "37refundlist" FOR ALL TO "mel" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2165	AND IS_MEL_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_MEL_FROM_SNO(("sno"))));
2166
2167CREATE POLICY mel ON "41shopgroupsales" FOR ALL TO "mel" USING ((IS_RECENT(GET_ARRIVE_DATE("sno")) 
2168	AND IS_MEL_FROM_SNO(("sno")))) WITH CHECK ((IS_RECENT(GET_ARRIVE_DATE("sno")) AND IS_MEL_FROM_SNO(("sno"))));
2169
2170
2171
2172-- RULESSSSSSSSS
2173
2174CREATE RULE "12groupschedule_update21" AS ON INSERT TO "12groupschedule" DO ALSO 
2175 INSERT INTO "21kbpayment"(sno) VALUES (NEW.sno);
2176
2177
2178CREATE RULE "12groupschedule_update41" AS ON INSERT TO "12groupschedule" DO ALSO 
2179 INSERT INTO "41shopgroupsales"(sno) VALUES (NEW.sno);
2180
2181CREATE RULE "11groupinfo_currency" AS ON INSERT TO "11groupinfo" DO ALSO
2182 UPDATE "11groupinfo" SET nzdr = (SELECT "currencyexchangerate" WHERE "03currencyexchange"."currencyname" = 'nzd-aud') WHERE