public void Create(ScriptDTO script)
{
try
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
string DDL = script.Script.Replace("[empresa]", Global.EmpresaAtiva);
dbCommand = db.GetSqlStringCommand(DDL);
db.ExecuteNonQuery(dbCommand, transaction);
Insert(script);
transaction.Commit();
connection.Close();
}
catch (Exception e)
{
transaction.Rollback();
connection.Close();
throw new Exception("A seguinte consulta não pode ser executada: " + "\nVersão: " + script.Versao + "\nMódulo: " + script.Modulo + "\nConsulta: " + script.Id_script, e);
}
}
}
catch
{
throw;
}
}
public bool GetScript(ScriptDTO script)
{
try
{
dbCommand = db.GetSqlStringCommand(" SELECT id_pacote, id_script FROM scriptpacote" +
" where id_pacote = @id_pacote and id_script = @id_script" +
" ORDER BY id_pacote desc");
db.AddInParameter(dbCommand, "@id_script", DbType.Int32, script.Id_script);
db.AddInParameter(dbCommand, "@id_pacote", DbType.Int32, script.Id_pacote);
using (IDataReader DR = (Transaction == null ? db.ExecuteReader(dbCommand) : db.ExecuteReader(dbCommand, Transaction)))
{
while (DR.Read())
{
return(true);
}
}
return(false);
}
catch
{
throw;
}
}
public async Task <(bool state, string msg)> DeleteScriptAsync(ScriptDTO item, bool rmDb = true)
{
if (item.LocalId > 0)
{
var info = await scriptRepository.FirstOrDefaultAsync(x => x.Id == item.LocalId);
if (info != null)
{
var url = Path.Combine(TAG, $"{info.MD5}.js");
try
{
var cachePath = Path.Combine(IOPath.CacheDirectory, url);
var cacheInfo = new FileInfo(cachePath);
if (cacheInfo.Exists)
{
cacheInfo.Delete();
}
}
catch (Exception e)
{
var msg = SR.Script_CacheDeleteError.Format(e.GetAllMessage());
logger.LogError(e, msg);
return(false, msg);
}
try
{
var savePath = Path.Combine(IOPath.AppDataDirectory, url);
var fileInfo = new FileInfo(savePath);
if (fileInfo.Exists)
{
fileInfo.Delete();
}
}
catch (Exception e)
{
var msg = SR.Script_FileDeleteError.Format(e.GetAllMessage());
logger.LogError(e, msg);
return(false, msg);
}
if (rmDb)
{
var state = (await scriptRepository.DeleteAsync(item.LocalId)) > 0;
return(state, state ? SR.Script_DeleteSuccess : SR.Script_DeleteError);
}
return(true, SR.Script_DeleteSuccess);
}
else
{
return(false, SR.Script_DeleteError);
}
}
return(false, SR.Script_NoKey);
}
/// <summary>
/// Inserta el script a partir de una prueba
/// </summary>
/// <param name="tipoPrueba"></param>
/// <returns></returns>
public ReturnMessage AddScript(ScriptDTO script)
{
ReturnMessage mensaje = new ReturnMessage();
string query = @"INSERT INTO [dbo].[SCRIPT]
([NOMBRE]
,[SCRIPT]
,[EXTENSION])
VALUES
(@Nombre
,@Script
,@Extension)
SELECT @@IDENTITY AS 'Identity'";
using (var con = ConectarDB())
{
con.Open();
try
{
using (SqlCommand command = new SqlCommand(query, con))
{
command.Parameters.Add(new SqlParameter("@Nombre", script.Nombre));
command.Parameters.Add(new SqlParameter("@Script", string.Empty));
command.Parameters.Add(new SqlParameter("@Extension", string.Empty));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
script.ID = Convert.ToInt32(reader[0]);
}
}
}
mensaje.Mensaje = "El Script se creó correctamente";
mensaje.TipoMensaje = TipoMensaje.Correcto;
mensaje.obj = script;
}
catch (Exception ex)
{
Console.WriteLine("Count not insert.");
mensaje.Mensaje = ex.Message;
mensaje.TipoMensaje = TipoMensaje.Error;
mensaje.obj = script;
}
finally
{
con.Close();
}
return(mensaje);
}
}
public ReturnMessage AddScript(ScriptDTO script)
{
try
{
//logica de negocio aquí
ScriptBehavior scriptBehavior = new ScriptBehavior();
return(scriptBehavior.AddScript(script));
}
catch (Exception ex)
{
ReturnMessage mensaje = new ReturnMessage();
mensaje.TipoMensaje = TipoMensaje.Error;
mensaje.Mensaje = ex.Message;
return(mensaje);
}
}
/// <summary>
/// 添加脚本
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public async Task <(bool state, ScriptDTO?model, string msg)> AddScriptAsync(string filePath, ScriptDTO?oldInfo = null, bool build = true, int?order = null, bool deleteFile = false, Guid?pid = null)
{
var fileInfo = new FileInfo(filePath);
if (fileInfo.Exists)
{
ScriptDTO.TryParse(filePath, out ScriptDTO? info);
return(await AddScriptAsync(fileInfo, info, oldInfo, build, order, deleteFile, pid));
}
else
{
var msg = SR.Script_NoFile.Format(filePath);// $"文件不存在:{filePath}";
logger.LogError(msg);
return(false, null, msg);
}
}
/// <summary>
/// 添加脚本
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public async Task <IApiResponse <ScriptDTO?> > AddScriptAsync(string filePath, ScriptDTO?oldInfo = null, bool build = true, int?order = null, bool deleteFile = false, Guid?pid = null, bool ignoreCache = false)
{
var fileInfo = new FileInfo(filePath);
if (fileInfo.Exists)
{
ScriptDTO.TryParse(filePath, out ScriptDTO? info);
return(await AddScriptAsync(fileInfo, info, oldInfo, build, order, deleteFile, pid, ignoreCache));
}
else
{
var msg = AppResources.Script_NoFile.Format(filePath);// $"文件不存在:{filePath}";
logger.LogError(msg);
return(ApiResponse.Fail <ScriptDTO?>(msg));
}
}
public ReturnMessage UpdateScript(ScriptDTO script)
{
ReturnMessage returnMessage = new ReturnMessage();
try
{
ScriptBehavior scriptBehavior = new ScriptBehavior();
return(scriptBehavior.UpdateScript(script));
}
catch (Exception ex)
{
returnMessage.Mensaje = ex.Message;
returnMessage.TipoMensaje = TipoMensaje.Error;
throw;
}
}
/// <summary>
/// Selecciona un script dado un tipo de prueba.
/// </summary>
/// <param name="tipoPrueba_id"></param>
/// <returns></returns>
public ScriptDTO SelectScript(int tipoPrueba_id)
{
StringBuilder query = new StringBuilder().Append(@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
SCRIPT.SCRIPT_ID,
SCRIPT.NOMBRE,
SCRIPT.SCRIPT,
SCRIPT.EXTENSION
FROM SCRIPT INNER JOIN TIPOPRUEBA ON SCRIPT.SCRIPT_ID=TIPOPRUEBA.SCRIPT_ID
AND TIPOPRUEBA.TIPOPRUEBA_ID=@tipoPrueba_id" );
using (var con = ConectarDB())
{
con.Open();
try
{
using (SqlCommand command = new SqlCommand(query.ToString(), con))
{
command.Parameters.Add(new SqlParameter("@tipoPrueba_id", tipoPrueba_id));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
ScriptDTO script = new ScriptDTO()
{
ID = Convert.ToInt32(reader[0]),
Nombre = reader[1].ToString(),
Script = reader[2].ToString(),
Extension = reader[3].ToString()
};
return(script);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Count not insert.");
}
finally
{
con.Close();
}
}
return(new ScriptDTO());
}
public void Insert(ScriptDTO script)
{
try
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
dbCommand = db.GetSqlStringCommand("Insert Into public.scriptpacote(" +
" id_pacote," +
" script," +
" executado," +
" id_script," +
" erro" +
") VALUES (" +
" @id_pacote," +
" @script," +
" @executado," +
" @id_script," +
" @erro);" +
" SELECT currval('scriptpacote_id_seq');");
db.AddInParameter(dbCommand, "@id_pacote", DbType.String, script.Id_pacote);
db.AddInParameter(dbCommand, "@script", DbType.String, script.Script);
db.AddInParameter(dbCommand, "@executado", DbType.Boolean, script.Executado);
db.AddInParameter(dbCommand, "@id_script", DbType.String, script.Id_script);
db.AddInParameter(dbCommand, "@erro", DbType.String, script.Erro);
db.ExecuteNonQuery(dbCommand, transaction);
transaction.Commit();
connection.Close();
}
catch (DbException dbex)
{
transaction.Rollback();
connection.Close();
throw dbex;
}
}
}
catch
{
throw;
}
}
public async Task <ScriptDTO> TryReadFile(ScriptDTO item)
{
var cachePath = Path.Combine(IOPath.CacheDirectory, item.CachePath);
var fileInfo = new FileInfo(cachePath);
if (fileInfo.Exists)
{
item.Content = File.ReadAllText(cachePath);
}
else
{
var infoPath = Path.Combine(IOPath.AppDataDirectory, item.FilePath);
var infoFile = new FileInfo(infoPath);
if (infoFile.Exists)
{
if (await BuildScriptAsync(item, item.IsBuild))
{
cachePath = Path.Combine(IOPath.CacheDirectory, item.CachePath);
fileInfo = new FileInfo(cachePath);
if (fileInfo.Exists)
{
item.Content = File.ReadAllText(cachePath);
}
}
else
{
toast.Show(SR.Script_ReadFileError.Format(item.Name));
}
}
else
{
var temp = await DeleteScriptAsync(item);
if (temp.state)//$"脚本:{item.Name}_文件丢失已删除"
{
toast.Show(SR.Script_NoFile.Format(item.Name));
}
else
{
toast.Show(SR.Script_NoFileDeleteError.Format(item.Name));
}
//toast.Show($"脚本:{item.Name}_文件丢失，删除失败去尝试手动删除");
}
}
return(item);
}
public void Update(ScriptDTO script)
{
try
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
dbCommand = db.GetSqlStringCommand(" UPDATE public.scriptpacote SET" +
" id_pacote = @id_pacote," +
" script = @script," +
" executado = @executado," +
" erro = @erro" +
" WHERE" +
" id = @id");
db.AddInParameter(dbCommand, "@id_pacote", DbType.String, script.Id_pacote);
db.AddInParameter(dbCommand, "@script", DbType.String, script.Script);
db.AddInParameter(dbCommand, "@executado", DbType.Boolean, script.Executado);
db.AddInParameter(dbCommand, "@erro", DbType.String, script.Erro);
db.ExecuteNonQuery(dbCommand, transaction);
transaction.Commit();
connection.Close();
}
catch (DbException dbex)
{
transaction.Rollback();
connection.Close();
throw dbex;
}
}
}
catch
{
throw;
}
}
public async Task <bool> BuildScriptAsync(ScriptDTO model, bool build = true)
{
try
{
if (model.RequiredJsArray != null)
{
var scriptContent = new StringBuilder();
if (build)
{
scriptContent.AppendLine("(function() {");
foreach (var item in model.RequiredJsArray)
{
try
{
var scriptInfo = await httpService.GetAsync <string>(item);
scriptContent.AppendLine(scriptInfo);
}
catch (Exception e)
{
var errorMsg = SR.Script_BuildDownloadError.Format(model.Name, item);
logger.LogError(e, errorMsg);
toast.Show(errorMsg);
}
}
scriptContent.AppendLine(model.Content);
scriptContent.AppendLine("})( )");
}
else
{
scriptContent.Append(model.Content);
}
var cachePath = Path.Combine(IOPath.CacheDirectory, model.CachePath);
var fileInfo = new FileInfo(cachePath);
if (!fileInfo.Directory.Exists)
{
fileInfo.Directory.Create();
}
if (fileInfo.Exists)
{
fileInfo.Delete();
}
using (var stream = fileInfo.CreateText())
{
stream.Write(scriptContent);
await stream.FlushAsync();
await stream.DisposeAsync();
//stream
}
return(true);
//var scriptRequired = new string[model.RequiredJsArray.Length];
//Parallel.ForEach(model.RequiredJsArray, async (item,index)=>
//{
// var scriptInfo = await httpService.GetAsync<string>(item);
//});
}
}
catch (Exception e)
{
var msg = SR.Script_BuildError.Format(e.GetAllMessage());
logger.LogError(e, msg);
toast.Show(msg);
}
return(false);
}
private bool ExecutarScriptsBanco(BackgroundWorker status)
{
try
{
int versaoInicial = int.Parse(versaoSistema.Substring(5, 4));
int versaoFinal = int.Parse(versaoServidor.Substring(5, 4));
versaoInicial++;
do
{
versaoServidor = "1.00." + CompletarZerosEsquerda(versaoInicial, 4);
if (Directory.Exists(DriveRede + "/sistemas/MechTech/Scripts/" + versaoServidor))
{
DirectoryInfo dirInfo = new DirectoryInfo(DriveRede + "/sistemas/MechTech/Scripts/" + versaoServidor);
tamanho = dirInfo.GetFiles().Count();
foreach (var item in dirInfo.GetFiles())
{
System.IO.StreamReader myFile = null;
myFile = new System.IO.StreamReader(item.FullName);
string texto = myFile.ReadToEnd();
consultas.Add(texto + "**" + item.Name.Replace(".sql", "") + "**" + versaoServidor);
}
}
versaoInicial++;
} while (versaoInicial.Equals(versaoFinal));
string nomeScript = string.Empty;
try
{
foreach (var consulta in consultas)
{
string[] scriptString = consulta.Split(new string[] { "**" }, StringSplitOptions.None);
//MONTANDO OS DADOS DO SCRIPT
ScriptDTO script = new ScriptDTO();
script.Script = scriptString[0];
script.Id_script = int.Parse(scriptString[1]);
string[] dadosVersaoModulo = scriptString[2].Split(new string[] { "." }, StringSplitOptions.None);
script.Modulo = dadosVersaoModulo[2];
script.Versao = dadosVersaoModulo[0];
script.Executado = true;
nomeScript = scriptString[1];
//VERIFICANDO SE O PACOTE JÁ EXISTE
int Id_pacote = new VersaoDAO().VerificaVersaoModulo(stringConexao, int.Parse(script.Versao), int.Parse(script.Modulo));
if (Id_pacote.Equals(0))
{
new VersaoDAO().Insert(int.Parse(script.Versao), int.Parse(script.Modulo), DateTime.Now.ToShortDateString(), DateTime.Now.ToString("hh:mm:ss tt"), stringConexao);
Id_pacote = new VersaoDAO().VerificaVersaoModulo(stringConexao, int.Parse(script.Versao), int.Parse(script.Modulo));
}
else
{
Id_pacote = new VersaoDAO().VerificaVersaoModulo(stringConexao, int.Parse(script.Versao), int.Parse(script.Modulo));
}
script.Id_pacote = Id_pacote;
//VERIFICAR SE O SCRIPT FOI EXECUTADO
bool scriptVerificar = new ExecutaSQLDAO(stringConexao).GetScript(script);
if (!scriptVerificar)
{
//EXECUTANDO O SCRIPT
new ExecutaSQLDAO(stringConexao).Create(script);
}
UpdateBarraExecucaoScriptSQLProgressBarControl(consultas.Count());
}
}
catch { throw new Exception("A seguinte consulta não pode ser executada: " + "\nVersão: " + versaoServidor + "\nConsulta: " + nomeScript); }
return(true);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Actualizar el script
/// </summary>
/// <param name="script"></param>
/// <returns></returns>
public ReturnMessage UpdateScript(ScriptDTO script)
{
ReturnMessage mensaje = new ReturnMessage();
StringBuilder query = new StringBuilder();
int flagUpdate = 0;
query.Append(@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE SCRIPT SET");
//valida que los campos tengan un valor para actualizar.
if (!string.IsNullOrEmpty(script.Script))
{
query.Append(" Script=@script");
flagUpdate = 1;
}
if (!string.IsNullOrEmpty(script.Extension))
{
if (flagUpdate == 1)
{
query.Append(",");
}
query.Append(" Extension=@extension");
flagUpdate = 1;
}
if (flagUpdate == 1)
{
query.Append(" WHERE SCRIPT_ID=@script_id;");
}
if (flagUpdate == 1)
{
using (var con = ConectarDB())
{
con.Open();
try
{
using (SqlCommand command = new SqlCommand(query.ToString(), con))
{
if (!string.IsNullOrEmpty(script.Script))
{
command.Parameters.Add(new SqlParameter("@script", script.Script));
}
if (!string.IsNullOrEmpty(script.Extension))
{
command.Parameters.Add(new SqlParameter("@extension", script.Extension));
}
if (flagUpdate == 1)
{
command.Parameters.Add(new SqlParameter("@script_id", script.ID));
}
command.ExecuteNonQuery();
}
mensaje.Mensaje = "El script se actualizó correctamente";
mensaje.TipoMensaje = TipoMensaje.Correcto;
mensaje.obj = script;
}
catch (Exception ex)
{
Console.WriteLine("Count not insert.");
mensaje.Mensaje = ex.Message;
mensaje.TipoMensaje = TipoMensaje.Error;
mensaje.obj = script;
}
finally
{
con.Close();
}
}
}
return(mensaje);
}
public ReturnMessage Put([FromBody] ScriptDTO script)
{
ControllerVulkano.ScriptController scriptController = new ControllerVulkano.ScriptController();
return(scriptController.UpdateScript(script));
}
public ReturnMessage Post([FromBody] ScriptDTO script)
{
Controller.ScriptController scriptController = new Controller.ScriptController();
return(scriptController.AddScript(script));
}
