File "demo.sql"
Full Path: /home/analogde/www/FormData/Fusion/account/demo.sql
File size: 6.92 KB
MIME-type: text/plain
Charset: utf-8
-- phpMyAdmin SQL Dump
-- version 4.9.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: May 26, 2021 at 01:13 AM
-- Server version: 5.6.37
-- PHP Version: 7.3.11
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `demo`
--
DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_adminchangepwd` (`newpwd` VARCHAR(120), `ldtime` VARCHAR(120), `uid` INT(5)) BEGIN
update tbladmin set Password=newpwd,updationDate=ldtime where id=uid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_admincurrentpwdvalidate` (`currentpwd` VARCHAR(120), `uid` INT(5)) BEGIN
select id from tbladmin where id=uid and Password=currentpwd;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_admindashboard` () BEGIN
select count(id) as totalusers,
COUNT(IF((date(RegDate)=CURDATE()),0,NULL)) as todayreguser,
COUNT(IF((date(RegDate)=CURDATE()-1),0,NULL)) as yesterdayreguser,
COUNT(IF((date(RegDate) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()),0,NULL)) as lastsevendaysreguser,
COUNT(IF((date(RegDate) BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()),0,NULL)) as lastthirtydaysreguser
from tblusers;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_adminlogin` (IN `username` VARCHAR(200), IN `adminpwd` VARCHAR(200)) BEGIN
select FullName,id,UserName from tbladmin where UserName=username and Password=adminpwd;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_adminpasswordrecovery` (`uname` VARCHAR(120), `adminemail` VARCHAR(200), `newpwd` VARCHAR(150), `ldtime` VARCHAR(120)) BEGIN
update tbladmin set Password=newpwd,updationDate=ldtime where UserName=uname and AdminEmail=adminemail;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_adminprofile` (`adminid` INT(5)) BEGIN
select * from tbladmin where id=adminid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_adminpwdrecoveryvalidation` (`uname` VARCHAR(120), `adminemail` VARCHAR(150)) BEGIN
select id from tbladmin where UserName=uname and AdminEmail=adminemail;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_allregisteredusers` () BEGIN
select * from tblusers;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_checkemailavailabilty` (`emalid` VARCHAR(150)) BEGIN
select EmailId from tblusers where EmailId=emalid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_recent15users` () BEGIN
select * from tblusers order by id desc limit 15;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_signup` (`fname` VARCHAR(120), `lname` VARCHAR(120), `emalid` VARCHAR(200), `inputpwd` VARCHAR(200), `isactve` INT(1)) BEGIN
insert into tblusers(FirstName,LastName,EmailId,UserPassword,IsActive) value(fname,lname,emalid,inputpwd,isactve);
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userchangepwd` (`newpwd` VARCHAR(120), `ldtime` VARCHAR(120), `uid` INT(5)) BEGIN
update tblusers set UserPassword=newpwd,LastUpdationDate=ldtime where id=uid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_usercurrentpwdvalidate` (`currentpwd` VARCHAR(120), `uid` INT(5)) BEGIN
select id from tblusers where id=uid and UserPassword=currentpwd;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userdeletion` (`uid` INT(5)) BEGIN
delete from tblusers where id=uid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_useremailupdation` (`newemail` VARCHAR(120), `ldtime` VARCHAR(120), `uid` INT(5)) BEGIN
update tblusers set EmailId=newemail,LastUpdationDate=ldtime where id=uid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userlogin` (IN `uemailid` VARCHAR(200), IN `userpwd` VARCHAR(200)) BEGIN
select FirstName,LastName,id from tblusers where EmailId=uemailid and UserPassword=userpwd;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userpasswordrecovery` (`lastname` VARCHAR(120), `useremailid` VARCHAR(200), `newpwd` VARCHAR(150), `ldtime` VARCHAR(120)) BEGIN
update tblusers set UserPassword=newpwd,LastUpdationDate=ldtime where LastName=lastname and EmailId=useremailid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userprofile` (`uid` INT(5)) BEGIN
select * from tblusers where id=uid;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userpwdrecoveryvalidation` (`lastname` VARCHAR(120), `useremail` VARCHAR(150)) BEGIN
select id from tblusers where LastName=lastname and EmailId=useremail;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userupdateprofile` (`fname` VARCHAR(120), `lname` VARCHAR(120), `ldtime` VARCHAR(120), `uid` INT(5)) BEGIN
update tblusers set FirstName=fname,LastName=lname,LastUpdationDate=ldtime where id=uid;
END$$
DELIMITER ;
-- --------------------------------------------------------
--
-- Table structure for table `tbladmin`
--
CREATE TABLE `tbladmin` (
`id` int(11) NOT NULL,
`FullName` varchar(100) DEFAULT NULL,
`AdminEmail` varchar(120) DEFAULT NULL,
`UserName` varchar(100) NOT NULL,
`Password` varchar(100) NOT NULL,
`updationDate` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbladmin`
--
INSERT INTO `tbladmin` (`id`, `FullName`, `AdminEmail`, `UserName`, `Password`, `updationDate`) VALUES
(1, 'BlogITCode', 'blogitcodes@gmail.com', 'admin', 'f925916e2754e5e03f75dd58a5733251', '17-01-2021 05:51:58 AM');
-- --------------------------------------------------------
--
-- Table structure for table `tblusers`
--
CREATE TABLE `tblusers` (
`id` int(11) NOT NULL,
`FirstName` varchar(150) DEFAULT NULL,
`LastName` varchar(150) DEFAULT NULL,
`EmailId` varchar(255) DEFAULT NULL,
`UserPassword` varchar(255) DEFAULT NULL,
`RegDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`IsActive` int(1) DEFAULT NULL,
`LastUpdationDate` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tblusers`
--
INSERT INTO `tblusers` (`id`, `FirstName`, `LastName`, `EmailId`, `UserPassword`, `RegDate`, `IsActive`, `LastUpdationDate`) VALUES
(13, 'demo', 'demo', 'demo@gmail.com', 'e10adc3949ba59abbe56e057f20f883e', '2021-05-26 01:06:56', 1, '26-05-2021 01:08:36 AM');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbladmin`
--
ALTER TABLE `tbladmin`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `tblusers`
--
ALTER TABLE `tblusers`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbladmin`
--
ALTER TABLE `tbladmin`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `tblusers`
--
ALTER TABLE `tblusers`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;