how to show pattern in asp-Repeater item template?

Question

I have a page in my asp.net project where I want to show the attendance of the employees. When present P should be shown and when absent then A and on holidays H should be shown in the repeater. Now on my web page I have 2 textboxes through which I enter the year and the month and for that month I want to get the attendance. My database tables are as follows.

(1) Calender

CalederID  Year  Month     WorkingDays
1          2013  January   1111001111100111110011111001111
2          2013  February  1001111100111110011111001111

and so on . Here 1 represent the working days in the month and 0's are Saturday's and Sunday's am using this pattern because on one of my page am getting checkboxes checked for (sat and sun) and unchecked for others so I know that these are holidays

(2) Attendance Table

AttendanceID    EmpID PresentDays CalenderID  LeaveDate
1                1      Null         1        2013-01-14
2                1      Null         2        2013-02-15
3                1      Null         4        2013-04-11
4                3      Null         6        2013-06-26

(3) EmpInfo Table

EmpID  EmpName  and so on
1       Joe
2      Sandra              

Now coming to the problem on my web page when I enter the year and month I want to show the repeater with headers as Date Numbers which represent the dates of that month. Now if the month has 30 days then 30 numbers are shown. Another repeater is used which has to show the attendance in the format P,A,H as told above

My Repeaters look like this

<table class="table1" >
        <asp:Repeater ID="Repeater1" runat="server">
           <HeaderTemplate>
               <tr>
                    <td>Employee ID</td>
           </HeaderTemplate>
           <ItemTemplate>
                    <td><asp:Label ID="lbldates" runat="server" Text='<%# Eval("Dates") %>' ></asp:Label></td>
            </ItemTemplate>
            <FooterTemplate>
                    <td>TOTAL</td></tr>
               <tr>
           </FooterTemplate>           
       </asp:Repeater>
           <asp:Repeater id="rptAttendance" runat="server" OnItemDataBound="rptAttendance_ItemDataBound">
        <ItemTemplate>
            <tr>
                <td><asp:Label ID="lblEmpName" runat="server" /></td>
                <asp:Repeater ID="rptAttendanceCode" runat="server" OnItemDataBound="rptAttendanceCode_ItemDataBound" >
                    <ItemTemplate><td><asp:Label ID="lblAttendanceCode" runat="server" /></td></ItemTemplate>
                </asp:Repeater>
                </tr>
        </ItemTemplate>
    </asp:Repeater>
        </table>

and the code behind is

protected void Page_Load(object sender, EventArgs e)
        {


        }

        public void search(object sender, EventArgs e)
        {
            string cnnString = "Server=localhost;Port=3307;Database=leavesystem;Uid=root;Pwd=ashish";
            MySqlConnection cnx = new MySqlConnection(cnnString);
            cnx.Open();
            string cmdText1 = "SELECT DAY(LAST_DAY(CAST(CONCAT('" + year.Value + "', '-', MONTH(STR_TO_DATE('" + month.Value + "', '%M')), '-', 1) AS DATE))) ";
            MySqlCommand cmd1 = new MySqlCommand(cmdText1, cnx);
            MySqlDataAdapter adapter1 = new MySqlDataAdapter();
            DataSet ds1 = new DataSet();
            adapter1.SelectCommand = cmd1;
            adapter1.Fill(ds1);
            DataRow dr;
            dr = ds1.Tables[0].Rows[0];
            string value = dr[0].ToString();

            string cmdText2 = "SELECT Dates from temp where Dates <= " + value + "  ";
            MySqlCommand cmd2 = new MySqlCommand(cmdText2, cnx);
            MySqlDataAdapter adapter2 = new MySqlDataAdapter();
            DataSet ds2 = new DataSet();
            adapter2.SelectCommand = cmd2;
            adapter2.Fill(ds2);
            DataTable dt = ds2.Tables[0];
            Repeater1.DataSource = dt;
            Repeater1.DataBind();


            string cmdText3 = "SELECT CalenderID, WorkingDays from calender where Year = '" + year.Value + "' and Month = '" + month.Value + "'   ";
            MySqlCommand cmd3 = new MySqlCommand(cmdText3, cnx);
            MySqlDataAdapter adapter3 = new MySqlDataAdapter();
            DataSet ds3 = new DataSet();
            adapter3.SelectCommand = cmd3;
            adapter3.Fill(ds3);
            DataTable calender = ds3.Tables[0];
            DataRow dr3;
            dr3 = ds3.Tables[0].Rows[0];
            string CalenderID = dr3[0].ToString();

            string cmdText4 = "SELECT EmpID,EmpName from empinfo ";
            MySqlCommand cmd4 = new MySqlCommand(cmdText4, cnx);
            MySqlDataAdapter adapter4 = new MySqlDataAdapter();
            DataSet ds4 = new DataSet();
            adapter4.SelectCommand = cmd4;
            adapter4.Fill(ds4);
            DataTable employees = ds4.Tables[0];

            string cmdText5 = "SELECT EmpID,DAY(LeaveDate) AS LeaveDayOfMonth from attendance where CalenderID = '" + CalenderID + "'   ";
            MySqlCommand cmd5 = new MySqlCommand(cmdText5, cnx);
            MySqlDataAdapter adapter5 = new MySqlDataAdapter();
            DataSet ds5 = new DataSet();
            adapter5.SelectCommand = cmd5;
            adapter5.Fill(ds5);
            DataTable attendance = ds5.Tables[0];

            List<Tuple<string, string[]>> info = new List<Tuple<string, string[]>>();
            int calendarID = calender.Rows[0].Field<int>("CalenderID");
            string days = calender.Rows[0].Field<string>("WorkingDays");
            days = days.Replace("1", "P");
            days = days.Replace("0", "H");
            string[] daysList = days.Select(d => d.ToString()).ToArray();
            int present = 0;
        int holidays = 0;

        for (int i = 0; i < daysList.Length; ++i)
        {
            if (daysList[i] == "P")
                present++;

            if (daysList[i] == "H")
                holidays++;

        }

        int working = (monthdays - holidays);
        string total = (present + "/" + working);



        Tuple<string, string[],string> employeeAttendance = null;
        foreach (DataRow employee in employees.Rows)
        {
            employeeAttendance = new Tuple<string, string[],string>(employee.Field<string>("EmpName"), daysList,total);


            foreach (DataRow absentDay in attendance.AsEnumerable().Where(a => a.Field<int>("EmpID") == employee.Field<int>("EmpID")))
            {
                var leaveDay = absentDay.Field<Int64>("LeaveDayOfMonth");
                int leaveDay1 = Convert.ToInt16(leaveDay);
                leaveDay1 = leaveDay1 - 1;
                employeeAttendance.Item2[leaveDay1] = "A";
            }


            info.Add(employeeAttendance);
        }
        this.rptAttendance.DataSource = info;
        this.rptAttendance.DataBind();
        }


    protected void rptAttendance_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            Tuple<string, string[],string> info = (Tuple<string, string[],string>)e.Item.DataItem;
            ((Label)e.Item.FindControl("lblEmpName")).Text = info.Item1;
            ((Label)e.Item.FindControl("lbltotal")).Text = info.Item3;
            Repeater attendanceCode = (Repeater)e.Item.FindControl("rptAttendanceCode");
            attendanceCode.DataSource = info.Item2;
            attendanceCode.DataBind();
        }
    }

    protected void rptAttendanceCode_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            ((Label)e.Item.FindControl("lblAttendanceCode")).Text = e.Item.DataItem.ToString();
        }
    }

In code behind I get the number of days in the month then compare it with a table which has 31 dates and from that logic I dsplay my numbers and on the bottom left using repeater 2 to show my EmpID's and now beside them below the date numbers I want to show the attendance. Can some one tell me how to do this. The PresendtDays columns in my attendance table is empty but I dont know how to use that. Please help me out I have been trying this from many hours and that is why I posted my complete code so that some one would help me. Looking for an early response. Thanks in advance !!

Answer 1

Due to the unconventional design of your database, I had to do some major data manipulation to make this work. That being said, here is my proposed solution.

Instead of the SQL statement "SELECT EmpID from empinfo ", you will need to perform three additional queries:

1. Retrieve info from the Calendar table:

   • SELECT CalendarID, WorkingDays FROM Calendar WHERE Year = [YEAR] and Month = [MONTH]
   • This will return a table that looks like this:

   

2. Retrieve info from the Calendar table using the CalendarID:

   • SELECT EmpID, EmpName FROM Employees
   • This will return a table that looks like this:

   

3. Retrieve info from Attendance table, using the CalendarID from the first query.

   • SELECT EmpID, DAY(LeaveDate) AS LeaveDayOfMonth FROM Attendance WHERE CalendarID = [CALENDAR ID]
   • This will return a table that looks like this:

   

Once you have done this, replace your second repeater (Repeater2) with the following TWO repeaters. The, first repeater (rptAttendance) lists each employee, the second repeater (rptAttendanceCode) list each day of the month for the employee. (Note, we are connecting to the repeaters's OnItemDataBound event, more on this later):

    <asp:Repeater id="rptAttendance" runat="server" OnItemDataBound="rptAttendance_ItemDataBound">
        <ItemTemplate>
            <tr>
                <td><asp:Label ID="lblEmpName" runat="server" /></td>
                <asp:Repeater ID="rptAttendanceCode" runat="server" OnItemDataBound="rptAttendanceCode_ItemDataBound" >
                    <ItemTemplate><td><asp:Label ID="lblAttendanceCode" runat="server" /></td></ItemTemplate>
                </asp:Repeater>
                </tr>
        </ItemTemplate>
    </asp:Repeater>

Now, this is where the fun starts!

First, you need to create a data structure that hold the employee name and his/her attendance for each day of the month. We will use the WorkingDays field for our base line and append it with each employee's attendance (taken from the Attendance table).

//This list (info) holds the employee's name in the first string, and their attendance in the string array
List<Tuple<string, string[]>> info = new List<Tuple<string, string[]>>();
int calendarID = calendar.Rows[0].Field<int>("CalendarID");
string days = calendar.Rows[0].Field<string>("WorkingDays");

Replace the day-type code with the corresponding letter

days = days.Replace("1", "P");
days = days.Replace("0", "H");

Convert this into an an array so we can iterate over it

string[] daysList = days.Select(d => d.ToString()).ToArray();

Now we will iterate over each employee record and create a data structure for each employee. Then we will iterate over each employee's day off and update their daylist collection with the days that they were not at work.

foreach (DataRow employee in employees.Rows)
{
    employeeAttendance = new Tuple<string, string[]>(employee.Field<string>("EmpName"), daysList);
    foreach (DataRow absentDay in attendance.AsEnumerable().Where(a => a.Field<int>("EmpID") == employee.Field<int>("EmpID")))
{
        employeeAttendance.Item2[absentDay.Field<int>("LeaveDayOfMonth") - 1] = "A";
}
    info.Add(employeeAttendance);
}

Here are each repeater's ItemDataBound event handlers:

protected void rptAttendance_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        Tuple<string, string[]> info = (Tuple<string, string[]>)e.Item.DataItem;
        ((Label)e.Item.FindControl("lblEmpName")).Text = info.Item1;
        Repeater attendanceCode = (Repeater)e.Item.FindControl("rptAttendanceCode");
        attendanceCode.DataSource = info.Item2;
        attendanceCode.DataBind();
    }
}

protected void rptAttendanceCode_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        ((Label)e.Item.FindControl("lblAttendanceCode")).Text = e.Item.DataItem.ToString();
    }
}

Here is the code-behind in its entirety:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            List<Tuple<string, string[]>> info = new List<Tuple<string, string[]>>();
            int calendarID = calendar.Rows[0].Field<int>("CalendarID");
            string days = calendar.Rows[0].Field<string>("WorkingDays");
            days = days.Replace("1", "P");
            days = days.Replace("0", "H");
            string[] daysList = days.Select(d => d.ToString()).ToArray();
            Tuple<string, string[]> employeeAttendance = null;
            foreach (DataRow employee in employees.Rows)
            {
                employeeAttendance = new Tuple<string, string[]>(employee.Field<string>("EmpName"), daysList);
                foreach (DataRow absentDay in attendance.AsEnumerable().Where(a => a.Field<int>("EmpID") == employee.Field<int>("EmpID")))
                {
                    employeeAttendance.Item2[absentDay.Field<int>("LeaveDayOfMonth") - 1] = "A";

                }
                info.Add(employeeAttendance);
            }
            this.rptAttendance.DataSource = info;
            this.rptAttendance.DataBind();
        }
    }

    protected void rptAttendance_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            Tuple<string, string[]> info = (Tuple<string, string[]>)e.Item.DataItem;
            ((Label)e.Item.FindControl("lblEmpName")).Text = info.Item1;
            Repeater attendanceCode = (Repeater)e.Item.FindControl("rptAttendanceCode");
            attendanceCode.DataSource = info.Item2;
            attendanceCode.DataBind();
        }
    }

    protected void rptAttendanceCode_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            ((Label)e.Item.FindControl("lblAttendanceCode")).Text = e.Item.DataItem.ToString();
        }
    }