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; */ }