public string AllValid(Menum Record) { string str = ""; try { sql = "select menu_pkid from ("; sql += "select menu_pkid from menum a where rec_company_code ='" + Record._globalvariables.comp_code + "' "; sql += " and (a.menu_code = '{CODE}' or a.menu_name = '{NAME}') "; sql += ") a where menu_pkid <> '{PKID}'"; sql = sql.Replace("{CODE}", Record.menu_code); sql = sql.Replace("{NAME}", Record.menu_name); sql = sql.Replace("{PKID}", Record.menu_pkid); if (Con_Oracle.IsRowExists(sql)) { str = "Code/Name Exists"; } } catch (Exception Ex) { str = Ex.Message.ToString(); } return(str); }
public IHttpActionResult Save(Menum Record) { try { using (MenuService obj = new MenuService()) return(Ok(obj.Save(Record))); } catch (Exception Ex) { return(ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.BadRequest, Ex.Message.ToString()))); } }
public Dictionary <string, object> GetRecord(Dictionary <string, object> SearchData) { Dictionary <string, object> RetData = new Dictionary <string, object>(); Menum mRow = new Menum(); string id = SearchData["pkid"].ToString(); try { DataTable Dt_Rec = new DataTable(); sql = "select a.menu_pkid, a.menu_code, a.menu_name, a.menu_route1,a.menu_route2, a.menu_type, menu_order, menu_module_id,menu_displayed "; sql += " from menum a "; sql += " where a.menu_pkid ='" + id + "'"; Con_Oracle = new DBConnection(); Dt_Rec = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); foreach (DataRow Dr in Dt_Rec.Rows) { mRow = new Menum(); mRow.menu_pkid = Dr["menu_pkid"].ToString(); mRow.menu_code = Dr["menu_code"].ToString(); mRow.menu_name = Dr["menu_name"].ToString(); mRow.menu_route1 = Dr["menu_route1"].ToString(); mRow.menu_route2 = Dr["menu_route2"].ToString(); mRow.menu_type = Dr["menu_type"].ToString(); mRow.menu_order = Lib.Conv2Integer(Dr["menu_order"].ToString()); mRow.menu_module_id = Dr["menu_module_id"].ToString(); mRow.menu_displayed = false; if (Dr["menu_displayed"].ToString() == "Y") { mRow.menu_displayed = true; } break; } } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.CloseConnection(); } throw Ex; } RetData.Add("record", mRow); return(RetData); }
public Dictionary <string, object> Save(Menum Record) { Dictionary <string, object> RetData = new Dictionary <string, object>(); string ErrorMessage = ""; try { Con_Oracle = new DBConnection(); if (Record.menu_code.Trim().Length <= 0) { Lib.AddError(ref ErrorMessage, "Code Cannot Be Empty"); } if (Record.menu_name.Trim().Length <= 0) { Lib.AddError(ref ErrorMessage, "Description Cannot Be Empty"); } if (Record.menu_module_id is null) { Lib.AddError(ref ErrorMessage, "Module Cannot Be Blank"); } if (ErrorMessage != "") { throw new Exception(ErrorMessage); } if ((ErrorMessage = AllValid(Record)) != "") { throw new Exception(ErrorMessage); } DBRecord Rec = new DBRecord(); Rec.CreateRow("menum", Record.rec_mode, "menu_pkid", Record.menu_pkid); Rec.InsertString("menu_code", Record.menu_code); Rec.InsertString("menu_name", Record.menu_name, "Z"); Rec.InsertString("menu_route1", Record.menu_route1, "Z"); Rec.InsertString("menu_route2", Record.menu_route2, "Z"); Rec.InsertString("menu_type", Record.menu_type); Rec.InsertNumeric("menu_order", Record.menu_order.ToString()); Rec.InsertString("menu_module_id", Record.menu_module_id); if (Record.menu_displayed) { Rec.InsertString("menu_displayed", "Y"); } else { Rec.InsertString("menu_displayed", "N"); } if (Record.rec_mode == "ADD") { Rec.InsertString("rec_company_code", Record._globalvariables.comp_code); } sql = Rec.UpdateRow(); Con_Oracle.BeginTransaction(); Con_Oracle.ExecuteNonQuery(sql); Con_Oracle.CommitTransaction(); Con_Oracle.CloseConnection(); } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.RollbackTransaction(); Con_Oracle.CloseConnection(); } throw Ex; } Con_Oracle.CloseConnection(); return(RetData); }
public IDictionary <string, object> List(Dictionary <string, object> SearchData) { string sWhere = ""; Dictionary <string, object> RetData = new Dictionary <string, object>(); Con_Oracle = new DBConnection(); List <Menum> mList = new List <Menum>(); Menum mRow; string comp_code = SearchData["comp_code"].ToString().ToUpper(); string type = SearchData["type"].ToString(); string searchstring = SearchData["searchstring"].ToString().ToUpper(); long page_count = (long)SearchData["page_count"]; long page_current = (long)SearchData["page_current"]; long page_rows = (long)SearchData["page_rows"]; long page_rowcount = (long)SearchData["page_rowcount"]; long startrow = 0; long endrow = 0; try { sWhere = " where a.rec_company_code ='" + comp_code + "' "; if (searchstring != "") { sWhere += " and ("; sWhere += " upper(b.module_name) like '%" + searchstring.ToUpper() + "%'"; sWhere += " or upper(a.menu_code) like '%" + searchstring.ToUpper() + "%'"; sWhere += " or upper(a.menu_name) like '%" + searchstring.ToUpper() + "%'"; sWhere += " or upper(a.menu_route1) like '%" + searchstring.ToUpper() + "%'"; sWhere += " )"; } if (type == "NEW") { sql = "SELECT count(*) as total, ceil(COUNT(*) / " + page_rows.ToString() + ") page_total FROM menum a "; if (Con_Oracle.DB == "SQL") { sql = "SELECT count(*) as total, ceiling(COUNT(*) / cast(" + page_rows.ToString() + " as decimal) ) page_total FROM menum a "; } sql += " inner join modulem b on menu_module_id = module_pkid "; sql += sWhere; DataTable Dt_Temp = new DataTable(); Dt_Temp = Con_Oracle.ExecuteQuery(sql); if (Dt_Temp.Rows.Count > 0) { page_rowcount = Lib.Conv2Integer(Dt_Temp.Rows[0]["total"].ToString()); page_count = Lib.Conv2Integer(Dt_Temp.Rows[0]["page_total"].ToString()); } page_current = 1; } else { if (type == "FIRST") { page_current = 1; } if (type == "PREV" && page_current > 1) { page_current--; } if (type == "NEXT" && page_current < page_count) { page_current++; } if (type == "LAST") { page_current = page_count; } } startrow = (page_current - 1) * page_rows + 1; endrow = (startrow + page_rows) - 1; DataTable Dt_List = new DataTable(); sql = ""; sql += " select * from ( "; sql += " select menu_pkid, a.menu_code, a.menu_name , module_name, menu_type, menu_route1,menu_route2, menu_order, row_number() over(order by module_name, menu_name) rn "; sql += " from menum a inner join modulem b on a.menu_module_id = b.module_pkid " + sWhere; sql += ") a where rn between {startrow} and {endrow}"; sql += " order by module_name, a.menu_order,a.menu_name "; sql = sql.Replace("{startrow}", startrow.ToString()); sql = sql.Replace("{endrow}", endrow.ToString()); Dt_List = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); foreach (DataRow Dr in Dt_List.Rows) { mRow = new Menum(); mRow.menu_pkid = Dr["menu_pkid"].ToString(); mRow.menu_code = Dr["menu_code"].ToString(); mRow.menu_name = Dr["menu_name"].ToString(); mRow.menu_route1 = Dr["menu_route1"].ToString(); mRow.menu_route2 = Dr["menu_route2"].ToString(); mRow.menu_type = Dr["menu_type"].ToString(); mRow.menu_order = Lib.Conv2Integer(Dr["menu_order"].ToString()); mRow.menu_module_name = Dr["module_name"].ToString(); mList.Add(mRow); } } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.CloseConnection(); } throw Ex; } RetData.Add("page_count", page_count); RetData.Add("page_current", page_current); RetData.Add("page_rowcount", page_rowcount); RetData.Add("list", mList); return(RetData); }