public void InsertDocumentObject(UnigueID unigueID, bool spend, DateTime spend_date, string table, string[] fieldArray, Dictionary <string, object> fieldValue)
{
string query_field = "uid, spend, spend_date";
string query_values = "@uid, @spend, @spend_date";
foreach (string field in fieldArray)
{
query_field += ", " + field;
query_values += ", @" + field;
}
string query = "INSERT INTO " + table + " (" + query_field + ") VALUES (" + query_values + ")";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("uid", unigueID.UGuid));
nCommand.Parameters.Add(new NpgsqlParameter("spend", spend));
nCommand.Parameters.Add(new NpgsqlParameter("spend_date", spend_date));
foreach (string field in fieldArray)
{
nCommand.Parameters.Add(new NpgsqlParameter(field, fieldValue[field]));
}
nCommand.ExecuteNonQuery();
}
/// <summary>
/// Зчитати дані
/// </summary>
/// <param name="uid">Унікальний ідентифікатор </param>
/// <returns></returns>
protected bool BaseRead(UnigueID uid)
{
if (uid == null || uid.UGuid == Guid.Empty)
{
return(false);
}
BaseClear();
bool spend = false;
DateTime spend_date = DateTime.MinValue;
if (Kernel.DataBase.SelectDocumentObject(uid, ref spend, ref spend_date, Table, FieldArray, FieldValue))
{
UnigueID = uid;
Spend = spend;
SpendDate = spend_date;
IsSave = true;
return(true);
}
else
{
return(false);
}
}
public void InsertDirectoryTablePartRecords(Guid UID, UnigueID ownerUnigueID, string table, string[] fieldArray, Dictionary <string, object> fieldValue)
{
string query_field = "uid, owner";
string query_values = "@uid, @owner";
foreach (string field in fieldArray)
{
query_field += ", " + field;
query_values += ", @" + field;
}
string query = "INSERT INTO " + table + " (" + query_field + ") VALUES (" + query_values + ")";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("uid", UID));
nCommand.Parameters.Add(new NpgsqlParameter("owner", ownerUnigueID.UGuid));
foreach (string field in fieldArray)
{
nCommand.Parameters.Add(new NpgsqlParameter(field, fieldValue[field]));
}
nCommand.ExecuteNonQuery();
}
//Зчитування даних
protected void BaseRead(UnigueID ownerUnigueID)
{
BaseClear();
JoinValue.Clear();
QuerySelect.Where.Clear();
QuerySelect.Where.Add(new Where("owner", Comparison.EQ, ownerUnigueID.UGuid));
Kernel.DataBase.SelectDocumentTablePartRecords(QuerySelect, FieldValueList);
//Якщо задані додаткові поля з псевдонімами, їх потрібно зчитати в список JoinValue
if (QuerySelect.FieldAndAlias.Count > 0)
{
foreach (Dictionary <string, object> fieldValue in FieldValueList)
{
Dictionary <string, string> joinFieldValue = new Dictionary <string, string>();
JoinValue.Add(fieldValue["uid"].ToString(), joinFieldValue);
foreach (NameValue <string> fieldAndAlias in QuerySelect.FieldAndAlias)
{
joinFieldValue.Add(fieldAndAlias.Value, fieldValue[fieldAndAlias.Value].ToString());
}
}
}
}
public bool SelectDirectoryObject(DirectoryObject directoryObject /*??*/, UnigueID unigueID, string table, string[] fieldArray, Dictionary <string, object> fieldValue)
{
string query = "SELECT uid ";
foreach (string field in fieldArray)
{
query += ", " + field;
}
query += " FROM " + table + " WHERE uid = @uid";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("uid", unigueID.UGuid));
NpgsqlDataReader reader = nCommand.ExecuteReader();
bool isSelectDirectoryObject = reader.HasRows;
while (reader.Read())
{
foreach (string field in fieldArray)
{
fieldValue[field] = reader[field];
}
}
reader.Close();
return(isSelectDirectoryObject);
}
public void SelectDocumentTablePartRecords(UnigueID ownerUnigueID, string table, string[] fieldArray, List <Dictionary <string, object> > fieldValueList)
{
string query = "SELECT uid";
foreach (string field in fieldArray)
{
query += ", " + field;
}
query += " FROM " + table + " WHERE owner = @owner";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("owner", ownerUnigueID.UGuid));
NpgsqlDataReader reader = nCommand.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> fieldValue = new Dictionary <string, object>();
fieldValueList.Add(fieldValue);
fieldValue.Add("uid", reader["uid"]);
foreach (string field in fieldArray)
{
fieldValue.Add(field, reader[field]);
}
}
reader.Close();
}
public void DeleteDocumentObject(UnigueID unigueID, string table)
{
string query = "DELETE FROM " + table + " WHERE uid = @uid";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("uid", unigueID.UGuid));
nCommand.ExecuteNonQuery();
}
public void DeleteDirectoryTablePartRecords(UnigueID ownerUnigueID, string table)
{
string query = "DELETE FROM " + table + " WHERE owner = @owner";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("owner", ownerUnigueID.UGuid));
nCommand.ExecuteNonQuery();
}
/// <summary>
/// Новий елемент
/// </summary>
/// <param name="use_uid">01.09.2021 Добавив можливість вказувати uid для нових елементів довідника. Використовується для обміну</param>
public void New(UnigueID use_uid = null)
{
if (use_uid != null)
{
UnigueID = use_uid;
}
else
{
UnigueID = new UnigueID(Guid.NewGuid());
}
IsNew = true;
}
public DirectoryObject(Kernel kernel, string table, string[] fieldsArray)
{
Kernel = kernel;
Table = table;
FieldArray = fieldsArray;
FieldValue = new Dictionary <string, object>();
foreach (string field in FieldArray)
{
FieldValue.Add(field, null);
}
UnigueID = new UnigueID(Guid.Empty);
}
/// <summary>
/// Зчитування полів обєкту з бази даних
/// </summary>
/// <param name="uid">Унікальний ідентифікатор обєкту</param>
/// <returns></returns>
protected bool BaseRead(UnigueID uid)
{
if (uid == null || uid.UGuid == Guid.Empty)
{
return(false);
}
BaseClear();
if (Kernel.DataBase.SelectDirectoryObject(this, uid, Table, FieldArray, FieldValue))
{
UnigueID = uid;
return(true);
}
else
{
return(false);
}
}
//public string SelectDirectoryView(DirectoryView directoryView)
//{
// string query = directoryView.QuerySelect.Construct();
// NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
// foreach (Where field in directoryView.QuerySelect.Where)
// if (field.UsingSQLToValue == false)
// nCommand.Parameters.Add(new NpgsqlParameter(field.Alias, field.Value));
// if (directoryView.QuerySelect.CreateTempTable == true)
// {
// nCommand.ExecuteNonQuery();
// query = "SELECT * FROM " + directoryView.QuerySelect.TempTable;
// nCommand = new NpgsqlCommand(query, Connection);
// }
// string xml = "<" + directoryView.Name + ">\n";
// NpgsqlDataReader reader = nCommand.ExecuteReader();
// while (reader.Read())
// {
// xml += " <row>\n";
// xml += " <uid>" + reader["uid"].ToString() + "</uid>\n";
// foreach (string field in directoryView.QuerySelect.Field)
// {
// xml += " <" + directoryView.AliasRevers[field] + ">";
// switch (directoryView.AliasFieldType[field])
// {
// case "string":
// {
// xml += "<![CDATA[" + reader[field].ToString() + "]]>";
// break;
// }
// case "integer":
// case "numeric":
// case "boolean":
// case "date":
// case "datetime":
// case "time":
// case "pointer":
// case "empty_pointer":
// case "enum":
// {
// xml += reader[field].ToString();
// break;
// }
// case "string[]":
// {
// string[] mas = (string[])reader[field];
// foreach (string elem in mas) xml += "<e><![CDATA[" + elem + "]]></e>";
// break;
// }
// case "integer[]":
// {
// int[] mas = (int[])reader[field];
// foreach (int elem in mas) xml += "<e>" + elem.ToString() + "</e>";
// break;
// }
// case "numeric[]":
// {
// decimal[] mas = (decimal[])reader[field];
// foreach (decimal elem in mas) xml += "<e>" + elem.ToString() + "</e>";
// break;
// }
// default:
// {
// xml += "<![CDATA[" + reader[field].ToString() + "]]>";
// break;
// }
// }
// xml += "</" + directoryView.AliasRevers[field] + ">\n";
// }
// xml += " </row>\n";
// }
// reader.Close();
// xml += "</" + directoryView.Name + ">\n";
// return xml;
//}
//public void DeleteDirectoryViewTempTable(DirectoryView directoryView)
//{
// if (directoryView.QuerySelect.CreateTempTable == true &&
// directoryView.QuerySelect.TempTable != "" &&
// directoryView.QuerySelect.TempTable.Substring(0, 4) == "tmp_")
// {
// string query = "DROP TABLE IF EXISTS " + directoryView.QuerySelect.TempTable;
// NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
// nCommand.ExecuteNonQueryAsync();
// }
//}
#endregion
#region Document
public bool SelectDocumentObject(UnigueID unigueID, ref bool spend, ref DateTime spend_date, string table, string[] fieldArray, Dictionary <string, object> fieldValue)
{
string query = "SELECT uid, spend, spend_date";
if (fieldArray != null)
{
foreach (string field in fieldArray)
{
query += ", " + field;
}
}
query += " FROM " + table + " WHERE uid = @uid";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("uid", unigueID.UGuid));
NpgsqlDataReader reader = nCommand.ExecuteReader();
bool isSelectDocumentObject = reader.HasRows;
while (reader.Read())
{
spend = (bool)reader["spend"];
spend_date = (DateTime)reader["spend_date"];
if (fieldValue != null)
{
foreach (string field in fieldArray)
{
fieldValue[field] = reader[field];
}
}
}
reader.Close();
return(isSelectDocumentObject);
}
public void UpdateDocumentObject(UnigueID unigueID, bool spend, DateTime spend_date, string table, string[] fieldArray, Dictionary <string, object> fieldValue)
{
string query = "UPDATE " + table + " SET spend = @spend, spend_date = @spend_date";
foreach (string field in fieldArray)
{
query += ", " + field + " = @" + field;
}
query += " WHERE uid = @uid";
NpgsqlCommand nCommand = new NpgsqlCommand(query, Connection);
nCommand.Parameters.Add(new NpgsqlParameter("uid", unigueID.UGuid));
nCommand.Parameters.Add(new NpgsqlParameter("spend", spend));
nCommand.Parameters.Add(new NpgsqlParameter("spend_date", spend_date));
foreach (string field in fieldArray)
{
nCommand.Parameters.Add(new NpgsqlParameter(field, fieldValue[field]));
}
nCommand.ExecuteNonQuery();
}
/// <summary>
/// Пустий вказівник
/// </summary>
public EmptyPointer()
{
UnigueID = new UnigueID(Guid.Empty);
}
/// <summary>
/// Зберегти один запис таб частини
/// </summary>
/// <param name="UID">Унікальний ідентифікатор запису</param>
/// <param name="ownerUnigueID">Унікальний ідентифікатор власника таб. частини</param>
/// <param name="fieldValue">Список значень полів</param>
protected void BaseSave(Guid UID, UnigueID ownerUnigueID, Dictionary <string, object> fieldValue)
{
Guid recordUnigueID = (UID == Guid.Empty ? Guid.NewGuid() : UID);
Kernel.DataBase.InsertDocumentTablePartRecords(recordUnigueID, ownerUnigueID, Table, FieldArray, fieldValue);
}
/// <summary>
/// Видалити всі дані з таб. частини
/// </summary>
/// <param name="ownerUnigueID">Унікальний ідентифікатор власника таб. частини</param>
protected void BaseDelete(UnigueID ownerUnigueID)
{
Kernel.DataBase.DeleteDocumentTablePartRecords(ownerUnigueID, Table);
}
/// <summary>
/// Ініціалізація вказівника
/// </summary>
/// <param name="uid">Унікальний ідентифікатор</param>
/// <param name="fields">Поля які потрібно додатково зчитати</param>
public void Init(UnigueID uid, Dictionary <string, object> fields = null)
{
UnigueID = uid;
Fields = fields;
}
public DocumentPointer()
{
UnigueID = new UnigueID(Guid.Empty);
}
/// <summary>
/// Новий обєкт
/// </summary>
public void New()
{
UnigueID = new UnigueID(Guid.NewGuid());
IsNew = true;
IsSave = false;
}
/// <summary>
/// Зчитати дані з бази даних
/// </summary>
/// <param name="ownerUnigueID"></param>
protected void BaseRead(UnigueID ownerUnigueID)
{
BaseClear();
Kernel.DataBase.SelectDirectoryTablePartRecords(ownerUnigueID, Table, FieldArray, FieldValueList);
}
public DirectoryPointer()
{
UnigueID = new UnigueID(Guid.Empty);
}