ANR | CIKM 2018 paper titled ANR : Aspect-based Neural Recommender | Recommender System library

import { hash } from "bcryptjs";
import { Request, Response } from "express";
import "reflect-metadata";
import { createConnection, getConnection, getManager } from "typeorm";
import * as uuid from "uuid";
import { Collection } from "../entity/Collection";
import { Loan } from "../entity/Loan";
import { Loanee } from "../entity/Loanee";
import { Plan } from "../entity/Plan";
import { Role } from "../entity/Role";
import { Route } from "../entity/Route";
import { TranscationType } from "../entity/TranscationType";
import { User } from "../entity/User";
import { Village } from "../entity/Village";
import { RoleType } from "../types/RoleType";
import { TranscationInterface } from "../types/TranscationInterface";
import collection from "./new-collection.json";
import loanee from "./new-loanee.json";
import onlyCollectores from "./onlyCollectors.json";
import villageData from "./tblCities.json";
import RouteCityData from "./tblRouteCities.json";
import routeData from "./tblRoutes.json";

export const migrate = async (req: Request, res: Response) => {
  const from = req.query.from || 0;
  const to = req.query.to || 100000000000000;

  res.status(200).json({
    success: true,
    from,
    to,
  });

  await addRole();
  await addVillages();
  await addRoute();
  await addTranscationType();
  await addPlan();
  await addAdmin();
  await addCollectors();
  await addRouteVillage();
  await addNewLoaneeCollectionData(from, to);
  await callStoredProcedure();
};

export const migrateOnlyLoaneAndCollections = async (
  req: Request,
  res: Response
) => {
  const from = parseInt(req.query.from) || 0;
  const to = parseInt(req.query.to) || 100000000000000;

  res.status(200).json({
    success: true,
    from,
    to,
  });

  await addNewLoaneeCollectionData(from, to);
  await callStoredProcedure();
};

export const deleteLoaneeRecords = async (req: Request, res: Response) => {
  const from = parseInt(req.query.from) || 0;
  const to = parseInt(req.query.to) || 100000000000000;

  res.status(200).json({
    success: true,
    from,
    to,
  });

  for (let i = from; i < to; i++) {
    await Loan.delete({ remark: `${i}_OLD_DATA` });
    await Loanee.delete({ remark: `${i}_OLD_DATA` });
  }
};

const addRoute = async () => {
  //Dumping Routes Data to Server DB
  for (let i = 0; i < routeData.length; i++) {
    const item = routeData[i];

    await Route.create({
      name: item.RouteName,
      timestamp: new Date(),
    }).save();
  }
};

const addVillages = async () => {
  // Dumping Villages Data to Server DB
  for (let i = 0; i < villageData.length; i++) {
    const item = villageData[i];

    await Village.create({
      name: item.CityName,
      timestamp: new Date(),
    }).save();
  }
};

const addRole = async () => {
  await Role.create({
    name: RoleType.admin,
    timestamp: new Date(),
  }).save();
  await Role.create({
    name: RoleType.collector,
    timestamp: new Date(),
  }).save();
};

const addAdmin = async () => {
  const AdminRole = await Role.findOne({ where: { name: RoleType.admin } });
  const user = new User();
  user.role = AdminRole!;
  user.userName = "admin";
  user.fullName = "admin";
  user.password = await hash("admin@123", 12);
  user.balance = 1000000000;
  user.timestamp = new Date();
  await user.save();

  const collectorRole = await Role.findOne({
    where: { name: RoleType.collector },
  });

  const defaultUser = new User();
  defaultUser.role = collectorRole!;
  defaultUser.userName = "SYSTEM_GENERATED_COLLECTOR";
  defaultUser.fullName = "SYSTEM_GENERATED_COLLECTOR";
  defaultUser.password = await hash("1234567890", 12);
  defaultUser.balance = 1000000000;
  defaultUser.timestamp = new Date();
  await defaultUser.save();

  const defaultRoute = new Route();
  defaultRoute.name = "SYSTEM_GENERATED_ROUTE";
  defaultRoute.timestamp = new Date();
  await defaultRoute.save();

  const defaultVillage = new Village();
  defaultVillage.name = "SYSTEM_GENERATED_VILLAGE";
  defaultVillage.timestamp = new Date();
  await defaultVillage.save();
};

const addCollectors = async () => {
  const CollectorRole = await Role.findOne({
    where: { name: RoleType.collector },
  });

  if (!CollectorRole) {
    return;
  }
  const password = await hash("123456", 12);
  for (let i = 0; i < onlyCollectores.length; i++) {
    const collector = onlyCollectores[i];
    const newCollector = new User();
    newCollector.role = CollectorRole;
    newCollector.userName = collector.Username.split(" ").join("_");
    newCollector.fullName = collector.FullName;
    newCollector.password = password;
    newCollector.timestamp = new Date();
    newCollector.last_updated = new Date();
    await newCollector.save();
  }
};

const addTranscationType = async () => {
  const t1 = new TranscationType();
  t1.name = TranscationInterface.cash_collection;
  t1.timestamp = new Date();
  await t1.save();

  const t2 = new TranscationType();
  t2.name = TranscationInterface.cash_collector;
  t2.timestamp = new Date();
  await t2.save();

  const t3 = new TranscationType();
  t3.name = TranscationInterface.cash_loan;
  t3.timestamp = new Date();
  await t3.save();

  const t4 = new TranscationType();
  t4.name = TranscationInterface.cash_office;
  t4.timestamp = new Date();
  await t4.save();

  const t6 = new TranscationType();
  t6.name = TranscationInterface.penalty_collected;
  t6.timestamp = new Date();
  await t6.save();
};

const addPlan = async () => {
  const p1 = new Plan();
  p1.name = "Daily";
  p1.duration = 1;
  p1.timestamp = new Date();
  await p1.save();

  const p2 = new Plan();
  p2.name = "Weekly";
  p2.duration = 7;
  p2.timestamp = new Date();
  await p2.save();

  const p3 = new Plan();
  p3.name = "Monthly";
  p3.duration = 30;
  p3.timestamp = new Date();
  await p3.save();

  const p4 = new Plan();
  p4.name = "Fortnight";
  p4.duration = 15;
  p4.timestamp = new Date();
  await p4.save();
};

const addRouteVillage = async () => {
  const AllVillage = await Village.find();
  const AllRoute = await Route.find();

  for (let i = 0; i < RouteCityData.length; i++) {
    const item = RouteCityData[i];

    const village = AllVillage.find((item2) => item2.id === item.CityId);
    const route = AllRoute.find((item2) => item2.id === item.RouteId);

    try {
      if (village && route) {
        await getConnection()
          .createQueryBuilder()
          .relation(Route, "villages")
          .of(route)
          .add(village);
      }
    } catch (error) {
      console.log(error.message);
    }
  }
};

const addNewLoaneeCollectionData = async (from, to) => {
  const allData: any = [];
  const loaneeData = loanee as any[];
  const collectionData = collection as any[];

  const defaultUser = await User.findOne({
    where: { userName: "SYSTEM_GENERATED_COLLECTOR" },
  });
  const defaultRoute = await Route.findOne({
    where: { name: "SYSTEM_GENERATED_ROUTE" },
  });

  const defaultVillage = await Village.findOne({
    where: { name: "SYSTEM_GENERATED_VILLAGE" },
  });

  const WeeklyPlan = await Plan.findOne({
    where: { name: "Weekly" },
  });
  const DailyPlan = await Plan.findOne({
    where: { name: "Daily" },
  });
  const MonthlyPlan = await Plan.findOne({
    where: { name: "Monthly" },
  });
  const FortnightPlan = await Plan.findOne({
    where: { name: "Fortnight" },
  });

  // const transcationTypeLoan = await TranscationType.findOne({
  //   where: { name: TranscationInterface.cash_loan },
  // });

  // const transcationTypeCollection = await TranscationType.findOne({
  //   where: { name: TranscationInterface.cash_collection },
  // });
  console.log(collectionData.length);

  loaneeData.forEach((item: any, index) => {
    if (index >= from && index <= to) {
      const block = {
        ...item,
        dataIndex: index,
      };
      block.collection = [];

      collectionData.forEach((item2: any) => {
        if (
          item.SerialNo === item2.SerialNo &&
          item.RouteName === item2.RouteName
        ) {
          block.collection.push(item2);
        }
      });

      allData.push(block);
    }
  });

  var count = 0;
  allData.forEach((item: any) => {
    count += item.collection.length;
  });
  console.log(allData.length);
  console.log("Total Count : " + count);

  const AllVillage = await Village.find();
  const AllCollector = await User.find();
  const AllRoute = await Route.find();

  for (let i = 0; i < allData.length; i++) {
    const item = allData[i];

    let roundedNum = 0;
    if (item.LoanAmount === 0 || item.InstallmentAmount === 0) {
      roundedNum = 0;
    } else {
      roundedNum = Number(
        (Number(item.LoanAmount) / Number(item.InstallmentAmount)).toFixed()
      );
    }
    const village = AllVillage.find(
      (item2) => item2.name.toLowerCase() === item.LoneeVillage.toLowerCase()
    );

    const routeColl = AllRoute.find(
      (route2) => route2.name.toLowerCase() === item.RouteName.toLowerCase()
    );

    let plan = null;

    if (item.PaymentMode.includes("Weekly")) {
      plan = WeeklyPlan;
    } else if (item.PaymentMode.includes("Daily")) {
      plan = DailyPlan;
    } else if (item.PaymentMode.includes("Monthly")) {
      plan = MonthlyPlan;
    } else if (item.PaymentMode.includes("Fortnight")) {
      plan = FortnightPlan;
    } else {
      plan = WeeklyPlan;
    }

    const loanee = new Loanee();

    loanee.id = uuid.v4();
    loanee.user = defaultUser!;
    loanee.village = village ? village : defaultVillage!;
    loanee.route = routeColl ? routeColl : defaultRoute!;
    loanee.fullName = `${item.LoneeName}_${item.dataIndex}`;
    loanee.address = item.LoneeAddress;
    loanee.guarantorName = item.GurantorName;
    loanee.guarantorVillage = item.GurantorVillage;
    loanee.guarantorAddress = item.GurantorAddress;
    loanee.loaneeNumber = `${item.SerialNo}`;
    loanee.remark = `${item.dataIndex}_OLD_DATA`;
    loanee.timestamp = new Date(item.FromDate);

    await loanee.save();

    const loan = new Loan();
    loan.id = uuid.v4();
    loan.loanee = loanee;
    loan.user = defaultUser!;
    loan.plan = plan!;
    loan.village = village ? village : defaultVillage!;
    loan.route = routeColl ? routeColl : defaultRoute!;
    loan.loanNumber = `${item.SerialNo}`;
    loan.createdAt = new Date(item.FromDate);
    loan.startAt = new Date(item.FromDate);
    loan.endAt = new Date(item.ToDate);
    loan.amount = item.LoanAmount.toFixed();
    loan.dueAmount = item.BalanceAmount.toFixed();
    loan.recoveryMoney = item.LoanAmount.toFixed();
    loan.installment = item.InstallmentAmount.toFixed();
    loan.numberOfInstallments = roundedNum;
    loan.remark = `${item.dataIndex}_OLD_DATA`;
    loan.timestamp = new Date(item.FromDate);

    await loan.save();

    const collArr = [];
    for (let j = 0; j < item.collection.length; j++) {
      const item2 = item.collection[j];

      const userCollector = AllCollector.find(
        (user1) =>
          user1.userName.toLowerCase() === item2.Collector.toLowerCase()
      );

      collArr.push({
        id: uuid.v4(),
        loan: loan,
        user: userCollector ? userCollector : defaultUser!,
        route: routeColl ? routeColl : defaultRoute!,
        amount: item2.Amount.toFixed(),
        dueAmount: 0,
        remark: `${item.dataIndex}_OLD_DATA`,
        timestamp: new Date(item2.Date),
      });
    }

    await getConnection()
      .createQueryBuilder()
      .insert()
      .into(Collection)
      .values(collArr)
      .execute();
  }
};

const callStoredProcedure = async () => {
  await getManager().query(`
    UPDATE loan l

      INNER JOIN (
        SELECT loanId, SUM(amount) as total
        FROM collection
        GROUP BY loanId
      ) c ON l.id = c.loanId

    SET l.dueAmount = (l.recoveryMoney - c.total); 
  `);

  await getManager().query(`
      UPDATE loan SET isActive = false  WHERE dueAmount <= 0;
  `);

  await getManager().query(`
    UPDATE route r

      INNER JOIN (
        SELECT  MAX(loaneeNumber) as maxNumber, routeId
        FROM loanee
        GROUP BY  routeId
      ) l ON l.routeId = r.id
    
      SET r.loaneeCount  = l.maxNumber;
  `);

  await getManager().query(`
    UPDATE route r

      INNER JOIN (
        SELECT  MAX(loanNumber) as maxNumber, routeId
        FROM loan
        GROUP BY  routeId
      ) l ON l.routeId = r.id

    SET r.loanCount  = l.maxNumber;
  `);
};