public string AllValid(Modulem Record)
{
string str = "";
try
{
sql = "select module_pkid from (";
sql += "select module_pkid from modulem a where rec_company_code ='" + Record._globalvariables.comp_code + "' ";
sql += " and (a.module_name = '{NAME}') ";
sql += ") a where module_pkid <> '{PKID}'";
sql = sql.Replace("{NAME}", Record.module_name);
sql = sql.Replace("{PKID}", Record.module_pkid);
if (Con_Oracle.IsRowExists(sql))
{
str = "Name Exists";
}
}
catch (Exception Ex)
{
str = Ex.Message.ToString();
}
return(str);
}
public Dictionary <string, object> Save(Modulem Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
try
{
Con_Oracle = new DBConnection();
if (Record.module_name.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Name Cannot Be Empty");
}
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
if ((ErrorMessage = AllValid(Record)) != "")
{
throw new Exception(ErrorMessage);
}
DBRecord Rec = new DBRecord();
Rec.CreateRow("modulem", Record.rec_mode, "module_pkid", Record.module_pkid);
Rec.InsertString("module_name", Record.module_name, "Z");
Rec.InsertNumeric("module_order", Record.module_order.ToString());
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 IHttpActionResult Save(Modulem Record)
{
try
{
using (ModuleService obj = new ModuleService())
return(Ok(obj.Save(Record)));
}
catch (Exception Ex)
{
return(ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.BadRequest, Ex.Message.ToString())));
}
}
public IDictionary <string, object> LoadDefault(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
Con_Oracle = new DBConnection();
List <Modulem> mList = new List <Modulem>();
Modulem mRow;
string comp_code = SearchData["comp_code"].ToString();
try
{
DataTable Dt_List = new DataTable();
sql = "";
sql += " select module_pkid,module_name from modulem where rec_company_code='" + comp_code + "'";
sql += " order by module_name";
Dt_List = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
foreach (DataRow Dr in Dt_List.Rows)
{
mRow = new Modulem();
mRow.module_pkid = Dr["module_pkid"].ToString();
mRow.module_name = Dr["module_name"].ToString();
mList.Add(mRow);
}
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("modules", mList);
return(RetData);
}
public Dictionary <string, object> GetRecord(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
Modulem mRow = new Modulem();
string id = SearchData["pkid"].ToString();
try
{
DataTable Dt_Rec = new DataTable();
sql = "select module_pkid, module_name, module_order ";
sql += " from modulem a ";
sql += " where a.module_pkid ='" + id + "'";
Con_Oracle = new DBConnection();
Dt_Rec = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
foreach (DataRow Dr in Dt_Rec.Rows)
{
mRow = new Modulem();
mRow.module_pkid = Dr["module_pkid"].ToString();
mRow.module_name = Dr["module_name"].ToString();
mRow.module_order = Lib.Conv2Integer(Dr["module_order"].ToString());
break;
}
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("record", mRow);
return(RetData);
}
public IDictionary <string, object> RightsList(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
Con_Oracle = new DBConnection();
List <Modulem> modList = new List <Modulem>();
Modulem modRow;
List <UserRights> mList = new List <UserRights>();
UserRights mRow;
string type = SearchData["type"].ToString();
string comp_code = SearchData["comp_code"].ToString();
string compid = SearchData["compid"].ToString();
//string branchid = SearchData["branchid"].ToString();
string userid = SearchData["userid"].ToString();
try
{
DataTable Dt_Modules = new DataTable();
sql = "select module_name from modulem where rec_company_code= '" + comp_code + "' order by module_order ";
Dt_Modules = Con_Oracle.ExecuteQuery(sql);
foreach (DataRow Dr in Dt_Modules.Rows)
{
modRow = new Modulem();
modRow.module_name = Dr["module_name"].ToString();
modList.Add(modRow);
}
DataTable Dt_List = new DataTable();
sql = "";
sql += " select rights_pkid, module_name, menu_pkid, menu_name, menu_type, rights_company, ";
sql += " rights_admin, rights_add, rights_edit,rights_delete, rights_print, rights_email,rights_docs, ";
sql += " rights_docs_upload,rights_view, rights_restricted,rights_approval ";
sql += " from menum a ";
sql += " inner join modulem b on menu_module_id = module_pkid ";
sql += " left join userrights c on menu_pkid = rights_menu_id and ";
sql += " rights_company_id = '{COMPID}' and rights_user_id = '{USERID}' ";
sql += " where a.rec_company_code = '" + comp_code + "'";
sql += " order by module_order, menu_order ";
sql = sql.Replace("{COMPID}", compid);
sql = sql.Replace("{USERID}", userid);
Dt_List = Con_Oracle.ExecuteQuery(sql);
foreach (DataRow Dr in Dt_List.Rows)
{
mRow = new UserRights();
mRow.rights_user_id = userid;
mRow.rights_company_id = compid;
//mRow.rights_branch_id = branchid;
if (Dr["rights_pkid"].Equals(DBNull.Value))
{
mRow.rights_id = System.Guid.NewGuid().ToString().ToUpper();
}
else if (Dr["rights_pkid"].ToString().Length <= 0)
{
mRow.rights_id = System.Guid.NewGuid().ToString().ToUpper();
}
else
{
mRow.rights_id = Dr["rights_pkid"].ToString();
}
mRow.module_name = Dr["module_name"].ToString();
mRow.menu_id = Dr["menu_pkid"].ToString();
mRow.menu_name = Dr["menu_name"].ToString();
mRow.menu_type = Dr["menu_type"].ToString();
mRow.rights_company = (Dr["rights_company"].ToString() == "Y") ? true : false;
mRow.rights_admin = (Dr["rights_admin"].ToString() == "Y") ? true : false;
mRow.rights_add = (Dr["rights_add"].ToString() == "Y") ? true : false;
mRow.rights_edit = (Dr["rights_edit"].ToString() == "Y") ? true : false;
mRow.rights_delete = (Dr["rights_delete"].ToString() == "Y") ? true : false;
mRow.rights_print = (Dr["rights_print"].ToString() == "Y") ? true : false;
mRow.rights_email = (Dr["rights_email"].ToString() == "Y") ? true : false;
mRow.rights_docs = (Dr["rights_docs"].ToString() == "Y") ? true : false;
mRow.rights_docs_upload = (Dr["rights_docs_upload"].ToString() == "Y") ? true : false;
mRow.rights_view = (Dr["rights_view"].ToString() == "Y") ? true : false;
mRow.rights_restricted = (Dr["rights_restricted"].ToString() == "Y") ? true : false;
mRow.rights_approval = Dr["rights_approval"].ToString();
mList.Add(mRow);
}
Con_Oracle.CloseConnection();
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("list", mList);
RetData.Add("modules", modList);
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 <Modulem> mList = new List <Modulem>();
Modulem mRow;
string type = SearchData["type"].ToString();
string comp_code = SearchData["comp_code"].ToString().ToUpper();
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 rec_company_code ='" + comp_code + "' ";
if (searchstring != "")
{
sWhere += " and (";
sWhere += " upper(a.module_name) like '%" + searchstring.ToUpper() + "%'";
sWhere += " )";
}
if (type == "NEW")
{
sql = "SELECT count(*) as total, ceil(COUNT(*) / " + page_rows.ToString() + ") page_total FROM modulem a ";
if (Con_Oracle.DB == "SQL")
{
sql = "SELECT count(*) as total, ceiling(COUNT(*) / cast(" + page_rows.ToString() + " as decimal) ) page_total FROM modulem a ";
}
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 module_pkid, module_name, module_order , row_number() over(order by module_order) rn ";
sql += " from modulem a " + sWhere;
sql += ") a where rn between {startrow} and {endrow}";
sql += " order by module_order";
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 Modulem();
mRow.module_pkid = Dr["module_pkid"].ToString();
mRow.module_name = Dr["module_name"].ToString();
mRow.module_order = Lib.Conv2Integer(Dr["module_order"].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);
}
