//build Organizations Hierarchy tree
public JsonResult json_readWorkFlow()
{
sqlServer db = new sqlServer(connString);
DataTable dt = db.fetch("select * from dbo.organization").Tables[0];
//build tree
List <Classes.organization> tree = new List <Classes.organization> {
};
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["parent_org_id"].ToString() == "")
{
var node = new Classes.organization()
{
org_id = dt.Rows[i][0].ToString(),
parent_org_id = dt.Rows[i][1].ToString(),
org_name = dt.Rows[i][2].ToString(),
logo = "/jsplugins/workflow/images/" + dt.Rows[i][6].ToString(),
children = new List <Classes.organization>(),
//assignedUsers = this.getTaskAssignedUsers(Convert.ToUInt16(dt.Rows[i][0]))
};
tree.Add(node);
}
}
for (int j = 0; j < tree.Count; j++)
{
this.RecTree(tree[j], dt);
}
return(Json(tree, 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 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 int createNew(int entityID, int catTypeID, decimal amount, int currencyID)
{
sqlServer db = new sqlServer(sqlServer.connString1);
var accountID = Account.getAccount(entityID, catTypeID, currencyID).ID;
SqlConnection sqlconnection = new SqlConnection(sqlServer.connString1);
sqlconnection.Open();
SqlDataAdapter da = new SqlDataAdapter(
//string.Format("exec Accounting.newTransaction @accountId={0},@amount={1}",accountID,amount)
"accounting.test "
, sqlconnection);
DataSet ds = new DataSet();
da.Fill(ds);
sqlconnection.Close();
return(Convert.ToInt32(ds.Tables[0].Rows[0][0]));
}
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 json_getTopicDetails()
{
var pars = Request.Params;
int topic_id = Convert.ToInt32(pars["topic_id"]);
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
DataTable dt = db.fetch("select topic_details from saeid.topic where is_active=1 and topic_id=" + topic_id + " order by datetime").Tables[0];
return(Json(new { result = dt.Rows[0][0].ToString() }));
}
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 JsonResult json_deleteTopic()
{
var pars = Request.Params;
string topic_id = pars["topic_id"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
db.exec("update saeid.topic set is_active=0 , datetime='" + DateTime.Now.Ticks + "' where topic_id=" + topic_id);
return Json(new { result = "Done" }, JsonRequestBehavior.AllowGet);
}
public void reset()
{
sqlServer db = new sqlServer("server=s06.winhost.com;database=DB_40114_codeclub;uid=DB_40114_codeclub_user;pwd=p0$31d0n;");
db.exec("[Accounting].[resetSeeds]");
var cur_ca = new accounting.classes.Currency();
cur_ca.create("CAD", (int)accounting.classes.enums.currencyType.Real);
}
public JsonResult json_deleteTopic()
{
var pars = Request.Params;
string topic_id = pars["topic_id"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
db.exec("update saeid.topic set is_active=0 , datetime='" + DateTime.Now.Ticks + "' where topic_id=" + topic_id);
return(Json(new { result = "Done" }, JsonRequestBehavior.AllowGet));
}
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 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 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));
}
public JsonResult json_saveSelectedTopic()
{
var pars = Request.Params;
string topic_id = pars["foldering_editPanel_id"];
string topic_title = pars["foldering_editPanel_title"];
string topic_details = pars["foldering_editPanel_detail"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
db.exec("update saeid.topic set topic_title='" + topic_title + "' , topic_details='" + topic_details + "',datetime='" + DateTime.Now.Ticks + "' where topic_id=" + topic_id);
return(Json(new { result = DateTime.Now.Ticks }, JsonRequestBehavior.AllowGet));
}
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 JsonResult json_saveOrdering()
{
var pars = Request.Params;
string[] ordering = pars["newOrdering"].Split('-');
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
foreach (var item in ordering)
{
string parentId = item.Split(',')[0];
string id = item.Split(',')[1];
db.exec("update saeid.topic set topic_parent_id=" + ((parentId == "") ? null : "'" + parentId + "'") + " , datetime='" + DateTime.Now.Ticks + "' where topic_id=" + id);
}
return(Json(new { result = "Done" }, JsonRequestBehavior.AllowGet));
}
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 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 static int createNew(int entityID, int catTypeID, decimal amount, int currencyID)
{
sqlServer db=new sqlServer(sqlServer.connString1);
var accountID = Account.getAccount(entityID, catTypeID, currencyID).ID;
SqlConnection sqlconnection = new SqlConnection(sqlServer.connString1);
sqlconnection.Open();
SqlDataAdapter da = new SqlDataAdapter(
//string.Format("exec Accounting.newTransaction @accountId={0},@amount={1}",accountID,amount)
"accounting.test "
, sqlconnection);
DataSet ds = new DataSet();
da.Fill(ds);
sqlconnection.Close();
return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
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 JsonResult json_createNewTopic()
{
var pars = Request.Params;
string topic_parent_id = pars["foldering_editPanel_id"];
string topic_title = pars["foldering_editPanel_title"];
string topic_details = pars["foldering_editPanel_detail"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
if (topic_parent_id == "")
{
db.exec("insert into saeid.topic (topic_title,topic_details,datetime)values("
+"'" + topic_title + "','" + topic_details + "','" + DateTime.Now.Ticks + "')");
}
else
{
db.exec("insert into saeid.topic (topic_parent_id,topic_title,topic_details,datetime)values("
+ topic_parent_id + ",'" + topic_title + "','" + topic_details + "','" + DateTime.Now.Ticks + "')");
}
return Json(new { result = "Done" }, JsonRequestBehavior.AllowGet);
}
public JsonResult json_createNewTopic()
{
var pars = Request.Params;
string topic_parent_id = pars["foldering_editPanel_id"];
string topic_title = pars["foldering_editPanel_title"];
string topic_details = pars["foldering_editPanel_detail"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
if (topic_parent_id == "")
{
db.exec("insert into saeid.topic (topic_title,topic_details,datetime)values("
+ "'" + topic_title + "','" + topic_details + "','" + DateTime.Now.Ticks + "')");
}
else
{
db.exec("insert into saeid.topic (topic_parent_id,topic_title,topic_details,datetime)values("
+ topic_parent_id + ",'" + topic_title + "','" + topic_details + "','" + DateTime.Now.Ticks + "')");
}
return(Json(new { result = "Done" }, JsonRequestBehavior.AllowGet));
}
//tik
public JsonResult json_getTopics()
{
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
DataTable dt = db.fetch(
"select topic_id as type_id,topic_title as type_title, topic_details as type_detail, topic_parent_id as parent_type_id,topic_title as type_name,is_active " +
" from saeid.topic where is_active=1 order by datetime").Tables[0];
List <menu> tree = new List <menu> {
};
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["parent_type_id"].ToString() == "")
{
var node = new menu()
{
id = dt.Rows[i]["type_id"].ToString(),
iconCls = "treei",
type_name = dt.Rows[i]["type_name"].ToString(),
parent_type_id = dt.Rows[i]["parent_type_id"].ToString(),
type_title = dt.Rows[i]["type_title"].ToString(),
type_detail = dt.Rows[i]["type_detail"].ToString(),
children = new List <menu>()
};
tree.Add(node);
}
}
for (int j = 0; j < tree.Count; j++)
{
Rec(tree[j], dt);
}
return(Json(tree, 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 JsonResult getOrgUsers()
{
var pars = Request.Params;
int orgID=Convert.ToInt32(pars["orgID"]);
sqlServer db = new sqlServer(connString);
var data= db.fetch("exec workflow.getOrgUsers @orgID =" + orgID).Tables[0].AsEnumerable();
List<dynamic> result = new List<dynamic>();
foreach (var item in data)
result.Add(new
{
person_id=item["person_id"],
name = item["name"]
});
return Json(result,JsonRequestBehavior.AllowGet);
}
//get Users assigned to Task
public IEnumerable<object> getTaskAssignedUsers(int task_id)
{
sqlServer db=new sqlServer(connString);
return db.fetch("exec [workflow].[getTaskAssignedUsers] @taskID="+task_id).Tables[0].AsEnumerable()
.Select(x => new
{
org_id=x["org_id"].ToString(),
person_id = x["person_id"].ToString(),
fname = x["fname"].ToString(),
lname = x["lname"].ToString()
});
}
public JsonResult json_getTopicDetails()
{
var pars = Request.Params;
int topic_id = Convert.ToInt32(pars["topic_id"]);
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
DataTable dt = db.fetch("select topic_details from saeid.topic where is_active=1 and topic_id=" + topic_id + " order by datetime").Tables[0];
return Json(new { result = dt.Rows[0][0].ToString() });
}
//tik
public JsonResult json_getTopics()
{
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
DataTable dt = db.fetch(
"select topic_id as type_id,topic_title as type_title, topic_details as type_detail, topic_parent_id as parent_type_id,topic_title as type_name,is_active " +
" from saeid.topic where is_active=1 order by datetime").Tables[0];
List<menu> tree = new List<menu> { };
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["parent_type_id"].ToString() == "")
{
var node = new menu()
{
id = dt.Rows[i]["type_id"].ToString(),
iconCls = "treei",
type_name = dt.Rows[i]["type_name"].ToString(),
parent_type_id = dt.Rows[i]["parent_type_id"].ToString(),
type_title = dt.Rows[i]["type_title"].ToString(),
type_detail = dt.Rows[i]["type_detail"].ToString(),
children = new List<menu>()
};
tree.Add(node);
}
}
for (int j = 0; j < tree.Count; j++)
Rec(tree[j], dt);
return Json(tree, JsonRequestBehavior.AllowGet);
}
public JsonResult json_saveOrdering()
{
var pars = Request.Params;
string[] ordering = pars["newOrdering"].Split('-');
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
foreach (var item in ordering)
{
string parentId = item.Split(',')[0];
string id = item.Split(',')[1];
db.exec("update saeid.topic set topic_parent_id=" + ((parentId == "") ? null : "'" + parentId + "'") + " , datetime='" + DateTime.Now.Ticks + "' where topic_id=" + id);
}
return Json(new { result = "Done" }, JsonRequestBehavior.AllowGet);
}
public JsonResult json_saveSelectedTopic()
{
var pars = Request.Params;
string topic_id = pars["foldering_editPanel_id"];
string topic_title = pars["foldering_editPanel_title"];
string topic_details = pars["foldering_editPanel_detail"];
sqlServer db = new sqlServer(ConfigurationManager.ConnectionStrings["winhostConnection"].ConnectionString);
db.exec("update saeid.topic set topic_title='" + topic_title + "' , topic_details='" + topic_details + "',datetime='" + DateTime.Now.Ticks + "' where topic_id=" + topic_id);
return Json(new { result = DateTime.Now.Ticks }, JsonRequestBehavior.AllowGet);
}
