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)
);

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

--
-- Structure for view `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`
--

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`
--

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`
--

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`
--

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`
--

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`;

