/* ============================================================================ *\
|| ########################################################################## ||
|| # 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 { default: mysqli } = require('../../front/modules/mysqli')
const mysqclass = require('./mysqli').default
const commonProduct = require('../../common/products').default
const conditionChange = (req, fieldName) => {
let condition = ''
if (req.body.action !== 'export') {
if (req.body.search === 'within') {
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
const { within } = req.body
if (within === 'day') {
condition = `AND DATE( ${fieldName} ) = DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 DAY ) )`
} else if (within === 'week') {
condition = `AND DATE( ${fieldName} ) BETWEEN DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 WEEK ) ) AND DATE( "${dateNow}" )`
} else if (within === 'month') {
condition = `AND DATE( ${fieldName} ) BETWEEN DATE( DATE_SUB( "${dateNow}" , INTERVAL 1 MONTH ) ) AND DATE( "${dateNow}" )`
} else {
condition = `AND 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 = `AND 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 old
* @param {object} req req object
* @param {string} type report type
* @param {number} count count for the pagination
* @returns {object} sql response
* @deprecated we are using userRelatedReport insted of this
*/
static async userRelatedReportOld(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' ||
type === 'unverified' ||
type === 'null'
) {
mysql.where = ''
if (type !== 'null') {
mysql.where += `and u.status = "${type}" `
}
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 += `and (u.email like "%${changed}%" or u.first_name like "%${changed}%" or u.last_name like "%${changed}%") and`
}
mysql.limit = ''
if (req.body.action !== 'export') {
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 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 = {}
const baseTableUsed = global.configColumns.users
const customTableUsed = global.configColumns.custom_users
let row = ''
let where = ''
let limitf = ''
let order = ''
const defaultOrder = 'id'
req.body.orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
order = `order by u.id desc`
if (req.body.orderby || req.body.order) {
const sortReturn = commonProduct.sortOrderData(req, where)
if (sortReturn.order) {
order = sortReturn.order
}
if (sortReturn.where) {
where += sortReturn.where
}
}
if (type !== 'null') {
where += `and u.status = "${type}" `
}
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
where += `and (u.email like "%${changed}%" or u.first_name like "%${changed}%" or u.last_name like "%${changed}%") `
}
where += conditionChange(req, 'u.created_at')
if (req.body.similars) {
where += commonProduct.similarData(req)
}
if (req.body.filters) {
where += commonProduct.filterData(req)
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.mainTableJoin = generatedData.mainTableJoin
mysql.customTableJoin = generatedData.customTableJoin
mysql.columns = generatedData.rowstoFetch
const additionalTable = global.configColumns.users
const customAdditionalTable = global.configColumns.custom_users
const additionalGeneratedData = commonProduct.additionalTableJoin(
{ ...additionalTable, short_name: 'uc' },
{ ...customAdditionalTable, short_name: 'cu2' },
baseTableUsed,
'left join',
'id',
'created_by',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData.customTableJoin}`
mysql.columns = mysql.columns.concat(additionalGeneratedData.rowstoFetch)
row = count === 1 ? 'alluserscreated_limit' : 'alluserscreated'
const limitrg = req.body.limit
let uidc = 0
if (req.user) {
uidc = typeof req.user.id === 'undefined' ? 0 : req.user.id
}
const escapeData = []
if (req.body.action !== 'export') {
if (req.body.limit_results !== '') {
limitf = `limit 0 , ${req.body.limit_results}`
} else if (count !== 1) {
let pagen = (req.body.page - 1) * limitrg
if (parseInt(pagen, 10) < 0) {
pagen = 0
}
limitf = ` limit ${pagen},${limitrg}`
}
}
mysql.order = order
mysql.where = where
mysql.limitf = limitf
mysql.dateNow = dateNow
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(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
* @deprecated we are using ProductRelatedReport insted of this.
*/
static async productRelatedReportOld(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
mysql.limit = ''
mysql.extra_coloumn = ''
// if (req.body.action !== 'export') {
// 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 += `and (p.title like "%${changed}%" or p.id like "%${changed}%") `
}
if (typeof global.configFeatures.project_follow_count !== 'undefined') {
if (parseInt(global.configFeatures.project_follow_count.enabled, 10) === 1) {
mysql.extra_coloumn +=
' ,(select count(w.id) from watchlists w where p.id = w.project_id and w.is_delete = 0) followCount '
}
}
if (type === 'open') {
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 === 'draft') {
mysql.dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
mysql.condition = conditionChange(req, 'p.date_added')
if (count === 1) {
msq = 'draftproductsreports_limit'
} else {
msq = 'draftproductsreports'
}
} else if (type === 'relisted') {
mysql.dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
mysql.condition = conditionChange(req, 'p.date_added')
if (count === 1) {
msq = 'relistedproductsreports_limit'
} else {
msq = 'relistedproductsreports'
}
} else if (type === 'closed' || type === 'unsold') {
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 Product Related Report
* @param {object} req req object
* @param {string} action action
* @param {number} count count for the pagination
* @returns {object} sql response
*/
static async productRelatedReport(req, action, count) {
const mysql = {}
const baseTableUsed = global.configColumns.projects
const customTableUsed = global.configColumns.custom_projects
let row = ''
let where = ''
let limitf = ''
let order = ''
let groupby = ''
const subQuery = ''
const defaultOrder = req.body.type === 'ending' ? 'p.date_closed' : 'p.id'
order = req.body.order === '' ? 'asc' : req.body.order
req.body.orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
if (action) {
if (action !== 'relisted') {
where += ' and p.is_relist = 0 '
}
}
order = `order by p.id desc`
if (req.body.orderby || req.body.order) {
const sortReturn = commonProduct.sortOrderData(req, where)
if (sortReturn.order) {
order = sortReturn.order
}
if (sortReturn.where) {
where += sortReturn.where
}
}
if (req.body.groupby) {
groupby += `GROUP BY ${req.body.groupby}`
} else if (action === 'open') {
groupby += `GROUP BY p.id`
}
if (req.body.similars) {
where += commonProduct.similarData(req)
}
if (req.body.filters) {
where += commonProduct.filterData(req)
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.customTableJoin = generatedData.customTableJoin
mysql.mainTableJoin = generatedData.mainTableJoin
mysql.columns = generatedData.rowstoFetch
const additionalTable = global.configColumns.buynow
const customAdditionalTable = { enabled: 0 }
const additionalGeneratedData = commonProduct.additionalTableJoin(
additionalTable,
customAdditionalTable,
baseTableUsed,
action === 'sold' ? 'inner join' : 'left join',
'project_id',
'id',
)
const additionalTable2 = global.configColumns.employees
const customAdditionalTable2 = { enabled: 0 }
const additionalGeneratedData2 = commonProduct.additionalTableJoin(
additionalTable2,
customAdditionalTable2,
baseTableUsed,
'left join',
'id',
'user_id',
)
const additionalTable3 = global.configColumns.users
const customAdditionalTable3 = global.configColumns.custom_users
const additionalGeneratedData3 = commonProduct.additionalTableJoin(
{ ...additionalTable3, short_name: 'uwin' },
{ ...customAdditionalTable3 },
additionalTable,
'left join',
'id',
'user_id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData.customTableJoin} ${additionalGeneratedData2.customTableJoin} ${additionalGeneratedData3.customTableJoin} `
mysql.columns = mysql.columns.concat(additionalGeneratedData.rowstoFetch)
mysql.columns = mysql.columns.concat(additionalGeneratedData2.rowstoFetch)
mysql.columns = mysql.columns.concat(additionalGeneratedData3.rowstoFetch)
const limitrg = req.body.limit
let uidc = 0
if (req.user) {
uidc = typeof req.user.id === 'undefined' ? 0 : req.user.id
}
const escapeData = []
if (req.body.action !== 'export') {
if (req.body.limit_results !== '') {
limitf = `limit 0,${req.body.limit_results}`
} else if (count !== 1) {
let pagen = (req.body.page - 1) * limitrg
if (parseInt(pagen, 10) < 0) {
pagen = 0
}
limitf = ` limit ${pagen},${limitrg}`
}
}
if (
action === 'sold' &&
req.body.filters &&
req.body.filters.auctiontype &&
req.body.filters.auctiontype.value === 'ttw'
) {
mysql.columns += ` ,(select id from ${customTableUsed.ext_name} where ttw_offer_project_id = p.id limit 1 ) isAlreadyOffered `
}
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
where += `and (p.title like "%${changed}%" or p.id like "%${changed}%") `
}
if (action === 'open') {
mysql.dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
where += conditionChange(req, 'p.date_added')
if (count === 1) {
row = 'activeproductsreports_limit'
} else {
row = 'activeproductsreports'
}
} else if (action === 'draft') {
mysql.dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
where += conditionChange(req, 'p.date_added')
if (count === 1) {
row = 'draftproductsreports_limit'
} else {
row = 'draftproductsreports'
}
} else if (action === 'relisted') {
mysql.dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
where += conditionChange(req, 'p.date_added')
if (count === 1) {
row = 'relistedproductsreports_limit'
} else {
row = 'relistedproductsreports'
}
} else if (action === 'closed' || action === 'unsold') {
where += conditionChange(req, 'date_closed')
if (count === 1) {
row = 'closedproductsreports_limit'
} else {
row = 'closedproductsreports'
}
} else if (action === 'sold') {
where += conditionChange(req, 'p.date_added')
if (count === 1) {
row = 'soldproductsreports_limit'
} else {
row = 'soldproductsreports'
}
} else if (action === 'ending') {
where += conditionChange(req, 'p.date_closed')
if (count === 1) {
row = 'endingproductsreports_limit'
} else {
row = 'endingproductsreports'
}
}
mysql.order = order
mysql.where = where
mysql.limitf = limitf
mysql.dateNow = dateNow
mysql.groupby = groupby
mysql.subQuery = subQuery
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* all Paid Reports
* @param {object} req req object
* @param {string} action action
* @param {number} count count for the pagination
* @returns {object} sql response
*/
static async allPaidReports(req, count) {
const type = 'sold'
const mysql = {}
let msq = ''
const escapeData = []
mysql.limit = ''
mysql.extra_coloumn = ''
if (req.body.action !== 'export') {
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 = '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 (typeof global.configFeatures.project_follow_count !== 'undefined') {
if (parseInt(global.configFeatures.project_follow_count.enabled, 10) === 1) {
mysql.extra_coloumn +=
' ,(select count(w.id) from watchlists w where p.id = w.project_id and w.is_delete = 0) followCount '
}
}
if (type === 'sold') {
mysql.condition = conditionChange(req, 'p.date_added')
if (count === 1) {
msq = 'all_paid_reports_limit'
} else {
msq = 'all_paid_reports'
}
}
const strQuery = await mysqclass.mysqli(mysql, msq)
console.log('dataReturn', strQuery, escapeData)
const dataReturn = await global.mysql.query(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
}
/**
* Get product related sold report
* @param {object} req req.body object
* @param {string} type requst type
* @param {number} count no of results which we want to fetch
* @returns {object} sql response
*/
static async productRelatedSoldReport(req, type, count) {
const mysql = {}
const escapeData = []
const baseTableUsed = global.configColumns.projects
const customTableUsed = global.configColumns.custom_projects
let row = ''
let where = ''
let limitf = ''
let order = ''
const groupby = ''
const subQuery = ''
mysql.limit = ''
const defaultOrder = type === 'ending' ? 'b.date_added' : 'b.id'
order = req.body.order === '' ? 'asc' : req.body.order
req.body.orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
order = `order by p.id desc`
if (req.body.orderby || req.body.order) {
const sortReturn = commonProduct.sortOrderData(req, where)
if (sortReturn.order) {
order = sortReturn.order
}
if (sortReturn.where) {
where += sortReturn.where
}
}
if (req.body.similars) {
where += commonProduct.similarData(req)
}
if (req.body.filters) {
where += commonProduct.filterData(req)
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.customTableJoin = generatedData.customTableJoin
mysql.mainTableJoin = generatedData.mainTableJoin
mysql.columns = generatedData.rowstoFetch
const additionalTable = global.configColumns.buynow
const customAdditionalTable = { enabled: 0 }
const additionalGeneratedData = commonProduct.additionalTableJoin(
additionalTable,
customAdditionalTable,
baseTableUsed,
'inner join',
'project_id',
'id',
)
const additionalTable3 = global.configColumns.users
const customAdditionalTable3 = global.configColumns.custom_users
const additionalGeneratedData3 = commonProduct.additionalTableJoin(
{ ...additionalTable3, short_name: 'ubn' },
{ ...customAdditionalTable3 },
additionalTable,
'left join',
'id',
'user_id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData.customTableJoin} ${additionalGeneratedData3.customTableJoin} `
mysql.columns = mysql.columns.concat(additionalGeneratedData.rowstoFetch)
mysql.columns = mysql.columns.concat(additionalGeneratedData3.rowstoFetch)
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
where += `and (p.title like "%${changed}%" or p.id like "%${changed}%") `
}
const limitrg = req.body.limit
if (req.body.action !== 'export') {
if (req.body.limit_results !== '') {
limitf = `limit 0,${req.body.limit_results}`
} else if (count !== 1) {
let pagen = (req.body.page - 1) * limitrg
if (parseInt(pagen, 10) < 0) {
pagen = 0
}
limitf = ` limit ${pagen},${limitrg}`
}
}
where += conditionChange(req, 'p.date_added')
if (count === 1) {
row = 'sold_orderproductsreports_limit'
} else {
row = 'sold_orderproductsreports'
}
mysql.order = order
mysql.where = where
mysql.limitf = limitf
mysql.dateNow = dateNow
mysql.groupby = groupby
mysql.subQuery = subQuery
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* employee Report old
* @param {object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
* @deprecated insted of this we are using employeeReport
*/
static async employeeReportOld(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
mysql.limit = ''
if (req.body.action !== 'export') {
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' ? 'e.created_at' : 'e.id'
const order = req.body.order === '' ? 'desc' : req.body.order
const orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
if (orderby !== '') {
if (orderby === 'id') {
mysql.order = ` order by e.id ${order}`
} else if (orderby === 'date_added') {
mysql.order = ` order by e.created_at ${order}`
} else {
mysql.order = ` order by ${orderby} ${order}`
}
}
mysql.where = ' '
mysql.condition = conditionChange(req, 'e.created_at')
if (count === 1) {
msq = 'employeesreports_limit'
} else {
msq = 'employeesreports'
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* employee Report old
* @param {object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
*/
static async employeeReport(req, type, count) {
const mysql = {}
let row = ''
let where = ''
let limitf = ''
let order = ''
const baseTableUsed = global.configColumns.employees
const customTableUsed = global.configColumns.custom_employees
const defaultOrder = type === 'ending' ? 'e.created_at' : 'e.id'
order = req.body.order === '' ? 'desc' : req.body.order
req.body.orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
// order = `order by e.id desc`
if (req.body.orderby || req.body.order) {
const sortReturn = commonProduct.sortOrderData(req, where)
if (sortReturn.order) {
order = sortReturn.order
}
if (sortReturn.where) {
where += sortReturn.where
}
}
// if (req.user.emp_type === 'employee') {
// mysql.where += ` and emp_type = "employee" and id != ${req.user.id}`
// } else if (req.user.emp_type === 'admin') {
// mysql.where += ` and emp_type = "employee" and id != ${req.user.id}`
// } else if (req.user.emp_type === 'super_admin') {
// mysql.where += ` and ( emp_type = "employee" or emp_type = "admin" or emp_type = "super_admin" )`
// } else {
// mysql.where += ` and id = 0`
// }
if (req.body.similars) {
where += commonProduct.similarData(req)
}
if (req.body.filters) {
where += commonProduct.filterData(req)
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.customTableJoin = generatedData.customTableJoin
mysql.mainTableJoin = generatedData.mainTableJoin
mysql.columns = generatedData.rowstoFetch
const limitrg = req.body.limit
let uidc = 0
if (req.user) {
uidc = typeof req.user.id === 'undefined' ? 0 : req.user.id
}
const escapeData = []
if (req.body.action !== 'export') {
if (req.body.limit_results !== '') {
limitf = `limit 0,${req.body.limit_results}`
} else if (count !== 1) {
let pagen = (req.body.page - 1) * limitrg
if (parseInt(pagen, 10) < 0) {
pagen = 0
}
limitf = ` limit ${pagen},${limitrg}`
}
}
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
where += `and (e.email like "%${changed}%" or e.first_name like "%${changed}%" or e.last_name like "%${changed}%") `
}
where += conditionChange(req, 'e.created_at')
row = count !== 1 ? 'employeesreports' : 'employeesreports_limit'
mysql.order = order
mysql.where = where
mysql.limit = limitf
mysql.dateNow = dateNow
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* user depost report
* @param {object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
*/
static async userDepositReport(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
mysql.limit = ''
if (req.body.action !== 'export') {
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' ? 'u.created_at' : 'u.id'
const order = req.body.order === '' ? 'desc' : req.body.order
const orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
if (orderby !== '') {
if (orderby === 'id') {
mysql.order = ` order by u.id ${order}`
} else if (orderby === 'date_added') {
mysql.order = ` order by u.created_at ${order}`
} else {
mysql.order = ` order by ${orderby} ${order}`
}
}
mysql.where = ' '
mysql.condition = conditionChange(req, 'u.created_at')
if (count === 1) {
msq = 'userdepositreports_limit'
} else {
msq = 'userdepositreports'
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* user Transcation report
* @param {object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
*/
static async userTranscationReport(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
mysql.limit = ''
if (req.body.action !== 'export') {
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' ? 'pr.created_at' : 'pr.cart_id'
const order = req.body.order === '' ? 'desc' : req.body.order
const orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
if (orderby !== '') {
if (orderby === 'id') {
mysql.order = ` order by pr.id ${order}`
} else if (orderby === 'date_added') {
mysql.order = ` order by pr.created_at ${order}`
} else {
mysql.order = ` order by ${orderby} ${order}`
}
}
mysql.where = ' '
mysql.condition = conditionChange(req, 'pr.created_at')
if (count === 1) {
msq = 'usertransreports_limit'
} else {
msq = 'usertransreports'
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* total amount invoice report
* @param {Object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
*/
static async totalAmountInviceReport(req, type) {
const mysql = {}
let msq = ''
const limit = ''
// let condition = ''
const typeCheck = type || ''
let escapeData = []
const result = []
msq = 'invoice_total'
const defaultOrder = type === 'ending' ? 'b.date_added' : 'b.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 b.id ${order}`
} else if (orderby === 'date_added') {
mysql.order = ` order by b.date_added ${order}`
} else {
mysql.order = ` order by ${orderby} ${order}`
}
}
mysql.condition = conditionChange(req, 'b.date_added')
if (typeCheck !== '') {
let typeCheck2
if (typeCheck === 'paid') {
typeCheck2 = 1
escapeData = ['paid']
} else {
typeCheck2 = 0
escapeData = ['unpaid']
}
mysql.where = ` and b.paid=${typeCheck2} `
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* total amount invoice report
* @param {Object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
*/
static async productRelatedInvoiceReport(req, type, count) {
const mysql = {}
const baseTableUsed = global.configColumns.buynow
const customTableUsed = { enabled: 0 }
let row = ''
let where = ''
let having = ''
let limitf = ''
let order = ''
const filterByDate = type === 'partial_paid' ? 'b.paid_date' : 'b.date_added'
const defaultOrder = type === 'ending' ? filterByDate : 'b.id'
order = req.body.order === '' ? 'asc' : req.body.order
req.body.orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
if (req.body.orderby || req.body.order) {
const sortReturn = commonProduct.sortOrderData(req, where)
if (sortReturn.order) {
order = sortReturn.order
}
if (sortReturn.where) {
where += sortReturn.where
}
}
if (req.body.similars) {
where += commonProduct.similarData(req)
}
if (req.body.filters) {
where += commonProduct.filterData(req)
}
if (req.body.having) {
having += commonProduct.havingData(req)
}
if (type !== '') {
let typeCheck2 = 0
if (type === 'paid') {
typeCheck2 = 1
}
if (type === 'partial_paid') {
typeCheck2 = 1
where += ` and b.paid="${typeCheck2}" and b.partial="${typeCheck2}"`
} else {
where += ` and b.paid="${typeCheck2}"`
}
} else {
where += ''
}
if (req.body.searchterm !== '' && req.body.searchterm !== undefined) {
let changed = req.body.searchterm.replace(/\\/g, '\\\\\\\\')
changed = changed.replace(/"/g, '\\"')
where += `and (u.email like "%${changed}%" or u.first_name like "%${changed}%" or u.last_name like "%${changed}%") `
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.customTableJoin = generatedData.customTableJoin
mysql.mainTableJoin = generatedData.mainTableJoin
mysql.columns = generatedData.rowstoFetch
const additionalTable2 = global.configColumns.projects
const customAdditionalTable2 = global.configColumns.custom_projects
const additionalGeneratedData2 = commonProduct.additionalTableJoin(
additionalTable2,
customAdditionalTable2,
baseTableUsed,
'inner join',
'id',
'project_id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData2.customTableJoin}`
mysql.columns = mysql.columns.concat(additionalGeneratedData2.rowstoFetch)
const additionalTable3 = global.configColumns.users
const customAdditionalTable3 = global.configColumns.custom_users
const additionalGeneratedData3 = commonProduct.additionalTableJoin(
additionalTable3,
customAdditionalTable3,
baseTableUsed,
'inner join',
'id',
'user_id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData3.customTableJoin}`
mysql.columns = mysql.columns.concat(additionalGeneratedData3.rowstoFetch)
const limitrg = req.body.limit
let uidc = 0
if (req.user) {
uidc = typeof req.user.id === 'undefined' ? 0 : req.user.id
}
const escapeData = []
if (req.body.action !== 'export') {
if (req.body.limit_results !== '') {
limitf = `limit 0,${req.body.limit_results}`
} else if (count !== 1) {
let pagen = (req.body.page - 1) * limitrg
if (parseInt(pagen, 10) < 0) {
pagen = 0
}
limitf = ` limit ${pagen},${limitrg}`
}
}
where += conditionChange(req, filterByDate)
row = count !== 1 ? 'invoice_orderproductsreports' : 'invoice_orderproductsreports_limit'
mysql.order = order
mysql.having = having
mysql.where = where
mysql.limitf = limitf
mysql.dateNow = dateNow
mysql.groupby = 'group by b.common_invoice'
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* product related auction lot old
* @param {Object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
* @deprecated insted of this we are using productRelatedAuctionLotReport
*/
static async productRelatedAuctionLotReport_old(req, type, count) {
const mysql = {}
let msq = ''
const escapeData = []
const typeCheck = type || ''
mysql.limit = ''
mysql.extra_coloumn = ''
if (req.body.action !== 'export') {
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' ? 'b.date_added' : 'b.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 b.id ${order}`
} else if (orderby === 'date_added') {
mysql.order = ` order by b.date_added ${order}`
} else {
mysql.order = ` order by ${orderby} ${order}`
}
}
if (typeCheck !== '') {
mysql.where = `and p.market_status="${typeCheck}" `
} else {
mysql.where = ''
}
if (typeof global.configFeatures.auction_follow_count !== 'undefined') {
if (parseInt(global.configFeatures.auction_follow_count.enabled, 10) === 1) {
mysql.extra_coloumn +=
' ,(select count(aw.id) from auction_watchlists aw where b.id = aw.auctionlot_id and aw.is_delete = 0) followCount '
}
}
mysql.condition = conditionChange(req, 'b.date_added')
if (count === 1) {
msq = 'auction_lot_orderproductsreports_limit'
} else {
msq = 'auction_lot_orderproductsreports'
}
const strQuery = await mysqclass.mysqli(mysql, msq)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* total amount invoice report
* @param {Object} req req.body object
* @param {string} type requst type
* @param {number} count count =1 limit result else fetch all result
* @returns {object} sql response
*/
static async productRelatedAuctionLotReport(req, type, count) {
const mysql = {}
const { action } = req.body
const baseTableUsed = global.configColumns.auctionlot
const customTableUsed = global.configColumns.custom_auctionlot
let row = ''
let where = ''
let having = ''
let limitf = ''
let order = ''
const defaultOrder = type === 'ending' ? 'ac.date_added' : 'ac.id'
order = req.body.order === '' ? 'asc' : req.body.order
req.body.orderby =
req.body.orderby === '' && !req.body.orderby ? defaultOrder : req.body.orderby
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
order = `order by ac.id desc`
if (req.body.orderby || req.body.order) {
const sortReturn = commonProduct.sortOrderData(req, where)
if (sortReturn.order) {
order = sortReturn.order
}
if (sortReturn.where) {
where += sortReturn.where
}
}
if (req.body.similars) {
where += commonProduct.similarData(req)
}
if (req.body.filters) {
where += commonProduct.filterData(req)
}
if (req.body.having) {
having += commonProduct.havingData(req)
}
if (type !== '') {
where += `and ac.market_status="${type}" `
} else {
where += ''
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.customTableJoin = generatedData.customTableJoin
mysql.mainTableJoin = generatedData.mainTableJoin
mysql.columns = generatedData.rowstoFetch
const additionalTable2 = global.configColumns.employees
const customAdditionalTable2 = { enabled: 0 }
const additionalGeneratedData2 = commonProduct.additionalTableJoin(
additionalTable2,
customAdditionalTable2,
baseTableUsed,
'left join',
'id',
'user_id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData2.customTableJoin}`
mysql.columns = mysql.columns.concat(additionalGeneratedData2.rowstoFetch)
const additionalTable3 = global.configColumns.projects
const customAdditionalTable3 = global.configColumns.custom_projects
const additionalGeneratedData3 = commonProduct.additionalTableJoin(
additionalTable3,
customAdditionalTable3,
baseTableUsed,
'left join',
'auctionid',
'id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData3.customTableJoin}`
mysql.columns = mysql.columns.concat(additionalGeneratedData3.rowstoFetch)
row =
count !== 1
? 'auction_lot_orderproductsreports'
: 'auction_lot_orderproductsreports_limit'
const limitrg = req.body.limit
let uidc = 0
if (req.user) {
uidc = typeof req.user.id === 'undefined' ? 0 : req.user.id
}
const escapeData = []
if (req.body.action !== 'export') {
if (req.body.limit_results !== '') {
limitf = `limit 0,${req.body.limit_results}`
} else if (count !== 1) {
let pagen = (req.body.page - 1) * limitrg
if (parseInt(pagen, 10) < 0) {
pagen = 0
}
limitf = ` limit ${pagen},${limitrg}`
}
}
if (typeof global.configFeatures.auction_follow_count !== 'undefined') {
if (parseInt(global.configFeatures.auction_follow_count.enabled, 10) === 1) {
mysql.columns +=
' ,(select count(aw.id) from auction_watchlists aw where ac.id = aw.auctionlot_id and aw.is_delete = 0) followCount '
}
}
where += conditionChange(req, 'ac.date_added')
mysql.order = order
mysql.having = having
mysql.where = where
mysql.limitf = limitf
mysql.dateNow = dateNow
mysql.groupby = ''
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
/**
* total amount invoice report
* @param {Object} req req.body object
* @returns {object} sql response
*/
static async bidHistory(req) {
const mysql = {}
const baseTableUsed = global.configColumns.bids
const customTableUsed = { enabled: 0 }
let row = ''
let where = ''
const limitf = ''
let order = ''
const dateNow = dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss')
if (typeof req.body.limit === 'undefined') {
req.body.limit = global.configurations.variables.search_limit || 25
}
if (typeof req.body.page === 'undefined') {
req.body.page = 1
}
order = `order by b.common_invoice desc `
if (req.body.search) {
where += ' and '
where += req.body.search
}
const generatedData = commonProduct.generateJoinWithColum(baseTableUsed, customTableUsed, [
'id',
])
mysql.customTableJoin = generatedData.customTableJoin
mysql.columns = generatedData.rowstoFetch
row = 'get_all_bid_history'
let uidc = 0
if (req.user) {
uidc = typeof req.user.id === 'undefined' ? 0 : req.user.id
}
const escapeData = []
mysql.userid = uidc
mysql.order = order
mysql.where = where
mysql.limitf = limitf
mysql.customTableJoin = generatedData.customTableJoin
mysql.columns = generatedData.rowstoFetch
mysql.mainTable = generatedData.mainTableJoin
const additionalTable = global.configColumns.users
const customAdditionalTable = global.configColumns.custom_users
const additionalGeneratedData = commonProduct.additionalTableJoin(
additionalTable,
customAdditionalTable,
baseTableUsed,
'inner join',
'id',
'user_id',
)
mysql.customTableJoin = `${mysql.customTableJoin} ${additionalGeneratedData.customTableJoin}`
mysql.columns = mysql.columns.concat(additionalGeneratedData.rowstoFetch)
const strQuery = await mysqclass.mysqli(mysql, row)
const dataReturn = await global.mysql.query(strQuery, escapeData)
return dataReturn
}
}
module.exports.default = adminReportModule