private DataTable GetReportData()
{
DataGridView gr = new DataGridView();
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand selectCmd = new HanaCommand(string.Format("SELECT \"QUERY\" AS \"SqlQuery\" FROM {1}.\"@PRINTDOCUMENT\" WHERE \"Code\" LIKE '{0}'", this.oid, ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaDataReader dr = selectCmd.ExecuteReader();
gr.DataSource = dr;
DataTable dt = new DataTable();
dt.Load(dr);
dr.Close();
conn.Close();
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
var cmdText = System.Text.ASCIIEncoding.ASCII.GetString((byte[])dt.Rows[0]["SqlQuery"]);
if (string.IsNullOrEmpty(cmdText))
{
return(null);
}
return(Utility.hanaConnection(cmdText));
//var conn1 = DBConnection.conn;
//try
//{
// var obj = Utility.hanaConnection(string.Format("SELECT \"QUERY\" AS \"SqlQuery\" FROM \"@PRINTDOCUMENT\" WHERE \"Code\" LIKE '{0}'", this.oid));
// if (obj == null || obj.Rows.Count == 0) return null;
// var cmdText = System.Text.ASCIIEncoding.ASCII.GetString((byte[])obj.Rows[0]["SqlQuery"]);
// if (string.IsNullOrEmpty(cmdText)) return null;
// return
// Utility.GetObjects(cmdText, conn1);
//}
//catch (Exception)
//{
// return null;
//}
}
public static void SaveLayout(System.IO.MemoryStream stream, string id)
{
try
{
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand insertCmd = new HanaCommand(string.Format("UPDATE {0}.\"@PRINTDOCUMENT\" SET \"LAYOUT\" = ? WHERE \"Code\" LIKE ?", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaParameter parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarBinary;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
insertCmd.Parameters[0].Value = stream.ToArray();
insertCmd.Parameters[1].Value = id;
int recordsAffected = insertCmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static Form1.allFields[][] whereFormIsOpenMatrix0(string insID)
{
Form1.allFields[][] dataForTables = new Form1.allFields[2][];
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
command.CommandText = string.Format(@"select * from ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" where ""U_insID""={0}", insID);
connection.Open();
using (HanaDataAdapter da = new HanaDataAdapter(command.CommandText, connection))
{
using (DataTable dt = new DataTable())
{
da.Fill(dt);
dataForTables[0] = new Form1.allFields[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
dataForTables[0][i].code1 = dt.Rows[i]["U_Code"].ToString();
dataForTables[0][i].title = dt.Rows[i]["U_Name"].ToString();
dataForTables[0][i].photo = dt.Rows[i]["U_Picture"].ToString();
dataForTables[0][i].serialNumber = dt.Rows[i]["U_SerialNumber"].ToString();
dataForTables[0][i].articul = dt.Rows[i]["U_Articul"].ToString();
dataForTables[0][i].EAN = dt.Rows[i]["U_EAN"].ToString();
dataForTables[0][i].instDate = dt.Rows[i]["U_InstDate"] != DBNull.Value ? Convert.ToDateTime(dt.Rows[i]["U_InstDate"]).ToString("yyyyMMdd") : null;
dataForTables[0][i].srokExpl = dt.Rows[i]["U_LifeTime"].ToString();
dataForTables[0][i].snDate = dt.Rows[i]["U_drDownDate"] != DBNull.Value ? Convert.ToDateTime(dt.Rows[i]["U_drDownDate"]).ToString("yyyyMMdd") : null; //dt.Rows[i]["U_drDownDate"].ToString();
dataForTables[0][i].comment = dt.Rows[i]["U_Comment"].ToString();
dataForTables[0][i].warning = dt.Rows[i]["U_Warning"].ToString();
dataForTables[0][i].charact = dt.Rows[i]["U_Characteristics"].ToString();
dataForTables[0][i].modif = dt.Rows[i]["U_Modification"].ToString();
dataForTables[0][i].track = dt.Rows[i]["U_trackingDate"].ToString();
dataForTables[0][i].insID = dt.Rows[i]["U_insID"].ToString();
}
}
}
connection.Close();
}
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
//command.CommandText = @"select * from ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2""";
//command.CommandText += string.Format(@"where ""U_insID""='{0}'", insID);
command.CommandText = string.Format(@"select * from ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2"" where ""U_insID""={0}", insID);
connection.Open();
using (HanaDataAdapter da = new HanaDataAdapter(command.CommandText, connection))
{
using (DataTable dt = new DataTable())
{
da.Fill(dt);
dataForTables[1] = new Form1.allFields[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
dataForTables[1][i].code1 = dt.Rows[i]["U_code"].ToString();
dataForTables[1][i].title = dt.Rows[i]["U_TitlePaint"].ToString();
dataForTables[1][i].batchNumber = dt.Rows[i]["U_BatchNumber"].ToString();
dataForTables[1][i].articul = dt.Rows[i]["U_Articul"].ToString();
dataForTables[1][i].EAN = dt.Rows[i]["U_EAN"].ToString();
//~~~~~~~~~~~
dataForTables[1][i].createDate = dt.Rows[i]["U_ManufDate"].ToString();
dataForTables[1][i].createDate = dt.Rows[i]["U_ManufDate"] != DBNull.Value ? Convert.ToDateTime(dt.Rows[i]["U_ManufDate"]).ToString("yyyyMMdd") : null;
dataForTables[1][i].shipingDate = dt.Rows[i]["U_DeliveryDate"].ToString();
dataForTables[1][i].shipingDate = dt.Rows[i]["U_DeliveryDate"] != DBNull.Value ? Convert.ToDateTime(dt.Rows[i]["U_DeliveryDate"]).ToString("yyyyMMdd") : null;
dataForTables[1][i].srokExpl = dt.Rows[i]["U_ShelfLife"].ToString();
dataForTables[1][i].srokExpl = dt.Rows[i]["U_ShelfLife"] != DBNull.Value ? Convert.ToDateTime(dt.Rows[i]["U_ShelfLife"]).ToString("yyyyMMdd") : null;
//~~~~~~~~~~~
dataForTables[1][i].comment = dt.Rows[i]["U_Comment"].ToString();
dataForTables[1][i].warning = dt.Rows[i]["U_Warning"].ToString();
dataForTables[1][i].track = dt.Rows[i]["U_trackingDate"].ToString();
dataForTables[1][i].insID = dt.Rows[i]["U_insID"].ToString();
}
}
}
}
connection.Close();
return(dataForTables);
}
public DataTable CostoInvetarioDSDT(DateTime FechaInicio, DateTime FechaFin, string CodCliente, string Canal)
{
DataTable dt = new DataTable();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_CON_RPT_INVENTARIO";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
HanaParameter cc = new HanaParameter();
HanaParameter c = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = FechaInicio;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = FechaFin;;
cmd.Parameters.Add(f2);
// Parametro Cliente.
cc = cmd.CreateParameter();
cc.HanaDbType = HanaDbType.VarChar;
cc.Direction = ParameterDirection.Input;
cc.Value = CodCliente;
cmd.Parameters.Add(cc);
// Parametro Canal.
c = cmd.CreateParameter();
c.HanaDbType = HanaDbType.VarChar;
c.Direction = ParameterDirection.Input;
c.Value = Canal;
cmd.Parameters.Add(c);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch (SqlException)
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public DataTable DocumentosAutorizadosConsultar(DateTime fechaInicial, DateTime fechaFinal, string numSAP, string codCliente, string codAlmacen)
{
DataTable dt = new DataTable();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VEN_RPT_FLUJODEOVS";
HanaParameter FechaInicio = new HanaParameter();
HanaParameter FechaFin = new HanaParameter();
HanaParameter NumSAP = new HanaParameter();
HanaParameter CodCliente = new HanaParameter();
HanaParameter CodAlmacen = new HanaParameter();
// Parametro Fecha 1.
FechaInicio = cmd.CreateParameter();
FechaInicio.HanaDbType = HanaDbType.Date;
FechaInicio.Direction = ParameterDirection.Input;
FechaInicio.Value = fechaInicial;
cmd.Parameters.Add(FechaInicio);
// Parametro Fecha 2.
FechaFin = cmd.CreateParameter();
FechaFin.HanaDbType = HanaDbType.Date;
FechaFin.Direction = ParameterDirection.Input;
FechaFin.Value = fechaFinal;;
cmd.Parameters.Add(FechaFin);
// Parametro Numero de Orden de Venta.
NumSAP = cmd.CreateParameter();
NumSAP.HanaDbType = HanaDbType.VarChar;
NumSAP.Direction = ParameterDirection.Input;
NumSAP.Value = numSAP;
cmd.Parameters.Add(NumSAP);
// Parametro Cliente.
CodCliente = cmd.CreateParameter();
CodCliente.HanaDbType = HanaDbType.VarChar;
CodCliente.Direction = ParameterDirection.Input;
CodCliente.Value = codCliente;
cmd.Parameters.Add(CodCliente);
// Parametro Almacen.
CodAlmacen = cmd.CreateParameter();
CodAlmacen.HanaDbType = HanaDbType.VarChar;
CodAlmacen.Direction = ParameterDirection.Input;
CodAlmacen.Value = codAlmacen;
cmd.Parameters.Add(CodAlmacen);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public static string InsertUpdatePrintDocument(string name, string group,
string sqlQuery, string printDocumentID,
DateTime?modifiedDate = null)
{
var cmd = "";
var isInsert = false;
if (printDocumentID == "" || printDocumentID == "0")
{
var printID = Utility.hanaConnection(string.Format("SELECT TOP 1 \"Code\" FROM {0}.\"@PRINTDOCUMENT\" ORDER BY \"Code\" * 1 DESC", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]));
if (printID == null)
{
printDocumentID = "1";
}
else
{
if (printID.Rows.Count == 0)
{
printDocumentID = "1";
}
else
{
printDocumentID = (Convert.ToDecimal(printID.Rows[0]["Code"]) + 1).ToString();
}
}
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand insertCmd = new HanaCommand(string.Format("INSERT INTO {0}.\"@PRINTDOCUMENT\" (\"Name\", \"QUERY\", \"Code\", \"GROUP\", \"PRINTNAME\", \"U_IsDeleted\") VALUES ( ?, ?, ?, ?, ?, 0)", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaParameter parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarBinary;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
insertCmd.Parameters[0].Value = group + ' ' + name;
insertCmd.Parameters[1].Value = System.Text.Encoding.ASCII.GetBytes(sqlQuery);
insertCmd.Parameters[2].Value = printDocumentID;
insertCmd.Parameters[3].Value = group;
insertCmd.Parameters[4].Value = name;
int recordsAffected = insertCmd.ExecuteNonQuery();
conn.Close();
isInsert = true;
}
else
{
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand insertCmd = new HanaCommand(string.Format("UPDATE {0}.\"@PRINTDOCUMENT\" SET \"Name\" = ?, \"QUERY\" = ?, \"GROUP\" = ?, \"PRINTNAME\" = ? WHERE \"Code\" = ?", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaParameter parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarBinary;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
insertCmd.Parameters[0].Value = group + ' ' + name;
insertCmd.Parameters[1].Value = System.Text.Encoding.ASCII.GetBytes(sqlQuery);
insertCmd.Parameters[2].Value = group;
insertCmd.Parameters[3].Value = name;
insertCmd.Parameters[4].Value = printDocumentID;
int recordsAffected = insertCmd.ExecuteNonQuery();
conn.Close();
}
if (isInsert)
{
var obj = Utility.hanaConnection(string.Format("SELECT \"Code\" FROM {1}.\"@PRINTDOCUMENT\" WHERE \"U_IsDeleted\" = 0 AND \"Name\" LIKE '{0}'", group + ' ' + name, System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]));
printDocumentID = obj == null || obj.Rows.Count == 0 ? "0" : obj.Rows[0]["Code"].ToString();
}
return(printDocumentID);
}
