package commands import ( connection "cf-capital/module_server/handlers/connection" investmentV1 "cf-capital/module_server/handlers/investment/statement-v1" slack "cf-capital/module_server/handlers/slack" server_handlers "cf-server/server/handlers" mail "cf-capital/module_server/handlers/email" "cf-capital/module_server/handlers/pdf" aes "cf-capital/module_server/handlers/aes" "database/sql" "context" "encoding/json" "fmt" log "github.com/rs/zerolog/log" "math" "net/http" "os" "reflect" "time" ) type responseBody struct { Status string `json:"status"` Message string `json:"message"` } type Accounts struct { SchemeID int64 WithdrawalPolicyType string AccountID int64 XIRR_RATE float64 SchemeType string InvestorID int64 InitialAmount float64 EndDate string StartDate string InterestCredited float64 Investor Investor } type Investor struct { Email string IFAEmail string Mobile string } type InterestEntry struct { AccountID int64 Type string Credit float64 Debit float64 } type Customers struct { Name string Email string IFAEmail string Mobile string Scheme string InvestorID int64 AccountID int64 XIRR_RATE float64 InvestmentType string ReferenceCode string InterestAmount float64 Amount float64 } func DailyCron() http.HandlerFunc { return func(w http.ResponseWriter, req *http.Request) { w.Header().Set("Content-Type", "application/json") dt := getDate() params := map[string]interface{}{ "Service": "CFC Cron Service - 0.0.1", "Status": "Running", "Date": dt.Format("2006-01-02"), } slack.SendSlack("CFC Cron Start", params) //Cron Service - Start //Daily() //End params = map[string]interface{}{ "Service": "CFC Cron Service - 0.0.1", "Status": "Closed", "Date": dt.Format("2006-01-02"), } slack.SendSlack("CFC Cron End", params) server_handlers.FormatResponse(w, 200) _ = json.NewEncoder(w).Encode(responseBody{Status: "OK", Message: "Daily cron - successfully executed"}) } } func Daily() { dt := getDate() tx, ctx, db := connection.Connect() query := "select ac.scheme_id, ac.account_id, s.scheme_type, s.xirr_rate, s.withdrawal_policy_type, 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(txn_date) = DATE(NOW() - INTERVAL 1 DAY) and ac.account_id = transaction.account_id limit 1) is null, 0, 1) as interest_credited, COALESCE(u.email, ''), COALESCE(u.mobile_number, ''), COALESCE((select email from users where u.referred_by = users.referral_code),'') as ifa_email from account ac left join scheme s on ac.scheme_id = s.scheme_id left join users u on u.id = ac.investor_id where account_status = 'ACTIVE' and start_date <= DATE(NOW() - INTERVAL 1 DAY)" //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)" rows, err := tx.Query(query) var accounts []Accounts var customers []Customers today := dt.Format("2006-01-02") for rows.Next() { var account Accounts err = rows.Scan(&account.SchemeID, &account.AccountID, &account.SchemeType, &account.XIRR_RATE, &account.WithdrawalPolicyType, &account.InvestorID, &account.InitialAmount, &account.StartDate, &account.EndDate, &account.InterestCredited, &account.Investor.Email, &account.Investor.Mobile, &account.Investor.IFAEmail) if account.InterestCredited == 0 { if err != nil { log.Print(err.Error()) } if len(account.EndDate) == 0 || account.EndDate == "0000-00-00" { account.EndDate = today } //account.InitialAmount = GetClosingBalance(account.AccountID) accounts = append(accounts, account) //log.Print(account.InitialAmount) //log.Print("AccountID:", account.AccountID) //log.Print("start:", account.StartDate) //log.Print("end:", account.EndDate) } else { log.Print("Interest already credited") } } if len(accounts) > 0 { log.Print("calculating interest") var investment investmentV1.FlexiPlan var interestEntries []InterestEntry for i := 0; i < len(accounts); i++ { var investmentStruct investmentV1.FlexiPlan var invest investmentV1.Invest var interestEntry InterestEntry var customer Customers investmentStruct.AccountId = accounts[i].AccountID investmentStruct.Principal = accounts[i].InitialAmount investmentStruct.MonthlyROI = accounts[i].XIRR_RATE investmentStruct.StartDate = accounts[i].StartDate investmentStruct.EndDate = accounts[i].EndDate investmentStruct.WithdrawalPolicyType = accounts[i].WithdrawalPolicyType investmentStruct.ForIndividualInvestment = accounts[i].EndDate investment, _ = investmentStruct.DailyCron() txns := getTransactionsByAccountID(tx, accounts[i].AccountID) /*log.Print(txns) dataJson, _ := json.Marshal(investment) log.Print(string(dataJson)) return*/ //----- ----- ----- ----- Multiple Interest Credit Entry START ----- ----- ----- ----- if len(investment.Payout) > 0 { //log.Print(len(investment.Payout)) payoutDateMap := make(map[string]bool) for f := 1; f < len(investment.Payout); f++ { if investment.Payout[f].Amount > 0 { payoutDate := investment.Payout[f].Date if ok := payoutDateMap[payoutDate.Format("2006-01-02")]; !ok { payoutDateMap[payoutDate.Format("2006-01-02")] = true //if payoutDate.Format("2006-01-02") < today && isInterestCredited(tx, accounts[i].AccountID, payoutDate) == 0{ if payoutDate.Format("2006-01-02") < today && math.Round(investment.Payout[f].Amount) > 0 { log.Print("Interest calculating Account ID ", accounts[i].AccountID, " ", payoutDate.Format("2006-01-02")) if ok := txns[payoutDate.Format("2006-01-02")]; !ok { interestEntry.AccountID = accounts[i].AccountID interestEntry.Type = "Deposit" interestEntry.Credit = investment.Payout[f].Amount interestEntries = append(interestEntries, interestEntry) log.Print("creating CreatePassbook") var payoutDatePlusOne = investment.Payout[f].Date payoutDatePlusOneString := payoutDatePlusOne.AddDate(0, 0, 1).Format("2006-01-02") _, err = investmentV1.CreatePassbook(interestEntry.AccountID, tx, ctx, "Interest Accrued for Date "+investment.Payout[f].Date.Format("2006-01-02"), payoutDatePlusOneString, interestEntry.Credit, 0, investment.Payout[f].PrincipalAmount) if err != nil { tx.Rollback() log.Print(err.Error()) } invest.AccountID = accounts[i].AccountID invest.Amount = interestEntry.Credit invest.PaymentMethod = 1 invest.PaymentGateway = 1 invest.MHP = accounts[i].SchemeID invest.TxnDate = investment.Payout[f].Date.Format("2006-01-02") invest.ReferenceCode = investmentV1.GetRequestID() log.Print("creating CreateTransaction") _, err = investmentV1.CreateTransaction(invest, tx, ctx, "INTEREST_CREDIT", "SUCCESS") if err != nil { log.Print(err.Error()) tx.Rollback() } if investment.Payout[f].MonthlyInterest.Amount > 0 { customer.InvestorID = accounts[i].InvestorID customer.AccountID = accounts[i].AccountID customer.Email = aes.DecryptData(accounts[i].Investor.Email) customer.Mobile = aes.DecryptData(accounts[i].Investor.Mobile) customer.Amount = investment.Payout[f].MonthlyInterest.Balance customer.XIRR_RATE = accounts[i].XIRR_RATE customer.InterestAmount = investment.Payout[f].MonthlyInterest.Amount customer.ReferenceCode = invest.ReferenceCode customer.InvestmentType = accounts[i].SchemeType customer.IFAEmail = aes.DecryptData(accounts[i].Investor.IFAEmail) customers = append(customers, customer) //createMonthlyPayout(tx, ctx, investment.Payout[f].MonthlyInterest.Statement) }else{ log.Print("test") } } else { //log.Print("Interest already calculated ", investment.Payout[f].Date, " Account Id", accounts[i].AccountID) } } else { //log.Print("Beyond date ", investment.Payout[f].Date, " Account Id", accounts[i].AccountID) } } } } } else { log.Print("No Interest accrued: Account ID ", accounts[i].AccountID) } //----- ----- ----- ----- Multiple Interest Credit Entry END ----- ----- ----- ----- //----- ----- ----- ----- Single Interest Credit Entry START ----- ----- ----- ----- /*if investment.IndividualInvestment.TotalInterest > 0 { interestEntry.AccountID = accounts[i].AccountID interestEntry.Type = "Deposit" interestEntry.Credit = investment.IndividualInvestment.TotalInterest interestEntries = append(interestEntries, interestEntry) log.Print("creating CreatePassbook") _, err = investmentV1.CreatePassbook(interestEntry.AccountID, tx, ctx, "Interest Accrued for Date "+today, interestEntry.Credit, 0) if err != nil { tx.Rollback() log.Print(err.Error()) } invest.AccountID = accounts[i].AccountID invest.Amount = interestEntry.Credit invest.PaymentMethod = 1 invest.PaymentGateway = 1 invest.MHP = accounts[i].SchemeID invest.ReferenceCode = investmentV1.GetRequestID() log.Print("creating CreateTransaction") _, err = investmentV1.CreateTransaction(invest, tx, ctx, "INTEREST_CREDIT") if err != nil { log.Print(err.Error()) tx.Rollback() } } else { log.Print("No Interest accrued: Account ID", accounts[i].AccountID) }*/ //----- ----- ----- ----- Single Interest Credit Entry END ----- ----- ----- ----- } } else { log.Print("No entries are found") } if len(customers) > 0 { //sendMonthlyPayoutConfirmation(customers) //sendMonthEndCommunication(customers) } connection.Close(tx, db) } func createMonthlyPayout(tx *sql.Tx, ctx context.Context, statement investmentV1.Statements){ log.Print(statement.PassbookInsert) res, err := tx.ExecContext(ctx, statement.PassbookInsert) if err != nil { log.Print(err.Error()) } transactionId, errCheck := res.LastInsertId() if errCheck != nil { log.Print("Monthly Passbook not created") }else{ log.Print("Monthly Passbook created ", transactionId) } res, err = tx.ExecContext(ctx, statement.TransactionInsert) if err != nil { log.Print(err.Error()) } transactionId, errCheck = res.LastInsertId() if errCheck != nil { log.Print("Monthly Passbook not created") }else{ log.Print("Monthly Passbook created ", transactionId) } } /*func MonthlyInterestCredit(date string){ rows, err := tx.Query("select date(txn_date) from transaction where status = 'SUCCESS' and txn_type = 'INTEREST_CREDIT' and account_id = ?", accountId) transactions := make(map[string]bool) for rows.Next() {} _, err = investmentV1.CreatePassbook(interestEntry.AccountID, tx, ctx, "Monthly Interest Payout for October-2022"+investment.Payout[f].Date.Format("2006-01-02"), payoutDatePlusOneString, 0, debit, investment.Payout[f].PrincipalAmount) _, err = investmentV1.CreateTransaction(invest, tx, ctx, "INTEREST_DEBIT_MONTHLY", "SUCCESS") }*/ func sendMonthlyPayoutConfirmation(customers []Customers) { //currentTime := time.Now() //dateTime := currentTime.Format("Mon 2006-01-2 15:04:05") for i := 0; i < len(customers); i++ { 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 " + customers[i].Name + ",

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

\nYour Monthly Interest pay-out is credited to your account.


Investment
Amount :- " + fmt.Sprintf("%.2f", customers[i].Amount) + "
Investment type:- " + customers[i].InvestmentType + "
Returns:- " + fmt.Sprintf("%.2f", customers[i].XIRR_RATE) + "
Interest Amount : " + fmt.Sprintf("%.2f", customers[i].InterestAmount) + "
Reference number: " + customers[i].ReferenceCode + "

Please 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.

Once again, Thank you for trusting with 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
" _, _ = mail.Email(HtmlBody, "Monthly Payout Confirmation", customers[i].Email, customers[i].IFAEmail + ",info@creditfaircapital.in") //investmentV1.SendAttachment(customers[i].AccountID, customers[i].InvestorID, HtmlBody, "Monthly Payout Confirmation", dateTime, customers[i].IFAEmail, 1) } } func isInterestCredited(tx *sql.Tx, accountId int64, date time.Time) int64 { //dt,_ := time.Parse("2006-01-02 15:04:05", date) row := tx.QueryRow("select txn_id from transaction where status = 'SUCCESS' and txn_type = 'INTEREST_CREDIT' and account_id = ? and date(txn_date) = ?", accountId, date.Format("2006-01-02")) var txn_id int64 err := row.Scan(&txn_id) if err != nil && err != sql.ErrNoRows { log.Print("isInterestCredited, error exeception") log.Print(err.Error()) return 1 } if txn_id > 0 { return 1 } return 0 } func getTransactionsByAccountID(tx *sql.Tx, accountId int64) map[string]bool { //dt,_ := time.Parse("2006-01-02 15:04:05", date) rows, err := tx.Query("select date(txn_date) from transaction where status = 'SUCCESS' and txn_type = 'INTEREST_CREDIT' and account_id = ?", accountId) transactions := make(map[string]bool) for rows.Next() { var txnDate string err = rows.Scan(&txnDate) if err != nil && err != sql.ErrNoRows { log.Print("getTransactionsByAccountID, no entry") log.Print(err.Error()) } else { transactions[txnDate] = true } } return transactions } func GetClosingBalance(accountId int64) float64 { tx, _, db := connection.Connect() //Interest Paid Out (Debit) row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type in ('INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY', 'WITHDRAW','WITHDRAW_MONTHLY') and status = 'SUCCESS' and account_id = ?", accountId) var interestPaidOut float64 var interestGained float64 err := row.Scan(&interestPaidOut) if err != nil { log.Print("GetClosingBalance: interestPaidOut") log.Print(err.Error()) return 0 } //End //Interest Gained (Credit) row = tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction where txn_type in ('INTEREST_CREDIT', 'DEPOSIT') and status = 'SUCCESS' and account_id = ?", accountId) err = row.Scan(&interestGained) if err != nil { log.Print("GetClosingBalance: interestGained") log.Print(err.Error()) return 0 } //End connection.Close(tx, db) return interestGained - interestPaidOut } func InterestCalculationByDays() { log.Print(os.Args[1:]) dt := getDate() format := "2006-01-02" tx, ctx, db := connection.Connect() query := "select ac.scheme_id, account_id, s.xirr_rate, investor_id, initial_amount, start_date, COALESCE(end_date, '') as end_date from account ac left join scheme s on ac.scheme_id = s.scheme_id where account_status = 'ACTIVE' and s.scheme_type = 'FLEXI' and s.tenure_type = 'D' and end_date is null" rows, err := tx.Query(query) var accounts []Accounts today := dt.Format(format) for rows.Next() { var account Accounts err = rows.Scan(&account.SchemeID, &account.AccountID, &account.XIRR_RATE, &account.InvestorID, &account.InitialAmount, &account.StartDate, &account.EndDate) if err != nil { log.Print(err.Error()) } if len(account.EndDate) == 0 { account.EndDate = today } accounts = append(accounts, account) } log.Print("calculating interest") if len(accounts) > 0 { var interestEntries []InterestEntry for i := 0; i < len(accounts); i++ { var investmentStruct investmentV1.FlexiPlan var invest investmentV1.Invest var interestEntry InterestEntry investmentStruct.Principal = accounts[i].InitialAmount investmentStruct.MonthlyROI = accounts[i].XIRR_RATE investmentStruct.StartDate = accounts[i].StartDate investmentStruct.EndDate = accounts[i].EndDate end, _ := time.Parse(format, accounts[i].EndDate) start, _ := time.Parse(format, accounts[i].StartDate) diff := end.Sub(start) days := int64(diff.Hours() / 24) if days > 1 { //get only one day's calculation days = 1 } log.Print("Days:", days) log.Print(investmentStruct) //data, _ := investmentStruct.FlexiV1() investment, _ := investmentStruct.InterestCalculationByDays(days) log.Print(investment.TotalInterestEarned) if investment.IndividualInvestment.TotalInterest > 0 { interestEntry.AccountID = accounts[i].AccountID interestEntry.Type = "Deposit" interestEntry.Credit = investment.TotalInterestEarned interestEntries = append(interestEntries, interestEntry) _, err = investmentV1.CreatePassbook(interestEntry.AccountID, tx, ctx, interestEntry.Type, today, interestEntry.Credit, 0, 0) if err != nil { tx.Rollback() log.Print(err.Error()) } invest.AccountID = accounts[i].AccountID invest.Amount = interestEntry.Credit invest.PaymentMethod = 1 invest.PaymentGateway = 1 invest.MHP = accounts[i].SchemeID invest.ReferenceCode = investmentV1.GetRequestID() log.Print("creating CreateTransaction") _, err = investmentV1.CreateTransaction(invest, tx, ctx, "INTEREST_CREDIT", "SUCCESS") if err != nil { log.Print(err.Error()) tx.Rollback() } } else { log.Print("No Interest accrued: Account ID", accounts[i].AccountID) } } } else { log.Print("No entries are found") } connection.Close(tx, db) } func getDate() time.Time { loc, _ := time.LoadLocation("Asia/Kolkata") dt := time.Now().In(loc) return dt } func getLastDayOfMonth() int { now := time.Now() currentYear, currentMonth, _ := now.Date() currentLocation := now.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 isMonthEnd() int { now := time.Now() if now.Day() == getLastDayOfMonth() { return 1 } return 0 } func isMonthStart() int { now := time.Now() if now.Day() == getFirstDayOfMonth() { return 1 } return 0 } func sendMonthEndCommunication(customers []Customers) { currentTime := time.Now() dateTime := currentTime.Format("Mon 2006-01-2 15:04:05") HtmlBody := "
" + "
" + "" + "" + "" + "
" + "
" + "CreditFair Capital" + "		 ·New Registration
" + "" + "" + "" + "
" + "
Monthly Withdrawal
" + "
" + dateTime + "
" + "" + "" + "" + "
" + "
Registration Summary ·
" + "" + "" + "" + "" + "" + "" + "
EmailTest
" + "
" //storage.Save(customers) for i := 0; i < len(customers); i++ { investmentV1.SendAttachment(customers[i].AccountID, customers[i].InvestorID, HtmlBody, fmt.Sprint("System - Monthly Withdrawal ", dateTime), dateTime, "", 1) } } func (d Customers) String() string { v := reflect.ValueOf(d) var csvString string for i := 0; i < v.NumField(); i++ { csvString = fmt.Sprintf("%v%v,", csvString, v.Field(i).Interface()) } return csvString } func MonthlyStatementCron() http.HandlerFunc { return func(w http.ResponseWriter, req *http.Request) { w.Header().Set("Content-Type", "application/json") MonthlyStatement(0) server_handlers.FormatResponse(w, 200) _ = json.NewEncoder(w).Encode(responseBody{Status: "OK", Message: "Daily cron - successfully executed"}) } } func MonthlyStatement(accountId int64) (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) query := "SELECT ac.account_id, DATE_FORMAT(ac.start_date,'%d-%m-%Y'), IF(u.first_name is null, u.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" //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 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 } 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 generateMonthlyStatement() bool { tx, _, db := connection.Connect() query := "select ac.account_id from account ac left join scheme s on ac.scheme_id = s.scheme_id where account_status = 'ACTIVE' and start_date <= DATE(NOW() - INTERVAL 1 DAY)" rows, err := tx.Query(query) if err != nil { log.Print(err.Error()) return false } for rows.Next() { var accountId int64 err = rows.Scan(&accountId) MonthlyStatement(accountId) } connection.Close(tx, db) return true }