Beispiel #1
0
        public static bool CodigoExiste(string qryStr)
        {
            try
            {
                OpenConection();
                command = new FbCommand(qryStr, conexao);
                Reader = command.ExecuteReader();

                if(Reader.HasRows)
                    FlExiste= true;
                else
                    FlExiste= false;

                Reader.Close();

            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message.ToString());
            }
            finally
            {
                CloseConection();
            }
            return FlExiste;
        }
 public int GetRowCountOfTable(string table)
 {
     string query = "SELECT count(*) FROM " + Database + "." + table;
     cmd = new FbCommand(query, connection);
     reader = cmd.ExecuteReader();
     reader.Read();
     int cnt = reader.GetInt32(0);
     reader.Close();
     return cnt;
 }
        /// <summary>
        /// Lists tables 
        /// </summary>
        /// <returns>List of tables matching tablename-spec</returns>
        public List<string> GetListOfTables()
        {
            List<string> tableList = new List<string>();

            cmd = new FbCommand("select rdb$relation_name from rdb$relations where rdb$view_blr is null and (rdb$system_flag is null or rdb$system_flag = 0);", connection);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tableList.Add(reader.GetString(0));
            }
            reader.Close();

            return tableList;
        }
Beispiel #4
0
        public LIS_HISTORPROCESSOCollection ReadCollection()
        {
            FbDataReader dataReader = null;

            try
            {
                LIS_HISTORPROCESSOCollection collection = null;

                //Verificando a existência de um transação aberta
                if (dbTransaction != null)
                {
                    if (dbCnn.State == ConnectionState.Closed)
                    {
                        dbCnn.Open();
                    }

                    dbCommand             = new FbCommand("SELECT * FROM LIS_HISTORPROCESSO", dbCnn);
                    dbCommand.Transaction = ((FbTransaction)(dbTransaction));
                }
                else
                {
                    if (dbCnn == null)
                    {
                        dbCnn = ((FbConnection)GetConnectionDB());
                    }

                    if (dbCnn.State == ConnectionState.Closed)
                    {
                        dbCnn.Open();
                    }

                    dbCommand             = new FbCommand("SELECT * FROM LIS_HISTORPROCESSO", dbCnn);
                    dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
                }

                // Tipo do comando de banco Procedure ou SQL
                dbCommand.CommandType = CommandType.Text;

                collection = ExecuteReader(ref collection, ref dataReader, dbCommand);

                if (dataReader != null)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }

                if (dbTransaction == null)
                {
                    dbCommand.Transaction.Commit();
                    dbCnn.Close();
                }

                return(collection);
            }
            catch (Exception ex)
            {
                // Deleta reader
                if (dataReader != null)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }

                if (dbTransaction != null)
                {
                    this.RollbackTransaction();
                }
                else
                {
                    if (dbCommand.Transaction != null)
                    {
                        dbCommand.Transaction.Rollback();
                    }
                    if (dbCnn.State == ConnectionState.Open)
                    {
                        dbCnn.Close();
                    }
                }

                throw ex;
            }
        }
Beispiel #5
0
 public void CloseReader()
 {
     dr.Close();
 }
Beispiel #6
0
        public ORCAMENTOCollection ReadCollectionByParameter(List <RowsFiltro> RowsFiltro, string FieldOrder)
        {
            FbDataReader        dataReader = null;
            ORCAMENTOCollection collection = null;

            string strSqlCommand = String.Empty;

            try
            {
                if (RowsFiltro != null)
                {
                    if (RowsFiltro.Count > 0)
                    {
                        strSqlCommand = "SELECT * FROM ORCAMENTO WHERE (";

                        ArrayList _rowsFiltro = new ArrayList();
                        RowsFiltro.ForEach(delegate(RowsFiltro i)
                        {
                            string[] item = { i.Condicao.ToString(), i.Campo.ToString(), i.Tipo.ToString(), i.Operador.ToString(), i.Valor.ToString() };
                            _rowsFiltro.Add(item);
                        });

                        int _count = 1;
                        foreach (string[] item in _rowsFiltro)
                        {
                            strSqlCommand += "(" + item[1] + " " + item[3];
                            switch (item[2])
                            {
                            case ("System.String"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " '" + item[4] + "')";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Int16"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " " + item[4] + ")";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Int32"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " " + item[4] + ")";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Int64"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " " + item[4] + ")";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Double"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " " + item[4] + ")";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Decimal"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " " + item[4] + ")";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Float"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " " + item[4] + ")";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Byte"):
                                strSqlCommand += " " + item[4] + ")";
                                break;

                            case ("System.SByte"):
                                strSqlCommand += " " + item[4] + ")";
                                break;

                            case ("System.Char"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " '" + item[4] + "')";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.DateTime"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " '" + item[4] + "')";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Guid"):
                                if (item[3].ToUpper() != "LIKE")
                                {
                                    strSqlCommand += " '" + item[4] + "')";
                                }
                                else
                                {
                                    strSqlCommand += " '%" + item[4] + "%')";
                                }
                                break;

                            case ("System.Boolean"):
                                strSqlCommand += " " + item[4] + ")";
                                break;
                            }
                            if (_rowsFiltro.Count > 1)
                            {
                                if (_count < _rowsFiltro.Count)
                                {
                                    strSqlCommand += " " + item[0] + " ";
                                }
                                _count++;
                            }
                        }
                        strSqlCommand += ")  order by  " + FieldOrder;
                    }
                    else
                    {
                        strSqlCommand = "SELECT * FROM ORCAMENTO  order by  " + FieldOrder;
                    }
                }
                else
                {
                    strSqlCommand = "SELECT * FROM ORCAMENTO  order by " + FieldOrder;
                }

                //Verificando a existência de um transação
                if (dbTransaction != null)
                {
                    if (dbCnn.State == ConnectionState.Closed)
                    {
                        dbCnn.Open();
                    }

                    dbCommand             = new FbCommand(strSqlCommand, dbCnn);
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.Transaction = ((FbTransaction)(dbTransaction));
                }
                else
                {
                    if (dbCnn == null)
                    {
                        dbCnn = new FbConnection(connectionString);
                    }

                    if (dbCnn.State == ConnectionState.Closed)
                    {
                        dbCnn.Open();
                    }

                    dbCommand             = new FbCommand(strSqlCommand, dbCnn);
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
                }


                collection = ExecuteReader(ref collection, ref dataReader, dbCommand);

                if (dataReader != null)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }

                if (dbTransaction == null)
                {
                    dbCommand.Transaction.Commit();
                    dbCnn.Close();
                }

                return(collection);
            }
            catch (Exception ex)
            {
                // Deleta reader
                if (dataReader != null)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }

                if (dbTransaction != null)
                {
                    this.RollbackTransaction();
                }
                else
                {
                    if (dbCommand.Transaction != null)
                    {
                        dbCommand.Transaction.Rollback();
                    }
                    if (dbCnn.State == ConnectionState.Open)
                    {
                        dbCnn.Close();
                    }
                }

                throw ex;
            }
        }
Beispiel #7
0
        public ORCAMENTOEntity Read(int IDORCAMENTO)
        {
            FbDataReader reader = null;

            try
            {
                //Verificando a existência de um transação aberta
                if (dbTransaction != null)
                {
                    if (dbCnn.State == ConnectionState.Closed)
                    {
                        dbCnn.Open();
                    }

                    dbCommand             = new FbCommand("Rea_ORCAMENTO", dbCnn);
                    dbCommand.Transaction = ((FbTransaction)(dbTransaction));
                }
                else
                {
                    if (dbCnn == null)
                    {
                        dbCnn = ((FbConnection)GetConnectionDB());
                    }

                    if (dbCnn.State == ConnectionState.Closed)
                    {
                        dbCnn.Open();
                    }

                    dbCommand             = new FbCommand("Rea_ORCAMENTO", dbCnn);
                    dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
                }

                dbCommand.CommandType = CommandType.StoredProcedure;

                dbCommand.Parameters.AddWithValue("@IDORCAMENTO", IDORCAMENTO);                //PrimaryKey


                reader = dbCommand.ExecuteReader();

                ORCAMENTOEntity entity = null;
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        entity = FillEntityObject(ref reader);
                    }
                }

                // Deleta reader
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }

                // Fecha conexão
                if (dbTransaction == null)
                {
                    dbCommand.Transaction.Commit();
                    if (dbCnn.State == ConnectionState.Open)
                    {
                        dbCnn.Close();
                    }
                }

                return(entity);
            }
            catch (Exception ex)
            {
                // Deleta reader
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }

                if (dbTransaction != null)
                {
                    this.RollbackTransaction();
                }
                else
                {
                    if (dbCommand.Transaction != null)
                    {
                        dbCommand.Transaction.Rollback();
                    }
                    if (dbCnn.State == ConnectionState.Open)
                    {
                        dbCnn.Close();
                    }
                }

                throw ex;
            }
        }
Beispiel #8
0
        public bool Gera(char tipo, string arquivo, string titulo, bool idt_inicial, DateTime data_inicial, bool idt_final, DateTime data_final, bool pagos, bool abertos)
        {
            FluxoCaixa fluxo = new FluxoCaixa();

            /*
             * PDF pdf = new PDF(arquivo);
             * pdf.Abre();
             * fluxo.Parte1(pdf, titulo, idt_inicial, data_inicial, idt_final, data_final);
             */

            FileStream fs     = new FileStream(arquivo, FileMode.Create);
            Document   doc    = new Document(PageSize.LETTER.Rotate());
            PdfWriter  writer = PdfWriter.GetInstance(doc, fs);

            doc.Open();
            PdfContentByte buf = writer.DirectContent;

            string periodo = "";

            if (idt_inicial || idt_final)
            {
                periodo = "Período: ";
                if (idt_inicial && idt_final)
                {
                    periodo += "de " + data_inicial.ToString("dd/MM/yyyy") + " até " + data_final.ToString("dd/MM/yyyy");
                }
                else
                {
                    if (idt_inicial)
                    {
                        periodo += "a partir de " + data_inicial.ToString("dd/MM/yyyy");
                    }
                    else
                    {
                        periodo += "até " + data_final.ToString("dd/MM/yyyy");
                    }
                }
            }

            if (pagos && !abertos)
            {
                Graficos.Cabecalho(doc, titulo + "- Pagos", periodo);
            }
            else
            if (!pagos && abertos)
            {
                Graficos.Cabecalho(doc, titulo + "- Abertos", periodo);
            }
            else
            {
                Graficos.Cabecalho(doc, titulo, periodo);
            }


            string where;
            string where2 = "";
            string col_cod;
            string col_per;

            if (tipo == 'v')
            {
                col_cod = "O.COD_VENDEDOR";
                col_per = "P.PER_VENDEDOR";
                if (pagos && abertos)
                {
                    where = "where P.DAT_PEDIDO is not null ";
                }
                else
                if (pagos)
                {
                    where = "where P.IDT_VENDEDOR = 'S' and P.DAT_PEDIDO is not null ";
                }
                else
                {
                    where = "where P.IDT_VENDEDOR <> 'S' and P.DAT_PEDIDO is not null ";
                }
            }
            else
            if (tipo == 'c')
            {
                col_cod = "O.COD_CONSULTOR";
                col_per = "P.PER_CONSULTOR";
                if (pagos && abertos)
                {
                    where = "where P.DAT_PEDIDO is not null ";
                }
                else
                if (pagos)
                {
                    where = "where P.IDT_CONSULTOR = 'S' and P.DAT_PEDIDO is not null ";
                }
                else
                {
                    where = "where P.IDT_CONSULTOR <> 'S' and P.DAT_PEDIDO is not null ";
                }
            }
            else
            {
                col_cod = "P.COD_FORNECEDOR";
                col_per = "0";
                where   = "where P.COD_PEDIDO = '1' and P.DAT_PEDIDO is not null ";
                where2  = "where P.COD_PEDIDO = '2' and P.DAT_PEDIDO is not null ";
            }
            if (idt_inicial || idt_final)
            {
                if (idt_inicial && idt_final)
                {
                    where  += "and (P.DAT_PEDIDO between '" + data_inicial.ToString("M/d/yyyy") + "' and '" + data_final.ToString("M/d/yyyy") + "') ";
                    where2 += "and (P.DAT_PEDIDO between '" + data_inicial.ToString("M/d/yyyy") + "' and '" + data_final.ToString("M/d/yyyy") + "') ";
                }
                else
                {
                    if (idt_inicial)
                    {
                        where  += "and (P.DAT_PEDIDO >= '" + data_inicial.ToString("M/d/yyyy") + "') ";
                        where2 += "and (P.DAT_PEDIDO >= '" + data_inicial.ToString("M/d/yyyy") + "') ";
                    }
                    else
                    {
                        where  += "and (P.DAT_PEDIDO <= '" + data_final.ToString("M/d/yyyy") + "') ";
                        where2 += "and (P.DAT_PEDIDO <= '" + data_final.ToString("M/d/yyyy") + "') ";
                    }
                }
            }
            string sql =
                "select " +
                col_cod + "," +
                "P.VLR_PEDIDO," +
                col_per + "," +
                "O.PER_CONSULTOR as PER_CONSULTOR_ORC," +
                "O.VLR_ORCAMENTO," +
                "O.VLR_DESCONTO," +
                "O.COD_CARACTERISTICA," +
                "C.PER_LIMIAR," +
                "C.PER_FILIAL," +
                "O.COD_FORNECEDOR," +
                "P.VLR_FRETE," +
                "C.PER_FRETE," +
                "P.DAT_ORCAMENTO," +
                "P.COD_ORCAMENTO," +
                "P.COD_PEDIDO " +
                "from PEDIDOS P " +
                "inner join ORCAMENTOS O " +
                "on O.COD_FORNECEDOR=P.COD_FORNECEDOR and O.DAT_ORCAMENTO=P.DAT_ORCAMENTO and O.COD_ORCAMENTO=P.COD_ORCAMENTO " +
                "inner join CARACTERISTICAS C " +
                "on C.COD_FORNECEDOR=O.COD_FORNECEDOR and C.COD_CARACTERISTICA=O.COD_CARACTERISTICA " +
                where;

            if (tipo == 'f')
            {
                sql = sql +
                      "union " +
                      "select " +
                      "'SERVICO-' || P.COD_FORNECEDOR," +
                      "P.VLR_PEDIDO," +
                      col_per + "," +
                      "O.PER_CONSULTOR as PER_CONSULTOR_ORC, " +
                      "O.VLR_ORCAMENTO," +
                      "O.VLR_DESCONTO," +
                      "O.COD_CARACTERISTICA," +
                      "C.PER_LIMIAR," +
                      "C.PER_FILIAL," +
                      "O.COD_FORNECEDOR," +
                      "P.VLR_FRETE," +
                      "C.PER_FRETE," +
                      "P.DAT_ORCAMENTO," +
                      "P.COD_ORCAMENTO," +
                      "P.COD_PEDIDO " +
                      "from PEDIDOS P " +
                      "inner join ORCAMENTOS O " +
                      "on O.COD_FORNECEDOR=P.COD_FORNECEDOR and O.DAT_ORCAMENTO=P.DAT_ORCAMENTO and O.COD_ORCAMENTO=P.COD_ORCAMENTO " +
                      "inner join CARACTERISTICAS C " +
                      "on C.COD_FORNECEDOR=O.COD_FORNECEDOR and C.COD_CARACTERISTICA=O.COD_CARACTERISTICA " +
                      where2;
            }
            sql = sql +
                  "order by 1";
            StreamWriter log = new StreamWriter("gerencial.log");

            log.WriteLine(sql);
            log.Flush();
            FbCommand    cmd    = new FbCommand(sql, Globais.bd);
            FbDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);

            string    codigo;
            float     valor;
            float     percentual;
            string    ultimo     = "";
            float     total      = 0;
            ArrayList valores    = new ArrayList();
            ArrayList descricoes = new ArrayList();

            VendedorConsultor vendedor_consultor = null;
            ArrayList         lista     = new ArrayList();
            cOrcamentos       orcamento = new cOrcamentos();
            cComissaoLimiar   comissao  = new cComissaoLimiar();
            cCaracteristicas  carac     = new cCaracteristicas();

            while (reader.Read())
            {
                codigo     = reader.GetString(0);
                valor      = reader.GetFloat(1);
                percentual = reader.GetFloat(2);

                float  vlr_itens      = reader.GetFloat(4);
                float  vlr_desconto   = reader.GetFloat(5);
                string caracteristica = reader.GetString(6);
                float  limiar         = reader.GetFloat(7);
                string fornecedor     = reader.GetString(9);

                float    vlr_frete      = reader.IsDBNull(10) ? 0 : reader.GetFloat(10);
                float    per_frete      = reader.IsDBNull(11) ? 0 : reader.GetFloat(11);
                DateTime dat_orcamento  = reader.GetDateTime(12);
                short    cod_orcamento  = reader.GetInt16(13);
                short    cod_pedido     = reader.GetInt16(14);
                float    vlr_frete_item = 0;
                if (vlr_frete > 0)
                {
                    vlr_frete_item = cPedidos.RateiaFrete(fornecedor, dat_orcamento, cod_orcamento, cod_pedido, ref vlr_frete);
                }

                string venpro = "", venser = "", conpro = "", conser = "", filpro = "", filser = "";
                carac.FormulasComissao(fornecedor, caracteristica, ref venpro, ref venser, ref conpro, ref conser, ref filpro, ref filser);

                if (percentual == 0)
                {
                    if (tipo == 'v')
                    {
                        if (codigo.StartsWith("SERVICO-"))
                        {
                            //Globais.CalculaFormula(ref valor, venser, 0, per_frete, vlr_frete_item);
                        }
                        else
                        {
                            //Globais.CalculaFormula(ref valor, venpro, 0, per_frete, vlr_frete_item);
                        }
                        percentual = reader.GetFloat(3);                         // consultor
                        float vlr_orcamento = vlr_itens - vlr_desconto;
                        float sinal         = orcamento.CalculaSinal(fornecedor, caracteristica, vlr_itens, vlr_desconto, percentual, limiar);
                        percentual = comissao.Calcula(fornecedor, caracteristica, valor, sinal);
                    }
                    else
                    if (tipo == 'c')
                    {
                        if (codigo.StartsWith("SERVICO-"))
                        {
                            //Globais.CalculaFormula(ref valor, conser, 0, per_frete, vlr_frete_item);
                        }
                        else
                        {
                            //Globais.CalculaFormula(ref valor, conpro, 0, per_frete, vlr_frete_item);
                        }
                        percentual = reader.GetFloat(3);
                    }
                    else
                    {
                        if (codigo.StartsWith("SERVICO-"))
                        {
                            //Globais.CalculaFormula(ref valor, filser, 0, per_frete, vlr_frete_item);
                            percentual = 100;
                        }
                        else
                        {
                            //Globais.CalculaFormula(ref valor, filpro, 0, per_frete, vlr_frete_item);
                            percentual = reader.GetFloat(8);
                        }
                    }
                }
                if (!codigo.Equals(ultimo))
                {
                    if (!ultimo.Equals(""))
                    {
                        lista.Add(vendedor_consultor);
                    }
                    ultimo                        = codigo;
                    vendedor_consultor            = new VendedorConsultor();
                    vendedor_consultor.codigo     = codigo;
                    vendedor_consultor.vendas     = 0;
                    vendedor_consultor.percentual = 0;
                    vendedor_consultor.n          = 0;
                }
                vendedor_consultor.vendas     += valor;
                vendedor_consultor.percentual += percentual;
                if (percentual > 0.01)
                {
                    vendedor_consultor.n++;
                }
                total += valor;
                log.WriteLine(valor.ToString() + " " + total.ToString());
                log.Flush();
            }
            log.Close();
            if (!ultimo.Equals(""))
            {
                lista.Add(vendedor_consultor);
            }
            reader.Close();

            foreach (VendedorConsultor vc in lista)
            {
                valores.Add(vc.vendas);
                descricoes.Add(vc.codigo);
            }

            //if (valores.Count > 12)
            //{
            ArrayList valores12    = new ArrayList();
            ArrayList descricoes12 = new ArrayList();
            int       max          = valores.Count > 12 ? 11 : valores.Count;

            //for (int i=0; i<11; i++)
            for (int i = 0; i < max; i++)
            {
                float maior = -1;
                int   v = 0, imaior = -1;
                foreach (float vlr in valores)
                {
                    if (vlr > maior)
                    {
                        maior  = vlr;
                        imaior = v;
                    }
                    v++;
                }
                float  vmaior = float.Parse(valores[imaior].ToString());
                string dmaior = descricoes[imaior].ToString();
                valores12.Add(vmaior);
                descricoes12.Add(dmaior);
                valores.RemoveAt(imaior);
                descricoes.RemoveAt(imaior);
            }
            if (valores.Count > 12)
            {
                float voutros = 0;
                foreach (float vlr in valores)
                {
                    voutros += vlr;
                }
                valores12.Add(voutros);
                descricoes12.Add("OUTROS");
            }
            Graficos.Pizza(buf, valores12, descricoes12, 200, 250, 120);
            //}
            //else
            //Graficos.Pizza(buf, valores, descricoes, 200, 250, 120);
            doc.NewPage();

            Tabela table = new Tabela(tipo == 'f' ? 3 : 4);

            if (tipo == 'v')
            {
                fluxo.AdicionaCelula(table, "Vendedor", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            }
            else
            if (tipo == 'c')
            {
                fluxo.AdicionaCelula(table, "Consultor", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            }
            else
            {
                fluxo.AdicionaCelula(table, "Fornecedor", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            }
            fluxo.AdicionaCelula(table, "Vendas", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, Element.ALIGN_RIGHT, 1);
            fluxo.AdicionaCelula(table, "% Total", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, Element.ALIGN_RIGHT, 1);
            if (tipo != 'f')
            {
                fluxo.AdicionaCelula(table, "Média Comissão", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, Element.ALIGN_RIGHT, 1);
            }
            foreach (VendedorConsultor vc in lista)
            {
                fluxo.AdicionaCelula(table, vc.codigo, BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 1);
                fluxo.AdicionaCelula(table, vc.vendas.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Element.ALIGN_RIGHT, 1);
                fluxo.AdicionaCelula(table, (vc.vendas * 100 / total).ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Element.ALIGN_RIGHT, 1);
                if (tipo != 'f')
                {
                    fluxo.AdicionaCelula(table, (vc.percentual / vc.n).ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Element.ALIGN_RIGHT, 1);
                }
            }

            fluxo.AdicionaCelula(table, "Total", BaseFont.HELVETICA_BOLD, 8, Color.GRAY, 1);
            fluxo.AdicionaCelula(table, total.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Color.GRAY, Element.ALIGN_RIGHT, 1);
            fluxo.AdicionaCelula(table, "", BaseFont.HELVETICA_BOLD, 8, Color.GRAY, tipo == 'f' ? 1 : 2);
            doc.Add(table);
            doc.Close();
            return(true);
        }
Beispiel #9
0
        public static decimal valorTabela(string qryStr)
        {
            try
            {
                OpenConection();
                command = new FbCommand(qryStr, conexao);
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    if (!Reader.IsDBNull(0))
                        valorEmpresa = Convert.ToDecimal(Reader[0]);
                    else
                        valorEmpresa = 0;
                }
                Reader.Close();

            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message.ToString());
            }
            finally
            {
                CloseConection();
            }
            return valorEmpresa;
        }
Beispiel #10
0
        public static int maxId(string qryStr)
        {
            try
            {
                OpenConection();
                command = new FbCommand(qryStr, conexao);
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    if (!Reader.IsDBNull(0))
                        maxrows = Convert.ToInt32(Reader[0]) + 1;
                    else
                        maxrows = 0;
                }
                Reader.Close();

            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message.ToString());
            }
            finally
            {
                CloseConection();
            }
            return maxrows;
        }
 public static void closeAll(FbConnection connection, FbCommand query, FbDataReader dataReader)
 {
     dataReader.Close();
     query.Dispose();
     connection.Close();
 }
    protected void Page_Load(object sender, EventArgs e)
    {
        string strGrupo = (string)Session["Grupo"];

        if (!strGrupo.Equals("ADMIN"))
        {
            lblFaturar.Visible = false;
            lblTecnico.Visible = false;
            DDLTecnico.Visible = false;
            DDLFaturar.Visible = false;
        }
        if (!IsPostBack)
        {
            DataTable Cliente = GetCliente();
            DDLCliente.DataSource     = Cliente;
            DDLCliente.DataMember     = "Cliente";
            DDLCliente.DataTextField  = "Nome";
            DDLCliente.DataValueField = "Id";
            DDLCliente.DataBind();

            DataTable Tecnico = GetTecnico();
            DDLTecnico.DataSource     = Tecnico;
            DDLTecnico.DataMember     = "Tecnico";
            DDLTecnico.DataTextField  = "Nome";
            DDLTecnico.DataValueField = "Id";
            DDLTecnico.DataBind();

            //DDLSistema.Items.Clear();

            //DataTable Sistema = GetSistema();
            //DDLSistema.DataSource = Sistema;
            //DDLSistema.DataMember = "Sistema";
            //DDLSistema.DataTextField = "Nome";
            //DDLSistema.DataValueField = "Id";
            //DDLSistema.DataBind();



            if (Request.QueryString["IdRel"].ToString() != String.Empty)
            {
                if (Request.QueryString["IdRel"].ToString() == "incluir")
                {
                    Label15.Text         = "Inclusão de novo Cadastro de Visita";
                    txtDataCadastro.Text = GetDataCorreta();
                    txtHoraInicio.Text   = "00:00";
                    txtHoraFinal.Text    = GetHoraCorreta();
                    txtHoraDesc.Text     = "00:00";
                    lblHoraTotal.Text    = "00:00";
                }
                else
                {
                    Label15.Text = "Alterar Cadastro de Visita: " + Request.QueryString["IdRel"].ToString();
                    string strCodRelatorio = Request.QueryString["IdRel"].ToString();

                    StringBuilder strSelect = new StringBuilder();

                    strSelect.Append("SELECT LANCREL.DT_REL, LANCREL.NM_CONTATO, ");
                    strSelect.Append("LANCREL.CD_CLIENTE, LANCREL.CD_OPERADO, ");
                    strSelect.Append("LANCREL.CD_SISTEMA, LANCREL.CD_TIPOREL, ");
                    strSelect.Append("LANCREL.ST_FATURA, LANCREL.HR_ENTRADA, ");
                    strSelect.Append("LANCREL.HR_DESCONTO, LANCREL.HR_TOTAL, LANCREL.HR_SAIDA, ");
                    strSelect.Append("LANCREL.NM_GUERRA, LANCREL.DS_OBS, ACESSO.NM_OPERADO, SISTEMA.DS_SISTEMA ");
                    strSelect.Append("FROM LANCREL ");
                    strSelect.Append("INNER JOIN ACESSO ON (ACESSO.CD_OPERADO = LANCREL.CD_OPERADO) ");
                    strSelect.Append("INNER JOIN SISTEMA ON (SISTEMA.CD_SISTEMA = LANCREL.CD_SISTEMA) ");
                    strSelect.Append("WHERE (NR_LANC = '" + strCodRelatorio + "')");

                    FbCommand cmdAutRel = (FbCommand)HlpBancoDados.CommandSelect(strSelect.ToString());
                    cmdAutRel.Connection.Open();

                    FbDataReader drRel = cmdAutRel.ExecuteReader();

                    if (drRel.Read())
                    {
                        txtDataCadastro.Text     = GetFormataDataRetorno(drRel["DT_REL"].ToString().Replace(".", "/"));
                        txtContato.Text          = drRel["NM_CONTATO"].ToString();
                        DDLCliente.SelectedValue = drRel["NM_GUERRA"].ToString();
                        DDLTecnico.SelectedValue = drRel["NM_OPERADO"].ToString();
                        DDLSistema.SelectedValue = drRel["DS_SISTEMA"].ToString();
                        if (drRel["CD_TIPOREL"].ToString().Equals("4"))
                        {
                            DDLTipoRelatoio.Text = "SOFTWARE";
                        }
                        else
                        {
                            DDLTipoRelatoio.Text = "HARDWARE";
                        }
                        if (drRel["ST_FATURA"].ToString().Equals("S"))
                        {
                            DDLFaturar.Text = "SIM";
                        }
                        else
                        {
                            DDLFaturar.Text = "NÃO";
                        }
                        txtHoraInicio.Text = drRel["HR_ENTRADA"].ToString();
                        txtHoraFinal.Text  = drRel["HR_SAIDA"].ToString();
                        txtHoraDesc.Text   = drRel["HR_DESCONTO"].ToString();
                        lblHoraTotal.Text  = drRel["HR_TOTAL"].ToString();

                        if (!drRel["DS_OBS"].ToString().Equals(String.Empty))
                        {
                            byte[] bt = (byte[])drRel["DS_OBS"];
                            txtMemo.Text = ASCIIEncoding.Default.GetString(bt);
                        }
                    }
                    drRel.Close();
                    cmdAutRel.Connection.Close();
                }
            }

            DDLTituloRel.Items.Clear();
            DDLTituloRel.Items.Add("MANUTENCAO");
            DDLTituloRel.Items.Add("ORCAMENTO");
            DDLTituloRel.Items.Add("GARANTIA");
            DDLTituloRel.Items.Add("OUTROS");

            DDLTituloRel.DataBind();
        }
    }
Beispiel #13
0
        public bool Gera(string arquivo, string titulo, float valor_inicial, bool idt_inicial, DateTime data_inicial, bool idt_final, DateTime data_final,
                         bool vencimento)
        {
            PDF pdf = new PDF(arquivo);

            pdf.Abre();
            Parte1(pdf, titulo, idt_inicial, data_inicial, idt_final, data_final);

            string whereP1 = "where P.DAT_PAGAMENTO is not null ";
            string whereR1 = "where R.DAT_RECEBIMENTO is not null ";
            string whereP2 = "where P.DAT_PAGAMENTO is null and P.DAT_VENCIMENTO is not null ";
            string whereR2 = "where R.DAT_RECEBIMENTO is null and R.DAT_VENCIMENTO is not null ";

            if (idt_inicial || idt_final)
            {
                if (idt_inicial && idt_final)
                {
                    whereP1 += "and (P.DAT_PAGAMENTO between '" + data_inicial.ToString("M/d/yyyy") + "' and '" + data_final.ToString("M/d/yyyy") + "') ";
                    whereR1 += "and (R.DAT_RECEBIMENTO between '" + data_inicial.ToString("M/d/yyyy") + "' and '" + data_final.ToString("M/d/yyyy") + "') ";
                    whereP2 += "and (P.DAT_VENCIMENTO between '" + data_inicial.ToString("M/d/yyyy") + "' and '" + data_final.ToString("M/d/yyyy") + "') ";
                    whereR2 += "and (R.DAT_VENCIMENTO between '" + data_inicial.ToString("M/d/yyyy") + "' and '" + data_final.ToString("M/d/yyyy") + "') ";
                }
                else
                {
                    if (idt_inicial)
                    {
                        whereP1 += "and (P.DAT_PAGAMENTO >= '" + data_inicial.ToString("M/d/yyyy") + "') ";
                        whereR1 += "and (R.DAT_RECEBIMENTO >= '" + data_inicial.ToString("M/d/yyyy") + "') ";
                        whereP2 += "and (P.DAT_VENCIMENTO >= '" + data_inicial.ToString("M/d/yyyy") + "') ";
                        whereR2 += "and (R.DAT_VENCIMENTO >= '" + data_inicial.ToString("M/d/yyyy") + "') ";
                    }
                    else
                    {
                        whereP1 += "and (P.DAT_PAGAMENTO <= '" + data_final.ToString("M/d/yyyy") + "') ";
                        whereR1 += "and (R.DAT_RECEBIMENTO <= '" + data_final.ToString("M/d/yyyy") + "') ";
                        whereP2 += "and (P.DAT_VENCIMENTO <= '" + data_final.ToString("M/d/yyyy") + "') ";
                        whereR2 += "and (R.DAT_VENCIMENTO <= '" + data_final.ToString("M/d/yyyy") + "') ";
                    }
                }
            }
            string sql =
                "select 'P'," +
                "P.DAT_PAGAMENTO," +
                "P.VLR_PAGO," +
                "P.COD_PARCEIRO," +
                "P.COD_TITULO," +
                "0," +
                "N.DES_NATUREZA," +
                "P.COD_FORMA," +
                "P.COD_DOC_ORIGEM," +
                "P.COD_DOC_GERADO," +
                "P.COD_FUNCIONARIO " +
                "from TITULOS_PAGAR P " +
                "inner join naturezas_pagamento N on N.COD_NATUREZA=P.COD_NATUREZA " +
                whereP1 +

                "union " +
                "select 'P'," +
                "P.DAT_VENCIMENTO," +
                "P.VLR_PAGO," +
                "P.COD_PARCEIRO," +
                "P.COD_TITULO," +
                "0," +
                "N.DES_NATUREZA," +
                "P.COD_FORMA," +
                "P.COD_DOC_ORIGEM," +
                "P.COD_DOC_GERADO," +
                "P.COD_FUNCIONARIO " +
                "from TITULOS_PAGAR P " +
                "inner join naturezas_pagamento N on N.COD_NATUREZA=P.COD_NATUREZA " +
                whereP2 +

                "union " +
                "select 'R'," +
                "R.DAT_RECEBIMENTO," +
                "R.VLR_RECEBIDO," +
                "R.COD_CLIENTE," +
                "R.NRO_NF," +
                "R.SEQ_TITULO," +
                "N.DES_NATUREZA," +
                "R.COD_FORMA," +
                "''," +
                "''," +
                "'' " +
                "from TITULOS_RECEBER R " +
                "inner join naturezas_recebimento N on N.COD_NATUREZA=R.COD_NATUREZA " +
                whereR1 +

                "union " +
                "select 'R'," +
                "R.DAT_VENCIMENTO," +
                "R.VLR_RECEBIDO," +
                "R.COD_CLIENTE," +
                "R.NRO_NF," +
                "R.SEQ_TITULO," +
                "N.DES_NATUREZA," +
                "R.COD_FORMA," +
                "''," +
                "''," +
                "'' " +
                "from TITULOS_RECEBER R " +
                "inner join naturezas_recebimento N on N.COD_NATUREZA=R.COD_NATUREZA " +
                whereR2 +

                "order by 2";
            FbCommand    cmd    = new FbCommand(sql, Globais.bd);
            FbDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);

            string   tipo;
            float    valor;
            float    debito;
            float    credito;
            float    total   = valor_inicial;
            float    tot_deb = 0;
            float    tot_cre = 0;
            DateTime data;
            string   parceiro;
            string   titulo_nf;
            string   gerado;
            string   funcionario;
            string   natureza;
            string   forma;
            Tabela   table = new Tabela(11);

            AdicionaCelula(table, "Saldo Inicial", BaseFont.HELVETICA_BOLD, 8, Color.GRAY, 10);
            AdicionaCelula(table, total.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Color.GRAY, Element.ALIGN_RIGHT, 1);
            AdicionaCelula(table, "Data", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            AdicionaCelula(table, "Doc Origem", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            AdicionaCelula(table, "Doc Gerado", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            AdicionaCelula(table, "Parceiro/Funcionário", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 2);
            AdicionaCelula(table, "Natureza", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 2);
            AdicionaCelula(table, "Forma Pagto", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, 1);
            AdicionaCelula(table, "Débito", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, Element.ALIGN_RIGHT, 1);
            AdicionaCelula(table, "Crédito", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, Element.ALIGN_RIGHT, 1);
            AdicionaCelula(table, "Saldo", BaseFont.HELVETICA_BOLD, 8, iTextSharp.text.Color.LIGHT_GRAY, Element.ALIGN_RIGHT, 1);

            while (reader.Read())
            {
                tipo        = reader.GetString(0);
                data        = reader.GetDateTime(1);
                valor       = reader.GetFloat(2);
                parceiro    = reader.GetString(3);
                natureza    = reader.GetString(6);
                forma       = reader.GetString(7);
                gerado      = reader.GetString(9);
                funcionario = reader.GetString(10);
                if (tipo.Equals("P"))
                {
                    total    -= valor;
                    tot_deb  += valor;
                    debito    = valor;
                    credito   = 0;
                    titulo_nf = reader.GetString(8);
                }
                else
                {
                    total    += valor;
                    tot_cre  += valor;
                    debito    = 0;
                    credito   = valor;
                    titulo_nf = reader.GetString(4).Trim() + "/" + reader.GetInt16(5).ToString();
                }
                AdicionaCelula(table, data.ToString("dd/MM/yyyy"), BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 1);
                AdicionaCelula(table, titulo_nf, BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 1);
                AdicionaCelula(table, gerado, BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 1);
                AdicionaCelula(table, parceiro.Trim() + funcionario, BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 2);
                AdicionaCelula(table, natureza, BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 2);
                AdicionaCelula(table, forma, BaseFont.HELVETICA, 8, Element.ALIGN_LEFT, 1);
                AdicionaCelula(table, debito.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Element.ALIGN_RIGHT, 1);
                AdicionaCelula(table, credito.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Element.ALIGN_RIGHT, 1);
                AdicionaCelula(table, total.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Element.ALIGN_RIGHT, 1);
            }
            AdicionaCelula(table, "Saldo Final", BaseFont.HELVETICA_BOLD, 8, Color.GRAY, 8);
            AdicionaCelula(table, tot_deb.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Color.GRAY, Element.ALIGN_RIGHT, 1);
            AdicionaCelula(table, tot_cre.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Color.GRAY, Element.ALIGN_RIGHT, 1);
            AdicionaCelula(table, total.ToString("#,###,##0.00"), BaseFont.HELVETICA, 8, Color.GRAY, Element.ALIGN_RIGHT, 1);
            pdf.doc.Add(table);
            reader.Close();
            pdf.Fecha();
            return(true);
        }
Beispiel #14
0
        public List <PH.Entidades.SP_CALL_COMBO> SP_CALL_COMBO(string IdProducto)
        {
            PH.Entidades.SP_CALL_COMBO        cm    = null;
            List <PH.Entidades.SP_CALL_COMBO> lista = new List <Entidades.SP_CALL_COMBO>();

            using (FirebirdSql.Data.FirebirdClient.FbConnection cn = new FbConnection(ConfigurationManager.ConnectionStrings["PHConnection"].ConnectionString))
            {
                try
                {
                    cn.Open();
                    string spName = "Select * FROM SP_CALL_COMBO p where p.IDPROMO = ?";
                    // declare command
                    FbCommand readCommand =
                        new FbCommand(spName, cn);
                    // new FbCommand("Select * From " + spName + "(@IDSucursal,@Header,@Details,@Cliente,@IdPedido)", cn);
                    readCommand.Parameters.Add(new FbParameter("@IDPROMO", IdProducto));

                    FbDataReader myreader = readCommand.ExecuteReader();
                    while (myreader.Read())
                    {
                        // load the combobox with the names of the people inside.
                        // myreader[0] reads from the 1st Column
                        //DeleteComboBox.Items.Add(myreader[0]);

                        cm                     = new PH.Entidades.SP_CALL_COMBO();
                        cm.IDPROMO             = myreader.GetString(0);
                        cm.PROMO               = myreader.GetString(1);
                        cm.IDDEFINICION_PROMO  = myreader.GetInt32(2);
                        cm.DEFINICION_PROMO    = myreader.GetString(3);
                        cm.CANTIDAD_DEFINICION = myreader.GetInt32(4);
                        cm.IDPRODUCTO_CMB      = myreader.GetInt32(5);
                        cm.IDPRODUCTO          = myreader.GetString(6);
                        cm.PRODUCTO            = myreader.GetString(7);
                        if (!myreader.IsDBNull(8))
                        {
                            cm.CANTIDAD = myreader.GetDecimal(8);
                        }
                        else
                        {
                            cm.CANTIDAD = 0;
                        }

                        cm.PREDETERMINADO = myreader.GetInt32(9);

                        if (!myreader.IsDBNull(10))
                        {
                            cm.ESTADO = myreader.GetInt32(10);
                        }
                        if (!myreader.IsDBNull(11))
                        {
                            cm.COMBINACIONES = myreader.GetInt32(11);
                        }
                        if (!myreader.IsDBNull(12))
                        {
                            cm.AGRANDADO = myreader.GetString(12);
                        }
                        if (!myreader.IsDBNull(13))
                        {
                            cm.IDART_COSTO_AGRANDADO = myreader.GetString(13);
                        }


                        lista.Add(cm);
                    }
                    myreader.Close();                     // we are done with the reader
                }
                catch (Exception x)
                {
                    //MessageBox.Show(x.Message);
                    throw x;
                }
                finally
                {
                    cn.Close();
                }
            }

            return(lista);
        }