1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
| <?xml version="1.0"?>
| <!DOCTYPE hibernate-mapping PUBLIC
| "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
| "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
|
| <hibernate-mapping>
| <sql-query name="IoOpeningClosingQuery">
| <![CDATA[
| select
|
| bbpr_id,
| bbpr_id iocd_bbpr_id,
| bbpr_name,
| bbpr_brief_name,
| bbpr_style,
| bbpr_unit,
| bbpr_second_unit,
| bbpr_second_size,
| bbpr_remark,
| iocd_opening_size,
| iocd_in_size,
| iocd_out_size,
| (
| ifnull(iocd_opening_size,0)
| +ifnull(iocd_in_size,0)
| -ifnull(iocd_out_size,0)
| ) iocd_closing_size,
| iocd_opening_reject_size,
| iocd_in_reject_size,
| iocd_out_reject_size,
| (
| ifnull(iocd_opening_reject_size,0)
| +ifnull(iocd_in_reject_size,0)
| -ifnull(iocd_out_reject_size,0)
| )
| iocd_closing_reject_size
| from
| (
|
| SELECT
| bbpr_id,
| bbpr_id iocd_bbpr_id,
| bbpr_name,
| bbpr_brief_name,
| bbpr_style,
| bbpr_unit,
| bbpr_second_unit,
| bbpr_second_size,
| bbpr_remark,
| ifnull(iocd_closing_size,0) iocd_opening_size,
| (
| select sum(iodd_size) from io_deliver_detail
| ) iocd_in_size,
| (
| 0
| ) iocd_out_size,
| 0.0 iocd_closing_size,
| ifnull(iocd_closing_reject_size,0) iocd_opening_reject_size,
| (
| 0
|
| ) iocd_in_reject_size,
| (
| 0
| ) iocd_out_reject_size,
| 0.0 iocd_closing_reject_size
| FROM
| bb_product a
| LEFT JOIN bb_branch e ON bbbr_id = bbpr_bbbr_id
| left join bb_product_invertory f on bbiv_bbpr_id=a.bbpr_id
| left join cg_stuff_entry_detail g on cgsd_bbpr_id=bbpr_id
| left join cg_stuff_entry se on cgse_id=cgsd_cgse_id
| left join io_deliver_detail h on iodd_bbpr_id=bbpr_id
| left join io_deliver d on iode_id=iodd_iode_id
| left join io_opening_closing_detail i on iocd_bbpr_id=bbpr_id
| left join io_opening_closing j on iocd_iooc_id=iooc_id
| WHERE
| 1 = 1
| <<and (bbiv_factory_id=:factoryId or bbiv_factory_id is null)>>
| <<and (se.factory_id=:factoryId or se.factory_id is null)>>
| <<and (d.factory_id=:factoryId or d.factory_id is null)>>
| <<and bbpr_bbbr_id in (:bbprBbbrId)>>
| <<and (bbpr_name like concat("%", :bbprName, "%") or bbpr_brief_name like concat("%", :bbprName, "%") )>>
| <<and bbpr_style like concat("%", :bbprStyle, "%") >>
| <<and bbpr_unit = (:bbprUnit)>>
| <<and bbpr_grammage = (:bbprGrammage)>>
| <<and iooc_year_month is null or iooc_year_month=:yearMonthPrev>>
| group by bbpr_id
| ) a2
| where 1=1
| and (
| (
| ifnull(a2.iocd_opening_size,0)
| +ifnull(a2.iocd_in_size,0)
| -ifnull(a2.iocd_out_size,0)
| ) <> 0
| or
| (
| ifnull(a2.iocd_opening_reject_size,0)
| +ifnull(a2.iocd_in_reject_size,0)
| -ifnull(a2.iocd_out_reject_size,0)
| ) <> 0)
| order by bbpr_name,bbpr_style
|
| ]]>
| </sql-query>
| </hibernate-mapping>
|
|
