private int _AddTemplateField(TemplateFieldData FieldData)
{
int result = -1;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD";
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows && resultData.Read())
{
int idxColumn = resultData.GetOrdinal("NEW_ID");
object value = resultData.GetValue(idxColumn);
if (typeof(DBNull) == value.GetType())
{
result = 1;
}
else
{
result = (int)value;//resultData.GetInt32(idxColumn);
}
}
else
{
result = 1;
}
resultData.Close();
sqlQuery = "INSERT INTO TEMPLATE_FIELD(ID,LABEL,ID_FIELD_TYPE) VALUES(" + result + ",'" + FieldData.FieldName + "'," + (FieldData.FieldType == TemplateFieldType.Text ? "0" : "1") + ")";
dataContext.Execute(sqlQuery);
}
return(result);
}
public Category[] GetCategories()
{
List <Category> result = new List <Category>();
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
// CHECK IF DATABASE IS OK
String sqlQuery = "SELECT ID, LABEL FROM CATEGORIES";
OleDbDataReader queryData = dataContext.Select(sqlQuery);
if (queryData.HasRows)
{
bool hasRecord = queryData.Read();
while (hasRecord)
{
Category newCatagory = new Category();
int idxColumn = queryData.GetOrdinal("ID");
newCatagory.Id = queryData.GetInt32(idxColumn);
idxColumn = queryData.GetOrdinal("LABEL");
newCatagory.Name = queryData.GetString(idxColumn);
string queryTemplateField = "SELECT TFC.ID AS ID_TFC, TF.LABEL AS LABEL_FIELD, TF.ID_FIELD_TYPE AS TYPE_FIELD " + Environment.NewLine
+ " FROM TEMPLATE_FIELD_CATEGORY AS TFC INNER JOIN TEMPLATE_FIELD AS TF ON TFC.ID_TEMPLATE_FIELD=TF.ID" + Environment.NewLine
+ " WHERE TFC.ID_CATEGORY = " + newCatagory.Id;
List <TemplateFieldData> categoryTfDatas = new List <TemplateFieldData>();
OleDbDataReader tfFieldData = dataContext.Select(queryTemplateField);
if (tfFieldData.HasRows)
{
bool hasTfData = true;
while (tfFieldData.Read())
{
TemplateFieldData dataTF = new TemplateFieldData();
idxColumn = tfFieldData.GetOrdinal("ID_TFC");
dataTF.Id = tfFieldData.GetInt32(idxColumn);
idxColumn = tfFieldData.GetOrdinal("LABEL_FIELD");
dataTF.FieldName = tfFieldData.GetString(idxColumn);
idxColumn = tfFieldData.GetOrdinal("TYPE_FIELD");
int typeId = tfFieldData.GetInt32(idxColumn);
dataTF.FieldType = (TemplateFieldType)typeId;
dataTF.IdFieldCategory = dataTF.Id;
//hasTfData = tfFieldData.Read();
categoryTfDatas.Add(dataTF);
}
}
tfFieldData.Close();
newCatagory.TemplateFields = categoryTfDatas.ToArray();
result.Add(newCatagory);
hasRecord = queryData.Read();
}
queryData.Close();
}
}
return(result.ToArray());
}
public TemplateFieldData GetTemplateField(int IdTemplateField)
{
TemplateFieldData result = null;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT ID, LABEL, ID_FIELD_TYPE FROM TEMPLATE_FIELD WHERE ID = " + IdTemplateField;
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows)
{
result = new TemplateFieldData();
int idxColumn = resultData.GetOrdinal("ID");
result.Id = resultData.GetInt32(idxColumn);
idxColumn = resultData.GetOrdinal("LABEL");
result.FieldName = resultData.GetString(idxColumn);
idxColumn = resultData.GetOrdinal("ID");
result.FieldType = (TemplateFieldType)resultData.GetInt32(idxColumn);
}
}
return(result);
}
public int AddTemplateField(TemplateFieldData FieldDefinition)
{
return(_AddTemplateField(FieldDefinition));
}
