Guest User

Guest User

  • Tech Writer
  • 529
  • 41.1k

Databind in datagirdview But Search Problem fix It Please

Apr 30 2022 10:50 AM

I Have 2 properties dtp_Year & dtp_Quarter  

than which i select Quarter Value 1 than get correct data 

This My Code What i Worng In Code Tell Me And send me right Code

private DataTable QuarterlyDayWiseAttendanceReport()
{

    SqlCommand cmd = new SqlCommand("UIPKGREPORTING_spFetchQuarterlyDayWiseAttendanceReport", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@inVolunteerID", SqlDbType.Int).Value = 0;
    cmd.Parameters.AddWithValue("@inType", drp_Type.Text);
    cmd.Parameters.AddWithValue("@inShift", drp_Shift.Text);
    cmd.Parameters.AddWithValue("@inFromDateTime", SqlDbType.Int).Value  = Convert.ToInt32(dtp_Year.Text =="yyyy");
    cmd.Parameters.AddWithValue("@inToDateTime", dtp_ToDate.Value);
    cmd.Parameters.Add("@inPoints", SqlDbType.Int).Value = -1;
    cmd.Parameters.Add("@inQuarter", SqlDbType.Int).Value = Convert.ToInt32(dtp_Quarter.Text =="1");   
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataSet dt = new DataSet();
    sda.Fill(dt, "abc");
    dataGridView1.DataSource = dt.Tables["abc"].DefaultView;
    return dt.Tables["abc"];
}

Store Proc Code

USE [BiometricDB]
GO
/****** Object:  StoredProcedure [dbo].[UIPKGREPORTING_spFetchQuarterlyDayWiseAttendanceReport]    Script Date: 04/30/2022 15:49:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================
-- Author:        Nazim Nizar Ali
-- Create date: 10-Jan-2016
-- Description:    returns volunteer quarterly day wise attendance report
-- ================================================
ALTER PROCEDURE [dbo].[UIPKGREPORTING_spFetchQuarterlyDayWiseAttendanceReport]
    @inVolunteerID int,
    @inType varchar(100),
    @inShift varchar(100),
    @inFromDateTime datetime,
    @inToDateTime datetime,
    @inPoints int,
    @inQuarter int
AS
DECLARE @varYear int,
        @varQuarterMonth1 int,
        @varQuarterMonth2 int,
        @varQuarterMonth3 int
BEGIN
    SET NOCOUNT ON;
    
    SET @varYear = DATEPART(YYYY, @inFromDateTime);
    
    if (@inQuarter = 1) 
    begin
        SET @varQuarterMonth1 = 1;
        SET @varQuarterMonth2 = 2;
        SET @varQuarterMonth3 = 3;
    end
    else if (@inQuarter = 2)
    begin
        SET @varQuarterMonth1 = 4;
        SET @varQuarterMonth2 = 5;
        SET @varQuarterMonth3 = 6;
    end
    else if (@inQuarter = 3)
    begin
        SET @varQuarterMonth1 = 7;
        SET @varQuarterMonth2 = 8;
        SET @varQuarterMonth3 = 9;
    end
    else
    begin
        SET @varQuarterMonth1 = 10;
        SET @varQuarterMonth2 = 11;
        SET @varQuarterMonth3 = 12;
    end
    
    select  ROW_NUMBER() over (order by pos.sortorder, temp.VolunteerName) as SNo,
            pos.positionname as Position,
            temp.VolunteerName as "Volunteer Name",
            temp.M1Days as "M1 Morning Days",
            temp.E1Days as "M1 Evening Days",
            temp.Ex1Days as "M1 Extra Days",
            temp.Total1Days as "M1 Total Days",
            temp.M2Days as "M2 Morning Days",
            temp.E2Days as "M2 Evening Days",
            temp.Ex2Days as "M2 Extra Days",
            temp.Total2Days as "M2 Total Days",
            temp.M3Days as "M3 Morning Days",
            temp.E3Days as "M3 Evening Days",
            temp.Ex3Days as "M3 Extra Days",
            temp.Total3Days as "M3 Total Days",
            temp.MTotal as "Morning Total Days",
            temp.ETotal as "Evening Total Days",
            temp.ExTotal as "Extra Total Days",
            temp.GrandTotal as "Grand Total",
            temp.TotalDuration as "Total Duration"
    from
    (select volunteer.volunteerpositionid as Position,
            volunteer.displayfullname as VolunteerName,
            COUNT(CASE when attendance.shift = 'Morning' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as M1Days,
            COUNT(CASE when attendance.shift = 'Evening' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as E1Days,
            COUNT(CASE when attendance.shift = 'Special' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as Ex1Days,
            COUNT(CASE when DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as Total1Days,
            COUNT(CASE when attendance.shift = 'Morning' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as M2Days,
            COUNT(CASE when attendance.shift = 'Evening' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as E2Days,
            COUNT(CASE when attendance.shift = 'Special' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as Ex2Days,
            COUNT(CASE when DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as Total2Days,
            COUNT(CASE when attendance.shift = 'Morning' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as M3Days,
            COUNT(CASE when attendance.shift = 'Evening' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as E3Days,
            COUNT(CASE when attendance.shift = 'Special' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as Ex3Days,
            COUNT(CASE when DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as Total3Days,
            COUNT(CASE when attendance.shift = 'Morning' then attendance.points end) as MTotal,
            COUNT(CASE when attendance.shift = 'Evening' then attendance.points end) as ETotal,
            COUNT(CASE when attendance.shift = 'Special' then attendance.points end) as ExTotal,
            COUNT(attendance.points) as GrandTotal,
            --SUM(isnull(CAST(duration/60.0 as DECIMAL(16, 2)), 0)) as TotalDuration
            CAST( ( SUM( isnull( duration, 0 ) )/60 ) + ( ( SUM( isnull( duration, 0 ) )%60 )/100.0 ) as DECIMAL(16, 2) ) as TotalDuration
    from tblvolunteer volunteer
    left outer join tblattendancelog attendance
        on attendance.volunteerid = volunteer.volunteerid
        and attendance.type = 'Present'
    --where (tblattendancelog.volunteerid = @inVolunteerID or @inVolunteerID = 0)
        --and (type = @inType or @inType = '< All >')
        --and (shift = @inShift or @inShift = '< All >')
        and (attendance.dutydatetime between @inFromDateTime and @inToDateTime)
        --and (points = @inPoints or @inPoints = -1)
    where volunteer.isresign = 0
    group by volunteer.volunteerpositionid, volunteer.displayfullname
    ) temp
    inner join tblvolunteerposition pos
    on temp.Position = pos.volunteerpositionid;
    
END

 


Answers (1)