· 10 years ago · May 05, 2016, 12:33 PM
1<?php
2class Neklo_RetailPro_Model_Cron {
3
4 var $dbName = '//96.247.80.18:1653/MYDB';
5 var $username = 'admin';
6 var $password = 'password123';
7
8 public function getRetailProInventory() {
9
10 $resource = Mage::getSingleton('core/resource');
11 $writeConnection = $resource->getConnection('core_write');
12 $readConnection = $resource->getConnection('core_read');
13
14 $params = array(
15 'username' => $this->username,
16 'password' => $this->password,
17 'dbname' => $this->dbName
18 );
19
20 $date = mktime(0,0,0,date('m'),date('d')-1,date('Y')) ;
21 $yesterdayDate = date("M-Y-d",$date);
22 $database = Zend_Db::factory('oracle', $params);
23 $result = $database->query("SELECT
24 invn_sbs.ITEM_NO,
25 invn_sbs.item_sid,
26 invn_sbs.style_sid,
27 ia.i_alu alu,
28 invn_sbs.CREATED_DATE,
29 invn_sbs.fst_rcvd_date,
30 invn_sbs.lst_rcvd_date,
31 invn_sbs.LST_SOLD_DATE,
32 invn_sbs.DESCRIPTION1,
33 invn_sbs.DESCRIPTION2,
34 invn_sbs.DESCRIPTION3,
35 invn_sbs.DESCRIPTION4,
36 invn_sbs.vend_code,
37 invn_sbs.attr,
38 invn_sbs.siz,
39 invn_sbs_price.price,
40 invn_sbs.cost,
41 invn_sbs.prod_cost,
42 invn_sbs.DCS_CODE,
43 dcs.d_name,
44 dcs.C_NAME,
45 dcs.S_NAME,
46 invn_sbs_qty.qty,
47 invn_sbs_qty.SOLD_QTY,
48 invn_sbs_qty.RCVD_QTY,
49 invn_sbs_qty.store_no,
50 store.store_name
51 from invn_sbs
52 inner join invn_sbs_qty on invn_sbs.ITEM_SID = invn_sbs_qty.ITEM_SID
53 and nvl(invn_sbs_qty.qty,0) > 0
54 inner join dcs on dcs.dcs_code = invn_sbs.DCS_CODE
55 inner join store on invn_sbs_qty.store_no = store.store_no
56 inner join
57 (SELECT inventory_alu_all.item_sid i_sid, min(alu) i_alu from inventory_alu_all
58 group by inventory_alu_all.item_sid
59 ) ia on ia.i_sid = invn_sbs.item_sid
60 inner join invn_sbs_price on invn_sbs_price.item_sid = invn_sbs.item_sid
61 and invn_sbs_price.price_lvl = 1 and invn_sbs_price.season_id = 0
62 where store.store_no not in (0,250,1,15,14)
63
64 ")->fetchAll();
65
66 // and to_char(LST_SOLD_DATE ,'Mon-YYYY-dd') = '".$yesterdayDate."'
67
68 $maxRunId = 1;
69 $maxRunIdSelection = $readConnection->query("SELECT max(run_id) as max_run_id from neklo_retailpro_inventory_history")->fetchAll();
70 if(isset($maxRunIdSelection[0]) && $maxRunIdSelection[0]['max_run_id']) {
71 $maxRunId = $maxRunIdSelection[0]['max_run_id'] + 1;
72 }
73
74 //$sql = "TRUNCATE TABLE neklo_retailpro_inventory";
75 //$writeConnection->query($sql);
76 $sql = '';
77 foreach($result as $k=>$r) {
78 foreach ($r as $key => $value) {
79 $r[$key] = addslashes($value);
80 }
81
82 $sql = "INSERT INTO neklo_retailpro_inventory_history VALUES (
83 id,
84 {$maxRunId},
85 ".(1*$r["ITEM_NO"]).",
86 '".$r["ITEM_SID"]."',
87 '".$r["STYLE_SID"]."',
88 ".(1*$r['ALU']).",
89 '".date('Y-m-d h:i:s', strtotime($r["CREATED_DATE"]))."',
90 '".date('Y-m-d h:i:s', strtotime($r["FST_RCVD_DATE"]))."',
91 '".date('Y-m-d h:i:s', strtotime($r["LST_RCVD_DATE"]))."',
92 '".date('Y-m-d h:i:s', strtotime($r["LST_SOLD_DATE"]))."',
93 '".$r["DESCRIPTION1"]."',
94 '".$r["DESCRIPTION2"]."',
95 '".$r["DESCRIPTION3"]."',
96 '".$r["DESCRIPTION4"]."',
97 '".$r["VEND_CODE"]."',
98 '".$r["ATTR"]."',
99 '".$r["SIZ"]."',
100 ".(1*$r['PRICE']).",
101 ".(1*$r['COST']).",
102 ".(1*$r['PROD_COST']).",
103 '".$r["DCS_CODE"]."',
104 '".$r["D_NAME"]."',
105 '".$r["C_NAME"]."',
106 '".$r["S_NAME"]."',
107 ".(1*$r['QTY']).",
108 ".(1*$r['SOLD_QTY']).",
109 ".(1*$r['RCVD_QTY']).",
110 ".(1*$r['STORE_NO']).",
111 '".$r["STORE_NAME"]."',
112 NOW()
113 );
114
115 INSERT INTO neklo_retailpro_inventory_tmp VALUES (
116 id,
117 ".(1*$r["ITEM_NO"]).",
118 '".$r["ITEM_SID"]."',
119 '".$r["STYLE_SID"]."',
120 ".(1*$r['ALU']).",
121 '".date('Y-m-d h:i:s', strtotime($r["CREATED_DATE"]))."',
122 '".date('Y-m-d h:i:s', strtotime($r["FST_RCVD_DATE"]))."',
123 '".date('Y-m-d h:i:s', strtotime($r["LST_RCVD_DATE"]))."',
124 '".date('Y-m-d h:i:s', strtotime($r["LST_SOLD_DATE"]))."',
125 '".$r["DESCRIPTION1"]."',
126 '".$r["DESCRIPTION2"]."',
127 '".$r["DESCRIPTION3"]."',
128 '".$r["DESCRIPTION4"]."',
129 '".$r["VEND_CODE"]."',
130 '".$r["ATTR"]."',
131 '".$r["SIZ"]."',
132 ".(1*$r['PRICE']).",
133 ".(1*$r['COST']).",
134 ".(1*$r['PROD_COST']).",
135 '".$r["DCS_CODE"]."',
136 '".$r["D_NAME"]."',
137 '".$r["C_NAME"]."',
138 '".$r["S_NAME"]."',
139 ".(1*$r['QTY']).",
140 ".(1*$r['SOLD_QTY']).",
141 ".(1*$r['RCVD_QTY']).",
142 ".(1*$r['STORE_NO']).",
143 '".$r["STORE_NAME"]."',
144 NOW()
145 );
146 ";
147
148 try {
149 $this->createTemporaryTable();
150 $writeConnection->query($sql);
151 $this->renameTempTable();
152 } catch (Exception $e) {
153 Mage::log($sql." \n ".$e->getMessage(), null, 'inventory-cron.log');
154 }
155 }
156 }
157
158
159 public function createTemporaryTable(){
160
161 $installer = new Mage_Core_Model_Resource_Setup;
162
163 $installer->run("
164 DROP TABLE IF EXISTS `". $installer->getTable('neklo_retailpro/inventory').'_tmp'."`;
165 CREATE TABLE IF NOT EXISTS `". $installer->getTable('neklo_retailpro/inventory') .'_tmp'."` (
166 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
167 `item_no` int(10) unsigned NOT NULL,
168 `item_sid` varchar(255) NOT NULL DEFAULT '',
169 `style_sid` varchar(255) NOT NULL DEFAULT '',
170 `alu` int(10) unsigned NOT NULL,
171 `created_date` TIMESTAMP NOT NULL,
172 `fst_rcvd_date` TIMESTAMP NOT NULL,
173 `lst_rcvd_date` TIMESTAMP NOT NULL,
174 `lst_sold_date` TIMESTAMP NOT NULL,
175 `description1` varchar(512) NOT NULL DEFAULT '',
176 `description2` varchar(512) NOT NULL DEFAULT '',
177 `description3` varchar(512) NOT NULL DEFAULT '',
178 `description4` varchar(512) NOT NULL DEFAULT '',
179 `vend_code` varchar(50) NOT NULL DEFAULT '',
180 `attr` varchar(50) NOT NULL DEFAULT '',
181 `size` varchar(50) NOT NULL DEFAULT '',
182 `price` FLOAT(12, 4) UNSIGNED DEFAULT 0,
183 `cost` FLOAT(12, 4) UNSIGNED DEFAULT 0,
184 `prod_cost` FLOAT(12, 4) UNSIGNED DEFAULT 0,
185 `dsc_code` varchar(50) NOT NULL DEFAULT '',
186 `d_name` varchar(50) NOT NULL DEFAULT '',
187 `c_name` varchar(50) NOT NULL DEFAULT '',
188 `s_name` varchar(50) NOT NULL DEFAULT '',
189 `qty` FLOAT(12, 4) UNSIGNED DEFAULT 0,
190 `sold_qty` FLOAT(12, 4) UNSIGNED DEFAULT 0,
191 `rcvd_qty` FLOAT(12, 4) UNSIGNED DEFAULT 0,
192 `store_no` int(10) unsigned NOT NULL,
193 `store_name` varchar(512) NOT NULL DEFAULT '',
194 `created_at` TIMESTAMP NOT NULL,
195 PRIMARY KEY (`id`)
196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Neklo Retail Pro Inventory Temp';
197
198 ");
199 }
200
201
202 public function renameTempTable(){
203 $installer = new Mage_Core_Model_Resource_Setup;
204 $installer->startSetup();
205
206 $installer->run("
207 DROP TABLE IF EXISTS `". $installer->getTable('neklo_retailpro/inventory') ."`;
208 RENAME TABLE `". $installer->getTable('neklo_retailpro/inventory') .'_tmp'."` TO `". $installer->getTable('neklo_retailpro/inventory') ."`;
209 ");
210
211 $installer->endSetup();
212 }
213}