Torna al Thread
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
String connectionString = "Data Source=localhost;Initial Catalog=xx;User Id=xx;Password=xx;";
SqlConnection cn = new SqlConnection(connectionString);
SqlDataReader myReader = null;
XmlDocument doc = new System.Xml.XmlDocument();
String testoquery = "";
XmlNodeList nodi;
doc.Load("C:/.../App_Data/queries.xml");
nodi = doc.GetElementsByTagName("query");
int righe = 0;
foreach (XmlNode nodo in nodi)
{
if (nodo.FirstChild.InnerText == "pagina4prestazioni")
testoquery = nodo.FirstChild.NextSibling.InnerText;
}
try
{
SqlCommand cmd = new SqlCommand(testoquery, cn);
cn.Open();
myReader = cmd.ExecuteReader();
dt.Load(myReader);
int i = 0;
foreach (DataRow row in dt.Rows)
{
righe++;
}//ho il numero di righe(quindi di eventi)che si sono verificati
String[] eventi = new String[righe];
foreach (DataRow row in dt.Rows)
{
eventi[i] = row["U_desc"].ToString();
i++;
}//ho un array eventi conenente i nomi di tutti gli eventi
for (int j = 2; j <= righe-1; j++)//parto da 2 perchè i primi 2 valori sono righe vuote
{
String p = eventi[j].ToString();
Label2.Text = p;
gvdati.DataSource = elaboraQuery02(eventi, j);
gvdati.DataBind();
gvdati01.DataSource = elaboraQuery01(eventi, j);
gvdati01.DataBind();
gvdati02.DataSource = elaboraQuery(eventi, j);
gvdati02.DataBind();
if (j == (righe - 1))
j = 1;
System.Threading.Thread.Sleep(5000);
}
}
catch (SqlException)
{
}
finally
{
cn.Close();
myReader.Close();
}
}
//------------------------------metodi per la pasqua-------------------------------
public DateTime GetPasqua(int anno)
{
int giorno, mese;
int a, b, c, d, e, f, n, h, i, k, l, m;
a = anno % 19;
b = anno / 100;
c = anno % 100;
d = b / 4;
e = b % 4;
f = (b + 8) / 25;
n = (b - f + 1) / 3;
h = (19 * a + b - d - n + 15) % 30;
i = c / 4;
k = c % 4;
l = (32 + 2 * e + 2 * i - h - k) % 7;
m = (a + 11 * h + 22 * l) / 451;
giorno = ((h + l - 7 * m + 114) % 31) + 1;
mese = (h + l - 7 * m + 114) / 31;
return (new DateTime(anno, mese, giorno));
}
public Boolean isPasqua(DateTime d)
{
DateTime pasqua = GetPasqua(d.Year);
//calcolo settimana di pasqua
//se pasqua viene il 4 aprile devo far si che il 29 marzo mi metta settimana di pasqua!
if (pasqua.Day < 7 & d.AddMonths(1).Month == pasqua.Month & d.AddDays(7).Day > pasqua.Day & d.AddDays(7).Month == pasqua.Month)
return true;
if (d.Day < (pasqua.Day) & (d.Day + 7) > (pasqua.Day) & d.Month == pasqua.Month)
return true;
return false;
}
//*********************************************************************************
public DataTable elaboraQuery(String[] eventi, int j)
{
DataTable dt = new DataTable();
String connectionString = "Data Source=localhost;Initial Catalog=xx;User Id=xx;Password=xx;";
SqlConnection cn = new SqlConnection(connectionString);
SqlDataReader myReader = null;
XmlDocument doc = new System.Xml.XmlDocument();
String testoquery = "";
XmlNodeList nodi;
doc.Load("C:/.../App_Data/queries.xml");
nodi = doc.GetElementsByTagName("query");
String pippo = eventi[j].ToString();
foreach (XmlNode nodo in nodi)
{
if (nodo.FirstChild.InnerText == "pagina4annocorrente")
testoquery = nodo.FirstChild.NextSibling.InnerText;
}
DateTime a = DateTime.Now;
if (Convert.ToInt32(a.DayOfWeek) == 2) a.AddDays(-1);
if (Convert.ToInt32(a.DayOfWeek) == 3) a.AddDays(-2);
if (Convert.ToInt32(a.DayOfWeek) == 4) a.AddDays(-3);
if (Convert.ToInt32(a.DayOfWeek) == 5) a.AddDays(-4);
if (Convert.ToInt32(a.DayOfWeek) == 6) a.AddDays(-5);
if (Convert.ToInt32(a.DayOfWeek) == 7) a.AddDays(-6);
DateTime b = DateTime.Now.AddYears(-1);
if (Convert.ToInt32(b.DayOfWeek) == 2) b.AddDays(-1);
if (Convert.ToInt32(b.DayOfWeek) == 3) b.AddDays(-2);
if (Convert.ToInt32(b.DayOfWeek) == 4) b.AddDays(-3);
if (Convert.ToInt32(b.DayOfWeek) == 5) b.AddDays(-4);
if (Convert.ToInt32(b.DayOfWeek) == 6) b.AddDays(-5);
if (Convert.ToInt32(b.DayOfWeek) == 7) b.AddDays(-6);
DateTime c = DateTime.Now.AddYears(-2);
if (Convert.ToInt32(c.DayOfWeek) == 2) c.AddDays(-1);
if (Convert.ToInt32(c.DayOfWeek) == 3) c.AddDays(-2);
if (Convert.ToInt32(c.DayOfWeek) == 4) c.AddDays(-3);
if (Convert.ToInt32(c.DayOfWeek) == 5) c.AddDays(-4);
if (Convert.ToInt32(c.DayOfWeek) == 6) c.AddDays(-5);
if (Convert.ToInt32(c.DayOfWeek) == 7) c.AddDays(-6);
if (isPasqua(a))
{
b = GetPasqua(b.Year);
b.AddDays(-6);
c = GetPasqua(c.Year);
c.AddDays(-6);
}
if (isPasqua(b))
{
a = GetPasqua(a.Year);
a.AddDays(-6);
c = GetPasqua(c.Year);
c.AddDays(-6);
}
if (isPasqua(c))
{
b = GetPasqua(b.Year);
b.AddDays(-6);
a = GetPasqua(a.Year);
a.AddDays(-6);
}
try
{
SqlCommand cmd = new SqlCommand(testoquery, cn);
cmd.Parameters.AddWithValue("@dataInizio", a);
cmd.Parameters.AddWithValue("@dataFine", a.AddDays(6));
cmd.Parameters.AddWithValue("@eventi", pippo);
cn.Open();
myReader = cmd.ExecuteReader();
dt.Load(myReader);
myReader.Close();
}
catch (SqlException)
{
}
finally
{
cn.Close();
}
return dt;
}
public DataTable elaboraQuery01(String[] eventi, int j)
{
DataTable dt = new DataTable();
String connectionString = "Data Source=localhost;Initial Catalog=xx;User Id=xx;Password=xx;";
SqlConnection cn = new SqlConnection(connectionString);
SqlDataReader myReader = null;
XmlDocument doc = new System.Xml.XmlDocument();
String testoquery = "";
XmlNodeList nodi;
doc.Load("C:/.../App_Data/queries.xml");
nodi = doc.GetElementsByTagName("query");
String pippo = eventi[j].ToString();
foreach (XmlNode nodo in nodi)
{
if (nodo.FirstChild.InnerText == "pagina4annopassato")
testoquery = nodo.FirstChild.NextSibling.InnerText;
}
DateTime a = DateTime.Now;
if (Convert.ToInt32(a.DayOfWeek) == 2) a.AddDays(-1);
if (Convert.ToInt32(a.DayOfWeek) == 3) a.AddDays(-2);
if (Convert.ToInt32(a.DayOfWeek) == 4) a.AddDays(-3);
if (Convert.ToInt32(a.DayOfWeek) == 5) a.AddDays(-4);
if (Convert.ToInt32(a.DayOfWeek) == 6) a.AddDays(-5);
if (Convert.ToInt32(a.DayOfWeek) == 7) a.AddDays(-6);
DateTime b = DateTime.Now.AddYears(-1);
if (Convert.ToInt32(b.DayOfWeek) == 2) b.AddDays(-1);
if (Convert.ToInt32(b.DayOfWeek) == 3) b.AddDays(-2);
if (Convert.ToInt32(b.DayOfWeek) == 4) b.AddDays(-3);
if (Convert.ToInt32(b.DayOfWeek) == 5) b.AddDays(-4);
if (Convert.ToInt32(b.DayOfWeek) == 6) b.AddDays(-5);
if (Convert.ToInt32(b.DayOfWeek) == 7) b.AddDays(-6);
DateTime c = DateTime.Now.AddYears(-2);
if (Convert.ToInt32(c.DayOfWeek) == 2) c.AddDays(-1);
if (Convert.ToInt32(c.DayOfWeek) == 3) c.AddDays(-2);
if (Convert.ToInt32(c.DayOfWeek) == 4) c.AddDays(-3);
if (Convert.ToInt32(c.DayOfWeek) == 5) c.AddDays(-4);
if (Convert.ToInt32(c.DayOfWeek) == 6) c.AddDays(-5);
if (Convert.ToInt32(c.DayOfWeek) == 7) c.AddDays(-6);
if (isPasqua(a))
{
b = GetPasqua(b.Year);
b.AddDays(-6);
c = GetPasqua(c.Year);
c.AddDays(-6);
}
if (isPasqua(b))
{
a = GetPasqua(a.Year);
a.AddDays(-6);
c = GetPasqua(c.Year);
c.AddDays(-6);
}
if (isPasqua(c))
{
b = GetPasqua(b.Year);
b.AddDays(-6);
a = GetPasqua(a.Year);
a.AddDays(-6);
}
try
{
SqlCommand cmd = new SqlCommand(testoquery, cn);
cmd.Parameters.AddWithValue("@dataInizio1", b);
cmd.Parameters.AddWithValue("@dataFine1", b.AddDays(6));
cmd.Parameters.AddWithValue("@eventi", pippo);
cn.Open();
myReader = cmd.ExecuteReader();
dt.Load(myReader);
myReader.Close();
}
catch (SqlException)
{
}
finally
{
cn.Close();
}
return dt;
}
public DataTable elaboraQuery02(String[] eventi, int j)
{
DataTable dt = new DataTable();
String connectionString = "Data Source=localhost;Initial Catalog=xx;User Id=xx;Password=xx;";
SqlConnection cn = new SqlConnection(connectionString);
SqlDataReader myReader = null;
XmlDocument doc = new System.Xml.XmlDocument();
String testoquery = "";
XmlNodeList nodi;
doc.Load("C:/.../App_Data/queries.xml");
nodi = doc.GetElementsByTagName("query");
String pippo = eventi[j].ToString();
foreach (XmlNode nodo in nodi)
{
if (nodo.FirstChild.InnerText == "pagina4dueannipassati")
testoquery = nodo.FirstChild.NextSibling.InnerText;
}
DateTime a = DateTime.Now;
if (Convert.ToInt32(a.DayOfWeek) == 2) a.AddDays(-1);
if (Convert.ToInt32(a.DayOfWeek) == 3) a.AddDays(-2);
if (Convert.ToInt32(a.DayOfWeek) == 4) a.AddDays(-3);
if (Convert.ToInt32(a.DayOfWeek) == 5) a.AddDays(-4);
if (Convert.ToInt32(a.DayOfWeek) == 6) a.AddDays(-5);
if (Convert.ToInt32(a.DayOfWeek) == 7) a.AddDays(-6);
DateTime b = DateTime.Now.AddYears(-1);
if (Convert.ToInt32(b.DayOfWeek) == 2) b.AddDays(-1);
if (Convert.ToInt32(b.DayOfWeek) == 3) b.AddDays(-2);
if (Convert.ToInt32(b.DayOfWeek) == 4) b.AddDays(-3);
if (Convert.ToInt32(b.DayOfWeek) == 5) b.AddDays(-4);
if (Convert.ToInt32(b.DayOfWeek) == 6) b.AddDays(-5);
if (Convert.ToInt32(b.DayOfWeek) == 7) b.AddDays(-6);
DateTime c = DateTime.Now.AddYears(-2);
if (Convert.ToInt32(c.DayOfWeek) == 2) c.AddDays(-1);
if (Convert.ToInt32(c.DayOfWeek) == 3) c.AddDays(-2);
if (Convert.ToInt32(c.DayOfWeek) == 4) c.AddDays(-3);
if (Convert.ToInt32(c.DayOfWeek) == 5) c.AddDays(-4);
if (Convert.ToInt32(c.DayOfWeek) == 6) c.AddDays(-5);
if (Convert.ToInt32(c.DayOfWeek) == 7) c.AddDays(-6);
if (isPasqua(a))
{
b = GetPasqua(b.Year);
b.AddDays(-6);
c = GetPasqua(c.Year);
c.AddDays(-6);
}
if (isPasqua(b))
{
a = GetPasqua(a.Year);
a.AddDays(-6);
c = GetPasqua(c.Year);
c.AddDays(-6);
}
if (isPasqua(c))
{
b = GetPasqua(b.Year);
b.AddDays(-6);
a = GetPasqua(a.Year);
a.AddDays(-6);
}
try
{
SqlCommand cmd = new SqlCommand(testoquery, cn);
cmd.Parameters.AddWithValue("@dataInizio2", c);
cmd.Parameters.AddWithValue("@dataFine2", c.AddDays(6));
cmd.Parameters.AddWithValue("@eventi", pippo);
cn.Open();
myReader = cmd.ExecuteReader();
dt.Load(myReader);
myReader.Close();
}
catch (SqlException)
{
}
finally
{
cn.Close();
}
return dt;
}
protected void gvdati_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
//Build custom header.
GridView oGridView = (GridView)sender;
GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
TableCell oTableCell = new TableCell();
oTableCell.Text = "settimana corrente " + DateTime.Now.AddYears(-2).Year;
oTableCell.ColumnSpan = 2;
oGridViewRow.Cells.Add(oTableCell);
oGridView.Controls[0].Controls.AddAt(0, oGridViewRow);
}
}
protected void gvdati01_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
//Build custom header.
GridView oGridView = (GridView)sender;
GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
TableCell oTableCell = new TableCell();
oTableCell.Text = "settimana corrente " + DateTime.Now.AddYears(-1).Year;
oTableCell.ColumnSpan = 2;
oGridViewRow.Cells.Add(oTableCell);
oGridView.Controls[0].Controls.AddAt(0, oGridViewRow);
}
}
protected void gvdati02_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
//Build custom header.
GridView oGridView = (GridView)sender;
GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
TableCell oTableCell = new TableCell();
oTableCell.Text = "settimana corrente " + DateTime.Now.Year;
oTableCell.ColumnSpan = 2;
oGridViewRow.Cells.Add(oTableCell);
oGridView.Controls[0].Controls.AddAt(0, oGridViewRow);
}
}
}