public string createTable(List <TableField> fields, Sql oSql)
{
//var commandStr = "If not exists (select name from sysobjects where name = 'Customer') CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime)";
//using (SqlCommand command = new SqlCommand(commandStr, con))
// command.ExecuteNonQuery();
string tableName = "table_" + GenerateName(10);
var commandStr = "IF NOT EXISTS (select name from sysobjects where name = '" + tableName + "') CREATE TABLE " + tableName + "(";
int x = 0;
foreach (var field in fields)
{
commandStr += (x > 0 ? "," : "") + field.ToString();
x++;
}
commandStr += ")";
//Console.WriteLine(commandStr);
SqlResultWithDataSet oResult = oSql.Open(commandStr,
CommandType.Text
);
return(tableName);
}
public string getListEmpresas()
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
var strSql = "Select * from Establecimientos";
SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text);
string json = null;
List <PlantillaExcel> fields = new List <PlantillaExcel>();
try
{
foreach (DataRow row in oResult.Results.Tables[0].Rows)
{
PlantillaExcel field = new PlantillaExcel(row["EstablecimientoID"].ToString(), "");
field.descripcion = row["NombreEstablecimiento"].ToString();
fields.Add(field);
}
json = JsonConvert.SerializeObject(fields);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(json);
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="Items.Results.SqlResultWithDataSet"/>
/// <seealso cref="Sql.Statement"/>
public Boolean TryOpen(Statement aStatement,
out SqlResultWithDataSet aResult)
{
// Original:
// return TryOpen(aStatement, CreateConnection(), out aResult);
SqlConnection oConnection = null;
Boolean bReturnValue = true;
aResult = null;
try
{
oConnection = CreateConnection();
}
catch (Exception aException)
{
bReturnValue = false;
aResult = new SqlResultWithDataSet {
Exception = aException, Statistics = null, Results = null
};
}
finally
{
if (bReturnValue)
{
bReturnValue = TryOpen(aStatement, oConnection, out aResult);
}
}
return(bReturnValue);
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param>
/// <param name="aConnection">A SQL Connection object</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="Items.Results.SqlResultWithDataSet"/>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
public Boolean TryOpen(Statement aStatement,
SqlConnection aConnection,
Boolean aCloseConnection,
out SqlResultWithDataSet aResult)
{
return(TryOpen(aStatement, aConnection, aCloseConnection, null, out aResult));
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param>
/// <param name="aConnection">A SQL Connection object</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <param name="aTransaction">The SqlTransaction object that this connection is part of</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="Items.Results.SqlResultWithDataSet"/>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
/// <seealso cref="System.Data.SqlClient.SqlTransaction"/>
public Boolean TryOpen(Statement aStatement,
SqlConnection aConnection,
Boolean aCloseConnection,
SqlTransaction aTransaction,
out SqlResultWithDataSet aResult)
{
// Assign default values
aResult = new Items.Results.SqlResultWithDataSet();
bool bCompleted;
try
{
// Open and execute
aResult = Open(aStatement, aConnection, aCloseConnection, aTransaction);
bCompleted = true;
}
catch (Exception e)
{
bCompleted = false;
aResult.Exception = e;
}
// Done
return(bCompleted);
}
/*
* public string sendJsonSqlMessage(XmppClient client, string message)
* {
*
* return "";
* }
*/
public string getJsonSqlMessage(string sqlMessage)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
var strSql = sqlMessage;
SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text);
string json = null;
try
{
json = JsonConvert.SerializeObject(oResult.Results.Tables[0]);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(json);
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param>
/// <param name="aCommandType">The type of SQL statement being passed</param>
/// <param name="aParameters">A list of parameters that will be used by the statement</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="System.Data.CommandType"/>
/// <seealso cref="Items.Results.SqlResultWithDataSet"/>
/// <seealso cref="System.Data.SqlClient.SqlParameter"/>
/// <seealso cref="System.Collections.Generic.IEnumerable{T}"/>
public Boolean TryOpen(String aSqlStatement,
CommandType aCommandType,
out SqlResultWithDataSet aResult,
params SqlParameter[] aParameters)
{
return(TryOpen
(
aSqlStatement,
aCommandType,
aParameters == null ? null : aParameters.AsEnumerable(),
out aResult
));
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param>
/// <param name="aCommandType">The type of SQL statement being passed</param>
/// <param name="aParameters">A list of parameters that will be used by the statement</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="System.Data.CommandType"/>
/// <seealso cref="Items.Results.SqlResultWithDataSet"/>
/// <seealso cref="System.Data.SqlClient.SqlParameter"/>
/// <seealso cref="System.Collections.Generic.IEnumerable{T}"/>
public Boolean TryOpen(String aSqlStatement,
CommandType aCommandType,
IEnumerable <SqlParameter> aParameters,
out SqlResultWithDataSet aResult)
{
return(TryOpen
(
new Statement
{
Sql = aSqlStatement,
Type = aCommandType,
Parameters = aParameters
},
out aResult
));
}
public bool deletePlantillaExcel(string id)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
SqlResultWithDataSet oResult = oSql.Open("Delete from PlantillasProcesadas Where plantillaProcesadaID = @id",
CommandType.Text,
new SqlParameter("id", id)
);
return(true);
}
public string getPlantilla(string id)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
var strSql = "SELECT tp.nombre, p.* FROM PlantillasProcesadas p Left Join tipoPlantillas tp On tp.tipoPlantillaID = p.tipoPlantillaID Where plantillaProcesadaID='" + id + "'";
SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text);
string json = null;
List <PlantillaExcel> fields = new List <PlantillaExcel>();
try
{
if (oResult.Results.Tables[0].Rows.Count > 0)
{
DataRow row = oResult.Results.Tables[0].Rows[0];
PlantillaExcel field = new PlantillaExcel(row["plantillaProcesadaID"].ToString().Trim(), row["usuarioID"].ToString());
field.estado = row["estado"].ToString() == "2" ? "Procesado" : (row["estado"].ToString() == "1" ? "Procesando" : "No Procesado");
field.tipo_nombre = row["nombre"].ToString();
field.tipo = row["tipoPlantillaID"].ToString().Trim();
field.archivo = row["rutaArchivo"].ToString();
field.fecha_creacion = row["fechaCarga"].ToString();
field.fecha_proceso = row["fechaProceso"].ToString();
//field.descripcion = "xxxx";
fields.Add(field);
json = JsonConvert.SerializeObject(field);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(json);
}
/// <summary>
/// Executes a SQL statement and returns the value of the statement
/// </summary>
/// <param name="aStatement">The SQL statement structure to execute</param>
/// <param name="aConnection">The connection to use for this SQL statement</param>
/// <param name="aCloseConnection">If true, the connection will be closed after execution</param>
/// <param name="aTransaction">The SqlTransaction object to use during execution</param>
/// <returns>An object of type <typeparam name="T">T</typeparam></returns>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
public SqlResultWithValue <T> Execute <T>(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction)
{
// Call .Open() and return the first row of the first column.
SqlResultWithDataSet oResults = Open(aStatement, aConnection, aCloseConnection, aTransaction);
DataTable oTable = oResults.Results.Tables[0]; // Open(aStatement).Tables[0];
// Create our T variable
T oRetval = default(T);
// Now that we have the table, grab the first row and first value
if (oTable.Rows.Count > 0 && oTable.Rows[0].ItemArray.Any())
{
oRetval = (T)Convert.ChangeType(oTable.Rows[0][0], typeof(T));
}
// Return!
return(new SqlResultWithValue <T>()
{
Exception = null, Value = oRetval, Statistics = oResults.Statistics
});
}
public string getEmpresa(string id)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
var strSql = "SELECT * From Establecimientos Where EstablecimientoID='" + id + "'";
SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text);
string json = null;
List <Item> fields = new List <Item>();
try
{
if (oResult.Results.Tables[0].Rows.Count > 0)
{
DataRow row = oResult.Results.Tables[0].Rows[0];
Item field = new Item();
field.id = row["EstablecimientoID"].ToString();
field.descripcion = row["NombreEstablecimiento"].ToString();
fields.Add(field);
json = JsonConvert.SerializeObject(field);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(json);
}
/// <summary>
/// Performs an asynchronous query with a single return value against a database
/// </summary>
/// <typeparam name="T">The return type expected as a result of the query</typeparam>
/// <param name="aStatement">The SQL statement structure to execute</param>
/// <param name="aConnection">The connection to use for this SQL statement</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aTransaction">The current transaction that this command should be executed on</param>
/// <returns>A Task object that represents the asynchronous request</returns>
/// <seealso cref="System.Threading.Tasks.Task{T}"/>
/// <seealso cref="SqlMagic.Monitor.Items.Results.SqlResultWithValue{T}"/>
/// <seealso cref="namespace SqlMagic.Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
/// <seealso cref="System.Data.SqlClient.SqlTransaction"/>
public async Task <SqlResultWithValue <T> > ExecuteAsync <T>(Statement aStatement,
SqlConnection aConnection,
Boolean aCloseConnection,
SqlTransaction aTransaction)
{
// Make a call to OpenAsync to return a dataset.
SqlResultWithDataSet oDataSet = await OpenAsync(aStatement, aConnection, aCloseConnection, aTransaction);
// Create a result w/ value opbject
SqlResultWithValue <T> oResult = new SqlResultWithValue <T>();
// Assign stuff manually
oResult.Exception = oDataSet.Exception;
oResult.Statistics = oDataSet.Statistics;
// Check for success+actual results
if (oDataSet.Success && oDataSet.Results.Tables[0].Rows.Count > 0)
{
oResult.Value = (T)Convert.ChangeType(oDataSet.Results.Tables[0].Rows[0][0], typeof(T));
}
// All done
return(oResult);
}
public bool authenticateUser(string user, string password)
{
try
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
SqlResultWithDataSet oResult = oSql.Open("SELECT * FROM Usuarios WHERE NombreUsuario = @user And Clave = @password",
CommandType.Text,
new SqlParameter("user", user),
new SqlParameter("password", password)
);
return(oResult.Results.Tables[0].Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
return(false);
}
}
public DataTable getContabilidadEstadoResultadosDataTable(Request req, Response res)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
/*
* exec UCO_Mayorizados_Select_PorMes_ComparativoPorHotel @TipoFormatoGGPPID=2,@TipoCuentaID=0,@Ejercicio=2017,
* @NivelID=0,@HotelID=0,@EstablecimientoID=112,@AreaID=0,@UnidadNegocioID=0,
* @Periodo='PeriodoID<=12',@OrderByExpression='',@FormatoReporte=2
*/
//Where Statement
int HotelID = req.PathVariables["HotelID"] != null?Convert.ToInt32(req.PathVariables["HotelID"]) : 0;
columns = new List <Column>();
_columns = new List <Column>();
columns.Add(new Column("Descripcion", "Descripcion"));
if (HotelID > 0)
{
var strSqlC = @"Select * from Hoteles Where HotelID=@HotelId";
SqlResultWithDataSet oResult1 = oSql.Open(strSqlC, CommandType.Text,
new SqlParameter("HotelId", HotelID)
);
columns.Add(new Column((oResult1.Results.Tables[0].Rows.Count == 1 ? oResult1.Results.Tables[0].Rows[0]["NombreHotel"].ToString().Trim() : ""), "H1"));
}
else
{
columns.Add(new Column("Todos", "Todos"));
columns.Add(new Column("SALKANTAY", "H1"));
columns.Add(new Column("LARES", "H2"));
columns.Add(new Column("EL MERCADO", "H3"));
columns.Add(new Column("VIÑAK", "H4"));
columns.Add(new Column("CORPORATIVO", "H5"));
columns.Add(new Column("HUACAHUASI", "H6"));
columns.Add(new Column("OFICINA CUSCO", "H7"));
}
foreach (Column col in columns)
{
_columns.Add(new Column("", col.accessor));
}
if (req.PathVariables["mes"] == null || req.PathVariables["tipo"] == null || req.PathVariables["moneda"] == null)
{
return(new DataTable());
}
int Ejercicio = req.PathVariables["periodo"] != null?Convert.ToInt32(req.PathVariables["periodo"]) : 2017;
int EstablecimientoID = req.PathVariables["empresa"] != null?Convert.ToInt32(req.PathVariables["empresa"]) : 112;
int Periodo = req.PathVariables["mes"] != null?Convert.ToInt32(req.PathVariables["mes"]) : 13;
int TipoFormato = req.PathVariables["TipoFormatoGGPPID"] != null?Convert.ToInt32(req.PathVariables["TipoFormatoGGPPID"]) : 2;
string Moneda = req.PathVariables["moneda"] != null ? req.PathVariables["moneda"] : "USD"; //Soles
string sTipo = req.PathVariables["tipo"] != null ? req.PathVariables["tipo"] : "mensual"; //0
int Tipo = sTipo == "mensual" ? 1 : 0;
bool isSoles = (Moneda == "USD" ? false : true);
/*
* exec UCO_Mayorizados_Select_PorMes_ComparativoPorHotel @TipoFormatoGGPPID=2,@TipoCuentaID=0,@Ejercicio=2018,@NivelID=0,@HotelID=0,@EstablecimientoID=112,@AreaID=0,@UnidadNegocioID=0,@Periodo='PeriodoID<=5',@OrderByExpression='',@FormatoReporte=2
*/
var strSql = @"exec UCO_Mayorizados_Select_PorMes_ComparativoPorHotel @TipoFormatoGGPPID=" + TipoFormato + @",@TipoCuentaID= 0,@Ejercicio=" + Ejercicio + @",
@NivelID = 0,@HotelID = " + HotelID + @",@EstablecimientoID = " + EstablecimientoID + @",@AreaID = 0,@UnidadNegocioID = 0,
@Periodo = 'PeriodoID " + (Tipo == 1?"=":"<=") + Periodo + "',@OrderByExpression = '',@FormatoReporte = 2";
SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text);
return(oResult.Results.Tables[0]);
}
public string createPlantilla(string json)
{
createFilePlantilla plantilla = new createFilePlantilla();
try
{
plantilla = JsonConvert.DeserializeObject <createFilePlantilla>(json);
}catch (Exception ex)
{
Console.WriteLine("JsonConvert" + ex.Message);
}
try
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
SqlResultWithDataSet oResult = oSql.Open("Select * from correlativos c Where nombretabla = 'PlantillasProcesadas'",
CommandType.Text
);
var plantillaProcesadaID = oResult.Results.Tables[0].Rows.Count == 1 ? oResult.Results.Tables[0].Rows[0]["numerogenerado"].ToString().Trim() : "0";
// string sql = "INSERT INTO PlantillasProcesadas (plantillaProcesadaID, tipoPlantillaID, rutaArchivo, fechaCarga, usuarioID, estado) VALUES("+ (Convert.ToInt32(plantillaProcesadaID) + 1) + ", '"+ plantilla.tipo + "', '"+ plantilla.tipo + "', @fecha, 1, '0'); "
oResult = oSql.Open("INSERT INTO PlantillasProcesadas (plantillaProcesadaID, tipoPlantillaID, rutaArchivo, fechaCarga, usuarioID, estado) VALUES(@id, @tipo, @ruta, GETDATE(), 1, '0'); ",
CommandType.Text,
new SqlParameter("id", Convert.ToInt32(plantillaProcesadaID) + 1),
new SqlParameter("tipo", plantilla.tipo),
new SqlParameter("ruta", plantilla.files[0].title)
);
oResult = oSql.Open("Update correlativos Set numerogenerado = @corr Where correlativoid = @id",
CommandType.Text,
new SqlParameter("id", 3103),
new SqlParameter("corr", Convert.ToInt32(plantillaProcesadaID) + 1)
);
plantilla.id = plantillaProcesadaID;
}catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(JsonConvert.SerializeObject(plantilla));
/*SELECT TOP 1000 [plantillaProcesadaID]
* ,[fechaCarga]
* ,[horaCarga]
* ,[tipoPlantillaID]
* ,[usuarioID]
* ,[rutaArchivo]
* ,[fechaProceso]
* ,[horaProceso]
* ,[usuarioIDProcesa]
* ,[estado]
* FROM[SHIOL_DATOS].[dbo].[PlantillasProcesadas]
*/
}
public bool processPlantillaExcel(string id)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
PlantillaExcel plantilla = getPlantillaField(id);
if (plantilla == null)
{
return(false);
}
string path = ShiolConfiguration.Instance.Config.DirPlantillas; //ConfigurationManager.AppSettings["dirPlantillas"]; //@"D:\SGH_Sistemas\";
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
string file = Path.Combine(path, plantilla.archivo); // @"D:\SGH_Sistemas\ShiolExcelService\ShiolWinSvc\FastExcel\FastExcelDemo\bin\Debug\SHIOL 1-31 FINAL.XLSX";
if (!File.Exists(file))
{
Console.WriteLine("Archivo : " + file + " no existe");
return(false);
}
List <TableField> fields = getExcelStructure(file);
if (fields.Count > 0)
{
string TableName = createTable(fields, oSql);
Console.WriteLine("Generated : " + TableName);
createRecords(file, fields, TableName, oSql);
SqlResultWithDataSet oResult = oSql.Open("USP_Plantilla_Excel_CargaCuentasPorPagar_RH @nombreTabla = '" + TableName + "';",
CommandType.Text
);
/*
* oResult = oSql.Open("IF OBJECT_ID('dbo."+TableName+"', 'U') IS NOT NULL DROP TABLE dbo."+TableName+"; ",
* CommandType.Text
* );
*/
oResult = oSql.Open("Update PlantillasProcesadas Set estado = 2 Where plantillaProcesadaID = @id",
CommandType.Text,
new SqlParameter("id", id)
);
Console.WriteLine("Processed : " + TableName);
return(true);
}
else
{
Console.WriteLine("No Processed...");
return(false);
}
}
public string getListExcelFiles(Request req, Response res)
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
/*SELECT TOP 1000 [plantillaProcesadaID]
* ,[fechaCarga]
* ,[horaCarga]
* ,[tipoPlantillaID]
* ,[usuarioID]
* ,[rutaArchivo]
* ,[fechaProceso]
* ,[horaProceso]
* ,[usuarioIDProcesa]
* ,[estado]
* FROM[SHIOL_DATOS].[dbo].[PlantillasProcesadas]
*/
int Estado = req.PathVariables["estado"] != null?Convert.ToInt32(req.PathVariables["estado"]) : -1;
int Tipo = req.PathVariables["tipo"] != null?Convert.ToInt32(req.PathVariables["tipo"]) : -1;
string FechaCreacion = req.PathVariables["fecha_creacion"] != null ? req.PathVariables["fecha_creacion"].Substring(0, 10) : "";
string strWhere = "";
if (Tipo > -1)
{
strWhere = "Where " + "p.tipoPlantillaID" + "=" + Tipo;
}
if (Estado > -1)
{
strWhere += strWhere == "" ? "Where p.Estado ='" + Estado + "' " : " And p.Estado ='" + Estado + "' ";
}
if (FechaCreacion != "")
{
strWhere += strWhere == "" ? "Where FORMAT(fechaCarga, 'yyyy-MM-dd') ='" + FechaCreacion + "' " : " And FORMAT(fechaCarga, 'yyyy-MM-dd') ='" + FechaCreacion + "' ";
}
//OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
string strCount = "0";
SqlResultWithDataSet oResult = null;
try
{
var strSql = "SELECT count(*) as _total FROM PlantillasProcesadas p Left Join tipoPlantillas tp On tp.tipoPlantillaID = p.tipoPlantillaID " + strWhere;
oResult = oSql.Open(strSql, CommandType.Text);
strCount = oResult.Results.Tables[0].Rows[0]["_total"].ToString();
strSql = "SELECT tp.nombre, p.* FROM PlantillasProcesadas p Left Join tipoPlantillas tp On tp.tipoPlantillaID = p.tipoPlantillaID " + strWhere + " ORDER by p.fechaCarga DESC OFFSET " + req.PathVariables["_start"] + " ROWS FETCH NEXT " + (Convert.ToInt32(req.PathVariables["_end"]) - Convert.ToInt32(req.PathVariables["_start"])) + " ROWS ONLY;";
oResult = oSql.Open(strSql, CommandType.Text);
}catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
string json = null;
List <PlantillaExcel> fields = new List <PlantillaExcel>();
try
{
foreach (DataRow row in oResult.Results.Tables[0].Rows)
{
PlantillaExcel field = new PlantillaExcel(row["plantillaProcesadaID"].ToString().Trim(), row["usuarioID"].ToString());
field.estado = row["estado"].ToString() == "2" ? "Procesado" : (row["estado"].ToString() == "1" ? "En Proceso" : "No Procesado");
field.tipo_nombre = row["nombre"].ToString();
field.tipo = row["tipoPlantillaID"].ToString();
field.archivo = row["rutaArchivo"].ToString();
field.fecha_creacion = row["fechaCarga"].ToString();
field.fecha_proceso = row["fechaProceso"].ToString();
fields.Add(field);
}
json = JsonConvert.SerializeObject(fields);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
res.httpListenerResponse.Headers.Add("Access-Control-Expose-Headers", "X-Total-Count");
res.httpListenerResponse.Headers.Add("X-Total-Count", strCount);
return(json);
}
static void TranferenciaVentas()
{
DateTime dateDesde = new DateTime(2016, 01, 01);
DateTime dateHasta = new DateTime(2016, 12, 30);
String Usuario = "5093";
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
//var connectionString1 = "user id=sa;password=server$123$;data source=localhost;initial catalog=SHIOL_DATOS_MLP;Connection Timeout=30;";
var connectionString = ConfigurationManager.ConnectionStrings["cadena"].ConnectionString;
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
Console.WriteLine("Start : " + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff"));
var watch = Stopwatch.StartNew();
var strSql = "Select valor from ParametrosGenerales where parametrogeneralID = '2010'";
SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text);
var EstablecimientoID = oResult.Results.Tables[0].Rows.Count == 1 ? oResult.Results.Tables[0].Rows[0]["valor"].ToString().Trim() : "";
var Periodo = dateDesde.Year.ToString();
var NombreTablaCabecera = "ACC_" + EstablecimientoID + "_" + Periodo;
var NombreTablaDetalle = "ACD_" + EstablecimientoID + "_" + Periodo;
//Deleting Header and Detail Tables
oSql.Open("Delete from " + NombreTablaCabecera + ";"
+ "Delete from " + NombreTablaDetalle,
CommandType.Text
);
// Creating Local Virtual Tables Header and Detail
oResult = oSql.Open(@"SET FMTONLY ON;
Select TOP 1 * from " + NombreTablaCabecera + ";" +
"Select TOP 1 * from " + NombreTablaDetalle + "; SET FMTONLY OFF;",
CommandType.Text
);
DataTable tablaHeader = new DataTable();
DataTable tablaDetail = new DataTable();
if (oResult.Results.Tables.Count < 2)
{
Console.WriteLine("No se encuantran las tablas:" +
NombreTablaCabecera + " o " + NombreTablaDetalle +
" de este periodo en la base de datos");
}
else
{
tablaHeader = oResult.Results.Tables[0];
tablaHeader.TableName = "ACC";
tablaDetail = oResult.Results.Tables[1];
tablaDetail.TableName = "ACD";
}
SqlResultWithDataSet oResult1 = oSql.Open("UCOTRA_VENTAS_SQL",
CommandType.StoredProcedure,
new SqlParameter("Desde", dateDesde.ToString("dd-MM-yyyy")),
new SqlParameter("Hasta", dateHasta.ToString("dd-MM-yyyy")),
new SqlParameter("UsuarioID", "0"),
new SqlParameter("Movimiento", "0"),
new SqlParameter("IPEquipo", "0"),
new SqlParameter("UsuarioactualEquipo", "0"),
new SqlParameter("NombreEquipo", "0")
);
DataSet dsTables = oResult1.Results;
//DataSet dataSet = oResult.Results;
dsTables.Tables[0].TableName = "Documentos";
DataTable tablaDocumentos = dsTables.Tables[0];
dsTables.Tables[1].TableName = "PlanDeCuentas";
DataTable tablaPlanDeCuentas = dsTables.Tables[1];
dsTables.Tables[2].TableName = "DetalleEventos";
DataTable tablaDetalleEventos = dsTables.Tables[2];
/*
* //Establecer Relaciones
* DataRelation planDeCuentasRel = dsTables.Relations.Add("PlanDeCuenta",
* new DataColumn[] { dsTables.Tables["Documentos"].Columns["EstablecimientoID1"] , dsTables.Tables["Documentos"].Columns["PlanCuentaID"] },
* new DataColumn[] { dsTables.Tables["PlanDeCuentas"].Columns["EstablecimientoID"], dsTables.Tables["PlanDeCuentas"].Columns["PlanCuentaID"]}, false);
*/
// Console.WriteLine("Records : " + tablaDocumentos.Rows.Count.ToString());
/******************************************
*
* Aquí empieza propiamente el proceso
*
******************************************/
//Correlativos Para Formato de Comprobante Cabecera
Int32 RegistroID = 0; // CorrelativoIDMensual = 0;
Int32 CorrelativoIDMensualComprobanteAsiento = 0;
Double TotalDebeCuadre = 0.00;
Double TotalHaberCuadre = 0.00;
Double TotalDebeSolesCuadre = 0.00;
Double TotalDebeDolaresCuadre = 0.00;
Double TotalHaberSolesCuadre = 0.00;
Double TotalHaberDolaresCuadre = 0.00;
Int32 AsientoContableID = 0;
Int32 AsientoContableDetalleID = 0;
String PlanCuentaID = "";
foreach (DataRow rowDocumento in dsTables.Tables["Documentos"].Rows)
{
String DocumentoId = rowDocumento["DocumentoId"].ToString();
// Console.WriteLine(rowDocumento["TransaccionDocumentoId"] + " - " + rowDocumento["TipoDocumentoID"]);
Int32 EmpresaID = 0;
if (Convert.ToDouble(rowDocumento["IGV"].ToString()) == 0 && Convert.ToInt32(rowDocumento["Turista"].ToString()) == 1)
{
EmpresaID = Convert.ToInt32(rowDocumento["EmpresaIDTuristas"].ToString());
}
else
{
if (DocumentoId.Substring(0, 2) == "03" ||
DocumentoId.ToString().Substring(0, 2) == "123" ||
DocumentoId.ToString().Substring(0, 2) == "12B")
{
EmpresaID = Convert.ToInt32(rowDocumento["EmpresaIDParticular"].ToString());
}
}
/*
* var intBack = (SqlResult)oSql.Execute("sp_ACC_ExisteRegistro",
* CommandType.StoredProcedure, [
* new SqlParameter("NombreTabla", NombreTabla),
* new SqlParameter("ReferenciaSHIOL", rowDocumento["TransaccionDocumentoID"].ToString().Trim()),
* new SqlParameter("TablaMovimientoID", rowDocumento["TablaMovimientoID"].ToString().Trim())
* ]
* );
*/
var TablaMovimientoID = "137"; //Indica que tipo de provision de venta se Hace
/*
* strSql = "Select Count(*) FROM " + NombreTabla
+ " Where ReferenciaSHIOL = '" + rowDocumento["TransaccionDocumentoID"].ToString().Trim() + "' "
+ " AND TablaMovimientoID = '" + TablaMovimientoID + "'";
+
+ oResult = oSql.Open(strSql, CommandType.Text);
+ var LPRegistrosExiste = oResult.Results.Tables[0].Rows.Count == 1 ? true : false;
+
+
+ Console.WriteLine(LPRegistrosExiste.ToString());
*/
//Console.WriteLine(rowDocumento["TransaccionDocumentoId"] + " - " + rowDocumento["TipoDocumentoID"]);
// Console.WriteLine(EmpresaID.ToString());
// Console.WriteLine(rowDocumento["TransaccionDocumentoId"].ToString());
if (AsientoContableID == 0)
{
var intBack = oSql.Execute <Int32>("USP_GeneraID",
CommandType.StoredProcedure,
new SqlParameter("lpTabla", "ASIENTOSCONTABLES_" + Periodo),
new SqlParameter("Codigo", "0"));
AsientoContableID = intBack.Value;
}
else
{
AsientoContableID++;
}
if (AsientoContableDetalleID == 0)
{
var intBack = oSql.Execute <Int32>("USP_GeneraID",
CommandType.StoredProcedure,
new SqlParameter("lpTabla", "AsientosContablesDetalle_" + Periodo),
new SqlParameter("Codigo", "0"));
AsientoContableDetalleID = intBack.Value;
}
else
{
AsientoContableDetalleID++;
}
String CodigoComprobante = "";
if (rowDocumento["FormatoComprobante"].ToString().Trim() == "1")
{
if (RegistroID == 0)
{
//Console.WriteLine(rowDocumento["SubDiarioID"].ToString());
//Console.WriteLine(rowDocumento["FechaEmision"].ToString());
var intBack = oSql.Execute <Int32>("USP_GeneraIDMensual",
CommandType.StoredProcedure,
new SqlParameter("MemoID", "0"),
new SqlParameter("UsuarioID", "0"),
new SqlParameter("Formato", rowDocumento["SubDiarioID"].ToString()),
new SqlParameter("Dia", rowDocumento["FechaEmision"].ToString()),
new SqlParameter("Codigo", "0")
);
RegistroID = intBack.Value;
}
else
{
RegistroID++;
}
DateTime FechaEmision = DateTime.Parse(rowDocumento["FechaEmision"].ToString());
CodigoComprobante = (FechaEmision.Month < 10 ? "0" : "")
+ FechaEmision.Month.ToString().Trim()
+ rowDocumento["HotelIDformato"].ToString()
+ rowDocumento["Localidad_CODIGO"].ToString()
+ "0000"
+ RegistroID.ToString().PadLeft(5, '0').Substring(RegistroID.ToString().PadLeft(5, '0').Length - 5, 5);
// AsientoContableID = RegistroID;
}
else
{
if (CorrelativoIDMensualComprobanteAsiento == 0)
{
var intBack = oSql.Execute <Int32>("UCO_GeneraIDMensualComprobanteAsiento",
CommandType.StoredProcedure,
new SqlParameter("Periodo", rowDocumento["Periodo"].ToString()),
new SqlParameter("Mensual", rowDocumento["Mes"].ToString()),
new SqlParameter("EstablecimientoID", rowDocumento["EstablecimientoID"].ToString()),
new SqlParameter("HotelID", rowDocumento["HotelID"].ToString()),
new SqlParameter("SubDiarioID", rowDocumento["SubDiarioID"].ToString()),
new SqlParameter("Codigo", "0")
);
CorrelativoIDMensualComprobanteAsiento = intBack.Value;
}
else
{
CorrelativoIDMensualComprobanteAsiento++;
}
AsientoContableID = CorrelativoIDMensualComprobanteAsiento;
}
// Insertar en Tabla de Cabecera
AddHeaderTable(AsientoContableID, CodigoComprobante, TablaMovimientoID, tablaHeader, rowDocumento);
//Insertar Detalle Step 001
Double Total = Convert.ToDouble(rowDocumento["total"]);
Double MontoDebe = Total > 0 ? Math.Round(Total, 2) : 0.00;
Double MontoHaber = Total > 0 ? 0.00 : Math.Round(Total, 2);
String Referencia = rowDocumento["Cliente"].ToString();
Double ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
Double ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2);
String AsientoContableDetallIDTransferencia = "0";
TotalDebeCuadre += MontoDebe;
TotalHaberCuadre += MontoHaber;
if (MontoDebe > 0)
{
TotalDebeSolesCuadre += ImporteSoles;
TotalDebeDolaresCuadre += ImporteDolares;
}
else
{
TotalHaberSolesCuadre += ImporteSoles;
TotalHaberDolaresCuadre += ImporteDolares;
}
PlanCuentaID = rowDocumento["PlanCuentaID"].ToString();
MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["Total"].ToString());
MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["Total"].ToString()) : 0.00;
ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2);
AddDetailTable(rowDocumento["Cliente"].ToString(), PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
if (Convert.ToInt32(rowDocumento["Estado"].ToString()) != 3)
{
if (Convert.ToDouble(rowDocumento["IGV"].ToString()) != 0.00)
{
PlanCuentaID = rowDocumento["CuentaIGV"].ToString();
Referencia = rowDocumento["Cliente"].ToString();
MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["IGV"].ToString());
MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["IGV"].ToString()) : 0.00;
ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2);
AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
}
if (Convert.ToDouble(rowDocumento["Servicio"].ToString()) != 0.00)
{
PlanCuentaID = rowDocumento["CuentaServicio"].ToString();;
Referencia = rowDocumento["Cliente"].ToString();
MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["Servicio"].ToString());
MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["Servicio"].ToString()):0.00;
ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2);
AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
}
if (Convert.ToDouble(rowDocumento["Propina"].ToString()) != 0.00)
{
PlanCuentaID = rowDocumento["CuentaPropina"].ToString();
Referencia = rowDocumento["Cliente"].ToString();
MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["Propina"].ToString());
MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["Propina"].ToString()) : 0.00;
ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2);
AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
}
if (Convert.ToInt32(rowDocumento["Prepago"].ToString()) == 1 && Convert.ToInt32(rowDocumento["PrepagosNoConsiderarVentas"].ToString()) == 1)
{
Int32 MonedaId = Convert.ToInt32(rowDocumento["MonedaId"].ToString());
if (Convert.ToInt32(rowDocumento["Vinculada"].ToString()) == 1 &&
(DocumentoId.Substring(0, 2) == "01" || DocumentoId.Substring(0, 2) == "08") &&
Convert.ToInt32(rowDocumento["Turista"].ToString()) == 0)
{
if (MonedaId == 1)
{
PlanCuentaID = Convert.ToString(rowDocumento["PlanCuentaIDVinculadaAnticipoSoles"]);
}
else
{
PlanCuentaID = Convert.ToString(rowDocumento["PlanCuentaIDVinculadaAnticipoDolares"]);
}
}
else
{
if (MonedaId == 1)
{
PlanCuentaID = Convert.ToString(rowDocumento["FacturaCuentaSolesPrepago"]);
}
else
{
PlanCuentaID = Convert.ToString(rowDocumento["FacturaCuentaDolaresPrepago"]);
}
Referencia = "ANTICIPO";
//Console.WriteLine("ANTICIPO");
ImporteDolares = MonedaId == 1 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"].ToString()), 2);
ImporteSoles = MonedaId == 2 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"].ToString()), 2);
if (MontoDebe > 0)
{
; // Poner cuadre completar
}
else
{
}
Double Diferencia = Math.Round(TotalDebeCuadre - TotalHaberCuadre, 2);
Double DiferenciaSoles = Math.Round(TotalDebeSolesCuadre - TotalHaberSolesCuadre, 2);
Double DiferenciaDolares = Math.Round(TotalDebeDolaresCuadre - TotalHaberDolaresCuadre, 2);
if (Math.Abs(Diferencia) < 0.05 || Math.Abs(DiferenciaSoles) < 0.05 || Math.Abs(DiferenciaDolares) < 0.05)
{
}
//PlanCuentaID = Pla
// Añadir Aquí registro
// Console.WriteLine("Adding Record");
AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
}
}
else
{
//Console.WriteLine(" DETALLE DE EVENTOS");
var query = tablaDetalleEventos.AsEnumerable().Where(d => d.Field <String>("DocumentoId") == DocumentoId);
foreach (var row in query)
{
Int32 MonedaID = Convert.ToInt32(row["MonedaIDPaidOut"].ToString());
if (Convert.ToDouble(row["TotalPaidOut"].ToString()) != 0.00)
{
//Console.WriteLine(row["DocumentoId"].ToString());
PlanCuentaID = row["PlanCuentaID"].ToString();
Referencia = rowDocumento["Cliente"].ToString();
MontoHaber = Convert.ToDouble(rowDocumento["TotalPaidOut"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["TotalPaidOut"].ToString());
MontoDebe = Convert.ToDouble(rowDocumento["TotalPaidOut"].ToString()) > 0 ? 0.00 :Math.Round(Convert.ToDouble(row["TotalPaidOut"].ToString()), 2);
ImporteSoles = MonedaID == 1 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
ImporteDolares = MonedaID == 2 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2);
AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
}
MontoDebe = Convert.ToDouble(row["TotalTipoProducto"].ToString()) > 0 ? 0.00 : Math.Round(Convert.ToDouble(row["TotalTipoProducto"].ToString()), 2);
MontoHaber = Convert.ToDouble(row["TotalTipoProducto"].ToString()) < 0 ? 0.00 : Math.Round(Convert.ToDouble(row["TotalTipoProducto"].ToString()), 2);
Referencia = row["nombre"].ToString();
ImporteSoles = MonedaID == 1 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
ImporteDolares = MonedaID == 2 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) / Convert.ToDouble(rowDocumento["TipoCambio"]), 2);
Double Diferencia = Math.Round(TotalDebeCuadre - TotalHaberCuadre, 2);
Double DiferenciaSoles = Math.Round(TotalDebeSolesCuadre - TotalHaberSolesCuadre, 2);
Double DiferenciaDolares = Math.Round(TotalDebeDolaresCuadre - TotalHaberDolaresCuadre, 2);
if (Math.Abs(Diferencia) < 0.05 || Math.Abs(DiferenciaSoles) < 0.05 || Math.Abs(DiferenciaDolares) < 0.05)
{
if (MontoHaber > 0.00)
{
MontoHaber += Diferencia;
ImporteSoles += DiferenciaSoles;
ImporteDolares += DiferenciaDolares;
}
if (MontoDebe > 0.00)
{
if (Diferencia > 0)
{
Diferencia *= -1;
}
else
{
Diferencia = Math.Abs(Diferencia);
}
if (DiferenciaSoles > 0)
{
DiferenciaSoles *= -1;
}
else
{
DiferenciaSoles = Math.Abs(DiferenciaSoles);
}
if (DiferenciaDolares > 0.00)
{
DiferenciaDolares *= -1;
}
else
{
DiferenciaDolares = Math.Abs(DiferenciaDolares);
}
MontoDebe += Diferencia;
ImporteSoles += DiferenciaSoles;
ImporteDolares += DiferenciaDolares;
}
}
PlanCuentaID = row["PlanCuentaID"].ToString();
Referencia = row["nombre"].ToString();
AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento);
}
}
}
}
bulkCopy(connectionString, NombreTablaCabecera, tablaHeader);
bulkCopy(connectionString, NombreTablaDetalle, tablaDetail);
watch.Stop();
Console.WriteLine("Total Time:" + watch.ElapsedMilliseconds / 1000);
Console.WriteLine("End : " + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff"));
Console.ReadKey();
}
static void Test()
{
// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;
var connectionString = "user id=sa;password=server$123$;data source=localhost;initial catalog=Shiol_Datos_MLP;Connection Timeout=30;";
// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql(connectionString, nTimeout, bLog);
// Now let's roll!
SqlResultWithDataSet oResult = oSql.Open("SELECT * FROM ParametrosGenerales");
DataSet dataSet = oResult.Results;
//// I'm going to re-use oSql and oResult from above ^^^
oResult = oSql.Open("SELECT * FROM ParametrosGenerales WHERE Estado = @id",
CommandType.Text,
new SqlParameter("id", 1)
);
//SQLMagic makes it incredibly simple to write proper queries:
/*
* oResult = oSql.Open("SELECT * FROM tbl WHERE tbl_id IN (@id1, @id2, @id3)",
* CommandType.Text,
* new SqlParameter("id1", 1),
* new SqlParameter("id2", 2),
* new SqlParameter("id3", 3)
* );
*/
// Stored Procedures with parameters?
oSql.Execute("sp_Productos",
CommandType.StoredProcedure,
new SqlParameter("TipoProductoID", "1224")
);
var intBack = (SqlResult)oSql.Execute("sp_Productos",
CommandType.StoredProcedure,
new SqlParameter("TipoProductoID", "1224")
);
// What about return values?
/* var datetime = oSql.Execute<DateTime>("SELECT GETDATE()");
* var intBack = oSql.Execute<Int32>("SELECT 1");*/
// Stored Procedure that has a return value?
var intBackAgain = oSql.Execute <Int32>("sp_Productos", CommandType.StoredProcedure);
//Asynchronous Support
//SQLMagic, with compilation flag "NET45" set, supports the async/await model of.NET, and implements it in a very clean, easy to use manner:
// Converting is VERY easy!
#if NET45
var oResult = await oSql.OpenAsync("SELECT * FROM aVeryLargeTable");
var nValue = await oSql.ExecuteAsync <Int32>("SELECT bigNumberComputationThatResultsInInteger");
var nProcedure = await oSql.ExecuteAsync <Int32>("sp_LongRunningProcedure", CommandType.StoredProcedure);
#endif
//Transaction Support
//SQLMagic gives you the ability to Begin, Commit, and RollBack transactions with and without asychronous capability:
// Converting is VERY easy!
#if NET45
// Start a Transaction:
SqlTransaction oTransaction = oSql.BeginTransaction();
// The asynchronous version:
SqlTransaction oTransaction = await oSql.BeginTransactionAsync();
// one of the overloads that SQLMagic has is the ability to specify a transaction!
// public SqlResultWithDataSet Open(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction)
// Statement is merely a SQLMagic struct that groups up some parameters from earlier:
Statement oStatement = new Statement
{
Sql = "INSERT INTO tbl VALUES(@val1)",
Type = CommandType.Text,
Parameters = new List <SqlParameters>()
{
new SqlParameter("val1", "value")
}
};
oSql.Open(oStatement, oTransaction.Connection, false, oTransaction);
// Open a DataSet of the given statement, connection, don't close it, and use that transaction
// Once you're all done, you end!
oSql.EndTransaction(oTransaction, true); // true or false indicates COMMIT or ROLLBACK!
// The asynchronous version:
await oSql.EndTransactionAsync(oTransaction);
#endif
//Manual Connection Creation
//SQLMagic lets you create connections yourself(remember that SQLMagic has overloads to indicate a SqlConnection object!):
// Synchronous
SqlConnection oConnection = oSql.CreateConnection(true);
// Converting is VERY easy!
#if NET45
// Asynchronous
SqlConnection oConnection = await oSql.CreateConnectionAsync(true);
#endif
// The boolean value indicates whether or not the connection is automatically opened when created
//TryOpen/TryExecute/TryExecute<T>
//SQLMagic can attempt to execute a query for you and, instead of throwing an exception, will simply return a Boolean value that indicates success, and use an out parameter to store your result:
// Variables
//SqlResultWithDataSet oResult;
if (oSql.TryOpen("SELECT * FROM tbl WHER tbl.id = 1", out oResult))
{
// This fails because of syntax ^
}
else
{
// Because it failed, you can handle the exception here!
//MessageBox.Show(oResult.Exception.ToString());
}
}
/// <summary>
/// Tries to create a DataSet object.
/// The resulting boolean indicates whether or not the query execution was successful
/// </summary>
/// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param>
/// <param name="aCommandType">The type of SQL statement being passed</param>
/// <param name="aResult">The result object that contains the DataSet and potential Exception object</param>
/// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns>
/// <seealso cref="System.Data.CommandType"/>
/// <seealso cref="Items.Results.SqlResultWithDataSet"/>
public Boolean TryOpen(String aSqlStatement,
CommandType aCommandType,
out SqlResultWithDataSet aResult)
{
return(TryOpen(aSqlStatement, aCommandType, null, out aResult));
}
/// <summary>
/// Creates a DataSet that contains zero or more DataTables based on the SQL Statement passed
/// </summary>
/// <param name="aStatement">The SQL statement structure to open</param>
/// <param name="aConnection">The connection to use for this SQL statement</param>
/// <param name="aCloseConnection">A boolean flag that indicates whether or not the
/// connection should be closed after the statement is executed</param>
/// <param name="aTransaction">The SqlTransaction object that this connection is part of</param>
/// <returns>A DataSet that contains a set of DataTables</returns>
/// <seealso cref="System.Data.DataSet"/>
/// <seealso cref="Sql.Statement"/>
/// <seealso cref="System.Data.SqlClient.SqlConnection"/>
/// <seealso cref="System.Data.SqlClient.SqlTransaction"/>
public SqlResultWithDataSet Open(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction)
{
// Create the connection
SqlDataReader oReader = null;
DataSet oDataSet = null;
SqlResultWithDataSet oResult = new SqlResultWithDataSet();
DataTable oSchema = null;
// Create a command
SqlCommand oCommand = new SqlCommand
{
CommandText = aStatement.Sql,
CommandType = aStatement.Type,
CommandTimeout = iTimeout,
Connection = aConnection,
Transaction = aTransaction
};
// Add the parameters
if (aStatement.Parameters != null)
{
oCommand.Parameters.AddRange(aStatement.Parameters.ToArray());
}
// Do a log of the command being executed
if (iLogging)
{
}
// Set back
oCommand.CommandText = aStatement.Sql;
try
{
// Store the reader
oReader = oCommand.ExecuteReader(aCloseConnection ? CommandBehavior.CloseConnection : CommandBehavior.Default);
// Schema
// oResult.DataTableSchema = oReader.GetSchemaTable();
// Set the statistics
oResult.Statistics = aConnection.RetrieveStatistics();
// Post-Execution
if (iLogging)
{
}
// Convert
oDataSet = oReader.ToDataSet();
// Set
oResult.Results = oDataSet;
// Close!
oReader.Close();
// Give back the result set
return(oResult);
}
catch (Exception)
{
// ToDo: Log the error here but rethrow the error!
throw;
}
finally
{
// Close the connection if necessary:
if (aCloseConnection && aConnection.State == ConnectionState.Open && aConnection != null)
{
aConnection.Close();
aConnection.Dispose();
}
}
}
public void createRecords(string FilePath, List <TableField> fields, string tableName, Sql oSql)
{
DateTimeFormatInfo DateTsysFormat = CultureInfo.CurrentCulture.DateTimeFormat;
FileInfo existingFile = new FileInfo(FilePath);
Console.WriteLine(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets["RH"];
//INSERT INTO table_name (column1, column2, column3, ...)
//VALUES(value1, value2, value3, ...);
string strCommand = "INSERT INTO " + tableName;
string strFields = " (";
int x = 0;
foreach (var field in fields)
{
strFields += (x > 0 ? "," : "") + field.name;
x++;
}
strFields += ")";
strCommand += strFields + " ";
Boolean header = false;
for (int row = 1; row < worksheet.Cells.Rows; row++)
{
if (!header)
{
header = true;
continue;
}
string strValues = "VALUES(";
x = 0;
string crc = "";
for (int col = 1; col <= fields.Count; col++)
{
//Console.WriteLine(fields[cell.ColumnNumber - 1].Name + " -- " + cell.Value.ToString() + " -- " + cell.GetType());
string value = worksheet.Cells[row, col].Value == null ? "" : worksheet.Cells[row, col].Value.ToString().Trim();
DateTime result;
if (IsDate(value, out result))
{
// do something with result
value = result.ToString("yyyyMMdd hh:mm:ss"); // ("yyyy-MM-dd HH:mm:ss.fff");
// Console.WriteLine(value);
}
strValues += (x > 0 ? "," : "") + "'" + value + "'"; //.Replace("\"","").Replace("'","")
x++;
crc += value;
if (x == fields.Count)
{
break;
}
}
strValues += ")";
if (crc == "")
{
return;
}
string strCommandExec = strCommand + strValues;
try
{
SqlResultWithDataSet oResult = oSql.Open(strCommandExec, CommandType.Text);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + "----" + strCommandExec);
}
}
// Read the rows using the worksheet index
// Worksheet indexes are start at 1 not 0
// This method is slightly faster to find the underlying file (so slight you probably wouldn't notice)
//worksheet = fastExcel.Read(1);
}
}
