public IEnumerable <DeviceControlOptionsRecord> GetDeviceControlOptions()
{
var records = new List <DeviceControlOptionsRecord>();
var selectCommand = new SqlCommandInfo(
"SELECT" +
" device," +
" current_state," +
" use_schedule," +
" target_state " +
" FROM DeviceStates");
SqlExecutionHelper.ExecuteReader(
selectCommand,
row =>
{
var device = row.GetFieldValue <Devices>("device");
var useSchedule = row.GetFieldValue <bool>("use_schedule");
var record = new DeviceControlOptionsRecord(device, useSchedule)
{
CurrentState = row.GetFieldValue <DeviceStates?>("current_state"),
TargetState = row.GetFieldValue <DeviceStates?>("target_state")
};
records.Add(record);
});
return(records);
}
private string InstallMembershipSchema(string connStringName)
{
//throw new NotImplementedException("This method is still under construction");
var sb = new StringBuilder();
var connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
sb.AppendFormat("Using connection string: {0}", connString);
sb.AppendLine();
using (var conn = new SqlConnection(connString))
{
conn.Open();
using (var cmd = new SqlCommand("", conn))
{
sb.AppendLine("Executing query...");
var helper = new SqlExecutionHelper();
helper.ExecuteBatchNonQuery(SQLScripts.MembershipSchema, cmd);
sb.AppendLine("Success.");
}
conn.Close();
}
sb.AppendLine("SQL Membership Schema has been successfully applied.");
sb.AppendLine();
return(sb.ToString());
}
internal IEnumerable <RebootLogRecord> GetRebootLog(int numberOfRecords)
{
var records = new List <RebootLogRecord>();
var selectCommand = new SqlCommandInfo(
$@"SELECT TOP {numberOfRecords} reboot_time, uptime
FROM
(
SELECT
origin_datetime AS reboot_time,
uptime,
LAG(uptime) OVER(ORDER BY origin_datetime DESC) AS next_uptime
FROM DiagnosticInfoLog
) subq
WHERE
uptime > next_uptime" );
SqlExecutionHelper.ExecuteReader(
selectCommand,
row =>
{
DateTime rebootDateTime = row.GetDateTime(row.GetOrdinal("reboot_time"));
long uptime = row.GetInt64(row.GetOrdinal("uptime"));
records.Add(new RebootLogRecord(rebootDateTime, uptime));
});
return(records);
}
internal decimal GetCurrentTemperature()
{
var selectCommand = new SqlCommandInfo("SELECT TOP 1 temperature FROM TemperatureLog ORDER BY origin_datetime DESC");
var temperature = SqlExecutionHelper.ExecuteScalar <decimal>(selectCommand);
return(temperature);
}
private IEnumerable <DeviceStateLogRecord> getDeviceStateLog(SqlCommandText whereClause)
{
var records = new List <DeviceStateLogRecord>();
var selectCommand = new SqlCommandInfo(
"SELECT" +
" switch_datetime," +
" device," +
" state" +
" FROM DeviceStateLog" +
(whereClause.IsEmpty ? string.Empty : " WHERE " + whereClause.CommandText) +
" ORDER BY switch_datetime DESC");
selectCommand.AddParameters(whereClause);
SqlExecutionHelper.ExecuteReader(
selectCommand,
row =>
{
DateTime datetime = row.GetDateTime(row.GetOrdinal("switch_datetime"));
var device = (Devices)row.GetInt16(row.GetOrdinal("device"));
var state = (DeviceStates)row.GetInt16(row.GetOrdinal("state"));
records.Add(new DeviceStateLogRecord(datetime, device, state));
});
return(records);
}
public override void RunWithoutDocumentContext(RunCustomActionWithoutContextParams args)
{
var log = new StringBuilder();
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
var response = new SkribbleHelper(log, Configuration.ApiConfig).ChecAllkDocumentStatus();
log.AppendLine($"API returned {response.Count()} envelopes");
var sqlQuery = $"SELECT WFD_ID, {Configuration.Workflow.OperationFieldName} " +
$"from WFElements where WFD_STPID = {Configuration.Workflow.StepId}";
var dt = SqlExecutionHelper.GetDataTableForSqlCommandOutsideTransaction(sqlQuery);
CheckSignStatus(dt, response, sw);
}
catch (Exception e)
{
log.AppendLine(e.ToString());
args.HasErrors = true;
args.Message = e.Message;
}
finally
{
args.LogMessage = log.ToString();
args.Context.PluginLogger.AppendInfo(log.ToString());
}
}
private void CheckAndMoveElements(List <Useragreementlist> agreements)
{
Stopwatch sw = new Stopwatch();
sw.Start();
var sqlQuery = $"SELECT WFD_ID, {Configuration.Workflow.OperationFieldName} " +
$"FROM WFElements WHERE WFD_STPID = {Configuration.Workflow.StepId}";
var dt = SqlExecutionHelper.GetDataTableForSqlCommandOutsideTransaction(sqlQuery);
CheckDocumentsStatus(dt, agreements, sw);
}
public DeviceStates GetDeviceState(Devices device)
{
var selectCommand = new SqlCommandInfo(
"SELECT current_state" +
" FROM DeviceStates" +
" WHERE device = @device");
selectCommand.Parameters.Add("device", device);
var rawDeviceState = SqlExecutionHelper.ExecuteScalar <bool>(selectCommand);
return(rawDeviceState ? DeviceStates.On : DeviceStates.Off);
}
internal DateTime GetBootDateTime()
{
var selectCommand = new SqlCommandInfo(
@"SELECT TOP 1 boot_time
FROM
(
SELECT
LAG(origin_datetime) OVER(ORDER BY origin_datetime DESC) AS boot_time,
uptime,
LAG(uptime) OVER(ORDER BY origin_datetime DESC) AS next_uptime
FROM DiagnosticInfoLog
) subq
WHERE uptime > next_uptime" );
return(SqlExecutionHelper.ExecuteScalar <DateTime>(selectCommand));
}
public void MergeDeviceState <T>(Devices device, string fieldName, T fieldValue)
{
string commandText = " MERGE DeviceStates AS target" +
$" USING(SELECT @device, @fieldValue) AS source (device, {fieldName})" +
" ON(target.device = source.device)" +
" WHEN MATCHED THEN" +
$" UPDATE SET {fieldName} = source.{fieldName}" +
" WHEN NOT MATCHED THEN" +
$" INSERT(device, {fieldName})" +
$" VALUES(source.device, source.{fieldName});";
var mergeCommand = new SqlCommandInfo(commandText);
mergeCommand.Parameters.Add("device", device);
mergeCommand.Parameters.Add("fieldValue", fieldValue);
SqlExecutionHelper.ExecuteNonQuery(mergeCommand);
}
private AttachmentData GetAttachment(ActionContextInfo context)
{
if (Configuration.AttConfig.InputAttType == InputType.Category)
{
_log.AppendLine("Downloading attachments by category");
var allAttachments = DocumentAttachmentsManager.GetAttachments(new GetAttachmentsParams()
{
DocumentId = context.CurrentDocument.ID,
IncludeContent = true
});
if (Configuration.AttConfig.CatType == CategoryType.ID)
{
return(allAttachments.FirstOrDefault(x =>
x.FileGroup.ID == Configuration.AttConfig.InputCategoryId.ToString() &&
(string.IsNullOrEmpty(Configuration.AttConfig.AttRegularExpression) || Regex.IsMatch(x.FileName, Configuration.AttConfig.AttRegularExpression))));
}
else if (Configuration.AttConfig.CatType == CategoryType.None)
{
return(allAttachments.FirstOrDefault(x =>
x.FileGroup == null &&
(string.IsNullOrEmpty(Configuration.AttConfig.AttRegularExpression) || Regex.IsMatch(x.FileName, Configuration.AttConfig.AttRegularExpression))));
}
else
{
return(allAttachments.First());
}
}
else
{
_log.AppendLine("Downloading attachments by SQL query");
var attId = SqlExecutionHelper.ExecSqlCommandScalar(Configuration.AttConfig.AttQuery, context);
if (attId == null)
{
throw new Exception("Sql query not returning result");
}
return(DocumentAttachmentsManager.GetAttachment(Convert.ToInt32(attId)));
}
}
private IEnumerable <TemperatureLogRecord> getTemperatureChartData(SqlCommandText whereClause)
{
var data = new List <TemperatureLogRecord>();
var selectCommand = new SqlCommandInfo("SELECT origin_datetime, temperature FROM TemperatureLog");
if (!whereClause.IsEmpty)
{
selectCommand.CommandText += " WHERE " + whereClause.CommandText;
selectCommand.AddParameters(whereClause);
}
SqlExecutionHelper.ExecuteReader(
selectCommand,
row =>
{
DateTime datetime = row.GetDateTime(row.GetOrdinal("origin_datetime"));
decimal temperature = row.GetDecimal(row.GetOrdinal("temperature"));
data.Add(new TemperatureLogRecord(datetime, temperature));
});
return(data.ToArray());
}
internal long GetUptime()
{
var selectCommand = new SqlCommandInfo("SELECT TOP 1 uptime FROM DiagnosticInfoLog ORDER BY origin_datetime DESC");
return(SqlExecutionHelper.ExecuteScalar <long>(selectCommand));
}