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>()); }