public static string getAbsEntry(string id)
{
string result = "";
//List<dataFromOINS> serviceCallsArr = new List<dataFromOINS>();
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
command.CommandText = @"select ""AbsEntry"" from ""OBTN""";
command.CommandText += string.Format(@"where ""DistNumber""='{0}'", id);
connection.Open();
using (HanaDataAdapter da = new HanaDataAdapter(command.CommandText, connection))
{
using (DataTable dt = new DataTable())
{
da.Fill(dt);
result = dt.Rows[0]["AbsEntry"].ToString();
}
}
connection.Close();
return(result);
}
}
public static DataSet GetDataSet(string commandText
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure)
{
HanaConnection connection = null;
HanaCommand command = null;
HanaDataAdapter da = new HanaDataAdapter();
DataSet ds = new DataSet();
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
da.SelectCommand = command;
da.Fill(ds);
}
catch (HanaException)
{
throw;
}
finally
{
CloseDataAdapter(da);
CloseCommand(command);
CloseConnection(connection);
}
return(ds);
}
public async Task <string> TestSapConnection()
{
try
{
using (var conn = new HanaConnection(_options.Value.DbConnectionString))
{
conn.Open();
var schema = _sapServiceSettingsService.GetSapSchema("AR");
var query = $"select * from {schema}.oeml";
var da = new HanaDataAdapter(query, conn);
var dt = new DataTable("Invoices");
da.Fill(dt);
conn.Close();
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error testing Hana connection");
return(ex.Message);
}
return("Successfully");
}
private static void CloseDataAdapter(HanaDataAdapter dataAdapter)
{
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
private void btLotClict(object sender, RoutedEventArgs e)
{
exeSql("truncate table OIBT");
HanaDataAdapter dta = new HanaDataAdapter();
DataTable dt = new DataTable();
string lSql = "SELECT T0.\"ItemCode\" ,T0.\"WhsCode\", T0.\"BatchNum\" ,T0.\"ItemName\" ,sum(T0.\"Quantity\") \"Quantity\" " +
"FROM \"SBOKPS_LIVE\".\"OIBT\" T0 " +
"where T0.\"WhsCode\" in ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'B01', 'B02', 'B03', 'B04', 'B05', 'B06', 'B07', 'B08', 'B09', 'B10', 'B11', 'B12', 'B13', 'B14', 'B15', 'B16', 'B17', 'B18', 'B19', 'B20', 'B21', 'B22', 'B23', 'B24', 'C01', 'C02', 'C03', 'C04') " +
" and T0.\"Quantity\" > 0 " +
" group by T0.\"ItemCode\" ,T0.\"WhsCode\", T0.\"BatchNum\" ,T0.\"ItemName\" ";// +
//" order by T0.\"WhsCode\", T0.\"ItemCode\" ,T0.\"BatchNum \" ";
dta = new HanaDataAdapter(lSql, sapConn());
dta.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
//insert SQL.SAP.OIBT
insertOIBT(dt.Rows[i]["ItemCode"].ToString(), dt.Rows[i]["BatchNum"].ToString(), dt.Rows[i]["WhsCode"].ToString(), double.Parse(dt.Rows[i]["Quantity"].ToString()));
}
MessageBox.Show("Ok");
}
private async Task <DataTable> GetInvoiceRecords(string clientPrefix, int clientId, string sapSystem, int?fileId = null)
{
using (var conn = new HanaConnection(_options.Value.DbConnectionString))
{
conn.Open();
var query = string.Empty;
/* Invoices */
query += CreateInvoiceQuery("FC", clientPrefix, clientId, sapSystem, fileId);
query += " UNION ";
/* Credit Notes */
query += CreateInvoiceQuery("NC", clientPrefix, clientId, sapSystem, fileId);
var da = new HanaDataAdapter(query, conn);
var dt = new DataTable("Invoices");
da.Fill(dt);
conn.Close();
return(dt);
}
}
public DataTable SapListaPrecioDT()
{
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_VTAS_WEB_LISTA_PRECIO";
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 DataSet VentasGeneralesDetalladaSAPDS(string Marca)
{
DataSet dts = new DataSet();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VTAS_WEB_LISTAPRECIOSTOCK";
HanaParameter mar = new HanaParameter();
// Parametro Canal.
mar = cmd.CreateParameter();
mar.HanaDbType = HanaDbType.VarChar;
mar.Direction = ParameterDirection.Input;
mar.Value = Marca;
cmd.Parameters.Add(mar);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dts, "ACSA_VTAS_WEB_LISTAPRECIOSTOCK");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
//return (dts.Tables["VentasMensualesGCDetalladoxCanal"]);
return(dts);
/*
*
* catch (SqlException)
* {
*
* throw new Exception();
*
* }
* finally
* {
* if (cnx.State == ConnectionState.Open)
* {
* cnx.Close();
* }
* cmd.Parameters.Clear();
* }*/
}
public DataTable VentasGCSAPConsultar(DateTime fecha1, DateTime fecha2, 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_VTAS_WEB_VentaGC";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
HanaParameter pc = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = fecha1;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = fecha2;;
cmd.Parameters.Add(f2);
// Parametro Canal.
pc = cmd.CreateParameter();
pc.HanaDbType = HanaDbType.VarChar;
pc.Direction = ParameterDirection.Input;
pc.Value = Canal;
cmd.Parameters.Add(pc);
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();
}
}
/// <summary>
/// 查詢
/// 傳入SqlString跟使用的Type跟HanaParameter參數回傳DataTable
/// Text=SQL語法
/// StoredProcedure=SP
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>查詢的DataTable</returns>
public DataTable QueryDataTable(string sqlString, CommandType cmdType, object[] paramsArr)
{
DataTable dataTable;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
using (HanaDataAdapter da = new HanaDataAdapter(cmd))
{
DataTable dt = new DataTable();
try
{
try
{
da.Fill(dt);
SqlParameterOutputSetting(cmdParams);
cmd.Parameters.Clear();
dataTable = dt;
}
catch (HanaException sqlException)
{
throw new Exception(sqlException.Message);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Dispose();
}
}
}
return(dataTable);
}
public DataTable VentaClienteDT(DateTime fechaInicial, DateTime fechaFinal)
{
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_VENTACLIENTES";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = fechaInicial;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = fechaFinal;;
cmd.Parameters.Add(f2);
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 VentasGeneralesDetalladaSAPDT(DateTime FechaInicial, DateTime FechaFinal)
{
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_VENTAXARTICULO";
HanaParameter fechaInicial = new HanaParameter();
HanaParameter fechaFinal = new HanaParameter();
// Parametro Fecha Inicial.
fechaInicial = cmd.CreateParameter();
fechaInicial.HanaDbType = HanaDbType.Date;
fechaInicial.Direction = ParameterDirection.Input;
fechaInicial.Value = FechaInicial;
cmd.Parameters.Add(fechaInicial);
// Parametro Fecha Final.
fechaFinal = cmd.CreateParameter();
fechaFinal.HanaDbType = HanaDbType.Date;
fechaFinal.Direction = ParameterDirection.Input;
fechaFinal.Value = FechaFinal;
cmd.Parameters.Add(fechaFinal);
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 SapListaPrecioDetalleDT(string Lista)
{
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_VTAS_WEB_LISTA_PRECIO_DETALLES";
HanaParameter List = new HanaParameter();
// Parametro Canal.
List = cmd.CreateParameter();
List.HanaDbType = HanaDbType.VarChar;
List.Direction = ParameterDirection.Input;
List.Value = Lista;
cmd.Parameters.Add(List);
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 VentasGeneralesDetalladaSAPDT(string Marca)
{
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_VTAS_WEB_LISTAPRECIOSTOCK";
HanaParameter mar = new HanaParameter();
// Parametro Canal.
mar = cmd.CreateParameter();
mar.HanaDbType = HanaDbType.VarChar;
mar.Direction = ParameterDirection.Input;
mar.Value = Marca;
cmd.Parameters.Add(mar);
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();
}
}
private void SapToSoldev()
{
HanaDataAdapter dta = new HanaDataAdapter();
DataTable dt = new DataTable();
string lSql = sapSqlSolDev();
HanaCommand cmd = new HanaCommand(lSql, sapConn());
HanaDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
dgSoldev.ItemsSource = reader;
//btnToSml.Visibility = Visibility.Visible;
}
else
{
MessageBox.Show("Document not found..!");
}
}
public static dataFromOINS GetEqCard(string id)
{
dataFromOINS dataOINS = new dataFromOINS();
//List<dataFromOINS> serviceCallsArr = new List<dataFromOINS>();
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
command.CommandText = @"select * from ""OINS""";
command.CommandText += string.Format(@"where ""insID""='{0}'", id);
connection.Open();
using (HanaDataAdapter da = new HanaDataAdapter(command.CommandText, connection))
{
using (DataTable dt = new DataTable())
{
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
dataOINS.customer = dt.Rows[i]["customer"].ToString();
dataOINS.itemCode = dt.Rows[i]["itemCode"].ToString();
dataOINS.internalSN = dt.Rows[i]["internalSN"].ToString();
dataOINS.custmrName = dt.Rows[i]["custmrName"].ToString();
dataOINS.itemName = dt.Rows[i]["itemName"].ToString();
dataOINS.createDate = dt.Rows[i]["createDate"].ToString();
dataOINS.docEntry = dt.Rows[i]["U_DocEntry"].ToString();
dataOINS.insID = dt.Rows[i]["insID"].ToString();
}
}
}
connection.Close();
return(dataOINS);
}
}
public DataTable PopulateDT()
{
DataTable Dt = new DataTable();
ErrorC = 0;
try
{
da = new HanaDataAdapter(cSql, cn);
da.SelectCommand.CommandTimeout = 10000;
da.Fill(Dt);
this.Registros = Dt.Rows.Count;
}
catch (Exception ex)
{
this.ErrorT = ex.ToString();
this.ErrorC = -1;
this.Registros = 0;
}
return(Dt);
}
private void PopulateDT()
{
this.DataTable = new DataTable();
this.CodigoError = 0;
try
{
this.cn = new HanaConnection(HanaConn);
cn.Open();
dataadapter = new HanaDataAdapter(this.Sql, cn);
dataadapter.SelectCommand.CommandTimeout = 10000;
dataadapter.Fill(this.DataTable);
this.Registros = this.DataTable.Rows.Count;
}
catch (Exception ex)
{
this.MensajeError = ex.ToString();
this.CodigoError = -1;
this.Registros = 0;
}
return;
}
public DataTable VentasSTOCKSAPConsultar(String CodigoStock)
{
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_LOG_WEB_Stock";
HanaParameter param = new HanaParameter();
param = cmd.CreateParameter();
param.HanaDbType = HanaDbType.NVarChar;
param.Direction = ParameterDirection.Input;
param.Value = CodigoStock;
cmd.Parameters.Add(param);
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 DataSet SAPSeguimientoOCDS(DateTime FechaInicio, DateTime FechaFin, string CodProveedor, string NomProveedor, string Solicitud, string Orden, string Entrada, string Factura)
{
DataSet dts = new DataSet();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_LOG_RPT_SEGUIMIENTO_OC";
HanaParameter fechaI = new HanaParameter();
HanaParameter fechaFin = new HanaParameter();
HanaParameter codPro = new HanaParameter();
HanaParameter nomPro = new HanaParameter();
HanaParameter Soli = new HanaParameter();
HanaParameter Ord = new HanaParameter();
HanaParameter entrada = new HanaParameter();
HanaParameter factura = new HanaParameter();
// Parametro Fecha 1.
fechaI = cmd.CreateParameter();
fechaI.HanaDbType = HanaDbType.Date;
fechaI.Direction = ParameterDirection.Input;
fechaI.Value = FechaInicio;
cmd.Parameters.Add(fechaI);
// Parametro Fecha 2.
fechaFin = cmd.CreateParameter();
fechaFin.HanaDbType = HanaDbType.Date;
fechaFin.Direction = ParameterDirection.Input;
fechaFin.Value = FechaFin;;
cmd.Parameters.Add(fechaFin);
codPro = cmd.CreateParameter();
codPro.HanaDbType = HanaDbType.VarChar;
codPro.Direction = ParameterDirection.Input;
codPro.Value = CodProveedor;
cmd.Parameters.Add(codPro);
nomPro = cmd.CreateParameter();
nomPro.HanaDbType = HanaDbType.VarChar;
nomPro.Direction = ParameterDirection.Input;
nomPro.Value = NomProveedor;
cmd.Parameters.Add(nomPro);
Soli = cmd.CreateParameter();
Soli.HanaDbType = HanaDbType.VarChar;
Soli.Direction = ParameterDirection.Input;
Soli.Value = Solicitud;
cmd.Parameters.Add(Soli);
Ord = cmd.CreateParameter();
Ord.HanaDbType = HanaDbType.VarChar;
Ord.Direction = ParameterDirection.Input;
Ord.Value = Orden;
cmd.Parameters.Add(Ord);
entrada = cmd.CreateParameter();
entrada.HanaDbType = HanaDbType.VarChar;
entrada.Direction = ParameterDirection.Input;
entrada.Value = Entrada;
cmd.Parameters.Add(entrada);
factura = cmd.CreateParameter();
factura.HanaDbType = HanaDbType.VarChar;
factura.Direction = ParameterDirection.Input;
factura.Value = Factura;
cmd.Parameters.Add(factura);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dts, "ACSA_LOG_RPT_SEGUIMIENTO_OC");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
//return (dts.Tables["VentasMensualesGCDetalladoxCanal"]);
return(dts);
/*
*
* catch (SqlException)
* {
*
* throw new Exception();
*
* }
* finally
* {
* if (cnx.State == ConnectionState.Open)
* {
* cnx.Close();
* }
* cmd.Parameters.Clear();
* }*/
}
private void btVanTrClict(object sender, RoutedEventArgs e)
{
exeSml("delete TB_ITRAN where ITN_USER='******' and ITN_DOCNO ='" + txtDocNo.Text + "'");
HanaDataAdapter dta = new HanaDataAdapter();
DataTable dt = new DataTable();
string lSql = sapSql();
dta = new HanaDataAdapter(lSql, sapConn());
dta.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
//insert TB_ITRAN
string itnDocno = dt.Rows[i]["ITN_DOCNO"].ToString();
DateTime itnDate = DateTime.Parse(dt.Rows[i]["ITN_DATE"].ToString());
int itnLine = int.Parse(dt.Rows[i]["ITN_LINE"].ToString());
string itnWhf = dt.Rows[i]["ITN_WHF"].ToString();
string itnWht = dt.Rows[i]["ITN_WHT"].ToString();
string itnDesc = dt.Rows[i]["ITN_DESC"].ToString();
string intGoods = dt.Rows[i]["ITN_GOODS"].ToString();
double itnQty = double.Parse(dt.Rows[i]["ITN_QTY"].ToString());
string itnUm = dt.Rows[i]["ITN_UM"].ToString();
double itnStockQty = double.Parse(dt.Rows[i]["ITN_STOCKQTY"].ToString());
string itnStockUm = dt.Rows[i]["ITN_STOCKUM"].ToString();
try
{
//TRANSFER OUT
insertTBITRAN(itnDocno,
itnDate,
itnLine - 1,
itnWhf,
itnWht,
itnDesc,
intGoods,
itnQty * -1,
itnUm,
itnStockQty * -1,
itnStockUm
);
//TRANSFER IN
insertTBITRAN(itnDocno,
itnDate,
itnLine,
itnWht,
itnWhf,
itnDesc,
intGoods,
itnQty,
itnUm,
itnStockQty,
itnStockUm
);
}
catch (Exception ex) {
//
MessageBox.Show(ex.Message);
}
//MessageBox.Show(dt.Rows[i]["ITN_GOODS"].ToString());
}
btnToSml.Visibility = Visibility.Hidden;
MessageBox.Show("Ok");
}
private void btSapInvClict(object sender, RoutedEventArgs e)
{
//exeSml("delete TB_ITRAN where VAL_USER='******'");
HanaDataAdapter dta = new HanaDataAdapter();
DataTable dt = new DataTable();
string lSql = sapSqlArInv();
dta = new HanaDataAdapter(lSql, sapConn());
dta.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
//insert TB_ITRAN
string valCUST = dt.Rows[i]["VAL_CUST"].ToString();
string valPREFIX = dt.Rows[i]["VAL_PREFIX"].ToString();
string valINVOICE = dt.Rows[i]["VAL_INVOICE"].ToString();
int valLINE = int.Parse(dt.Rows[i]["VAL_LINE"].ToString());
DateTime valINVDATE = DateTime.Parse(dt.Rows[i]["VAL_INVDATE"].ToString());
string valGOODSTYPE = "1"; // dt.Rows[i]["VAL_GOODSTYPE"].ToString();
string valGOODS = dt.Rows[i]["VAL_GOODS"].ToString();
string valDESC = dt.Rows[i]["VAL_DESC"].ToString();
double valSTOCKQTY = double.Parse(dt.Rows[i]["VAL_STOCKQTY"].ToString());
string valSTOCKUM = dt.Rows[i]["VAL_STOCKUM"].ToString();
string valUMDESC = dt.Rows[i]["VAL_UMDESC"].ToString();
double valQTY = double.Parse(dt.Rows[i]["VAL_QTY"].ToString());
string valUM = dt.Rows[i]["VAL_UM"].ToString();
double valUMPRICE = double.Parse(dt.Rows[i]["VAL_UMPRICE"].ToString());
double valUPRICE = double.Parse(dt.Rows[i]["VAL_UPRICE"].ToString());
double valAMOUNT = double.Parse(dt.Rows[i]["VAL_AMOUNT"].ToString());
double valDISC = double.Parse(dt.Rows[i]["VAL_DISC"].ToString());
double valDISCAMT = double.Parse(dt.Rows[i]["VAL_DISCAMT"].ToString());
double valTOTAL = double.Parse(dt.Rows[i]["VAL_TOTAL"].ToString());
double valAVGDISC = double.Parse(dt.Rows[i]["VAL_AVGDISC"].ToString());
double valAVGVAT = double.Parse(dt.Rows[i]["VAL_AVGVAT"].ToString());
string valUSER = dt.Rows[i]["VAL_USER"].ToString();
DateTime valUDATE = DateTime.Parse(dt.Rows[i]["VAL_UDATE"].ToString());
DateTime valDATE = DateTime.Parse(dt.Rows[i]["VAL_DATE"].ToString());
try
{
insertSapVanl(valCUST
, valPREFIX
, valINVOICE
, valLINE
, valINVDATE
, valGOODSTYPE
, valGOODS
, valDESC
, valSTOCKQTY
, valSTOCKUM
, valUMDESC
, valQTY
, valUM
, valUMPRICE
, valUPRICE
, valAMOUNT
, valDISC
, valDISCAMT
, valTOTAL
, valAVGDISC
, valAVGVAT
, valUSER
, valUDATE
, valDATE);
}
catch (Exception ex)
{
//
MessageBox.Show(ex.Message);
}
//MessageBox.Show(dt.Rows[i]["VAL_GOODS"].ToString());
}
MessageBox.Show("Ok");
}
public DataSet VentaClienteDS(DateTime fechaInicial, DateTime fechaFinal)
{
DataSet dts = new DataSet();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VEN_RPT_VENTACLIENTES";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = fechaInicial;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = fechaFinal;;
cmd.Parameters.Add(f2);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dts, "ACSA_VEN_RPT_VENTACLIENTES");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
//return (dts.Tables["VentasMensualesGCDetalladoxCanal"]);
return(dts);
/*
*
* 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 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 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 SAPSeguimientoOCIMPDT(DateTime FechaInicio, DateTime FechaFin, string CodProveedor, string NomProveedor, string Solicitud, string Orden, string Reserva, string Entrada, string Precio, string Transferencia)
{
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_LOG_RPT_SEGUIMIENTO_OC_IMP";
HanaParameter fechaI = new HanaParameter();
HanaParameter fechaFin = new HanaParameter();
HanaParameter codPro = new HanaParameter();
HanaParameter nomPro = new HanaParameter();
HanaParameter Soli = new HanaParameter();
HanaParameter Ord = new HanaParameter();
HanaParameter reser = new HanaParameter();
HanaParameter entrada = new HanaParameter();
HanaParameter precio = new HanaParameter();
HanaParameter transferencia = new HanaParameter();
// Parametro Fecha 1.
fechaI = cmd.CreateParameter();
fechaI.HanaDbType = HanaDbType.Date;
fechaI.Direction = ParameterDirection.Input;
fechaI.Value = FechaInicio;
cmd.Parameters.Add(fechaI);
// Parametro Fecha 2.
fechaFin = cmd.CreateParameter();
fechaFin.HanaDbType = HanaDbType.Date;
fechaFin.Direction = ParameterDirection.Input;
fechaFin.Value = FechaFin;;
cmd.Parameters.Add(fechaFin);
codPro = cmd.CreateParameter();
codPro.HanaDbType = HanaDbType.VarChar;
codPro.Direction = ParameterDirection.Input;
codPro.Value = CodProveedor;
cmd.Parameters.Add(codPro);
nomPro = cmd.CreateParameter();
nomPro.HanaDbType = HanaDbType.VarChar;
nomPro.Direction = ParameterDirection.Input;
nomPro.Value = NomProveedor;
cmd.Parameters.Add(nomPro);
Soli = cmd.CreateParameter();
Soli.HanaDbType = HanaDbType.VarChar;
Soli.Direction = ParameterDirection.Input;
Soli.Value = Solicitud;
cmd.Parameters.Add(Soli);
Ord = cmd.CreateParameter();
Ord.HanaDbType = HanaDbType.VarChar;
Ord.Direction = ParameterDirection.Input;
Ord.Value = Orden;
cmd.Parameters.Add(Ord);
reser = cmd.CreateParameter();
reser.HanaDbType = HanaDbType.VarChar;
reser.Direction = ParameterDirection.Input;
reser.Value = Reserva;
cmd.Parameters.Add(reser);
entrada = cmd.CreateParameter();
entrada.HanaDbType = HanaDbType.VarChar;
entrada.Direction = ParameterDirection.Input;
entrada.Value = Entrada;
cmd.Parameters.Add(entrada);
precio = cmd.CreateParameter();
precio.HanaDbType = HanaDbType.VarChar;
precio.Direction = ParameterDirection.Input;
precio.Value = Precio;
cmd.Parameters.Add(precio);
transferencia = cmd.CreateParameter();
transferencia.HanaDbType = HanaDbType.VarChar;
transferencia.Direction = ParameterDirection.Input;
transferencia.Value = Transferencia;
cmd.Parameters.Add(transferencia);
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 DataSet VentasGeneralesDetalladaSAPEEDS(DateTime fecha1, DateTime fecha2, string Canal)
{
DataSet dts = new DataSet();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VTAS_WEB_VentaGCxDetalladaProducto";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
HanaParameter pc = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = fecha1;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = fecha2;;
cmd.Parameters.Add(f2);
// Parametro Canal.
pc = cmd.CreateParameter();
pc.HanaDbType = HanaDbType.VarChar;
pc.Direction = ParameterDirection.Input;
pc.Value = Canal;
cmd.Parameters.Add(pc);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dts, "ACSA_VTAS_WEB_VentaGCxDetalladaProducto");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
//return (dts.Tables["VentasMensualesGCDetalladoxCanal"]);
return(dts);
/*
*
* catch (SqlException)
* {
*
* throw new Exception();
*
* }
* finally
* {
* if (cnx.State == ConnectionState.Open)
* {
* cnx.Close();
* }
* cmd.Parameters.Clear();
* }*/
}
