protected void btn_exportClubsSansBureau_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Config.GetConnectionString());
conn.Open();
SqlCommand sql = new SqlCommand("select cric, name as 'Nom du club' ,adress_1 as 'Adresse 1' ,adress_2 as 'Adresse 2',adress_3 as 'Adresse 3' ,zip as 'Code postal' ,town as 'Ville' , email as 'Email',web as 'Web' from ais_clubs where type_club='rotary' and cric not in (select distinct [cric] FROM[rodi.dnn].[dbo].[ais_rya] where rotary_year = @rotary_year)", conn);
sql.Parameters.AddWithValue("@rotary_year", Functions.GetRotaryYear() + 1);
SqlDataAdapter da = new SqlDataAdapter(sql);
DataSet ds = new DataSet();
da.Fill(ds);
List <DataTable> tables = new List <DataTable>();
foreach (DataTable table in ds.Tables)
{
tables.Add(table);
}
Media media = DataMapping.ExportDataTablesToXLS(tables, "Liste des clubs qui n'ont pas déclaré de bureau.xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
protected void btn_exportBureau_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Config.GetConnectionString());
conn.Open();
SqlCommand sql = new SqlCommand("SELECT c.name as 'Club', a.name as 'Membre', a.[function] as 'Fonction' FROM ais_clubs c, ais_rya a WHERE c.cric = a.cric AND a.rotary_year = @rotary_year ORDER BY c.name", conn);
sql.Parameters.AddWithValue("@rotary_year", Functions.GetRotaryYear() + 1);
SqlDataAdapter da = new SqlDataAdapter(sql);
DataSet ds = new DataSet();
da.Fill(ds);
List <DataTable> tables = new List <DataTable>();
foreach (DataTable table in ds.Tables)
{
tables.Add(table);
}
Media media = DataMapping.ExportDataTablesToXLS(tables, "Bureau année " + (Functions.GetRotaryYear() + 1) + "-" + (Functions.GetRotaryYear() + 2) + ".xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
protected void btn_exportPres_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Config.GetConnectionString());
conn.Open();
SqlCommand sql = new SqlCommand("select c.cric, c.name as 'nom club',a.[function] as 'fonction',a.name as 'prenom nom',(select top 1 email from ais_members where nim = a.nim) as email,(select top 1 [gsm] from ais_members where nim = a.nim) as gsm from ais_clubs c,ais_rya a where c.cric=a.cric and a.rotary_year=@rotary_year and c.type_club='rotary' and a.[function]='Président' order by c.name", conn);
sql.Parameters.AddWithValue("@rotary_year", Functions.GetRotaryYear() + 1);
SqlDataAdapter da = new SqlDataAdapter(sql);
DataSet ds = new DataSet();
da.Fill(ds);
List <DataTable> tables = new List <DataTable>();
foreach (DataTable table in ds.Tables)
{
tables.Add(table);
}
Media media = DataMapping.ExportDataTablesToXLS(tables, "Liste des présidents élus année " + (Functions.GetRotaryYear() + 1) + "-" + (Functions.GetRotaryYear() + 2) + ".xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
/// <summary>
/// Exporte les inscrits vers un fichier Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BT_Exporter_Inscrits_Click(object sender, EventArgs e)
{
DataSet ds = DataMapping.ExecSql("SELECT C.club as 'Nom club',D.wording as 'Nom participant',C.dt as 'Inscrit le',C.[rule] as 'Payé' FROM [ais_orders_details] D,[ais_orders] C where D.id_order = C.id and C.id_payment='" + HF_id.Value + "' order by C.club,D.wording");
List <DataTable> liste = new List <DataTable>();
liste.Add(ds.Tables[0]);
Media media = DataMapping.ExportDataTablesToXLS(liste, "List des inscrits au " + DateTime.Now.ToShortDateString().Replace("/", "-") + ".xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
/// <summary>
/// Exporte les transactions vers un fichier Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BT_Exporter_Transactions_CB_Click(object sender, EventArgs e)
{
DataSet ds = DataMapping.ExecSql("SELECT [dt],[data] FROM [ais_mercanet_resp]");
List <DataTable> liste = new List <DataTable>();
liste.Add(ds.Tables[0]);
Media media = DataMapping.ExportDataTablesToXLS(liste, "List des transactions au " + DateTime.Now.ToShortDateString().Replace("/", "-") + ".xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
/// <summary>
/// Exporte les commandes vers un fichier excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BT_Export_Orders_Click(object sender, EventArgs e)
{
DataSet ds = DataMapping.ExecSql("SELECT club,amount,[rule],info_rule,type_rule,par_rule,dt_rule,dt,transaction_id FROM [ais_orders] where id_payment='" + HF_id.Value + "' order by club");
List <DataTable> liste = new List <DataTable>();
liste.Add(ds.Tables[0]);
Media media = DataMapping.ExportDataTablesToXLS(liste, "List des commandes au " + DateTime.Now.ToShortDateString().Replace("/", "-") + ".xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
/// <summary>
/// Permet d'exporter le GridView en CSV
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BT_Export_CSV_Click(object sender, EventArgs e)
{
List <DataTable> liste = new List <DataTable>();
DataSet ds = DataMapping.ExecSql("SELECT nim as NIM, section as Section, surname as Nom, name as Prenom, job as Poste, cric as Cric, club as 'Nom du club', [description] as 'Description' FROM " + Const.TABLE_PREFIX + "drya WHERE rotary_year = '" + rbl_rotaryYear.SelectedValue + "' order by section, rank");
liste.Add(ds.Tables[0]);
Media media = DataMapping.ExportDataTablesToXLS(liste, "Organigramme District " + rbl_rotaryYear.SelectedValue + "-" + (1 + int.Parse(rbl_rotaryYear.SelectedValue)) + ".csv", Aspose.Cells.SaveFormat.CSV);
List <DataTable> liste2 = new List <DataTable>();
DataSet dsCom = DataMapping.ExecSql("SELECT name as 'Nom de la commission', memberName as Membre, job as 'Poste' FROM " + Const.TABLE_PREFIX + "commission where rotary_year ='" + rbl_rotaryYear.SelectedValue + "' order by name");
dsCom.Tables[0].TableName = "Commissions";
liste2.Add(dsCom.Tables[0]);
Media media2 = DataMapping.ExportDataTablesToXLS(liste2, "Commission " + rbl_rotaryYear.SelectedValue + "-" + (1 + int.Parse(rbl_rotaryYear.SelectedValue)) + ".csv", Aspose.Cells.SaveFormat.CSV);
Media media3 = new Media();
media3.content_size = media.content_size + media2.content_size;
media3.content = new byte[media3.content_size];
for (int i = 0; i < media3.content_size; i++)
{
if (i < media.content_size)
{
media3.content[i] = media.content[i];
}
else
{
media3.content[i] = media2.content[i - media.content_size];
}
}
media3.dt = media.dt;
media3.name = media.name;
string guid = Guid.NewGuid().ToString();
Session[guid] = media3;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}
/// <summary>
/// Permet d'exporter le GridView en fichier Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BT_Export_XLS_Click(object sender, EventArgs e)
{
List <DataTable> liste = new List <DataTable>();
foreach (ListItem laSection in ddl_section.Items)
{
DataSet ds_ = DataMapping.ExecSql("SELECT nim as NIM, surname as Nom, name as Prénom, job as Poste, cric as Cric, club as 'Nom du club', [description] as 'Description' FROM " + Const.TABLE_PREFIX + "drya WHERE rotary_year = '" + rbl_rotaryYear.SelectedValue + "' AND section = '" + laSection.Value + "' order by rank");
ds_.Tables[0].TableName = laSection.Text;
liste.Add(ds_.Tables[0]);
}
DataSet ds = DataMapping.ExecSql("SELECT name as 'Nom de la commission', memberName as Membre, job as 'Poste' FROM " + Const.TABLE_PREFIX + "commission where rotary_year ='" + rbl_rotaryYear.SelectedValue + "' order by name");
ds.Tables[0].TableName = "Commissions";
liste.Add(ds.Tables[0]);
Media media = DataMapping.ExportDataTablesToXLS(liste, "Organigramme District " + rbl_rotaryYear.SelectedValue + "-" + (1 + int.Parse(rbl_rotaryYear.SelectedValue)) + ".xls", Aspose.Cells.SaveFormat.Excel97To2003);
string guid = Guid.NewGuid().ToString();
Session[guid] = media;
Response.Redirect(Const.MEDIA_DOWNLOAD_URL + "?id=" + guid);
}