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)); }