Esempio n. 1
0
        public static XPDataView ExecuteSqlQuery(this UnitOfWork uow, string sSqlString)
        {
            var dv     = new XPDataView();
            var result = uow.ExecuteQueryWithMetadata(sSqlString);

            if (result.ResultSet[0].Rows.Length > 0)
            {
                foreach (var r in result.ResultSet[0].Rows)
                {
                    dv.AddProperty((string)r.Values[0],
                                   DBColumn.GetType((DBColumnType)Enum.Parse(typeof(DBColumnType), (string)r.Values[2])));
                }
                dv.LoadData(new SelectedData(result.ResultSet[1]));
            }
            return(dv);
        }
Esempio n. 2
0
        private void Execute(string sql, bool remoteCommand = false)
        {
            try
            {
                if (string.IsNullOrEmpty(sql))
                {
                    return;
                }
                if (!remoteCommand)
                {
                    EventBusFactory.Default.Publish(new RemoteExecuteCommand(Id, sql));
                }
                var session = new Session(_databaseServer.GetDataLayer());
                if (sql.StartsWith("select", StringComparison.OrdinalIgnoreCase))
                {
                    gridView.Columns.Clear();
                    XPDataView   dv   = new XPDataView();
                    SelectedData data = session.ExecuteQueryWithMetadata(sql);
                    foreach (var row in data.ResultSet[0].Rows)
                    {
                        dv.AddProperty((string)row.Values[0],
                                       DBColumn.GetType((DBColumnType)Enum.Parse(typeof(DBColumnType), (string)row.Values[2])));
                    }
                    dv.LoadData(new SelectedData(data.ResultSet[1])); //如果包含多个结果将丢弃
                    gridControl.DataSource = dv;
                    PrintLog($"数据行数={dv.Count}");
                }

                else
                {
                    var count = session.ExecuteNonQuery(sql);
                    PrintLog($"受影响行数={count}");
                }
                session.Disconnect();
                _tabPage.Appearance.HeaderActive.BackColor = Color.Empty;
            }
            catch (Exception ex)
            {
                PrintLog($"{ex.Message}{Environment.NewLine}{ex.StackTrace}");
                _tabPage.Appearance.HeaderActive.BackColor = Color.Brown;
            }
        }
        protected XPDataView AtualizarGrid()
        {
            Control.UsuariosControle ctrlUsu = new Control.UsuariosControle();
            string dataCadINI = "";
            string dataCadFIM = "";
            string envolvido = "";
            string procNum = "";
            string undorca = "";
            string situacao = "";
            string usuarioEnvio = "";
            string assunto = "";
            string usuarioDefault = ctrlUsu.GetUsuarioByLogin(HttpContext.Current.Session["pUId"].ToString()).UsuarioID.ToString();

            Control.PerfilCtrl perfil = new Control.PerfilCtrl();

            bool perfilConsultaGeral = perfil.getPermissaoConsultaProcessos(usuarioDefault);

            perfil = null;

            string sqlTable = "select " +
                                "id," +
                                "NUMERO," +
                                "datahoracadastro," +
                                "responsavel," +
                                "nome," +
                                "ENVID," +
                                "descricao," +
                                "UNDABREV ," +
                                "unidfuncnome," +
                                "undunificado," +
                                "usuarioenvio," +
                                "Cast(valor as numeric(15,2)) valor," +
                                "cast(VAL_LIQUIDADO as numeric(15,2)) VAL_LIQUIDADO," +
                                "CENNOME," +
                                "SITUACAO_ATUAL " +
                                "from CONSULTA_PROCESSOS ";

            if (dtCadastroIni.Value != null)
            {
                dataCadINI = "'" + DateTime.Parse(dtCadastroIni.Value.ToString()).ToShortDateString().Replace("/", ".") + "' ";
            }
            else
            {
                dataCadINI = "DATAHORACADASTRO";
            }

            if (dtCadastroFim.Value != null)
            {
                dataCadFIM = "'" + DateTime.Parse(dtCadastroFim.Value.ToString()).ToShortDateString().Replace("/", ".") + "' ";
            }
            else
            {
                dataCadFIM = "DATAHORACADASTRO";
            }

            if (cbbEnvolvido.Value != null)
            {
                envolvido = cbbEnvolvido.Value.ToString();
            }
            else
            {
                envolvido = "ENVID ";
            }

            if (txtNumProcesso.Value != null)
            {
                procNum = " like '" + txtNumProcesso.Value.ToString() + "%'";
            }
            else
            {
                procNum = " = NUMERO ";
            }

            if (cbbUndOrc.Value != null)
            {
                undorca = "= '" + cbbUndOrc.Value.ToString() + "' ";
            }
            else
            {

                if (!perfilConsultaGeral)
                {
                    string idUndFunc = Control.UndFuncionalCtrl.getUndFuncByUsuID(usuarioDefault).ToString();
                    string undOrcCad = Control.UnidadeOrcamentariaCtrl.GetUnidadeOrcamentriaByUnidadeFuncional(idUndFunc).undUnificado;

                    undorca = "in (    SELECT UO.undunificado " +
                        "    FROM unidadeorcamentaria UO " +
                        "    JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                        "    WHERE " +
                        "    uog.uogunidadegestora = " +
                        "    (SELECT UOG.uogunidadegestora " +
                        "        FROM unidadeorcamentaria UO " +
                        "        JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                        "        where uo.undunificado = '" + undOrcCad + "' " +
                        "    ) ) ";
                }
                else {
                    undorca = "= UNDUNIFICADO ";
                }
            }

            if (cbbAssunto.Value != null)
            {
                assunto =  cbbAssunto.Value.ToString() ;
            }
            else
            {
                assunto = "ASSID ";
            }

            string idSituacao = string.Empty;

            if (cbbSituacao.Value != null)
            {
                situacao = cbbSituacao.Value.ToString();
            }

            //usuarioEnvio = "USUARIOENVIO";

            sqlTable += " WHERE ";
            sqlTable += " (";
            sqlTable += " (DATAHORACADASTRO >= " + dataCadINI + ") ";
            sqlTable += " AND ";
            sqlTable += " (DATAHORACADASTRO <= " + dataCadFIM + ")";
            sqlTable += " ) ";
            sqlTable += " AND ";
            sqlTable += " ENVID = " + envolvido;
            sqlTable += " AND ";
            sqlTable += " NUMERO " + procNum;
            sqlTable += " AND ";
            //sqlTable += " UNDUNIFICADO = " + undorca;
            sqlTable += " UNDUNIFICADO " + undorca;
            if(cbbSituacao.Value != null){
                //sqlTable += "and (" + situacao + " in (Select idSituacaoProjeto from webtramite where idProcesso = proc.ID)) ";
                sqlTable += "and SITID in (Select sit.id from webgruposituacoes gsit join websituacaoprocesso sit on sit.id_gruposituacao = gsit.id where gsit.id = " + cbbSituacao.Value + ")";
            }
            //sqlTable += " AND ";
            //sqlTable += "USUARIOENVIO = " + usuarioEnvio;
            sqlTable += " AND ASSID = "+ assunto;
            sqlTable += "AND ((EXCLUIR IS NULL AND EXERCICIO <> (SELECT FIRST 1 ENTEXERCICIO FROM ENTIDADE)) ";
            sqlTable += "OR ";
            sqlTable += "(EXERCICIO = (SELECT FIRST 1 ENTEXERCICIO FROM ENTIDADE)))";

            DevExpress.Xpo.Session xpoSession = new DevExpress.Xpo.Session();
            XpoDataSource1.Session = xpoSession;

            //string connTeste = AcessoDados.AcessoDados.GetConnectionStringXPO();
            XpoDataSource1.Session.Connection = Persist.GetConn.getConn();

            SelectedData resultSet = xpoSession.ExecuteQuery(sqlTable);
            XPDataView dv = new XPDataView();

            dv.AddProperty("ID", typeof(int));
            dv.AddProperty("NUMERO", typeof(string));
            dv.AddProperty("DATAHORACADASTRO", typeof(DateTime));
            dv.AddProperty("RESPONSAVEL", typeof(string));
            dv.AddProperty("NOME", typeof(string));
            dv.AddProperty("ENVID", typeof(int));
            dv.AddProperty("DESCRICAO", typeof(string));
            dv.AddProperty("UNDABREV", typeof(string));
            dv.AddProperty("UNIDFUNCNOME", typeof(string));
            dv.AddProperty("UNDUNIFICADO", typeof(int));
            dv.AddProperty("USUARIOENVIO", typeof(int));
            dv.AddProperty("VALOR", typeof(decimal));
            dv.AddProperty("VAL_LIQUIDADO", typeof(decimal));
            dv.AddProperty("CENNOME", typeof(string));
            dv.AddProperty("SITUACAO_ATUAL", typeof(string));

            dv.LoadData(resultSet);

            ASPxGridView1.DataSource = dv;
            Session["DTable"] = dv;
            ASPxGridView1.DataBind();

            return dv;
        }
Esempio n. 4
0
        public void atualizaGridEmpenhos(string tipo)
        {
            string unidOrc = "";
            string empenhoNum = "";
            string dataIni = "";
            string dataFim = "";
            string credor = "";
            string processo = "";

            if (!tipo.Equals("Limpar"))
            {
                bool parametro = Control.Configuracoes.Dados.getParametroGradeDIDComplementar();

                if (cbbUndOrc.Value != null)
                {
                    unidOrc = "'" + cbbUndOrc.Value.ToString() + "'";
                }
                else
                {
                    Control.UsuariosControle ctrlUsu = new Control.UsuariosControle();

                    string idUsu = ctrlUsu.GetUsuarioByLogin(HttpContext.Current.Session["pUId"].ToString()).UsuarioID.ToString();
                    string idUndFunc = Control.UndFuncionalCtrl.getUndFuncByUsuID(idUsu).ToString();

                    string undOrcCad = Control.UnidadeOrcamentariaCtrl.GetUnidadeOrcamentriaByUnidadeFuncional(idUndFunc).undUnificado;
                    if (parametro == false)
                    {
                        unidOrc = "    SELECT UO.undunificado " +
                                "    FROM unidadeorcamentaria UO " +
                                "    JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                                "    WHERE " +
                                "    uog.uogunidadegestora = " +
                                "    (SELECT UOG.uogunidadegestora " +
                                "        FROM unidadeorcamentaria UO " +
                                "        JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                                "        where uo.undunificado = '" + undOrcCad + "' " +
                                "    ) ";
                    }
                    else {
                        unidOrc = " select UNDUNIFICADO from SCI_USUARIOS_UNDORC where usu_codigo = " + idUsu + " ";
                    }
                }

                if (txtNumEmpenho.Value != null)
                {
                    empenhoNum = "'" + txtNumEmpenho.Value.ToString() + "'";
                }
                else
                {
                    empenhoNum = "emp.empnumero";
                }

                if (dtCadastroIni.Value != null)
                {
                    dataIni = "'" + dtCadastroIni.Value.ToString().Substring(0, 10) + "'";
                }
                else
                {
                    dataIni = "emp.empdata";
                }

                if (dtCadastroFim.Value != null)
                {
                    dataFim = "'" + dtCadastroFim.Value.ToString().Substring(0, 10) + "'";
                }
                else
                {
                    dataFim = "emp.empdata";
                }

                if (cbbCredor.Value != null)
                {
                    credor = "'" + cbbCredor.Value.ToString() + "'";
                }
                else
                {
                    credor = "emp.emppessoa";
                }

                if ( txtNumProcesso.Value != null)
                {
                    processo = "AND PROC.NUMERO = '" + txtNumProcesso.Value.ToString() + "'";
                }
                else
                {
                    processo = "";
                }

                string sqlTable = "select uo.undunificado, uo.undabrev, emp.empdata, emp.empnumero, emp.emppessoa, " +
                                    "emp.empnomepessoa, emp.empnatureza, (emp.empvalor - coalesce(ema.emavalor, 0)) empvalor, lic.licnumero, lic.licdata,"+
                                    "coalesce(proc.NUMERO,(select numero from webprocesso where id = emp.empsemdid)) NUMERO, " +
                                    "case when emp.empstatus = 'T' then 'NORMAL' else 'Erro' end " +
                                    "empstatus, " +
                                    "case " +
                                    "    when emp.empfundeb = 1 then '1 - Magistério 60%' " +
                                    "    when emp.empfundeb = 2 then '2 - Administrativo 40%' " +
                                    "    when emp.empfundeb = 3 then '3 - MDE - Outros' " +
                                    "    else null " +
                                    "end " +
                                    "empfundeb, EMP.empcod, " +
                                    "CTR.CTRNUMERO, CTR.CTRDATA " +
                                    "from empenho emp " +
                                    "join unidadeorcamentaria uo on uo.undcodorgao = emp.emporgao and uo.undcodigo = emp.empunidade " +
                                    "left join licitacao lic on lic.liccod = emp.emplicitacao " +
                                    "LEFT JOIN CONTRATOS CTR ON CTR.ctrcod = EMP.empcontrato " +
                                    "LEFT JOIN WEBPROCESSO PROC ON PROC.ID = EMP.EMPDID "+
                                    "Left join empenhoanulacao ema on ema.emaempenho = emp.empcod " +
                                    "where uo.undunificado IN (" + unidOrc + ") "+
                                    " and emp.empnumero = " + empenhoNum + " and emp.emppessoa = " + credor +
                                    " and (emp.empdata >= " + dataIni.Replace("/", ".") + " and emp.empdata <= " + dataFim.Replace("/", ".") + ") " +  processo +
                                    " order by emp.empdata desc";

                DevExpress.Xpo.Session xpoSession = new DevExpress.Xpo.Session();
                XpoDataSource1.Session = xpoSession;

                //string connTeste = AcessoDados.AcessoDados.GetConnectionStringXPO();
                XpoDataSource1.Session.Connection = Persist.GetConn.getConn();
                //XpoDataSource1.Session.ConnectionString = connTeste;

                SelectedData resultSet = xpoSession.ExecuteQuery(sqlTable);
                XPDataView dv = new XPDataView();

                dv.AddProperty("UNDUNIFICADO", typeof(string));
                dv.AddProperty("UNDABREV", typeof(string));
                dv.AddProperty("EMPDATA", typeof(DateTime));
                dv.AddProperty("EMPNUMERO", typeof(string));
                dv.AddProperty("EMPPESSOA", typeof(string));
                dv.AddProperty("EMPNOMEPESSOA", typeof(string));
                dv.AddProperty("EMPNATUREZA", typeof(string));
                dv.AddProperty("EMPVALOR", typeof(double));
                dv.AddProperty("LICNUMERO", typeof(string));
                dv.AddProperty("LICDATA", typeof(DateTime));
                dv.AddProperty("NUMERO", typeof(string));
                dv.AddProperty("EMPSTATUS", typeof(string));
                dv.AddProperty("EMPFUNDEB", typeof(double));
                dv.AddProperty("EMPCOD", typeof(int));
                dv.AddProperty("CTRNUMERO", typeof(string));
                dv.AddProperty("CTRDATA", typeof(DateTime));

                dv.LoadData(resultSet);

                ASPxGridView1.DataSource = dv;
                Session["DTable"] = dv;
                ASPxGridView1.DataBind();

            }
            else {
                ASPxGridView1.DataSourceID = string.Empty;
                ASPxGridView1.DataBind();
            }
        }
        protected XPDataView AtualizarGrid()
        {
            UsuariosControle ctrlUsu = new Control.UsuariosControle();

            string usuarioDefault = ctrlUsu.GetUsuarioByLogin(HttpContext.Current.Session["pUId"].ToString()).UsuarioID.ToString();

            PerfilCtrl perfil = new Control.PerfilCtrl();

            bool perfilConsultaGeral = perfil.getPermissaoConsultaProcessos(usuarioDefault);

            perfil = null;

            string centrocusto = string.Empty;
            string undorca = string.Empty;

            string sqlTable = "select distinct " +
                                "CENCOD,"+
                                "CENNOME,"+
                                "FONCOD,"+
                                "FONNOME,"+
                                "UNDUNIFICADO,"+
                                "UNDNOME,"+
                                "CCCMESES,"+
                                "DESPJAN,"+
                                "DESPFEV,"+
                                "DESPMAR,"+
                                "DESPABR,"+
                                "DESPMAI,"+
                                "DESPJUN,"+
                                "DESPJUL,"+
                                "DESPAGO,"+
                                "DESPSET,"+
                                "DESPOUT,"+
                                "DESPNOV,"+
                                "DESPDEZ,"+
                                "DIDJAN,"+
                                "DIDFEV,"+
                                "DIDMAR,"+
                                "DIDABR,"+
                                "DIDMAI,"+
                                "DIDJUN,"+
                                "DIDJUL,"+
                                "DIDAGO,"+
                                "DIDSET,"+
                                "DIDOUT,"+
                                "DIDNOV,"+
                                "DIDDEZ,"+
                                "SALDOJAN,"+
                                "SALDOFEV,"+
                                "SALDOMAR,"+
                                "SALDOABR,"+
                                "SALDOMAI,"+
                                "SALDOJUN,"+
                                "SALDOJUL,"+
                                "SALDOAGO,"+
                                "SALDOSET,"+
                                "SALDOOUT,"+
                                "SALDONOV,"+
                                "SALDODEZ " +
                                "from VW_FLX_REL_EXT_CENCUSTO_GRID ";

            if (cbbCentroCusto.Value != null)
            {
                centrocusto = cbbCentroCusto.Value.ToString();
            }
            else
            {
                centrocusto = "CENCOD ";
            }

            if (cbbUndOrc.Value != null)
            {
                undorca = "= '" + cbbUndOrc.Value.ToString() + "' ";
            }
            else
            {

                if (!perfilConsultaGeral)
                {
                    string idUndFunc = Control.UndFuncionalCtrl.getUndFuncByUsuID(usuarioDefault).ToString();
                    string undOrcCad = Control.UnidadeOrcamentariaCtrl.GetUnidadeOrcamentriaByUnidadeFuncional(idUndFunc).undUnificado;

                    undorca = "in (    SELECT UO.undunificado " +
                        "    FROM unidadeorcamentaria UO " +
                        "    JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                        "    WHERE " +
                        "    uog.uogunidadegestora = " +
                        "    (SELECT UOG.uogunidadegestora " +
                        "        FROM unidadeorcamentaria UO " +
                        "        JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                        "        where uo.undunificado = '" + undOrcCad + "' " +
                        "    ) ) ";
                }
                else
                {
                    undorca = "= UNDUNIFICADO ";
                }
            }

            ////usuarioEnvio = "USUARIOENVIO";

            sqlTable += " WHERE ";
            sqlTable += " CENCOD = " + centrocusto;
            sqlTable += " AND ";
            sqlTable += " UNDUNIFICADO " + undorca;

            DevExpress.Xpo.Session xpoSession = new DevExpress.Xpo.Session();
            XpoDataSource1.Session = xpoSession;

            XpoDataSource1.Session.Connection = Persist.GetConn.getConn();

            SelectedData resultSet = xpoSession.ExecuteQuery(sqlTable);
            XPDataView dv = new XPDataView();

            dv.AddProperty("CENCOD", typeof(int));
            dv.AddProperty("CENNOME", typeof(string));
            dv.AddProperty("FONCOD", typeof(int));
            dv.AddProperty("FONNOME", typeof(string));
            dv.AddProperty("UNDUNIFICADO", typeof(string));
            dv.AddProperty("UNDNOME", typeof(string));
            dv.AddProperty("CCCMESES", typeof(decimal));
            dv.AddProperty("DESPJAN", typeof(decimal));
            dv.AddProperty("DESPFEV", typeof(decimal));
            dv.AddProperty("DESPMAR", typeof(decimal));
            dv.AddProperty("DESPABR", typeof(decimal));
            dv.AddProperty("DESPMAI", typeof(decimal));
            dv.AddProperty("DESPJUN", typeof(decimal));
            dv.AddProperty("DESPJUL", typeof(decimal));
            dv.AddProperty("DESPAGO", typeof(decimal));
            dv.AddProperty("DESPSET", typeof(decimal));
            dv.AddProperty("DESPOUT", typeof(decimal));
            dv.AddProperty("DESPNOV", typeof(decimal));
            dv.AddProperty("DESPDEZ", typeof(decimal));
            dv.AddProperty("DIDJAN", typeof(decimal));
            dv.AddProperty("DIDFEV", typeof(decimal));
            dv.AddProperty("DIDMAR", typeof(decimal));
            dv.AddProperty("DIDABR", typeof(decimal));
            dv.AddProperty("DIDMAI", typeof(decimal));
            dv.AddProperty("DIDJUN", typeof(decimal));
            dv.AddProperty("DIDJUL", typeof(decimal));
            dv.AddProperty("DIDAGO", typeof(decimal));
            dv.AddProperty("DIDSET", typeof(decimal));
            dv.AddProperty("DIDOUT", typeof(decimal));
            dv.AddProperty("DIDNOV", typeof(decimal));
            dv.AddProperty("DIDDEZ", typeof(decimal));
            dv.AddProperty("SALDOJAN", typeof(decimal));
            dv.AddProperty("SALDOFEV", typeof(decimal));
            dv.AddProperty("SALDOMAR", typeof(decimal));
            dv.AddProperty("SALDOABR", typeof(decimal));
            dv.AddProperty("SALDOMAI", typeof(decimal));
            dv.AddProperty("SALDOJUN", typeof(decimal));
            dv.AddProperty("SALDOJUL", typeof(decimal));
            dv.AddProperty("SALDOAGO", typeof(decimal));
            dv.AddProperty("SALDOSET", typeof(decimal));
            dv.AddProperty("SALDOOUT", typeof(decimal));
            dv.AddProperty("SALDONOV", typeof(decimal));
            dv.AddProperty("SALDODEZ", typeof(decimal));

            dv.LoadData(resultSet);

            ASPxGridView1.DataSource = dv;
            Session["DTable"] = dv;
            ASPxGridView1.DataBind();

            return dv;
        }
Esempio n. 6
0
        public void load_data()
        {
            int      month    = Int32.Parse(hS06dnMonth.Get("month_id").ToString());
            int      year     = Int32.Parse(hS06dnYear.Get("year_id").ToString());
            string   owner    = hS06dnOwner.Get("owner_id").ToString();
            string   asset    = hS06dnAsset.Get("asset_id").ToString();
            DateTime fromDate = new DateTime(year, month, 1);


            int lastmonth = 0;
            int lastyear  = year;

            if (month == 1)
            {
                lastmonth = 12;
                lastyear  = year - 1;
            }
            else
            {
                lastmonth = month - 1;
            }

            m_Sql = "" +
                    "select result.Code as AccountCode, result.Name as AccountName, " +
                    "	sum(result.beginDebit) as BeginDebit, "+
                    "	sum(result.beginCredit) as BeginCredit,"+
                    "	sum(result.Debit) as Debit, "+
                    "	sum(result.Credit) as Credit,"+
                    "	case "+
                    "		when sum(result.beginDebit) + sum(result.Debit) - sum(result.beginCredit)- sum(result.Credit) > 0 THEN (sum(result.beginDebit) + sum(result.Debit) - sum(result.Credit)) ELSE 0  end as EndDebit, 		 		"+
                    "	case "+
                    "		when sum(result.beginCredit) + sum(result.Credit) - sum(result.beginDebit) - sum(result.Debit) > 0 THEN (sum(result.beginCredit) + sum(result.Credit) - sum(result.Debit)) ELSE 0 end as EndCredit	"+
                    "from	"+
                    "	(select transact.Code, transact.Name, "+
                    "		"+
                    "		case "+
                    "			when sum(isnull(BeginDebitBalance,0)) > 0 "+
                    "				THEN sum(isnull(transact.BeginDebitBalance,0)) + sum(transact.Debit) - sum(transact.Credit) ELSE 0  end as beginDebit, 		"+
                    "		case 	"+
                    "			when sum(isnull(BeginCreditBalance,0)) > 0 "+
                    "				THEN sum(isnull(transact.BeginCreditBalance,0)) + sum(transact.Credit) - sum(transact.Debit) ELSE 0 end as beginCredit,	"+
                    "		0 as Debit, 0 as Credit"+
                    "		"+
                    "	from	"+
                    "		(		"+
                    "		select b.Code, b.Name, a.BeginDebitBalance, a.BeginCreditBalance, 0 as Debit, 0 as Credit   		"+
                    "		from FinancialGeneralLedgerByYear_Fact a, FinancialAccountDim b			"+
                    "		where a.FinancialAccountDimId = b.FinancialAccountDimId"+
                    "	"+
                    "		union all"+
                    "		"+
                    "		select e.Code, e.Name, 0 as BeginDebitBalance, 0 as BeginCreditBalance,"+
                    "			a.CreditSum as Debit, a.DebitSum as Credit"+
                    "		from FinancialGeneralLedgerByMonth a,"+
                    "			FinancialGeneralLedgerByYear_Fact b,"+
                    "			MonthDim c,"+
                    "			YearDim d,"+
                    "			FinancialAccountDim e"+
                    "		where a.FinancialGeneralLedgerByYear_FactId = b.FinancialGeneralLedgerByYear_FactId"+
                    "		and a.MonthDimId = c.MonthDimId"+
                    "		and b.YearDimId = d.YearDimId"+
                    "		and b.FinancialAccountDimId = e.FinancialAccountDimId"+
                    "		and c.Name <= "+ lastmonth.ToString() +
                    "		and d.Name = "+ lastyear.ToString() + ") transact 				"+
                    "	group by transact.Code, transact.Name"+
                    "	"+
                    "	union all"+
                    "	select transact.Code, transact.Name,"+
                    "			0 as beginDebit, 0 as beginCredit,"+
                    "			transact.Debit, transact.Credit"+
                    "			"+
                    "	from "+
                    "		(select e.Code, e.Name, 			"+
                    "			sum(CreditSum) as Debit, sum(DebitSum) as Credit	"+
                    "		from FinancialGeneralLedgerByMonth a,"+
                    "			FinancialGeneralLedgerByYear_Fact b,"+
                    "			MonthDim c,"+
                    "			YearDim d,"+
                    "			FinancialAccountDim e"+
                    "		where a.FinancialGeneralLedgerByYear_FactId = b.FinancialGeneralLedgerByYear_FactId"+
                    "		and a.MonthDimId = c.MonthDimId"+
                    "		and b.YearDimId = d.YearDimId"+
                    "		and b.FinancialAccountDimId = e.FinancialAccountDimId"+
                    "		and c.Name = "+ month.ToString() +
                    "		and d.Name = "+ year.ToString() +
                    "		group by b.FinancialAccountDimId, e.Code, e.Name) transact	"+
                    "	) result "+
                    " where ( " +
                    "result.beginDebit > 0 or " +
                    "result.beginCredit > 0 or " +
                    "result.Debit > 0 or " +
                    "result.Credit > 0 " +
                    ") " +
                    " group by result.Code, result.Name " +
                    " order by result.Code, result.Name ";

            m_SelectedData = m_Session.ExecuteQuery(m_Sql);

            xpDataView = new XPDataView();
            xpDataView.AddProperty("AccountCode", typeof(string));
            xpDataView.AddProperty("AccountName", typeof(string));
            xpDataView.AddProperty("BeginDebit", typeof(double));
            xpDataView.AddProperty("BeginCredit", typeof(double));
            xpDataView.AddProperty("Debit", typeof(double));
            xpDataView.AddProperty("Credit", typeof(double));
            xpDataView.AddProperty("EndDebit", typeof(double));
            xpDataView.AddProperty("EndCredit", typeof(double));

            xpDataView.LoadData(m_SelectedData);



            WebModule.Accounting.Report.S06_DN report = new WebModule.Accounting.Report.S06_DN();
            report.DataSource = xpDataView;
            report.DataMember = "";
            report.Parameters["fromDate"].Value = fromDate;


            S06dnReportViewer.Report = report;
        }
        public XPDataView atualizaGridEmpenhos(string tipo)
        {
            string unidOrc = "";
            string unidOrcDefault = "";
            string empenhoNum = "";
            string dataIni = "";
            string dataFim = "";
            string credor = "";
            string sql = string.Empty;

            XPDataView dv = new XPDataView();

            if (!tipo.Equals("Limpar"))
            {
                if (!chkRestosPagar.Checked)
                {
                    Control.UsuariosControle ctrlUsu = new Control.UsuariosControle();

                    bool parametro = Control.Configuracoes.Dados.getParametroGradeDIDComplementar();

                    string idUsu = ctrlUsu.GetUsuarioByLogin(HttpContext.Current.Session["pUId"].ToString()).UsuarioID.ToString();
                    string idUndFunc = Control.UndFuncionalCtrl.getUndFuncByUsuID(idUsu).ToString();

                    unidOrcDefault = Control.UnidadeOrcamentariaCtrl.GetUnidadeOrcamentriaByUnidadeFuncional(idUndFunc).undUnificado;

                    if (cbbUndOrc.Value != null)
                    {
                        unidOrc = "'" + cbbUndOrc.Value.ToString() + "'";
                    }
                    else
                    {
                        unidOrc = "uo.undunificado";
                    }

                    if (txtNumEmpenho.Value != null)
                    {
                        empenhoNum = "'" + txtNumEmpenho.Value.ToString() + "'";
                    }
                    else
                    {
                        empenhoNum = "emp.empnumero";
                    }

                    if (dtCadastroIni.Value != null)
                    {
                        dataIni = "'" + dtCadastroIni.Value.ToString().Substring(0, 10) + "'";
                    }
                    else
                    {
                        dataIni = "emp.empdata";
                    }

                    if (dtCadastroFim.Value != null)
                    {
                        dataFim = "'" + dtCadastroFim.Value.ToString().Substring(0, 10) + "'";
                    }
                    else
                    {
                        dataFim = "emp.empdata";
                    }

                    if (cbbCredor.Value != null)
                    {
                        credor = "'" + cbbCredor.Value.ToString() + "'";
                    }
                    else
                    {
                        credor = "emp.emppessoa";
                    }

                    sql = "select uo.undunificado, uo.undabrev, emp.empdata, emp.empnumero, emp.emppessoa, " +
                    "emp.empnomepessoa, emp.empnatureza, (emp.empvalor - coalesce(ema.emavalor,0)) empvalor, lic.licnumero, lic.licdata,proc.numero PROCNUMERO,  did.id DIDID," +
                    "case when emp.empstatus = 'T' then 'NORMAL' else 'Erro' end " +
                    "empstatus, " +
                    "(select * from SP_FLUXUS_SALDOLIQUIDACAO(proc.id)) SALDOLIQUIDACAO," +
                    "(select * from SP_FLUXUS_SALDOPAGAMENTO(proc.id)) SALDOPAGAMENTO," +
                    "case " +
                    "    when emp.empfundeb = 1 then '1 - Magistério 60%' " +
                    "    when emp.empfundeb = 2 then '2 - Administrativo 40%' " +
                    "    when emp.empfundeb = 3 then '3 - MDE - Outros' " +
                    "    else null " +
                    "end " +
                    "empfundeb, EMP.empcod, " +
                    "CTR.CTRNUMERO, CTR.CTRDATA " +
                    "from empenho emp " +
                    "join unidadeorcamentaria uo on uo.undcodorgao = emp.emporgao and uo.undcodigo = emp.empunidade " +
                    "left join licitacao lic on lic.liccod = emp.emplicitacao " +
                    "LEFT JOIN CONTRATOS CTR ON CTR.ctrcod = EMP.empcontrato " +
                    "join webprocesso proc on emp.empdid = proc.id " +
                    "join webDID did on did.numero = proc.numero " +
                    "Left join empenhoanulacao ema on ema.emaempenho = emp.empcod " +
                    "where ";

                    //if (parametro == false)
                    //{
                    //    sql += "uo.undunificado in (SELECT UO.undunificado " +
                    //    "    FROM unidadeorcamentaria UO " +
                    //    "    JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                    //    "    WHERE " +
                    //    "    uog.uogunidadegestora = " +
                    //    "    (SELECT UOG.uogunidadegestora " +
                    //    "        FROM unidadeorcamentaria UO " +
                    //    "        JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                    //    "        where uo.undunificado = '" + unidOrcDefault + "' " +
                    //    "    ) " +
                    //    ")";
                    //}
                    //else
                    //{
                        sql += "uo.undunificado in (select UNDUNIFICADO from SCI_USUARIOS_UNDORC where usu_codigo = " + idUsu;
                        sql += " union " +
                            "SELECT UNDUNIFICADO FROM unidadeorcamentaria UN " +
                            "JOIN webunidadefuncional UF ON UF.unidorccod = UN.undcodigo AND UF.unidorcorg = UN.undcodorgao " +
                            "JOIN sci_usuarios USU ON USU.usu_idundfuncional = UF.unidfuncid " +
                            "WHERE USU.usu_codigo = " + idUsu;
                        sql += " )";
                    //}

                    if (txtNumEmpenho.Value == null && dtCadastroIni.Value == null && dtCadastroFim.Value == null && cbbCredor.Value == null)
                    {
                        sql += " and (((select * from SP_FLUXUS_SALDOLIQUIDACAO(proc.id)) + (select * from SP_FLUXUS_SALDOPAGAMENTO(proc.id))) <> 0) ";
                    }

                    sql += " and emp.empnumero = " + empenhoNum + " and emp.emppessoa = " + credor + " and uo.undunificado =" + unidOrc +
                                    " and (emp.empdata >= " + dataIni.Replace("/", ".") + " and emp.empdata <= " + dataFim.Replace("/", ".") + ") " +
                                    "order by emp.empdata desc";
                }
                else
                {
                    Control.UsuariosControle ctrlUsu = new Control.UsuariosControle();

                    bool parametro = Control.Configuracoes.Dados.getParametroGradeDIDComplementar();

                    string idUsu = ctrlUsu.GetUsuarioByLogin(HttpContext.Current.Session["pUId"].ToString()).UsuarioID.ToString();
                    string idUndFunc = Control.UndFuncionalCtrl.getUndFuncByUsuID(idUsu).ToString();

                    unidOrcDefault = Control.UnidadeOrcamentariaCtrl.GetUnidadeOrcamentriaByUnidadeFuncional(idUndFunc).undUnificado;

                    if (cbbUndOrc.Value != null)
                    {
                        unidOrc = "'" + cbbUndOrc.Value.ToString() + "'";
                    }
                    else
                    {
                        unidOrc = "uo.undunificado";
                    }

                    if (txtNumEmpenho.Value != null)
                    {
                        empenhoNum = "'" + txtNumEmpenho.Value.ToString() + "'";
                    }
                    else
                    {
                        empenhoNum = "rpemp.rpenumero";
                    }

                    if (dtCadastroIni.Value != null)
                    {
                        dataIni = "'" + dtCadastroIni.Value.ToString().Substring(0, 10) + "'";
                    }
                    else
                    {
                        dataIni = "rpemp.rpedata";
                    }

                    if (dtCadastroFim.Value != null)
                    {
                        dataFim = "'" + dtCadastroFim.Value.ToString().Substring(0, 10) + "'";
                    }
                    else
                    {
                        dataFim = "rpemp.rpedata";
                    }

                    if (cbbCredor.Value != null)
                    {
                        credor = "'" + cbbCredor.Value.ToString() + "'";
                    }
                    else
                    {
                        credor = "rpemp.rpepessoa";
                    }

                    sql = "select uo.undunificado, uo.undabrev, rpemp.rpedata, rpemp.rpenumero, rpemp.rpepessoa, pes.pesnome as empnomepessoa, " +
                    "rpemp.rpenatureza, (rpemp.rpevalor - coalesce(rpema.rpavalor,0)) rpempvalor, lic.licnumero, lic.licdata, " +
                    "proc.numero PROCNUMERO,  did.id DIDID, " +
                    "case when rpemp.rpestatus = 'T' then 'NORMAL' else 'Erro' end rpempstatus, " +
                    "(select VALORLIQ from SP_FLUXUS_SALDORP_NPROC(rpemp.rpenumero)) SALDOLIQUIDACAO, " +
                    "(select VALORLIQ from SP_FLUXUS_SALDORP_PROC(rpemp.rpenumero)) SALDOPAGAMENTO, " +
                    "case " +
                    "when rpemp.rpefundeb = 1 then '1 - Magistério 60%' " +
                    "when rpemp.rpefundeb = 2 then '2 - Administrativo 40%' " +
                    "when rpemp.rpefundeb = 3 then '3 - MDE - Outros' " +
                    "else null end rpefundeb, rpEMP.rpecod, CTR.CTRNUMERO, CTR.CTRDATA " +
                    "from rpempenho rpemp " +
                    "join unidadeorcamentaria uo on uo.undcodorgao = rpemp.rpeorgao and uo.undcodigo = rpemp.rpeunidade " +
                    "left join licitacao lic on lic.liccod = rpemp.rpelicitacao " +
                    "LEFT JOIN CONTRATOS CTR ON CTR.ctrcod = rpEMP.rpecontrato " +
                    "join webprocesso proc on rpemp.rpedid = proc.id " +
                    "join webDID did on did.numero = proc.numero " +
                    "Left join rpempenhoanulacao rpema on rpema.rparpempenho = rpemp.rpecod " +
                    "join pessoa pes on pes.pescod = rpemp.rpepessoa where ";

                    sql += "uo.undunificado in (select UNDUNIFICADO from SCI_USUARIOS_UNDORC where usu_codigo = " + idUsu ;
                    sql += " union "+
                        "SELECT UNDUNIFICADO FROM unidadeorcamentaria UN "+
                        "JOIN webunidadefuncional UF ON UF.unidorccod = UN.undcodigo AND UF.unidorcorg = UN.undcodorgao "+
                        "JOIN sci_usuarios USU ON USU.usu_idundfuncional = UF.unidfuncid "+
                        "WHERE USU.usu_codigo = " + idUsu;
                    sql += " )";

                    sql += " and rpemp.rpenumero = " + empenhoNum + " and rpemp.rpepessoa = " + credor + " and uo.undunificado =" + unidOrc +
                            " and (rpemp.rpedata >= " + dataIni.Replace("/", ".") + " and rpemp.rpedata <= " + dataFim.Replace("/", ".") + ") " +
                            "order by rpemp.rpedata desc";
                }

                DevExpress.Xpo.Session xpoSession = new DevExpress.Xpo.Session();
                XpoDataSource1.Session = xpoSession;

                //string connTeste = AcessoDados.AcessoDados.GetConnectionStringXPO();
                XpoDataSource1.Session.Connection = Persist.GetConn.getConn();
                //XpoDataSource1.Session.ConnectionString = connTeste;

                SelectedData resultSet = xpoSession.ExecuteQuery(sql);

                dv.AddProperty("UNDUNIFICADO", typeof(string));
                dv.AddProperty("UNDABREV", typeof(string));
                dv.AddProperty("EMPDATA", typeof(DateTime));
                dv.AddProperty("EMPNUMERO", typeof(string));
                dv.AddProperty("EMPPESSOA", typeof(string));
                dv.AddProperty("EMPNOMEPESSOA", typeof(string));
                dv.AddProperty("EMPNATUREZA", typeof(string));
                dv.AddProperty("EMPVALOR", typeof(double));
                dv.AddProperty("LICNUMERO", typeof(string));
                dv.AddProperty("LICDATA", typeof(DateTime));
                dv.AddProperty("PROCNUMERO", typeof(string));
                dv.AddProperty("DIDID", typeof(int));
                dv.AddProperty("EMPSTATUS", typeof(string));
                dv.AddProperty("SALDOLIQUIDACAO", typeof(double));
                dv.AddProperty("SALDOPAGAMENTO", typeof(double));
                dv.AddProperty("EMPFUNDEB", typeof(string));
                dv.AddProperty("EMPCOD", typeof(int));
                dv.AddProperty("CTRNUMERO", typeof(string));
                dv.AddProperty("CTRDATA", typeof(DateTime));

                dv.LoadData(resultSet);

                ASPxGridView1.DataSource = dv;
                ASPxGridView1.DataBind();

            }
            else
            {
                ASPxGridView1.DataSourceID = string.Empty;
                ASPxGridView1.DataBind();
            }

            Session["DTGRIDEMPENHO"] = dv;
            return dv;
        }
        public void AtualizarGridLiqPag(string LiqPag)
        {
            string sql = string.Empty;
            if (!chkRestosPagar.Checked)
            {
                if (LiqPag == "L")
                {
                    sql = "select * " +
                            "from (select PROC.ID, PROC.NUMERO PROCNUMERO, EMP.EMPNUMERO, EMP.EMPCOD, " +
                            "             (" +
                            "             case " +
                            "               when PROC.IDPROCESSOPAI is null then TRA.VAL_LIQUIDADO " +
                            "               else DID.VALOR " +
                            "             end) VALOR, " +
                            "             PROC.DATAHORACADASTRO, PROC.RESPONSAVEL, PES.PESNOME as EMPNOMEPESSOA, TRA.NF_LIQUIDACAO NOTAFISCAL, " +
                            "             'X' as PRINT, SP.descricao " +
                            "      from WEBPROCESSO PROC " +
                            "      join WEBDID DID on DID.NUMERO = PROC.NUMERO " +
                            "      join PESSOA PES on PES.PESCOD = DID.IDFORNECEDOR " +
                            "      join WEBTRAMITE TRA on TRA.IDPROCESSO = PROC.ID " +
                            "      join EMPENHO EMP on EMP.EMPDID = PROC.IDPROCESSOPAI " +
                            "      JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "      where (PROC.IDPROCESSOPAI = (select ID " +
                            "                                   from WEBPROCESSO " +
                            "                                   where NUMERO = " + txtProcNumero.Text + ") and " +
                            "            TRA.VAL_LIQUIDADO is not null and " +
                            "            TRA.VAL_LIQUIDADO <> 0 AND TRA.nf_liquidacao IS NOT NULL and " +
                            "            TRA.ID = (select first 1 ID " +
                            "                      from WEBTRAMITE TRA3 " +
                            "                      where TRA3.IDPROCESSO = PROC.ID and " +
                            "                            TRA3.VAL_LIQUIDADO is not null and " +
                            "                            TRA3.VAL_LIQUIDADO <> 0 AND TRA3.nf_liquidacao IS NOT NULL " +
                            "                      order by TRA3.ID desc) " +
                            "            ) " +
                            "      union all " +
                            "      select PROC.ID, PROC.NUMERO PROCNUMERO, EMP.EMPNUMERO, EMP.EMPCOD, " +
                            "             ( " +
                            "             case " +
                            "               when PROC.IDPROCESSOPAI is null then TRA.VAL_LIQUIDADO " +
                            "               else DID.VALOR " +
                            "             end) VALOR, " +
                            "             PROC.DATAHORACADASTRO, PROC.RESPONSAVEL, PES.PESNOME as EMPNOMEPESSOA, TRA.NF_LIQUIDACAO NOTAFISCAL, " +
                            "             'X' as PRINT, SP.descricao " +
                            "      from WEBPROCESSO PROC " +
                            "      join WEBDID DID on DID.NUMERO = PROC.NUMERO " +
                            "      join PESSOA PES on PES.PESCOD = DID.IDFORNECEDOR " +
                            "      join WEBTRAMITE TRA on TRA.IDPROCESSO = PROC.ID " +
                            "      join EMPENHO EMP on EMP.EMPDID = PROC.ID " +
                            "      JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "      where PROC.NUMERO = " + txtProcNumero.Text + " and " +
                            "            TRA.ID = (select first 1 TRA.ID " +
                            "                      from WEBTRAMITE TRA " +
                            "                      join WEBPROCESSO PRO on PRO.ID = TRA.IDPROCESSO " +
                            "                      where PRO.NUMERO = " + txtProcNumero.Text + " and " +
                            "                            TRA.VAL_LIQUIDADO is not null  AND TRA.VAL_LIQUIDADO <> 0 " +
                            "                      order by TRA.ID desc)) " +
                            "order by ID  ";
                }
                else
                {
                    sql = "Select * from (SELECT proc.id, proc.numero PROCNUMERO,  emp.empnumero, emp.empcod," +
                            "(case when proc.idprocessopai is null then tra.val_pago else did.valor end) valor," +
                            "proc.datahoracadastro," +
                            "PROC.responsavel, pes.pesnome as  empnomepessoa, 'X' NOTAFISCAL, '' as Print, SP.descricao " +
                            "from webprocesso proc " +
                            "join webdid did on did.numero = proc.numero " +
                            "join pessoa pes on pes.pescod = did.idfornecedor " +
                            "join webtramite tra on tra.idprocesso = proc.id " +
                            "join empenho emp on emp.empdid = proc.idprocessopai " +
                            "JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "where (proc.idprocessopai = (select id from webprocesso where numero = " + txtProcNumero.Text + ") and (tra.val_pago is not null and tra.val_pago <> 0)) " +
                            "union ALL " +
                            "SELECT proc.id, proc.numero PROCNUMERO,  emp.empnumero, emp.empcod, " +
                            "(case when proc.idprocessopai is null then tra.val_pago else did.valor end) valor,proc.datahoracadastro, " +
                            "PROC.responsavel, pes.pesnome as  empnomepessoa, 'X' NOTAFISCAL, '' as Print, SP.descricao " +
                            "from webprocesso proc " +
                            "join webdid did on did.numero = proc.numero " +
                            "join pessoa pes on pes.pescod = did.idfornecedor " +
                            "join webtramite tra on tra.idprocesso = proc.id " +
                            "join empenho emp on emp.empdid = proc.id " +
                            "JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "WHERE PROC.numero = " + txtProcNumero.Text + " AND TRA.id = (select first 1 tra.id from webtramite tra join webprocesso pro on pro.id " +
                            "= tra.idprocesso where pro.numero = " + txtProcNumero.Text + " and (tra.val_pago is not null and tra.val_pago <>0) order by tra.id desc)) order by id";

                }
            }
            else {
                if (LiqPag == "L")
                {
                    sql = "select * " +
                            "from (select PROC.ID, PROC.NUMERO PROCNUMERO, RPEMP.RPENUMERO EMPNUMERO, RPEMP.RPECOD EMPCOD, " +
                            "             (" +
                            "             case " +
                            "               when PROC.IDPROCESSOPAI is null then TRA.VAL_LIQUIDADO " +
                            "               else DID.VALOR " +
                            "             end) VALOR, " +
                            "             PROC.DATAHORACADASTRO, PROC.RESPONSAVEL, PES.PESNOME as EMPNOMEPESSOA, TRA.NF_LIQUIDACAO NOTAFISCAL, " +
                            "             'X' as PRINT, SP.descricao " +
                            "      from WEBPROCESSO PROC " +
                            "      join WEBDID DID on DID.NUMERO = PROC.NUMERO " +
                            "      join PESSOA PES on PES.PESCOD = DID.IDFORNECEDOR " +
                            "      join WEBTRAMITE TRA on TRA.IDPROCESSO = PROC.ID " +
                            "      join RPEMPENHO RPEMP on RPEMP.RPEDID = PROC.IDPROCESSOPAI " +
                            "      JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "      where (PROC.IDPROCESSOPAI = (select ID " +
                            "                                   from WEBPROCESSO " +
                            "                                   where NUMERO = " + txtProcNumero.Text + ") and " +
                            "            TRA.VAL_LIQUIDADO is not null and " +
                            "            TRA.VAL_LIQUIDADO <> 0 AND TRA.nf_liquidacao IS NOT NULL and " +
                            "            TRA.ID = (select first 1 ID " +
                            "                      from WEBTRAMITE TRA3 " +
                            "                      where TRA3.IDPROCESSO = PROC.ID and " +
                            "                            TRA3.VAL_LIQUIDADO is not null and " +
                            "                            TRA3.VAL_LIQUIDADO <> 0 AND TRA3.nf_liquidacao IS NOT NULL " +
                            "                      order by TRA3.ID desc) " +
                            "            ) AND PROC.EXERCICIO = (SELECT FIRST 1 ENTEXERCICIO FROM ENTIDADE)" +
                            "      union all " +
                            "      select PROC.ID, PROC.NUMERO PROCNUMERO, RPEMP.RPENUMERO EMPNUMERO, RPEMP.RPECOD EMPCOD, " +
                            "             ( " +
                            "             case " +
                            "               when PROC.IDPROCESSOPAI is null then TRA.VAL_LIQUIDADO " +
                            "               else DID.VALOR " +
                            "             end) VALOR, " +
                            "             PROC.DATAHORACADASTRO, PROC.RESPONSAVEL, PES.PESNOME as EMPNOMEPESSOA, TRA.NF_LIQUIDACAO NOTAFISCAL, " +
                            "             'X' as PRINT, SP.descricao " +
                            "      from WEBPROCESSO PROC " +
                            "      join WEBDID DID on DID.NUMERO = PROC.NUMERO " +
                            "      join PESSOA PES on PES.PESCOD = DID.IDFORNECEDOR " +
                            "      join WEBTRAMITE TRA on TRA.IDPROCESSO = PROC.ID " +
                            "      join RPEMPENHO RPEMP on RPEMP.RPEDID = PROC.ID " +
                            "      JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "      where PROC.NUMERO = " + txtProcNumero.Text + " and " +
                            "            TRA.ID = (select first 1 TRA.ID " +
                            "                      from WEBTRAMITE TRA " +
                            "                      join WEBPROCESSO PRO on PRO.ID = TRA.IDPROCESSO " +
                            "                      where PRO.NUMERO = " + txtProcNumero.Text + " and " +
                            "                            TRA.VAL_LIQUIDADO is not null  AND TRA.VAL_LIQUIDADO <> 0 " +
                            "                      order by TRA.ID desc) AND PROC.EXERCICIO = (SELECT FIRST 1 ENTEXERCICIO FROM ENTIDADE)) " +
                            "order by ID  ";
                }
                else
                {
                    sql = "Select * from (SELECT proc.id, proc.numero PROCNUMERO,  RPEMP.RPENUMERO EMPNUMERO, RPEMP.RPECOD EMPCOD," +
                            "(case when proc.idprocessopai is null then tra.val_pago else did.valor end) valor," +
                            "proc.datahoracadastro," +
                            "PROC.responsavel, pes.pesnome as  empnomepessoa, 'X' NOTAFISCAL, '' as Print, SP.descricao " +
                            "from webprocesso proc " +
                            "join webdid did on did.numero = proc.numero " +
                            "join pessoa pes on pes.pescod = did.idfornecedor " +
                            "join webtramite tra on tra.idprocesso = proc.id " +
                            "join rpempenho rpemp on rpemp.rpedid = proc.idprocessopai " +
                            "JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "where (proc.idprocessopai = (select id from webprocesso where numero = " + txtProcNumero.Text + ") and (tra.val_pago is not null and tra.val_pago <> 0)) " +
                            "union ALL " +
                            "SELECT proc.id, proc.numero PROCNUMERO,  RPEMP.RPENUMERO EMPNUMERO, RPEMP.RPECOD EMPCOD, " +
                            "(case when proc.idprocessopai is null then tra.val_pago else did.valor end) valor,proc.datahoracadastro, " +
                            "PROC.responsavel, pes.pesnome as  empnomepessoa, 'X' NOTAFISCAL, '' as Print, SP.descricao " +
                            "from webprocesso proc " +
                            "join webdid did on did.numero = proc.numero " +
                            "join pessoa pes on pes.pescod = did.idfornecedor " +
                            "join webtramite tra on tra.idprocesso = proc.id " +
                            "join rpempenho rpemp on rpemp.rpedid = proc.id " +
                            "JOIN websituacaoprocesso SP ON SP.id = (select first 1 idsituacaoprojeto from webtramite t where idprocesso = proc.id order by id desc) " +
                            "WHERE PROC.numero = " + txtProcNumero.Text + " AND TRA.id = (select first 1 tra.id from webtramite tra join webprocesso pro on pro.id " +
                            "= tra.idprocesso where pro.numero = " + txtProcNumero.Text + " and (tra.val_pago is not null and tra.val_pago <> 0) order by tra.id desc)) order by id";

                }
            }
            //ASPxGridView2.DataSource = AcessoDados.AcessoDados.dtable(sql);
            //ASPxGridView2.DataBind();

            DevExpress.Xpo.Session xpoSession = new DevExpress.Xpo.Session();
            XpoDataSource2.Session = xpoSession;

            //string connTeste = AcessoDados.AcessoDados.GetConnectionStringXPO();
            XpoDataSource2.Session.Connection = Persist.GetConn.getConn();
            //XpoDataSource2.Session.ConnectionString = connTeste;

            SelectedData resultSet = xpoSession.ExecuteQuery(sql);
            XPDataView dv = new XPDataView();

            dv.AddProperty("ID", typeof(int));
            dv.AddProperty("PROCNUMERO", typeof(string));
            dv.AddProperty("EMPNUMERO", typeof(string));
            dv.AddProperty("EMPCOD", typeof(int));
            dv.AddProperty("VALOR", typeof(double));
            dv.AddProperty("DATAHORACADASTRO", typeof(DateTime));
            dv.AddProperty("RESPONSAVEL", typeof(string));
            dv.AddProperty("EMPNOMEPESSOA", typeof(string));
            dv.AddProperty("NOTAFISCAL", typeof(string));
            dv.AddProperty("PRINT", typeof(string));
            dv.AddProperty("DESCRICAO", typeof(string));

            dv.LoadData(resultSet);

            ASPxGridView2.DataSource = dv;
            ASPxGridView2.DataBind();
        }
        private void BindSchedular()
        {
            Session currentSession = ((XPObjectSpace)object_Space).Session;
            // dummy ds
            var ds  = object_Space.GetObjects <LeavePlanner>().Where(r => r.RequestStatus == RequestStatus.Approved).Select(r => new { Oid = 0, AllDay = 1, Description = "", EndTime = new DateTime(1800, 1, 1), Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = new DateTime(1800, 1, 1), Status = "", Subject = "", EvenType = "", Employee = r.Employee }).ToList().Take(1);
            var ds1 = ds;
            var ds2 = ds;
            var ds3 = ds;
            var ds4 = ds;
            var ds5 = ds;
            var ds6 = ds;

            if (chkPlannedLeaveApprove.Checked)
            {
                ds1 = object_Space.GetObjects <LeavePlanner>().Where(r => r.RequestStatus == RequestStatus.Approved).Select(r => new { Oid = (r.Employee != null?r.Employee.Oid:0), AllDay = 1, Description = (r.Employee != null ? r.Employee.FullNameEn:""), EndTime = r.EndDate, Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = (r.StartDate.ToString("hh:mm") == "00:00"?r.StartDate.AddMinutes(59).AddHours(23):r.StartDate), Status = "", Subject = (r.Employee != null ? r.Employee.FullNameEn:""), EvenType = "", Employee = r.Employee });
            }
            if (chkPlannedLeaveUnApprove.Checked)
            {
                ds2 = object_Space.GetObjects <LeavePlanner>().Where(r => r.RequestStatus != RequestStatus.Approved).Select(r => new { Oid = (r.Employee != null ? r.Employee.Oid : 0), AllDay = 1, Description = (r.Employee != null ? r.Employee.FullNameEn : ""), EndTime = r.EndDate, Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = (r.StartDate.ToString("hh:mm") == "00:00" ? r.StartDate.AddMinutes(59).AddHours(23) : r.StartDate), Status = "", Subject = (r.Employee != null ? r.Employee.FullNameEn : ""), EvenType = "", Employee = r.Employee });
            }
            if (chkLeaveApprove.Checked)
            {
                ds3 = object_Space.GetObjects <LeaveRequest>().Where(r => r.RequestStatus == RequestStatus.Approved).Select(r => new { Oid = (r.Employee != null ? r.Employee.Oid : 0), AllDay = 1, Description = (r.Employee != null ? r.Employee.FullNameEn : ""), EndTime = r.EndDate, Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = (r.StartDate.ToString("hh:mm") == "00:00" ? r.StartDate.AddMinutes(59).AddHours(23) : r.StartDate), Status = "", Subject = (r.Employee != null ? r.Employee.FullNameEn : ""), EvenType = "", Employee = r.Employee });
            }
            if (chkLeaveUnapproved.Checked)
            {
                ds4 = object_Space.GetObjects <LeaveRequest>().Where(r => r.RequestStatus != RequestStatus.Approved).Select(r => new { Oid = (r.Employee != null ? r.Employee.Oid : 0), AllDay = 1, Description = (r.Employee != null ? r.Employee.FullNameEn : ""), EndTime = r.EndDate, Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = (r.StartDate.ToString("hh:mm") == "00:00" ? r.StartDate.AddMinutes(59).AddHours(23) : r.StartDate), Status = "", Subject = (r.Employee != null ? r.Employee.FullNameEn : ""), EvenType = "", Employee = r.Employee });
            }
            if (chkLeaveApprove.Checked)
            {
                ds5 = object_Space.GetObjects <LeaveAmendmentRequest>().Where(r => r.RequestStatus == RequestStatus.Approved).Select(r => new { Oid = (r.Employee != null ? r.Employee.Oid : 0), AllDay = 1, Description = (r.Employee != null ? r.Employee.FullNameEn : ""), EndTime = r.EndDate, Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = (r.StartDate.ToString("hh:mm") == "00:00" ? r.StartDate.AddMinutes(59).AddHours(23) : r.StartDate), Status = "", Subject = (r.Employee != null ? r.Employee.FullNameEn : ""), EvenType = "", Employee = r.Employee });
            }
            if (chkLeaveUnapproved.Checked)
            {
                ds6 = object_Space.GetObjects <LeaveAmendmentRequest>().Where(r => r.RequestStatus != RequestStatus.Approved).Select(r => new { Oid = (r.Employee != null ? r.Employee.Oid : 0), AllDay = 1, Description = (r.Employee != null ? r.Employee.FullNameEn : ""), EndTime = r.EndDate, Label = "", Location = "", recurrenceinfo = 0, reminderinfo = 0, firmId = 1, StartTime = (r.StartDate.ToString("hh:mm") == "00:00" ? r.StartDate.AddMinutes(59).AddHours(23) : r.StartDate), Status = "", Subject = (r.Employee != null ? r.Employee.FullNameEn : ""), EvenType = "", Employee = r.Employee });
            }
            //String sqlAppointment = "SELECT leave.Oid,0 as AllDay,emp.FullNameEn as Description, (CAST(EndDate as Datetime) + CAST(EndTime as Time)) as EndTime, '' as Label, '' as Location,0 as recurrenceinfo,0 as reminderinfo, 1 as firmId,(CAST(StartDate as Datetime) + CAST(StartTime as Time)) as StartTime,'' as Status,emp.FullNameEn as Subject,'' as EvenType  FROM [EmployeeAbsence] leave inner join [Employee] emp on leave.Employee = emp.Oid";
            //if (cmbEmployee.Text != "" || cmbDepartment.Text != "")
            //    sqlAppointment += " where ";
            //if (cmbEmployee.Text != "")
            //    sqlAppointment += " emp.FullNameEn='"+ cmbEmployee.Text + "' ";
            //if (cmbDepartment.Text != "")
            //    sqlAppointment += " emp.FullNameEn='" + cmbDepartment.Text + "' ";
            //XPDataView xpDataView1 = new XPDataView();
            //xpDataView1.AddProperty("Oid", typeof(int));
            //xpDataView1.AddProperty("AllDay", typeof(string));
            //xpDataView1.AddProperty("Description", typeof(string));
            //xpDataView1.AddProperty("EndTime", typeof(DateTime));
            //xpDataView1.AddProperty("Label", typeof(string));
            //xpDataView1.AddProperty("Location", typeof(string));
            //xpDataView1.AddProperty("recurrenceinfo", typeof(int));
            //xpDataView1.AddProperty("reminderinfo", typeof(int));
            //xpDataView1.AddProperty("firmId", typeof(int));
            //xpDataView1.AddProperty("StartTime", typeof(DateTime));
            //xpDataView1.AddProperty("Status", typeof(string));
            //xpDataView1.AddProperty("Subject", typeof(string));
            //xpDataView1.AddProperty("EvenType", typeof(string));
            //xpDataView1.LoadData(currentSession.ExecuteQuery(sqlAppointment));
            //ASPxScheduler1.AppointmentDataSource = xpDataView1;
            ds = ds.Union(ds1).Union(ds2).Union(ds3).Union(ds4).Union(ds5).Union(ds6);
            if (cmbEmployee.Text != "" && cmbEmployee.Text != null)
            {
                ds = ds.Where(x => (x.Employee != null?x.Employee.FullNameEn:"") == cmbEmployee.Text);
            }
            if (cmbDepartment.Text != "" && cmbDepartment.Text != null)
            {
                ds = ds.Where(x => (x.Employee != null ? (x.Employee.CurrentDepartment != null ? x.Employee.CurrentDepartment.NameEn :""):"") == cmbDepartment.Text);
            }
            ASPxScheduler1.AppointmentDataSource = ds;
            XPDataView xpDataView2 = new XPDataView();
            String     sqlResource = "SELECT 1 as Id, 'Test' as Model";

            xpDataView2.AddProperty("Id", typeof(string));
            xpDataView2.AddProperty("Model", typeof(string));
            xpDataView2.LoadData(currentSession.ExecuteQuery(sqlResource));
            ASPxScheduler1.ResourceDataSource = xpDataView2;
            ASPxScheduler1.DataBind();
        }
        protected XPDataView AtualizarGrid()
        {
            Control.UsuariosControle ctrlUsu = new Control.UsuariosControle();

            string usuarioDefault = ctrlUsu.GetUsuarioByLogin(HttpContext.Current.Session["pUId"].ToString()).UsuarioID.ToString();

            Control.PerfilCtrl perfil = new Control.PerfilCtrl();

            bool perfilConsultaGeral = perfil.getPermissaoConsultaProcessos(usuarioDefault);

            perfil = null;

            string dataIni = string.Empty;
            string dataFim = string.Empty;
            string envolvido = string.Empty;
            string procNum = string.Empty;
            string undorca = string.Empty;

            string sqlTable = "select " +
                                "CTRCOD," +
                                "CTRNUMERO," +
                                "CTRPESSOA," +
                                "CTRCONTROLEPORUO," +
                                "PESNOME," +
                                "UO," +
                                "adcobjeto ," +
                                "adcinicio," +
                                "adcfinal," +
                                "licnumero," +
                                "liccod," +
                                "MODALIDADE," +
                                "IDMODALIDADE, " +
                                "VALORCONTRATO, " +
                                "SALDOCONTRATO, " +
                                "SITUACAOADITIVOS " +
                                "from VW_FLX_CONTRATOS ";

            if (dtCadastroIni.Value != null)
            {
                dataIni = "'" + DateTime.Parse(dtCadastroIni.Value.ToString()).ToShortDateString().Replace("/", ".") + "' ";
            }
            else
            {
                dataIni = "ADCINICIO";
            }

            if (dtCadastroFim.Value != null)
            {
                dataFim = "'" + DateTime.Parse(dtCadastroFim.Value.ToString()).ToShortDateString().Replace("/", ".") + "' ";
            }
            else
            {
                dataFim = "ADCFINAL";
            }

            if (cbbEnvolvido.Value != null)
            {
                envolvido = cbbEnvolvido.Value.ToString();
            }
            else
            {
                envolvido = "CTRPESSOA ";
            }

            if (txtNumProcesso.Value != null)
            {
                procNum = " like '" + txtNumProcesso.Value.ToString() + "%'";
            }
            else
            {
                procNum = " = CTRNUMERO ";
            }

            if (cbbUndOrc.Value != null)
            {
                undorca = "= '" + cbbUndOrc.Value.ToString() + "' ";
            }
            else
            {

                if (!perfilConsultaGeral)
                {
                    string idUndFunc = Control.UndFuncionalCtrl.getUndFuncByUsuID(usuarioDefault).ToString();
                    string undOrcCad = Control.UnidadeOrcamentariaCtrl.GetUnidadeOrcamentriaByUnidadeFuncional(idUndFunc).undUnificado;

                    undorca = "in (    SELECT UO.undunificado " +
                        "    FROM unidadeorcamentaria UO " +
                        "    JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                        "    WHERE " +
                        "    uog.uogunidadegestora = " +
                        "    (SELECT UOG.uogunidadegestora " +
                        "        FROM unidadeorcamentaria UO " +
                        "        JOIN und_orc_und_ges UOG ON UOG.uogorgao = UO.undcodorgao AND UOG.uogunidade = UO.undcodigo " +
                        "        where uo.undunificado = '" + undOrcCad + "' " +
                        "    ) ) ";
                }
                else
                {
                    undorca = "= UO ";
                }
            }

            ////usuarioEnvio = "USUARIOENVIO";

            sqlTable += " WHERE ";
            sqlTable += " (";
            sqlTable += " (ADCFINAL >= " + dataFim + ") ";
            sqlTable += " AND ";
            sqlTable += " (ADCINICIO <= " + dataIni + ")";
            sqlTable += " ) ";
            sqlTable += " AND ";
            sqlTable += " CTRPESSOA = " + envolvido;
            sqlTable += " AND ";
            sqlTable += " CTRNUMERO " + procNum;
            sqlTable += " AND ";
            sqlTable += " UO " + undorca;

            DevExpress.Xpo.Session xpoSession = new DevExpress.Xpo.Session();
            XpoDataSource1.Session = xpoSession;

            XpoDataSource1.Session.Connection = Persist.GetConn.getConn();

            var resultSet = xpoSession.ExecuteQuery(sqlTable);
            var dv = new XPDataView();

            dv.AddProperty("CTRCOD", typeof(int));
            dv.AddProperty("CTRNUMERO", typeof(string));
            dv.AddProperty("CTRPESSOA", typeof(DateTime));
            dv.AddProperty("CTRCONTROLEPORUO", typeof(string));
            dv.AddProperty("PESNOME", typeof(string));
            dv.AddProperty("UO", typeof(int));
            dv.AddProperty("ADCOBJETO", typeof(string));
            dv.AddProperty("ADCINICIO", typeof(string));
            dv.AddProperty("ADCFINAL", typeof(string));
            dv.AddProperty("LICNUMERO", typeof(int));
            dv.AddProperty("LICCOD", typeof(int));
            dv.AddProperty("MODALIDADE", typeof(decimal));
            dv.AddProperty("IDMODALIDADE", typeof(decimal));
            dv.AddProperty("VALORCONTRATO", typeof(decimal));
            dv.AddProperty("SALDOCONTRATO", typeof(decimal));
            dv.AddProperty("SITUACAOADITIVOS", typeof(string));

            dv.LoadData(resultSet);

            ASPxGridView1.DataSource = dv;
            Session["DTable"] = dv;
            ASPxGridView1.DataBind();

            return dv;
        }