public static T ExecuteToEntity <T>(string commandText
, RowMapper <T> rowMapper
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure) where T : class
{
HanaConnection connection = null;
HanaCommand command = null;
HanaDataReader dataReader = null;
T entity = null;
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
entity = rowMapper(dataReader);
}
}
catch (HanaException)
{
throw;
}
finally
{
CloseDataReader(dataReader);
CloseCommand(command);
CloseConnection(connection);
}
return(entity);
}
/// <summary>
/// 設定HanaCommand物件的方法
/// </summary>
/// <param name="cmd">HanaCommand物件</param>
/// <param name="cmdSetModel">cmd設定物件</param>
/// <param name="cmdParams">HanaParameter參數陣列</param>
private void SettingCommand(HanaCommand cmd, CmdSettingModel cmdSetModel, HanaParameter[] cmdParams)
{
cmd.Connection = cmdSetModel.Conn;
cmd.CommandText = cmdSetModel.Text;
if (cmdSetModel.Trans != null)
{
cmd.Transaction = cmdSetModel.Trans;
}
else if (cmdSetModel.Conn.State != ConnectionState.Open)
{
cmdSetModel.Conn.Open();
}
cmd.Parameters.Clear();
if (cmdParams != null)
{
HanaParameter[] sqlParameterArray = cmdParams;
for (int i = 0; i < sqlParameterArray.Length; i++)
{
HanaParameter param = sqlParameterArray[i];
cmd.Parameters.Add(param);
}
SqlParameterOriginal(cmdParams);
}
}
public static T ExecuteScalar <T>(string commandText
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure)
{
HanaConnection connection = null;
HanaCommand command = null;
T result;
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
result = (T)command.ExecuteScalar();
}
catch (HanaException)
{
throw;
}
finally
{
CloseCommand(command);
CloseConnection(connection);
}
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 DataTable ExecuteQuery(string query, Parameters parameters)
{
using (var conn = (T)Activator.CreateInstance(typeof(T), ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
command.CommandText = query;
if (parameters != null)
{
object param;
command.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair <string, object> kvp in parameters.paramsList)
{
if (typeof(T).Equals(typeof(HanaConnection)))
{
param = new HanaParameter(kvp.Key, kvp.Value);
}
else
{
param = new SqlParameter(kvp.Key, kvp.Value);
}
command.Parameters.Add(param);
}
}
var result = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(result);
conn.Close();
return(dt);
}
}
/// <summary>
/// 新增、修改、刪除
/// 傳入參數對SQL資料做更動
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// 不含交易物件的方法
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料異動數</returns>
public int ExcuteSQL(string sqlString, CommandType cmdType, object[] paramsArr)
{
int num;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
int result = 0;
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);
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
result = cmd.ExecuteNonQuery();
SqlParameterOutputSetting(cmdParams);
cmd.Parameters.Clear();
num = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Dispose();
}
}
return(num);
}
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();
}
}
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();
* }*/
}
/// <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);
}
/// <summary>
/// 回傳Scalar(一筆資料的一個欄位)
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料</returns>
public object ExecuteScalar(string sqlString, CommandType cmdType, object[] paramsArr)
{
object obj;
object result = null;
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);
}
try
{
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
result = cmd.ExecuteScalar();
SqlParameterOutputSetting(cmdParams);
obj = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
}
}
return(obj);
}
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 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();
}
}
public static void Execute(string commandText
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, Dictionary <string, object> paramsOutputValue = null
, CommandType type = CommandType.StoredProcedure
, HanaConnection connection = null
, HanaTransaction transaction = null)
{
HanaCommand command = null;
try
{
if (connection == null)
{
connection = OpenConnection();
}
command = PrepareCommand(commandText, connection, type, parameter, parameters, transaction);
command.ExecuteNonQuery();
if (parameter != null || parameters != null)
{
foreach (HanaParameter x in parameters)
{
if (x.Direction == ParameterDirection.Output)
{
paramsOutputValue.Add(x.ParameterName, command.Parameters[x.ParameterName].Value);
}
}
}
}
catch (HanaException)
{
throw;
}
finally
{
CloseCommand(command);
if (transaction == null)
{
CloseConnection(connection);
}
}
}
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();
}
}
/// <summary>
/// 設置params參數
/// </summary>
/// <param name="cmdParams"></param>
/// <param name="paramsArr"></param>
private void SettingParams(HanaParameter[] cmdParams, object[] paramsArr)
{
int count = paramsArr.Length;
HanaParameter[] result = new HanaParameter[count];
if (paramsArr.Length > 0)
{
for (int i = 0; i < paramsArr.Length; i++)
{
if (paramsArr[i] == null)
{
cmdParams[i] = new HanaParameter("@p" + i, (object)DBNull.Value);
}
else
{
cmdParams[i] = new HanaParameter("@p" + i, paramsArr[i]);
}
}
}
}
/// <summary>
/// 新增、修改、刪除
/// 傳入參數對SQL資料做更動
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// 包含交易物件
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="connection">連線物件(為了確保同一個連線)</param>
/// <param name="tran">交易物件(為了確保同一筆交易)</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料異動數</returns>
public int ExcuteSQL(string sqlString, CommandType cmdType, ref HanaConnection connection, ref HanaTransaction tran, object[] paramsArr)
{
int num;
int result = 0;
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 = tran, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
try
{
result = cmd.ExecuteNonQuery();
SqlParameterOutputSetting(cmdParams);
num = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
return(num);
}
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();
}
}
private static HanaCommand PrepareCommand(string commandText
, HanaConnection connection
, CommandType cmdType = CommandType.StoredProcedure
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, HanaTransaction transaction = null)
{
HanaCommand command = new HanaCommand();
command.Connection = connection;
command.CommandType = cmdType;
command.CommandText = commandText;
if (transaction != null)
{
command.Transaction = transaction;
}
if (parameter != null)
{
if (parameters == null)
{
parameters = new List <HanaParameter>();
}
parameters.Add(parameter);
}
if (parameters != null)
{
foreach (HanaParameter x in parameters)
{
command.Parameters.Add(x);
}
}
return(command);
}
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 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();
* }*/
}
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);
}
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 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 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 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();
* }*/
}
