/Portal/CSP Portal Code/CSP.SimpleCMS/DM/CaseDM.cs

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Text;



using CSP.SimpleCMS.Data;

using CSP.MembershipInterface.ObjectModel;



namespace CSP.SimpleCMS.DataModel

{

    /// <summary>Manages the operations related with the Cases</summary>

    public static class CaseDM

    {



        /// <summary>Adds a new case</summary>

        /// <param name="crmCase">A Case object with the information of the new case to be added</param>

        /// <returns>The generated guid.</returns>

        public static string AddCase(Case crmCase)

        {

            string guid = null;



            using (DataAccess dataAccess = new DataAccess())

            {

                guid = dataAccess.ExecSPReturnScalar(DMConstants.SPAddCase, new SqlParameter[] {

                    new SqlParameter(DMConstants.FieldCaseTypeID, crmCase.Type.CaseTypeId),

                    new SqlParameter(DMConstants.FieldCaseStatusID, crmCase.Status.CaseStatusId),

                    new SqlParameter(DMConstants.FieldCaseRoleTypeID, crmCase.RoleType.CaseRoleTypeId),

                    new SqlParameter(DMConstants.FieldTitle, crmCase.Title),

                    new SqlParameter(DMConstants.FieldDescription, IsNullOrEmpty(crmCase.Description)),

                    new SqlParameter(DMConstants.FieldDueDate, IsNullOrEmpty(crmCase.DueDate)),

                    new SqlParameter(DMConstants.FieldRequestorID, IsNullOrEmpty(crmCase.RequestorID)),

                    new SqlParameter(DMConstants.FieldRequestorName, IsNullOrEmpty(crmCase.RequestorName)),

                    new SqlParameter(DMConstants.FieldRequestorEmail, IsNullOrEmpty(crmCase.RequestorEmail)),

                    new SqlParameter(DMConstants.FieldRequestorAddress, IsNullOrEmpty(crmCase.RequestorAddress)),

                    new SqlParameter(DMConstants.FieldRequestorCity, IsNullOrEmpty(crmCase.RequestorCity)),

                    new SqlParameter(DMConstants.FieldRequestorPostalCode, IsNullOrEmpty(crmCase.RequestorPostalCode)),

                    new SqlParameter(DMConstants.FieldRequestorPhone, IsNullOrEmpty(crmCase.RequestorPhone)),

                    new SqlParameter(DMConstants.FieldFormXml, IsNullOrEmpty(crmCase.FormXml)),

                    new SqlParameter(DMConstants.FieldExternalDocumentRef, IsNullOrEmpty(crmCase.ExternalDocumentRef)),

                    new SqlParameter(DMConstants.FieldOwnerID, IsNullOrEmpty(crmCase.OwnerID)) }).ToString();

            }

            

            return guid;

        }





        /// <summary>Updates an existing case</summary>

        /// <param name="crmCase">A Case object with the information of the case to be updated</param>

        public static void UpdateCase(Case crmCase)

        {

            using (DataAccess dataAccess = new DataAccess())

            {

                dataAccess.ExecSP(DMConstants.SPAddCase, new SqlParameter[] {

                    new SqlParameter(DMConstants.FieldCaseID, crmCase.ID),

                    new SqlParameter(DMConstants.FieldCaseTypeID, crmCase.Type.CaseTypeId),

                    new SqlParameter(DMConstants.FieldCaseStatusID, crmCase.Status.CaseStatusId),

                    new SqlParameter(DMConstants.FieldCaseRoleTypeID, crmCase.RoleType.CaseRoleTypeId),

                    new SqlParameter(DMConstants.FieldTitle, crmCase.Title),

                    new SqlParameter(DMConstants.FieldDescription, IsNullOrEmpty(crmCase.Description)),

                    new SqlParameter(DMConstants.FieldDueDate, IsNullOrEmpty(crmCase.DueDate)),

                    new SqlParameter(DMConstants.FieldRequestorID, IsNullOrEmpty(crmCase.RequestorID)),

                    new SqlParameter(DMConstants.FieldRequestorName, IsNullOrEmpty(crmCase.RequestorName)),

                    new SqlParameter(DMConstants.FieldRequestorEmail, IsNullOrEmpty(crmCase.RequestorEmail)),

                    new SqlParameter(DMConstants.FieldRequestorAddress, IsNullOrEmpty(crmCase.RequestorAddress)),

                    new SqlParameter(DMConstants.FieldRequestorCity, IsNullOrEmpty(crmCase.RequestorCity)),

                    new SqlParameter(DMConstants.FieldRequestorPostalCode, IsNullOrEmpty(crmCase.RequestorPostalCode)),

                    new SqlParameter(DMConstants.FieldRequestorPhone, IsNullOrEmpty(crmCase.RequestorPhone)),

                    new SqlParameter(DMConstants.FieldFormXml, IsNullOrEmpty(crmCase.FormXml)),

                    new SqlParameter(DMConstants.FieldExternalDocumentRef, IsNullOrEmpty(crmCase.ExternalDocumentRef)),

                    new SqlParameter(DMConstants.FieldOwnerID, IsNullOrEmpty(crmCase.OwnerID)) });

            }

        }



        /// <summary>Returns an array of Cases with all Cases stored in the data source</summary>

        /// <returns>A Case object with the information of the new Case to be added</returns>

        public static Case[] GetCases()

        {

            List<Case> cases = new List<Case>();



            DataSet dataSet = new DataSet();

            using (DataAccess dataAccess = new DataAccess())

            {

                dataSet = dataAccess.ExecSPReturnDS(DMConstants.SPGetCases, null);

            }



            if (dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows.Count > 0)

            {

                foreach (DataRow row in dataSet.Tables[0].Rows)

                {

                    Case crmCase = ParseRow(row);

                    cases.Add(crmCase);

                }

            }



            return cases.ToArray();

        }



        /// <summary>Returns an array of Cases depending on the CaseType</summary>

        /// <param name="caseType">The CaseType of the Cases to be returned</param>

        /// <returns>An array of Cases</returns>

        public static Case[] GetCasesByCaseType(CaseType caseType)

        {

            List<Case> cases = new List<Case>();



            DataSet dataSet = new DataSet();

            using (DataAccess dataAccess = new DataAccess())

            {

                dataSet = dataAccess.ExecSPReturnDS(DMConstants.SPGetCases, new SqlParameter[] { new SqlParameter(DMConstants.FieldCaseTypeID, caseType.CaseTypeId) });

            }



            if (dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows.Count > 0)

            {

                foreach (DataRow row in dataSet.Tables[0].Rows)

                {

                    Case crmCase = ParseRow(row);

                    cases.Add(crmCase);

                }

            }



            return cases.ToArray();

        }



        /// <summary>Returns an array of Cases depending on the CaseRoleType</summary>

        /// <param name="caseRoleType">The CaseRoleType of the Cases to be returned</param>

        /// <returns>An array of Cases</returns>

        public static Case[] GetCasesByCaseRoleType(CaseRoleType caseRoleType)

        {

            List<Case> cases = new List<Case>();



            DataSet dataSet = new DataSet();

            using (DataAccess dataAccess = new DataAccess())

            {

                dataSet = dataAccess.ExecSPReturnDS(DMConstants.SPGetCases, new SqlParameter[] { new SqlParameter(DMConstants.FieldCaseRoleTypeID, caseRoleType.CaseRoleTypeId) });

            }



            if (dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows.Count > 0)

            {

                foreach (DataRow row in dataSet.Tables[0].Rows)

                {

                    Case crmCase = ParseRow(row);

                    cases.Add(crmCase);

                }

            }



            return cases.ToArray();

        }



        /// <summary>Returns an array of Cases depending on the CaseStatus</summary>

        /// <param name="caseStatus">The CaseStatus of the Cases to be returned</param>

        /// <returns>An array of Cases</returns>

        public static Case[] GetCasesByCaseStatus(CaseStatus caseStatus)

        {

            List<Case> cases = new List<Case>();



            DataSet dataSet = new DataSet();

            using (DataAccess dataAccess = new DataAccess())

            {

                dataSet = dataAccess.ExecSPReturnDS(DMConstants.SPGetCases, new SqlParameter[] { new SqlParameter(DMConstants.FieldCaseStatusID, caseStatus.CaseStatusId) });

            }



            if (dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows.Count > 0)

            {

                foreach (DataRow row in dataSet.Tables[0].Rows)

                {

                    Case crmCase = ParseRow(row);

                    cases.Add(crmCase);

                }

            }



            return cases.ToArray();

        }





        /// <summary>Returns a Cases with a specified caseID</summary>

        /// <param name="caseID">The caseID of the Cases to be returned</param>

        /// <returns>The Case with the caseID</returns>

        public static Case GetCaseByCaseID(string caseID)

        {

            Case crmCase = null;



            DataSet dataSet = new DataSet();

            using (DataAccess dataAccess = new DataAccess())

            {

                dataSet = dataAccess.ExecSPReturnDS(DMConstants.SPGetCases, new SqlParameter[] { new SqlParameter(DMConstants.FieldCaseID, caseID) });

            }



            if (dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows.Count == 1)

            {

                crmCase = ParseRow(dataSet.Tables[0].Rows[0]);

            }



            return crmCase;

        }



        /// <summary>Returns an array of citizens Cases</summary>

        /// <param name="username">The username of the citizen</param>

        /// <returns>An anrray of Cases</returns>

        public static Case[] GetCasesByCitizenID(string username)

        {

            List<Case> cases = new List<Case>();



            DataSet dataSet = new DataSet();

            using (DataAccess dataAccess = new DataAccess())

            {

                dataSet = dataAccess.ExecSPReturnDS(DMConstants.SPGetCases, new SqlParameter[] { new SqlParameter(DMConstants.FieldRequestorID, username) });

            }



            if (dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows.Count > 0)

            {

                foreach (DataRow row in dataSet.Tables[0].Rows)

                {

                    Case crmCase = ParseRow(row);

                    cases.Add(crmCase);

                }

            }



            return cases.ToArray();

        }



        /// <summary>Returns an or a null value depending on the value of the object sent by param</summary>

        /// <param name="caseType">The object to be evaluated</param>

        /// <returns>The object or null value</returns>

        private static object IsNullOrEmpty(object o)

        {

            if (o == null || (o is string && ((string)o).Length == 0))

                return System.DBNull.Value;

            else

                return o;

        }



        /// <summary>Parses a datarow to a Case</summary>

        /// <param name="row">The datarow to be parsed</param>

        /// <returns>A Case object with the information stored in the datarow</returns>

        private static Case ParseRow(DataRow row)

        {

            Case crmCase = new Case();



            crmCase.ID = row[DMConstants.FieldCaseID].ToString();

            crmCase.Type = new CaseType();

            crmCase.Type.CaseTypeId = (int)row[DMConstants.FieldCaseTypeID];

            //crmCase.Type.CaseTypeCode = (string)row["CaseTypeCode"];

            crmCase.Type.CaseTypeTitle = (string)row[DMConstants.FieldCaseTypeTitle];

            crmCase.Status = new CaseStatus();

            crmCase.Status.CaseStatusId = (int)row[DMConstants.FieldCaseStatusID];

            crmCase.Status.CaseStatusTitle = (string)row[DMConstants.FieldCaseStatusTitle];

            crmCase.RoleType = new CaseRoleType();

            crmCase.RoleType.CaseRoleTypeId = (int)row[DMConstants.FieldCaseRoleTypeID];

            crmCase.RoleType.CaseRoleTypeTitle = DMConstants.FieldCivilServant;



            crmCase.Title = row[DMConstants.FieldTitle].ToString();

            if (row[DMConstants.FieldDescription] != System.DBNull.Value)

                crmCase.Description = row[DMConstants.FieldDescription].ToString();

            if (row[DMConstants.FieldDueDate] != System.DBNull.Value)

                crmCase.DueDate = Convert.ToDateTime(row[DMConstants.FieldDueDate]);

            if (row[DMConstants.FieldRequestorID] != System.DBNull.Value)

                crmCase.RequestorID = row[DMConstants.FieldRequestorID].ToString();

            if (row[DMConstants.FieldRequestorName] != System.DBNull.Value)

                crmCase.RequestorName = row[DMConstants.FieldRequestorName].ToString();

            if (row[DMConstants.FieldRequestorEmail] != System.DBNull.Value)

                crmCase.RequestorEmail = row[DMConstants.FieldRequestorEmail].ToString();

            if (row[DMConstants.FieldRequestorAddress] != System.DBNull.Value)

                crmCase.RequestorAddress = row[DMConstants.FieldRequestorAddress].ToString();

            if (row[DMConstants.FieldRequestorCity] != System.DBNull.Value)

                crmCase.RequestorCity = row[DMConstants.FieldRequestorCity].ToString();

            if (row[DMConstants.FieldRequestorPostalCode] != System.DBNull.Value)

                crmCase.RequestorPostalCode = row[DMConstants.FieldRequestorPostalCode].ToString();

            if (row[DMConstants.FieldRequestorPhone] != System.DBNull.Value)

                crmCase.RequestorPhone = row[DMConstants.FieldRequestorPhone].ToString();

            if (row[DMConstants.FieldFormXML] != System.DBNull.Value)

                crmCase.FormXml = row[DMConstants.FieldFormXml].ToString();

            if (row[DMConstants.FieldExternalDocumentRef] != System.DBNull.Value)

                crmCase.ExternalDocumentRef = row[DMConstants.FieldExternalDocumentRef].ToString();

            if (row[DMConstants.FieldOwnerID] != System.DBNull.Value)

                crmCase.OwnerID = row[DMConstants.FieldOwnerID].ToString();

            crmCase.LastModified = Convert.ToDateTime(row[DMConstants.FieldLastModified]);

            crmCase.CreatedDate = Convert.ToDateTime(row[DMConstants.FieldCreatedDate]);



            return crmCase;

        }



    }

}