public Response insertConfiguration(ConfigurationIntegration configurationIntegration)
{
using (var sqlConnection = new SqlConnection(conexion))
{
try
{
var queryVerify = "SELECT idCompany from tblConIntegration where idCompany=" + configurationIntegration.idCompany;
int res = sqlConnection.ExecuteScalar <int>(queryVerify, commandType: System.Data.CommandType.Text);
if (res == 0)
{
var query = "insert into tblConIntegration (idCash,idFiscalCredit,idFiscalDebit,idPurchases,idSales,idIt,idItToPay,idCompany,isIntegration)" +
" values (@idCash,@idFiscalCredit,@idFiscalDebit,@idPurchases,@idSales,@idIt,@idItToPay,@idCompany,@isIntegration)";
sqlConnection.Execute(query, configurationIntegration);
return(new Response {
Done = true, Message = "Agregado Exitosamente ", Value = 1
});
}
else
{
//Edit
var query = "update tblConIntegration set idCash=@idCash,idFiscalCredit=@idFiscalCredit,idFiscalDebit=@idFiscalDebit,idPurchases=@idPurchases," +
"idSales=@idSales,idIt=@idIt,idItToPay=@idItToPay,isIntegration=@isIntegration where idCompany = @idCompany";
sqlConnection.Execute(query, configurationIntegration);
return(new Response {
Done = true, Message = "Editado exitosamente", Value = 1
});
}
} catch (Exception e)
{
return(new Response {
Done = false, Message = e.Message, Value = 0
});
}
}
}
public ConfigurationDTO checkIfExist(int idCompany)
{
using (var sqlConnection = new SqlConnection(conexion))
{
ConfigurationDTO configurationDTO = new ConfigurationDTO();
try
{
var queryVerify = "SELECT * from tblConIntegration where idCompany=" + idCompany;
ConfigurationIntegration conf = sqlConnection.Query <ConfigurationIntegration>(queryVerify, commandType: System.Data.CommandType.Text).First();
if (conf == null)
{
configurationDTO.response = new Response {
Done = false, Message = "Error", Value = 0
};
return(configurationDTO);
}
else
{
configurationDTO.response = new Response {
Done = true, Message = "Existe", Value = 1
};
configurationDTO.configuration = conf;
return(configurationDTO);
}
}
catch (Exception e)
{
configurationDTO.response = new Response {
Done = false, Message = e.Message, Value = 0
};
return(configurationDTO);
}
}
}
public List <VoucherDetail> makeListVoucher(ConfigurationIntegration configurationIntegration, float total, int idVoucher)
{
List <VoucherDetail> listVoucher = new List <VoucherDetail>();
VoucherDetail compras = new VoucherDetail();
VoucherDetail creditoFiscal = new VoucherDetail();
VoucherDetail caja = new VoucherDetail();
caja.amountAssets = (double)total;
caja.idAccount = configurationIntegration.idCash;
caja.gloss = "Compra de Mercaderias";
caja.idVoucher = idVoucher;
caja.idUser = 1;
creditoFiscal.amountOwed = (double)total * (double)0.13;
creditoFiscal.gloss = "Compra de Mercaderias";
creditoFiscal.idVoucher = idVoucher;
creditoFiscal.idUser = 1;
creditoFiscal.idAccount = configurationIntegration.idFiscalCredit;
compras.amountOwed = (double)total - creditoFiscal.amountOwed;
compras.gloss = "Compra de Mercaderias";
compras.idVoucher = idVoucher;
compras.idUser = 1;
compras.idAccount = configurationIntegration.idPurchases;
listVoucher.Add(compras);
listVoucher.Add(creditoFiscal);
listVoucher.Add(caja);
return(listVoucher);
}
public JsonResult insertConfiguration(ConfigurationIntegration configurationIntegration)
{
var company = (Company)Session["company"];
configurationIntegration.idCompany = company.idCompany;
var configuration = bsnConfiguration.insertConfiguration(configurationIntegration);
return(Json(new { data = configuration }, JsonRequestBehavior.AllowGet));
}
public List <VoucherDetail> makeListVoucherSale(ConfigurationIntegration configurationIntegration, float total, int idVoucher)
{
List <VoucherDetail> listVoucher = new List <VoucherDetail>();
VoucherDetail caja = new VoucherDetail();
VoucherDetail it = new VoucherDetail();
VoucherDetail ventas = new VoucherDetail();
VoucherDetail debitoFiscal = new VoucherDetail();
VoucherDetail itxPagar = new VoucherDetail();
string gloss = "Venta de Mercaderias ";
caja.amountOwed = (double)total;
caja.idAccount = configurationIntegration.idCash;
caja.gloss = gloss;
caja.idVoucher = idVoucher;
caja.idUser = 1;
it.amountOwed = (double)total * (double)0.03;
it.gloss = gloss;
it.idVoucher = idVoucher;
it.idUser = 1;
it.idAccount = configurationIntegration.idIt;
debitoFiscal.amountAssets = (double)total * (double)0.13;
debitoFiscal.gloss = gloss;
debitoFiscal.idVoucher = idVoucher;
debitoFiscal.idUser = 1;
debitoFiscal.idAccount = configurationIntegration.idFiscalDebit;
ventas.amountAssets = caja.amountOwed - debitoFiscal.amountAssets;
ventas.gloss = gloss;
ventas.idVoucher = idVoucher;
ventas.idUser = 1;
ventas.idAccount = configurationIntegration.idSales;
itxPagar.amountAssets = (double)total * (double)0.03;
itxPagar.gloss = gloss;
itxPagar.idVoucher = idVoucher;
itxPagar.idUser = 1;
itxPagar.idAccount = configurationIntegration.idItToPay;
listVoucher.Add(caja);
listVoucher.Add(it);
listVoucher.Add(ventas);
listVoucher.Add(debitoFiscal);
listVoucher.Add(itxPagar);
return(listVoucher);
}
public Response insertConfiguration(ConfigurationIntegration configurationIntegration)
{
return(classConfiguration.insertConfiguration(configurationIntegration));
}
public NoteDTO insertBuyNote(Note note, List <Lote> lotes)
{
using (var sqlConnection = new SqlConnection(conexion))
{
sqlConnection.Open();
NoteDTO noteDTO = new NoteDTO();
using (var transaction = sqlConnection.BeginTransaction())
{
try
{
note.typeNote = 1;
string sqlInsertNote = "insert into tblNotes (nroNote,dateNote,description,total,typeNote,idCompany,idUser,idVoucher) values (@nroNote,@dateNote," +
"@description,@total,@typeNote,@idCompany,@idUser,@idVoucher)SELECT SCOPE_IDENTITY()";
var idRes = sqlConnection.ExecuteScalar(sqlInsertNote, note, transaction: transaction);
int idNote = Convert.ToInt32(idRes);
string getNote = "select * from tblNotes where idNote = " + idNote;
Note noteGet = sqlConnection.Query <Note>(getNote, commandType: System.Data.CommandType.Text, transaction: transaction).FirstOrDefault();
noteDTO.note = noteGet;
if (lotes.Count > 0)
{
foreach (var lote in lotes)
{
lote.stock = lote.quantityLote;
//Get number lote by article
string sqlGetNroLote = "select top 1 nroLote from tblLotes where idArticle = " + lote.idArticle + " ORDER BY nroLote desc ";
int nroLote = sqlConnection.ExecuteScalar <int>(sqlGetNroLote, commandType: System.Data.CommandType.Text, transaction: transaction) + 1;
this.insertVoucherBuyNote(note.dateNote);
//insert Lote
lote.nroLote = nroLote;
lote.idNote = idNote;
lote.dateEntry = note.dateNote;
string sqlInsertLote = "insert into tblLotes (idArticle,nroLote,idNote,dateEntry,dueDate,quantityLote,price,stock) values (@IdArticle,@nroLote,@idNote,@dateEntry,@dueDate,@quantityLote,@price,@stock)";
if (lote.dueDate.ToString() == "1/1/0001 00:00:00")
{
sqlInsertLote = "insert into tblLotes (idArticle,nroLote,idNote,dateEntry,dueDate,quantityLote,price,stock) values (@IdArticle,@nroLote,@idNote,@dateEntry,null,@quantityLote,@price,@stock)";
}
sqlConnection.Execute(sqlInsertLote, lote, transaction: transaction);
//lote.idNote = Convert.ToInt32(idNoteInserted);
}
noteDTO.lotes = lotes;
}
//Obtengo la configuracion de las cuentas para generar el comprobante
string getConfiguration = "select * from tblConIntegration where idCompany = " + note.idCompany + " and isIntegration = 1";
ConfigurationIntegration configurationIntegration = sqlConnection.Query <ConfigurationIntegration>(getConfiguration, transaction: transaction).FirstOrDefault();
//Obtengo la moneda actual
if (configurationIntegration == null)
{
transaction.Commit();
noteDTO.response = new Response {
Done = true, Message = "Creado exitosamente", Value = 0
};
return(noteDTO);
}
string getCurrencyMain = "select * from tblCompanyCurrency where idCompany = " + note.idCompany + " and active = 1 ";
var currency = sqlConnection.Query <CompanyCurrency>(getCurrencyMain, commandType: System.Data.CommandType.Text, transaction: transaction).FirstOrDefault();
Voucher voucher = new Voucher();
voucher.dateVoucher = note.dateNote;
voucher.gloss = "Compra de Mercaderia";
voucher.typeVoucher = 2;
voucher.tc = currency.exchange;
voucher.idUser = 1;
voucher.idCompany = note.idCompany;
voucher.idCurrency = currency.idCurrencyMain;
voucher.serieVoucher = getIdNext(note.idCompany);
List <VoucherDetail> listVoucher = new List <VoucherDetail>();
VoucherDTO voucherDTO = _voucher.sendAll(voucher, makeListVoucher(configurationIntegration, note.total, voucher.idVoucher));
if (voucherDTO.response.Done == true)
{
transaction.Commit();
noteDTO.lotes = lotes;
noteDTO.response = new Response {
Done = true, Message = "Creado exitosamente", Value = 0
};
return(noteDTO);
}
else
{
transaction.Rollback();
noteDTO.response = new Response {
Done = false, Message = voucherDTO.response.Message, Value = 0
};
return(noteDTO);
}
}catch (Exception e)
{
transaction.Rollback();
noteDTO.response = new Response {
Done = false, Message = "Error", Value = 0
};
return(noteDTO);
}
}
}
}
public DetailDTO insertSaleNote(Note note, List <Detail> details)
{
using (var sqlConnection = new SqlConnection(conexion))
{
sqlConnection.Open();
DetailDTO detailDTO = new DetailDTO();
using (var transaction = sqlConnection.BeginTransaction())
{
try
{
note.typeNote = 2;
string sqlInsertNote = "insert into tblNotes (nroNote,dateNote,description,total,typeNote,idCompany,idUser,idVoucher) values (@nroNote,@dateNote," +
"@description,@total,@typeNote,@idCompany,@idUser,@idVoucher)SELECT SCOPE_IDENTITY()";
var idRes = sqlConnection.ExecuteScalar(sqlInsertNote, note, transaction: transaction);
int idNote = Convert.ToInt32(idRes);
string getNote = "select * from tblNotes where idNote = " + idNote;
Note noteGet = sqlConnection.Query <Note>(getNote, commandType: System.Data.CommandType.Text, transaction: transaction).FirstOrDefault();
detailDTO.note = noteGet;
if (details.Count > 0)
{
var dateEntry = DateTime.Now;
foreach (var detail in details)
{
detail.dateEntry = dateEntry;
detail.idNote = idNote;
detail.idVoucher = 1;
string sqlInsertLote = "insert into tblDetail (idArticle,nroLote,idNote,quantityDetail,priceSale,idVoucher)" +
" values (@IdArticle,@nroLote,@idNote,@quantityDetail,@priceSale,@idVoucher)";
sqlConnection.Execute(sqlInsertLote, detail, transaction: transaction);
////Edit Article
string updateArticle = "update tblArticles set quantity = quantity -" + detail.quantityDetail + " where idArticle = " + detail.idArticle;
sqlConnection.Execute(updateArticle, transaction: transaction);
}
detailDTO.details = details;
}
//Obtengo la configuracion de las cuentas para generar el comprobante
string getConfiguration = "select * from tblConIntegration where idCompany = " + note.idCompany + " and isIntegration = 1";
ConfigurationIntegration configurationIntegration = sqlConnection.Query <ConfigurationIntegration>(getConfiguration, transaction: transaction).FirstOrDefault();
//Obtengo la moneda actual
string getCurrencyMain = "select * from tblCompanyCurrency where idCompany = " + note.idCompany + " and active = 1 ";
var currency = sqlConnection.Query <CompanyCurrency>(getCurrencyMain, commandType: System.Data.CommandType.Text, transaction: transaction).FirstOrDefault();
if (configurationIntegration == null)
{
transaction.Commit();
detailDTO.response = new Response {
Done = true, Message = "Creado exitosamente", Value = 0
};
return(detailDTO);
}
Voucher voucher = new Voucher();
voucher.dateVoucher = note.dateNote;
voucher.gloss = "Venta de Mercaderias";
voucher.typeVoucher = 1;
voucher.tc = currency.exchange;
voucher.idUser = 1;
voucher.idCompany = note.idCompany;
voucher.idCurrency = currency.idCurrencyMain;
voucher.serieVoucher = getIdNext(note.idCompany);
List <VoucherDetail> listVoucher = new List <VoucherDetail>();
VoucherDTO voucherDTO = _voucher.sendAll(voucher, makeListVoucherSale(configurationIntegration, note.total, voucher.idVoucher));
if (voucherDTO.response.Done == true)
{
transaction.Commit();
detailDTO.details = details;
detailDTO.response = new Response {
Done = true, Message = "Creado exitosamente", Value = 0
};
return(detailDTO);
}
else
{
transaction.Rollback();
detailDTO.response = new Response {
Done = false, Message = voucherDTO.response.Message, Value = 0
};
return(detailDTO);
}
}catch (Exception e)
{
transaction.Rollback();
detailDTO.response = new Response {
Done = false, Message = "Error", Value = 0
};
return(detailDTO);
}
}
}
}
