package statement import ( aes "cf-capital/module_server/handlers/aes" connection "cf-capital/module_server/handlers/connection" eloquent "cf-capital/module_server/handlers/eloquent" mail "cf-capital/module_server/handlers/email" financial "cf-capital/module_server/handlers/investment/financial" "cf-capital/module_server/handlers/pdf" "context" "database/sql" "encoding/csv" "errors" "fmt" log "github.com/rs/zerolog/log" "math" "math/rand" "os" "strconv" "strings" "time" ) 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 WithdrawalPolicyType string 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 MonthlyInterest MonthlyInterest } type MonthlyInterest struct { Amount float64 Date string Statement Statements Balance float64 } type Statements struct { PassbookInsert string TransactionInsert string } type Invest struct { InvestorID int `json:"id"` 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"` //Callback string 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"` InvestorID int64 `json:"investor_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"` InvestorID int64 `json:"investor_id"` 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 InvestorPortfolio struct { Dashboard Dashboard ID string `json:"id"` } type Withdrawal struct { InvestorID int `json:"id"` 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 Investors struct { Data InvestorsData Auth eloquent.Auth } type InvestorsData struct { InvestorId int64 `json:"id"` Name string `json:"name"` MobileNumber string `json:"mobile_number"` NetInvestment string `json:"net_investment"` WithdrawalAmount string `json:"withdrawal_amount"` } type Transactions struct { Data TransactionData Auth eloquent.Auth } type TransactionData struct { AccountID int64 `json:"account_id"` TxnDate string `json:"txn_date"` Name string `json:"name"` Amount string `json:"amount"` TxnType string `json:"txn_type"` } 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 /*if t.Unix() >= time.Now().Unix(){ break }*/ 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 if transactionDataDeposit[s.Format("2006-01-02")] > 0 { payMain[inc].Amount = transactionDataDeposit[s.Format("2006-01-02")] } principal = payMain[inc].Amount payMain[inc].Type = "Investment" } if (inc + 1) == len(payMain) { break } e := payMain[inc].Date diff := e.Sub(s) d := int64(diff.Hours() / 24) payMain[inc].Days = d payMain[inc].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].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].Date).AddDate(0, 0, 0).Format("2006-01-02")] //log.Print("Pre", (payMain[inc+1].Date).AddDate(0, 0, -1).Format("2006-01-02")) //log.Print("Current", (payMain[inc+1].Date).Format("2006-01-02")) //log.Print(DepositAmount) //log.Print((payMain[inc+1].Date).Format("2006-01-02")) if DepositAmount > 0 && inc > 0 { //log.Print("deposit Amount consider - ", DepositAmount, " Date ", (payMain[inc+1].Date).Format("2006-01-02"), " Consider ", (payMain[inc+1].Date).AddDate(0, 0, -1).Format("2006-01-02")) principal += DepositAmount //log.Print("Principal - ",principal, " Date ", (payMain[inc+1].Date).Format("2006-01-02")) } DepositAmount = 0 //Deposit - end amount := c * principal if amount < 0 { amount = 0 } if principal < 0 { principal = 0 } payMain[inc].Amount = amount principal += amount payMain[inc].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 //fmt.Println(manager.WithdrawalPolicyType) if isMonthEnd(payMain[inc].Date) == 1 && manager.WithdrawalPolicyType == "M" { principal = payMain[inc].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) //if payMain[inc].Date.Format("2006-01-02") == "2023-04-30" { pStatement := "INSERT INTO pass_book (account_id, entry_date, narration, credit, debit, principal_amount, account_balance, created_at) VALUES("+ strconv.FormatInt(manager.AccountId, 10)+ ",'"+ payMain[inc].Date.Format("2006-01-02")+ "', 'Monthly Interest Payout for "+ payMain[inc].Date.Format("January-2006")+ "', 0, "+ fmt.Sprintf("%.2f", totalInterestEarned)+ ","+ fmt.Sprintf("%.2f", principal)+ ","+ fmt.Sprintf("%.2f", principal)+ ", '"+ payMain[inc].Date.Format("2006-01-02 15:04:05")+ "')" tStatement := "INSERT INTO transaction (account_id, txn_date, txn_type, amount, deposit_type, status, created_at) VALUES("+ strconv.FormatInt(manager.AccountId, 10)+ ",'"+ payMain[inc].Date.Format("2006-01-02")+ "', 'INTEREST_DEBIT_MONTHLY', "+ fmt.Sprintf("%.2f", totalInterestEarned)+ ", 'OFFLINE', 'SUCCESS', '"+ payMain[inc].Date.Format("2006-01-02 15:04:05")+ "')" var monthlyInterest MonthlyInterest var statementStruct Statements statementStruct.PassbookInsert = pStatement statementStruct.TransactionInsert = tStatement monthlyInterest.Amount = totalInterestEarned monthlyInterest.Balance = principal monthlyInterest.Date = payMain[inc].Date.Format("2006-01-02") monthlyInterest.Statement = statementStruct payMain[inc].MonthlyInterest = monthlyInterest //} //fmt.Println("AccountID", manager.AccountId) //fmt.Println("totalInterestEarned", totalInterestEarned) 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 if manager.InvestorID > 0 { investment.Auth.ID = manager.Auth.ID } _, 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 } if manager.InvestorID > 0 { manager.Auth.ID = strconv.Itoa(manager.InvestorID) } 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_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())", manager.AccountID, txnDate, TransactionDeposit, manager.Amount, manager.IP4Address, method, gateway, manager.ReferenceCode, manager.TransactionReference, status, manager.Auth.ID) 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, entry_date 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, entry_date, 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, reason, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", manager.AccountID, TxnType, txnDate, manager.Amount, manager.IP4Address, method, gateway, manager.ReferenceCode, "INIT", manager.Reason, manager.Auth.ID, 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, reason, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", manager.AccountID, TxnType, txnDate, interestBalance, manager.IP4Address, method, gateway, manager.ReferenceCode, "INIT", manager.Reason, manager.Auth.ID, 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, reason, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", manager.AccountID, manager.TxnType, txnDate, principal, manager.IP4Address, method, gateway, manager.ReferenceCode, "INIT", manager.Reason, manager.Auth.ID, 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 pass_book.entry_date >= ? and pass_book.entry_date <= ? order by pass_book.entry_date asc" rows, err = tx.Query(query, manager.AccountID, 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.entry_date 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.InvestorID > 0 { log.Print("InvestorID") log.Print(manager.InvestorID) //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.InvestorID) } else 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 account.account_status = 'ACTIVE'" rows, err = tx.Query(query, manager.AccountID) } 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 if manager.InvestorID > 0 { query = "select s.scheme_id, s.scheme_name from scheme s left join account a on s.scheme_id = a.scheme_id where status = 'ACTIVE' and is_visible = 1 and investor_id = ?" rows, err = tx.Query(query, manager.InvestorID) } 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, 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()) } format := "2006-01-02 15:04:05" _format := "2006-01-02" _txnDate, _ := time.Parse(format, txnDate) if _txnDate.Hour() <= 14 || (_txnDate.Hour() == 15 && _txnDate.Minute() == 0 && _txnDate.Second() == 0) { txnDate = _txnDate.Format(_format) } else { txnDate = _txnDate.AddDate(0, 0, 1).Format(_format) } if trxns[txnDate] > 0 { trxns[txnDate] += amount } else { 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, 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()) } format := "2006-01-02 15:04:05" _format := "2006-01-02" _txnDate, _ := time.Parse(format, txnDate) if _txnDate.Hour() <= 14 || (_txnDate.Hour() == 15 && _txnDate.Minute() == 0 && _txnDate.Second() == 0) { txnDate = _txnDate.Format(_format) } else { txnDate = _txnDate.AddDate(0, 0, 1).Format(_format) } if trxns[txnDate] > 0 { trxns[txnDate] += amount } else { trxns[txnDate] = amount } } log.Print(trxns) 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") } InvestorId := manager.Auth.ID if manager.Auth.UserType == 3 && manager.InvestorID > 0 { InvestorId = strconv.Itoa(manager.InvestorID) log.Print("InvestorId", InvestorId) } log.Print("InvestorId - ", InvestorId) 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, InvestorId) 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 InitialAmount = getTotalInvestmentAvailableForWithdrawalFromPassbook(manager.AccountID) log.Print(InitialAmount) log.Print(manager.Amount) 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() log.Print("accountId", accountId) 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) log.Print("totalInvestment", 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, sendCommunication bool) 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()) }*/ } investmentItemAmountAvailableForWithdrawal := getTotalInvestmentAvailableForWithdrawalFromPassbook(accId) log.Print(investmentItemAmountAvailableForWithdrawal) var updatedAmount float64 if investmentItemAmountAvailableForWithdrawal > 0 { updatedAmount = investmentItemAmountAvailableForWithdrawal + trAmount log.Print("updatedAmount", updatedAmount) } if updatedAmount == 0 { updatedAmount = trAmount } log.Print("updatedAmount -", updatedAmount) //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, updatedAmount, updatedAmount, 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) if sendCommunication { for i := 0; i < len(payments); i++ { id, name, _, _, _, _, ifaEmail, err := getInvestedUserDetails(payments[i].AccountId, payments[i].TransactionID) if err == nil { sendInvestmentConfirmation(payments[i].AccountId, id, name, ifaEmail) } else { log.Print(err) } } } return "" } func UpdateWithdrawal(withdrawal []WithdrawalConfirmation) string { tx, ctx, db := connection.Connect() interestAmountMap := make(map[int]float64) for i := 0; i < len(withdrawal); i++ { //Principal Withdrawal balanceAmount, err := PrincipalWithdrawal(tx, ctx, withdrawal, i) log.Print("balanceAmount", balanceAmount) //Interest Withdrawal _, interestAmount := InterestWithdrawal(tx, ctx, withdrawal, i, balanceAmount) if len(err) == 0 { interestAmountMap[i] = interestAmount } } connection.Close(tx, db) for i := 0; i < len(interestAmountMap); i++ { name, xirrRate, schemeType, amount, email, ifaEmail, err := getWithdrawalUserDetails(withdrawal[i].AccountId, withdrawal[i].TransactionID) if err == nil { sendWithdrawalConfirmation(name, amount, schemeType, xirrRate, pdf.FormatMoneyFloat64(interestAmountMap[i], 0), email, ifaEmail) } else { log.Print(err) } } return "" } func getInvestedUserDetails(accountId int64, referenceCode string) (int64, string, string, string, string, string, string, error) { tx, _, db := connection.Connect() row := tx.QueryRow("select id, COALESCE(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')), '') as name, COALESCE(s.xirr_rate, ''), s.scheme_type, SUM(t.amount), u.email, (select COALESCE(email, '') from users where u.referred_by = users.referral_code) as ifa_email from transaction as t left join account a on a.account_id = t.account_id left join scheme s on a.scheme_id = s.scheme_id left join users u on a.investor_id = u.id where a.account_id = ? and t.reference_code = ? and t.txn_type in ('DEPOSIT')", accountId, referenceCode) var id int64 var name string var xirrRate string var schemeType string var amount string var email string var ifaEmail string err := row.Scan(&id, &name, &xirrRate, &schemeType, &amount, &email, &ifaEmail) if err != nil { log.Print(err.Error()) return id, name, xirrRate, schemeType, amount, email, ifaEmail, err } connection.Close(tx, db) return id, name, xirrRate, schemeType, amount, email, ifaEmail, err } func getWithdrawalUserDetails(accountId int64, referenceCode string) (string, string, string, string, string, string, error) { tx, _, db := connection.Connect() row := tx.QueryRow("select COALESCE(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')), '') as name, COALESCE(s.xirr_rate, ''), s.scheme_type, SUM(t.amount), u.email, COALESCE((select email from creditcapital.users where u.referred_by = users.referral_code), '') as ifa_email from transaction as t left join account a on a.account_id = t.account_id left join scheme s on a.scheme_id = s.scheme_id left join users u on a.investor_id = u.id where a.account_id = ? and t.reference_code = ? and t.txn_type in ('WITHDRAW', 'INTEREST_DEBIT')", accountId, referenceCode) var name string var xirrRate string var schemeType string var amount string var email string var ifaEmail string err := row.Scan(&name, &xirrRate, &schemeType, &amount, &email, &ifaEmail) if err != nil { log.Print(err.Error()) return name, xirrRate, schemeType, amount, email, ifaEmail, err } connection.Close(tx, db) return name, xirrRate, schemeType, amount, email, ifaEmail, err } func sendInvestmentConfirmation(accountId int64, InvestorID int64, name string, ifaEmail string) { HtmlBody := "
\n
\n\n
\n\n\n\n\n\n\n
\n\n\n\n\n\n\n\n\n\n\n\n\n \n\n\n
\n\n\n\n\n\n\n
\n\n
\n
\n
\nHello " + name + ",

\nThank you for Investing in Credit Fair Capital. You have successfully completed your investment process.

\nYou can find your investment details in the attached file.

\nPlease click on the link to keep continuing with your investment journey with us.

\nWe are here to serve you so that you can reach out to us any time in case of any queries at info@creditfaircapital.in and 8097400463.

\nOnce again, thank you for trusting your faith in Credit Fair Capital.
\n
\n
\n Regards,
\n Team Credit Fair Capital
\n info@creditfaircapital.in
\n +91-8097400463

\n

Disclaimer: This message (including any attachments) may contain\n confidential, proprietary, privileged and/or private information. The information is intended for the use only\n to whom they are addressed. If you are not the intended recipient of this message, please notify the sender\n immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use\n of this message or any attachments by an individual or entity other than the intended recipient is prohibited.We\n do not guarantee the security of any information transmitted through email and, are not liable for any\n interception, corruption or delays. Anyone communicating with us by email accepts the risks and\n consequences.\n

\n
\n \n
\n
\n
\n
\n

\n
\n
\n
\n
\n
" currentTime := time.Now() dateTime := currentTime.Format("Mon 2006-01-2 15:04:05") log.Print("trigger email to investor") SendAttachment(accountId, InvestorID, HtmlBody, "Investment Confirmation", dateTime, ifaEmail+",info@creditfaircapital.in", 0) /*_, err := mail.Email(HtmlBody, "Investment Confirmation", email, ifaEmail) if err != nil { log.Print("trigger email error") log.Print(err.Error()) }*/ } func sendWithdrawalConfirmation(name string, amount string, planType string, rate string, interestAmount string, email string, ifaEmail string) { HtmlBody := "
\n
\n\n
\n\n\n\n\n\n\n
\n\n\n\n\n\n\n\n\n\n\n\n\n \n\n\n
\n\n\n\n\n\n\n
\n\n
\n
\n
\nHello " + name + ",

\nThank you for trusting and being associated with Credit fair Capital.

\nYour Withdrawal is credited to your account.

Investment
Amount :- " + amount + "
Investment type:- " + planType + "
Returns:- " + rate + "
Interest Amount :- " + interestAmount + "

\nPlease click on the link to keep continuing with your investment journey with us.

We are here to serve you, so you can reach out to us any time in case of any queries at info@creditfaircapital.in and 8097400463.\n

\nOnce again, thank you for trusting your faith in Credit Fair Capital.
\n
\n
\n Regards,
\n Team Credit Fair Capital
\n info@creditfaircapital.in
\n +91-8097400463

\n

Disclaimer: This message (including any attachments) may contain\n confidential, proprietary, privileged and/or private information. The information is intended for the use only\n to whom they are addressed. If you are not the intended recipient of this message, please notify the sender\n immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use\n of this message or any attachments by an individual or entity other than the intended recipient is prohibited.We\n do not guarantee the security of any information transmitted through email and, are not liable for any\n interception, corruption or delays. Anyone communicating with us by email accepts the risks and\n consequences.\n

\n
\n \n
\n
\n
\n
\n

\n
\n
\n
\n
\n
" log.Print("trigger sendWithdrawalConfirmation") _, err := mail.Email(HtmlBody, "Withdrawal Confirmation", email, ifaEmail+",info@creditfaircapital.in") if err != nil { log.Print("trigger email error") log.Print(err.Error()) } } func PrincipalWithdrawal(tx *sql.Tx, ctx context.Context, withdrawal []WithdrawalConfirmation, i int) (float64, 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 0, err.Error() } totalAmount := getTotalInvestmentAvailableForWithdrawalFromPassbook(withdrawal[i].AccountId) if totalAmount < trAmount { return 0, "Amount is greater than the available amount" } 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 0, 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()) }*/ principalAmount := trAmount if totalAmount > 0 { principalAmount = totalAmount - trAmount } //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, principalAmount, principalAmount, dt.Format("2006-01-02 15:04:05")) if err != nil { log.Print(err.Error()) return 0, err.Error() } passbook_id, err := res.LastInsertId() if err != nil { log.Print(err.Error()) return 0, err.Error() } log.Print("passbook_id", passbook_id) return principalAmount, "" } func IsInvestorUnderIFA(ifdID string, ID int64) int64 { tx, _, db := connection.Connect() log.Print(ifdID) log.Print(ID) row := tx.QueryRow("select id from users where referred_by in (select referral_code from users where id = ?) and id = ?", ifdID, ID) var trId int64 err := row.Scan(&trId) if err != nil { log.Print(err.Error()) return 0 } connection.Close(tx, db) return trId } func GetInvestorListUnderIFA(ID string) map[int]int64 { tx, _, db := connection.Connect() rows, err := tx.Query("select id from users where referred_by in (select referral_code from users where id = ?)", ID) investors := make(map[int]int64) if err != nil { log.Print(err.Error()) return investors } i := 0 for rows.Next() { var investorId int64 err = rows.Scan(&investorId) if err != nil { log.Print(err.Error()) tx.Rollback() return investors } else { investors[i] = investorId } i++ } connection.Close(tx, db) log.Print(investors) return investors } func (manager Investors) GetInvestors() ([]InvestorsData, error) { var investorsData []InvestorsData investors := GetInvestorListUnderIFA(manager.Auth.ID) if len(investors) > 0 { args := make([]interface{}, len(investors)) for i, id := range investors { args[i] = id } log.Print(args) tx, _, db := connection.Connect() rows, err := tx.Query("select users.id, COALESCE(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')), '') as name, mobile_number, (select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type = 'DEPOSIT' and status = 'SUCCESS' and account_id in (select account_id from account where account.investor_id = users.id)) as netInvestmentAmount, (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 in (select account_id from account where account.investor_id = users.id)) as withdrawalAmount from users where id in (?"+strings.Repeat(",?", len(args)-1)+") group by users.id", args...) if err != nil { log.Print(err.Error()) } for rows.Next() { var investorData InvestorsData err = rows.Scan(&investorData.InvestorId, &investorData.Name, &investorData.MobileNumber, &investorData.NetInvestment, &investorData.WithdrawalAmount) if err != nil { log.Print(err.Error()) } investorData.MobileNumber = aes.DecryptData(investorData.MobileNumber) investorsData = append(investorsData, investorData) } connection.Close(tx, db) } return investorsData, nil } func (manager Transactions) GetRecentTransaction() ([]TransactionData, error) { var transactions []TransactionData investors := GetInvestorListUnderIFA(manager.Auth.ID) if len(investors) > 0 { args := make([]interface{}, len(investors)) for i, id := range investors { args[i] = id } tx, _, db := connection.Connect() rows, err := tx.Query("select transaction.account_id, DATE_FORMAT(txn_date, '%d-%m-%Y'), COALESCE(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')), '') as name, transaction.amount, transaction.txn_type from transaction left join account a on transaction.account_id = a.account_id left join users u on u.id = a.investor_id where a.investor_id in (?"+strings.Repeat(",?", len(args)-1)+") and status = 'SUCCESS' and txn_type IN ('DEPOSIT', 'WITHDRAW') order by transaction.txn_date desc limit 5", args...) //rows, err := tx.Query("select account_id, amount, txn_date, txn_type, deposit_type from transaction where account_id in (select account.account_id from account where investor_id in (?"+strings.Repeat(",?", len(args)-1)+")) and txn_type != 'INTEREST_CREDIT' group by account_id limit 5", args...) if err != nil { log.Print(err.Error()) } for rows.Next() { var transaction TransactionData err = rows.Scan(&transaction.AccountID, &transaction.TxnDate, &transaction.Name, &transaction.Amount, &transaction.TxnType) if err != nil { log.Print(err.Error()) } if transaction.TxnType == "WITHDRAW" { transaction.TxnType = "WITHDRAWAL" } transactions = append(transactions, transaction) } connection.Close(tx, db) } return transactions, nil } func InterestWithdrawal(tx *sql.Tx, ctx context.Context, withdrawal []WithdrawalConfirmation, i int, balanceAmount float64) (string, float64) { 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(), trAmount } 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(), trAmount } /*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()) }*/ principalAmount := trAmount if balanceAmount > 0 && trAmount <= balanceAmount { principalAmount = balanceAmount - trAmount } //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, principalAmount, principalAmount, dt.Format("2006-01-02 15:04:05")) if err != nil { log.Print(err.Error()) return err.Error(), trAmount } passbook_id, err := res.LastInsertId() if err != nil { log.Print(err.Error()) return err.Error(), trAmount } log.Print("passbook_id", passbook_id) return "", trAmount } 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() } func SendAttachment(AccountID int64, InvestorId int64, HtmlBody string, subject string, dateTime string, cc string, isCheck int) { var statement pdf.Statement statement, base64 := MonthlyStatement(AccountID, isCheck) var file eloquent.S3File file.FileName = "monthly-statement-" + dateTime + ".pdf" file.Path = fmt.Sprintf("/capital/", InvestorId, "/", file.FileName) file.ID = InvestorId file.Base64 = base64 _, err := eloquent.UploadDocumentToS3(file) if err != nil { log.Print(err.Error()) } log.Print("trigger email, month end") attachment := map[string]string{"File": file.Base64, "FileName": file.FileName} _, err = mail.EmailAttachment(statement.InvestorDetails.Email, HtmlBody, subject, attachment, cc) if err != nil { log.Print("trigger email error") log.Print(err.Error()) } } func MonthlyStatement(accountId int64, checkDate int) (pdf.Statement, string) { dt := getDate() //dt = dt.AddDate(0,0,-21) //log.Print(dt.Format("2006-01-02")) tx, _, db := connection.Connect() startOfMonth := getFirstDateOfMonth(dt) endOfMonth := getLastDateOfMonth(dt) var query string if checkDate == 1 { query = "SELECT ac.account_id, DATE_FORMAT(ac.start_date,'%d-%m-%Y'), IF(u.first_name is null, u.first_name, (concat(COALESCE(u.first_name,''), ' ', COALESCE(u.middle_name,''),' ', COALESCE(u.last_name,'')))) as name, COALESCE(u.address, ''), COALESCE(u.mobile_number,''), COALESCE(u.email,''), COALESCE(bd.bank_name,''), COALESCE(bd.ifsc_code,''), COALESCE(bd.account_number,''), COALESCE(u.pan,''), COALESCE(sc.scheme_name,''), COALESCE(sc.scheme_type,''), IF(sc.withdrawal_policy_type = 'M', 'Monthly', 'Cumulative') as withdrawal_policy_type, COALESCE(sc.tenure_type, 'D'), COALESCE(sc.tenure_duration,0), IF(u.referred_by is not null, (select concat(COALESCE(users.first_name,''), ' ', COALESCE(users.middle_name,''),' ', COALESCE(users.last_name,'')) from users where users.referral_code = u.referred_by), '') as ifa_name, (select IF(account.initial_amount is null, 0, SUM(account.initial_amount)) as amount from account where account.account_id = ac.account_id) as NetInvestment, (select IF(pass_book.account_balance is null, 0, pass_book.account_balance) as amount from pass_book where pass_book.account_id = ac.account_id order by pass_book.created_at desc limit 1) as NetCurrentValue, (select IF(transaction.amount is null, 0, SUM(transaction.amount)) as amount from transaction where transaction.account_id = ac.account_id and txn_type in ('INTEREST_DEBIT','INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS') as InterestRedeemed, (select IF(transaction.amount is null, 0, SUM(transaction.amount)) as amount from transaction where transaction.account_id = ac.account_id and txn_type in ('INTEREST_CREDIT')) as TotalInterestEarned, sc.xirr_rate from users as u left join account ac on ac.investor_id = u.id left join bank_details bd on bd.user_id = u.id left join scheme sc on sc.scheme_id = ac.scheme_id where ac.account_status = 'ACTIVE' and ((ac.end_date is null || ac.end_date = '0000-00-00') or ac.end_date >= DATE(NOW())) and ac.account_id = ? group by u.id" } else { query = "SELECT ac.account_id, DATE_FORMAT(ac.start_date,'%d-%m-%Y'), IF(u.first_name is null, u.first_name, (concat(COALESCE(u.first_name,''), ' ', COALESCE(u.middle_name,''),' ', COALESCE(u.last_name,'')))) as name, COALESCE(u.address, ''), COALESCE(u.mobile_number,''), COALESCE(u.email,''), COALESCE(bd.bank_name,''), COALESCE(bd.ifsc_code,''), COALESCE(bd.account_number,''), COALESCE(u.pan,''), COALESCE(sc.scheme_name,''), COALESCE(sc.scheme_type,''), IF(sc.withdrawal_policy_type = 'M', 'Monthly', 'Cumulative') as withdrawal_policy_type, COALESCE(sc.tenure_type, 'D'), COALESCE(sc.tenure_duration,0), IF(u.referred_by is not null, (select concat(COALESCE(users.first_name,''), ' ', COALESCE(users.middle_name,''),' ', COALESCE(users.last_name,'')) from users where users.referral_code = u.referred_by), '') as ifa_name, (select IF(account.initial_amount is null, 0, SUM(account.initial_amount)) as amount from account where account.account_id = ac.account_id) as NetInvestment, (select IF(pass_book.account_balance is null, 0, pass_book.account_balance) as amount from pass_book where pass_book.account_id = ac.account_id order by pass_book.created_at desc limit 1) as NetCurrentValue, (select IF(transaction.amount is null, 0, SUM(transaction.amount)) as amount from transaction where transaction.account_id = ac.account_id and txn_type in ('INTEREST_DEBIT','INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS') as InterestRedeemed, (select IF(transaction.amount is null, 0, SUM(transaction.amount)) as amount from transaction where transaction.account_id = ac.account_id and txn_type in ('INTEREST_CREDIT')) as TotalInterestEarned, sc.xirr_rate from users as u left join account ac on ac.investor_id = u.id left join bank_details bd on bd.user_id = u.id left join scheme sc on sc.scheme_id = ac.scheme_id where ac.account_status IN ('PENDING', 'ACTIVE') and ac.account_id = ? group by u.id" } //query := "select ac.scheme_id, ac.account_id, s.xirr_rate, investor_id, initial_amount, start_date, COALESCE(end_date, '') as end_date, IF ((select txn_id from transaction where status = 'SUCCESS' and txn_type = 'INTEREST_CREDIT' and date(created_at) = DATE(NOW()) and ac.account_id = transaction.account_id) is null, 0, 1) as interest_credited from account ac left join scheme s on ac.scheme_id = s.scheme_id where account_status = 'ACTIVE' and s.scheme_type = 'FLEXI' and end_date = '0000-00-00' and start_date <= DATE(NOW() - INTERVAL 1 DAY)" var statement pdf.Statement var summaryDetails []pdf.AccountSummary today := dt.Format("02-01-2006") /*totalRows := 0 for rowsCount.Next() {totalRows++} accIds := make([]interface{}, totalRows) i := 0 for rowsCount.Next() { var acId int64 err = rowsCount.Scan(&acId) if err != nil && err != sql.ErrNoRows { log.Error().Err(err).Msg(err.Error()) } accIds[i] = acId i++ } transactionQuery := "select book_id, narration, entry_date, credit, principal_amount, account_balance from pass_book where account_id in (?"+strings.Repeat(",?", len(accIds)-1)+")" transactionRows, err := tx.Query(transactionQuery, accIds...) var transactionDetails []pdf.Transaction transactionDetailsObject := make([]pdf.Transaction, totalRows) for transactionRows.Next() { var transaction pdf.Transaction err = transactionRows.Scan(&transaction.TransactionType, &transaction.TransactionType, &transaction.Date, &transaction.TransactionAmount, &transaction.ClosingPrincipalAsOnDate, &transaction.CurrentValueAsOnDate) if err != nil && err != sql.ErrNoRows { log.Error().Err(err).Msg(err.Error()) } transactionDetails = append(transactionDetails, transaction) transactionDetailsObject[1] = transactionDetails }*/ rows, err := tx.Query(query, accountId) for rows.Next() { log.Print(&statement.AccountId) var summary pdf.AccountSummary var summarySecond pdf.AccountSummary err = rows.Scan(&statement.AccountId, &statement.StatementPeriod.From, &statement.InvestorDetails.Name, &statement.InvestorDetails.Address, &statement.InvestorDetails.Phone, &statement.InvestorDetails.Email, &statement.InvestorDetails.Bank.Name, &statement.InvestorDetails.Bank.IFSCCode, &statement.InvestorDetails.Bank.AccountNumber, &statement.InvestorDetails.Bank.PAN, &summary.SchemeName, &summary.Scheme, &summary.Payout, &summary.TenureType, &summary.HoldingPeriod, &statement.InvestorDetails.Bank.FinancialAdvisor, &summarySecond.NetInvestment, &summarySecond.NetCurrentValue, &summarySecond.InterestRedeemed, &statement.InterestSummary.InterestEarnedAsOfAmount, &summarySecond.CurrentReturn) if err != nil && err != sql.ErrNoRows { log.Error().Err(err).Msg(err.Error()) } transactionsObject := getTransactions(statement.AccountId, summary.Payout, summarySecond.NetInvestment) //to show total investment till now summarySecond.NetInvestment = getTotalDepositAmountByAccountID(statement.AccountId) summarySecond.InterestAccrued = getThisMonthInterest(statement.AccountId, summary.Payout, startOfMonth, endOfMonth) statement.StatementDate = today //statement.StatementPeriod.From = startOfMonth statement.StatementPeriod.To = today statement.InvestorDetails.Bank.ModeOfPayout = "Electronic" statement.InvestorDetails.Bank.ModeOfHolding = "Single" statement.InterestSummary.InterestEarnedAsOfDate = today statement.Account.AccountSummaryAsOfDate = today //summarySecond.InterestAccrued = summarySecond.NetInvestment - summarySecond.NetCurrentValue summaryDetails = append(summaryDetails, summary) summaryDetails = append(summaryDetails, summarySecond) statement.Account.AccountSummary = summaryDetails statement.Transaction = transactionsObject } var base64 string if len(statement.Transaction) > 0 { base64, err = pdf.StatementPDF(statement) if err != nil { log.Print("OK") } } connection.Close(tx, db) return statement, base64 } func getFirstDateOfMonth(now time.Time) string { currentYear, currentMonth, _ := now.Date() currentLocation := now.Location() firstOfMonth := time.Date(currentYear, currentMonth, 1, 0, 0, 0, 0, currentLocation) return firstOfMonth.Format("2006-01-02") } func getLastDateOfMonth(now time.Time) string { currentYear, currentMonth, _ := now.Date() currentLocation := now.Location() firstOfMonth := time.Date(currentYear, currentMonth, 1, 0, 0, 0, 0, currentLocation) lastOfMonth := firstOfMonth.AddDate(0, 1, -1) return lastOfMonth.Format("2006-01-02") } func getTransactions(accountId int64, WithdrawalPolicyType string, investmentAmount float64) []pdf.Transaction { tx, _, db := connection.Connect() var transactionQuery string var transactionRows *sql.Rows var err error if WithdrawalPolicyType == "Monthly" { transactionQuery = "select book_id, narration, DATE_FORMAT(entry_date,'%d-%m-%Y'), credit, debit, principal_amount, account_balance, created_at from ((select book_id, narration, entry_date, credit, debit, principal_amount, account_balance, created_at from pass_book where account_id = ? and DATE_FORMAT(entry_date,'%d-%m') in ('01-01', '01-02', '01-03', '01-04', '01-05', '01-06', '01-07','01-08', '01-09', '01-10', '01-11', '01-12') order by entry_date asc) union (select book_id, narration, entry_date, credit, debit, principal_amount, account_balance, created_at from pass_book where account_id = ? and narration in ('Deposit Amount', 'Withdrawal Amount - Principal', 'Withdrawal Amount - Interest'))) results order by entry_date desc, credit asc" transactionRows, err = tx.Query(transactionQuery, accountId, accountId) } else { //transactionQuery = "select book_id, narration, DATE_FORMAT(entry_date,'%d-%m-%Y'), credit, debit, principal_amount, account_balance, created_at from ((select book_id, narration, entry_date, credit, debit, principal_amount, account_balance, created_at from pass_book where account_id = ? and book_id = (select MAX(book_id) from pass_book where account_id = ?)) union (select book_id, narration, entry_date, credit, debit, principal_amount, account_balance, created_at from pass_book where account_id = ? and narration in ('Deposit Amount', 'Withdrawal Amount - Principal', 'Withdrawal Amount - Interest'))) results order by entry_date desc" transactionQuery = "select book_id, narration, entry_date, credit, debit, principal_amount, account_balance, created_at from pass_book where account_id = ? and narration in ('Deposit Amount', 'Withdrawal Amount - Principal', 'Withdrawal Amount - Interest') order by book_id desc" transactionRows, err = tx.Query(transactionQuery, accountId) } var transactionDetails []pdf.Transaction for transactionRows.Next() { var transaction pdf.Transaction var createdAt string err = transactionRows.Scan(&transaction.TransactionType, &transaction.TransactionType, &transaction.Date, &transaction.TransactionAmount, &transaction.TransactionAmountDebit, &transaction.ClosingPrincipalAsOnDate, &transaction.CurrentValueAsOnDate, &createdAt) if err != nil && err != sql.ErrNoRows { log.Error().Err(err).Msg(err.Error()) } //transaction.ClosingPrincipalAsOnDate = investmentAmount transactionDetails = append(transactionDetails, transaction) } connection.Close(tx, db) return transactionDetails } func getThisMonthInterest(accountID int64, payout string, start string, to string) float64 { tx, _, db := connection.Connect() var totalInterest float64 var row *sql.Row if payout == "Monthly" { 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 >= ? and txn_date <= ?", accountID, start, to) } else { row = tx.QueryRow("select IF(amount is null, 0, SUM(amount) - (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 = t.account_id)) as amount from transaction as t where t.txn_type = 'INTEREST_CREDIT' and t.status = 'SUCCESS' and t.account_id = ?", accountID) } err := row.Scan(&totalInterest) if err != nil { return totalInterest } connection.Close(tx, db) return totalInterest } func getTotalDepositAmountByAccountID(accountID int64) 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 from transaction where txn_type in ('DEPOSIT') and status = 'SUCCESS' and account_id = ?", accountID) var totalAmount float64 for rows.Next() { var amount float64 err = rows.Scan(&amount) if err != nil { log.Print(err.Error()) } totalAmount += amount } connection.Close(tx, db) return totalAmount }