/// <summary> /// Возвращает список участков схемы фидера /// </summary> /// <param name="fider">Фидер</param> /// <returns>Список участков схемы, список вершин графа схемы</returns> public FiderScheme GetFiderLines(Model.Fider fider) { FiderScheme result = new FiderScheme(); string query; int id_fider = fider.ID; int id_sect_nn = fider.ID_Sect_NN; // словарь вершин ИД вершин схемы фидера ICollection <Model.LineVertex> linesVertities = GetLinesVertities(id_fider); // список вершин графа схемы фидера List <Graph.FiderGraphVertex> vertitiesList = new List <Graph.FiderGraphVertex>(linesVertities.Count); // словарь вершин графа схемы фидера Dictionary <int, Graph.FiderGraphVertex> vertities = new Dictionary <int, Graph.FiderGraphVertex>(linesVertities.Count); // список ребер графа схемы фидера List <Graph.FiderGraphEdge> edges = new List <Graph.FiderGraphEdge>(); // заполняем словарь вершин foreach (Model.LineVertex item in linesVertities) { Graph.FiderGraphVertex vertex = new Graph.FiderGraphVertex(item.ID); vertitiesList.Add(vertex); vertities.Add(item.ID, vertex); } // список ребер графа схемы фидера // список участков схемы фидера (узел задан ИД и названием) List <Model.Line> linesWithNodeNameList = new List <Model.Line>(); #region Таблица для поиска названия узла ТП по его ИД Dictionary <int, string> dictionaryNodeTPNames = new Dictionary <int, string>(); query = String.Format( @"SELECT NODELINKS.ID, CAST((TP.NAME||'/'||SECTTP.NAME) AS CHAR(100)) NODE_NAME FROM TP INNER JOIN SECTTP ON (SECTTP.ID_PST = TP.ID) INNER JOIN NODELINKS ON (SECTTP.ID_NODELINKS = NODELINKS.ID) WHERE NODELINKS.NODE_TYPE=1", id_fider); try { using (var command = new FbCommand(query, fbConnection)) { if (fbConnection.State == System.Data.ConnectionState.Closed) { fbConnection.Open(); } FbDataReader reader = command.ExecuteReader(); if (reader.HasRows) { try { while (reader.Read()) { int key = reader[0] != DBNull.Value ? (int)reader[0] : default(int); string value = reader[1] != DBNull.Value ? (string)reader[1] : default(string); dictionaryNodeTPNames.Add(key, value); } } finally { reader.Close(); } } } } catch (Exception ex) { HasError = true; LastException = ex; return(result); } #endregion Таблица для поиска названия узла ТП по его ИД #region Таблица для поиска названия узла по его ИД Dictionary <int, string> dictionaryNodeNames = new Dictionary <int, string>(); query = String.Format( @"SELECT NODELINKS.ID, NODE.NAME NODE_NAME FROM NODE INNER JOIN NODELINKS ON (NODE.ID_NODELINKS = NODELINKS.ID) WHERE NODELINKS.NODE_TYPE=2 AND NODELINKS.ID IN ( SELECT LINE.ID_NODE1 NODEID FROM LINE WHERE LINE.ID_FIDER = {0} UNION SELECT LINE.ID_NODE2 NODEID FROM LINE WHERE LINE.ID_FIDER = {0} )", id_fider); try { using (var command = new FbCommand(query, fbConnection)) { if (fbConnection.State == System.Data.ConnectionState.Closed) { fbConnection.Open(); } FbDataReader reader = command.ExecuteReader(); if (reader.HasRows) { try { while (reader.Read()) { int key = reader[0] != DBNull.Value ? (int)reader[0] : default(int); string value = reader[1] != DBNull.Value ? (string)reader[1] : default(string); if (dictionaryNodeNames.ContainsKey(key) == false) { dictionaryNodeNames.Add(key, value); } } } finally { reader.Close(); } } } } catch (Exception ex) { HasError = true; LastException = ex; return(result); } #endregion Таблица для поиска названия узла по его ИД #region Таблица для поиска названия узла подстанции по его ИД Dictionary <int, string> dictionaryNodePSTName = new Dictionary <int, string>(); query = String.Format( @"SELECT NODELINKS.ID, CAST(('[ЦП] - '||PST.NAME||'/'||SECTPST.NAME) AS CHAR(100)) NODE_NAME FROM PST INNER JOIN SECTPST ON (SECTPST.ID_PST = PST.ID) INNER JOIN NODELINKS ON (SECTPST.ID_NODELINKS = NODELINKS.ID) WHERE NODELINKS.NODE_TYPE=3 AND SECTPST.ID = {0}", id_sect_nn); try { using (var command = new FbCommand(query, fbConnection)) { if (fbConnection.State == System.Data.ConnectionState.Closed) { fbConnection.Open(); } FbDataReader reader = command.ExecuteReader(); if (reader.HasRows) { try { while (reader.Read()) { int key = reader[0] != DBNull.Value ? (int)reader[0] : default(int); string value = reader[1] != DBNull.Value ? (string)reader[1] : default(string); dictionaryNodePSTName.Add(key, value); } } finally { reader.Close(); } } } } catch (Exception ex) { HasError = true; LastException = ex; return(result); } #endregion Таблица для поиска названия узла подстанции по его ИД // список участков схемы фидера (узел задан ИД) ICollection <Model.Line> lineNodesList = GetLines(id_fider); // цикл по всем участкам схемы фидера int edgeIndex = 1; foreach (var item in lineNodesList) { FillVertexProperties(item.NodeStartId, ref dictionaryNodeTPNames, ref dictionaryNodeNames, ref dictionaryNodePSTName, ref vertities); FillVertexProperties(item.NodeEndId, ref dictionaryNodeTPNames, ref dictionaryNodeNames, ref dictionaryNodePSTName, ref vertities); item.NodeStart = vertities[item.NodeStartId].Name; item.NodeEnd = vertities[item.NodeEndId].Name; edges.Add(new Graph.FiderGraphEdge(edgeIndex, vertities[item.NodeStartId], vertities[item.NodeEndId])); edgeIndex++; } result.Lines = lineNodesList; result.GraphVertities = vertitiesList; result.GraphEdges = edges; return(result); }
private void window_edit_role_Activated(object sender, EventArgs e) { ////////////////////////////////Процедура проверки номенклатура FbCommand sqlpas = new FbCommand("select * from GET_ITEM_ALLOW(" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ", 'Справочник Номенклатура')", con.preh.fb); FbDataReader readerpas = sqlpas.ExecuteReader(); DataTable dt_pas = new DataTable(); dt_pas.Load(readerpas); if (dt_pas.Rows[0][0].ToString() == "0") { chnom.IsChecked = false; } else { chnom.IsChecked = true; } ////////////////////////////////Процедура проверки единиц измерения FbCommand sqlmes = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Справочник Единицы измерения')", con.preh.fb); FbDataReader readermes = sqlmes.ExecuteReader(); DataTable dt_mes = new DataTable(); dt_mes.Load(readermes); if (dt_mes.Rows[0][0].ToString() == "0") { chmes.IsChecked = false; } else { chmes.IsChecked = true; } ////////////////////////////////Процедура проверки характеристик FbCommand sqlch = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Справочник Характеристики')", con.preh.fb); FbDataReader readerch = sqlch.ExecuteReader(); DataTable dt_ch = new DataTable(); dt_ch.Load(readerch); if (dt_ch.Rows[0][0].ToString() == "0") { chchar.IsChecked = false; } else { chchar.IsChecked = true; } ////////////////////////////////Процедура проверки контрагентов FbCommand sqlcon = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Справочник Контрагенты')", con.preh.fb); FbDataReader readercon = sqlcon.ExecuteReader(); DataTable dt_con = new DataTable(); dt_con.Load(readercon); if (dt_con.Rows[0][0].ToString() == "0") { chcontr.IsChecked = false; } else { chcontr.IsChecked = true; } ////////////////////////////////Процедура проверки объектов FbCommand sqlobj = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Справочник Объекты')", con.preh.fb); FbDataReader readerobj = sqlobj.ExecuteReader(); DataTable dt_obj = new DataTable(); dt_obj.Load(readerobj); if (dt_obj.Rows[0][0].ToString() == "0") { chobj.IsChecked = false; } else { chobj.IsChecked = true; } ////////////////////////////////Процедура проверки сотрудников FbCommand sqlemp = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Справочник Сотрудники')", con.preh.fb); FbDataReader readeremp = sqlemp.ExecuteReader(); DataTable dt_emp = new DataTable(); dt_emp.Load(readeremp); if (dt_emp.Rows[0][0].ToString() == "0") { chemp.IsChecked = false; } else { chemp.IsChecked = true; } ////////////////////////////////Процедура проверки складов FbCommand sqlpaswar = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Справочник Склады')", con.preh.fb); FbDataReader readerpaswar = sqlpaswar.ExecuteReader(); DataTable dt_paswar = new DataTable(); dt_paswar.Load(readerpaswar); if (dt_paswar.Rows[0][0].ToString() == "0") { chwar.IsChecked = false; } else { chwar.IsChecked = true; } ////////////////////////////////Процедура проверки прихода FbCommand sqlpasarr = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Журнал прихода')", con.preh.fb); FbDataReader readerpasarr = sqlpasarr.ExecuteReader(); DataTable dt_pasarr = new DataTable(); dt_pasarr.Load(readerpasarr); if (dt_pasarr.Rows[0][0].ToString() == "0") { charr.IsChecked = false; } else { charr.IsChecked = true; } ////////////////////////////////Процедура проверки расхода FbCommand sqlpasleave = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Журнал расхода')", con.preh.fb); FbDataReader readerpasleave = sqlpasleave.ExecuteReader(); DataTable dt_pasleave = new DataTable(); dt_pasleave.Load(readerpasleave); if (dt_pasleave.Rows[0][0].ToString() == "0") { chleave.IsChecked = false; } else { chleave.IsChecked = true; } ////////////////////////////////Процедура проверки перемещения FbCommand sqlpasmove = new FbCommand("select * from GET_ITEM_ALLOW (" + con.grid_roles.GetFocusedRowCellValue("ID").ToString() + ",'Журнал перемещения')", con.preh.fb); FbDataReader readerpasmove = sqlpasmove.ExecuteReader(); DataTable dt_pasmove = new DataTable(); dt_pasmove.Load(readerpasmove); if (dt_pasmove.Rows[0][0].ToString() == "0") { chmove.IsChecked = false; } else { chmove.IsChecked = true; } }
public static List <Parceiro> AuditaCpfCnpj() { FbCommand cmd = new FbCommand("select NRO_CPF_CNPJ, " + " COD_PARCEIRO, " + " NOM_PARCEIRO, " + " IDT_FISJUR " + "from PARCEIROS " + "order by NRO_CPF_CNPJ, COD_PARCEIRO", Globais.bd); Dictionary <string, List <Parceiro> > dic = new Dictionary <string, List <Parceiro> >(); FbDataReader reader = cmd.ExecuteReader(); Parceiro anterior = new Parceiro(); anterior.setCpfCnpj("?"); while (reader.Read()) { string cpfCnpj = !reader.IsDBNull(0) ? reader.GetString(0).Trim() : ""; Console.WriteLine(cpfCnpj); if (cpfCnpj.Equals("")) { continue; } long l = 0; long.TryParse(cpfCnpj, out l); if (l == 0) { continue; } string codigo = !reader.IsDBNull(1) ? reader.GetString(1).Trim() : ""; string nome = !reader.IsDBNull(2) ? reader.GetString(2).Trim() : ""; string tipo = !reader.IsDBNull(3) ? reader.GetString(3).Trim() : "F"; if (tipo.Equals("F")) { cpfCnpj = CPF.PoeEdicao(cpfCnpj); } else { cpfCnpj = CNPJ.PoeEdicao(cpfCnpj); } Parceiro parceiro = new Parceiro(); parceiro.setCpfCnpj(cpfCnpj); parceiro.setCodigo(codigo); parceiro.setNome(nome); if (anterior.getCpfCnpj().Equals(cpfCnpj)) { List <Parceiro> lista = dic.ContainsKey(cpfCnpj) ? dic[cpfCnpj] : null; if (lista == null) { lista = new List <Parceiro>(); lista.Add(anterior); } lista.Add(parceiro); dic[cpfCnpj] = lista; } anterior = parceiro; } reader.Close(); List <Parceiro> repetidos = new List <Parceiro>(); foreach (List <Parceiro> lista in dic.Values) { foreach (Parceiro parceiro in lista) { repetidos.Add(parceiro); } } return(repetidos); }
private void YeniSatisBtn2_Click(object sender, EventArgs e) { try { string satis_musteri = ""; byte sonuc; byte sonuc1; float tutar = 0, bakiye = 0; FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); int satisadet = int.Parse(SatisAdetBarkod.Value.ToString()); int eklesay = 0; for (int i = 1; i <= satisadet; i++) { if (KayitliMi2.Checked) { string[] ads = SatisMusteriCb.Text.Split(' '); baglan.Open(); FbCommand MusBilgi = new FbCommand("SELECT Musteri_id,Musteri_bakiye FROM Musteriler WHERE Musteri_adi='" + ads[0] + "' and Musteri_soyadi='" + ads[1] + "'", baglan); FbDataReader MusOku = MusBilgi.ExecuteReader(); MusOku.Read(); satis_musteri = MusOku["Musteri_id"].ToString(); bakiye = float.Parse(MusOku["Musteri_bakiye"].ToString()); MusOku.Close(); baglan.Close(); } string tarih = DateTime.Now.Year.ToString(); tarih += '-' + DateTime.Now.Month.ToString(); tarih += '-' + DateTime.Now.Day.ToString(); if (satis_musteri == "") { sonuc = fk.Ekle("Satis", "Satis_urun,Satis_tarih,Satis_not", "'" + satis_urun + "','" + tarih + "','" + SatisNot.Text + "'"); if (sonuc == 1) { baglan.Open(); FbCommand UrunVarMi = new FbCommand("SELECT rapor_satisId,rapor_tarih FROM Rapor WHERE rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'", baglan); object varmi = UrunVarMi.ExecuteScalar(); if (varmi != null) { sonuc1 = fk.Guncelle("Rapor", "rapor_sayac=rapor_sayac+1", "rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'"); } else { sonuc1 = fk.Ekle("Rapor", "rapor_satisId,rapor_satisKt,rapor_sayac,rapor_tarih", "'" + satis_urun + "','" + satis_kategori + "','1','" + tarih + "'"); } baglan.Close(); eklesay++; } else { eklesay = eklesay + 0; } } else { sonuc = fk.Ekle("Satis", "Satis_urun,Satis_musteri,Satis_tarih,Satis_not", "'" + satis_urun + "','" + satis_musteri + "','" + tarih + "','" + SatisNot.Text + "'"); if (sonuc == 1) { baglan.Open(); FbCommand UrunVarMi = new FbCommand("SELECT rapor_satisId,rapor_tarih FROM Rapor WHERE rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'", baglan); object varmi = UrunVarMi.ExecuteScalar(); if (varmi != null) { sonuc1 = fk.Guncelle("Rapor", "rapor_sayac=rapor_sayac+1", "rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'"); } else { sonuc1 = fk.Ekle("Rapor", "rapor_satisId,rapor_satisKt,rapor_sayac,rapor_tarih", "'" + satis_urun + "','" + satis_kategori + "','1','" + tarih + "'"); } baglan.Close(); eklesay++; if (checkBox3.Checked) { tutar = float.Parse(SatisTutar.Text); bakiye = bakiye - tutar; ybakiye.Text = bakiye.ToString(); ybakiye.Text = ybakiye.Text.Replace(',', '.'); fk.Guncelle("Musteriler", "Musteri_bakiye='" + ybakiye.Text + "'", "Musteri_id='" + satis_musteri + "'"); } } else { eklesay = eklesay + 0; } } } if (eklesay > 0) { MessageBox.Show("Satış başarıyla eklenmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Satış eklenemedi ( Hata kodu: S-01 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } eklesay = 0; BarkodNo.Text = ""; } catch (Exception e5) { MessageBox.Show(e5.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void urunAra_TextChanged(object sender, EventArgs e) { try { if (urunAra.Text.Length > 0) { FbConnection b1 = new FbConnection(fk.Baglanti_Kodu()); b1.Open(); FbCommand KatidAl = new FbCommand("SELECT * FROM Urun_kategori WHERE Kategori_adi='" + SatisUrunKategorisi.Text + "'", b1); object ss1 = KatidAl.ExecuteScalar(); if (ss1 != null) { FbDataReader Katidoku = KatidAl.ExecuteReader(); Katidoku.Read(); string id = Katidoku["Kategori_id"].ToString(); Katidoku.Close(); FbConnection b2 = new FbConnection(fk.Baglanti_Kodu()); b2.Open(); FbCommand UrunAra = new FbCommand("SELECT Urun_adi FROM Urunler WHERE Urun_kategori='" + id + "' and Urun_adi like '%" + urunAra.Text + "%'", b2); object aSor = UrunAra.ExecuteScalar(); if (aSor != null) { SatisUrun.Items.Clear(); FbDataReader UrunOku = UrunAra.ExecuteReader(); while (UrunOku.Read()) { SatisUrun.Items.Add(UrunOku["Urun_adi"].ToString()); } SatisUrun.SelectedIndex = 0; } else { MessageBox.Show("Girilen kelimeye göre sistemde ekli ürün bulunamadı, tüm ürünler listelenmiştir.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information); } b2.Close(); } else { MessageBox.Show("Sistemsel bir sorun oluştu, lütfen sağlayıcınıza başvurunuz.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } b1.Close(); } else { string id = ""; SatisUrun.Items.Clear(); FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); baglan.Open(); FbCommand KatidAl = new FbCommand("SELECT * FROM Urun_kategori WHERE Kategori_adi='" + SatisUrunKategorisi.Text + "'", baglan); object e1 = KatidAl.ExecuteScalar(); if (e1 != null) { FbDataReader Katidoku = KatidAl.ExecuteReader(); Katidoku.Read(); id = Katidoku["Kategori_id"].ToString(); Katidoku.Close(); } else { MessageBox.Show("Sistemsel bir sorun oluştu, lütfen sağlayıcınıza başvurunuz.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } baglan.Close(); baglan.Open(); FbCommand urunList = new FbCommand("SELECT * FROM Urunler WHERE Urun_kategori='" + id + "' order by Urun_adi asc", baglan); object sonuc = urunList.ExecuteScalar(); if (sonuc != null) { FbDataReader oku2 = urunList.ExecuteReader(); SatisUrun.Enabled = true; YeniSatisBtn.Enabled = true; while (oku2.Read()) { SatisUrun.Items.Add(oku2["Urun_adi"].ToString()); } //if(SatisUrun.Text != "") SatisUrun.SelectedIndex = 0; } else { SatisUrun.Enabled = false; YeniSatisBtn.Enabled = false; MessageBox.Show("Bu kategoride henüz ürün eklenmemiş", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information); } baglan.Close(); } } catch (Exception ee1) { MessageBox.Show(ee1.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
protected override bool Run(object parameters) { List <IpCity> rangeData = (List <IpCity>)parameters; rangeData.Clear(); FbConnection db = new FbConnection(_settings.DatabaseConnectionString); try { db.Open(); string SQL = "SELECT ipc.WD$ID, COALESCE(c.WD$COUNTRY_CODE, 'Unknown'), COALESCE(ipc.WD$REGION, ''), " + "COALESCE(ipc.WD$CITY, ''), COALESCE(ipc.WD$LATITUDE, 0.0), COALESCE(ipc.WD$LONGITUDE, 0.0), " + "COALESCE(c.WD$FROM_IP, 0), COALESCE(c.WD$TO_IP, 0) FROM WD$IPTOCOUNTRY c " + "LEFT JOIN WD$IPCITY ipc ON (ipc.WD$ID = c.WD$CITY_ID) "; string whereClause = String.Empty; foreach (string countryCode in _settings.CountryList) { if (String.IsNullOrEmpty(countryCode)) { continue; } if (whereClause.Length > 0) { whereClause += ", "; } whereClause += $"'{countryCode}'"; } if (!String.IsNullOrEmpty(whereClause)) { SQL += $"WHERE c.WD$COUNTRY_CODE IN ({whereClause})"; } FbTransaction tran = db.BeginTransaction(); try { FbCommand cmd = new FbCommand(SQL, db, tran); try { FbDataReader rdr = cmd.ExecuteReader(); try { while (rdr.Read()) { rangeData.Add(new IpCity(rdr.GetInt64(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetInt64(6), rdr.GetInt64(7))); } } finally { rdr.Close(); rdr = null; } } finally { cmd.Dispose(); cmd = null; } } finally { tran.Rollback(); tran.Dispose(); tran = null; } } finally { db.Close(); db.Dispose(); } rangeData.Sort(); return(false); }
private void carregarDgView() { string squery; string query2; int i = 0; squery = string.Format("SELECT * FROM CRUZAMENTO WHERE (ID_FAZENDA = {0})", frmHome.IDFazendaSelecionada); FbConnection fbConn = new FbConnection(frmHome.strConn); FbCommand fbCmd = new FbCommand(squery, fbConn); try { fbConn.Open(); FbDataReader r = fbCmd.ExecuteReader(); while (r.Read()) { dataGridView1.Rows.Add(); if (r["SEMEM"].ToString() == "1") { try { string query3; query2 = string.Format("SELECT NOME FROM S***N WHERE (ID = {0})", r["ID_GADO_MACHO"]); FbCommand fbCmd2 = new FbCommand(query2, fbConn); FbDataReader r2 = fbCmd2.ExecuteReader(); r2.Read(); dataGridView1.Rows[i].Cells["Reprodutor"].Value = r2["NOME"].ToString(); dataGridView1.Rows[i].Cells["Numero"].Value = "0"; query3 = string.Format("SELECT NOME, NUMERO FROM GADO WHERE (ID = {0})", r["ID_GADO_FEMEA"]); FbCommand fbCmd3 = new FbCommand(query3, fbConn); FbDataReader r3 = fbCmd3.ExecuteReader(); r3.Read(); idfemea.AddLast(Convert.ToInt32(r["ID_GADO_FEMEA"].ToString())); dataGridView1.Rows[i].Cells["Vaca"].Value = r3["NOME"].ToString(); dataGridView1.Rows[i].Cells["NumeroVaca"].Value = r3["NUMERO"].ToString(); } catch (FbException fbex) { MessageBox.Show("Erro no banco de dados: " + fbex.ToString()); } } else { try { string query3; query2 = string.Format("SELECT NOME, NUMERO FROM GADO WHERE (ID = {0})", r["ID_GADO_MACHO"]); FbCommand fbCmd2 = new FbCommand(query2, fbConn); FbDataReader r2 = fbCmd2.ExecuteReader(); if (r2.Read()) { dataGridView1.Rows[i].Cells[1].Value = r2[0].ToString(); dataGridView1.Rows[i].Cells[2].Value = r2[1].ToString(); } else { MessageBox.Show("Falha ao acessar o banco de dados."); } query3 = string.Format("SELECT NOME, NUMERO FROM GADO WHERE (ID = {0})", r["ID_GADO_FEMEA"]); idfemea.AddLast(Convert.ToInt32(r["ID_GADO_FEMEA"].ToString())); FbCommand fbCmd3 = new FbCommand(query3, fbConn); FbDataReader r3 = fbCmd3.ExecuteReader(); r3.Read(); dataGridView1.Rows[i].Cells["Vaca"].Value = r3["NOME"].ToString(); dataGridView1.Rows[i].Cells["NumeroVaca"].Value = r3["NUMERO"].ToString(); } catch (FbException fbex) { MessageBox.Show("Erro no banco de dados: " + fbex.ToString()); } } dataGridView1.Rows[i].Cells["Data"].Value = r["DATA_CRUZAMENTO"].ToString(); ids.AddLast(Convert.ToInt32(r["ID"].ToString())); i++; } } catch (FbException fbex) { MessageBox.Show("Erro no banco de dados: " + fbex.ToString()); } }
private void FamilySelectForm_Shown(object sender, EventArgs e) { FbConnection fb_con = new FbConnection(ConnectionString); if (fb_con.State == System.Data.ConnectionState.Closed) { fb_con.Open(); } FbTransaction fb_trans = null; fb_trans = fb_con.BeginTransaction(); FbCommand SelectFamilies = new FbCommand(); FbDataReader readerFamilies = null; try { try { /* Select full list of the reports families */ SelectFamilies.CommandText = "select id, pos, name, displayname, visible, frselect from reportfamily where visible = 1 order by pos"; SelectFamilies.Connection = fb_con; SelectFamilies.Transaction = fb_trans; readerFamilies = SelectFamilies.ExecuteReader(); // ... Create a list of the families while (readerFamilies.Read()) { families.Add(new ReportFamily((int)readerFamilies["id"], (int)readerFamilies["pos"], (string)readerFamilies["name"], (string)readerFamilies["displayname"], (int)readerFamilies["visible"], (int)readerFamilies["frselect"], 0)); } readerFamilies.Close(); /* Select families for every report */ SelectFamilies.CommandText = "select famid from get_family_result(@idr)"; SelectFamilies.Connection = fb_con; SelectFamilies.Transaction = fb_trans; // ... create new parameter FbParameter par_resid = new FbParameter("@idr", FbDbType.Integer); // ... for every result, which was passed into the form for (int i = 0; i < results.Count; i++) { // ... clear the parameters set of the query SelectFamilies.Parameters.Clear(); // ... set new value of the parameter par_resid.Value = results[i].IdRes; // ... add current parameter to the query SelectFamilies.Parameters.Add(par_resid); readerFamilies = SelectFamilies.ExecuteReader(); // ... calculate numver of the families, we'd been able to build for given result while (readerFamilies.Read()) { int fam_id = GetFamilyIndex(families, (int)readerFamilies[0]); if (fam_id > -1) { families[fam_id].RepCount++; } } readerFamilies.Close(); } fb_trans.Commit(); // Before showing of the families list, we need to clear the families list // and delete families with RepCount == 0 for (int i = 0; i < families.Count;) { if (families[i].RepCount == 0) { families.RemoveAt(i); // if RepCount == 0, then we remove the Family } else { i++; // else we should take the next element of the families list } } // And now we can show selected families this.groupBox1.AutoSize = true; this.groupBox1.Controls.Clear(); for (int i = 0; i < families.Count; i++) { RadioButton rb = new RadioButton(); rb.Text = families[i].DispName; rb.Name = "RadioButton" + i.ToString(); rb.Location = new Point(5, 30 * (i + 1)); rb.AutoSize = true; // checkedListBox1.Items.Add(readerReports.GetString(1), false); groupBox1.Controls.Add(rb); } } catch (Exception ex) { MessageBox.Show("Error Info = " + ex.ToString()); fb_trans.Rollback(); } } finally { if (readerFamilies != null) { readerFamilies.Close(); } if (fb_trans != null) { fb_trans.Dispose(); } if (SelectFamilies != null) { SelectFamilies.Dispose(); } if (fb_con != null && fb_con.State == System.Data.ConnectionState.Open) { fb_con.Close(); } } }
/** * @Kyle van Raaij * Checks what the rights are and looks in the database if there are menuitems for that right. * if so the menuitem will be created and added to the list. when all the rights are checked * a list with menuitems wil be returned. * * @_userid the id of the user * @return List with menuitems **/ public List <MenuItem> MakeMenu(string _userid) { List <MenuItem> menuItems = new List <MenuItem>(); FbConnection connection = dbconn.GetFbConnection(); DBAutorisation autorisation = new DBAutorisation(); foreach (int item in autorisation.GetRights(_userid)) { FbCommand cmd = new FbCommand("SELECT * FROM CCDA_MENUITEM WHERE CCDA_M_ID IN (SELECT CCDA_MR_M_ID FROM CCDA_MENURIGHT WHERE CCDA_MR_AR_ID = @id); ", connection) { CommandType = CommandType.Text }; cmd.Parameters.Add("@id", item); connection.Open(); FbDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read() && reader.GetInt16(0) != 0) { MenuItem menuItem = new MenuItem(); if (!reader.IsDBNull(0)) { menuItem.Id = reader.GetInt16(0); } //Menu id if (!reader.IsDBNull(1)) { menuItem.Name = reader.GetString(1); } //Description if (!reader.IsDBNull(2)) { menuItem.ParentID = reader.GetInt16(2); } //Parent id if (!reader.IsDBNull(3)) { menuItem.Page = reader.GetString(3); } //Action menuItems.Add(menuItem); } } connection.Close(); } //Zet alle menuItems op de juiste plaats neer. List <MenuItem> menuItems2 = new List <MenuItem>(); //menuItems.RemoveAt(menuItems.Count - 1); foreach (MenuItem menuItem in menuItems) { if (menuItem.ParentID == 0) { menuItems2.Add(menuItem); } } foreach (MenuItem menuItem in menuItems) { if (menuItem.ParentID != 0) { foreach (MenuItem item in menuItems2) { if (menuItem.ParentID == item.Id) { item.AddSubItem(menuItem); } } } } return(menuItems2); }
public string RefreshIndicesAndGetTablename(string _indexObjectName) { string cmd_index = IndexSQLStatementsClass.Instance.GetIndiciesByName(_dbReg.Version, _indexObjectName.Trim()); _dataFilled = false; txtIndexName.Text = _indexObjectName.Trim(); string TableName = string.Empty; try { lvFields.Items.Clear(); var con = new FbConnection(ConnectionStrings.Instance.MakeConnectionString(_dbReg)); con.Open(); string pkColumn = string.Empty; string indexName = string.Empty; string indexColumnName = string.Empty; string oldIndexColumnName = string.Empty; int Unique = 0; int IndexType = -1; bool Active = false; FbCommand fcmd = new FbCommand(cmd_index, con); var dread = fcmd.ExecuteReader(); if (dread.HasRows) { while (dread.Read()) { TableName = dread.GetValue(0).ToString().Trim(); indexColumnName = dread.GetValue(2).ToString().Trim(); Unique = StaticFunctionsClass.ToIntDef(dread.GetValue(3).ToString().Trim(), 0); Active = StaticFunctionsClass.ToIntDef(dread.GetValue(4).ToString().Trim(), 0) != 1; IndexType = StaticFunctionsClass.ToIntDef(dread.GetValue(5).ToString().Trim(), 0); cbUnique.Checked = Unique > 0; ckActive.Checked = Active; if (IndexType < 0) { cbSorting.SelectedItem = eSort.NONE; } else if (IndexType == 0) { cbSorting.SelectedItem = eSort.ASC; } else if (IndexType == 1) { cbSorting.SelectedItem = eSort.DESC; } string[] lv = new string[1]; lv[0] = indexColumnName; if (this.oldIndexColumnName != indexColumnName) { ListViewItem lvi = new ListViewItem(indexColumnName); lvFields.Items.Add(lvi); this.oldIndexColumnName = indexColumnName; } } _dataFilled = true; } con.Close(); } catch (Exception ex) { _localNotify?.AddToERROR($@"{StaticFunctionsClass.DateTimeNowStr()}->IndexForm->RefreshIndicesAndGetTablename()->{ex.Message}"); } return(TableName); }
/* * Print reports selected via selection of the reports family for every result * * */ private void PrintFamily() { FbConnection fb_con = new FbConnection(ConnectionString); if (fb_con.State == System.Data.ConnectionState.Closed) { fb_con.Open(); } FbTransaction fb_trans = null; fb_trans = fb_con.BeginTransaction(); FbCommand SelectReports = new FbCommand(); FbDataReader readerReports = null; int IdFam = GetFamilyd(); int FamIndex = GetFamilyIndex(IdFam); int IdReport = 0; ReportBuilder rep_builder = null; try { try { // Prepare the query, receiving the report's ID SelectReports.CommandText = "select first 1 id from get_family_result_report(@idr, @famid)"; SelectReports.Connection = fb_con; SelectReports.Transaction = fb_trans; // ... and its parameters FbParameter par_res = new FbParameter("@idr", FbDbType.Integer); FbParameter par_fam = new FbParameter("@famid", FbDbType.Integer); par_fam.Value = IdFam; // For every result we'll print the report, that is corresponding the selected family for (int i = 0; i < results.Count; i++) { // ... prepare query's parameters SelectReports.Parameters.Clear(); par_res.Value = results[i].IdRes; SelectReports.Parameters.Add(par_res); SelectReports.Parameters.Add(par_fam); // ... execute query and obtain the report's ID readerReports = SelectReports.ExecuteReader(); if (readerReports.Read()) { IdReport = (int)readerReports[0]; for (int copies = Convert.ToInt32(Copies.Value); copies > 0; copies--) { // Create a new report builder // ConnectionString - parameters to connect to the database // results[i].IdRes - the ID of the current result // ReqId - the current value of the Requiered ID (ID of the session) // ShowPrint == false (the 4th parameter) - in this case the builder will print the report, but don't show the report's preview // ShowFrDialog (the 5th parameter) - the dialog for the selection of the fractions will be shown, if just ... // ... the selected family requires to select the fractions and groups before the report's building // ... this is the very 1st reports ought to be printed // ... this is the very 1st cpoy of the report ought to be printed // NeedClear (the 6th parameter) influes at the process of the database cleaning // (in part of the temporary data, which were used during the report creation) // It'll be settled to the TRUE, if ... // ... it is the very last report in the queue of the reports ought to be printed // ... it is the last copy of the report (because of the parameters of the cycle the last copy will have the number 1) rep_builder = new ReportBuilder(ConnectionString, results[i].IdRes, ReqId, false, families[FamIndex].FrSelect == 1 && i == 0 && copies == Convert.ToInt32(Copies.Value), i == results.Count - 1 && copies == 1); rep_builder.BuildReport(IdReport); } } readerReports.Close(); } } catch (Exception ex) { MessageBox.Show("Error Info = " + ex.ToString()); fb_trans.Rollback(); } } finally { if (readerReports != null) { readerReports.Close(); } if (fb_trans != null) { fb_trans.Dispose(); } if (SelectReports != null) { SelectReports.Dispose(); } if (fb_con != null && fb_con.State == System.Data.ConnectionState.Open) { fb_con.Close(); } } }
public List <Transact> GetLastRecords() { using (var connection = new FbConnection(_connectionString)) { try { var dates = DateTime.Now; //dates = dates.AddMonths(-4); //dates = dates.AddDays(-18); var transactions = new List <Transact>(); //var select = "SELECT TR_SQ,TR_DATE,TR_TIME,TR_TAGCODE,TR_MSTSQ,TR_TAGTYPE,TR_DIRECTION" + // " FROM transack WHERE TR_TERMSLA IN( '" + _interminal + "','" + _outterminal + "')" + //" AND TR_DATE = " + dates.Year.ToString() + dates.Month.ToString().PadLeft(2, '0') + //dates.Day.ToString().PadLeft(2, '0') + " AND TR_TIME < " + dates.Hour.ToString() + //dates.Minute.ToString().PadLeft(2, '0') + dates.Second.ToString().PadLeft(2, '0'); var select = "SELECT FIRST 10 TR_SQ,TR_DATE,TR_TIME,TR_TAGCODE,TR_MSTSQ,TR_TAGTYPE,TR_DIRECTION FROM transack"; using (var command = new FbCommand(select, connection)) { connection.Open(); var result = command.ExecuteReader(CommandBehavior.CloseConnection); if (result == null) { return(transactions); } if (!result.HasRows) { return(transactions); } while (result.Read()) { var date = result.TestNull("TR_DATE").ToString(); var time = result.TestNull("TR_TIME").ToString().PadLeft(6, '0'); transactions.Add(new Transact() { Id = Convert.ToInt64(result.TestNull("TR_SQ")), Date = IntToDateTime(date, time), TagCode = result.TestNull("TR_TAGCODE").ToString(), Mstsqs = result.TestNull("TR_MSTSQ").ToString(), TagType = result.TestNull("TR_TAGTYPE").ToString(), Direction = Convert.ToInt16(result.TestNull("TR_DIRECTION")), }); } } return(transactions); } catch (StackOverflowException ex) { throw; } catch (NullReferenceException ex) { throw new FbConnectionException(ex, "Firebird Exception"); } catch (FbException ex) { throw new FbConnectionException(ex); } catch (Exception ex) { throw; } } }
public ViewFinalExam(int examid) { InitializeComponent(); this.examid = examid; string[] foo; string bar = ""; if (fb.State == ConnectionState.Closed) //SELECT CURRENT EXAM { fb.Open(); } FbTransaction fbt = fb.BeginTransaction(); FbCommand SelectSQL = new FbCommand("SELECT final_exams.*, users.name, users.surname, users.patronymic, course.name FROM final_exams, users, course WHERE final_exams.id = " + examid + " AND users.id = final_exams.user_id AND course.id = final_exams.course_id", fb); SelectSQL.Transaction = fbt; FbDataReader reader = SelectSQL.ExecuteReader(); reader.Read(); for (int i = 0; i < 10; i++) { examinfo[i] = reader[i].ToString(); } reader.Close(); SelectSQL.Dispose(); fbt.Commit(); string questionlist = examinfo[3]; FbTransaction fbt1 = fb.BeginTransaction(); //SELECT CURRENT QUESTIONS FbCommand SelectSQL1 = new FbCommand("SELECT * FROM question WHERE id IN (" + questionlist + ")", fb); SelectSQL1.Transaction = fbt1; FbDataReader reader1 = SelectSQL1.ExecuteReader(); while (reader1.Read()) { bar = bar + reader1[0].ToString() + "|" + reader1[1].ToString() + "|" + reader1[2].ToString() + "|" + reader1[3].ToString() + "|" + reader1[4].ToString() + "|" + reader1[5].ToString() + "|" + reader1[6].ToString() + "|" + reader1[7].ToString() + "|" + reader1[8].ToString() + "\n"; } reader1.Close(); SelectSQL1.Dispose(); fbt1.Commit(); fb.Close(); bar = bar.Substring(0, bar.Length - 1); foo = bar.Split('\n'); string[][] qList = new string[foo.Length][]; for (int i = 0; i < foo.Length; i++) { qList[i] = foo[i].Split('|'); } this.questionlist = qList; int min = 0; int sec = 0; string[] time = examinfo[5].Split(':'); if (time.Count() > 1) { min = int.Parse(time[0]); sec = int.Parse(time[1]); } ExamTimeLabel.Text = ExamTimeLabel.Text + min.ToString() + ":" + sec.ToString(); ResultLabel.Text = ResultLabel.Text + " " + calculate.Percent(examid) + "%"; UserNameLabel.Text = examinfo[6] + " " + examinfo[7] + " " + examinfo[8]; }
/// <summary> /// Возвращает список участков схемы фидеров всей подстанции /// </summary> /// <param name="substation">Подстанция</param> /// <returns>Список участков схемы, список вершин графа схемы</returns> public FiderScheme GetSubstationLines(Model.Substation substation) { FiderScheme result = new FiderScheme(); string query; int id_substation = substation.ID; // словарь вершин ИД вершин List <Model.LineVertex> substationFidersLinesVertities = new List <Model.LineVertex>(); // список вершин графа List <Graph.FiderGraphVertex> substationFidersVertitiesList = new List <Graph.FiderGraphVertex>(); // словарь вершин графа Dictionary <int, Graph.FiderGraphVertex> substationFidersVertities = new Dictionary <int, Graph.FiderGraphVertex>(); // список ребер графа List <Graph.FiderGraphEdge> substationFidersEdges = new List <Graph.FiderGraphEdge>(); // общий список участков List <Model.Line> substationFidersLineNodesList = new List <Model.Line>(); // счётчик ИД ребер графа int edgeIndex = 1; // для каждого фидера подстанции foreach (TMP.DWRES.Objects.Fider fider in substation.Fiders) { // получение списка вершин ICollection <Model.LineVertex> currentFiderLinesVertities = GetLinesVertities(fider.ID); List <Graph.FiderGraphVertex> currentFiderVertitiesList = new List <Graph.FiderGraphVertex>(currentFiderLinesVertities.Count); // заполняем словарь вершин foreach (Model.LineVertex item in currentFiderLinesVertities) { // создание вершины графа Graph.FiderGraphVertex vertex = new Graph.FiderGraphVertex(item.ID); currentFiderVertitiesList.Add(vertex); // словарь вершин графа с ключём - кодом вершины if (substationFidersVertities.ContainsKey(item.ID) == false) { substationFidersVertities.Add(item.ID, vertex); } } // вершины графа трёх типов: центр питания (ЦП) или узел подстанции, просто узел и узел, представляющий подстанцию // полученные выше списки не содержат наименования узла, для его получения создаём следующие таблицы #region Таблица для поиска названия узла ТП по его ИД Dictionary <int, string> dictionaryNodeTPNames = new Dictionary <int, string>(); query = String.Format( @"SELECT NODELINKS.ID, CAST((TP.NAME||'/'||SECTTP.NAME) AS CHAR(100)) NODE_NAME FROM TP INNER JOIN SECTTP ON (SECTTP.ID_PST = TP.ID) INNER JOIN NODELINKS ON (SECTTP.ID_NODELINKS = NODELINKS.ID) WHERE NODELINKS.NODE_TYPE=1", fider.ID); try { using (var command = new FbCommand(query, fbConnection)) { if (fbConnection.State == System.Data.ConnectionState.Closed) { fbConnection.Open(); } FbDataReader reader = command.ExecuteReader(); if (reader.HasRows) { try { while (reader.Read()) { int key = reader[0] != DBNull.Value ? (int)reader[0] : default(int); string value = reader[1] != DBNull.Value ? (string)reader[1] : default(string); dictionaryNodeTPNames.Add(key, value); } } finally { reader.Close(); } } } } catch (Exception ex) { HasError = true; LastException = ex; return(result); } #endregion Таблица для поиска названия узла ТП по его ИД #region Таблица для поиска названия узла по его ИД Dictionary <int, string> dictionaryNodeNames = new Dictionary <int, string>(); query = String.Format( @"SELECT NODELINKS.ID, NODE.NAME NODE_NAME FROM NODE INNER JOIN NODELINKS ON (NODE.ID_NODELINKS = NODELINKS.ID) WHERE NODELINKS.NODE_TYPE=2 AND NODELINKS.ID IN ( SELECT LINE.ID_NODE1 NODEID FROM LINE WHERE LINE.ID_FIDER = {0} UNION SELECT LINE.ID_NODE2 NODEID FROM LINE WHERE LINE.ID_FIDER = {0} )", fider.ID); try { using (var command = new FbCommand(query, fbConnection)) { if (fbConnection.State == System.Data.ConnectionState.Closed) { fbConnection.Open(); } FbDataReader reader = command.ExecuteReader(); if (reader.HasRows) { try { while (reader.Read()) { int key = reader[0] != DBNull.Value ? (int)reader[0] : default(int); string value = reader[1] != DBNull.Value ? (string)reader[1] : default(string); if (dictionaryNodeNames.ContainsKey(key) == false) { dictionaryNodeNames.Add(key, value); } } } finally { reader.Close(); } } } } catch (Exception ex) { HasError = true; LastException = ex; return(result); } #endregion Таблица для поиска названия узла по его ИД #region Таблица для поиска названия узла подстанции по его ИД Dictionary <int, string> dictionaryNodePSTName = new Dictionary <int, string>(); query = String.Format( @"SELECT NODELINKS.ID, CAST(('[ЦП] - '||PST.NAME||'/'||SECTPST.NAME) AS CHAR(100)) NODE_NAME FROM PST INNER JOIN SECTPST ON (SECTPST.ID_PST = PST.ID) INNER JOIN NODELINKS ON (SECTPST.ID_NODELINKS = NODELINKS.ID) WHERE NODELINKS.NODE_TYPE=3 AND SECTPST.ID = {0}", fider.ID_Sect_NN); try { using (var command = new FbCommand(query, fbConnection)) { if (fbConnection.State == System.Data.ConnectionState.Closed) { fbConnection.Open(); } FbDataReader reader = command.ExecuteReader(); if (reader.HasRows) { try { while (reader.Read()) { int key = reader[0] != DBNull.Value ? (int)reader[0] : default(int); string value = reader[1] != DBNull.Value ? (string)reader[1] : default(string); dictionaryNodePSTName.Add(key, value); } } finally { reader.Close(); } } } } catch (Exception ex) { HasError = true; LastException = ex; return(result); } #endregion Таблица для поиска названия узла подстанции по его ИД // список участков схемы фидера (узел задан ИД) ICollection <Model.Line> lineNodesList = GetLines(fider.ID); // цикл по всем участкам схемы фидера foreach (var item in lineNodesList) { FillVertexProperties(item.NodeStartId, ref dictionaryNodeTPNames, ref dictionaryNodeNames, ref dictionaryNodePSTName, ref substationFidersVertities); FillVertexProperties(item.NodeEndId, ref dictionaryNodeTPNames, ref dictionaryNodeNames, ref dictionaryNodePSTName, ref substationFidersVertities); item.NodeStart = substationFidersVertities[item.NodeStartId].Name; item.NodeEnd = substationFidersVertities[item.NodeEndId].Name; // добавление ребра графа substationFidersEdges.Add(new Graph.FiderGraphEdge(edgeIndex, substationFidersVertities[item.NodeStartId], substationFidersVertities[item.NodeEndId])); edgeIndex++; } // добавление в общий список substationFidersLineNodesList.AddRange(lineNodesList); substationFidersVertitiesList.AddRange(currentFiderVertitiesList); } result.Lines = substationFidersLineNodesList; result.GraphVertities = substationFidersVertitiesList; result.GraphEdges = substationFidersEdges; return(result); }
private void cihazlar_SelectedIndexChanged_1(object sender, EventArgs e) { string[] ads = cihazlar.Text.Split(' '); islemler.Items.Clear(); FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu()); baglan.Open(); FbCommand TkpNoSql = new FbCommand("SELECT * FROM Teknik_Takip WHERE Takip_id='" + ads[0] + "'", baglan); object sonuc; FbDataReader TkpNoOku; sonuc = TkpNoSql.ExecuteScalar(); if (sonuc != null) { TkpNoOku = TkpNoSql.ExecuteReader(); TkpNoOku.Read(); grup3.Enabled = true; id = TkpNoOku["Takip_id"].ToString(); if (int.Parse(TkpNoOku["Takip_durum"].ToString()) == 1) { grup5.Enabled = true; } else { grup5.Enabled = false; } cihazbilgi.Text = TkpNoOku["Takip_marka"].ToString() + " " + TkpNoOku["Takip_model"].ToString(); this.Text = cihazbilgi.Text; baglan2.Open(); FbCommand TakipGunSqlSay = new FbCommand("SELECT COUNT(*) FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); object say = TakipGunSqlSay.ExecuteScalar(); baglan2.Close(); baglan2.Open(); FbCommand TakipGunSql = new FbCommand("SELECT * FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); if (int.Parse(say.ToString()) > 0) { FbDataReader TakipGunOku = TakipGunSql.ExecuteReader(); while (TakipGunOku.Read()) { islemler.Items.Add(TakipGunOku["Takip_bilgi"].ToString()); } } else { islemler.Items.Add("Bu cihazda şuanda işlem yapılmamıştır"); } baglan2.Close(); if (TkpNoOku["Takip_musteri"].ToString() != "") { string mmid = TkpNoOku["Takip_musteri"].ToString(); baglan2.Open(); FbCommand MusSql = new FbCommand("SELECT Musteri_adi,Musteri_soyadi,Musteri_telefon FROM Musteriler WHERE Musteri_id='" + mmid + "'", baglan2); FbDataReader MusOku = MusSql.ExecuteReader(); MusOku.Read(); musadsoyad.Text = MusOku["Musteri_adi"].ToString() + " " + MusOku["Musteri_soyadi"].ToString(); mustel.Text = MusOku["Musteri_telefon"].ToString(); baglan2.Close(); } else { musadsoyad.Text = TkpNoOku["Takip_kisibilgi"].ToString(); mustel.Text = TkpNoOku["Takip_kisiTel"].ToString(); } } else { MessageBox.Show("Takip bulunamadı ( Hata kodu: T-04 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } baglan.Close(); }
public bool Le() { string sql = "select " + "COD_EMPRESA," + "NOM_EMPRESA," + "NRO_REMESSA," + "NRO_NOSSO," + "COD_CARTEIRA," + "COD_AGENCIA," + "DIG_AGENCIA," + "COD_CONTA," + "DIG_CONTA," + "PER_MULTA," + "VLR_BONIFICACAO," + "VLR_ATRASO," + "QTD_PRAZO_DESCONTO," + "VLR_DESCONTO," + "DES_MENSAGEM1," + "DES_MENSAGEM2," + "DES_MENSAGEM3," + "DES_MENSAGEM4 " + "from parametros_remessa"; FbCommand cmd = new FbCommand(sql, Globais.bd); FbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { COD_EMPRESA = reader.GetString(0).Trim(); NOM_EMPRESA = reader.GetString(1).Trim(); NRO_REMESSA = reader.GetInt32(2); NRO_NOSSO = reader.GetInt32(3); COD_CARTEIRA = reader.GetInt16(4); COD_AGENCIA = reader.GetInt32(5); DIG_AGENCIA = reader.GetInt16(6); COD_CONTA = reader.GetInt32(7); DIG_CONTA = reader.GetInt16(8); PER_MULTA = reader.GetFloat(9); VLR_BONIFICACAO = reader.GetFloat(10); VLR_ATRASO = reader.GetFloat(11); QTD_PRAZO_DESCONTO = reader.GetInt16(12); VLR_DESCONTO = reader.GetFloat(13); DES_MENSAGEM1 = reader.GetString(14).Trim(); DES_MENSAGEM2 = reader.GetString(15).Trim(); DES_MENSAGEM3 = reader.GetString(16).Trim(); DES_MENSAGEM4 = reader.GetString(17).Trim(); reader.Close(); return(true); } COD_EMPRESA = ""; NOM_EMPRESA = ""; NRO_REMESSA = 0; NRO_NOSSO = 0; COD_CARTEIRA = 0; COD_AGENCIA = 0; DIG_AGENCIA = 0; COD_CONTA = 0; DIG_CONTA = 0; PER_MULTA = 0; VLR_BONIFICACAO = 0; VLR_ATRASO = 0; QTD_PRAZO_DESCONTO = 0; VLR_DESCONTO = 0; DES_MENSAGEM1 = ""; DES_MENSAGEM2 = ""; DES_MENSAGEM3 = ""; DES_MENSAGEM4 = ""; reader.Close(); sql = "insert into parametros_remessa values('','',0,0,0,0,0,0,0,0,0,0,0,0,'','','','')"; cmd = new FbCommand(sql, Globais.bd); try { Log.Grava(Globais.sUsuario, cmd.CommandText); cmd.ExecuteNonQuery(); return(true); } catch { return(false); } }
private void button10_Click(object sender, EventArgs e) { int estadoID = 0; FbConnection con = new FbConnection(); con.ConnectionString = Properties.Settings.Default.BancoFirebird; FbCommand cmd = new FbCommand("select * from nfe", con); con.Open(); FbDataReader DR; DR = cmd.ExecuteReader(); DataTable DT = new DataTable(); DT.Load(DR); con.Close(); DataView dv = new DataView(DT); dataGridView2.DataSource = dv; DataContextFirebird dbFb = new DataContextFirebird(); NotaFreeNFE nf = new NotaFreeNFE(); // cadastro de cliente var cliente = db.Clientes.Find(Convert.ToInt32(txtcodCliente.Text.Trim())); FbConnection conCl = new FbConnection(); conCl.ConnectionString = Properties.Settings.Default.BancoFirebird; string selectC = "select ID from PESSOA WHERE ID = " + cliente.ID; FbCommand cmd2 = new FbCommand(selectC, conCl); try { var cidMs = db.Cidades.Find(cliente.CidadeCodigo); var cidFb = dbFb.Municipios.Where(x => x.Ibge == cidMs.Ibge).FirstOrDefault(); con.Open(); FbDataReader dr = cmd2.ExecuteReader(); if (dr.Read()) { } else { string insertC = "INSERT INTO PESSOA(ID, ID_MUNICIPIO, ID_ESTADO, ID_PAIS, ID_FILIAL, TIPO, PESSOA, RAZAO, FANTASIA, )" + "CNPJCPF, IE, ISENTO_ICMS, CEP, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO, EMAIL, FONE, CELULAR, CONTATO, " + "OBS, CADASTRO, CNAE, INDFINAL, EXIBIROBS) VALUES @(ID, @ID_MUNICIPIO, @ID_ESTADO, @ID_PAIS, @ID_FILIAL, @TIPO, @PESSOA, @RAZAO, @FANTASIA, " + "@CNPJCPF, @IE, @ISENTO_ICMS, @CEP, @ENDERECO, @NUMERO, @COMPLEMENTO, @BAIRRO, @EMAIL, @FONE, @CELULAR, @CONTATO, " + "@OBS, @CADASTRO, @CNAE, @INDFINAL, @EXIBIROBS)"; cmd2.Parameters.Add("@ID", FbDbType.Integer).Value = cliente.ID; cmd2.Parameters.Add("@ID_MUNICIPIO", FbDbType.Integer).Value = cidFb.ID; cmd2.Parameters.Add("@ID_ESTADO", FbDbType.Integer).Value = cidFb.EstadoID; estadoID = cidFb.EstadoID; cmd2.Parameters.Add("@ID_PAIS", FbDbType.Integer).Value = 1058; cmd2.Parameters.Add("@ID_FILIAL", FbDbType.Integer).Value = 1; cmd2.Parameters.Add("@TIPO", FbDbType.Integer).Value = 0; cmd2.Parameters.Add("@PESSOA", FbDbType.VarChar).Value = "J"; cmd2.Parameters.Add("@RAZAO", FbDbType.VarChar).Value = cliente.Razao; cmd2.Parameters.Add("@FANTASIA", FbDbType.VarChar).Value = cliente.Razao; cmd2.Parameters.Add("@CNPJCPF", FbDbType.VarChar).Value = cliente.Cpf; cmd2.Parameters.Add("@IE", FbDbType.VarChar).Value = cliente.IE; cmd2.Parameters.Add("@ISENTO_ICMS", FbDbType.Integer).Value = 0; cmd2.Parameters.Add("@CEP", FbDbType.VarChar).Value = cliente.Cep; cmd2.Parameters.Add("@ENDERECO", FbDbType.VarChar).Value = cliente.Endereco; cmd2.Parameters.Add("@NUMERO", FbDbType.VarChar).Value = cliente.Numero; cmd2.Parameters.Add("@COMPLEMENTO", FbDbType.VarChar).Value = ""; cmd2.Parameters.Add("@BAIRRO", FbDbType.VarChar).Value = cliente.Bairro; cmd2.Parameters.Add("@EMAIL", FbDbType.VarChar).Value = cliente.Email; cmd2.Parameters.Add("@FONE", FbDbType.VarChar).Value = cliente.Telefone; cmd2.Parameters.Add("@CELULAR", FbDbType.VarChar).Value = cliente.Celular; cmd2.Parameters.Add("@CONTATO", FbDbType.VarChar).Value = ""; cmd2.Parameters.Add("@OBS", FbDbType.VarChar).Value = cliente.Observacoes; cmd2.Parameters.Add("@CADASTRO", FbDbType.Date).Value = DateTime.Now.Date; cmd2.Parameters.Add("@CNAE", FbDbType.VarChar).Value = ""; cmd2.Parameters.Add("@INDFINAL", FbDbType.Integer).Value = 0; cmd2.Parameters.Add("@EXIBIROBS", FbDbType.Integer).Value = 1; try { cmd2.CommandType = CommandType.Text; conCl.Open(); int iii = cmd.ExecuteNonQuery(); if (iii > 0) { } } catch (Exception) { throw; } } } catch (Exception) { throw; } //cadastro de produto var i = 0; foreach (var row in this.dataGridView1.Rows) { i = i + 1; var dataGridViewRow = row as DataGridViewRow; var celula = dataGridViewRow.Cells["cod_produto"]; var valVenda = dataGridViewRow.Cells[3]; var valor = celula.Value; if (valor != DBNull.Value) { var produto = db.Produtos.Find(valor); string select = "select ID from PRODUTO WHERE ID = " + valor; FbCommand cmd1 = new FbCommand(select, con); try { string comando = ""; FbConnection conFb = new FbConnection(); conFb.ConnectionString = Properties.Settings.Default.BancoFirebird; FbCommand cmdFb = new FbCommand(comando, conFb); con.Open(); FbDataReader dr = cmd1.ExecuteReader(); if (dr.Read()) { comando = "update produto set PRECO = " + produto.ValorVenda + "where ID = " + valor; } else { comando = "insert into PRODUTO(ID, ID_GRUPO, ID_SUBGRUPO, ID_TRIBUTACAO, NCM, CODIGO , DESCRICAO, UNIDADE, TIPO, " + " SITUACAO, PRECO, ORIGEM, CEST) VALUES (@ID, @ID_GRUPO, @ID_SUBGRUPO, @ID_TRIBUTACAO, @NCM, @CODIGO , " + "@DESCRICAO, @UNIDADE, @TIPO, @SITUACAO, @PRECO, @ORIGEM, @CEST)"; cmdFb.Parameters.Add("@ID", FbDbType.Integer).Value = produto.ID; cmdFb.Parameters.Add("@ID_GRUPO", FbDbType.Integer).Value = 1; cmdFb.Parameters.Add("@ID_SUBGRUPO", FbDbType.Integer).Value = 1; cmdFb.Parameters.Add("@ID_TRIBUTACAO", FbDbType.Integer).Value = 1; cmdFb.Parameters.Add("@NCM, @CODIGO", FbDbType.VarChar).Value = produto.NCM; cmdFb.Parameters.Add("@CODIGO", FbDbType.VarChar).Value = produto.Referencia; cmdFb.Parameters.Add("@DESCRICAO", FbDbType.VarChar).Value = produto.DescricaoReduzida; cmdFb.Parameters.Add("@UNIDADE", FbDbType.VarChar).Value = produto.UnidadeMedida; cmdFb.Parameters.Add("@TIPO", FbDbType.SmallInt).Value = 0; cmdFb.Parameters.Add("@SITUACAO", FbDbType.SmallInt).Value = 0; cmdFb.Parameters.Add("@PRECO", FbDbType.Decimal).Value = valVenda; cmdFb.Parameters.Add("@ORIGEM", FbDbType.Integer).Value = produto.OrigemDoProduto; cmdFb.Parameters.Add("@CEST", FbDbType.VarChar).Value = produto.CEST; } try { cmdFb.CommandType = CommandType.Text; conFb.Open(); int ii = cmd.ExecuteNonQuery(); if (ii > 0) { } } catch (Exception ex) { u.messageboxErro(ex.ToString()); throw; } finally { conFb.Close(); } } catch (Exception ex) { u.messageboxErro(ex.ToString()); throw; } finally { con.Close(); } } } //dados da nota var filial = db.Filiais.Find(1); var cidad = db.Cidades.Find(filial.CidadeCodigo); var ultimaNf = dbFb.Faturas.Max(x => x.ID); var cfop = db.Cfops.Find(cfopNumero); var ultimaNota = dbFb.Faturas.Find(ultimaNf); var dest = db.Clientes.Find(Convert.ToInt32(txtcodCliente.Text)); var cidDest = db.Cidades.Find(dest.CidadeCodigo); // dados do emitente nf.Ambiente = 1; nf.Bairro = filial.Bairro; nf.CepEmitente = filial.Cep; //nf.CnaeEmitente = "4520-0/01"; nf.CnpjEmitente = filial.CpfCnpj; nf.CodigoIbge = cidad.Ibge; nf.CodPais = "1058"; nf.CRT = 1; nf.DataEmissao = DateTime.Now; nf.DataSaida = DateTime.Now; nf.DataSaida = DateTime.Now; nf.DescMunicipioEmitente = cidad.Descricao; nf.FantasiaEmitente = filial.Fantasia; nf.FilialID = filial.ID; nf.FinalidadeNF = 1; nf.FoneEmitente = filial.Telefone; //nf.HoraSaida = DateTime.Now; nf.IbgeEmitente = cidad.Ibge; nf.ID = ultimaNf + 1; nf.IDE_CMUNFG = Convert.ToInt32(cidad.Ibge); nf.IEEmitente = filial.IE; //nf.IestEmitente = filial. nf.InfComplEmitente = "LOTES 107 A B E 108"; nf.LogradouroEmitente = filial.Endereco; nf.Modelo = 55; nf.NaturezaOperacao = cfop.Descricao; nf.NomeEmitente = filial.Razao; nf.NomePaisEmitente = "Brasil"; nf.NumeroEmitente = filial.Numero; nf.NumeroNF = ultimaNota.NumeroNF; nf.Serie = ultimaNota.Serie; nf.SituacaoNf = 0; nf.TipoEmissao = 0; nf.TipoNF = 1; nf.TipoNf = 0; nf.UFEmitente = cidad.UF; nf.VersaoApp = ultimaNota.VersaoApp; //dados do destinatario nf.BairroDestinatario = dest.Bairro; nf.CepDestinatario = dest.Cep; nf.CodPaisDestinatario = "1058"; nf.EstadoDestinatario = cidDest.UF; nf.FoneDestinatario = dest.Telefone; nf.IbgeDestinario = cidDest.Ibge; nf.IEDestinatario = dest.IE; //nf.InfComplDestinatario = nf.LogradouroDestinatario = dest.Endereco; nf.NomeDestinatario = dest.Razao; nf.NomeMunDestinario = cidDest.Descricao; nf.NomePaisDestinatario = "Brasil"; nf.NumeroDestinatario = dest.Numero; nf.UFDestinatario = cidDest.UF; ultimaNf += 1; dbFb.Faturas.Add(nf); dbFb.SaveChanges(); //itens da nota i = 0; foreach (var row in this.dataGridView1.Rows) { i = i + 1; var dataGridViewRow = row as DataGridViewRow; var celula = dataGridViewRow.Cells["cod_produto"]; var valVenda = dataGridViewRow.Cells["itp_valor"]; var quantidade = dataGridViewRow.Cells["itp_qtde"]; var valor = celula.Value; if (valor != DBNull.Value) { var produto = db.Produtos.Find(valor); try { string getNitem = "select max(NITEM) from NFE_ITENS where ID = " + ultimaNf; FbConnection Ncon = new FbConnection(); Ncon.ConnectionString = Properties.Settings.Default.Ducaun; FbCommand Ncmd = new FbCommand(getNitem, Ncon); Ncmd.CommandType = CommandType.Text; Ncon.Open(); FbDataReader fbDr = Ncmd.ExecuteReader(); if (fbDr.Read()) { getNitem = fbDr[0].ToString(); getNitem = Convert.ToString(Convert.ToInt32(getNitem) + 1); } else { getNitem = "1"; } string comando = ""; FbConnection conI = new FbConnection(); conI.ConnectionString = Properties.Settings.Default.BancoFirebird; FbCommand cmdI = new FbCommand(comando, conI); comando = "insert into NFE_ITENS(ID, ID_NFE, ID_FILIAL, ID_PRODUTO, NITEM, CPROD , XPROD, NCM, CFOP, UCOM, QCOM, VUNCOM" + " VPROD, UTRIB, QTRIB, VUNTRIB, VDESC, VEICPROD_TPOP, VEICPROD_CONDVEIC, COMB_CPRODANP, COMB_CODIF, " + " COMB_CIDE_QBCPROD, COMB_CIDE_VALIQPROD, COMB_ICMS_VBCICMS, COMB_ICMS_VBCICMSST, COMB_ICMSCONS_UFCONS " + " ICMS_ORIGEM, ICMS_MODBC, ICMS_MODBCST) " + " VALUES (@ID, @ID_NFE, @ID_FILIAL, @ID_PRODUTO, @NITEM, @CPROD , @XPROD, @NCM, @CFOP, @UCOM, @QCOM, @VUNCOM" + " @VPROD, @UTRIB, @QTRIB, @VUNTRIB, @VDESC, @VEICPROD_TPOP, @VEICPROD_CONDVEIC, @COMB_CPRODANP, @COMB_CODIF, " + " @COMB_CIDE_QBCPROD, @COMB_CIDE_VALIQPROD, @COMB_ICMS_VBCICMS, @COMB_ICMS_VBCICMSST, @COMB_ICMSCONS_UFCONS" + " @ICMS_ORIGEM, @ICMS_MODBC, @ICMS_MODBCST)"; cmdI.Parameters.Add("@ID", FbDbType.Integer).Value = produto.ID; cmdI.Parameters.Add("@ID_NFE", FbDbType.Integer).Value = ultimaNf; cmdI.Parameters.Add("@ID_FILIAL", FbDbType.Integer).Value = 1; cmdI.Parameters.Add("@ID_PRODUTO", FbDbType.Integer).Value = valor; cmdI.Parameters.Add("@NITEM", FbDbType.Integer).Value = Convert.ToInt32(getNitem); cmdI.Parameters.Add("@CPROD", FbDbType.VarChar).Value = produto.Referencia; cmdI.Parameters.Add("@XPROD", FbDbType.VarChar).Value = produto.DescricaoReduzida; cmdI.Parameters.Add("@NCM", FbDbType.VarChar).Value = produto.NCM; cmdI.Parameters.Add("@CFOP", FbDbType.Integer).Value = produto.CFOP; cmdI.Parameters.Add("@UCOM", FbDbType.VarChar).Value = produto.UnidadeMedida; cmdI.Parameters.Add("@QCOM", FbDbType.Decimal).Value = quantidade; cmdI.Parameters.Add("@VUNCOM", FbDbType.Decimal).Value = valVenda; cmdI.Parameters.Add("@VPROD", FbDbType.Decimal).Value = (Convert.ToDecimal(quantidade) * Convert.ToDecimal(valVenda)); cmdI.Parameters.Add("@UTRIB", FbDbType.VarChar).Value = produto.UnidadeMedida; cmdI.Parameters.Add("@QTRIB", FbDbType.Decimal).Value = quantidade; cmdI.Parameters.Add("@VUNTRIB", FbDbType.Decimal).Value = 0; cmdI.Parameters.Add("@VDESC", FbDbType.Decimal).Value = 0; cmdI.Parameters.Add("@VEICPROD_TPOP", FbDbType.SmallInt).Value = 0; cmdI.Parameters.Add("@VEICPROD_CONDVEIC", FbDbType.SmallInt).Value = 0; cmdI.Parameters.Add("@COMB_CPRODANP", FbDbType.SmallInt).Value = 0; cmdI.Parameters.Add("@COMB_CODIF", FbDbType.SmallInt).Value = 0; cmdI.Parameters.Add("@COMB_CIDE_QBCPROD", FbDbType.Decimal).Value = (Convert.ToDecimal(quantidade) * Convert.ToDecimal(valVenda)); cmdI.Parameters.Add("@COMB_CIDE_VALIQPROD", FbDbType.Decimal).Value = 0; cmdI.Parameters.Add("@COMB_ICMS_VBCICMS", FbDbType.Decimal).Value = (Convert.ToDecimal(quantidade) * Convert.ToDecimal(valVenda)); cmdI.Parameters.Add("@COMB_ICMS_VBCICMSST", FbDbType.Decimal).Value = (Convert.ToDecimal(quantidade) * Convert.ToDecimal(valVenda)); var estado = dbFb.Estados.Find(estadoID); cmdI.Parameters.Add("@COMB_ICMSCONS_UFCONS", FbDbType.VarChar).Value = estado.Sigla; cmdI.Parameters.Add("@ICMS_ORIGEM", FbDbType.SmallInt).Value = 0; cmdI.Parameters.Add("@ICMS_MODBC", FbDbType.SmallInt).Value = 0; cmdI.Parameters.Add("@ICMS_MODBCST", FbDbType.SmallInt).Value = 4; cmdI.CommandType = CommandType.Text; conI.Open(); int ii = cmd.ExecuteNonQuery(); conI.Close(); } catch (Exception ex) { u.messageboxErro(ex.ToString()); throw; } } } u.messageboxSucesso(); }
public string[] detallesDelDia(string desde, string hasta, string usuario, string tipoVenta) { string[] datos = new string[4]; #region Ventas Realizadas try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT count(p.ID_PEDIDO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_TIPO = '0' and p.ID_USUARIO='" + usuario + "' and p.id_estado='1'"; break; case 2: sentencia = "SELECT count(p.ID_PEDIDO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_TIPO = '0' and p.ID_USUARIO='" + usuario + "' and p.id_estado='1'"; break; case 3: sentencia = "SELECT count(p.ID_PEDIDO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_TIPO = '0' and p.id_estado='1'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[0] = fb_datareader.GetString(0); if (datos[0] == "") { datos[0] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Pedidos Realizados try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT count(p.ID_PEDIDO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_TIPO = '1' and p.ID_USUARIO='" + usuario + "' and p.id_estado='1'"; break; case 2: sentencia = "SELECT count(p.ID_PEDIDO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_TIPO = '1' and p.ID_USUARIO='" + usuario + "' and p.id_estado='1'"; break; case 3: sentencia = "SELECT count(p.ID_PEDIDO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_TIPO = '1' and p.id_estado='1'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[1] = fb_datareader.GetString(0); if (datos[1] == "") { datos[1] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Cantidad Productos try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(dp.CANTIDAD) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA INNER JOIN DETALLE_PEDIDO dp on dp.ID_PEDIDO = p.ID_PEDIDO where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1'"; break; case 2: sentencia = "SELECT sum(dp.CANTIDAD) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA INNER JOIN DETALLE_PEDIDO dp on dp.ID_PEDIDO = p.ID_PEDIDO where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1'"; break; case 3: sentencia = "SELECT sum(dp.CANTIDAD) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA INNER JOIN DETALLE_PEDIDO dp on dp.ID_PEDIDO = p.ID_PEDIDO where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[2] = fb_datareader.GetString(0); if (datos[2] == "") { datos[2] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Total Vendido try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(p.PRECIO_TOTAL) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where(p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO = '" + usuario + "' and p.id_estado='1'"; break; case 2: sentencia = "SELECT sum(p.PRECIO_TOTAL) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where(p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO = '" + usuario + "' and p.id_estado='1'"; break; case 3: sentencia = "SELECT sum(p.PRECIO_TOTAL) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where(p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[3] = fb_datareader.GetString(0); if (datos[3] == "") { datos[3] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion return(datos); }
public static bool GeraRecibo(Document doc, PdfWriter writer, string nf, short seq, float y0) { PdfPTable table = new PdfPTable(1); table.WidthPercentage = 100; //table.HorizontalAlignment = Element.ALIGN_CENTER; PdfPCell cell; Chunk chunk; string logo = "imagens\\logo_rel.jpg"; Image img; float largura = 100F; float altura = 100F; try { img = Image.GetInstance(logo); float w = img.Width; float h = img.Height; while ((w > largura) || (h > altura)) { w *= 0.9F; h *= 0.9F; } img.ScaleAbsolute(w, h); chunk = new Chunk(img, 0, 0); } catch { chunk = new Chunk(""); } cell = new PdfPCell(new Phrase(chunk)); cell.BorderWidth = 0; cell.HorizontalAlignment = Element.ALIGN_CENTER; table.AddCell(cell); cell = new PdfPCell(new Phrase(new Chunk("RECIBO NRO " + nf, FontFactory.GetFont(BaseFont.HELVETICA, 16)))); cell.BorderWidth = 0; cell.HorizontalAlignment = Element.ALIGN_CENTER; table.AddCell(cell); salta(table, 2); string cliente = ""; string cpf_cnpj = ""; double valor = 0; DateTime data = DateTime.Now; DateTime vencimento = DateTime.Now; string natureza = ""; string forma = ""; string sql = "select " + " a.COD_CLIENTE, " + " b.IDT_FISJUR, " + " b.NRO_CPF_CNPJ," + " a.VLR_RECEBIDO," + " a.DAT_VENCIMENTO," + " c.DES_NATUREZA," + " d.DES_FORMA," + " a.DAT_RECEBIMENTO " + "from TITULOS_RECEBER a " + "inner join PARCEIROS b on b.COD_PARCEIRO=a.COD_CLIENTE " + "inner join NATUREZAS_RECEBIMENTO c on c.COD_NATUREZA=a.COD_NATUREZA " + "inner join FORMAS_RECEBIMENTO d on d.COD_FORMA=a.COD_FORMA " + "where a.NRO_NF='" + nf + "' and SEQ_TITULO=" + seq.ToString(); FbCommand cmd = new FbCommand(sql, Globais.bd); FbDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read()) { cliente = reader.GetString(0).Trim(); string fj = reader.GetString(1).Trim(); if (fj.Equals("F")) { cpf_cnpj = CPF.PoeEdicao(reader.GetString(2).Trim()); } else { cpf_cnpj = CNPJ.PoeEdicao(reader.GetString(2).Trim()); } valor = reader.GetDouble(3); vencimento = reader.GetDateTime(4); natureza = reader.GetString(5).Trim(); forma = reader.GetString(6).Trim(); data = reader.GetDateTime(7); } NumeroPorExtenso extenso = new NumeroPorExtenso((decimal)valor); string texto = " Recebi de " + cliente + ", " + cpf_cnpj + ", a quantia de " + valor.ToString("#,###,##0.00") + "(" + extenso.ToString() + ") referente ao vencimento " + vencimento.ToString("d/M/yyyy"); string sql2 = "select " + " a.COD_FORNECEDOR, " + " a.DAT_ORCAMENTO," + " a.COD_ORCAMENTO," + " a.COD_PEDIDO " + "from PEDIDOS_RECEBIDOS a " + //"inner join PEDIDOS b on b.COD_FORNECEDOR=a.COD_FORNECEDOR and b.DAT_ORCAMENTO=a.DAT_ORCAMENTO and b.COD_ORCAMENTO=a.COD_ORCAMENTO and b.COD_PEDIDO=a.COD_PEDIDO " + "where a.NRO_NF='" + nf + "' and SEQ_TITULO=" + seq.ToString(); FbCommand cmd2 = new FbCommand(sql2, Globais.bd); FbDataReader reader2 = cmd2.ExecuteReader(CommandBehavior.Default); string pedidos = ""; while (reader2.Read()) { string fornecedor = reader2.GetString(0).Trim(); DateTime dataorc = reader2.GetDateTime(1); short orcamento = reader2.GetInt16(2); short pedido = reader2.GetInt16(3); if (!pedidos.Equals("")) { pedidos += ", "; } pedidos += fornecedor + " " + dataorc.Month.ToString() + "/" + dataorc.Year.ToString() + " " + orcamento + "-" + pedido; } reader2.Close(); if (!pedidos.Equals("")) { texto += ", dos pedidos (" + pedidos + ")"; } texto += ", através de " + forma + "."; cell = new PdfPCell(new Phrase(new Chunk(texto, FontFactory.GetFont(BaseFont.HELVETICA, 12)))); cell.BorderWidth = 0; cell.HorizontalAlignment = Element.ALIGN_LEFT; table.AddCell(cell); salta(table, 5); string[] meses = { "janeiro", "fevereiro", "março", "abril", "maio", "junho", "julho", "agosto", "setembro", "outubro", "novembro", "dezembro" }; cell = new PdfPCell(new Phrase(new Chunk(data.Day + " de " + meses[data.Month - 1] + " de " + data.Year, FontFactory.GetFont(BaseFont.HELVETICA, 12)))); cell.BorderWidth = 0; cell.HorizontalAlignment = Element.ALIGN_CENTER; table.AddCell(cell); salta(table, 5); cell = new PdfPCell(new Phrase(new Chunk(data.ToString(" _________________________________________________________"), FontFactory.GetFont(BaseFont.HELVETICA, 12)))); cell.BorderWidth = 0; table.AddCell(cell); salta(table, 3); cell = new PdfPCell(new Phrase(new Chunk(" Nome:", FontFactory.GetFont(BaseFont.HELVETICA, 12)))); cell.BorderWidth = 0; table.AddCell(cell); salta(table, 3); cell = new PdfPCell(new Phrase(new Chunk(" Cargo:", FontFactory.GetFont(BaseFont.HELVETICA, 12)))); cell.BorderWidth = 0; table.AddCell(cell); //doc.Add(table); table.TotalWidth = PageSize.LETTER.Width - doc.LeftMargin - doc.RightMargin; table.WriteSelectedRows(0, -1, doc.LeftMargin, y0, writer.DirectContent); return(true); }
public string[] detallesReporte(string desde, string hasta, string usuario) { string[] datos = new string[5]; #region Total Ventas Efectivo try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='0'"; break; case 2: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='0'"; break; case 3: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1' and pd.id_tipopago='0'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[0] = fb_datareader.GetString(0); if (datos[0] == "") { datos[0] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Total Ventas Credito try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='1'"; break; case 2: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='1'"; break; case 3: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1' and pd.id_tipopago='1'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[1] = fb_datareader.GetString(0); if (datos[1] == "") { datos[1] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Total Ventas Debito try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='2'"; break; case 2: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='2'"; break; case 3: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1' and pd.id_tipopago='2'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[2] = fb_datareader.GetString(0); if (datos[2] == "") { datos[2] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Total Transferancias CBU try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='3'"; break; case 2: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='3'"; break; case 3: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1' and pd.id_tipopago='3'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[3] = fb_datareader.GetString(0); if (datos[3] == "") { datos[3] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion #region Total Transferencias MercadoPago try { conexion.ConnectionString = Convert.ToString(Conexion_BD.Recuperar_cadena()); conexion.Open(); switch (int.Parse(usuario)) { case 1: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='4'"; break; case 2: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.ID_USUARIO='" + usuario + "' and p.id_estado='1' and pd.id_tipopago='4'"; break; case 3: sentencia = "SELECT sum(pd.MONTO) FROM PEDIDO p INNER JOIN PAGO_DETALLE pd ON pd.ID_PEDIDO = p.ID_PEDIDO INNER JOIN PAGO_TIPO pt ON pt.ID_TIPOPAGO = pd.ID_TIPOPAGO INNER JOIN USUARIO u ON u.ID_USUARIO = p.ID_USUARIO INNER JOIN PERSONA pr ON pr.ID_PERSONA = u.ID_PERSONA where (p.fecha_pedido >= '" + desde + "' and p.fecha_pedido <= '" + hasta + "') and p.id_estado='1' and pd.id_tipopago='4'"; break; } FbCommand cmd = new FbCommand(sentencia, conexion); FbDataReader fb_datareader = cmd.ExecuteReader(); if (fb_datareader.Read()) { datos[4] = fb_datareader.GetString(0); if (datos[4] == "") { datos[4] = "0"; } } cmd = null; conexion.Close(); } catch (Exception) { conexion.Close(); throw; } #endregion return(datos); }
private void YeniSatisBtn_Click(object sender, EventArgs e) { try { string satis_urun = "", satis_musteri = "", satis_kategori = ""; byte sonuc, sonuc1; float bakiye = 0, tutar = 0; FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); baglan.Open(); FbCommand UrunBilgi = new FbCommand("SELECT Urun_id FROM Urunler WHERE Urun_adi='" + SatisUrun.Text + "'", baglan); object s1 = UrunBilgi.ExecuteScalar(); if (s1 != null) { FbDataReader UrunOku = UrunBilgi.ExecuteReader(); UrunOku.Read(); satis_urun = UrunOku["Urun_id"].ToString(); UrunOku.Close(); } else { MessageBox.Show("Sistemsel bir sorun oluştu, lütfen sağlayıcınıza başvurunuz.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } baglan.Close(); baglan.Open(); FbCommand KategoriBilgi = new FbCommand("SELECT * FROM Urun_kategori WHERE Kategori_adi='" + SatisUrunKategorisi.Text + "'", baglan); object s2 = KategoriBilgi.ExecuteScalar(); if (s2 != null) { FbDataReader KategoriOku = KategoriBilgi.ExecuteReader(); KategoriOku.Read(); satis_kategori = KategoriOku["Kategori_id"].ToString(); KategoriOku.Close(); } else { MessageBox.Show("Sistemsel bir sorun oluştu, lütfen sağlayıcınıza başvurunuz.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } baglan.Close(); int satisadet = int.Parse(SatisAdet.Value.ToString()); int satisSay = 0; for (int i = 1; i <= satisadet; i++) { if (KayitliMi1.Checked) { string[] ads = SatisMusteri.Text.Split(' '); baglan.Open(); FbCommand MusBilgi = new FbCommand("SELECT Musteri_id,Musteri_bakiye FROM Musteriler WHERE Musteri_adi='" + ads[0] + "' and Musteri_soyadi='" + ads[1] + "'", baglan); object s3 = MusBilgi.ExecuteScalar(); if (s3 != null) { FbDataReader MusOku = MusBilgi.ExecuteReader(); MusOku.Read(); satis_musteri = MusOku["Musteri_id"].ToString(); bakiye = float.Parse(MusOku["Musteri_bakiye"].ToString()); MusOku.Close(); } else { MessageBox.Show("Sistemsel bir sorun oluştu, lütfen sağlayıcınıza başvurunuz.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } baglan.Close(); } string tarih = DateTime.Now.Year.ToString(); tarih += '-' + DateTime.Now.Month.ToString(); tarih += '-' + DateTime.Now.Day.ToString(); if (satis_musteri == "") { sonuc = fk.Ekle("Satis", "Satis_urun,Satis_tarih,Satis_not", "'" + satis_urun + "','" + tarih + "','" + SatisNot.Text + "'"); baglan.Open(); FbCommand UrunVarMi = new FbCommand("SELECT rapor_satisId,rapor_tarih FROM Rapor WHERE rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'", baglan); object varmi = UrunVarMi.ExecuteScalar(); if (varmi != null) { sonuc1 = fk.Guncelle("Rapor", "rapor_sayac=rapor_sayac+1", "rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'"); } else { sonuc1 = fk.Ekle("Rapor", "rapor_satisId,rapor_satisKt,rapor_sayac,rapor_tarih", "'" + satis_urun + "','" + satis_kategori + "','1','" + tarih + "'"); } baglan.Close(); if (sonuc == 1 && sonuc1 == 1) { satisSay++; } else { satisSay = satisSay + 0; } } else { sonuc = fk.Ekle("Satis", "Satis_urun,Satis_musteri,Satis_tarih,Satis_not", "'" + satis_urun + "','" + satis_musteri + "','" + tarih + "','" + SatisNot.Text + "'"); baglan.Open(); FbCommand UrunVarMi = new FbCommand("SELECT rapor_satisId,rapor_tarih FROM Rapor WHERE rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'", baglan); object varmi = UrunVarMi.ExecuteScalar(); if (varmi != null) { sonuc1 = fk.Guncelle("Rapor", "rapor_sayac=rapor_sayac+1", "rapor_satisId='" + satis_urun + "' and rapor_tarih='" + tarih + "'"); } else { sonuc1 = fk.Ekle("Rapor", "rapor_satisId,rapor_satisKt,rapor_sayac,rapor_tarih", "'" + satis_urun + "','" + satis_kategori + "','1','" + tarih + "'"); } baglan.Close(); if (sonuc == 1 && sonuc1 == 1) { satisSay++; if (checkBox1.Checked) { tutar = float.Parse(SatisTutar.Text); bakiye = bakiye - tutar; ybakiye.Text = bakiye.ToString(); ybakiye.Text = ybakiye.Text.Replace(',', '.'); fk.Guncelle("Musteriler", "Musteri_bakiye='" + ybakiye.Text + "'", "Musteri_id='" + satis_musteri + "'"); } } else { satisSay = satisSay + 0; } } } if (satisSay > 0) { MessageBox.Show("Satış başarıyla eklenmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information); SatisAdet.Value = 1; SatisNot.Text = ""; } else { MessageBox.Show("Satış eklenemedi ( Hata kodu: S-01 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception e4) { MessageBox.Show(e4.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
//Поиск абонента по ОКПО protected void lbFindOKPO_Click(object sender, EventArgs e) { /* * Поиск по базе водосбыта */ if (tbCorporateOKPO.Text.Length >= 4) { try { FbConnection fbCn = new FbConnection("Database=water_wdk;Server=db3;User=viewer_;Password=VIEWER_;Role=viewer;"); FbDataAdapter fbDa = new FbDataAdapter(); FbCommand fbCmd = new FbCommand(); //DataTable dt = new DataTable(); fbCn.Open(); fbCmd = new FbCommand(string.Format(@"select delzero(d.id_dog)||' / '||r.name_ray||' район' dogovor,d.id_doc, d.okpo,d.bank,d.r_s,d.mfo, h.full_name,h.ur_address, case k.gr when 3 then 1 when 4 then 1 when 5 then 1 when 6 then 1 else 0 end budzhet,h.inn, h.id_svid, d.info_1 kontact, d.v_litse,d.na_osnovanii,d.otv_litso,d.contact_phone,d.ot_imeni,h.sys_no,s.name_no from dogovor d left join rayon r on r.id_ray=d.id_ray left join history h on h.id_doc=d.id_doc left join history n on n.id_doc=h.id_doc and (n.datebeg>h.datebeg or n.datebeg=h.datebeg and n.kod>h.kod) left join kods k on k.podgr=h.podgr left join spr_sys_no s on s.id_no=h.sys_no where d.okpo like '%'||{0}||'%' and d.date_close_d is null and n.kod is null", tbCorporateOKPO.Text), fbCn); FbDataReader dr = fbCmd.ExecuteReader(); System.Data.DataTable dt = new System.Data.DataTable(); dt.Load(dr); if (dt.Rows.Count > 0) { repCorporate.DataSource = dt; repCorporate.DataBind(); litSearch.Text = ""; } else { repCorporate.DataBind(); litSearch.Text = "Вас не найдено в базе Донецкгорводоканала, пожалуйста, пройдите в водосбыт для регистрации"; } dr.Close(); fbCn.Close(); } catch (Exception ex) { radWM.RadAlert("Сбой подключения к базе данных <br>" + ex.Message, null, null, "Ошибка", ""); } } else { radWM.RadAlert("Для поиска необходимо ввести минимум 4 символа.", null, null, "Предупреждение", ""); } radgridD.Rebind(); }
private void BarkodNo_TextChanged(object sender, EventArgs e) { try { if (BarkodNo.Text.Length > 14 && BarkodNo.Text != "000000000000000") { // barkoda göre arattır if (BarkodNo.Text != "") { FbDataReader oku; object barkod; FbConnection baglanti = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglanti2 = new FbConnection(fk.Baglanti_Kodu()); FbCommand UrunBarkodSorgu = new FbCommand("SELECT * FROM Urunler where Urun_barkodNo='" + BarkodNo.Text + "'", baglanti); baglanti.Open(); barkod = UrunBarkodSorgu.ExecuteScalar(); if (barkod != null) { groupBox3.Enabled = true; oku = UrunBarkodSorgu.ExecuteReader(); oku.Read(); UrunAdiBarkod.Text = oku["Urun_adi"].ToString(); ssFiyat = float.Parse(oku["Urun_fiyat"].ToString()); ssFiyat = (ssFiyat * float.Parse((SatisAdetBarkod.Value).ToString())); SatisTutarBarkod.Text = (ssFiyat * float.Parse((SatisAdetBarkod.Value).ToString())).ToString(); satis_urun = oku["Urun_id"].ToString(); satis_kategori = oku["Urun_kategori"].ToString(); SatisAdetBarkod.Value = 1; oku.Close(); } else { groupBox3.Enabled = false; MessageBox.Show("Ürün bulunamadı. Tam barkod numarasını girin veya okutun", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Warning); UrunAdiBarkod.Text = ""; SatisAdetBarkod.Value = 1; SatisTutarBarkod.Text = "00.00"; SatisNotB.Text = ""; } baglanti.Close(); } else { MessageBox.Show("Barkod numarasını girin. ( Hata kodu: S-02 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { if (BarkodNo.Text == "") { groupBox3.Enabled = false; UrunAdiBarkod.Text = ""; SatisAdetBarkod.Value = 1; SatisTutarBarkod.Text = "00.00"; SatisNotB.Text = ""; } } } catch (Exception ee) { MessageBox.Show(ee.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public FbDataReader Select(string sql) { var cmd = new FbCommand(sql, conn, GetTransaction()); return(cmd.ExecuteReader()); // DataReader }
//Regresa Producto Especifico public Producto ObtenerProducto(string AClave) { Producto pResult = null; string pSentencia = "SELECT * FROM DRASPROD WHERE CLAVE = @CLAVE"; FbConnection con = _Conexiones.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@CLAVE", FbDbType.VarChar).Value = AClave + ""; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { pResult = new Producto(); pResult.Clave = reader["CLAVE"] != DBNull.Value ? (string)reader["CLAVE"] : ""; pResult.Descripcion = reader["DESCRIPCION"] != DBNull.Value ? (string)reader["DESCRIPCION"] : ""; pResult.Almacen = reader["ALMACEN"] != DBNull.Value ? (int)reader["ALMACEN"] : 0; pResult.Clave_Alm = reader["CLAVE_ALM"] != DBNull.Value ? (string)reader["CLAVE_ALM"] : ""; pResult.Mercado = reader["MERCADO"] != DBNull.Value ? (string)reader["MERCADO"] : ""; pResult.Codbarras = reader["CODBARRAS"] != DBNull.Value ? (string)reader["CODBARRAS"] : ""; pResult.Pesofijo = reader["PESOFIJO"] != DBNull.Value ? (float)reader["PESOFIJO"] : 0; pResult.Clase = reader["CLASE"] != DBNull.Value ? (string)reader["CLASE"] : ""; pResult.Pesotara = reader["PESOTARA"] != DBNull.Value ? (float)reader["PESOTARA"] : 0; pResult.Diascad = reader["DIASCAD"] != DBNull.Value ? (float)reader["DIASCAD"] : 0; pResult.Linea = reader["LINEA"] != DBNull.Value ? (string)reader["LINEA"] : ""; pResult.Pesomaximo = reader["PESOMAXIMO"] != DBNull.Value ? (float)reader["PESOMAXIMO"] : 0; pResult.Pesominimo = reader["PESOMINIMO"] != DBNull.Value ? (float)reader["PESOMINIMO"] : 0; pResult.Rendimiento = reader["RENDIMIENTO"] != DBNull.Value ? (float)reader["RENDIMIENTO"] : 0; pResult.Grupo = reader["GRUPO"] != DBNull.Value ? (Int16)reader["GRUPO"] : 0; pResult.Operador = reader["OPERADOR"] != DBNull.Value ? (Int16)reader["OPERADOR"] : 0; pResult.Fondo = reader["FONDO"] != DBNull.Value ? (int)reader["FONDO"] : 0; pResult.Tapa = reader["TAPA"] != DBNull.Value ? (int)reader["TAPA"] : 0; pResult.Pliego = reader["PLIEGO"] != DBNull.Value ? (int)reader["PLIEGO"] : 0; pResult.Panal = reader["PANAL"] != DBNull.Value ? (int)reader["PANAL"] : 0; pResult.Separador = reader["SEPARADOR"] != DBNull.Value ? (int)reader["SEPARADOR"] : 0; pResult.Bolsa = reader["BOLSA"] != DBNull.Value ? (int)reader["BOLSA"] : 0; pResult.Huleespuma = reader["HULEESPUMA"] != DBNull.Value ? (int)reader["HULEESPUMA"] : 0; pResult.Palillos = reader["PALILLOS"] != DBNull.Value ? (int)reader["PALILLOS"] : 0; pResult.Huleburbuja = reader["HULEBURBUJA"] != DBNull.Value ? (int)reader["HULEBURBUJA"] : 0; pResult.Otros = reader["OTROS"] != DBNull.Value ? (int)reader["OTROS"] : 0; pResult.OtrosDos = reader["OTROSDOS"] != DBNull.Value ? (int)reader["OTROSDOS"] : 0; pResult.Fondo_D = reader["FONDO_D"] != DBNull.Value ? (string)reader["FONDO_D"] : ""; pResult.Tapa_D = reader["TAPA_D"] != DBNull.Value ? (string)reader["TAPA_D"] : ""; pResult.Pliego_D = reader["PLIEGO_D"] != DBNull.Value ? (string)reader["PLIEGO_D"] : ""; pResult.Panal_D = reader["PANAL_D"] != DBNull.Value ? (string)reader["PANAL_D"] : ""; pResult.Separador_D = reader["SEPARADOR_D"] != DBNull.Value ? (string)reader["SEPARADOR_D"] : ""; pResult.Bolsa_D = reader["BOLSA_D"] != DBNull.Value ? (string)reader["BOLSA_D"] : ""; pResult.Huleespuma_D = reader["HULEESPUMA_D"] != DBNull.Value ? (string)reader["HULEESPUMA_D"] : ""; pResult.Palillos_D = reader["PALILLOS_D"] != DBNull.Value ? (string)reader["PALILLOS_D"] : ""; pResult.Huleburbuja_D = reader["HULEBURBUJA_D"] != DBNull.Value ? (string)reader["HULEBURBUJA_D"] : ""; pResult.Otros_D = reader["OTROS_D"] != DBNull.Value ? (string)reader["OTROS_D"] : ""; pResult.OtrosDos_D = reader["OTROSDOS_D"] != DBNull.Value ? (string)reader["OTROSDOS_D"] : ""; pResult.Temperatura = reader["TEMPERATURA"] != DBNull.Value ? (string)reader["TEMPERATURA"] : ""; pResult.Copias = reader["COPIAS"] != DBNull.Value ? (Int16)reader["COPIAS"] : 0; pResult.AplicaTemperatura = reader["APLICATEMPERATURA"] != DBNull.Value ? (string)reader["APLICATEMPERATURA"] : ""; pResult.CodigoProveedor = reader["CODIGOPROVEDOR"] != DBNull.Value ? (string)reader["CODIGOPROVEDOR"] : ""; pResult.Camara_Default = reader["CAMARA_DEFAULT"] != DBNull.Value ? (int)reader["CAMARA_DEFAULT"] : 0; pResult.Posicion_Default = reader["POSICION_DEFAULT"] != DBNull.Value ? (string)reader["POSICION_DEFAULT"] : ""; pResult.Usa_Camara_Default = reader["USA_CAMARA_DEFAULT"] != DBNull.Value ? (string)reader["USA_CAMARA_DEFAULT"] : ""; pResult.Etiqueta = reader["ETIQUETA"] != DBNull.Value ? (decimal)reader["ETIQUETA"] : 0; pResult.Etiqueta_D = reader["ETIQUETA_D"] != DBNull.Value ? (string)reader["ETIQUETA_D"] : ""; pResult.Inventariable = reader["INVENTARIABLE"] != DBNull.Value ? (string)reader["INVENTARIABLE"] : ""; pResult.Formato_Etiqueta = reader["FORMATO_ETIQUETA"] != DBNull.Value ? (int)reader["FORMATO_ETIQUETA"] : 0; pResult.Calcula_Dias_Cad = reader["CALCULA_DIAS_CAD"] != DBNull.Value ? (string)reader["CALCULA_DIAS_CAD"] : ""; pResult.Decimales = reader["DECIMALES"] != DBNull.Value ? (Int16)reader["DECIMALES"] : 0; pResult.CodigoSap = reader["CODIGOSAP"] != DBNull.Value ? (string)reader["CODIGOSAP"] : ""; pResult.Tipo_Redondeo = reader["TIPO_REDONDEO"] != DBNull.Value ? (int)reader["TIPO_REDONDEO"] : 0; pResult.Dias_Sacrificio = reader["DIAS_SACRIFICIO"] != DBNull.Value ? (int)reader["DIAS_SACRIFICIO"] : 0; pResult.Moldeado = reader["MOLDEADO"] != DBNull.Value ? (string)reader["MOLDEADO"] : ""; pResult.Fecha_Sacrificio = reader["FECHA_SACRIFICIO"] != DBNull.Value ? Utilerias.dateTimeToString((DateTime)reader["FECHA_SACRIFICIO"]) : ""; pResult.Tipo_Fecha_Sacrificio = reader["TIPO_FECHA_SACRIFICIO"] != DBNull.Value ? (Int16)reader["TIPO_FECHA_SACRIFICIO"] : 0; pResult.Fecha_Produccion = reader["FECHA_PRODUCCION"] != DBNull.Value ? (DateTime?)reader["FECHA_PRODUCCION"] : null; pResult.Tipo_Fecha_Produccion = reader["TIPO_FECHA_PRODUCCION"] != DBNull.Value ? (Int16)reader["TIPO_FECHA_PRODUCCION"] : 0; pResult.Tipo_Fecha_Caducidad = reader["TIPO_FECHA_CADUCIDAD"] != DBNull.Value ? (Int16)reader["TIPO_FECHA_CADUCIDAD"] : 0; pResult.Fecha_Caducidad = reader["FECHA_CADUCIDAD"] != DBNull.Value ? (DateTime?)reader["FECHA_CADUCIDAD"] : null; pResult.Kilos_Por_Caja = reader["KILOS_POR_CAJA"] != DBNull.Value ? (decimal)reader["KILOS_POR_CAJA"] : 0; pResult.Captura_Pedimento = reader["CAPTURA_PEDIMENTO"] != DBNull.Value ? (string)reader["CAPTURA_PEDIMENTO"] : ""; pResult.Categoria = reader["CATEGORIA"] != DBNull.Value ? (int)reader["CATEGORIA"] : 0; pResult.FechaHoraSistema = reader["FECHAHORASISTEMA"] != DBNull.Value ? (DateTime?)reader["FECHAHORASISTEMA"] : null; pResult.Usuario_Cambio = reader["USUARIO_CAMBIO"] != DBNull.Value ? (string)reader["USUARIO_CAMBIO"] : ""; pResult.Fec_Cad_Manual = reader["FEC_CAD_MANUAL"] != DBNull.Value ? (string)reader["FEC_CAD_MANUAL"] : ""; pResult.Decimales_Etiqueta = reader["DECIMALES_ETIQUETA"] != DBNull.Value ? (int)reader["DECIMALES_ETIQUETA"] : 0; pResult.Lectura_E1 = reader["LECTURA_E1"] != DBNull.Value ? (string)reader["LECTURA_E1"] : ""; pResult.Lectura_E2 = reader["LECTURA_E2"] != DBNull.Value ? (string)reader["LECTURA_E2"] : ""; pResult.Lectura_E3 = reader["LECTURA_E3"] != DBNull.Value ? (string)reader["LECTURA_E3"] : ""; pResult.Lectura_Sagarpa = reader["LECTURA_SAGARPA"] != DBNull.Value ? (string)reader["LECTURA_SAGARPA"] : ""; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return(pResult); }
internal Livro Carregar(int codigoTitulo) { using (FbConnection conexaoFireBird = Conexao.GetInstancia().GetConexao()) { var listaLivroRetorno = new Livro(); try { conexaoFireBird.Open(); string mSQL = @"SELECT TITULO.codigotitulo, TITULO.nometitulo AS TITULO, AUTOR.nome AS AUTOR, IDIOMA.lingua, IDIOMA.pais, EDICAO.edicao, EDICAO.ano, EDICAO.qtde_paginas, EDICAO.data_lancamento, EDITORA.nome AS EDITORA, EDITORA.codigoeditora, IDIOMA.codigoidioma, TITULO.descricao, AUTOR.codigoautor, EDICAO.codigoidioma, EDICAO.codigoedicao FROM TITULO_AUTOR INNER JOIN AUTOR ON TITULO_AUTOR.codigoautor = AUTOR.codigoautor RIGHT JOIN TITULO ON TITULO_AUTOR.codigotitulo = TITULO.codigotitulo RIGHT JOIN EDICAO ON TITULO_AUTOR.codigoedicao = EDICAO.codigoedicao INNER JOIN IDIOMA ON EDICAO.codigoidioma = IDIOMA.codigoidioma INNER JOIN EDITORA ON TITULO.codigoeditora = EDITORA.codigoeditora WHERE TITULO.desabilitar = 'N' AND TITULO.codigotitulo = @codigoTitulo"; FbCommand cmd = new FbCommand(mSQL, conexaoFireBird); cmd.Parameters.Add("@CODIGOTITULO", codigoTitulo); var dr = cmd.ExecuteReader(); while (dr.Read()) { listaLivroRetorno = new Livro { Titulo = { CodigoTitulo = int.Parse(dr["CODIGOTITULO"].ToString()), NomeTitulo = dr["TITULO"].ToString(), Descricao = dr["DESCRICAO"].ToString() }, Autor = { Nome = dr["AUTOR"].ToString(), CodigoAutor = int.Parse(dr["CODIGOAUTOR"].ToString()), }, Idioma = { CodigoIdioma = int.Parse(dr["CODIGOIDIOMA"].ToString()), Lingua = dr["LINGUA"].ToString(), Pais = dr["PAIS"].ToString() }, Edicao = { CodigoEdicao = int.Parse(dr["codigoedicao"].ToString()), CodigoIdioma = int.Parse(dr["CODIGOIDIOMA"].ToString()), NumeroEdicao = dr["EDICAO"].ToString(), Ano = int.Parse(dr["ANO"].ToString()), QtdePagina = int.Parse(dr["QTDE_PAGINAS"].ToString()), DataLancamento = DateTime.Parse(dr["DATA_LANCAMENTO"].ToString()), }, Editora = { Nome = dr["EDITORA"].ToString(), CodigoEditora = int.Parse(dr["CODIGOEDITORA"].ToString()) } }; } } catch (Exception e) { XtraMessageBox.Show(e.Message); } finally { conexaoFireBird.Close(); } return(listaLivroRetorno); } }
public CONTRATOEntity Read(int IDCONTRATO) { 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_CONTRATO", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Rea_CONTRATO", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.AddWithValue("@IDCONTRATO", IDCONTRATO); //PrimaryKey reader = dbCommand.ExecuteReader(); CONTRATOEntity 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; } }
private void AraButton_Click(object sender, EventArgs e) { islemler.Items.Clear(); FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu()); if (AramaText.Text != "") { if (TkpNo.Checked) { baglan.Open(); FbCommand TkpNoSql = new FbCommand("SELECT * FROM Teknik_Takip WHERE Takip_no='" + AramaText.Text + "'", baglan); object sonuc; FbDataReader TkpNoOku; sonuc = TkpNoSql.ExecuteScalar(); if (sonuc != null) { TkpNoOku = TkpNoSql.ExecuteReader(); TkpNoOku.Read(); grup3.Enabled = true; grup2.Enabled = false; id = TkpNoOku["Takip_id"].ToString(); if (int.Parse(TkpNoOku["Takip_durum"].ToString()) == 1) { grup5.Enabled = true; } else { grup5.Enabled = false; } cihazbilgi.Text = TkpNoOku["Takip_marka"].ToString() + " " + TkpNoOku["Takip_model"].ToString(); this.Text = cihazbilgi.Text; baglan2.Open(); FbCommand TakipGunSqlSay = new FbCommand("SELECT COUNT(*) FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); object say = TakipGunSqlSay.ExecuteScalar(); baglan2.Close(); baglan2.Open(); FbCommand TakipGunSql = new FbCommand("SELECT * FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); if (int.Parse(say.ToString()) > 0) { FbDataReader TakipGunOku = TakipGunSql.ExecuteReader(); while (TakipGunOku.Read()) { islemler.Items.Add(TakipGunOku["Takip_bilgi"].ToString()); } } else { islemler.Items.Add("Bu cihazda şuanda işlem yapılmamıştır"); } baglan2.Close(); if (TkpNoOku["Takip_musteri"].ToString() != "") { baglan2.Open(); FbCommand MusSql = new FbCommand("SELECT Musteri_adi,Musteri_soyadi,Musteri_telefon FROM Musteriler WHERE Musteri_id='" + TkpNoOku["Takip_musteri"].ToString() + "'", baglan2); FbDataReader MusOku = MusSql.ExecuteReader(); MusOku.Read(); musadsoyad.Text = MusOku["Musteri_adi"].ToString() + " " + MusOku["Musteri_soyadi"].ToString(); mustel.Text = MusOku["Musteri_telefon"].ToString(); baglan2.Close(); } else { musadsoyad.Text = TkpNoOku["Takip_kisibilgi"].ToString(); mustel.Text = TkpNoOku["Takip_kisiTel"].ToString(); } } else { MessageBox.Show("Takip bulunamadı ( Hata kodu: T-04 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); grup3.Enabled = false; grup2.Enabled = false; cihazlar.Items.Clear(); islemler.Items.Clear(); cihazbilgi.Text = ""; musadsoyad.Text = ""; mustel.Text = ""; guncelleme.Text = ""; tkpKapat.Checked = false; this.Text = "Takip Güncelleme"; } baglan.Close(); } else if (İmeiNo.Checked) { baglan.Open(); FbCommand TkpNoSql = new FbCommand("SELECT * FROM Teknik_Takip WHERE Takip_imei='" + AramaText.Text + "'", baglan); object sonuc; FbDataReader TkpNoOku; sonuc = TkpNoSql.ExecuteScalar(); if (sonuc != null) { TkpNoOku = TkpNoSql.ExecuteReader(); TkpNoOku.Read(); grup3.Enabled = true; grup2.Enabled = false; id = TkpNoOku["Takip_id"].ToString(); if (int.Parse(TkpNoOku["Takip_durum"].ToString()) == 1) { grup5.Enabled = true; } else { grup5.Enabled = false; } cihazbilgi.Text = TkpNoOku["Takip_marka"].ToString() + " " + TkpNoOku["Takip_model"].ToString(); this.Text = cihazbilgi.Text; baglan2.Open(); FbCommand TakipGunSqlSay = new FbCommand("SELECT COUNT(*) FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); object say = TakipGunSqlSay.ExecuteScalar(); baglan2.Close(); baglan2.Open(); FbCommand TakipGunSql = new FbCommand("SELECT * FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); if (int.Parse(say.ToString()) > 0) { FbDataReader TakipGunOku = TakipGunSql.ExecuteReader(); while (TakipGunOku.Read()) { islemler.Items.Add(TakipGunOku["Takip_bilgi"].ToString()); } } else { islemler.Items.Add("Bu cihazda şuanda işlem yapılmamıştır"); } baglan2.Close(); if (TkpNoOku["Takip_musteri"].ToString() != "") { baglan2.Open(); FbCommand MusSql = new FbCommand("SELECT Musteri_adi,Musteri_soyadi,Musteri_telefon FROM Musteriler WHERE Musteri_id='" + TkpNoOku["Takip_musteri"].ToString() + "'", baglan2); FbDataReader MusOku = MusSql.ExecuteReader(); MusOku.Read(); musadsoyad.Text = MusOku["Musteri_adi"].ToString() + " " + MusOku["Musteri_soyadi"].ToString(); mustel.Text = MusOku["Musteri_telefon"].ToString(); baglan2.Close(); } else { musadsoyad.Text = TkpNoOku["Takip_kisibilgi"].ToString(); mustel.Text = TkpNoOku["Takip_kisiTel"].ToString(); } baglan.Close(); } else { MessageBox.Show("Takip bulunamadı ( Hata kodu: T-04 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); grup3.Enabled = false; grup2.Enabled = false; cihazlar.Items.Clear(); islemler.Items.Clear(); cihazbilgi.Text = ""; musadsoyad.Text = ""; mustel.Text = ""; guncelleme.Text = ""; tkpKapat.Checked = false; this.Text = "Takip Güncelleme"; } baglan.Close(); } else if (SeriNo.Checked) { baglan.Open(); FbCommand TkpNoSql = new FbCommand("SELECT * FROM Teknik_Takip WHERE Takip_serino='" + AramaText.Text + "'", baglan); object sonuc; FbDataReader TkpNoOku; sonuc = TkpNoSql.ExecuteScalar(); if (sonuc != null) { TkpNoOku = TkpNoSql.ExecuteReader(); TkpNoOku.Read(); grup2.Enabled = false; id = TkpNoOku["Takip_id"].ToString(); if (int.Parse(TkpNoOku["Takip_durum"].ToString()) == 1) { grup5.Enabled = true; } else { grup5.Enabled = false; } cihazbilgi.Text = TkpNoOku["Takip_marka"].ToString() + " " + TkpNoOku["Takip_model"].ToString(); this.Text = cihazbilgi.Text; baglan2.Open(); FbCommand TakipGunSqlSay = new FbCommand("SELECT COUNT(*) FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); object say = TakipGunSqlSay.ExecuteScalar(); baglan2.Close(); baglan2.Open(); FbCommand TakipGunSql = new FbCommand("SELECT * FROM TeknikTakipIslemler WHERE Takip_id='" + id + "'", baglan2); if (int.Parse(say.ToString()) > 0) { FbDataReader TakipGunOku = TakipGunSql.ExecuteReader(); while (TakipGunOku.Read()) { islemler.Items.Add(TakipGunOku["Takip_bilgi"].ToString()); } } else { islemler.Items.Add("Bu cihazda şuanda işlem yapılmamıştır"); } baglan2.Close(); if (TkpNoOku["Takip_musteri"].ToString() != "") { baglan2.Open(); FbCommand MusSql = new FbCommand("SELECT Musteri_adi,Musteri_soyadi,Musteri_telefon FROM Musteriler WHERE Musteri_id='" + TkpNoOku["Takip_musteri"].ToString() + "'", baglan2); FbDataReader MusOku = MusSql.ExecuteReader(); MusOku.Read(); musadsoyad.Text = MusOku["Musteri_adi"].ToString() + " " + MusOku["Musteri_soyadi"].ToString(); mustel.Text = MusOku["Musteri_telefon"].ToString(); baglan2.Close(); } else { musadsoyad.Text = TkpNoOku["Takip_kisibilgi"].ToString(); mustel.Text = TkpNoOku["Takip_kisiTel"].ToString(); } baglan.Close(); } else { MessageBox.Show("Takip bulunamadı ( Hata kodu: T-04 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); grup3.Enabled = false; grup2.Enabled = false; cihazlar.Items.Clear(); islemler.Items.Clear(); cihazbilgi.Text = ""; musadsoyad.Text = ""; mustel.Text = ""; guncelleme.Text = ""; tkpKapat.Checked = false; this.Text = "Takip Güncelleme"; } baglan.Close(); } else if (AdveSoyad.Checked) { baglan.Open(); FbCommand TkpNoSql = new FbCommand("SELECT * FROM Teknik_Takip WHERE Takip_kisibilgi like '%" + fk.IlkHarfleriBuyut(AramaText.Text) + "%' or Takip_musteri in ( SELECT Musteri_id FROM Musteriler WHERE Musteri_adi like '%" + fk.IlkHarfleriBuyut(AramaText.Text) + "%' or Musteri_soyadi like '%" + fk.IlkHarfleriBuyut(AramaText.Text) + "%')", baglan); object sonuc; FbDataReader TkpNoOku; sonuc = TkpNoSql.ExecuteScalar(); if (sonuc != null) { TkpNoOku = TkpNoSql.ExecuteReader(); grup2.Enabled = true; cihazlar.Items.Clear(); while (TkpNoOku.Read()) { cihazlar.Items.Add(TkpNoOku["Takip_id"].ToString() + " " + TkpNoOku["Takip_marka"].ToString() + " " + TkpNoOku["Takip_model"].ToString()); } cihazlar.SelectedIndex = 0; } else { MessageBox.Show("Takip bulunamadı ( Hata kodu: T-04 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); grup3.Enabled = false; islemler.Items.Clear(); cihazbilgi.Text = ""; musadsoyad.Text = ""; mustel.Text = ""; guncelleme.Text = ""; tkpKapat.Checked = false; this.Text = "Takip Güncelleme"; } baglan.Close(); } } else { MessageBox.Show("Lütfen veri giriniz ( Hata kodu: T-05 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void SilEvet_Click(object sender, EventArgs e) { byte sonuc; FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan1 = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu()); // Müşteri sipariş verdiyse bu siparişleri silicez. baglan.Open(); FbCommand MusUrun = new FbCommand("SELECT Satis_urun FROM Satis WHERE Satis_musteri='" + id + "'", baglan); string[] urun_id = new string[0]; FbDataReader UrunidOku = MusUrun.ExecuteReader(); int i = 0; while (UrunidOku.Read()) { Array.Resize(ref urun_id, urun_id.Length + 1); urun_id[i] = UrunidOku["Satis_urun"].ToString(); i++; } baglan.Close(); foreach (string uid in urun_id) { baglan.Open(); FbCommand SatisTab = new FbCommand("SELECT Satis_id FROM Satis WHERE Satis_urun='" + uid + "'", baglan); FbDataReader SatisIdOku = SatisTab.ExecuteReader(); while (SatisIdOku.Read()) { fk.Sil("Satis", "Satis_id='" + SatisIdOku["Satis_id"].ToString() + "'"); } baglan.Close(); baglan.Open(); FbCommand RaporTab = new FbCommand("SELECT rapor_id,rapor_sayac FROM Rapor WHERE rapor_satisId='" + uid + "'", baglan); FbDataReader RaporIdOku = RaporTab.ExecuteReader(); while (RaporIdOku.Read()) { if (int.Parse(RaporIdOku["rapor_sayac"].ToString()) > 1) { fk.Guncelle("Rapor", "rapor_sayac=rapor_sayac-1", "rapor_id='" + RaporIdOku["rapor_id"].ToString() + "'"); } else { fk.Sil("Rapor", "rapor_id='" + RaporIdOku["rapor_id"].ToString() + "'"); } } baglan.Close(); } // Teknik takipleri sil baglan.Open(); FbCommand TeknikTab = new FbCommand("SELECT Takip_id FROM Teknik_Takip WHERE Takip_musteri='" + id + "'", baglan); FbDataReader Takipid = TeknikTab.ExecuteReader(); string[] takip_id = new string[0]; int b = 0; while (Takipid.Read()) { Array.Resize(ref takip_id, takip_id.Length + 1); takip_id[b] = Takipid["Takip_id"].ToString(); b++; } baglan.Close(); foreach (string tid in takip_id) { fk.Sil("Teknik_TakipIslemler", "Takip_id='" + tid + "'"); fk.Sil("Teknik_Takip", "Takip_id='" + tid + "'"); } sonuc = fk.Sil("Musteriler", "Musteri_id='" + id + "'"); if (sonuc == 1) { MessageBox.Show("Müşteri başarıyla silinmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } else { MessageBox.Show("Müşteri silinemedi ( Hata kodu: M-09 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void LoadInterface(Reserver reserverForm) { // POSSIBILI TODO: // // - Nuovo stato COLLAUDO giallo al posto di rosso / verde? // // - Chiudere reader vari nel codice // // - // using (FbConnection connection = new FbConnection(reserverForm.ConnectionString)) { try { connection.Open(); string queryStatusInfo = string.Format(@" SELECT s.descrizione, r.stato, s.codice FROM statiservers r JOIN servers s ON s.idserver = r.idserver"); FbCommand getStatusInfo = new FbCommand(queryStatusInfo, connection); FbDataAdapter dataReader = new FbDataAdapter(getStatusInfo); DataTable tableStatusInfo = new DataTable(); dataReader.Fill(tableStatusInfo); int top = 70; int insideLeft = 25; foreach (DataRow row in tableStatusInfo.Rows) { Label labelServerDescription = new Label(); labelServerDescription.Top = top; labelServerDescription.Left = 50; labelServerDescription.Size = new Size(200, 60); labelServerDescription.BackColor = Color.FromArgb(92, 162, 150); labelServerDescription.Text = row["DESCRIZIONE"].ToString(); labelServerDescription.TextAlign = ContentAlignment.MiddleCenter; // Esempio modifica Font // labelServerDescription.Font = new Font("Verdana", 9F, FontStyle.Regular, GraphicsUnit.Point, 0); labelServerDescription.Font = new Font(privateFontCollection.Families[0], 11, FontStyle.Bold); labelServerDescription.Name = "groupBox" + row["CODICE"].ToString(); panelServerStatus.Controls.Add(labelServerDescription); Panel panelServerInfo = new Panel(); panelServerInfo.Top = top; panelServerInfo.Left = 250; panelServerInfo.Size = new Size(450, 60); panelServerInfo.BackColor = Color.FromArgb(142, 198, 189); panelServerStatus.Controls.Add(panelServerInfo); MetroFramework.Controls.MetroButton button = new MetroFramework.Controls.MetroButton(); button.Left = insideLeft; button.Size = new Size(150, 25); button.Top = 20; button.Name = row["CODICE"].ToString(); button.FontSize = MetroFramework.MetroButtonSize.Small; button.FontWeight = MetroFramework.MetroButtonWeight.Regular; // Verifica utenti con rilasci attivi che hanno chiuso l'applicazione string queryCheckRilasciAttivi = string.Format(@" SELECT s.idserver FROM servers s JOIN storicorilasci sr ON s.idserver = sr.idserver WHERE s.codice = '{0}' AND sr.idutente = {1} AND sr.attivo = 1", row["CODICE"].ToString(), reserverForm.CurrentUserID); FbCommand getCheckRilasciAttivi = new FbCommand(queryCheckRilasciAttivi, connection); FbDataReader readerGetCheckRilasciAttivi = getCheckRilasciAttivi.ExecuteReader(); button.Text = readerGetCheckRilasciAttivi.Read() ? "Rilascio concluso" : "Rilascia"; readerGetCheckRilasciAttivi.Close(); button.Click += new EventHandler(this.ReleaseButton_Click); Label statusLabel = new Label(); statusLabel.Left = insideLeft + 175; statusLabel.Top = 20; statusLabel.Size = new Size(25, 25); statusLabel.Name = "status" + row["CODICE"].ToString(); statusLabel.Font = new Font("Verdana", 9F, FontStyle.Regular, GraphicsUnit.Point, 0); Label dateLabel = new Label(); dateLabel.Left = insideLeft + 225; dateLabel.Top = 20; dateLabel.AutoSize = false; dateLabel.TextAlign = ContentAlignment.MiddleCenter; dateLabel.Font = new Font("Verdana", 8F, FontStyle.Regular, GraphicsUnit.Point, 0); dateLabel.Size = new Size(100, 25); dateLabel.Name = "date" + row["CODICE"].ToString(); toolTip = new ToolTip(); toolTip.SetToolTip(dateLabel, null); Label avatarLabel = new Label(); avatarLabel.Left = insideLeft + 350; avatarLabel.Top = 5; avatarLabel.Size = new Size(50, 50); avatarLabel.Name = "icon" + row["CODICE"].ToString(); toolTip = new ToolTip(); toolTip.SetToolTip(avatarLabel, null); panelServerInfo.Controls.Add(button); panelServerInfo.Controls.Add(statusLabel); panelServerInfo.Controls.Add(dateLabel); panelServerInfo.Controls.Add(avatarLabel); top += labelServerDescription.Height + 10; } } catch (Exception ex) { MessageBox.Show("Richiesta fallita", "Errore di comunicazione con il server", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); Logger.LogStack("Errore.", ex.StackTrace.ToString()); } finally { connection.Close(); UpdateStatus(); } } }