protected void activatePartner(object sender, EventArgs e)
        {
            Partner partner = new Partner();
            partner.id = this.ID_PARTNER.Text;

            PartnerDAO partnerDAO = new PartnerDAO();
            this.activatePartnerForm.Visible = false;
            this.responseForm.Visible = true;
            this.responseText.Text = partnerDAO.activatePartner(partner);
        }
        protected void insertNewPartner(object sender, EventArgs e)
        {
            Partner partner = new Partner();

            partner.name = this.partnerName.Text.Trim();
            partner.dateJoined = this.datepicker.Text;
            partner.address = this.address.Text.Trim();
            partner.city = this.city.Text.Trim();
            partner.postalCode = this.postalCode.Text.ToUpper();
            partner.website = this.website.Text.Trim();
            partner.emailAddress = this.emailAddress.Text.Trim();
            partner.phoneNumber = this.phoneNumber.Text;
            partner.faxNumber = this.faxNumber.Text;
            partner.contactPerson = this.contactPerson.Text.Trim();

            PartnerDAO partnerDAO = new PartnerDAO();
            this.response.InnerText = partnerDAO.insertNewPartner(partner);
            this.insertNewPartnerForm.Visible = false;
            this.response.Visible = true;
        }
        protected void updatePartner(object sender, EventArgs e)
        {
            Partner partner = new Partner();
            string databaseResponse = "";

            if (partnerName.Text.ToString() == "" || address.Text.ToString() == "" ||
                city.Text.ToString() == "" || postalCode.Text.ToString() == "")
            {
                this.editPartnerForm.Visible = false;
                this.response.Visible = true;

            }
            else
            {   //Loads the form content into the partner object
                partner.id = this.searchPartners.SelectedRow.Cells[4].Text;
                partner.dateJoined = this.searchPartners.SelectedRow.Cells[5].Text;
                partner.status = this.searchPartners.SelectedRow.Cells[6].Text;
                partner.name = partnerName.Text.ToString().Trim();
                partner.address = address.Text.ToString().Trim();
                partner.city = city.Text.ToString().Trim();
                partner.postalCode = postalCode.Text.ToString();
                partner.website = website.Text.ToString().Trim();
                partner.emailAddress = emailAddress.Text.ToString().Trim();
                partner.phoneNumber = phoneNumber.Text.ToString();
                partner.faxNumber = faxNumber.Text.ToString();
                partner.contactPerson = contactPerson.Text.ToString().Trim();

                this.clearAllFields();

                PartnerDAO partnerDAO = new PartnerDAO();
                databaseResponse = partnerDAO.updatePartner(partner);

                this.response.InnerText = databaseResponse;
                this.editPartnerForm.Visible = false;
                this.response.Visible = true;
            }
        }
        protected void uploadExcelFile(object sender, EventArgs e)
        {
            //-------------------

            if (this.partnerList.Text.Equals("") || this.selectYear.SelectedIndex == 0)
            {
                this.errorDiv.Visible = true;
                this.errorMsg.Text = "Please select a Partner and the Year of Rebate.";
            }
            else
            {

                //-------------------

                PartnerDAO partnerDAO = new PartnerDAO();

                if (partnerDAO.alreadyExists(this.partnerList.SelectedValue.ToString(), this.selectYear.Text + "-01-01"))
                {
                    this.errorDiv.Visible = true;
                    this.errorMsg.Text = @"The Database contains data related to both the selected Partner and Year.
                The Inclusion of this file could make the records doubled or inaccurate.
                The transaction has been cancelled.";
                }
                else
                {

                    //-------------------

                    string conString = "";
                    string extension = "";
                    try
                    {
                        //Upload and save the file
                        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(this.uploadFile.PostedFile.FileName);
                        uploadFile.SaveAs(excelPath);

                        conString = string.Empty;
                        extension = Path.GetExtension(uploadFile.PostedFile.FileName);

                        switch (extension)
                        {
                            case ".xls": //Excel 97-03
                                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                                break;
                            case ".xlsx": //Excel 07 or higher
                                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                                break;

                        }

                        conString = string.Format(conString, excelPath);

                    }
                    catch (Exception exception)
                    {
                        this.errorDiv.Visible = true;
                        this.errorMsg.Text = "Some error has occured. Please verify the file. DBInfo.: " + exception.Message;
                    }

                    try
                    {
                        using (OleDbConnection excel_con = new OleDbConnection(conString))
                        {
                            excel_con.Open();
                            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                            DataTable dtExcelData = new DataTable();

                            dtExcelData.Columns.AddRange(new DataColumn[8] {
                    new DataColumn("ID_MEMBER", typeof(int)),
                    new DataColumn("ID_PARTNER", typeof(int)),
                    new DataColumn("IS_DELIVERED_BY_PARTNER", typeof(int)),
                    new DataColumn("QUANTITY", typeof(float)),
                    new DataColumn("CATEGORY", typeof(string)),
                    new DataColumn("YEAR",typeof(string)),
                    new DataColumn("DATE_MODIFIED",typeof(string)),
                    new DataColumn("MODIFIED_BY",typeof(string)) });

                            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                            {
                                oda.Fill(dtExcelData);
                            }
                            excel_con.Close();

                            string consString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;

                            using (SqlConnection con = new SqlConnection(consString))
                            {
                                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                                {
                                    //Set the database table name
                                    sqlBulkCopy.DestinationTableName = "dbo.REBATE";

                                    //[OPTIONAL]: Map the Excel columns with that of the database table
                                    sqlBulkCopy.ColumnMappings.Add("ID_MEMBER", "ID_MEMBER");
                                    sqlBulkCopy.ColumnMappings.Add("ID_PARTNER", "ID_PARTNER");
                                    sqlBulkCopy.ColumnMappings.Add("IS_DELIVERED_BY_PARTNER", "IS_DELIVERED_BY_PARTNER");
                                    sqlBulkCopy.ColumnMappings.Add("QUANTITY", "QUANTITY");
                                    sqlBulkCopy.ColumnMappings.Add("CATEGORY", "CATEGORY");
                                    sqlBulkCopy.ColumnMappings.Add("YEAR", "YEAR");
                                    sqlBulkCopy.ColumnMappings.Add("DATE_MODIFIED", "DATE_MODIFIED");
                                    sqlBulkCopy.ColumnMappings.Add("MODIFIED_BY", "MODIFIED_BY");

                                    con.Open();
                                    sqlBulkCopy.WriteToServer(dtExcelData);
                                    con.Close();
                                }
                            }
                        }

                        this.responseForm.Visible = true;
                        this.uploadForm.Visible = false;
                        this.response.Text = "File uploaded successfully into the database";

                    }
                    catch (Exception excep)
                    {
                        this.errorDiv.Visible = true;
                        this.errorMsg.Text = "Some error has occured. Please verify the file. [BDInfo.: " + excep.Message + "]";
                    }

                }
            }
        }