/// <summary>
/// 表批量写入（须手动处理自增列SQL）
/// 根据行数据 RowState 状态新增、修改
/// </summary>
/// <param name="dt">数据表（Namespace=SchemaName，TableName=TableName）</param>
/// <param name="sqlEmpty">查询空表脚本，默认*，可选列，会影响数据更新的列</param>
/// <param name="dataAdapter">执行前修改（命令行脚本、超时等信息）</param>
/// <param name="openTransaction">开启事务，默认开启</param>
/// <returns></returns>
public int BulkBatchPostgreSQL(DataTable dt, string sqlEmpty = null, Action <NpgsqlDataAdapter> dataAdapter = null, bool openTransaction = true)
{
return(SafeConn(() =>
{
var connection = (NpgsqlConnection)Connection;
NpgsqlTransaction transaction = openTransaction ? (NpgsqlTransaction)(Transaction = connection.BeginTransaction()) : null;
var cb = new NpgsqlCommandBuilder();
if (string.IsNullOrWhiteSpace(sqlEmpty))
{
var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.PostgreSQL);
sqlEmpty = SqlEmpty(sntn);
}
cb.DataAdapter = new NpgsqlDataAdapter
{
SelectCommand = new NpgsqlCommand(sqlEmpty, connection, transaction)
};
cb.ConflictOption = ConflictOption.OverwriteChanges;
var da = new NpgsqlDataAdapter
{
InsertCommand = cb.GetInsertCommand(true),
UpdateCommand = cb.GetUpdateCommand(true)
};
da.InsertCommand.CommandTimeout = 300;
da.UpdateCommand.CommandTimeout = 300;
//处理：无效的 "UTF8" 编码字节顺序: 0x00
var listColName = dt.Columns.Cast <DataColumn>().Where(x => x.DataType == typeof(string)).Select(x => x.ColumnName).ToList();
foreach (DataRow dr in dt.Rows)
{
listColName.ForEach(colName =>
{
var val = dr[colName];
if (val is not DBNull)
{
dr[colName] = val.ToString().Replace("\0", "");
}
});
}
//执行前修改
dataAdapter?.Invoke(da);
var num = da.Update(dt);
transaction?.Commit();
return num;
}));
}
public int Insert(NpgsqlConnection conn, IEnumerable<T> list, NpgsqlTransaction tran = null, string otherTable = null)
{
if (list == null || !list.Any())
return 0;
var mis = mappingList.Where(mi => !mi.IsDbGenerated).ToList();
var tableName = defaultTableName ?? otherTable;
using (tran = GetTransaction(conn, tran))
{
try
{
var count = CopyData(conn, list, mis, tableName);
tran?.Commit();
return count;
}
catch (Exception e)
{
tran?.Rollback();
throw e;
}
}
}
public int Update(NpgsqlConnection conn, IEnumerable<T> list, NpgsqlTransaction tran = null, IEnumerable<string> updateFields = null, string otherTable = null)
{
if (list == null || !list.Any())
return 0;
var tableName = defaultTableName ?? otherTable;
var mappings = mappingList;
if (updateFields != null && updateFields.Any())
{
mappings = new List<MappingInfo>();
foreach (var mi in mappingList)
{
if (mi.IsPrimaryKey || updateFields.Contains(mi.PropertyName))
mappings.Add(mi);
}
}
using (tran = GetTransaction(conn, tran))
{
try
{
var temp = $"temp_{tableName.Trim('"').ToLower()}_{DateTime.Now.Ticks}";
var sql = $"CREATE TEMP TABLE {temp} (LIKE {tableName} INCLUDING DEFAULTS) ON COMMIT DROP";
new NpgsqlCommand(sql, conn, tran).ExecuteNonQuery();
CopyData(conn, list, mappings, temp);
var mis = mappings.Where(m => !m.IsPrimaryKey).ToList();
var updateCols = string.Join(",", mis.Select(m => $"{m.ColumnName} = source.{m.ColumnName}"));
var pk = mappings.First(m => m.IsPrimaryKey).ColumnName;
var updateSql = $"UPDATE {tableName} SET {updateCols} FROM {temp} as source WHERE {tableName}.{pk}=source.{pk}";
var count = new NpgsqlCommand(updateSql, conn, tran).ExecuteNonQuery();
tran?.Commit();
return count;
}
catch (Exception e)
{
tran?.Rollback();
throw e;
}
}
}
/// <summary>
/// Método para actualizar un Usuario en la base de datos.
/// </summary>
/// <param name="infoUsuario"></param>
/// <returns></returns>
public MensajesUsuarios ActualizacionUsuario(Usuarios infoUsuario)
{
MensajesUsuarios msjUsuarios = new MensajesUsuarios();
try
{
NpgsqlTransaction tran = conn_BD.BeginTransaction();
string pwdUsuario = ConfigEncryption.EncriptarValor(infoUsuario.PasswordUsuario);
ConsultasUsuarios objConsultaUsuarios = new ConsultasUsuarios();
if (objConsultaUsuarios.ObtenerUsuarioPorNick(infoUsuario.NickUsuario).ObjetoInventarios == null)
{
using (NpgsqlCommand cmd = new NpgsqlCommand("UPDATE dcicc_usuarios set id_rol = @ir,nombres_usuario = @nu,password_usuario = @pu,correo_usuario = @cu,telefono_usuario = @tu,telefonocelular_usuario = @tcu,direccion_usuario = @du,habilitado_usuario = @hu where id_usuario = @iu", conn_BD))
{
cmd.Parameters.Add("ir", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoUsuario.IdRol;
cmd.Parameters.Add("nu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.NombresUsuario.Trim();
cmd.Parameters.Add("pu", NpgsqlTypes.NpgsqlDbType.Text).Value = pwdUsuario.Trim();
cmd.Parameters.Add("cu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.CorreoUsuario.Trim();
cmd.Parameters.Add("tu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.TelefonoUsuario) ? (object)infoUsuario.TelefonoUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("tcu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.TelefonoCelUsuario) ? (object)infoUsuario.TelefonoCelUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("du", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.DireccionUsuario) ? (object)infoUsuario.DireccionUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("hu", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoUsuario.HabilitadoUsuario;
cmd.Parameters.Add("iu", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoUsuario.IdUsuario;
cmd.ExecuteNonQuery();
}
tran.Commit();
msjUsuarios = ActualizacionNickUsuario(infoUsuario);
if (infoUsuario.NombreRolAntiguo != null)
{
ActualizarRolUsuario(infoUsuario.NombreRolAntiguo.Trim(), infoUsuario.NombreRol.Trim(), infoUsuario.NickUsuario.Trim());
}
conn_BD.Close();
}
else
{
using (NpgsqlCommand cmd = new NpgsqlCommand("UPDATE dcicc_usuarios set id_rol = @ir,nombres_usuario = @nu,nick_usuario = @niu,password_usuario = @pu,correo_usuario = @cu,telefono_usuario = @tu,telefonocelular_usuario = @tcu,direccion_usuario = @du,habilitado_usuario = @hu where id_usuario = @iu", conn_BD))
{
cmd.Parameters.Add("ir", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoUsuario.IdRol;
cmd.Parameters.Add("nu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.NombresUsuario.Trim();
cmd.Parameters.Add("niu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.NickUsuario.Trim();
cmd.Parameters.Add("pu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pwdUsuario.Trim();
cmd.Parameters.Add("cu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.CorreoUsuario.Trim();
cmd.Parameters.Add("tu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.TelefonoUsuario) ? (object)infoUsuario.TelefonoUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("tcu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.TelefonoCelUsuario) ? (object)infoUsuario.TelefonoCelUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("du", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.DireccionUsuario) ? (object)infoUsuario.DireccionUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("hu", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoUsuario.HabilitadoUsuario;
cmd.Parameters.Add("iu", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoUsuario.IdUsuario;
cmd.ExecuteNonQuery();
}
if (infoUsuario.NombreRolAntiguo != null)
{
ActualizarRolUsuario(infoUsuario.NombreRolAntiguo.Trim(), infoUsuario.NombreRol.Trim(), infoUsuario.NickUsuario.Trim());
}
string query = string.Format("ALTER USER {0} with password '{1}';", infoUsuario.NickUsuario.Trim(), pwdUsuario.Trim());
using (var cmd = new NpgsqlCommand(query, conn_BD))
{
cmd.ExecuteNonQuery();
}
tran.Commit();
conn_BD.Close();
msjUsuarios.OperacionExitosa = true;
}
}
catch (Exception e)
{
conn_BD.Close();
msjUsuarios.OperacionExitosa = false;
msjUsuarios.MensajeError = e.Message;
}
return(msjUsuarios);
}
/// <summary>
/// Método para actualizar el perfil de un Usuario en la base de datos.
/// </summary>
/// <param name="infoUsuario"></param>
/// <returns></returns>
public MensajesUsuarios ActualizacionPerfilUsuario(Usuarios infoUsuario)
{
MensajesUsuarios msjUsuarios = new MensajesUsuarios();
try
{
NpgsqlTransaction tran = conn_BD.BeginTransaction();
using (NpgsqlCommand cmd = new NpgsqlCommand("UPDATE dcicc_usuarios set nombres_usuario = @nu,correo_usuario = @cu,telefono_usuario = @tu,telefonocelular_usuario = @tcu,direccion_usuario = @du where id_usuario = @iu", conn_BD))
{
cmd.Parameters.Add("nu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.NombresUsuario.Trim();
cmd.Parameters.Add("cu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.CorreoUsuario.Trim();
cmd.Parameters.Add("tu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.TelefonoUsuario) ? (object)infoUsuario.TelefonoUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("tcu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.TelefonoCelUsuario) ? (object)infoUsuario.TelefonoCelUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("du", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoUsuario.DireccionUsuario) ? (object)infoUsuario.DireccionUsuario.Trim() : DBNull.Value;
cmd.Parameters.Add("iu", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoUsuario.IdUsuario;
cmd.ExecuteNonQuery();
}
ConsultasUsuarios objConsultaUsuarios = new ConsultasUsuarios();
if (objConsultaUsuarios.ObtenerUsuarioPorNick(infoUsuario.NickUsuario).ObjetoInventarios == null)
{
ConsultasUsuarios objConsultaUsuariosBD = new ConsultasUsuarios();
Usuarios infoUsuarioBD = objConsultaUsuariosBD.ObtenerUsuarioPorId(infoUsuario.IdUsuario).ObjetoInventarios;
string nickAnterior = infoUsuarioBD.NickUsuario.Trim();
using (NpgsqlCommand cmd = new NpgsqlCommand("UPDATE dcicc_usuarios set nick_usuario = @niu where id_usuario = @iu", conn_BD))
{
cmd.Parameters.Add("niu", NpgsqlTypes.NpgsqlDbType.Varchar).Value = infoUsuario.NickUsuario.Trim();
cmd.Parameters.Add("iu", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoUsuario.IdUsuario;
cmd.ExecuteNonQuery();
}
tran.Commit();
conn_BD.Close();
NpgsqlConnection connBD = new NpgsqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=Admin*1234;Database=DCICC_BDInventario; CommandTimeout=3020;");
connBD.Open();
NpgsqlTransaction tranBD = connBD.BeginTransaction();
string queryUser = string.Format("ALTER USER {0} RENAME TO {1};", nickAnterior.Trim(), infoUsuario.NickUsuario.Trim());
using (var cmd = new NpgsqlCommand(queryUser, connBD))
{
cmd.ExecuteNonQuery();
}
string queryPwd = string.Format("ALTER USER {0} with password '{1}';", infoUsuario.NickUsuario.Trim(), ConfigEncryption.EncriptarValor(infoUsuario.PasswordUsuario.Trim()));
using (var cmd = new NpgsqlCommand(queryPwd, connBD))
{
cmd.ExecuteNonQuery();
}
tranBD.Commit();
connBD.Close();
}
else
{
tran.Commit();
conn_BD.Close();
}
msjUsuarios.OperacionExitosa = true;
}
catch (Exception e)
{
conn_BD.Close();
msjUsuarios.OperacionExitosa = false;
msjUsuarios.MensajeError = e.Message;
}
return(msjUsuarios);
}
public int CreateStudent(Student student)
{
conn = DAO.getConnection();
NpgsqlCommand command = new NpgsqlCommand(DAOStudentResource.CreateStudentSP, conn);
NpgsqlTransaction transaction = conn.BeginTransaction();
NpgsqlParameter email = new NpgsqlParameter();
NpgsqlParameter password = new NpgsqlParameter();
NpgsqlParameter name = new NpgsqlParameter();
NpgsqlParameter lastName = new NpgsqlParameter();
NpgsqlParameter birthDate = new NpgsqlParameter();
NpgsqlParameter phone = new NpgsqlParameter();
NpgsqlParameter country = new NpgsqlParameter();
NpgsqlParameter grade = new NpgsqlParameter();
email.ParameterName = DAOStudentResource.Email;
password.ParameterName = DAOStudentResource.Password;
name.ParameterName = DAOStudentResource.Name;
lastName.ParameterName = DAOStudentResource.Lastname;
birthDate.ParameterName = DAOStudentResource.Birthdate;
phone.ParameterName = DAOStudentResource.Phone;
country.ParameterName = DAOStudentResource.Country;
grade.ParameterName = DAOStudentResource.GradeID;
email.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
password.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
name.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
lastName.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
birthDate.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
phone.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
country.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
grade.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
email.Direction = ParameterDirection.Input;
password.Direction = ParameterDirection.Input;
name.Direction = ParameterDirection.Input;
lastName.Direction = ParameterDirection.Input;
birthDate.Direction = ParameterDirection.Input;
phone.Direction = ParameterDirection.Input;
country.Direction = ParameterDirection.Input;
grade.Direction = ParameterDirection.Input;
email.Value = student.Email;
password.Value = student.Password;
name.Value = student.Name;
lastName.Value = student.LastName;
birthDate.Value = student.BirthDate;
phone.Value = student.Phone;
country.Value = student.Country;
grade.Value = student.Grade.Id;
command.Parameters.Add(email);
command.Parameters.Add(password);
command.Parameters.Add(name);
command.Parameters.Add(lastName);
command.Parameters.Add(birthDate);
command.Parameters.Add(phone);
command.Parameters.Add(country);
command.Parameters.Add(grade);
command.CommandType = CommandType.StoredProcedure;
int response = 500;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
response = dr.GetInt32(0);
}
dr.Close();
transaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(response);
}
/// <summary>
/// Create revision history for each entry in the database.
/// </summary>
private void CreateRevisionHistoryForEachEntry()
{
NpgsqlConnection connection = null;
NpgsqlTransaction transaction = null;
this.RequestUtils.QueryParameters = new QueryParameters();
try
{
// Create a revision history for SiteDirectory's entries
transaction = this.TransactionManager.SetupTransaction(ref connection, null);
this.TransactionManager.SetFullAccessState(true);
// Get first person Id (so that actor isnt guid.empty), it is hard to determine who it should be.
var actor = this.PersonService.GetShallow(transaction, TopContainer, null, new RequestSecurityContext {
ContainerReadAllowed = true
}).OfType <Person>().FirstOrDefault();
var actorId = actor != null ? actor.Iid : Guid.Empty;
// Save revision history for SiteDirectory's entries
this.RevisionService.SaveRevisions(transaction, TopContainer, actorId, EngineeringModelSetupSideEffect.FirstRevision);
var siteDirectory = this.SiteDirectoryService.Get(
transaction,
TopContainer,
null,
new RequestSecurityContext {
ContainerReadAllowed = true
}).OfType <SiteDirectory>().ToList();
var engineeringModelSetups = this.EngineeringModelSetupService.GetShallow(
transaction,
TopContainer,
siteDirectory[0].Model,
new RequestSecurityContext {
ContainerReadAllowed = true
}).OfType <EngineeringModelSetup>()
.ToList();
// commit revision history
transaction.Commit();
// Get all EngineeringModelSetups and create a revision history for each EngineeringModel
foreach (var engineeringModelSetup in engineeringModelSetups)
{
transaction = this.TransactionManager.SetupTransaction(ref connection, null);
this.TransactionManager.SetFullAccessState(true);
var partition =
this.RequestUtils.GetEngineeringModelPartitionString(engineeringModelSetup.EngineeringModelIid);
// Save revision history for EngineeringModel's entries
this.RevisionService.SaveRevisions(transaction, partition, actorId, EngineeringModelSetupSideEffect.FirstRevision);
// commit revision history
transaction.Commit();
}
}
catch (NpgsqlException ex)
{
transaction?.Rollback();
Logger.Error(ex, "Error occured during revision history creation");
}
catch (Exception ex)
{
transaction?.Rollback();
Logger.Error(ex, "Error occured during revision history creation");
}
finally
{
transaction?.Dispose();
connection?.Dispose();
}
}
public static long Add(DateTime valueDate, int officeId, int userId, long logOnId, int costCenterId, string referenceNumber, Collection <MixERP.Net.Common.Models.Transactions.TransactionDetailModel> details)
{
if (details == null)
{
return(0);
}
if (details.Count.Equals(0))
{
return(0);
}
string sql = string.Empty;
long transactionMasterId = 0;
decimal debitTotal = details.Sum(d => (d.Debit));
decimal creditTotal = details.Sum(d => (d.Credit));
string tranType = string.Empty;
decimal amount = 0;
if (debitTotal != creditTotal)
{
return(0);
}
using (NpgsqlConnection connection = new NpgsqlConnection(DBConnection.ConnectionString()))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
sql = "INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, user_id, login_id, office_id, cost_center_id, reference_number) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LogOnId), @Book, @ValueDate, @UserId, @LogOnId, @OfficeId, @CostCenterId, @ReferenceNumber;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
using (NpgsqlCommand master = new NpgsqlCommand(sql, connection))
{
master.Parameters.AddWithValue("@ValueDate", valueDate);
master.Parameters.AddWithValue("@OfficeId", officeId);
master.Parameters.AddWithValue("@UserId", userId);
master.Parameters.AddWithValue("@LogOnId", logOnId);
master.Parameters.AddWithValue("@Book", "Journal");
master.Parameters.AddWithValue("@CostCenterId", costCenterId);
master.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
transactionMasterId = MixERP.Net.Common.Conversion.TryCastLong(master.ExecuteScalar());
}
foreach (MixERP.Net.Common.Models.Transactions.TransactionDetailModel model in details)
{
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, amount) SELECT @TransactionMasterId, @TranType, core.get_account_id_by_account_code(@AccountCode::text), @StatementReference, office.get_cash_repository_id_by_cash_repository_name(@CashRepositoryName::text), @Amount;";
if (model.Credit > 0 && model.Debit > 0)
{
throw new InvalidOperationException(MixERP.Net.Common.Helpers.LocalizationHelper.GetResourceString("Warnings", "BothSidesHaveValue"));
}
else
{
if (model.Credit.Equals(0) && model.Debit > 0)
{
tranType = "Dr";
amount = model.Debit;
}
else
{
tranType = "Cr";
amount = model.Credit;
}
using (NpgsqlCommand transactionDetail = new NpgsqlCommand(sql, connection))
{
transactionDetail.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
transactionDetail.Parameters.AddWithValue("@TranType", tranType);
transactionDetail.Parameters.AddWithValue("@AccountCode", model.AccountCode);
transactionDetail.Parameters.AddWithValue("@StatementReference", model.StatementReference);
transactionDetail.Parameters.AddWithValue("@CashRepositoryName", model.CashRepositoryName);
transactionDetail.Parameters.AddWithValue("@Amount", amount);
transactionDetail.ExecuteNonQuery();
}
}
}
transaction.Commit();
return(transactionMasterId);
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
catch (InvalidOperationException)
{
transaction.Rollback();
throw;
}
}
}
}
public static long Add(DateTime valueDate, int officeId, int userId, long logOnId, int costCenterId, string referenceNumber, string statementReference, StockMasterModel stockMaster, Collection <StockMasterDetailModel> details, Collection <int> transactionIdCollection)
{
if (stockMaster == null)
{
return(0);
}
if (details == null)
{
return(0);
}
if (details.Count.Equals(0))
{
return(0);
}
if (stockMaster.AgentId.Equals(0))
{
return(0);
}
decimal total = details.Sum(d => (d.Price * d.Quantity));
decimal discountTotal = details.Sum(d => d.Discount);
decimal taxTotal = details.Sum(d => d.Tax);
const string creditInvariantParameter = "Sales.Receivables";
const string salesInvariantParameter = "Sales";
const string salesTaxInvariantParamter = "Sales.Tax";
const string salesDiscountInvariantParameter = "Sales.Discount";
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.ConnectionString()))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
#region TransactionMaster
string sql = "INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, user_id, login_id, office_id, cost_center_id, reference_number, statement_reference) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LogOnId), @Book, @ValueDate, @UserId, @LogOnId, @OfficeId, @CostCenterId, @ReferenceNumber, @StatementReference;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
long transactionMasterId;
using (NpgsqlCommand tm = new NpgsqlCommand(sql, connection))
{
tm.Parameters.AddWithValue("@ValueDate", valueDate);
tm.Parameters.AddWithValue("@OfficeId", officeId);
tm.Parameters.AddWithValue("@UserId", userId);
tm.Parameters.AddWithValue("@LogOnId", logOnId);
tm.Parameters.AddWithValue("@Book", "Sales.Delivery");
tm.Parameters.AddWithValue("@CostCenterId", costCenterId);
tm.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
tm.Parameters.AddWithValue("@StatementReference", statementReference);
transactionMasterId = Conversion.TryCastLong(tm.ExecuteScalar());
}
#region TransactionDetails
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, amount) SELECT @TransactionMasterId, @TranType, core.get_account_id_by_parameter(@ParameterName), @StatementReference, @Amount;";
using (NpgsqlCommand salesRow = new NpgsqlCommand(sql, connection))
{
salesRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
salesRow.Parameters.AddWithValue("@TranType", "Cr");
salesRow.Parameters.AddWithValue("@ParameterName", salesInvariantParameter);
salesRow.Parameters.AddWithValue("@StatementReference", statementReference);
salesRow.Parameters.AddWithValue("@Amount", total);
salesRow.ExecuteNonQuery();
}
if (taxTotal > 0)
{
using (NpgsqlCommand taxRow = new NpgsqlCommand(sql, connection))
{
taxRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
taxRow.Parameters.AddWithValue("@TranType", "Cr");
taxRow.Parameters.AddWithValue("@ParameterName", salesTaxInvariantParamter);
taxRow.Parameters.AddWithValue("@StatementReference", statementReference);
taxRow.Parameters.AddWithValue("@Amount", taxTotal);
taxRow.ExecuteNonQuery();
}
}
if (discountTotal > 0)
{
using (NpgsqlCommand discountRow = new NpgsqlCommand(sql, connection))
{
discountRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
discountRow.Parameters.AddWithValue("@TranType", "Dr");
discountRow.Parameters.AddWithValue("@ParameterName", salesDiscountInvariantParameter);
discountRow.Parameters.AddWithValue("@StatementReference", statementReference);
discountRow.Parameters.AddWithValue("@Amount", discountTotal);
discountRow.ExecuteNonQuery();
}
}
using (NpgsqlCommand creditRow = new NpgsqlCommand(sql, connection))
{
creditRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
creditRow.Parameters.AddWithValue("@TranType", "Dr");
creditRow.Parameters.AddWithValue("@ParameterName", creditInvariantParameter);
creditRow.Parameters.AddWithValue("@StatementReference", statementReference);
creditRow.Parameters.AddWithValue("@Amount", total - discountTotal + taxTotal + stockMaster.ShippingCharge);
creditRow.ExecuteNonQuery();
}
if (stockMaster.ShippingCharge > 0)
{
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, amount) SELECT @TransactionMasterId, @TranType, core.get_account_id_by_shipper_id(@ShipperId), @StatementReference, @Amount;";
using (NpgsqlCommand shippingChargeRow = new NpgsqlCommand(sql, connection))
{
shippingChargeRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
shippingChargeRow.Parameters.AddWithValue("@TranType", "Cr");
shippingChargeRow.Parameters.AddWithValue("@ShipperId", stockMaster.ShipperId);
shippingChargeRow.Parameters.AddWithValue("@StatementReference", statementReference);
shippingChargeRow.Parameters.AddWithValue("@Amount", stockMaster.ShippingCharge);
shippingChargeRow.ExecuteNonQuery();
}
}
#endregion
#endregion
#region StockMaster
sql = "INSERT INTO transactions.stock_master(stock_master_id, transaction_master_id, party_id, agent_id, price_type_id, is_credit, shipper_id, shipping_charge, store_id) SELECT nextval(pg_get_serial_sequence('transactions.stock_master', 'stock_master_id')), @TransactionMasterId, core.get_party_id_by_party_code(@PartyCode), @AgentId, @PriceTypeId, @IsCredit, @ShipperId, @ShippingCharge, @StoreId; SELECT currval(pg_get_serial_sequence('transactions.stock_master', 'stock_master_id'));";
long stockMasterId;
using (NpgsqlCommand stockMasterRow = new NpgsqlCommand(sql, connection))
{
stockMasterRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
stockMasterRow.Parameters.AddWithValue("@PartyCode", stockMaster.PartyCode);
stockMasterRow.Parameters.AddWithValue("@AgentId", stockMaster.AgentId);
stockMasterRow.Parameters.AddWithValue("@PriceTypeId", stockMaster.PriceTypeId);
stockMasterRow.Parameters.AddWithValue("@IsCredit", true);
if (stockMaster.ShipperId.Equals(0))
{
stockMasterRow.Parameters.AddWithValue("@ShipperId", DBNull.Value);
}
else
{
stockMasterRow.Parameters.AddWithValue("@ShipperId", stockMaster.ShipperId);
}
stockMasterRow.Parameters.AddWithValue("@ShippingCharge", stockMaster.ShippingCharge);
stockMasterRow.Parameters.AddWithValue("@StoreId", stockMaster.StoreId);
stockMasterId = Conversion.TryCastLong(stockMasterRow.ExecuteScalar());
}
#region StockDetails
sql = @"INSERT INTO
transactions.stock_details(stock_master_id, tran_type, store_id, item_id, quantity, unit_id, base_quantity, base_unit_id, price, discount, tax_rate, tax)
SELECT @StockMasterId, @TranType, @StoreId, core.get_item_id_by_item_code(@ItemCode), @Quantity, core.get_unit_id_by_unit_name(@UnitName), core.get_base_quantity_by_unit_name(@UnitName, @Quantity), core.get_base_unit_id_by_unit_name(@UnitName), @Price, @Discount, @TaxRate, @Tax;";
foreach (StockMasterDetailModel model in details)
{
using (NpgsqlCommand stockMasterDetailRow = new NpgsqlCommand(sql, connection))
{
stockMasterDetailRow.Parameters.AddWithValue("@StockMasterId", stockMasterId);
stockMasterDetailRow.Parameters.AddWithValue("@TranType", "Cr");
stockMasterDetailRow.Parameters.AddWithValue("@StoreId", model.StoreId);
stockMasterDetailRow.Parameters.AddWithValue("@ItemCode", model.ItemCode);
stockMasterDetailRow.Parameters.AddWithValue("@Quantity", model.Quantity);
stockMasterDetailRow.Parameters.AddWithValue("@UnitName", model.UnitName);
stockMasterDetailRow.Parameters.AddWithValue("@Price", model.Price);
stockMasterDetailRow.Parameters.AddWithValue("@Discount", model.Discount);
stockMasterDetailRow.Parameters.AddWithValue("@TaxRate", model.TaxRate);
stockMasterDetailRow.Parameters.AddWithValue("@Tax", model.Tax);
stockMasterDetailRow.ExecuteNonQuery();
}
}
#endregion
#endregion
if (transactionIdCollection != null)
{
if (transactionIdCollection.Count > 0)
{
foreach (int tranId in transactionIdCollection)
{
sql = "INSERT INTO transactions.stock_master_non_gl_relations(stock_master_id, non_gl_stock_master_id) SELECT @Id, @RelationId;";
using (NpgsqlCommand relation = new NpgsqlCommand(sql, connection))
{
relation.Parameters.AddWithValue("@Id", transactionMasterId);
relation.Parameters.AddWithValue("@RelationId", tranId);
relation.ExecuteNonQuery();
}
}
}
}
transaction.Commit();
return(transactionMasterId);
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
public void CommitTransaction()
{
_SqlTransaction.Commit();
_SqlTransaction.Dispose();
}
static void Main2(string[] args)
{
Stopwatch watch = new Stopwatch();
Stopwatch recordmeter = new Stopwatch();
NpgsqlConnection conn = new NpgsqlConnection(conPostGreGIS);
ClearWayExtData();
conn.Open();
var ids = GetWayIdsInStockholm();
int idcount = 0;
int oneway = 0;
int progress = 0;
int waysPertransaction = 100;
int transactioncounter = 0;
NpgsqlTransaction trans = null; // conn.BeginTransaction();
float recordsPerSecond = 0;
watch.Start();
foreach (var id in ids)
{
Console.Clear();
Console.WriteLine(
"Generating segments for " + id + " " + progress + "/" + ids.Length + " ...." + (100 * progress / (float)ids.Length).ToString("0.00") + "%");
Console.WriteLine(recordsPerSecond.ToString("0.00") + " ways/sec");
if (transactioncounter == 0)
{
recordmeter.Restart();
}
progress++;
var nodes = GetWay(conn, id);
int lastnode = 0;
oneway = IsOneWay(conn, id);
if (transactioncounter == 0)
{
trans = conn.BeginTransaction();
}
for (int i = 1; i < nodes.Count; i++) // we skip the first nodes in the way.
{
if (CheckConnectivity(conn, nodes[i][0])) // if it is connected, start a way
{
// if it is start of the line
// TODO:
// - Create a new way
// - Add points from last node up to this index (i).
// - lastnode = i
int newseqforward = 0;
int newseqreverse = 0;
if (oneway == 1 || oneway == 0)
{
for (int j = lastnode; j <= i; j++)
{
InsertNew_Way_ext(conn, idcount, nodes[j][0], newseqforward, id, lastnode, i);
newseqforward++;
}
}
if (oneway == -1 || oneway == 0)
{
for (int j = i; j >= lastnode; j--) // TODO Reverse the indices
{
InsertNew_Way_ext(conn, idcount, nodes[j][0], newseqreverse, id, i, lastnode);
newseqreverse++;
}
}
lastnode = i;
idcount++;
}
}
if (transactioncounter++ > waysPertransaction)
{
trans.Commit();
recordmeter.Stop();
recordsPerSecond = transactioncounter / (float)recordmeter.Elapsed.TotalSeconds;
transactioncounter = 0;
}
}
watch.Stop();
Console.WriteLine(
String.Format(
"Segment generation was completed in {0} hrs and {1} mins and {2} seconds", watch.Elapsed.Hours, watch.Elapsed.Minutes, watch.Elapsed.Seconds));
conn.Close();
}
static void Main(string[] args)
{
Stopwatch watch = new Stopwatch();
Stopwatch recordmeter = new Stopwatch();
Aram.OSMParser.Bounds b = new Aram.OSMParser.Bounds();
b.minlat = 59.32973;
b.maxlat = 59.34481;
b.minlon = 18.07556;
b.maxlon = 18.1062;
if (args.Length == 1)
{
conPostGreGIS = conPostGreGIS.Replace("127.0.0.1", args[0]);
}
if (args.Length == 5)
{
conPostGreGIS = conPostGreGIS.Replace("127.0.0.1", args[0]);
b.minlat = double.Parse(args[1]);
b.maxlat = double.Parse(args[2]);
b.minlon = double.Parse(args[3]);
b.maxlon = double.Parse(args[4]);
}
Console.WriteLine("Preprocessing...");
ClearWayExtData();
PerformPreprocessingForWay_ext();
Console.WriteLine("Preprocessing is finished.");
//conn.Open();
var ids = GetWayIdsInBound(b); //GetWayIdsInStockholm();
int idcount = 0;
int progress = 1;
object lockTransaction = new object();
object lockCounter = new object();
watch.Start();
Parallel.ForEach <String>(ids, (id) =>
{
//lock (lockTransaction)
//{
// Console.Clear();
// Console.WriteLine(
// "Generating segments for " + id + "\n" + progress + "/" + ids.Length + "\t" + (100 * progress / (float)ids.Length).ToString("0.00") + "%");
//}
NpgsqlConnection conn = new NpgsqlConnection(conPostGreGIS);
conn.Open();
NpgsqlTransaction trans = conn.BeginTransaction();
Interlocked.Increment(ref progress);
//progress++;
if (IsHighway(id, conn))
{
var nodes = GetWay(conn, id);
var lastnode = 0;
var oneway = IsOneWay(conn, id);
for (int i = 1; i < nodes.Count; i++) // we skip the first nodes in the way.
{
if (CheckConnectivity(conn, nodes[i][0])) // if it is connected, start a way
{
int newseqforward = 0;
int newseqreverse = 0;
if (oneway == 1 || oneway == 0)
{
int localcount;
lock (lockCounter)
{
localcount = idcount;
idcount++;
}
for (int j = lastnode; j <= i; j++)
{
InsertNew_Way_ext(conn, localcount, nodes[j][0], newseqforward, id, lastnode, i);
newseqforward++;
}
}
if (oneway == -1 || oneway == 0)
{
int localcount;
lock (lockCounter)
{
localcount = idcount;
idcount++;
}
for (int j = i; j >= lastnode; j--)
{
InsertNew_Way_ext(conn, localcount, nodes[j][0], newseqreverse, id, i, lastnode);
newseqreverse++;
}
}
lastnode = i;
}
}
}
else
{
// just copy to destination
var nodes = GetWay(conn, id);
int localcount;
lock (lockCounter)
{
localcount = idcount;
idcount++;
}
for (int i = 0; i < nodes.Count; i++) // we skip the first nodes in the way.
{
InsertNew_Way_ext(conn, localcount, nodes[i][0], i, id, 0, nodes.Count - 1);
}
}
trans.Commit();
conn.Close();
});
Console.WriteLine("Postprocessing...");
PerformPostprocessingForWay_ext();
Console.WriteLine("Postprocessing is finished.");
watch.Stop();
Console.WriteLine(
String.Format(
"Segment generation was completed in {0} hrs and {1} mins and {2} seconds", watch.Elapsed.Hours, watch.Elapsed.Minutes, watch.Elapsed.Seconds));
Console.ReadKey();
}
/// <summary>
/// Create an asset in database, or update it if existing.
/// </summary>
/// <param name="asset">Asset UUID to create</param>
/// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
public void StoreAsset(AssetBase asset)
{
// m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID);
lock (m_dbLock)
{
using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
{
dbcon.Open();
using (NpgsqlTransaction transaction = dbcon.BeginTransaction())
{
string assetName = asset.Name;
if (asset.Name.Length > 64)
{
assetName = asset.Name.Substring(0, 64);
m_log.WarnFormat(
"[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
asset.Name, asset.ID, asset.Name.Length, assetName.Length);
}
string assetDescription = asset.Description;
if (asset.Description.Length > 64)
{
assetDescription = asset.Description.Substring(0, 64);
m_log.WarnFormat(
"[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
}
if (m_enableCompression)
{
MemoryStream outputStream = new MemoryStream();
using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false))
{
// Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue));
// We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream.
compressionStream.Close();
byte[] compressedData = outputStream.ToArray();
asset.Data = compressedData;
}
}
byte[] hash = hasher.ComputeHash(asset.Data);
UUID asset_id;
UUID.TryParse(asset.ID, out asset_id);
// m_log.DebugFormat(
// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
// asset.ID, asset.Name, hash, compressedData.Length);
try
{
using (NpgsqlCommand cmd =
new NpgsqlCommand(
@"insert INTO XAssetsMeta(id, hash, name, description, ""AssetType"", local, temporary, create_time, access_time, asset_flags, creatorid)
Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID
where not exists( Select id from XAssetsMeta where id = :ID);
update XAssetsMeta
set id = :ID, hash = :Hash, name = :Name, description = :Description,
""AssetType"" = :AssetType, local = :Local, temporary = :Temporary, create_time = :CreateTime,
access_time = :AccessTime, asset_flags = :AssetFlags, creatorid = :CreatorID
where id = :ID;
",
dbcon))
{
// create unix epoch time
int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
cmd.Parameters.Add(m_database.CreateParameter("ID", asset_id));
cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
cmd.Parameters.Add(m_database.CreateParameter("Name", assetName));
cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription));
cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type));
cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local));
cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary));
cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now));
cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now));
cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID));
cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags));
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
m_log.ErrorFormat("[ASSET DB]: PGSQL failure creating asset metadata {0} with name \"{1}\". Error: {2}",
asset.FullID, asset.Name, e.Message);
transaction.Rollback();
return;
}
if (!ExistsData(dbcon, transaction, hash))
{
try
{
using (NpgsqlCommand cmd =
new NpgsqlCommand(
@"INSERT INTO XAssetsData(hash, data) VALUES(:Hash, :Data)",
dbcon))
{
cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data));
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
m_log.ErrorFormat("[XASSET DB]: PGSQL failure creating asset data {0} with name \"{1}\". Error: {2}",
asset.FullID, asset.Name, e.Message);
transaction.Rollback();
return;
}
}
transaction.Commit();
}
}
}
}
public void closeConnection()
{
transaction.Commit();
conn.Close();
}
private static void lGrabarVencimientos(List <FactVencimiento> vencimientos, GrupoVencimiento grupo, List <FactVencimiento> listAllFactVencimientosInDB, string USDTag)
{
string connectionString = ConfigurationManager.ConnectionStrings[Config.Common.JASPER].ConnectionString;
// Para cada ID Doco en la BD de postgres
using (NpgsqlConnection pgConnection = new NpgsqlConnection(connectionString))
{
try
{
pgConnection.Open();
using (NpgsqlTransaction transaction = pgConnection.BeginTransaction())
{
try
{
foreach (FactVencimiento vencimiento in vencimientos)
{
NpgsqlCommand theCommand;
FactVencimiento oFactVencimientToPersist = vencimiento;
// Check if record exists.
bool exists = false;
foreach (FactVencimiento record in listAllFactVencimientosInDB)
{
if (record.Cliente.IdCliente == vencimiento.Cliente.IdCliente)
{
exists = true;
break;
}
}
// if record exists, update the record.
string updateString = "";
if (exists)
{
updateString = "UPDATE fact_vencido" + USDTag + " " +
"SET saldo_vencido=@saldo_vencido " +
"WHERE id_cliente=@id_cliente and id_grupo_vencimiento=@id_grupo_vencimiento;";
}
// if record does not exists, insert the record
else
{
updateString = "INSERT INTO fact_vencido" + USDTag + "(" +
"id_cliente, id_grupo_vencimiento, saldo_vencido) " +
"VALUES (@id_cliente, @id_grupo_vencimiento, @saldo_vencido);";
}
theCommand = new NpgsqlCommand(updateString, pgConnection);
theCommand.Parameters.Add("@saldo_vencido", NpgsqlTypes.NpgsqlDbType.Numeric);
theCommand.Parameters.Add("@id_cliente", NpgsqlTypes.NpgsqlDbType.Integer);
theCommand.Parameters.Add("@id_grupo_vencimiento", NpgsqlTypes.NpgsqlDbType.Integer);
theCommand.Parameters["@saldo_vencido"].Value = oFactVencimientToPersist.Importe;
theCommand.Parameters["@id_cliente"].Value = oFactVencimientToPersist.Cliente.IdCliente;
theCommand.Parameters["@id_grupo_vencimiento"].Value = grupo.Id;
theCommand.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
throw ex;
}
}
}
catch (Exception ex)
{
throw ex;
}
pgConnection.Close();
}
}
public static void GrabarPorVencer(List <FactVencimiento> porvencer, GrupoVencimiento grupo, string USDTag)
{
List <FactVencimiento> listAllFactPorVencerInDB = new List <FactVencimiento>();
string connectionString = ConfigurationManager.ConnectionStrings[Config.Common.JASPER].ConnectionString;
using (NpgsqlConnection pgConnection = new NpgsqlConnection(connectionString))
{
pgConnection.Open();
NpgsqlDataReader dr;
NpgsqlCommand cmd;
string sqlString = "SELECT id_cliente, id_grupo_vencimiento, saldo_por_vencer " +
"FROM fact_por_vencer" + USDTag + ";";
cmd = new NpgsqlCommand(sqlString, pgConnection);
dr = cmd.ExecuteReader();
while (dr.Read())
{
FactVencimiento oFactPorVencer = new FactVencimiento();
DimCliente dCliente = new DimCliente();
dCliente.IdCliente = int.Parse(dr["id_cliente"].ToString());
oFactPorVencer.Cliente = dCliente;
oFactPorVencer.IdVencimiento = int.Parse(dr["id_grupo_vencimiento"].ToString());
oFactPorVencer.Importe = double.Parse(dr["saldo_por_vencer"].ToString());
listAllFactPorVencerInDB.Add(oFactPorVencer);
}
dr.Close();
cmd.Dispose();
pgConnection.Close();
}
// Para cada ID Doco en la BD de postgres
using (NpgsqlConnection pgConnection = new NpgsqlConnection(connectionString))
{
try
{
pgConnection.Open();
using (NpgsqlTransaction transaction = pgConnection.BeginTransaction())
{
try
{
foreach (FactVencimiento currentPorVencer in porvencer)
{
NpgsqlCommand theCommand;
FactVencimiento oFactPorVencerToPersist = currentPorVencer;
// Check if record exists.
bool exists = false;
foreach (FactVencimiento record in listAllFactPorVencerInDB)
{
if (record.Cliente.IdCliente == currentPorVencer.Cliente.IdCliente)
{
exists = true;
break;
}
}
//FactPorVencer oFactPorVencerExistente = listAllFactPorVencerInDB.First(record => record.IdCliente == currentPorVencer.IdCliente && record.IdEmpresa == currentPorVencer.IdEmpresa && record.IdVencimiento == currentPorVencer.IdVencimiento);
// if record exists, update the record.
if (exists)
{
string sqlUpdateVencimiento = "UPDATE fact_por_vencer" + USDTag + " " +
"SET saldo_por_vencer=@saldo_por_vencer " +
"WHERE id_cliente=@id_cliente and id_grupo_vencimiento=@id_grupo_vencimiento;";
theCommand = new NpgsqlCommand(sqlUpdateVencimiento, pgConnection);
}
// if record does not exists, insert the record
else
{
string sqlUpdateVencimiento = "INSERT INTO fact_por_vencer" + USDTag + "(" +
"id_cliente, id_grupo_vencimiento, saldo_por_vencer) " +
"VALUES (@id_cliente, @id_grupo_vencimiento, @saldo_por_vencer);";
theCommand = new NpgsqlCommand(sqlUpdateVencimiento, pgConnection);
}
theCommand.Parameters.Add("@saldo_por_vencer", NpgsqlTypes.NpgsqlDbType.Double);
theCommand.Parameters.Add("@id_cliente", NpgsqlTypes.NpgsqlDbType.Integer);
theCommand.Parameters.Add("@id_grupo_vencimiento", NpgsqlTypes.NpgsqlDbType.Integer);
theCommand.Parameters["@saldo_por_vencer"].Value = oFactPorVencerToPersist.Importe;
theCommand.Parameters["@id_cliente"].Value = oFactPorVencerToPersist.Cliente.IdCliente;
theCommand.Parameters["@id_grupo_vencimiento"].Value = grupo.Id;
theCommand.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
throw ex;
}
}
}
catch (Exception ex)
{
throw ex;
}
pgConnection.Close();
}
}
public static long Add(string catalog, DateTime valueDate, DateTime bookDate, int officeId, int userId,
long loginId, int costCenterId, string referenceNumber, Collection <JournalDetail> details,
Collection <Attachment> attachments)
{
if (details == null)
{
throw new InvalidOperationException(Errors.NoTransactionToPost);
}
if (details.Count.Equals(0))
{
throw new InvalidOperationException(Errors.NoTransactionToPost);
}
decimal debitTotal = (from detail in details select detail.LocalCurrencyDebit).Sum();
decimal creditTotal = (from detail in details select detail.LocalCurrencyCredit).Sum();
if (debitTotal != creditTotal)
{
throw new InvalidOperationException(Errors.ReferencingSidesNotEqual);
}
var decimalPlaces = CultureManager.GetCurrencyDecimalPlaces();
if ((from detail in details
where
Decimal.Round(detail.Credit * detail.ExchangeRate, decimalPlaces) !=
Decimal.Round(detail.LocalCurrencyCredit, decimalPlaces) ||
Decimal.Round(detail.Debit * detail.ExchangeRate, decimalPlaces) !=
Decimal.Round(detail.LocalCurrencyDebit, decimalPlaces)
select detail).Any())
{
throw new InvalidOperationException(Errors.ReferencingSidesNotEqual);
}
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
string sql =
"INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, book_date, user_id, login_id, office_id, cost_center_id, reference_number) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LoginId), @Book, @ValueDate, @BookDate, @UserId, @LoginId, @OfficeId, @CostCenterId, @ReferenceNumber;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
long transactionMasterId;
using (NpgsqlCommand master = new NpgsqlCommand(sql, connection))
{
master.Parameters.AddWithValue("@ValueDate", valueDate);
master.Parameters.AddWithValue("@BookDate", bookDate);
master.Parameters.AddWithValue("@OfficeId", officeId);
master.Parameters.AddWithValue("@UserId", userId);
master.Parameters.AddWithValue("@LoginId", loginId);
master.Parameters.AddWithValue("@Book", "Journal");
master.Parameters.AddWithValue("@CostCenterId", costCenterId);
master.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
transactionMasterId = Conversion.TryCastLong(master.ExecuteScalar());
}
foreach (JournalDetail model in details)
{
sql =
"INSERT INTO transactions.transaction_details(value_date, transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, currency_code, amount_in_currency, local_currency_code, er, amount_in_local_currency) " +
"SELECT @ValueDate, @TransactionMasterId, @TranType, core.get_account_id_by_account_number(@AccountNumber::text), @StatementReference, office.get_cash_repository_id_by_cash_repository_code(@CashRepositoryCode), @CurrencyCode, @AmountInCurrency, transactions.get_default_currency_code_by_office_id(@OfficeId), @Er, @AmountInLocalCurrency;";
if (model.Credit > 0 && model.Debit > 0)
{
throw new InvalidOperationException(Errors.BothSidesCannotHaveValue);
}
if (model.LocalCurrencyCredit > 0 && model.LocalCurrencyDebit > 0)
{
throw new InvalidOperationException(Errors.BothSidesCannotHaveValue);
}
decimal amountInCurrency;
decimal amountInLocalCurrency;
string tranType;
if (model.Credit.Equals(0) && model.Debit > 0)
{
tranType = "Dr";
amountInCurrency = model.Debit;
amountInLocalCurrency = model.LocalCurrencyDebit;
}
else
{
tranType = "Cr";
amountInCurrency = model.Credit;
amountInLocalCurrency = model.LocalCurrencyCredit;
}
using (NpgsqlCommand transactionDetail = new NpgsqlCommand(sql, connection))
{
transactionDetail.Parameters.AddWithValue("@ValueDate", valueDate);
transactionDetail.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
transactionDetail.Parameters.AddWithValue("@TranType", tranType);
transactionDetail.Parameters.AddWithValue("@AccountNumber", model.AccountNumber);
transactionDetail.Parameters.AddWithValue("@StatementReference",
model.StatementReference);
transactionDetail.Parameters.AddWithValue("@CashRepositoryCode",
model.CashRepositoryCode);
transactionDetail.Parameters.AddWithValue("@CurrencyCode", model.CurrencyCode);
transactionDetail.Parameters.AddWithValue("@AmountInCurrency", amountInCurrency);
transactionDetail.Parameters.AddWithValue("@OfficeId", officeId);
transactionDetail.Parameters.AddWithValue("@Er", model.ExchangeRate);
transactionDetail.Parameters.AddWithValue("@AmountInLocalCurrency",
amountInLocalCurrency);
transactionDetail.ExecuteNonQuery();
}
}
#region Attachment
if (attachments != null && attachments.Count > 0)
{
foreach (Attachment attachment in attachments)
{
sql =
"INSERT INTO core.attachments(user_id, resource, resource_key, resource_id, original_file_name, file_extension, file_path, comment) SELECT @UserId, @Resource, @ResourceKey, @ResourceId, @OriginalFileName, @FileExtension, @FilePath, @Comment;";
using (NpgsqlCommand attachmentCommand = new NpgsqlCommand(sql, connection))
{
attachmentCommand.Parameters.AddWithValue("@UserId", userId);
attachmentCommand.Parameters.AddWithValue("@Resource",
"transactions.transaction_master");
attachmentCommand.Parameters.AddWithValue("@ResourceKey", "transaction_master_id");
attachmentCommand.Parameters.AddWithValue("@ResourceId", transactionMasterId);
attachmentCommand.Parameters.AddWithValue("@OriginalFileName",
attachment.OriginalFileName);
attachmentCommand.Parameters.AddWithValue("@FileExtension",
System.IO.Path.GetExtension(attachment.OriginalFileName));
attachmentCommand.Parameters.AddWithValue("@FilePath", attachment.FilePath);
attachmentCommand.Parameters.AddWithValue("@Comment", attachment.Comment);
attachmentCommand.ExecuteNonQuery();
}
}
}
#endregion Attachment
#region Auto Verification
sql = "SELECT * FROM transactions.auto_verify(@TranId::bigint, @OfficeId::integer);";
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@TranId", transactionMasterId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.ExecuteNonQuery();
}
#endregion
transaction.Commit();
return(transactionMasterId);
}
catch (NpgsqlException ex)
{
Log.Warning(
@"Could not post transaction. ValueDate: {ValueDate}, OfficeId: {OfficeId}, UserId: {UserId}, LoginId: {LoginId}, CostCenterId:{CostCenterId}, ReferenceNumber: {ReferenceNumber}, Details: {Details}, Attachments: {Attachments}. {Exception}.",
valueDate, officeId, userId, loginId, costCenterId, referenceNumber, details, attachments,
ex);
transaction.Rollback();
throw;
}
catch (InvalidOperationException ex)
{
Log.Warning(
@"Could not post transaction. ValueDate: {ValueDate}, OfficeId: {OfficeId}, UserId: {UserId}, LoginId: {LoginId}, CostCenterId:{CostCenterId}, ReferenceNumber: {ReferenceNumber}, Details: {Details}, Attachments: {Attachments}. {Exception}.",
valueDate, officeId, userId, loginId, costCenterId, referenceNumber, details, attachments,
ex);
transaction.Rollback();
throw;
}
}
}
}
public Message WriteMessage(Message message, out int errorCode, out string errorMessage)
{
using var connection = GetNpgsqlConnection();
connection.Open();
errorCode = 0;
errorMessage = "";
// We are generating SEQ based on previous max value so we must retry in case
// a parallel request already used same SEQ that we generated.
bool retryInsert;
Message messageRes = null;
do
{
try
{
using NpgsqlTransaction transaction = connection.BeginTransaction();
retryInsert = false;
string selectChannel =
"SELECT locked, sequenced " +
"FROM Channel " +
"WHERE id = @channel " +
"FOR UPDATE;";
// Channel should exist as it is validated at authentication
var channelData = connection.Query(
selectChannel,
new { channel = message.Channel }
).First();
if (channelData.locked)
{
errorCode = SPVChannelsHTTPError.ChannelLocked.Code;
errorMessage = SPVChannelsHTTPError.ChannelLocked.Description;
return(null);
}
if (channelData.sequenced)
{
string selectUnreadMessageCount =
"SELECT Count(MessageStatus.*) " +
"FROM MessageStatus " +
"WHERE MessageStatus.token = @tokenid " +
" AND MessageStatus.isread = FALSE;";
var unreadCount = connection.ExecuteScalar <long>(
selectUnreadMessageCount,
new { tokenid = message.FromToken }
);
if (unreadCount > 0)
{
errorCode = SPVChannelsHTTPError.SequencingFailure.Code;
errorMessage = SPVChannelsHTTPError.SequencingFailure.Description;
return(null);
}
}
string insertMessage =
"INSERT INTO Message " +
" (fromtoken, channel, seq, receivedts, contenttype, payload) " +
"SELECT @fromtoken, @channel, COALESCE(MAX(seq) + 1, 1) AS seq, @receivedts, @contenttype, @payload " +
"FROM Message " +
"WHERE channel = @channel " +
"RETURNING *;";
messageRes = connection.Query <Message>(insertMessage,
new
{
fromtoken = message.FromToken,
channel = message.Channel,
receivedts = message.ReceivedTS,
contenttype = message.ContentType,
payload = message.Payload
}
).SingleOrDefault();
string insertMessageStatus =
"INSERT INTO MessageStatus " +
" (message, token, isread, isdeleted) " +
"SELECT @messageid, id, " +
" CASE WHEN id = @author THEN TRUE ELSE FALSE END AS isread, " +
" FALSE AS isdeleted " +
"FROM APIToken " +
"WHERE validto IS NULL AND channel = @channel; ";
connection.Execute(insertMessageStatus,
new
{
messageid = messageRes.Id,
channel = message.Channel,
author = message.FromToken
}
);
transaction.Commit();
}
catch (PostgresException e) when(e.SqlState == PostgresErrorCodes.UniqueViolation &&
e.ConstraintName == "message_mailbox_seq_key")
{
logger.LogInformation($"Failed to acquire unique message sequence when inserting new message. Will retry.");
retryInsert = true;
}
} while (retryInsert);
return(messageRes);
}
public IEnumerable <Message> GetMessages(long apiTokenId, bool onlyUnread, out string maxSeq)
{
using var connection = GetNpgsqlConnection();
connection.Open();
using NpgsqlTransaction transaction = connection.BeginTransaction();
string selectChannel =
"SELECT Channel.sequenced " +
"FROM Channel " +
"INNER JOIN APIToken ON APIToken.channel = Channel.id " +
"WHERE APIToken.id = @tokenid " +
"FOR UPDATE";
// Channel should exist as it is validated at authentication
var channelData = connection.Query(
selectChannel,
new { tokenid = apiTokenId }
).First();
if (channelData.sequenced)
{
string selectMaxSequence =
"SELECT MAX(Message.seq) AS max_sequence " +
"FROM Message " +
"INNER JOIN MessageStatus ON MessageStatus.message = Message.id " +
"WHERE MessageStatus.token = @tokenid AND NOT MessageStatus.isdeleted;";
var maxSequence = connection.ExecuteScalar <long?>(
selectMaxSequence,
new { tokenid = apiTokenId }
);
maxSeq = maxSequence.ToString() ?? "0";
}
else
{
maxSeq = "";
}
string selectMessageById =
"SELECT Message.* " +
"FROM Message " +
"INNER JOIN MessageStatus ON MessageStatus.message = Message.id " +
"INNER JOIN APIToken ON MessageStatus.token = APIToken.id " +
"WHERE APIToken.id = @tokenid " +
" AND MessageStatus.isdeleted = false " +
" AND (MessageStatus.isread = false OR @onlyunread = false) " +
"ORDER BY Message.seq;";
var data = connection.Query <Message>(
selectMessageById,
new
{
tokenid = apiTokenId,
onlyunread = onlyUnread
}
).ToArray();
transaction.Commit();
return(data);
}
public bool SaveRdReceipt(RdReceiptDTO ObjRdReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
string Referenceno = string.Empty;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (ObjRdReceiptDTO.pModeofreceipt != "ADJUSTMENT")
{
if (Savegenaralreceipt(ObjRdReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
Referenceno = OUTReceiptid;
}
}
else
{
string adjustrefno = string.Empty;
JournalVoucherNewDTO objJournalVoucherDTO = new JournalVoucherNewDTO();
List <PaymentsNewDTO> _Paymentslist = new List <PaymentsNewDTO>();
objJournalVoucherDTO.pjvdate = ObjRdReceiptDTO.pReceiptdate;
objJournalVoucherDTO.pCreatedby = ObjRdReceiptDTO.pCreatedby;
objJournalVoucherDTO.pnarration = ObjRdReceiptDTO.pNarration;
objJournalVoucherDTO.pmodoftransaction = "MANUAL";
PaymentsNewDTO objPaymentsDTO = new PaymentsNewDTO
{
ppartyid = null,
ptranstype = "C",
psubledgerid = ObjRdReceiptDTO.pSubledgerid,
pamount = Convert.ToDecimal(ObjRdReceiptDTO.pReceivedamount)
};
_Paymentslist.Add(objPaymentsDTO);
objPaymentsDTO = new PaymentsNewDTO
{
ptranstype = "D",
ppartyid = null,
ppartyname = "",
ppartyreferenceid = "",
ppartyreftype = ""
};
// long creditaccountid = memberaccountid;
objPaymentsDTO.psubledgerid = ObjRdReceiptDTO.pSavingsMemberAccountid;
objPaymentsDTO.pamount = Convert.ToDecimal(ObjRdReceiptDTO.pReceivedamount);
_Paymentslist.Add(objPaymentsDTO);
objJournalVoucherDTO.pJournalVoucherlist = _Paymentslist;
AccountingTransactionsDAL Accontstrans = new AccountingTransactionsDAL();
if (Accontstrans.SaveJournalVoucherNew(objJournalVoucherDTO, trans, out adjustrefno))
{
Referenceno = adjustrefno;
}
}
if (string.IsNullOrEmpty(ObjRdReceiptDTO.pRecordid.ToString()) || ObjRdReceiptDTO.pRecordid == 0)
{
sbInsert.Append("INSERT INTO Rd_receipt(rd_receipt_date,member_id,rd_account_id,deposit_type, instalment_amount,penalty_amount, received_amount, mode_of_receipt,receipt_no)VALUES('" + FormatDate(ObjRdReceiptDTO.pReceiptdate) + "'," + ObjRdReceiptDTO.pMemberid + "," + ObjRdReceiptDTO.pAccountid + ",'" + ObjRdReceiptDTO.pDeposittype + "'," + ObjRdReceiptDTO.pInstalmentamount + ", " + ObjRdReceiptDTO.pPenaltyamount + "," + ObjRdReceiptDTO.pReceivedamount + ",'" + ObjRdReceiptDTO.pModeofreceipt + "','" + Referenceno + "');");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
if (ObjRdReceiptDTO.pModeofreceipt == "CASH" || ObjRdReceiptDTO.pModeofreceipt == "ADJUSTMENT")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "select fn_rd_instalments_update('" + ObjRdReceiptDTO.pAccountno + "','" + Referenceno + "')");
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "SELECT FN_PROMOTOR_SALARY_JV(" + ObjRdReceiptDTO.pAccountid + ",'" + FormatDate(ObjRdReceiptDTO.pReceiptdate) + "','RD')");
}
trans.Commit();
Issaved = true;
OUTReceiptid = Referenceno;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveChallanaPayment(ChallanaPaymentDTO _ChallanaPaymentDTO, string Connectionstring, out string _PaymentId)
{
bool IsSaved = false;
StringBuilder sbQuery = new StringBuilder();
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
//if (string.IsNullOrEmpty(_CommissionPaymentDTO.ppaymentid.ToString()))
//{
_ChallanaPaymentDTO.ppaymentid = Convert.ToString(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('PAYMENT VOUCHER','CASH','" + FormatDate(_ChallanaPaymentDTO.pCommissionpaymentDate) + "')"));
//}
long creditaccountid = 0;
string query = "";
long detailsid = 0;
string accountName = string.Empty;
creditaccountid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(Connectionstring, CommandType.Text, "select bankaccountid from tblmstbank where recordid = " + _ChallanaPaymentDTO.pbankid));
query = "insert into tbltranspaymentvoucher( paymentid, paymentdate, modeofpayment, totalpaidamount, narration, creditaccountid, statusid, createdby, createddate)values('" + _ChallanaPaymentDTO.ppaymentid + "', '" + FormatDate(_ChallanaPaymentDTO.pCommissionpaymentDate) + "', '" + ManageQuote(_ChallanaPaymentDTO.ptranstype) + "', " + _ChallanaPaymentDTO.pTotalpaymentamount + ", '" + ManageQuote(_ChallanaPaymentDTO.pNarration) + "', " + creditaccountid + ", " + Convert.ToInt32(Status.Active) + "," + _ChallanaPaymentDTO.pCreatedby + ", current_timestamp) returning recordid";
detailsid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, query));
if (_ChallanaPaymentDTO.pcommissionpaymentlist != null)
{
for (int i = 0; i < _ChallanaPaymentDTO.pcommissionpaymentlist.Count; i++)
{
accountName = Convert.ToString(NPGSqlHelper.ExecuteScalar(Connectionstring, CommandType.Text, "select distinct accountname from tblmstaccounts where accountid =" + _ChallanaPaymentDTO.pcommissionpaymentlist[i].pdebitaccountid + ""));
sbQuery.Append("insert into tbltranspaymentvoucherdetails( detailsid, paymentid,ledgeramount,contactname,debitaccountid)values (" + detailsid + ", '" + _ChallanaPaymentDTO.ppaymentid + "', '" + _ChallanaPaymentDTO.pcommissionpaymentlist[i].ptotalamount + "','" + accountName + "'," + _ChallanaPaymentDTO.pcommissionpaymentlist[i].pdebitaccountid + ");");
sbQuery.Append("update challana_entry_details set voucher_id=(select recordid from tbltranspaymentvoucher where paymentid='" + _ChallanaPaymentDTO.ppaymentid + "') where challana_details_id=" + _ChallanaPaymentDTO.pcommissionpaymentlist[i].pChallanaDetailsId + ";");
}
}
if (_ChallanaPaymentDTO.ptranstype != "CASH")
{
string particulars = "";
if (string.IsNullOrEmpty(_ChallanaPaymentDTO.ptypeofpayment))
{
_ChallanaPaymentDTO.ptypeofpayment = _ChallanaPaymentDTO.ptypeofpayment;
}
if (_ChallanaPaymentDTO.pbankname.Contains('-'))
{
_ChallanaPaymentDTO.pbankname = _ChallanaPaymentDTO.pbankname.Split('-')[0].Trim();
}
if (_ChallanaPaymentDTO.ptranstype == "CHEQUE")
{
sbQuery.Append("update tblmstcheques set statusid =(SELECT statusid from tblmststatus where upper(statusname) ='USED-CHEQUES') where bankid =" + _ChallanaPaymentDTO.pbankid + " and chequenumber=" + _ChallanaPaymentDTO.pchequeno + ";");
sbQuery.Append("insert into tbltranspaymentreference( paymentid, bankname, branchname, transtype, typeofpayment, bankid, chequenumber,cardnumber , upiid , upiname, paymentdate, paidamount, clearstatus, particulars, statusid, createdby, createddate ) values('" + _ChallanaPaymentDTO.ppaymentid + "', '" + _ChallanaPaymentDTO.pbankname + "', '" + _ChallanaPaymentDTO.pbranchname + "', '" + _ChallanaPaymentDTO.ptranstype + "', '" + _ChallanaPaymentDTO.ptranstype + "', " + _ChallanaPaymentDTO.pbankid + ", '" + _ChallanaPaymentDTO.pchequeno + "', '" + _ChallanaPaymentDTO.pdebitcard + "', '" + _ChallanaPaymentDTO.pUpiid + "', '" + _ChallanaPaymentDTO.pUpiname + "', '" + FormatDate(_ChallanaPaymentDTO.pCommissionpaymentDate) + "', " + _ChallanaPaymentDTO.pTotalpaymentamount + ", 'N', '" + particulars + "', " + Convert.ToInt32(Status.Active) + "," + _ChallanaPaymentDTO.pCreatedby + ", current_timestamp);");
}
else if (_ChallanaPaymentDTO.ptranstype == "ONLINE")
{
sbQuery.Append("insert into tbltranspaymentreference( paymentid, bankname, branchname, transtype, typeofpayment, bankid, chequenumber,cardnumber , upiid , upiname, paymentdate, paidamount, clearstatus, particulars, statusid, createdby, createddate ) values('" + _ChallanaPaymentDTO.ppaymentid + "', '" + _ChallanaPaymentDTO.pBankname + "', '" + _ChallanaPaymentDTO.pBankbranchname + "', '" + _ChallanaPaymentDTO.ptranstype + "', '" + _ChallanaPaymentDTO.ptypeofpayment + "', " + _ChallanaPaymentDTO.pbankid + ", '" + _ChallanaPaymentDTO.preferencenoonline + "', '" + _ChallanaPaymentDTO.pdebitcard + "', '" + _ChallanaPaymentDTO.pUpiid + "', '" + _ChallanaPaymentDTO.pUpiname + "', '" + FormatDate(_ChallanaPaymentDTO.pCommissionpaymentDate) + "', " + _ChallanaPaymentDTO.pTotalpaymentamount + ", 'N', '" + particulars + "', " + Convert.ToInt32(Status.Active) + "," + _ChallanaPaymentDTO.pCreatedby + ", current_timestamp);");
}
else if (_ChallanaPaymentDTO.ptranstype == "DEBIT CARD")
{
sbQuery.Append("insert into tbltranspaymentreference( paymentid, bankname, branchname, transtype, typeofpayment, bankid, chequenumber,cardnumber , upiid , upiname, paymentdate, paidamount, clearstatus, particulars, statusid, createdby, createddate ) values('" + _ChallanaPaymentDTO.ppaymentid + "', '" + _ChallanaPaymentDTO.pBankname + "', '" + _ChallanaPaymentDTO.pBankbranchname + "', '" + _ChallanaPaymentDTO.ptranstype + "', '" + _ChallanaPaymentDTO.ptranstype + "', " + _ChallanaPaymentDTO.pbankid + ", '" + _ChallanaPaymentDTO.preferencenodcard + "', '" + _ChallanaPaymentDTO.pdebitcard + "', '" + _ChallanaPaymentDTO.pUpiid + "', '" + _ChallanaPaymentDTO.pUpiname + "', '" + FormatDate(_ChallanaPaymentDTO.pCommissionpaymentDate) + "', " + _ChallanaPaymentDTO.pTotalpaymentamount + ", 'N', '" + particulars + "', " + Convert.ToInt32(Status.Active) + "," + _ChallanaPaymentDTO.pCreatedby + ", current_timestamp);");
}
}
if (!string.IsNullOrEmpty(sbQuery.ToString()))
{
sbQuery.Append("select fntotaltransactions('" + _ChallanaPaymentDTO.ppaymentid + "','PAYMENT VOUCHER');");
// select accountsupdate();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbQuery.ToString());
trans.Commit();
IsSaved = true;
}
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
_PaymentId = _ChallanaPaymentDTO.ppaymentid;
return(IsSaved);
}
public List <Classlist> ReadClasslist(ClassMeeting classMeeting)
{
conn = DAO.getConnection();
Classlist readClasslist = new Classlist();
List <Classlist> classLists = new List <Classlist>();
int id;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand(DAOClasslistResource.ReadClasslistByClassSP, conn);
NpgsqlParameter parameter = new NpgsqlParameter();
parameter.ParameterName = DAOClasslistResource.Id;
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = classMeeting.Id;
command.Parameters.Add(parameter);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
id = dr.GetInt32(0);
String classId = dr.GetString(1);
DateTime classDate = dr.GetDateTime(2);
String classLink = dr.GetString(3);
String classComment = dr.GetString(4);
String studentEmail = dr.GetString(5);
String studentName = dr.GetString(6);
String studentLastname = dr.GetString(7);
Student student = new Student();
student.Email = studentEmail;
student.Name = studentName;
student.LastName = studentLastname;
classMeeting.StartTime = classDate;
classMeeting.JoinUrl = classLink;
classMeeting.Comment = classComment;
readClasslist = new Classlist(id, classMeeting, student);
classLists.Add(readClasslist);
}
dr.Close();
tran.Commit();
return(classLists);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
}
public void Commit()
{
_transaction.Commit();
}
public void Complete()
{
_transaction?.Commit();
}
/// Run transaction
public static functionResult ExeNonQuery(string sql, params string[] p_str)
{
using (NpgsqlCommand command = new NpgsqlCommand())
{
#region Npgsql connection
NpgsqlConnection conn = new NpgsqlConnection();
conn.ConnectionString = Settings.retConnStr();
// トランザクションを開始します。
try
{
conn.Open();
}
catch (NpgsqlException)
{
conn.Close();
return(functionResult.connectionError);
}
catch (System.IO.IOException)
{
conn.Close();
return(functionResult.connectionError);
}
#endregion
NpgsqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
command.CommandText = sql;
command.Connection = conn;
//MessageBox.Show(sql);
string p;
for (int i = 0; i < p_str.Length; i++)
{
p = ":p" + i.ToString();
//MessageBox.Show(p + " to " + p_str[i]);
command.Parameters.Add(new NpgsqlParameter(p, p_str[i]));
}
command.Transaction = transaction;
command.ExecuteNonQuery();
//Commit transaction
transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show("[ExeNonQuery]" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//Roll back transaction
transaction.Rollback();
conn.Close();
//throw ex;
return(functionResult.failed);
}
conn.Close();
}
return(functionResult.success);
}
/// <summary>
/// Parse the url segments and return the data as serialized JSON
/// </summary>
/// <param name="fileName">
/// The exchange file name.
/// </param>
/// <param name="password">
/// The optional archive password as supplied by the request
/// </param>
/// <param name="seed">
/// Optional seed indicator which removes any existing data first if true
/// </param>
/// <returns>
/// True if successful
/// </returns>
internal bool InsertModelData(string fileName, string password = null, bool seed = true)
{
NpgsqlConnection connection = null;
NpgsqlTransaction transaction = null;
try
{
var sw = new Stopwatch();
if (seed)
{
// clear database schemas if seeding
Logger.Info("Start clearing the current data store");
transaction = this.TransactionManager.SetupTransaction(ref connection, null);
this.TransactionManager.SetFullAccessState(true);
this.ClearDatabaseSchemas(transaction);
transaction.Commit();
}
sw.Start();
Logger.Info("Start seeding the data");
// use new transaction to for inserting the data
transaction = this.TransactionManager.SetupTransaction(ref connection, null);
this.TransactionManager.SetFullAccessState(true);
// important, make sure to defer the constraints
var command = new NpgsqlCommand("SET CONSTRAINTS ALL DEFERRED;", transaction.Connection, transaction);
command.ExecuteAndLogNonQuery(this.TransactionManager.CommandLogger);
// make sure to only log insert changes, no subsequent trigger updates for exchange import
this.TransactionManager.SetAuditLoggingState(transaction, false);
// get sitedirectory data
var items = this.ExchangeFileProcessor.ReadSiteDirectoryFromfile(fileName, password).ToList();
// assign default password to all imported persons.
foreach (var person in items.OfType <Person>())
{
person.Password = AppConfig.Current.Defaults.PersonPassword;
}
var topContainer = items.SingleOrDefault(x => x.IsSameOrDerivedClass <TopContainer>()) as TopContainer;
if (topContainer == null)
{
Logger.Error("No Topcontainer item encountered");
throw new NoNullAllowedException("Topcontainer item needs to be present in the dataset");
}
this.RequestUtils.Cache = new List <Thing>(items);
// setup Site Directory schema
using (var siteDirCommand = new NpgsqlCommand())
{
Logger.Info("Start Site Directory structure");
siteDirCommand.ReadSqlFromResource("CDP4Orm.AutoGenStructure.SiteDirectoryDefinition.sql");
siteDirCommand.Connection = transaction.Connection;
siteDirCommand.Transaction = transaction;
siteDirCommand.ExecuteAndLogNonQuery(this.TransactionManager.CommandLogger);
}
// apply migration on new SiteDirectory partition
this.MigrationService.ApplyMigrations(transaction, typeof(SiteDirectory).Name, false);
var result = false;
if (topContainer.GetType().Name == "SiteDirectory")
{
// make sure single iterationsetups are set to unfrozen before persitence
this.FixSingleIterationSetups(items);
var siteDirectoryService =
this.ServiceProvider.MapToPersitableService <SiteDirectoryService>("SiteDirectory");
result = siteDirectoryService.Insert(transaction, "SiteDirectory", topContainer);
}
if (result)
{
this.RequestUtils.QueryParameters = new QueryParameters();
// Add missing Person permissions
this.CreateMissingPersonPermissions(transaction);
var engineeringModelSetups =
items.OfType <EngineeringModelSetup>()
.ToList();
var engineeringModelService =
this.ServiceProvider.MapToPersitableService <EngineeringModelService>("EngineeringModel");
var iterationService = this.ServiceProvider.MapToPersitableService <IterationService>("Iteration");
foreach (var engineeringModelSetup in engineeringModelSetups)
{
// cleanup before handling TopContainer
this.RequestUtils.Cache.Clear();
// get referenced engineeringmodel data
var engineeringModelItems = this.ExchangeFileProcessor
.ReadEngineeringModelFromfile(fileName, password, engineeringModelSetup).ToList();
// should return one engineeringmodel topcontainer
var engineeringModel = engineeringModelItems.OfType <EngineeringModel>().Single();
if (engineeringModel == null)
{
result = false;
break;
}
var dataPartition = CDP4Orm.Dao.Utils.GetEngineeringModelSchemaName(engineeringModel.Iid);
this.RequestUtils.Cache = new List <Thing>(engineeringModelItems);
if (!engineeringModelService.Insert(transaction, dataPartition, engineeringModel))
{
result = false;
break;
}
// Add missing Participant permissions
this.CreateMissingParticipantPermissions(transaction);
// extract any referenced file data to disk if not already present
this.PersistFileBinaryData(fileName, password);
var iterationSetups = items.OfType <IterationSetup>()
.Where(
x => engineeringModelSetup.IterationSetup.Contains(x.Iid)).ToList();
// get current maximum iterationNumber and increase by one for the next value
int maxIterationNumber = iterationSetups.Select(x => x.IterationNumber).Max() + IterationNumberSequenceInitialization;
// reset the start number of iterationNumber sequence
this.EngineeringModelDao.ResetIterationNumberSequenceStartNumber(
transaction,
dataPartition,
maxIterationNumber);
var iterationInsertResult = true;
foreach (var iterationSetup in iterationSetups)
{
this.RequestUtils.Cache.Clear();
var iterationItems = this.ExchangeFileProcessor
.ReadModelIterationFromFile(fileName, password, iterationSetup).ToList();
// FixRevisionNumber(iterationItems);
this.RequestUtils.Cache = new List <Thing>(iterationItems);
// should return one iteration
var iteration =
iterationItems.SingleOrDefault(x => x.ClassKind == ClassKind.Iteration) as Iteration;
if (iteration == null || !iterationService.CreateConcept(
transaction,
dataPartition,
iteration,
engineeringModel))
{
iterationInsertResult = false;
break;
}
}
if (!iterationInsertResult)
{
result = false;
break;
}
// extract any referenced file data to disk if not already present
this.PersistFileBinaryData(fileName, password);
}
}
transaction.Commit();
sw.Stop();
Logger.Info("Finished seeding the data store in {0} [ms]", sw.ElapsedMilliseconds);
return(result);
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
Logger.Error(ex, "Error occured during data store seeding");
return(false);
}
finally
{
// clean log (will happen at end of request as well due to IOC lifetime
this.TransactionManager.CommandLogger.ClearLog();
if (transaction != null)
{
transaction.Dispose();
}
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
/// <summary>
/// Set changes in database table
/// </summary>
/// <param name="_list"></param>
/// <param name="name_table"></param>
/// <param name="guid_col"></param>
/// <returns></returns>
public async Task <int> PSTRG_Changes_to_dataTable(Changes_List <T> _list, string name_table, string guid_col, string[] query_before, string [] query_after)
{
try
{
Dictionary <string, int> P_columns = new Dictionary <string, int>();
Dictionary <int, Type> P_types = new Dictionary <int, Type>();
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
int counter = 0;
foreach (var p in Accessors)
{
P_types.Add(counter, p.PropertyInfo.PropertyType);
P_columns.Add(p.PropertyInfo.Name.ToLower(), counter);
counter++;
}
List <Npgsql_Schema_fields> Schema = await Get_shema(name_table, P_columns);
using (NpgsqlConnection conO = new NpgsqlConnection(npC))
{
await conO.OpenAsync();
using (NpgsqlTransaction npgsqlTransaction = conO.BeginTransaction())
{
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET start_update=current_timestamp, in_progress=true,updt_errors=false " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
if (query_before != null)
{
foreach (string comm in query_before)
{
using (NpgsqlCommand cmd = new NpgsqlCommand(comm, conO))
{
cmd.ExecuteNonQuery();
}
}
}
if (_list.Delete.Count > 0)
{
string comand = "DELETE FROM " + name_table;
string tbl_values = " WHERE ";
string param_values = "=";
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conO;
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (guid_col == nam && _Fields.Dtst_col != 10000)
{
tbl_values += nam;
param_values = "=@" + nam.ToLower();
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values + param_values;
cmd.Prepare();
foreach (T row in _list.Delete)
{
cmd.Parameters[0].Value = Accessors[P_columns[guid_col]].GetValue(row);
cmd.ExecuteNonQuery();
}
}
}
if (_list.Update.Count > 0)
{
string comand = "UPDATE " + name_table + " SET";
string tbl_values = " ";
string param_values = " WHERE ";
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conO;
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (_Fields.Dtst_col != 10000)
{
if (nam.ToLower() == guid_col.ToLower())
{
param_values = param_values + nam + "=@" + nam.ToLower();
}
else
{
tbl_values = tbl_values + nam + "=@" + nam.ToLower() + ",";
}
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values.Substring(0, tbl_values.Length - 1) + " " + param_values;
cmd.Prepare();
foreach (T row in _list.Update)
{
foreach (Npgsql_Schema_fields _field in Schema)
{
if (_field.Dtst_col != 10000)
{
cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
if (_list.Insert.Count > 0)
{
string comand = "INSERT INTO " + name_table;
string tbl_values = "(";
string param_values = " VALUES (";
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conO;
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (_Fields.Dtst_col != 10000)
{
tbl_values = tbl_values + nam + ",";
param_values = param_values + "@" + nam.ToLower() + ",";
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values.Substring(0, tbl_values.Length - 1) + ")" + param_values.Substring(0, param_values.Length - 1) + ")";
cmd.Prepare();
foreach (T row in _list.Insert)
{
foreach (Npgsql_Schema_fields _field in Schema)
{
if (_field.Field_name.ToLower() == guid_col.ToLower())
{
if (_field.Field_type == NpgsqlTypes.NpgsqlDbType.Uuid)
{
cmd.Parameters[_field.DB_Col_number].Value = Guid.NewGuid();
}
else
{
cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value;
}
}
else if (_field.Dtst_col != 10000)
{
cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
if (query_after != null)
{
foreach (string comm in query_after)
{
using (NpgsqlCommand cmd = new NpgsqlCommand(comm, conO))
{
cmd.ExecuteNonQuery();
}
}
}
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET last_modify=current_timestamp, in_progress=false " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
npgsqlTransaction.Commit();
}
}
return(0);
}
catch (Exception e)
{
using (NpgsqlConnection conO = new NpgsqlConnection(npC))
{
await conO.OpenAsync();
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET in_progress=false,updt_errors=true " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
}
Loger.Log("Error in update table :" + name_table + " :" + e.StackTrace);
return(1);
}
}
private object PgSQLObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
CommandText = CommandText.ToLower();
NpgsqlConnection conn = new NpgsqlConnection(ConString);
conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand theCmd = new NpgsqlCommand(CommandText, conn);
theCmd.CommandType = CommandType.StoredProcedure;
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
}
if (CommandText.Substring(0, 14).ToUpper() == "UPDATE IQTOOLS")
{
theCmd.CommandType = CommandType.Text;
}
if (CommandText.Substring(0, 4).ToUpper() == "WITH") //CTE
{
theCmd.CommandType = CommandType.Text;
}
for (i = 1; i <= Params.Count;)
{
cmdpara = Params[i].ToString();
if (cmdpara.Contains("@"))
{
cmdpara = cmdpara.Substring(1, cmdpara.Length - 1);
}
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
try
{
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
DataSet ds = new DataSet();
int j = 0;
int k = 0;
NpgsqlDataReader dr = theCmd.ExecuteReader();
for (j = 0; j <= 10; j++)
{
if (!dr.IsClosed)
{
dr.Close();
}
dr = theCmd.ExecuteReader();
for (k = 0; k < j; k++)
{
dr.NextResult();
}
//if (dr.HasRows)
//{
ds.Tables.Add();
//if (dr.HasRows)
//{
ds.Tables[j].Load(dr);
//}
//}
//else
//{
// break;
//}
}
//tran.Commit();
return(ds);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
DataTable theDT = new DataTable();
NpgsqlDataReader dr = theCmd.ExecuteReader();
theDT.Load(dr);
tran.Commit();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
DataTable theDT = new DataTable();
NpgsqlDataReader dr = theCmd.ExecuteReader();
theDT.Load(dr);
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
tran.Commit();
return(NoRowsAffected);
}
return(0);
}
catch (Exception err)
{
throw err;
}
finally
{
conn.Close();
}
}
public void CommitTransaction()
{
_transaction?.Commit();
}
public Student ReadStudent(Student student)
{
conn = DAO.getConnection();
Student readStudent = new Student();
String email;
String password;
String name;
String lastName;
DateTime birthDate;
String phone;
String country;
int gradeId;
String gradeName;
Char status;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand(DAOStudentResource.ReadStudentSP, conn);
NpgsqlParameter parameter = new NpgsqlParameter();
parameter.ParameterName = DAOStudentResource.Email;
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = student.Email;
command.Parameters.Add(parameter);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
email = dr.GetString(0);
password = dr.GetString(1);
name = dr.GetString(2);
lastName = dr.GetString(3);
birthDate = dr.GetDateTime(4);
phone = dr.GetString(5);
country = dr.GetString(6);
status = dr.GetChar(7);
gradeId = dr.GetInt32(8);
gradeName = dr.GetString(9);
Grade grade = new Grade(gradeId, gradeName);
readStudent = new Student(email, password, name, lastName, birthDate, phone, country, grade);
readStudent.Status = status;
}
dr.Close();
tran.Commit();
return(readStudent);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
}
