Skip to content

Add files stats to asset selectquery #177

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
kambizzandi merged 4 commits into from
Jul 30, 2022
Merged

Add files stats to asset selectquery #177

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
6883c0a
change querybuilder for accepting nested from query
kambizzandi Jul 27, 2022
d1870b6
working on it
kambizzandi Jul 28, 2022
32d2aa9
nested from queries completed
kambizzandi Jul 30, 2022
254ab63
asset files completed
kambizzandi Jul 30, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 2 additions & 2 deletions App/Server/clsRequestHandler.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -149,8 +149,8 @@ void clsRequestHandler::process(const QString& _api) {
static auto decodePercentEncoding = [](QByteArray& _value) {
_value = _value.replace("+"," ");
QUrl URL = QUrl::fromPercentEncoding("http://127.0.0.1/?key=" + _value);
_value=URL.query(QUrl::FullyDecoded).toUtf8();
_value=_value.mid(_value.indexOf('=') + 1);
_value = URL.query(QUrl::FullyDecoded).toUtf8();
_value = _value.mid(_value.indexOf('=') + 1);
return _value;
};

Expand Down
22 changes: 13 additions & 9 deletions Interfaces/AAA/Accounting_Defs.hpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -239,7 +239,7 @@ namespace tblAccountUserAssetsBase {
}

namespace tblAccountUserAssetsFilesBase {
constexpr char Name[] = "tblAccountUserAssetsFiles";
constexpr char Name[] = "tblAccountUserAssets_files";

namespace Fields {
TARGOMAN_CREATE_CONSTEXPR(uasuflID);
Expand Down Expand Up @@ -590,7 +590,9 @@ namespace tblAccountSaleablesTranslateBase {
};

inline const QList<stuRelation> Relations(Q_DECL_UNUSED const QString& _schema) {
return {};
return {
//<Col Reference Table ForeignCol Rename LeftJoin
};
};

const QList<stuDBIndex> Indexes = {
Expand Down Expand Up @@ -629,7 +631,10 @@ namespace tblAccountSaleablesFilesBase {
};

inline const QList<stuRelation> Relations(Q_DECL_UNUSED const QString& _schema) {
return {};
return {
//<Col Reference Table ForeignCol Rename LeftJoin
{ Fields::slf_slbID, R(_schema, tblAccountSaleablesBase::Name), tblAccountSaleablesBase::Fields::slbID, "", true },
};
};

const QList<stuDBIndex> Indexes = {
Expand Down Expand Up @@ -680,13 +685,12 @@ namespace tblAccountUserAssetsBase {

inline const QList<stuRelation> Relations(Q_DECL_UNUSED const QString& _schema) {
return {
///< Col Reference Table ForeignCol Rename LeftJoin
{ Fields::uas_usrID, R(AAASchema, tblUser::Name), tblUser::Fields::usrID, "Owner_" },
//<Col Reference Table ForeignCol Rename LeftJoin
{ Fields::uas_usrID, R(AAASchema, tblUser::Name), tblUser::Fields::usrID, "Owner_" },
{ Relation::Saleable,
{ Fields::uas_slbID, R(_schema, tblAccountSaleablesBase::Name), tblAccountSaleablesBase::Fields::slbID, "", true } },
{ Fields::uas_cpnID, R(_schema, tblAccountCouponsBase::Name), tblAccountCouponsBase::Fields::cpnID, "", true },
//Voucher is not accessible as it is in another schema
//{tblAccountUserAssets::Fields::uas_vchID, R(AAASchema,tblVoucher::Name), tblVoucher::Fields::vchID, "", true},
{ Fields::uas_slbID, R(_schema, tblAccountSaleablesBase::Name), tblAccountSaleablesBase::Fields::slbID, "", true } },
{ Fields::uas_cpnID, R(_schema, tblAccountCouponsBase::Name), tblAccountCouponsBase::Fields::cpnID, "", true },
// { Fields::uas_vchID, R(AAASchema, tblVoucher::Name), tblVoucher::Fields::vchID, "", true},
};
};

Expand Down
131 changes: 129 additions & 2 deletions Interfaces/AAA/Accounting_Interfaces.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -412,6 +412,8 @@ bool IMPL_ORMDELETE(intfAccountSaleables) {
}

/******************************************************************/
intfAccountSaleablesFiles* intfAccountSaleablesFiles::myInstance;

intfAccountSaleablesFiles::intfAccountSaleablesFiles(
const QString& _schema,
const QList<DBM::clsORMField>& _exclusiveCols,
Expand All @@ -424,7 +426,9 @@ intfAccountSaleablesFiles::intfAccountSaleablesFiles(
tblAccountSaleablesFilesBase::Private::ORMFields + _exclusiveCols,
tblAccountSaleablesFilesBase::Private::Relations(_schema) + _exclusiveRelations,
tblAccountSaleablesFilesBase::Private::Indexes + _exclusiveIndexes
) { ; }
) {
intfAccountSaleablesFiles::myInstance = this;
}

QVariant IMPL_ANONYMOUSE_ORMGET(intfAccountSaleablesFiles) {
constexpr quint16 CACHE_TIME = 15 * 60;
Expand Down Expand Up @@ -466,6 +470,125 @@ intfAccountUserAssets::intfAccountUserAssets(
tblAccountUserAssetsBase::Private::Indexes + _exclusiveIndexes
) { ; }

/*
SELECT
tmpNeededFiles.*
, tmpProvidedFiles.*
, uas.*

FROM tblAccountUserAssets uas

LEFT JOIN (
SELECT slf_slbID
, SUM(slfMinCount) AS MandatoryFilesCount
FROM tblAccountSaleablesFiles slf
WHERE slfMinCount > 0
GROUP BY slf_slbID
) tmpNeededFiles
ON tmpNeededFiles.slf_slbID = uas.uas_slbID

LEFT JOIN (
SELECT
uasufl_uasID
, SUM(LEAST(slfMinCount, ProvidedFilesCount)) AS ProvidedMandatoryFilesCount

FROM (
SELECT
uasufl_uasID
, uasufl_slfID
, slfMinCount
, COUNT(*) AS ProvidedFilesCount
FROM tblAccountUserAssets_files uasufl
INNER JOIN tblAccountSaleablesFiles slf
ON slf.slfID = uasufl.uasufl_slfID
WHERE slfMinCount > 0
GROUP BY uasufl_uasID
, uasufl_slfID
) t1

GROUP BY uasufl_uasID
) tmpProvidedFiles
ON tmpProvidedFiles.uasufl_uasID = uas.uasID
*/
ORMSelectQuery intfAccountUserAssets::GetSelectQuery(INTFAPICALLBOOM_IMPL &APICALLBOOM_PARAM, const QString &_alias, Q_DECL_UNUSED bool _isRoot) {
ORMSelectQuery Query = intfSQLBasedModule::GetSelectQuery(APICALLBOOM_PARAM, _alias);

if (_isRoot) {
Query.addCols(this->SelectableColumnNames())
// LEFT JOIN (
// SELECT slf_slbID
// , SUM(slfMinCount) AS MandatoryFilesCount
// FROM tblAccountSaleablesFiles
// WHERE slfMinCount > 0
// GROUP BY slf_slbID
// ) tmpNeededFiles
// ON tmpNeededFiles.slf_slbID = tblAccountUserAssets.uas_slbID

.nestedLeftJoin(intfAccountSaleablesFiles::myInstance->GetSelectQuery(APICALLBOOM_PARAM, "", false)
.addCol(tblAccountSaleablesFilesBase::Fields::slf_slbID, tblAccountSaleablesFilesBase::Fields::slf_slbID)
.addCol(enuAggregation::SUM, tblAccountSaleablesFilesBase::Fields::slfMinCount, "MandatoryFilesCount")
.where({ tblAccountSaleablesFilesBase::Fields::slfMinCount, enuConditionOperator::Greater, 0 })
.groupBy(tblAccountSaleablesFilesBase::Fields::slf_slbID)
, "tmpNeededFiles"
, { "tmpNeededFiles", tblAccountSaleablesFilesBase::Fields::slf_slbID,
enuConditionOperator::Equal,
tblAccountUserAssetsBase::Name, tblAccountUserAssetsBase::Fields::uas_slbID }
)
.addCol(QString("tmpNeededFiles.") + tblAccountSaleablesFilesBase::Fields::slf_slbID)
.addCol(DBExpression::VALUE("IFNULL(tmpNeededFiles.MandatoryFilesCount, 0)"), "MandatoryFilesCount")

// LEFT JOIN (
// SELECT uasufl_uasID
// , SUM(LEAST(slfMinCount, ProvidedFilesCount)) AS ProvidedMandatoryFilesCount

// FROM (
// SELECT uasufl_uasID
// , uasufl_slfID
// , slfMinCount
// , COUNT(*) AS ProvidedFilesCount
// FROM tblAccountUserAssets_files
// INNER JOIN tblAccountSaleablesFiles
// ON tblAccountSaleablesFiles.slfID = tblAccountUserAssets_files.uasufl_slfID
// WHERE slfMinCount > 0
// GROUP BY uasufl_uasID
// , uasufl_slfID
// ) t1

// GROUP BY uasufl_uasID
// ) tmpProvidedFiles
// ON tmpProvidedFiles.uasufl_uasID = tblAccountUserAssets.uasID

.nestedLeftJoin(ORMSelectQuery(APICALLBOOM_PARAM,
intfAccountUserAssetsFiles::myInstance->GetSelectQuery(APICALLBOOM_PARAM, "", false)
.addCol(tblAccountUserAssetsFilesBase::Fields::uasufl_uasID)
.addCol(tblAccountUserAssetsFilesBase::Fields::uasufl_slfID)
.addCol(tblAccountSaleablesFilesBase::Fields::slfMinCount)
.addCol(enuAggregation::COUNT, tblAccountUserAssetsFilesBase::Fields::uasuflID, "ProvidedFilesCount")
.innerJoin(tblAccountSaleablesFilesBase::Name)
.where({ tblAccountSaleablesFilesBase::Fields::slfMinCount,
enuConditionOperator::Greater,
0 })
.groupBy({
tblAccountUserAssetsFilesBase::Fields::uasufl_uasID,
tblAccountUserAssetsFilesBase::Fields::uasufl_slfID
})
,
"t1")
.addCol(tblAccountUserAssetsFilesBase::Fields::uasufl_uasID, tblAccountUserAssetsFilesBase::Fields::uasufl_uasID)
.addCol(DBExpression::VALUE("SUM(LEAST(slfMinCount, ProvidedFilesCount))"), "ProvidedMandatoryFilesCount")
.groupBy(tblAccountUserAssetsFilesBase::Fields::uasufl_uasID)
, "tmpProvidedFiles"
, { "tmpProvidedFiles", tblAccountUserAssetsFilesBase::Fields::uasufl_uasID,
enuConditionOperator::Equal,
tblAccountUserAssetsBase::Name, tblAccountUserAssetsBase::Fields::uasID }
)
.addCol(DBExpression::VALUE("IFNULL(tmpProvidedFiles.ProvidedMandatoryFilesCount, 0)"), "ProvidedMandatoryFilesCount")
;
}

return Query;
}

QVariant IMPL_ORMGET(intfAccountUserAssets) {
if (Authorization::hasPriv(APICALLBOOM_PARAM, this->privOn(EHTTP_GET, this->moduleBaseName())) == false)
this->setSelfFilters({{tblAccountUserAssetsBase::Fields::uas_usrID, APICALLBOOM_PARAM.getUserID() }}, _filters);
Expand Down Expand Up @@ -510,6 +633,8 @@ bool IMPL_REST_UPDATE(intfAccountUserAssets, disablePackage, (
}

/******************************************************************/
intfAccountUserAssetsFiles* intfAccountUserAssetsFiles::myInstance;

intfAccountUserAssetsFiles::intfAccountUserAssetsFiles(
const QString& _schema,
const QList<DBM::clsORMField>& _exclusiveCols,
Expand All @@ -522,7 +647,9 @@ intfAccountUserAssetsFiles::intfAccountUserAssetsFiles(
tblAccountUserAssetsFilesBase::Private::ORMFields + _exclusiveCols,
tblAccountUserAssetsFilesBase::Private::Relations(_schema) + _exclusiveRelations,
tblAccountUserAssetsFilesBase::Private::Indexes + _exclusiveIndexes
) { ; }
) {
intfAccountUserAssetsFiles::myInstance = this;
}

QVariant IMPL_ORMGET(intfAccountUserAssetsFiles) {
Authorization::checkPriv(APICALLBOOM_PARAM, this->privOn(EHTTP_GET, this->moduleBaseName()));
Expand Down
12 changes: 12 additions & 0 deletions Interfaces/AAA/Accounting_Interfaces.h
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -162,6 +162,10 @@ class intfAccountSaleablesFiles : public intfSQLBasedModule
//public:
// virtual ORMSelectQuery GetSelectQuery(INTFAPICALLBOOM_DECL &APICALLBOOM_PARAM, const QString &_alias = {}, bool _isRoot = true);

protected:
static intfAccountSaleablesFiles* myInstance;
friend class intfAccountUserAssets;

private slots:
QVariant ANONYMOUSE_ORMGET("Get Available Saleables Files")
quint32 ORMCREATE("Create a new Saleables File by priviledged user")
Expand All @@ -182,8 +186,12 @@ class intfAccountUserAssets : public intfSQLBasedModule
const QList<DBM::stuRelation>& _exclusiveRelations = {},
const QList<DBM::stuDBIndex>& _exclusiveIndexes = {});

public:
virtual ORMSelectQuery GetSelectQuery(INTFAPICALLBOOM_DECL &APICALLBOOM_PARAM, const QString &_alias = {}, bool _isRoot = true);

private slots:
QVariant ORMGET("Get User Assets")

bool REST_UPDATE(
disablePackage,
(
Expand Down Expand Up @@ -214,6 +222,10 @@ class intfAccountUserAssetsFiles : public intfSQLBasedModule
const QList<DBM::stuRelation>& _exclusiveRelations = {},
const QList<DBM::stuDBIndex>& _exclusiveIndexes = {});

protected:
static intfAccountUserAssetsFiles* myInstance;
friend class intfAccountUserAssets;

private slots:
QVariant ORMGET("Get User Assets Files")

Expand Down
4 changes: 2 additions & 2 deletions Interfaces/AAA/intfAccountingBasedModule.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1171,7 +1171,7 @@ void intfAccountingBasedModule::computeCouponDiscount(
QVariantMap DiscountInfo = this->AccountCoupons->GetSelectQuery(APICALLBOOM_PARAM)
.addCols(this->AccountCoupons->SelectableColumnNames())

.nestedLeftJoin(this->AccountUserAssets->GetSelectQuery(APICALLBOOM_PARAM)
.nestedLeftJoin(this->AccountUserAssets->GetSelectQuery(APICALLBOOM_PARAM, "", false)
.addCols({
tblAccountUserAssetsBase::Fields::uas_cpnID,
tblAccountUserAssetsBase::Fields::uas_vchID,
Expand All @@ -1190,7 +1190,7 @@ void intfAccountingBasedModule::computeCouponDiscount(
)
.addCol("tmp_cpn_count._discountUsedCount")

.nestedLeftJoin(this->AccountUserAssets->GetSelectQuery(APICALLBOOM_PARAM)
.nestedLeftJoin(this->AccountUserAssets->GetSelectQuery(APICALLBOOM_PARAM, "", false)
.addCol(tblAccountUserAssetsBase::Fields::uas_cpnID)
.addCol(enuAggregation::SUM, tblAccountUserAssetsBase::Fields::uasDiscountAmount, "_discountUsedAmount")
.where({ tblAccountUserAssetsBase::Fields::uas_usrID, enuConditionOperator::Equal, CurrentUserID })
Expand Down
Loading