public static bool DeleteDate(PTDate date)
{
bool deleted = false;
SqlCeConnection connection = null;
int noOfRowAffected = 0;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM Dates WHERE Date=@date";
command.Parameters.Add("@date", SqlDbType.DateTime).Value = date.date;
noOfRowAffected = command.ExecuteNonQuery();
if (noOfRowAffected > 0)
{
deleted = true;
}
}
finally
{
connection.Close();
}
return(deleted);
}
public static bool DeleteProcessInfo(PTProcessInfo info, PTDate date)
{
bool deleted = false;
SqlCeConnection connection = null;
int noOfRowAffected = 0;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM ProcessInfos WHERE Name=@name AND DateIdx=@dateIdx";
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = info.name;
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
noOfRowAffected = command.ExecuteNonQuery();
if (noOfRowAffected > 0)
{
deleted = true;
}
}
finally
{
connection.Close();
}
return(deleted);
}
public static bool InsertProcessInfo(string name, TimeSpan time, PTDate date)
{
bool inserted = false;
SqlCeConnection connection = null;
int noOfRowAffected = 0;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO ProcessInfos (Name, ActiveTime, DateIdx) VALUES (@name, @active, @dateIdx)";
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
command.Parameters.Add("@active", SqlDbType.Int).Value = time.TotalMinutes;
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
noOfRowAffected = command.ExecuteNonQuery();
if (noOfRowAffected > 0)
{
inserted = true;
}
}
finally
{
connection.Close();
}
return(inserted);
}
public static PTDate GetDateForDateTime(DateTime datetime)
{
SqlCeConnection connection = null;
PTDate date = new PTDate(0, default(DateTime));
bool success = false;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Dates WHERE Date=@date";
command.Parameters.Add("@date", SqlDbType.DateTime).Value = datetime.Date;
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read())
{
date.index = reader.GetInt32(0);
date.date = reader.GetDateTime(1);
success = true;
}
}
finally
{
connection.Close();
}
if (!success)
{
InsertDate(datetime);
date = GetDateForDateTime(datetime);
}
return(date);
}
public static bool InsertDate(PTDate date)
{
bool inserted = false;
SqlCeConnection connection = null;
int noOfRowAffected = 0;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO Dates (Date) VALUES (@date)";
command.Parameters.Add("@date", SqlDbType.DateTime).Value = date.date;
noOfRowAffected = command.ExecuteNonQuery();
if (noOfRowAffected > 0)
{
inserted = true;
}
}
finally
{
connection.Close();
}
return(inserted);
}
public static PTProcessInfo GetInfoForProcessOnDate(string processName, PTDate date)
{
PTProcessInfo info = new PTProcessInfo(0, string.Empty, default(TimeSpan));
SqlCeConnection connection = null;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM ProcessInfos WHERE Name=@name AND DateIdx=@dateIdx";
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = processName;
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read())
{
info.index = reader.GetInt32(0);
info.name = reader.GetString(1);
info.activeTime = new TimeSpan(0, reader.GetInt32(2), 0);
}
}
finally
{
connection.Close();
}
return(info);
}
public static List <PTProcessInfo> ProcessInfoForDate(PTDate date)
{
List <PTProcessInfo> processInfoList = new List <PTProcessInfo>();
SqlCeConnection connection = null;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM ProcessInfos WHERE DateIdx=@dateIdx";
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PTProcessInfo processInfo;
processInfo.index = reader.GetInt32(0);
processInfo.name = reader.GetString(1);
processInfo.activeTime = new TimeSpan(0, reader.GetInt32(2), 0);
processInfoList.Add(processInfo);
}
}
finally
{
connection.Close();
}
return(processInfoList);
}
public DataTable GetProcessInfoTableForDate(DateTime datetime)
{
DataTable table = new DataTable("ProcessInfos");
PTDate date = PTDatabase.GetDateForDateTime(datetime);
SqlCeDataAdapter adapter = PTDatabase.GetAdaperForProcessInfosViewForDate(date);
adapter.Fill(table);
return(table);
}
public static void HandleRecord(PTRecord record)
{
PTDate date = PTDatabase.GetDateForDateTime(record.datetime);
if (!PTDatabase.UpdateProcessTime(record.name, date, record.time))
{
PTDatabase.InsertProcessInfo(record.name, record.time, date);
}
}
public static SqlCeDataAdapter GetAdaperForProcessInfosViewForDate(PTDate date)
{
SqlCeDataAdapter adapter = null;
SqlCeConnection connection = null;
try
{
connection = Connection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT Name, ActiveTime FROM ProcessInfos WHERE DateIdx=@dateIdx";
command.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
command.ExecuteNonQuery();
adapter = new SqlCeDataAdapter(command);
Console.WriteLine(adapter.ToString());
}
finally
{
connection.Close();
}
return(adapter);
}
public static bool UpdateProcessTime(string name, PTDate date, TimeSpan time)
{
bool updated = false;
SqlCeConnection connection = null;
int noOfRowAffected = 0;
try
{
connection = Connection();
connection.Open();
var getTimeStepCommand = connection.CreateCommand();
getTimeStepCommand.CommandText = "SELECT ActiveTime FROM ProcessInfos WHERE Name=@name AND DateIdx=@dateIdx";
getTimeStepCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
getTimeStepCommand.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
SqlCeDataReader reader = getTimeStepCommand.ExecuteReader();
if (reader.Read())
{
int previousTime = reader.GetInt32(0);
var updateCommand = connection.CreateCommand();
updateCommand.CommandText = "UPDATE ProcessInfos SET ActiveTime=@active WHERE Name=@name AND DateIdx=@dateIdx";
updateCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
updateCommand.Parameters.Add("@active", SqlDbType.Int).Value = previousTime + time.Minutes;
updateCommand.Parameters.Add("@dateIdx", SqlDbType.Int).Value = date.index;
noOfRowAffected = updateCommand.ExecuteNonQuery();
if (noOfRowAffected > 0)
{
updated = true;
}
}
}
finally
{
connection.Close();
}
return(updated);
}