Need Help Urgently

[HDS]

Hello,

I need help on urgent basis. I have created the new entity "components" and made the relation with opportunity. now in components I have two fields 1) amount per unit, 2) Quantity.

I also added one more field Total amount,on components which is working fine by putting the below code on custom..entitydefs..

"amountPerUnitConvertedby": {
"type": "currency",
"readOnly": true,
"notStorable": false,
"select": "components.amount_per_unit * components.quantity",
"where": {
"=": "(components.amount_per_unit * components.quantity) = {value}",
"<": "(components.amount_per_unitt * components.quantity) < {value}",
">": "(components.amount_per_unit * components.quantity) > {value}",
"<=": "(components.amount_per_unit * components.quantity) <= {value}",
">=": "(components.amount_per_unit * components.quantity) >= {value}",
"<>": "(components.amount_per_unit * components.quantity) <> {value}"
},
"orderBy": "amountPerUnitConvertedby {direction}",
"required": false,
"audited": false
}


I want to add one field on opportunity screen, which should be amount_per_unit * Quantity and related with same opportunity, actually SUM of Total amount, i.e. SUM of amountPerUnitConvertedby, but this value is not getting stored in database.So I tried other-way like below
Like if I have linked multiple components in opportunity
1) amount_per_unit 100 and Quantity is 20 ,
2) amount_per_unit 200 and Quantity is 50 ,


the the read-only field should display sum of total "amount_per_unit"=200 multiples with "Quantity" 20 = 2000 and amount_per_unit 200 * Quantity is 50 = 10000 and now 2000 + 10000 = 12000 , should display 12000 , something like SUM related

I took the help from campaign entity, like below code, but my problem is the read-only field is calculating the total of all amount_per_unit from components (from entire entity rather related one), not with only related once with opportunity, I tried to put findRelated multiple of places but failed..I guess this is because, componentsId column is not there on opportunity table in database. it is there in opportunity_components, how can I ask the below code to relate with this.

This is the one problem and second one is how to multiply both and get one record

I tried to put (->getRepository('components')->findRelated($entity, 'components')) multiple of place but failed, kindly suggest where should i put this in below code ?

public function loadAdditionalFields(Entity $entity)
{
parent::loadAdditionalFields($entity);
$count = 0;
$count += $this->getEntityManager()->getRepository('components')->countRelated($entity, 'components');
$entity->set('type', $count); ( <--- This is working fine for me)

$entity->set('revenueCurrency', $this->getConfig()->get('defaultCurrency'));

$params = array(
'select' => array('SUM:amount_per_unit') , (<-- How to multiply this with quantity too, something like SUM(amount_per_unit * Quantity) for related one )
'whereClause' => array(
'componentsId' => $entity->id
),
'groupBy' => array('opportunity.componentsId') <-- (When I put this , app throws the error column not found, when I remove this app sums from entire entity rather related one)

);

$this->getEntityManager()->getRepository('components')->handleSelectParams($params);

$sql = $this->getEntityManager()->getQuery()->createSelectQuery('components', $params);


$pdo = $this->getEntityManager()->getPDO();
$sth = $pdo->prepare($sql);
$sth->execute();

if ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
$revenue = floatval($row['SUM:amount_per_unit']); (<-- How to multiply this with quantity too, something like SUM(amount_per_unit * Quantity) for related one )
if ($revenue > 0) {
$entity->set('revenue', $revenue);

I will appreciate if someone correct the above code, that will be a great help. Thanks in advance

Kindly see the attachment for reference

Question 2 - How to get total sum values in bottom of panel (kindly see the second print screen for reference)

[HDS]

Kindly reply, this is bit urgent for me..at-least say no if no one wants to help me...

[yuri]

Try to add "opportunity.componentsId" to select.

If you need to multiply columns you can do it only with result of sql query in php.

[HDS]

Please put some more light....how can i achive this....just help me little more ...

Kindly provide me some example select query and for multiply query

Kindly help me with some example code or if you have time then please correct the above code, this will be a great help

[HDS]

Kindly suggest some example please....

[yuri]

Some examples https://github.com/espocrm/espocrm/b.../QueryTest.php