package statement import ( connection "cf-capital/module_server/handlers/connection" eloquent "cf-capital/module_server/handlers/eloquent" financial "cf-capital/module_server/handlers/investment/financial" "context" "database/sql" "errors" "fmt" "math" "math/rand" "strings" "time" "encoding/csv" "os" log "github.com/rs/zerolog/log" ) type Statement interface { Flexi() } type FlexiPlan struct { AccountId int64 `json:"account_id"` Month int64 `json:"month"` XIRR float64 `json:"xirr"` MonthlyROI float64 `json:"monthly_roi"` Cumulative float64 `json:"cumulative"` StartDate string `json:"start_date"` EndDate string `json:"end_date"` DiffInDays int64 Principal float64 `json:"principal"` TotalInterest float64 TotalPayout float64 TotalInterestEarned float64 ForIndividualInvestment string `json:"for_individual_investment"` IndividualInvestment individualInvestment Payout []payout } type individualInvestment struct { AsOnDate string CapitalizedInterest float64 InterestPaidOut float64 AccruedInterest float64 TotalInterest float64 } type payout struct { Date time.Time Days int64 Amount float64 PrincipalAmount float64 Type string } type Invest struct { PaymentMethod int64 `json:"payment_method"` PaymentGateway int64 `json:"payment_gateway"` Amount float64 `json:"amount"` PayoutType int64 `json:"payout_type"` MHP int64 `json:"mhp"` ReferenceCode string `json:"reference_code"` InvestmentPaymentID string TransactionID string TransactionReference string `json:"transaction_reference"` AccountID int64 IP4Address string StartDate string EndDate string TxnDate string `json:"txn_date"` Auth eloquent.Auth } /*type InvestWebhook struct { PaymentPaymentId string `schema:"razorpay_payment_id"` RazorpayPaymentLinkId string `schema:"razorpay_payment_link_id"` PaymentLinkReferenceId string `schema:"razorpay_payment_link_reference_id"` PaymentLinkStatus string `schema:"razorpay_payment_link_status"` Signature string `schema:"razorpay_signature"` AccountId string `schema:"account_id"` TransactionId string `schema:"transaction_id"` Auth eloquent.Auth }*/ type InvestWebhook struct { PaymentPaymentId string `json:"razorpay_payment_id"` RazorpayPaymentLinkId string `json:"razorpay_payment_link_id"` PaymentLinkReferenceId string `json:"razorpay_payment_link_reference_id"` PaymentLinkStatus string `json:"razorpay_payment_link_status"` Signature string `json:"razorpay_signature"` AccountId int64 `json:"account_id"` TransactionId string `json:"transaction_id"` Auth eloquent.Auth } type Customer struct { Contact string `json:"contact"` Email string `json:"email"` Name string `json:"name"` } type Notify struct { Email string `json:"email"` SMS string `json:"sms"` } type InvestPayment struct { AcceptPartial bool `json:"accept_partial"` Amount float64 `json:"amount"` AmountPaid float64 `json:"amount_paid"` CancelledAt int64 `json:"cancelled_at"` CreatedAt int64 `json:"created_at"` Ccurrency string `json:"currency"` Customer Customer Description string `json:"description"` ExpireBy int64 `json:"expire_by"` ExpiredAt int64 `json:"expired_at"` FirstMinPartialAmount float64 `json:"first_min_partial_amount"` Id string `json:"id"` Notes string `json:"notes"` Notify Notify Payments string `json:"payments"` ReferenceId string `json:"reference_id"` ReminderEnable string `json:"reminder_enable"` ShortUrl string `json:"short_url"` Status string `json:"status"` Updated_at int64 `json:"updated_at"` UpiLink bool `json:"upi_link"` UserId string `json:"user_id"` } type Passbook struct { StartDate string `json:"start_date"` EndDate string `json:"end_date"` AccountID int64 `json:"account_id"` Data PassbookData Auth eloquent.Auth } type PassbookData struct { ID int64 `json:"id"` SchemeName string `json:"scheme_name"` AccountID int64 `json:"account_id"` Date string `json:"date"` Narration string `json:"narration"` Credit float64 `json:"credit"` Debit float64 `json:"debit"` Principal float64 `json:"principal"` ValueAsOnDate float64 `json:"value_as_on_date"` } type Accounts struct { Auth eloquent.Auth AccountID int64 `json:"account_id"` Data AccountData } type AccountData struct { AccountID int64 `json:"account_id"` AccountName string `json:"account_name"` } type Schemes struct { Auth eloquent.Auth SchemeID int64 `json:"scheme_id"` PayoutID int64 `json:"payout_id"` PayoutType string `json:"payout_type"` Data SchemeData } type SchemeData struct { SchemeID int64 `json:"scheme_id"` SchemeName string `json:"scheme_name"` } type InterestSummary struct { CapitalisedInterest int64 `json:"capitalised_interest"` InterestPaidOut float64 `json:"interest_paid_out"` AccruedInterest float64 `json:"accrued_interest"` TotalInterest float64 `json:"total_interest"` } type EstimatedReturnSummary struct { AbsoluteReturnRupee int64 `json:"absolute_return_in_rupee"` AbsoluteReturnPercentage float64 `json:"absolute_return_in_percentage"` AnnualisedReturn float64 `json:"annualised_return"` } type InvestmentSummary struct { AccountID int64 `json:"account_id"` Scheme string `json:"scheme"` DateOfInvestment string `json:"date_of_investment"` Amount float64 `json:"amount"` MAX_XIRR float64 `json:"max_xirr"` InterestRepaid float64 `json:"interest_repaid"` PrincipalRepaid float64 `json:"principal_repaid"` TotalRepaid float64 `json:"total_repaid"` PortfolioValue float64 `json:"portfolio_value"` PortfolioValueAvailableForWithdrawal float64 `json:"portfolio_value_available_for_withdrawal"` EndDate string `json:"end_date"` } type Dashboard struct { TotalInvestment float64 `json:"total_investment"` AmountRepaid float64 `json:"amount_repaid"` NetPrincipalInvestment float64 `json:"net_principal_investment"` Portfolio float64 `json:"portfolio"` PortfolioValueAvailableForWithdrawal float64 `json:"portfolio_value_available_for_withdrawal"` InterestSummary InterestSummary `json:"interest_summary"` EstimatedReturnSummary EstimatedReturnSummary `json:"estimated_return_summary"` InvestmentSummary []InvestmentSummary `json:"investment_summary"` } type Withdrawal struct { Reason string `json:"reason"` BankID int64 `json:"bank_id"` Amount float64 `json:"amount"` AccountID int64 `json:"account_id"` FullPortfolio bool `json:"full_portfolio"` TransactionID string `json:"transaction_id"` IP4Address string `json:"ip4_address"` ReferenceCode string `json:"reference_code"` TxnType string `json:"txn_type"` TxnDate string `json:"txn_date"` Auth eloquent.Auth } type WithdrawalList struct { AccountID int64 `json:"account_id"` Amount float64 `json:"amount"` DepositType string `json:"deposit_type"` Gateway string `json:"gateway"` ReferenceCode string `json:"reference_code"` Status string `json:"status"` DateOfWithdrawal string `json:"date_of_withdrawal"` Auth eloquent.Auth } type PaymentConfirmation struct { AccountId int64 `json:"account_id"` TransactionID string `json:"transaction_id"` } type WithdrawalConfirmation struct { AccountId int64 `json:"account_id"` TransactionID string `json:"transaction_id"` } func (manager FlexiPlan) FlexiV1() (FlexiPlan, error) { format := "2006-01-02" end, _ := time.Parse(format, manager.EndDate) start, _ := time.Parse(format, manager.StartDate) diff := end.Sub(start) principal := manager.Principal manager.DiffInDays = int64(diff.Hours() / 24) manager.TotalInterest = (float64(manager.Cumulative) * float64(manager.DiffInDays) * manager.Principal / 100) / 365 manager.TotalPayout = manager.TotalInterest + manager.Principal log.Print("Days:", manager.DiffInDays) var payouts []payout for c := 0; c < int(manager.DiffInDays); c++ { var payout payout start_date := time.Date(start.Year(), start.Month(), start.Day()+c, 0, 0, 0, 0, time.UTC) v := math.Pow((manager.MonthlyROI/100+1), (1/365.00)) - 1 x := v * principal log.Print(start_date, ": ", x) principal = x + principal payout.Date = start_date payout.Amount = x payouts = append(payouts, payout) } manager.Payout = payouts return manager, nil } func (manager FlexiPlan) DailyCron() (FlexiPlan, error) { format := "2006-01-02" end, _ := time.Parse(format, manager.EndDate) start, _ := time.Parse(format, manager.StartDate) diff := end.Sub(start) fmt.Printf("Days: %d\n", int64(diff.Hours()/24)) //diffInMonth := int(diff.Hours() / 24 / 30) fmt.Printf("Month: %d\n", int64(diff.Hours()/24/30)) manager.DiffInDays = int64(diff.Hours() / 24) //d := float64(manager.DiffInDays)/365 //log.Print(d) //log.Print(manager.MonthlyROI) //log.Print(manager.MonthlyROI/100+1) //log.Print((manager.MonthlyROI/100+1)*100) //log.Print("D^%") //log.Print(((manager.MonthlyROI/100+1)*100) * d) manager.TotalInterest = (float64(manager.Cumulative) * float64(manager.DiffInDays) * manager.Principal / 100) / 365 manager.TotalPayout = manager.TotalInterest + manager.Principal //var m int = 10 //log.Print(start.Year()) //log.Print(time.Month(start.Month())) //p := manager.Principal //x := 0 //daily calculation log.Print("daily calculation started") /*for c := 1; c <= int(manager.DiffInDays); c++ { v := math.Pow((manager.MonthlyROI/100+1), (1/365.00)) - 1 x := v * p p = x + p }*/ //end //log.Print(start) //log.Print("diffInMonth:", manager.DiffInDays) //var lDate time.Time var payMain []payout for i := 1; i <= int(manager.DiffInDays); i++ { var pay payout if i == 1 { pay.Date = start payMain = append(payMain, pay) } t := time.Date(start.Year(), start.Month(), start.Day()+i, 0, 0, 0, 0, time.UTC) // -- daily //diff := t.Sub(payMain[i].Date) //d := int64(diff.Hours() / 24) //log.Print("DiffInDays:", d) // -- daily //log.Print(t) //lDate = t pay.Date = t payMain = append(payMain, pay) //log.Print("lDate", pay.Date) //if (i + 1) > diffInMonth { // //lastDayDiff := getDayDiffFromStartOfMonth(format, start) // endDateWithDays := time.Date(lDate.Year(), time.Month(lDate.Month()), end.Day(), 0, 0, 0, 0, time.UTC) // pay.Date = endDateWithDays // payMain = append(payMain, pay) //} } log.Print("daily calculation done") //log.Print(payMain) principal := manager.Principal var totalInterestEarned float64 log.Print("daily calculation - getWithdrawalAmountByAccountIDWithTxnDate started") transactionData := getWithdrawalAmountByAccountIDWithTxnDate(manager.AccountId) //log.Print(transactionData) log.Print("daily calculation - getWithdrawalAmountByAccountIDWithTxnDate - End") log.Print("daily calculation - getDepositAmountByAccountIDWithTxnDate started") transactionDataDeposit := getDepositAmountByAccountIDWithTxnDate(manager.AccountId) //log.Print(transactionData) log.Print("daily calculation - getDepositAmountByAccountIDWithTxnDate - End") log.Print("daily calculation - interest") var considerForTomorrowDeposit bool var considerForTomorrowWithdrawal bool var DepositAmountPreviousDay float64 var WithdrawalAmountPreviousDay float64 //empData := [][]string{{"Date", "Amount", "Balance"}} for inc, payItem := range payMain { s := payItem.Date if inc == 0 { payMain[inc].Amount = manager.Principal payMain[inc].Type = "Investment" } if (inc + 1) == len(payMain) { break } e := payMain[inc+1].Date diff := e.Sub(s) d := int64(diff.Hours() / 24) payMain[inc+1].Days = d payMain[inc+1].Type = "Payout" //log.Print(d) //log.Print((1 / 365)) //log.Print(((manager.MonthlyROI/100 + 1) * 100)) c := math.Pow((manager.MonthlyROI/100+1), (1/365.00)) - 1 //log.Print(c) //log.Print(c * principal) //if there is any withdrawal --- //withdrawalAmount := getWithdrawalAmountByAccountIDAndDate(manager.AccountId, payMain[inc+1].Date) //Withdrawal withdrawalAmount := transactionData[(payMain[inc+1].Date).Format("2006-01-02")] /*if withdrawalAmount > 0 { log.Print("withdrawal Amount - ", withdrawalAmount) principal -= withdrawalAmount }*/ if withdrawalAmount > 0 && !considerForTomorrowWithdrawal{ log.Print("withdrawal Amount - ", withdrawalAmount) considerForTomorrowWithdrawal = true WithdrawalAmountPreviousDay = withdrawalAmount }else if considerForTomorrowWithdrawal{ considerForTomorrowWithdrawal = false log.Print("withdrawal Amount consider - ", WithdrawalAmountPreviousDay, " Date ", (payMain[inc+1].Date).Format("2006-01-02")) principal -= WithdrawalAmountPreviousDay WithdrawalAmountPreviousDay = 0 } //Withdrawal - end //Deposit DepositAmount := transactionDataDeposit[(payMain[inc+1].Date).Format("2006-01-02")] if DepositAmount > 0 && !considerForTomorrowDeposit{ log.Print("deposit Amount - ", DepositAmount) considerForTomorrowDeposit = true DepositAmountPreviousDay = DepositAmount }else if considerForTomorrowDeposit{ considerForTomorrowDeposit = false log.Print("deposit Amount consider - ", DepositAmountPreviousDay, " Date ", (payMain[inc+1].Date).Format("2006-01-02")) principal += DepositAmountPreviousDay DepositAmountPreviousDay = 0 } //Deposit - end amount := c * principal if amount < 0 { amount = 0 } if principal < 0 { principal = 0 } payMain[inc+1].Amount = amount principal += amount payMain[inc+1].PrincipalAmount = principal totalInterestEarned += amount //CSV Download Code //empItem := []string{(payMain[inc+1].Date).Format("2006-01-02"), fmt.Sprintf("%v",payMain[inc+1].Amount), fmt.Sprintf("%v",payMain[inc+1].PrincipalAmount)} //empData = append(empData, empItem) //end if isMonthEnd(payMain[inc+1].Date) == 1 { principal = payMain[inc+1].PrincipalAmount - totalInterestEarned //fmt.Println("Date ",payMain[inc+1].Date) //fmt.Printf("PrincipalAmount %f",payMain[inc+1].PrincipalAmount) //fmt.Println("--") //fmt.Println("totalInterestEarned",totalInterestEarned) //fmt.Println("--") //fmt.Printf("principal remaining %f",principal) totalInterestEarned = 0 } } //CSVDownload(empData) log.Print("daily calculation - interest done") manager.TotalInterestEarned = totalInterestEarned manager.Payout = payMain //financial.CalculateXIRR() if len(manager.ForIndividualInvestment) > 0 { endForIndividualInvestment, _ := time.Parse(format, manager.ForIndividualInvestment) startOfIndividualInvestment := time.Date(endForIndividualInvestment.Year(), endForIndividualInvestment.Month(), endForIndividualInvestment.Day(), 0, 0, 0, 0, time.UTC) diff := endForIndividualInvestment.Sub(startOfIndividualInvestment) d := int64(diff.Hours() / 24) diff = startOfIndividualInvestment.Sub(start) den := int64(diff.Hours() / 24) manager.IndividualInvestment.AsOnDate = manager.ForIndividualInvestment manager.IndividualInvestment.CapitalizedInterest = 0 c := math.Pow((manager.MonthlyROI/100+1), (float64(den)/365.00)) - 1 manager.IndividualInvestment.InterestPaidOut = c * manager.Principal v := math.Pow((manager.MonthlyROI/100+1), (float64(d)/365.00)) - 1 manager.IndividualInvestment.AccruedInterest = v * manager.Principal manager.IndividualInvestment.TotalInterest = manager.IndividualInvestment.InterestPaidOut + manager.IndividualInvestment.AccruedInterest } return manager, nil } func CSVDownload(empData [][]string){ csvFile, err := os.Create("employee.csv") if err != nil { log.Print("failed creating file: %s", err) } csvwriter := csv.NewWriter(csvFile) for _, empRow := range empData { _ = csvwriter.Write(empRow) } csvwriter.Flush() csvFile.Close() } func (manager FlexiPlan) InterestCalculationByDays(Days int64) (FlexiPlan, error) { manager.TotalInterest = (float64(manager.Cumulative) * float64(manager.DiffInDays) * manager.Principal / 100) / 365 manager.TotalPayout = manager.TotalInterest + manager.Principal c := math.Pow(manager.MonthlyROI/100+1, float64(Days)/365.00) - 1 manager.TotalInterestEarned = c * manager.Principal return manager, nil } func (manager FlexiPlan) Flexi() (FlexiPlan, error) { format := "2006-01-02" end, _ := time.Parse(format, manager.EndDate) start, _ := time.Parse(format, manager.StartDate) diff := end.Sub(start) fmt.Printf("Days: %d\n", int64(diff.Hours()/24)) diffInMonth := int(diff.Hours() / 24 / 30) fmt.Printf("Month: %d\n", int64(diff.Hours()/24/30)) manager.DiffInDays = int64(diff.Hours() / 24) //d := float64(manager.DiffInDays)/365 //log.Print(d) //log.Print(manager.MonthlyROI) //log.Print(manager.MonthlyROI/100+1) //log.Print((manager.MonthlyROI/100+1)*100) //log.Print("D^%") //log.Print(((manager.MonthlyROI/100+1)*100) * d) manager.TotalInterest = (manager.Cumulative * float64(manager.DiffInDays) * manager.Principal / 100) / 365 manager.TotalPayout = manager.TotalInterest + manager.Principal //var m int = 10 //log.Print(start.Year()) //log.Print(time.Month(start.Month())) p := manager.Principal //x := 0 //daily calculation for c := 1; c <= int(manager.DiffInDays); c++ { start_date := time.Date(start.Year(), start.Month(), c, 0, 0, 0, 0, time.UTC) log.Print(start_date) //log.Print((manager.MonthlyROI/100+1)) //log.Print(float64(1/365.00)) v := math.Pow((manager.MonthlyROI/100+1), (1/365.00)) - 1 //log.Print(v) x := v * p log.Print(x) p = x + p } //end //log.Print(start) log.Print("diffInMonth:", diffInMonth) var lDate time.Time var payMain []payout for i := 1; i <= diffInMonth; i++ { var pay payout if i == 1 { pay.Date = start payMain = append(payMain, pay) } t := time.Date(start.Year(), time.Month(start.Month()+time.Month(i)), 1, 0, 0, 0, 0, time.UTC) // -- daily diff := t.Sub(payMain[i-1].Date) d := int64(diff.Hours() / 24) log.Print("DiffInDays:", d) // -- daily //log.Print(t) lDate = t pay.Date = lDate payMain = append(payMain, pay) log.Print(lDate) if (i + 1) > diffInMonth { //lastDayDiff := getDayDiffFromStartOfMonth(format, start) endDateWithDays := time.Date(lDate.Year(), time.Month(lDate.Month()), end.Day(), 0, 0, 0, 0, time.UTC) pay.Date = endDateWithDays payMain = append(payMain, pay) } } //log.Print(payMain) var totalInterestEarned float64 for inc, payItem := range payMain { s := payItem.Date if inc == 0 { payMain[inc].Amount = manager.Principal payMain[inc].Type = "Investment" } if (inc + 1) == len(payMain) { break } e := payMain[inc+1].Date diff := e.Sub(s) d := int64(diff.Hours() / 24) payMain[inc+1].Days = d payMain[inc+1].Type = "Payout" log.Print(d) log.Print((float64(d) / 365)) log.Print(((manager.MonthlyROI/100 + 1) * 100)) c := math.Pow((manager.MonthlyROI/100+1), (float64(d)/365)) - 1 log.Print(c) log.Print(c * manager.Principal) payMain[inc+1].Amount = c * manager.Principal totalInterestEarned += payMain[inc+1].Amount } manager.TotalInterestEarned = totalInterestEarned manager.Payout = payMain financial.CalculateXIRR() if len(manager.ForIndividualInvestment) > 0 { endForIndividualInvestment, _ := time.Parse(format, manager.ForIndividualInvestment) startOfIndividualInvestment := time.Date(endForIndividualInvestment.Year(), endForIndividualInvestment.Month(), 1, 0, 0, 0, 0, time.UTC) diff := endForIndividualInvestment.Sub(startOfIndividualInvestment) d := int64(diff.Hours() / 24) diff = startOfIndividualInvestment.Sub(start) den := int64(diff.Hours() / 24) manager.IndividualInvestment.AsOnDate = manager.ForIndividualInvestment manager.IndividualInvestment.CapitalizedInterest = 0 c := math.Pow((manager.MonthlyROI/100+1), (float64(den)/365)) - 1 manager.IndividualInvestment.InterestPaidOut = c * manager.Principal v := math.Pow((manager.MonthlyROI/100+1), (float64(d)/365)) - 1 manager.IndividualInvestment.AccruedInterest = v * manager.Principal manager.IndividualInvestment.TotalInterest = manager.IndividualInvestment.InterestPaidOut + manager.IndividualInvestment.AccruedInterest } return manager, nil } func PaymentTransactionLogsUpdate(TransactionId string, PaymentID string, status string) (int64, error){ tx, ctx, db := connection.Connect() dt := getDate() res, err := tx.ExecContext(ctx, "UPDATE payment_transaction_logs SET status = ?, payment_id = ?, updated_at = ? where reference_code = ?", status, PaymentID, dt.Format("2006-01-02 15:04:05"), TransactionId) if err != nil { log.Print(err.Error()) return 0, err } log.Print("PaymentTransactionLogsUpdate: RowsAffected") log.Print(res.RowsAffected()) connection.Close(tx, db) return 1, nil } func (manager Invest) PaymentTransactionLogs(investmentPayment Invest) (int64, error){ tx, ctx, db := connection.Connect() dt := getDate() paymentGateway := "-" if manager.PaymentGateway == 1 { paymentGateway = "Razorpay" } res, err := tx.ExecContext(ctx, "INSERT INTO payment_transaction_logs (reference_code, txn_type, amount, ip4_address, gateway, created_at) VALUES (?, ?, ?, ?, ?, ?)", investmentPayment.ReferenceCode, "DEPOSIT", manager.Amount, manager.IP4Address, paymentGateway, dt.Format("2006-01-02 15:04:05")) if err != nil { log.Print(err.Error()) return 0, err } transactionId, errCheck := res.LastInsertId() if errCheck != nil { return 0, errCheck } connection.Close(tx, db) return transactionId, nil } func (manager Invest) Invest() (Invest, error) { tx, ctx, db := connection.Connect() manager.ReferenceCode = GetRequestID() investment, err := createAccount(manager, tx, ctx) if err != nil { connection.Close(tx, db) log.Print(err.Error()) return manager, err } manager.AccountID = investment.AccountID _, err = CreateTransaction(investment, tx, ctx, "DEPOSIT", "PENDING") if err != nil { connection.Close(tx, db) log.Print(err.Error()) return manager, err } /*if manager.PaymentMethod != 2 { _, err = CreatePassbook(investment.AccountID, tx, ctx, "Deposit", investment.Amount, 0) if err != nil { connection.Close(tx, db) log.Print(err.Error()) return manager, err } }*/ connection.Close(tx, db) return manager, nil } func createAccount(manager Invest, tx *sql.Tx, ctx context.Context) (Invest, error) { dt := getDate() schemeId, tenureDuration, schemeType, _, errCheck := getScheme(manager.MHP, manager.PayoutType, tx) log.Print("scheme_id:", schemeId) if errCheck != nil { log.Print(errCheck.Error()) return manager, errCheck } var now time.Time if len(manager.TxnDate) > 0 { now, _ = time.Parse("2006-01-02 15:04:05", manager.TxnDate) manager.StartDate = now.Format("2006-01-02 15:04:05") } else { now = time.Now() manager.StartDate = dt.Format("2006-01-02 15:04:05") } manager.EndDate = now.AddDate(0, tenureDuration, -1).Format("2006-01-02 15:04:05") //manager.EndDate = time.Date(dt.Year(), time.Month(dt.Month()+time.Month(tenureDuration)), 0, 0, 0, 0, 0, time.UTC).Format("2006-01-02 15:04:05") var accountId int64 var err error log.Print("Plan Type", schemeType) if schemeType != "FLEXI" { //accountId = isAccountExisting(manager.Auth.ID, schemeId, withdrawalPolicyType, tx) accountId, err = createInvestmentAccount(manager, tx, ctx) if err != nil { log.Print(err.Error()) return manager, err } manager.AccountID = accountId } else { manager.EndDate = "" row := tx.QueryRow("select account_id from account where scheme_id = ? and investor_id = ?", schemeId, manager.Auth.ID) var ID int64 err := row.Scan(&ID) if err != nil && err != sql.ErrNoRows { return manager, errors.New("Scheme not found") } log.Print("ID to update", ID) if ID > 0 { log.Print("PaymentMethod", manager.PaymentMethod) if manager.PaymentMethod == 2 { manager.AccountID = ID log.Print("ignoring account entries") return manager, nil } //Update Flexi Plan log.Print("Update account") _, err = UpdateFlexiAccount(manager.Amount, ID, tx, ctx, "DEPOSIT") if err != nil { return manager, errors.New("Not able to update") } manager.AccountID = ID } else { log.Print("createInvestmentAccount account") accountId, err = createInvestmentAccount(manager, tx, ctx) if err != nil { log.Print(err.Error()) return manager, err } manager.AccountID = accountId } } return manager, nil } func isAccountExisting(ID string, schemeId int64, withdrawalPolicyType string, tx *sql.Tx) int64 { row := tx.QueryRow("select account_id from account left join scheme on scheme.scheme_id = account.scheme_id where account.scheme_id = ? and scheme.withdrawal_policy_type = ? and account.investor_id = ?", schemeId, withdrawalPolicyType, ID) var AccountID int64 err := row.Scan(&AccountID) if err == sql.ErrNoRows { return 0 } if err != nil { log.Error().Err(err).Msg(err.Error()) log.Print("isAccountExisting - rollback") tx.Rollback() return 0 } return AccountID } func GetAccountIDByReference(ReferenceCode string) int64 { tx, _, db := connection.Connect() row := tx.QueryRow("select account_id, payment_transaction_logs.status from payment_transaction_logs left join transaction on transaction.reference_code = payment_transaction_logs.reference_code where payment_transaction_logs.reference_code = ?", ReferenceCode) var AccountID int64 var LogStatus string err := row.Scan(&AccountID, &LogStatus) if err == sql.ErrNoRows { return 0 } if err != nil { log.Error().Err(err).Msg(err.Error()) log.Print("GetAccountIDByReference - not found") connection.Close(tx, db) return 0 } connection.Close(tx, db) if LogStatus == "INIT"{ return AccountID } return 0 } func createInvestmentAccount(manager Invest, tx *sql.Tx, ctx context.Context) (int64, error) { dt := getDate() status := "PENDING" if manager.PaymentMethod == 1 { //status = "ACTIVE" } res, err := tx.ExecContext(ctx, "INSERT INTO account (scheme_id, investor_id, initial_amount, start_date, end_date, account_status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)", manager.MHP, manager.Auth.ID, manager.Amount, manager.StartDate, manager.EndDate, status, dt.Format("2006-01-02 15:04:05")) var account_id int64 if err != nil { log.Print(err.Error()) return 0, err } account_id, errCheck := res.LastInsertId() if errCheck != nil { return 0, errCheck } return account_id, nil } func UpdateFlexiAccount(amount float64, account_id int64, tx *sql.Tx, ctx context.Context, TxnType string) (int64, error) { dt := getDate() log.Print("Updating DB: ", amount, account_id) var err error var res sql.Result if TxnType == "DEPOSIT" { res, err = tx.ExecContext(ctx, "UPDATE account SET initial_amount = initial_amount + ?, updated_at = ? where account_id = ?", amount, dt.Format("2006-01-02 15:04:05"), account_id) } else { res, err = tx.ExecContext(ctx, "UPDATE account SET initial_amount = initial_amount - ?, updated_at = ? where account_id = ?", amount, dt.Format("2006-01-02 15:04:05"), account_id) } if err != nil { log.Print(err.Error()) return 0, err } id, err := res.LastInsertId() log.Print("UpdateFlexiAccount Updated: ", id) if err != nil { log.Print(err.Error()) return 0, err } return id, nil } func CreateTransaction(manager Invest, tx *sql.Tx, ctx context.Context, TransactionDeposit string, _status string) (Invest, error) { dt := getDate() method := "OFFLINE" log.Print("manager.PaymentMethod") log.Print(manager) status := "PENDING" if manager.PaymentMethod == 1 { method = "ONLINE" //status = "SUCCESS" } if len(_status) > 0{ status = _status } gateway := "NEFT" log.Print("manager.PaymentGateway") log.Print(manager.PaymentGateway) if method == "ONLINE" && manager.PaymentGateway == 1 { gateway = "Razorpay" } var txnDate string if len(manager.TxnDate) > 0 { txnDate = manager.TxnDate } else { txnDate = dt.Format("2006-01-02 15:04:05") } res, err := tx.ExecContext(ctx, "INSERT INTO transaction (account_id, txn_date, txn_type, amount, ip4_address, deposit_type, gateway, reference_code, transaction_reference, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())", manager.AccountID, txnDate, TransactionDeposit, manager.Amount, manager.IP4Address, method, gateway, manager.ReferenceCode, manager.TransactionReference, status) var invest Invest if err != nil { log.Print(err.Error()) return invest, err } transaction_id, errCheck := res.LastInsertId() if errCheck != nil { return invest, errCheck } invest.TransactionID = fmt.Sprint(transaction_id) return invest, nil } func CreatePassbook(AccountID int64, tx *sql.Tx, ctx context.Context, narration string, credit float64, debit float64, PrincipalAmount float64) (Invest, error) { dt := getDate() log.Print("inserting the pass book") res, err := tx.ExecContext(ctx, "INSERT INTO pass_book (account_id, entry_date, narration, credit, debit, principal_amount, account_balance, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", AccountID, dt.Format("2006-01-02 15:04:05"), narration, credit, debit, PrincipalAmount, PrincipalAmount, dt.Format("2006-01-02 15:04:05")) var invest Invest if err != nil { log.Print(err.Error()) return invest, err } transaction_id, errCheck := res.LastInsertId() log.Print("transaction_id", transaction_id) if errCheck != nil { return invest, errCheck } invest.TransactionID = fmt.Sprint(transaction_id) return invest, nil } func createWithdrawalTransaction(manager Withdrawal, tx *sql.Tx, ctx context.Context) (Withdrawal, error) { dt := getDate() method := "OFFLINE" gateway := "NEFT" var txnDate string if len(manager.TxnDate) > 0 { txnDate = manager.TxnDate } else { txnDate = dt.Format("2006-01-02 15:04:05") } totalInterestAccrued, totalInterestPaidOut := getInterestByDateAndAccount(tx, manager.AccountID, txnDate) log.Print("totalInterestAccrued", totalInterestAccrued) log.Print("totalInterestPaidOut", totalInterestPaidOut) interestBalance := totalInterestAccrued - totalInterestPaidOut log.Print("interestBalance", interestBalance) var TxnType = manager.TxnType var err error var res sql.Result var withdrawal Withdrawal if interestBalance > manager.Amount { log.Print("Full interest only") TxnType = "INTEREST_DEBIT" res, err = tx.ExecContext(ctx, "INSERT INTO transaction (account_id, txn_type, txn_date, amount, ip4_address, deposit_type, gateway, reference_code, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", manager.AccountID, TxnType, txnDate, manager.Amount, manager.IP4Address, method, gateway, manager.ReferenceCode, "INIT", dt.Format("2006-01-02 15:04:05")) } else { TxnType = "INTEREST_DEBIT" principal := manager.Amount - interestBalance log.Print("Principal balance ", principal) log.Print("interestBalance balance ", interestBalance) if interestBalance > 0 { res, err = tx.ExecContext(ctx, "INSERT INTO transaction (account_id, txn_type, txn_date, amount, ip4_address, deposit_type, gateway, reference_code, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", manager.AccountID, TxnType, txnDate, interestBalance, manager.IP4Address, method, gateway, manager.ReferenceCode, "INIT", dt.Format("2006-01-02 15:04:05")) } if principal > 0 { res, err = tx.ExecContext(ctx, "INSERT INTO transaction (account_id, txn_type, txn_date, amount, ip4_address, deposit_type, gateway, reference_code, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", manager.AccountID, manager.TxnType, txnDate, principal, manager.IP4Address, method, gateway, manager.ReferenceCode, "INIT", dt.Format("2006-01-02 15:04:05")) if err != nil { log.Print(err.Error()) return withdrawal, err } } } if err != nil { log.Print(err.Error()) return withdrawal, err } transaction_id, errCheck := res.LastInsertId() if errCheck != nil { return withdrawal, errCheck } withdrawal.TransactionID = fmt.Sprint(transaction_id) return withdrawal, nil } func getFlexiScheme(tx *sql.Tx) (int64, error) { log.Print("getFlexiScheme") row := tx.QueryRow("select scheme_id from scheme where scheme_type = 'FLEXI' and status = 'ACTIVE' and is_visible = 1") var scheme_id int64 err := row.Scan(&scheme_id) if err != nil { log.Print(err.Error()) return 0, errors.New("Scheme not found or inactive") } return scheme_id, nil } func getScheme(schemeID int64, withdrawalPolicyType int64, tx *sql.Tx) (int64, int, string, string, error) { PolicyType := "C" if withdrawalPolicyType == 1 { PolicyType = "M" } row := tx.QueryRow("select scheme_id, tenure_duration, scheme_type, withdrawal_policy_type from scheme where scheme_id = ? and status = 'ACTIVE' and is_visible = 1 and withdrawal_policy_type = ?", schemeID, PolicyType) var tenure_duration int var scheme_id int64 var scheme_type string var withdrawal_policy_type string err := row.Scan(&scheme_id, &tenure_duration, &scheme_type, &withdrawal_policy_type) if err != nil { log.Print("scheme_id", schemeID) log.Print("PolicyType", PolicyType) log.Print("withdrawalPolicyType", withdrawalPolicyType) log.Print("test") return 0, 0, "", "", errors.New("Scheme not found or inactive") } else { log.Print("testtsetst") log.Print("withdrawalPolicyType", withdrawalPolicyType) log.Print("scheme_id", schemeID) log.Print("PolicyType", PolicyType) } return scheme_id, tenure_duration, scheme_type, withdrawal_policy_type, nil } func (manager Passbook) GetPassbook() ([]PassbookData, error) { tx, _, db := connection.Connect() var query string var rows *sql.Rows var err error if manager.AccountID > 0 { log.Print("Acc") log.Print(manager.AccountID) log.Print(manager.Auth.ID) query = "select pass_book.book_id, COALESCE(sc.scheme_name, ''), pass_book.account_id, COALESCE(pass_book.entry_date, ''), COALESCE(pass_book.narration, ''), COALESCE(pass_book.credit, 0), COALESCE(pass_book.debit, 0), COALESCE(pass_book.principal_amount, ''), COALESCE(pass_book.account_balance, '') from pass_book left join account as ac on ac.account_id = pass_book.account_id left join scheme as sc on sc.scheme_id = ac.scheme_id where ac.account_status = 'ACTIVE' and ac.account_id = ? and ac.investor_id = ? and pass_book.entry_date >= ? and pass_book.entry_date <= ? order by pass_book.created_at asc" rows, err = tx.Query(query, manager.AccountID, manager.Auth.ID, manager.StartDate, manager.EndDate) } else { log.Print("Acc1") log.Print(manager.Auth.ID) query := "select pass_book.book_id, COALESCE(sc.scheme_name, ''), pass_book.account_id, COALESCE(pass_book.entry_date, ''), COALESCE(pass_book.narration, ''), COALESCE(pass_book.credit, 0), COALESCE(pass_book.debit, 0), COALESCE(pass_book.principal_amount, ''), COALESCE(pass_book.account_balance, '') from pass_book left join account as ac on ac.account_id = pass_book.account_id left join scheme as sc on sc.scheme_id = ac.scheme_id where ac.account_status = 'ACTIVE' and ac.investor_id = ? and pass_book.entry_date >= ? and pass_book.entry_date <= ? order by pass_book.created_at asc" rows, err = tx.Query(query, manager.Auth.ID, manager.StartDate, manager.EndDate) } var passbookItems []PassbookData if err != nil { log.Print(err.Error()) tx.Rollback() return passbookItems, errors.New("Passbook not found") } i := 0 //var ValueAsOnDate float64 for rows.Next() { var passbook PassbookData err = rows.Scan(&passbook.ID, &passbook.SchemeName, &passbook.AccountID, &passbook.Date, &passbook.Narration, &passbook.Credit, &passbook.Debit, &passbook.Principal, &passbook.ValueAsOnDate) if err != nil { log.Print(err.Error()) tx.Rollback() return passbookItems, errors.New("Passbook iterate error") } /*ValueAsOnDate += passbook.Credit ValueAsOnDate -= passbook.Debit if i > 0 { passbook.ValueAsOnDate = ValueAsOnDate }*/ passbook.ValueAsOnDate = passbook.Principal passbookItems = append(passbookItems, passbook) i++ } if len(passbookItems) == 0 { tx.Rollback() return passbookItems, errors.New("Passbook is empty") } connection.Close(tx, db) return passbookItems, nil } func (manager Accounts) GetAccounts() ([]AccountData, error) { tx, _, db := connection.Connect() var query string var rows *sql.Rows var err error if manager.AccountID > 0 { log.Print("Acc") log.Print(manager.AccountID) log.Print(manager.Auth.ID) query = "select account.account_id, s.scheme_name as account_name from account left join scheme s on account.scheme_id = s.scheme_id where account_id = ? and investor_id = ? and account.account_status = 'ACTIVE'" rows, err = tx.Query(query, manager.AccountID, manager.Auth.ID) } else { log.Print("Acc1") log.Print(manager.Auth.ID) query := "select account.account_id, s.scheme_name as account_name from account left join scheme s on account.scheme_id = s.scheme_id where investor_id = ? and account.account_status = 'ACTIVE'" rows, err = tx.Query(query, manager.Auth.ID) } var accounts []AccountData if err != nil { log.Print(err.Error()) tx.Rollback() return accounts, errors.New("accounts not found") } for rows.Next() { var account AccountData err = rows.Scan(&account.AccountID, &account.AccountName) if err != nil { log.Print(err.Error()) tx.Rollback() return accounts, errors.New("accounts not found") } accounts = append(accounts, account) } if len(accounts) == 0 { connection.Close(tx, db) return accounts, errors.New("accounts not found") } connection.Close(tx, db) return accounts, nil } func (manager Invest) GetSchemeDetails() string { tx, _, db := connection.Connect() PolicyType := "C" if manager.PayoutType == 1 { PolicyType = "M" } log.Print("PolicyType", PolicyType) log.Print("manager.MHP", manager.MHP) row := tx.QueryRow("select scheme_name from scheme where scheme_id = ? and status = 'ACTIVE' and is_visible = 1 and withdrawal_policy_type = ?", manager.MHP, PolicyType) var scheme_name string err := row.Scan(&scheme_name) if err != nil { log.Print("Scheme not found or inactive") return "No Scheme" } connection.Close(tx, db) return scheme_name } func (manager Withdrawal) GetSchemeDetailsByAccount() string { tx, _, db := connection.Connect() row := tx.QueryRow("select scheme_name from account left join scheme on scheme.scheme_id = account.scheme_id where account.account_id = ? and account_status = 'ACTIVE' and investor_id = ?", manager.AccountID, manager.Auth.ID) var scheme_name string err := row.Scan(&scheme_name) if err != nil { log.Print("Scheme not found or inactive") return "No Scheme" } connection.Close(tx, db) return scheme_name } func (manager Schemes) GetSchemes() ([]SchemeData, error) { tx, _, db := connection.Connect() var query string var rows *sql.Rows var err error if manager.SchemeID > 0 && manager.PayoutID > 0 { query = "select scheme_id, scheme_name from scheme where scheme_id = ? and status = 'ACTIVE' and is_visible = 1 and withdrawal_policy_type = ?" rows, err = tx.Query(query, manager.SchemeID, manager.PayoutID) } else if manager.SchemeID > 0 { query = "select scheme_id, scheme_name from scheme where scheme_id = ? and status = 'ACTIVE' and is_visible = 1" rows, err = tx.Query(query, manager.SchemeID) } else if manager.PayoutID > 0 { query = "select scheme_id, scheme_name from scheme where withdrawal_policy_type = ? and status = 'ACTIVE' and is_visible = 1" rows, err = tx.Query(query, manager.PayoutID) } else { query = "select scheme_id, scheme_name from scheme where status = 'ACTIVE' and is_visible = 1" rows, err = tx.Query(query) } var schemes []SchemeData if err != nil { log.Print(err.Error()) tx.Rollback() return schemes, errors.New("schemes not found") } for rows.Next() { var scheme SchemeData err = rows.Scan(&scheme.SchemeID, &scheme.SchemeName) if err != nil { log.Print(err.Error()) tx.Rollback() return schemes, errors.New("schemes not found") } schemes = append(schemes, scheme) } if len(schemes) == 0 { connection.Close(tx, db) return schemes, errors.New("schemes not found") } connection.Close(tx, db) return schemes, nil } func getWithdrawalAmountByAccountID(accountID int64, tx *sql.Tx) float64 { row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type in ('WITHDRAW', 'INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id = ?", accountID) var amount float64 err := row.Scan(&amount) if err != nil { log.Print(err.Error()) return amount } return amount } func getWithdrawalAmountByAccountIDAndDate(accountID int64, txnDate time.Time) float64 { tx, _, db := connection.Connect() row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type in ('WITHDRAW', 'INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id = ? and date(txn_date) = ?", accountID, txnDate.Format("2006-01-02")) var amount float64 err := row.Scan(&amount) connection.Close(tx, db) if err != nil { log.Print(err.Error()) return amount } return amount } func getWithdrawalAmountByAccountIDWithTxnDate(accountID int64) map[string]float64 { tx, _, db := connection.Connect() //rows, err := tx.Query("select IF(amount is null, 0, SUM(amount)) as amount, date(txn_date) from transaction where txn_type in ('WITHDRAW', 'INTEREST_DEBIT') and status = 'SUCCESS' and account_id = ?", accountID) rows, err := tx.Query("select IF(amount is null, 0, SUM(amount)) as amount, date(txn_date) from transaction where txn_type in ('WITHDRAW', 'INTEREST_DEBIT') and status = 'SUCCESS' and account_id = ? group by txn_date", accountID) trxns := make(map[string]float64) for rows.Next() { var amount float64 var txnDate string err = rows.Scan(&amount, &txnDate) if err != nil { log.Print(err.Error()) } trxns[txnDate] = amount } connection.Close(tx, db) return trxns } func getDepositAmountByAccountIDWithTxnDate(accountID int64) map[string]float64 { tx, _, db := connection.Connect() //rows, err := tx.Query("select IF(amount is null, 0, SUM(amount)) as amount, date(txn_date) from transaction where txn_type in ('WITHDRAW', 'INTEREST_DEBIT') and status = 'SUCCESS' and account_id = ?", accountID) rows, err := tx.Query("select IF(amount is null, 0, SUM(amount)) as amount, date(txn_date) from transaction where txn_type in ('DEPOSIT') and status = 'SUCCESS' and account_id = ? group by txn_date", accountID) trxns := make(map[string]float64) for rows.Next() { var amount float64 var txnDate string err = rows.Scan(&amount, &txnDate) if err != nil { log.Print(err.Error()) } trxns[txnDate] = amount } connection.Close(tx, db) return trxns } func (manager Withdrawal) Withdrawal() (Withdrawal, error) { tx, ctx, db := connection.Connect() /*scheme_id, errCheck := getFlexiScheme(tx) if errCheck != nil { log.Print(errCheck.Error()) return manager, errCheck } log.Print("Plan Type", scheme_id)*/ if manager.Amount < 1000 { return manager, errors.New("Minimum amount should be 10K") } row := tx.QueryRow("select account_id from account where account_id = ? and investor_id = ? and account_status = 'ACTIVE' and start_date <= DATE(NOW() - INTERVAL 2 DAY)", manager.AccountID, manager.Auth.ID) var ID int64 var InitialAmount float64 err := row.Scan(&ID) if err != nil { tx.Rollback() return manager, errors.New("Account is not active or you can withdraw on T+2") } //InitialAmount = getTotalInvestmentWithInterestCredit(ID) InitialAmount = getTotalInvestmentAvailableForWithdrawal(ID) InterestGainedOut := getInterestGainedOut(ID, tx) log.Print("ID to update", ID) if ID > 0 { totalWithdrawalAmount := getWithdrawalAmountByAccountID(manager.AccountID, tx) InitialAmount = (InitialAmount + InterestGainedOut) - totalWithdrawalAmount if InitialAmount < manager.Amount { return manager, errors.New("Amount is greater than the available amount") } balance := InitialAmount - totalWithdrawalAmount log.Print("totalWithdrawalAmount ", totalWithdrawalAmount) log.Print("balanceAmount ", balance) if manager.Amount > balance { log.Print("Balance amount is ", balance) return manager, errors.New("Amount is greater than the available amount") } TxnType := "WITHDRAW" //Update Flexi Plan /*_, err = UpdateFlexiAccount(manager.Amount, ID, tx, ctx, TxnType) if err != nil { tx.Rollback() log.Print(err.Error()) return manager, errors.New("Not able to update") }*/ manager.AccountID = ID manager.IP4Address = "" manager.TxnType = TxnType manager.ReferenceCode = GetRequestID() _, err = createWithdrawalTransaction(manager, tx, ctx) if err != nil { tx.Rollback() log.Print(err.Error()) return manager, err } } /*_, err = CreatePassbook(ID, tx, ctx, "withdrawal", 0, manager.Amount) if err != nil { connection.Close(tx, db) log.Print(err.Error()) return manager, err }*/ manager.AccountID = ID connection.Close(tx, db) return manager, nil } func (manager InvestmentSummary) GetInvestmentSummary(AuthID string) (float64, []InvestmentSummary, float64, float64, float64, float64, float64, float64, error) { tx, _, db := connection.Connect() rows, err := tx.Query("select ac.account_id, s.scheme_name, ac.start_date, ac.initial_amount, COALESCE(xirr_rate, ''), COALESCE(ac.end_date, '') from account ac left join scheme as s on s.scheme_id = ac.scheme_id where ac.account_status = 'ACTIVE' and ac.investor_id = ?", AuthID) var investmentSummary []InvestmentSummary if err != nil { log.Print(err.Error()) return 0, investmentSummary, 0, 0, 0, 0, 0, 0, err } var totalInvestment float64 var totalPortfolioValue float64 var totalPortfolioValueAvailableForWithdrawal float64 var accounts []int64 for rows.Next() { var investmentitem InvestmentSummary err = rows.Scan(&investmentitem.AccountID, &investmentitem.Scheme, &investmentitem.DateOfInvestment, &investmentitem.Amount, &investmentitem.MAX_XIRR, &investmentitem.EndDate) if err != nil { log.Print(err.Error()) tx.Rollback() return totalPortfolioValueAvailableForWithdrawal, investmentSummary, totalPortfolioValue, 0, 0, 0, 0, 0, errors.New("InvestmentSummary iterate error") } if investmentitem.EndDate == "0000-00-00" { investmentitem.EndDate = "" } investmentitem.InterestRepaid = 0 investmentitem.PrincipalRepaid = 0 investmentitem.TotalRepaid = 0 investmentitemAmount := getTotalInvestment(investmentitem.AccountID) //investmentitemAmountAvailableForWithdrawal := getTotalInvestmentAvailableForWithdrawal(investmentitem.AccountID) investmentitemAmountAvailableForWithdrawal := getTotalInvestmentAvailableForWithdrawalFromPassbook(investmentitem.AccountID) totalInvestment += investmentitemAmount investmentitem.PortfolioValue = investmentitemAmountAvailableForWithdrawal investmentitem.PortfolioValueAvailableForWithdrawal = investmentitemAmountAvailableForWithdrawal investmentitem.Amount = investmentitemAmount investmentSummary = append(investmentSummary, investmentitem) //totalInvestment += investmentitem.Amount accounts = append(accounts, investmentitem.AccountID) } var totalWithdrawal float64 var totalInterest float64 var totalInterestAccrued float64 var totalInterestPaidOut float64 interestPaidOutMap := make(map[int64]float64) //interestGainedMap := make(map[int64]float64) withdrawalMap := make(map[int64]float64) if len(accounts) > 0 { args := make([]interface{}, len(accounts)) for i, id := range accounts { args[i] = id } rows, err := tx.Query("select account_id, amount from transaction where txn_type in ('WITHDRAW', 'INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) for rows.Next() { var withdrawalAmount float64 var account_id int64 err = rows.Scan(&account_id, &withdrawalAmount) if err != nil { log.Print(err.Error()) return totalPortfolioValueAvailableForWithdrawal, investmentSummary, totalPortfolioValue, totalInvestment, totalWithdrawal, totalInterestPaidOut, totalInterestAccrued, totalInterest, nil } totalWithdrawal += withdrawalAmount withdrawalMap[account_id] += withdrawalAmount } totalInterestPaidOut, totalInterestAccrued, totalInterest, interestPaidOutMap, _ = getInterestSummaryByAccount(tx, args) } //log.Print(interestPaidOutMap) for c := 0; c < len(investmentSummary); c++ { if interestPaidAmount, ok := interestPaidOutMap[investmentSummary[c].AccountID]; ok { investmentSummary[c].InterestRepaid = interestPaidAmount } /*if interestGainedAmount, ok := interestGainedMap[investmentSummary[c].AccountID]; ok { investmentSummary[c].PortfolioValueAvailableForWithdrawal += interestGainedAmount investmentSummary[c].PortfolioValue += interestGainedAmount }*/ if withdrawalMapAmount, ok := withdrawalMap[investmentSummary[c].AccountID]; ok { //investmentSummary[c].PortfolioValue -= withdrawalMapAmount //investmentSummary[c].PortfolioValueAvailableForWithdrawal -= withdrawalMapAmount investmentSummary[c].TotalRepaid = withdrawalMapAmount log.Print("withdrawalMapAmount", withdrawalMapAmount) } investmentSummary[c].PrincipalRepaid += investmentSummary[c].TotalRepaid - investmentSummary[c].InterestRepaid totalPortfolioValue += investmentSummary[c].PortfolioValue totalPortfolioValueAvailableForWithdrawal += investmentSummary[c].PortfolioValueAvailableForWithdrawal } connection.Close(tx, db) return totalPortfolioValueAvailableForWithdrawal, investmentSummary, totalPortfolioValue, totalInvestment, totalWithdrawal, totalInterestPaidOut, totalInterestAccrued, totalInterest, nil } func getTotalInvestment(accountId int64) float64 { tx, _, db := connection.Connect() row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type = 'DEPOSIT' and status = 'SUCCESS' and account_id = ?", accountId) var totalInvestment float64 err := row.Scan(&totalInvestment) if err != nil { log.Print(err.Error()) return totalInvestment } connection.Close(tx, db) return totalInvestment } func getTotalInvestmentAvailableForWithdrawal(accountId int64) float64 { tx, _, db := connection.Connect() row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type = 'DEPOSIT' and status = 'SUCCESS' and account_id = ? and txn_date <= DATE(NOW() - INTERVAL 2 DAY)", accountId) var totalInvestment float64 err := row.Scan(&totalInvestment) if err != nil { log.Print(err.Error()) return totalInvestment } connection.Close(tx, db) return totalInvestment } func getTotalInvestmentAvailableForWithdrawalFromPassbook(accountId int64) float64 { tx, _, db := connection.Connect() row := tx.QueryRow("select IF(account_balance is null, 0, account_balance) as amount from pass_book where account_id = ? order by book_id desc limit 1", accountId) var totalInvestment float64 err := row.Scan(&totalInvestment) if err != nil { log.Print(err.Error()) return totalInvestment } connection.Close(tx, db) return totalInvestment } func getTotalInvestmentWithInterestCredit(accountId int64) float64 { tx, _, db := connection.Connect() row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type in ('DEPOSIT', 'INTEREST_CREDIT') and status = 'SUCCESS' and account_id = ?", accountId) var totalInvestment float64 err := row.Scan(&totalInvestment) if err != nil { log.Print(err.Error()) return totalInvestment } connection.Close(tx, db) return totalInvestment } func getInterestByDateAndAccount(tx *sql.Tx, accountId int64, date string) (float64, float64) { row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type in ('INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id = ? and txn_date <= ?", accountId, date) log.Print("interest debit ", date) var totalInterestPaidOut float64 var totalInterestAccrued float64 err := row.Scan(&totalInterestPaidOut) //End if err != nil && err != sql.ErrNoRows { log.Print(err.Error()) return totalInterestAccrued, totalInterestPaidOut } //Interest Gained (Credit) row = tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type = 'INTEREST_CREDIT' and status = 'SUCCESS' and account_id = ? and txn_date <= ?", accountId, date) err = row.Scan(&totalInterestAccrued) if err != nil && err != sql.ErrNoRows { log.Print(err.Error()) return totalInterestAccrued, totalInterestPaidOut } return totalInterestAccrued, totalInterestPaidOut //End } func getInterestGainedOut(ID int64, tx *sql.Tx) float64 { var totalInterestAccrued float64 row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type = 'INTEREST_CREDIT' and status = 'SUCCESS' and account_id = ?", ID) err := row.Scan(&totalInterestAccrued) if err != nil && err != sql.ErrNoRows { log.Print(err.Error()) return 0 } return totalInterestAccrued } func getInterestSummaryByAccount(tx *sql.Tx, args []interface{}) (float64, float64, float64, map[int64]float64, map[int64]float64) { interestPaidOutMap := make(map[int64]float64) interestGainedMap := make(map[int64]float64) var totalInterest float64 /*row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type = 'INTEREST_CREDIT' and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) err := row.Scan(&totalInterest) if err != nil { log.Print(err.Error()) return 0, 0, 0, interestPaidOutMap }*/ //Interest Paid Out (Debit) rows, err := tx.Query("select account_id, amount from transaction where txn_type in ('INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) var totalInterestPaidOut float64 if err != nil { log.Print(err.Error()) return 0, 0, totalInterest, interestPaidOutMap, interestGainedMap } for rows.Next() { var interestPaidOut float64 var accountId int64 err = rows.Scan(&accountId, &interestPaidOut) if err != nil { log.Print(err.Error()) return 0, 0, 0, interestPaidOutMap, interestGainedMap } totalInterestPaidOut += interestPaidOut interestPaidOutMap[accountId] += interestPaidOut } //End //Interest Gained (Credit) rows, err = tx.Query("select account_id, amount from transaction where txn_type = 'INTEREST_CREDIT' and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) if err != nil { log.Print(err.Error()) return 0, 0, totalInterest, interestPaidOutMap, interestGainedMap } for rows.Next() { var interestGained float64 var accountId int64 err = rows.Scan(&accountId, &interestGained) if err != nil { log.Print(err.Error()) return 0, 0, 0, interestPaidOutMap, interestGainedMap } totalInterest += interestGained interestGainedMap[accountId] += interestGained } //End row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type IN ('INTEREST_CREDIT', 'INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) var totalInterestAccrued float64 err = row.Scan(&totalInterestAccrued) if err != nil { log.Print(err.Error()) return 0, 0, 0, interestPaidOutMap, interestGainedMap } return totalInterestPaidOut, totalInterestAccrued, totalInterest, interestPaidOutMap, interestGainedMap } func (manager WithdrawalList) GetWithdrawalList() ([]WithdrawalList, error) { tx, _, db := connection.Connect() rows, err := tx.Query("select account_id, amount, deposit_type, gateway, reference_code, status, created_at from transaction where account_id = ? and txn_type = 'WITHDRAW' and status = 'INIT'", manager.Auth.ID) var withdrawalList []WithdrawalList if err != nil { log.Print(err.Error()) return withdrawalList, err } for rows.Next() { var withdrawalListItem WithdrawalList err = rows.Scan(&withdrawalListItem.AccountID, &withdrawalListItem.Amount, &withdrawalListItem.DepositType, &withdrawalListItem.Gateway, &withdrawalListItem.ReferenceCode, &withdrawalListItem.Status, &withdrawalListItem.DateOfWithdrawal) if err != nil { log.Print(err.Error()) tx.Rollback() return withdrawalList, errors.New("InvestmentSummary iterate error") } withdrawalList = append(withdrawalList, withdrawalListItem) } connection.Close(tx, db) return withdrawalList, nil } func GetGainedInterestByDate(date string, args []interface{}) float64 { tx, _, db := connection.Connect() //Interest Paid Out (Debit) rows, err := tx.Query("select amount from transaction where txn_type in ('INTEREST_DEBIT','INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) var totalInterestPaidOut float64 var totalInterestGained float64 if err != nil { log.Print(err.Error()) return 0 } for rows.Next() { var interestPaidOut float64 err = rows.Scan(&interestPaidOut) if err != nil { log.Print(err.Error()) return 0 } totalInterestPaidOut += interestPaidOut } //End //Interest Gained (Credit) rows, err = tx.Query("select amount from transaction where txn_type = 'INTEREST_CREDIT' and status = 'SUCCESS' and account_id in (?"+strings.Repeat(",?", len(args)-1)+")", args...) if err != nil { log.Print(err.Error()) return 0 } for rows.Next() { var interestGained float64 err = rows.Scan(&interestGained) if err != nil { log.Print(err.Error()) return 0 } totalInterestGained += interestGained } //End connection.Close(tx, db) return totalInterestGained - totalInterestPaidOut } //Confirmation func UpdatePayment(payments []PaymentConfirmation) string { dt := getDate() tx, ctx, db := connection.Connect() for i := 0; i < len(payments); i++ { row := tx.QueryRow("select txn_id, amount from transaction where reference_code = ? and account_id = ? and status = 'PENDING' and txn_type = 'DEPOSIT'", payments[i].TransactionID, payments[i].AccountId) var trId int64 var trAmount float64 err := row.Scan(&trId, &trAmount) if err != nil { log.Error().Err(err).Msg(err.Error()) return err.Error() } if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE transaction SET status = 'SUCCESS', updated_at = ? where txn_id = ?"), dt.Format("2006-01-02 15:04:05"), trId) if err != nil { log.Error().Err(err).Msg(err.Error()) } log.Print(res.RowsAffected()) } //check and update accounts row = tx.QueryRow("select account_id, scheme_id, account_status from account where account_id = ?", payments[i].AccountId) var accId int64 var schemeId int64 var accStatus string err = row.Scan(&accId, &schemeId, &accStatus) if err != nil { log.Error().Err(err).Msg(err.Error()) return err.Error() } if accStatus == "PENDING" { if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE account SET account_status = 'ACTIVE', updated_at = ? where account_id = ?"), dt.Format("2006-01-02 15:04:05"), accId) if err != nil { log.Error().Err(err).Msg(err.Error()) } log.Print(res.RowsAffected()) } } else { /*if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE account SET initial_amount = initial_amount + ? , updated_at = ? where account_id = ?"), trAmount, dt.Format("2006-01-02 15:04:05"), accId) if err != nil { log.Error().Err(err).Msg(err.Error()) } log.Print(res.RowsAffected()) }*/ } //create passbook res, err := tx.ExecContext(ctx, "INSERT INTO pass_book (account_id, entry_date, narration, credit, debit, principal_amount, account_balance, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", accId, dt.Format("2006-01-02 15:04:05"), "Deposit Amount", trAmount, 0, trAmount, trAmount, dt.Format("2006-01-02 15:04:05")) if err != nil { log.Error().Err(err).Msg(err.Error()) return err.Error() } passbook_id, err := res.LastInsertId() if err != nil { log.Error().Err(err).Msg(err.Error()) return err.Error() } log.Print("passbook_id", passbook_id) } connection.Close(tx, db) return "" } func UpdateWithdrawal(withdrawal []WithdrawalConfirmation) string { tx, ctx, db := connection.Connect() for i := 0; i < len(withdrawal); i++ { //Principal Withdrawal _ = PrincipalWithdrawal(tx, ctx, withdrawal, i) //Interest Withdrawal _ = InterestWithdrawal(tx, ctx, withdrawal, i) } connection.Close(tx, db) return "" } func PrincipalWithdrawal(tx *sql.Tx, ctx context.Context, withdrawal []WithdrawalConfirmation, i int) string { dt := getDate() row := tx.QueryRow("select txn_id, amount from transaction where reference_code = ? and account_id = ? and status = 'INIT' and txn_type = 'WITHDRAW'", withdrawal[i].TransactionID, withdrawal[i].AccountId) var trId int64 var trAmount float64 err := row.Scan(&trId, &trAmount) if err != nil { log.Print(err.Error()) return err.Error() } if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE transaction SET status = 'SUCCESS', updated_at = ? where txn_id = ?"), dt.Format("2006-01-02 15:04:05"), trId) if err != nil { log.Print(err.Error()) } log.Print(res.RowsAffected()) } //check and update accounts row = tx.QueryRow("select account_id, scheme_id, account_status from account where account_id = ?", withdrawal[i].AccountId) var accId int64 var schemeId int64 var accStatus string err = row.Scan(&accId, &schemeId, &accStatus) if err != nil { log.Print(err.Error()) return err.Error() } /*if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE account SET initial_amount = initial_amount - ? , updated_at = ? where account_id = ?"), trAmount, dt.Format("2006-01-02 15:04:05"), accId) if err != nil { log.Print(err.Error()) } log.Print(res.RowsAffected()) }*/ //create passbook res, err := tx.ExecContext(ctx, "INSERT INTO pass_book (account_id, entry_date, narration, credit, debit, principal_amount, account_balance, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", accId, dt.Format("2006-01-02 15:04:05"), "Withdrawal Amount - Principal", 0, trAmount, trAmount, trAmount, dt.Format("2006-01-02 15:04:05")) if err != nil { log.Print(err.Error()) return err.Error() } passbook_id, err := res.LastInsertId() if err != nil { log.Print(err.Error()) return err.Error() } log.Print("passbook_id", passbook_id) return "" } func InterestWithdrawal(tx *sql.Tx, ctx context.Context, withdrawal []WithdrawalConfirmation, i int) string { dt := getDate() row := tx.QueryRow("select txn_id, amount from transaction where reference_code = ? and account_id = ? and status = 'INIT' and txn_type in ('INTEREST_DEBIT','INTEREST_DEBIT_MONTHLY')", withdrawal[i].TransactionID, withdrawal[i].AccountId) var trId int64 var trAmount float64 err := row.Scan(&trId, &trAmount) if err != nil { log.Print(err.Error()) return err.Error() } if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE transaction SET status = 'SUCCESS', updated_at = ? where txn_id = ?"), dt.Format("2006-01-02 15:04:05"), trId) if err != nil { log.Print(err.Error()) } log.Print(res.RowsAffected()) } //check and update accounts row = tx.QueryRow("select account_id, scheme_id, account_status from account where account_id = ?", withdrawal[i].AccountId) var accId int64 var schemeId int64 var accStatus string err = row.Scan(&accId, &schemeId, &accStatus) if err != nil { log.Print(err.Error()) return err.Error() } /*if trId > 0 { res, err := tx.ExecContext(ctx, fmt.Sprint("UPDATE account SET initial_amount = initial_amount - ? , updated_at = ? where account_id = ?"), trAmount, dt.Format("2006-01-02 15:04:05"), accId) if err != nil { log.Print(err.Error()) } log.Print(res.RowsAffected()) }*/ //create passbook res, err := tx.ExecContext(ctx, "INSERT INTO pass_book (account_id, entry_date, narration, credit, debit, principal_amount, account_balance, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", accId, dt.Format("2006-01-02 15:04:05"), "Withdrawal Amount - Interest", 0, trAmount, trAmount, trAmount, dt.Format("2006-01-02 15:04:05")) if err != nil { log.Print(err.Error()) return err.Error() } passbook_id, err := res.LastInsertId() if err != nil { log.Print(err.Error()) return err.Error() } log.Print("passbook_id", passbook_id) return "" } func GetRequestID() string { var letters = []rune("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ") rand.Seed(time.Now().UnixNano()) var n int64 = 10 b := make([]rune, n) for i := range b { b[i] = letters[rand.Intn(len(letters))] } log.Print(string(b)) return string(b) } func getDayDiffFromStartOfMonth(format string, date time.Time) int64 { start := time.Date(date.Year(), time.Month(date.Month()), 1, 0, 0, 0, 0, time.UTC) diff := date.Sub(start) return int64(diff.Hours() / 24) } func getDate() time.Time { loc, _ := time.LoadLocation("Asia/Kolkata") dt := time.Now().In(loc) return dt } func isMonthStart(date time.Time) int { if date.Day() == getFirstDayOfMonth() { return 1 } return 0 } func isMonthEnd(date time.Time) int { if date.Day() == getLastDayOfMonth(date) { return 1 } return 0 } func getLastDayOfMonth(date time.Time) int { //now := time.Now() currentYear, currentMonth, _ := date.Date() currentLocation := date.Location() firstOfMonth := time.Date(currentYear, currentMonth, 1, 0, 0, 0, 0, currentLocation) return firstOfMonth.AddDate(0, 1, -1).Day() } func getFirstDayOfMonth() int { now := time.Now() currentYear, currentMonth, _ := now.Date() currentLocation := now.Location() firstOfMonth := time.Date(currentYear, currentMonth, 1, 0, 0, 0, 0, currentLocation) return firstOfMonth.Day() }