· 8 years ago · Feb 20, 2018, 06:24 PM
1$plcQueryDB2 = "
2 with dealers as (
3 select xcstno, c.xslsno, trim(xbllncc) as dealername, (Select slsupr from salmpbpf where slsnob = xslsno fetch first 1 rows only) as sup from custmstfc c
4 ),
5
6 FRAMES AS (
7 SELECT sku.cat, f10.group as style, f10.grpnam, f20.framfmt, sku.frame, sku.cover1, sku.color1, cvp.coldss, sku.cover2, sku.color2,
8 (select coldss from cvp010 where coverr = sku.cover2 and colerr = sku.color2) as colorname2
9 from plcrptskus sku
10 left join fap020 f20 on f20.frame = sku.frame
11 left join fap010 f10 on f10.group = f20.group
12 left join cvp010 cvp on cvp.coverr = sku.cover1 and cvp.colerr = sku.color1
13 where cover1 >= 0 and color1 >= 0
14 order by sku.cat, f10.group, sku.frame, sku.cover1, sku.color1
15 ),
16 DATA AS (
17 select
18 xslsno as rep,
19 xcstno as dealer,
20 f.style as Style,
21 trim(f.grpnam) as StyleName,
22 trim(f.framfmt) as Frame,
23 f.cover1 as Cover,
24 f.color1 as Color,
25 trim(f.coldss) as Colorname,
26 sum(skunoc) as placements,
27 a.extd1d as shipdate
28 from gporpcfl a
29 join datutl on invdtc = mmddyyyy
30 join dealers on xcstno = a.cstnoc
31 join frames f on f.frame = a.framec and f.cover1 = a.covr1c and f.color1 = a.colr1c
32 where fstatc in ('S','M')
33 group by xcstno, xslsno, sup, f.style, f.grpnam, f.framfmt, f.cover1, f.color1, f.coldss,a.extd1d
34 order by xcstno, xslsno, sup, f.style
35 ),
36 data2 as (
37 select
38 xslsno as rep,
39 xcstno as dealer,
40 f.style as Style,
41 trim(f.grpnam) as StyleName,
42 trim(f.framfmt) as Frame,
43 f.cover1 as Cover,
44 f.color1 as Color,
45 trim(f.coldss) as Colorname,
46 sum(skunoc) as placements,
47 a.extd1d as shipdate
48 from gporpcfl a
49 join datutl on invdtc = mmddyyyy
50 join dealers on xcstno = a.cstnoc
51 join FRAMES f on f.frame = a.framec
52 where fstatc in ('S','M') AND iso > CURRENT_DATE - 120 DAYS AND iso < CURRENT_DATE
53 group by xcstno, xslsno, sup, f.style, f.grpnam, f.framfmt, f.cover1, f.color1, f.coldss,a.extd1d
54 order by xcstno, xslsno, sup, f.style
55 ),
56 TOTAL AS (
57 SELECT * FROM DATA
58 UNION ALL
59 SELECT * FROM DATA2
60 )
61 SELECT * FROM total
62 where left(shipdate, 4) >= '2016'
63 limit 100
64 ";
65
66
67
68 $prep = odbc_prepare($DB2Conn, $plcQueryDB2);
69 $exec = odbc_execute($prep);
70 $result = odbc_exec($DB2Conn, $plcQueryDB2);
71 }
72 catch(Exception $e) {
73 echo $e->getMessage();
74 }
75
76
77
78 //Writing column headers
79 $columns = array('rep', 'Dealer', 'Style', 'StyleName', 'Frame', 'Cover', 'Color', 'Colorname', 'Placements', 'shipdate');
80 $fs = fopen('DB2_Query.csv', 'w');
81 fputcsv($fs, $columns);
82 fclose($fs);
83
84 // Writing data rows
85 while($arr = odbc_fetch_array($result)) {
86 $fs = fopen('DB2_Query.csv', 'a');
87 fputcsv($fs, $arr);
88 fclose($fs);
89 }
90
91 odbc_close($DB2Conn);
92 $DB2Conn = null;
93
94
95
96 $sql = "CREATE TABLE IF NOT EXISTS db2Temp (
97 rep INTEGER,
98 dealer INTEGER,
99 style INTEGER,
100 stylename INTEGER,
101 frame INTEGER,
102 cover INTEGER,
103 color INTEGER,
104 colorName INTEGER,
105 placements INTEGER,
106 shipdate DATE
107 )";
108
109 if (mysqli_query($mysqlConn, $sql)){
110 echo "db2temp table created";
111 }else{
112 echo "ERROR:" . mysqli_error($mysqlConn);
113 }
114
115
116 $sql2 = "DELETE FROM db2Temp";
117
118 if (mysqli_query($mysqlConn, $sql2)){
119 echo "db2temp deletion successful";
120 }else{
121 echo "ERROR:" . mysqli_error($mysqlConn);
122 }
123
124
125
126
127 $sql3 = "LOAD DATA LOCAL INFILE 'DB2_Query.csv'
128 INTO TABLE db2temp
129 FIELDS TERMINATED BY ','
130 OPTIONALLY ENCLOSED BY '"'
131 LINES TERMINATED BY 'n'
132 IGNORE 1 LINES";
133
134 if (mysqli_query($mysqlConn, $sql3)){
135 echo "data load successful";
136 }else{
137 echo "ERROR:" . mysqli_error($mysqlConn);
138 }
139
140 $insertPlacement = "INSERT INTO placements (sku_id, group_id, dealer_ID, start_Date,
141 expire_Date, placements)
142 SELECT sku.ID, sku.sku_Group_ID, db2.dealer, db2.shipDate,
143 DATE_ADD(
144 DATE_FORMAT(CONVERT(db2.shipDate, CHAR(20)), '%Y-%m-%d'),
145 INTERVAL 127 DAY) as expire_Date,
146 db2.placements
147 FROM skus as sku
148 INNER JOIN db2temp db2
149 ON db2.frame = sku.frame
150 AND db2.cover = sku.cover1
151 AND db2.color = sku.color1
152 WHERE NOT EXISTS
153 (SELECT 1 FROM placements p
154 WHERE sku.ID = p.sku_ID)";
155
156 if (mysqli_query($mysqlConn, $insertPlacement)){
157 echo "Successful addition to table";
158 }else{
159 echo "ERROR:" . mysqli_error($mysqlConn);
160 }
161
162
163 if (mysqli_close($mysqlConn)){
164 echo "MySQL Closed";
165 }