CEL_Payroll/Payroll.Service/Survey/DA/SurveyResultDA.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Payroll.BO;
using System.Data;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
   internal class SurveyResultDA
    {
        #region  Constructor

        private SurveyResultDA() { }

        #endregion

        #region  Insert function
     
   
        internal static void Insert(TransactionContext tc, SurveyResult item)
        {
            tc.ExecuteNonQuery("INSERT INTO SurveyResult(SurveyID, EmployeeID, AnswerID, QuestionID, IsCorrect, ForEmployeeID, Weight)" +
            " VALUES(%n, %n, %n, %n, %b, %n, %n)", item.SurveyID.Integer, item.EmployeeID, item.AnswerID, item.QuestionID, item.IsCorrect, item.ForEmployeeID, item.Weight);
        }
        public static void InsertSurveyResult(TransactionContext tc, SurveyResult oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO SurveyResult(SurveyId, EmployeeId, QuestionId, AnswerId, IsCorrect,ForEmployeeID)" +
            " VALUES(%n, %n, %n, %n, %n, %n)", oItem.SurveyID.Integer, oItem.EmployeeID, oItem.QuestionID, oItem.AnswerID, oItem.IsCorrect, oItem.ForEmployeeID);
        }
       

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, SurveyResult item)
        {
            tc.ExecuteNonQuery("UPDATE SurveyResult SET EmployeeID=%n, AnswerID=%n, QuestionID=%n, IsCorrect=%b, ForEmployeeID=%n, Weight=%n" +
            " WHERE SurveyID=%n", item.EmployeeID, item.AnswerID, item.QuestionID, item.IsCorrect, item.ForEmployeeID, item.Weight, item.SurveyID.Integer);
        }
        public static void UpdateSurveyResult(TransactionContext tc, SurveyResult oItem)
        {
            tc.ExecuteNonQuery("UPDATE SurveyResult SET IsCorrect=%n,ForEmployeeID=%n" +
            " WHERE SurveyId=%n AND EmployeeId=%n AND QuestionId=%n AND AnswerId=%n", oItem.IsCorrect, oItem.ForEmployeeID, oItem.SurveyID.Integer, oItem.EmployeeID, oItem.QuestionID, oItem.AnswerID);
        }
        #endregion

        #region Get Function

        public static IDataReader GetSurveyResult(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM SurveyResult");
        }
        public static IDataReader GetSurveyResult(TransactionContext tc, int nSurveyId)
        {
            return tc.ExecuteReader("SELECT * FROM SurveyResult WHERE SurveyId=%n", nSurveyId);
        }
        public static IDataReader GetSurveyResult(TransactionContext tc, int nSurveyId, string EmployeeIds)
        {
            return tc.ExecuteReader("SELECT * FROM SurveyResult WHERE SurveyId=%n AND EmployeeId in (" + EmployeeIds + ") and ForEmployeeID=0", nSurveyId);
        }
        public static IDataReader GetSurveyResult(TransactionContext tc, int surveyId, int employeeId, int questionID)
        {
            return tc.ExecuteReader("SELECT * FROM SurveyResult WHERE SurveyId=%n AND EmployeeId =%n and QuestionId=%n ", surveyId, employeeId, questionID);
        }
        public static IDataReader GetSurveyResult(TransactionContext tc, int nSurveyId, string EmployeeIds, int forEmpId)
        {
            return tc.ExecuteReader("SELECT * FROM SurveyResult WHERE SurveyId=%n AND EmployeeId in (%s) and ForEmployeeID=%n", nSurveyId, EmployeeIds, forEmpId);
        }

        public static DataSet GetSurveyResultReport(TransactionContext tc, int surveyID)
        {
            string query = string.Empty;
            DataSet dataSet = null;

            query = "select P.*,Q.HitCount from " +
                    "(select Ques.QuestionID,Ques.QuestionBody,QA.AnswerID,QA.AnswerBody from questionAnswer QA inner join Question Ques on QA.QuestionID=Ques.QuestionID)P " +
                    "inner join " +
                    "(select SR.QuestionID,SR.AnswerID,count(Iscorrect)as HitCount,SR.SurveyID from SurveyResult SR group by SR.QuestionID,SR.AnswerID,SR.SurveyID)Q " +
                    "on P.QuestionID=Q.QuestionID " +
                    "and P.AnswerID=Q.AnswerID " +
                    "and P.QuestionID in(select QuestionID from surveyQuestion where surveyid=%n) " +
                    "union all " +
                    "select P.*,0 as HitCount from " +
                    "(select Ques.QuestionID,Ques.QuestionBody, QA.AnswerID,QA.AnswerBody from questionAnswer QA inner join Question Ques on QA.QuestionID=Ques.QuestionID)P " +
                    "where P.AnswerID not in( select distinct SR.AnswerID from surveyResult SR where SR.surveyID=%n) " +
                    "and P.QuestionID in(select QuestionID from surveyQuestion where surveyid=%n) " +
                    "order by P.answerid";
            dataSet = tc.ExecuteDataSet(query, surveyID, surveyID, surveyID);

            return dataSet;
        }

        #endregion

        #region  Delete function

        public static void DeleteSurveyResult(TransactionContext tc, int nSurveyId, int nEmployeeId, int nQuestionId, int nAnswerId)
        {
            tc.ExecuteNonQuery("DELETE FROM [SurveyResult] WHERE SurveyId=%n AND EmployeeId=%n AND QuestionId=%n AND AnswerId=%n", nSurveyId, nEmployeeId, nQuestionId, nAnswerId);
        }
        public static void DeleteSurveyResult(TransactionContext tc, int nSurveyId, int nEmployeeId, int nQuestionId)
        {
            tc.ExecuteNonQuery("DELETE FROM [SurveyResult] WHERE SurveyId=%n AND EmployeeId=%n AND QuestionId=%n ", nSurveyId, nEmployeeId, nQuestionId);
        }
        public static void UpdaeAnswerCount(TransactionContext tc, int surveyId, int QuestionId, int AnswerId)
        {
            tc.ExecuteNonQuery("Update SurveyAnswerCount set HitCount=HitCount+1 where SurveyId=%n,QuestionId=%n,AnswerId=%n", surveyId, QuestionId, AnswerId);
        }
        public static void DeleteSurveyResult(TransactionContext tc, int nSurveyId, int nEmployeeId)
        {
            tc.ExecuteNonQuery("DELETE FROM [SurveyResult] WHERE SurveyId=%n AND EmployeeId=%n", nSurveyId, nEmployeeId);
        }
        #endregion
    }
}