· 9 years ago · Oct 05, 2016, 05:18 AM
1/**********************************************
2 Report : Form - Quotation
3
4***********************************************/
5
6drop table if exists "dbFinance".rpPQuoForm cascade;
7create table if not exists "dbFinance".rpPQuoForm (
8 --* Constant
9 tbn_par text not null default '"dbFinance".tbPQuo'
10 ,tbn_item text not null default '"dbFinance".tbPQuoItem'
11 ,tbn_chq text null
12 ,tbl_cheq text null
13 -- ,orginal_name text not null default 'ต้นฉบับ/Original'
14 -- ,copy_name text not null default 'สำเนา/Copy'
15
16 --*
17 ,id_rec int null
18 ,tbl text null
19 ,tbl_item text null
20 ,tbl_chq text null
21 ,tbl_contact text null
22 ---* ------------------------
23 ,acc_slogan text null
24 ,reportname text null
25 ,OrgCopy boolean not null default true
26 ,Origination text not null default 'ต้นฉบับ/Original' check ( Origination in ( 'ต้นฉบับ/Original', 'สำเนา/Copy' ) )
27
28 --* Quotation Data ---------------------------------------
29 ,TranNo text -- varchar(20) null, -- comment 'เลขที่เà¸à¸à¸ªà¸²à¸£',
30 ,TranDate timestamp_wtz -- null, -- comment 'วันที่ทำรายà¸à¸²à¸£à¸•à¸²à¸¡à¹€à¸à¸à¸ªà¸²à¸£' ,
31 ,Reference text -- null, -- comment 'à¸à¹‰à¸²à¸‡à¸à¸´à¸‡',
32 ,taxno text
33 ,Description text -- null , -- เรื่à¸à¸‡
34 ,PayerType text -- "dbFinance".PayerPayeeType
35 ,Payer int -- null, -- comment 'ผู้จ่าย',
36 , PayerName text
37 ,PayeeType text -- "dbFinance".PayerPayeeType
38 ,Payee int -- null, -- comment 'ผู้รับ',
39 ,PayeeName text
40 --,payer_taxno text
41 ,IncExp Boolean -- default 'Y' , --enum ('Y','N') comment 'รายรับ หรืภรายจ่าย' ,
42 ,TaskType int -- null , -- comment 'ประเภทงาน',
43 , TaskName text -- ชื่à¸à¸›à¸£à¸°à¹€à¸ ทงาน
44 ,SumItem smallint -- null , -- comment 'จำนวนรายà¸à¸²à¸£',
45 ,SumAmt Num -- null, -- float(19,4) null comment 'รวามจำนวนเงินทั้งหมด ' ,
46
47 ,SumPay text-- num
48 ,SumBal text-- num
49 ,v_inc boolean
50 ,v_tax num
51 ,v_amt num
52 ,whd_amt num
53 ,dis_amt num
54
55 ,UID int -- not null, -- comment 'User DeName/ID',
56 ,ACC int -- not null,
57 ,TranType text
58 --* Workflow
59 ,Status text -- Status
60 ,StatusUpdate timestamp -- approve update
61 --*
62 ,salemanid int
63 ,saleman text -- saleman name
64 ,payer_taxno text
65 ,payer_crtermid int
66 ,payer_crterm text
67
68 ,payer_coordinator text
69 ,payer_phone text
70 ,payer_email text
71 --* End of Quotation Data ----------------
72 ,payer_admail text
73 ,saleman_phone text
74 ,dis_rate num
75 ,v_rate num
76 ,whd_rate num
77 ,total_gross num
78 ,total_after_discount num
79 ,total_after_vat num
80 ,trandate_text text
81 ,delvery_admail text
82 --*
83 ,pay_method text not null default 'วิธีชำระ'
84 ,pay_method_cash text not null default 'เงินสด/Cash'
85 ,pay_method_check text not null default 'เช็ค/Check'
86 ,pay_method_transfer text not null default 'เงินโà¸à¸™/TR'
87 ,pay_method_other text not null default 'à¸à¸·à¹ˆà¸™à¹†/Other'
88 ,pay_no text not null default 'เลขที่ /No'
89 ,pay_date text not null default 'วันที่ /Date'
90 ,pay_amt text not null default 'จำนวนเงิน/Amount'
91 ,pay_bank text not null default 'ธนาคาร/Bank'
92 ,pay_bankbr text not null default 'สาขา/Branch'
93 ,pay_payee text not null default 'ในนาม/Payee'
94 --*
95 ,pay_method_val_cash text not null default ''
96 ,pay_method_val_check text not null default ''
97 ,pay_method_val_transfer text not null default ''
98 ,pay_method_val_creditcard text not null default ''
99 ,pay_method_val_other text not null default ''
100 ,pay_method_val_remark text not null default ''
101 ,pay_no_val text not null default ''
102 ,pay_date_val text not null default ''
103 ,pay_amt_val text not null default ''
104 ,pay_bank_val text not null default ''
105 ,pay_bankbr_val text not null default ''
106 ,pay_payee_val text not null default ''
107
108) INHERITS ("dbSysRPT".tbReportParam ) ;
109select "dbSys".tblRegistration( '"dbFinance".rpPQuoForm', true );
110
111/**************************
112 Function : Item number format
113 Wut, 2016-09-26
114
115 "dbFinance".itmnum( a.amt, a.prod )
116
117 select "dbFinance".itmnum( 546::num, 1 );
118 select "dbFinance".itmnum( 546::int, 1 );
119 -- select "dbFinance".itmnum( 1::smallint, null );
120
121**************************************/
122drop function if exists "dbFinance".itmnum(
123 in val anyelement
124 , in prodid int
125 , in fm text
126);
127create or replace function "dbFinance".itmnum(
128 in val anyelement
129 , in prodid int
130 , in fm text= '999,999,999D99'
131 , in offs smallint = null
132)
133returns text as $$
134declare datatype text = pg_typeof( val); s text;
135begin
136 -- raise notice '93 datatype %', datatype;
137 s:=
138 case
139 when val is null then ''::text
140 else
141 case
142 when prodid is null then
143 case
144 when datatype = 'text' then val ::text
145 else ''::text
146 end
147 when val is null then ''::text
148 when datatype = 'text' then val::text
149 when datatype = 'smallint' then val::smallint::text
150 when datatype = 'integer' then val::integer::text
151 --when datatype = '"public".num' then to_char(val,fm)
152 else trim( to_char(val::numeric ,fm ))
153 end
154 end
155 ; --a.amt --D
156 --* offset
157 if (offs is not null) then
158 if offs >0 then
159 s:= repeat(' ',offs*2)||s;
160 end if;
161 end if;
162 return s;
163end $$ language plpgsql;
164
165/***********************
166 Trigger : Validate parameters
167 21-10-2014,Wut
168************************/
169create or replace function "dbFinance".rpPQuoForm_validate() returns trigger as $$
170declare stock record; period record;
171declare find record; dat record; c int;
172 sql_cash text;
173 sql text;
174begin
175 if new.id_rec is null then
176 raise exception 'No Input';
177 end if;
178
179 --select id from "dbFinance".tbpquo where id =new.id_rec and acc=new.acc into find;
180 execute format('select id from %s where id = %s and acc= %s', new.tbn_par, new.id_rec, new.acc ) into find;
181 if find.id is null then
182 raise exception '%',format('Not found "%s"', new.id_rec );
183 end if;
184 --* view
185 --select "dbSys".tvCreateView ( new.uid, new.acc, '"dbFinance".tbPQuo') nam into new.tbl;
186 --select "dbSys".tvCreateView ( new.uid, new.acc, '"dbFinance".tbPQuoitem') nam into new.tbl_item;
187 select "dbSys".tvCreateView ( new.uid, new.acc, new.tbn_par) nam into new.tbl;
188 select "dbSys".tvCreateView ( new.uid, new.acc, new.tbn_item) nam into new.tbl_item;
189 select "dbSys".tvCreateView ( new.uid, new.acc, '"dbContact".tbcontact' ) nam into new.tbl_contact;
190
191 --* get data --
192 execute format(
193 'select a.*
194 ,b1.admail payer_admail
195 ,c1.tvalue saleman_phone
196 ,null::num dis_rate
197 ,null::num v_rate
198 ,null::num whd_rate
199 ,a.v_amt total_gross
200 ,a.v_amt - a.dis_amt total_after_discount
201 ,a.v_amt - a.dis_amt + a.v_tax total_after_vat
202 ,to_date( a.trandate::timestamp_wtz,yt:=1 ) trandate_text
203 ,"dbBase".tbaccconf_getdata( %L, a.acc ) acc_slogan -- E1
204 ,d.reportname
205 ,case when e1.admail is null then %L::text else e1.admail end delvery_admail -- E3
206 , %L Origination -- E2
207 ,f.taxid taxno
208 from %s a -- A
209 left join "dbContact".tbcontaddr b on b.pid = a.payer and b.title= %L and b.tcustom is null -- C
210 left join "dbContact".tbaddr b1 on b1.id = b.tvalue
211 left join "dbHR".tbemp c on c.id = a.salemanid
212 left join "dbContact".tbcontphone c1 on c1.pid= c.contactid and c1.title = %L and c1.tcustom is null -- D
213 left join "dbSysRPT".tbreportregis d on d.id = %s -- E
214 left join "dbContact".tbcontaddr e on e.pid = a.payer and e.title= %L and e.tcustom is null -- F
215 left join "dbContact".tbaddr e1 on e1.id = e.tvalue
216 left join %s f on f.id = a.payer -- G
217 where a.id = %s --B
218 '
219 ,'acc_slogan' -- E1
220 ,'' -- E3
221 , case when new.OrgCopy then 'ต้นฉบับ/Original' else 'สำเนา/Copy' end -- E2
222 , new.tbl -- A
223 , 'Home' -- C
224 , 'Main' -- D
225 , new.pid -- E
226 ,'Delivery' -- F
227 , new.tbl_contact -- G
228 , new.id_rec -- B
229
230
231 ) into dat;
232
233
234
235 select
236 dat.TranNo
237 ,dat.TranDate
238 ,dat.Reference
239 ,dat.Description
240 ,dat.PayerType
241 ,dat.Payer
242 ,dat. PayerName
243 ,dat.PayeeType
244 ,dat.Payee
245 ,dat.PayeeName
246 ,dat.IncExp
247 ,dat.TaskType
248 ,dat. TaskName
249 ,dat.SumItem
250 ,dat.SumAmt
251
252 ,to_char ( dat.SumPay,'999,999,999D99' )
253 ,to_char ( dat.SumBal,'999,999,999D99' ) -- dat.SumBal
254 ,dat.v_inc
255 ,dat.v_tax
256 ,dat.v_amt
257 ,dat.whd_amt
258 ,dat.dis_amt
259
260 ,dat.UID
261 ,dat.ACC
262 ,dat.TranType
263
264 ,dat.Status
265 ,dat.StatusUpdate
266
267 ,dat.salemanid
268 ,dat.saleman
269 ,dat.payer_taxno
270 ,dat.payer_crtermid
271 ,dat.payer_crterm
272
273 ,dat.payer_coordinator
274 ,dat.payer_phone
275 ,dat.payer_email
276 --*-------------------
277 ,dat.payer_admail
278 ,dat.saleman_phone
279 ,dat.dis_rate
280 ,dat.v_rate
281 ,dat.whd_rate
282 ,dat.total_gross
283 ,dat.total_after_discount
284 ,dat.total_after_vat
285 ,dat.trandate_text
286 ,dat.acc_slogan
287 ,dat.reportname
288 ,dat.delvery_admail
289 ,dat.Origination
290 ,dat.taxno
291 into
292 new.TranNo
293 ,new.TranDate
294 ,new.Reference
295 ,new.Description
296 ,new.PayerType
297 ,new.Payer
298 ,new. PayerName
299 ,new.PayeeType
300 ,new.Payee
301 ,new.PayeeName
302 ,new.IncExp
303 ,new.TaskType
304 ,new. TaskName
305 ,new.SumItem
306 ,new.SumAmt
307
308 ,new.SumPay
309 ,new.SumBal
310 ,new.v_inc
311 ,new.v_tax
312 ,new.v_amt
313 ,new.whd_amt
314 ,new.dis_amt
315
316 ,new.UID
317 ,new.ACC
318 ,new.TranType
319
320 ,new.Status
321 ,new.StatusUpdate
322
323 ,new.salemanid
324 ,new.saleman
325 ,new.payer_taxno
326 ,new.payer_crtermid
327 ,new.payer_crterm
328
329 ,new.payer_coordinator
330 ,new.payer_phone
331 ,new.payer_email
332 --*-------------------
333 ,NEW.payer_admail
334 ,new.saleman_phone
335 ,new.dis_rate
336 ,new.v_rate
337 ,new.whd_rate
338 ,new.total_gross
339 ,new.total_after_discount
340 ,new.total_after_vat
341 ,new.trandate_text
342 ,new.acc_slogan
343 ,new.reportname
344 ,new.delvery_admail
345 ,new.Origination
346 ,new.taxno
347 ;
348
349 --* dis rate ----------
350 -- select count( distinct dis_rate) from "dbFinance".tbpreqitemvw4 where (dis_rate is not null) or (dis_rate <> 0)
351 execute format(
352 'select count(*) from (
353 select distinct dis_rate from %s -- A
354 where pid= %s -- B
355 and ( (dis_rate is not null) or (dis_rate <> 0) )
356 ) a
357 '
358 ,new.tbl_item -- A
359 ,new.id_rec -- B
360
361 )
362 into c;
363 if c=1 then
364
365 execute format(
366 '
367 select distinct dis_rate from %s -- A
368 where pid= %s -- B
369 and ( (dis_rate is not null) or (dis_rate <> 0) )
370
371 '
372 ,new.tbl_item -- A
373 ,new.id_rec -- B
374
375 )
376 into new.dis_rate;
377
378 end if;
379
380 --* vat rate ----------
381 -- select count( distinct dis_rate) from "dbFinance".tbpreqitemvw4 where (dis_rate is not null) or (dis_rate <> 0)
382 execute format(
383 'select count(*) from (
384 select distinct v_rate from %s -- A
385 where pid= %s -- B
386 and ( (v_rate is not null) or (v_rate <> 0) )
387 ) a
388 '
389 ,new.tbl_item -- A
390 ,new.id_rec -- B
391
392 )
393 into c;
394 if c=1 then
395 execute format(
396 '
397 select distinct v_rate from %s -- A
398 where pid= %s -- B
399 and ( (v_rate is not null) or (v_rate <> 0) )
400
401 '
402 ,new.tbl_item -- A
403 ,new.id_rec -- B
404
405 )
406 into new.v_rate;
407
408 end if;
409
410
411 --* whd rate by item ----------
412 -- select count( distinct dis_rate) from "dbFinance".tbpreqitemvw4 where (dis_rate is not null) or (dis_rate <> 0)
413 execute format(
414 'select count(*) from (
415 select distinct whd_rate from %s -- A
416 where pid= %s -- B
417 and ( (whd_rate is not null) or (whd_rate <> 0) )
418 ) a
419 '
420 ,new.tbl_item -- A
421 ,new.id_rec -- B
422
423 )
424 into c;
425 if c=1 then
426 execute format(
427 '
428 select distinct whd_rate from %s -- A
429 where pid= %s -- B
430 and ( (whd_rate is not null) or (whd_rate <> 0) )
431
432 '
433 ,new.tbl_item -- A
434 ,new.id_rec -- B
435
436 )
437 into new.whd_rate;
438
439 end if;
440 -- raise notice '245';
441
442 --* Chq Payment -----------------------------------------------------------------------------------------------
443 if new.tbn_chq is not null then
444 select "dbSys".tvCreateView ( new.uid, new.acc, new.tbn_chq) nam into new.tbl_chq;
445 select "dbSys".tvCreateView ( new.uid, new.acc, '"dbFinance".tbcheq') nam into new.tbl_cheq;
446
447 /*
448 sql_cash := format (
449 'select
450 b.chqkind
451 ,array_to_string( array_agg( a.chqno order by a.orderno ), %L ) chqno -- A
452 ,array_to_string( array_agg( a.chqdate::text order by a.orderno ), %L) chqdate -- B
453 ,array_to_string( array_agg( a.payamt::text order by a.orderno ), %L ) chqamt --C
454 ,array_to_string( array_agg( b.bankname::text order by a.orderno ), %L ) bankname -- D
455 ,array_to_string( array_agg( b.bankbrname::text order by a.orderno ), %L ) banbrkname -- E
456 from
457 %s a -- "dbFinance".tbprecchqvw4 -- F
458 left join %s b on b.id = a.chq -- "dbFinance".tbcheqvw4 -- G
459 where
460 a.pid = %s -- H
461 and b.chqkind = -- I
462 group by
463 b.chqkind
464 '
465 ,',' -- A
466 ,',' -- B
467 ,',' -- C
468 ,',' -- D
469 ,',' -- E
470 ,new.tbl_chq -- F
471 ,new.tbl_cheq -- G
472 , new.id_rec -- H
473 , 'Default' -- I
474
475 ); */
476
477 --* Cash
478 sql := format( '
479 select
480 case when b.id is null then %L else %L end -- A1
481 from %s a -- A
482 left join %s b on b.pid = a.id and b.chqkind = %L --A1
483 where
484 a.id = %s -- B
485 limit 1
486 '
487 ,'', 'X' -- A1
488 ,new.tbl -- A
489 ,new.tbl_chq , 'Default' -- A1
490 ,new.id_rec -- B
491
492 );
493 execute sql into new.pay_method_val_cash;
494
495 --* Cheque
496 sql := format( '
497 select
498 case when b.id is null then %L else %L end -- A1
499 from %s a -- A
500 left join %s b on b.pid = a.id and b.chqkind = %L --A1
501 where
502 a.id = %s -- B
503 limit 1
504 '
505 ,'', 'X' -- A1
506 ,new.tbl -- A
507 ,new.tbl_chq , 'Cheque' -- A1
508 ,new.id_rec -- B
509
510 );
511 -- raise notice '501 sql=%', sql;
512 execute sql into new.pay_method_val_check;
513
514 --* Transfer
515 sql := format( '
516 select
517 case when b.id is null then %L else %L end -- A1
518 from %s a -- A
519 left join %s b on b.pid = a.id and b.chqkind = %L --A1
520 where
521 a.id = %s -- B
522 limit 1
523 '
524 ,'', 'X' -- A1
525 ,new.tbl -- A
526 ,new.tbl_chq , 'Transfer' -- A1
527 ,new.id_rec -- B
528
529 );
530 --raise notice '501 sql=%', sql;
531 execute sql into new.pay_method_val_Transfer;
532
533
534 --* credit
535 sql := format( '
536 select
537 case when b.id is null then %L else %L end -- A1
538 from %s a -- A
539 left join %s b on b.pid = a.id and b.chqkind = %L --A1
540 where
541 a.id = %s -- B
542 limit 1
543 '
544 ,'', 'X' -- A1
545 ,new.tbl -- A
546 ,new.tbl_chq , 'CreditCard' -- A1
547 ,new.id_rec -- B
548
549 );
550 --raise notice '501 sql=%', sql;
551 execute sql into new.pay_method_val_creditcard;
552
553 --* Other
554 sql := format( '
555 select
556 case when b.id is null then %L else %L end -- A1
557 from %s a -- A
558 left join %s b on b.pid = a.id and b.chqkind not in (%L,%L,%L,%L) --A1
559 where
560 a.id = %s -- B
561 limit 1
562 '
563 ,'', 'X' -- A1
564 ,new.tbl -- A
565 ,new.tbl_chq , 'Default','Cheque' ,'CreditCard','Transfer' -- A1
566 ,new.id_rec -- B
567
568 );
569 --raise notice '501 sql=%', sql;
570 execute sql into new.pay_method_val_remark;
571
572 --* Cheq - Remark
573 sql := format (
574 'select
575 array_to_string ( array_agg( b.ChqKindCustom order by a.orderno ), %L ) chqkindcustom -- A-1
576 ,array_to_string( array_agg( a.chqno order by a.orderno ), %L ) chqno -- A
577 ,array_to_string( array_agg( to_date( a.chqdate::timestamp_wtz,yt:=1 ) order by a.orderno ), %L) chqdate -- B
578 ,array_to_string( array_agg( to_char(a.payamt, %L )::text order by a.orderno ), %L ) chqamt --C
579 ,array_to_string( array_agg( b.bankname::text order by a.orderno ), %L ) bankname -- D
580 ,array_to_string( array_agg( b.bankbrname::text order by a.orderno ), %L ) banbrkname -- E
581 ,array_to_string( array_agg( b.payeename::text order by a.orderno ), %L ) payeename -- E+1
582
583 from
584 %s c -- "dbFinance".tbprecvw4 -- F-1
585 left join %s a on a.pid = c.id -- "dbFinance".tbprecchqvw4 -- F
586 left join %s b on b.id = a.chq -- "dbFinance".tbcheqvw4 -- G
587 where
588 c.id = %s -- H
589 group by a.pid
590
591 '
592 ,',' -- A-1
593 ,',' -- A
594 ,',' -- B
595 ,'999,999,999D99',',' -- C
596 ,',' -- D
597 ,',' -- E
598 ,',' -- E+1
599 ,new.tbl -- F-1
600 ,new.tbl_chq -- F
601 ,new.tbl_cheq -- G
602 , new.id_rec -- H
603
604 );
605 /*
606 sql := format ( '
607 select
608 case when a.chqkindcustom is null then %L else a.chqkindcustom end -- A
609 ,case when a.chqno is null then %L else a.chqno end -- B
610 ,case when a.chqdate is null then %L else a.chqdate end -- C
611 ,case when a.chqamt is null then %L else a.chqamt end -- D
612 ,case when a.bankname is null then %L else a.bankname end -- E
613 ,case when a.banbrkname is null then %L else a.banbrkname end -- F
614 ,case when a.payeename is null then %L else a.payeename end -- G
615 from (%s) a -- H
616 '
617 , '' -- A
618 , '' -- B
619 , '' -- C
620 , '' -- D
621 , '' -- E
622 , '' -- F
623 , '' -- G
624 ,sql
625
626 );
627 raise notice '501 sql=%', sql;
628 */
629
630 -- raise notice '501 sql=%', sql;
631
632 execute sql into
633 new.pay_method_val_remark
634 ,new.pay_no_val
635 ,new.pay_date_val
636 ,new.pay_amt_val
637 ,new.pay_bank_val
638 ,new.pay_bankbr_val
639 ,new.pay_payee_val
640 ;
641
642 end if;
643
644 return new;
645end $$ language plpgsql;
646
647
648/**********************************
649 Function : Report Data
650 21-10-2014, Wut.
651
652 select * from "dbBase".tbaccconf
653
654***********************************/
655drop function if exists "dbFinance".rpPQuoForm ( in prm "dbFinance".rpPQuoForm );
656create or replace function "dbFinance".rpPQuoForm( in prm "dbFinance".rpPQuoForm
657 ,out sql text, out sql_createtemp text ) returns record as $$
658
659declare
660
661 sql_item text =null;
662 sql_total_gross text = null;
663 sql_dis_amt text = null;
664 sql_total_after_discount text = null;
665 sql_v_amt text = null;
666 sql_total_after_vat text = null;
667
668 sql_whd_amt text = null;
669 sql_empty_item text = null;
670
671
672 line_limit smallint ; c smallint;
673begin
674 select val::smallint from "dbBase".tbaccconf where title = 'dbFinance_quo_form_max_line' into line_limit;
675 --* gross total
676 if true then
677 -- line_limit := line_limit - 1;
678 sql_total_gross := format(
679 '
680 select
681 null::text item
682 ,null::text sku
683 ,%L -- A
684 ,%L bfQty -- C
685 ,null::text qty
686 ,null::text unitprice
687 ,%L amt
688 ,2::int ord
689 ,null::int[] path
690
691 '
692 , format('%s %s' ,'', '' ) -- A
693 ,format('%s', 'รวม/Total' ) -- C
694 ,to_char ( prm.total_gross,'999,999,999D99' ) -- B
695
696 );
697 end if;
698
699 --* discount
700 --if prm.dis_amt is not null then
701 if true then
702 -- line_limit := line_limit - 1;
703 sql_dis_amt := format(
704 '
705 select
706 null::text item
707 ,null::text sku
708 ,%L -- A
709 ,%L bfQty -- C
710 ,null::text
711 ,null::text
712 ,%L -- B a.dis_amt
713 ,3::int ord
714 ,null::int[] path
715
716 '
717 , format('%s %s' ,'', '' ) -- A
718 , format('%s %s %s', 'ส่วนลด/Discount', case when prm.dis_amt=0 then null else prm.dis_rate::float end , case when prm.dis_amt=0 then null else '%' end ) -- C
719 , case when prm.dis_amt=0 then null else to_char ( prm.dis_amt,'999,999,999D99' ) end -- B
720
721 );
722 end if;
723
724 --* total after discount
725 sql_total_after_discount := format(
726 '
727 select
728 null::text item
729 ,null::text sku
730 ,%L -- A
731
732 ,%L bfQty -- C
733 ,null::text
734 ,null ::text
735 ,%L -- B
736 ,4::int ord
737 ,null::int[] path
738
739 '
740 , format('%s %s', '','' ) -- A
741 , format('%s', 'รวม หลังหัà¸à¸ªà¹ˆà¸§à¸™à¸¥à¸”/Total After Discount' ) -- C
742 , to_char ( prm.total_after_discount,'999,999,999D99' ) -- -- B
743
744 );
745
746 --* VAT ---
747 if prm.v_amt is not null then
748 -- line_limit := line_limit - 1;
749 sql_v_amt := format(
750 '
751 select
752 null::text item
753 ,null::text sku
754 ,%L -- A
755
756 ,%L bfQty -- C
757 ,null::text
758 ,null ::text
759 ,%L -- B
760 ,5::int ord
761 ,null::int[] path
762 '
763 , format('%s %s','', '' ) -- A
764 --, format('%s %s %L', 'ภาษีมูลค่าเพิ่ม/VAT', case when prm.v_amt=0 then '' else prm.v_rate::text end , case when prm.v_amt=0 then '' else '%' end ) -- C
765 --, to_char ( prm.v_amt,'999,999,999D99' ) -- B
766
767 , format('%s %s %s', 'ภาษีมูลค่าเพิ่ม/VAT', case when prm.v_tax=0 then null else prm.v_rate::float end , case when prm.v_rate is null then null else '%' end ) -- C
768 , case when prm.v_tax=0 then null else to_char ( prm.v_tax,'999,999,999D99' ) end -- B
769
770 );
771
772 end if;
773
774 --* total after VAT
775 sql_total_after_vat := format(
776 '
777 select
778 null::text item
779 ,null::text sku
780 ,%L -- A
781 ,%L bfQty -- C
782 ,null::text
783 ,null::text
784 ,%L -- B
785 ,6::int ord
786 ,null::int[] path
787 '
788 , format('%s %s', '','' ) -- A
789 , format('%s', 'รวม บวà¸à¸ าษีมูลค่าเพิ่ม/Total After VAT' ) -- C
790 , to_char ( prm.total_after_VAT,'999,999,999D99' ) -- -- B
791
792 );
793
794 --* witholding tax
795 --if prm.whd_amt is not null then
796 if true then
797 -- line_limit := line_limit - 1;
798 sql_whd_amt := format(
799 '
800 select
801 null::text item
802 ,null::text sku
803 ,%L -- A
804 ,%L bfQty -- C
805 ,null::text
806 ,null::text
807 ,%L -- B
808 ,7::int ord
809 ,null::int[] path
810 '
811 , format('%s %s', '','' ) -- A
812 --, format('%s %s %L', 'หัภภาษีหัภณ ที่จ่าย/Witholding Tax', case when prm.whd_amt=0 then '' else prm.whd_rate::text end , case when prm.whd_amt=0 then '' else '%' end ) -- C
813 --, to_char ( prm.whd_amt,'999,999,999D99' ) -- B
814 , format('%s %s %s', 'หัภภาษีหัภณ ที่จ่าย/Witholding Tax', case when prm.whd_amt=0 then null else prm.whd_rate::float end , case when prm.whd_amt=0 then null else '%' end ) -- C
815 , case when prm.whd_amt=0 then null else to_char ( prm.whd_amt,'999,999,999D99' ) end -- B
816
817
818 );
819 end if;
820
821 --* item
822 sql_item := format(
823 '
824 select
825 "dbFinance".itmnum( a.orderno , a.prod ) item
826 ,"dbFinance".itmnum( case when a2.no=a2.title then null else a2.no end, a.prod) sku
827 ,"dbFinance".itmnum( a.ProdTitle , a.prod, offs:=a.dis ) prodtitle
828 ,"dbFinance".itmnum( null::text , a.prod ) bfQty
829 ,"dbFinance".itmnum( a.qty , a.prod ) qty
830 ,"dbFinance".itmnum( a.v_unitprice , a.prod ) unitprice
831 ,"dbFinance".itmnum( a.v_amt, a.prod ) amt
832 ,1::int ord
833 ,a1.path
834 from
835 %s a -- A1
836 right join "dbSys".grpGetData( %s, %s, %L, path_only:=true, uid_child:=true ) a1 on a1.id = a.id -- A2
837 left join "dbInventory".tbinven a2 on a2.id = a.prod
838 where
839 a.pid = %s -- A
840 limit %s -- B
841 '
842 , prm.tbl_item -- A1
843 , prm.uid, prm.acc, '"dbFinance".tbPReqItem' -- A2
844 , prm.id_rec -- A
845 , line_limit -- B
846
847
848 );
849
850 --* empty item
851 --raise notice '442 prm.SumItem % , line_limt %', prm.SumItem, line_limit;
852 c:= prm.SumItem ;
853 while c< line_limit loop
854
855 sql_empty_item := concat_ws(' union all ', sql_empty_item ,
856 format(
857 '
858 select
859 null::text item
860 ,null::text sku
861 ,null::text prodtitle -- A
862 ,null::text bfQty -- C
863 ,null::text qty
864 ,null::text unitprice
865 ,null::text amt -- B
866 ,1::int ord
867 ,null::int[] path
868
869 '
870 )
871 );
872 c:=c+1;
873 end loop;
874 --raise notice '460 sql_empty_item =%', sql_empty_item;
875 --* main
876 sql := format(
877 '
878 select a.* from (%s) a -- A
879 %s -- A1
880 %s -- F
881 %s -- C
882 %s -- G
883 %s -- E
884 %s -- H
885 %s -- D
886
887 '
888 ,sql_item -- A
889
890 ,case when sql_empty_item is not null then format( 'union all select a.* from (%s) a',sql_empty_item ) else '' end -- A1
891 ,case when sql_total_gross is not null then format( 'union all select a.* from (%s) a',sql_total_gross ) else '' end -- F
892 ,case when sql_dis_amt is not null then format( 'union all select a.* from (%s) a',sql_dis_amt ) else '' end -- C
893 ,case when sql_total_after_discount is not null then format( 'union all select a.* from (%s) a',sql_total_after_discount ) else '' end -- G
894 ,case when sql_v_amt is not null then format( 'union all select a.* from (%s) a',sql_v_amt ) else '' end -- E
895
896 ,case when sql_total_after_vat is not null then format( 'union all select a.* from (%s) a',sql_total_after_vat ) else '' end -- H
897
898 ,case when sql_whd_amt is not null then format( 'union all select a.* from (%s) a',sql_whd_amt ) else '' end -- D
899 );
900
901 --raise notice '304, sql=%', sql;
902 --*
903 sql_createtemp:= format('select a.* from (%s) a where false',sql);
904 sql := format('%s order by ord, path ',sql);
905 -- return sql ;
906end $$ language plpgsql;
907
908/****************************
909 Trigger : Report Data - Before Insert
910 26-10-2014
911*****************************/
912create or replace function "dbFinance".reportdata_beforeinsert() returns trigger as $$
913declare
914 paramid int = tg_argv[0]; --*** id of parameter record ***
915 rec record;
916begin
917 --* get variables , get the whole record
918 -- select * from "dbFinance".rpPQuoForm where id = paramid into rec;
919
920 --* update variables
921 /*
922 update "dbFinance".rpPQuoForm
923 set var_item = rec.var_item
924 ,var_balance = rec.var_balance
925 where id = paramid; */
926
927 return new;
928end $$ language plpgsql;
929
930/************************************************
931 Registration
932**************************************************/
933select "dbSysRPT".rptRegistration(
934 '"dbFinance".rpPQuoForm'
935 ,_ReportName :=
936'ใบเสนà¸à¸£à¸²à¸„า
937Quotation'
938 ,tblTrigger := '"dbFinance".rpPQuoForm_Validate()'
939 ,tgBeforeInsertWork := '"dbFinance".reportdata_beforeinsert()'
940 ,_InitParam := 'Default'
941 ,ValidateInsUpd := false -- only validate on execute report, not inserting parementer
942 );