CREATE TABLE IF NOT EXISTS `tbl_imuploads` (
  `CID` int(11) DEFAULT NULL,
  `detail` varchar(100) CHARACTER SET utf8 NOT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `file` varchar(100) CHARACTER SET utf8 NOT NULL,
  `type` varchar(10) CHARACTER SET utf8 NOT NULL,
  `size` int(11) NOT NULL,
  `updatedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `tbl_imuploads`
--

INSERT INTO `tbl_imuploads` (`CID`, `detail`, `id`, `file`, `type`, `size`, `updatedon`) VALUES
(5514, 'Indian National Flag', 7, '12855-flag.gif', 'image/gif', 31, '2017-06-10 12:36:22'),
(5523, 'My Photo', 8, '28230-scan0004.jpg', 'image/jpeg', 40, '2017-06-10 14:46:58'),
(5523, 'My 2nd Photo', 9, '66637-scan0005.jpg', 'image/jpeg', 36, '2017-06-10 14:47:38'),
(5534, 'Ashok Stambh', 10, '9698-ashoka_pillar.gif', 'image/gif', 3, '2017-06-10 17:56:29'),
(5535, 'Kohinoor Logo', 11, '65806-kohinoor-logo.gif', 'image/gif', 37, '2017-06-11 02:10:04'),
(5536, 'Test Image', 12, '82390-_scn0838.jpg', 'image/jpeg', 730, '2017-06-11 02:12:25'),
(5514, 'Ashok Stambh', 13, '32185-ashoka_pillar.gif', 'image/gif', 3, '2017-06-11 05:16:44'),
(5537, 'Ashok Stambh', 14, '82459-ashoka_pillar.gif', 'image/gif', 3, '2017-06-15 12:14:26'),
(5537, 'Test123', 15, '4199-wding1.png', 'image/png', 17, '2017-06-15 12:14:57');

-- --------------------------------------------------------

--
-- Stand-in structure for view `totalfee`
--
CREATE TABLE IF NOT EXISTS `totalfee` (
`stu_sl` int(11)
,`sum(amount)` decimal(32,0)
);
-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `RID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `City` varchar(50) NOT NULL,
  `phone` bigint(10) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `DOB` date NOT NULL,
  `Pass` varchar(15) NOT NULL,
  PRIMARY KEY (`RID`),
  UNIQUE KEY `Email` (`Email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5538 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`RID`, `Name`, `Address`, `City`, `phone`, `Email`, `DOB`, `Pass`) VALUES
(5514, 'Anupam', '1291, 12/A', 'Bokaro', 8051048001, 'arunoday4@rediffmail.com', '1989-01-25', 'Bokaro@12345'),
(5523, 'Saket', 'Sec-3', 'Bokaro Steel City', 9835313220, 'saket@bhartitek.in', '1982-06-11', 'Saket1234@'),
(5534, 'a', 'a', 'a', 1234567890, 'a@a.a', '2010-05-10', '1103@Saket'),
(5536, 'c', 'c', 'c', 1234554321, 'c@c.c', '1998-06-11', '1103@Saket'),
(5537, 'Rishikesh', 'Sec-2', 'Bokaro', 9334932005, 'rishikesh25@gmail.com', '1980-01-15', 'Bokaro@12345');

-- --------------------------------------------------------

--
-- Structure for view `batch1`
--
DROP TABLE IF EXISTS `batch1`;

CREATE ALGORITHM=UNDEFINED DEFINER=`bhartitek`@`localhost` SQL SECURITY DEFINER VIEW `batch1` AS select `students`.`batch` AS `batch`,count(`students`.`batch`) AS `total` from `students` group by `students`.`batch`;

-- --------------------------------------------------------

--
-- Structure for view `dues`
--
DROP TABLE IF EXISTS `dues`;

CREATE ALGORITHM=UNDEFINED DEFINER=`bhartitek`@`localhost` SQL SECURITY DEFINER VIEW `dues` AS select `students`.`Sl` AS `sl`,`students`.`name` AS `name`,(`students`.`fee` - `totalfee`.`sum(amount)`) AS `dues` from (`students` join `totalfee`) where (`students`.`Sl` = `totalfee`.`stu_sl`);

-- --------------------------------------------------------

--
-- Structure for view `report`
--
DROP TABLE IF EXISTS `report`;

CREATE ALGORITHM=UNDEFINED DEFINER=`bhartitek`@`localhost` SQL SECURITY DEFINER VIEW `report` AS select `f`.`sl` AS `rno`,`s`.`name` AS `name`,`s`.`roll` AS `roll`,`f`.`amount` AS `amount`,`f`.`date` AS `date` from (`feesubmit` `f` join `students` `s`) where (`s`.`Sl` = `f`.`stu_sl`);

-- --------------------------------------------------------

--
-- Structure for view `smssend`
--
DROP TABLE IF EXISTS `smssend`;

CREATE ALGORITHM=UNDEFINED DEFINER=`bhartitek`@`localhost` SQL SECURITY DEFINER VIEW `smssend` AS select `a`.`roll` AS `roll`,`a`.`date` AS `date`,`s`.`name` AS `name`,`s`.`Sl` AS `sl`,`s`.`mobile` AS `mobile`,concat('Dear Parents, Your ward ',`s`.`name`,' is absent in school on ',`a`.`date`,'.') AS `sms` from (`absent` `a` join `students` `s`) where (`a`.`roll` = `s`.`roll`);

-- --------------------------------------------------------

--
-- Structure for view `smssend1`
--
DROP TABLE IF EXISTS `smssend1`;

CREATE ALGORITHM=UNDEFINED DEFINER=`bhartitek`@`localhost` SQL SECURITY DEFINER VIEW `smssend1` AS select `a`.`roll` AS `roll`,`a`.`duedate` AS `date`,`a`.`amount` AS `amount`,`s`.`name` AS `name`,`s`.`Sl` AS `sl`,`s`.`mobile` AS `mobile`,concat('Dear Parents, Fee dues of your ward is ',`a`.`amount`,'. Plz pay it before ',`a`.`duedate`,'.') AS `sms` from (`smsdues` `a` join `students` `s`) where (`a`.`roll` = `s`.`roll`);

-- --------------------------------------------------------

--
-- Structure for view `totalfee`
--
DROP TABLE IF EXISTS `totalfee`;

CREATE ALGORITHM=UNDEFINED DEFINER=`bhartitek`@`localhost` SQL SECURITY DEFINER VIEW `totalfee` AS select `feesubmit`.`stu_sl` AS `stu_sl`,sum(`feesubmit`.`amount`) AS `sum(amount)` from `feesubmit` group by `feesubmit`.`stu_sl`;

/*!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 */;