/// <summary>
/// Retrieves the parents of the UNIT or GROUP passed
/// </summary>
/// <param name="grpid">Id of the Unit OR GROUP passed</param>
/// <param name="isUnit">If true means that grpid is a UNIT (otherwise grpid is a GROUP)</param>
/// <param name="dgrpid">At the exit of the function contains the group_def of the parent (or -1 if not aplicable)</param>
/// <param name="searchOnlyPhyGroups">If true only groups with DGRP_PHYORDER NOT NULL will be considered.</param>
/// <returns>DataTable with all data requested. Note that if searchOnlyPhyGroups is FALSE the column DGRP_PHYORDER will contain -1 for non physical items</returns>
public DataTable GetParentsGroup(int grpid, bool isUnit, bool searchOnlyPhyGroups)
{
Database d = DatabaseFactory.GetDatabase();
// Get the physical parent of the element
string ssql = "select CGRPG_ID, CGRPG_TYPE, CGRPG_ORDER, DGRP_ID, DGRP_PHYORDER FROM GROUPS_CHILDS_GIS " +
"INNER JOIN GROUPS ON CGRPG_ID = GRP_ID INNER JOIN GROUPS_DEF ON GRP_DGRP_ID = DGRP_ID " +
"WHERE CGRPG_CHILD = @GROUPS_CHILDS_GIS.CGRPG_CHILD@";
if (searchOnlyPhyGroups)
{
ssql = ssql + " AND DGRP_PHYORDER IS NOT NULL";
}
IDataReader dr = d.ExecQuery(ssql, grpid);
DataTable dt = new DataTable("Parents");
dt.Columns.Add("CGRPG_ID", typeof(int));
dt.Columns.Add("DGRP_ID", typeof(int));
dt.Columns.Add("DGRP_PHYORDER", typeof(int));
while (dr.Read())
{
// Have one or more physical parent. We must check that the CGRPG_TYPE is "U" when isUnit is true
// That is because the IDs of UNITS and GROUPS can be the same, so if we are the GROUP X
// and find one parent Y, we must check that the CGRPG_TYPE is not "U" (because X is a GROUP
// not a Unit!
bool bAddRegister = false;
string stype = dr["CGRPG_TYPE"].ToString();
if (isUnit)
{
if (stype.Equals("U"))
{
bAddRegister = true;
}
}
else
{
if (!stype.Equals("U"))
{
bAddRegister = true;
}
}
// Adds the current register to the DataTable
if (bAddRegister)
{
DataRow row = dt.NewRow();
row["CGRPG_ID"] = dr["CGRPG_ID"];
row["DGRP_ID"] = dr["DGRP_ID"];
row["DGRP_PHYORDER"] = dr["DGRP_PHYORDER"] != DBNull.Value ? dr["DGRP_PHYORDER"] : -1;
dt.Rows.Add(row);
}
}
dr.Close();
dt.AcceptChanges();
return(dt);
}
// private static System.Threading.Mutex m = new System.Threading.Mutex();
//
// public CmpParametersDB() {}
//
// public override DataTable GetData (string[] fields, string where, string orderby,object[] values)
// {
// Database d = DatabaseFactory.GetDatabase ();
// if (fields == null)
// {
// fields = new string[] {"PAR_ID", "PAR_DESCSHORT", "PAR_VALUE", "PAR_CATEGORY"};
// }
// string[] pk = new string[] {"PAR_ID"};
// StringBuilder sb = base.ProcessFields(fields,pk);
// return base.DoGetData (sb.ToString(),where, orderby,values,"PARAMETERS","PARAMETERS",pk);
//
// }
//
// public override DataTable GetPagedData (string[] fields, string orderByField, bool orderByAsc, string where, int rowstart, int rowend)
// {
// Database d = DatabaseFactory.GetDatabase();
//
// if (fields == null) fields = new string[] {"PAR_ID", "PAR_DESCSHORT", "PAR_VALUE", "PAR_CATEGORY"};
// string[] pk = new string[] {"PAR_ID"};
// StringBuilder sb = base.ProcessFields(fields, pk);
// return base.DoGetPagedData (sb.ToString(), orderByField,orderByAsc,where,rowstart,rowend,"PARAMETERS","PARAMETERS",pk);
// }
//
// public override void SaveData (DataTable dt)
// {
// Database d = DatabaseFactory.GetDatabase ();
// IDbDataAdapter da = d.GetDataAdapter ();
// da.InsertCommand = d.PrepareCommand ("INSERT INTO PARAMETERS (PAR_ID, PAR_DESCSHORT, PAR_VALUE, PAR_CATEGORY, PAR_VERSION) VALUES (@PARAMETERS.PAR_ID@, @PARAMETERS.PAR_DESCSHORT@, @PARAMETERS.PAR_VALUE@, @PARAMETERS.PAR_CATEGORY@)",false);
// da.UpdateCommand = d.PrepareCommand ("UPDATE PARAMETERS SET PAR_DESCSHORT = @PARAMETERS.PAR_DESCSHORT@, PAR_VALUE = @PARAMETERS.PAR_VALUE@, PAR_CATEGORY = @PARAMETERS.PAR_CATEGORY@ WHERE PAR_ID = @PARAMETERS.PAR_ID@", false);
// da.DeleteCommand = d.PrepareCommand ("DELETE FROM PARAMETERS WHERE PAR_ID = @PARAMETERS.PAR_ID@", false);
// IDbConnection con = d.GetNewConnection();
// con.Open();
// da.InsertCommand.Connection = con;
// da.UpdateCommand.Connection = con;
// da.DeleteCommand.Connection = con;
// d.UpdateDataSet(da,dt);
// dt.AcceptChanges();
// con.Close();
//
// }
// public override void GetForeignData(DataSet ds, string sTable) {}
// public override string MainTable {get { return "PARAMETERS"; }}
// public override long GetCount(string where, params object[] values)
// {
// Database d = DatabaseFactory.GetDatabase();
// string sql = "SELECT COUNT(*) FROM PARAMETERS";
// if (where != null)
// {
// sql = sql + " WHERE " + where;
// }
// return Convert.ToInt64(d.ExecuteScalar(sql, values));
// }
//
// public override Int64 LastPKValue
// {
// get
// {
// Database d = DatabaseFactory.GetDatabase();
// m.WaitOne();
// long l = Convert.ToInt64(d.ExecuteScalar("SELECT NVL(MAX(PAR_ID),0) FROM PARAMETERS"));
// m.ReleaseMutex();
// return l;
// }
// }
// // **************** END implementation of ICmpDataSource ************************
/// <summary>
/// Gets the value of a parameter identified by its PAR_DESCSHORT
/// </summary>
/// <param name="pdescshort">PAR_DESCSHORT of the parameter</param>
/// <returns>A string containing the parameter value</returns>
public string GetParameter(string pdescshort)
{
Database d = DatabaseFactory.GetDatabase();
IDataReader dr = d.ExecQuery("SELECT PAR_VALUE FROM PARAMETERS WHERE PAR_DESCSHORT = @PARAMETERS.PAR_DESCSHORT@", pdescshort);
string parResult = "";
if (dr.Read())
{
parResult = dr.GetString(0);
}
dr.Close();
return(parResult);
}
/// <summary>
/// Checks if grpid is a group or not (if is not a group is a unit).
/// </summary>
/// <param name="grpid">ID to check</param>
/// <returns>true if ID is a GROUP (if is not a group is a UNIT)</returns>
public bool IsGroup(int grpid)
{
Database d = DatabaseFactory.GetDatabase();
string ssql = "select GRP_ID from GROUPS where GRP_ID = @GROUPS.GRP_ID@";
IDataReader dr = d.ExecQuery(ssql, grpid);
try
{
return(dr.Read());
}
finally
{
dr.Close();
dr.Dispose();
}
}
/// <summary>
/// Checks if uniid is a unit or not (if is not a unit is a group).
/// </summary>
/// <param name="uniid">ID to check</param>
/// <returns>true if ID is a UNIT (if is not a group is a GROUP)</returns>
public bool IsUnit(int uniid)
{
Database d = DatabaseFactory.GetDatabase();
string ssql = "select UNI_ID from UNITS where UNI_ID = @UNITS.UNI_ID@";
IDataReader dr = d.ExecQuery(ssql, uniid);
try
{
return(dr.Read());
}
finally
{
dr.Close();
dr.Dispose();
}
}
// private static System.Threading.Mutex m = new System.Threading.Mutex();
//
// public CmpGroupsChildsGisDB() {}
//
// #region Implementation of CmpDataSourceAdapter
//
// public override DataTable GetData (string[] fields, string where, string orderby,object[] values)
// {
// Database d = DatabaseFactory.GetDatabase ();
// string[] pk = new string[] {"CGRPG_ID,CGRPG_TYPE"};
// if (fields == null)
// {
// fields = new string[] {"CGRPG_ID","CGRPG_TYPE","CGRPG_CHILD","CGRPG_ORDER"};
// }
// StringBuilder sb = base.ProcessFields(fields,pk);
// return base.DoGetData (sb.ToString(),where, orderby, values,"GROUPS_CHILDS_GIS","GROUPS_CHILDS_GIS", pk);
// }
//
// public override DataTable GetPagedData (string[] fields, string orderByField, bool orderByAsc, string where, int rowstart, int rowend)
// {
// Database d = DatabaseFactory.GetDatabase();
// if (fields == null)
// {
// fields = new string[] {"CGRPG_ID","CGRPG_TYPE","CGRPG_CHILD","CGRPG_ORDER"};
// }
// string[] pk = new string[] {"CGRPG_ID,CGRPG_TYPE"};
// StringBuilder sb = base.ProcessFields(fields,pk);
// return base.DoGetPagedData (sb.ToString(),orderByField,orderByAsc,where,rowstart,rowend,"GROUPS_CHILDS_GIS","GROUPS_CHILDS_GIS",pk);
// }
//
// public override void SaveData (DataTable dt)
// {
// Database d = DatabaseFactory.GetDatabase ();
// IDbDataAdapter da = d.GetDataAdapter ();
// da.InsertCommand = d.PrepareCommand ("INSERT INTO GROUPS_CHILDS_GIS (CGRPG_ID, CGRPG_TYPE, CGRPG_CHILD, CGRPG_ORDER, LAN_DEFAULT) VALUES (@GROUPS_CHILDS_GIS.CGRPG_ID@, @GROUPS_CHILDS_GIS.CGRPG_TYPE@, @GROUPS_CHILDS_GIS.CGRPG_CHILD@, @GROUPS_CHILDS_GIS.CGRPG_ORDER@)",false);
// da.UpdateCommand = d.PrepareCommand ("UPDATE GROUPS_CHILDS_GIS SET CGRPG_TYPE = @GROUPS_CHILDS_GIS.CGRPG_TYPE@, CGRPG_CHILD = @GROUPS_CHILDS_GIS.CGRPG_CHILD@, CGRPG_ORDER = @GROUPS_CHILDS_GIS.CGRPG_ORDER@ WHERE CGRPG_ID = @GROUPS_CHILDS_GIS.CGRPG_ID@", false);
// da.DeleteCommand = d.PrepareCommand ("DELETE FROM GROUPS_CHILDS_GIS WHERE CGRPG_ID = @GROUPS_CHILDS_GIS.CGRPG_ID@ AND CGRPG_TYPE = @GROUPS_CHILDS_GIS.CGRPG_TYPE@", false);
// IDbConnection con = d.GetNewConnection();
// con.Open();
// da.InsertCommand.Connection = con;
// da.UpdateCommand.Connection = con;
// da.DeleteCommand.Connection = con;
// d.UpdateDataSet(da,dt);
// dt.AcceptChanges();
// con.Close();
//
// }
// public override void GetForeignData(DataSet ds, string sTable) {}
// public override string MainTable {get { return "GROUPS_CHILDS_GIS"; }}
// public override long GetCount(string where, params object[] values)
// {
// Database d = DatabaseFactory.GetDatabase();
// string sql = "SELECT COUNT(*) FROM GROUPS_CHILDS_GIS";
// if (where != null)
// {
// sql = sql + " WHERE " + where;
// }
// return Convert.ToInt64(d.ExecuteScalar(sql, values));
// }
//
// public override Int64 LastPKValue
// {
// get
// {
// Database d = DatabaseFactory.GetDatabase();
// m.WaitOne();
// long l = Convert.ToInt64(d.ExecuteScalar("SELECT NVL(MAX(CGRPG_ID),0) FROM GROUPS_CHILDS_GIS"));
// m.ReleaseMutex();
// return l;
// }
// }
// #endregion
/// <summary>
/// Returns the ID of the 1st Physical parent of the unit specified.
/// The 1st physical parent is the physical parent with the lowest CGRPG_ORDER
/// </summary>
/// <param name="uniid">UNI_ID of the unit</param>
/// <returns>GRP_ID of the parent sarched or -1 if not register found</returns>
public int GetFirstPhysicalParent(int uniid)
{
Database d = DatabaseFactory.GetDatabase();
string ssql = "select CGRPG_ID FROM GROUPS_CHILDS_GIS INNER JOIN GROUPS ON CGRPG_ID = GRP_ID INNER JOIN GROUPS_DEF " +
"ON GRP_DGRP_ID = DGRP_ID WHERE CGRPG_CHILD = @GROUPS_CHILDS_GIS.CGRPG_CHILD@ AND DGRP_PHYORDER IS NOT NULL ORDER BY CGRPG_ORDER ASC";
IDataReader dr = null;
try
{
dr = d.ExecQuery(ssql, uniid);
return(dr.Read() ? Convert.ToInt32(dr["CGRPG_ID"]) : -1);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
}
