public string AddWish(string cprNo, short companyNo, short departmentNo, byte rooms, short?type)
{
/**************** 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;
// to get autoNo
RestEgBoligTest.EgBoligService.Member member = svc.MemberGet(memberCompanyNo, memberNo);
// define wish
RestEgBoligTest.EgBoligService.Wish wish = new RestEgBoligTest.EgBoligService.Wish();
wish.MemberCompanyNo = memberCompanyNo;
wish.MemberNo = memberNo;
wish.AutoNo = member.AutoNo;
wish.CompanyNo = companyNo;
wish.DepartmentNo = departmentNo;
wish.FloorMax = 99;
wish.FloorMin = -1;
wish.Room = rooms;
wish.SqmMax = 9999;
wish.SqmMin = 0;
wish.TenancyType = type;
// add wish
svc.WishAdd(wish, null);
return("Wish added");
}
public string DeleteWish(string cprNo, short companyNo, short departmentNo, byte rooms, short?type)
{
/**************** 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;
// this method splits the wishes to seperate collums and makes it possible to delete a specific wish
svc.WishGetList(memberCompanyNo, memberNo);
//define wish to delete
RestEgBoligTest.EgBoligService.Wish wishDelete = new RestEgBoligTest.EgBoligService.Wish();
/**************** FROM DATABASE ****************/
// connectionstring
SqlConnection cn = new SqlConnection(@"Data Source=HAXDMA49; Initial Catalog=Bolig2; Integrated Security=False; User ID=EGBoligWS; Password=zYnc6hvWeytL9AVe; Multipleactiveresultsets=True; App=EntityFramework");
cn.Open();
// SQL for Kontrakt table in Bolig2 database
string sql = "select autonum from [Bolig2].[dbo].[Medlafd] where sel = " + memberCompanyNo + " and medlem = " + memberNo + " and selmin = " + companyNo + " and selmax = " + companyNo + " and afdmin = " + departmentNo + " and afdmax = " + departmentNo + " and rummin = " + rooms + " and rummax = " + rooms + " and lmtype1 = " + type;
SqlCommand cmd = new SqlCommand(sql, cn);
short autoNo = 0;
// get data from Kontrakt table
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
autoNo = Convert.ToInt16(dr["autonum"]);
wishDelete.MemberCompanyNo = memberCompanyNo;
wishDelete.MemberNo = memberNo;
wishDelete.AutoNo = autoNo;
wishDelete.CompanyNo = companyNo;
wishDelete.DepartmentNo = departmentNo;
wishDelete.Room = rooms;
wishDelete.TenancyType = type;
svc.WishDelete(wishDelete);
}
}
cn.Close();
return("Wish deleted!");
}
public string UpdateMember(string cprNo, string country, string postalCodeCity, string address, string email, string homePhone, string mobilePhone, short children, [FromBody] string comment)
{
/**************** 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;
int interessentNo = memberFromService[0].InteressentNo;
decimal autoNo = memberFromService[0].AutoNo;
string type = memberFromService[0].Type;
/**************** FROM DATABASE ****************/
// 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 sqlSetComment = "update Top(1) [Bolig2].[dbo].[Medlem] set krittext = '" + comment + "' where sel = " + memberCompanyNo + " and medlem = " + memberNo;
SqlCommand cmd = new SqlCommand(sqlSetComment, connection);
// execute command
cmd.ExecuteReader();
connection.Close();
//define member to update
RestEgBoligTest.EgBoligService.Member memberUpdate = new RestEgBoligTest.EgBoligService.Member();
memberUpdate.MemberCompanyNo = memberCompanyNo;
memberUpdate.MemberNo = memberNo;
memberUpdate.InteressentNo = interessentNo;
memberUpdate.AutoNo = autoNo;
memberUpdate.Type = type;
memberUpdate.Country = country;
memberUpdate.PostalCodeCity = postalCodeCity;
memberUpdate.Address = address;
memberUpdate.Email = email;
memberUpdate.HomePhone = homePhone;
memberUpdate.MobilePhone = mobilePhone;
memberUpdate.Children = children;
// comment
// Update member in service
svc.MemberUpdate(memberUpdate);
return("Member " + memberUpdate.Name + " updated!");
}
public string ChangeStatus(string cprNo, string lmType, string status)
{
/**************** 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;
/**************** FROM DATABASE ****************/
SqlConnection cn = new SqlConnection(@"Data Source=HAXDMA49; Initial Catalog=Bolig2; Integrated Security=False; User ID=EGBoligWS; Password=zYnc6hvWeytL9AVe; Multipleactiveresultsets=True; App=EntityFramework");
// SQL for status type 7 in MedlemAfSelskab table in Bolig2 database
string sqlGet = "select Top(1) status, lmtype from [Bolig2].[dbo].[MedlemAfSelskab] where sel = " + memberCompanyNo + " and medlem = " + memberNo + " and lmtype = " + lmType;
SqlCommand cmd = new SqlCommand(sqlGet, cn);
string sqlSet = "update [Bolig2].[dbo].[MedlemAfSelskab] set status = " + status + " where sel = " + memberCompanyNo + " and medlem = " + memberNo + " and lmtype =" + lmType;
SqlCommand cmd1 = new SqlCommand(sqlSet, cn);
cn.Open();
// get dat from MedlemAfSelskab table
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
if (dr["lmtype"].ToString() == "1" || dr["lmtype"].ToString() == "4" || dr["lmtype"].ToString() == "7")
{
if (dr["status"].ToString() != "0" && status == "0" || dr["status"].ToString() != "1" && status == "1" || dr["status"].ToString() != "3" && status == "3")
{
cmd1.ExecuteReader();
return("LMType " + lmType + " changed status to " + status);
}
else
{
return("Status " + status + " is invalid");
}
}
}
}
return("Not possible!");
}
public Member GetMember(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);
/**************** FROM DATABASE ****************/
// 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 to get status for LMtypes
string sqlGetStatus = "select Lmtype, status from [Bolig2].[dbo].[MedlemAfSelskab] where sel = " + memberFromService[0].MemberCompanyNo + " and medlem = " + memberFromService[0].MemberNo;
SqlCommand cmdGetStatus = new SqlCommand(sqlGetStatus, connection);
string statusForType1 = "";
string statusForType4 = "";
string statusForType7 = "";
using (SqlDataReader dr = cmdGetStatus.ExecuteReader())
{
// Loop to find all LMtypes and store status
while (dr.Read())
{
if (dr["Lmtype"].ToString() == "1")
{
statusForType1 = dr["status"].ToString();
}
if (dr["Lmtype"].ToString() == "4")
{
statusForType4 = dr["status"].ToString();
}
if (dr["Lmtype"].ToString() == "7")
{
statusForType7 = dr["status"].ToString();
}
}
}
// SQL for comment in Medlem table in Bolig2 database
string sqlGetComment = "select Top(1) krittext from [Bolig2].[dbo].[Medlem] where sel = " + memberFromService[0].MemberCompanyNo + " and medlem = " + memberFromService[0].MemberNo;
SqlCommand cmdGetComment = new SqlCommand(sqlGetComment, connection);
string comment = "";
// get dat from MedlemAfSelskab table
using (SqlDataReader dr = cmdGetComment.ExecuteReader())
{
if (dr.Read())
{
comment = dr["krittext"].ToString();
}
}
connection.Close();
/**************** DEFINE MEMBER TO RETURN ****************/
Member member = new Member();
member.MemberCompanyNo = memberFromService[0].MemberCompanyNo;
member.MemberNo = memberFromService[0].MemberNo;
member.Name = memberFromService[0].Name;
member.Address = memberFromService[0].Address;
member.PostalCodeCity = memberFromService[0].PostalCodeCity;
member.Country = memberFromService[0].Country;
member.CprNo = (memberFromService[0].CprNo).Remove(6, 1);
member.Email = memberFromService[0].Email;
member.HomePhone = memberFromService[0].HomePhone;
member.MobilePhone = memberFromService[0].MobilePhone;
member.Children = memberFromService[0].Children;
member.StatusForType1 = statusForType1;
member.StatusForType4 = statusForType4;
member.StatusForType7 = statusForType7;
member.Comment = comment;
/**************** RETURN MEMBER ****************/
return(member);
}
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);
}