/* ============================================================================ *\
|| ########################################################################## ||
|| # Auction Software Marketplace Release: 0.6 Build 0.7 # ||
|| # ---------------------------------------------------------------------- # ||
|| # License # 35YAHCNR9344X6O666C123AB # ||
|| # ---------------------------------------------------------------------- # ||
|| # Copyright ©2014–2021 Develop Scripts LLC. All Rights Reserved # ||
|| # This file may not be redistributed in whole or significant part. # ||
|| # ------------- AUCTION SOFTWARE IS NOT FREE SOFTWARE ------------------ # ||
|| # http://www.auctionsoftwaremarketplace.com|support@auctionsoftware.com # ||
|| # ---------------------------------------------------------------------- # ||
|| ########################################################################## ||
\* ============================================================================ */
/* eslint-disable consistent-return */
const dateFormat = require('dateformat')
const md5 = require('md5')
const _ = require('underscore')
const moment = require('moment')
const mysqclass = require('./mysqli').default
const conditionChange = (req, fieldName) => {
let condition = ''
if (req.body.search === 'within') {
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
const { within } = req.body
if (within === 'day') {
condition = `DATE( ${fieldName} ) = DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 DAY ) )`
} else if (within === 'week') {
condition = `DATE( ${fieldName} ) BETWEEN DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 WEEK ) ) AND DATE( "${dateNow}" )`
} else if (within === 'month') {
condition = `DATE( ${fieldName} ) BETWEEN DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 MONTH ) ) AND DATE( "${dateNow}" )`
} else {
condition = `DATE( ${fieldName} ) BETWEEN DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 YEAR ) ) AND DATE( "${dateNow}" )`
}
} else {
const fromDate = dateFormat(
new Date(moment(req.body.start_date, 'MM-DD-YYYY').format()),
'yyyy-mm-dd',
)
const toDate = dateFormat(
new Date(moment(req.body.end_date, 'MM-DD-YYYY').format()),
'yyyy-mm-dd',
)
condition = `DATE( ${fieldName} ) BETWEEN DATE('${fromDate}') AND DATE('${toDate}')`
}
return condition
}
/**
* @class class to handle report functions
*/
class adminReportModule {
static async userStatusUpdate(status, pid) {
const mysql = {}
const escapeData = [status, pid]
const strQuery = await mysqclass.mysqli(mysql, 'user_change_market_status')
const dataReturn = await global.mysql.query(strQuery, escapeData)
console.log('dataReturn', strQuery, escapeData)
return dataReturn
}
/**
* Get User Related Report
* @param {object} req req object
* @param {string} type report type
* @param {number} count count for the pagination
* @returns {object} sql response
*/
static async userRelatedReport(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
const defaultOrder = 'id'
const order = req.body.order === '' ? 'asc' : req.body.order
const orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
if (orderby !== '') {
mysql.order = ` order by ${orderby} ${order}`
}
if (type === 'buyerhistory') {
mysql.condition = conditionChange(req, 'b.date_added')
msq = '6255'
} else if (type === 'deactivate' || type === 'active') {
mysql.where = ''
mysql.where += ` u.status = "${type}" and `
mysql.condition = conditionChange(req, 'u.created_at')
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
mysql.where += ` (u.email like "%${changed}%" or u.first_name like "%${changed}%" or u.last_name like "%${changed}%") and`
}
mysql.limit = ''
if (req.body.limit_results !== '') {
mysql.limit = `limit 0,${req.body.limit_results}`
} else if (count !== 2) {
const pagen = (req.body.page - 1) * req.body.limit
mysql.limit = ` limit ${pagen},${req.body.limit}`
}
if (count === 0) {
msq = 'alluserscreated'
} else if (count === 1) {
msq = 'alluserscreated_limit'
} else {
msq = 'alluserscreated'
}
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
console.log('dataReturn', strQuery, escapeData)
return dataReturn
}
/**
* Get Product Related Report
* @param {object} req req object
* @param {string} type report type
* @param {number} count count for the pagination
* @returns {object} sql response
*/
static async productRelatedReport(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
mysql.limit = ''
if (req.body.limit_results !== '') {
mysql.limit = `limit 0,${req.body.limit_results}`
} else if (count !== 2) {
const pagen = (req.body.page - 1) * req.body.limit
mysql.limit = ` limit ${pagen},${req.body.limit}`
}
const defaultOrder = type === 'ending' ? 'p.date_closed' : 'p.id'
const order = req.body.order === '' ? 'asc' : req.body.order
const orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
if (orderby !== '') {
if (orderby === 'id') {
mysql.order = ` order by p.id ${order}`
} else if (orderby === 'date_added') {
mysql.order = ` order by p.date_added ${order}`
} else {
mysql.order = ` order by ${orderby} ${order}`
}
}
mysql.where = ''
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
mysql.where += ` (p.title like "%${changed}%" or p.id like "%${changed}%") and`
}
if (type === 'active') {
mysql.dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
mysql.condition = conditionChange(req, 'p.date_added')
if (count === 1) {
msq = 'activeproductsreports_limit'
} else {
msq = 'activeproductsreports'
}
} else if (type === 'closed') {
mysql.condition = conditionChange(req, 'date_closed')
if (count === 1) {
msq = 'closedproductsreports_limit'
} else {
msq = 'closedproductsreports'
}
} else if (type === 'sold') {
mysql.condition = conditionChange(req, 'p.date_added')
if (count === 1) {
msq = 'soldproductsreports_limit'
} else {
msq = 'soldproductsreports'
}
} else if (type === 'ending') {
mysql.condition = conditionChange(req, 'p.date_closed')
if (count === 1) {
msq = 'endingproductsreports_limit'
} else {
msq = 'endingproductsreports'
}
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
console.log('dataReturn', strQuery, escapeData)
return dataReturn
}
/**
* Get All Product User Related Report
* @param {object} req req object
* @param {string} type report type
* @param {number} count count for the pagination
* @returns {object} sql response
*/
static async allVehicleListUser(req, data) {
let mysql = ''
let where = ''
let msq = ''
const escapeData = []
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
where += ` and p.market_status = "open" and p.date_closed >= "${dateNow}" `
if (data === 'live') {
where += ` and p.date_added <= "${dateNow}" `
}
if (data === 'future') {
where += ` and p.date_added > "${dateNow}" `
}
if (typeof req.session.userid === 'undefined') {
where += 'and p.selltype = "all"'
} else {
where += `and (p.selltype = "all" or (p.selltype = "notall" && FIND_IN_SET( ${req.session.user_type}, p.selectedbuyer)))`
}
mysql = {
where,
order: 'order by p.id desc',
dateNow,
}
msq = 'liveuser_vehicles'
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
console.log('dataReturn', strQuery, escapeData)
return dataReturn
}
/**
* Get All Logs data report
* @param {object} data req.body object
* @returns {object} sql response
*/
static async allEmailLogsAdmin() {
const mysql = {}
const escapeData = []
const strQuery = await mysqclass.mysqli(mysql, '319_logs')
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* Get All Templates data report
* @param {object} data req.body object
* @returns {object} sql response
*/
static async allTemplatesAdmin(data) {
const mysql = {}
const escapeData = [data]
const strQuery = await mysqclass.mysqli(mysql, '819')
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* Get All User Related data report
* @param {object} data req.body object
* @returns {object} sql response
*/
static async userRelatedExport(data) {
const mysql = {}
let escapeData = []
if (data !== 'all') {
mysql.condition = 'and u.status = ?'
escapeData = [data]
} else {
mysql.condition = ''
}
const strQuery = await mysqclass.mysqli(mysql, 'allusersexport')
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* Get All Employees data report
* @param {object} data req.body object
* @returns {object} sql response
*/
static async employeeRelatedExport(data) {
const mysql = {}
let escapeData = []
if (data !== 'all') {
mysql.condition = 'and u.status = ?'
escapeData = [data]
} else {
mysql.condition = ''
}
const strQuery = await mysqclass.mysqli(mysql, 'allemployeesexport')
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
}
module.exports.default = adminReportModule