private string obtenerTipoOperacion(string ordenServicio)
{
string tipoOperacion = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SELECT CTPOOP FROM [email protected] WHERE NORSRN = @NORSRN", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@NORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tipoOperacion = (dr.IsDBNull(dr.GetOrdinal("CTPOOP")) ? 1 : dr.GetInt32(dr.GetOrdinal("CTPOOP"))) == 1 ? "I" : "E";
}
}
catch (Exception ex)
{
tipoOperacion = "I";
}
finally
{
cn.Close();
}
return(tipoOperacion);
}
private int PrimoNumeroLibero()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT max(o.prog) " +
" FROM $EMIEDATI.oda200f o " +
" where NORD = " + _n;
iDB2DataReader myReader = myCommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
try
{ return(myReader.GetInt32(0)); }
catch
{ return(0); }
}
}
else
{
return(0);
}
DBCONN.Close();
return(0);
}
public List <PendientesLlenos> ListaStockVacios()
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_CONSULTA_DSREPSTOCKCONTVACIOS_V2", cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.NUMID = dr.IsDBNull(dr.GetOrdinal("NUMID")) ? 0 : dr.GetInt32(dr.GetOrdinal("NUMID"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
a.ReferenceNumber = dr.IsDBNull(dr.GetOrdinal("DOCREF")) ? "" : dr.GetString(dr.GetOrdinal("DOCREF"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
private int PrimoNumeroLibero()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT max(o.nord)" +
" FROM $EMIEDATI.oda200f o ";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
if (myReader.HasRows)
{
return(myReader.GetInt32(0));
}
else
{
return(0);
}
}
public List <User> findAll()
{
string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" ORDER BY USERNAME";
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, null))
{
if (rdr.Read())
{
List <User> list = new List <User>();
do
{
User user = new User();
user.userNo = rdr.GetInt32(0);
user.username = rdr.GetString(1);
user.password = rdr.GetString(2);
list.Add(user);
} while (rdr.Read());
return(list);
}
else
{
return(null);
}
}
}
public User get(string username)
{
string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" WHERE USERNAME=@USERNAME";
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@USERNAME", username)
};
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, parms))
{
if (rdr.Read())
{
User user = new User();
user.userNo = rdr.GetInt32(0);
user.username = rdr.GetString(1);
user.password = rdr.GetString(2);
return(user);
}
else
{
return(null);
}
}
}
public List <PendientesLlenos> ListaPendientesLlenos(string tipo)
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_INTEGRACIONTDEPOT_JMY", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XTIPO", iDB2DbType.iDB2VarChar).Value = tipo;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.NUMID = dr.IsDBNull(dr.GetOrdinal("NUMID")) ? 0 : dr.GetInt32(dr.GetOrdinal("NUMID"));
a.VesselIMO = dr.IsDBNull(dr.GetOrdinal("CINDVP")) ? "" : dr.GetString(dr.GetOrdinal("CINDVP"));
a.OceanCarrier = dr.IsDBNull(dr.GetOrdinal("CLINE")) ? "" : dr.GetString(dr.GetOrdinal("CLINE"));
a.CustomerERPCode = dr.IsDBNull(dr.GetOrdinal("NRUCA")) ? "" : dr.GetString(dr.GetOrdinal("NRUCA"));
a.YardCode = dr.IsDBNull(dr.GetOrdinal("CTPDEP")) ? "" : dr.GetString(dr.GetOrdinal("CTPDEP"));
a.ActionType = dr.IsDBNull(dr.GetOrdinal("CACTTP")) ? "" : dr.GetString(dr.GetOrdinal("CACTTP"));
a.VoyageNumber = dr.IsDBNull(dr.GetOrdinal("VOYNUM")) ? "" : dr.GetString(dr.GetOrdinal("VOYNUM"));
a.VoyageStopExternalCode = dr.IsDBNull(dr.GetOrdinal("CRECAL")) ? "" : dr.GetString(dr.GetOrdinal("CRECAL"));
a.ReferenceType = dr.IsDBNull(dr.GetOrdinal("CTDCRF")) ? "" : dr.GetString(dr.GetOrdinal("CTDCRF"));
a.ReferenceNumber = dr.IsDBNull(dr.GetOrdinal("NDCREF")) ? "" : dr.GetString(dr.GetOrdinal("NDCREF"));
a.OperationType = dr.IsDBNull(dr.GetOrdinal("CTIPOP")) ? "" : dr.GetString(dr.GetOrdinal("CTIPOP"));
a.PortOfDischarge = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN"));
a.PlaceOfDelivery = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN"));
a.PlaceOfReceipt = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1"));
a.PortOfLoading = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1"));
a.ShipmentType = dr.IsDBNull(dr.GetOrdinal("CTPCNC")) ? "" : dr.GetString(dr.GetOrdinal("CTPCNC"));
a.ActivityCode = dr.IsDBNull(dr.GetOrdinal("CACTVC")) ? "" : dr.GetString(dr.GetOrdinal("CACTVC"));
a.direction = dr.IsDBNull(dr.GetOrdinal("CDIREC")) ? "" : dr.GetString(dr.GetOrdinal("CDIREC"));
a.Status = dr.IsDBNull(dr.GetOrdinal("CSTSMV")) ? "" : dr.GetString(dr.GetOrdinal("CSTSMV"));
a.VehiclePlate = dr.IsDBNull(dr.GetOrdinal("NPLCUN")) ? "" : dr.GetString(dr.GetOrdinal("NPLCUN"));
a.ExecutionDate = dr.IsDBNull(dr.GetOrdinal("FCHMOV")) ? "" : dr.GetString(dr.GetOrdinal("FCHMOV"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
a.EquipmentTypeSizeISOCode = dr.IsDBNull(dr.GetOrdinal("ISOCODE")) ? "" : dr.GetString(dr.GetOrdinal("ISOCODE"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
public List <LiquidacionOpe> ListarLiquidacionOperaciones(int norsrn)
{
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_INTEGRACION_CARCOOL_LIQOPE", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@NORSRN", iDB2DbType.iDB2Numeric).Value = norsrn;
List <LiquidacionOpe> LstLiqOpe = new List <LiquidacionOpe>();
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//refrescar datos
LiquidacionOpe e = new LiquidacionOpe();
e.NMRLQO = dr.GetInt32(dr.GetOrdinal("NMRLQO"));
e.NORSRN = dr.GetInt32(dr.GetOrdinal("NORSRN"));
e.FINDSC = dr.GetString(dr.GetOrdinal("FINDSC"));
e.HINDSC = dr.GetString(dr.GetOrdinal("HINDSC"));
e.FFNDSC = dr.GetString(dr.GetOrdinal("FFNDSC"));
e.HFNDSC = dr.GetString(dr.GetOrdinal("HFNDSC"));
e.FINEMB = dr.GetString(dr.GetOrdinal("FINEMB"));
e.HINEMB = dr.GetString(dr.GetOrdinal("HINEMB"));
e.FFNEMB = dr.GetString(dr.GetOrdinal("FFNEMB"));
e.HFNEMB = dr.GetString(dr.GetOrdinal("HFNEMB"));
e.FCOFF1 = dr.GetString(dr.GetOrdinal("FCOFF1"));
e.HCOFF1 = dr.GetString(dr.GetOrdinal("HCOFF1"));
e.FCOFF2 = dr.GetString(dr.GetOrdinal("FCOFF2"));
e.HCOFF2 = dr.GetString(dr.GetOrdinal("HCOFF2"));
e.FPSTAK = dr.GetString(dr.GetOrdinal("FPSTAK"));
e.HPSTAK = dr.GetString(dr.GetOrdinal("HPSTAK"));
e.FATQNA = dr.GetString(dr.GetOrdinal("FATQNA"));
e.HATQNA = dr.GetString(dr.GetOrdinal("HATQNA"));
LstLiqOpe.Add(e);
}
}
catch (Exception ex)
{
List <string> listaDestinatarios = new List <string>();
List <string> listaCopia = new List <string>();
List <string> listaCopiaOculta = new List <string>();
string destinatarios = ConfigurationManager.AppSettings["Destinatarios"].ToString();
if (destinatarios != "" && destinatarios.Contains(";"))
{
var correosDestino = destinatarios.Split(';');
foreach (string item in correosDestino)
{
listaDestinatarios.Add(item);
}
}
else
{
listaDestinatarios.Add(destinatarios);
}
string copia = ConfigurationManager.AppSettings["ConCopia"].ToString();
if (copia != "" && copia.Contains(";"))
{
var correosCopia = copia.Split(';');
foreach (string item in correosCopia)
{
listaCopia.Add(item);
}
}
else
{
listaCopia.Add(copia);
}
string copiaOculta = ConfigurationManager.AppSettings["CopiaOculta"].ToString();
if (copiaOculta != "" && copiaOculta.Contains(";"))
{
var correosCopiaOculta = copiaOculta.Split(';');
foreach (string item in correosCopiaOculta)
{
listaCopiaOculta.Add(item);
}
}
else
{
listaCopiaOculta.Add(copiaOculta);
}
new ManejadorCorreos().EnviarCorreo(ConfigurationManager.AppSettings["AsuntoCorreoError"].ToString(), ex.Message.ToString(), listaDestinatarios, listaCopia, listaCopiaOculta);
LstLiqOpe = null;
}
finally
{
cn.Close();
}
return(LstLiqOpe);
}
//WCSACCDB
/// <summary>
/// Returns true if HD exist, and saves this hd in OriginalHdModel instance created in this class.
/// If hd is Unknown returns false.
/// </summary>
public bool DownloadHdFromReflex(string _hd)
{
Console.WriteLine("Trying to connect to Reflex for downloading HD informations.");
try
{
conn.Open();
if (conn != null)
{
Console.WriteLine("Successfully connected to Reflex for downloading HD informations.");
// Below are DB2 functions needed for executing query
// Query join table where we can see lines by items. A2CFAN is telling to DB2 to show only lines value.
// You can change it to show CAPO or something else. Result will be in field A2CFAR.
// This also needs to be ordered by Item.
string _queryString = $"SELECT GECART, GECQAL, A.A2CFAR, GEQGEI, B.A2CFAR FROM {Environment}.HLGEINP " +
$"inner join {Environment}.HLCDFAP A on GECART = A.A2CART " +
$"inner join {Environment}.HLCDFAP B on GECART = B.A2CART " +
$"WHERE GENSUP = '{_hd}' and A.A2CFAN = 'LINE' AND B.A2CFAN = 'STAGIONE'" +
"Order by GECART";
iDB2Command comm = conn.CreateCommand();
comm.CommandText = _queryString;
iDB2DataReader reader = comm.ExecuteReader();
// Below if checks if there is some data in result. If no then it return false.
// That means that HD is Unknown.
if (reader.HasRows)
{
// Reader in while goes through all rows of results from Reflex.
while (reader.Read())
{
// Here we are adding new IPG to HD object.
OriginalHdModel.ListOfIpgs.Add(new IpgModel()
{
Item = reader.GetString(0).ToString().Trim(),
Grade = reader.GetString(1).ToString().Trim(),
// Lines is an enum so we need parse string to enum here.
Line = (Lines)Enum.Parse(typeof(Lines), reader.GetString(2)),
Quantity = reader.GetInt32(3),
Season = reader.GetString(4).ToString().Trim()
});
}
// some cleaning.
reader.Close();
comm.Dispose();
// Returns true so we have our data in "OriginalHdModel" instance.
return(true);
}
else
{
// When there is no data from Reflex
return(false);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex);
Console.WriteLine(ex.StackTrace);
return(false);
}
finally
{
conn.Close();
}
// This will never reach but needs to be here because of error "Not all is returning value".
return(false);
}