Web-based Day Rental Calendar

• Download demo project - 76.7 Kb

Requirements

I was asked to make an online calendar for summer cottage rentals, with the following feaures:

• month per page,
• if one or more units is rented for a certain day, show all units in the list for that day,
• ability to control the display order of rental units and add/subtract from the rental list,
• color-coded entries lining up across the page,
• MS Access database backend, and
• password protected admin pages.

Background

Hoping to find a shareware to meet my requirements, I found Paul Apostolos' excellent article, "Building an Event Calendar Web Application". This became my starting point.

Database

In the tbl_place table, the place field holds the full rental unit name used in the dropdown listbox on the New and EditDel page. The abbre field is the rental header abbreviation (two letters) used for display. In the Datasheet view, put your own place and abbre values. dsplOrder is the display order of the cottages for each day. Change the numbering here to determine the order on the calendar. Leave blank (null) to omit that unit from being displayed.

Using the code

As Paul's article points out, each day on the calendar is rendered separately, so it's better to store whatever you need from the database once for the page you are displaying. The myCollection and MyDates are from Paul's article. I have added the last three arrays to keep track of the display requirements. These stores are filled and emptied for each day being displayed in Calendar1_DayRender().

//holds MyDate structs. 
//See function: void GetDBItems()
public ArrayList myCollection;

public struct MyDates
{
    //tbl_events.ID
    public int _ID;
    //tbl_events.date
    public DateTime _date;
    //tbl_events.cust
    public string _renter;
    //tbl_events.place_ID
    public long  _place;
    //tbl_place.dsplOrder
    public long _dsplOdr;
}

//abbreviations of rental items
public ArrayList rentalHeaders;

//displayed rental item that has a renter
public ArrayList placeDisplayOrder;

//ID of place is different than display order
public ArrayList placeID;

EditDel.aspx

Calendar1_DayRender(): first, GetDBItems() fills the myCollection array with MyDates structs (database records) for the month we are displaying. ORDER BY tbl_events.date and tbl_place.dsplOrder so a DataReader can make one pass and get each day with the correct cottage display order. Get the two digit rental headers. In the foreach loop, we check the date. Now, build an HTML line for that day.

private void Calendar1_DayRender(object sender, 
        System.Web.UI.WebControls.DayRenderEventArgs e)
{
    DateTime dayHold;
    dayHold=Calendar1.TodaysDate.AddYears(-40);
    bool dayTextHasChanged = false;
    temp = new StringBuilder();
    //display order
    placeDisplayOrder = new ArrayList();
    
    if(myCollection==null)
    {
        GetDBItems();
        GetRentalHeaders();
    }
    foreach(MyDates Item in myCollection)
    {
        if(dayHold.CompareTo(Item._date)==0)
        {
            if(dayTextHasChanged==true)
            //start a new day
            {
                return;
            }
            dayHold=Item._date;
        }
    
        if(e.Day.Date.ToShortDateString()==Item._date.ToString("d"))
        {
            //a list of cottages with renters for the day we are on
            placeDisplayOrder.Add((int)Item._dsplOdr);
            //start by putting the span
            temp.Append("<span style='font-family:Arial; " + 
                        "font-weight:bold;font-size:12px;'>");

            //See Display() below
            Display(sequence, (int)Item._dsplOdr-1, Item._renter);
            sequence++;
            dayTextHasChanged = true;
        }//end day compare
    }//end foreach

    if(dayTextHasChanged == true)
    {
        //finally print any remaining 
        //rental headers after the last booking
        //See DisplayLast() below
        DisplayLast(sequence, (int)placeD
          isplayOrder[placeDisplayOrder.Count-1]);
        temp.Append("</font>");
        temp.Append("</span>");

        e.Cell.Controls.Add(new LiteralControl(temp.ToString()));
    //reset sequence
        sequence=0;
    }
}//End Calendar1_DayRender

Display(): adds to the HTML line we are building for the current day. The DataReader is going row by row through the database. When we start a new day, the value of sequence will be 0. pl is the cottage display number. Suppose that number is 3. The first for loop prints the rental headers 0, 1, and 2. When i hits 3, we print the renter. If it's not the first cottage with a renter for the current day, we go to the else statement. In the placeDisplayOrder array, if the last number minus the second to last number in the array isn't equal to 1, then we have to print the rentalHeaders in between those two cottages.

void Display(int sq, int pl, string renter)
{
    string textColor;
    //first time build string up to and including the first booking
    if(sq==0)
    {
        for(int i=sq; i<=pl; i++)
        {
            textColor = GetTextColor(i);
            temp.Append("<font color='"+textColor+"'>");
            temp.Append(rentalHeaders[i]);
            if(i==pl)
            {
                temp.Append(renter);
            }
            temp.Append("</font>");
        }
    }
    else
    {
        //if the index of the current place 
        //minus index of last place is not one
        if((int)placeDisplayOrder[sq]-(int)placeDisplayOrder[sq-1]!=1)
        {
            //print rental headers between the two bookings
            for(int i=(int)placeDisplayOrder[sq-1]; i<=pl; i++)
            {
                textColor = GetTextColor(i);
                temp.Append("<font color='"+textColor+"'>");
                temp.Append(rentalHeaders[i]);
                if(i==pl)
                {
                    temp.Append(renter);
                }
                temp.Append("</font>");
            }
        }
        else
        {
            //otherwise just print the next booking
            textColor = GetTextColor((int)placeDisplayOrder[sq-1]);
            temp.Append("<font color='"+textColor+"'>");
            temp.Append(rentalHeaders[(int)placeDisplayOrder[sq-1]]);
            temp.Append(renter);
            temp.Append("</font>");
        }
    }
}

When the foreach loop in Calendar1_DayRender() hits a new date, we use DisplayLast() to check for any remaining rentalHeaders, and add them to the HTML string for that day. In other words, if there are 6 cottages for rent but the sixth cottage has no renter, we still want to display the rentalHeader for the last cottage.

//Display remaining headers, take the total number 
//of rental units minus the last unit with a booking you
//printed. Use the array of rentalHeaders to print 
//the headers at the end of the list you need.
void DisplayLast(int sq, int pl)
{
    int remainingHeaders = (int)Session["numRentals"]-pl;
    int i=0;
    while(i<remainingHeaders)
    {
        string txtcolor = GetTextColor(pl+i);
        temp.Append("<font color='"+txtcolor+"'>");
        temp.Append(rentalHeaders[pl+i]);
        i++;
    }
}

In the calendar, day numbers are hyperlinks. When you click on one, the Calendar1_SelectionChanged() is called. First, grab all the bookings from that day into a DataReader. Build a hyperlink with a query string of the ID, cottage, and renter. Using the HtmlGenericControl class lets you run regular HTML tags from the server. The selectedday <span> tag is used to display the date. The daydetail_render <span> tag is used to display the hyperlink. The daydetail <div> tag encloses the other two tags, and is used to control when the other two are visible. Here's the HTML code inside the <form> tag:

<div id="daydetail" runat="server" Visible="False">
    <h3>Update or Delete: 
        <span id="selectedday" runat="server"></span>
    </h3>
    <span id="daydetail_render" runat="server"></span>
</div>

Here's the code-behind:

private void Calendar1_SelectionChanged(object sender, System.EventArgs e)
{
    string temp="";
    //reset record updated msg
    lblMsg.Text = "";
    string selDate = "#"+Calendar1.SelectedDate.ToShortDateString()+"#";
    string sql = "SELECT tbl_events.ID, tbl_events.date," + 
                 " tbl_events.cust, tbl_place.place " + 
                 "FROM tbl_events INNER JOIN tbl_place" + 
                 " ON tbl_events.place_ID = tbl_place.ID" + 
    "WHERE [date] = "+selDate+" ORDER BY tbl_place.ID";
    conn = new OleDbConnection(strConn);
    try
    {
        conn.Open();
        cmd = new OleDbCommand(sql, conn);
        dr = cmd.ExecuteReader();
        int i=0;
        if(dr.HasRows)
        {
            while(dr.Read())
            {
                temp +=  "<a href='EditDel.aspx?ID=" +dr["ID"]+"'>" + 
                         dr["place"]+": "+dr["cust"]+ "</a><br>";
                i++;
            }
            daydetail.Visible = true;
            daydetail_render.InnerHtml = temp;
            selectedday.InnerHtml = 
               Calendar1.SelectedDate.ToString("MMM d, yyyy");
            dr.Close();
            conn.Close();
            Calendar1.Attributes.Add("style", calendarPos(i));
        }
    }
    catch
    {
        conn.Close();
    }
}

All the bookings for a selected day appear above the calendar as hyperlinks. In Calendar1.Attributes.Add("style", calendarPos(i));, i is the number of lines of hyperlinks. calendarPos(i) changes the absolute position of the calendar down i times so you can see the hyperlinks.

Here's the display after choosing a day:

Clicking on a hyperlink (School House) tells the server to open the EditDel.aspx (same page you're on), only this time there's a query string attached. Bind() sees the query string: if(Request.QueryString["ID"]!= null) makes visible and populates another calendar control, a drop down box, and a text box with data from the record based on the ID. Calendar1.SelectionMode = (CalendarSelectionMode)0; turns off the day number hyperlinks on the main calendar to prevent the user from choosing another date before either updating or deleting the currently selected day or using the back button (which is the same as cancel). Here's the display after choosing a hyperlink.

When the Update button is clicked, IsAvailable() checks for double bookings and then displays a message.

bool IsAvailable(DateTime date, int place, int id)
{
    bool isAvail = true;
    if(myCollection==null)
    {
        GetDBItems();
    }
    foreach(MyDates Item in myCollection)
    {
        if(id != Item._ID)
        {
            if(Item._date == date && Item._place == place)
            {
                isAvail=false;
            }
        }
    }
    if(isAvail==false)
    {
        lblMsg.Text = "<span style='font-family:Arial; " + 
                      "font-weight:bold;font-size:16px; color:red;'>" + 
                      "This date and cottage is " + 
                      "already booked.</span>";
        return false;
    }
    else
    {
        lblMsg.Text = "<span style='font-family:Arial; " + 
                      "font-weight:bold;font-size:16px; color:green;'>" + 
        "Record updated.</span>";
        return true;
    }
}

Folders

Here is the application in my inetpub/wwwroot/. All the public pages are in the cottrent root folder and don't require login. The admin folder holds the Add.aspx, EditDel.aspx, login.aspx, and the link page admin.aspx. The db folder holds the cottrent.mdb Access database file.

Web.config

The Web.config file in the root folder keeps the connection strings, one for local and one for remote. The local version stays on my machine while the remote version is on the Windows host server. This way, I don't have to change any code or recompile between testing on the local server and uploading to the remote server. The remote string is commented out so this config file is used on my local machine. The code goes between <configuration> and <system.web>:

<appSettings>
   <!--Local Database connection string-->
   <add key="AccessConnStr" value="Provider=Microsoft.Jet.OLEDB.4.0;" + 
        "Data Source=C:\\inetpub\\wwwroot\
         \cottrent\\db\\cottrent.mdb;" />
   <!--Remote Database connection string-->
   <!--<add key="AccessConnStr" value="Provider=Microsoft.Jet.OLEDB.4.0;" + 
        Data Source=c:\\account\\domain\\wwwRoot\\
                    rent\\db\\cottrent.mdb;" />-->
</appSettings>

In my C# code, I declare the string.

protected static string strConn = 
   ConfigurationSettings.AppSettings["AccessConnStr"];

Also, in the root Web.config is the forms login. Set your username and password here.

<authentication mode="Forms" > 
    <forms loginUrl="admin/login.aspx" >
        <credentials passwordFormat="Clear">
          <user name="user" password="password"/>
        </credentials>
    </forms>
</authentication > 

<authorization>
   <allow users="*" /> <!-- Allow all users -->
</authorization>

Another Web.config in the admin folder allows the user named "user" and calls the login form. Everyone else is denied.

<authorization>
  <allow users="user" /> <!-- Allow user -->
  <deny users="*" /> <!-- deny everyone else -->
</authorization>

Printer friendly

Here's the HTML for dayrental.aspx generated by Visual Studio. The text in bold (see first page) is removed and put in the style section (see text in bold on the second snippet).

<HTML>
    <HEAD>
        <title>dayrental</title>
        <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
        <meta name="CODE_LANGUAGE" Content="C#">
        <meta name="vs_defaultClientScript" content="JavaScript">
        <meta name="vs_targetSchema" 
              content="http://schemas.microsoft.com/intellisense/ie5">
    </HEAD>
    <body MS_POSITIONING="GridLayout">
        <form id="Form1" method="post" runat="server">
            <asp:Calendar id="Calendar1" 
                style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px" 
                runat="server" Width="910px" 
                BorderColor="Black" SelectionMode="None" 
                PrevMonthText="&lt;&lt;Back"
                NextMonthText="Next&gt;&gt;">
                <DayStyle Font-Size="12px" Font-Names="Verdana" 
                     Font-Bold="True" Wrap="False" HorizontalAlign="Left"
                     Height="100px" BorderWidth="1px" 
                     BorderStyle="Solid" Width="120px" 
                     VerticalAlign="Top"></DayStyle>
                <NextPrevStyle Font-Size="14px"></NextPrevStyle>
                <DayHeaderStyle Font-Names="Verdana"></DayHeaderStyle>
                <SelectedDayStyle ForeColor="Black" 
                            BackColor="White"></SelectedDayStyle>
                <TitleStyle Font-Size="18px" 
                     Font-Names="Verdana" Font-Bold="True"></TitleStyle>
                <OtherMonthDayStyle ForeColor="DarkGray">
                </OtherMonthDayStyle>
            </asp:Calendar>
        </form>r>
    </body>
</HTML>

To make the calendar print correctly, take the properties out of the <form> section and put them into the <style> section in <head>. Make one version for the screen, and one for print, using the CSS @media tag.

<HTML>
    <HEAD>
        <title>dayrental</title>
        <meta name="GENERATOR" 
              Content="Microsoft Visual Studio .NET 7.1">
        <meta name="CODE_LANGUAGE" Content="C#">
        <meta name="vs_defaultClientScript" content="JavaScript">
        <meta name="vs_targetSchema" 
              content="http://schemas.microsoft.com/intellisense/ie5">
        <style type="text/css"> 
            @media Screen { .dayrentcal { WIDTH: 910px; }
              .caldaystyle { FONT-SIZE: 12px; Height: 100px; }}
            @media Print { .dayrentcal { WIDTH: 460pt; }
              .caldaystyle { FONT-SIZE: 7.5pt; Height: 90px; }}
        </style>
    </HEAD>
    <body MS_POSITIONING="GridLayout">
        <form id="Form1" method="post" runat="server">
            <asp:Calendar id="Calendar1" 
                style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px" 
                runat="server" BorderColor="Black" SelectionMode="None" 
                PrevMonthText="&lt;&lt;Back" 
                NextMonthText="Next&gt;&gt;" CssClass="dayrentcal">
              <DayStyle Font-Names="Verdana" Font-Bold="True" Wrap="False" 
                 HorizontalAlign="Left" BorderWidth="1px" BorderStyle="Solid" 
                 CssClass="caldaystyle" VerticalAlign="Top"></DayStyle>
              <NextPrevStyle Font-Size="14px"></NextPrevStyle>
              <DayHeaderStyle Font-Names="Verdana"></DayHeaderStyle>
              <SelectedDayStyle ForeColor="Black" BackColor="White">
              </SelectedDayStyle>
              <TitleStyle Font-Size="18px" Font-Names="Verdana" Font-Bold="True">
              </TitleStyle>
              <OtherMonthDayStyle ForeColor="DarkGray"></OtherMonthDayStyle>
            </asp:Calendar>
        </form>
    </body>
</HTML>

Where did the .dayrentcal in the <style> section come from? That is set in Visual Studio in the Properties window of the calendar. The property is called CssClass. Likewise, .caldaystyle is set in the same Properties window under DayStyle, and then CssClass. That's it. Now, when the user clicks the browser's Window menu or toolbox Print button, the calendar will fit on the page.

Installing locally

Unzip into inetpub/wwwroot. Create a virtual directory in IIS. Open with Visual Studio 2003 or above. Open Access and change the tbl_place table to suit your needs.

Installing remotely

Put the demo project download on your website.

• Create a folder and set it to "Set as an application directory" and "Execute ASP/ASP.NET/PHP Scripts".
• In application directory/Web.config - set the database connection string, username, and password.
• In admin/Web.config - set the username the same as other Web.config.
• Upload the file dayrental.aspx, denied.html, and Web.config into your application directory.
• Create three directories inside your application directory: admin, bin, and db.
• Put Add.aspx, admin.aspx, EditDel.aspx, login.aspx, and Web.config into the admin directory.
• Put cottrent.dll into the bin directory.
• Open Access and change the tbl_place table to suit your needs.
• Put cottrent.mdb into the db directory.
• Set the db directory to "Lock directory from web browsing (still allows access via website scripts)".
• Set permissions to modify.