package commands import ( aes "cf-capital/module_server/handlers/aes" connection "cf-capital/module_server/handlers/connection" mail "cf-capital/module_server/handlers/email" investmentV1 "cf-capital/module_server/handlers/investment/statement-v1" "cf-capital/module_server/handlers/pdf" slack "cf-capital/module_server/handlers/slack" server_handlers "cf-server/server/handlers" "context" "database/sql" "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 } // type Cron struct { // StartDate string `json:"start_date" default:""` // EndDate string `json:"end_date" default:""` // AccountID string `json:"account_id" default:""` // } type EmailStruct struct { ID int64 `json:"id"` Email string `json:"email"` Name string `json:"name"` Interest string `json:"interest"` EndDate string `json:"enddate"` Balance string `json:"balance"` IFAEmail string `json:"ifa_email"` } 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() { log.Print("Running Daily Cron") dt := getDate() tx, ctx, db := connection.Connect(connection.CfcDb) yesterday := dt.AddDate(0,0,0).Format("2006-01-02") 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) = ? 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 limit 1),'') as ifa_email, COALESCE((select txn_type from transaction where transaction.account_id = ac.account_id and transaction.status = 'INIT' order by created_at desc limit 1), '') as pending_withdrawal 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 <= ?" //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)" // if cron.AccountID != "" { // query = fmt.Sprintf("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) = ? 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, COALESCE((select txn_type from transaction where transaction.account_id = ac.account_id and transaction.status = 'INIT' order by created_at desc limit 1), '') as pending_withdrawal 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 ac.account_id = %s and start_date <= ?", cron.AccountID) // } rows, err := tx.Query(query, yesterday, yesterday) var accounts []Accounts var customers []Customers today := dt.Format("2006-01-02") loc, err := time.LoadLocation("Asia/Kolkata") t := time.Now().In(loc) currentHour := t.Hour() // currentMins := t.Minute() if currentHour >= 15 { today = dt.AddDate(0, 0, 1).Format("2006-01-02") } // if cron.EndDate != "" { // today = cron.EndDate // } log.Print(today) todayTwoAgo := dt.AddDate(0,0,-2).Format("2006-01-02") // if cron.StartDate != "" { // todayTwoAgo = cron.StartDate // } var withdrawalPendingList = map[string]bool{"WITHDRAW" : true, "INTEREST_DEBIT": true} for rows.Next() { var account Accounts var isWithdrawalPending string 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, &isWithdrawalPending) if _, ok := withdrawalPendingList[isWithdrawalPending]; ok { account.InterestCredited = 1 log.Print("Withdrawal entry not verified yet: ", account.AccountID) } if account.InterestCredited == 0 { log.Print("Interest to be credited: ", account.AccountID) 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: ", account.AccountID) } } if len(accounts) > 0 { log.Print("calculating interest of accounts: ", len(accounts)) 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 := 0; 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") > todayTwoAgo && payoutDate.Format("2006-01-02") < today && math.Round(investment.Payout[f].Amount) > 0 { //log.Print(payoutDate.Format("2006-01-02")) //log.Print("Interest calculating Account ID ", accounts[i].AccountID, " ", payoutDate.Format("2006-01-02")) if ok := txns[payoutDate.Format("2006-01-02")]; !ok { log.Print("Calculating Interest of: ", payoutDate.Format("2006-01-02")) 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") log.Print(payoutDatePlusOneString) _, err = investmentV1.CreatePassbook(interestEntry.AccountID, tx, ctx, "Interest Accrued for Date "+investment.Payout[f].Date.Format("2006-01-02"), investment.Payout[f].Date.Format("2006-01-02"), 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 { } } } } 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++ { if len(customers[i].Email) > 0 { 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) + "

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 AbortTransactions() { tx, _, db := connection.Connect(connection.CfcDb) log.Print("Checking time") dt := getDate() date := dt.Format("2006-01-02") datetime := dt.Format("2006-01-02 15:04:05") log.Print("Query started") timelimit := date +" 15:00:00" query := "UPDATE transaction, account SET transaction.status = 'ABORT', transaction.updated_at = ?, account.account_status = CASE WHEN account.account_status = 'PENDING' THEN 'INACTIVE' ELSE account.account_status END where transaction.account_id = account.account_id and transaction.txn_date <= ? and transaction.gateway = 'Razorpay' and transaction.deposit_type = 'ONLINE' and transaction.status = 'PENDING'" log.Print("Query completed") rows, err := tx.Query(query, datetime, timelimit) log.Print("Aborted records") log.Print(rows) if err != nil { connection.Close(tx, db) log.Print(err.Error()) } connection.Close(tx, db) } func SendMaturityMails() { tx, _, db := connection.Connect(connection.CfcDb) log.Print("Connecting to db") var maildatas []EmailStruct // rows, err := tx.Query("SELECT u.id, COALESCE (u.email,''), (concat(COALESCE(u.first_name,''), ' ', COALESCE(u.middle_name,''),' ', COALESCE(u.last_name,''))) as name, COALESCE(s.xirr_rate,''), COALESCE (a.end_date,''),(SELECT COALESCE(pb.account_balance,'') where max(pb.book_id)) from users u left join account a on u.id = a.investor_id left join scheme s on a.scheme_id = s.scheme_id left join pass_book pb on a.account_id = pb.account_id where a.id = 197") rows, err := tx.Query("SELECT u.id, COALESCE (u.email,''), (concat(COALESCE(u.first_name,''), ' ', COALESCE(u.middle_name,''),' ', COALESCE(u.last_name,''))) as name, COALESCE(s.xirr_rate,''), COALESCE (a.end_date,''), COALESCE((SELECT pb2.account_balance from pass_book pb2 where pb2.account_id = a.account_id order by pb2.book_id desc limit 1),''), COALESCE((select email from users where u.referred_by = users.referral_code),'') from users u left join account a on u.id = a.investor_id left join scheme s on a.scheme_id = s.scheme_id where a.account_status = 'ACTIVE' and DATEDIFF(a.end_date,NOW()) = 10") log.Print("Query successful") if err != nil { connection.Close(tx, db) log.Print(err.Error()) log.Print("Data Not found") } for rows.Next() { var maildata EmailStruct err := rows.Scan(&maildata.ID, &maildata.Email, &maildata.Name, &maildata.Interest, &maildata.EndDate, &maildata.Balance, &maildata.IFAEmail) if err != nil { log.Print(err.Error()) log.Print("Can't extract data") } else { log.Print(maildata) maildatas = append(maildatas, maildata) } } for _, maildata := range maildatas { if maildata.Email != "" { var 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
\nDear " + maildata.Name + ",

\nWe hope this email finds you well. We are writing to inform you about the maturity of your investment with Credit Fair Capital. We wanted to give you advance notice so that you could make any necessary arrangements or decisions regarding your investment.

Investment Details:
  • Investment Type:Credit Fair Capital P2P
  • Maturity Date: " + maildata.EndDate + "
  • Current Balance: ₹ " + maildata.Balance + "
  • Interest Rate: " + maildata.Interest + " %
\n

Action Required:

As your investment is approaching its maturity date, we kindly request that you review the options available to you and decide on the course of action you wish to take. Here are the available options:

1. Withdrawal: If you prefer to withdraw your investment upon maturity, please inform us before the maturity date. We will guide you through the process to ensure a smooth withdrawal of your funds.



2. Reinvestment: If you would like to reinvest your funds into another investment option offered by our company.



Please note that if we do not receive any communication from you before the maturity date, we will assume that you wish to renew your investment under the same terms and conditions for the specified duration.

Thank you for entrusting us with your investment. We appreciate your continued partnership, and we look forward to serving you further.\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
" _, err = mail.Email(HtmlBody, "Investment Maturity Notice", maildata.Email, maildata.IFAEmail + ",info@creditfaircapital.in") if err != nil { log.Print("trigger email error") log.Print(err.Error()) } log.Print("Email sent") } } connection.Close(tx, db) } 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(connection.CfcDb) //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(connection.CfcDb) 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) time.Local = 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(connection.CfcDb) startOfMonth := getFirstDateOfMonth(dt) _endOfMonth, 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(count(pass_book.account_balance), pass_book.account_balance, 0) as amount from pass_book where pass_book.account_id = ac.account_id and pass_book.entry_date = ? order by pass_book.book_id 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 := "01-07-2023"//dt.Format("02-01-2006") endOfMonthPlusDay := _endOfMonth.AddDate(0, 0, 1).Format("2006-01-02") log.Print(endOfMonthPlusDay) /*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, endOfMonthPlusDay, 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.InterestSummary.InterestEarnedAsOfAmount = summarySecond.InterestRedeemed + summarySecond.InterestAccrued 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.NetCurrentValue = summarySecond.NetInvestment + summarySecond.InterestAccrued //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 generateInterestStatement(accountId int64, amt float64, acc float64, Capitalised float64) (pdf.InterestStatement, string) { //dt := getDate() tx, _, db := connection.Connect(connection.CfcDb) query := "SELECT ac.account_id, u.id as investorId, IF(u.first_name is null, u.first_name, (concat(COALESCE(u.first_name,''), ' ', COALESCE(u.middle_name,''),' ', COALESCE(u.last_name,'')))) as name, 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, 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,''), sc.withdrawal_policy_type from users as u left join account ac on ac.investor_id = u.id left join scheme sc on sc.scheme_id = ac.scheme_id left join bank_details bd on bd.user_id = u.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 u.lb_user_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.InterestStatement //today := dt.Format("02-01-2006") rows, err := tx.Query(query, accountId) if err != nil { log.Print(err) return statement, err.Error() } for rows.Next() { var schemeType string err = rows.Scan(&statement.AccountId, &statement.InvestorDetails.InvestorID, &statement.InvestorDetails.Name, &statement.InvestorDetails.Bank.FinancialAdvisor, &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, &schemeType) if err != nil && err != sql.ErrNoRows { log.Print("Query Error") log.Error().Err(err).Msg(err.Error()) } statement.InvestorDetails.Phone = aes.DecryptData(statement.InvestorDetails.Phone) statement.InvestorDetails.Email = aes.DecryptData(statement.InvestorDetails.Email) sYear, eYear, _ := getFinancialYearDetails() statement.StatementDate = eYear.Format("02-01-2006") statement.StatementPeriod.From = sYear.Format("02-01-2006") statement.StatementPeriod.To = eYear.Format("02-01-2006") statement.InvestorDetails.Bank.ModeOfPayout = "Electronic" statement.InvestorDetails.Bank.ModeOfHolding = "Single" paidOut := amt //math.Round(getInterestPaidOut(statement.InvestorDetails.InvestorID, sYear.Format("2006-01-02 15:04:05"), eYear.AddDate(0,0,1).Format("2006-01-02"))) accrued := acc /*if schemeType != "M"{ accrued = getInterestAccrued(statement.InvestorDetails.InvestorID, sYear.Format("2006-01-02 15:04:05"), eYear.Format("2006-01-02")) log.Print(accrued) accrued = accrued - paidOut }*/ statement.InterestDetails.OpeningInterest = 0 statement.InterestDetails.PaidOutInterest = paidOut statement.InterestDetails.ClosingInterest = accrued statement.InterestDetails.CapitalisedInterest = Capitalised statement.InterestDetails.TotalInterestEarned = accrued+paidOut + Capitalised } var base64 string base64, err = pdf.InterestStatementPDF(statement) if err != nil { log.Print("OK") } connection.Close(tx, db) return statement, base64 } func getInterestAccrued(InvestorId string, fromDate string, toDate string) float64 { tx, _, db := connection.Connect(connection.CfcDb) log.Print(fromDate) row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction left join account ac on transaction.account_id = ac.account_id where txn_type IN ('INTEREST_CREDIT') and status = 'SUCCESS' and ac.investor_id = ? and transaction.txn_date >= ? and transaction.txn_date <= ?", InvestorId, fromDate, toDate+" 23:59:59") var totalInterestAccrued float64 err := row.Scan(&totalInterestAccrued) if err != nil { log.Print(err.Error()) } connection.Close(tx, db) return totalInterestAccrued } func getInterestPaidOut(InvestorId string, fromDate string, toDate string) float64 { tx, _, db := connection.Connect(connection.CfcDb) row := tx.QueryRow("select IF(amount is null, 0, SUM(amount)) as amount from transaction left join account ac on transaction.account_id = ac.account_id where txn_type in ('INTEREST_DEBIT', 'INTEREST_DEBIT_MONTHLY') and status = 'SUCCESS' and ac.investor_id = ? and transaction.txn_date >= ? and transaction.txn_date <= ?", InvestorId, fromDate, toDate+" 23:59:59") var totalInterestPaidOut float64 err := row.Scan(&totalInterestPaidOut) if err != nil { log.Print(err.Error()) } connection.Close(tx, db) return totalInterestPaidOut } func getTransactions(accountId int64, WithdrawalPolicyType string, investmentAmount float64) []pdf.Transaction { tx, _, db := connection.Connect(connection.CfcDb) 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(connection.CfcDb) var totalInterest float64 log.Print(start) log.Print(to) 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 and transaction.txn_date <= ?)) as amount from transaction as t where t.txn_type = 'INTEREST_CREDIT' and t.status = 'SUCCESS' and t.account_id = ? and t.txn_date <= ?", to, accountID, to) } err := row.Scan(&totalInterest) if err != nil { return totalInterest } connection.Close(tx, db) return totalInterest } func getTotalDepositAmountByAccountID(accountID int64) float64 { tx, _, db := connection.Connect(connection.CfcDb) //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) (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, lastOfMonth.Format("2006-01-02") } func getFinancialYearDetails() (time.Time, time.Time, int64) { loc, _ := time.LoadLocation("Asia/Kolkata") dt := time.Now().In(loc) currentYearStart := dt.AddDate(-1, 0, 0).Year() firstOfMonth := time.Date(currentYearStart, 4, 1, 0, 0, 0, 0, loc) lastOfMonth := time.Date(dt.Year(), 3, 1, 0, 0, 0, 0, loc) lastOfMonth = lastOfMonth.AddDate(0, 1, -1) diff := lastOfMonth.Sub(firstOfMonth) return firstOfMonth, lastOfMonth, int64(diff.Hours() / 24) } func generateMonthlyStatement() bool { tx, _, db := connection.Connect(connection.CfcDb) 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 }