API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 9th, 2017
323
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.69 KB | None | 0 0
raw download clone embed print report
  1. var Client = require('mariasql');
  2.  
  3. module.exports = {
  4.  
  5. home(req, res) {
  6. var c = new Client({
  7. host: '127.0.0.1',
  8. user: 'root',
  9. password: '',
  10. db: 'play60'
  11. });
  12.  
  13. var find = c.prepare('SELECT * FROM Posting');
  14.  
  15. c.query(find(), function(err, rows) {
  16. if (err)
  17. throw err;
  18. res.render('home', { uid:req.params.id, postings: rows });
  19.  
  20. });
  21.  
  22. c.end();
  23.  
  24.  
  25. },
  26. login(req, res) {
  27. res.render('login');
  28. },
  29. signup(req, res) {
  30. res.render('signup',{error: 'none'});
  31. },
  32. createPost(req, res) {
  33. res.render('create_post', { uid: req.params.id });
  34. },
  35. profile(req, res) {
  36. var c = new Client({
  37. host: '127.0.0.1',
  38. user: 'root',
  39. password: '',
  40. db: 'play60'
  41. });
  42. var groupBy = c.prepare('SELECT AVG(cnt) as avg FROM (SELECT COUNT(*) AS cnt FROM Posting GROUP BY (UID)) AS T');
  43.  
  44. var get = c.prepare("SELECT * FROM User WHERE UID=" + "'" + req.params.id + "'");
  45. var getCount = c.prepare("SELECT count(PID) as pidcount FROM Posting JOIN User USING (UID) WHERE UID =" + req.params.id);
  46. var getCountBySport = c.prepare("SELECT game,count(PID) as pidcount FROM (SELECT * FROM Posting WHERE UID = " + req.params.id + ") as T GROUP BY (game)");
  47.  
  48. //var getCountBySport =
  49. c.query(get(), function(err, rows) {
  50. if (err)
  51. throw err
  52. c.query(getCount(), function(err, rows1) {
  53. if (err)
  54. throw err
  55. c.query(getCountBySport(), function(err, rows2) {
  56. if (err)
  57. throw err
  58. c.query(groupBy(), function(err, rows3) {
  59. if (err)
  60. throw err
  61. res.render('profile', {user:rows[0], uid: req.params.id,count_pid:rows1[0],p_sport_count:rows2,other:rows3[0]});
  62. });
  63. });
  64.  
  65. });
  66.  
  67. });
  68.  
  69. },
  70.  
  71.  
  72. login_post(req, res) {
  73. var c = new Client({
  74. host: '127.0.0.1',
  75. user: 'root',
  76. password: '',
  77. db: 'play60'
  78. });
  79.  
  80. var check = c.prepare('SELECT UID FROM User WHERE email =' + "'" + req.body.email_input + "'" + 'AND password =' +"'" +req.body.password_input + "'");
  81. //select * from User where email ='earends@zagmail.gonzaga.edu' and password = 'password'
  82. c.query(check(), function(err, rows) {
  83. if (err)
  84. throw err;
  85.  
  86. if (rows.info.numRows == 1) {
  87. res.redirect('/' + rows[0].UID + '/home');
  88. } else {
  89. res.render('login',{error: 'wrong'})
  90. }
  91. });
  92.  
  93. c.end();
  94. },
  95.  
  96.  
  97. signup_post(req, res) {
  98. var c = new Client({
  99. host: '127.0.0.1',
  100. user: 'root',
  101. password: '',
  102. db: 'play60'
  103. });
  104. if (req.body.password_input != req.body.password_c_input) {
  105. res.render('signup',{error:'password-match'})
  106. return;
  107. }
  108. var check = c.prepare('SELECT email FROM User WHERE email =' + "'" + req.body.email_input + "'");
  109. var insert = c.prepare("INSERT INTO User (name,age,email,password) VALUES (" + "'" + req.body.name_input + "'" + "," + "'" + req.body.age_input + "'" + "," + "'" + req.body.email_input + "'"+ "," + "'" + req.body.password_input + "'" + ")");
  110. //checks to see if email is already in their if so re route to signup
  111. c.query(check(), function(err, rows) {
  112. if (err)
  113. res.res.render('signup',{error: 'in-use'})
  114. if (rows.info.numRows != 0) {
  115. res.render('signup',{error:'in-use'});
  116. } else { // else if your all good
  117. c.query(insert(), function(err, rows) {
  118. res.redirect('/login');
  119. });
  120. }
  121. });
  122. // adds email if good route to login page if no good re reoute to signup page
  123.  
  124.  
  125.  
  126. c.end();
  127.  
  128. },
  129. createPost_save(req,res) {
  130. var c = new Client({
  131. host: '127.0.0.1',
  132. user: 'root',
  133. password: '',
  134. db: 'play60'
  135. });
  136. var timeInMs = Date.now();
  137. var insert = c.prepare("INSERT INTO Posting (title,game,description,UID,event_time,zipcode,timestamp) VALUES (" + "'" + req.body.title_input + "'" + "," + "'" + req.body.game_input + "'"+ "," + "'" + req.body.details_input + "'"+ "," + "'" + parseInt(req.params.id) + "'"+ "," + "'" + req.body.time_input + "'" + "," + "'" +req.body.zip_input + "'" + "," + timeInMs + ")" )
  138. c.query(insert(), function(err, rows) {
  139. if (err)
  140. throw err
  141. res.redirect('/' + req.params.id + '/home');
  142. });
  143.  
  144. c.end();
  145. },
  146.  
  147. posting_get(req,res) {
  148. var c = new Client({
  149. host: '127.0.0.1',
  150. user: 'root',
  151. password: '',
  152. db: 'play60'
  153. });
  154.  
  155. var get = c.prepare("SELECT * FROM Posting WHERE PID =" + "'" +req.params.pid + "'")
  156.  
  157. c.query(get(), function(err, rows) {
  158. if (err)
  159. throw err
  160.  
  161. if (req.params.id == rows[0].UID) {
  162. res.render('posting',{data:rows[0],uid:req.params.id,pid: req.params.pid,owner:true})
  163. } else {
  164. res.render('posting',{data:rows[0],uid:req.params.id,pid:req.params.pid,owner:false})
  165. }
  166.  
  167. });
  168.  
  169. c.end();
  170.  
  171. },
  172. posting_post(req,res) {
  173. var c = new Client({
  174. host: '127.0.0.1',
  175. user: 'root',
  176. password: '',
  177. db: 'play60'
  178. });
  179.  
  180.  
  181. if (req.body.going == null && req.body.maybe == null) {
  182. if (req.body.edit == null) {
  183. console.log('delete clicked');
  184. var d = c.prepare("DELETE FROM Posting WHERE PID = " + req.params.pid);
  185. c.query(d(), function(err, rows) {
  186. if (err)
  187. throw err
  188. res.redirect('/' + req.params.id + '/profile');
  189. });
  190. c.end();
  191. } else {
  192. console.log('edit clicked');
  193. res.redirect('/' + req.params.id +/home/ + req.params.pid + '/edit');
  194. }
  195.  
  196. } else if (req.body.maybe == null) {
  197. var maybe = c.prepare()
  198. console.log('going clicked')
  199. var count_going = c.prepare("SELECT going FROM Posting WHERE PID =" + "'" +req.params.pid + "'")
  200.  
  201. c.query(count_going(), function(err, rows) {
  202. if (err)
  203. throw err
  204. var update = c.prepare("UPDATE Posting SET going =" + "'" + (parseInt(rows[0].going) + 1) + "'" + "WHERE PID =" + "'" + req.params.pid + "'")
  205. c.query(update(), function(err, rows) {
  206.  
  207. if (err)
  208. throw err
  209. res.redirect('/' + req.params.id + '/home')
  210. });
  211. });
  212. c.end();
  213. } else {
  214. console.log('maybe clicked')
  215. var count_maybe = c.prepare("SELECT maybe FROM Posting WHERE PID =" + "'" +req.params.pid + "'")
  216.  
  217. c.query(count_maybe(), function(err, rows) {
  218. if (err)
  219. throw err
  220. var update = c.prepare("UPDATE Posting SET maybe =" + "'" + (parseInt(rows[0].maybe) + 1) + "'" + "WHERE PID =" + "'" + req.params.pid + "'")
  221. c.query(update(), function(err, rows) {
  222.  
  223. if (err)
  224. throw err
  225. res.redirect('/' + req.params.id + '/home')
  226. });
  227. });
  228. c.end();
  229. }
  230.  
  231. },
  232.  
  233. home_post(req,res) {
  234.  
  235. var string = 'SELECT * FROM Posting ';
  236. first_flag = true;
  237. date_flag = false;
  238. popularity_flag = false;
  239. if (req.body.owned != null) {
  240. string = string + "WHERE UID = " + "'" + req.params.id + "'";
  241. first_flag = false
  242. }
  243. if (req.body.pop != null) {
  244. popularity_flag = true;
  245. }
  246. if (req.body.date != null) {
  247. date_flag = true;
  248. }
  249. if (req.body.football != null) {
  250. if (first_flag) {
  251. string = string + " WHERE game = 'football' ";
  252. first_flag = false;
  253. } else {
  254. string = string + " OR game = 'football' ";
  255. }
  256.  
  257. }
  258. if (req.body.soccer != null) {
  259. if (first_flag) {
  260. string = string + " WHERE game = 'soccer' ";
  261. first_flag = false;
  262. } else {
  263. string = string + " OR game = 'soccer' ";
  264. }
  265. }
  266. if (req.body.basketball != null) {
  267. if (first_flag) {
  268. string = string + " WHERE game = 'basketball' ";
  269. first_flag = false;
  270. } else {
  271. string = string + " OR game = 'basketball' ";
  272. }
  273. }
  274. if (req.body.baseball != null) {
  275. if (first_flag) {
  276. string = string + " WHERE game = 'baseball' ";
  277. first_flag = false;
  278. } else {
  279. string = string + " OR game = 'baseball' ";
  280. }
  281. }
  282.  
  283. if (req.body.zip_input != '') {
  284. if (first_flag) {
  285. string = string + " WHERE zipcode = '" + req.body.zip_input + "'";
  286. first_flag = false;
  287. } else {
  288. string = string + " OR zipcode = '" + req.body.zip_input + "'";
  289. }
  290. }
  291. var c = new Client({
  292. host: '127.0.0.1',
  293. user: 'root',
  294. password: '',
  295. db: 'play60'
  296. });
  297.  
  298. if (popularity_flag) {
  299. var findMost = 'SELECT game, count(*) FROM Posting GROUP BY game order by count(*) DESC LIMIT 1';
  300. var find = c.prepare(findMost);
  301. c.query(find(), function(err, rows) {
  302. if (err)
  303. throw err
  304. var game = rows[0].game;
  305. findGame = 'SELECT * FROM Posting WHERE game = ' + "'" + game + "'";
  306. if (date_flag) {
  307. findGame = findGame + " ORDER BY timestamp DESC"
  308. }
  309. var findGamePrep = c.prepare(findGame);
  310. c.query(findGamePrep(), function(err, rows1) {
  311. if (err)
  312. throw err
  313. res.render('home',{ uid:req.params.id, postings: rows1 })
  314. });
  315.  
  316. });
  317. } else {
  318. if (date_flag) {
  319. string = string + " ORDER BY timestamp"
  320. var get = c.prepare(string)
  321. c.query(get(), function(err, rows) {
  322. if (err)
  323. throw err
  324. res.render('home',{ uid:req.params.id, postings: rows })
  325. });
  326. } else {
  327. var get = c.prepare(string)
  328. c.query(get(), function(err, rows) {
  329. if (err)
  330. throw err
  331. res.render('home',{ uid:req.params.id, postings: rows })
  332. });
  333. }
  334. }
  335.  
  336.  
  337.  
  338.  
  339.  
  340.  
  341. },
  342.  
  343. posting_update_get(req,res) {
  344. var c = new Client({
  345. host: '127.0.0.1',
  346. user: 'root',
  347. password: '',
  348. db: 'play60'
  349. });
  350.  
  351. var get = c.prepare("SELECT title FROM Posting WHERE PID =" + "'" +req.params.pid + "'")
  352. c.query(get(), function(err, rows) {
  353. if (err)
  354. throw err
  355. res.render('posting_update',{uid:req.params.id,pid:req.params.pid,title:rows[0].title})
  356. });
  357. c.end();
  358.  
  359. },
  360.  
  361. posting_update_post(req,res) {
  362. console.log('going here');
  363. var c = new Client({
  364. host: '127.0.0.1',
  365. user: 'root',
  366. password: '',
  367. db: 'play60'
  368. });
  369. var update = c.prepare("UPDATE Posting SET title =" + "'" + req.body.title_input + "'" + "," + "game =" + "'" + req.body.game_input + "'"+ "," + "event_time =" + "'" + req.body.time_input + "'"+ "," + "zipcode =" + "'" + req.body.zip_input + "'"+ "," + "description =" + "'" + req.body.details_input + "'" + "WHERE PID =" + "'" + req.params.pid + "'")
  370. c.query(update(), function(err, rows) {
  371. if (err)
  372. throw err
  373. res.redirect('/' + req.params.id + '/home')
  374. });
  375.  
  376. c.end();
  377. },
  378. profile_post(req,res) {
  379. var c = new Client({
  380. host: '127.0.0.1',
  381. user: 'root',
  382. password: '',
  383. db: 'play60'
  384. });
  385. var update = c.prepare("UPDATE User SET name =" + "'" + req.body.name_input + "'" + "," + "age =" + "'" + req.body.age_input + "'"+ "," + "email =" + "'" + req.body.email_input + "'"+ "," + "password =" + "'" + req.body.new_password_input + "'" + 'WHERE UID =' + "'" + req.params.id + "'")
  386. c.query(update(), function(err, rows) {
  387. if (err)
  388. throw err
  389. res.redirect('/login');
  390. });
  391. c.end();
  392. }
  393.  
  394.  
  395. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!