using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CodeCafe.Web;
using System.IO;
using System.Data.SqlClient;
using OfficeOpenXml;

public partial class Admin_Users : System.Web.UI.Page
{
  /****************************************************************************************************/
  protected void Page_Load(object sender, EventArgs e)
  {

  }

  /****************************************************************************************************/
  /// <summary>
  /// Exports the users to Excel.
  /// </summary>
  /// <param name="sender">The sender.</param>
  /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
  protected void Export(object sender, EventArgs e)
  {
    string filename = Server.MapPath("~/DynamicData/User Export.xlsx");
    try
    {
      if (File.Exists(filename))
        File.Delete(filename);
      FileInfo xlFile = new FileInfo(filename);
      FileInfo xlTemplate = new FileInfo(Server.MapPath("~/Templates/User export.xlsx"));

      using (ExcelPackage xlPackage = new ExcelPackage(xlFile, xlTemplate))
      {
        ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Users"];
        InsertData(worksheet);

        xlPackage.Workbook.Properties.Title = "BluWave Website Users";
        xlPackage.Workbook.Properties.Author = "Code Cafe";
        xlPackage.Save();
      }
    }
    catch (Exception err)
    {
      Logger.Log(err);
    }
    finally
    {
      if (File.Exists(filename))
        Utils.DownloadFile(filename, "Users.xlsx", Response, DownloadType.attachement, true);
    }

  }
  
  /****************************************************************************************************/
  /// <summary>
  /// Inserts the data into the worksheet.
  /// </summary>
  /// <param name="worksheet">The worksheet.</param>
  private void InsertData(ExcelWorksheet worksheet)
  {
    MSSqlTools db = new MSSqlTools();
    SqlDataReader rdr = null;
    SqlCommand cmd;
    try
    {
      db.DBase.Open();
      cmd = new SqlCommand("UserInfo_ListAll", db.DBase);
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      int row = 3;
      rdr = cmd.ExecuteReader();

      while (rdr.Read())
      {
        worksheet.Cell(row, 1).Value = db.ProcessField(rdr["ui_firstName"], "");
        worksheet.Cell(row, 2).Value = db.ProcessField(rdr["ui_surname"], "");
        worksheet.Cell(row, 3).Value = db.ProcessField(rdr["ui_jobTitle"], "");
        worksheet.Cell(row, 4).Value = "'" + db.ProcessField(rdr["ui_contact"], "");
        worksheet.Cell(row, 5).Value = db.ProcessField(rdr["ui_email"], "");
        worksheet.Cell(row, 6).Value = db.ProcessField(rdr["ui_company"], "");
        worksheet.Cell(row, 7).Value = Global.GetCompanySize(db.ProcessField(rdr["ui_numEmployees"], 1));
        worksheet.Cell(row, 8).Value = Global.GetProvince(db.ProcessField(rdr["pv_id"], 1));
        row++;
      }
      rdr.Close();
    }
    catch (Exception err)
    {
      Logger.Log(err);
    }
    finally
    {
      db.DBase.Close();
    }
  }

  /****************************************************************************************************/

}