public List<Zone> GetZoneWiseVisitors(out int zoneWiseTotal)
{
SqlConnection connection = new SqlConnection(connectionString);
List<Zone> zones = new List<Zone>();
string query = string.Format(@"select tbl_ZoneType.ZoneType , COUNT(tbl_Visitor.Email)
from tbl_ZoneType join tbl_VisitorZone on tbl_ZoneType.ID = tbl_VisitorZone.ZID
join tbl_Visitor on tbl_VisitorZone.VID = tbl_Visitor.ID
group by tbl_Zonetype.ZoneType");
SqlCommand cmd = new SqlCommand(query,connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Zone zone = new Zone();
zone.ZoneType = reader["ZoneType"].ToString();
zone.zoneWiseTotalVisitor = Convert.ToInt16(reader[1]);
zones.Add(zone);
}
connection.Close();
zoneWiseTotal = GetTotalVisitor();
return zones;
}
public string Save(Zone aZone)
{
if(gateway.HasZonExists(aZone.ZoneType))
{
return "Zone Type Already Exists";
}
else
{
return gateway.Save(aZone);
}
}
public List<Zone> LoadCheckedListBox()
{
List<Zone> zoneList = new List<Zone>();
SqlConnection connection = new SqlConnection(connectionString);
string query = string.Format("SELECT * FROM tbl_ZoneType ");
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Zone aZone = new Zone();
aZone.ID = Convert.ToInt32(reader["ID"]);
aZone.ZoneType = reader["ZoneType"].ToString();
zoneList.Add(aZone);
}
reader.Close();
connection.Close();
return zoneList;
}
public string Save(Zone aZone)
{
string message = "";
SqlConnection connection = new SqlConnection(connectionString);
string query = string.Format("INSERT INTO tbl_ZoneType VALUES('{0}')",aZone.ZoneType);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
message = aZone.ZoneType + " has been saved";
return message;
}