I have stored Procedure for retriving counts of old or new patients.My stored Procedure works correctly.Now I've to write DAL file,BAL file using this stored procedure.But i'm in stage just i fully confused now.can you help me guys?I have to use MySqlDataReader for return counts of patients.
CREATE DEFINER=`root`@`%` PROCEDURE `GetPatientsCount`(IN PDID INT, IN PDATE DATE , IN UTYPE VARCHAR(20))
BEGIN
IF PDID > 0
THEN
SELECT
if(DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')
AS UserType,
count(
if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')) as Cnt
FROM commonpersoninfo AS C,
appointment_has_doctor AS AD,
appointment AS A,
symptoms AS S,
personinfo P
WHERE A.pkey = AD.Appointment_pkey
AND C.PKEY = A.PatientInfo_PKey
AND AD.DoctorsInfo_pkey = PDID
AND S.CommonPersonInfo_pkey = C.pkey
AND DATE_FORMAT(a.STARTDATETIME, '%Y-%m-%d') = PDATE
AND P.CommonPersonInfo_pkey = C.pkey
AND if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient') = UTYPE
GROUP BY UserType;
ELSE
SELECT if(DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')
AS UserType,
count(
if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')) as Cnt
FROM commonpersoninfo AS C,
appointment_has_doctor AS AD,
appointment AS A,
symptoms AS S,
personinfo P
WHERE A.pkey = AD.Appointment_pkey
AND C.PKEY = A.PatientInfo_PKey
AND S.CommonPersonInfo_pkey = C.pkey
AND DATE_FORMAT(a.STARTDATETIME, '%Y-%m-%d') = PDATE
AND P.CommonPersonInfo_pkey = C.pkey
AND if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient') = UTYPE
GROUP BY UserType;
END IF;
END