public List <WaitListObject> GetWaitList(string cprNo)
{
/**************** FROM WEB SERVICE ****************/
string cprNoFormatted = cprNo.Insert(6, "0");
RestEgBoligTest.EgBoligService.Service10540Client svc = new RestEgBoligTest.EgBoligService.Service10540Client();
RestEgBoligTest.EgBoligService.Member[] memberFromService = svc.MemberGetListByCprNo(cprNoFormatted, false);
short MemberCompanyNo = memberFromService[0].MemberCompanyNo;
decimal MemberNo = memberFromService[0].MemberNo;
List <WaitListObject> waitList = new List <WaitListObject>();
RestEgBoligTest.EgBoligService.WaitList[] waitListObjectFromService = svc.WaitListGetList(MemberCompanyNo, MemberNo);
foreach (RestEgBoligTest.EgBoligService.WaitList value in waitListObjectFromService)
{
/***************** FROM DATABASE *****************/
//ToString get address and postalcodecity
SqlConnection connection = new SqlConnection(@"Data Source=HAXDMA49; Initial Catalog=Bolig2; Integrated Security=False; User ID=EGBoligWS; Password=zYnc6hvWeytL9AVe; Multipleactiveresultsets=True; App=EntityFramework");
connection.Open();
string sqlGetFromAfdeling = "select Top(1) navn, adresse, postby from [Bolig2].[dbo].[Afdeling] where sel = " + value.CompanyNo + " and afd = " + value.DepartmentNo;
SqlCommand cmd = new SqlCommand(sqlGetFromAfdeling, connection);
string navn = "";
string address = "";
string postalCodeCity = "";
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
navn = dr["navn"].ToString();
address = dr["adresse"].ToString();
postalCodeCity = dr["postby"].ToString();
}
}
//to get areal average and price average
string sqlGetFromLejemaal = "select bareal, vurd from [Bolig2].[dbo].[Lejemaal] where sel = " + value.CompanyNo + " and afd = " + value.DepartmentNo + " and antrum = " + value.Rooms + " and lmtype = " + value.TenancyType;
SqlCommand cmd1 = new SqlCommand(sqlGetFromLejemaal, connection);
List <decimal> arealList = new List <decimal>();
decimal arealAverage = 0;
List <decimal> priceList = new List <decimal>();
decimal priceAverage = 0;
using (SqlDataReader dr = cmd1.ExecuteReader())
{
while (dr.Read())
{
string areal = "";
areal = dr["bareal"].ToString();
string price = "";
price = dr["vurd"].ToString();
if (areal != "")
{
arealList.Add(Convert.ToDecimal(areal));
}
if (price != "")
{
priceList.Add(Convert.ToDecimal(price));
}
}
}
// claculate areage of areal and price
if (arealList.Count == 0)
{
arealAverage = 0;
}
else
{
arealAverage = arealList.Average();
}
if (priceList.Count == 0)
{
priceAverage = 0;
}
else
{
priceAverage = priceList.Average();
}
connection.Close();
WaitListObject waitListObject = new WaitListObject();
waitListObject.CompanyNo = value.CompanyNo;
waitListObject.DepartmentNo = value.DepartmentNo;
waitListObject.Type = value.TenancyType;
waitListObject.Name = navn;
waitListObject.Address = address;
waitListObject.PostalCodeCity = postalCodeCity;
waitListObject.Rooms = value.Rooms;
waitListObject.Areal = decimal.Round(arealAverage);
waitListObject.Price = decimal.Round(priceAverage);
waitListObject.Amount = arealList.Count;
waitListObject.NumberOnList = value.PriorityNoActive;
waitList.Add(waitListObject);
}
return(waitList);
}
public List <WaitListObject> GetAllDepartments()
{
// connectionstring
SqlConnection connection = new SqlConnection(@"Data Source=HAXDMA49; Initial Catalog=Bolig2; Integrated Security=False; User ID=EGBoligWS; Password=zYnc6hvWeytL9AVe; Multipleactiveresultsets=True; App=EntityFramework");
connection.Open();
// SQL for Kontrakt table in Bolig2 database - Update comment
string sqlGetDepartment = "SELECT count(*) as amount, afd.[Sel] as company, afd.[Afd] as department, afd.navn as name, afd.Adresse as address, afd.postby as postalCodeCity, lm.Lmtype as type, Antrum FROM[Bolig2].[dbo].[Lejemaal] as lm inner join[Bolig2].[dbo].[Afdeling] as afd on lm.Afd = afd.afd and afd.sel = lm.sel where lmtype in (1, 4, 7) and afd.sel = 1 group by afd.[Sel], afd.[Afd], afd.navn, afd.Adresse, afd.postby, Antrum, lm.Lmtype order by afd.afd";
SqlCommand cmdGetComment = new SqlCommand(sqlGetDepartment, connection);
short company = 0;
int amount = 0;
short department = 0;
string name = "";
string address = "";
string postalCodeCity = "";
short type = 0;
short rooms = 0;
List <WaitListObject> listOfDepartments = new List <WaitListObject>();
// get dat from MedlemAfSelskab table
using (SqlDataReader dr = cmdGetComment.ExecuteReader())
{
while (dr.Read())
{
company = Convert.ToInt16(dr["company"]);
amount = Convert.ToInt32(dr["amount"]);
department = Convert.ToInt16(dr["department"]);
name = dr["name"].ToString();
address = dr["address"].ToString();
postalCodeCity = dr["postalCodeCity"].ToString();
type = Convert.ToInt16(dr["type"]);
rooms = Convert.ToInt16(dr["Antrum"]);
WaitListObject wishListObject = new WaitListObject();
// Areal og prísur verður definerað seinni, tí tað samsvarar ikki við tað sum er í GetWishList (øll feltini eru ikki definerað)
wishListObject.CompanyNo = company;
wishListObject.DepartmentNo = department;
wishListObject.Type = type;
wishListObject.Name = name;
wishListObject.Address = address;
wishListObject.PostalCodeCity = postalCodeCity;
wishListObject.Rooms = rooms;
wishListObject.Amount = amount;
listOfDepartments.Add(wishListObject);
}
}
connection.Close();
return(listOfDepartments);
/*
* SELECT count(*) as Antal
* , afd.[Afd]
* , afd.navn
* , afd.Adresse
* , afd.postby
* , lm.Lmtype
* , Antrum
* FROM [Bolig2].[dbo].[Lejemaal] as lm inner join [Bolig2].[dbo].[Afdeling] as afd on lm.Afd = afd.afd and afd.sel = lm.sel
* where lmtype in (1,4,7) and afd.sel=1
* group by afd.[Afd]
* , afd.navn
* ,afd.Adresse
* ,afd.postby
* ,Antrum
* , lm.Lmtype
* order by afd.afd
*
*
* // Get all departments that have the tenancyType 1, 4 or 7
*
* RestEgBoligTest.EgBoligService.Service10540Client svc = new RestEgBoligTest.EgBoligService.Service10540Client();
*
* List<WaitListObject> listOfDepartments = new List<WaitListObject>();
*
* // there are not more than 100 departments
* for(short i = 1; i<100; i++)
* {
* RestEgBoligTest.EgBoligService.Department departmentFromService = svc.DepartmentGet(1,i);
*
* // if Department(companyNo, departmentNo) contains a department
* if(departmentFromService != null)
* {
* // go thorugh every TenancyQuantity in departmentFromService
* foreach(var tenancyQuantity in departmentFromService.TenancyQuantities)
* {
* // vanligan-, lestrar- og eldrabústað
* if(tenancyQuantity.TenancyType == 1 || tenancyQuantity.TenancyType == 4 || tenancyQuantity.TenancyType == 7)
* {
* WaitListObject wishListObject = new WaitListObject();
*
* // Areal og prísur verður definerað seinni, tí tað samsvarar ikki við tað sum er í GetWishList (øll feltini eru ikki definerað)
* wishListObject.CompanyNo = departmentFromService.CompanyNo;
* wishListObject.DepartmentNo = departmentFromService.DepartmentNo;
* wishListObject.Type = tenancyQuantity.TenancyType;
* wishListObject.Address = departmentFromService.Address;
* wishListObject.PostalCodeCity = departmentFromService.ZipCity;
* wishListObject.Rooms = tenancyQuantity.Rooms;
* wishListObject.Amount = tenancyQuantity.Count;
*
* listOfDepartments.Add(wishListObject);
* }
* }
* }
* }
* return listOfDepartments;
*/
}
