public List <MetodoPagamento> ListarMetodoPagamento(int idVendedor)
{
Abrirconexao();
using (Cmd = new SqlCommand("ListarMetPgtoVendedor", Con))
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@IdUsuario", idVendedor);
Cmd.ExecuteNonQuery();
Dr = Cmd.ExecuteReader();
List <MetodoPagamento> mPagamentoList = new List <MetodoPagamento>();
if (Dr.HasRows)
{
while (Dr.Read())
{
MetodoPagamento mPagamento = new MetodoPagamento();
mPagamento.Id = Convert.ToInt32(Dr["Id"]);
mPagamento.tMetodoPgto = new TipoMetodosPagamento();
mPagamento.tMetodoPgto.Id = Convert.ToInt32(Dr["IdTipoMetodo"]);
mPagamento.Nome = Convert.ToString(Dr["Nome"]);
mPagamentoList.Add(mPagamento);
}
}
return(mPagamentoList);
}
catch (Exception ex)
{
throw new Exception("Erro o carregar Metodo de Pagamento: " + ex.Message);
}
finally
{
Dr.Close();
FecharConexao();
}
}
}
public List <Pais> ListarPaises(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "SELECT * FROM [PAIS] ";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <Pais> lista = new List <Pais>();
while (Dr.Read())
{
Pais p = new Pais();
p.CodigoPais = Convert.ToInt32(Dr["CD_PAIS"]);
p.DescricaoPais = Convert.ToString(Dr["DS_PAIS"]);
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas Paises: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public DataTable ObterGpoComissao(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [GRUPO_DE_COMISSAO]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
// Cria DataTable
DataTable dt = new DataTable();
dt.Columns.Add("CodigoGpoComissao", typeof(Int32));
dt.Columns.Add("DescricaoGpoComissao", typeof(string));
while (Dr.Read())
{
dt.Rows.Add(Convert.ToInt32(Dr["CD_GPO_COMISSAO"]), Convert.ToString(Dr["DS_GPO_COMISSAO"]));
}
return(dt);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todos as GpoComissaos: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public List <Doca> ListarDocasCompleto(List <DBTabelaCampos> ListaFiltros)
{
try
{
AbrirConexao();
string strValor = "";
string strSQL = "Select * from [DOCA]";
strValor = MontaFiltroIntervalo(ListaFiltros);
strSQL = strSQL + strValor;
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <Doca> lista = new List <Doca>();
while (Dr.Read())
{
Doca p = new Doca();
Empresa empresa = new Empresa();
EmpresaDAL empresaDAL = new EmpresaDAL();
p.CodigoDoca = Convert.ToInt32(Dr["CD_DOCA"]);
p.DescricaoDoca = Convert.ToString(Dr["DS_DOCA"]);
p.CodigoSituacao = Convert.ToInt32(Dr["CD_SITUACAO"]);
p.CodigoEmpresa = Convert.ToInt64(Dr["CD_EMPRESA"]);
empresa = empresaDAL.PesquisarEmpresa(Convert.ToInt64(Dr["CD_EMPRESA"]));
p.Cpl_NomeEmpresa = empresa.NomeEmpresa;
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas as Docas: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public Cliente DAOGetCliente(int Id)
{
try
{
Open();
var clienteVM = new Cliente();
string selectEditCliente = @"SELECT* FROM CLIENTE WHERE IdCliente =" + Id;
SQL = new SqlCommand(selectEditCliente, sqlconnection);
Dr = SQL.ExecuteReader();
while (Dr.Read())
{
clienteVM.IdCliente = Convert.ToInt32(Dr["IdCliente"]);
clienteVM.nmCliente = Dr["nmCliente"].ToString();
clienteVM.nmApelido = Dr["nmApelido"].ToString();
clienteVM.flSexo = Dr["flSexo"].ToString();
clienteVM.nrTelefone = Dr["nrTelefone"].ToString();
clienteVM.nrCelular = Dr["nrCelular"].ToString();
clienteVM.nrCEP = Dr["nrCEP"].ToString();
clienteVM.dsLogradouro = Dr["dsLogradouro"].ToString();
clienteVM.nrResidencial = Dr["nrResidencial"].ToString();
clienteVM.dsBairro = Dr["dsBairro"].ToString();
clienteVM.dsComplemento = Dr["dsComplemento"].ToString();
clienteVM.dsEmail = Dr["dsEmail"].ToString();
clienteVM.nrCPF = Dr["nrCPF"].ToString();
clienteVM.nrRG = Dr["nrRG"].ToString();
clienteVM.idCidade = Convert.ToInt32(Dr["IdCidade"]);
clienteVM.IdCondPag = Convert.ToInt32(Dr["IdCondPagamento"]);
clienteVM.dataNasc = Dr["dataNasc"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(Dr["dataNasc"]);
clienteVM.dtCadastro = Dr["dtCadastro"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(Dr["dtCadastro"]);
clienteVM.dtUltAlteracao = Dr["dtUltAlteracao"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(Dr["dtUltAlteracao"]);
}
return(clienteVM);
}
catch (Exception e)
{
throw new Exception("Erro ao selecionar o Cliente: " + e.Message);
}
finally
{
Close();
}
}
public DataTable ObterPerfisUsuario(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [PERFIL_DO_USUARIO]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
// Cria DataTable
DataTable dt = new DataTable();
dt.Columns.Add("CodigoPflUsuario", typeof(Int64));
dt.Columns.Add("DescricaoPflUsuario", typeof(string));
while (Dr.Read())
{
dt.Rows.Add(Convert.ToInt64(Dr["CD_PFL_USUARIO"]), Convert.ToString(Dr["DS_PFL_USUARIO"]));
}
return(dt);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todos Perfis de Usuário: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public List <Produto> ListarProdutosPesquisa(string strDescricao)
{
try
{
AbrirConexao();
string strSQL = "select * from produto as p inner join unidade as u on p.CD_UNIDADE = u.CD_UNIDADE where cd_situacao = 1 ";
if (strDescricao != "")
{
strSQL += " and NM_PRODUTO like '%" + strDescricao + "%'";
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <Produto> lista = new List <Produto>();
while (Dr.Read())
{
Produto p = new Produto();
p.CodigoProduto = Convert.ToInt64(Dr["CD_Produto"]);
p.DescricaoProduto = Dr["NM_PRODUTO"].ToString();
p.ValorCompra = Convert.ToDouble(Dr["VL_COMPRA"]);
p.DsSigla = Convert.ToString(Dr["SIGLA"]);
p.DsEmbalagem = Convert.ToString(Dr["DS_EMBALAGEM"]);
p.QtEmbalagem = Convert.ToInt16(Dr["QT_EMBALAGEM"]);
p.CodigoBarras = Convert.ToString(Dr["CD_BARRAS"]);
p.CodigoNCM = Convert.ToString(Dr["CD_NCM"]);
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Pesquisar Produto: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public List <Vendedor> ListarRepresentantes()
{
try
{
AbrirConexao();
string strSQL = "select v.CD_VENDEDOR, v.CD_PESSOA, p.NM_PESSOA,ENDE.DS_MUNICIPIO + '/' +" +
"SUBSTRING(ENDE.DS_ESTADO, 0, 3) + ' - ' + PAIS.DS_PAIS AS MUNICIPIO_PAIS " +
"from VENDEDOR AS V " +
"INNER JOIN PESSOA AS P " +
"INNER JOIN PESSOA_ENDERECO AS ENDE ON ENDE.CD_PESSOA = P.CD_PESSOA AND ENDE.CD_ENDERECO = 1 " +
"ON P.CD_PESSOA = V.CD_PESSOA " +
"INNER JOIN PESSOA_INSCRICAO AS INS ON INS.CD_PESSOA = P.CD_PESSOA AND INS.CD_INSCRICAO = 1 " +
"INNER JOIN PAIS ON PAIS.CD_PAIS = INS.CD_PAIS " +
"WHERE V.CD_TIPO_VENDEDOR = 118";
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <Vendedor> lista = new List <Vendedor>();
while (Dr.Read())
{
Vendedor p = new Vendedor();
p.CodigoVendedor = Convert.ToInt64(Dr["CD_Vendedor"]);
p.CodigoPessoa = Convert.ToInt64(Dr["CD_PESSOA"]);
p.Pessoa.NomePessoa = Convert.ToString(Dr["NM_PESSOA"]) + " - " + Dr["MUNICIPIO_PAIS"].ToString().ToUpper();
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todos representantes: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public List <TipoAcesso> ListarTipoAcessos(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [Tipo_de_Acesso]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <TipoAcesso> lista = new List <TipoAcesso>();
while (Dr.Read())
{
TipoAcesso p = new TipoAcesso();
p.CodigoTipoAcesso = Convert.ToInt32(Dr["CD_TIPO_ACESSO"]);
p.DescricaoTipoAcesso = Convert.ToString(Dr["DS_TIPO_ACESSO"]);
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas Tipo de Acesso: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
private void PersonelListesiGuncelleme()
{
try
{
SqlConnection connection = new SqlConnection(@"Server = tcp:hotelieu.database.windows.net,1433; Initial Catalog = HotelProject; Persist Security Info = False; User ID = hotelieu; Password = Hotelproject35; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30");
SqlCommand cmd = new SqlCommand();
listePersonel.Items.Clear();
SqlDataReader Dr;
cmd.CommandText = "select * from Personel where personelDepartman='" + cmbPersonelKategorisi.SelectedItem.ToString() + "' and Sil=0";
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
connection.Open();
Dr = cmd.ExecuteReader();
listePersonel.Items.Clear();
while (Dr.Read())
{
bool listedevar = false;
if (listeGorevliler.Items.Count != 0)
{
for (int i = 0; i < listeGorevliler.Items.Count; i++)
{
if (string.Equals(Dr["personelAdi"].ToString() + " " + Dr["personelSoyadi"].ToString(), listeGorevliler.Items[i].Text))
{
listedevar = true;
}
if (!listedevar)
{
listePersonel.Items.Add(Dr["personelAdi"].ToString() + " " + Dr["personelSoyadi"].ToString());
}
}
}
else
{
listePersonel.Items.Add(Dr["personelAdi"].ToString() + " " + Dr["personelSoyadi"].ToString());
}
}
connection.Close();
}
catch (Exception ex)
{
HotelWarningForm.Show(ex.ToString(), Localization.Tamam, 1);
}
}
public List <RelatorioPedido> RelatorioPedido(Usuario usuario, DateTime inicio, DateTime fim)
{
Abrirconexao();
using (Cmd = new SqlCommand("RelatorioPedidos", Con))
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@IdVendedor", usuario.Id);
Cmd.Parameters.AddWithValue("@DataInicio", inicio);
Cmd.Parameters.AddWithValue("@DataFinal", fim);
Dr = Cmd.ExecuteReader();
List <RelatorioPedido> relatorio = new List <RelatorioPedido>();
if (Dr.HasRows)
{
while (Dr.Read())
{
RelatorioPedido rel = new RelatorioPedido(
Convert.ToString(Dr["ValorTotal"]),
Convert.ToDateTime(Dr["Data"])
);
relatorio.Add(rel);
}
}
Dr.Close();
return(relatorio);
}
catch (Exception ex)
{
throw new Exception("Erro executar relatorio: " + ex.Message);
}
finally
{
FecharConexao();
}
}
}
private void search_J()
{
try {
var name = new List <string>();
var sta = new List <string>();
var pri = new List <string>();
var data_acc = new OrcDataAcess3();
var data_acc1 = new orcDataacess4();
var dt = new DataTable();
data_acc.Show(dt);
data_acc1.Show(dt);
foreach (DataRow Dr in dt.Rows)
{
name.Add(Dr.Field <string>("NAME"));
sta.Add(Dr.Field <string>("PRICE"));
pri.Add(Dr.Field <string>("STATE"));
}
int MAX = 0;
for (int i = 0; i < name.Count; i++)
{
if (this.search.Text != name[i])
{
MAX++;
}
else
{
this.AA.IsChecked = false;
this.pri1.Text = pri[i];
this.Name1.Text = name[i];
this.bar1.Text = sta[i];
}
if (MAX == name.Count)
{
this.AA.IsChecked = false;
this.AA.IsChecked = true;
this.pri1.Text = "";
this.Name1.Text = "";
this.bar1.Text = "";
}
}
}
catch (Exception ex) { }
}
public List <CategoriaVeiculo> ListarCategoriaVeiculos(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [Categoria_do_Veiculo]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <CategoriaVeiculo> lista = new List <CategoriaVeiculo>();
while (Dr.Read())
{
CategoriaVeiculo p = new CategoriaVeiculo();
p.CodigoCategoriaVeiculo = Convert.ToInt32(Dr["CD_Cat_Veiculo"]);
p.DescricaoCategoriaVeiculo = Convert.ToString(Dr["DS_Cat_Veiculo"]);
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas Categoria do Veiculo: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public IEnumerable <Fornecedor> SelecionarFornecedor()
{
try
{
Open();
SQL = new SqlCommand(@"SELECT* FROM Fornecedor", sqlconnection);
SQL.CommandType = CommandType.Text;
Dr = SQL.ExecuteReader();
// Criando uma lista vazia
var lista = new List <Fornecedor>();
while (Dr.Read())
{
var fornecedor = new Fornecedor()
{
IdFornecedor = Convert.ToInt32(Dr["IdFornecedor"]),
nmNome = Convert.ToString(Dr["nmNome"]),
dsNome = Convert.ToString(Dr["dsNome"]),
nrContato = Convert.ToString(Dr["nrContato"]),
nrTelefone = Convert.ToString(Dr["nrTelefone"]),
nrCelular = Convert.ToString(Dr["nrCelular"]),
dsLogradouro = Convert.ToString(Dr["dsLogradouro"]),
nrResidencial = Convert.ToString(Dr["nrResidencial"]),
dsBairro = Convert.ToString(Dr["dsBairro"]),
dsComplemento = Convert.ToString(Dr["dsComplemento"]),
dsEmail = Convert.ToString(Dr["dsEmail"]),
nrCPFCNPJ = Convert.ToString(Dr["nrCPFCNPJ"]),
nrRGIE = Convert.ToString(Dr["nrRGIE"]),
idCidade = Convert.ToInt32(Dr["idCidade"]),
dtCadastro = Convert.ToDateTime(Dr["dtCadastro"]),
};
lista.Add(fornecedor);
}
return(lista);
}
catch (Exception e)
{
throw new Exception("Erro ao selecionar o Fornecedor: " + e.Message);
}
finally
{
Close();
}
}
public DataTable ObterFabricante(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [FABRICANTE]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
// Cria DataTable
DataTable dt = new DataTable();
dt.Columns.Add("CodigoFabricante", typeof(short));
dt.Columns.Add("DescricaoFabricante", typeof(string));
while (Dr.Read())
{
dt.Rows.Add(Convert.ToInt16(Dr["CD_FABRICANTE"]), Convert.ToString(Dr["DS_FABRICANTE"]));
}
return(dt);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas Fabricante: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
string strConnection = WebConfigurationManager.ConnectionStrings["28dayConnectionString"].ConnectionString.ToString();
SqlConnection Connection = new SqlConnection(strConnection);
Connection.Open();
String strSQL = "Select * From student where s_sno='" + this.GridView1.SelectedDataKey["c_sno"].ToString() + "'";
String strSQL1 = "Select * From communist where c_sno='" + this.GridView1.SelectedDataKey["c_sno"].ToString() + "'";
SqlCommand command = new SqlCommand(strSQL, Connection);
SqlCommand command1 = new SqlCommand(strSQL1, Connection);
SqlDataReader Dr;
SqlDataReader Dr1;
Dr = command.ExecuteReader();
while (Dr.Read())
{
TextBox3.Text = Dr["s_name"].ToString();
TextBox4.Text = Dr["s_sno"].ToString();
TextBox5.Text = Dr["s_sex"].ToString();
TextBox6.Text = Dr["s_birthplace"].ToString();
TextBox7.Text = Dr["s_ethnic"].ToString();
TextBox8.Text = Dr["s_birthplace"].ToString();
TextBox9.Text = Dr["s_idcard"].ToString();
TextBox10.Text = Dr["s_academy"].ToString();
TextBox11.Text = Dr["s_phone"].ToString();
TextBox12.Text = Dr["s_email"].ToString();
//Image1.ImageUrl = ResolveUrl(Dr["out_add1"].ToString());
}
Dr.Close();
Dr1 = command1.ExecuteReader();
while (Dr1.Read())
{
TextBox13.Text = Dr1["c_contactor"].ToString();
TextBox14.Text = Dr1["c_partytime"].ToString();
TextBox15.Text = Dr1["c_note"].ToString();
}
Dr1.Close();
Connection.Close();
/*string content;
* content = Doc2Text(Server.MapPath("image3/" + this.GridView1.SelectedDataKey["out_fname"].ToString()));
* Image1 = content;*/
}
public List <Fabricante> ListarFabricantes(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [FABRICANTE]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <Fabricante> lista = new List <Fabricante>();
while (Dr.Read())
{
Fabricante p = new Fabricante();
p.CodigoFabricante = Convert.ToInt16(Dr["CD_FABRICANTE"]);
p.DescricaoFabricante = Convert.ToString(Dr["DS_FABRICANTE"]);
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas F: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
public Item DetalheItemComprador(int idItem)
{
Abrirconexao();
using (Cmd = new SqlCommand("DetalheItemCompradador", Con))
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@IdItem", idItem);
Cmd.ExecuteNonQuery();
Dr = Cmd.ExecuteReader();
Item item = null;
if (Dr.HasRows)
{
item = new Item();
Dr.Read();
item.Codigo = Convert.ToString(Dr["Codigo"]);
item.Nome = Convert.ToString(Dr["Nome"]);
item.Descricao = Convert.ToString(Dr["Descricao"]);
item.ValorUnitario = Convert.ToDouble(Dr["Valorunitario"]);
item.Quantidade = Convert.ToDouble(Dr["Quantidade"]);
//item.Categoria = new Categoria(Convert.ToString(Dr["Categoria.Nome"]));
item.Comprador = new Usuario();
item.Comprador.Nome = Convert.ToString(Dr["Usuario.Nome"]);
}
Dr.Close();
return(item);
}
catch (Exception ex)
{
throw new Exception("Erro ao carregar Item: " + ex.Message);
}
finally
{
FecharConexao();
}
}
}
// SELECT COUNT(IdVeiculo) AS QtdVeiculo ,M.Nome
//FROM Marca AS M
//INNER JOIN Veiculo AS V ON M.IdMarca = V.IdMarca
//WHERE V.Ativo = 1
//GROUP BY M.IdMarca
public List <Marca> ListarByTipo(string tipo, bool?ativo)
{
try
{
AbrirConexao();
var strQuery = "procMarcaByTipoSELECT";
Cmd = new MySqlCommand
{
CommandText = strQuery,
CommandType = CommandType.StoredProcedure,
Connection = minhaConexao
};
Cmd.Parameters.AddWithValue("idTipo", tipo);
Cmd.Parameters.AddWithValue("ativo", ativo);
Dr = Cmd.ExecuteReader();
List <Marca> lista = new List <Marca>();
while (Dr.Read())
{
Marca marca = new Marca();
marca.Tipo = new Tipo();
marca.IdMarca = Convert.ToInt32(Dr["IdMarca"]);
marca.Nome = Convert.ToString(Dr["Nome"]).ToUpper();
marca.QtdVeiculo = Convert.ToInt32(Dr["QtdVeiculo"]);
marca.Tipo.Nome = Convert.ToString(Dr["TipoNome"]);
lista.Add(marca);
}
Dr.Close();
Dr.Dispose();
return(lista);
}
catch (Exception)
{
throw;
}
finally
{
FecharConexao();
}
}
public List <Marca> ListarMarcas(string strNomeCampo, string strTipoCampo, string strValor, string strOrdem)
{
try
{
AbrirConexao();
string strSQL = "Select * from [Marca]";
if (strValor != "")
{
strSQL = strSQL + " Where " + MontaFiltro(strNomeCampo, strTipoCampo, strValor);
}
if (strOrdem != "")
{
strSQL = strSQL + "Order By " + strOrdem;
}
Cmd = new SqlCommand(strSQL, Con);
Dr = Cmd.ExecuteReader();
List <Marca> lista = new List <Marca>();
while (Dr.Read())
{
Marca p = new Marca();
p.CodigoMarca = Convert.ToInt16(Dr["CD_MARCA"]);
p.DescricaoMarca = Convert.ToString(Dr["DS_MARCA"]);
lista.Add(p);
}
return(lista);
}
catch (Exception ex)
{
throw new Exception("Erro ao Listar Todas Marca: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
private bool IsUser(string userid, string password, string userdept)
{
bool boolReturnValue = false;
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\") || dataDir.EndsWith(@"\bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
String strSQL = "Select * From Table_user";
SqlCommand command = new SqlCommand(strSQL, conn);
SqlDataReader Dr;
conn.Open();
Dr = command.ExecuteReader();
while (Dr.Read())
{
if ((userid == Dr["user_id"].ToString().Trim()) && (password == Dr["user_password"].ToString().Trim()) && (userdept == Dr["user_dept"].ToString().Trim()))
{
if (userdept == "管委会用户")
{
Session["isLogin"] = "******";
Response.Redirect("GWH/Default_GWH.aspx");
}
if (userdept == "新港办用户")
{
Session["isLogin"] = "******";
Response.Redirect("XGB/Index.aspx");
Session["UserName"] = userid;
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "u1", "alert('用户名或密码错误！')", true);
}
}
Dr.Close();
return(boolReturnValue);
}
}
public ContaPagarVM GetContaPagar(string filter, string nmModelo, string nrSerie, int nrNota, int?nrParcela, int?IdFornecedor)
{
try
{
Open();
var contaVM = new ContaPagarVM();
var sql = this.BuscarCompra(filter, nmModelo, nrSerie, nrNota, IdFornecedor, nrParcela);
SQL = new SqlCommand(sql, sqlconnection);
Dr = SQL.ExecuteReader();
while (Dr.Read())
{
contaVM.nrModelo = Convert.ToString(Dr["ContaPagar_nrModelo"]);
contaVM.nrNota = Convert.ToInt32(Dr["ContaPagar_nrNota"]);
contaVM.nrSerie = Convert.ToString(Dr["ContaPagar_nrSerie"]);
contaVM.vlParcela = Convert.ToDecimal(Dr["ContaPagar_vlParcela"]);
contaVM.nrParcela = Convert.ToInt32(Dr["ContaPagar_NrParcela"]);
contaVM.flSituacao = Convert.ToString(Dr["ContaPagar_Situacao"]);
contaVM.dtVencimento = Dr["ContaPagar_DataVencimento"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(Dr["ContaPagar_DataVencimento"]);
contaVM.dtPagamento = Dr["ContaPagar_DataPagamento"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(Dr["ContaPagar_DataPagamento"]);
contaVM.Fornecedor = new ViewModels.Fornecedores.SelectFornecedorVM
{
IdFornecedor = Convert.ToInt32(Dr["ContaPagar_Fornecedor_ID"]),
nmNome = Convert.ToString(Dr["ContaPagar_Fornecedor_Nome"])
};
contaVM.formaPag = new ViewModels.FormaPagamentos.SelectFormaPagamentoVM
{
Id = Convert.ToInt32(Dr["ContaPagar_FormaPagamento_ID"]),
Text = Convert.ToString(Dr["ContaPagar_FormaPagamento_dsFormaPagamento"]),
};
}
return(contaVM);
}
catch (Exception e)
{
throw new Exception("Erro ao selecionar o Agenda: " + e.Message);
}
finally
{
Close();
}
}
public Item DetalheItemCarrinho(int idItem)
{
Abrirconexao();
Item item = null;
TextInfo ti = CultureInfo.CurrentCulture.TextInfo;
using (Cmd = new SqlCommand("DetalheItem", Con))
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@IdItem", idItem);
Cmd.ExecuteNonQuery();
Dr = Cmd.ExecuteReader();
if (Dr.HasRows)
{
item = new Item();
Dr.Read();
item.Id = Convert.ToInt32(Dr["Id"]);
item.Nome = ti.ToTitleCase(Convert.ToString(Dr["Nome"]));
item.ValorUnitario = Math.Round(Convert.ToDouble(Dr["Valorunitario"]), 2);
item.Quantidade = Convert.ToDouble(Dr["Quantidade"]);
item.Vendedor = new Usuario();
item.Vendedor.Id = Convert.ToInt32(Dr["IdVendedor"]);
}
return(item);
}
catch (Exception ex)
{
throw new Exception("Erro o carregar Item: " + ex.Message);
}
finally
{
Dr.Close();
FecharConexao();
}
}
}
private void Refund_Sale_Load(object sender, EventArgs e)
{
txtItemName.Enabled = false;
txtQuantityPurchased.Enabled = false;
txtCostOfSale.Enabled = false;
listBox1.Items.Clear();
SqlConnection sqlcon1 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon1.Open();
string Select = "SELECT SaleID FROM Sales";
SqlCommand sqlcom1 = new SqlCommand(Select, sqlcon1);
SqlDataReader Dr;
Dr = sqlcom1.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
listBox1.Items.Add(Dr["SaleID"].ToString());
}
}
Dr.Close();
sqlcon1.Close();
//Add Customers
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string Select2 = "SELECT CustomerFullName FROM Customers";
SqlCommand sqlcom2 = new SqlCommand(Select2, sqlcon2);
SqlDataReader dr2;
dr2 = sqlcom2.ExecuteReader();
if (dr2.HasRows)
{
while (dr2.Read())
{
cbxCustomerName.Items.Add((dr2["CustomerFullName"].ToString()));
}
}
}
//統計每一段的FLY 成本
private static void ReCalculateFlyCost(ref Boolean ChkFlyCost, ref float AdultFlyCost, ref float ChdFlyCost, ref float SenoirFlyCost,
List <DataRow> cost_lst, bool IsHoliday)
{
if (cost_lst.Count > 0)
{
//先找出這一天是星期幾..
//如果航段定義為來回,當去程是否加假有落在上面,有的話以假日加價的成本計,回程依去程定義為主
//如果航段定義為單程,就單程單程看.
//int ThisDayOfWeek = Convert.ToStringEx(e.Day.Date.DayOfWeek);
List <string> TripWayIsRt = new List <string> {
}; //定義了去程 trp_way 是 RT 的航段明細
foreach (DataRow Dr in cost_lst)
{
string AddPriceWeeks = Dr.ToStringEx("ADD_PRICE_WEEKS");
try
{
float AdultPrice = 0, ChdPrice = 0, SeniorPrice = 0;
AdultPrice = IsHoliday ? Dr.ToSingle("ADULT_COST_H") : Dr.ToSingle("ADULT_COST");
ChdPrice = IsHoliday ? Dr.ToSingle("CHD_COST_H") : Dr.ToSingle("CHD_COST");
SeniorPrice = IsHoliday ? Dr.ToSingle("OLD_COST_H") : Dr.ToSingle("OLD_COST");
AdultFlyCost = AdultPrice;
ChdFlyCost = ChdPrice;
SenoirFlyCost = SeniorPrice;
ChkFlyCost = true;
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
ChkFlyCost = false;
}
}
}
else
{
ChkFlyCost = false;
}
}
} ///
///
/// 检验是否存在数据
///
///
public bool ExistDate(string SQL)
{
SqlConnection Conn;
Conn = ReturnConn();
SqlDataReader Dr;
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
Dispose(Conn);
return(true);
}
else
{
Dispose(Conn);
return(false);
}
} ///
public void FiilComboboxOdaGrubu()
{
SqlConnection connection = new SqlConnection(@"Server = tcp:hotelieu.database.windows.net,1433; Initial Catalog = HotelProject; Persist Security Info = False; User ID = hotelieu; Password = Hotelproject35; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select OdaKategoriAciklama from OdaKategori";
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
SqlDataReader Dr;
connection.Open();
Dr = cmd.ExecuteReader();
while (Dr.Read())
{
ComboBoxOdaGrubu.Items.Add(Dr["OdaKategoriAciklama"]);
}
connection.Close();
}
public Licenca PesquisarLicenca(long Codigo)
{
try
{
AbrirConexao();
if (Codigo == 0)
{
Cmd = new SqlCommand("Select Top 1 * from LICENCA_DE_USO ", Con);
}
else
{
Cmd = new SqlCommand("Select * from LICENCA_DE_USO Where CD_LICENCA = @v1", Con);
Cmd.Parameters.AddWithValue("@v1", Codigo);
}
Dr = Cmd.ExecuteReader();
Licenca p = null;
if (Dr.Read())
{
p = new Licenca();
p.CodigoDaLicenca = Convert.ToInt64(Dr["CD_LICENCA"]);
p.CodigoDoCliente = Convert.ToInt64(Dr["CD_CLIENTE"]);
p.NomeDoCliente = Convert.ToString(Dr["NM_CLIENTE"]);
p.NumeroDeUsuarios = Convert.ToInt32(Dr["NR_USUARIOS"]);
p.ServidorDoCliente = Convert.ToString(Dr["TX_SERVIDOR"]);
p.BancoDoCliente = Convert.ToString(Dr["TX_BANCO"]);
p.UsuarioBancoDoCliente = Convert.ToString(Dr["TX_USUARIO"]);
p.SenhaBancoDoCliente = Convert.ToString(Dr["TX_SENHA"]);
p.CodigoDaAtualizacaoBanco = Convert.ToInt64(Dr["CD_STR_DATABASE"]);
}
return(p);
}
catch (Exception ex)
{
throw new Exception("Erro ao Pesquisar Licença: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
//ESSE MÉTODO LISTA OS ITENS CONFORME SUA CATEGORIA
public List <Item> ListarItemPorCategoria(Usuario user, int idCategoria)
{
Abrirconexao();
using (Cmd = new SqlCommand("ListarItemPorCategoria", Con))
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@IdCategoria", idCategoria);
Cmd.Parameters.AddWithValue("@LatitudeComprador", user.Latitude);
Cmd.Parameters.AddWithValue("@Longitudeomprador", user.Longitude);
Cmd.ExecuteNonQuery();
Dr = Cmd.ExecuteReader();
List <Item> itemList = new List <Item>();
while (Dr.HasRows)
{
Item item = new Item();
Dr.Read();
item.Id = Convert.ToInt32(Dr["Id"]);
item.Codigo = Convert.ToString(Dr["Codigo"]);
item.Nome = Convert.ToString(Dr["Nome"]);
item.ValorUnitario = Convert.ToDouble(Dr["Valorunitario"]);
item.Quantidade = Convert.ToDouble(Dr["Quantidade"]);
itemList.Add(item);
}
return(itemList);
}
catch (Exception ex)
{
throw new Exception("Ao Listar Item: " + ex.Message);
}
finally
{
FecharConexao();
}
}
}
public List <RelatorioItem> RelatorioItem(Usuario usuario)
{
Abrirconexao();
using (Cmd = new SqlCommand("RelatorioPedidos", Con))
{
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@IdVendedor", usuario.Id);
Dr = Cmd.ExecuteReader();
List <RelatorioItem> relatorio = new List <RelatorioItem>();
if (Dr.HasRows)
{
while (Dr.Read())
{
RelatorioItem rel = new RelatorioItem(
Convert.ToInt32(Dr["IdItem"]),
Convert.ToInt32(Dr["QuantidadeItens"]),
Convert.ToString(Dr["Nome"])
);
relatorio.Add(rel);
}
}
Dr.Close();
return(relatorio);
}
catch (Exception ex)
{
throw new Exception("Erro executar relatorio: " + ex.Message);
}
finally
{
FecharConexao();
}
}
}
public static JsonResult GetDRS(String query)
{
DrList icds = new DrList();
String list = null;
int count = 0;
try
{
string icd = null;
if (query != null)
icd = query;
if (query.Length > 1)
{
DataSet pDRs = _base.GetDrs(icd);
count = pDRs.Tables[0].Rows.Count;
//list = "{ \"matches\": [";
list = "[";
for (int i = 0; i < pDRs.Tables[0].Rows.Count; i++)
{
Dr icd9 = new Dr { Id = pDRs.Tables[0].Rows[i]["ID"].ToString(), Name = pDRs.Tables[0].Rows[i]["LASTNAME"].ToString() };
list += "{\"Name\": \"" + pDRs.Tables[0].Rows[i]["LASTNAME"].ToString() + "\"},";
//icds.addToList(pICD.Tables[0].Rows[i]["ICDR_CODE"].ToString(), pICD.Tables[0].Rows[i]["ICDR_MED_DESCRIPTION"].ToString());
}
list = list.Substring(0, list.Length - 1); // remove trailing comma
//list += "]};";
list += "]";
}
}
catch (Exception ex)
{
/*
return new JsonResult
{
ContentType = "text/plain",
Data = new
{
ex.Message
},
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
* */
}
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
using (JsonWriter writer = new JsonTextWriter(sw))
{
writer.Formatting = Formatting.Indented;
writer.WriteStartObject();
}
//return list;
return new JsonResult
{
ContentType = "text/plain",
Data = new
{
matches = list,
},
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
}
