private void read_param() { try { param = new STValidDataParam(); param.dtbegin = new DateTime(dateTimePickerDB.Value.Year, dateTimePickerDB.Value.Month, dateTimePickerDB.Value.Day, 0, 0, 0, 0); param.dtend = new DateTime(dateTimePickerDE.Value.Year, dateTimePickerDE.Value.Month, dateTimePickerDE.Value.Day, 23, 59, 59, 0); if (radioButton1.Checked == true) { param.orientation = 1; } if (radioButton2.Checked == true) { param.orientation = 2; } string name = comboBoxApp.Text.Trim(); foreach (MA s in config.madata) { if (s.Name == name) { param.app_code = s.Code; } } param.b = false; if (checkBoxVO.Checked == true) { param.b = true; } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.Source); } }
// Drive&Pay public List <STValidData> GetData(STValidDataParam viewparam) { List <STValidData> list_data = new List <STValidData>(); try { // if (viewparam.type == 2) list_data = GetData1(viewparam); // if (viewparam.type == 1) list_data = GetData2(viewparam); // if (viewparam.type == 3) list_data = GetData3(viewparam); } catch (Exception ex) { log.LogLine(ex.Message); } return(list_data); }
public int GetTable(STValidDataParam param, out DataTable table, out string msg) { int ret = 0; table = new DataTable(); msg = null; string query = null; try { // if we don't get query - return if (get_query(param.orientation, param.app_code, param.b, out query, out msg) != 0) { return(1); } using (OracleConnection connect = new OracleConnection(config.connectionstring)) { connect.Open(); if (connect.State != ConnectionState.Open) { msg = "no connection to DB"; return(2); } using (OracleCommand cmd = new OracleCommand(query, connect)) { cmd.Parameters.Add(crp(OracleType.DateTime, param.dtbegin, "1", false)); cmd.Parameters.Add(crp(OracleType.DateTime, param.dtend, "2", false)); cmd.CommandTimeout = 250; OracleDataReader reader = cmd.ExecuteReader(); OracleDataAdapter da = new OracleDataAdapter(cmd); da.Fill(table); } } } catch (Exception ex) { log.LogLine(ex.Message); msg = ex.Message; return(-1); } return(ret); }
public List <STValidData> GetData1(STValidDataParam viewparam) { List <STValidData> list_data = new List <STValidData>(); STValidData item; OracleConnection connet = new OracleConnection(); try { connet = new OracleConnection(config.connectionstring); connet.Open(); if (connet.State != ConnectionState.Open) { log.LogLine(string.Format("No connection to DB Oracle. CS: {0}", config.connectionstring)); return(list_data); } string query = string.Format("select RRN, EMTCODEFRM, AZSCODE ,DOCNUMBER, DOCDATE ,S_DIIS,SUMM1,Comments FROM (select T1.RRN, T1.EMTCODEFRM, T1.AZSCODE, T1.DOCNUMBER, " + "T1.DOCDATE, T1.S_DIIS, case when T2.AMOUNT>0 then ROUND(T2.AMOUNT,2) else 0 end AS SUMM1, " + "case when T2.AMOUNT is null then 'не найден RRN в WebPay' else null end AS Comments FROM ( SELECT RRN, EMTCODEFRM, AZSCODE, DOCNUMBER, DOCDATE, sum(S_DIIS) AS " + "S_DIIS FROM Rcd.VALID_RCPDATA WHERE EMTCODETO=300 AND SHIFTDATE>=:1 AND SHIFTDATE<=:2 GROUP BY RRN, EMTCODEFRM, AZSCODE, DOCNUMBER, DOCDATE ) T1 LEFT JOIN " + "Rcd.VALID_WEBPAYDATA T2 ON T1.RRN=T2.RRN ) WHERE S_DIIS<>SUMM1"); OracleCommand cmd = new OracleCommand(query, connet); cmd.Parameters.Add(crp(OracleType.DateTime, viewparam.dtbegin, "1", false)); cmd.Parameters.Add(crp(OracleType.DateTime, viewparam.dtend, "2", false)); OracleDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { item = new STValidData(); if (!reader.IsDBNull(0)) { item.RRN = reader.GetString(0); } if (!reader.IsDBNull(1)) { item.EMTCODEFRM = reader.GetInt32(1); } if (!reader.IsDBNull(2)) { item.AZSCODE = reader.GetInt32(2); } if (!reader.IsDBNull(3)) { item.DOCNUMBER = reader.GetInt32(3); } if (!reader.IsDBNull(4)) { item.DOCDATE = reader.GetDateTime(4); } if (!reader.IsDBNull(5)) { item.S_DIIS = reader.GetDouble(5); } if (!reader.IsDBNull(6)) { item.amount = reader.GetDouble(6); } if (!reader.IsDBNull(7)) { item.comments = reader.GetString(7); } list_data.Add(item); } } reader.Dispose(); } catch (Exception ex) { log.LogLine(ex.Message); if (connet.State == ConnectionState.Open) { connet.Close(); } } return(list_data); }
// Drive&Pay public List <STValidData> GetData2(STValidDataParam viewparam) { List <STValidData> list_data = new List <STValidData>(); STValidData item; OracleConnection connet = new OracleConnection(); try { connet = new OracleConnection(config.connectionstring); connet.Open(); if (connet.State != ConnectionState.Open) { log.LogLine(string.Format("No connection to DB Oracle. CS: {0}", config.connectionstring)); return(list_data); } string query = string.Format("select T3.RRN, T3.EMTCODEFRM, T3.AZSCODE, T3.DOCNUMBER, T3.DOCDATE, T3.S_DIIS, T4.RRN, ROUND(T4.AMOUNT,2) FROM Rcd.VALID_RCPDATA T3 " + "LEFT JOIN Rcd.VALID_WEBPAYDATA T4 ON T3.RRN=T4.RRN where T3.RRN in ( select RRN FROM ( select RRN, count(*) AS CNT FROM " + "( SELECT RRN, EMTCODEFRM, AZSCODE, DOCNUMBER, DOCDATE, sum(S_DIIS) AS S_DIIS FROM Rcd.VALID_RCPDATA WHERE EMTCODETO=300 AND SHIFTDATE>=:1 AND SHIFTDATE<=:2 " + "GROUP BY RRN, EMTCODEFRM, AZSCODE, DOCNUMBER, DOCDATE) T1 GROUP BY RRN ) T2 WHERE CNT>1 ) order by T3.RRN"); OracleCommand cmd = new OracleCommand(query, connet); cmd.Parameters.Add(crp(OracleType.DateTime, viewparam.dtbegin, "1", false)); cmd.Parameters.Add(crp(OracleType.DateTime, viewparam.dtend, "2", false)); OracleDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { item = new STValidData(); if (!reader.IsDBNull(0)) { item.RRN = reader.GetString(0); } if (!reader.IsDBNull(1)) { item.EMTCODEFRM = reader.GetInt32(1); } if (!reader.IsDBNull(2)) { item.AZSCODE = reader.GetInt32(2); } if (!reader.IsDBNull(3)) { item.DOCNUMBER = reader.GetInt32(3); } if (!reader.IsDBNull(4)) { item.DOCDATE = reader.GetDateTime(4); } if (!reader.IsDBNull(5)) { item.S_DIIS = reader.GetDouble(5); } if (!reader.IsDBNull(6)) { item.rrn = reader.GetString(6); } if (!reader.IsDBNull(7)) { item.amount = reader.GetDouble(7); } list_data.Add(item); } } reader.Dispose(); } catch (Exception ex) { log.LogLine(ex.Message); if (connet.State == ConnectionState.Open) { connet.Close(); } } return(list_data); }