939 lines
34 KiB
C#
939 lines
34 KiB
C#
using System;
|
|
using CSLA.Data;
|
|
using CSLA;
|
|
using GZTW.Profile;
|
|
using GZTW.Data;
|
|
using System.Data;
|
|
using System.Threading;
|
|
using CSLA.Security;
|
|
|
|
|
|
namespace GZTW.AyaNova.BLL
|
|
{
|
|
/// <summary>
|
|
/// Various DB helpers to save duplication
|
|
/// in individual business objects
|
|
/// </summary>
|
|
public class DBUtil
|
|
{
|
|
|
|
|
|
#region construction and initialization
|
|
static DBUtil()
|
|
{
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region DataBase
|
|
private static GZTWDatabase _DB = null;
|
|
//private static AyaNovaConnectionSettings _ConnectionSettings=null;
|
|
|
|
internal static GZTWDatabase DB
|
|
{
|
|
get
|
|
{
|
|
//if(_ConnectionSettings==null)
|
|
//{
|
|
// _ConnectionSettings=new AyaNovaConnectionSettings();
|
|
// _ConnectionSettings.GetConnectionData();
|
|
//}
|
|
if(_DB==null)
|
|
_DB=GZTWDatabaseFactory.CreateDatabase(AyaBizUtils.AyaNovaConnectionSetting);
|
|
|
|
return _DB;
|
|
|
|
}
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region Fetch data helpers
|
|
/// <summary>
|
|
/// Return a reader based on query with no parameters
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL query</param>
|
|
/// <returns></returns>
|
|
internal static SafeDataReader GetReaderFromSQLString(string SqlCommandString)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
return new SafeDataReader(DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a reader based on query with no parameters
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL query</param>
|
|
/// <param name="tr">Database transaction</param>
|
|
/// <returns></returns>
|
|
internal static SafeDataReader GetReaderFromSQLString(string SqlCommandString, IDbTransaction tr)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
return new SafeDataReader(DB.ExecuteReader(dbCommandWrapper, tr));
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a reader based on query and ID parameter
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL with single ID paramter</param>
|
|
/// <param name="ID">ID of record desired</param>
|
|
/// <returns></returns>
|
|
internal static SafeDataReader GetReaderFromSQLString(string SqlCommandString, Guid ID)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
dbCommandWrapper.AddInParameter("@ID",DbType.Guid,ID);
|
|
return new SafeDataReader(DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a reader based on query and ID parameter
|
|
/// without using parameters for performance in a tight loop
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL with single ID paramter</param>
|
|
/// <param name="ID">ID of record desired</param>
|
|
/// <returns></returns>
|
|
internal static SafeDataReader GetReaderFromSQLStringParameterlessly(string SqlCommandString, Guid ID)
|
|
{
|
|
//if empty (null) then let normal method handle
|
|
//because it can't be done dynamically propery
|
|
if(ID==Guid.Empty)
|
|
return GetReaderFromSQLString(SqlCommandString,ID);
|
|
|
|
|
|
|
|
string strParameter="'{"+ID.ToString().ToUpper()+"}'";
|
|
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString.Replace("@ID",strParameter));
|
|
|
|
return new SafeDataReader(DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a reader based on query and ID parameter
|
|
/// and database transaction
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL with single ID paramter</param>
|
|
/// <param name="ID">ID of record desired</param>
|
|
/// <param name="tr">Database transaction</param>
|
|
/// <returns></returns>
|
|
internal static SafeDataReader GetReaderFromSQLString(string SqlCommandString, Guid ID, IDbTransaction tr)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
dbCommandWrapper.AddInParameter("@ID",DbType.Guid,ID);
|
|
return new SafeDataReader(DB.ExecuteReader(dbCommandWrapper,tr));
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a single object value based on query and ID parameter
|
|
/// (returns first column of first row as Object)
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL with single ID paramter</param>
|
|
/// <param name="ID">ID of record desired</param>
|
|
/// <returns></returns>
|
|
internal static object GetScalarFromSQLString(string SqlCommandString, Guid ID)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
dbCommandWrapper.AddInParameter("@ID",DbType.Guid,ID);
|
|
return DB.ExecuteScalar(dbCommandWrapper);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a single object value based on query and ID parameter
|
|
/// (returns first column of first row as Object)
|
|
/// Within the specified transaction
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL with single ID paramter</param>
|
|
/// <param name="ID">ID of record desired</param>
|
|
/// <param name="tr">Database transaction</param>
|
|
/// <returns></returns>
|
|
internal static object GetScalarFromSQLString(string SqlCommandString, Guid ID, IDbTransaction tr)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
dbCommandWrapper.AddInParameter("@ID",DbType.Guid,ID);
|
|
return DB.ExecuteScalar(dbCommandWrapper,tr);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a single object value based on query
|
|
/// (returns first column of first row as Object)
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">SQL</param>
|
|
/// <returns></returns>
|
|
internal static object GetScalarFromSQLString(string SqlCommandString)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
return DB.ExecuteScalar(dbCommandWrapper);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a single object value based on query
|
|
/// within transaction
|
|
/// (returns first column of first row as Object)
|
|
/// </summary>
|
|
internal static object GetScalarFromSQLString(string SqlCommandString, IDbTransaction tr)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
return DB.ExecuteScalar(dbCommandWrapper,tr);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a DB generated sequential identity number
|
|
/// </summary>
|
|
/// <param name="field">Field containing ID value</param>
|
|
/// <param name="table">Table name</param>
|
|
/// <param name="ID">Guid of record desired</param>
|
|
/// <param name="tr"></param>
|
|
/// <returns>32bit sequential integer identity value</returns>
|
|
internal static int GetIdentity(string field, string table, Guid ID, IDbTransaction tr)
|
|
{
|
|
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper("SELECT " + field + " FROM " + table + " WHERE aID=@ID;");
|
|
dbCommandWrapper.AddInParameter("@ID",DbType.Guid,ID);
|
|
SafeDataReader dr = null;
|
|
if(tr==null)
|
|
dr=new SafeDataReader(DB.ExecuteReader(dbCommandWrapper));
|
|
else
|
|
dr=new SafeDataReader(DB.ExecuteReader(dbCommandWrapper,tr));
|
|
|
|
|
|
if(!dr.Read())
|
|
ThrowFetchError("DBUtil:GetIdentity for "+table+"."+field+ " ID=" + ID.ToString());
|
|
int i=dr.GetInt32(field);
|
|
dr.Close();
|
|
if(i==0)
|
|
throw new System.ApplicationException("ERROR - DBUtil:GetIdentity for "+table+"."+field+ " ID=" + ID.ToString() + " RETURNED 0");
|
|
return i;
|
|
}
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// Return a reader based on query, RootObjectType and ID parameter
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">A SQL string containing two parameters @RootObjectID and @RootObjectType</param>
|
|
/// <param name="objectType">@RootObjectType parameter</param>
|
|
/// <param name="RootObjectID">@RootObjectID parameter</param>
|
|
/// <returns></returns>
|
|
internal static SafeDataReader GetReaderFromSQLString(string SqlCommandString, Guid RootObjectID, RootObjectTypes objectType)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
dbCommandWrapper.AddInParameter("@RootObjectID", DbType.Guid, RootObjectID);
|
|
dbCommandWrapper.AddInParameter("@RootObjectType", DbType.Int16, (int)objectType);
|
|
return new SafeDataReader(DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Return a single object value based on query, ID parameter and RootObjectType
|
|
/// (returns first column of first row as Object)
|
|
/// </summary>
|
|
/// <param name="SqlCommandString">A SQL string containing two parameters @RootObjectID and @RootObjectType</param>
|
|
/// <param name="RootObjectID">RootObjectID parameter</param>
|
|
/// <param name="objectType">@RootObjectType parameter</param>
|
|
/// <returns></returns>
|
|
internal static object GetScalarFromSQLString(string SqlCommandString, Guid RootObjectID, RootObjectTypes objectType)
|
|
{
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
dbCommandWrapper.AddInParameter("@RootObjectID", DbType.Guid, RootObjectID);
|
|
dbCommandWrapper.AddInParameter("@RootObjectType", DbType.Int16, (int)objectType);
|
|
return DB.ExecuteScalar(dbCommandWrapper);
|
|
|
|
}
|
|
|
|
|
|
#region Region query modifiers
|
|
/// <summary>
|
|
/// Returns a fragment of a where clause for aClient.aRegion prefixed with AND
|
|
/// for queries with existing where clauses
|
|
/// for filtering queries that are regionalized and contain the aclient table
|
|
/// Used in all selected queries that have subqueries
|
|
/// </summary>
|
|
internal static string RegionAClientClause
|
|
{
|
|
get
|
|
{
|
|
return (User.CurrentUserRegionID != Region.DefaultRegionID) ?
|
|
" AND (aClient.aRegionID IN ('{8236E8D1-CAB1-4797-9C34-93861954AE6A}','{" + User.CurrentUserRegionID.ToString().ToUpperInvariant() + "}')) "
|
|
: "";
|
|
}
|
|
}
|
|
|
|
///// <summary>
|
|
///// Returns a fragment of a where clause for any table name passed in prefixed with WHERE
|
|
///// for queries without existing where clauses
|
|
///// for filtering queries that are regionalized
|
|
///// </summary>
|
|
///// <param name="sTable"></param>
|
|
///// <returns></returns>
|
|
//internal static string RegionGenericWithWhereClause(string sTable)
|
|
//{
|
|
// return (User.CurrentUserRegionID != Region.DefaultRegionID) ?
|
|
// " WHERE ("+ sTable +".aRegionID IN ('{8236E8D1-CAB1-4797-9C34-93861954AE6A}','{" + User.CurrentUserRegionID.ToString().ToUpperInvariant() + "}')) "
|
|
// : "";
|
|
//}
|
|
|
|
///// <summary>
|
|
///// Returns a fragment of a where clause for any table name passed in prefixed with AND
|
|
///// for queries with existing where clauses
|
|
///// for filtering queries that are regionalized
|
|
///// </summary>
|
|
///// <param name="sTable"></param>
|
|
///// <returns></returns>
|
|
//internal static string RegionGenericClause(string sTable)
|
|
//{
|
|
// return (User.CurrentUserRegionID != Region.DefaultRegionID) ?
|
|
// " AND (" + sTable + ".aRegionID IN ('{8236E8D1-CAB1-4797-9C34-93861954AE6A}','{" + User.CurrentUserRegionID.ToString().ToUpperInvariant() + "}')) "
|
|
// : "";
|
|
//}
|
|
|
|
|
|
///// <summary>
|
|
///// Returns a fragment of a where clause for any column name passed in prefixed with AND
|
|
///// for queries with existing where clauses
|
|
///// for filtering queries that are regionalized
|
|
///// </summary>
|
|
///// <param name="sColumn"></param>
|
|
///// <returns></returns>
|
|
//internal static string RegionGenericColumnClause(string sColumn)
|
|
//{
|
|
// return (User.CurrentUserRegionID != Region.DefaultRegionID) ?
|
|
// " AND (" + sColumn + " IN ('{8236E8D1-CAB1-4797-9C34-93861954AE6A}','{" + User.CurrentUserRegionID.ToString().ToUpperInvariant() + "}')) "
|
|
// : "";
|
|
//}
|
|
|
|
///// <summary>
|
|
///// Returns a fragment of a where clause for aClient.aRegion prefixed without AND
|
|
///// for queries with no existing where clauses like client pick list
|
|
///// </summary>
|
|
//internal static string RegionAClientWithWhereClause
|
|
//{
|
|
// get
|
|
// {
|
|
// return (User.CurrentUserRegionID != Region.DefaultRegionID) ?
|
|
// " WHERE (aClient.aRegionID IN ('{8236E8D1-CAB1-4797-9C34-93861954AE6A}','{" + User.CurrentUserRegionID.ToString().ToUpperInvariant() + "}')) "
|
|
// : "";
|
|
// }
|
|
//}
|
|
|
|
/// <summary>
|
|
/// Add region for queries that filter on client region
|
|
/// </summary>
|
|
/// <param name="q"></param>
|
|
/// <returns></returns>
|
|
internal static string AddRegionFilter(string q)
|
|
{
|
|
return AddRegionFilter(q, "", "");
|
|
}
|
|
|
|
/// <summary>
|
|
/// Same as regular AddRegionFilter but with bool parameter for
|
|
/// queries that may or may not use regions
|
|
/// </summary>
|
|
/// <param name="q"></param>
|
|
/// <param name="sTable"></param>
|
|
/// <param name="sColumn"></param>
|
|
/// <param name="Regional"></param>
|
|
/// <returns></returns>
|
|
internal static string AddRegionFilter(string q, string sTable, string sColumn, bool Regional)
|
|
{
|
|
if(!Regional) return q;
|
|
|
|
return AddRegionFilter(q,sTable,sColumn);
|
|
}
|
|
/// <summary>
|
|
/// Modifies input string to include region fragment
|
|
/// checks to see if where clause needs to be added
|
|
/// and inserts before Order by clause
|
|
/// </summary>
|
|
/// <param name="q">Ref query string</param>
|
|
/// <param name="sTable">table name if not "aClient" (default)</param>
|
|
/// <param name="sColumn">column name if not "aRegionID" (default)</param>
|
|
internal static string AddRegionFilter(string q, string sTable, string sColumn)
|
|
{
|
|
|
|
//Short circuit, none of the below may be necessary:
|
|
//If user is in the default region, no need to do any mods to query
|
|
if (User.CurrentUserRegionID == Region.DefaultRegionID) return q;
|
|
|
|
//case 2033 - regions shouldn't affect client user queries because they are irrelevant
|
|
if (User.CurrentUserType == UserTypes.Client || User.CurrentUserType == UserTypes.HeadOffice) return q;
|
|
|
|
//default to aClient because the majority of queries filter by client
|
|
if (string.IsNullOrEmpty(sTable))
|
|
sTable = "aClient";
|
|
|
|
if (string.IsNullOrEmpty(sColumn))
|
|
sColumn = "aRegionID";
|
|
|
|
//build fragment
|
|
System.Text.StringBuilder sb = new System.Text.StringBuilder();
|
|
int nOrderByStart = -1;
|
|
int nWhereStart = -1;
|
|
//last index because queries could contain sub queries
|
|
nOrderByStart = q.LastIndexOf("ORDER BY", StringComparison.InvariantCultureIgnoreCase);
|
|
nWhereStart = q.LastIndexOf(" WHERE ", StringComparison.InvariantCultureIgnoreCase);
|
|
|
|
//special case for embedded sub queries where the order by may come before the final where clause
|
|
if (nOrderByStart < nWhereStart)
|
|
nOrderByStart = -1;
|
|
|
|
if (nWhereStart == -1)
|
|
sb.Append(" WHERE (");
|
|
else
|
|
sb.Append(" AND (");
|
|
|
|
sb.Append(sTable);
|
|
sb.Append(".");
|
|
sb.Append(sColumn);
|
|
sb.Append(" IN ('{8236E8D1-CAB1-4797-9C34-93861954AE6A}','{");
|
|
sb.Append(User.CurrentUserRegionID.ToString().ToUpperInvariant());
|
|
sb.Append("}')) ");
|
|
|
|
|
|
//If no order by then can just append and done
|
|
if (nOrderByStart == -1)
|
|
{
|
|
q = q + sb.ToString();
|
|
return q;
|
|
}
|
|
|
|
//There is an order by clause so we need to insert our filter before it
|
|
string s1 = q.Substring(0, nOrderByStart - 1);
|
|
string s2 = q.Substring(nOrderByStart);
|
|
q = q.Substring(0, nOrderByStart - 1) + sb.ToString() + q.Substring(nOrderByStart);
|
|
return q;
|
|
|
|
}
|
|
#endregion region query modifiers
|
|
#endregion
|
|
|
|
#region Insert Keywords
|
|
/// <summary>
|
|
/// Insert keywords into search tables
|
|
/// </summary>
|
|
/// <param name="transaction"></param>
|
|
/// <param name="RootObjectID"></param>
|
|
/// <param name="RootObjectType"></param>
|
|
/// <param name="NewRecord">if false then clears any existing keywords out of dictionary first</param>
|
|
/// <param name="Keywords"></param>
|
|
internal static void ProcessKeywords(IDbTransaction transaction,Guid RootObjectID, RootObjectTypes RootObjectType, bool NewRecord,string Keywords)
|
|
{
|
|
//Clear index if necessary:
|
|
if(!NewRecord)
|
|
{
|
|
DBCommandWrapper cm = GetCommandFromSQL("DELETE FROM aSearchKey WHERE (aSourceObjectID=@RootObjectID AND aSourceObjectType=@RootObjectType);");
|
|
cm.AddInParameter("@RootObjectID",DbType.Guid,RootObjectID);
|
|
cm.AddInParameter("@RootObjectType",DbType.Int16,RootObjectType);
|
|
DB.ExecuteNonQuery(cm, transaction);
|
|
}
|
|
|
|
//bail early if there is nothing to index
|
|
if(Keywords==null || Keywords=="") return;
|
|
|
|
|
|
//Using an in memory data table get all the relevant word id's
|
|
//and insert the non-existing ones
|
|
|
|
//Build a DataTable of all words and their id's
|
|
//set to Guid.empty any word that isn't in the database already
|
|
DataTable dtWords = new DataTable("WORDDATA");
|
|
dtWords.Columns.Add("WORD",typeof(System.String));
|
|
dtWords.Columns.Add("WORDID",typeof(System.Guid));
|
|
|
|
DBCommandWrapper dbWords = DB.GetSqlStringCommandWrapper("SELECT aID FROM aSearchDictionary WHERE aWord = @Word;");
|
|
|
|
string[] kw=Keywords.Split(',');
|
|
foreach(string s in kw)
|
|
{
|
|
DataRow dr=dtWords.NewRow();
|
|
dr["WORD"]=s;
|
|
dbWords.Command.Parameters.Clear();
|
|
dbWords.AddInParameter("@Word",DbType.String,s);
|
|
|
|
|
|
SafeDataReader r = new SafeDataReader(DB.ExecuteReader(dbWords,transaction));
|
|
if(r.Read())
|
|
dr["WORDID"]=r.GetGuid("aID");
|
|
else
|
|
dr["WORDID"]=Guid.Empty;
|
|
|
|
r.Close();
|
|
dtWords.Rows.Add(dr);
|
|
|
|
}
|
|
|
|
//Loop through word table and insert in dictionary words not already there
|
|
DBCommandWrapper cmDict = GetCommandFromSQL("INSERT INTO aSearchDictionary (aWord, aID) VALUES (@Word, @WordID);");
|
|
|
|
foreach(DataRow dr in dtWords.Rows)
|
|
{
|
|
Guid g=(Guid)dr["WORDID"];
|
|
if(g==Guid.Empty)
|
|
{
|
|
g=Guid.NewGuid();
|
|
cmDict.Command.Parameters.Clear();
|
|
cmDict.AddInParameter("@Word",DbType.String,(string)dr["WORD"]);
|
|
cmDict.AddInParameter("@WordID",DbType.Guid,g);
|
|
|
|
|
|
DB.ExecuteNonQuery(cmDict,transaction);
|
|
dr["WORDID"]=g;
|
|
|
|
}
|
|
}
|
|
|
|
//Now insert searchkey records
|
|
DBCommandWrapper cmKey = GetCommandFromSQL(
|
|
"INSERT INTO aSearchKey (aWordID, aSourceObjectID, aSourceObjectType) " +
|
|
"VALUES (@WordID, @RootObjectID, @RootObjectType)");
|
|
|
|
foreach(DataRow dr in dtWords.Rows)
|
|
{
|
|
cmKey.Command.Parameters.Clear();
|
|
cmKey.AddInParameter("@WordID",DbType.Guid,(Guid)dr["WORDID"]);
|
|
cmKey.AddInParameter("@RootObjectID",DbType.Guid,RootObjectID);
|
|
cmKey.AddInParameter("@RootObjectType",DbType.Int16,RootObjectType);
|
|
|
|
DB.ExecuteNonQuery(cmKey,transaction);
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region Getcommandfromsql
|
|
internal static DBCommandWrapper GetCommandFromSQL(string SqlCommandString)
|
|
{
|
|
|
|
return DB.GetSqlStringCommandWrapper(SqlCommandString);
|
|
}
|
|
#endregion
|
|
|
|
#region Remove keywords / Documents
|
|
/// <summary>
|
|
///Used by every object indexed with keywords
|
|
///to delete that objects keywords from the search dictionary
|
|
///when that object is deleted under the same transaction
|
|
/// </summary>
|
|
/// <param name="transaction"></param>
|
|
/// <param name="RootObjectType"></param>
|
|
/// <param name="RootObjectID"></param>
|
|
internal static void RemoveKeywords(IDbTransaction transaction, RootObjectTypes RootObjectType, Guid RootObjectID)
|
|
{
|
|
DBCommandWrapper cm = GetCommandFromSQL("DELETE FROM aSearchKey WHERE (aSourceObjectID=@RootObjectID AND aSourceObjectType=@RootObjectType);");
|
|
cm.AddInParameter("@RootObjectID",DbType.Guid,RootObjectID);
|
|
cm.AddInParameter("@RootObjectType",DbType.Int16,RootObjectType);
|
|
DB.ExecuteNonQuery(cm, transaction);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
///Used by every object with Assigned documents and wikipages
|
|
///to delete that objects documents and wikipages
|
|
///when that object is deleted under the same transaction
|
|
/// </summary>
|
|
/// <param name="transaction"></param>
|
|
/// <param name="RootObjectType"></param>
|
|
/// <param name="RootObjectID"></param>
|
|
internal static void RemoveDocs(IDbTransaction transaction, RootObjectTypes RootObjectType, Guid RootObjectID)
|
|
{
|
|
//AssignedDoc Keywords
|
|
SafeDataReader dr = null;
|
|
System.Collections.ArrayList al = new System.Collections.ArrayList();
|
|
try
|
|
{
|
|
dr=GetReaderFromSQLString("SELECT aID " +
|
|
"FROM AASSIGNEDDOC " +
|
|
"WHERE aRootObjectID=@ID",RootObjectID,transaction);
|
|
while(dr.Read())
|
|
{
|
|
al.Add(dr.GetGuid("aID"));
|
|
|
|
}
|
|
dr.Close();
|
|
foreach(object o in al)
|
|
RemoveKeywords(transaction,RootObjectTypes.AssignedDocument,(Guid)o);
|
|
}
|
|
finally
|
|
{
|
|
if(dr!=null) dr.Close();
|
|
}
|
|
|
|
|
|
//Delete AssignedDocs
|
|
DBCommandWrapper cmDeleteDocs = GetCommandFromSQL(
|
|
"DELETE FROM AASSIGNEDDOC WHERE (aRootObjectID=@RootObjectID and aRootObjectType=@RootObjectType)");
|
|
|
|
cmDeleteDocs.AddInParameter("@RootObjectID",DbType.Guid,RootObjectID);
|
|
cmDeleteDocs.AddInParameter("@RootObjectType",DbType.Int16,RootObjectType);
|
|
|
|
//Execute
|
|
//DB.ExecuteNonQuery(cmDeleteDocKeywords, transaction);
|
|
DB.ExecuteNonQuery(cmDeleteDocs, transaction);
|
|
|
|
|
|
//Delete WikiPages linked to this rootobject case 73
|
|
//Collect id's of all wikipages that are linked to object being deleted
|
|
System.Collections.ArrayList alWikiPages = new System.Collections.ArrayList();
|
|
try
|
|
{
|
|
dr = GetReaderFromSQLString("SELECT aID " +
|
|
"FROM AWIKIPAGE " +
|
|
"WHERE aRootObjectID=@ID", RootObjectID, transaction);
|
|
while (dr.Read())
|
|
{
|
|
alWikiPages.Add(dr.GetGuid("aID"));
|
|
|
|
}
|
|
dr.Close();
|
|
//Delete them. WikiPage.Delete will look after keywords and child wikipages of itself
|
|
foreach (object o in alWikiPages)
|
|
WikiPage.DeleteItem((Guid)o, transaction);
|
|
}
|
|
finally
|
|
{
|
|
if (dr != null) dr.Close();
|
|
}
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region Concurrency checking
|
|
/// <summary>
|
|
/// Compares two dates and determines if they are equal down to seconds.
|
|
/// (Portions smaller than seconds are ignored)
|
|
/// </summary>
|
|
/// <param name="dt1"></param>
|
|
/// <param name="dt2"></param>
|
|
/// <returns></returns>
|
|
public static bool DatesAreEqualish(System.DateTime dt1, System.DateTime dt2)
|
|
{
|
|
System.DateTime dtFirst=new DateTime(dt1.Year,dt1.Month,dt1.Day,dt1.Hour,dt1.Minute,dt1.Second,0);
|
|
System.DateTime dtSecond=new DateTime(dt2.Year,dt2.Month,dt2.Day,dt2.Hour,dt2.Minute,dt2.Second,0);
|
|
return dtFirst.Equals(dtSecond);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Check for record being changed by another user while they were editing it separately
|
|
/// </summary>
|
|
/// <param name="LastUpdated"></param>
|
|
/// <param name="ID"></param>
|
|
/// <param name="table"></param>
|
|
internal static void CheckSafeToUpdate(System.DateTime LastUpdated, Guid ID, string table)
|
|
{
|
|
//case 624
|
|
if (AyaBizUtils.AyaNovaConnectionSetting.SingleUserConnection) return;
|
|
CheckSafeToUpdateInsideTransaction(LastUpdated,ID,table,null);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Transaction version, called by objects in collections to avoid timeout on conflict
|
|
/// </summary>
|
|
/// <param name="LastUpdated"></param>
|
|
/// <param name="ID"></param>
|
|
/// <param name="table"></param>
|
|
/// <param name="tr"></param>
|
|
internal static void CheckSafeToUpdateInsideTransaction(System.DateTime LastUpdated, Guid ID, string table,IDbTransaction tr)
|
|
{
|
|
//case 624
|
|
if (AyaBizUtils.AyaNovaConnectionSetting.SingleUserConnection) return;
|
|
|
|
DBCommandWrapper dbCommandWrapper = DB.GetSqlStringCommandWrapper("SELECT aModified, aModifier FROM " + table + " WHERE aID = @ID;");
|
|
dbCommandWrapper.AddInParameter("@ID",DbType.Guid,ID);
|
|
SafeDataReader r = null;
|
|
//changed: 20-June-2006 Noticed this isn't explicitly closing the data reader
|
|
//added the try and catch block so could put that in the finally block
|
|
try
|
|
{
|
|
if (tr != null)
|
|
r = new SafeDataReader(DBUtil.DB.ExecuteReader(dbCommandWrapper, tr));
|
|
else
|
|
r = new SafeDataReader(DBUtil.DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
if (r.Read())
|
|
{
|
|
|
|
if (!DatesAreEqualish(DBUtil.ToUTC(LastUpdated), r.GetSmartDate("aModified").Date))
|
|
{
|
|
Guid gModifier = r.GetGuid("aModifier");
|
|
r.Close();
|
|
dbCommandWrapper.Command.Parameters.Clear();
|
|
dbCommandWrapper.AddInParameter("@ID", DbType.Guid, gModifier);
|
|
dbCommandWrapper.Command.CommandText = "SELECT aFirstName, aLastName FROM aUser WHERE aID = @ID";
|
|
|
|
//changed: 20-June-2006 so that transaction is used here if one was passed
|
|
//if this is not done then this code will throw an exception on a transaction
|
|
//type check
|
|
if (tr != null)
|
|
r = new SafeDataReader(DBUtil.DB.ExecuteReader(dbCommandWrapper, tr));
|
|
else
|
|
r = new SafeDataReader(DBUtil.DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
//r = new SafeDataReader(DBUtil.DB.ExecuteReader(dbCommandWrapper));
|
|
|
|
if (r.Read())
|
|
{
|
|
string sUser = r.GetString("aFirstName") + " " + r.GetString("aLastName");
|
|
r.Close();
|
|
|
|
throw new AyConcurrencyException(string.Format(LocalizedTextTable.GetLocalizedTextDirect("Error.DB.RecordModifiedExternally"), table, sUser));
|
|
|
|
}
|
|
|
|
}
|
|
//else
|
|
//{
|
|
// //de nada
|
|
// r.Close();
|
|
// return;
|
|
//}
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
if(r!=null) r.Close();
|
|
}
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region DB related error handling
|
|
internal static void ThrowFetchError(string ExtraInfo)
|
|
{
|
|
|
|
throw new FetchException(string.Format(LocalizedTextTable.GetLocalizedTextDirect("Error.DB.FetchError"),ExtraInfo));
|
|
|
|
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Convert dates to / from UTC offsetting if required
|
|
//case 1163 changes to accomodate timezone offset feature
|
|
/// <summary>
|
|
/// Convert SmartDate object to universal time
|
|
/// If a user time zone offset is not null
|
|
/// that value is used for conversion, otherwise local machine
|
|
/// timezone is used
|
|
/// </summary>
|
|
/// <param name="sd"></param>
|
|
/// <returns></returns>
|
|
public static SmartDate ToUTC(SmartDate sd)
|
|
{
|
|
if (sd.IsEmpty)
|
|
return sd;
|
|
else
|
|
{
|
|
|
|
if (!AyaBizUtils.OverrideTimeZone)
|
|
return new SmartDate(sd.Date.ToUniversalTime());
|
|
else
|
|
return new SmartDate(sd.Date.AddHours(AyaBizUtils.TimeZoneOffset*-1));//*-1 to flip the sign since there is no SubtractHours method in datetime
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Convert SmartDate object to local time
|
|
/// If a user time zone offset is not null
|
|
/// that value is used for conversion, otherwise local machine
|
|
/// timezone is used
|
|
/// </summary>
|
|
/// <param name="sd"></param>
|
|
/// <returns></returns>
|
|
public static SmartDate ToLocal(SmartDate sd)
|
|
{
|
|
if(sd.IsEmpty)
|
|
return sd;
|
|
else
|
|
if (!AyaBizUtils.OverrideTimeZone)
|
|
return new SmartDate(sd.Date.ToLocalTime());
|
|
else
|
|
return new SmartDate(sd.Date.AddHours(AyaBizUtils.TimeZoneOffset));
|
|
}
|
|
|
|
/// <summary>
|
|
/// Convert DateTime object to universal time
|
|
/// If a user time zone offset is not null
|
|
/// that value is used for conversion, otherwise local machine
|
|
/// timezone is used
|
|
/// </summary>
|
|
/// <param name="sd"></param>
|
|
/// <returns></returns>
|
|
public static DateTime ToUTC(DateTime sd)
|
|
{
|
|
if (!AyaBizUtils.OverrideTimeZone)
|
|
return sd.ToUniversalTime();
|
|
else
|
|
return sd.AddHours(AyaBizUtils.TimeZoneOffset * -1);//*-1 to flip the sign since there is no SubtractHours method in datetime
|
|
}
|
|
|
|
/// <summary>
|
|
/// Convert DateTime object to local time
|
|
/// If a user time zone offset is not null
|
|
/// that value is used for conversion, otherwise local machine
|
|
/// timezone is used
|
|
/// </summary>
|
|
/// <param name="sd"></param>
|
|
/// <returns></returns>
|
|
public static DateTime ToLocal(DateTime sd)
|
|
{
|
|
if (!AyaBizUtils.OverrideTimeZone)
|
|
return sd.ToLocalTime();
|
|
else
|
|
return sd.AddHours(AyaBizUtils.TimeZoneOffset);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Returns current date time adjusted by user TimeZoneOffset
|
|
/// if applicable
|
|
/// </summary>
|
|
public static DateTime CurrentWorkingDateTime
|
|
{
|
|
get
|
|
{
|
|
DateTime dtNow = DateTime.Now;
|
|
if (!AyaBizUtils.OverrideTimeZone)
|
|
return dtNow;
|
|
else
|
|
return dtNow.ToUniversalTime().AddHours(AyaBizUtils.TimeZoneOffset);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Returns current date time adjusted by user TimeZoneOffset
|
|
/// if applicable as a SmartDate
|
|
/// </summary>
|
|
public static SmartDate CurrentWorkingDateTimeAsSmartDate
|
|
{
|
|
get
|
|
{
|
|
DateTime dtNow = DateTime.Now;
|
|
if (!AyaBizUtils.OverrideTimeZone)
|
|
return new SmartDate(dtNow);
|
|
else
|
|
return new SmartDate(dtNow.ToUniversalTime().AddHours(AyaBizUtils.TimeZoneOffset));
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region Convert scalar object to Guid from FireBird / MSSql
|
|
internal static Guid ToGuid(object o)
|
|
{
|
|
if(o is Guid) return (Guid)o;
|
|
if(o==null || o==System.DBNull.Value) return Guid.Empty;
|
|
if(o is String) return new Guid((string)o);
|
|
throw new ApplicationException("DBUtil.ToGuid - Cannot convert type " + o.GetType().ToString() + " to Guid value.");
|
|
|
|
|
|
}
|
|
|
|
internal static string ScalarToString(object o)
|
|
{
|
|
if(o is string) return (string)o;
|
|
if(o==null || o==System.DBNull.Value) return "";
|
|
|
|
throw new ApplicationException("DBUtil.ToString - Cannot convert type " + o.GetType().ToString() + " to Guid value.");
|
|
|
|
|
|
}
|
|
|
|
internal static long ScalarToLong(object o)
|
|
{
|
|
if(o is long) return (long)o;
|
|
|
|
if(o==null || o==System.DBNull.Value) return 0;
|
|
return System.Convert.ToInt64(o);
|
|
//throw new ApplicationException("DBUtil.ToString - Cannot convert type " + o.GetType().ToString() + " to Guid value.");
|
|
|
|
|
|
}
|
|
|
|
//case 963
|
|
internal static decimal ScalarToDecimal(object o)
|
|
{
|
|
if (o is decimal) return (decimal)o;
|
|
|
|
if (o == null || o == System.DBNull.Value) return 0;
|
|
return System.Convert.ToDecimal(o);
|
|
|
|
|
|
}
|
|
|
|
internal static bool ScalarToBool(object o)
|
|
{
|
|
if (o is bool) return (bool)o;
|
|
|
|
if (o == null || o == System.DBNull.Value) return false;
|
|
return System.Convert.ToBoolean(o);
|
|
//throw new ApplicationException("DBUtil.ToString - Cannot convert type " + o.GetType().ToString() + " to Guid value.");
|
|
|
|
|
|
}
|
|
|
|
//case 1283
|
|
internal static int ScalarToInt(object o)
|
|
{
|
|
if (o is int) return (int)o;
|
|
|
|
if (o == null || o == System.DBNull.Value) return 0;
|
|
return System.Convert.ToInt32(o);
|
|
//throw new ApplicationException("DBUtil.ToString - Cannot convert type " + o.GetType().ToString() + " to Guid value.");
|
|
|
|
|
|
}
|
|
|
|
//case 2094
|
|
/// <summary>
|
|
/// Scalar to date
|
|
/// </summary>
|
|
/// <param name="o"></param>
|
|
/// <returns>DateTime.MinValue if null or unparseable</returns>
|
|
internal static DateTime ScalarToDate(object o)
|
|
{
|
|
if (o is DateTime) return (DateTime)o;
|
|
if (o == null || o == System.DBNull.Value) return DateTime.MinValue;
|
|
return System.Convert.ToDateTime(o);
|
|
}
|
|
#endregion
|
|
|
|
#region Identity field import utils
|
|
internal static void AllowIdentityInsert()
|
|
{
|
|
throw new ApplicationException("DBUtil:AllowIdentityInsert: STUB");
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
}
|
|
}
|