internal override DtoBase PopulateDto(OracleDataReader reader) { var turno = new TurnoDto(); // if (!reader.IsDBNull(_ordTrnId)) { turno.TrnId = reader.GetInt32(_ordTrnId); } // if (!reader.IsDBNull(_ordTrnFecha)) { turno.TrnFecha = reader.GetString(_ordTrnFecha); } // if (!reader.IsDBNull(_ordTrnHora)) { turno.TrnHora = reader.GetString(_ordTrnHora); } // if (!reader.IsDBNull(_ordTrnMontoCobrado)) { turno.TrnMontoCobrado = reader.GetInt32(_ordTrnMontoCobrado); } // if (!reader.IsDBNull(_ordTrnEstId)) { turno.TrnEstId = reader.GetInt32(_ordTrnEstId); } // if (!reader.IsDBNull(_ordTrnProId)) { turno.TrnProId = reader.GetInt32(_ordTrnProId); } // if (!reader.IsDBNull(_ordTrnSesId)) { turno.TrnSesId = reader.GetInt32(_ordTrnSesId); } // if (!reader.IsDBNull(_ordTrnPaeId)) { turno.TrnPaeId = reader.GetInt32(_ordTrnPaeId); } // if (!reader.IsDBNull(_ordTrnDatId)) { turno.TrnDatId = reader.GetInt32(_ordTrnDatId); } // if (!reader.IsDBNull(_ordTrnOspId)) { turno.TrnOspId = reader.GetInt32(_ordTrnOspId); } // IsNew turno.IsNew = false; return turno; }
/// Safely reads decimal values from the database if they are null ///<returns>0 if null otherwise value from DB</returns> decimal SafeReadDecimal(OracleDataReader odr, int colindex) { if (!odr.IsDBNull(colindex)) return odr.GetDecimal(colindex); else return 0; }
private int ReadSequenceValue(OracleDataReader reader) { if (!reader.Read()) return 0; return (int)(decimal)reader[SequenceValueColumn]; }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordTstId = reader.GetOrdinal("tstId"); _ordTstObservacion = reader.GetOrdinal("tstObservacion"); _ordTstZcuId = reader.GetOrdinal("tst_zcuId"); _ordTstTptId = reader.GetOrdinal("tst_tptId"); }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordVacId = reader.GetOrdinal("vacId"); _ordVacFechaDesde = reader.GetOrdinal("vacFechaDesde"); _ordVacFechaHasta = reader.GetOrdinal("vacFechaHasta"); _ordVacProId = reader.GetOrdinal("vac_proId"); }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordOspId = reader.GetOrdinal("ospId"); _ordOspPaeId = reader.GetOrdinal("osp_paeId"); _ordOspOsoId = reader.GetOrdinal("osp_osoId"); _ordOspNroSocio = reader.GetOrdinal("OspNroSocio"); }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordZcuId = reader.GetOrdinal("zcuId"); _ordZcuDescripcion = reader.GetOrdinal("zcuDescripcion"); _ordZcuNivel = reader.GetOrdinal("zcuNivel"); _ordZcuIdPadre = reader.GetOrdinal("zcuIdPadre"); }
private IEnumerable<Phrase> ReadPhrases(OracleDataReader reader) { var phrases = new List<Phrase>(); Phrase phrase; Phrase lastPhrase = null; PhraseWord word; while ((phrase = ReadPhraseWord(reader, out word)) != null) { if (lastPhrase == null) lastPhrase = phrase; if (lastPhrase.PhraseNumber != phrase.PhraseNumber) { phrases.Add(lastPhrase); lastPhrase = phrase; } lastPhrase.Words.Add(word); } if (lastPhrase != null) phrases.Add(lastPhrase); return phrases; }
private IEnumerable<Relation> ReadRelations(OracleDataReader reader) { var relations = new List<Relation>(); Relation relation; Relation lastRelation = null; Pair pair; while ((relation = ReadRelationPair(reader, out pair)) != null) { if (lastRelation == null) lastRelation = relation; if (lastRelation.Name != relation.Name) { relations.Add(lastRelation); lastRelation = relation; } lastRelation.Pairs.Add(pair); } if (lastRelation != null) relations.Add(lastRelation); return relations; }
/// Safely reads string values from the database if they are null ///<returns>empty string otherwise value from DB</returns> string SafeReadString(OracleDataReader odr, int colindex) { if (!odr.IsDBNull(colindex)) return odr.GetString(colindex); else return string.Empty; }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordAfnId = reader.GetOrdinal("afnId"); _ordAfnDescripcion = reader.GetOrdinal("afnDescripcion"); _ordAfnTafId = reader.GetOrdinal("afn_tafId"); _ordAfnZcuId = reader.GetOrdinal("afn_zcuId"); }
/// Safely reads datetime values from the database if they are null ///<returns>datetime minimimvalue if null otherwise value from DB</returns> DateTime SafeReadDateTime(OracleDataReader odr, int colindex) { if (!odr.IsDBNull(colindex)) return odr.GetDateTime(colindex); else return DateTime.MinValue; }
private IEnumerable<Group> ReadGroups(OracleDataReader reader) { var groups = new List<Group>(); Group group; Group lastGroup = null; Word word; while ((group = ReadGroupWord(reader, out word)) != null) { if (lastGroup == null) lastGroup = group; if (lastGroup.Name != group.Name) { groups.Add(lastGroup); lastGroup = group; } lastGroup.Words.Add(word); } if (lastGroup != null) groups.Add(lastGroup); return groups; }
internal override DtoBase PopulateDto(OracleDataReader reader) { var turno = new TurneroDto(); // if (!reader.IsDBNull(_ordTurId)) { turno.TurId = reader.GetInt32(_ordTurId); } // if (!reader.IsDBNull(_ordTurTitulo)) { turno.TurTitulo = reader.GetString(_ordTurTitulo); } // if (!reader.IsDBNull(_ordTurDescripcion)) { turno.TurDescripcion = reader.GetString(_ordTurDescripcion); } // if (!reader.IsDBNull(_ordTurFechaIni)) { turno.TurFechaIni = reader.GetDateTime(_ordTurFechaIni); } // if (!reader.IsDBNull(_ordTurFechaFin)) { turno.TurFechaFin = reader.GetDateTime(_ordTurFechaFin); } // if (!reader.IsDBNull(_ordTurTodoDia)) { turno.TurTodoDia = reader.GetString(_ordTurTodoDia); } // if (!reader.IsDBNull(_ordTurPae)) { turno.TurPae = reader.GetInt32(_ordTurPae); } // if (!reader.IsDBNull(_ordTurPro)) { turno.TurPro= reader.GetInt32(_ordTurPro); } // if (!reader.IsDBNull(_ordTurMonto)) { turno.TurMonto = reader.GetDecimal(_ordTurMonto); } // if (!reader.IsDBNull(_ordTurOspId)) { turno.TurOspId = reader.GetInt32(_ordTurOspId); } // IsNew turno.IsNew = false; return turno; }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordOsoId = reader.GetOrdinal("osoId"); _ordOsoDescripcion = reader.GetOrdinal("osoDescripcion"); _ordOsoCoseguro = reader.GetOrdinal("osoCoseguro"); _ordOsoContacto = reader.GetOrdinal("osoContacto"); }
public BlogBericht CreateBlogBerichtFromReader(OracleDataReader reader) { int blogBerichtId = Convert.ToInt32(reader["BLOGBERICHTID"]); int categorieId = Convert.ToInt32(reader.GetOrdinal("CATEGORIEID")); int actualCategorieId = SafeGetInt(reader, categorieId); int productId = Convert.ToInt32(reader.GetOrdinal("PRODUCTID")); int actualProductId = SafeGetInt(reader, productId); string titel = Convert.ToString(reader["TITEL"]); DateTime datum = Convert.ToDateTime(reader["DATUM"]); string tekst = Convert.ToString(reader["TEKST"]); int afbeeldingPath = Convert.ToInt32(reader.GetOrdinal("AFBEELDINGPATH")); string actualAfbeeldingPath = SafeGetString(reader, afbeeldingPath); Categorie c = null; foreach (Categorie categorie in GetAlleCategorien()) { if (categorie.CategorieId == categorieId) { c = categorie; } } return new BlogBericht(blogBerichtId, titel, datum, tekst, actualAfbeeldingPath, c); }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordProId = reader.GetOrdinal("proId"); _ordProMatricula = reader.GetOrdinal("proMatricula"); _ordProTelefonoInterno = reader.GetOrdinal("proTelefonoInterno"); _ordProPsnId = reader.GetOrdinal("pro_psnId"); }
public static Employee GetInfo(OracleDataReader reader, DPFP.Sample Sample, string Finger) { OracleConnection conn = new OracleConnection(ConStr); Employee _info = null; DPFP.Template _template = null; bool IsFound = false; if (reader[Finger] != DBNull.Value) { _template = Util.ProcessDBTemplate((byte[])reader[Finger]); IsFound = Util.Verify(Sample, _template); } if (IsFound == true) { string sqlEmp = "select * from employees where Empl_Id_No=" + reader["Empl_Id_No"]; OracleCommand cmd = new OracleCommand(sqlEmp, conn); cmd.CommandType = CommandType.Text; conn.Open(); OracleDataReader odr = cmd.ExecuteReader(); if (odr.HasRows) { _info = new Employee(); _info.Empl_Id_No = Convert.ToInt32(reader["Empl_Id_No"]); _info.Empl_Name = (string)odr["Empl_Name"]; _info.Empl_Deptname = (string)odr["Empl_Deptname"]; _info.Shift_Id = Convert.ToInt32(odr["Shift_Id"]); } odr.Dispose(); cmd.Dispose(); conn.Close(); conn.Dispose(); } return _info; }
protected void Giris_Buton(object sender, EventArgs e) { if (Kul_TB.Text == "Admin" && Sifre_TB.Text == "Password") { Page.Response.Redirect("MusteriKayitlari.aspx"); } baglanti = new OracleConnection(constring); baglanti.Open(); komut = new OracleCommand(); komut.Connection = baglanti; komut.CommandText = "select * from t_musteri where kul_ad='" + Kul_TB.Text + "' and sifre='" + Sifre_TB.Text + "'"; rd = komut.ExecuteReader(); if (rd.Read()) { Session.Add("kullanici", Kul_TB.Text); Page.Response.Redirect("AfterLogin.aspx"); } else { Durum_Label.Text = "Giriş Başarısız.."; Durum_Label.Visible = true; } }
/// <summary> /// Safely reads int values from the database if they are null /// </summary> /// <param name="odr">oracle datareader</param> /// <param name="colindex">column index</param> /// <returns>-1 if null otherwise value from DB</returns> int SafeReadInt(OracleDataReader odr, int colindex) { if (!odr.IsDBNull(colindex)) return odr.GetInt32(colindex); else return -1; }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordLicId = reader.GetOrdinal("LICID"); _ordLicFechaDesde = reader.GetOrdinal("LICFECHADESDE"); _ordLicFechaHasta = reader.GetOrdinal("LICFECHAHASTA"); _ordLicLctId = reader.GetOrdinal("LIC_LCTID"); _ordLicProId = reader.GetOrdinal("LIC_PROID"); }
protected void grid_yenile() { baglanti_kur(); komut.CommandText = "select durum_id,durum,detay,ucret,durum_tarih from t_durum where ariza_id='" + Session["ariza_id"] + "' order by durum_id"; rd = komut.ExecuteReader(); durum_gecmis.DataSource = rd; durum_gecmis.DataBind(); Durum_Gir.Visible = true; }
//private Recht CreateRechtFromReader(OracleDataReader reader) //{ // return new Recht( // Convert.ToInt32(reader["ID"]), // Convert.ToString((reader["Omschrijving"])), // Convert.ToInt32(reader["Functie_ID"]) // ); //} //private Diersoort CreateDiersoortFromReader(OracleDataReader reader) //{ // int id = Convert.ToInt32(reader["ID"]); // string naam = Convert.ToString(reader["Naam"]); // string afkorting = Convert.ToString(reader["Afkorting"]); // DateTime start = Convert.ToDateTime(reader["BroedStart"]); // DateTime eind = Convert.ToDateTime(reader["BroedEind"]); // int req = Convert.ToInt32(reader["BroedReq"]); // return new Diersoort(id, naam, afkorting, start, eind, req); //} private Gebied CreateGebiedFromReader(OracleDataReader reader) { int id = Convert.ToInt32(reader["ID"]); string naam = Convert.ToString(reader["Naam"]); string kaartpath = Convert.ToString(reader["KaartPath"]); return new Gebied(id, naam, kaartpath); }
protected void mus_sifre_cek() { baglanti_kur(); komut.CommandText = "select sifre from t_musteri where kul_ad='"+Session["kullanici"].ToString()+"'"; rd = komut.ExecuteReader(); if (rd.Read()) sifre = rd["sifre"].ToString(); baglanti.Close(); }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordAgeId = reader.GetOrdinal("ageId"); _ordAgeHoraDesde = reader.GetOrdinal("ageHoraDesde"); _ordAgeHoraHasta = reader.GetOrdinal("ageHoraHasta"); _ordAgeProId = reader.GetOrdinal("age_proId"); _ordAgeDiaId = reader.GetOrdinal("age_diaId"); }
private TelGebied CreateTelgebiedFromReader(OracleDataReader reader) { return new TelGebied( Convert.ToInt32(reader["ID"]), Convert.ToString(reader["Naam"]), Convert.ToString(reader["PathMap"]) ); }
public static Function Transform(OracleDataReader record) { return new Function { Id = Convert.ToInt32(record["ID"]), Name = Convert.ToString(record["NAME"]), PageLink = Convert.ToString(record["PAGELINK"]) }; }
public static ApprovalConfiguration Transform(OracleDataReader record) { return new ApprovalConfiguration { Id = Convert.ToInt32(record["ID"]), Type = Convert.ToString(record["TYPE"]), Approve = Convert.ToInt32(record["APPROVE"]) == 0 ? false : true }; }
/// <summary> /// Executes the command and puts the results in the DataReader. /// </summary> /// <returns></returns> public bool ExecuteCommand() { dataReader = cmd.ExecuteReader(); if (dataReader != null) { return true; } return false; }
public static void Main(string[] args) { string tainted_2 = null; string tainted_3 = null; tainted_2 = "hardcoded"; tainted_3 = tainted_2; if ((1 == 0)) { {} } else if (!(1 == 0)) { {} } else { StringBuilder escape = new StringBuilder(); for (int i = 0; i < tainted_2.Length; ++i) { char current = tainted_2[i]; switch (current) { case '\\': escape.Append(@"\5c"); break; case '*': escape.Append(@"\2a"); break; case '(': escape.Append(@"\28"); break; case ')': escape.Append(@"\29"); break; case '\u0000': escape.Append(@"\00"); break; case '/': escape.Append(@"\2f"); break; default: escape.Append(current); break; } } tainted_3 = escape.ToString(); } string query = "SELECT * FROM '" + tainted_3 + "'"; string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password"; OracleConnection dbConnection = null; try{ dbConnection = new OracleConnection(connectionString); dbConnection.Open(); OracleCommand cmd = dbConnection.CreateCommand(); cmd.CommandText = query; OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.ToString()); } dbConnection.Close(); }catch (Exception e) { Console.WriteLine(e.ToString()); } }
static string get_max_idseq_trx_table_in_oracle() { string max_id_seq = null; OracleConnection conn = null; OracleTransaction transaction = null; try { conn = new OracleConnection(cs_oracle); conn.Open(); transaction = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT MAX(ID_PAY) FROM " + oracle_pg_trx_payment_table + ""; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { int the_field_count = dr.FieldCount; for (int a = 0; a < dr.FieldCount; a++) { //Console.WriteLine(dr.GetValue(a)); max_id_seq = dr.GetValue(0).ToString(); } } //Console.Read(); } catch (OracleException ex) { try { transaction.Rollback(); } catch (OracleException ex1) { //Console.WriteLine("Error: {0}", ex1.ToString()); sb.Append("Error: {0}" + ex1.ToString() + Environment.NewLine); checking_file_log(); write_info_to_log_file(); } Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (conn != null) { conn.Close(); } } return(max_id_seq); }
///// <summary> ///// Called after the reader has been positioned at the next row ///// </summary> //[Obsolete] //internal void RefreshValues() //{ // //_reader.GetProviderSpecificValues(_values); // _reader.GetOracleValues(_values); //} /// <summary> /// Call this each time the reader is repositioned /// </summary> /// <param name="reader"></param> internal void SetValues(OracleDataReader reader) { reader.GetOracleValues(_values); }
private void filtr_Click(object sender, EventArgs e) { filter_for_invoice r = new filter_for_invoice(); r.StartPosition = FormStartPosition.CenterParent; r.ShowDialog(); if (Data_bill.date_from != false || Data_bill.ser_num != false || Data_bill.ser_aggr != false || Data_bill.crp != false || Data_bill.inn != false || Data_bill.pinfl != false || Data_bill.code_nds != false || Data_bill.name != false || Data_bill.serv != false || Data_bill.status != false || Data_bill.fio != false) { string request = ""; string name_cl = ""; OracleCommand cmd = con.CreateCommand(); if (Data_bill.date_from == true) { request = $" AND DATE_T >= '{Data_bill.s_date_from}' AND DATE_T <= '{Data_bill.s_date_to}' "; } if (Data_bill.ser_num == true) { request = request + $" AND ser LIKE '{Data_bill.s_ser_num}/%'"; } if (Data_bill.ser_aggr == true) { request = request + $" AND ser LIKE '%/{Data_bill.s_ser_aggr}'"; } if (Data_bill.crp == true) { request = request + $" AND CRP = {Data_bill.s_crp} "; } if (Data_bill.name == true) { for (int i = 0; i < Data_bill.s_name.Length; i++) { if (Data_bill.s_name[i] == '%') { name_cl += '_'; } else { name_cl += Data_bill.s_name[i]; } } request = request + $" AND CRP_NM LIKE '%{name_cl}%' "; } if (Data_bill.inn == true) { request = request + $" AND INN = '{Data_bill.s_inn}'"; } if (Data_bill.pinfl == true) { request = request + $" AND NDS_PINFL = '{Data_bill.s_pinfl}' AND IF_FIZ = 1"; } if (Data_bill.code_nds == true) { request = request + $" AND NDS_PINFL = '{Data_bill.s_code_nds}' AND IF_FIZ = 0"; } if (Data_bill.serv == true) { request = request + $" AND SERVICE_T = '{Data_bill.s_serv}'"; } if (Data_bill.fio == true) { request = request + $" AND FIO = '{Data_bill.s_fio}'"; } if (Data_bill.status == true) { request = request + $" AND STATUS = '{Data_bill.s_status}'"; } if (Data_bill.its_ok) { string str = "SELECT * from registration_of_invoice where 1 = 1 " + request + " order by ID desc "; cmd.CommandText = str; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { LoadData(str); } else { MessageBox.Show("Не найдено по данному запросу!"); } Data_bill.clear(); } } }
internal override void PopulateOrdinals(OracleDataReader reader) { _ordDiaId = reader.GetOrdinal("diaId"); _ordDiaDescripcion = reader.GetOrdinal("diaDescripcion"); }
public List <OrdenCocina> getOrdenesByEstadoEnCocina() { List <OrdenCocina> ordenesCocina = new List <OrdenCocina>(); try { cmd.CommandText = "PACKAGE_ORDEN.PR_LISTAR_ORDENES_EN_COCINA"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("CURSOR_ORDENES", OracleDbType.RefCursor).Direction = ParameterDirection.Output; OracleDataReader reader = cmd.ExecuteReader(); foreach (var item in reader) { Orden orden = new Orden(); orden.IdOrden = reader.GetInt32(0); orden.Descripcion = reader.GetString(1); orden.TiempoPreparacion = reader.GetInt32(2); Cliente cliente = new Cliente(); Mesa mesa = new Mesa(); mesa.NumeroMesa = reader.GetInt32(3); cliente.Mesa = mesa; orden.Cliente = cliente; orden.FechaCompleta = reader.GetString(4); orden.Fecha = reader.GetString(5); orden.Hora = reader.GetString(6); OrdenCocina ordenCocina = new OrdenCocina(); List <RecetaOrdenada> recetasOrdeadas = new List <RecetaOrdenada>(); ordenCocina.Orden = orden; ordenCocina.RecetaOrdenada = recetasOrdeadas; ordenesCocina.Add(ordenCocina); } con.Close(); return(ordenesCocina); } catch (Exception) { OrdenCocina ordenCocina = new OrdenCocina(); Orden orden = new Orden(); orden.Descripcion = "Error: ENTTRO EN EL CASH DE PROCEDURE"; ordenCocina.Orden = orden; ordenesCocina.Add(ordenCocina); ordenesCocina.Add(ordenCocina); ordenesCocina.Add(ordenCocina); ordenesCocina.Add(ordenCocina); return(ordenesCocina); } }
//1 private void LastColumnComboSelectionChanged(object sender, EventArgs e) { var DS = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator[0]; var currentcell = dataGridView_invoice.CurrentCellAddress; string num_date_invoice = dataGridView_invoice.Rows[currentcell.Y].Cells[0].Value.ToString(); string previous_value = dataGridView_invoice.Rows[currentcell.Y].Cells[10].Value.ToString(); int num; string ID1 = ""; int i = 0; while (num_date_invoice[i] != ' ') { ID1 += num_date_invoice[i]; i++; } OracleCommand cmd; string sum = ""; if (dataGridView_invoice.Rows[currentcell.Y].Cells[currentcell.X].EditedFormattedValue.ToString() == "Выставлена") { num = 0; } else if (dataGridView_invoice.Rows[currentcell.Y].Cells[currentcell.X].EditedFormattedValue.ToString() == "Часть оплаты") { num = 1; } else { //123 cmd = con.CreateCommand(); cmd.CommandText = $"select SUM_T from registration_of_invoice where ID = {ID1}"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); sum = dr[0].ToString().Replace(DS.ToString(), "."); dr.Close(); num = 2; } if (num == 0 && previous_value != dataGridView_invoice.Rows[currentcell.Y].Cells[currentcell.X].EditedFormattedValue.ToString()) { cmd = con.CreateCommand(); dataGridView_invoice.Rows[currentcell.Y].Cells[11].ReadOnly = true; cmd.CommandText = $"UPDATE REGISTRATION_OF_INVOICE SET PROCESS = {num}, SUM_PAID = 0 where id = {ID1}"; cmd.ExecuteNonQuery(); LoadData("select * from registration_of_invoice order by ID, num_of_ser"); } else if (num == 1 && previous_value != dataGridView_invoice.Rows[currentcell.Y].Cells[currentcell.X].EditedFormattedValue.ToString()) { sum_for_pay r = new sum_for_pay(ID1, 1); r.StartPosition = FormStartPosition.CenterParent; r.ShowDialog(); if (Data.yes == true) { cmd = con.CreateCommand(); dataGridView_invoice.Rows[currentcell.Y].Cells[11].ReadOnly = false; cmd.CommandText = $"UPDATE REGISTRATION_OF_INVOICE SET PROCESS = {num} where id = {ID1}"; cmd.ExecuteNonQuery(); LoadData("select * from registration_of_invoice order by ID, num_of_ser"); } Data.yes = false; } else if (num == 2 && previous_value != dataGridView_invoice.Rows[currentcell.Y].Cells[currentcell.X].EditedFormattedValue.ToString()) { cmd = con.CreateCommand(); dataGridView_invoice.Rows[currentcell.Y].Cells[11].ReadOnly = true; cmd.CommandText = $"UPDATE REGISTRATION_OF_INVOICE SET PROCESS = {num}, SUM_PAID = {sum} where id = {ID1}"; cmd.ExecuteNonQuery(); LoadData("select * from registration_of_invoice order by ID, num_of_ser"); } }
public void getDataBy(DateTime rq) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.Parameters.Add(":rq", OracleType.DateTime).Value = rq; #region 本日部分 //炼钢铁 cmd.CommandText = "select gaolu,case when fesi<=1.25 then '炼钢铁' else '铸造铁' end,sum(feliang) from ddluci where trunc(zdsj)=:rq and dksj is not null group by gaolu,case when fesi<=1.25 then '炼钢铁' else '铸造铁' end"; OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); if (!dr.IsDBNull(1)) { this.SetValue(string.Format("本日.高炉{0}.{1}", gaolu, dr.GetString(1)), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); } } } dr.Close(); //大中修 cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间)=:rq and (分类='大修' or 分类='中修') and 间隔>0 and 间隔 is not null group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { int gaolu = dr.IsDBNull(0) ? 6 : dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.大中修", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } dr.Close(); //合格铁 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where 合格铁(fesi,fes)=1 and trunc(zdsj)=:rq and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.合格铁", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //送炼钢厂,本厂铸造,SI小,SI大 cmd.CommandText = "select gaolu,decode(quchu,'炼钢','送炼钢厂','本厂铸造'),case when fesi<=1.25 then 'SI小' else 'SI大' end,sum(feliang) from ddluci where trunc(zdsj)=:rq and dksj is not null group by gaolu,decode(quchu,'炼钢','送炼钢厂','本厂铸造'),case when fesi<=1.25 then 'SI小' else 'SI大' end"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.{1}{2}", gaolu, dr.GetString(1), dr.GetString(2)), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); } } dr.Close(); ////炼钢铁、铸造铁成分 cmd.CommandText = "select nvl(gaolu,9999),case when fesi<=1.25 then '炼钢铁' else '铸造铁' end,round(sum(feliang*FESI)/sum(feliang),2),round(sum(feliang*FEMn)/sum(feliang),2),round(sum(feliang*FEP)/sum(feliang),3),round(sum(feliang*FES)/sum(feliang),3) from ddluci where feliang>0 and trunc(zdsj)=:rq and dksj is not null group by rollup(gaolu),case when fesi<=1.25 then '炼钢铁' else '铸造铁' end"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); if (gaolu != 9999) { this.SetValue(string.Format("本日.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "Si"), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("本日.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "Mn"), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("本日.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "P"), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("本日.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "S"), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); } else { this.SetValue(string.Format("本日.{0}{1}", dr.GetString(1), "Si"), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("本日.{0}{1}", dr.GetString(1), "Mn"), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("本日.{0}{1}", dr.GetString(1), "P"), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("本日.{0}{1}", dr.GetString(1), "S"), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); } } } dr.Close(); //炉渣成分 cmd.CommandText = "select nvl(gaolu,9999),trunc(avg(ZHAR2),2) from ddluci where trunc(zdsj)=:rq and dksj is not null group by rollup(gaolu)"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); if (gaolu != 9999) { this.SetValue(string.Format("本日.高炉{0}.炉渣碱度", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } else { this.SetValue(string.Format("本日.炉渣碱度"), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } } dr.Close(); //全铁产量 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj)=:rq and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.全铁产量", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //s<0.02比例 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj)=:rq and dksj is not null and fes<=0.02 group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.S小于002", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //P<0.009比例 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj)=:rq and dksj is not null and fep<=0.09 group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); double fesliang = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); this.SetValue(string.Format("本日.高炉{0}.P小于009", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //Ti<0.055比例 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj)=:rq and dksj is not null and feti<=0.055 group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); double fesliang = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); this.SetValue(string.Format("本日.高炉{0}.TI小于055", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //折算产量 cmd.CommandText = "select gaolu,sum(折算产量(feliang,fesi,fes)) from ddluci where trunc(zdsj)=:rq and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.折算产量", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); cmd.CommandText = "select P05二号皮带,P06三号皮带,P07总返矿,自产湿焦,落地湿焦,P02自产焦水分,P03落地焦水分,P04焦粉水分 from 全厂日消耗 where P01日期=:rq"; dr = cmd.ExecuteReader(); if (dr.Read()) { this.SetValue(string.Format("本日.二号皮带"), dr.IsDBNull(0) ? 0 : dr.GetDouble(0)); this.SetValue(string.Format("本日.三号皮带"), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("本日.返矿"), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("本日.自产湿焦"), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("本日.落地湿焦"), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("本日.自产湿焦水份"), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); this.SetValue(string.Format("本日.落地湿焦水份"), dr.IsDBNull(6) ? 0 : dr.GetDouble(6)); this.SetValue(string.Format("本日.湿焦粉水份"), dr.IsDBNull(7) ? 0 : dr.GetDouble(7)); // this.SetValue(string.Format("本日.湿焦粉水份"), dr.IsDBNull(8) ? 0 : dr.GetDouble(8)); } dr.Close(); // 原料消耗 cmd.CommandText = "select 高炉,trunc(机烧矿,2),trunc(球团矿,2),trunc(国内球团矿,2),trunc(进口球团矿,2),trunc(PB块,2),trunc(纽曼块,2),trunc(其它块矿,2),trunc(高钛球团矿,2),trunc(高品位钛球,2),trunc(钛球,2),trunc(锰矿,2)," + "工艺称,煤粉,焦丁,trunc(自产湿焦,2),trunc(落地湿焦,2),trunc(硅石,2),trunc(萤石,2),trunc(蛇纹石,2),trunc(其它熔剂,2) from 原料消耗 where 日期=:rq"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.机烧", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("本日.高炉{0}.球团矿", gaolu), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("本日.高炉{0}.国内球团矿", gaolu), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("本日.高炉{0}.进口球团矿", gaolu), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("本日.高炉{0}.PB块", gaolu), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); this.SetValue(string.Format("本日.高炉{0}.纽曼块", gaolu), dr.IsDBNull(6) ? 0 : dr.GetDouble(6)); this.SetValue(string.Format("本日.高炉{0}.其它块矿", gaolu), dr.IsDBNull(7) ? 0 : dr.GetDouble(7)); this.SetValue(string.Format("本日.高炉{0}.高钛球团矿", gaolu), dr.IsDBNull(8) ? 0 : dr.GetDouble(8)); this.SetValue(string.Format("本日.高炉{0}.高品位钛球", gaolu), dr.IsDBNull(9) ? 0 : dr.GetDouble(9)); this.SetValue(string.Format("本日.高炉{0}.钛球", gaolu), dr.IsDBNull(10) ? 0 : dr.GetDouble(10)); this.SetValue(string.Format("本日.高炉{0}.锰矿", gaolu), dr.IsDBNull(11) ? 0 : dr.GetDouble(11)); this.SetValue(string.Format("本日.高炉{0}.工艺称焦炭", gaolu), dr.IsDBNull(12) ? 0 : dr.GetDouble(12)); this.SetValue(string.Format("本日.高炉{0}.煤粉总耗", gaolu), dr.IsDBNull(13) ? 0 : dr.GetDouble(13)); this.SetValue(string.Format("本日.高炉{0}.焦丁", gaolu), dr.IsDBNull(14) ? 0 : dr.GetDouble(14)); this.SetValue(string.Format("本日.高炉{0}.自产湿焦", gaolu), dr.IsDBNull(15) ? 0 : dr.GetDouble(15)); this.SetValue(string.Format("本日.高炉{0}.落地湿焦", gaolu), dr.IsDBNull(16) ? 0 : dr.GetDouble(16)); this.SetValue(string.Format("本日.高炉{0}.硅石", gaolu), dr.IsDBNull(17) ? 0 : dr.GetDouble(17)); this.SetValue(string.Format("本日.高炉{0}.萤石", gaolu), dr.IsDBNull(18) ? 0 : dr.GetDouble(18)); this.SetValue(string.Format("本日.高炉{0}.蛇纹石", gaolu), dr.IsDBNull(19) ? 0 : dr.GetDouble(19)); this.SetValue(string.Format("本日.高炉{0}.其它熔剂", gaolu), dr.IsDBNull(20) ? 0 : dr.GetDouble(20)); } } dr.Close(); // cmd.CommandText = "select gaolu,shijiaofen,fuyang,lengfengliuliang,feitie,fengwen,ludingwendu,refengyali,ludingyali,fengkoudatao,fengkouzhongtao,fengkouxiaotao,zhakoudatao,zhakouzhongtao,zhakouxiaotao,zuoliao,xuanliao,bengliao,trunc(shijiaofen*(1-全厂日消耗.P04焦粉水分/100),2) as 干焦粉 from xiaohao,全厂日消耗 where 全厂日消耗.P01日期=xiaohao.RQ and xiaohao.RQ=:rq"; cmd.CommandText = "select gaolu,shijiaofen,fuyang,lengfengliuliang,feitie,fengwen,ludingwendu,refengyali,ludingyali,fengkoudatao,fengkouzhongtao,fengkouxiaotao,zhakoudatao,zhakouzhongtao,zhakouxiaotao,zuoliao,xuanliao,bengliao,jiaoding,Jiaodingshuifen,JiaofenShuiFen from xiaohao where RQ=:rq"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.湿焦粉", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("本日.高炉{0}.富氧量", gaolu), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("本日.高炉{0}.冷风流量", gaolu), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("本日.高炉{0}.废铁总耗", gaolu), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("本日.高炉{0}.平均风温", gaolu), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); this.SetValue(string.Format("本日.高炉{0}.炉顶温度", gaolu), dr.IsDBNull(6) ? 0 : dr.GetDouble(6)); this.SetValue(string.Format("本日.高炉{0}.热风压力", gaolu), dr.IsDBNull(7) ? 0 : dr.GetDouble(7)); this.SetValue(string.Format("本日.高炉{0}.炉顶压力", gaolu), dr.IsDBNull(8) ? 0 : dr.GetDouble(8)); this.SetValue(string.Format("本日.高炉{0}.风口损坏数大", gaolu), dr.IsDBNull(9) ? 0 : dr.GetDouble(9)); this.SetValue(string.Format("本日.高炉{0}.风口损坏数中", gaolu), dr.IsDBNull(10) ? 0 : dr.GetDouble(10)); this.SetValue(string.Format("本日.高炉{0}.风口损坏数小", gaolu), dr.IsDBNull(11) ? 0 : dr.GetDouble(11)); this.SetValue(string.Format("本日.高炉{0}.渣口损坏数大", gaolu), dr.IsDBNull(12) ? 0 : dr.GetDouble(12)); this.SetValue(string.Format("本日.高炉{0}.渣口损坏数中", gaolu), dr.IsDBNull(13) ? 0 : dr.GetDouble(13)); this.SetValue(string.Format("本日.高炉{0}.渣口损坏数小", gaolu), dr.IsDBNull(14) ? 0 : dr.GetDouble(14)); this.SetValue(string.Format("本日.高炉{0}.坐料次数", gaolu), dr.IsDBNull(15) ? 0 : dr.GetDouble(15)); this.SetValue(string.Format("本日.高炉{0}.悬料次数", gaolu), dr.IsDBNull(16) ? 0 : dr.GetDouble(16)); this.SetValue(string.Format("本日.高炉{0}.崩料次数", gaolu), dr.IsDBNull(17) ? 0 : dr.GetDouble(17)); this.SetValue(string.Format("本日.高炉{0}.湿焦丁", gaolu), dr.IsDBNull(18) ? 0 : dr.GetDouble(18)); this.SetValue(string.Format("本日.高炉{0}.焦丁水份", gaolu), dr.IsDBNull(19) ? 0 : dr.GetDouble(19)); this.SetValue(string.Format("本日.高炉{0}.焦粉水份", gaolu), dr.IsDBNull(20) ? 0 : dr.GetDouble(20)); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间)=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.休风情况", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 慢风 where trunc(时间)=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("本日.高炉{0}.慢风", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //纽曼块 //cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%纽曼%' or beizhu like '%纽曼%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.纽曼块", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////PB块 //cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%PB%' or beizhu like '%PB%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.PB块", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); //钛球 //cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%钛球%' or beizhu like '%钛球%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.钛球", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////锰矿 //cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%锰矿%' or beizhu like '%锰矿%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.锰矿", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); //硅石 // cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%硅石%' or beizhu like '%硅石%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.硅石", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////白云石 //cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%白云石%' or beizhu like '%白云石%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.白云石", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////蛇纹石 //cmd.CommandText = "select gaolu,nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0) from rbxiaohao where trunc(sj)=:rq and (MC like '%蛇纹石%' or beizhu like '%蛇纹石%') "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("本日.高炉{0}.蛇纹石", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); #endregion #region 累计部分 //炼钢铁 cmd.CommandText = "select gaolu,case when fesi<=1.25 then '炼钢铁' else '铸造铁' end,sum(feliang) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by gaolu,case when fesi<=1.25 then '炼钢铁' else '铸造铁' end"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); if (!dr.IsDBNull(1)) { this.SetValue(string.Format("累计.高炉{0}.{1}", gaolu, dr.GetString(1)), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); } } } dr.Close(); //大中修 cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间,'MONTH')=trunc(:rq,'MONTH') and trunc(时间)<=:rq and (分类='大修' or 分类='中修') and 间隔>0 and 间隔 is not null group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { int gaolu = dr.IsDBNull(0) ? 6 : dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.大中修", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } dr.Close(); //合格铁 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where 合格铁(fesi,fes)=1 and trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.合格铁", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //送炼钢厂,本厂铸造,SI小,SI大 cmd.CommandText = "select gaolu,decode(quchu,'炼钢','送炼钢厂','本厂铸造'),case when fesi<=1.25 then 'SI小' else 'SI大' end,sum(feliang) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by gaolu,decode(quchu,'炼钢','送炼钢厂','本厂铸造'),case when fesi<=1.25 then 'SI小' else 'SI大' end"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.{1}{2}", gaolu, dr.GetString(1), dr.GetString(2)), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); } } dr.Close(); //炼钢铁、铸造铁成分 cmd.CommandText = "select nvl(gaolu,9999),case when fesi<=1.25 then '炼钢铁' else '铸造铁' end,round(sum(feliang*FESI)/sum(feliang),2),round(sum(feliang*FEMn)/sum(feliang),2),round(sum(feliang*FEP)/sum(feliang),3),round(sum(feliang*FES)/sum(feliang),3) from ddluci where feliang>0 and trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by rollup(gaolu),case when fesi<=1.25 then '炼钢铁' else '铸造铁' end"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); if (gaolu != 9999) { this.SetValue(string.Format("累计.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "Si"), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("累计.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "Mn"), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("累计.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "P"), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("累计.高炉{0}.{1}{2}", gaolu, dr.GetString(1), "S"), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); } else { this.SetValue(string.Format("累计.{0}{1}", dr.GetString(1), "Si"), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("累计.{0}{1}", dr.GetString(1), "Mn"), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("累计.{0}{1}", dr.GetString(1), "P"), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("累计.{0}{1}", dr.GetString(1), "S"), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); } } } dr.Close(); //炉渣成分 cmd.CommandText = "select nvl(gaolu,9999),trunc(avg(ZHAR2),2) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by rollup(gaolu)"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); if (gaolu != 9999) { this.SetValue(string.Format("累计.高炉{0}.炉渣碱度", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } else { this.SetValue(string.Format("累计.炉渣碱度"), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } } dr.Close(); //全铁产量 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.全铁产量", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //s<0.02 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and fes<=0.02 and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.S小于002", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //p<0.002 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and fep<=0.09 and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.P小于009", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //p<0.0055 cmd.CommandText = "select gaolu,sum(feliang) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and feti<=0.055 and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.TI小于055", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); //折算产量 cmd.CommandText = "select gaolu,sum(折算产量(feliang,fesi,fes)) from ddluci where trunc(zdsj,'MONTH')=trunc(:rq,'MONTH') and trunc(zdsj)<=:rq and dksj is not null group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.折算产量", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); cmd.CommandText = "select SUM(P05二号皮带),SUM(P06三号皮带),SUM(P07总返矿),SUM(自产湿焦),SUM(落地湿焦),TRUNC(AVG(P02自产焦水分),2),TRUNC(AVG(P03落地焦水分),2),TRUNC(AVG(P04焦粉水分),2) from 全厂日消耗 where trunc(P01日期,'MONTH')=trunc(:rq,'MONTH') and P01日期<=:rq"; dr = cmd.ExecuteReader(); if (dr.Read()) { this.SetValue(string.Format("累计.二号皮带"), dr.IsDBNull(0) ? 0 : dr.GetDouble(0)); this.SetValue(string.Format("累计.三号皮带"), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("累计.返矿"), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("累计.自产湿焦"), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("累计.落地湿焦"), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("累计.自产湿焦水份"), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); this.SetValue(string.Format("累计.落地湿焦水份"), dr.IsDBNull(6) ? 0 : dr.GetDouble(6)); this.SetValue(string.Format("累计.湿焦粉水份"), dr.IsDBNull(7) ? 0 : dr.GetDouble(7)); } dr.Close(); cmd.CommandText = "select 高炉,sum(trunc(机烧矿,2)),sum(trunc(球团矿,2)),sum(trunc(国内球团矿,2)),sum(trunc(进口球团矿,2)),sum(trunc(PB块,2)),sum(trunc(纽曼块,2)),sum(trunc(其它块矿,2)),sum(trunc(高钛球团矿,2)),sum(trunc(高品位钛球,2)),sum(trunc(钛球,2)),sum(trunc(锰矿,2))," + "sum(工艺称),sum(煤粉),sum(焦丁),sum(trunc(自产湿焦,2)),sum(trunc(落地湿焦,2)),sum(trunc(硅石,2)),sum(trunc(萤石,2)),sum(trunc(蛇纹石,2)),sum(trunc(其它熔剂,2))" + " from 原料消耗 where trunc(日期,'MONTH')=trunc(:rq,'MONTH') and 日期<=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.机烧", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("累计.高炉{0}.球团矿", gaolu), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("累计.高炉{0}.国内球团矿", gaolu), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("累计.高炉{0}.进口球团矿", gaolu), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("累计.高炉{0}.PB块", gaolu), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); this.SetValue(string.Format("累计.高炉{0}.纽曼块", gaolu), dr.IsDBNull(6) ? 0 : dr.GetDouble(6)); this.SetValue(string.Format("累计.高炉{0}.其它块矿", gaolu), dr.IsDBNull(7) ? 0 : dr.GetDouble(7)); this.SetValue(string.Format("累计.高炉{0}.高钛球团矿", gaolu), dr.IsDBNull(8) ? 0 : dr.GetDouble(8)); this.SetValue(string.Format("累计.高炉{0}.高品位钛球", gaolu), dr.IsDBNull(9) ? 0 : dr.GetDouble(9)); this.SetValue(string.Format("累计.高炉{0}.钛球", gaolu), dr.IsDBNull(10) ? 0 : dr.GetDouble(10)); this.SetValue(string.Format("累计.高炉{0}.锰矿", gaolu), dr.IsDBNull(11) ? 0 : dr.GetDouble(11)); this.SetValue(string.Format("累计.高炉{0}.工艺称焦炭", gaolu), dr.IsDBNull(12) ? 0 : dr.GetDouble(12)); this.SetValue(string.Format("累计.高炉{0}.煤粉总耗", gaolu), dr.IsDBNull(13) ? 0 : dr.GetDouble(13)); this.SetValue(string.Format("累计.高炉{0}.焦丁", gaolu), dr.IsDBNull(14) ? 0 : dr.GetDouble(14)); this.SetValue(string.Format("累计.高炉{0}.自产湿焦", gaolu), dr.IsDBNull(15) ? 0 : dr.GetDouble(15)); this.SetValue(string.Format("累计.高炉{0}.落地湿焦", gaolu), dr.IsDBNull(16) ? 0 : dr.GetDouble(16)); this.SetValue(string.Format("累计.高炉{0}.硅石", gaolu), dr.IsDBNull(17) ? 0 : dr.GetDouble(17)); this.SetValue(string.Format("累计.高炉{0}.萤石", gaolu), dr.IsDBNull(18) ? 0 : dr.GetDouble(18)); this.SetValue(string.Format("累计.高炉{0}.蛇纹石", gaolu), dr.IsDBNull(19) ? 0 : dr.GetDouble(19)); this.SetValue(string.Format("累计.高炉{0}.其它熔剂", gaolu), dr.IsDBNull(20) ? 0 : dr.GetDouble(20)); } } dr.Close(); // cmd.CommandText = "select gaolu,shijiaofen,fuyang,lengfengliuliang,feitie,fengwen,ludingwendu,refengyali,ludingyali,fengkoudatao,fengkouzhongtao,fengkouxiaotao,zhakoudatao,zhakouzhongtao,zhakouxiaotao,zuoliao,xuanliao,bengliao,trunc(shijiaofen*(1-全厂日消耗.P04焦粉水分/100),2) as 干焦粉 from xiaohao,全厂日消耗 where 全厂日消耗.P01日期=xiaohao.RQ and xiaohao.RQ=:rq"; cmd.CommandText = "select gaolu,sum(shijiaofen),sum(fuyang),sum(lengfengliuliang),sum(feitie),trunc(avg(fengwen),0),trunc(avg(ludingwendu),0),trunc(avg(refengyali),0),trunc(avg(ludingyali),0),sum(fengkoudatao),sum(fengkouzhongtao),sum(fengkouxiaotao),sum(zhakoudatao),sum(zhakouzhongtao),sum(zhakouxiaotao),sum(zuoliao),sum(xuanliao),sum(bengliao), round(sum(jiaoding*(100-JiaodingShuiFen)/100),2), round(sum(shijiaofen*(100-JiaoFenShuiFen)/100),2) from xiaohao where trunc(RQ,'MONTH')=trunc(:rq,'MONTH') and RQ<=:rq group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.湿焦粉", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("累计.高炉{0}.富氧量", gaolu), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); this.SetValue(string.Format("累计.高炉{0}.冷风流量", gaolu), dr.IsDBNull(3) ? 0 : dr.GetDouble(3)); this.SetValue(string.Format("累计.高炉{0}.废铁总耗", gaolu), dr.IsDBNull(4) ? 0 : dr.GetDouble(4)); this.SetValue(string.Format("累计.高炉{0}.平均风温", gaolu), dr.IsDBNull(5) ? 0 : dr.GetDouble(5)); this.SetValue(string.Format("累计.高炉{0}.炉顶温度", gaolu), dr.IsDBNull(6) ? 0 : dr.GetDouble(6)); this.SetValue(string.Format("累计.高炉{0}.热风压力", gaolu), dr.IsDBNull(7) ? 0 : dr.GetDouble(7)); this.SetValue(string.Format("累计.高炉{0}.炉顶压力", gaolu), dr.IsDBNull(8) ? 0 : dr.GetDouble(8)); this.SetValue(string.Format("累计.高炉{0}.风口损坏数大", gaolu), dr.IsDBNull(9) ? 0 : dr.GetDouble(9)); this.SetValue(string.Format("累计.高炉{0}.风口损坏数中", gaolu), dr.IsDBNull(10) ? 0 : dr.GetDouble(10)); this.SetValue(string.Format("累计.高炉{0}.风口损坏数小", gaolu), dr.IsDBNull(11) ? 0 : dr.GetDouble(11)); this.SetValue(string.Format("累计.高炉{0}.渣口损坏数大", gaolu), dr.IsDBNull(12) ? 0 : dr.GetDouble(12)); this.SetValue(string.Format("累计.高炉{0}.渣口损坏数中", gaolu), dr.IsDBNull(13) ? 0 : dr.GetDouble(13)); this.SetValue(string.Format("累计.高炉{0}.渣口损坏数小", gaolu), dr.IsDBNull(14) ? 0 : dr.GetDouble(14)); this.SetValue(string.Format("累计.高炉{0}.坐料次数", gaolu), dr.IsDBNull(15) ? 0 : dr.GetDouble(15)); this.SetValue(string.Format("累计.高炉{0}.悬料次数", gaolu), dr.IsDBNull(16) ? 0 : dr.GetDouble(16)); this.SetValue(string.Format("累计.高炉{0}.崩料次数", gaolu), dr.IsDBNull(17) ? 0 : dr.GetDouble(17)); this.SetValue(string.Format("累计.高炉{0}.干焦丁", gaolu), dr.IsDBNull(18) ? 0 : dr.GetDouble(18)); this.SetValue(string.Format("累计.高炉{0}.干焦粉", gaolu), dr.IsDBNull(19) ? 0 : dr.GetDouble(19)); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间,'MONTH')=trunc(:rq,'MONTH') and trunc(时间)<=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.休风情况", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 慢风 where trunc(时间,'MONTH')=trunc(:rq,'MONTH') and trunc(时间)<=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); this.SetValue(string.Format("累计.高炉{0}.慢风", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); } } dr.Close(); ////纽曼块 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%纽曼%' or beizhu like '%纽曼%') group by gaolu"; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.纽曼块", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); //FB块 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%PB%' or beizhu like '%PB%') group by gaolu"; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.PB块", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////钛球 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%钛球%' or beizhu like '%钛球%') group by gaolu"; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.钛球", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////锰矿 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%锰矿%' or beizhu like '%锰矿%') group by gaolu "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.锰矿", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////硅石 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%硅石%' or beizhu like '%硅石%') group by gaolu"; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.硅石", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////白云石 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%白云石%' or beizhu like '%白云石%') group by gaolu "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.白云石", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); ////蛇纹石 //cmd.CommandText = "select gaolu,sum(nvl(baiban,0)+nvl(zhongban,0)+nvl(yeban,0)) from rbxiaohao where trunc(sj,'MONTH')=trunc(:rq,'MONTH') and trunc(sj)<=:rq and (MC like '%蛇纹石%' or beizhu like '%蛇纹石%') group by gaolu "; //dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // if (!dr.IsDBNull(0)) // { // int gaolu = dr.GetInt32(0); // this.SetValue(string.Format("累计.高炉{0}.蛇纹石", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); // } //} //dr.Close(); if (rq.Day > 1) { cmd.CommandText = "select P01单位,P38干毛焦,P27入炉焦炭总耗 from 技术日报 where P日期+1=:rq and P02项目='累计'"; dr = cmd.ExecuteReader(); while (dr.Read()) { string gaolustr = dr.GetString(0); if (gaolustr.Contains("#") && gaolustr.Length > 1) { int gaolu; if (int.TryParse(gaolustr.Substring(0, gaolustr.Length - 1), out gaolu)) { this.SetValue(string.Format("累计.高炉{0}.昨日干毛焦", gaolu), dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue(string.Format("累计.高炉{0}.昨日入炉焦炭", gaolu), dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); } } else if (gaolustr == "Q") { this.SetValue("累计.昨日干毛焦", dr.IsDBNull(1) ? 0 : dr.GetDouble(1)); this.SetValue("累计.昨日入炉焦炭", dr.IsDBNull(2) ? 0 : dr.GetDouble(2)); } } } this.SetValue("累计.累计天数", rq.Day); #endregion conn.Close(); }
/// <summary> /// Ottiene tutte le comunicazioni che hanno un certo status /// </summary> /// <param name="status"></param> /// <returns></returns> internal IList <ComunicazioniType> GetComunicazioniByStatus(TipoCanale tipoCanale, List <MailStatus> status, bool include, int?minRec, int?maxRec, string utente) { List <ComunicazioniType> lComunicazioni = new List <ComunicazioniType>(); using (OracleCommand oCmd = base.CurrentConnection.CreateCommand()) { #region "old query" /* old query * oCmd.CommandText = "WITH t_flussi AS" + " (SELECT REF_ID_COM" + ", ROW_NUMBER() OVER (ORDER BY REF_ID_COM) AS RN" + " FROM (" + "SELECT DISTINCT REF_ID_COM" + " FROM COMUNICAZIONI_FLUSSO CF" + " WHERE CANALE = '" + tipoCanale.ToString() + "'" + " AND" + ((include == false) ? " NOT" : "") + " EXISTS (SELECT *" + " FROM COMUNICAZIONI_FLUSSO CF0" + " WHERE CF0.REF_ID_COM = CF.REF_ID_COM" + " AND CF0.CANALE = CF.CANALE" + " AND CF0.STATO_COMUNICAZIONE_NEW" + " IN ('" + String.Join("', '", status.Select(s => ((int)s).ToString()).ToArray()) + "'))" + " ORDER BY REF_ID_COM)) " + queryBase + " WHERE v0.com_flussi IS NOT EMPTY" + " AND v0.id_com IN (SELECT ref_id_com FROM t_flussi" + //+ " WHERE " + //+ ((minRec.HasValue) ? (" RN >= " + minRec.Value.ToString()) : " RN >= 0") + //+ ((maxRec.HasValue) ? (" AND RN <= " + maxRec.Value.ToString()) : "") + ")" + (String.IsNullOrEmpty(utente) ? "" : " AND v0.mail_com.mail_sender = '" + utente + "'"); */ #endregion StringBuilder sb = new StringBuilder("WITH t_flussi AS") .Append(" (SELECT DISTINCT IDX, ROW_NUMBER() OVER (ORDER BY IDX) AS RN") .Append(" FROM (SELECT DISTINCT mc.REF_ID_COM as IDX FROM COMUNICAZIONI_FLUSSO CF INNER JOIN MAIL_CONTENT MC") .Append(" on cf.ref_id_com = mc.ref_id_com") .Append(" WHERE mc.mail_sender = :p_sender and CANALE = :p_canale AND") .Append(include ? "" : " NOT") .Append(" EXISTS (SELECT 1") .Append(" FROM COMUNICAZIONI_FLUSSO CF0") .Append(" WHERE CF0.REF_ID_COM = CF.REF_ID_COM") .Append(" AND CF0.CANALE = CF.CANALE") .Append(" AND CF0.STATO_COMUNICAZIONE_NEW IN (") .Append(string.Format("'{0}'", string.Join("', '", status.Select(s => ((int)s).ToString()).ToArray()))) .Append(")))") .Append(" ORDER BY IDX)") .Append(" SELECT VALUE(v0)") .Append(" FROM v_comunicazioni_complete_obj v0") .Append(" WHERE v0.com_flussi IS NOT EMPTY") .Append(" AND v0.id_com IN (SELECT idx FROM t_flussi") .Append(" where RN >= :p_minRec") .Append(" AND RN <= :p_maxRec") .Append(")"); oCmd.CommandText = sb.ToString(); oCmd.BindByName = true; oCmd.Parameters.Add(new OracleParameter { Direction = System.Data.ParameterDirection.Input, OracleDbType = OracleDbType.Varchar2, ParameterName = "p_sender", Value = utente }); oCmd.Parameters.Add(new OracleParameter { Direction = System.Data.ParameterDirection.Input, OracleDbType = OracleDbType.Varchar2, ParameterName = "p_canale", Size = 20, Value = tipoCanale.ToString() }); oCmd.Parameters.Add(new OracleParameter { Direction = System.Data.ParameterDirection.Input, OracleDbType = OracleDbType.Decimal, ParameterName = "p_minRec", Precision = 10, Scale = 0, Value = ((minRec.HasValue) ? minRec.Value : 0) }); oCmd.Parameters.Add(new OracleParameter { Direction = System.Data.ParameterDirection.Input, OracleDbType = OracleDbType.Decimal, ParameterName = "p_maxRec", Value = ((maxRec.HasValue) ? maxRec.Value : int.MaxValue) }); try { using (OracleDataReader r = oCmd.ExecuteReader()) { while (r.Read()) { lComunicazioni.Add((ComunicazioniType)r.GetValue(0)); } } } catch (Exception ex) { lComunicazioni = null; //TASK: Allineamento log - Ciro if (!ex.GetType().Equals(typeof(ManagedException))) { ManagedException mEx = new ManagedException(ex.Message, "ORA_ERR013", string.Empty, string.Empty, ex.InnerException); ErrorLogInfo err = new ErrorLogInfo(mEx); log.Error(err); throw mEx; } else { throw ex; } } } return(lComunicazioni); }
public TableRowsApiResultModel GetDbTableRows(string sqlStr, List <OracleParameter> parameters) { // initiate the result TableRowsApiResultModel rows = new TableRowsApiResultModel(); // create and open the connection OracleConnection conn = new OracleConnection(ConnStr); // C# conn.Open(); try { // create the command for querying OracleCommand cmd = new OracleCommand { Connection = conn, CommandText = sqlStr, CommandType = CommandType.Text }; if (parameters.Count > 0) { cmd.BindByName = true; for (int paramIter = 0; paramIter < parameters.Count; paramIter++) { cmd.Parameters.Add(parameters[paramIter]); } } // execute command and read into an oracle data reader object OracleDataReader dr = cmd.ExecuteReader(); // populate the column names for (int i = 0; i < dr.FieldCount; i++) { string colName = dr.GetName(i); rows.TableColNames.Add(colName); string colType = dr.GetFieldType(i).Name; rows.TableColTypes.Add(colType); } // populate the rows of the query table while (dr.Read()) { object[] objs = new object[dr.FieldCount]; dr.GetValues(objs); rows.TableRows.Add(new List <object>(objs)); //Console.WriteLine(dr.GetInt32(dr.GetOrdinal("ID"))); //Console.WriteLine(dr.GetString(dr.GetOrdinal("LINE_NAME"))); } } catch (Exception e) { Console.WriteLine(e.Message); } //close the connection conn.Close(); // free the resources conn.Dispose(); // return the result return(rows); }
protected void btnUpdateProject_Click(object sender, EventArgs e) { if (Request.QueryString["id"] != null) { OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("SELECT PRO_ID FROM TB_PROJECT WHERE START_DATE BETWEEN " + Util.DatabaseToDateSearch(tbStartDate.Text) + " AND " + Util.DatabaseToDateSearch(tbEndDate.Text) + " AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "'", con)) { using (OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { if (!reader.IsDBNull(0)) { Project ProjectData = new Project(); ProjectData.Load(reader.GetInt32(0)); ChangeNotification("danger", "ไม่สามารถเพิ่มข้อมูลได้ พบวันซ้อนทับกัน (รหัสโครงการ " + ProjectData.PRO_ID + ", " + ProjectData.START_DATE.Value.ToLongDateString() + " ถึง " + ProjectData.END_DATE.Value.ToLongDateString() + ")"); return; } } } } } /*string[] validFileTypes = { "pdf" }; * string ext = System.IO.Path.GetExtension(FUdocument.PostedFile.FileName); * bool isValidFile = false; * * for (int i = 0; i < validFileTypes.Length; i++) * { * if (ext == "." + validFileTypes[i]) * { * isValidFile = true; * break; * } * } * if (!isValidFile) * { * ScriptManager.GetCurrent(this.Page).SetFocus(this.FUdocument); * ChangeNotification("danger", "กรุณาแนบไฟล์นามสกุล " + string.Join(",", validFileTypes) + " เท่านั้น"); * return; * } * * else if (FUdocument.PostedFile.ContentLength > 26214400) * { * ScriptManager.GetCurrent(this.Page).SetFocus(this.FUdocument); * ChangeNotification("danger", "กรุณาแนบไฟล์ไม่เกิน 25 MB"); * return; * } * else * { * ChangeNotification("", ""); * }*/ if (tbStartDate.Text != "" && tbEndDate.Text != "") { DateTime dtEndDate = DateTime.Parse(tbEndDate.Text); DateTime dtStartDate = DateTime.Parse(tbStartDate.Text); int totalDay = (int)(dtEndDate - dtStartDate).TotalDays + 1; if (totalDay <= 0) { notification.Attributes["class"] = "alert alert_danger"; notification.InnerHtml = ""; notification.InnerHtml += "<div> <img src='Image/Small/red_alert.png' /> วันที่เริ่มโครงการ - วันที่สิ้นสุดโครงการ : วันที่ไม่ถูกต้อง !</div>"; ScriptManager.GetCurrent(this.Page).SetFocus(this.tbStartDate); return; } else { notification.Attributes["class"] = "none"; notification.InnerHtml = ""; } } if (Util.ToDateTimeOracle(tbStartDate.Text) > DateTime.Now) { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('วันที่ไม่สามารถมากกว่าวันปัจจุบัน')", true); return; } if (Util.ToDateTimeOracle(tbEndDate.Text) > DateTime.Now) { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('วันที่ไม่สามารถมากกว่าวันปัจจุบัน')", true); return; } using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { PersonnelSystem ps = PersonnelSystem.GetPersonnelSystem(this); Person loginPerson = ps.LoginPerson; con.Open(); string query = "Update TB_PROJECT Set"; query += " CATEGORY_ID = :CATEGORY_ID ,"; query += " COUNTRY_ID = :COUNTRY_ID ,"; query += " SUB_COUNTRY_ID = :SUB_COUNTRY_ID ,"; query += " PROJECT_NAME = :PROJECT_NAME ,"; query += " ADDRESS_PROJECT = :ADDRESS_PROJECT ,"; query += " START_DATE = :START_DATE ,"; query += " END_DATE = :END_DATE ,"; query += " EXPENSES = :EXPENSES ,"; query += " FUNDING = :FUNDING ,"; query += " CERTIFICATE = :CERTIFICATE ,"; query += " SUMMARIZE_PROJECT = :SUMMARIZE_PROJECT ,"; query += " RESULT_TEACHING = :RESULT_TEACHING ,"; query += " RESULT_ACADEMIC = :RESULT_ACADEMIC ,"; query += " DIFFICULTY_PROJECT = :DIFFICULTY_PROJECT ,"; query += " RESULT_PROJECT = :RESULT_PROJECT ,"; query += " RESULT_RESEARCHING = :RESULT_RESEARCHING ,"; query += " RESULT_OTHER = :RESULT_OTHER ,"; query += " COUNSEL = :COUNSEL ,"; query += " PDF_FILE = :PDF_FILE "; query += " where PRO_ID = :PRO_ID "; using (OracleCommand com = new OracleCommand(query, con)) { com.Parameters.Add(new OracleParameter("CATEGORY_ID", Convert.ToInt32(ddlCategory.SelectedValue))); com.Parameters.Add(new OracleParameter("COUNTRY_ID", Convert.ToInt32(ddlCountry.SelectedValue))); com.Parameters.Add(new OracleParameter("SUB_COUNTRY_ID", Convert.ToInt32(ddlSubCountry.SelectedValue))); com.Parameters.Add(new OracleParameter("PROJECT_NAME", tbProjectName.Text)); com.Parameters.Add(new OracleParameter("ADDRESS_PROJECT", tbAddressProject.Text)); com.Parameters.Add(new OracleParameter("START_DATE", DateTime.Parse(tbStartDate.Text))); com.Parameters.Add(new OracleParameter("END_DATE", DateTime.Parse(tbEndDate.Text))); com.Parameters.Add(new OracleParameter("EXPENSES", Convert.ToInt32(tbExpenses.Text))); com.Parameters.Add(new OracleParameter("FUNDING", tbFunding.Text)); com.Parameters.Add(new OracleParameter("CERTIFICATE", tbCertificate.Text)); com.Parameters.Add(new OracleParameter("SUMMARIZE_PROJECT", tbSummarizeProject.Text)); com.Parameters.Add(new OracleParameter("RESULT_TEACHING", tbResultTeaching.Text)); com.Parameters.Add(new OracleParameter("RESULT_ACADEMIC", tbResultAcademic.Text)); com.Parameters.Add(new OracleParameter("DIFFICULTY_PROJECT", tbDifficultyProject.Text)); com.Parameters.Add(new OracleParameter("RESULT_PROJECT", tbResultProject.Text)); com.Parameters.Add(new OracleParameter("RESULT_RESEARCHING", tbResultResearching.Text)); com.Parameters.Add(new OracleParameter("RESULT_OTHER", tbResultOther.Text)); com.Parameters.Add(new OracleParameter("COUNSEL", tbCounsel.Text)); if (FUdocument.HasFile) { string CountBase = DatabaseManager.ExecuteString("SELECT COUNT(*) FROM TB_PROJECT WHERE CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "'"); FileInfo fi = new FileInfo(FUdocument.FileName); string imgFile = "CID=" + loginPerson.PS_CITIZEN_ID + "&count=" + CountBase + fi.Extension; FUdocument.SaveAs(Server.MapPath("Upload/Project/PDF/" + imgFile)); com.Parameters.Add(new OracleParameter("PDF_FILE", imgFile)); } else { com.Parameters.Add(new OracleParameter("PDF_FILE", DBNull.Value)); } com.Parameters.Add(new OracleParameter("PRO_ID", int.Parse(MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())))); com.ExecuteNonQuery(); } } ChangeNotification("", ""); Notsuccess.Visible = false; success.Visible = true; } }
private void toolStripButton1_Click(object sender, EventArgs e) { c1Chart1.ChartGroups[0].ChartData.SeriesList.Clear(); List <DateTime> dts = new List <DateTime>(); List <double> vs = new List <double>(); DateTime dt1 = DateTime.Now.AddDays(-1); DateTime dt2 = DateTime.Now; int gaolu = 0; switch (this.toolStrip_gaolu.Text) { case "1高炉": gaolu = 1; break; case "2高炉": gaolu = 2; break; case "3高炉": gaolu = 3; break; case "4高炉": gaolu = 4; break; case "5高炉": gaolu = 5; break; } OracleConnection conn = new OracleConnection(Properties.Settings.Default.ltznConnectionString); conn.Open(); string sql = "select zdsj,FESi from ddluci where zdsj>=:sjBegin and zdsj<=:sjEnd and gaolu=:gaolu"; OracleCommand siCmd = new OracleCommand(sql, conn); siCmd.Parameters.Add(":sjBegin", OracleType.DateTime).Value = this.sjBegin.Value; siCmd.Parameters.Add(":sjEnd", OracleType.DateTime).Value = this.sjEnd.Value; siCmd.Parameters.Add(":gaolu", OracleType.Int32).Value = gaolu; OracleDataReader dr = siCmd.ExecuteReader(); while (dr.Read()) { dts.Add(dr.GetDateTime(0)); vs.Add(dr.GetDouble(1)); } dr.Close(); conn.Close(); c1Chart1.Header.Text = this.toolStrip_gaolu.Text + " " + this.sjBegin.Text + "--" + this.sjEnd.Text + "Si(炉温)趋势"; // Add the data ChartData data = c1Chart1.ChartGroups[0].ChartData; ChartDataSeriesCollection series = data.SeriesList; //plot the student scores ChartDataSeries StuSeries = series.AddNewSeries(); StuSeries.Label = "Si(炉温)趋势"; StuSeries.LineStyle.Pattern = LinePatternEnum.Solid; StuSeries.LineStyle.Color = Color.DarkBlue; StuSeries.SymbolStyle.Shape = SymbolShapeEnum.Star; StuSeries.SymbolStyle.Color = Color.DarkRed; StuSeries.X.CopyDataIn(dts.ToArray()); StuSeries.Y.CopyDataIn(vs.ToArray()); StuSeries = null; DateTime[] ax = new DateTime[] { dts[0], dts[dts.Count - 1] }; double[] ay1 = new double[] { 0.7, 0.7 }; double[] ay2 = new double[] { 0.4, 0.4 }; StuSeries = series.AddNewSeries(); StuSeries.Label = "最大值"; StuSeries.LineStyle.Pattern = LinePatternEnum.Solid; StuSeries.LineStyle.Color = Color.Black; StuSeries.SymbolStyle.Shape = SymbolShapeEnum.None; StuSeries.SymbolStyle.Color = Color.DarkRed; StuSeries.X.CopyDataIn(ax); StuSeries.Y.CopyDataIn(ay1); StuSeries = null; StuSeries = series.AddNewSeries(); StuSeries.Label = "最小值"; StuSeries.LineStyle.Pattern = LinePatternEnum.Solid; StuSeries.LineStyle.Color = Color.Black; StuSeries.SymbolStyle.Shape = SymbolShapeEnum.None; StuSeries.SymbolStyle.Color = Color.DarkRed; StuSeries.X.CopyDataIn(ax); StuSeries.Y.CopyDataIn(ay2); StuSeries = null; // Add and show the alarm zones AlarmZonesCollection zones = c1Chart1.ChartArea.PlotArea.AlarmZones; zones.Clear(); AlarmZone zone1 = zones.AddNewZone(); AlarmZone zone2 = zones.AddNewZone(); AlarmZone zone3 = zones.AddNewZone(); zone1.Name = "大值区"; zone1.BackColor = Color.Aqua; zone1.UpperExtent = c1Chart1.ChartArea.AxisY.Max; zone1.LowerExtent = 0.7; zone1.Visible = true; zone2.Name = "正常"; zone2.BackColor = Color.Beige; zone2.UpperExtent = 0.7; zone2.LowerExtent = 0.4; zone2.Visible = true; zone3.Name = "小值区"; zone3.BackColor = Color.Aqua; zone3.UpperExtent = 0.4; zone3.LowerExtent = c1Chart1.ChartArea.AxisY.Min; zone3.Visible = true; }
public List <TIMKIEM_VATTU_DTO> TIMKIEM_DULIEU_HANGHOA_DATABASE_ORACLE(string DIEUKIENLOC, int SUDUNG_TIMKIEM_ALL, int DIEUKIENCHON, string UNITCODE) { List <TIMKIEM_VATTU_DTO> LST_TIMKIEM_VATTU_DTO = new List <TIMKIEM_VATTU_DTO>(); if (!string.IsNullOrEmpty(DIEUKIENLOC)) { using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["TBNETERP_SERVER"].ConnectionString)) { try { connection.Open(); if (connection.State == ConnectionState.Open) { OracleCommand command = new OracleCommand(); command.Connection = connection; command.CommandType = CommandType.StoredProcedure; command.CommandText = @"BANLE_TIMKIEM_BOHANG_MAHANG"; command.Parameters.Add(@"P_MADONVI", OracleDbType.NVarchar2, 50).Value = UNITCODE; command.Parameters.Add(@"P_TUKHOA", OracleDbType.NVarchar2, 50).Value = DIEUKIENLOC.ToString().ToUpper().Trim(); command.Parameters.Add(@"P_SUDUNG_TIMKIEM_ALL", OracleDbType.Int32).Value = SUDUNG_TIMKIEM_ALL; command.Parameters.Add(@"P_DIEUKIENCHON", OracleDbType.Int32).Value = DIEUKIENCHON; command.Parameters.Add(@"CURSOR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output; OracleDataReader dataReader = command.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { TIMKIEM_VATTU_DTO TIMKIEM_VATTU_DTO = new TIMKIEM_VATTU_DTO(); if (dataReader["MAVATTU"] != null) { TIMKIEM_VATTU_DTO.MAVATTU = dataReader["MAVATTU"].ToString(); } if (dataReader["MAKHAC"] != null) { TIMKIEM_VATTU_DTO.MAKHAC = dataReader["MAKHAC"].ToString(); } if (dataReader["TENVATTU"] != null) { TIMKIEM_VATTU_DTO.TENVATTU = dataReader["TENVATTU"].ToString(); } if (dataReader["MALOAIVATTU"] != null) { TIMKIEM_VATTU_DTO.MALOAIVATTU = dataReader["MALOAIVATTU"].ToString(); } if (dataReader["MANHOMVATTU"] != null) { TIMKIEM_VATTU_DTO.MANHOMVATTU = dataReader["MANHOMVATTU"].ToString(); } if (dataReader["DONVITINH"] != null) { TIMKIEM_VATTU_DTO.DONVITINH = dataReader["DONVITINH"].ToString(); } if (dataReader["MANHACUNGCAP"] != null) { TIMKIEM_VATTU_DTO.MANHACUNGCAP = dataReader["MANHACUNGCAP"].ToString(); } if (dataReader["TENNHACUNGCAP"] != null) { TIMKIEM_VATTU_DTO.TENNHACUNGCAP = dataReader["TENNHACUNGCAP"].ToString(); } if (dataReader["MAKEHANG"] != null) { TIMKIEM_VATTU_DTO.MAKEHANG = dataReader["MAKEHANG"].ToString(); } if (dataReader["GIABANLEVAT"] != null) { decimal GIABANLECOVAT = 0; decimal.TryParse(dataReader["GIABANLEVAT"].ToString(), out GIABANLECOVAT); TIMKIEM_VATTU_DTO.GIABANLECOVAT = GIABANLECOVAT; } if (dataReader["ITEMCODE"] != null) { TIMKIEM_VATTU_DTO.ITEMCODE = dataReader["ITEMCODE"].ToString(); } if (dataReader["BARCODE"] != null) { TIMKIEM_VATTU_DTO.BARCODE = dataReader["BARCODE"].ToString(); } LST_TIMKIEM_VATTU_DTO.Add(TIMKIEM_VATTU_DTO); } } } } catch (Exception ex) { WriteLogs.LogError(ex); } finally { connection.Close(); connection.Dispose(); } } } return(LST_TIMKIEM_VATTU_DTO); }
public static void Main(string[] args) { string tainted_2 = null; string tainted_3 = null; Process process = new Process(); process.StartInfo.FileName = "/bin/bash"; process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'"; process.StartInfo.UseShellExecute = false; process.StartInfo.RedirectStandardOutput = true; process.Start(); using (StreamReader reader = process.StandardOutput) { tainted_2 = reader.ReadToEnd(); process.WaitForExit(); process.Close(); } tainted_3 = tainted_2; if ((Math.Pow(4, 2) <= 42)) { {} } else if (!(Math.Pow(4, 2) <= 42)) { {} } else { StringBuilder escape = new StringBuilder(); for (int i = 0; i < tainted_2.Length; ++i) { char current = tainted_2[i]; switch (current) { case '\\': escape.Append(@"\5c"); break; case '*': escape.Append(@"\2a"); break; case '(': escape.Append(@"\28"); break; case ')': escape.Append(@"\29"); break; case '\u0000': escape.Append(@"\00"); break; case '/': escape.Append(@"\2f"); break; default: escape.Append(current); break; } } tainted_3 = escape.ToString(); } //flaw string query = "SELECT * FROM Articles WHERE id=" + tainted_3; string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password"; OracleConnection dbConnection = null; try{ dbConnection = new OracleConnection(connectionString); dbConnection.Open(); OracleCommand cmd = dbConnection.CreateCommand(); cmd.CommandText = query; OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.ToString()); } dbConnection.Close(); }catch (Exception e) { Console.WriteLine(e.ToString()); } }
public string GetDataTableArgJson(string Procedure, string para, string dbName, string dbUser, string dbPwd) { _conString = string.Format(_conString, dbName, dbUser, dbPwd); OracleConnection connection; DataTable dt; try { if (!para.Contains("*|*")) { object[] ParaIn = para.Split('|'); string exeStatement, temp = ""; dt = new DataTable(Procedure); connection = new OracleConnection(_conString); connection.Open(); OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); exeStatement = "Call " + Procedure + "(" + temp + ",:p_rtn_value)"; command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output; } else { //select data for grid without any condition exeStatement = "Call " + Procedure + "(:p_rtn_value)"; command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output; } command.CommandText = exeStatement; OracleDataReader reader = command.ExecuteReader(); dt.Load(reader); connection.Close(); connection.Dispose(); json = JsonConvert.SerializeObject(dt, Formatting.Indented); return(json); } else { //tach thanh nhieu dong string[] stringSeparators = new string[] { "*|*" }; string[] rows = para.Split(stringSeparators, StringSplitOptions.None); DataTable rs = new DataTable(Procedure); connection = new OracleConnection(_conString); connection.Open(); dt = new DataTable(Procedure); foreach (string row in rows) { string[] stringSeparators1 = new string[] { "|" }; object[] ParaIn = row.Split(stringSeparators1, StringSplitOptions.None); string exeStatement = "", temp = ""; dt = new DataTable(Procedure); try { OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); exeStatement = "Call " + Procedure + "(" + temp + ",:p_rtn_value)"; command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output; } else { //select data for grid without any condition exeStatement = "Call " + Procedure + "(:p_rtn_value)"; command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output; } command.CommandText = exeStatement; OracleDataReader reader = command.ExecuteReader(); dt.Load(reader); if (rs.Columns.Count == 0) { rs = dt.Clone(); if (rs.Columns.IndexOf("_RSTATUS_".ToLower()) == -1) { rs.Columns.Add("_RSTATUS_"); } } foreach (DataRow r in dt.Rows) { rs.Rows.Add(r.ItemArray); //rs.Rows.Add(new object[] { r.ItemArray, "OK" });// += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } } catch (Exception e) { if (rs.Columns.Count == 0) { rs = dt.Clone(); if (rs.Columns.IndexOf("_RSTATUS_".ToLower()) == -1) { rs.Columns.Add("_RSTATUS_"); } } foreach (DataRow r in dt.Rows) { rs.Rows.Add(r.ItemArray); //rs.Rows.Add(new object[] { r.ItemArray, "NOOK" });// += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } } } connection.Close(); connection.Dispose(); json = JsonConvert.SerializeObject(rs, Formatting.Indented); return(json); } } catch (Exception ex) { dt = new DataTable(Procedure); dt.Columns.Add("ERROR"); dt.Rows.Add(ex.Message); json = JsonConvert.SerializeObject(dt, Formatting.Indented); return(json); } }
public void getDataBy(DateTime rq) { rq = rq.Date; OracleConnection cn = new OracleConnection(); cn.ConnectionString = Properties.Settings.Default.ConnectionString; cn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = cn; cmd.CommandText = "select P05二号皮带,P06三号皮带,P07总返矿,自产湿焦,落地湿焦,P02自产焦水分,P03落地焦水分,P04焦粉水分,TRUNC(NVL(自产湿焦,0)*(1-NVL(P02自产焦水分,0)/100)+NVL(落地湿焦,0)*(1-NVL(P03落地焦水分,0)/100),2) as 干毛焦 from 全厂日消耗 where P01日期=:rq"; cmd.Parameters.Add(":rq", OracleType.DateTime).Value = rq; OracleDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { 二号皮带 = dr.IsDBNull(0) ? 0 : dr.GetDouble(0); 号皮带 = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); 返矿 = dr.IsDBNull(2) ? 0 : dr.GetDouble(2); //自产湿焦[5] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3); //落地湿焦[5] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4); 自产湿焦水分 = dr.IsDBNull(5) ? 0 : dr.GetDouble(5); 落地湿焦水分 = dr.IsDBNull(6) ? 0 : dr.GetDouble(6); 湿焦粉水分 = dr.IsDBNull(7) ? 0 : dr.GetDouble(7); 干毛焦[5] = dr.IsDBNull(8) ? 0 : dr.GetDouble(8); } dr.Close(); cmd.CommandText = "select 高炉,trunc(机烧矿,2),trunc(竖球,2),trunc(熟料,2),trunc(生料,2),trunc(本溪矿,2),工艺称,煤粉,焦丁,trunc(自产湿焦,2),trunc(落地湿焦,2) from 原料消耗 where 日期=:rq"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 机烧[gaolu - 1] = dr.IsDBNull(1) ? 0 : Convert.ToInt32(dr.GetDouble(1)); 竖球[gaolu - 1] = dr.IsDBNull(2) ? 0 : Convert.ToInt32(dr.GetDouble(2)); 熟料[gaolu - 1] = dr.IsDBNull(3) ? 0 : Convert.ToInt32(dr.GetDouble(3)); 生料[gaolu - 1] = dr.IsDBNull(4) ? 0 : Convert.ToInt32(dr.GetDouble(4)); 本溪矿[gaolu - 1] = dr.IsDBNull(5) ? 0 : Convert.ToInt32(dr.GetDouble(5)); 工艺称焦炭[gaolu - 1] = dr.IsDBNull(6) ? 0 : dr.GetDouble(6); 煤粉[gaolu - 1] = dr.IsDBNull(7) ? 0 : dr.GetDouble(7); 焦丁[gaolu - 1] = dr.IsDBNull(8) ? 0 : dr.GetDouble(8); 自产湿焦[gaolu - 1] = dr.IsDBNull(9) ? 0 : dr.GetDouble(9); 落地湿焦[gaolu - 1] = dr.IsDBNull(10) ? 0 : dr.GetDouble(10); } } dr.Close(); // cmd.CommandText = "select gaolu,shijiaofen,fuyang,lengfengliuliang,feitie,fengwen,ludingwendu,refengyali,ludingyali,fengkoudatao,fengkouzhongtao,fengkouxiaotao,zhakoudatao,zhakouzhongtao,zhakouxiaotao,zuoliao,xuanliao,bengliao,trunc(shijiaofen*(1-全厂日消耗.P04焦粉水分/100),2) as 干焦粉 from xiaohao,全厂日消耗 where 全厂日消耗.P01日期=xiaohao.RQ and xiaohao.RQ=:rq"; cmd.CommandText = "select gaolu,shijiaofen,fuyang,lengfengliuliang,feitie,fengwen,ludingwendu,refengyali,ludingyali,fengkoudatao,fengkouzhongtao,fengkouxiaotao,zhakoudatao,zhakouzhongtao,zhakouxiaotao,zuoliao,xuanliao,bengliao from xiaohao where RQ=:rq"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 湿焦粉[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); 富氧量[gaolu - 1] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2); 冷风流量[gaolu - 1] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3); 废铁[gaolu - 1] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4); 平均风温[gaolu - 1] = dr.IsDBNull(5) ? 0 : dr.GetDouble(5); 炉顶温度[gaolu - 1] = dr.IsDBNull(6) ? 0 : dr.GetDouble(6); 热风压力[gaolu - 1] = dr.IsDBNull(7) ? 0 : dr.GetDouble(7); 炉顶压力[gaolu - 1] = dr.IsDBNull(8) ? 0 : dr.GetDouble(8); 风口损坏数大[gaolu - 1] = dr.IsDBNull(9) ? 0 : dr.GetDouble(9); 风口损坏数中[gaolu - 1] = dr.IsDBNull(10) ? 0 : dr.GetDouble(10); 风口损坏数小[gaolu - 1] = dr.IsDBNull(11) ? 0 : dr.GetDouble(11); 渣口损坏数大[gaolu - 1] = dr.IsDBNull(12) ? 0 : dr.GetDouble(12); 渣口损坏数中[gaolu - 1] = dr.IsDBNull(13) ? 0 : dr.GetDouble(13); 渣口损坏数小[gaolu - 1] = dr.IsDBNull(14) ? 0 : dr.GetDouble(14); 坐料次数[gaolu - 1] = dr.IsDBNull(15) ? 0 : dr.GetDouble(15); 悬料次数[gaolu - 1] = dr.IsDBNull(16) ? 0 : dr.GetDouble(16); 崩料次数[gaolu - 1] = dr.IsDBNull(17) ? 0 : dr.GetDouble(17); //干焦粉[gaolu - 1]= dr.IsDBNull(18) ? 0 : dr.GetDouble(18); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间)=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 休风情况[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 慢风 where trunc(时间)=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 慢风[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); } } dr.Close(); //大中修 cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间)=:rq and (分类='大修' or 分类='中修') and 间隔>0 and 间隔 is not null group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { int gaolu = dr.IsDBNull(0) ? 6 : dr.GetInt32(0); if (gaolu < 6) { 大中修[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); } } 大中修[5] = 大中修[0] + 大中修[1] + 大中修[2] + 大中修[3] + 大中修[4]; dr.Close(); for (int i = 0; i < 5; i++) { 休风情况[5] += 休风情况[i]; 慢风[5] += 慢风[i]; 坐料次数[5] += 坐料次数[i]; 悬料次数[5] += 悬料次数[i]; 崩料次数[5] += 崩料次数[i]; 风口损坏数大[5] += 风口损坏数大[i]; 风口损坏数中[5] += 风口损坏数中[i]; 风口损坏数小[5] += 风口损坏数小[i]; 渣口损坏数大[5] += 渣口损坏数大[i]; 渣口损坏数中[5] += 渣口损坏数中[i]; 渣口损坏数小[5] += 渣口损坏数小[i]; 废铁[5] += 废铁[i]; 机烧[5] += 机烧[i]; 竖球[5] += 竖球[i]; 熟料[5] += 熟料[i]; 生料[5] += 生料[i]; 本溪矿[5] += 本溪矿[i]; 工艺称焦炭[5] += 工艺称焦炭[i]; 湿焦粉[5] += 湿焦粉[i]; 煤粉[5] += 煤粉[i]; 焦丁[5] += 焦丁[i]; 富氧量[5] += 富氧量[i]; 冷风流量[5] += 冷风流量[i]; 热风压力[5] += 热风压力[i]; 炉顶温度[5] += 炉顶温度[i]; 炉顶压力[5] += 炉顶压力[i]; 自产湿焦[5] += 自产湿焦[i]; 落地湿焦[5] += 落地湿焦[i]; // 干焦粉[5] += 干焦粉[i]; } 效炉容[0] = (((1440 * 1) - 大中修[0]) / (1440 * 1)) * Constants.volume1; 效炉容[1] = (((1440 * 1) - 大中修[1]) / (1440 * 1)) * Constants.volume2; 效炉容[2] = (((1440 * 1) - 大中修[2]) / (1440 * 1)) * Constants.volume3; 效炉容[3] = (((1440 * 1) - 大中修[3]) / (1440 * 1)) * Constants.volume4; 效炉容[4] = (((1440 * 1) - 大中修[4]) / (1440 * 1)) * Constants.volume5; 效炉容[5] = 效炉容[0] + 效炉容[1] + 效炉容[2] + 效炉容[3] + 效炉容[4]; 平均风温[5] = (平均风温[0] * Convert.ToInt32(平均风温[0] > 0) + 平均风温[1] * Convert.ToInt32(平均风温[1] > 0) + 平均风温[2] * Convert.ToInt32(平均风温[2] > 0) + 平均风温[3] * Convert.ToInt32(平均风温[3] > 0) + 平均风温[4] * Convert.ToInt32(平均风温[4] > 0)) / (Convert.ToInt32(平均风温[0] > 0) + Convert.ToInt32(平均风温[1] > 0) + Convert.ToInt32(平均风温[2] > 0) + Convert.ToInt32(平均风温[3] > 0) + Convert.ToInt32(平均风温[4] > 0)); 热风压力[5] = (热风压力[0] * Convert.ToInt32(热风压力[0] > 0) + 热风压力[1] * Convert.ToInt32(热风压力[1] > 0) + 热风压力[2] * Convert.ToInt32(热风压力[2] > 0) + 热风压力[3] * Convert.ToInt32(热风压力[3] > 0) + 热风压力[4] * Convert.ToInt32(热风压力[4] > 0)) / (Convert.ToInt32(热风压力[0] > 0) + Convert.ToInt32(热风压力[1] > 0) + Convert.ToInt32(热风压力[2] > 0) + Convert.ToInt32(热风压力[3] > 0) + Convert.ToInt32(热风压力[4] > 0)); 炉顶温度[5] = (炉顶温度[0] * Convert.ToInt32(炉顶温度[0] > 0) + 炉顶温度[1] * Convert.ToInt32(炉顶温度[1] > 0) + 炉顶温度[2] * Convert.ToInt32(炉顶温度[2] > 0) + 炉顶温度[3] * Convert.ToInt32(炉顶温度[3] > 0) + 炉顶温度[4] * Convert.ToInt32(炉顶温度[4] > 0)) / (Convert.ToInt32(炉顶温度[0] > 0) + Convert.ToInt32(炉顶温度[1] > 0) + Convert.ToInt32(炉顶温度[2] > 0) + Convert.ToInt32(炉顶温度[3] > 0) + Convert.ToInt32(炉顶温度[4] > 0)); 炉顶压力[5] = (炉顶压力[0] * Convert.ToInt32(炉顶压力[0] > 0) + 炉顶压力[1] * Convert.ToInt32(炉顶压力[1] > 0) + 炉顶压力[2] * Convert.ToInt32(炉顶压力[2] > 0) + 炉顶压力[3] * Convert.ToInt32(炉顶压力[3] > 0) + 炉顶压力[4] * Convert.ToInt32(炉顶压力[4] > 0)) / (Convert.ToInt32(炉顶压力[0] > 0) + Convert.ToInt32(炉顶压力[1] > 0) + Convert.ToInt32(炉顶压力[2] > 0) + Convert.ToInt32(炉顶压力[3] > 0) + Convert.ToInt32(炉顶压力[4] > 0)); 全厂机烧 = 二号皮带 + 号皮带 - 返矿 + ((竖球[5] + 熟料[5] + 生料[5] + 本溪矿[5]) / 0.92 - (竖球[5] + 熟料[5] + 生料[5] + 本溪矿[5])); for (int i = 0; i < 5; i++) { if (机烧[5] > 0) { 机烧[i] = double.Parse(((double)(全厂机烧 * 机烧[i] / 机烧[5])).ToString("########0")); //分配机烧 } //if (工艺称焦炭[5] > 0) //{ // 自产湿焦[i] = 自产湿焦[5] * 工艺称焦炭[i] / 工艺称焦炭[5]; // 落地湿焦[i] = 落地湿焦[5] * 工艺称焦炭[i] / 工艺称焦炭[5]; //} } 机烧[5] = 机烧[0] + 机烧[1] + 机烧[2] + 机烧[3] + 机烧[4]; //干毛焦[5] = 自产湿焦[5] * (1 - 自产湿焦水分 / 100) + 落地湿焦[5] * (1 - 落地湿焦水分 / 100); for (int i = 0; i < 5; i++) { 干毛焦[i] = 自产湿焦[i] * (1 - 自产湿焦水分 / 100) + 落地湿焦[i] * (1 - 落地湿焦水分 / 100); 入炉焦炭[i] = 干毛焦[i] - 湿焦粉[i] * (1 - 湿焦粉水分 / 100); } 干毛焦[5] = 0; 入炉焦炭[5] = 0; for (int i = 0; i < 5; i++) { 干毛焦[5] += 干毛焦[i]; 入炉焦炭[5] += 入炉焦炭[i]; } //入炉焦炭[5] = 干毛焦[5] - 湿焦粉[5] * (1 - 湿焦粉水分 / 100); //for (int i = 0; i < 5; i++) //{ // if (工艺称焦炭[5] > 0) // { // 入炉焦炭[i] = 入炉焦炭[5] * 工艺称焦炭[i] / 工艺称焦炭[5]; // } // 干毛焦[i] = 入炉焦炭[i] + 湿焦粉[i] * (1 - 湿焦粉水分 / 100); //} for (int i = 0; i < 6; i++) { 总耗[i] = 机烧[i] + 竖球[i] + 熟料[i] + 生料[i] + 本溪矿[i]; if (总耗[i] > 0) { 熟料比[i] = (机烧[i] + 竖球[i] + 熟料[i]) * 100 / 总耗[i]; } 综合焦炭[i] = 入炉焦炭[i] + 0.8 * 煤粉[i] + 0.9 * 焦丁[i]; if (冷风流量[i] > 0) { 富氧率[i] = 0.79 * 富氧量[i] * 100 / 冷风流量[i] / 24; } if (入炉焦炭[i] > 0) { 焦炭负荷[i] = 总耗[i] / 入炉焦炭[i]; } } 冶炼强度[0] = 入炉焦炭[0] / 效炉容[0]; 冶炼强度[1] = 入炉焦炭[1] / 效炉容[1]; 冶炼强度[2] = 入炉焦炭[2] / 效炉容[2]; 冶炼强度[3] = 入炉焦炭[3] / 效炉容[3]; 冶炼强度[4] = 入炉焦炭[4] / 效炉容[4]; 冶炼强度[5] = 入炉焦炭[5] / 效炉容[5]; for (int i = 0; i < 6; i++) { 总耗[i] = double.Parse(总耗[i].ToString("#######0")); 机烧[i] = double.Parse(机烧[i].ToString("#######0")); 竖球[i] = double.Parse(竖球[i].ToString("#######0")); 生料[i] = double.Parse(生料[i].ToString("#######0")); 熟料[i] = double.Parse(熟料[i].ToString("#######0")); 本溪矿[i] = double.Parse(本溪矿[i].ToString("#######0")); 自产湿焦[i] = double.Parse(自产湿焦[i].ToString("#######0")); 落地湿焦[i] = double.Parse(落地湿焦[i].ToString("#######0")); 干毛焦[i] = double.Parse(干毛焦[i].ToString("#######0.00")); 湿焦粉[i] = double.Parse(湿焦粉[i].ToString("#######0")); 入炉焦炭[i] = double.Parse(入炉焦炭[i].ToString("#######0.0")); 煤粉[i] = double.Parse(煤粉[i].ToString("#######0.0")); 焦丁[i] = double.Parse(焦丁[i].ToString("#######0.0")); 综合焦炭[i] = double.Parse(综合焦炭[i].ToString("#######0.0")); 熟料比[i] = double.Parse(熟料比[i].ToString("#######0.00")); 富氧率[i] = double.Parse(富氧率[i].ToString("#######0.00")); } //累计 //大中修 cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间,'MONTH')=trunc(:rq,'MONTH') and trunc(时间)<=trunc(:rq) and (分类='大修' or 分类='中修') and 间隔>0 and 间隔 is not null group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { int gaolu = dr.IsDBNull(0) ? 6 : dr.GetInt32(0); if (gaolu < 6) { 累计大中修[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); } } 累计大中修[5] = 累计大中修[0] + 累计大中修[1] + 累计大中修[2] + 累计大中修[3] + 累计大中修[4]; dr.Close(); cmd.CommandText = "select SUM(P05二号皮带),SUM(P06三号皮带),SUM(P07总返矿),SUM(自产湿焦),SUM(落地湿焦),TRUNC(AVG(P02自产焦水分),2),TRUNC(AVG(P03落地焦水分),2),TRUNC(AVG(P04焦粉水分),2),TRUNC(SUM(NVL(自产湿焦,0)*(1-NVL(P02自产焦水分,0)/100)+NVL(落地湿焦,0)*(1-NVL(P03落地焦水分,0)/100)),2) as 累计干毛焦 from 全厂日消耗 where trunc(P01日期,'MONTH')=trunc(:rq,'MONTH') and P01日期<=:rq"; dr = cmd.ExecuteReader(); if (dr.Read()) { 累计二号皮带 = dr.IsDBNull(0) ? 0 : dr.GetDouble(0); 累计三号皮带 = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); 累计返矿 = dr.IsDBNull(2) ? 0 : dr.GetDouble(2); //累计自产湿焦[5] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3); //累计落地湿焦[5] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4); 累计自产湿焦水分 = dr.IsDBNull(5) ? 0 : dr.GetDouble(5); 累计落地湿焦水分 = dr.IsDBNull(6) ? 0 : dr.GetDouble(6); 累计湿焦粉水分 = dr.IsDBNull(7) ? 0 : dr.GetDouble(7); 累计干毛焦[5] = dr.IsDBNull(8) ? 0 : dr.GetDouble(8); } dr.Close(); cmd.CommandText = "select 高炉,SUM(trunc(机烧矿,2)),SUM(trunc(竖球,2)),SUM(trunc(熟料,2)),SUM(trunc(生料,2)),SUM(trunc(本溪矿,2)),SUM(工艺称),SUM(煤粉),SUM(焦丁),TRUNC(SUM(自产湿焦),2),TRUNC(SUM(落地湿焦),2) from 原料消耗 where trunc(日期,'MONTH')=trunc(:rq,'MONTH') and 日期<=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 累计机烧[gaolu - 1] = dr.IsDBNull(1) ? 0 : Convert.ToInt32(dr.GetDouble(1)); 累计竖球[gaolu - 1] = dr.IsDBNull(2) ? 0 : Convert.ToInt32(dr.GetDouble(2)); 累计熟料[gaolu - 1] = dr.IsDBNull(3) ? 0 : Convert.ToInt32(dr.GetDouble(3)); 累计生料[gaolu - 1] = dr.IsDBNull(4) ? 0 : Convert.ToInt32(dr.GetDouble(4)); 累计本溪矿[gaolu - 1] = dr.IsDBNull(5) ? 0 : Convert.ToInt32(dr.GetDouble(5)); 累计工艺称焦炭[gaolu - 1] = dr.IsDBNull(6) ? 0 : dr.GetDouble(6); 累计煤粉[gaolu - 1] = dr.IsDBNull(7) ? 0 : dr.GetDouble(7); 累计焦丁[gaolu - 1] = dr.IsDBNull(8) ? 0 : dr.GetDouble(8); 累计自产湿焦[gaolu - 1] = dr.IsDBNull(9) ? 0 : dr.GetDouble(9); 累计落地湿焦[gaolu - 1] = dr.IsDBNull(10) ? 0 : dr.GetDouble(10); } } dr.Close(); cmd.CommandText = "select gaolu,sum(shijiaofen),sum(fuyang),sum(lengfengliuliang),sum(feitie),trunc(avg(fengwen),0),trunc(avg(ludingwendu),0),trunc(avg(refengyali),0),trunc(avg(ludingyali),0),sum(fengkoudatao),sum(fengkouzhongtao),sum(fengkouxiaotao),sum(zhakoudatao),sum(zhakouzhongtao),sum(zhakouxiaotao),sum(zuoliao),sum(xuanliao),sum(bengliao),trunc(sum(shijiaofen*(1-全厂日消耗.P04焦粉水分/100)),2) from xiaohao,全厂日消耗 where 全厂日消耗.P01日期=xiaohao.RQ and trunc(RQ,'MONTH')=trunc(:rq,'MONTH') and RQ<=:rq group by gaolu"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 累计湿焦粉[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); 累计富氧量[gaolu - 1] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2); 累计冷风流量[gaolu - 1] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3); 累计废铁[gaolu - 1] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4); 累计平均风温[gaolu - 1] = dr.IsDBNull(5) ? 0 : dr.GetDouble(5); 累计炉顶温度[gaolu - 1] = dr.IsDBNull(6) ? 0 : dr.GetDouble(6); 累计热风压力[gaolu - 1] = dr.IsDBNull(7) ? 0 : dr.GetDouble(7); 累计炉顶压力[gaolu - 1] = dr.IsDBNull(8) ? 0 : dr.GetDouble(8); 累计风口损坏数大[gaolu - 1] = dr.IsDBNull(9) ? 0 : dr.GetDouble(9); 累计风口损坏数中[gaolu - 1] = dr.IsDBNull(10) ? 0 : dr.GetDouble(10); 累计风口损坏数小[gaolu - 1] = dr.IsDBNull(11) ? 0 : dr.GetDouble(11); 累计渣口损坏数大[gaolu - 1] = dr.IsDBNull(12) ? 0 : dr.GetDouble(12); 累计渣口损坏数中[gaolu - 1] = dr.IsDBNull(13) ? 0 : dr.GetDouble(13); 累计渣口损坏数小[gaolu - 1] = dr.IsDBNull(14) ? 0 : dr.GetDouble(14); 累计坐料次数[gaolu - 1] = dr.IsDBNull(15) ? 0 : dr.GetDouble(15); 累计悬料次数[gaolu - 1] = dr.IsDBNull(16) ? 0 : dr.GetDouble(16); 累计崩料次数[gaolu - 1] = dr.IsDBNull(17) ? 0 : dr.GetDouble(17); 累计干焦粉[gaolu - 1] = dr.IsDBNull(18) ? 0 : dr.GetDouble(18); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 休风 where trunc(时间,'MONTH')=trunc(:rq,'MONTH') and trunc(时间)<=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 累计休风情况[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); } } dr.Close(); cmd.CommandText = "select 高炉,sum(间隔) from 慢风 where trunc(时间,'MONTH')=trunc(:rq,'MONTH') and trunc(时间)<=:rq group by 高炉"; dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { int gaolu = dr.GetInt32(0); 累计慢风[gaolu - 1] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); } } dr.Close(); for (int i = 0; i < 5; i++) { 累计休风情况[5] += 累计休风情况[i]; 累计慢风[5] += 累计慢风[i]; 累计坐料次数[5] += 累计坐料次数[i]; 累计悬料次数[5] += 累计悬料次数[i]; 累计崩料次数[5] += 累计崩料次数[i]; 累计风口损坏数大[5] += 累计风口损坏数大[i]; 累计风口损坏数中[5] += 累计风口损坏数中[i]; 累计风口损坏数小[5] += 累计风口损坏数小[i]; 累计渣口损坏数大[5] += 累计渣口损坏数大[i]; 累计渣口损坏数中[5] += 累计渣口损坏数中[i]; 累计渣口损坏数小[5] += 累计渣口损坏数小[i]; 累计废铁[5] += 累计废铁[i]; 累计机烧[5] += 累计机烧[i]; 累计竖球[5] += 累计竖球[i]; 累计熟料[5] += 累计熟料[i]; 累计生料[5] += 累计生料[i]; 累计本溪矿[5] += 累计本溪矿[i]; 累计工艺称焦炭[5] += 累计工艺称焦炭[i]; 累计湿焦粉[5] += 累计湿焦粉[i]; 累计煤粉[5] += 累计煤粉[i]; 累计焦丁[5] += 累计焦丁[i]; 累计富氧量[5] += 累计富氧量[i]; 累计冷风流量[5] += 累计冷风流量[i]; 累计热风压力[5] += 累计热风压力[i]; 累计炉顶温度[5] += 累计炉顶温度[i]; 累计炉顶压力[5] += 累计炉顶压力[i]; //累计干焦粉[5] += 累计干焦粉[i]; 累计自产湿焦[5] += 累计自产湿焦[i]; 累计落地湿焦[5] += 累计落地湿焦[i]; } 累计有效炉容[0] = (((1440 * rq.Day) - 累计大中修[0]) / (1440 * rq.Day)) * Constants.volume1 * rq.Day; 累计有效炉容[1] = (((1440 * rq.Day) - 累计大中修[1]) / (1440 * rq.Day)) * Constants.volume2 * rq.Day; 累计有效炉容[2] = (((1440 * rq.Day) - 累计大中修[2]) / (1440 * rq.Day)) * Constants.volume3 * rq.Day; 累计有效炉容[3] = (((1440 * rq.Day) - 累计大中修[3]) / (1440 * rq.Day)) * Constants.volume4 * rq.Day; 累计有效炉容[4] = (((1440 * rq.Day) - 累计大中修[4]) / (1440 * rq.Day)) * Constants.volume5 * rq.Day; 累计有效炉容[5] = 累计有效炉容[0] + 累计有效炉容[1] + 累计有效炉容[2] + 累计有效炉容[3] + 累计有效炉容[4]; 累计平均风温[5] = (累计平均风温[0] * Convert.ToInt32(累计平均风温[0] > 0) + 累计平均风温[1] * Convert.ToInt32(累计平均风温[1] > 0) + 累计平均风温[2] * Convert.ToInt32(累计平均风温[2] > 0) + 累计平均风温[3] * Convert.ToInt32(累计平均风温[3] > 0) + 累计平均风温[4] * Convert.ToInt32(累计平均风温[4] > 0)) / (Convert.ToInt32(累计平均风温[0] > 0) + Convert.ToInt32(累计平均风温[1] > 0) + Convert.ToInt32(累计平均风温[2] > 0) + Convert.ToInt32(累计平均风温[3] > 0) + Convert.ToInt32(累计平均风温[4] > 0)); 累计热风压力[5] = (累计热风压力[0] * Convert.ToInt32(累计热风压力[0] > 0) + 累计热风压力[1] * Convert.ToInt32(累计热风压力[1] > 0) + 累计热风压力[2] * Convert.ToInt32(累计热风压力[2] > 0) + 累计热风压力[3] * Convert.ToInt32(累计热风压力[3] > 0) + 累计热风压力[4] * Convert.ToInt32(累计热风压力[4] > 0)) / (Convert.ToInt32(累计热风压力[0] > 0) + Convert.ToInt32(累计热风压力[1] > 0) + Convert.ToInt32(累计热风压力[2] > 0) + Convert.ToInt32(累计热风压力[3] > 0) + Convert.ToInt32(累计热风压力[4] > 0)); 累计炉顶温度[5] = (累计炉顶温度[0] * Convert.ToInt32(累计炉顶温度[0] > 0) + 累计炉顶温度[1] * Convert.ToInt32(累计炉顶温度[1] > 0) + 累计炉顶温度[2] * Convert.ToInt32(累计炉顶温度[2] > 0) + 累计炉顶温度[3] * Convert.ToInt32(累计炉顶温度[3] > 0) + 累计炉顶温度[4] * Convert.ToInt32(累计炉顶温度[4] > 0)) / (Convert.ToInt32(累计炉顶温度[0] > 0) + Convert.ToInt32(累计炉顶温度[1] > 0) + Convert.ToInt32(累计炉顶温度[2] > 0) + Convert.ToInt32(累计炉顶温度[3] > 0) + Convert.ToInt32(累计炉顶温度[4] > 0)); 累计炉顶压力[5] = (累计炉顶压力[0] * Convert.ToInt32(累计炉顶压力[0] > 0) + 累计炉顶压力[1] * Convert.ToInt32(累计炉顶压力[1] > 0) + 累计炉顶压力[2] * Convert.ToInt32(累计炉顶压力[2] > 0) + 累计炉顶压力[3] * Convert.ToInt32(累计炉顶压力[3] > 0) + 累计炉顶压力[4] * Convert.ToInt32(累计炉顶压力[4] > 0)) / (Convert.ToInt32(累计炉顶压力[0] > 0) + Convert.ToInt32(累计炉顶压力[1] > 0) + Convert.ToInt32(累计炉顶压力[2] > 0) + Convert.ToInt32(累计炉顶压力[3] > 0) + Convert.ToInt32(累计炉顶压力[4] > 0)); // 累计平均风温[5] = (累计平均风温[0] * 350 + 累计平均风温[1] * 350 + 累计平均风温[2] * 350 + 累计平均风温[3] * 400 + 累计平均风温[4] * 420) / 1870; // 累计热风压力[5] = 累计热风压力[5] / 5; // 累计炉顶温度[5] = 累计炉顶温度[5] / 5; // 累计炉顶压力[5] = 累计炉顶压力[5] / 5; 累计全厂机烧 = 累计二号皮带 + 累计三号皮带 - 累计返矿 + ((累计竖球[5] + 累计熟料[5] + 累计生料[5] + 累计本溪矿[5]) / 0.92 - (累计竖球[5] + 累计熟料[5] + 累计生料[5] + 累计本溪矿[5])); for (int i = 0; i < 5; i++) { if (累计机烧[5] > 0) { 累计机烧[i] = double.Parse(((double)(累计全厂机烧 * 累计机烧[i] / 累计机烧[5])).ToString("########0")); //分配机烧 } if (累计工艺称焦炭[5] > 0) { 累计自产湿焦[i] = 累计自产湿焦[5] * 累计工艺称焦炭[i] / 累计工艺称焦炭[5]; 累计落地湿焦[i] = 累计落地湿焦[5] * 累计工艺称焦炭[i] / 累计工艺称焦炭[5]; } } 累计机烧[5] = 累计机烧[0] + 累计机烧[1] + 累计机烧[2] + 累计机烧[3] + 累计机烧[4]; //for (int i = 0; i < 5; i++) //{ // 累计干毛焦[i] = 累计自产湿焦[i] * (1 - 累计自产湿焦水分 / 100) + 累计落地湿焦[i] * (1 - 累计落地湿焦水分 / 100); // 累计入炉焦炭[i] = 累计干毛焦[i] - 累计湿焦粉[i] * (1 - 累计湿焦粉水分 / 100); //} //累计干毛焦[5] = 0; //累计入炉焦炭[5] = 0; //for (int i = 0; i < 5; i++) //{ // 累计干毛焦[5] += 累计干毛焦[i]; // 累计入炉焦炭[5] += 累计入炉焦炭[i]; //} if (rq.Day == 1) { for (int i = 0; i < 6; i++) { 累计干毛焦[i] = 干毛焦[i]; 累计入炉焦炭[i] = 入炉焦炭[i]; } } else { DateTime rq2 = rq.AddDays(-1); cmd.CommandText = "select P01单位,P38干毛焦,P27入炉焦炭总耗 from 技术日报 where trunc(P日期)=trunc(:rq) and P02项目='累计'"; cmd.Parameters.Add(":rq", OracleType.DateTime).Value = rq2; dr = cmd.ExecuteReader(); while (dr.Read()) { int gaolu = 7; string gaolustr = dr.GetString(0); switch (gaolustr) { case "1#": gaolu = 0; break; case "2#": gaolu = 1; break; case "3#": gaolu = 2; break; case "4#": gaolu = 3; break; case "5#": gaolu = 4; break; case "Q": gaolu = 5; break; } if (gaolu < 6) { 累计干毛焦[gaolu] = dr.IsDBNull(1) ? 0 : dr.GetDouble(1); 累计入炉焦炭[gaolu] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2); 累计干毛焦[gaolu] += 干毛焦[gaolu]; 累计入炉焦炭[gaolu] += 入炉焦炭[gaolu]; } } dr.Close(); } cn.Close(); //累计干毛焦[5] = 累计自产湿焦[5] * (1 - 累计自产湿焦水分 / 100) + 累计落地湿焦[5] * (1 - 累计落地湿焦水分 / 100); //累计入炉焦炭[5] = 累计干毛焦[5] - 累计干焦粉[5]; //for (int i = 0; i < 5; i++) //{ // if (累计工艺称焦炭[5] > 0) // { // 累计入炉焦炭[i] = 累计入炉焦炭[5] * 累计工艺称焦炭[i] / 累计工艺称焦炭[5]; // } // 累计干毛焦[i] = 累计入炉焦炭[i] + 累计湿焦粉[i] * (1 - 累计湿焦粉水分 / 100); //} for (int i = 0; i < 6; i++) { 累计总耗[i] = 累计机烧[i] + 累计竖球[i] + 累计熟料[i] + 累计生料[i] + 累计本溪矿[i]; if (累计总耗[i] > 0) { 累计熟料比[i] = (累计机烧[i] + 累计竖球[i] + 累计熟料[i]) * 100 / 累计总耗[i]; } 累计综合焦炭[i] = 累计入炉焦炭[i] + 0.8 * 累计煤粉[i] + 0.9 * 累计焦丁[i]; if (累计冷风流量[i] > 0) { 累计富氧率[i] = 0.79 * 累计富氧量[i] * 100 / 累计冷风流量[i] / 24; } if (累计入炉焦炭[i] > 0) { 累计焦炭负荷[i] = 累计总耗[i] / 累计入炉焦炭[i]; } } 累计冶炼强度[0] = 累计入炉焦炭[0] / 累计有效炉容[0]; 累计冶炼强度[1] = 累计入炉焦炭[1] / 累计有效炉容[1]; 累计冶炼强度[2] = 累计入炉焦炭[2] / 累计有效炉容[2]; 累计冶炼强度[3] = 累计入炉焦炭[3] / 累计有效炉容[3]; 累计冶炼强度[4] = 累计入炉焦炭[4] / 累计有效炉容[4]; 累计冶炼强度[5] = 累计入炉焦炭[5] / 累计有效炉容[5]; for (int i = 0; i < 6; i++) { 累计总耗[i] = double.Parse(累计总耗[i].ToString("#######0")); 累计机烧[i] = double.Parse(累计机烧[i].ToString("#######0")); 累计竖球[i] = double.Parse(累计竖球[i].ToString("#######0")); 累计生料[i] = double.Parse(累计生料[i].ToString("#######0")); 累计熟料[i] = double.Parse(累计熟料[i].ToString("#######0")); 累计本溪矿[i] = double.Parse(累计本溪矿[i].ToString("#######0")); 累计自产湿焦[i] = double.Parse(累计自产湿焦[i].ToString("#######0")); 累计落地湿焦[i] = double.Parse(累计落地湿焦[i].ToString("#######0")); 累计干毛焦[i] = double.Parse(累计干毛焦[i].ToString("#######0.00")); 累计湿焦粉[i] = double.Parse(累计湿焦粉[i].ToString("#######0")); 累计入炉焦炭[i] = double.Parse(累计入炉焦炭[i].ToString("#######0.0")); 累计煤粉[i] = double.Parse(累计煤粉[i].ToString("#######0.0")); 累计焦丁[i] = double.Parse(累计焦丁[i].ToString("#######0.0")); 累计综合焦炭[i] = double.Parse(累计综合焦炭[i].ToString("#######0.0")); 累计熟料比[i] = double.Parse(累计熟料比[i].ToString("#######0.00")); 累计富氧率[i] = double.Parse(累计富氧率[i].ToString("#######0.00")); } cn.Close(); }
public List <RecetaOrdenada> getRecetasOrdenadasByIdOrden() { cmd.CommandText = "PACKAGE_ORDEN.PR_LIST_RECTAS_ORDENS_ALL"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("CURSOR_ORDENES", OracleDbType.RefCursor).Direction = ParameterDirection.Output; List <RecetaOrdenada> recetasOrdenadas = new List <RecetaOrdenada>(); try { OracleDataReader reader = cmd.ExecuteReader(); foreach (var item in reader) { RecetaOrdenada recetaOrdenada = new RecetaOrdenada(); recetaOrdenada.IdRecetaOrdenada = reader.GetInt32(0); Receta receta = new Receta(); receta.NombreReceta = reader.GetString(1); recetaOrdenada.Cantidad = reader.GetInt32(2); Orden orden = new Orden(); orden.IdOrden = reader.GetInt32(3); receta.IdReceta = reader.GetInt32(4); recetaOrdenada.Receta = receta; recetaOrdenada.Orden = orden; recetasOrdenadas.Add(recetaOrdenada); } con.Close(); return(recetasOrdenadas); } catch (Exception ex) { RecetaOrdenada recetaOrdenada = new RecetaOrdenada(); Receta receta = new Receta(); receta.NombreReceta = ex.Message; recetaOrdenada.Cantidad = 1; recetaOrdenada.Receta = receta; recetasOrdenadas.Add(recetaOrdenada); return(recetasOrdenadas); } }
public string GetDataByJson(string Procedure, string para, int numcurr, string dbName, string dbUser, string dbPwd) { _conString = string.Format(_conString, dbName, dbUser, dbPwd); OracleConnection connection; DataTable dt; try { if (!para.Contains("*|*")) { object[] ParaIn = para.Split('|'); string exeStatement, temp = ""; dt = new DataTable(Procedure); connection = new OracleConnection(_conString); connection.Open(); OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); //exeStatement = "Call " + Procedure + "(" + temp + ",:p_cv_1)"; exeStatement = "Call " + Procedure + "(" + temp; String outparr = ""; for (int ou = 1; ou <= numcurr; ou++) { outparr += ",:p_cv_" + ou; command.Parameters.Add(":p_cv_" + ou, OracleType.Cursor).Direction = ParameterDirection.Output; } exeStatement += outparr + ")"; } else //no parram { //select data for grid without any condition exeStatement = "Call " + Procedure + "("; String outparr = ""; for (int ou = 1; ou <= numcurr; ou++) { outparr += ":p_cv_" + ou + ","; command.Parameters.Add(":p_cv_" + ou, OracleType.Cursor).Direction = ParameterDirection.Output; } outparr = outparr.Substring(0, outparr.Length - 1); exeStatement += outparr + ")"; exeStatement = "Call " + Procedure + "(:p_rtn_value)"; command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output; } command.CommandText = exeStatement; OracleDataReader reader = command.ExecuteReader(); DataSet ds = new DataSet(); do { DataTable table = new DataTable(); table.Load(reader); ds.Tables.Add(table); } while (!reader.IsClosed); connection.Close(); connection.Dispose(); json = CJson.ResultSet2JSONString(ds); return(json); } else { //tach thanh nhieu dong string[] stringSeparators = new string[] { "*|*" }; string[] rows = para.Split(stringSeparators, StringSplitOptions.None); DataTable rs = new DataTable(Procedure); connection = new OracleConnection(_conString); connection.Open(); dt = new DataTable(Procedure); foreach (string row in rows) { string[] stringSeparators1 = new string[] { "|" }; object[] ParaIn = row.Split(stringSeparators1, StringSplitOptions.None); string exeStatement = "", temp = ""; dt = new DataTable(Procedure); try { OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); exeStatement = "Call " + Procedure + "(" + temp + ",:p_cv_1)"; command.Parameters.Add(":p_cv_1", OracleType.Cursor).Direction = ParameterDirection.Output; } else { //select data for grid without any condition exeStatement = "Call " + Procedure + "(:p_cv_1)"; command.Parameters.Add(":p_cv_1", OracleType.Cursor).Direction = ParameterDirection.Output; } command.CommandText = exeStatement; OracleDataReader reader = command.ExecuteReader(); dt.Load(reader); if (rs.Columns.Count == 0) { rs = dt.Clone(); if (rs.Columns.IndexOf("_RSTATUS_".ToLower()) == -1) { rs.Columns.Add("_RSTATUS_"); } } foreach (DataRow r in dt.Rows) { rs.Rows.Add(r.ItemArray); //rs.Rows.Add(new object[] { r.ItemArray, "OK" });// += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } } catch (Exception e) { if (rs.Columns.Count == 0) { rs = dt.Clone(); if (rs.Columns.IndexOf("_RSTATUS_".ToLower()) == -1) { rs.Columns.Add("_RSTATUS_"); } } foreach (DataRow r in dt.Rows) { rs.Rows.Add(r.ItemArray); //rs.Rows.Add(new object[] { r.ItemArray, "NOOK" });// += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } } } DataSet ds = new DataSet(); ds.Tables.Add(rs); json = CJson.ResultSet2JSONString(ds); connection.Close(); connection.Dispose(); //json = JsonConvert.SerializeObject(rs, Formatting.Indented); return(json); } return(""); } catch (Exception ex) { json = CJson.ResultSet2JSONError(100, ex.Message, Procedure, para); return(json); } }
private void LoadData(string request) { NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat; nfi.NumberDecimalSeparator = "."; nfi.NumberGroupSeparator = ""; dataGridView_invoice.Rows.Clear(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = request; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); List <string[]> data = new List <string[]>(); int i = 0; int priv = -1; while (dr.Read()) { if (Convert.ToInt32(dr[0]) != priv) { if (app_for_CD.Properties.Settings.Default["Theme"].ToString() == "False") { dataGridView_invoice.ForeColor = Color.Black; } dataGridView_invoice.Rows.Add(); dataGridView_invoice.Rows[i].Cells[0].Value = dr[0] + " от " + dr[9]; dataGridView_invoice.Rows[i].Cells[1].Value = dr[2] + " от " + dr[13]; dataGridView_invoice.Rows[i].Cells[2].Value = dr[1]; dataGridView_invoice.Rows[i].Cells[3].Value = dr[10]; if (dr[11] == null || dr[11].ToString() == "") { dataGridView_invoice.Rows[i].Cells[4].Value = "-"; } else { dataGridView_invoice.Rows[i].Cells[4].Value = dr[11]; } if (dr[12].ToString() == "0") { dataGridView_invoice.Rows[i].Cells[5].Value = dr[8]; dataGridView_invoice.Rows[i].Cells[6].Value = "-"; } else { dataGridView_invoice.Rows[i].Cells[5].Value = "-"; dataGridView_invoice.Rows[i].Cells[6].Value = dr[8]; } dataGridView_invoice.Rows[i].Cells[7].Value = dr[3].ToString(); dataGridView_invoice.Rows[i].Cells[8].Value = double.Parse(dr[4].ToString()).ToString("N", nfi); if (dr[15].ToString() == "1") { dataGridView_invoice.Rows[i].Cells[9].Value = "Активный"; } else { dataGridView_invoice.Rows[i].Cells[9].Value = "Неактивный"; } if (dr[17].ToString() == "0") { dataGridView_invoice.Rows[i].Cells[11].ReadOnly = true; } if (dr[17].ToString() == "1") { dataGridView_invoice.Rows[i].Cells[11].ReadOnly = false; } if (dr[17].ToString() == "2") { dataGridView_invoice.Rows[i].Cells[11].ReadOnly = true; } dataGridView_invoice.Rows[i].Cells[10].Value = (dataGridView_invoice.Rows[i].Cells[10] as DataGridViewComboBoxCell).Items[Convert.ToInt32(dr[17])]; dataGridView_invoice.Rows[i].Cells[11].Value = double.Parse(dr[18].ToString()).ToString("N", nfi); dataGridView_invoice.Rows[i].Cells[12].Value = dr[14]; i++; } else { dataGridView_invoice.Rows[i - 1].Cells[7].Value = dataGridView_invoice.Rows[i - 1].Cells[7].Value.ToString() + '\n' + dr[3].ToString(); dataGridView_invoice.Rows[i - 1].Cells[8].Value = (dataGridView_invoice.Rows[i - 1].Cells[8].Value).ToString() + '\n' + (Convert.ToDouble(dr[4])).ToString(); } priv = Convert.ToInt32(dr[0]); for (int j = 0; j < 14; j++) { if (app_for_CD.Properties.Settings.Default["Theme"].ToString() != "False") { if (i % 2 == 0) { dataGridView_invoice.Rows[i - 1].Cells[j].Style.BackColor = Color.FromArgb(89, 89, 89); } else { dataGridView_invoice.Rows[i - 1].Cells[j].Style.BackColor = Color.FromArgb(128, 128, 128); } } } } for (int row = 0; row <= dataGridView_invoice.Rows.Count - 1; row++) { ((DataGridViewImageCell)dataGridView_invoice.Rows[row].Cells[13]).Value = Properties.Resources.change; } dr.Close(); }
private void comboBox4_TextChanged(object sender, EventArgs e) { string kodejenis = ""; search_kodejenis(comboBox4.Text, ref kodejenis); //mengisi combo box kamar yang tersedia conn.Open(); try { List <string> datakamar = new List <string>(); OracleCommand cmd = new OracleCommand("select id_kamar from kamar where kode_jenis='" + kodejenis + "'"); cmd.Connection = conn; OracleDataReader reader = cmd.ExecuteReader(); comboBox3.Items.Clear(); //tanggal masuk yang dipilih string tgl00 = dateTimePicker1.Value.ToShortDateString(); string[] pisahtgl00 = tgl00.Substring(0, 10).Split('/'); int jumlah_hari00 = 0; jumlah_hari00 = int.Parse(pisahtgl00[0]); jumlah_hari00 = jumlah_hari00 + (int.Parse(pisahtgl00[1]) * 30); jumlah_hari00 = jumlah_hari00 + (int.Parse(pisahtgl00[2]) * 365); //tanggal keluar yang dipilih string tgl01 = dateTimePicker3.Value.ToShortDateString(); string[] pisahtgl01 = tgl01.Substring(0, 10).Split('/'); int jumlah_hari01 = 0; jumlah_hari01 = int.Parse(pisahtgl01[0]); jumlah_hari01 = jumlah_hari01 + (int.Parse(pisahtgl01[1]) * 30); jumlah_hari01 = jumlah_hari01 + (int.Parse(pisahtgl01[2]) * 365); while (reader.Read()) { OracleCommand cmd1 = new OracleCommand("select tgl_msk,tgl_keluar from booking where id_kamar='" + String.Format("{0}", reader[0]) + "'"); cmd1.Connection = conn; OracleDataAdapter da = new OracleDataAdapter(cmd1); DataTable tgl = new DataTable(); da.Fill(tgl); bool cekbisa = true; foreach (DataRow x in tgl.Rows) { if (cekbisa == true) { //tanggal masuk string tgl1 = x[0].ToString(); string[] pisahtgl1 = tgl1.Substring(0, 10).Split('/'); int jumlah_hari1 = 0; jumlah_hari1 = int.Parse(pisahtgl1[0]); jumlah_hari1 = jumlah_hari1 + (int.Parse(pisahtgl1[1]) * 30); jumlah_hari1 = jumlah_hari1 + (int.Parse(pisahtgl1[2]) * 365); //tanggal keluar string tgl2 = x[1].ToString(); string[] pisahtgl2 = tgl2.Substring(0, 10).Split('/'); int jumlah_hari2 = 0; jumlah_hari2 = int.Parse(pisahtgl2[0]); jumlah_hari2 = jumlah_hari2 + (int.Parse(pisahtgl2[1]) * 30); jumlah_hari2 = jumlah_hari2 + (int.Parse(pisahtgl2[2]) * 365); if (jumlah_hari00 >= jumlah_hari1 && jumlah_hari00 <= jumlah_hari2) { cekbisa = false; } if (jumlah_hari01 >= jumlah_hari1 && jumlah_hari01 <= jumlah_hari2) { cekbisa = false; } if (jumlah_hari00 <= jumlah_hari1 && jumlah_hari01 >= jumlah_hari2) { cekbisa = false; } } } if (cekbisa) { comboBox3.Items.Add(String.Format("{0}", reader[0])); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } conn.Close(); }
private void DoExcelThings() { string old_ser, ser = ""; OracleCommand cmd1; OracleDataReader dr1; string ch_data; excelFilePath = Path.GetFullPath("invoice_t.xlsx"); openExcel(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = $"select * from registration_of_invoice where ID = {ID}"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); ch_data = ChangeFormatData(dr[9].ToString()); myExcelWorkSheet.Cells[2, "B"].Value = $"№ {ID} от {ch_data}"; ch_data = ChangeFormatData(dr[13].ToString()); myExcelWorkSheet.Cells[3, "B"].Value = $"к договору № {dr[2].ToString()} от {ch_data}"; cmd1 = con.CreateCommand(); cmd1.CommandText = $"select CRP_NM, REG_ADDR_CONT from tbcb_crp_info where CRP_CD = '{dr[1]}'"; cmd1.CommandType = CommandType.Text; dr1 = cmd1.ExecuteReader(); dr1.Read(); myExcelWorkSheet.Cells[6, "AX"].Value = dr1[0].ToString(); myExcelWorkSheet.Cells[8, "AX"].Value = dr1[1].ToString(); dr1.Close(); myExcelWorkSheet.Cells[10, "AX"].Value = $"{dr[11]}"; myExcelWorkSheet.Cells[12, "AX"].Value = "\t" + dr[8].ToString(); cmd1 = con.CreateCommand(); cmd1.CommandText = $"Select bk_acnt_no, mfo_cd from tbcb_crp_bk where crp_cd = '{dr[1]}' AND tbcb_crp_bk.USED_YN = 'Y' AND TRGT_YN = 'Y'"; cmd1.CommandType = CommandType.Text; dr1 = cmd1.ExecuteReader(); dr1.Read(); myExcelWorkSheet.Cells[14, "AX"].Value = $"{dr1[0]}"; myExcelWorkSheet.Cells[16, "AX"].Value = $"{dr1[1]}"; string num_double = dr[4].ToString(); bool flag = false; var DS = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator[0]; string val = "", frac = "";; for (int i = 0; i < num_double.Count(); i++) { if (num_double[i] != DS && flag == false) { val += num_double[i]; } else if (num_double[i] != DS && flag == true) { frac += num_double[i]; } else if (num_double[i] == DS) { flag = true; } } if (flag == false) { frac = "00"; } myExcelWorkSheet.Cells[27, "J"].Value = $"{dr[6]}"; // комментарий myExcelWorkSheet.Cells[29, "J"].Value = $"{dr[7]}"; // основание myExcelWorkSheet.Cells[37, "AE"].Value = $"{dr[14]}"; dr1.Close(); old_ser = dr[2].ToString(); flag = false; for (int i = 0; i < old_ser.Count(); i++) { if (old_ser[i] == '/') { flag = true; } else if (old_ser[i] != '/' && flag == true) { ser += old_ser[i]; } } cmd1 = con.CreateCommand(); cmd1.CommandText = $"select NDS from tbcb_cd where CD like '{ser}%' AND CD_NM = '{dr[3].ToString()}'"; cmd1.CommandType = CommandType.Text; dr1 = cmd1.ExecuteReader(); dr1.Read(); double percent = double.Parse(dr1[0].ToString()); dr1.Close(); myExcelWorkSheet.Cells[22, "AE"].Value = $"{dr[5]}"; myExcelWorkSheet.Cells[22, "D"].Value = $"{dr[3]}"; myExcelWorkSheet.Cells[22, "AI"].Value = double.Parse(dr[4].ToString()); myExcelWorkSheet.Cells[22, "BF"].Value = double.Parse(dr[4].ToString()); double sum_without_NDS; sum_without_NDS = double.Parse(dr[4].ToString()) / (1 + percent / 100); myExcelWorkSheet.Cells[22, "AO"].Value = sum_without_NDS; if (percent == 0) { myExcelWorkSheet.Cells[22, "AZ"].Value = "БЕЗ НДС"; myExcelWorkSheet.Cells[22, "AW"].Value = "БЕЗ НДС"; myExcelWorkSheet.Cells[25, "B"].Value = "Всего к оплате: " + RusNumber.Str(Int32.Parse(val)) + dr[5].ToString() + " " + frac + " тийин"; } else { myExcelWorkSheet.Cells[22, "AZ"].Value = double.Parse(dr[4].ToString()) - sum_without_NDS; myExcelWorkSheet.Cells[22, "AW"].Value = $"{percent}%"; // процент myExcelWorkSheet.Cells[25, "B"].Value = "Всего к оплате: " + RusNumber.Str(Int32.Parse(val)) + dr[5].ToString() + " " + frac + " тийин, в.т.ч. НДС: " + Math.Round(myExcelWorkSheet.Cells[22, "AZ"].Value, 2) + " " + dr[5].ToString(); } dr.Close(); myExcelApplication.Visible = true; // true will open Excel myExcelWorkSheet.PrintPreview(); myExcelApplication.Visible = false; // hides excel file when user closes preview }
private async void btnCalcular_Click(object sender, RoutedEventArgs e) { try { if (txtBuscarPostulante.Text.Length > 0) { Cone = Oracle.abrirConexion(); OracleCommand cmd = new OracleCommand("FN_LISTAR_TODO", Cone); cmd.CommandType = CommandType.StoredProcedure; List <Postulante> listaBeneficiados = new List <Postulante>(); OracleParameter rut = new OracleParameter("rut", OracleDbType.Varchar2); rut.Direction = ParameterDirection.Input; rut.Value = txtBuscarPostulante.Text; OracleParameter copia_cursor = cmd.Parameters.Add("L_BUSCAR", OracleDbType.RefCursor); // es igual a %rowtype copia_cursor.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(rut); //añade la variable de entrada cmd.ExecuteNonQuery(); OracleDataReader info_leida = ((OracleRefCursor)copia_cursor.Value).GetDataReader(); //lo parseamos a cursor, por los distintos tipo de datos que contiene while (info_leida.Read()) { //rescatamos Postulante pos = new Postulante(); pos.Rut = info_leida.GetString(0); pos.Nombre = info_leida.GetString(1); pos.Edad = info_leida.GetInt32(2); pos.PuntjEdad = info_leida.GetInt32(3); pos.CantCargas = info_leida.GetInt32(4); pos.PuntjCargas = info_leida.GetInt32(5); pos.EstadoCivil = info_leida.GetString(6); pos.PuntjCivil = info_leida.GetInt32(7); pos.PuebloIndigena = info_leida.GetString(8); pos.PuntjIndigena = info_leida.GetInt32(9); pos.MontoAhorrado = info_leida.GetString(10); pos.PuntjAhorro = info_leida.GetInt32(11); pos.Titulo = info_leida.GetString(12); pos.PuntjTitulo = info_leida.GetInt32(13); pos.Region = info_leida.GetString(14); pos.PuntjRegion = info_leida.GetInt32(15); pos.TipoVivienda = info_leida.GetString(16); pos.ValorVivienda = info_leida.GetString(17); pos.PuntjTotal = info_leida.GetInt32(18); txtAhorro.Text = info_leida.GetInt32(11).ToString(); txtCarga.Text = info_leida.GetInt32(5).ToString(); txtEdad.Text = info_leida.GetInt32(3).ToString(); txtEstadoCivil.Text = info_leida.GetInt32(7).ToString(); txtIndigena.Text = info_leida.GetInt32(9).ToString(); txtRegion.Text = info_leida.GetInt32(15).ToString(); txtTitulo.Text = info_leida.GetInt32(13).ToString(); txtTotal.Text = info_leida.GetInt32(18).ToString(); //agregamos a la lista listaBeneficiados.Add(pos); } gvListarFiltro.ItemsSource = listaBeneficiados; } else { txtAhorro.Clear(); txtCarga.Clear(); txtEdad.Clear(); txtEstadoCivil.Clear(); txtIndigena.Clear(); txtRegion.Clear(); txtTitulo.Clear(); txtTotal.Clear(); } } catch (Exception ex) { await this.ShowMessageAsync("Mensaje:", string.Format("Error al Buscar el Postulante")); } }
private void button2_Click(object sender, EventArgs e) { if (textBox1.Text == "" || textBox2.Text == "" || (textBox1.Text == "" && textBox2.Text == "")) { MessageBox.Show("Enter All the Details", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { int FLAG = 0; string STR = ""; query = "select * from booking where id ='" + textBox1.Text + "' "; cmd = new OracleCommand(query, con1); con1.Open(); RDR = cmd.ExecuteReader(); while (RDR.Read()) { STR = (string)RDR["name"]; FLAG = 1; } RDR.Close(); con1.Close(); if (FLAG == 0) { MessageBox.Show(" DATA DIDN'T MATCH. PLEASE ENTER THE DATA CORRECTLY", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { string message = "Dear Customer," + Environment.NewLine + Environment.NewLine + "Your ticket was canceled successfully." + Environment.NewLine + Environment.NewLine + "ID:" + textBox1.Text + Environment.NewLine + "Name: " + textBox2.Text + Environment.NewLine + ""; con1.Open(); query = "delete from booking where ID='" + textBox1.Text + "' and name='" + textBox2.Text + "'"; cmd = new OracleCommand(query, con1); int TEMP = cmd.ExecuteNonQuery(); if (TEMP > 0) { // Command line argument must the the SMTP host. SmtpClient client = new SmtpClient(); client.Port = 587; client.Host = "smtp.gmail.com"; client.EnableSsl = true; client.Timeout = 10000; client.DeliveryMethod = SmtpDeliveryMethod.Network; client.UseDefaultCredentials = false; client.Credentials = new System.Net.NetworkCredential("*****@*****.**", "gmailpassword"); MailMessage mm = new MailMessage("*****@*****.**", mail, "BUS RESERVATION SERVICE", message); mm.BodyEncoding = UTF8Encoding.UTF8; mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure; client.Send(mm); MessageBox.Show(" RECORD DELETED SUCESSFULLY"); textBox1.Text = ""; textBox2.Text = ""; } else { MessageBox.Show("DATA DIDN'T MATCH. DELETE OPERATION FAILED ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); } con1.Close(); } } }
public DetallePuntaje(Postulacion origen) { InitializeComponent(); try { if (txtBuscarPostulante.Text != null) { Cone = Oracle.abrirConexion(); OracleCommand cmd = new OracleCommand("FN_LISTAR_TODO", Cone); cmd.CommandType = CommandType.StoredProcedure; List <Postulante> listaBeneficiados = new List <Postulante>(); OracleParameter copia_cursor = cmd.Parameters.Add("L_TODO", OracleDbType.RefCursor); // es igual a %rowtype copia_cursor.Direction = ParameterDirection.ReturnValue; OracleParameter rut = new OracleParameter("rut", OracleDbType.Varchar2); rut.Direction = ParameterDirection.Input; rut.Value = txtBuscarPostulante.Text; cmd.ExecuteNonQuery(); OracleDataReader info_leida = ((OracleRefCursor)copia_cursor.Value).GetDataReader(); //lo parseamos a cursor, por los distintos tipo de datos que contiene while (info_leida.Read()) { //rescatamos Postulante pos = new Postulante(); pos.Rut = info_leida.GetString(0); pos.Nombre = info_leida.GetString(1); pos.Edad = info_leida.GetInt32(2); pos.PuntjEdad = info_leida.GetInt32(3); pos.CantCargas = info_leida.GetInt32(4); pos.PuntjCargas = info_leida.GetInt32(5); pos.EstadoCivil = info_leida.GetString(6); pos.PuntjCivil = info_leida.GetInt32(7); pos.PuebloIndigena = info_leida.GetString(8); pos.PuntjIndigena = info_leida.GetInt32(9); pos.MontoAhorrado = info_leida.GetString(10); pos.PuntjAhorro = info_leida.GetInt32(11); pos.Titulo = info_leida.GetString(12); pos.PuntjTitulo = info_leida.GetInt32(13); pos.Region = info_leida.GetString(14); pos.PuntjRegion = info_leida.GetInt32(15); pos.TipoVivienda = info_leida.GetString(16); pos.ValorVivienda = info_leida.GetString(17); pos.PuntjTotal = info_leida.GetInt32(18); //agregamos a la lista listaBeneficiados.Add(pos); } gvListarFiltro.ItemsSource = listaBeneficiados; txtAhorro.Text = pos.PuntjAhorro.ToString(); txtCarga.Text = pos.PuntjCargas.ToString(); txtEdad.Text = pos.PuntjEdad.ToString(); txtEstadoCivil.Text = pos.PuntjCivil.ToString(); txtIndigena.Text = pos.PuntjIndigena.ToString(); txtRegion.Text = pos.PuntjRegion.ToString(); txtTitulo.Text = pos.PuntjTitulo.ToString(); txtTotal.Text = pos.PuntjTotal.ToString(); } } catch (Exception exa) { MessageBox.Show("Error!" + exa.Message); } }
protected void Page_Load(object sender, EventArgs e) { // Declaration string APP_APPID = Session["APP_APPID"].ToString(); string sql = "SELECT * FROM APP_REPORT WHERE APP_APPID = " + APP_APPID; con.Open(); OracleCommand cmd = new OracleCommand(); cmd.CommandText = sql; cmd.Connection = con; OracleDataReader dr = null; dr = cmd.ExecuteReader(); dr.Read(); Session["acadStudMt"] = dr["APP_MATRIC"].ToString(); Session["acadStudNm"] = dr["STUD_NAME"].ToString(); Session["acadStudDoB"] = String.Format("{0:dd-MMM-yyyy}", dr["STUD_DOB"]); Session["acadStudIC"] = dr["STUD_IC"].ToString(); Session["acadStudTl"] = dr["STUD_CONTACT"].ToString(); Session["acadStudEm"] = dr["STUD_EMAIL"].ToString(); Session["acadStudReligion"] = dr["STUD_RELIGION"].ToString(); Session["acadStudCitizenship"] = dr["STUD_CITIZEN"].ToString(); Session["acadStudRace"] = dr["STUD_RACE"].ToString(); Session["acadStudAddress"] = dr["STUD_ADDRESS"].ToString(); Session["acadStudNextOfKin"] = dr["STUD_KIN"].ToString(); Session["acadStudEmergencyContact"] = dr["STUD_EMERCONT"].ToString(); Session["acadStudKinAddress"] = dr["STUD_KINADDRESS"].ToString(); Session["acadStudSs"] = "201620171"; Session["acadStudPr"] = "Bachelor Of Computer Science (Software Engineering)"; Session["acadStudFn"] = "Computing"; Session["acadStudBs"] = "5"; Session["acadStudNs"] = "8"; Session["acadStudPassport"] = "1234567890"; Session["acadStudPassportEx"] = "31/12/2020"; Session["acadStudCGPA"] = "3.98"; Session["acadStudStatus"] = "Active"; Session["acadStudGraduation"] = "2018"; Session["acadStudField"] = "-"; Session["acadProgType"] = dr["PROG_TYPES"].ToString(); Session["acadProgName"] = dr["PROG_NAME"].ToString(); Session["acadProgUniversity"] = dr["PROG_UNIVERSITY"].ToString(); Session["acadProgCountry"] = dr["PROG_COUNTRY"].ToString(); Session["acadProgStartDate"] = String.Format("{0:dd-MMM-yyyy}", dr["PROG_STARTDATE"]); Session["acadProgEndDate"] = String.Format("{0:dd-MMM-yyyy}", dr["PROG_ENDDATE"]); Session["acadReportQuan"] = dr["REP_QUAN"].ToString(); Session["acadReportQual"] = dr["REP_QUAL"].ToString(); Session["acadReportTest"] = dr["REP_TEST"].ToString(); con.Close(); // Close Connection with database if (!IsPostBack) { string sesisem = Session["acadStudSs"].ToString(); showReport(); } }
private void register_Click(object sender, EventArgs e) { if (fname.Text == "First Name" || lname.Text == "Last Name" || phone_no.Text == "Phone Number" || dept.Text == "Department" || year.Text == "Year" || username.Text == "Username" || password.Text == "Password") { MessageBox.Show("Enter All the Details "); } else if (password.Text == confirm_pass.Text) { I = 0; QUERY = "select * from sign_up where username='******' "; CON1.Open(); CMD = new OracleCommand(QUERY, CON1); RDR = CMD.ExecuteReader(); while (RDR.Read()) { if (RDR["username"] == username.Text) { I = 1; } else { I = I; } } RDR.Close(); CON1.Close(); if (I == 1) { MessageBox.Show(" username exists"); username.Text = " "; username.Focus(); } else { CON1.Open(); QUERY = "Insert into sign_up(fname,lname,phone_no,dept,year,username,password) values('" + fname.Text + "','" + lname.Text + "','" + phone_no.Text + "','" + dept.Text + "','" + year.Text + "' , '" + username.Text + "','" + password.Text + "')"; CMD = new OracleCommand(QUERY, CON1); CMD.CommandType = CommandType.Text; TEMP = CMD.ExecuteNonQuery(); if (TEMP > 0) { MessageBox.Show(" REGISTRATION SUCCESSFULL"); } else { MessageBox.Show("REGISTRATION FAILED"); } clear(); next(); } } else { MessageBox.Show("PASSWORDS DO NOT MATCH"); password.Text = ""; confirm_pass.Text = ""; password.Focus(); } }
public static void Main(string[] args) { string tainted_2 = null; string tainted_3 = null; Process process = new Process(); process.StartInfo.FileName = "/bin/bash"; process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'"; process.StartInfo.UseShellExecute = false; process.StartInfo.RedirectStandardOutput = true; process.Start(); using (StreamReader reader = process.StandardOutput) { tainted_2 = reader.ReadToEnd(); process.WaitForExit(); process.Close(); } tainted_3 = tainted_2; goto Skip_9052; Skip_9052: string pattern = @"/^[0-9]*$/"; Regex r = new Regex(pattern); Match m = r.Match(tainted_2); if (!m.Success) { tainted_3 = ""; } else { tainted_3 = tainted_2; } string query = "SELECT * FROM '" + tainted_3 + "'"; string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password"; OracleConnection dbConnection = null; try{ dbConnection = new OracleConnection(connectionString); dbConnection.Open(); OracleCommand cmd = dbConnection.CreateCommand(); cmd.CommandText = query; OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.ToString()); } dbConnection.Close(); }catch (Exception e) { Console.WriteLine(e.ToString()); } }
public GetCollectionsResponse GetCollections() { string oradb = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select distinct * from collection"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); GetCollectionsResponse response = new GetCollectionsResponse(); List <Collection> collections = new List <Collection>(); while (dr.HasRows && dr.Read()) { int id = 0; bool isPrivate; Int32.TryParse(dr[0].ToString(), out id); if (dr[3].ToString() == "0") { isPrivate = false; } else { isPrivate = true; } if (id == 0) { response.ResponseType = ResponseType.Failed; response.Message = "Could not get Collections or the list is empty"; return(response); } Collection collection = new Collection { ID = id, Title = dr[1].ToString(), Description = dr[2].ToString(), IsPrivate = isPrivate, Items = new List <Item>() // Items }; collections.Add(collection); } conn.Close(); OracleConnection conn2 = new OracleConnection(oradb); conn2.Open(); cmd.Connection = conn2; cmd.CommandText = "select * from metadata"; List <Metadata> metaList = new List <Metadata>(); OracleDataReader drMetadata = cmd.ExecuteReader(); while (drMetadata.HasRows && drMetadata.Read()) { //(id, itemid, documenttypeid, author,abstract, publisher, language,url, rights,datepublished,extra) int id, itemId, documentTypeId; Int32.TryParse(drMetadata[0].ToString(), out id); Int32.TryParse(drMetadata[1].ToString(), out itemId); Int32.TryParse(drMetadata[2].ToString(), out documentTypeId); Metadata metaData = new Metadata() { ID = id, ItemId = itemId, DocumentTypeId = documentTypeId, Author = drMetadata[3].ToString(), Abstract = drMetadata[4].ToString(), Publisher = drMetadata[5].ToString(), Language = drMetadata[6].ToString(), Url = drMetadata[7].ToString(), Rights = drMetadata[8].ToString(), Date = Convert.ToDateTime(drMetadata[9]), Extra = drMetadata[10].ToString() }; metaList.Add(metaData); } cmd.CommandText = "select distinct * from item"; OracleDataReader drItems = cmd.ExecuteReader(); List <Item> items = new List <Item>(); while (drItems.HasRows && drItems.Read()) { int id, collectionId, documentTypeId; bool isPrivate; Int32.TryParse(drItems[0].ToString(), out id); Int32.TryParse(drItems[1].ToString(), out collectionId); Int32.TryParse(drItems[2].ToString(), out documentTypeId); bool.TryParse(drItems[3].ToString(), out isPrivate); Item item = new Item { ID = id, CollectionId = collectionId, DocumentTypeId = documentTypeId, IsPrivate = isPrivate, Title = drItems[4].ToString(), Metadata = metaList.FirstOrDefault(x => x.ItemId == id) }; items.Add(item); } foreach (var collection in collections) { var itemsList = items.Where(x => x.CollectionId == collection.ID).ToList(); if (itemsList.Count > 0) { collection.Items.AddRange(itemsList); } } response.collections = collections; conn2.Dispose(); return(response); }
static void read_data_trx_table(int min_seq_id, int max_seq_id) { string[] data = new string[12]; OracleConnection conn = null; OracleTransaction transaction = null; for (int i = min_seq_id + 1; i <= max_seq_id; i++) { Console.WriteLine(i); try { conn = new OracleConnection(cs_oracle); conn.Open(); transaction = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT * FROM " + oracle_pg_trx_payment_table + " WHERE ID_PAY = " + ":min_seq_id_param"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add("min_seq_id_param", i); cmd.ExecuteNonQuery(); OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { int the_field_count = dr.FieldCount; for (int a = 0; a < dr.FieldCount; a++) { //Console.WriteLine(dr.GetValue(a)); data[a] = dr.GetValue(a).ToString(); } var cust_id = cust_no_convertion(data[3]); string merchant_bank = "INDOMARET"; try { string api_cust_detail = get_request_to_api("http://192.168.177.185:1111/api/iccapi/ap1msky!/customer/GetCustDetailByCustId/" + cust_id); dynamic JsonResult = JsonConvert.DeserializeObject(api_cust_detail); cust_id = JsonResult.Id; #region do payment to icc string the_json = get_request_to_api("http://192.168.177.185:1111/api/iccapi/ap1msky!/payment/doPaymentWithFulfillQuote/" + cust_id + "/" + data[4].ToString() + "/" + merchant_bank); dynamic parsedJson = JsonConvert.DeserializeObject(the_json); //Console.WriteLine(parsedJson); // show the json result from ICC API if (parsedJson == null) { Console.WriteLine("no answer from ICC API"); break; } else if (parsedJson != null) { Console.WriteLine("cust ID = " + data[3] + "| CUST CONVERTION = " + cust_id + " | Amount = " + data[4] + "| MERCHANT = " + merchant_bank); #region copy data to mysql mirror table do_insert_to_pg_trx_payment_mysql(data); #endregion copy data to mysql mirror table sb.Append(cust_id + '#' + data[4] + '#' + data[11] + '#' + merchant_bank + "#" + DateTime.Now.ToString("dd_M_yyyy_HH:mm:ss") + Environment.NewLine); checking_file_log(); write_info_to_log_file(); } #endregion do payment to icc } catch (Exception ex) { // Console.WriteLine("Error: {0}", ex.ToString()); #region insert data to mysql problem table do_insert_to_pg_trx_problem_mysql(data); #endregion insert data to mysql problem table sb.Append("insert to problem table#" + cust_id + '#' + data[4] + '#' + data[11] + '#' + merchant_bank + "#" + DateTime.Now.ToString("dd_M_yyyy_HH:mm:ss") + Environment.NewLine); checking_file_log(); write_info_to_log_file(); } do_update_max_row_id_to_table(i.ToString()); #region update paid on master table int c = count_cust_new_in_master_table_oracle(data[3]); //if (c == 0) // insert_data_on_master_table_payment_oracle_using_api(data); //else if (c > 0) // update_data_on_master_table_payment_oracle_using_api(data); update_data_on_master_table_payment_oracle_using_api(data); #endregion update paid on master table } } catch (OracleException ex) { try { transaction.Rollback(); } catch (OracleException ex1) { Console.WriteLine("Error: {0}", ex1.ToString()); sb.Append("Error: {0}" + ex1.ToString() + "#catch (OracleException ex1)" + Environment.NewLine); checking_file_log(); write_info_to_log_file(); } Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (conn != null) { conn.Close(); } } sb.Append(Environment.NewLine + Environment.NewLine); checking_file_log(); write_info_to_log_file(); //Console.WriteLine("paused 5 minutes"); //Thread.Sleep(2000); } }
protected void btnCrear_Click(object sender, EventArgs e) { conexion.Open(); //VERIFICANDO QUE LOS PARAMETROS NO VENGAN EN VACIO if (!(lblNombre.Text == "" || lblApellido.Text == "" || lblRut.Text == "" || lblDireccion.Text == "" || lblNumero.Text == "" || lblCorreo.Text == "" || txtSueldo.Text == "" || lblContrasena.Text == "" || lblpass2.Text == "")) { //VERIFICANDO QUE CIERTOS PARAMETROS SEAN TENGAN UN MINIMO PARA INGRESARSE if (lblContrasena.Text.Length < 5 || lblNombre.Text.Length < 4 || lblRut.Text.Length <= 8 & lblRut.Text.Length >= 10 || txtSueldo.Text.Length <= 4 || lblApellido.Text.Length < 4 || validarEmail(lblCorreo.Text) == false || lblNumero.Text.Length <= 8 & lblNumero.Text.Length >= 10) { Response.Write("<script>alert('algunos datos tienen que ser redefinidos');</script>"); } else { if (cboSituacion.SelectedIndex == 0) { Response.Write("<script>alert('DEBE SELECCIONAR UNA AFP');</script>"); } else { OracleCommand codigo = new OracleCommand(); OracleConnection conectanos = new OracleConnection(); codigo.Connection = conexion; //CREANDO QUERYS PARA VERIFICAR SI ESTAN DENTRO DE LA BASE DE DATOS codigo.CommandText = ("SELECT * FROM CLIENTE WHERE RUT_CLIENTE = '" + lblRut.Text + "' OR MAIL_CLIENTE = '" + lblCorreo.Text + "' "); OracleDataReader leer = codigo.ExecuteReader(); if (leer.Read())//SI EL COMANDO LEE QUE ESTAN DENTRO ARROJA MENSAJE DE ERROR { Response.Write("<script>alert('CORREO O RUT YA INGRESADO');</script>"); } else { int situacion = Int32.Parse(cboSituacion.SelectedValue); //INSERTANDO LOS DATOS VERIFICADOS DENTRO DE LA BASE DE DATOS // String queryUsuario = " INSERT INTO CLIENTE( NOM_CLIENTE, APE_CLIENTE, RUT_CLIENTE, DIRE_CLIENTE, FONO_CLIENTE, MAIL_CLIENTE, PASSWORD_CLIENTE, ID_SIT_ECO, SUELDO_CLIENTE) VALUES('" + lblNombre.Text + "','" + lblApellido.Text + "','" + lblRut.Text + "','" + lblDireccion.Text + "','" + lblNumero.Text + "','" + lblCorreo.Text + "','" + lblContrasena.Text + "','" + cboSituacion.SelectedValue + "','"+txtSueldo.Text+ "')"; String queryUsuario = "INSERT INTO CLIENTE(NOM_CLIENTE, APE_CLIENTE,RUT_CLIENTE,DIRE_CLIENTE,FONO_CLIENTE,MAIL_CLIENTE,PASSWORD_CLIENTE,ID_SIT_ECO,SUELDO_CLIENTE) VALUES('" + lblNombre.Text + "','" + lblApellido.Text + "','" + lblRut.Text + "','" + lblDireccion.Text + "','" + lblNumero.Text + "','" + lblCorreo.Text + "','" + lblContrasena.Text + "','" + situacion + "','" + txtSueldo.Text + "')"; EjecutarQuery(queryUsuario); conexion.Close(); //--------------------------------------- //ABANDONANDO LA SESION PARA NO ENTREGAR PARAMETROS ESCRITOS Session.Abandon(); //BORRANDO EL CACHE DE LA SESION....CREO //Response.Cache.SetCacheability(HttpCacheability.NoCache); //Response.Cache.SetExpires(DateTime.UtcNow.AddHours(-1));- //Response.Cache.SetNoStore(); Response.Redirect("login.aspx"); } } } } else { Response.Write("los parametros tienen que estar rellenados en su totalidad"); } }