[Spce-user] [EXTERNAL] RE: AW: Resellers Operating Logics

[Henk]

Hi Danny,

In the CE version it's difficult to get information on your reseller. 
Maybe you can use the "Billing data export" feature described in the manual.

I made my own script to export what I need to a csv file, so I have full 
control of the format.

Attached is an example, you can run it from a cron job in the beginning 
of the month, only change directory and reseller domains. If you want to 
edit the SQL I advice you to use an editor like notepad++ to highlight 
the syntax as in a normal editor this is unreadable.

Regards,

Henk Plessius

VoipDigit

On 22-May-24 16:37, rkrenn--- via Spce-user wrote:
>
> >Ok, so is it correct that I set the peering profile for my reseller and the price list agreed between us for my 
> reseller's customers?
>
> carrier costs are defined by billing profiles of contracts assigned to 
> a ngcp peergroup entity
>
> ->    billingprofile with 0.xx cents/second your peering providers are 
> charging you as an platform operator (company1) for calls leaving the 
> ngcp platform
>
> reseller costs are defined by billing profiles of contracts assigned 
> to a ngpc reseller entity (company 2)
>
> ->    billingprofile with 0.02 cents/minute you will charge company 2
>
> customer costs are defined by billing profiles of contracts assigned 
> to a ngcp subscriber entity (company 2’s subscribers)
>
> ->    billingprofile withCompany 2's subscriber price list, which 
> company 2 can assign to its susbcribers.
>
> >Is there a way to generate the invoice with the total traffic developed by the reseller's subscribers?
>
> Yes newer ngcp version support generating PDF invoices for DID (pbx), 
> reseller and peering/carrier costs.
>
> please check with sales at sipwise.com if this functionality is available 
> in the CE version.
>
> br
>
> *Von:*Danny Cappellari <cappellari at connectlife.it>
> *Gesendet:* Mittwoch, 22. Mai 2024 15:44
> *An:* rkrenn at sipwise.com; mcapetta <mcapetta at sipwise.com>
> *Cc:* spce-user <spce-user at lists.sipwise.com>
> *Betreff:* [EXTERNAL] RE: AW: [Spce-user] Resellers Operating Logics
>
> I'll rephrase the question more specifically
>
> Company 1 (us)
> Company 2 (reseller of Company1)
>
> Company 1 agrees a price of 0.02 cents/minute to Italy with Company 2
>
> Company 1 sets the carrier cost profile on Company 2 and the 
> subscribers of Company 2 the profile of 0.02 cents/minute
>
> In reality, for Azienda 2 subscribers we should set the price list 
> that Azienda 2 applies to them..
>
> Company 1 is not interested in Company 2's subscriber price list, so 
> how does it bill Company 2's subscriber traffic?
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20240523/cdff00a2/attachment.htm>
-------------- next part --------------
#!/bin/bash
# Calculate reseller cost for domain below from the last month
# on line 27 you can change the directory and exported filename. Don't forget to fill in the domain names.

process_domain(){
  echo "Processing $1 ..."
	mysql accounting -t<<SQLEND
		SELECT * FROM (
			SELECT from_unixtime(start_time) as stime,source_account_id,source_external_subscriber_id,source_external_contract_id,source_cli,INSERT(destination_user_in,LENGTH(destination_user_in)-2,3,'XXX'),
			(select detail from billing.billing_zones where billing.billing_zones.id=source_reseller_billing_zone_id),
			(select zone from billing.billing_zones where billing.billing_zones.id=source_customer_billing_zone_id),
			round(duration,1),round(source_reseller_cost/100,4), round(source_customer_cost/100,4)
			FROM cdr WHERE start_time >= UNIX_TIMESTAMP(DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' ))
			AND start_time < UNIX_TIMESTAMP(DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' ))
			AND call_status='OK' AND source_domain LIKE '%$1%' AND source_account_id > 190
			UNION ALL (
			SELECT CHAR(160),' ',' ','Totals:',' ',' ',' ',' ',round(sum(duration),1),round(sum(source_reseller_cost/100),2),round(sum(source_customer_cost/100),2)
			FROM cdr WHERE start_time >= UNIX_TIMESTAMP(DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' ))
			AND start_time < UNIX_TIMESTAMP(DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' ))
			AND call_status='OK' AND source_domain LIKE '%$1%' AND source_account_id > 190
			)
		) T2
		ORDER BY stime
		INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' starting by '';
SQLEND
  if [ "$?" = "0" ]; then
	fn='/var/backup/cdr/'$1_cdr_`date -d "$(date -d '-1 month' +%Y-%m-15)" +%b`.csv
	#Replace spaces with _
	fn="${fn// /_}"
	#To lowercase
	fn=${fn,,}
	echo "StartTime;Source_account_id;Ext_subscriber_id;Ext_contract_id;Source;Destination;Reseller zone;Zone;Duration;Reseller Cost;Client Cost" > $fn
	cat /tmp/data.csv >> $fn
	rm /tmp/data.csv
    echo "done"
  else
	echo "SQL Error!" 1>&2
	exit 1
  fi
}

process_domain 'reseller1.com'
process_domain 'reseller2.com'