如何显示在ASP直放站项目模板模式?(how to show pattern in asp-Repe

2019-10-30 04:15发布

我有一个网页在我的asp.net项目中,我想展示员工的出勤。 当存在时P应该显示和不存在时,然后A和假日H应在中继器中显示。 现在我的网页上我有2个文本框,通过它我进入年份和月份和该月我想参加。 我的数据库表如下所示。

(1)日历

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

等等 。 这里1代表工作日月和0的是使用这种模式周六和周日的上午,因为在我的网页之一是越来越复选框检查(周六,周日),并选中了别人,所以我知道这些都是假期

(2)表出席

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

EmpID  EmpName  and so on
1       Joe
2      Sandra              

现在来这个问题我的网页上,当我进入的年份和月份我要显示页眉的日期数字代表该月的日期中继。 现在,如果一个月有30天,然后30号所示。 另一中继器使用具有显示格式P上的出勤,A,H如上告诉

我的中继器看起来像这样

<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>

而后面的代码是

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();
        }
    }

在后面的代码我得到当月的天数,然后将其与具有31日期和表进行比对,从这个逻辑我dsplay我的号码,并在底部使用中继器2显示我的EmpID的离开,现在他们旁边下面的日期数字我想说明的上座率。 有人能告诉我如何做到这一点。 在我的出勤表中的列PresendtDays是空的,但我不知道如何使用它。 请帮我出我已经从很多时间尝试这一点,这就是为什么我贴我的完整代码,使一些人会帮助我。 寻找一个早期反应。 提前致谢 !!

Answer 1:

由于数据库的非常规设计,我不得不做一些重大的数据操作,使这项工作。 话虽这么说,这是我提出的解决方案。

取而代之的是SQL语句的"SELECT EmpID from empinfo " ,您将需要执行三个附加查询:

  1. 检索的信息Calendar表:

    • SELECT CalendarID,WorkingDays从日程表其中year = [年份]和Month = [月]
    • 这将返回一个表,如下所示:

  2. 检索使用CalendarID日历表信息:

    • 选择的EmpID,EmpName FROM员工
    • 这将返回一个表,如下所示:

  3. 检索考勤表信息,使用CalendarID第一个查询。

    • SELECT的EmpID,DAY(LeaveDate)AS LeaveDayOfMonth FROM考勤WHERE CalendarID = [CALENDAR ID]
    • 这将返回一个表,如下所示:

一旦你做到了这一点,更换你的第二个中继器( Repeater2以下两个中继器)。 的,第一中继器(rptAttendance)列出了每个员工,每月为员工的每一天第二中继器(rptAttendanceCode)名单。 (请注意,我们连接到中继器的OnItemDataBound事件,以后会更多):

    <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>

现在,这是乐趣的开始!

首先,你需要创建一个保存员工的名字和他/她每月的每一天出勤的数据结构。 我们将使用WorkingDays领域为我们的基线,并与每位员工的出勤(从考勤表所)追加它。

//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");

与相应的字母替换日类型代码

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

转换成一个阵列,以使我们可以通过它遍历

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

现在,我们将遍历每个员工的记录,并为每个员工创建一个数据结构。 然后,我们将遍历每个员工的一天,并与他们没有工作的日子里更新自己的daylist集合。

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);
}

下面是每个直放站的ItemDataBound事件处理程序:

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();
    }
}

以下是全部的代码隐藏:

    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();
        }
    }


文章来源: how to show pattern in asp-Repeater item template?