private IList <EstoqueFilasProducao.FaseIdentificacaoManual> ObterEstoqueIdentificacaoManual()
{
var sql = @"
SELECT classificacaoEmAndamento.Total Alocado, classificacaoPendente.Total RestaTerminar FROM
(SELECT
count(0) Total
FROM BATCH B
INNER JOIN MDOC M ON B.BATCH_CODE = M.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE B.BATCH_STATUS = 'I5'
AND M.TYPEDOC_ID = 990
AND M.MDOC_STATUS != '*'
AND M.MDOC_VIRTUAL = 0
AND (M.MDOC_STARTTIME IS NULL OR M.MDOC_STARTTIME < (SYSDATE - 5 / 24 / 60))) classificacaoPendente,
(SELECT
count(0) Total
FROM BATCH B
INNER JOIN MDOC M ON B.BATCH_CODE = M.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE B.BATCH_STATUS = 'I5'
AND M.TYPEDOC_ID = 990
AND M.MDOC_STATUS != '*'
AND M.MDOC_VIRTUAL = 0
AND (M.MDOC_STARTTIME IS NOT NULL AND M.MDOC_STARTTIME BETWEEN (SYSDATE - 5 / 24 / 60) AND SYSDATE)) classificacaoEmAndamento
";
var valor = this.Session.CreateSQLQuery(sql)
.AddScalar("Alocado", NHibernateUtil.Int32)
.AddScalar("RestaTerminar", NHibernateUtil.Int32)
.SetResultTransformer(CustomResultTransformer <EstoqueFilasProducao.FaseIdentificacaoManual> .Do())
.List <EstoqueFilasProducao.FaseIdentificacaoManual>();
return(valor);
}
public override IList <AcessoDocumento> GetEntities(IStatelessSession session)
{
const string Sql = @"
select
profiles.usr_code UsrCode, access_doc.groupdoc_code Category
from
access_doc access_doc,
(select usr_code from usr where usr_type = 'G') profiles
where
access_doc.usr_code = profiles.usr_code";
var profileCategories = new List <AcessoDocumento>();
var dtos = session
.CreateSQLQuery(Sql)
.SetResultTransformer(CustomResultTransformer <AccessDocDto> .Do())
.List <AccessDocDto>();
var i = 1;
foreach (var dto in dtos)
{
profileCategories.Add(new AcessoDocumento
{
Id = i++,
TipoDocumento = this.Create <TipoDocumento>(dto.Category),
AtorId = dto.UsrCode.ToInt(),
Papel = Papel.Pefil
});
}
return(profileCategories);
}
public IList <ProcessoDevolvidoOuLiberado> ObterPorOperador(DateTime dataInicio, DateTime dataFim, int usuarioId)
{
const string Sql = @"
SELECT
P.BATCH_CODE LoteId, P.PROC_CODE ProcessoId, P.PROC_AGENCIA Agencia, P.PROC_CONTA Conta,
LP.LOGPROC_DATE DataOperacao, LP.LOGPROC_ACTION AcaoRealizada, U.USR_NAME Operador
FROM LOGPROC LP
INNER JOIN PROC P ON P.PROC_CODE = LP.PROC_CODE
INNER JOIN USR U ON U.USR_CODE = LP.USR_CODE
WHERE LOGPROC_ACTION IN ('DA','LA')
AND LP.LOGPROC_DATE BETWEEN :dataInicio AND :dataFim
AND (U.USR_CODE = :usuarioId OR :usuarioId = 0)
ORDER BY P.PROC_AGENCIA, P.PROC_CONTA, LP.PROC_CODE ASC
";
return(this.Session.CreateSQLQuery(Sql)
.AddScalar("LoteId", NHibernateUtil.Int32)
.AddScalar("ProcessoId", NHibernateUtil.Int32)
.AddScalar("Agencia", NHibernateUtil.String)
.AddScalar("Conta", NHibernateUtil.String)
.AddScalar("DataOperacao", NHibernateUtil.DateTime)
.AddScalar("AcaoRealizada", NHibernateUtil.String)
.AddScalar("Operador", NHibernateUtil.String)
.SetParameter("dataInicio", dataInicio)
.SetParameter("dataFim", dataFim)
.SetParameter("usuarioId", usuarioId)
.SetResultTransformer(CustomResultTransformer <ProcessoDevolvidoOuLiberado> .Do())
.List <ProcessoDevolvidoOuLiberado>());
}
private IEnumerable <AcompanhamentoPorHora> ObterAcompanhamentoPorHora(int pacoteProcessadoId)
{
const string Hql = @"
SELECT PP.PACOTEPROCESSADO_ARQUIVO Arquivo, PP.PACOTEPROCESSADO_RECEBIDOEM DataRecebimento, PP.PACOTEPROCESSADO_TOTALCONTAS TotalDeContas,
Ceil((B.BATCH_TFIM - PP.PACOTEPROCESSADO_RECEBIDOEM) * 24) QuantidadeDeHoras,
Count(B.BATCH_CODE) FinalizadasNaHora
FROM BATCH B
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE
WHERE PP.PACOTEPROCESSADO_RECEBIDOEM IS NOT NULL AND B.BATCH_TFIM IS NOT NULL
AND B.BATCH_STATUS = :status
AND PP.PACOTEPROCESSADO_CODE = :pacoteProcessadoCode
GROUP BY PP.PACOTEPROCESSADO_ARQUIVO, PP.PACOTEPROCESSADO_RECEBIDOEM, PP.PACOTEPROCESSADO_TOTALCONTAS, Ceil((B.BATCH_TFIM - PP.PACOTEPROCESSADO_RECEBIDOEM) * 24)
ORDER BY Arquivo, QuantidadeDeHoras
";
return(this.Session.CreateSQLQuery(Hql)
.AddScalar("Arquivo", NHibernateUtil.String)
.AddScalar("DataRecebimento", NHibernateUtil.DateTime)
.AddScalar("TotalDeContas", NHibernateUtil.Int32)
.AddScalar("QuantidadeDeHoras", NHibernateUtil.Int32)
.AddScalar("FinalizadasNaHora", NHibernateUtil.Int32)
.SetParameter("status", LoteStatus.Finalizado.Value)
.SetParameter("pacoteProcessadoCode", pacoteProcessadoId)
.SetResultTransformer(CustomResultTransformer <AcompanhamentoPorHora> .Do())
.List <AcompanhamentoPorHora>());
}
public IList <ProcessoDevolvidoOuLiberado> ObterPorAgenciaEConta(string agencia, string conta)
{
const string Sql = @"
SELECT
P.BATCH_CODE LoteId, P.PROC_CODE ProcessoId, P.PROC_AGENCIA Agencia, P.PROC_CONTA Conta,
LP.LOGPROC_DATE DataOperacao, LP.LOGPROC_ACTION AcaoRealizada, U.USR_NAME Operador
FROM LOGPROC LP
INNER JOIN PROC P ON P.PROC_CODE = LP.PROC_CODE
INNER JOIN USR U ON U.USR_CODE = LP.USR_CODE
WHERE LOGPROC_ACTION IN ('DA','LA')
AND P.PROC_AGENCIA = :agencia
AND P.PROC_CONTA = :conta
ORDER BY P.PROC_AGENCIA, P.PROC_CONTA, LP.PROC_CODE ASC
";
return(this.Session.CreateSQLQuery(Sql)
.AddScalar("LoteId", NHibernateUtil.Int32)
.AddScalar("ProcessoId", NHibernateUtil.Int32)
.AddScalar("Agencia", NHibernateUtil.String)
.AddScalar("Conta", NHibernateUtil.String)
.AddScalar("DataOperacao", NHibernateUtil.DateTime)
.AddScalar("AcaoRealizada", NHibernateUtil.String)
.AddScalar("Operador", NHibernateUtil.String)
.SetParameter("agencia", agencia)
.SetParameter("conta", conta)
.SetResultTransformer(CustomResultTransformer <ProcessoDevolvidoOuLiberado> .Do())
.List <ProcessoDevolvidoOuLiberado>());
}
public IList <CaixaParaDevolucaoViewModel> Obter(DateTime?diaDevolucao)
{
if (diaDevolucao.HasValue == false)
{
diaDevolucao = DateTime.Today;
}
/*AND PA.PACK_STATUS = 'X'*/
const string Sql = @"
SELECT PA.PACK_IDENTIFICACAO Identificacao, Count(B.BATCH_CODE) QuantidadeDeDossies, SUM(M.MDOC_QTDEPAG) QuantidadeDePaginas
FROM PACK PA
INNER JOIN BATCH B ON PA.PACK_CODE = B.PACK_CODE
LEFT JOIN MDOC M ON B.BATCH_CODE = M.BATCH_CODE
WHERE M.MDOC_STATUS != '*'
AND M.TYPEDOC_ID NOT IN (75,121)
AND PA.PACK_DTDEVOLUCAO = :DtDevolucao
GROUP BY PA.PACK_IDENTIFICACAO";
return(this.Session.CreateSQLQuery(Sql).AddScalar("Identificacao", NHibernateUtil.String)
.AddScalar("QuantidadeDeDossies", NHibernateUtil.Int32)
.AddScalar("QuantidadeDePaginas", NHibernateUtil.Int32)
.SetParameter("DtDevolucao", diaDevolucao)
.SetResultTransformer(CustomResultTransformer <CaixaParaDevolucaoViewModel> .Do())
.List <CaixaParaDevolucaoViewModel>());
}
public IList <LogDeReprovacaoCefViewModel> Obter(int lotecefId)
{
const string Sql = @"
SELECT
L.LOG_MODULE Tipo,
U.USR_NAME Usuario,
L.LOG_DATETIME Data,
L.LOG_DESC Observacao,
L.LOG_ACTION Acao
FROM
Log L
INNER JOIN USR U ON U.USR_MATRICULA = L.USR_NAME
WHERE
L.LOG_REGISTER = :lotecefId
ORDER BY Data
";
return(this.Session
.CreateSQLQuery(Sql)
.AddScalar("Tipo", NHibernateUtil.String)
.AddScalar("Usuario", NHibernateUtil.String)
.AddScalar("Data", NHibernateUtil.DateTime)
.AddScalar("Observacao", NHibernateUtil.String)
.AddScalar("Acao", NHibernateUtil.String)
.SetParameter("lotecefId", lotecefId)
.SetResultTransformer(CustomResultTransformer <LogDeReprovacaoCefViewModel> .Do())
.List <LogDeReprovacaoCefViewModel>());
}
public IList <CamposReconhecidosPorDocumentoECampo> Obter(DateTime dataInicio, DateTime dataFinal)
{
var sql = @"
SELECT
TD.TYPEDOC_DESC TipoDocumento, TC.TDCAMPOS_DESC Campo,
Sum (MDD.MDOCDADOS_OCRCOMPLEMENTOU) Acertos,
Sum (CASE MDD.MDOCDADOS_OCRCOMPLEMENTOU WHEN 0 THEN 1 ELSE 0 END) Erros,
Count(0) Total
FROM TDCAMPOS TC
INNER JOIN MDOCDADOS MDD ON TC.TDCAMPOS_CODE = MDD.TDCAMPOS_CODE
INNER JOIN MDOC MD ON MDD.MDOC_CODE = MD.MDOC_CODE
INNER JOIN BATCH B ON MD.BATCH_CODE = B.BATCH_CODE
INNER JOIN TYPEDOC TD ON MD.TYPEDOC_ID = TD.TYPEDOC_ID
WHERE TC.TDCAMPOS_RECONHECIVEL = 1
AND B.BATCH_TICR BETWEEN :dataInicio AND :dataFinal
GROUP BY TD.TYPEDOC_DESC, TC.TDCAMPOS_DESC
ORDER BY TD.TYPEDOC_DESC, TC.TDCAMPOS_DESC
";
return(this.Session.CreateSQLQuery(sql)
.SetDateTime("dataInicio", dataInicio)
.SetDateTime("dataFinal", dataFinal)
.SetResultTransformer(CustomResultTransformer <CamposReconhecidosPorDocumentoECampo> .Do())
.List <CamposReconhecidosPorDocumentoECampo>());
}
public IList <LicencaConsumidaPorTipoDocumento> Obter(string dataInicio, string dataFim)
{
var sql = @"
select
typedoc.typedoc_desc TipoDocumento,
sum(licencaconsumida.licencaconsumida_qtde) QuantidadeLicencasConsumidas
from
mdoc mdoc,
doc doc,
typedoc typedoc,
licencaconsumida licencaconsumida,
batch batch
where
mdoc.mdoc_code = doc.mdoc_code
and typedoc.typedoc_id = mdoc.typedoc_id
and licencaconsumida.doc_code = doc.doc_code
and typedoc.typedoc_reconhecivel = 1
and mdoc.batch_code = batch.batch_code
and batch.batch_dt between :dataInicio and :dataFim
group by
typedoc.typedoc_desc
";
return(this.Session
.CreateSQLQuery(sql)
.AddScalar("TipoDocumento", NHibernateUtil.String)
.AddScalar("QuantidadeLicencasConsumidas", NHibernateUtil.Int32)
.SetDateTime("dataInicio", DateTime.Parse(dataInicio))
.SetDateTime("dataFim", DateTime.Parse(dataFim))
.SetResultTransformer(CustomResultTransformer <LicencaConsumidaPorTipoDocumento> .Do())
.List <LicencaConsumidaPorTipoDocumento>());
}
/// <summary>
/// Envia documento versionado para o content
/// </summary>
/// <param name="file">Arquivo a ser enviado</param>
private void SendRevisionDocument(string file)
{
var vsdoc = new VsDoc();
var documentKey = Path.GetFileNameWithoutExtension(file).ToInt();
ImportDatabase.Using(session =>
{
var sql = "select doc_code DocCode, vsdoc_revisao VsDocRevisao from vsdoc where vsdoc_code = " +
documentKey;
vsdoc = session.CreateSQLQuery(sql)
.SetResultTransformer(CustomResultTransformer <VsDoc> .Do())
.UniqueResult <VsDoc>();
});
var ecm6DocVersionado = this.GetEcm6VersionedDocument(documentKey);
if (ecm6DocVersionado == null)
{
Log.App.ErrorFormat("Documento ecm6 #{0} não foi importado", vsdoc.DocCode);
return;
}
this.ImportEcm6VersionedDocumentFile(ecm6DocVersionado, file, vsdoc);
}
public IList <DocumentoRecontar> Obter(string statusConsulta)
{
var sql = @"
SELECT
DISTINCT MDOC_CODE Id
FROM
MDOC MD
INNER JOIN BATCH B ON B.BATCH_CODE = MD.BATCH_CODE
WHERE
MD.TYPEDOC_ID NOT IN (121, 75)
AND MDOC_VIRTUAL = 1
AND MDOC_STATUS <> '*'
AND (MDOC_RECONTADO = 0 OR MDOC_RECONTADO IS NULL)
AND (MDOC_QTDEPAG = 0 OR MDOC_QTDEPAG IS NULL)
AND MD.BATCH_CODE NOT IN (SELECT BATCH_CODE FROM BATCH_BKPCEF)
AND B.BATCH_STATUS = :statusConsulta
";
var documentosPdfConsulta = this.Session
.CreateSQLQuery(sql)
.AddScalar("Id", NHibernateUtil.Int32)
.SetParameter("statusConsulta", statusConsulta)
.SetResultTransformer(CustomResultTransformer <DocumentoRecontar> .Do())
.List <DocumentoRecontar>();
return(documentosPdfConsulta);
}
public IList <CaixaPorUniColRecArm> Obter(string dataInicio, string dataFim)
{
var sql = @"
SELECT
Sum (COLETA_QTD2) QuantidadeDeCaixas,
COLETA_ENDERECO Unidade,
to_Char(coleta_data, 'DD.MM.YYYY') DataColeta,
to_Char(COLETA_DTRECEPCAO, 'DD.MM.YYYY') DataRecepcao,
to_Char(COLETA_DTRECEPCAO, 'DD.MM.YYYY') DataArmazenamento
FROM
coleta
WHERE
coleta_data BETWEEN :dataInicio and :dataFim
GROUP BY
COLETA_ENDERECO,
to_Char (COLETA_DATA, 'DD.MM.YYYY'),
to_Char (COLETA_DTRECEPCAO, 'DD.MM.YYYY'),
to_Char (COLETA_DTRECEPCAO, 'DD.MM.YYYY')
ORDER BY
To_Date(to_Char(COLETA_DATA, 'DD.MM.YYYY'), 'DD.MM.YYYY')
";
return(this.Session
.CreateSQLQuery(sql)
.AddScalar("DataColeta", NHibernateUtil.Date)
.AddScalar("DataRecepcao", NHibernateUtil.Date)
.AddScalar("DataArmazenamento", NHibernateUtil.Date)
.AddScalar("QuantidadeDeCaixas", NHibernateUtil.Int32)
.AddScalar("Unidade", NHibernateUtil.String)
.SetDateTime("dataInicio", DateTime.Parse(dataInicio))
.SetDateTime("dataFim", DateTime.Parse(dataFim))
.SetResultTransformer(CustomResultTransformer <CaixaPorUniColRecArm> .Do())
.List <CaixaPorUniColRecArm>());
}
public IList <CertificadoDeEntregaConsulta> Obter()
{
var sql = @"
SELECT
LC.LOTECEF_CODE AS LoteCefId, LC.LOTECEF_QTBATCH AS QuantidadesDossies, LC.LOTECEF_DTFIM AS DataFim,
SUM(MD.MDOC_QTDEPAG) AS QuantidadeDePaginas,
LC.LOTECEF_DTGERACERTIFIC AS DataGeracaoCertificado,
LC.LOTECEF_DTASSINACERTIFIC AS DataAssinaturaCertificado
FROM
MDOC MD
INNER JOIN BATCH B ON B.BATCH_CODE=MD.BATCH_CODE
INNER JOIN LOTECEF LC ON LC.LOTECEF_CODE=B.LOTECEF_CODE
WHERE
LC.LOTECEF_STATUS = :status
AND LC.LOTECEF_DTGERACERTIFIC IS NOT NULL
AND MD.MDOC_STATUS <> '*'
AND MD.MDOC_VIRTUAL = 1
GROUP BY LC.LOTECEF_CODE, LC.LOTECEF_QTBATCH, LC.LOTECEF_DTFIM, LC.LOTECEF_DTGERACERTIFIC, LC.LOTECEF_DTASSINACERTIFIC";
return(this.Session
.CreateSQLQuery(sql)
.SetParameter("status", LoteCefStatus.AprovadoNaQualidade.Value)
.SetResultTransformer(CustomResultTransformer <CertificadoDeEntregaConsulta> .Do())
.List <CertificadoDeEntregaConsulta>());
}
private IList <EstoqueFilasProducao.FaseAjustesQCef> ObterEstoqueAjusteQCef()
{
var sql = @"
SELECT ajustePendente.Total RestaTerminar, ajusteEmAndamento.Total Alocado FROM
(SELECT
Count(DISTINCT p.proc_code) Total
FROM
PROC P
INNER JOIN BATCH B ON P.BATCH_CODE = B.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE
B.BATCH_STATUS = 'J5'
AND B.BATCH_RESULTQCEF = 'M'
AND (P.PROC_STARTTIME IS NULL OR P.PROC_STARTTIME < (SYSDATE - 5 / 24 / 60))) ajustePendente,
(SELECT
Count(DISTINCT p.proc_code) Total
FROM
PROC P
INNER JOIN BATCH B ON P.BATCH_CODE = B.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE
B.BATCH_STATUS = 'J5'
AND B.BATCH_RESULTQCEF = 'M'
AND (P.PROC_STARTTIME IS NOT NULL AND P.PROC_STARTTIME BETWEEN (SYSDATE - 5 / 24 / 60) AND (SYSDATE + 1 / 24 / 60))) ajusteEmAndamento
";
var valor = this.Session.CreateSQLQuery(sql)
.AddScalar("Alocado", NHibernateUtil.Int32)
.AddScalar("RestaTerminar", NHibernateUtil.Int32)
.SetResultTransformer(CustomResultTransformer <EstoqueFilasProducao.FaseAjustesQCef> .Do())
.List <EstoqueFilasProducao.FaseAjustesQCef>();
return(valor);
}
public IList <DossiesDaCaixa> Obter(int pacoteId)
{
var sql = @"
SELECT pk.pack_code Id, pk.pack_identificacao Caixa, pp.pacoteprocessado_recebidoem Data,
p.proc_identificacao Dossie, de.dossieesperado_code DossieId, de.dossieesperado_codigobarras BarcodeCef, p.proc_status Status
FROM pacoteprocessado pp
INNER JOIN batch b ON pp.pacoteprocessado_code = b.pacoteprocessado_code
INNER JOIN pack pk ON b.pack_Code = pk.pack_code
INNER JOIN proc p ON p.batch_Code = b.batch_code
INNER JOIN dossieesperado de ON de.dossieesperado_Code = b.dossieesperado_code
WHERE pk.pack_code = :pacoteId";
return(this.Session
.CreateSQLQuery(sql)
.AddScalar("Id", NHibernateUtil.Int32)
.AddScalar("Caixa", NHibernateUtil.String)
.AddScalar("Data", NHibernateUtil.Date)
.AddScalar("Dossie", NHibernateUtil.String)
.AddScalar("DossieId", NHibernateUtil.Int32)
.AddScalar("BarcodeCef", NHibernateUtil.String)
.AddScalar("Status", NHibernateUtil.String)
.SetParameter("pacoteId", pacoteId)
.SetResultTransformer(CustomResultTransformer <DossiesDaCaixa> .Do())
.List <DossiesDaCaixa>());
}
private IList <EstoqueFilasProducao.FilaTriagemOcr> ObterEstoqueTriagemOcr()
{
var sql = @"
SELECT triagemPendente.Total RestaTerminar, triagemEmAndamento.Total Alocado FROM
(
SELECT count(1) Total
FROM PROC P
INNER JOIN BATCH B ON P.BATCH_CODE = B.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE P.PROC_STATUS= 'T5'
AND (P.PROC_STARTTIME IS NULL OR P.PROC_STARTTIME < (SYSDATE - 5 / 24 / 60))) triagemPendente,
(SELECT count(1) Total
FROM PROC P
INNER JOIN BATCH B ON P.BATCH_CODE = B.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE P.PROC_STATUS= 'T5'
AND (P.PROC_STARTTIME IS NOT NULL AND P.PROC_STARTTIME BETWEEN (SYSDATE - 5 / 24 / 60) AND (SYSDATE + 1 / 24 / 60))) triagemEmAndamento
";
var valor = this.Session.CreateSQLQuery(sql)
.AddScalar("Alocado", NHibernateUtil.Int32)
.AddScalar("RestaTerminar", NHibernateUtil.Int32)
.SetResultTransformer(CustomResultTransformer <EstoqueFilasProducao.FilaTriagemOcr> .Do())
.List <EstoqueFilasProducao.FilaTriagemOcr>();
return(valor);
}
private IList <EstoqueFilasProducao.FaseQualidadeM2Sys> ObterEstoqueQualidadeM2()
{
var sql = @"
SELECT qualidadeM2EmAndamento.Total Alocado, qualidadeM2Pendente.Total RestaTerminar FROM
(SELECT
Count(0) Total
FROM
PROC P
INNER JOIN BATCH B ON B.BATCH_CODE = P.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE
P.PROC_STATUS = 'M5'
AND B.BATCH_STATUS = 'M5'
AND (P.PROC_STARTTIME IS NULL OR P.PROC_STARTTIME < (SYSDATE - 10 / 24 / 60))) qualidadeM2Pendente,
(SELECT
Count(0) Total
FROM
PROC P
INNER JOIN BATCH B ON B.BATCH_CODE = P.BATCH_CODE
INNER JOIN PACOTEPROCESSADO PP ON B.PACOTEPROCESSADO_CODE = PP.PACOTEPROCESSADO_CODE AND PP.PACOTEPROCESSADO_ATIVADO = 'S'
WHERE
P.PROC_STATUS = 'M5'
AND B.BATCH_STATUS = 'M5'
AND (P.PROC_STARTTIME IS NOT NULL AND P.PROC_STARTTIME BETWEEN (SYSDATE - 10 / 24 / 60) AND SYSDATE)) qualidadeM2EmAndamento
";
var valor = this.Session.CreateSQLQuery(sql)
.AddScalar("Alocado", NHibernateUtil.Int32)
.AddScalar("RestaTerminar", NHibernateUtil.Int32)
.SetResultTransformer(CustomResultTransformer <EstoqueFilasProducao.FaseQualidadeM2Sys> .Do())
.List <EstoqueFilasProducao.FaseQualidadeM2Sys>();
return(valor);
}
public IList <DocumentosPdfConsulta> Obter(string statusConsulta)
{
var sql = @"
SELECT DISTINCT d.doc_code PaginaId, d.mdoc_code DocumentoId, d.batch_code LoteId, d.DOC_DTCENTERID DataCenterId
FROM
doc d
INNER JOIN mdoc m ON m.mdoc_code = d.mdoc_code
INNER JOIN batch b ON b.batch_code = d.batch_code
WHERE
m.mdoc_virtual = 1
AND d.doc_filetype = 'PDF'
AND m.mdoc_status <> '*'
AND b.batch_status = :statusConsulta
";
var documentosPdfConsulta = this.Session
.CreateSQLQuery(sql)
.AddScalar("PaginaId", NHibernateUtil.Int32)
.AddScalar("DocumentoId", NHibernateUtil.Int32)
.AddScalar("LoteId", NHibernateUtil.Int32)
.AddScalar("DataCenterId", NHibernateUtil.Int32)
.SetParameter("statusConsulta", statusConsulta)
.SetResultTransformer(CustomResultTransformer <DocumentosPdfConsulta> .Do())
.List <DocumentosPdfConsulta>();
return(documentosPdfConsulta);
}
public IList <RegraVioladaReprovacaoCefViewModel> Obter(int lotecefId)
{
const string Sql = @"
SELECT
P.PROC_IDENTIFICACAO Dossie, M.MDOC_ORDEM Ordem,
T.TYPEDOC_DESC TipoDocumento,
R.REGRAPROC_OBS ErroApontado, R.REGRAPROC_PAGINA Pagina,
B.BATCH_QUALICEF Amostra
FROM BATCH B JOIN PROC P ON B.BATCH_CODE = P.BATCH_CODE
JOIN REGRAPROC R ON R.PROC_CODE = P.PROC_CODE AND R.REGRA_CODE = 2
JOIN MDOC M ON M.MDOC_CODE = R.MDOC_CODE
JOIN TYPEDOC T ON T.TYPEDOC_ID = M.TYPEDOC_ID
WHERE
B.LOTECEF_CODE = :lotecefId
ORDER BY P.BATCH_CODE, M.MDOC_ORDEM, M.MDOC_CODE
";
return(this.Session
.CreateSQLQuery(Sql)
.AddScalar("Dossie", NHibernateUtil.String)
.AddScalar("Ordem", NHibernateUtil.String)
.AddScalar("TipoDocumento", NHibernateUtil.String)
.AddScalar("ErroApontado", NHibernateUtil.String)
.AddScalar("Pagina", NHibernateUtil.String)
.AddScalar("Amostra", NHibernateUtil.Int32)
.SetParameter("lotecefId", lotecefId)
.SetResultTransformer(CustomResultTransformer <RegraVioladaReprovacaoCefViewModel> .Do())
.List <RegraVioladaReprovacaoCefViewModel>());
}
public IList <ObterColetaParaConferencia> Pesquisar(PesquisaPacoteViewModel filtros)
{
var statusPacote = filtros.PacoteStatus;
var sql = @"
SELECT P.PACK_CODE PacoteId, P.PACK_IDENTIFICACAO Identificacao, C.COLETA_CODE ColetaId,
COUNT(DE.DOSSIEESPERADO_CODE) TotalDossies, C.COLETA_DESCRICAO Descricao, C.COLETA_ENDERECO Endereco,
C.COLETA_DATA DataColeta, C.COLETA_DTRECEPCAO DataRecepcao
FROM PACK P INNER JOIN COLETA C ON P.COLETA_CODE = C.COLETA_CODE
LEFT JOIN DOSSIEESPERADO DE ON P.PACK_CODE = DE.PACOTE_CODE
WHERE P.PACK_STATUS = :statusPacote
GROUP BY P.PACK_CODE, P.PACK_IDENTIFICACAO, C.COLETA_CODE, C.COLETA_DESCRICAO, C.COLETA_ENDERECO,
C.COLETA_DATA, C.COLETA_DTRECEPCAO";
switch (filtros.ColunaDeOrdenacao)
{
case "caixa":
sql = filtros.TipoDeOrdenacao == "A" ? sql + " ORDER BY P.PACK_IDENTIFICACAO ASC" : sql + " ORDER BY P.PACK_IDENTIFICACAO DESC";
break;
case "coleta":
sql = filtros.TipoDeOrdenacao == "A" ? sql + " ORDER BY C.COLETA_CODE ASC" : sql + " ORDER BY C.COLETA_CODE DESC";
break;
case "descricao":
sql = filtros.TipoDeOrdenacao == "A" ? sql + " ORDER BY C.COLETA_DESCRICAO ASC" : sql + " ORDER BY C.COLETA_DESCRICAO DESC";
break;
case "endereco":
sql = filtros.TipoDeOrdenacao == "A" ? sql + " ORDER BY C.COLETA_ENDERECO ASC" : sql + " ORDER BY C.COLETA_ENDERECO DESC";
break;
case "dataColeta":
sql = filtros.TipoDeOrdenacao == "A" ? sql + " ORDER BY C.COLETA_DATA ASC" : sql + " ORDER BY C.COLETA_DATA DESC";
break;
case "dataRecepcao":
sql = filtros.TipoDeOrdenacao == "A" ? sql + " ORDER BY C.COLETA_DTRECEPCAO ASC" : sql + " ORDER BY C.COLETA_DTRECEPCAO DESC";
break;
default:
sql = sql + " ORDER BY P.PACK_IDENTIFICACAO";
break;
}
return(this.Session
.CreateSQLQuery(sql)
.AddScalar("PacoteId", NHibernateUtil.Int32)
.AddScalar("Identificacao", NHibernateUtil.String)
.AddScalar("ColetaId", NHibernateUtil.Int32)
.AddScalar("TotalDossies", NHibernateUtil.Int32)
.AddScalar("Descricao", NHibernateUtil.String)
.AddScalar("Endereco", NHibernateUtil.String)
.AddScalar("DataColeta", NHibernateUtil.DateTime)
.AddScalar("DataRecepcao", NHibernateUtil.DateTime)
.SetParameter("statusPacote", statusPacote)
.SetResultTransformer(CustomResultTransformer <ObterColetaParaConferencia> .Do())
.List <ObterColetaParaConferencia>());
}
public DataInstalacao Obter()
{
var sql = @"
SELECT Max(INSTALADOR_DATA) Data FROM instalador WHERE ROWNUM = 1";
return(this.Session.CreateSQLQuery(sql)
.SetResultTransformer(CustomResultTransformer <DataInstalacao> .Do())
.UniqueResult <DataInstalacao>());
}
public VersaoMigrate Obter()
{
var sql = @"
SELECT To_Char(Max(VERSION)) Versao FROM SCHEMAINFO WHERE ROWNUM = 1";
return(this.Session.CreateSQLQuery(sql)
.SetResultTransformer(CustomResultTransformer <VersaoMigrate> .Do())
.UniqueResult <VersaoMigrate>());
}
public IList <DocumentoParaPalavraChave> Obter(string sqlNativo)
{
//// exemplo de query esperada (listar sempre os PDFs):
//// SELECT m.mdoc_code DocumentoId, m.mdoc_pai DocumentoPaiId, m.typedoc_id TipoDocumentoId FROM mdoc m JOIN batch b ON b.batch_code = m.batch_code where (...)
return(this.Session.CreateSQLQuery(sqlNativo)
.AddScalar("DocumentoId", NHibernateUtil.Int32)
.AddScalar("DocumentoPaiId", NHibernateUtil.Int32)
.AddScalar("TipoDocumentoId", NHibernateUtil.Int32)
.SetResultTransformer(CustomResultTransformer <DocumentoParaPalavraChave> .Do())
.List <DocumentoParaPalavraChave>());
}
public IList <LogDeAjusteViewModel> Obter(int loteId)
{
const string Sql = @"
SELECT
LOG_TIPO Tipo
,USR_CODE UsuarioId
,LOG_DATE Data
,LOG_OBS Observacao
,LOG_ACTION Acao
FROM (
SELECT 'LP' LOG_TIPO,
L.USR_CODE,
L.LOGPROC_DATE LOG_DATE,
CASE L.LOGPROC_ACTION
WHEN 'FA' THEN 'Aprovado no Ajuste'
WHEN 'SR' THEN 'Solicitado Recaptura no Ajuste: ' || L.LOGPROC_OBS
ELSE L.LOGPROC_OBS
END LOG_OBS,
L.LOGPROC_ACTION LOG_ACTION
FROM LOGPROC L
INNER JOIN PROC P ON L.PROC_CODE = P.PROC_CODE
WHERE L.LOGPROC_ACTION IN ('FA', 'SM', 'SR', 'QM', 'QC')
AND P.BATCH_CODE = :loteId
UNION
SELECT 'LB' LOG_TIPO,
L.USR_CODE,
L.LOGBATCH_DATE LOG_DATE,
CASE L.LOGBATCH_ACTION
WHEN 'SS' THEN L.LOGBATCH_OBS
ELSE LOGBATCH_OBS
END LOG_OBS,
L.LOGBATCH_ACTION LOG_ACTION
FROM LOGBATCH L
WHERE L.LOGBATCH_ACTION IN ('SS')
AND L.BATCH_CODE = :loteId
)
ORDER BY Data
";
return(this.Session
.CreateSQLQuery(Sql)
.AddScalar("Tipo", NHibernateUtil.String)
.AddScalar("UsuarioId", NHibernateUtil.Int32)
.AddScalar("Data", NHibernateUtil.DateTime)
.AddScalar("Observacao", NHibernateUtil.String)
.AddScalar("Acao", NHibernateUtil.String)
.SetParameter("loteId", loteId)
.SetResultTransformer(CustomResultTransformer <LogDeAjusteViewModel> .Do())
.List <LogDeAjusteViewModel>());
}
private Ecm6DocVersionado GetEcm6VersionedDocument(int ecm6DocVersion)
{
Ecm6DocVersionado ecm6Docs;
ecm6Docs = NHibernateSession
.Current
.CreateSQLQuery("select id Id, ecm6_id Ecm6Id, ecm8_id Ecm8Id, import_status ImportStatus from ecm6_docversionado where ecm6_id = :ecm6Id")
.SetInt32("ecm6Id", ecm6DocVersion)
.SetResultTransformer(CustomResultTransformer <Ecm6DocVersionado> .Do())
.UniqueResult <Ecm6DocVersionado>();
return(ecm6Docs);
}
private IList <LoteParaExpurgoView> Obter(string ambiente, int pacoteId)
{
const string Hql = @"
SELECT B.BATCH_CODE LoteId
FROM BATCH{0} B
WHERE B.PACOTEPROCESSADO_CODE = :pacoteId";
return(this.Session.CreateSQLQuery(string.Format(Hql, ambiente))
.AddScalar("LoteId", NHibernateUtil.Int32)
.SetParameter("pacoteId", pacoteId)
.SetResultTransformer(CustomResultTransformer <LoteParaExpurgoView> .Do())
.List <LoteParaExpurgoView>());
}
public IList <HistoricoBasicoViewModel> Obter(Lote lote)
{
var sql = @"SELECT t1.*, Nvl(u.usr_name,'sistema') Usuario FROM
(
SELECT 'Caixa (Pack)' NivelDeLog, pack_code Codigo, null CodigoDoUsuario, PACK_DTINIPREPARO Data, NULL AcaoLogDocumento, 'Inicio Preparo da Caixa ['|| pack_identificacao ||'] - Matricula dos envolvidos: '
|| u1.usr_matricula || ',' || u2.usr_matricula || ',' || u3.usr_matricula || ',' || u4.usr_matricula LogDocumentoObservacao
FROM pack p
left JOIN usr u1 ON u1.usr_code = p.USR_PREPARO
left JOIN usr u2 ON u2.usr_code = USR_PREPARO2
left JOIN usr u3 ON u3.usr_code = USR_PREPARO3
left JOIN usr u4 ON u4.usr_code = USR_PREPARO4
WHERE PACK_code = (SELECT pack_code FROM batch WHERE batch_code = {0})
UNION
SELECT 'Caixa (Pack)' NivelDeLog, pack_code Codigo, null CodigoDoUsuario, PACK_DTPREPARO Data, NULL AcaoLogDocumento, 'Fim Preparo da Caixa ['|| pack_identificacao ||'] - Matricula dos envolvidos: '
|| u1.usr_matricula || ',' || u2.usr_matricula || ',' || u3.usr_matricula || ',' || u4.usr_matricula LogDocumentoObservacao
FROM pack p
left JOIN usr u1 ON u1.usr_code = p.USR_PREPARO
left JOIN usr u2 ON u2.usr_code = USR_PREPARO2
left JOIN usr u3 ON u3.usr_code = USR_PREPARO3
left JOIN usr u4 ON u4.usr_code = USR_PREPARO4
WHERE PACK_code = (SELECT pack_code FROM batch WHERE batch_code = {0})
UNION
SELECT 'Pagina (doc)' NivelDeLog, doc_code Codigo, usr_code CodigoDoUsuario, LOGDOC_DATETIME Data, LOGDOC_ACTION AcaoLogDocumento, LOGDOC_OBS LogDocumentoObservacao FROM logdoc WHERE doc_code IN (SELECT doc_code FROM doc WHERE batch_code = {0})
UNION
SELECT 'Item Docum. (mdoc)' NivelDeLog, mdoc_code Codigo, usr_code CodigoDoUsuario, LOGMDOC_DATE Data,LOGMDOC_ACTION AcaoLogDocumento, LOGMDOC_OBS LogDocumentoObservacao FROM logmdoc WHERE mdoc_code IN (SELECT mdoc_code FROM mdoc WHERE batch_code = {0})
UNION
SELECT 'Dossie (batch)' NivelDeLog, batch_code Codigo, usr_code CodigoDoUsuario, LOGBATCH_DATE Data,LOGBATCH_ACTION AcaoLogDocumento, LOGBATCH_OBS LogDocumentoObservacao FROM logbatch WHERE batch_code = {0}
union
SELECT 'Dossie (proc)' NivelDeLog, PROC_CODE Codigo, usr_code CodigoDoUsuario, logproc_date Data, LOGPROC_ACTION AcaoLogDocumento, LOGPROC_OBS LogDocumentoObservacao FROM logproc WHERE proc_code IN (SELECT proc_code FROM proc WHERE batch_code = {0})
UNION
SELECT 'QualiM2sys ' NivelDeLog, mdoc_code Codigo, usr_code CodigoDoUsuario, regraproc_dtstart Data, NULL AcaoLogDocumento, regraproc_obs LogDocumentoObservacao FROM regraproc WHERE proc_code IN (SELECT proc_code FROM proc WHERE batch_code = {0}) AND regra_code = 1
union
SELECT 'QualiCEF ' NivelDeLog, mdoc_code Codigo, usr_code CodigoDoUsuario, regraproc_dtstart Data, NULL AcaoLogDocumento, regraproc_obs LogDocumentoObservacao FROM regraproc WHERE proc_code IN (SELECT proc_code FROM proc WHERE batch_code = {0}) AND regra_code = 2
UNION
SELECT 'Planilha CEF' NivelDeLog, dossieesperado_code Codigo, usr_code CodigoDoUsuario, LOGDOSSIEESPERADO_DATETIME Data, LOGDOSSIEESPERADO_ACTION AcaoLogDocumento, LOGDOSSIEESPERADO_DESC LogDocumentoObservacao FROM logdossieesperado WHERE DOSSIEESPERADO_CODE IN (SELECT DOSSIEESPERADO_CODE FROM batch WHERE batch_code = {0})
) t1
left JOIN usr u ON u.usr_code = t1.CodigoDoUsuario
ORDER BY t1.data";
return(this.Session.CreateSQLQuery(string.Format(sql, lote.Id))
.AddScalar("NivelDeLog", NHibernateUtil.AnsiString)
.AddScalar("Codigo", NHibernateUtil.Int32)
.AddScalar("CodigoDoUsuario", NHibernateUtil.Int32)
.AddScalar("Data", NHibernateUtil.DateTime)
.AddScalar("AcaoLogDocumento", NHibernateUtil.AnsiString)
.AddScalar("LogDocumentoObservacao", NHibernateUtil.AnsiString)
.AddScalar("Usuario", NHibernateUtil.AnsiString)
.SetResultTransformer(CustomResultTransformer <HistoricoBasicoViewModel> .Do())
.List <HistoricoBasicoViewModel>());
}
private IList <PacoteParaExpurgoView> Obter(int quantidadeDeDias, string origem)
{
const string Hql = @"
SELECT P.PACOTEPROCESSADO_CODE PacoteId
FROM PACOTEPROCESSADO{0} P
WHERE P.PACOTEPROCESSADO_RECEBIDOEM < SYSDATE - :quantidadeDeDias
";
return(this.Session.CreateSQLQuery(string.Format(Hql, origem))
.AddScalar("PacoteId", NHibernateUtil.Int32)
.SetParameter("quantidadeDeDias", quantidadeDeDias)
.SetResultTransformer(CustomResultTransformer <PacoteParaExpurgoView> .Do())
.List <PacoteParaExpurgoView>());
}
public override List <Documento> GetEntities(IStatelessSession session, int startRow, int endRow)
{
const string Sql = @"
select
ROW_NUMBER() over (order by predoc_code) as RowNum,
predoc_code Code,
groupdoc_code GroupDocCode,
predoc_usr Usr,
predoc_date Date
from
predoc
where
predoc_processed = 'N'" ;
var documentos = new List <Documento>();
var dtos = session
.CreateSQLQuery(this.GetPaginatedSql(Sql, startRow, endRow))
.SetResultTransformer(CustomResultTransformer <PreIndexedFileDto> .Do())
.List <PreIndexedFileDto>();
foreach (var preIndexedFileDto in dtos)
{
Log.App.DebugFormat("Importando PreIndexedFileDto #{0}", preIndexedFileDto.Code);
var documento = new Documento();
documento.Id = preIndexedFileDto.Code.ToInt();
documento.DataCriacao = preIndexedFileDto.Date;
if (string.IsNullOrEmpty(preIndexedFileDto.Usr) == false)
{
documento.Usuario = this.Create <Usuario>(preIndexedFileDto.Usr);
}
else
{
documento.Usuario = this.Create <Usuario>("1");
}
documento.TipoDocumento = this.Create <TipoDocumento>(preIndexedFileDto.GroupDocCode);
documento.SearchStatus = SearchStatus.DontIndex;
documento.EhPreIndexacao = true;
documento.Assunto = "(sem assunto)";
documentos.Add(documento);
}
return(documentos);
}
public IList <TotaisAguardandoQualidadeCef> Obter(int loteCefId = 0)
{
var filtroPacote = loteCefId > 0 ?
" AND L.LOTECEF_CODE=" + loteCefId :
string.Empty;
const string Sql = @"
SELECT
L.LOTECEF_CODE LoteCefId,
L.LOTECEF_STATUS LoteCefStatus,
L.LOTECEF_DTFIM LoteCefData,
L.LOTECEF_DTAPROV LoteCefAprovacao,
L.LOTECEF_DTASSINACERTIFIC LoteCefAssinatura,
B.BATCH_QUALICEF TipoDeAmostra,
Count(B.BATCH_CODE) TotalPacote,
Sum(CASE WHEN B.BATCH_RESULTQCEF = 'M' THEN 1 ELSE 0 END) QtdMarcados,
Sum(CASE WHEN B.BATCH_RESULTQCEF = 'A' THEN 1 ELSE 0 END) QtdAprovados,
Sum(CASE WHEN B.BATCH_QUALICEF >= 1 AND B.BATCH_STATUS NOT IN ('QA', 'Q5', 'H0') THEN 1 ELSE 0 END) QtdEmReprocessamento,
Sum(CASE WHEN B.BATCH_QUALICEF > 0 THEN 1 ELSE 0 END) QtdSelecionados
FROM
LOTECEF L
INNER JOIN BATCH B ON L.LOTECEF_CODE = B.LOTECEF_CODE
WHERE
B.BATCH_STATUS != '*'
AND L.LOTECEF_STATUS IN ('FE', 'RP', 'AP', 'RN')
AND L.LOTECEF_VISIVEL = 1
{0}
GROUP BY
L.LOTECEF_CODE, L.LOTECEF_STATUS, L.LOTECEF_DTFIM, L.LOTECEF_DTAPROV, L.LOTECEF_DTASSINACERTIFIC, B.BATCH_QUALICEF
ORDER BY
L.LOTECEF_DTFIM desc
";
return(this.Session
.CreateSQLQuery(string.Format(Sql, filtroPacote))
.AddScalar("LoteCefId", NHibernateUtil.Int32)
.AddScalar("LoteCefStatus", NHibernateUtil.AnsiString)
.AddScalar("LoteCefData", NHibernateUtil.Date)
.AddScalar("LoteCefAprovacao", NHibernateUtil.Date)
.AddScalar("LoteCefAssinatura", NHibernateUtil.Date)
.AddScalar("TipoDeAmostra", NHibernateUtil.Int32)
.AddScalar("TotalPacote", NHibernateUtil.Int32)
.AddScalar("QtdMarcados", NHibernateUtil.Int32)
.AddScalar("QtdAprovados", NHibernateUtil.Int32)
.AddScalar("QtdEmReprocessamento", NHibernateUtil.Int32)
.AddScalar("QtdSelecionados", NHibernateUtil.Int32)
.SetResultTransformer(CustomResultTransformer <TotaisAguardandoQualidadeCef> .Do())
.List <TotaisAguardandoQualidadeCef>());
}
