public string Save(NewLoan x)
{
try {
db.Loan();
db.Account();
bool isNewLoan = false;
string manipulativeCostsId = Guid.NewGuid().ToString();
string withdrawId = Guid.NewGuid().ToString();
if (string.IsNullOrEmpty(x.id))
{
x.id = Guid.NewGuid().ToString();
isNewLoan = true;
}
if (isNewLoan && x.restToRepayment > 0 && x.repaidOldDebt) // x.repaidOldDebt je za slucaj ako se greskom uplati na neciji žiro racun iako nije trazio pozajmicu, u tom slucaju treba biti false
{
UpdateActiveLoan(x); //********** Ako postoji pozajmica koja nije otplacena onda se ona otplacuje sa dijelom nove pozajmice.
}
string sql = string.Format(@"BEGIN TRAN
IF EXISTS (SELECT * from Loan WITH (updlock,serializable) WHERE id = '{0}')
BEGIN
UPDATE Loan SET userId = '{1}', loan = '{2}', loanDate = '{3}', repayment = '{4}', manipulativeCosts = '{5}', withdraw = '{6}', dedline = '{7}', isRepaid = '{8}', note = '{9}' WHERE id = '{0}';
UPDATE Account SET userId = '{1}', amount = '{5}', recordDate = '{3}', mo = '{10}', yr = '{11}', note = 'Manipulativni troškovi' WHERE loanId = '{0}' AND recordType = 'manipulativeCosts';
UPDATE Account SET userId = '{1}', amount = '{6}', recordDate = '{3}', mo = '{10}', yr = '{11}', note = 'Isplata pozajmice' WHERE loanId = '{0}' AND recordType = 'withdraw';
UPDATE Account SET userId = '{1}', recordDate = '{3}', mo = '{10}', yr = '{11}' WHERE loanId = '{0}' AND recordType = 'loan';
END
ELSE
BEGIN
INSERT INTO Loan (id, userId, loan, loanDate, repayment, manipulativeCosts, withdraw, dedline, isRepaid, note)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}')
INSERT INTO Account (id, userId, amount, recordDate, mo, yr, recordType, loanId, note)
VALUES ('{12}', '{1}', '{5}', '{3}', '{10}', '{11}', 'manipulativeCosts', '{0}', 'Manipulativni troškovi')
INSERT INTO Account (id, userId, amount, recordDate, mo, yr, recordType, loanId, note)
VALUES ('{13}', '{1}', '{6}', '{3}', '{10}', '{11}', 'withdraw', '{0}', 'Isplata pozajmice')
END
COMMIT TRAN", x.id, x.user.id, x.loan, x.loanDate, x.repayment, x.manipulativeCosts, x.withdraw, x.dedline, x.isRepaid, x.note, g.GetMonth(x.loanDate), g.GetYear(x.loanDate), manipulativeCostsId, withdrawId);
using (SqlConnection connection = new SqlConnection(g.connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection)) {
command.ExecuteNonQuery();
}
connection.Close();
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) {
return(JsonConvert.SerializeObject("Error: " + e.Message, Formatting.None));
}
}
public double GetAmount(string id, string type)
{
db.Account();
double x = 0;
string sql = string.Format(@"SELECT SUM(CONVERT(decimal(10,2), amount)) FROM Account WHERE userId = '{0}' AND recordType = '{1}'", id, type);
using (SqlConnection connection = new SqlConnection(g.connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x = reader.GetValue(0) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetDecimal(0));
}
}
}
connection.Close();
}
return(x);
}
