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);
}
}