public static void AddRowM4(DataSet dserrors, string AuxID, string Tank, DateTime proddate, string msg, string ticket, string pgmname, string logpath)
{ // build row to added to table "ErrRow" for errors in pder4pf for DOGG
Logger oLogger = new Logger();
try
{
DataRow newErrRow = dserrors.Tables["ErrRow"].NewRow();
newErrRow["PGMNM"] = pgmname;
newErrRow["MESSAG"] = msg;
newErrRow["DATMSG"] = DateIBM.MStoIBMDate(proddate);
newErrRow["PRLEAS"] = AuxID;
newErrRow["PRTKYY"] = DateIBM.MStoIBMDateYY(proddate);
newErrRow["PRTKMM"] = DateIBM.MStoIBMDateMM(proddate);;
newErrRow["PRTKDD"] = DateIBM.MStoIBMDateDD(proddate);
newErrRow["PRTANK"] = Tank;
newErrRow["DIFDAY"] = 0;
newErrRow["YMDCHG"] = DateIBM.MStoIBMDate(proddate);
newErrRow["BOPD"] = 0.0M;
newErrRow["MCFD"] = 0.0M;
newErrRow["RUNTKT"] = ticket;
dserrors.Tables["ErrRow"].Rows.Add(newErrRow);
}
catch (Exception Ex)
{
oLogger.logErrorToLog("ERROR AddRowM4 add:" + Ex.Message, pgmname, logpath);
throw (Ex);
}
}
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);
}
}
public static void Write(DataSet dserrors, string ilibfil, OdbcConnection IseriesConn, string pgmname, string logpath)
{ // write errors to Iseries pass in data set dserrors
Console.WriteLine("Writting IseriesErrors ");
try
{
DataTable dtErrors = dserrors.Tables["ErrRow"];
// ?clear error file on iseries PDERxPF
Logger oLogger = new Logger();
// note csDComp matches the PGMNAM below
string strDelete = "Delete from " + ilibfil + " where PGMNM= " + pgmname;
OdbcCommand cmdDeleteMSG = new OdbcCommand(strDelete, IseriesConn);
String strcmdInsert = "insert into " + ilibfil +
" (" +
" PGMNM, " + // 1
" MESSAG, " +
" MSGDAT, " +
" PRLEAS, " +
" PRTKYY, " + //5
" PRTKMM, " + //6
" PRTKDD, " +
" PRTANK," +
"DIFDAY," +
"YMDCHG," + //10
"BOPD," +
"MCFD," +
"RUNTKT," +
"BATT," +
"PUID" + //15
")" +
" values( " +
"?," +
"?," +
"?," +
"?," +
"?," + //5
"?," +
"?," +
"?," +
"?," +
"?," + //10
"?," +
"?," +
"?," +
"?," +
"?" + //15
")";
OdbcCommand insx = new OdbcCommand(strcmdInsert, IseriesConn);
var Error = from Errorrow in dtErrors.AsEnumerable()
select new // layout of file PDERxPF
{
pgm = Errorrow["PGMNM"].ToString(),
Comments = Errorrow["MESSAG"].ToString(),
Date = (decimal)Errorrow["DATMSG"],
AuxID = Errorrow["PRLEAS"].ToString(),
yr = (decimal)Errorrow["PRTKYY"],
mo = (decimal)Errorrow["PRTKMM"],
da = (decimal)Errorrow["PRTKDD"],
Tank = Errorrow["PRTANK"].ToString(),
Tickt = Errorrow["PRTCKT"].ToString()
};
int GaugeNumsCount = 0;
foreach (var ErrRow in Error)
{ // iseries insert into PDER4PF
GaugeNumsCount = GaugeNumsCount + 1;
int intYear = (int)ErrRow.yr;
int intMonth = (int)ErrRow.mo;
int intDay = (int)ErrRow.da;
// build data for insert
insx.Parameters.Clear();
insx.Parameters.AddWithValue("PGMNAM", ErrRow.pgm);// pgmname);//1
int len = ErrRow.Comments.Length;
if (len > 34)
{
len = 34;
}
string errmsg = "ERR-" + ErrRow.Comments.Substring(0, len);
if ((errmsg.Length > 1))
{
insx.Parameters.AddWithValue("MESSAG", errmsg);
}
else
{
insx.Parameters.AddWithValue("MESSAG", " "); //2
}
insx.Parameters.AddWithValue("MSGDAT", ErrRow.Date);
insx.Parameters.AddWithValue("PRLEAS", ErrRow.AuxID);
insx.Parameters.AddWithValue("PRTKYY", intYear);
insx.Parameters.AddWithValue("PRTKMM", intMonth); //6
insx.Parameters.AddWithValue("PRTKDD", intDay);
insx.Parameters.AddWithValue("PRTANK", ErrRow.Tank);
insx.Parameters.AddWithValue("DIFDAY", 0.0M);
insx.Parameters.AddWithValue("YMDCHG", DateIBM.YYYYMMDD(intYear, intMonth, intDay));//10
insx.Parameters.AddWithValue("BOPD", 0.0M);
insx.Parameters.AddWithValue("MCFD", 0.0M);
insx.Parameters.AddWithValue("RUNTKT", ErrRow.Tickt);
insx.Parameters.AddWithValue("OILADJ", 0.0m);//not in error table
insx.Parameters.AddWithValue("BATT", " ");
insx.Parameters.AddWithValue("PUID", " ");
string tnk = ErrRow.Tank;
try
{ // insert errors are logged but do not stop process
insx.ExecuteNonQuery();
}
catch (Exception Ex)
{
oLogger.logErrorToLog("ERROR Write to Iseries ErrRow:" +
ErrRow + "." + Ex.Message, pgmname, logpath);
}
}
}
catch (Exception Ex)
{
throw (Ex);
}
}