Jump to content

How to add custom field GSTIN Into Sales Tax Liability reports

grapyhost

By grapyhost
in Developer Corner

 Share

Recommended Posts

grapyhost Junior Member

grapyhost

Posted
Posted

Hi,

I have added custom client field name - GSTIN so i want this fields should show in  Sales Tax Liability reports.  

In Code - 

$reportdata["tableheadings"] = array(
    $aInt->lang('fields', 'invoiceid'),
    $aInt->lang('fields', 'clientname'),
    $aInt->lang('fields', 'invoicedate'),
    $aInt->lang('fields', 'datepaid'),
    $aInt->lang('fields', 'subtotal'),
    $aInt->lang('fields', 'tax'),
    $aInt->lang('fields', 'credit'),
    $aInt->lang('fields', 'total'),
    $aInt->lang('fields', 'taxid'),
);

 

last field not working,  any specific or how to identify that field.

 

Thanks

grapyhost

Link to comment
Share on other sites
sentq Senior Member

sentq

Posted
Posted (edited)

I've edited the original report to query your custom field value, create new file inside /modules/reports/ directory (as any changes to the original file will be overwritten when WHMCS updated) and copy the following code inside, test :)

<?php

/**
 * WHMCS Sales Tax Liability Report
 * 
 * Edited version to display "GSTIN" custom field value
 * 
 * @author SENTQ (19/11/2017)
 * 
 * 
 */

if (!defined("WHMCS"))
    die("This file cannot be accessed directly");

$reportdata["title"] = "Sales Tax Liability";
$reportdata["description"] = "This report shows sales tax liability for the selected period";

$reportdata["currencyselections"] = true;

$query = "select year(min(date)) as minimum, year(max(date)) as maximum from tblaccounts;";
$result = full_query($query);
$data = mysql_fetch_array($result);
$minyear = $data['minimum'];
$maxyear = $data['maximum'];

if (!$startdate) {
    $startdate = fromMySQLDate(date('Y-m-d'));
}
if (!$enddate) {
    $enddate = fromMySQLDate(date('Y-m-d'));
}

$queryStartDate = db_make_safe_human_date($startdate);
$queryEndDate = db_make_safe_human_date($enddate);
$currencyID = (int) $currencyid;

$reportdata["headertext"] = "<form method=\"post\" action=\"?report=$report&currencyid=$currencyid&calculate=true\"><center>Start Date: <input type=\"text\" name=\"startdate\" value=\"$startdate\" class=\"datepick\" />     End Date: <input type=\"text\" name=\"enddate\" value=\"$enddate\" class=\"datepick\" />     <input type=\"submit\" value=\"Generate Report\"></form>";

if ($calculate) {

    $query = <<<QUERY
SELECT COUNT(*), SUM(total), SUM(tblinvoices.credit), SUM(tax), SUM(tax2)
FROM tblinvoices
INNER JOIN tblclients ON tblclients.id = tblinvoices.userid
WHERE datepaid >= '{$queryStartDate}'
    AND datepaid <= '{$queryEndDate} 23:59:59'
    AND tblinvoices.status = 'Paid'
    AND currency = {$currencyID}
    AND (SELECT count(tblinvoiceitems.id)
        FROM tblinvoiceitems
        WHERE invoiceid = tblinvoices.id
            AND (type = 'AddFunds' OR type = 'Invoice')
        ) = 0;
QUERY;
    $result = full_query($query);
    $data = mysql_fetch_array($result);
    $numinvoices = $data[0];
    $total = $data[1] + $data[2];
    $tax = $data[3];
    $tax2 = $data[4];

    if (!$total) $total="0.00";
    if (!$tax) $tax="0.00";
    if (!$tax2) $tax2="0.00";

    $reportdata["headertext"] .= "<br>$numinvoices Invoices Found<br><B>Total Invoiced:</B> ".formatCurrency($total)."   <B>Tax Level 1 Liability:</B> ".formatCurrency($tax)."   <B>Tax Level 2 Liability:</B> ".formatCurrency($tax2);
}

$reportdata["headertext"] .= "</center>";

$reportdata["tableheadings"] = array(
    $aInt->lang('fields', 'invoiceid'),
    $aInt->lang('fields', 'clientname'),
    $aInt->lang('fields', 'invoicedate'),
    $aInt->lang('fields', 'datepaid'),
    $aInt->lang('fields', 'subtotal'),
    $aInt->lang('fields', 'tax'),
    $aInt->lang('fields', 'credit'),
    $aInt->lang('fields', 'total'),
    "GSTIN"
);

$query = <<<QUERY
SELECT tblinvoices.*, tblclients.firstname, tblclients.lastname
FROM tblinvoices
INNER JOIN tblclients ON tblclients.id = tblinvoices.userid
WHERE datepaid >= '{$queryStartDate}'
    AND datepaid <= '{$queryEndDate} 23:59:59'
    AND tblinvoices.status = 'Paid'
    AND currency = {$currencyID}
    AND (SELECT count(tblinvoiceitems.id)
        FROM tblinvoiceitems
        WHERE invoiceid = tblinvoices.id
            AND (type = 'AddFunds' OR type = 'Invoice')
        ) = 0
ORDER BY date ASC;
QUERY;
$result = full_query($query);
while ($data = mysql_fetch_array($result)) {
    $id = $data["id"];
    $userid = $data["userid"];
    $client = $data["firstname"]." ".$data["lastname"];
    $date = fromMySQLDate($data["date"]);
    $datepaid = fromMySQLDate($data["datepaid"]);
    $currency = getCurrency($userid);
    $subtotal = $data["subtotal"];
    $credit = $data["credit"];
    $tax = $data["tax"]+$data["tax2"];
    $total = $data["total"] + $credit;
    
    # Get Custom Field Value
    $getCustomFieldValue = full_query("SELECT `value` FROM `tblcustomfieldsvalues` INNER JOIN `tblcustomfields` ON (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) WHERE `tblcustomfields`.`type` = 'client' AND `fieldname` = 'Your GSTIN No.' AND `tblcustomfieldsvalues`.`relid` = '".$data["userid"]."' LIMIT 1");
    $getCustomFieldValue = mysql_fetch_assoc($getCustomFieldValue);
    
    $reportdata["tablevalues"][] = array("$id","$client","$date","$datepaid","$subtotal","$tax","$credit","$total", "$getCustomFieldValue['value']");
}

$data["footertext"]="This report excludes invoices that affect a clients credit balance "
    . "since this income will be counted and reported when it is applied to invoices for products/services.";

 

Edited by sentq
Changed custom field name
Link to comment
Share on other sites
grapyhost Junior Member

grapyhost

Posted
  • Author
Posted

Hello Sentq,

 

Thanks for reply.  but getting 500 blank page error.    for more information, I am using custom client fields exact name is - Your GSTIN No.

and 

$taxname

Origin: "Smarty object"

Value

"IGST"

Please modify the code, and update me same.

 

Thanks

M. Malhotra

Link to comment
Share on other sites
sentq Senior Member

sentq

Posted
Posted
4 hours ago, grapyhost said:

Hello Sentq,

 

Thanks for reply.  but getting 500 blank page error.    for more information, I am using custom client fields exact name is - Your GSTIN No.

and 

$taxname

Origin: "Smarty object"

Value

"IGST"

Please modify the code, and update me same.

 

Thanks

M. Malhotra

I've updated the code above to use the custom field name you mentioned "Your GSTIN No.", if you still get 500 error page then enable display errors features and try again error message will be displayed then.

It was already tested before I publish it here yesterday, so let me know.

Link to comment
Share on other sites
grapyhost Junior Member

grapyhost

Posted
  • Author
Posted

Getting same 500 error.  and after display errors - 

 

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /home/bagflnet/public_html/modules/reports/sales_tax_liability.php on line 116

 

Thanks

Link to comment
Share on other sites
brian! Senior Member

brian!

Posted
Posted

try... 

   # Get Custom Field Value
    $getCustomFieldValue = full_query("SELECT `value` FROM `tblcustomfieldsvalues` INNER JOIN `tblcustomfields` ON (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) WHERE `tblcustomfields`.`type` = 'client' AND `tblcustomfields`.`fieldname` = 'Your GSTIN No.' AND `tblcustomfieldsvalues`.`relid` = '".$data["userid"]."' LIMIT 1");
    $getCustomFieldValue = mysql_fetch_assoc($getCustomFieldValue);
	$value = $getCustomFieldValue['value'];
    
    $reportdata["tablevalues"][] = array("$id","$client","$date","$datepaid","$subtotal","$tax","$credit","$total","$value");

this for me is working on v7.4 (using a different custom field) - the fact that you're using v7 shouldn't make any difference as I believe the v7 and v7.4 Sales Tax reports are identical.

Link to comment
Share on other sites
sentq Senior Member

sentq

Posted
Posted
13 hours ago, grapyhost said:

Getting same 500 error.  and after display errors - 

 

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /home/bagflnet/public_html/modules/reports/sales_tax_liability.php on line 116

 

Thanks

replace: 

$reportdata["tablevalues"][] = array("$id","$client","$date","$datepaid","$subtotal","$tax","$credit","$total", "$getCustomFieldValue['value']");

with: 

$reportdata["tablevalues"][] = array("$id","$client","$date","$datepaid","$subtotal","$tax","$credit","$total", "{$getCustomFieldValue['value']}");

tested with v7.4.1 but it should work with older versions the same.

 

Link to comment
Share on other sites
brian! Senior Member

brian!

Posted
Posted
3 hours ago, rabijit said:

Is it possible to show tax as CGST, SGST, and IGST?

in theory yes, but I don't know how you've assigned these taxes in WHMCS? I know the first two are 9% and the other is 18%... but if the information is in the database, then it can be in the report.

in the above report, the tax value is a total of tax 1 and tax 2... so they could easily be split, but whether that's enough for what you want, i'm unsure. :?:

btw - there is a GST addon that includes a GST report, but it isn't free! :)

Link to comment
Share on other sites
brian! Senior Member

brian!

Posted
Posted
2 minutes ago, rabijit said:

Yes, It is in the database as tax1 and tax2.

in that case, try this (untested!)..

change 

    $aInt->lang('fields', 'tax'),

to 

"TAX1",
"TAX2",

these should really be called "CGST", "SGST", and "IGST" - but just call them tax 1 & 2 for now 

$tax = $data["tax"]+$data["tax2"];

becomes... 

$tax1 = $data["tax"];
$tax2 = $data["tax2"];

and then replace the reportdata line... 

$reportdata["tablevalues"][] = array("$id","$client","$date","$datepaid","$subtotal","$tax1","$tax2","$credit","$total", "$getCustomFieldValue['value']");

see if that works, then examine one of the results in detail to see what TAX1 & TAX2 should be renamed to in the report headings.

Link to comment
Share on other sites
Guest
This topic is now closed to further replies.
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated

  I accept