public async Task <IActionResult> GetUserByID(string user_id)
{
string Msg = "";
if (user_id == null)
{
return(Content("{\"code\":300,\"msg\":\"请传入关键值\"}"));
}
string sql = @"select u.user_id,u.user_code,u.user_name,u.person_id,p.person_name from app_user u,app_person p where u.person_id=p.person_id(+) and u.user_id=:user_id";
OracleParameter[] sp = { data.MakeInParam(":user_id", user_id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetMenuInfoById(string menu_id)
{
string Msg = "";
if (menu_id == null || menu_id == "")
{
return(Content("{\"code\":300,\"msg\":\"请传入关键值\"}"));
}
string sql = @"select m.menu_id , m.parent_menu_id , m.menu_code, m.menu_name, m.menu_icon, M.menu_type, m.menu_url, m.menu_sort from app_menu m where m.menu_id=:menu_id";
OracleParameter[] sp = { data.MakeInParam(":menu_id", menu_id ?? "") };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":1,\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetFixValue(string fixvalue, string fixvaluetypeid, int page, int limit)
{
if (fixvaluetypeid == null)
{
return(Content("{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}"));
}
string Msg = "";
string sqlpj = fixvalue == null ? " " : @" and (f.fixvalue_code like '%' || :code || '%' or f.fixvalue_name like '%' || :name || '%')";
string sqlpj1 = fixvalue == null ? " " : @" and (f.fixvalue_code like '%' || :code1 || '%' or f.fixvalue_name like '%' || :name1 || '%')";
string sql = @"select * from (select rownum as rowno,r.* from(select (select count(*) from app_fixvalue f, app_fixvalue_type ft
where f.fixvalue_type_id = ft.fixvalue_type_id " + sqlpj1 + @" and ft.fixvalue_type_id=:type_id1) totalPage,
f.fixvalue_id, f.fixvalue_code, f.fixvalue_name, f.note,f.fixvalue_type_id
from app_fixvalue f, app_fixvalue_type ft
where f.fixvalue_type_id = ft.fixvalue_type_id
" + sqlpj + @"
and ft.fixvalue_type_id = :type_id) r where rownum<= :page * :limit) table_alias where table_alias.rowno>( :page - 1) * :limit";
OracleParameter[] sp = fixvalue == null ?
new OracleParameter[] {
data.MakeInParam(":type_id1", fixvaluetypeid),
data.MakeInParam(":type_id", fixvaluetypeid),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
}
:
new OracleParameter[] {
data.MakeInParam(":code1", fixvalue ?? ""),
data.MakeInParam(":name1", fixvalue ?? ""),
data.MakeInParam(":type_id1", fixvaluetypeid),
data.MakeInParam(":code", fixvalue ?? ""),
data.MakeInParam(":name", fixvalue ?? ""),
data.MakeInParam(":type_id", fixvaluetypeid),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows[0]["totalPage"] + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetDeptById(string dept_id)
{
string Msg = "";
if (dept_id == null)
{
return(Content("{\"code\":300,\"msg\":\"请传入关键值\"}"));
}
string sql = @"select t.corp_id,t.dept_id,t.dept_code,t.dept_name from app_dept t where t.dept_id=:dept_id";
OracleParameter[] sp = { data.MakeInParam(":dept_id", dept_id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetPersonByID(string person_id)
{
string Msg = "";
if (person_id == null)
{
return(Content("{\"code\":300,\"msg\":\"请传入关键值\"}"));
}
string sql = @"Select t.Person_Id,
t.Person_Type,
t.Person_Code,
t.Person_Name,
t.Id_Card_Number,
t.Sex,
t.Mobile_Phone,
t.Fixed_Phone,
t.Email,
d.Corp_Id,
t.Dept_Id,
t.Post_Id
From App_Person t, App_Dept d
Where t.Dept_Id = d.Dept_Id(+)
And t.Person_Id = :person_id";
OracleParameter[] sp = { data.MakeInParam(":person_id", person_id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetButton(string button_name, string menu_id, string status, string role_id, int page, int limit)
{
string Msg = "";
string sqlpj = button_name == null ? "" : " and b.button_name like '%'|| :button_name ||'%' ";
string sql1 = status == "未选" ? @"Select count(m.Menu_Button_Id)
From App_Menu_Button m, App_Button b
Where m.Button_Id = b.Button_Id
And m.Menu_Id = :menu_id
And m.Button_Id Not In (Select Rb.Button_Id
From App_Role_Button Rb
Where Rb.Role_Id = :role
And Rb.Attribute1 = :menu_id1)
" + sqlpj :
@"Select count(rb.Role_Button_Id)
From App_Role_Button Rb, App_Button b
Where Rb.Button_Id = b.Button_Id(+)
And Rb.Role_Id = :role_id
And Rb.Attribute1 =:menu_id
And Rb.Button_Id In
(Select Button_Id From App_Menu_Button Where Menu_Id = :menu_id1)
" + sqlpj;
OracleParameter[] sp1 = { };
if (button_name == null)
{
if (status == "未选")
{
sp1 = new OracleParameter[] {
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id1", menu_id)
};
}
else
{
sp1 = new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":menu_id", menu_id)
};
}
}
else
{
if (status == "未选")
{
sp1 = new OracleParameter[] {
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id1", menu_id),
data.MakeInParam(":button_name", button_name ?? "")
};
}
else
{
sp1 = new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":button_name", button_name ?? "")
};
}
}
string n = await data.GetStringByParam(sql1, sp1);
string sql = status == "未选" ? @"SELECT * FROM (SELECT ROWNUM AS rowno, r.*
FROM (Select b.button_id,b.Button_Name,
b.Button_Icon,
b.Button_Event,
b.Button_Sort,
0 Role_Button_Id
From App_Menu_Button m, App_Button b
Where m.Button_Id = b.Button_Id
And m.Menu_Id = :menu_id
And m.Button_Id Not In (Select Rb.Button_Id
From App_Role_Button Rb
Where Rb.Role_Id = :role_id
And Rb.Attribute1 = :menu_id1)
" + sqlpj + @") r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit"
:
@"SELECT * FROM (SELECT ROWNUM AS rowno, r.*
FROM (Select b.button_id,b.Button_Name,
b.Button_Icon,
b.Button_Event,
b.Button_Sort,
Nvl(Rb.Role_Button_Id, 0) Role_Button_Id
From App_Role_Button Rb, App_Button b
Where Rb.Button_Id = b.Button_Id(+)
And Rb.Role_Id = :role_id
And Rb.Attribute1 =:menu_id
And Rb.Button_Id In
(Select Button_Id From App_Menu_Button Where Menu_Id = :menu_id1)
" + sqlpj + @") r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
OracleParameter[] sp = { };
if (button_name == null)
{
if (status == "未选")
{
sp = new OracleParameter[] {
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id1", menu_id),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
}
else
{
sp = new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":menu_id1", menu_id),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
}
}
else
{
if (status == "未选")
{
sp = new OracleParameter[] {
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id1", menu_id),
data.MakeInParam(":button_name", button_name ?? ""),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
}
else
{
sp = new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":menu_id1", menu_id),
data.MakeInParam(":button_name", button_name ?? ""),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
}
}
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetCorpById(string corp_id)
{
string Msg = "";
if (corp_id == null)
{
return(Content("{\"code\":300,\"msg\":\"请传入关键值\"}"));
}
string sql = @"select corp_id, corp_code, corp_name, detailed_address, law_person_name, fax, zip, tax_rq_number, e_mail, status, note, attribute1 from app_corp where corp_id=:corp_id";
OracleParameter[] sp = { data.MakeInParam(":corp_id", corp_id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetButton(string BUTTON_NAME, int page, int limit)
{
string Msg = "";
string sqlpj = (BUTTON_NAME == null ? "" : " where BUTTON_NAME like '%'|| :BUTTON_NAME || '%'");
string sql = @"Select *
From (Select Rownum As Rowno, r.*
From (Select Button_Id,
Button_Name,
Button_Icon,
Button_Event,
Button_Sort,
Attribute1,
Attribute2
From App_Button " + sqlpj + @"
Order By Button_Sort Asc) r
Where Rownum <= :Page * :Limit) Table_Alias
Where Table_Alias.Rowno > (:Page - 1) * :Limit";
string sql1 = @"Select count(*)
From App_Button " + sqlpj + @"
Order By Button_Sort Asc";
OracleParameter[] p1 = sqlpj == "" ?
new OracleParameter[] { } :
new OracleParameter[] {
data.MakeInParam(":BUTTON_NAME", BUTTON_NAME)
};
string n = await data.GetStringByParam(sql1, p1);
OracleParameter[] p = sqlpj == ""? new OracleParameter[] {
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
}
: new OracleParameter[] {
data.MakeInParam(":BUTTON_NAME", BUTTON_NAME),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
};
DataSet ds = await data.GetDataSetByParam(sql, p);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetColByTableName(string table_name)
{
string Msg = "";
if (table_name == null)
{
return(Content("{\"code\":1,\"msg\":\"请传入数据表名称\"}"));
}
string sql = @"Select Utc.Column_Name, Utc.Data_Type, Ucc.Comments, Utc.Table_Name
From User_Tab_Columns Utc, User_Col_Comments Ucc
Where Utc.Column_Name = Ucc.Column_Name
And Utc.Table_Name = Ucc.Table_Name
And Utc.Table_Name = :table_name
Order By Utc.Column_Id";
OracleParameter[] sp = { data.MakeInParam(":table_name", table_name) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetButtonForExport(string button_name)
{
string Msg = "";
string sql = @"select button_id,button_name,button_icon,button_event,button_sort,attribute1 from app_button where BUTTON_NAME like '%'|| :BUTTON_NAME || '%' order by button_sort asc";
OracleParameter[] p = new OracleParameter[] { data.MakeInParam(":BUTTON_NAME", button_name ?? "") };
DataSet ds = await data.GetDataSetByParam(sql, p);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetButtons(string button_name, string menu_id, string status, int limit, int page)
{
string Msg = "";
string sqlpj = status == "未选" ? @"button_id, button_name, button_icon, button_event,button_sort, attribute1,
menu_button_id from (select t.button_id,t.button_name,t.button_icon, t.button_event,
t.button_sort, t.attribute1, nvl(mb.menu_button_id, 0) menu_button_id
from app_button t, app_menu_button mb where t.button_id = mb.button_id(+)
and t.button_name like '%' || :button_name || '%'
and mb.menu_id(+) = :menu_id) where menu_button_id = 0"
: @" t.button_id, t.button_name, t.button_icon, t.button_event, t.button_sort, t.attribute1,
nvl(mb.menu_button_id, 0) menu_button_id from app_button t, app_menu_button mb
where t.button_id = mb.button_id and t.button_name like '%' || :button_name || '%'
and mb.menu_id = :menu_id order by t.button_sort asc";
string sql = @"select * from (select rownum as rowno,r.* from(select " + sqlpj + @") r where rownum<= :page * :limit) table_alias where table_alias.rowno>( :page - 1) * :limit";
string sql1 = status == "未选" ? @"Select Count(*)
From (Select *
From (Select t.Button_Id,
t.Button_Name,
t.Button_Icon,
t.Button_Event,
t.Button_Sort,
t.Attribute1,
Nvl(Mb.Menu_Button_Id, 0) Menu_Button_Id
From App_Button t, App_Menu_Button Mb
Where t.Button_Id = Mb.Button_Id(+)
And t.Button_Name Like '%' || :Button_Name || '%'
And Mb.Menu_Id(+) = :Menu_Id)
Where Menu_Button_Id = 0)" :
@"Select Count(*)
From App_Button t, App_Menu_Button Mb
Where t.Button_Id = Mb.Button_Id
And t.Button_Name Like '%' || :Button_Name || '%'
And Mb.Menu_Id = :Menu_Id
Order By t.Button_Sort Asc";
OracleParameter[] p1 =
{
data.MakeInParam(":Button_Name", button_name ?? ""),
data.MakeInParam(":Menu_Id", menu_id)
};
string n = await data.GetStringByParam(sql1, p1);
OracleParameter[] p =
{
data.MakeInParam(":button_name", button_name ?? ""),
data.MakeInParam(":menu_id", menu_id),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
};
DataSet ds = await data.GetDataSetByParam(sql, p);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetButtonById(string id)
{
string Msg = "";
string sql = "select button_id,button_name,button_icon,button_event,button_sort,attribute1,attribute2 from app_button where button_id=:button_id";
OracleParameter[] sp = { data.MakeInParam(":button_id", id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":1,\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}"): "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetUserByCorpDeptPost(int limit, int page, string user_name, string dept_id, string corp_id, string post_id, string status)
{
string msg = "";
corp_id = corp_id ?? HttpContext.Session.GetString("CORP_ID");
string pj = (corp_id != null && dept_id == null) ? " and d.corp_id=:Corp_Id "
: ((corp_id != null && dept_id != null && post_id == null) ? " and t.dept_id=:Dept_Id "
: ((corp_id != null && dept_id != null && post_id != null)
? " and t.dept_id=:Dept_Id and t.post_id=:Post_Id " : ""));
string sql = @"SELECT *
FROM (SELECT ROWNUM AS rowno, r.*
FROM (select ceil(count(t.post_id) over()/ :limit) totalPage,u.user_id,t.Person_Id,
u.user_code,
u.user_name,
t.Sex,
t.Mobile_Phone,
t.Fixed_Phone,
t.Email,
c.Corp_Name,
d.Dept_Name,
p.Post_Name,
Decode(u.Status, 0, '有效', 1, '无效') Status
From app_user u,App_Person t, App_Corp c, App_Dept d, App_Posts p
Where u.person_id=t.person_id(+) and t.Dept_Id = d.Dept_Id(+)
And d.Corp_Id = c.Corp_Id(+)
And t.Post_Id = p.Post_Id(+)
And u.Status = :Status
and (u.user_code like '%'|| :user_code ||'%' or u.user_name like '%'|| :user_name || '%')
" + pj + @" ) r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
OracleParameter[] sp1 = (corp_id != null && dept_id == null) ?
new OracleParameter[] {
data.MakeInParam(":limit", limit),
data.MakeInParam(":status", status),
data.MakeInParam(":user_code", user_name ?? ""),
data.MakeInParam(":user_name", user_name ?? ""),
data.MakeInParam(":Corp_Id", corp_id ?? ""),
data.MakeInParam(":page", page)
}
: ((corp_id != null && dept_id != null && post_id == null) ?
new OracleParameter[] {
data.MakeInParam(":limit", limit),
data.MakeInParam(":status", status),
data.MakeInParam(":user_code", user_name ?? ""),
data.MakeInParam(":user_name", user_name ?? ""),
data.MakeInParam(":Dept_Id", dept_id ?? ""),
data.MakeInParam(":page", page)
}
: ((corp_id != null && dept_id != null && post_id != null) ?
new OracleParameter[] {
data.MakeInParam(":limit", limit),
data.MakeInParam(":status", status),
data.MakeInParam(":user_code", user_name ?? ""),
data.MakeInParam(":user_name", user_name ?? ""),
data.MakeInParam(":Dept_Id", dept_id ?? ""),
data.MakeInParam(":Post_Id", post_id ?? ""),
data.MakeInParam(":page", page)
} :
new OracleParameter[] {
data.MakeInParam(":limit", limit),
data.MakeInParam(":status", status),
data.MakeInParam(":user_code", user_name ?? ""),
data.MakeInParam(":user_name", user_name ?? ""),
data.MakeInParam(":page", page)
}
));
DataSet ds = await data.GetDataSetByParam(sql, sp1);
msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows[0]["totalPage"] + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(msg));
}
public async Task <IActionResult> GetRoleForUser(string limit, string page, string status, string role_name, string user_id)
{
string msg = "";
string pj = status == "0" ? " And Rr.Role_Id Is Null " : " And Rr.Role_Id Is not Null ";
string sql = @"SELECT *
FROM (SELECT ROWNUM AS rowno, r.*
FROM (select ceil(count(*) over()/ :limit) totalPage,Role_Id,Role_Name
From (Select Sr.Role_Id, Sr.Role_Name
From App_Role Sr,
(Select Role_Id From App_User_Role Where User_Id = :user_Id) Rr
Where Sr.Role_Id = Rr.Role_Id(+)
" + pj + @" ) Sst
Where Sst.Role_Name Like '%'||:role_name ||'%') r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
OracleParameter[] sp = { data.MakeInParam(":limit", limit),
data.MakeInParam(":user_id", user_id),
data.MakeInParam(":role_name", role_name),
data.MakeInParam(":page", page) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows[0]["totalPage"] + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(msg));
}
public async Task <IActionResult> GetDept()
{
string Msg = "";
string sql = @"select t.dept_id,t.dept_name from app_dept t,app_corp c where t.corp_id=c.corp_id and t.status=0 and t.corp_id=:corp_id";
OracleParameter[] sp = { data.MakeInParam(":corp_id", HttpContext.Session.GetString("CORP_ID")) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetCurrentUserDataTable()
{
string Msg = "";
string sql = @"Select a.Table_Name, b.Comments From User_Tables a, User_Tab_Comments b Where a.Table_Name = b.Table_Name Order By Table_Name";
DataSet ds = await data.GetDataSet(sql);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetUser(string user_name, string dept_id, string corp_id, string status, string role_id, int page, int limit)
{
string Msg = "";
string sqlpj = status == "未选" ? " not " : "";
string sqlpj2 = dept_id == "-99" ? " and c.corp_id=:corp_id " : " and d.dept_id=:dept_id ";
string sql1 = @"select count(*) from app_user t,
app_person p,
app_dept d,
app_posts ps,
app_corp c
where t.person_id = p.person_id
and p.dept_id = d.dept_id
and p.post_id = ps.post_id
and d.corp_id = c.corp_id
and t.user_id " + sqlpj + @" in (select user_id from app_user_role where role_id = :role_id)
and t.user_name like '%' || :user_name || '%'
" + sqlpj2;
OracleParameter[] sp1 = dept_id == "-99"
? new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":user_name", user_name),
data.MakeInParam(":corp_id", corp_id ?? "")
}
: new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":user_name", user_name),
data.MakeInParam(":dept_id", dept_id)
};
string n = await data.GetStringByParam(sql1, sp1);
string sql = @"SELECT * FROM (SELECT ROWNUM AS rowno, r.*
FROM (select t.USER_ID, t.user_code, t.user_name,
p.mobile_phone, d.dept_name, ps.post_name, c.corp_name
from app_user t,
app_person p,
app_dept d,
app_posts ps,
app_corp c
where t.person_id = p.person_id
and p.dept_id = d.dept_id
and p.post_id = ps.post_id
and d.corp_id = c.corp_id
and t.user_id " + sqlpj + @" in (select user_id from app_user_role where role_id = :role_id)
and t.user_name like '%' || :user_name || '%'
" + sqlpj2 + @") r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
OracleParameter[] sp = dept_id == "-99"
? new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":user_name", user_name),
data.MakeInParam(":corp_id", corp_id ?? ""),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
}
: new OracleParameter[] {
data.MakeInParam(":role_id", role_id),
data.MakeInParam(":user_name", user_name),
data.MakeInParam(":dept_id", dept_id),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetDept()
{
string Msg = "";
DataSet ds = new DataSet();
string sql = @"select t.dept_id id,nvl(t.attribute1,t.dept_name) name from app_dept t where t.status=0 and corp_id=:corp_id order by nvl(t.attribute4,9999),t.dept_name";
OracleParameter[] sp = { data.MakeInParam(":corp_id", HttpContext.Session.GetString("CORP_ID")) };
ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0
? "{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}"
: "{\"code\":0,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetRole(string role_name, int page, int limit)
{
string Msg = "";
string sqlpj = role_name == null ? " " : "where role_name like '%'||:role_name ||'%' ";
string sql = @"select * from (select rownum as rowno,r.* from(select ceil(count(*) over()/ :limit) totalPage, t.role_id,t.role_name from app_role t " + sqlpj + " order by t.creation_date) r where rownum<= :page * :limit) table_alias where table_alias.rowno>( :page - 1) * :limit";
OracleParameter[] sp = role_name == null ? new OracleParameter[] { data.MakeInParam(":limit", limit), data.MakeInParam(":page", page) } : new OracleParameter[] { data.MakeInParam(":limit", limit), data.MakeInParam(":role_name", role_name), data.MakeInParam(":page", page) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows[0]["totalPage"] + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetPost()
{
string Msg = "";
DataSet ds = new DataSet();
string sql = @"select f.fixvalue_code id, f.fixvalue_name name
from app_fixvalue f, app_fixvalue_type ft
where f.fixvalue_type_id = ft.fixvalue_type_id
and ft.fixvalue_type_code='APP_POST'";
ds = await data.GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
Msg = "{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}";
}
else
{
Msg = "{\"code\":0,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
}
return(Content(Msg));
}
public async Task <IActionResult> GetCorp(string corp_name, string status, int limit, int page)
{
string Msg = "";
string sql = @"SELECT *
FROM (SELECT ROWNUM AS rowno, r.*
FROM (select corp_id,
corp_code,
corp_name,
detailed_address,
law_person_name,
fax,
zip,
tax_rq_number,
e_mail,
status,
note,
attribute1
from app_corp
where (corp_name like '%' || :corp_name || '%' or
corp_code like '%' || :corp_code || '%' or
attribute1 like '%' || :attribute1 || '%')
and status = :status) r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
string sql1 = @"select count(*)
from app_corp
where (corp_name like '%' || :corp_name || '%' or
corp_code like '%' || :corp_code || '%' or
attribute1 like '%' || :attribute1 || '%')
and status = :status";
OracleParameter[] sp1 = { data.MakeInParam(":corp_name", corp_name ?? ""), data.MakeInParam(":corp_code", corp_name ?? ""), data.MakeInParam(":attribute1", corp_name ?? ""), data.MakeInParam(":status", status) };
string n = await data.GetStringByParam(sql1, sp1);
OracleParameter[] sp = { data.MakeInParam(":corp_name", corp_name ?? ""), data.MakeInParam(":corp_code", corp_name ?? ""), data.MakeInParam(":attribute1", corp_name ?? ""), data.MakeInParam(":status", status), data.MakeInParam(":page", page), data.MakeInParam(":limit", limit) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetButton(string menu_id)
{
string Msg = "";
if (menu_id == null)
{
return(Content("{\"code\":1,\"msg\":\"请传入菜单主键\"}"));
}
string sql = @"select distinct b.button_id, b.button_name, b.button_icon, b.button_event, b.button_sort, b.attribute1,b.attribute2
from app_button b, app_role_button rb where b.button_id = rb.button_id and rb.attribute1 = :menu_id And Rb.Role_Id In
(Select Role_Id From App_User_Role Where User_Id = :user_id) order by b.button_sort";
OracleParameter[] sp = { data.MakeInParam(":menu_id", menu_id), data.MakeInParam(":user_id", HttpContext.Session.GetString("USER_ID")) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"当前登录角色在此菜单未分配按钮\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetDeptByCorp(int limit, int page, string dept_name, string corp_id, string status)
{
string msg = "";
string sql = @"SELECT *
FROM (SELECT ROWNUM AS rowno, r.*
FROM (select t.Dept_Id,
c.Corp_Name,
t.Dept_Code,
t.Dept_Name,
Decode(t.Status, 0, '有效', 1, '无效') Status
From App_Dept t, App_Corp c
Where (t.Dept_Code Like '%' || :dept_code|| '%' Or t.Dept_Name Like '%' || :dept_name || '%')
And t.Corp_Id = c.Corp_Id(+)
And t.Status = :status
And t.Corp_Id = :corp_id) r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
string sql1 = @"select count(*)
From App_Dept t, App_Corp c
Where (t.Dept_Code Like '%' || :dept_code|| '%' Or t.Dept_Name Like '%' || :dept_name || '%')
And t.Corp_Id = c.Corp_Id(+)
And t.Status = :status
And t.Corp_Id = :corp_id";
OracleParameter[] sp1 =
{
data.MakeInParam(":dept_code", dept_name ?? ""),
data.MakeInParam(":dept_name", dept_name ?? ""),
data.MakeInParam(":status", status),
data.MakeInParam(":corp_id", corp_id ?? "")
};
string n = await data.GetStringByParam(sql1, sp1);
OracleParameter[] sp =
{
data.MakeInParam(":dept_code", dept_name ?? ""),
data.MakeInParam(":dept_name", dept_name ?? ""),
data.MakeInParam(":status", status),
data.MakeInParam(":corp_id", corp_id ?? ""),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
};
DataSet ds = await data.GetDataSetByParam(sql, sp);
msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(msg));
}
public async Task <IActionResult> GetCorp()
{
string sql = @"select corp_id,corp_code,corp_name from app_corp where status=1";
DataSet ds = await data.GetDataSet(sql);
string msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未获取到数据\",\"count\":0,\"data\":[]}";
return(Content(msg));
}
public async Task <IActionResult> GetPersonByCorpDeptPost(int limit, int page, string person_name, string dept_id, string corp_id, string post_id, string status)
{
string msg = "";
corp_id = corp_id ?? HttpContext.Session.GetString("CORP_ID");
//dept_id=dept_id ?? HttpContext.Session.GetString("DEPT_ID");
//post_id=post_id?? HttpContext.Session.GetString("POST_ID");
string pj = (corp_id != null && dept_id == null)?" and d.corp_id=:Corp_Id "
:((corp_id != null && dept_id != null && post_id == null)?" and t.dept_id=:Dept_Id "
:((corp_id != null && dept_id != null && post_id != null)
?" and t.dept_id=:Dept_Id and t.post_id=:Post_Id ":""));
string sql = @"SELECT *
FROM (SELECT ROWNUM AS rowno, r.*
FROM (select t.Person_Id,
Decode(Nvl(t.Person_Type, 3), 0, '员工', 1, '非员工', 3, '未填写') Person_Type,
t.Person_Code,
t.Person_Name,
t.Id_Card_Number,
t.Sex,
t.Mobile_Phone,
t.Fixed_Phone,
t.Email,
c.Corp_Name,
d.Dept_Name,
p.Post_Name,
Decode(t.Status, 0, '有效', 1, '无效') Status
From App_Person t, App_Corp c, App_Dept d, App_Posts p
Where t.Dept_Id = d.Dept_Id
And d.Corp_Id = c.Corp_Id
And t.Post_Id = p.Post_Id
And t.Status = :Status
and (t.person_code like '%'|| :person_code ||'%' or t.person_name like '%'|| :person_name || '%')
" + pj + @" ) r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (:page - 1) * :limit";
string sql1 = @"select count(*)
From App_Person t, App_Corp c, App_Dept d, App_Posts p
Where t.Dept_Id = d.Dept_Id
And d.Corp_Id = c.Corp_Id
And t.Post_Id = p.Post_Id
And t.Status = :Status
and (t.person_code like '%'|| :person_code ||'%' or t.person_name like '%'|| :person_name || '%')
" + pj;
OracleParameter[] sp2 = (corp_id != null && dept_id == null) ?
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":Corp_Id", corp_id ?? "")
}
: ((corp_id != null && dept_id != null && post_id == null) ?
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":Dept_Id", dept_id ?? "")
}
: ((corp_id != null && dept_id != null && post_id != null) ?
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":Dept_Id", dept_id ?? ""),
data.MakeInParam(":Post_Id", post_id ?? "")
} :
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? "")
}
));
string n = await data.GetStringByParam(sql1, sp2);
OracleParameter[] sp1 = (corp_id != null && dept_id == null)?
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":Corp_Id", corp_id ?? ""),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
}
:((corp_id != null && dept_id != null && post_id == null)?
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":Dept_Id", dept_id ?? ""),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
}
:((corp_id != null && dept_id != null && post_id != null)?
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":Dept_Id", dept_id ?? ""),
data.MakeInParam(":Post_Id", post_id ?? ""),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
} :
new OracleParameter[] {
data.MakeInParam(":status", status),
data.MakeInParam(":person_code", person_name ?? ""),
data.MakeInParam(":person_name", person_name ?? ""),
data.MakeInParam(":page", page), data.MakeInParam(":limit", limit)
}
));
DataSet ds = await data.GetDataSetByParam(sql, sp1);
msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + n + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(msg));
}
public async Task <IActionResult> GetDeptByCorp(string corp_id)
{
if (corp_id == null)
{
return(Content("{\"code\":1,\"msg\":\"请传入公司主键\"}"));
}
string sql = @"select dept_id,dept_code,dept_name from app_dept where status=0 And corp_id=:corp_id";
OracleParameter[] sp = { data.MakeInParam(":corp_id", corp_id ?? "") };
DataSet ds = await data.GetDataSetByParam(sql, sp);
string msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未获取到数据\",\"count\":0,\"data\":[]}";
return(Content(msg));
}
public async Task <IActionResult> GetFixValueTypeById(string type_id)
{
if (type_id == null)
{
return(Content("{\"code\":1,\"msg\":\"请传入关键值\",\"count\":0,\"data\":[]}"));
}
string Msg = "";
string sql = @"select fixvalue_type_id, fixvalue_type_code, fixvalue_type_name from app_fixvalue_type where fixvalue_type_id=:type_id";
OracleParameter[] sp = { data.MakeInParam(":type_id", type_id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> Login(Txry_Person person)
{
#region sql
string sql = @"select PERSON_ID,
PERSON_NAME,
SEX,
AGE,
PHONE,
ID_CARD_NUMBER,
NATIONAL,
POLITICAL_LANDSCAPE,
LONG_TERM_RESIDENCE,
DOMICILE_PLACE,
SPECIAL_PERSON,
HEALTH,
E_I_ADDRESS,
MEDICAL_I_ADDRESS,
IS_GSBX,
LIVING_SITUATION,
SPOUSE_NAME,
SPOUSE_HEALTH,
SPOUSE_PHONE,
FAMILY_MAJOR_PERSON_NAME,
FAMILY_MAJOR_P_RELATIONSHIP,
FAMILY_MAJOR_PERSON_ADDRESS,
FAMILY_MAJOR_PERSON_PHONE,
STATUS,
EMERGENCY_PERSON,
EMERGENCY_PHONE,
EMERGENCY_ADDRESS,
TRANSFER_TYPE
from txry_person t Where t.person_name=:person_name And t.id_card_number=:id_card_number";
#endregion
OracleParameter[] p =
{
data.MakeInParam(":person_name", person.PERSON_NAME ?? ""),
data.MakeInParam(":id_card_number", person.ID_CARD_NUMBER ?? "")
};
try
{
DataSet ds = await data.GetDataSetByParam(sql, p);
if (ds.Tables[0].Rows.Count > 0)
{
//if (ds.Tables[0].Rows[0]["STATUS"].ToString() !="2")
//{
string json = "[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]";
return(Json(new { code = 0, msg = "已查询到数据", count = ds.Tables[0].Rows.Count, data = json.ToJson() }));
//}
//else
//{
// return Json(new { code = 2, msg = "信息已通过复核，无法再进行修改" });
//}
}
else
{
return(Json(new { code = 1, msg = "检索不到你的身份信息，请与员工服务中心联系，联系电话：8798509、8788390。" }));
}
}
catch (System.Exception ex)
{
return(Json(new { code = 1, msg = ex.Message }));
}
}
public async Task <IActionResult> GetPolicy(string search_text, int page, int limit)
{
string Msg = "";
string sql = @"SELECT *
FROM(SELECT ROWNUM AS rowno, r.*
FROM(select (Select Count(*) From Policy p, App_User u
Where p.Creation_By = u.User_Id(+)
And p.Title Like '%' || :Title || '%') totalPage,
To_Char(Policy_Id) Policy_Id,
Title,
Upload_Time,
File_Link,
u.User_Name Creation_By
From Policy p, App_User u
Where p.Creation_By = u.User_Id(+)
And p.Title Like '%' || :Title1 || '%') r
where ROWNUM <= :page * :limit) table_alias
WHERE table_alias.rowno > (: page - 1) * :limit";
OracleParameter[] sp =
{
data.MakeInParam(":Title", search_text ?? ""),
data.MakeInParam(":Title1", search_text ?? ""),
data.MakeInParam(":page", page),
data.MakeInParam(":limit", limit)
};
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows[0]["totalPage"] + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}
public async Task <IActionResult> GetPostByPostId(string post_id)
{
string Msg = "";
if (post_id == null)
{
return(Content("{\"code\":300,\"msg\":\"请传入关键值\"}"));
}
string sql = @"Select t.Post_Id,
t.Post_Code,
t.Post_Name,
t.Parent_Post_Id,
Ps.Post_Code parent_post_code,
Ps.Post_Name parent_post_name,
d.Dept_Id,
d.Dept_Code,
d.Dept_Name,
c.Corp_Id,
c.Corp_Code,
c.Corp_Name
From App_Posts t, App_Posts Ps, App_Dept d, App_Corp c
Where t.Parent_Post_Id = Ps.Post_Id(+)
And t.Dept_Id = d.Dept_Id
And d.Corp_Id = c.Corp_Id
And t.post_id=:post_id";
OracleParameter[] sp = { data.MakeInParam(":post_id", post_id) };
DataSet ds = await data.GetDataSetByParam(sql, sp);
Msg = ds.Tables[0].Rows.Count > 0 ? ("{\"code\":0,\"msg\":\"已查询到数据\",\"count\":" + ds.Tables[0].Rows.Count + ",\"data\":[" + JsonTools.DataTableToJson(ds.Tables[0]) + "]}") : "{\"code\":1,\"msg\":\"未查询到数据\",\"count\":0,\"data\":[]}";
return(Content(Msg));
}