public ActionResult getListings()
{
sqlServer db = new sqlServer(connString);
var listings = db.fetch("exec realestate.getListings").Tables[0].AsEnumerable();
ViewBag.data = listings;
return PartialView("index1/listings");
}
public JsonResult getListingByFilter()
{
var pars=Request.Params;
sqlServer db = new sqlServer(connString);
List<sqlServerPar> sppars=new List<sqlServerPar>();
sppars.Add(new sqlServerPar { name = "bdr", value = pars["bdr"],dbType=SqlDbType.VarChar });
sppars.Add(new sqlServerPar { name = "bath", value = pars["bath"], dbType = SqlDbType.VarChar });
sppars.Add(new sqlServerPar { name = "pricestart", value = pars["pricerange"].Split(new char[]{','})[0], dbType = SqlDbType.VarChar });
sppars.Add(new sqlServerPar { name = "priceend", value = pars["pricerange"].Split(new char[] { ',' })[1], dbType = SqlDbType.VarChar });
sppars.Add(new sqlServerPar { name = "ptypes", value = pars["ptypes"], dbType = SqlDbType.VarChar });
sppars.Add(new sqlServerPar { name = "showSelection", value = pars["showSelection"], dbType = SqlDbType.VarChar });
var listings = db.runSP("realestate.getListingByFilter",sppars).Tables[0].AsEnumerable().Select(x=>x.ItemArray);
return Json(listings,JsonRequestBehavior.AllowGet);
}
public static organization getOrgDetails(string org_id)
{
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
DataTable dt = db.fetch("select * from organization where org_id=" + org_id).Tables[0];
organization data = new organization();
data =
new organization()
{
org_id = dt.Rows[0][0].ToString(),
parent_org_id = dt.Rows[0][1].ToString(),
org_name = dt.Rows[0][2].ToString(),
street = dt.Rows[0][3].ToString(),
city = dt.Rows[0][4].ToString(),
postalcode = dt.Rows[0][5].ToString()
};
return data;
}
public object getTransactions()
{
string connString = "server=s06.winhost.com;uid=DB_40114_codeclub_user;pwd=p0$31d0n;database=DB_40114_codeclub";
sqlServer db = new sqlServer(connString);
var data = db.fetch("select * from accounting.categoryType ct " +
"inner join accounting.gltype t on t.id=ct.glTypeID " +
"inner join Accounting.account a on a.catTypeID=ct.ID " +
"full join Accounting.[transaction] trans on trans.accountid=a.ID")
.Tables[0]
.AsEnumerable()
.Select(r => new
{
catTypeID = r.ItemArray[0],
name = r.ItemArray[1]
});
return data;
}
public string header_update()
{
var pars = Request.Params;
string header = pars["header"];
string headerid = pars["headerid"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
db.exec(string.Format("update sandbox.header set label='{0}' where id='{1}'", header,headerid));
return "";
}
public string header_savenew()
{
var pars=Request.Params;
string header=pars["header"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
db.exec(string.Format("insert into sandbox.header (label) values('{0}')",header));
return "";
}
public string header_delete()
{
var pars = Request.Params;
string headerid = pars["headerid"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
db.exec(string.Format("delete from sandbox.header where ID='{0}'", headerid));
return "";
}
public string headercontent_update()
{
var pars = Request.Params;
string headerid = pars["headerid"];
string url = pars["viewurl"];
string content = pars["description"];
string label = pars["label"];
string contentid = pars["contentid"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
db.exec(string.Format("update sandbox.content set content='{0}' ,label = '{1}', viewurl ='{2}' where id='{3}';"
, content, label, url,contentid));
db.exec(string.Format("update sandbox.headercontent set headerID='{0}' where contentID='{1}'"
, headerid, contentid));
return "";
}
public string headercontent_savenew()
{
var pars = Request.Params;
string headerid = pars["headerid"];
string url = pars["viewurl"];
string content = pars["description"];
string label = pars["label"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
DataTable dt1 = db.fetch(string.Format("insert into sandbox.content (content,label,viewurl) values('{0}','{1}','{2}');select SCOPE_IDENTITY();", content, label, url)).Tables[0];
var contentid = dt1.Rows[0][0].ToString();
db.exec(string.Format("insert into sandbox.headercontent (headerId,contentid) values('{0}','{1}')", headerid, contentid));
return "";
}
public string headercontent_delete()
{
var pars = Request.Params;
string contentid = pars["contentid"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
db.fetch(string.Format("delete from sandbox.headercontent where contentID='{0}' ;", contentid));
db.fetch(string.Format("delete from sandbox.content where contentID='{0}' ;", contentid));
return "";
}
//DS
public ActionResult getHeaders()
{
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
var source = db.fetch("select id,label from sandbox.header").Tables[0]
.AsEnumerable()
.Select(x => new
{
id = x.ItemArray[0],
label = x.ItemArray[1]
});
return Json(source, JsonRequestBehavior.AllowGet);
}
public ActionResult getHeaderContents()
{
var query =
"select id,label from sandbox.header;"
+ "select hc.ID as headerContentID,h.id as headerID , contentID,c.label , content,viewurl from sandbox.header h "
+ "inner join sandbox.headerContent hc on hc.headerID=h.ID "
+ "inner join sandbox.Content c on hc.contentID=c.ID "
+ " order by h.id"
;
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhost"].ConnectionString);
var ds = db.fetch(query);
var headers = ds.Tables[0]
.AsEnumerable()
.Select(x => new
{
id = x.ItemArray[0],
label = x.ItemArray[1]
});
var headerContents = ds.Tables[1]
.AsEnumerable()
.Select(x => new
{
headerContentID = x.ItemArray[0],
headerID = x.ItemArray[1],
contentID = x.ItemArray[2],
contentLabel = x.ItemArray[3],
content = x.ItemArray[4],
viewurl = x.ItemArray[5]
})
.OrderBy(x => x.headerID);
return Json(new { headers = headers, headerContents = headerContents }, JsonRequestBehavior.AllowGet);
}
public JsonResult transactionTypes()
{
sqlServer db = new sqlServer(connString);
var data = db.fetch("select id,name from accounting.categoryType")
.Tables[0]
.AsEnumerable()
.Select(r=>new
{
id=r.ItemArray[0] ,
name = r.ItemArray[1]
});
return Json(data,JsonRequestBehavior.AllowGet);
}
