Odyssey
2018012900_loanapp.php
1 <?php
2 /* Notes:
3  * 1. Anything complicated needs to be done with SQL.
4  * 2. Always have a check to know if the migration needs to occur (up or down).
5  * 3. Use up() and down(), not change(), because of the SQL
6  */
7 use Phinx\Migration\AbstractMigration;
8 
9 class LoanApp extends AbstractMigration {
10 
11  /*
12  * ADD loan app tables to database for monitor
13  */
14  public function up() {
15 
16  /**
17  * TABLE LIST
18  */
19  $hasTableLookupDetail = $this->hasTable("lnappanslookupdetail");
20  $hasTableLookupHeader = $this->hasTable("lnappanslookupheader");
21  $hasTableSchemaDetail = $this->hasTable("lnappschemadetail");
22  $hasTableSchemaMaster = $this->hasTable("lnappschemamaster");
23  $hasTableUser = $this->hasTable("lnappuser");
24  $hasTableQuestSelect = $this->hasTable("lnappuser_questselect");
25  $hasTableUserResponse = $this->hasTable("lnappuserresponse");
26  $hasTableConfig = $this->hasTable("lnappconfig");
27 
28  /**
29  * CREATE TABLE lnappanslookupdetail
30  * CREATE SEQUENCE lnappanswerlookupdetail_ansdetailid_seq
31  * CREATE INDEX lnappanswerlookupdetail_ansdetailid_idx01
32  */
33  if (!$hasTableLookupDetail) {
34  $sql = "CREATE TABLE lnappanslookupdetail (
35  ansdetailid integer NOT NULL,
36  ansid integer,
37  ansvalue character varying(50),
38  ansdisplay character varying(50)
39  );";
40  $this->query($sql);
41 
42  $sql = "CREATE SEQUENCE lnappanswerlookupdetail_ansdetailid_seq
43  START WITH 1
44  INCREMENT BY 1
45  NO MINVALUE
46  NO MAXVALUE
47  CACHE 1;";
48  $this->query($sql);
49 
50  $sql = "ALTER SEQUENCE lnappanswerlookupdetail_ansdetailid_seq OWNED BY lnappanslookupdetail.ansdetailid;";
51  $this->query($sql);
52 
53  $sql = "SET default_with_oids = false;";
54  $this->query($sql);
55 
56  $sql = "ALTER TABLE ONLY lnappanslookupdetail ALTER COLUMN ansdetailid SET DEFAULT nextval('lnappanswerlookupdetail_ansdetailid_seq'::regclass);";
57  $this->query($sql);
58 
59  $sql = "CREATE UNIQUE INDEX lnappanswerlookupdetail_ansdetailid_idx01 ON lnappanslookupdetail USING btree (ansdetailid);";
60  $this->query($sql);
61  }
62 
63  /**
64  * CREATE TABLE lnappanslookupheader_ansid_seq
65  * CREATE SEQUENCE lnappanswerlookupdetail_ansdetailid_seq
66  * CREATE INDEX lnappanslookupheader_ansid_idx01
67  */
68  if (!$hasTableLookupHeader) {
69  $sql = "CREATE TABLE lnappanslookupheader (
70  ansid integer NOT NULL,
71  anstitle character varying(50)
72  );";
73  $this->query($sql);
74 
75  $sql = "CREATE SEQUENCE lnappanslookupheader_ansid_seq
76  START WITH 1
77  INCREMENT BY 1
78  NO MINVALUE
79  NO MAXVALUE
80  CACHE 1;";
81  $this->query($sql);
82 
83  $sql = "ALTER SEQUENCE lnappanslookupheader_ansid_seq OWNED BY lnappanslookupheader.ansid;";
84  $this->query($sql);
85 
86  $sql = "SET default_with_oids = false;";
87  $this->query($sql);
88 
89  $sql = "ALTER TABLE ONLY lnappanslookupheader ALTER COLUMN ansid SET DEFAULT nextval('lnappanslookupheader_ansid_seq'::regclass);";
90  $this->query($sql);
91 
92  $sql = "CREATE UNIQUE INDEX lnappanslookupheader_ansid_idx01 ON lnappanslookupheader USING btree (ansid);";
93  $this->query($sql);
94  }
95 
96  /**
97  * CREATE TABLE lnappschemadetail
98  * CREATE SEQUENCE lnappschemadetail_detailid_seq
99  * CREATE INDEX lnappschemadetail_detailid_idx_01
100  */
101  if (!$hasTableSchemaDetail) {
102  $sql = "CREATE TABLE lnappschemadetail (
103  detailid integer NOT NULL,
104  loanid integer,
105  pageid integer,
106  groupid integer,
107  lineid integer,
108  fieldtype character varying(10),
109  fieldvalue character varying(2000),
110  fieldattr character varying(3000)
111  );";
112  $this->query($sql);
113 
114  $sql = "CREATE SEQUENCE lnappschemadetail_detailid_seq
115  START WITH 1
116  INCREMENT BY 1
117  NO MINVALUE
118  NO MAXVALUE
119  CACHE 1;";
120  $this->query($sql);
121 
122  $sql = "ALTER SEQUENCE lnappschemadetail_detailid_seq OWNED BY lnappschemadetail.detailid;";
123  $this->query($sql);
124 
125  $sql = "SET default_with_oids = false;";
126  $this->query($sql);
127 
128  $sql = "ALTER TABLE ONLY lnappschemadetail ALTER COLUMN detailid SET DEFAULT nextval('lnappschemadetail_detailid_seq'::regclass);";
129  $this->query($sql);
130 
131  $sql = "CREATE UNIQUE INDEX lnappschemadetail_detailid_idx_01 ON lnappschemadetail USING btree (detailid);";
132  $this->query($sql);
133  }
134 
135  /**
136  * CREATE TABLE lnappschemamaster
137  * CREATE SEQUENCE lnappschemamaster_loanid_seq
138  * CREATE INDEX lnappschemamaster_loanid_idx01
139  */
140  if (!$hasTableSchemaMaster) {
141  $sql = "CREATE TABLE lnappschemamaster (
142  loanid integer NOT NULL,
143  cu character varying(10),
144  loantitle character varying(50),
145  loandisclosure_fragment character varying(50),
146  lastmodified date
147  );";
148  $this->query($sql);
149 
150  $sql = "CREATE SEQUENCE lnappschemamaster_loanid_seq
151  START WITH 1
152  INCREMENT BY 1
153  NO MINVALUE
154  NO MAXVALUE
155  CACHE 1;";
156  $this->query($sql);
157 
158  $sql = "ALTER SEQUENCE lnappschemamaster_loanid_seq OWNED BY lnappschemamaster.loanid;";
159  $this->query($sql);
160 
161  $sql = "SET default_with_oids = false;";
162  $this->query($sql);
163 
164  $sql = "ALTER TABLE ONLY lnappschemamaster ALTER COLUMN loanid SET DEFAULT nextval('lnappschemamaster_loanid_seq'::regclass);";
165  $this->query($sql);
166 
167  $sql = "CREATE UNIQUE INDEX lnappschemamaster_loanid_idx01 ON lnappschemamaster USING btree (loanid);";
168  $this->query($sql);
169  }
170 
171  /**
172  * CREATE TABLE lnappuser
173  * CREATE SEQUENCE lnappuser_userid_seq
174  * CREATE INDEX lnappuser_uniquefields_idx
175  * CREATE INDEX lnappuser_userid_key
176  */
177  if (!$hasTableUser) {
178  $sql = "CREATE TABLE lnappuser (
179  userid integer NOT NULL,
180  email character varying(50),
181  pwd character varying(50),
182  allow_e_comm character(1),
183  cu character varying(10),
184  confidenceword character varying(30),
185  failedloginattempts integer,
186  challenge_quest_id integer,
187  userlogintype character(1),
188  user_name character varying(12)
189  );";
190  $this->query($sql);
191 
192  $sql = "CREATE SEQUENCE lnappuser_userid_seq
193  START WITH 1
194  INCREMENT BY 1
195  NO MINVALUE
196  NO MAXVALUE
197  CACHE 1;";
198  $this->query($sql);
199 
200  $sql = "ALTER SEQUENCE lnappuser_userid_seq OWNED BY lnappuser.userid;";
201  $this->query($sql);
202 
203  $sql = "SET default_with_oids = false;";
204  $this->query($sql);
205 
206  $sql = "ALTER TABLE ONLY lnappuser ALTER COLUMN userid SET DEFAULT nextval('lnappuser_userid_seq'::regclass);";
207  $this->query($sql);
208 
209  $sql = "CREATE UNIQUE INDEX lnappuser_uniquefields_idx ON lnappuser USING btree (cu, userlogintype, email, user_name);";
210  $this->query($sql);
211 
212  $sql = "CREATE UNIQUE INDEX lnappuser_userid_key ON lnappuser USING btree (userid);";
213  $this->query($sql);
214  }
215 
216  /**
217  * CREATE TABLE lnappuser_questselect
218  * CREATE INDEX lnappuser_questselect_userid_questid_idx01
219  */
220  if (!$hasTableQuestSelect) {
221  $sql = "CREATE TABLE lnappuser_questselect (
222  userid integer NOT NULL,
223  questid integer NOT NULL,
224  user_answer character varying(100)
225  );";
226  $this->query($sql);
227 
228  $sql = "CREATE UNIQUE INDEX lnappuser_questselect_userid_questid_idx01 ON lnappuser_questselect USING btree (userid, questid);";
229  $this->query($sql);
230  }
231 
232  /**
233  * CREATE TABLE lnappuserresponse
234  * CREATE SEQUENCE lnappuserresponse_respid_seq
235  * CREATE INDEX lnappuserresponse_respid_idx01
236  */
237  if (!$hasTableUserResponse) {
238  $sql = "CREATE TABLE lnappuserresponse (
239  respid integer NOT NULL,
240  userid integer,
241  loanid integer,
242  respstatus character(3),
243  respcomments character varying(200),
244  respcoreloanappid character varying(15),
245  respstarton timestamp without time zone,
246  respsubmiton timestamp without time zone,
247  respmodifiedon timestamp without time zone,
248  resplname character varying(55),
249  respfname character varying(25),
250  respmname character varying(25),
251  respssn character(11),
252  respdob character varying(10),
253  respmember character varying(15),
254  respphone character varying(15),
255  respapplication text,
256  respstatusdesc character varying(200),
257  respamt numeric(14,2),
258  resplastinquire timestamp without time zone
259  );";
260  $this->query($sql);
261 
262  $sql = "CREATE SEQUENCE lnappuserresponse_respid_seq
263  START WITH 1
264  INCREMENT BY 1
265  NO MINVALUE
266  NO MAXVALUE
267  CACHE 1;";
268  $this->query($sql);
269 
270  $sql = "ALTER SEQUENCE lnappuserresponse_respid_seq OWNED BY lnappuserresponse.respid;";
271  $this->query($sql);
272 
273  $sql = "SET default_with_oids = false;";
274  $this->query($sql);
275 
276  $sql = "ALTER TABLE ONLY lnappuserresponse ALTER COLUMN respid SET DEFAULT nextval('lnappuserresponse_respid_seq'::regclass);";
277  $this->query($sql);
278 
279  $sql = "CREATE UNIQUE INDEX lnappuserresponse_respid_idx01 ON lnappuserresponse USING btree (respid);";
280  $this->query($sql);
281  }
282 
283  /**
284  * CREATE TABLE lnappconfig
285  */
286  if (!$hasTableConfig) {
287  $sql = "CREATE TABLE lnappconfig (
288  cu character varying(12),
289  appconfig character varying
290  );";
291  $this->query($sql);
292  }
293  }
294 
295  /* To execute this you need to run the following command:
296  * php vendor/bin/phinx rollback (using correct path to phinx)
297  * Or, to roll back to a specific time:
298  * php vendor/bin/phinx rollback -t 20151123211207
299  */
300  public function down() {
301  /**
302  * DROP TABLE lnappanslookupdetail
303  * DROP SEQUENCE lnappanswerlookupdetail_ansdetailid_seq
304  * DROP INDEX lnappanswerlookupdetail_ansdetailid_idx01
305  */
306  $sql = "DROP TABLE IF EXISTS lnappanslookupdetail;";
307  $this->query($sql);
308  $sql = "DROP SEQUENCE IF EXISTS lnappanswerlookupdetail_ansdetailid_seq;";
309  $this->query($sql);
310  $sql = "DROP INDEX IF EXISTS lnappanswerlookupdetail_ansdetailid_idx01;";
311  $this->query($sql);
312 
313  /**
314  * DROP TABLE lnappanslookupheader
315  * DROP SEQUENCE lnappanslookupheader_ansid_seq
316  * DROP INDEX lnappanslookupheader_ansid_idx01
317  */
318  $sql = "DROP TABLE IF EXISTS lnappanslookupheader;";
319  $this->query($sql);
320  $sql = "DROP SEQUENCE IF EXISTS lnappanslookupheader_ansid_seq;";
321  $this->query($sql);
322  $sql = "DROP INDEX IF EXISTS lnappanslookupheader_ansid_idx01;";
323  $this->query($sql);
324 
325  /**
326  * DROP TABLE lnappschemadetail
327  * DROP SEQUENCE lnappschemadetail_detailid_seq
328  * DROP INDEX lnappschemadetail_detailid_idx_01
329  */
330  $sql = "DROP TABLE IF EXISTS lnappschemadetail;";
331  $this->query($sql);
332  $sql = "DROP SEQUENCE IF EXISTS lnappschemadetail_detailid_seq;";
333  $this->query($sql);
334  $sql = "DROP INDEX IF EXISTS lnappschemadetail_detailid_idx_01;";
335  $this->query($sql);
336 
337  /**
338  * DROP TABLE lnappschemamaster
339  * DROP SEQUENCE lnappschemamaster_loanid_seq
340  * DROP INDEX lnappschemamaster_loanid_idx01
341  */
342  $sql = "DROP TABLE IF EXISTS lnappschemamaster;";
343  $this->query($sql);
344  $sql = "DROP SEQUENCE IF EXISTS lnappschemamaster_loanid_seq;";
345  $this->query($sql);
346  $sql = "DROP INDEX IF EXISTS lnappschemamaster_loanid_idx01;";
347  $this->query($sql);
348 
349  /**
350  * DROP TABLE lnappuser
351  * DROP SEQUENCE lnappuser_userid_seq
352  * DROP INDEX lnappuser_uniquefields_idx
353  * DROP INDEX lnappuser_userid_key
354  */
355  $sql = "DROP TABLE IF EXISTS lnappuser;";
356  $this->query($sql);
357  $sql = "DROP SEQUENCE IF EXISTS lnappuser_userid_seq;";
358  $this->query($sql);
359  $sql = "DROP INDEX IF EXISTS lnappuser_uniquefields_idx;";
360  $this->query($sql);
361  $sql = "DROP INDEX IF EXISTS lnappuser_userid_key;";
362  $this->query($sql);
363 
364  /**
365  * DROP TABLE lnappuser_questselect
366  * DROP INDEX lnappuser_questselect_userid_questid_idx01
367  */
368  $sql = "DROP TABLE IF EXISTS lnappuser_questselect;";
369  $this->query($sql);
370  $sql = "DROP INDEX IF EXISTS lnappuser_questselect_userid_questid_idx01;";
371  $this->query($sql);
372 
373  /**
374  * DROP TABLE lnappuserresponse
375  * DROP SEQUENCE lnappuserresponse_respid_seq
376  * DROP INDEX lnappuserresponse_respid_idx01
377  */
378  $sql = "DROP TABLE IF EXISTS lnappuserresponse;";
379  $this->query($sql);
380  $sql = "DROP SEQUENCE IF EXISTS lnappuserresponse_respid_seq;";
381  $this->query($sql);
382  $sql = "DROP INDEX IF EXISTS lnappuserresponse_respid_idx01;";
383  $this->query($sql);
384 
385  /**
386  * DROP TABLE lnappconfig
387  * DROP SEQUENCE lnappuserresponse_respid_seq
388  * DROP INDEX lnappuserresponse_respid_idx01
389  */
390  $sql = "DROP TABLE IF EXISTS lnappconfig;";
391  $this->query($sql);
392  }
393 }
394 
395 ?>
Generated by   doxygen 1.8.15