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 void SetUp()
{
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
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 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 SAPMarcaQtyDS()
{
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_VENTAS_WEB_MARCAS";
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 void TestInsertOperationHanaShortTypes()
{
IScriptBuilder builder = new ScriptHanaBuilder();
var cliente = new Employee()
{
Id = 1, Name = "Moisés", Age = 25
};
var hanaConnection = new HanaConnection();
hanaConnection.ConnectionString = ConnectionStringReader.GetConnstring("hana");
hanaConnection.Open();
var trans = hanaConnection.BeginTransaction();
var createTableScript = builder.GetCreateTableCommand <Employee>();
hanaConnection.Execute(createTableScript);
hanaConnection.Insert <Employee>(cliente);
var employeeFromDatabase = hanaConnection.GetAll <Employee>();
Assert.AreEqual(1, employeeFromDatabase.Count());
hanaConnection.Execute("drop table \"Employee\"");
}
public ScopedConnection()
{
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(TestContext.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
public void SetUp()
{
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
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>
/// 新增、修改、刪除
/// 傳入參數對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);
}
private static HanaConnection OpenConnection()
{
HanaConnection connection = new HanaConnection();
connection.ConnectionString = Constantes.CNX_STRING;
connection.Open();
return(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();
}
}
static void Main(string[] args)
{
string hostName = "43875a52-8238-4efd-aaa1-19b0f9f714ee.hana.trial-us10.hanacloud.ondemand.com";
string userName = "******";
string password = "******";
string port = "443";
string connectionString = $"Server={hostName}:{port};UID={userName};PWD={password};encrypt=true;sslValidateCertificate=false";
try
{
using (var conn = new HanaConnection(connectionString))
{
conn.Open();
Console.WriteLine("Connected");
//Create table
//var createTableQuery = $"alter table todo alter (\"NAME\" NVARCHAR(500));";
//using (var cmd = new HanaCommand(createTableQuery, conn))
//{
// int n = cmd.ExecuteNonQuery();
//}
// Querying table
var query = "SELECT ID, NAME From todo";
using (var cmd = new HanaCommand(query, conn))
using (var reader = cmd.ExecuteReader())
{
Console.WriteLine("Query result:");
var sbCol = new System.Text.StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
sbCol.Append(reader.GetName(i).PadRight(20));
}
Console.WriteLine(sbCol.ToString());
// Print rows
while (reader.Read())
{
var sbRow = new System.Text.StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
sbRow.Append(reader[i].ToString().PadRight(20));
}
Console.WriteLine(sbRow.ToString());
}
conn.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error - " + ex.Message);
Console.WriteLine(ex.ToString());
}
}
public CadastroPedido ConsultaUltimoPedido()
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroPedido _Retorno = new CadastroPedido();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaUltimoPedido, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
while (productInfoReader.Read())
{
var DocEntry = productInfoReader.GetString(0);
var GetPedido = Service.Get($"Orders({DocEntry})");
_Retorno = JsonConvert.DeserializeObject <CadastroPedido>(GetPedido.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
//var GetJob = Service.Get($"JOB?$select=*&$filter=U_NEO_DocEntry eq '{DocEntry}'");
//var RetObjJob = Newtonsoft.Json.JsonConvert.DeserializeObject<CadastroJobs>(GetJob.Documento, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
//if (RetObjJob.value.ToList().Count > 0)
//{
// _Retorno.CadastroJob = new CadastroJob[RetObjJob.value.ToList().Count];
// _Retorno.CadastroJob = RetObjJob.value;
//}
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
public void SetUp()
{
if (!IntegrationTestOptions.Hana.IsEnabled)
{
Assert.Ignore();
}
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(TestContext.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
public CadastroPN ConsultaUltimoPN()
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroPN _Retorno = new CadastroPN();
try
{
//var RetLogin = Service.Login();
//if (!RetLogin.Sucesso)
//{
// Log.Error("Erro do Login SL");
//}
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaUltimoPN, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
while (productInfoReader.Read())
{
var CardCode = productInfoReader.GetString(0);
var GetPN = Service.Get($"BusinessPartners('{CardCode}')");
_Retorno = JsonConvert.DeserializeObject <CadastroPN>(GetPN.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
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 CadastroAtividade ConsultaUltimoAtividade()
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroAtividade _Retorno = new CadastroAtividade();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaUltimoAtividade, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
while (productInfoReader.Read())
{
var DocEntry = productInfoReader.GetString(0);
var CardName = productInfoReader.GetString(1);
var GetAtividade = Service.Get($"Activities({DocEntry})");
_Retorno = JsonConvert.DeserializeObject <CadastroAtividade>(GetAtividade.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
_Retorno.CardName = CardName;
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
public Porcentagem ConsultaPorcentagem(string etapa)
{
Porcentagem _Retorno = new Porcentagem();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaPorcentagem, Schema, etapa);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
if (productInfoReader.HasRows)
{
productInfoReader.Read();
string valor = productInfoReader.GetString(0);
_Retorno.Valor = valor;
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
}
return(_Retorno);
}
public string ConsultaCardCodePN(string Tipo, string Serie)
{
string _Retorno = string.Empty;
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaCardCodePN, Schema, Tipo, Serie);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
if (productInfoReader.HasRows)
{
productInfoReader.Read();
string Code = productInfoReader.GetString(0);
_Retorno = Code;
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
}
return(_Retorno);
}
//static void testInsertEf()
//{
// using (var context = new Entities1())
// {
// var tm_Master01 = new Tm_Master01();
// tm_Master01.Description = "ini di insert dari EF";
// context.Tm_Master01.Add(tm_Master01);
// context.SaveChanges();
// }
//}
static void testKoneksiBiasa()
{
HanaConnection conn = new HanaConnection("Server=$userHost$:$userPort$;UserName=$userName$;Password=$userPassword$");
conn.Open();
HanaCommand cmd = new HanaCommand("select 'Hello, World' from DUMMY", conn); // Exception
try
{
HanaDataReader reader = cmd.ExecuteReader();
reader.Read();
Console.WriteLine(reader.GetString(0));
}
catch (Exception e)
{
String errorMsg = e.ToString();
Console.WriteLine(errorMsg);
}
}
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 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;
}
}
private HanaConnection sapConn()
{
//using (SqlConnection conn = new SqlConnection())
//{
// conn.ConnectionString = sapCon;
// conn.Open();
// return conn;
//}
HanaConnection conn = new HanaConnection(sapCon);
try
{
conn.Open();
}
catch (HanaException ex)
{
MessageBox.Show(ex.Errors[0].Source + " : " +
ex.Errors[0].Message + " (" +
ex.Errors[0].NativeError.ToString() + ")",
"Failed to connect");
}
return(conn);
}
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 Vendedores ConsultaVendedorPedido()
{
Vendedores _Retorno = new Vendedores();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaVendedorPN, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
if (productInfoReader.HasRows)
{
productInfoReader.Read();
string Code = productInfoReader.GetString(0);
string Name = productInfoReader.GetString(1);
string Count = productInfoReader.GetString(2);
_Retorno.value = new Vendedor[int.Parse(Count)];
_Retorno.value[0] = new Vendedor();
_Retorno.value[0].Code = Code;
_Retorno.value[0].Name = Name;
int i = 1;
while (productInfoReader.Read())
{
Code = productInfoReader.GetString(0);
Name = productInfoReader.GetString(1);
_Retorno.value[i] = new Vendedor();
_Retorno.value[i].Code = Code;
_Retorno.value[i].Name = Name;
i++;
}
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
}
return(_Retorno);
}
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 Retorno InsereAtualizaPedido(CadastroPedido Pedido)
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
Retorno _Retorno = new Retorno();
try
{
if (Pedido.DocEntry == null)
{
#region Insere Pedido
//var ObjJob = Pedido.CadastroJob;
//Pedido.CadastroJob = null;
var Json = JsonConvert.SerializeObject(Pedido, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
_Retorno = Service.Add("Orders", Json);
#endregion
#region Insere Projeto Gerencial
if (_Retorno.Sucesso)
{
ProjetoGerencial projetoGerencial = new ProjetoGerencial();
projetoGerencial.ProjectName = Pedido.DocumentLines[0].ProjectCode;
projetoGerencial.StartDate = Pedido.DocDate;
projetoGerencial.DueDate = Pedido.DocDueDate;
projetoGerencial.BusinessPartner = Pedido.CardCode;
projetoGerencial.BusinessPartnerName = Pedido.CardName;
projetoGerencial.SalesEmployee = Pedido.SalesPersonCode;
projetoGerencial.FinancialProject = Pedido.DocumentLines[0].ProjectCode;
Json = JsonConvert.SerializeObject(projetoGerencial, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
var _RetornoProjeto = Service.Add("ProjectManagements", Json);
if (!_RetornoProjeto.Sucesso)
{
Log.Error("Erro ao Gerar Projeto Gerencial: " + _RetornoProjeto.Documento);
}
}
#endregion
#region Insere Pré LCM
if (_Retorno.Sucesso)
{
Modelos.Pedido.CadastroPedido GetObjPedido = JsonConvert.DeserializeObject <Modelos.Pedido.CadastroPedido>(_Retorno.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
foreach (var item in GetObjPedido.DocumentLines)
{
string U_NEO_Dt_Inicio = item.U_NEO_Dt_Inicio;
float? valor = item.UnitPrice;
float? valorTotal = 0;
string U_NEO_RecReceita = item.U_NEO_RecReceita;
string ItemCode = item.ItemCode;
string CardCode = GetObjPedido.CardCode;
string Projeto = item.ProjectCode;
int? DocNum = GetObjPedido.DocNum;
string Conta = string.Empty;
int? Filial = GetObjPedido.BPL_IDAssignedToInvoice;
if (!string.IsNullOrEmpty(U_NEO_Dt_Inicio))
{
if (!string.IsNullOrEmpty(U_NEO_RecReceita))
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaGrupoItem, Schema, ItemCode);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
if (productInfoReader.HasRows)
{
productInfoReader.Read();
string GrupoItem = productInfoReader.GetString(0);
if (GrupoItem.Equals("107"))
{
Conta = "3010201010098";
}
else
{
Conta = "3010201010099";
}
//3010201010098 - DIFERIMENTO - PRODUÇÃO - 107
//3010201010099 - DIFERIMENTO - MYND - 105
DateTime Data = new DateTime();
Data = DateTime.Parse(U_NEO_Dt_Inicio);
int TotalDias = 0;
DateTime ultimoDiaDoMes = new DateTime();
for (int i = 0; i < int.Parse(U_NEO_RecReceita); i++)
{
ultimoDiaDoMes = new DateTime(Data.Year, Data.Month, DateTime.DaysInMonth(Data.Year, Data.Month));
var qtddias = ultimoDiaDoMes.Day;
var diaAtual = Data.Day;
var restoDias = qtddias - diaAtual + 1;
TotalDias = TotalDias + qtddias - diaAtual + 1;
//Data = Data.AddDays(restoDias - 1);
Data = Data.AddDays(restoDias);
}
PreLCM preLCM = new PreLCM();
preLCM.JournalVoucher = new Journalvoucher();
preLCM.JournalVoucher.JournalEntry = new Journalentry();
preLCM.JournalVoucher.JournalEntry.JournalEntryLines = new Journalentryline[int.Parse(U_NEO_RecReceita) + 1];
preLCM.JournalVoucher.JournalEntry.ReferenceDate = DateTime.Now.ToString("yyyy-MM-dd");
preLCM.JournalVoucher.JournalEntry.Memo = "Pré Lançamento gerado pelo Site Pedido: " + DocNum;
Data = DateTime.Parse(U_NEO_Dt_Inicio);
for (int i = 0; i < int.Parse(U_NEO_RecReceita); i++)
{
ultimoDiaDoMes = new DateTime(Data.Year, Data.Month, DateTime.DaysInMonth(Data.Year, Data.Month));
var qtddias = ultimoDiaDoMes.Day;
var diaAtual = Data.Day;
var restoDias = qtddias - diaAtual + 1;
var ValorLCM = (valor / TotalDias) * restoDias;
valorTotal = valorTotal + ValorLCM;
preLCM.JournalVoucher.JournalEntry.DueDate = ultimoDiaDoMes.ToString("yyyy-MM-dd");
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i] = new Journalentryline();
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].AccountCode = Conta;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].Credit = ValorLCM;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].Debit = 0;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].BPLID = Filial;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].DueDate = ultimoDiaDoMes.ToString("yyyy-MM-dd");
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].LineMemo = "Pré Lançamento gerado pelo Site Pedido: " + DocNum;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[i].ProjectCode = Projeto;
Data = Data.AddDays(restoDias);
}
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)] = new Journalentryline();
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].AccountCode = "1010201010001";
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].Credit = 0;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].Debit = valorTotal;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].BPLID = Filial;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].ShortName = CardCode;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].DueDate = ultimoDiaDoMes.ToString("yyyy-MM-dd");
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].LineMemo = "Pré Lançamento gerado pelo Site Pedido: " + DocNum;
preLCM.JournalVoucher.JournalEntry.JournalEntryLines[int.Parse(U_NEO_RecReceita)].ProjectCode = Projeto;
var JsonLCM = JsonConvert.SerializeObject(preLCM, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
var _RetornoLCM = Service.Add("JournalVouchersService_Add", JsonLCM);
if (!_RetornoLCM.Sucesso)
{
Log.Error("Erro ao Gerar Pré LCM: " + _RetornoLCM.Documento);
}
}
}
}
}
}
}
}
}
#endregion
#region Insere Job
//if (_Retorno.Sucesso)
//{
// foreach (var itemJobb in ObjJob)
// {
// var RetObjPedido = Newtonsoft.Json.JsonConvert.DeserializeObject<CadastroPedido>(_Retorno.Documento, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
// itemJobb.U_NEO_DocEntry = RetObjPedido.DocEntry.ToString();
// itemJobb.U_NEO_DocType = "17";
// var JsonJob = Newtonsoft.Json.JsonConvert.SerializeObject(itemJobb, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
// var _RetornoJob = Service.Add("JOB", JsonJob);
// if (!_RetornoJob.Sucesso)
// {
// }
// }
//}
#endregion
}
else
{
#region Atualiza Pedido
//var ObjJob = Pedido.CadastroJob;
//Pedido.CadastroJob = null;
var Json = Newtonsoft.Json.JsonConvert.SerializeObject(Pedido, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
_Retorno = Service.Update($"Orders({Pedido.DocEntry})", Json);
#endregion
#region Insere Job
//if (_Retorno.Sucesso)
//{
// var GetJob = Service.Get($"JOB?$select=*&$filter=U_NEO_DocEntry eq '{Pedido.DocEntry}'");
// var RetObjJob = Newtonsoft.Json.JsonConvert.DeserializeObject<CadastroJobs>(GetJob.Documento, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
// if (RetObjJob.value.ToList().Count > 0)
// {
// foreach (var item in RetObjJob.value)
// {
// var _RetornoJob = Service.Delete($"JOB({item.DocEntry})");
// if (!_RetornoJob.Sucesso)
// {
// }
// }
// foreach (var itemJobb in ObjJob)
// {
// itemJobb.U_NEO_DocEntry = Pedido.DocEntry.ToString();
// itemJobb.U_NEO_DocType = "17";
// var JsonJob = Newtonsoft.Json.JsonConvert.SerializeObject(itemJobb, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
// var _RetornoJob = Service.Add("JOB", JsonJob);
// if (!_RetornoJob.Sucesso)
// {
// }
// }
// }
// else
// {
// foreach (var item in ObjJob)
// {
// item.U_NEO_DocEntry = Pedido.DocEntry.ToString();
// item.U_NEO_DocType = "17";
// var JsonJob = Newtonsoft.Json.JsonConvert.SerializeObject(item, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
// var _RetornoJob = Service.Add("JOB", JsonJob);
// if (!_RetornoJob.Sucesso)
// {
// }
// }
// }
//}
#endregion
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
public ContasReceber ConsultaContasReceber(string NomeCliente, string DataVencimentoDe, string DataVencimentoAte, string DataPagamentoDe, string DataPagamentoAte, string Projeto, string Status)
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
ContasReceber _Retorno = new ContasReceber();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
if (string.IsNullOrEmpty(Status))
{
Status = "Todos";
}
string Sql = string.Format(Properties.Resources.ConsultaContasReceber, Schema, NomeCliente, DataVencimentoDe, DataVencimentoAte, DataPagamentoDe, DataPagamentoAte, Projeto, Status);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
if (productInfoReader.HasRows)
{
productInfoReader.Read();
var pNomeCliente = productInfoReader.GetString(0);
var pParcela = productInfoReader.GetString(1);
var pValorParcela = productInfoReader.GetString(2);
var pNumeroNota = productInfoReader.GetString(3);
var pDataVencimento = productInfoReader.GetString(4);
var pDataPagamento = productInfoReader.GetString(5);
var pProjeto = productInfoReader.GetString(6);
var pStatus = productInfoReader.GetString(7);
var Count = productInfoReader.GetString(8);
_Retorno.value = new ContaReceber[int.Parse(Count)];
_Retorno.value[0] = new ContaReceber();
_Retorno.value[0].NomeCliente = pNomeCliente;
_Retorno.value[0].Parcela = pParcela;
_Retorno.value[0].ValorParcela = pValorParcela;
_Retorno.value[0].NumeroNota = pNumeroNota;
_Retorno.value[0].DataVencimento = pDataVencimento;
_Retorno.value[0].DataPagamento = pDataPagamento;
_Retorno.value[0].Projeto = pProjeto;
_Retorno.value[0].Status = pStatus;
int i = 1;
while (productInfoReader.Read())
{
_Retorno.value[i] = new ContaReceber();
pNomeCliente = productInfoReader.GetString(0);
pParcela = productInfoReader.GetString(1);
pValorParcela = productInfoReader.GetString(2);
pNumeroNota = productInfoReader.GetString(3);
pDataVencimento = productInfoReader.GetString(4);
pDataPagamento = productInfoReader.GetString(5);
pProjeto = productInfoReader.GetString(6);
pStatus = productInfoReader.GetString(7);
Count = productInfoReader.GetString(8);
_Retorno.value[i].NomeCliente = pNomeCliente;
_Retorno.value[i].Parcela = pParcela;
_Retorno.value[i].ValorParcela = pValorParcela;
_Retorno.value[i].NumeroNota = pNumeroNota;
_Retorno.value[i].DataVencimento = pDataVencimento;
_Retorno.value[i].DataPagamento = pDataPagamento;
_Retorno.value[i].Projeto = pProjeto;
_Retorno.value[i].Status = pStatus;
i++;
}
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
public CadastroAtividades ConsultaAtividade(CadastroAtividade atividade)
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroAtividades _Retorno = new CadastroAtividades();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaAtividade, Schema, atividade.CardCode, atividade.CardName, atividade.ActivityCode);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
if (productInfoReader.HasRows)
{
productInfoReader.Read();
var DocEntry = productInfoReader.GetString(0);
var CardName = productInfoReader.GetString(1);
var Count = productInfoReader.GetString(2);
var GetPN = Service.Get($"Activities({DocEntry})");
var _RetornoAtividade = JsonConvert.DeserializeObject <CadastroAtividade>(GetPN.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
_RetornoAtividade.CardName = CardName;
_Retorno.value = new CadastroAtividade[int.Parse(Count)];
_Retorno.value[0] = new CadastroAtividade();
_Retorno.value[0] = _RetornoAtividade;
int i = 1;
while (productInfoReader.Read())
{
_Retorno.value[i] = new CadastroAtividade();
DocEntry = productInfoReader.GetString(0);
CardName = productInfoReader.GetString(1);
GetPN = Service.Get($"Activities({DocEntry})");
_RetornoAtividade = JsonConvert.DeserializeObject <CadastroAtividade>(GetPN.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
_RetornoAtividade.CardName = CardName;
_Retorno.value[i] = _RetornoAtividade;
i++;
}
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}