protected void Export(object sender, EventArgs e)
{
DataTable dt_feedback = GetFeedback(true);
if (dt_feedback.Rows.Count > 0)
{
String template_filename = "Survey Feedback-Template.xlsx";
String new_filename = template_filename.Replace("-Template.xlsx", "")
+ " - " + DateTime.Now.ToString().Replace("/", "-").Replace(":", "-") + ".xlsx";
String folder_dir = AppDomain.CurrentDomain.BaseDirectory + @"Dashboard\SurveyFeedback\XL\";
File.Copy(folder_dir + template_filename, folder_dir + Util.SanitiseStringForFilename(new_filename), true); // copy template file
// Add sheet with data for each territory
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(folder_dir + new_filename, 99);
if (ss != null)
{
ExcelAdapter.AddDataToWorkSheet(ss, "Survey Feedback", dt_feedback, true, true, true);
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(folder_dir + new_filename);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + file.Name + "\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
Util.WriteLogWithDetails("Survey Feedback exported.", "surveyfeedback_log");
}
catch
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
finally
{
file.Delete();
}
}
else
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
else
{
Util.PageMessage(this, "Nothing to export!");
}
}
protected void ExportToExcel(DataTable dt_data)
{
Util.WriteLogWithDetails("Exporting company search results from " + dd_facet.SelectedItem.Text + ".", "search_log");
String dir = AppDomain.CurrentDomain.BaseDirectory + @"dashboard\search\xl\xltemplate.xlsx";
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(dir, 99);
if (ss != null)
{
ExcelAdapter.AddDataToWorkSheet(ss, "Exported", dt_data, true, true, false);
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(dir);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + dd_facet.SelectedItem.Text + " Export "
+ "(" + DateTime.Now.ToString().Replace(" ", "-").Replace("/", "_").Replace(":", "_")
.Substring(0, (DateTime.Now.ToString().Length - 3)) + DateTime.Now.ToString("tt") + ").xls\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
}
catch
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
else
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
protected void ExportPull(object sender, EventArgs e)
{
DataTable dt_pull = GetPull();
bool IsData = dt_pull != null && dt_pull.Rows.Count > 0;
if (IsData)
{
String template_filename = "Data Pull Template.xlsx";
String new_filename = Util.SanitiseStringForFilename(
template_filename.Replace(" Template.xlsx", String.Empty) + " - "
+ Util.GetUserName() + " - "
+ DateTime.Now.ToString("d-M-yyyy HH-mm-ss")
+ ".xlsx");
String folder_dir = AppDomain.CurrentDomain.BaseDirectory + @"dashboard\leads\files\templates\";
File.Copy(folder_dir + template_filename, folder_dir + new_filename, true); // copy template file
// Add sheet with data for each territory
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(folder_dir + new_filename, 99);
if (ss != null)
{
String SeparatorField = dd_separate.SelectedItem.Value;
if (SeparatorField != String.Empty)
{
// Sort by region
dt_pull.DefaultView.Sort = SeparatorField + ", Company";
dt_pull = dt_pull.DefaultView.ToTable();
DataTable dt_region_data = dt_pull.Copy();
dt_region_data.Clear();
for (int i = 0; i < dt_pull.Rows.Count; i++)
{
DataRow dr = dt_pull.Rows[i];
dt_region_data.ImportRow(dr);
String ThisSeparator = dt_pull.Rows[i][SeparatorField].ToString();
String NextSeparator = String.Empty;
if (i < (dt_pull.Rows.Count - 1))
{
NextSeparator = dt_pull.Rows[i + 1][SeparatorField].ToString();
}
if (NextSeparator == String.Empty || ThisSeparator.ToLower().Trim() != NextSeparator.ToLower().Trim())
{
ExcelAdapter.InsertWorkSheetWithData(ss, ThisSeparator, dt_region_data, true, false);
dt_region_data.Clear();
}
}
}
else
{
ExcelAdapter.AddDataToWorkSheet(ss, "Pull Data", dt_pull, true, true, false);
}
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(folder_dir + new_filename);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + file.Name + "\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
}
catch { Util.PageMessageAlertify(this, "There was an error downloading the Excel file. Please try again."); }
finally { file.Delete(); }
}
else
{
Util.PageMessageAlertify(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
else
{
Util.PageMessageAlertify(this, "Nothing to export!");
}
}
protected void ExportSelectedIssues(object sender, EventArgs e)
{
// Get book data into datatable
double n_c = Util.GetOfficeConversion("Africa");
String office_expr = String.Empty;
if (dd_office.SelectedItem.Text != "Group")
{
office_expr = " AND Office=@office ";
}
String qry =
"SELECT Office, IssueName as Issue, Advertiser, Feature as 'Feature Company', " +
"territory_magazine as Region, channel_magazine as Sector, Size as 'Advert Size', " +
"Price as 'Original Price', CONVERT(price*conversion,SIGNED) as PriceUSD, " +
"Rep as 'Sold By', list_gen as 'List Gen.', IF(deleted=0, 'N', 'Y') as Cancellation, " +
"IF(date_paid IS NULL, 'N', 'Y') as 'Invoice Paid', " +
"Invoice, date_paid as 'Date Paid', ent_date as 'Date Added' " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND IsDeleted=0 AND (StartDate BETWEEN " +
"(SELECT MIN(StartDate) FROM db_salesbookhead WHERE IssueName=@from) AND " +
"(SELECT MAX(StartDate) FROM db_salesbookhead WHERE IssueName=@to)) " + office_expr +
"ORDER BY StartDate, Office";
DataTable dt_book_data = SQL.SelectDataTable(qry,
new String[] { "@from", "@to", "@n_c", "@office" },
new Object[] { dd_start_issue.SelectedItem.Text, dd_end_issue.SelectedItem.Text, n_c, dd_office.SelectedItem.Text });
if (dt_book_data.Rows.Count > 0)
{
String template_filename = "Sales Book Sales-Template.xlsx";
String new_filename = Util.SanitiseStringForFilename(template_filename.Replace("-Template.xlsx", "")
+ " - " + dd_office.SelectedItem.Text + " From " + dd_start_issue.SelectedItem.Text
+ " to " + dd_end_issue.SelectedItem.Text + ".xlsx");
String folder_dir = AppDomain.CurrentDomain.BaseDirectory + @"Dashboard\SBInput\XL\";
File.Copy(folder_dir + template_filename, folder_dir + new_filename, true); // copy template file
// Add sheet with data for each territory
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(folder_dir + new_filename, 99);
if (ss != null)
{
ExcelAdapter.AddDataToWorkSheet(ss, "Book Data", dt_book_data, true, true, true);
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(folder_dir + new_filename);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + file.Name + "\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
Util.WriteLogWithDetails("Sales Book Issues exported [" + dd_start_issue.SelectedItem.Text
+ " to " + dd_end_issue.SelectedItem.Text + " - " + dd_office.SelectedItem.Text + "]", "salesbook_log");
}
catch
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
finally
{
file.Delete();
}
}
else
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
else
{
Util.PageMessage(this, "No data found between that book range.");
}
}
protected void Export(object sender, EventArgs e)
{
// Type expr
String type_expr = String.Empty;
if (dd_type.SelectedItem.Text != "All Accounts")
{
if (dd_type.SelectedItem.Text == "Paid Only")
{
type_expr = " AND (date_paid IS NOT NULL) ";
}
else
{
type_expr = " AND (date_paid IS NULL) ";
}
}
// Office expr
String office_expr = String.Empty;
if (dd_office.SelectedItem.Text != "Group")
{
office_expr = " AND sbh.Office=@office ";
}
// Date type expr
DateTime dt_from = new DateTime();
DateTime dt_to = new DateTime();
String date_type_expr = String.Empty;
if (dp_from.SelectedDate != null && dp_to.SelectedDate != null &&
DateTime.TryParse(dp_from.SelectedDate.ToString(), out dt_from) && DateTime.TryParse(dp_to.SelectedDate.ToString(), out dt_to) &&
(dd_from_to_type.SelectedItem.Value == "ent_date" || dd_from_to_type.SelectedItem.Value == "date_paid"))
{
date_type_expr = " AND (" + dd_from_to_type.SelectedItem.Value + " BETWEEN @from AND @to) ";
}
String qry = "SELECT sbh.Office as 'Territory', TabName as 'Tab', YEAR(ent_date) as 'Year', ent_date as 'Added', " +
"CASE WHEN date_paid IS NULL THEN DATEDIFF(NOW(), ent_date) ELSE DATEDIFF(date_paid, ent_date) END as 'Days to Pay', " +
"Advertiser, Price, " +
"CASE WHEN date_paid IS NULL THEN Outstanding ELSE 0 END as 'Outstanding', date_paid as 'Date Paid', Invoice " +
"FROM db_financesales fs, db_salesbook sb, db_salesbookhead sbh, db_financesalestabs fst " +
"WHERE fs.SaleID = sb.ent_id " +
"AND sb.sb_id = sbh.SalesBookID " +
"AND fs.FinanceTabID = fst.FinanceTabID "
+ type_expr + office_expr + date_type_expr +
"AND deleted=0 AND sb.IsDeleted=0 AND red_lined=0 AND price > 0 " +
//"#AND CASE WHEN date_paid IS NULL THEN DATEDIFF(NOW(), ent_date) ELSE DATEDIFF(date_paid, ent_date) END > -1 "+
"ORDER BY sbh.Office, ent_date";
DataTable dt_accounts = SQL.SelectDataTable(qry,
new String[] { "@from", "@to", "@office" },
new Object[] { dt_from.ToString("yyyy/MM/dd"), dt_to.ToString("yyyy/MM/dd"), dd_office.SelectedItem.Text });
if (dt_accounts.Rows.Count > 0)
{
String template_filename = "Finance Accounts Export-Template.xlsx";
String date_name = " - From " + dt_from.ToString().Substring(0, 10).Replace("/", ".") + " to " + dt_to.ToString().Substring(0, 10).Replace("/", ".");
if (date_type_expr == String.Empty)
{
date_name = " - All Time";
}
String new_filename = Util.SanitiseStringForFilename(template_filename.Replace("-Template.xlsx", String.Empty)
+ " - " + dd_office.SelectedItem.Text + date_name + " - " + dd_type.SelectedItem.Text + ".xlsx");
String folder_dir = AppDomain.CurrentDomain.BaseDirectory + @"Dashboard\Finance\Docs\";
File.Copy(folder_dir + template_filename, folder_dir + new_filename, true); // copy template file
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(folder_dir + new_filename, 99);
if (ss != null)
{
ExcelAdapter.AddDataToWorkSheet(ss, "Accounts", dt_accounts, true, true, false);
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(folder_dir + new_filename);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + file.Name + "\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
Util.WriteLogWithDetails("Finance accounts exported [" + dd_office.SelectedItem.Text + " - " + date_name.Replace(" - ", String.Empty)
+ " - " + dd_type.SelectedItem.Text + "]", "finance_log");
}
catch
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
finally
{
file.Delete();
}
}
else
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
else
{
Util.PageMessage(this, "No data found between that date range.");
}
}