static void Main(string[] args) { Member usr = new Member(); var strODBConn = ConfigurationManager.ConnectionStrings["ODBConn"].ToString(); // iseires ODBC COnnection OdbcConnection connIS = new OdbcConnection(strODBConn); DataSet ds = new DataSet(); DataSet dserrors = new DataSet(); try { cIProduction.IseriesErrors.TableBuild(dserrors); //.TableBuild(dserrors); Logger oLogger = new Logger(); oLogger.createLogFile("Process Started, days look back=" + usr.daysback, usr.PGMNM, usr.logpath); connIS.Open(); string sqlsv = usr.sqlserver;; SqlConnection DOGGConn = new SqlConnection(@"Data Source=" + sqlsv + "; Initial Catalog = " + usr.outdb + " ; Integrated Security=true"); DOGGConn.Open(); IseriesErrors.AddRowM4(dserrors, "-", "-", DateTime.Today, "Starting:" + usr.pgmname, "-", usr.pgmname, usr.logpath); int daysback = Convert.ToInt32(usr.daysback); TimeSpan daterange = new TimeSpan(daysback, 0, 0, 0, 0); //days back to load DateTime dtBackDate = DateTime.Today.Subtract(daterange); string stBackDate = dtBackDate.ToString("MM/dd/yy"); string strYYYYMM = (dtBackDate.Year * 100 + dtBackDate.Month).ToString(); DateTime dtfldBackDate = DateTime.Today.Subtract(daterange); //date format must by '2013-01-01 00:00:00' System.DateTime.Today.ToString("MM/dd/yy"); // all Production tables string strSelectProduction = "select * from DailyProductionTransfer where ProductionDate >= '" + stBackDate + "' and TransferDirection = 1"; // only select DOGG entered tickets SqlCommand findTProductions = new SqlCommand(strSelectProduction, DOGGConn); SqlDataAdapter ProductionTable = new SqlDataAdapter(strSelectProduction, DOGGConn); ProductionTable.Fill(ds, "DOGGProduction"); cIProduction.Iproduction.write(ds, usr, dserrors, connIS); //actual write of tickets to Isereis IseriesErrors.Write(dserrors, usr.ilibfil, connIS, usr.pgmname, usr.logpath); ds.Dispose(); dserrors.Dispose(); if (usr.delDOGG == "yes") { oLogger.logErrorToLog("Production Deleted from DOGG:" + ds.Tables[0].Rows.Count.ToString(), usr.PGMNM, usr.logpath); SqlCommand dltTickets = new SqlCommand("delete from DailyProductionTransfer where TransferDirection = 1"); dltTickets.ExecuteNonQuery(); } DOGGConn.Close(); DOGGConn.Dispose(); connIS.Close(); connIS.Dispose(); oLogger.logErrorToLog("day Production Read from DOGG:" + ds.Tables[0].Rows.Count.ToString(), usr.PGMNM, usr.logpath); } catch (Exception DBserror) { Logger oLogger = new Logger(); oLogger.logErrorToLog(DBserror.Message, usr.pgmname, usr.logpath); IseriesErrors.Write(dserrors, usr.ilibfil, connIS, usr.pgmname, usr.logpath); connIS.Close(); connIS.Dispose(); } }
public static void write(DataSet ds, Program.Member usr, DataSet dserrors, OdbcConnection connIS) { Logger oLogger = new Logger(); Console.WriteLine("Writting Production to Iseries PDDPDPF"); SqlDataAdapter adapterTest = new SqlDataAdapter(); String cmdinsertProduction = "insert into " + usr.idatalib + ".PDDPDPF " + //DOGG PRODUCTION FILE " (" + // "PDLEAS," + "PDRPYY," + "PDRPMM," + "PDRPDD," + "PDOBBL, " + //5 "PDGMCF, " + //6 "PDWATR," + "TTLTK," + //8 "OILMTR," + "PDTUBP," + "PDCASP," + "PDCHOK," + "PDSPM," + "PDRMKS," + "BATT," + "COM, " + "SYS" + //17 " )" + " values(" + //must insert in order of fields "?," + "?," + "?," + "?," + "?," + "?, " + "?," + "?," + "?," + "?," + "?," + "?," + "?," + "?," + "?," + "?," + "?" + //17 ")"; try { OdbcCommand dltIprod = new OdbcCommand("delete from " + usr.outtable, connIS); dltIprod.ExecuteNonQuery(); int WriteCount = 0; OdbcCommand writeProduction = new OdbcCommand(cmdinsertProduction, connIS); foreach (DataTable DOGGProduction in ds.Tables) // only one table, but as an example for reading all tables in ds { //must insert in order of fields foreach (DataRow ProdRow in DOGGProduction.Rows) { //format each column of DOGG for Iseries try { writeProduction.Parameters.Clear(); writeProduction.Parameters.AddWithValue("@PDLEAS", ProdRow.Field <string>("LeaseNumber")); int runyy = DateIBM.MStoIBMDateYY(Convert.ToDateTime((ProdRow["ProductionDate"].ToString()))); int runmm = DateIBM.MStoIBMDateMM(Convert.ToDateTime((ProdRow["ProductionDate"].ToString()))); int rundd = DateIBM.MStoIBMDateDD(Convert.ToDateTime((ProdRow["ProductionDate"].ToString()))); int PRRPDT = runmm * 10000 + rundd * 100 + runyy; writeProduction.Parameters.AddWithValue("@PDRPYY", runyy); writeProduction.Parameters.AddWithValue("@PDRPMM", runmm); writeProduction.Parameters.AddWithValue("@PDRPDD", rundd); writeProduction.Parameters.AddWithValue("@PDOBBL", Convert.ToDecimal((ProdRow["OilProduction"].ToString()))); // writeProduction.Parameters.AddWithValue("@PDGMCF", Convert.ToDecimal((ProdRow["GasProduction"].ToString()))); //gas writeProduction.Parameters.AddWithValue("@PDWATR", Convert.ToDecimal((ProdRow["WaterProduction"].ToString()))); // writeProduction.Parameters.AddWithValue("@TTLTK", Convert.ToDecimal((ProdRow["OilSales"].ToString()))); //sales writeProduction.Parameters.AddWithValue("@OILMTR", Convert.ToDecimal(0.0)); //18 writeProduction.Parameters.AddWithValue("@PDTUBP", Convert.ToDecimal((ProdRow["TubingPressure"].ToString()))); writeProduction.Parameters.AddWithValue("@PDCASP", Convert.ToDecimal((ProdRow["CasingPressure"].ToString()))); //20 double ChokeDouble = 0.0d; bool result = Double.TryParse(ProdRow["ChokeSize"].ToString(), out ChokeDouble); decimal ChokeDecimal = 0.0m; if (result) { ChokeDecimal = Convert.ToDecimal(ChokeDouble); } writeProduction.Parameters.AddWithValue("@PDCHOK", ChokeDecimal); //writeProduction.Parameters.AddWithValue("@PDSPM", 0.0m); decimal strokesper = Convert.ToDecimal((ProdRow["StrokesPerMinute"].ToString())); strokesper = Math.Round(strokesper, 1, MidpointRounding.AwayFromZero); // Rounds "up" writeProduction.Parameters.AddWithValue("@PDSPM", strokesper); if (ProdRow["Remarks"].ToString().Length < 1 || ProdRow["Remarks"].ToString().Length > 50) { writeProduction.Parameters.AddWithValue("@PDRMKS", " "); //1 } else { writeProduction.Parameters.AddWithValue("@PDRMKS", ProdRow["Remarks"].ToString()); // } writeProduction.Parameters.AddWithValue("@BATT", ProdRow.Field <string>("LeaseNumber")); writeProduction.Parameters.AddWithValue("@COM", " "); //16 writeProduction.Parameters.AddWithValue("@SYS", "DOGG"); writeProduction.ExecuteNonQuery(); WriteCount += 1; } catch (Exception DBserror) { oLogger.logErrorToLog(DBserror.Message, usr.pgmname, usr.logpath); // Lease tank date ticket IseriesErrors.AddRowM4(dserrors, ProdRow["LeaseNumber"].ToString(), "No Tank", (Convert.ToDateTime((ProdRow["ProductionDate"].ToString()))), DBserror.ToString(), "Insert Err:", usr.pgmname, usr.logpath); } } } writeProduction.Dispose(); oLogger.logErrorToLog("NAR Wrote Iprod:" + WriteCount.ToString(), usr.pgmname, usr.logpath); } catch (Exception DBserror) { oLogger.logErrorToLog(DBserror.Message, usr.pgmname, usr.logpath); } }