public async static Task <List <int> > CheckForEqual(ControlledApp app)
{
try
{
List <int> result = new List <int>();
using (var Connection = await ConnectAsync())
{
var command = Connection.CreateCommand();
command.CommandText = string.Format("SELECT AppId FROM dbo.Applications WHERE Name='{0}' AND ProjectId={1}", app.Name, app.Parent.Id);
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
result.Add(Convert.ToInt32(reader["AppId"]));
}
reader.Close();
Disconnect(Connection);
}
return(result);
}
catch (Exception ex)
{
MainClass.OnErrorInLibrary(string.Format("Ошибка проверки уникальности проекта в БД: {0}", ex.Message));
return(new List <int>());
}
}
/// <summary>
/// Обновить данные по приложению
/// </summary>
/// <param name="app"></param>
/// <returns></returns>
public async static Task UpdateApp(ControlledApp app)
{
try
{
using (var Connection = await ConnectAsync())
{
if (Connection != null)
{
var command = Connection.CreateCommand();
command.CommandText = string.Format("UPDATE dbo.Applications SET Name = '{2}', ReleaseDirectory = '{3}', SourceDirectory = '{4}', DocumentDirectory = '{5}',Description = '{6}',MainFileName = '{7}'" +
",MainFileReleaseHash = '{8}',MainFileReleaseVersion = '{9}',MainFileReleaseDate = '{10}',Status = {11},CompatibleOSs = '{12}',CompatibleScadas = '{13}',CompatibleSZI = '{14}',IdentificationType = '{15}',Installer = '{16}',Report = '{17}'" +
",BuildingComponents = '{18}',FunctionalComponents = '{19}',DataStoringMechanism = '{20}',SUBD = '{21}',LocalData = '{22}',AuthorizationType = '{23}',Platform = '{24}',OtherSoft = '{25}',IsInReestr = '{26}', UserCategories='{27}' WHERE AppId={0} AND ProjectId={1} ",
app.Id, app.Parent.Id, app.Name, app.ReleaseDirectory, app.SourceDirectory, app.DocumentDirectory, app.Description, app.MainFileName, app.MainFileReleaseHash, app.MainFileReleaseVersion
, app.MainFileReleaseDate, (int)app.Status, app.CompatibleOSs, app.CompatibleScadas, app.CompatibleSZI, app.IdentificationType, app.Installer, app.Report
, app.BuildingComponents, app.FunctionalComponents, app.DataStoringMechanism, app.SUBD, app.LocalData, app.AuthorizationType, app.Platform, app.OtherSoft, app.IsInReestr, app.UserCategories);
await command.ExecuteNonQueryAsync();
Disconnect(Connection);
}
}
}
catch (Exception ex)
{
MainClass.OnErrorInLibrary(string.Format("Не удалось обновить данные по приложению {0} в БД: {1}", app.Name, ex.Message));
}
}
/// <summary>
/// Сохранить данные по приложениям для указанного проекта
/// </summary>
/// <param name="app"></param>
/// <returns></returns>
public async static Task AddApp(ControlledApp app)
{
try
{
using (var Connection = await ConnectAsync())
{
if (Connection != null)
{
if (!await CheckTableExistance(Connection, "Applications"))
{
await CreateAppTable();
}
var command = Connection.CreateCommand();
command.CommandText = string.Format("INSERT INTO dbo.Applications (AppId, ProjectId, Name, ReleaseDirectory, SourceDirectory, DocumentDirectory,Description,MainFileName" +
",MainFileReleaseHash,MainFileReleaseVersion,MainFileReleaseDate,Status,CompatibleOSs,CompatibleScadas,CompatibleSZI,IdentificationType,Installer,Report" +
",BuildingComponents,FunctionalComponents,DataStoringMechanism,SUBD,LocalData,AuthorizationType,Platform,OtherSoft,IsInReestr, UserCategories) VALUES (" +
"{0},{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}',{26},'{27}')",
app.Id, app.Parent.Id, app.Name, app.ReleaseDirectory, app.SourceDirectory, app.DocumentDirectory, app.Description, app.MainFileName, app.MainFileReleaseHash, app.MainFileReleaseVersion
, app.MainFileReleaseDate, (int)app.Status, app.CompatibleOSs, app.CompatibleScadas, app.CompatibleSZI, app.IdentificationType, app.Installer, app.Report
, app.BuildingComponents, app.FunctionalComponents, app.DataStoringMechanism, app.SUBD, app.LocalData, app.AuthorizationType, app.Platform, app.OtherSoft, app.IsInReestr ? 1 : 0, app.UserCategories);
await command.ExecuteNonQueryAsync();
Disconnect(Connection);
}
}
}
catch (Exception ex)
{
MainClass.OnErrorInLibrary(string.Format("Не удалось сохранить данные по приложению {0} в БД: {1}", app.Name, ex.Message));
}
}
/// <summary>
/// Удалить приложение
/// </summary>
/// <param name="app"></param>
/// <returns></returns>
public async static Task DeleteApp(ControlledApp app)
{
try
{
using (var Connection = await ConnectAsync())
{
var command = Connection.CreateCommand();
command.CommandText = string.Format("DELETE FROM dbo.Applications WHERE AppId={0} AND ProjectId={1}", app.Id, app.Parent.Id);
await command.ExecuteNonQueryAsync();
Disconnect(Connection);
}
}
catch (Exception ex)
{
MainClass.OnErrorInLibrary(string.Format("Не удалось удалить данные по приложению {0} в БД: {1}", app.Name, ex.Message));
}
}
public static void AddDataToSheet(ControlledApp app)
{
try
{
var credeintial = GetCredintials();
var service = GetService(credeintial);
String spreadsheetId = ServiceLib.Configuration.Configuration.Get("SpreadsheetAddress").ToString();
//int sheetId = 1539764994;
int sheetId = Convert.ToInt32(ServiceLib.Configuration.Configuration.Get("SpreadsheetId"));
String range = "Test!A2:N100";
SpreadsheetsResource.ValuesResource.GetRequest dataRequest =
service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange response = dataRequest.Execute();
IList <IList <Object> > exValues = response.Values;
List <Request> requests = new List <Request>();
List <CellData> values = new List <CellData>()
{
new CellData()
{
UserEnteredValue = new ExtendedValue {
NumberValue = exValues == null?1:exValues.Count + 1
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.Name
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.MainFileName
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.MainFileReleaseVersion
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = "АО \"НПО \"Спецэлектромеханика\""
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = string.IsNullOrEmpty(app.MainFileReleaseDate)?"":app.MainFileReleaseDate.Remove(app.MainFileReleaseDate.IndexOf(" "))
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.MainFileReleaseHash
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.Description
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = EnumHelper.Description(app.Parent.Category)
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.CompatibleOSs
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.CompatibleScadas
}
},
new CellData()
{
UserEnteredValue = new ExtendedValue {
StringValue = app.CompatibleSZI
}
},
};
requests.Add(new Request()
{
UpdateCells = new UpdateCellsRequest()
{
Start = new GridCoordinate()
{
SheetId = sheetId,
ColumnIndex = 0,
RowIndex = exValues == null ? 1 : exValues.Count + 1
},
Rows = new List <RowData>()
{
new RowData()
{
Values = values
}
},
Fields = "userEnteredValue"
}
});
BatchUpdateSpreadsheetRequest request = new BatchUpdateSpreadsheetRequest()
{
Requests = requests
};
service.Spreadsheets.BatchUpdate(request, spreadsheetId).Execute();
}
catch (Exception ex)
{
MainClass.OnErrorInLibrary(string.Format("Ошибка при добавлениии данных в таблицу Google: {0}", ex.Message));
}
}