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; }
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; } }
public void CloseReader() { dr.Close(); }
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; } }
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; } }
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); }
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; }
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(); } }
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); }
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); }