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 + "]";
}
}
}
}