Pocket Personal Health Record

//**************************************************************************//
// Copyright (C) Abdul Rasheed. All rights Reserved.                        //
// rasheedat.blogspot.com                                                   //
//**************************************************************************//
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PPHR.Common;
using PPHR.DataAccess;
namespace PPHR.DataLogic
{
    /// <summary>
    /// Holds all report queries
    /// </summary>
    public class ReportsDataLogic
    {
        DBConnection dbCon = new DBConnection();

        /// <summary>
        /// Get all visit details as single table
        /// </summary>
        /// <param name="PVData"></param>
        /// <returns></returns>
        public DataTable VisitReport(VisitReportData PVData)
        {
            DataTable dt = null;
            try
            {
                string sql = string.Empty;
                string where = string.Empty;
                sql = "SELECT ";
                sql += "  V.VisitNumber, V.VisitDate, MC.Description AS VisitType, V.ConsultantName, V.FacilityName, ";
                sql += "  V.VisitDetail, VP.PrescriptionDocument, VP.Note AS PreNote, VD.DoctorVoiceComment, ";
                sql += "  VD.Note AS DrNote, VA.AdditionalInformationDocument, VA.Note AS AddNote";
                sql += " FROM";
                sql += " VisitPrescription VP RIGHT OUTER JOIN";
                sql += "   VisitDoctorVoice VD RIGHT OUTER JOIN";
                sql += "     VisitAdditionalInfo VA RIGHT OUTER JOIN";
                sql += "       MasterCodes MC INNER JOIN";
                sql += "         VisitDetail V ";
                sql += "       ON V.VisitType = MC.Code And MC.CodeGroup = 'VISITTYP'";
                sql += "     ON V.VisitNumber = VA.VisitID ";
                sql += "   ON V.VisitNumber = VD.VisitID ";
                sql += " ON V.VisitNumber = VP.VisitNumber";
                if (!PVData.DrName.Trim().Equals(string.Empty))
                {
                    where += " AND V.ConsultantName like '%" + PVData.DrName.Trim() + "%'";
                }
                if (!PVData.FacilityName.Trim().Equals(string.Empty))
                {
                    where += " AND V.FacilityName like '%" + PVData.FacilityName.Trim() + "%'";
                }
                if (PVData.StartDate != null)
                {
                    where += " AND V.VisitDate >='" + PVData.StartDate + "'";
                }
                if (PVData.EndDate != null)
                {
                    where += " AND V.VisitDate <='" + PVData.EndDate + "'";
                }
                if (!where.Trim().Equals(string.Empty))
                {
                    sql += " Where 1=1 " + where;
                }
                sql += " Order By V.VisitDate DESC";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }
       
        /// <summary>
        /// Get visit information
        /// </summary>
        /// <param name="PVData">Report filter criteria</param>
        /// <returns></returns>
        public DataTable VisitReportHeader(VisitReportData PVData)
        {
            DataTable dt = null;
            try
            {
                string sql = string.Empty;
                string where = string.Empty;
                sql = "SELECT ";
                sql += "  V.VisitNumber, V.VisitDate, MC.Description AS VisitType, V.ConsultantName, V.FacilityName, ";
                sql += "  V.VisitDetail ";
                sql += " FROM";
                sql += "       MasterCodes MC INNER JOIN";
                sql += "         VisitDetail V ";
                sql += "       ON V.VisitType = MC.Code And MC.CodeGroup = 'VISITTYP'";
                if (!PVData.DrName.Trim().Equals(string.Empty))
                {
                    where += " AND V.ConsultantName like '%" + PVData.DrName.Trim() + "%'";
                }
                if (!PVData.FacilityName.Trim().Equals(string.Empty))
                {
                    where += " AND V.FacilityName like '%" + PVData.FacilityName.Trim() + "%'";
                }
                if (PVData.StartDate != null && !PVData.StartDate.ToString().Equals("1/1/01 12:00:00 AM"))
                {
                    where += " AND V.VisitDate >='" + PVData.StartDate + "'";
                }
                if (PVData.EndDate != null && !PVData.StartDate.ToString().Equals("1/1/01 12:00:00 AM"))
                {
                    where += " AND V.VisitDate <='" + PVData.EndDate + "'";
                }
                if (!PVData.VisitType.Trim().Equals(string.Empty))
                {
                    where += " AND V.VisitType ='" + PVData.VisitType+ "'";
                }
                if (!where.Trim().Equals(string.Empty))
                {
                    sql += " Where 1=1 " + where;
                }
                sql += " Order By V.VisitDate DESC";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }
    }
}