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